Tuto Excel : recherche verticale RECHERCHEV (VLOOKUP)

Je sais que la fonction RECHERCHEV (VLOOKUP) semble comme la chose la plus compliquée de tous les temps. Mais lorsque vous aurez fini de lire ce tutoriel, vous vous demanderez comment vous avez pu survivre dans Excel sans cela.

Comme dans le cas des tableaux croisés dynamiques, la fonction RECHERCHEV (VLOOKUP) de Microsoft Excel est plus simple à utiliser que vous ne le pensez. De plus, elle est incroyablement puissante et vous voudrez certainement l'avoir dans votre arsenal d'armes analytiques.

Que fait RECHERCHEV, exactement ? Voici l'explication simple : la fonction RECHERCHEV recherche une valeur spécifique dans vos données et, une fois identifiée, elle peut trouver et afficher une autre information associée à cette valeur.

Vous pouvez utiliser la formule RECHERCHEV pour transférer les données sur les revenus d'une feuille de calcul distincte et les associer au client approprié en fonction d'un identifiant commun, tel qu'un identifiant client ou une adresse électronique. Dans cet exemple, RECHERCHEV vous permet de visualiser facilement les revenus par client sans rechercher, copier et coller pour chaque cellule individuelle.

Concrètement, cela signifie que vous pouvez extraire les données de chiffre d’affaires de votre deuxième feuille de calcul et les intégrer aux données client de votre premier tableur afin d’obtenir une vision plus globale des performances de votre entreprise.

Dans ce tutoriel vous allez apprendre en quoi ça consiste la fonction RECHERCHEV, vous verrez aussi la syntaxe de cette fonction et ses arguments, et comment l’utiliser.

Qu'est-ce que RechercheV ?

RECHERCHEV est une fonction Excel qui vous permet de rechercher et de récupérer le contenu d’une cellule à partir d’une autre colonne. Comme vous pouvez le deviner, ce « V » signifie vertical et repose sur la recherche de données dans la colonne la plus à gauche d'une table de correspondance. Cette colonne peut être sur la feuille de calcul que vous utilisez ou une autre. La fonction nécessite un champ commun ou une clé et quatre arguments.

Fondamentalement, RECHERCHEV vous permet de rechercher des informations spécifiques dans votre feuille de calcul. Par exemple, si vous avez une liste de produits avec des prix, vous pouvez rechercher le prix d'un article spécifique.

Nous allons utiliser RECHERCHEV pour trouver le prix du produit 7. Vous pouvez probablement déjà voir que le prix est de 35 DH, mais c'est parce que c'est un exemple simple. Une fois que vous aurez appris à utiliser RECHERCHEV, vous pourrez l’utiliser avec des feuilles de calcul plus volumineuses et plus complexes. C’est à ce moment-là que cela deviendra vraiment utile.

La fonction vous permet de spécifier s'il faut utiliser une correspondance exacte ou approximative. Un exemple courant pourrait être la recherche du nom d’un produit à partir de son ID. Et comme nous le verrons bientôt, Excel fournit des indications sur les arguments requis.

Exemple 1 : Une feuille de calcul et une correspondance approximative

On suppose par exemple que vous vous êtes porté volontaire pour travailler aux élections locales. La partie qu’on vous a demandé de faire est l'analyse des données du fichier d'inscription des électeurs. Ce sont généralement des fichiers volumineux contenant de nombreuses informations.

Un élément de données est la date de naissance de l’électeur. Les données de la feuille de calcul sont tranchées, coupées en dés et réutilisées. Cependant, on ne voulait pas que la date de naissance de l’électeur soit indiquée sur les fichiers distribués.

Au lieu de cela, je vous propose de créer des segments basés sur des tranches d'âge et d’utiliser une recherche verticale qui renverrait la valeur à la cellule souhaitée.

Comprendre la fonction RECHERCHEV (VLOOKUP)

Commençons par la première électrice fictive, Karima Laayoune. Si vous parcourez la colonne D, vous verrez qu’elle a 41 ans et qu’elle est dans le segment « Mature ». La valeur « Mature » de la colonne E a été intégrée de manière dynamique à l’aide de la fonction RECHERCHEV d’Excel.

