Formule générique
=MAX(INDEX(data,0,MATCH(column,header,0)))
Sommaire
Pour récupérer la valeur maximale dans un ensemble de données, où la colonne est variable, vous pouvez utiliser INDEX et MATCH avec la fonction MAX. Dans l'exemple illustré, la formule en J5 est:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
où les données (B5: F15) et l'en- tête (B4: F4) sont des plages nommées.
Explication
Remarque: Si vous êtes nouveau dans INDEX et MATCH, voir: Comment utiliser INDEX et MATCH
Dans une configuration standard, la fonction INDEX récupère une valeur à une ligne et une colonne données. Par exemple, pour obtenir la valeur à la ligne 2 et à la colonne 3 dans une plage donnée:
=INDEX(range,2,3) // get value at row 2, column 3
Cependant, INDEX a une astuce spéciale - la possibilité de récupérer des colonnes et des lignes entières. La syntaxe implique de fournir zéro pour l'argument «autre». Si vous voulez une colonne entière, vous indiquez la ligne comme zéro. Si vous voulez une ligne entière, vous indiquez la colonne comme zéro:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Dans l'exemple illustré, nous voulons trouver la valeur maximale dans une colonne donnée. Le problème est que la colonne doit être variable pour pouvoir être facilement modifiée. En F5, la formule est:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
En travaillant de l'intérieur vers l'extérieur, nous utilisons d'abord la fonction MATCH pour obtenir l '"index" de la colonne demandée dans la cellule J4:
MATCH(J4,header,0) // get column index
Avec "Green" dans J4, la fonction MATCH renvoie 3, puisque Green est la troisième valeur de l'en- tête de plage nommé . Une fois que MATCH a renvoyé un résultat, la formule peut être simplifiée comme suit:
=MAX(INDEX(data,0,3))
Avec zéro fourni comme numéro de ligne, INDEX renvoie toutes les valeurs de la colonne 3 des données de plage nommées . Le résultat est renvoyé à la fonction MAX dans un tableau comme celui-ci:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Et MAX renvoie le résultat final, 93.
Valeur minimum
Pour obtenir la valeur minimale avec une colonne variable, vous pouvez simplement remplacer la fonction MAX par la fonction MIN. La formule en J6 est:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Avec FILTER
La nouvelle fonction FILTER peut également être utilisée pour résoudre ce problème, puisque FILTER peut filtrer les données par ligne ou par colonne. L'astuce consiste à construire un filtre logique qui exclura d'autres colonnes. COUNTIF fonctionne bien dans ce cas, mais il doit être configuré "à l'envers", avec J4 comme plage et en- tête pour les critères:
=MAX(FILTER(data,COUNTIF(J4,header)))
Une fois COUNTIF exécuté, nous avons:
=MAX(FILTER(data,(0,0,1,0,0)))
Et FILTER délivre la 3ème colonne à MAX, comme la fonction INDEX ci-dessus.
Comme alternative à COUNTIF, vous pouvez utiliser ISNUMBER + MATCH à la place:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
La fonction MATCH est à nouveau configurée "à l'envers", de sorte que nous obtenons un tableau avec 5 valeurs qui servira de filtre logique. Après ISNUMBER et MATCH, nous avons:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Et FILTER délivre à nouveau la 3ème colonne à MAX.
