Cours
Introduction aux statistiques dans Google Sheets
4 h
47.9K
Lors des premiers pas sur Excel, on vous présente souvent RECHERCHEV comme la méthode de base pour manipuler des données. RECHERCHEV est en effet une fonction puissante, capable de couvrir de nombreux cas d’usage, comme illustré dans Data Wrangling with VLOOKUP in Spreadsheets. Dans ce même tutoriel, nous avons également abordé les fondamentaux de la combinaison INDEX-MATCH, une autre méthode pour effectuer des recherches verticales dans des tableaux, qui offre plusieurs avantages par rapport à RECHERCHEV, au prix d’une formule un peu plus complexe.
Parmi ces avantages :
- Références de colonnes dynamiques : elles permettent d’insérer ou de supprimer des colonnes en toute sécurité dans votre jeu de données, sans casser la recherche.
- Meilleures performances sur de grands volumes : si vos tableaux sont volumineux, vous constaterez souvent qu’INDEX-MATCH est bien plus rapide que RECHERCHEV, car INDEX-MATCH ne considère que la colonne à retourner et la colonne de recherche.
- Emplacement des valeurs de recherche : RECHERCHEV ne peut pas récupérer une valeur située à gauche de la première colonne sélectionnée dans la plage, tandis qu’INDEX-MATCH peut également chercher horizontalement et n’a donc pas cette limite.
- Taille de clé moins limitée : RECHERCHEV ne peut pas rechercher des valeurs de plus de 255 caractères, alors qu’INDEX-MATCH le peut.
En outre, INDEX-MATCH permet les correspondances basées sur plusieurs colonnes et les recherches sensibles à la casse, très utiles lorsque vous devez gérer des identifiants qui diffèrent par des majuscules/minuscules. Ce tutoriel va donc approfondir les fonctionnalités avancées de la combinaison INDEX-MATCH et montrer, à travers des exemples, dans quels cas elles sont particulièrement pratiques.
Les bases d’INDEX-MATCH
Dans sa forme la plus simple, INDEX-MATCH s’utilise comme RECHERCHEV pour effectuer des recherches verticales basées sur une clé commune. La structure de base de la formule est la suivante :
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range : la plage de la colonne dont vous souhaitez récupérer la valeur.
- lookup_value : la valeur recherchée (la clé).
- lookup_column_range : la plage de la colonne contenant les clés à faire correspondre.
- match_type : 0 ou 1. 0 indique une correspondance exacte et 1 une correspondance approximative. C’est l’équivalent du paramètre FAUX/VRAI dans RECHERCHEV.
Illustrons le fonctionnement d’INDEX-MATCH avec deux petits tableaux. Le premier contient des noms et des clés uniques. Le second contient les mêmes clés (pas forcément dans le même ordre) et des descriptions. L’objectif est donc d’associer les descriptions du second tableau aux noms du premier à l’aide d’INDEX-MATCH. Voici les tableaux :
 |
 |
