Re : Création liste modifiable en fonction de 2 paramètres
🙂
Me revoilà avec un métro de retard, mais comme j’étais lancée, voilà ci dessous les quelques commentaires autour des fonctions que j’ai utilisées dans le fichier joint. Mêmes fonctions et même idée que J Boisgontier, mais il va sans dire, en beaucoup moins bien évidemment!
Fonction Decaler:
Formule théorique: DECALER( Réf; Nombre de lignes; Nombre de colonnes; Hauteur; Largeur)
soit dans notre exemple: DECALER=DECALER($A$16;EQUIV($F$3;$A$16:$A$50;0)-1+(EQUIV($D$13*$D$14;$B$16:$B$50;0)-2);4;SOMMEPROD(($A$16:$A$50=$F$3)*(($D$13*$D$14)=$B$16:$B$50))😉
Réf: Cellule de départ à partir de laquelle le décalage doit être opéré. Ici, nous partons de la cellule $A$16 de l'onglet Feuil1
Nbre de lignes :
Nombre de lignes de décalage vers le haut (chiffre négatif) ou vers le bas (chiffre positif) .
Concrètement ici nous cherchons la première ligne qui correspond au libellé contenu dans la cellule E3. Pour « Biosol 190 P6+ Intégré » il s’agira de la 8ème ligne à partir de A16, et pour calculer ce 8 nous utilisons la fonction Equiv() décrite ci dessous.
Pour le second critère, une fois que nous sommes sur cette 8ème ligne, il faut calculer un décalage ou pas vers le bas pour trouver la première valeur égale à D13*D14.
C’est encore la fonction Equiv ci dessous qui va nous y aider, ainsi pour « Biosol 190 P6+ Intégré » de 15 panneaux, nous devons encore nous décaler de 4
Nombre de colonnes.
Même raisonnement que pour les lignes, ici nous nous décalons de 4 vers la droite pour aller chercher les valeurs en colonne E
Hauteur: Indique la hauteur de la plage, en nombre de lignes (elle doit être impérativement supérieure à 0)
Dans notre exemple la hauteur de la zone correspond au nombre de valeurs entre A16 et A20 qui sont égales à la cellule E3 (puisque le tableau est trié) ET dont le nombre de panneaux correspond à la multiplication D13*D14.
Ici il y a 7 types d’appareils correspondant à « Biosol 190 P6+ Intégré », dont 3 de 15 panneaux, cette valeur 3 est calculée à l’aide de la fonction sommeprod() décrite si dessous
Largeur: Indique la largeur de la plage, en nombre de colonnes (la largeur doit également être supérieure à 0).
Même chose que pour la hauteur, ici la largeur est de 1 pour indiquer qu’il n’y a qu’une seule colonne. Quand cette valeur n’est pas renseignée (ici rien après le point virgule) la valeur par défaut est 1.
Concrètement pour « Biosol 190 P6+ Intégré » et « 15 panneaux », la fonction DECALER($A$16;EQUIV($F$3;$A$16:$A$50;0)-1+(EQUIV($D$13*$D$14;$B$16:$B$50;0)-2);4;SOMMEPROD((A16:A50=F3)*((D13*D14)=B16:B50))😉
va donner ici DECALER($A$16;12;3;3😉 soit partir de A16, descendre de 12 lignes, se décaler de 3 colonnes, à ce stade nous sommes en cellule E28 ; puis prendre en compte une plage de hauteur 3 largeur 1, soit les valeurs contenues entre E28 et E30
Pour la fonction Equiv:
Cette fonction permet de trouver la position de l'élément recherché
Formule théorique : Equiv(valeur_cherchée;matrice de recherche;type)
soit dans notre exemple :=EQUIV($F$3;$A$16:$A$50;0)
Valeur cherchée: Nous cherchons la valeur de la cellule F3
Matrice de recherche: Nous cherchons cette valeur dans les cellules $A$16 à $A$50
Type: Dans notre exemple l'argument est 0 pour dire que nous cherchons la valeur exacte.
Les arguments peuvent être:
1 pour chercher la valeur la plus élevée inférieure ou égale à la valeur cherchée,
-1 pour chercher la plus petite valeur supérieure ou égale à la valeur cherchée,
si l'argument n'est pas renseigné, la valeur par défaut est 1
Dans ces derniers cas, il faut que la matrice soit triée sans quoi le résultat risque d'être faux.
Dans notre exemple, la valeur renvoyée par cette fonction est 9 (partant de A16) et il convient de retirer 1 pour tenir compte de la 1ère ligne qui est vide
La deuxième fonction Equiv() fait la même chose : (EQUIV($D$13*$D$14;$B$16:$B$50;0)-2 : elle cherche l’équivalent le D13*D14 (par exemple 3*5 soit 15), dans les cellules B16 à B50.
Attention : cette formule ici n’est adaptée qu’à notre exemple, et uniquement parce que le nombre de lignes est identique entre les deux produits Biosol 180 et Biosol 190, s’il n’y avait pas 7 lignes dont 4 pour 16 panneaux et 3 pour 15 panneaux dans les deux cas, il faudrait calculer le décalage autrement.
C’est une des raisons pour lesquelles il faut naturellement préférer la formule proposée par JBoisgontier, car elle permet de tenir compte d’un décalage variable alors que celle que j’ai déposée dans le fichier joint est figée.
Fonction Sommeprod:
Cette fonction va permettre de calculer un nombre de valeurs correspondant à 2 critères.
Formule théorique : sommeprod(matrice1;matrice2 ; …)
soit dans notre exemple :=SOMMEPROD(($A$16:$A$50=$F$3)*(($D$13*$D$14)=$B$16:$B$50))
Cette fonction va compter le nombre de cellules entre A16 et A50 qui ont la même valeur que la cellule F3, et pour lesquelles les cellules de B16 à B50 ont la même valeur que le résultat de la multiplication de D13 par D14
Remarque : la taille de la matrice doit toujours être la même, ici de la ligne 16 à la ligne 50.
Pour« Biosol 190 P6+ Intégré » ET 15 panneaux la fonction renvoie 3
Ouf !
Beaucoup de bavardage mais comme tu nous disais que tu n’étais pas à l’aise avec les formules compliquées ; entre nous celle-ci l’est un peu … et j’ai essayé de te décortiquer les formules utilisées dans ton fichier en espérant que cela pourra t’aider non seulement pour ce cas là mais également pour tes futurs fichiers 🙂
Bon courage pour ton travail et à une prochaine fois,
Sans oublier bien sûr de saluer avec grand plaisir J Boisgontier
mth
PS: je mets le fichier en pj, mais plus pour illustrer mes commentaires qu'autre chose
