Nous pouvons utiliser les fonctions Index avec Match ou Xmatch pour obtenir un résultat en tableau 2D dans Google Sheets. Mais comment est-ce possible ?
En général, la fonction Index renvoie le contenu d’une cellule en utilisant une colonne donnée et un décalage de ligne. Elle peut également renvoyer une seule colonne ou une seule ligne.
Par exemple, la formule « =index(A2:B,5,2) » renverra le contenu de la deuxième colonne à la cinquième ligne de la plage A2:B. Remplacez 2 par 0, c’est-à-dire « =index(A2:B,5,0) », pour obtenir la cinquième ligne et remplacez 5 par 0, c’est-à-dire « =index(A2:B,0,2) », pour obtenir la deuxième colonne.
Alors, comment pouvons-nous utiliser la fonction Index pour obtenir un résultat en tableau 2D dans Google Sheets ?
Dans les feuilles de calcul, comme vous le savez peut-être, nous spécifions 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 la même manière, nous pouvons placer un deux-points (:) entre deux résultats de la fonction Index pour obtenir une sortie de matrice dans Google Sheets.
Nous pouvons utiliser cette fonctionnalité dans plusieurs situations de la vie réelle dans Google Sheets.
Exemples d’utilisation de l’Index avec Match pour un tableau 2D dans Google Sheets
Nous verrons deux ou 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 de réaliser cette tâche dans Google Sheets.
La manière la plus simple est d’utiliser Choosecols avec Match ou Xmatch.
Nous y reviendrons plus tard. D’abord, voyons la formule Index et Match qui renvoie un résultat en tableau 2D.
=index(B2:B8):index(B2:F8,0,match(« Mar »,B2:F2,0))
Cela fonctionne comme ceci.
- La formule dans la partie gauche des deux-points renvoie la première colonne de la plage.
- La formule dans la partie droite renvoie la quatrième colonne (étiquette de champ correspondant à l’aide de la fonction Match).
Cela équivaut à =index(B2:B8,0,0):index(B2:F8,0,4).
Comme nous avons placé les deux formules Index de chaque côté des deux-points, cela renvoie un résultat de matrice.
Nous pouvons utiliser une formule alternative CHOOSECOLS + Match pour copier un tableau, jusqu’à la colonne qui correspond à l’en-tête de ligne dans Google Sheets.
Voici cette formule.
=choosecols(B2:F8,sequence(match(« Mar »,B2:F2,0)))
2. Trouver une valeur et renvoyer des lignes supplémentaires
Dans le même tableau, comment trouver « b » dans la première colonne et renvoyer cette ligne + 2 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 renvoie « b » en raison du décalage de ligne # 3 (renvoyé par Match). La plage de décalage est B2:B8.
La partie droite des deux-points utilise la même formule, mais le décalage de ligne est de 5 (3+2) et le décalage de colonne est également de 5.
Ainsi, la formule renvoie la plage B4:F6.
C’est un autre exemple d’utilisation de l’Index et du Match pour renvoyer un résultat en tableau 2D dans Google Sheets.
Exemples d’utilisation de l’Index avec Xmatch pour un tableau 2D dans Google Sheets
Tout comme la fonction Match, nous pouvons également utiliser Xmatch avec Index.
J’utilise principalement Xmatch indépendamment dans deux scénarios.
- Lorsque je veux faire correspondre une clé dans une colonne depuis la dernière occurrence jusqu’à la première occurrence.
- Pour les correspondances partielles (joker).
Ci-dessous, nous utiliserons ces fonctionnalités de Xmatch et apprendrons à renvoyer une matrice en utilisant la combinaison Index et Xmatch au fur et à mesure.
1. Renvoyer les lignes entre la première et la dernière occurrence d’une valeur
J’ai des données d’échantillon non structurées qui rendent difficile l’application de fonctions comme FILTER, QUERY, etc.
Je suis donc incapable d’extraire les lignes contenant un produit particulier.
Dans l’exemple suivant, je souhaite extraire les lignes pour « Orange » et « Orange Total ».
Le premier critère sera une correspondance exacte et le dernier sera une correspondance partielle (joker), car 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)
Le premier XMATCH recherche de haut en bas dans la colonne B pour le mot clé « Orange ».
Le deuxième Xmatch recherche de bas en haut dans la même colonne pour une correspondance partielle du mot clé « Orange ».
Nous pourrions renvoyer un résultat de matrice en utilisant deux formules Index de chaque côté des deux-points.
L’Index avec Match ou Xmatch pour les résultats de tableau 2D sera particulièrement utile lorsque vous avez des données non structurées.
