Formule générique
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Sommaire
Pour localiser la position d'une valeur dans un tableau 2D, vous pouvez utiliser la fonction SUMPRODUCT. Dans l'exemple illustré, les formules utilisées pour localiser les numéros de ligne et de colonne de la valeur maximale dans le tableau sont:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
où "données" est la plage nommée C5: G14.
Remarque: pour cet exemple, nous trouvons arbitrairement l'emplacement de la valeur maximale dans les données, mais vous pouvez remplacer data = MAX (data) par tout autre test logique qui isolera une valeur donnée. Notez également que ces formules échoueront s'il y a des valeurs en double dans le tableau.
Explication
Pour obtenir le numéro de ligne, les données sont comparées à la valeur maximale, ce qui génère un tableau de résultats TRUE FALSE. Celles-ci sont multipliées par le résultat de ROW (data) qui génère un tableau de numéros de ligne associé à la plage nommée «data»:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
L'opération de multiplication oblige Excel à forcer les valeurs TRUE FALSE du premier tableau à 1 et 0, afin que nous puissions visualiser une étape intermédiaire comme celle-ci:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
SUMPRODUCT renvoie ensuite un résultat de 9, qui correspond à la 9e ligne de la feuille de calcul. Pour obtenir un index relatif à la plage nommée "data", nous utilisons:
-ROW(data)+1
Le résultat final est le tableau (5; 4; 3; 2; 1; 0; -1), à partir duquel seule la première valeur (5) est affichée.
La formule pour déterminer la position de la colonne fonctionne de la même manière.
Remarque: j'ai rencontré cette approche dans un commentaire de Mike Erickson sur MrExcel.com. Il y a également d'autres bonnes idées dans ce fil, y compris une option de formule matricielle.