La petite table à droite avec des en-têtes rouges est la table de recherche. Certaines personnes se réfèrent également à cette table de recherche en tant que tableau de table. C’est là qu’on a défini les 4 tranches d’âge.

La façon dont les segments fonctionnent est que si les électeurs ont moins de 21 ans, ils sont « nouveaux ». De 21 à 38 ans, ils sont « jeunes ». Entre 39 et 59 ans, ils sont « matures » et s'ils ont 60 ans ou plus, ils sont « aînés ».

Dans le cas de Karima, Excel prendrait son âge de 41 ans dans la cellule D2 et trouverait la correspondance la plus proche dans la colonne H. Ces deux colonnes contiennent des données d’âge, qui constituent notre clé commune.

Lorsque Excel trouve une correspondance, il passe ensuite à la colonne I et obtient l’étiquette. Cette valeur a ensuite été copiée dans la cellule E2, qui représente le « segment ». Il peut être utile d’imaginer les colonnes verticales. Après tout, il s’agit d’une recherche verticale.

Vous remarquerez peut-être que la table de recherche ne répertorie pas tous les âges. Ce n’est pas nécessaire, car on utilise une correspondance approximative. On dit à Excel de trouver l’âge le plus proche. Par exemple, la prochaine électrice, Sara Bent, a 48 ans, mais la valeur 48 n’a pas de valeur dans la colonne H. Dans ce cas, 48 tombes entre 39 et 59 ; elle est donc également étiquetée « mature ».

Comme vous l'avez probablement deviné, l'utilisation de la fonction RECHERCHEV nécessite une clé commune. Dans ce cas, c’est son âge. Les deux colonnes D et H contiennent les âges. Les en-têtes et le contenu des cellules peuvent être différents.

Comment fonctionne REHERCHEV

Voyons un peu le mystère et montrons comment apparaît RECHERCHEV dans la barre de formule. Dans cette illustration, j'ai cliqué sur la cellule E2.

[1] - Ceci représente la formule RECHERCHEV pour la cellule E2

[2] - D2 est notre premier argument appelé valeur_recherché.

[3] - $ H2 $ 2: $ I $ 5 est notre table_matrice et c’est le deuxième argument.

[4] - 2 est le no_index_col de votre Table_matrice. C’est le troisième argument.

[5] - VRAI est le Valeur_proche. C’est le quatrième argument.

Comprendre les arguments de RECHERCHEV

Le terme "argument" n’est pas aussi compliqué comme ça apparait. Si vous connaissez la barre de formule Excel, un argument est ce qui se trouve entre les parenthèses (). Il fournit une sorte de valeur d'entrée pour une fonction Excel.

Certaines fonctions nécessitent des arguments et d’autres pas. Par exemple, pour calculer l’âge de l’électeur, j’ai également utilisé la fonction AUJOURDHUI = AUJOURDHUI (), qui n’utilise aucun argument. Voici quelques exemples d'arguments courants :

• Plage de cellules
• Valeur logique vrai / faux
• nombre

En utilisant la formule de la cellule E2, voici comment fonctionnent ces arguments.

• valeur_recherché:  Pensez à ce champ comme point de départ. Dans cet exemple, je souhaite rechercher l’Age Karima de à partir de la cellule D2.
• Table_matrice : Il s'agit de la plage de cellules de votre table de recherche. Cette recherche de plage peut figurer sur votre feuille de calcul existante ou sur une autre feuille de calcul.
• No_index_col :  Il s'agit du numéro de la colonne de votre table de recherche contenant les informations dont vous avez besoin. Dans notre exemple, nous voulons la colonne 2 de la colonne « Type ». Cela deviendra notre nom de segment. Lorsque nous comptons, nous comptons les colonnes de la table de recherche. Même si la colonne « Type » est la colonne 1 ou la neuvième colonne, il s’agit de la deuxième colonne de la table de recherche. Certaines personnes appellent cela un index de colonne.
• Valeur_proche :  ce champ définit le degré de correspondance nécessaire entre votre valeur Valeur_recherché (D2) et la valeur de la colonne la plus à gauche de notre table de recherche. Dans notre cas, nous voulons une correspondance approximative afin d’utiliser « VRAI ».

