Vous vous demandez comment utiliser les fonctions Index et Match (ou Xmatch) pour retourner un résultat de tableau 2D dans Google Sheets ? Vous êtes au bon endroit !
Index dans Google Sheets : Le Retour du Contenu d’une Cellule
Normalement, la fonction Index retourne le contenu d’une cellule en fonction du décalage de la colonne et de la ligne spécifiée. Elle peut également retourner une colonne ou une ligne unique.
Par exemple, la formule =index(A2:B,5,2) retournera le contenu de la deuxième colonne de la cinquième ligne dans la plage A2:B.
Si vous remplacez le 2 par 0, c’est-à-dire =index(A2:B,5,0), vous obtiendrez la cinquième ligne. Et si vous remplacez le 5 par 0, c’est-à-dire =index(A2:B,0,2), vous obtiendrez la deuxième colonne.
Index pour un Résultat de Tableau 2D
Maintenant que nous savons comment utiliser la fonction Index pour retourner une seule colonne ou ligne, comment pouvons-nous l’utiliser pour obtenir un résultat de tableau 2D dans Google Sheets ?
En général, nous définissons une plage de cellules en plaçant deux références de cellules séparées par deux points (:).
Par exemple, B2:D10 est une plage de cellules où B2 et D10 sont des références de cellules.
De même, nous pouvons utiliser deux résultats de la fonction Index séparés par deux points (:), ce qui nous donnera une sortie sous forme de matrice dans Google Sheets.
Cette fonctionnalité peut être utilisée dans différents scénarios réels dans Google Sheets.
Exemples d’Index et Match pour un Tableau 2D dans Google Sheets
Voyons maintenant deux à trois exemples de retour d’un tableau 2D en utilisant Index et Match dans Google Sheets.
1. Copier un Tableau jusqu’à l’En-tête de Ligne Correspondant (Étiquette de Champ)
Il existe plusieurs façons d’accomplir cette tâche dans Google Sheets. La plus simple consiste à utiliser la fonction Choosecols avec Match ou Xmatch. Mais commençons par voir la formule Index et Match qui retourne un résultat de tableau 2D :
=index(B2:B8):index(B2:F8,0,match(« Mar »,B2:F2,0))
Cette formule fonctionne comme suit :
- La formule à gauche des deux points retourne la première colonne de la plage.
- La formule à droite des deux points retourne la quatrième colonne (étiquette de champ correspondant à la fonction Match).
Cela équivaut à =index(B2:B8,0,0):index(B2:F8,0,4).
En plaçant les deux formules Index de part et d’autre des deux points, nous obtenons un résultat sous forme de matrice.
Nous pouvons également utiliser une formule alternative avec Choosecols + Match pour copier un tableau jusqu’à la colonne qui correspond à l’en-tête de ligne dans Google Sheets :
=choosecols(B2:F8,sequence(match(« Mar »,B2:F2,0)))
2. Faire Correspondre une Valeur et Retourner des Lignes Supplémentaires
Dans le même tableau, comment faire correspondre « b » dans la première colonne et retourner cette ligne ainsi que deux lignes supplémentaires ?
Solution :
=index(B2:B8,match(« b »,B2:B8,0)):index(B2:F8,match(« b »,B2:B8,0)+2,5)
La partie gauche des deux points retourne « b » en raison du décalage de ligne #3 (retourné par Match). La plage à décaler est B2:B8.
La partie droite des deux points utilise la même formule, mais avec un décalage de ligne de 5 (3+2) et un décalage de colonne également de 5.
Ainsi, la formule retourne la plage B4:F6.
C’est un autre exemple d’utilisation d’Index et Match pour obtenir un résultat de tableau 2D dans Google Sheets.
Utilisation de Xmatch pour un Tableau 2D dans Google Sheets
Tout comme la fonction Match, nous pouvons utiliser la fonction Xmatch avec Index. Je l’utilise principalement dans deux scénarios :
- Lorsque je veux faire correspondre une clé dans une colonne de la dernière occurrence à la première occurrence.
- Pour les recherches de texte avec des caractères génériques (correspondance partielle).
Nous allons utiliser ces fonctionnalités de Xmatch et apprendre comment obtenir un résultat de matrice en utilisant la combinaison de Index et Xmatch.
1. Retourner les Lignes entre la Première et la Dernière Occurrence d’une Valeur
J’ai des données d’exemple non structurées qui rendent difficile l’utilisation de fonctions comme FILTER ou QUERY.
Je suis donc dans l’incapacité d’extraire les lignes contenant un produit spécifique.
Dans l’exemple suivant, je souhaite extraire les lignes contenant « Orange » et « Orange Total ».
Le premier critère sera une correspondance exacte, tandis que le dernier sera une correspondance partielle (avec des caractères génériques). En effet, je ne sais pas si le deuxième mot-clé est « Orange Total » ou « Total of Orange ».
Nous pouvons utiliser Index avec Xmatch pour cela et le résultat sera un tableau 2D.
=index(B:B,xmatch(« Orange »,B:B,0)):index(B:D,xmatch(« Orange* »,B:B,2,-1),3)
La première fonction Xmatch recherche de haut en bas dans la colonne B le mot-clé « Orange ».
La deuxième fonction Xmatch recherche de bas en haut dans la même colonne la correspondance partielle du mot-clé « Orange ».
En utilisant deux formules Index de part et d’autre des deux points, nous pouvons retourner un résultat de matrice.
Index et Match (ou Xmatch) pour des résultats de tableaux 2D sont particulièrement utiles lorsque vous avez des données non structurées.
J’espère que cet article vous a aidé à comprendre comment utiliser Index et Match (ou Xmatch) pour obtenir des résultats de tableaux 2D dans Google Sheets. Si vous souhaitez en savoir plus sur les fonctionnalités avancées de Google Sheets, n’hésitez pas à consulter Crawlan.com.
À bientôt pour de nouvelles astuces Google Sheets !
