Imaginez un monde où vous pouvez obtenir un résultat de tableau 2D en utilisant simplement les fonctions Index et Match dans Google Sheets. Et bien, c’est possible ! Dans cet article, je vais vous montrer comment vous pouvez utiliser ces deux fonctions pour obtenir des résultats de tableau complexes et utiles.
Qu’est-ce que la fonction Index avec Match pour un résultat de tableau 2D dans Google Sheets ?
La fonction Index est généralement utilisée pour renvoyer le contenu d’une cellule en fonction de l’offset de colonne et de ligne donné. 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 de la cinquième ligne de la plage A2:B.
Mais comment pouvons-nous utiliser la fonction Index pour renvoyer un résultat de tableau 2D dans Google Sheets ?
Utilisation de la fonction Index pour obtenir un résultat de tableau 2D
Dans les feuilles de calcul, 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 deux résultats de la fonction Index séparés par un deux-points (:), ce qui nous donne un résultat de tableau dans Google Sheets.
Cette fonctionnalité peut être utilisée dans plusieurs scénarios réels dans Google Sheets.
Exemples d’utilisation de la fonction Index avec Match pour un tableau 2D dans Google Sheets
Dans cet exemple, nous allons voir comment retourner un tableau 2D en utilisant les fonctions 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’effectuer cette tâche dans Google Sheets. La plus simple est d’utiliser Choosecols avec Match ou Xmatch.
Voici la formule Index et Match qui renvoie un tableau 2D :
‘=index(B2:B8):index(B2:F8,0,match(« Mar »,B2:F2,0))’
Cette formule fonctionne de la manière suivante :
- La formule dans la partie gauche du 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 à la fonction Match).
En plaçant les deux formules Index de chaque côté du deux-points, nous obtenons un résultat sous forme de tableau.
Nous pouvons également 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 faire correspondre la valeur « b » dans la première colonne et renvoyer cette ligne ainsi que deux lignes supplémentaires ?
La solution est la suivante :
‘=index(B2:B8,match(« b »,B2:B8,0)):index(B2:F8,match(« b »,B2:B8,0)+2,5)’
La partie gauche du deux-points renvoie « b » en raison du décalage de ligne # 3 (résultat de Match). La plage à décaler est B2:B8.
La partie droite du 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 de l’utilisation des fonctions Index et Match pour obtenir un résultat de tableau 2D dans Google Sheets.
Utilisation de la fonction Index avec Xmatch pour un résultat de tableau 2D dans Google Sheets
De manière similaire à la fonction Match, nous pouvons également utiliser la fonction Xmatch avec Index.
J’utilise principalement Xmatch dans deux scénarios :
- Lorsque je veux faire correspondre une clé dans une colonne à partir de la dernière occurrence jusqu’à la première occurrence.
- Pour effectuer une correspondance partielle avec un joker.
Nous allons utiliser ces fonctionnalités de Xmatch et apprendre comment obtenir un résultat de tableau en utilisant la combinaison de Index et Xmatch.
1. Renvoyer les lignes entre la première et la dernière occurrence d’une valeur
Si vous avez des données d’échantillon non structurées qui rendent difficile l’utilisation de fonctions telles que FILTER ou QUERY, vous pouvez utiliser Index avec Xmatch pour extraire les lignes contenant un produit spécifique.
Dans l’exemple suivant, je souhaite extraire les lignes contenant « Orange » et « Orange Total ». La première condition doit correspondre exactement, et la dernière doit correspondre partiellement (avec un joker).
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 pour le mot-clé « Orange ».
La deuxième fonction Xmatch recherche de bas en haut dans la même colonne pour une correspondance partielle du mot-clé « Orange ».
En utilisant deux formules Index de chaque côté du deux-points, nous pouvons obtenir un résultat sous forme de tableau.
L’utilisation de la fonction Index avec Match ou Xmatch pour obtenir des résultats de tableau 2D sera extrêmement utile lorsque vous avez des données non structurées.
Voilà, vous savez maintenant comment utiliser les fonctions Index et Match ou Xmatch pour obtenir des résultats de tableau 2D dans Google Sheets. J’espère que ces techniques vous seront utiles dans vos projets de feuilles de calcul passionnants !
Si vous souhaitez en savoir plus sur les fonctionnalités avancées de Google Sheets, pourquoi ne pas visiter Crawlan.com ? Vous y trouverez des ressources utiles et des astuces pour améliorer vos compétences en feuilles de calcul.
À bientôt sur Crawlan.com !