Utiliser RECHERCHEV dans notre formule

Ajoutez dans la colonne où vous allez entrer la formule. Dans mon cas, j'ai ajouté la colonne E « Segment ».

Cliquez sur la cellule E2 puis cliquez sur l'onglet « Formules » dans le menu supérieur et ensuite cliquez sur le bouton « Insérer une fonction ».

Dans la boîte de dialogue « Insérer une fonction », tapez « RechercheV » dans la zone de texte « Rechercher une fonction ». Vous pouvez également le sélectionner dans la catégorie « Recherche et référence » devant le champ « Ou sélectionnez une catégorie ». Cliquez sur OK.

La boîte de dialogue « Arguments de la fonction » apparaîtra avec les zones de texte pour les arguments requis.

Dans Valeur_recherché, tapez D2. Ou, vous pouvez cliquer sur la cellule D2.

Dans Table_matrice, tapez $H$2:$I$5. Notez les signes $ pour fixer les cellules.

Dans no_index_col, tapez 2.

Dans Valeur_proche, tapez vrai.

La boîte de dialogue Arguments de votre fonction devrait ressembler à ceci. Remarquez-en bas à gauche de la boite de dialogue, vous pouvez voir le résultat de la formule.

Cliquez sur OK. Vous devriez maintenant voir « Mature » dans la cellule E2.

Cliquez sur la cellule E2 ensuite cliquez sur le petit carré vert (poignée de recopie) dans le coin inférieur droit de la cellule pour copier la formule RECHERCHEV dans la colonne.

Exemple 2 - Plusieurs feuilles de calcul et correspondance exacte

Prenons l'exemple RECHERCHEV ci-dessus. Supposons que vous examiniez vos données HubSpot et que vous vérifiiez quelles pages de votre site ont été consultées par vos contacts. Vous faites également attention à savoir si l'un ou l'autre de ces contacts s'est converti en client.

En plus de savoir lesquels de ces contacts ont été fermés, vous voulez savoir combien de revenus récurrents mensuels rapportent chacun. Ainsi, vous pouvez lier vos revenus aux pages de votre site et en faire quelques analyses pour voir quelles pages ont le plus grand impact sur vos résultats.

Un seul problème : vos données de revenus récurrents mensuels (MRR) sont stockées dans votre CRM. Et bien que vous puissiez rechercher manuellement chaque contact dans votre CRM pour trouver son MRR, puis associer manuellement ces valeurs à ses contacts correspondants dans vos données HubSpot, le processus dans son ensemble serait fastidieux et peu pratique.

Dans les étapes ci-dessous, nous allons attribuer la bonne valeur à chacun de ces composants, en utilisant le nom du client comme identifiant unique pour trouver le MRR de chaque client.

1. Identifiez une colonne de cellules que vous souhaitez remplir avec de nouvelles données.

J’ai ajouté une colonne que j’ai nommé « revenu (MRR) » à ma table, pour les revenus récurrents mensuels. Cette nouvelle colonne indique où iront les données que nous allons récupérer récupérer.

2. Insérez la formule dans votre cellule en surbrillance.

À gauche de la barre de texte au-dessus de votre feuille de calcul, vous verrez une petite icône de fonction qui ressemble à un script « Fx ». Cliquez sur la première cellule vide sous le titre de votre colonne pour moi c’est D2, puis cliquez sur l'icône de cette fonction. La boite de dialogue « insérer une fonction » vu précédemment apparaîtra sur votre écran.

Sélectionnez « RECHERCHEV » dans la liste comme vu précédemment. Ensuite, sélectionnez « OK » au bas de la boite. La cellule que vous avez actuellement sélectionnée dans votre feuille de calcul doit maintenant ressembler à ceci : = RECHERCHEV ()

Vous pouvez également entrer manuellement cette formule dans un appel en saisissant exactement le texte en gras ci-dessus dans la cellule souhaitée.

3. Entrez la valeur de recherche pour laquelle vous voulez récupérer de nouvelles données.

