Problème de formule
Bonjour à tous,
Je vous soumets mon problème, avec les fichiers joints.
Je cherche à adapter un fichier Excel produit pour la filière STI2D (sciences et technologies de l'industrie et du développement durable) en lycée technologique pour la voie professionnelle en mathématiques-sciences.
○ Le principe de mes modifications a été de changer le référentiel professionnel, il y a moins de compétences dans celui de mathématiques-sciences. Elles allaient jusquà la colonne BC de la feuille "Problématiques_compétences" dans le fichier STI2D, elles ne vont qu'à la colonne AC dans le fichier 20201223-progression-pedagogique-modele.xlsx.
○ Dans mon fichier (20201223-progression-pedagogique-modele.xlsx), il y a une feuille en moins ("Croisement-comp-conn") que j'ai retirée.
○ Dans mon fichier, j'ai également modifié la feuille "Générateur 1", car il y a moins de colonnes nécessaires pour les mathématiques-sciences (c'est peut-être ça qui coince, d'ailleurs, maintenant que je l'écris !)
○ Le fonctionnement attendu est le suivant dans le fichier pour les STI2D :
• le principe est de partir de la feuille "Problématiques_compétences" et de cocher par des "x" les compétences des élèves qui seront mises en jeu dans une séquence définie par l'enseignant ;
• ces "x" se reportent dans la feuille "Progression_STI2D" ("Progression_MS" dans mon fichier). Cette feuille permet à l'enseignant d'avoir une vision d'ensemble des compétences sollicitées et du nombre de ces sollicitations au cours de l'année scolaire ;
• la feuille "Générateur 1" est un générateur de séquences pédagogiques, justement. Il faut la dupliquer, la renommer (dans mon fichier, c'est "S11") et ensuite, dans la cellule A1-A2 (qui sont fusionnées), indiquer la référence de la séquence en question (ici, "S11") ;
• à ce moment, les compétences cochées par un "x" précédemment, dans la ligne de la séquence S11 de la feuille "Problématiques_compétences", apparaissent dans les premières colonnes du tableau de la feuille S11, ainsi que les connaissances associées. Pour tout le reste du tableau, c'est à l'enseignant de remplir pour préciser clairement ce qu'il va faire faire aux élèves.
○ Le problème est niveau de l'onglet Générateur 1, et donc des onglets qui en sont les clones :
• tout fonctionne bien tant que dans la feuille "Problématique_compétences", je ne coche pas de cases avec des "x" au-delà de la colonne "W" (compétences notées dans le ligne 2 : G1, G2, AP1, AP2, AP3, AP4 et AP5, ce qui amène à la colonne "AC"). En effet, si je coche des compétences avant celles-ci, tout apparait bien automatiquement dans la feuille "Progression_MS" et "S11". Mais si je garde ces compétences cochées et que j'en coche une après la colonne W, toutes disparaissent de "S11", mais pas de "Progression_MS" ! Et heureusement, celles cochées avant la colonne W réapparaissent bien si je supprime les "x" des compétences après "W" ;
• la formule en qui me pose problème, qui se trouve dans les cellules A4:A5 (cellules fusionnées) de la feuille "S11" :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$W$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0)):INDIRECT("Problématiques_compétences!BC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0))="x";COLONNE(Problématiques_compétences!$F$1:$W$1)-5;"");LIGNE()-4));"")• en lisant la formule contenue dans A4:A5 (cellules fusionnées) de la feuille "S11", je me rends bien compte que les plages de données des coches "x" de la feuille "Problématiques_compétences" sont $F$1:$W$1 et $F$2:$W$2, ce qui indiquerait qu'elle ne prend pas en compte les colonnes au-delà de de W. Cependant, si j'étends la plage de W à AC (jusqu'où vont les compétences désormais), plus rien ne s'affiche dans S11, même si j'enlève les coches pour les compétences au-delà de W !
Merci d'avance d'avoir pris le temps de lire toute cette prose et de vous pencher sur le problème !
Bonjour Fabien3D et
Une petite présentation ICI serait la bienvenue
Sinon, je pense qu'il faut que vous soyez plus concis dans votre demande
perso, vous m'avez perdu au 3ème paragraphe
@+
Bonjour,
Présentation rédigée !
J'ai été assez long, justement pour essayer d'expliquer précisément le problème, car le fichier comporte plusieurs feuilles interdépendantes, et les soucis ne se présentent qu' à un seul endroit !
Merci en tous cas pour votre retour !
Bonjour à tous,
Tu la sors d'où, ta formule ?
Regarde bien la syntaxe de indirect(); toutes les plages de si ...... doivent avoir la meme longueur et ton ligne()-5, a la 5 éme ligne vaut 0. Et petite.valeur(xxx;0)==>erreur
Bonjour,
Merci d'avoir passé du temps sur mon problème !
Cette formule, je n'y avais pas touché. En regardant le fichier d'origine (STI2D), elle n'a d'ailleurs pas bougé, ce qui est d'ailleurs peut-être le problème !
Après avoir téléchargé ta version, je vois bien la modification effectuée entre la version d'origine :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$W$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0)):INDIRECT("Problématiques_compétences!BC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0))="x";COLONNE(Problématiques_compétences!$F$1:$W$1)-5;"");LIGNE()-3));"")
et ta version modifiée :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$W$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0)&":BC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-3));"")
qui semble fonctionner, mais, je me rends compte que la plage des compétences de la feuille "Problématique_compétences" doit être comprise entre $F$2 et $AC$2. Si je modifie la formule de ton fichier en ce sens, cela donne la formule suivante :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$AC$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0)&":AC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$174;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-3));"")
doit renvoyer une erreur, car plus rien ne s'affiche (cf. fichier en pièce jointe).
Re
Bonjour,
DjiDji59430, un grand merci !
Il me semble que les modifications que tu as effectuées correspondent à ce que je proposais de faire dans mon dernier message, je constate que dès que je modifie quoi que ce soit dans ces formules, il se produit une erreur sur mon Excel !
En effet, en relisant ta formule :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$AC$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0)&":aC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-3));"")
j'ai voulu corriger le &":aC"& en &":AC"& . Cette correction fait que le contenu des cellules de la ligne A4 disparait, comme s'il y avait désormais une erreur !
De plus, pour suivre ce que tu avais modifié, j'ai supprimé les cellules fusionnées de la colonne A de la feuille "S11" et j'ai collé ta formule modifiée dans chacune des cellules de A5 à A8 (cf. fichier joint). Pour suivre la logique des formules de cette feuille, j'ai reproduit le décalage d'une ligne qui se trouve dans le fichier d'origine STI2D, ce qui donne, en fin de formule :
A4 (ta formule) :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$AC$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0)&":aC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-3));"")
A5 :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$AC$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0)&":aC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-4));"")
A6 :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$AC$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0)&":aC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-5));"")
A7 :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$AC$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0)&":aC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-6));"")
A8 :
=SIERREUR(INDEX(Problématiques_compétences!$F$2:$AC$2;PETITE.VALEUR(SI(INDIRECT("Problématiques_compétences!F"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0)&":aC"&EQUIV($A$1;Problématiques_compétences!$D$1:$D$173;0))="x";COLONNE(Problématiques_compétences!$F$1:$AC$1)-5);LIGNE()-7));"")
Mais je constate qu'il y a une erreur à chaque fois pour ces autres cellules, même si des coches se trouvent bien sur la ligne 29 de la feuille "Problématiques_compétences". En effet, rien ne s'affiche !
Saurais-tu ce qui se passe ?
C'est une formule matricielle, donc a valider par
Maj+Ctrl+Entrée (les 3 doigts en même temps) pour faire apparaître les accolades.
Elle doit s'afficher automatiquement entre accolades dans la barre de formule, et être re-validée de la même façon à chaque fois qu'on y touche.
Cette formule te donne une liste dont le premier élément est donné (en gros) par petite.valeur(xxx;1)le 1 est donné par ligne()-3 en ligne 4
et ligne()-3 en ligne 5 te donne 2, soit le deuxième élément de ta liste.
Je l'ai remplacé par lignes($1:1) qui vaut 1 et qui en s'incrémentant deviendra lignes($1:2) =2 pour te donner le deuxième élément, etc ...
Crdlmt
Un très très grand merci pour ton temps et ta patience ! Tout fonctionne correctement !
Et j'aurai appris comment valider des formules matricielles ! Mon utilisation d'Excel ne m'avait jamais amené à utiliser ce type de fonction !
Encore merci !