Simplifier une formule
babie971
Messages postés
11
Date d'inscription
Statut
Membre
Dernière intervention
-
babie971 -
babie971 -
Bonjour,
j'aurais besoin d'aide pour réduire une fonction et en y rajoutant des conditions
sur la feuille remplacement, en P2 et P3 rajouté en recherche R3 et R4 avant R
https://www.cjoint.com/c/EHdnvd71nZj
Merci de faire les tests sur le mois de juillet, je n'ai pas encore maj tout le fichier.
A votre disposition au besoin
A bientôt
j'aurais besoin d'aide pour réduire une fonction et en y rajoutant des conditions
sur la feuille remplacement, en P2 et P3 rajouté en recherche R3 et R4 avant R
https://www.cjoint.com/c/EHdnvd71nZj
Merci de faire les tests sur le mois de juillet, je n'ai pas encore maj tout le fichier.
A votre disposition au besoin
A bientôt
A voir également:
- Simplifier une formule
- Formule si et - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Formule mathématique - Télécharger - Études & Formations
- Formule somme excel colonne - Guide
- Excel mise en forme conditionnelle formule - Guide
3 réponses
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.
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 :
En E16 :
En G16 :
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
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
Bonjour,
J'ai complètement revu la façon de voir le problème.
je te laisse tester : https://www.cjoint.com/c/EHfoM34w58F
tu pourras choisir n'importe quelle priorité des codes, simplement en réorganisant l'ordre des colonnes cachées à droite de ton tableau Dde de Remplacement
cordialement
J'ai complètement revu la façon de voir le problème.
je te laisse tester : https://www.cjoint.com/c/EHfoM34w58F
tu pourras choisir n'importe quelle priorité des codes, simplement en réorganisant l'ordre des colonnes cachées à droite de ton tableau Dde de Remplacement
cordialement
Merci d'avoir tenté. il est vrai que la formule est complexe mais elle fonctionne du tonnerre.
Malheureusement mon besoin a changé et quand je tente d'augmenter mes conditions je suis bloquée.
Merci quand même. je continue!!