Préparer ses données avec RECHERCHEV dans les feuilles de calcul

Parfois, la data science ne rime pas avec deep learning, big data ou systèmes de recommandation. Il arrive, surtout si vous travaillez dans un secteur comme la finance ou dans une entreprise ayant longtemps misé sur Excel, que vous deviez manipuler des feuilles de calcul Excel très souvent. C’était mon cas : mon employeur s’appuyait massivement sur Excel et j’ai donc dû en maîtriser de nombreuses fonctionnalités pour certaines de mes missions.

Même si R et Python présentent de nets avantages sur Excel dans bien des situations, Excel reste un outil très performant pour certaines tâches, comme la création de rapports métier. Dans ce tutoriel, je vais me concentrer sur l’une des fonctions intégrées d’Excel qui facilite grandement le travail lorsqu’on jongle avec plusieurs tableaux en parallèle.

Cette fonction, c’est RECHERCHEV (VLOOKUP). RECHERCHEV permet de relier différentes colonnes d’une même feuille ou entre plusieurs feuilles d’un même classeur. En substance, elle vous permet de rechercher une valeur dans une colonne et d’en renvoyer la valeur correspondante située dans une autre colonne. Cette fonctionnalité est très utile lorsque vous avez de grands tableaux non triés et que vous devez les relier via une colonne donnée (idéalement contenant une clé unique).

Illustrons le fonctionnement de RECHERCHEV entre des colonnes d’une même feuille avec un petit tableau d’exemple :

La capture ci‑dessus montre une expression RECHERCHEV classique telle qu’on l’écrirait dans un rapport. Elle se compose des éléments suivants :

• Fonction SI.CONDITIONS/gestion d’erreurs (SIERREUR) : utilisée pour remplacer une erreur par un tiret (-) si la clé est vide ou absente du tableau. C’est une bonne pratique pour un affichage propre en cas d’erreur, mais ce n’est pas indispensable au fonctionnement de RECHERCHEV.
• Arguments de RECHERCHEV :
  • E2 est la valeur cherchée (ici nous avons utilisé KOG comme exemple, mais cela pourrait être n’importe quelle clé). Cette valeur doit se trouver dans la colonne la plus à gauche de la plage de recherche.
  • B1:C18 est la plage/tableau de recherche. La colonne la plus à gauche a l’indice 1, comme indiqué. Cette colonne doit contenir les valeurs recherchées pour que la fonction fonctionne.
  • 2 est le numéro d’indice de colonne dans la plage à partir de laquelle vous souhaitez récupérer la valeur. Dans cet exemple, nous voulons récupérer la description : nous choisissons donc la colonne 2 de la plage.
  • FAUX indique que vous souhaitez une correspondance exacte de la valeur cherchée. Ici, nous choisissons FAUX pour obtenir l’équivalent exact de la clé KOG. L’autre option est VRAI, qui cherche une correspondance approximative et qui est la valeur par défaut si vous n’indiquez rien. En général, on déconseille VRAI pour faire correspondre des clés ou des chaînes de caractères, car cela peut donner des résultats inattendus.

Après avoir saisi cette formule RECHERCHEV, si vous appuyez sur ENTRÉE dans Excel, vous obtiendrez le résultat suivant :

Comme vous pouvez le voir, RECHERCHEV renvoie exactement ce que l’on attend : "King of Gondor", la description associée à la clé KOG. Ce sera vrai pour toute clé que vous saisirez désormais dans la cellule E2. Essayons par exemple avec la clé KIN.

The King in the North ! Exactement : si nous saisissons la clé KIN, nous obtenons la description attendue pour notre cher Jon (Snow).

Cependant, RECHERCHEV ne sert pas qu’à récupérer une seule valeur à partir d’une clé. Imaginons que nous scindions ce tableau en deux et que nous plaçons chaque partie sur une feuille distincte du même classeur.

Joindre des tableaux

Comme vous le voyez, les données sont désormais réparties sur deux feuilles (S1 et S2) et les clés du tableau de la deuxième feuille (S2) ne sont pas dans le même ordre. Bonne nouvelle : la colonne des clés est commune aux deux feuilles, quel que soit l’ordre. Nous pouvons donc reconstituer les données dans S1 avec RECHERCHEV (vous pouvez l’assimiler à une jointure gauche sur une seule colonne). La syntaxe est la suivante :

Cette fois, le premier argument, la valeur cherchée, devient B2, puisque nous voulons faire correspondre la clé B2 avec le tableau de S2. La plage de recherche est maintenant l’intégralité du tableau de S2, avec la première colonne contenant les clés communes. Vous remarquerez la présence de signes dollar dans la plage de recherche. On les ajoute en appuyant sur F4 après avoir sélectionné la plage : cela "verrouille" la plage afin que, lorsque vous recopie-collez la formule vers le bas dans S1, RECHERCHEV continue d’utiliser la même zone sans la faire glisser. Le numéro d’indice de colonne reste 2 puisque nous voulons récupérer la description, qui est la deuxième colonne de S2. Enfin, nous mettons FAUX en dernier argument pour exiger une correspondance exacte des clés.

