Simplifier une formule

Bonjour,
C'est incompréhensible ..... même avec le classeur !

My Babie, ma voisine des Abymes, tu crois vraiment qu'un simple bénévole, de passage sur CCM, va pouvoir consacrer assez de son temps à décortiquer, analyser et comprendre ta formule-usine à gaz ? Tu sais qu'en cellule G16 il y a 3211 caractères ?
Tu pourrais déjà alléger en utilisant des noms de formule définis ; par exemple, dans une copie de ton fichier, étant dans G16, tu définis le nom EM pour la formule =ENT((MOIS(A16)-1)/6)+1
Dans le même esprit, tu saisis "ER SEMESTRE " en Z1 et "EME SEMESTRE " en Z2 et tu définis les noms RS et MS pour ces 2 cellules.
Puis tu sélectionnes F16:G16 (il ne faut pas prendre une cellule isolée) et utilises ctrl+H pour remplacer d'abord
ENT((MOIS(A16)-1)/6)+1 par EM
puis pour remplacer "ER SEMESTRE " par RS et "EME SEMESTRE " par MS.

Bonsoir à tous,

je réponds surtout au titre de ce post : "simplifier une formule"
voilà le résultat :

En C16 :

 =SI(à_traiter;SIERREUR(TRIG_du_mois_du_jour_dans_semestre_code_J_et_fonction;SIERREUR(TRIG_du_mois_du_jour_dans_semestre_code_R_et_fonction;""));"")

En E16 :

=SI(à_traiter;SI(pas_code_J_ni_correspondance_fonction_saisie;SIERREUR(TRIG_2_du_mois_du_jour_dans_semestre_code_R_et_fonction;"");SIERREUR(TRIG_du_mois_du_jour_dans_semestre_code_R_et_fonction;""));"")

En G16 :

=SI(à_traiter;SI(pas_code_J_ni_correspondance_fonction_saisie;SIERREUR(TRIG_3_du_mois_du_jour_dans_semestre_code_R_et_fonction;"");SIERREUR(TRIG_2_du_mois_du_jour_dans_semestre_code_R_et_fonction;""));"")

Ces 3 formules sont en matriciel donc validation par CTRL+MAJ+ENTER

Quelques noms à créer, en se positionnant sur une cellule de la ligne 16 :

à_traiter
=OU('Dde de remplacement'!$B16="M";'Dde de remplacement'!$B16="N";'Dde de remplacement'!$B16="A")
Commentaire : on ne calcule quelque chose que si la colonne B contient "M" ou "N" ou "A". Sinon, chaine vide.

data_du_semestre
=INDIRECT(onglet_du_semestre&"!$B$9:$HA$43")
Commentaire : contient les données du tableau situé dans l'onglet "onglet_du_semestre"

data_jour_dans_semestre
=INDEX(data_du_semestre;0;indice_jour_dans_semestre)
Commentaire : contient la colonne du jour dans les données du tableau situé dans l'onglet "onglet_du_semestre"

fonction_du_mois_du_jour_dans_semestre
=DECALER(TRIG_1er_mois_Semestre;0;indice_colonne_fonction_du_jour_dans_semestre)
Commentaire : il s'agit de la colonne "fonction" du sous-tableau du mois de la date ('Dde de remplacement'!$A16). Pour l'identifier, on déplace la 1ère colonne TRIG du semestre d'un certain nombre de colonnes.

fonction_saisie
="("&'Dde de remplacement'!$G$10&")"
Commentaire : contient la valeur de la fonction saisie en $G$10 de l'onglet 'Dde de remplacement'

indice_colonne_fonction_du_jour_dans_semestre
=indice_jour_dans_semestre-JOUR('Dde de remplacement'!$A16)-1
Commentaire : contient l'indice de la colonne "fonction" du mois du jour ('Dde de remplacement'!$A16) dans le tableau des données du semestre concerné

indice_colonne_TRIG_du_jour_dans_semestre
=indice_colonne_fonction_du_jour_dans_semestre-1
Commentaire : contient l'indice de la colonne "TRIG" du mois du jour ('Dde de remplacement'!$A16) dans le tableau des données du semestre concerné. C'est la colonne juste à gauche de celle des fonctions, d'où la simple différence de 1 entre les 2 indices.

indice_jour_dans_semestre
=EQUIV('Dde de remplacement'!$A16;ligne_des_dates_du_semestre;0)
Commentaire : contient l'indice de la colonne du jour ('Dde de remplacement'!$A16) dans le tableau des données du semestre concerné

