Pour retourner l’en-tête de colonne d’une valeur maximale dans Google Sheets, nous pouvons utiliser une formule Hlookup combinée avec Max. Mais que se passe-t-il lorsque vous souhaitez répéter la même opération pour chaque ligne de votre tableau ?
Bien sûr, Hlookup peut renvoyer des résultats dans plusieurs lignes, mais en même temps, Max ne le peut pas. Pour compliquer davantage le problème, nous avons besoin de la sortie de la ligne des en-têtes. Cela nous empêche d’utiliser Hlookup.
Doit-on dépendre d’une formule de glisser-déposer pour retourner les en-têtes de colonne des valeurs maximales dans chaque ligne de Google Sheets ?
Non ! J’ai une formule matricielle pour retourner l’en-tête de colonne de la valeur maximale dans Google Sheets. Dans cet article, je vais partager cela avec vous. Permettez-moi de commencer par une formule de glisser-déposer.
Utilisation d’une formule de glisser-déposer qui implique Max et Hlookup
Supposons qu’une entreprise de commerce possède trois entrepôts et gère les stocks par article dans une simple feuille de calcul Google. La colonne A contient les noms des articles, et les colonnes B à D contiennent la quantité stockée dans chaque entrepôt.
En utilisant cet ensemble de données, on peut facilement trouver la quantité maximale de chaque article stocké dans chaque entrepôt. Nous devons simplement trouver l’en-tête de colonne de la valeur maximale.
Dans l’exemple ci-dessus, il n’y a que cinq articles dans les lignes 2 à 6.
Ici, nous pouvons utiliser une formule non matricielle pour retourner le nom de l’entrepôt en fonction de la quantité maximale stockée. Dans la cellule F2, insérez la formule MAX suivante pour retourner la valeur maximale (qty) de cette ligne.
=max(B2:D2)
Dans la cellule G2, insérez la formule HLOOKUP suivante pour retourner l’en-tête (numéro d’entrepôt) correspondant à la valeur maximale (qty).
=hlookup(F2,{A2:D2;$A$1:$D$1},2,0)
Copiez-collez ou faites glisser les deux formules vers le bas pour obtenir l’en-tête de colonne de la valeur maximale dans chaque ligne de Google Sheets.
Remarque : Si vous ne souhaitez pas voir les chiffres max dans la colonne F, incluez la formule F2 dans Hlookup comme suit.
=hlookup(max(B2:D2),{A2:D2;$A$1:$D$1},2,0)
Formule matricielle pour retourner l’en-tête de colonne de la valeur maximale dans Google Sheets
Je sais que, très probablement, vous êtes ici pour la formule matricielle permettant d’obtenir l’en-tête de colonne des valeurs maximales dans chaque ligne d’un tableau. Vous avez peut-être rencontré deux obstacles : étendre le Max ci-dessus, puis le Hlookup. Permettez-moi de l’appeler Partie I et Partie II à des fins d’explication.
mise à jour : J’ai ajouté une nouvelle solution lambda à la fin de ce tutoriel sous le titre « Lambda pour retourner l’en-tête de colonne maximale dans chaque ligne ».
Partie I – Solution utilisant Dmax
Pour étendre le résultat max, nous pouvons utiliser DMAX comme expliqué ici – Renvoyer les première et deuxième plus grandes valeurs dans chaque ligne de Google Sheets. Après avoir vidé F1:F, entrez la formule DMAX suivante dans la cellule F1.
={"Max"; ArrayFormula( if(len(A2:A), DMAX( transpose({{"Row";sequence(rows(A2:A1000),1)},B1:D1000}), sequence(1000,1,2), transpose({"Row",B1:D1}) ), ) ) }
J’ai écrit la formule pour couvrir jusqu’à la ligne 1000.
Lorsque vous avez plus d’enregistrements, n’oubliez pas de changer 1000 dans la plage de cellules A2:A1000 et D1000 avec 2000 (selon les besoins).
Gardez à l’esprit qu’un grand ensemble de données peut entraîner des problèmes de performance dans la formule matricielle ci-dessus.
Partie II – Formule matricielle pour obtenir l’en-tête de colonne de la valeur maximale dans Google Sheets
Similairement à la partie I, videz également la colonne G1:G. Ensuite, entrez la formule suivante dans la cellule G1.
={"Header"; ArrayFormula( ifna( if(len(F2:F1000), vlookup( row(A2:A1000)&"~"&F2:F1000, Query(split(flatten(row(A2:A1000)&"~"&B2:D1000&"🐟"&B1:D1),"🐟"), "Select * where Col1 is not null and Col2 is not null") ,2,0 ), ) ) ) }
Cela renverrait l’en-tête de colonne des valeurs maximales dans Google Sheets (lire l’en-tête de colonne comme les numéros d’entrepôt) jusqu’à la ligne 1000. Cela signifie que le dernier paragraphe sous le titre ci-dessus s’applique également ici.
Je ne veux pas vous laisser dans l’ignorance sans partager comment la formule fonctionne (nous pouvons dire la logique de la formule) ou comment je les ai combinées et leur objectif.
Explication de la formule Partie II
La meilleure façon d’apprendre la formule ci-dessus est de limiter l’expansion de la formule à la ligne n° 6, car c’est la dernière ligne qui contient une valeur. Cela nous permettra de supprimer certaines chaînes indésirables de la formule et ainsi de la raccourcir.
Voici la formule qui doit retourner l’en-tête de colonne des valeurs maximales des lignes deux à six. Étant donné qu’elle ne contient pas d’étiquette de champ, lors des tests, vous devez l’insérer dans la cellule G2, pas dans la cellule G1 comme ci-dessus.
=ArrayFormula( vlookup( row(A2:A6)&"~"&F2:F6, Query(split(flatten(row(A2:A6)&"~"&B2:D6&"🐟"&B1:D1),"🐟"), "Select * where Col1 is not null and Col2 is not null"),2,0 ) )
Ce qui précède est une formule VLOOKUP. La syntaxe et les arguments utilisés sont les suivants.
Syntaxe
VLOOKUP(search_key, range, index, [is_sorted])
Arguments
Search_key: row(A2:A6)& »~ »&F2:F6
Cette formule ajoute simplement les numéros de LIGNE avec les valeurs maximales de la colonne F. Le signe tilde est inséré comme séparateur entre les numéros de ligne et les valeurs maximales.
range– Query(split(flatten(row(A2:A6)& »~ »&B2:D6& »🐟 »&B1:D1), »🐟 »), »Select * where Col1 is not null and Col2 is not null »)
La sortie ci-dessus est l’âme de la formule qui retourne les en-têtes de colonne des valeurs maximales dans chaque ligne de Google Sheets. Regardez le résultat.
Vous pouvez voir que la formule ajoute les numéros de ligne à chaque valeur de B2:D6 (j’ai utilisé le même séparateur tilde). De plus, elle a une colonne supplémentaire contenant les en-têtes de colonne correspondants.
En fait, j’ai utilisé la fonction FLATTEN pour dénormaliser les données dans la plage B1:D6. Vous pouvez trouver plus d’infos ici – Une formule simple pour dénormaliser un ensemble de données dans Google Sheets.
La QUERY n’est pas obligatoire dans le cas ci-dessus. Vous pouvez utiliser directement Flatten comme suit.
=ArrayFormula(split(flatten(row(A2:A6)&"~"&B2:D6&"🐟"&B1:D1),"🐟"))
La requête est incluse pour supprimer les cellules vides lorsque nous utilisons la plage comme A2:A1000 et B2:D1000.
index– 2is_sorted– 0 (la plage n’est pas triée)
La fonction Vlookup recherche search_key dans la plage ci-dessus et retourne la valeur correspondante de la deuxième colonne (index n° 2).
Lambda pour retourner l’en-tête de colonne maximale dans chaque ligne
La formule ci-dessus présente deux inconvénients.
Elle est un peu complexe, mais cela ne pose pas de problème car elle fonctionne dans chaque ligne.
Le deuxième inconvénient est le plus évident.
Par exemple, si j’ai la même quantité maximale conservée dans deux entrepôts ou plus pour un article. Je veux tous les noms d’entrepôt. Comment les obtenir ?
La formule ci-dessus ne prendra pas en charge ce type de sortie. Voici la meilleure formule matricielle qui fonctionne indépendamment des valeurs maximales uniques ou multiples.
=byrow(B2:D,lambda(row, if(counta(row)=0,,join(", ",filter($B$1:$D$1,row=max(row))))))
Entrez cette formule dans la cellule I2 de la colonne vide I.
Cette formule auxiliaire Lambda BYROW retournera les en-têtes de colonne maximale dans chaque ligne.
Comment fonctionne cette formule ?
J’ai utilisé le nom « row » dans la formule Lambda ci-dessus pour définir les lignes dans B2:D (plage). Dans chaque ligne, elle prend la ligne correspondante.
- La formule ROW retourne la valeur maximale de la première ligne.
- La formule FILTER filtre l’en-tête en utilisant les valeurs renvoyées au point 1 ci-dessus comme critère.
- JOIN combine la sortie du point 2.
- La formule lambda BYROW fonctionne dans chaque ligne. Ainsi, la ligne sera B2:D2 dans la première ligne, B3:D3 dans la deuxième ligne, et ainsi de suite.
De cette manière, nous pouvons utiliser BYROW pour retourner l’en-tête de colonne des valeurs maximales dans chaque ligne de Google Sheets.
Ressources :