Il ne reste plus qu’à recopier la formule jusqu’à la dernière ligne du tableau. Le tableau obtenu dans S1 est le suivant :

Et voilà ! Nous avons rassemblé les données avec RECHERCHEV. Joindre des tableaux de cette manière est très courant dans un contexte métier, surtout lorsque SQL, R ou Python ne sont pas les outils privilégiés des dirigeants. Par exemple, vous pouvez avoir deux fichiers contenant des informations différentes sur des opportunités commerciales qualifiées, reliées par un identifiant d’opportunité unique, pour produire un rapport de ventes mensuel dans Excel (c’est d’ailleurs mon cas d’usage le plus fréquent).

Utiliser les correspondances approximatives

Jusqu’ici, vous aurez noté que nous n’avons jamais utilisé VRAI en dernier argument (c’est‑à‑dire une correspondance approximative) dans nos RECHERCHEV. C’est parce que nous nous sommes concentrés sur des clés uniques à faire correspondre exactement. Vous pouvez toutefois utiliser VRAI sans risque lorsque vous travaillez avec des données numériques.

Supposons, par exemple, que nous vendions du poisson et que nous offrions les remises suivantes selon les quantités achetées (si l’on vous demande pourquoi le poisson : facile, le poisson c’est génial !).

La formule RECHERCHEV dans la cellule F2 est cette fois :

Une fois cette formule en place, la remise sera la plus proche des seuils indiqués dans le tableau. Par exemple, si nous achetons 25 poissons, notre remise sera de 5 %, si nous en achetons 70, elle sera de 10 %, et pour 120 poissons, elle sera de 15 %. Voici ces exemples :

Introduction à INDEX-EQUIV

RECHERCHEV est une fonction puissante pour relier des colonnes. Il existe toutefois une alternative : la combinaison INDEX‑EQUIV (INDEX‑MATCH). Contrairement à RECHERCHEV, qui est une seule fonction, INDEX‑EQUIV en combine deux et offre plusieurs avantages, même si sa syntaxe est un peu plus complexe.

Dans ce tutoriel, nous allons voir un exemple simple où nous utilisons INDEX‑EQUIV pour joindre deux tableaux selon une clé commune, comme nous l’avons fait plus haut avec RECHERCHEV, et en expliquer la syntaxe (voir ci‑dessous).

Les composants d’INDEX‑EQUIV sont les suivants :

• Arguments d’INDEX :
  • M2:M18 : c’est la plage de la colonne dont vous souhaitez récupérer les valeurs. Ici, nous voulons la colonne Description, donc nous sélectionnons cette plage. Notez que nous avons appuyé sur F4 après la sélection pour la verrouiller et éviter qu’elle ne bouge quand on recopie la formule.
• Arguments d’EQUIV :
  • B2 : c’est la clé que nous cherchons. Elle correspond à la valeur cherchée de RECHERCHEV.
  • L2:L18 : c’est la plage contenant les clés dans laquelle la valeur cherchée doit se trouver. Là aussi, nous avons utilisé F4.
  • 0 : équivaut à FAUX dans RECHERCHEV, c’est‑à‑dire une correspondance exacte.

En observant la syntaxe, on perçoit déjà l’un des grands avantages d’INDEX‑EQUIV par rapport à RECHERCHEV : l’immunité aux colonnes. Si nous décidons d’ajouter une colonne entre Key et Description dans le tableau de droite, cela ne pose aucun problème : la formule continuera à pointer vers la colonne Description. Illustration ci‑dessous :

Avec RECHERCHEV, ce n’est pas possible sans modifier la formule, car la référence de colonne est statique. Autrement dit, dans l’exemple ci‑dessus, vous auriez initialement mis 2 comme numéro d’indice de colonne pour récupérer la Description. Après l’ajout de la colonne Fish, RECHERCHEV renverrait désormais la valeur de Fish, devenue la colonne 2 de la plage. Il faudrait alors modifier manuellement la formule et passer le numéro d’indice de colonne à 3 pour retrouver le bon comportement.

Pour joindre la description au nom dans le tableau de gauche avec INDEX‑EQUIV, la procédure est identique à RECHERCHEV : il suffit de recopier la formule vers le bas, comme ci‑dessous :

Pour joindre des tableaux entre différentes feuilles d’un même classeur, la procédure est la même. La seule différence est que vous sélectionnerez la plage des clés communes ainsi que la plage de la colonne depuis laquelle récupérer les valeurs sur la seconde feuille.

Conclusion

J’espère que ce tutoriel vous a plu. RECHERCHEV et INDEX‑EQUIV sont quasiment incontournables pour quiconque travaille avec Excel en contexte professionnel. Je vous encourage à explorer INDEX‑EQUIV au‑delà de ce qui est présenté ici : correspondance sensible à la casse (oui, par défaut INDEX‑EQUIV et RECHERCHEV ne distinguent pas les majuscules/minuscules), appariement sur plusieurs colonnes, etc. Continuez à apprendre : le ciel est votre seule limite !

Pour aller plus loin avec RECHERCHEV, suivez le cours Data Analysis with Spreadsheets de DataCamp.