Avec le texte = RECHERCHEV entré dans votre première cellule, il est temps de remplir la formule avec quatre critères différents. Ces critères aideront Excel à préciser exactement où se trouvent les données souhaitées et ce qu’il faut rechercher.

Le premier critère est votre valeur de recherche : il s'agit de la valeur de votre feuille de calcul à laquelle des données sont associées, que vous souhaitez que Excel recherche et renvoie pour vous. Pour le saisir, cliquez sur la cellule qui contient une valeur pour laquelle vous essayez de trouver une correspondance. Dans notre exemple ci-dessus, il se trouve dans la cellule A2. Vous commencerez à migrer vos nouvelles données vers D2, car cette cellule représente le MRR du nom du client répertorié en A2.

N'oubliez pas que votre valeur de recherche peut être n'importe quoi : texte, chiffres, liens vers des sites Web, vous le nommez. Tant que la valeur que vous recherchez correspond à la valeur de la feuille de calcul référente (nous en reparlerons à l'étape suivante), cette fonction renverra les données souhaitées.

4. Entrez le tableau de la feuille de calcul où se trouvent les données souhaitées.

En regard du champ « table_matrice », entrez la plage de cellules que vous souhaitez rechercher et la feuille contenant ces cellules, en utilisant le format présenté dans la capture d'écran ci-dessus. L'entrée ci-dessus signifie que les données que nous recherchons se trouvent dans un tableur intitulé « Revenu » et peuvent être trouvées n'importe où entre la colonne A et la colonne B.

La feuille contenant vos données doit se trouver dans votre fichier Excel actuel. Cela signifie que vos données peuvent figurer dans un tableau de cellules différent quelque part dans votre feuille de calcul actuelle ou dans une feuille de calcul différente liée au bas de votre classeur.

5. Entrez le numéro de colonne des données à renvoyer par Excel.

Sous le champ tableau de table, vous entrez le "numéro d'index de colonne" du tableau que vous recherchez. Par exemple, si vous vous concentrez sur les colonnes A et B, mais que les valeurs spécifiques que vous souhaitez figurent dans la colonne B, vous devrez entrer « 2 » dans le champ « no_index_col »,  puisque la colonne B est la 2ème colonne à partir de la gauche.

6. Entrez votre plage de recherche pour trouver une correspondance exacte ou approximative de votre valeur de recherche.

Dans des situations comme la nôtre, qui concernent les revenus mensuels, vous souhaitez rechercher des correspondances exactes dans le tableau que vous recherchez. Pour ce faire, entrez « faux » dans le champ « valeur proche » Cela indique à Excel que vous souhaitez rechercher uniquement le chiffre d'affaires exact associé à chaque contact commercial.

Pour répondre à votre question brûlante : Oui, vous pouvez autoriser Excel à rechercher une correspondance approximative plutôt qu'une correspondance exacte. Pour ce faire, entrez simplement « vrai » au lieu de « faux » comme dans le premier exemple que nous avons vu.

Lorsque RECHERCHEV est défini pour une correspondance approximative, il recherche des données qui ressemblent le plus à votre valeur de recherche, plutôt que des données identiques à cette valeur. Si vous recherchez des données associées à une liste de liens de sites Web, par exemple, et si certains de vos liens ont un "https: //" au début, il pourrait vous incomber de rechercher une correspondance approximative juste au cas où des liens n’ont pas cette balise "https: //". De cette façon, le reste du lien peut correspondre sans cette balise de texte initiale, ce qui entraîne le renvoi d'une erreur par votre formule RECHERCHEV si Excel ne parvient pas à la trouver.

7. Cliquez sur « OK » et remplissez votre nouvelle colonne.

Pour importer officiellement les valeurs souhaitées dans votre nouvelle colonne, cliquez sur « OK » après avoir renseigné le champ « valeur_proche ». Cela va peupler votre première cellule. Vous pouvez en profiter pour regarder dans l’autre feuille de calcul afin de vous assurer que cette valeur est correcte.

Si tel est le cas, renseignez le reste de la nouvelle colonne avec chaque valeur suivante en cliquant sur la première cellule remplie, puis sur la minuscule carrée qui apparaît dans le coin inférieur droit de cette cellule. Terminé ! Toutes vos valeurs devraient apparaître.