ligne_des_dates_du_semestre =INDIRECT(onglet_du_semestre&"!$B$7:$HA$7")
Commentaire : contient la ligne des dates (la n° 7) dans le tableau des données du semestre concerné

lignes_code_J_et_fonction
=SI((fonction_du_mois_du_jour_dans_semestre=fonction_saisie)*(data_jour_dans_semestre="J");LIGNE(INDIRECT(onglet_du_semestre&"!$D$9:$D$43"))-8;9^9)
Commentaire : tableau intermédiaire des lignes répondant aux critères "J" pour la colonne du jour (data_jour_dans_semestre) et fonction_saisie pour la colonne fonction_du_mois_du_jour_dans_semestre

lignes_code_R_et_fonction
=SI((fonction_du_mois_du_jour_dans_semestre=fonction_saisie)*(data_jour_dans_semestre="R");LIGNE(INDIRECT(onglet_du_semestre&"!$D$9:$D$43"))-8;9^9)
Commentaire : même tableau que le précédent mais pour le code "R".

no_lig_code_J_et_fonction
=MIN(lignes_code_J_et_fonction)
Commentaire : contient le premier n° de ligne de lignes_code_J_et_fonction

no_lig_code_R_et_fonction
=MIN(lignes_code_R_et_fonction)
Commentaire : contient le premier n° de ligne de lignes_code_R_et_fonction

onglet_du_semestre
="'"&ENT((MOIS('Dde de remplacement'!$A16)-1)/6)+1&SI(ENT((MOIS('Dde de remplacement'!$A16)-1)/6)+1=1;"ER SEMESTRE ";"EME SEMESTRE ")&ANNEE('Dde de remplacement'!$A16)&"'"
Commentaire : contient le nom de l'onglet contenant la date 'Dde de remplacement'!$A16

pas_code_J_ni_correspondance_fonction_saisie
=SIERREUR(TRIG_du_mois_du_jour_dans_semestre_code_J_et_fonction;"")=""
Commentaire : booléen indiquant que l'on n'a rien trouvé correspondant aux 2 conditions "J" et "fonction". On sera donc amené à dérouler la règle de gestion suivante sur les "R" et "fonction".

TRIG_1er_mois_Semestre
=INDIRECT(onglet_du_semestre&"!$B$9:$B$43")
Commentaire : il s'agit de la 1ère colonne "TRIG" du semestre concerné.

TRIG_2_du_mois_du_jour_dans_semestre_code_R_et_fonction
=INDEX(TRIG_du_mois_du_jour_dans_semestre;PETITE.VALEUR(lignes_code_R_et_fonction;2))
Commentaire : 2ème valeur dans la colonne "TRIG" du mois du jour pour laquelle "R" est rencontré dans data_jour_dans_semestre et fonction_saisie dans la colonne fonction_du_mois_du_jour_dans_semestre. Permet de gérer les multiples "R"

TRIG_3_du_mois_du_jour_dans_semestre_code_R_et_fonction
=INDEX(TRIG_du_mois_du_jour_dans_semestre;PETITE.VALEUR(lignes_code_R_et_fonction;3))
Commentaire : 3ème valeur dans la colonne "TRIG" du mois du jour pour laquelle "R" est rencontré dans data_jour_dans_semestre et fonction_saisie dans la colonne fonction_du_mois_du_jour_dans_semestre. Permet de gérer les multiples "R"

TRIG_du_mois_du_jour_dans_semestre
=DECALER(TRIG_1er_mois_Semestre; 0; indice_colonne_TRIG_du_jour_dans_semestre)
Commentaire : colonne "TRIG" du mois du jour saisi en 'Dde de remplacement'!$A16

TRIG_du_mois_du_jour_dans_semestre_code_J_et_fonction
=INDEX(TRIG_du_mois_du_jour_dans_semestre;no_lig_code_J_et_fonction)
Commentaire : 1ère valeur dans la colonne "TRIG" du mois du jour pour laquelle "J" est rencontré dans data_jour_dans_semestre et fonction_saisie dans la colonne fonction_du_mois_du_jour_dans_semestre

TRIG_du_mois_du_jour_dans_semestre_code_R_et_fonction
=INDEX(TRIG_du_mois_du_jour_dans_semestre;no_lig_code_R_et_fonction)
Commentaire : 1ère valeur dans la colonne "TRIG" du mois du jour pour laquelle "R" est rencontré dans data_jour_dans_semestre et fonction_saisie dans la colonne fonction_du_mois_du_jour_dans_semestre

Les commentaires devraient aider pour l'adaptation des formules aux nouveaux besoins.

Cordialement