Vous utilisez probablement la fonction Index de Google Sheets pour décaler des lignes. Dans ce tutoriel, nous allons apprendre comment indexer une plage filtrée dans Google Sheets. Cela signifie que nous allons utiliser Index pour décaler les lignes dans une plage filtrée vers les cellules appropriées.
Ce qui est intéressant, c’est que le décalage des lignes de la formule de filtre sera basé sur le nombre de critères.
Je sais que cela peut être difficile à comprendre. Pas de soucis ! Je vais l’expliquer avec un exemple. Mais avant cela, voici les fonctions utilisées dans la formule d’indexation d’une plage filtrée dans Google Sheets.
Les fonctions que nous allons utiliser dans cette formule sont Index, Filtrer et Compter.si. En plus de cela, pour rendre la formule de filtre fructueuse, j’inclus également Regexmatch.
Commençons par comprendre cette combinaison et comment indexer une plage filtrée dans Google Sheets en fonction du nombre de critères. J’espère que certains d’entre vous trouveront cette combinaison utile dans votre utilisation quotidienne.
Indexer une plage filtrée dans Google Sheets – Explication du sujet
Le sujet concerne le décalage de la sortie filtrée vers le bon emplacement dans une plage de critères.
Ici, j’utilise simplement une liste pour expliquer le sujet. Une fois que vous aurez compris le but, vous pourrez utiliser un tableau (une plage à plusieurs colonnes) avec la formule. Je vais également essayer de l’inclure dans cet article.
Voici l’exemple.
La liste à filtrer se trouve dans la plage A2:A6 et contient le nom de certains fruits, comme « Orange » et « Apple », avec les grades des fruits aux États-Unis. De plus, 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 : l’un avec les grades des fruits (plage de filtrage) et l’autre sans les grades des fruits (critères de filtrage).
Prenons par exemple 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 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) de 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 les grades de la manière suivante : décalage d’une ligne en D2, de deux lignes en D3 et de trois lignes en D7. Ainsi, même si les valeurs de C2, C3 et C7 sont les mêmes, la formule renvoie des résultats différents.
C’est également le cas avec l’autre critère « Apple ». Passons à la formule pour indexer la plage filtrée comme expliqué ci-dessus (en fonction du nombre de critères) dans Google Sheets.
Combinaison de Index, Filter et Countif dans Google Sheets
J’espère que vous avez pris le temps de comprendre le sujet. Si vous êtes prêt à continuer, suivez mes instructions étape par étape ci-dessous pour écrire la combinaison de 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 indiqué dans la capture d’écran ci-dessus. Vous avez ainsi terminé la première étape.
Maintenant, la prochaine étape consiste à écrire la formule d’indexation de plage filtrée. Nous pouvons commencer par le filtre afin d’utiliser Index pour décaler les lignes.
Formule de filtre (avec Regexmatch)
Le premier critère se trouve dans la cellule C2. Saisissez la formule suivante dans la cellule D2.
=filtrer($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. Le Regexmatch dans Filter permet 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 de critère en tant que référence relative. C’est possible grâce à l’utilisation appropriée du symbole $.
Cela nous permet de garder la plage de filtrage identique et de simplement changer le critère en C3, C4… lorsque nous faisons glisser la formule vers le bas.
Formule d’indexation de plage filtrée basée sur le nombre de critères
Dans cette troisième étape, nous pouvons indexer la plage filtrée précédente 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 de la « référence ». La « référence » ici est le résultat de la formule de filtrage précédente.
Nous nous intéressons uniquement au décalage des lignes, n’est-ce pas ?
Compter.si comme décalage des lignes dans Index
Avant de continuer, vous devriez essayer la formule de Compter.si 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.
=compter.si($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 de ligne dans Index en D2, 2 en D3 et 3 en D7.
Les Compter.si ci-dessus servent uniquement à expliquer. Vous pouvez donc supprimer les formules de Compter.si ci-dessus.
Formule pour indexer une plage filtrée basée sur le nombre de critères dans Google Sheets
Maintenant, je vais combiner Index, Filter et Countif selon la formule générique ci-dessous.
=index(formule_filtre, formule_compter.si)La formule dans la cellule D2 sera donc la suivante.
=index(filtre($A$2:$A$6,regexmatch($A$2:$A$6,C2)), compter.si($C$2:C2,C2))La formule a été copiée-collée dans D3:D7.
En 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 »). Donc, 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.
=si(len(C2), sierreur(index(filtrer($A$2:$A$6,regexmatch($A$2:$A$6,C2)), compter.si($C$2:C2,C2))), )De cette manière, nous pouvons indexer une plage filtrée dans Google Sheets.
Conseils (formule dans une liste à deux colonnes)
J’ai une liste à deux colonnes qui contient une colonne supplémentaire B avec une quantité. Quels changements dois-je apporter à la formule ?
Changez 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. Amusez-vous bien !
Si vous souhaitez en savoir plus sur Google Sheets et découvrir d’autres tutoriels passionnants, rendez-vous sur Crawlan.com.
