Vous connaissez peut-être déjà la fonction Index de Google Sheets qui permet de décaler des lignes. Dans ce tutoriel, nous allons apprendre comment indexer une plage filtrée dans Google Sheets. Cela signifie utiliser Index pour décaler les lignes dans une plage filtrée jusqu’aux cellules appropriées.
Ce qui est intéressant, c’est que le décalage des lignes d’index de la formule de filtrage sera basé sur le nombre de critères.
Je sais que c’est un sujet difficile à expliquer. Pas de soucis ! Je vais l’expliquer avec un exemple. Avant cela, voici les fonctions impliquées dans la formule d’indexation de la plage filtrée dans Google Sheets.
Les fonctions que nous allons utiliser dans la formule sont Index, Filter et Countif. En plus de cela, pour rendre la formule Filter fructueuse, j’inclus également Regexmatch.
Commençons par comprendre le combo ci-dessus et comment indexer une plage filtrée dans Google Sheets (en fonction du nombre d’occurrences). J’espère que certains d’entre vous trouveront ce combo utile dans votre utilisation réelle.
Indexer une plage filtrée dans Google Sheets – Explication du sujet
Le sujet concerne le décalage de la sortie filtrée à l’endroit correct dans une plage de critères.
Je vais juste utiliser une simple liste pour expliquer le sujet. Une fois que vous avez compris le but, vous pouvez utiliser un tableau (une plage à plusieurs colonnes) avec la formule. Je vais essayer de l’inclure dans cet article.
Voici l’exemple.
La liste à filtrer se trouve dans A2:A6 et contient le nom de certains fruits, c’est-à-dire « Orange » et « Apple » avec les grades américains des fruits. En outre, la plage C2:C7, qui est la plage de critères, contient des noms de fruits sans grades.
Nous avons donc deux plages (tableaux) contenant des noms de fruits – une avec des grades de fruits (plage de filtrage) et l’autre sans grades de fruits (critères de filtrage).
Prenons le fruit « Orange » qui se trouve dans les cellules C2, C3 et C7 et laissons le fruit « Apple » de côté pour le moment. Sur l’image, veuillez vous concentrer simplement sur le résultat de la formule dans les cellules correspondantes D2, D3 et D7.
J’espère que vous comprenez ce qui se passe en arrière-plan.
Ma formule d’indexation de plage filtrée dans ces cellules (D2, D3 et D7) dans Google Sheets prend les critères de la même ligne (C2, C3 et C7) pour filtrer A2:A6 et décaler le résultat en fonction du nombre de critères.
La formule décale les fruits filtrés avec des grades de la manière suivante : décalage d’une ligne dans D2, de deux lignes dans D3 et de trois lignes dans D7. Ainsi, même si les valeurs de C2, C3 et C7 sont identiques, la formule renvoie des résultats différents.
Il en va de même pour l’autre critère « Apple ». Passons à la formule pour indexer la plage filtrée comme décrit ci-dessus (en fonction du nombre d’occurrences) dans Google Sheets.
Combo Index, Filter et Countif dans Google Sheets
J’espère que vous avez pris votre temps pour comprendre le sujet. Si vous êtes prêt à continuer, suivez mes instructions ci-dessous étape par étape pour écrire la combinaison Index, Filter et Countif.
Données d’exemple et critères
Tout d’abord, ouvrez un fichier Google Sheets et saisissez les valeurs dans les colonnes A et C comme sur la capture d’écran ci-dessus. Vous avez maintenant terminé la première étape.
Maintenant, la prochaine étape consiste à écrire la formule d’indexation de plage filtrée. Nous pouvons commencer par Filter afin de pouvoir utiliser Index pour décaler les lignes.
Formule Filter (incluant Regexmatch)
Le premier critère se trouve dans la cellule C2. Saisissez la formule suivante dans la cellule D2.
=filter($A$2:$A$6, regexmatch($A$2:$A$6,C2))La formule filtre $A$2:$A$6 pour les lignes de cette plage qui correspondent partiellement au critère de la cellule C2. Regexmatch dans Filter est utilisé pour une correspondance partielle.
Dans la formule, j’ai gardé la plage de filtrage en tant que référence absolue et la référence de la cellule du critère en tant que référence relative. Cela est possible grâce à une utilisation appropriée des signes $.
En utilisant cette approche, nous pouvons garder la plage de filtrage identique et simplement changer le critère en C3, C4… en faisant glisser la formule vers le bas.
Formule d’indexation de plage filtrée basée sur le nombre d’occurrences
Dans cette troisième étape, nous pouvons indexer la plage filtrée ci-dessus dans Google Sheets.
La fonction Index nous permet de décaler des lignes et des colonnes. Ici, nous utilisons uniquement le décalage des lignes et laissons le décalage des colonnes dans Index.
Regardons d’abord la syntaxe de la fonction Index pour comprendre l’ordre de positionnement des deux décalages mentionnés.
INDEX(référence, [ligne], [colonne])
Lorsque nous laissons le décalage des colonnes, la formule renverra toutes les colonnes dans la « référence ». La « référence » ici est le résultat de la formule de filtrage ci-dessus.
Nous sommes seulement intéressés par le décalage des « lignes », n’est-ce pas ?
Avant de continuer, vous devriez essayer la formule Countif suivante dans n’importe quelle cellule, par exemple dans la cellule B2, et la faire glisser vers le bas car nous l’utilisons comme décalage des lignes dans Index.
=countif($C$2:C2,C2)La formule renvoie le compte progressif des fruits.
Cela signifie que pour l’élément « Orange », nous utiliserons 1 comme décalage des lignes dans Index dans la cellule D2, 2 dans la cellule D3 et 3 dans la cellule D7.
Les formules de Countif ci-dessus servent uniquement à des fins d’explication. Vous pouvez donc supprimer les formules de Countif ci-dessus.
Maintenant, je vais combiner Index, Filter et Countif comme suit :
=index(filter($A$2:$A$6,regexmatch($A$2:$A$6,C2)),countif($C$2:C2,C2))La formule a été copiée-collée dans D3:D7.
Dans la cellule D6, la formule renvoie une erreur #REF! car le fruit « Apple » est répété pour la troisième fois dans la colonne C. Mais dans le filtre, il n’y aura que deux lignes (« Apple U.S. No. 1 » et « Apple U.S. Fancy »). Par conséquent, Index ne peut pas décaler de 3 lignes.
Pour éviter les erreurs et exclure les cellules vides, le cas échéant, dans C2:C7, nous pouvons modifier davantage la formule comme suit :
=if(len(C2),iferror(index(filter($A$2:$A$6,regexmatch($A$2:$A$6,C2)),countif($C$2:C2,C2))),)De cette manière, nous pouvons indexer une plage filtrée dans Google Sheets.
Astuces (formule dans une liste à deux colonnes)
J’ai une liste à deux colonnes qui contient une colonne B supplémentaire avec les quantités. Quels changements dois-je apporter à la formule ?
Modifiez la plage de filtrage $A$2:$A$6 en $A$2:$B$6 comme indiqué ci-dessus ou, si vous ne voulez que la quantité, changez la plage de filtrage en $B$2:$B$6. Il n’y a pas de changements dans la partie Index ou Countif.
C’est tout. Profitez-en !