Comme vous le voyez, le premier tableau n’a pas la description. Associons maintenant chaque nom à sa description avec INDEX-MATCH :
Facile, n’est-ce pas ? Comme dans la vidéo, on commence par sélectionner, dans la fonction INDEX, la column_range qui contient les données à retourner. Ici, la colonne Description (M2:M8). Ensuite, on choisit, dans la fonction MATCH, la lookup_value, c’est-à-dire la cellule B2. Enfin, on ajoute la lookup_column_range (L2:L8) dans MATCH, et on sélectionne 0 comme match_type puisque nous voulons une correspondance exacte de la lookup_value. Il ne reste plus qu’à valider avec ENTRÉE et à recopier la formule vers le bas pour associer tous les noms à leur description.
INDEX-MATCH peut aussi relier des tableaux situés sur des feuilles distinctes, comme RECHERCHEV. Exemple en vidéo ci-dessous :
Remarque : vous aurez noté que les plages sélectionnées dans les vidéos sont entourées de signes $. Cela permet de figer les plages pour éviter qu’elles ne bougent quand on recopie les formules verticalement ou horizontalement. Nous garderons cette notation tout au long du tutoriel.
Jusqu’ici, tout se comporte comme un RECHERCHEV simple, avec un soupçon de complexité supplémentaire. Pourtant, comme indiqué au début, INDEX-MATCH présente divers atouts. L’un des plus utiles est la gestion dynamique des colonnes : vous pouvez ajouter des colonnes dans la plage de recherche sans impacter la formule elle-même.
Dans notre exemple, si nous ajoutons une nouvelle colonne et que nous utilisons RECHERCHEV, il faudrait réécrire la formule dans le tableau de gauche pour pointer vers la description, devenue colonne 3. Avec INDEX-MATCH et ses références de colonnes dynamiques, ce n’est pas nécessaire. La comparaison est visible dans la vidéo ci-dessous :
Comme montré, la colonne qui effectuait la recherche via INDEX-MATCH s’est automatiquement ajustée quand nous avons ajouté la colonne des types de vaisseaux, alors que celle basée sur RECHERCHEV ne l’a pas fait. Cet avantage d’INDEX-MATCH se révèle particulièrement précieux pour des recherches dans de grands tableaux Excel, par exemple un rapport commercial. Imaginez plusieurs tableaux de bord Excel s’appuyant sur une table unique : si vous utilisez RECHERCHEV et qu’un jour vous devez insérer une colonne au milieu pour ajouter un indicateur, il faudra modifier toutes les formules RECHERCHEV des tableaux de bord concernés. Avec INDEX-MATCH comme méthode de recherche, ce problème ne se pose pas.
INDEX-MATCH avancé : recherche sensible à la casse
Les références de colonnes dynamiques sont très utiles, mais INDEX-MATCH ne s’arrête pas là. On peut, par exemple, enrichir la syntaxe de base pour effectuer des recherches sensibles à la casse. Pour cela, on ajoute la fonction EXACT à l’intérieur de MATCH. La syntaxe détaillée est la suivante :
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range : la plage du tableau d’intérêt, incluant la colonne de recherche et la colonne à retourner.
- lookup_value : la valeur recherchée (la clé).
- lookup_column_range : la plage de la colonne contenant les clés à faire correspondre.
- match_type : 0 ou 1. 0 pour une correspondance exacte, 1 pour une correspondance approximative. Équivalent au paramètre FAUX/VRAI de RECHERCHEV.
- desired_column_number : identique au numéro d’index de colonne dans RECHERCHEV. C’est l’index de la colonne qui contient la donnée à récupérer.
La recherche sensible à la casse via cette variante d’INDEX-MATCH est très utile lorsque les clés contiennent des lettres et se distinguent uniquement par la casse (par exemple : "00567UUp" et "00567UUP"). Ce cas survient notamment dans le CRM Salesforce lors du rapprochement d’IDs de comptes ou d’opportunités. Pour l’instant, nous n’utiliserons pas de données Salesforce, mais une version adaptée de nos petits tableaux dans la vidéo ci-dessous pour montrer la formule en action.
Remarque : la combinaison de fonctions ci-dessus est encadrée d’accolades. Cela signifie qu’il s’agit d’une formule matricielle, à valider avec Ctrl+Maj+Entrée.
La syntaxe est un peu longue à écrire, mais le résultat est celui attendu : la recherche fonctionne comme un INDEX-MATCH classique, tout en tenant compte de la casse. La « magie » vient de la fonction EXACT, qui renvoie un tableau de VRAI/FAUX. Chaque VRAI indique une valeur strictement identique, casse comprise. MATCH récupère ensuite la position du premier VRAI.
Attention : il est essentiel de valider avec Ctrl+Maj+Entrée en finalisant la formule, sans quoi vous obtiendrez une erreur #N/A difficile à diagnostiquer.
INDEX-MATCH avancé : recherche basée sur plusieurs critères
La recherche sensible à la casse, c’est déjà très pratique, mais il y a encore mieux : la recherche basée sur plusieurs critères. Prendre en compte plusieurs critères est courant avec des fonctions conditionnelles de comptage ou de somme comme NB.SI.ENS (COUNTIFS) ou SOMME.SI.ENS (SUMIFS), mais c’est plus rare dans les fonctions de recherche. Pourtant, cela peut s’avérer déterminant. Par exemple, dans un fichier RH, vous pouvez vouloir retrouver le salaire d’un collaborateur à partir de son prénom et de son nom. En ne tenant compte que d’une seule colonne, vous risquez de tomber sur l’homonyme d’un autre service. En combinant plusieurs critères, vous maximisez vos chances d’identifier la bonne personne.
Voici la structure d’INDEX-MATCH prenant en compte plusieurs critères :
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
La formule reprend pour l’essentiel les mêmes composants qu’INDEX-MATCH standard, mais elle peut intégrer n valeurs de recherche et leurs plages respectives. Si besoin, remontez à la syntaxe de base pour un rappel des éléments. Le « 1 » dans MATCH s’explique par le fait que la formule génère des tableaux de 1 et de 0 représentant les correspondances pour chaque critère ; MATCH retourne ensuite la position du premier 1 trouvé.
Comme pour la variante sensible à la casse, cette formule est entre accolades : il faut la valider avec Ctrl+Maj+Entrée, car c’est une formule matricielle.
Voyons un exemple en sélectionnant des vaisseaux correspondant à une race donnée et à une vitesse de distorsion maximale donnée, dans la vidéo ci-dessous :
Et voilà ! Vous avez désormais les outils pour faire des recherches sur plusieurs clés. Dans ce petit exemple, je n’ai utilisé que deux critères, la clé et la vitesse maximale, mais vous pouvez en ajouter autant que nécessaire. Pour revenir à l’exemple RH, vous pourriez combiner prénom, nom et intitulé de poste afin de limiter le risque d’associer le salaire d’un mauvais collaborateur.
Conclusion
Félicitations ! Vous maîtrisez désormais INDEX-MATCH et tout ce qu’il permet au-delà d’une simple recherche verticale. Si vous devez un jour gérer des rapports Excel complexes, cette combinaison de fonctions deviendra probablement votre meilleure alliée. Comme beaucoup, j’ai appris RECHERCHEV à l’école, mais en utilisant Excel sérieusement pour des tâches comme les recherches sensibles à la casse d’IDs de comptes Salesforce, j’ai compris qu’INDEX-MATCH était un bien meilleur choix sur le long terme, même si sa mise en place est plus fastidieuse et sujette aux erreurs de saisie. Essayez-le lors de votre prochaine recherche verticale sous Excel : vous pourriez bien l’adopter, comme moi.
Envie d’aller plus loin ? Jetez un œil au cours de DataCamp Data Analysis with Spreadsheets Course.