Ça y est, vous avez enfin maîtrisé 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 la fonction Index pour décaler des lignes dans une plage filtrée jusqu’aux cellules appropriées.
Ce qui est intéressant, c’est que le décalage des lignes de la formule de filtrage sera basé sur le nombre de critères.
Je sais que cela peut sembler compliqué à expliquer. Pas de panique ! Je vais vous le montrer à l’aide d’un exemple. Avant cela, voici les fonctions nécessaires pour indexer une plage filtrée dans Google Sheets.
Les fonctions que nous allons utiliser dans la formule sont Index, Filter et Countif. Pour rendre la formule de filtrage efficace, 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 leur utilisation de tous les jours.
L’index de la plage filtrée dans Google Sheets – Explication du sujet
Le sujet porte sur le décalage de la sortie filtrée à l’endroit correct dans une plage de critères.
Je vais utiliser une simple liste pour expliquer le sujet. Une fois que vous aurez compris l’objectif, vous pourrez utiliser un tableau (une plage de plusieurs colonnes) avec la formule. Je vais également essayer d’inclure cela dans cet article.
Voici l’exemple :
La liste à filtrer se trouve dans les cellules A2:A6 et contient le nom de certains fruits, tels que « Orange » et « Pomme », avec des notes de fruits aux États-Unis. De plus, la plage C2:C7, qui est la plage de critères, contient certains noms de fruits sans notes.
Nous avons donc deux plages (tableaux) contenant des noms de fruits – une avec des notes de fruits (plage de filtrage) et une autre sans notes de fruits (critères de filtrage).
Prenons le fruit « Orange », qui se trouve dans les cellules C2, C3 et C7, et laissons le fruit « Pomme » 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 de l’autre côté.
Ma formule d’index 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 les notes comme suit : 1 ligne dans D2, 2 lignes dans D3 et 3 lignes dans D7. Ainsi, même si les valeurs de C2, C3 et C7 sont les mêmes, la formule renvoie des résultats différents.
Il en va de même pour les autres critères « Pomme ». Passons maintenant à la formule pour indexer la plage filtrée comme indiqué ci-dessus (en fonction du nombre) 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 les instructions étape par étape ci-dessous pour écrire la combinaison Index, Filter et Countif.
Données d’exemple et critères
D’abord, ouvrez un fichier Google Sheets et entrez les valeurs dans les colonnes A et C comme indiqué dans la capture d’écran ci-dessus. Vous avez ainsi terminé l’étape 1.
Maintenant, passons à l’écriture de la formule d’index de plage filtrée. Nous pouvons commencer par Filter afin de pouvoir utiliser Index pour décaler les lignes.
Formule de filtrage (avec Regexmatch)
Le premier critère se trouve dans la cellule C2. Entrez la formule suivante dans la cellule D2 :
=filter( $A$2:$A$6, regexmatch($A$2:$A$6,C2) )La formule filtre les lignes de la plage $A$2:$A$6 qui correspondent partiellement au critère de la cellule C2. Regexmatch dans Filter est utilisé pour une correspondance partielle.
Dans la formule, j’ai conservé la plage de filtrage en tant que référence absolue et la référence de cellule de critère en tant que référence relative. C’est possible grâce à une utilisation adéquate des signes $.
Cela nous permet de garder la plage de filtrage identique et de simplement modifier le critère en C3, C4… en faisant glisser la formule vers le bas.
Formule d’index pour filtrer le résultat en fonction du nombre de critères
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 n’utilisons que le décalage des lignes et laissons le décalage des colonnes dans Index.
Examinons d’abord la syntaxe de la fonction Index pour comprendre la position (l’ordre) des deux décalages.
INDEX(reference, [row], [column])Lorsque nous laissons le décalage des colonnes, la formule retourne toutes les colonnes de la ‘référence’. La ‘référence’ ici est le résultat de la formule de filtrage ci-dessus.
Nous nous intéressons uniquement au décalage des ‘lignes’, n’est-ce pas ?
Countif comme décalage de lignes dans Index
Avant de poursuivre, vous devriez essayer la formule Countif suivante dans n’importe quelle cellule, par exemple dans la cellule B2, et faites-la glisser vers le bas car nous l’utilisons comme décalage de lignes dans Index.
=countif($C$2:C2,C2)La formule retourne le décompte progressif des fruits.
Cela signifie que, pour l’élément « Orange », nous utiliserons 1 comme décalage de lignes dans Index dans la cellule D2, 2 dans la cellule D3 et 3 dans la cellule D7.
Les Countifs ci-dessus sont uniquement à des fins d’explication. Vous pouvez donc supprimer les formules de Countif ci-dessus.
Formule pour indexer une plage filtrée en fonction du 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_countif )La formule dans la cellule D2 sera donc la suivante :
=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 les cellules D3:D7.
Dans la cellule D6, la formule renvoie une erreur #REF! car le fruit « Pomme » est répété une troisième fois dans la colonne C. Mais dans le filtre, il n’y aura que deux lignes (« Pomme U.S. No. 1 » et « Pomme U.S. Fancy »). Ainsi, Index ne peut pas décaler de 3 lignes.
Pour éviter les erreurs et exclure les cellules vides, le cas échéant, dans la plage 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 la plage filtrée dans Google Sheets.
Conseils (formule dans une liste à deux colonnes)
J’ai une liste à deux colonnes qui contient une colonne B supplémentaire avec des 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 aucun changement dans la partie Index ou Countif.
C’est tout. Amusez-vous bien !
Visitez Crawlan.com pour plus de conseils et de tutoriels sur Google Sheets.
