Dans un article récent, nous avons introduit la fonction Excel appelée RECHERCHEV et a expliqué comment il pourrait être utilisé pour récupérer des informations d’une base de données dans une cellule d’une feuille de calcul locale. Dans cet article, nous avons mentionné qu’il y avait deux utilisations de RECHERCHEV, et une seule d’entre elles concernait l’interrogation de bases de données. Dans cet article, le deuxième et dernier de la série RECHERCHEV, nous examinons cette autre utilisation moins connue de la fonction RECHERCHEV.
Si vous ne l’avez pas déjà fait, veuillez lire le premier article RECHERCHEV – cet article supposera que la plupart des concepts expliqués dans cet article sont déjà connus du lecteur.
Lorsque vous travaillez avec des bases de données, VLOOKUP reçoit un «identifiant unique» qui sert à identifier l’enregistrement de données que nous souhaitons trouver dans la base de données (par exemple, un code produit ou un identifiant client). Cet identifiant unique existe dans la base de données, sinon VLOOKUP nous renvoie une erreur. Dans cet article, nous examinerons une manière d’utiliser RECHERCHEV où l’identifiant n’a pas du tout besoin d’exister dans la base de données. C’est presque comme si RECHERCHEV peut adopter une approche «assez proche est assez bonne» pour renvoyer les données que nous recherchons. Dans certaines circonstances, c’est ce dont nous avons besoin.
Nous illustrerons cet article avec un exemple concret – celui du calcul des commissions générées sur un ensemble de chiffres de vente. Nous allons commencer par un scénario très simple, puis le rendre progressivement plus complexe, jusqu’à ce que la seule solution rationnelle au problème soit d’utiliser RECHERCHEV. Le scénario initial de notre entreprise fictive fonctionne comme suit: si un vendeur crée plus de 30 000 $ de ventes au cours d’une année donnée, la commission qu’il gagne sur ces ventes est de 30%. Sinon, leur commission n’est que de 20%. Jusqu’à présent, c’est une feuille de calcul assez simple:
Pour utiliser cette feuille de calcul, le vendeur entre ses chiffres de vente dans la cellule B1 et la formule de la cellule B2 calcule le taux de commission correct qu’il a le droit de recevoir, qui est utilisé dans la cellule B3 pour calculer la commission totale due au vendeur (qui est une simple multiplication de B1 et B2).
La cellule B2 contient la seule partie intéressante de cette feuille de calcul – la formule pour décider du taux de commission à utiliser: celui du seuil de 30 000 $ ou celui du seuil. Cette formule utilise la fonction Excel appelée SI. Pour les lecteurs qui ne sont pas familiers avec IF, cela fonctionne comme ceci:
SI()
Où est une expression qui évalue soit vrai ou faux. Dans l’exemple ci-dessus, le est l’expression B1
Comme vous pouvez le voir, l’utilisation d’un total de ventes de 20 000 $ nous donne un taux de commission de 20% dans la cellule B2. Si nous saisissons une valeur de 40000 USD, nous obtenons un taux de commission différent:
Donc, notre feuille de calcul fonctionne.
Rendons les choses plus complexes. Introduisons un deuxième seuil: si le vendeur gagne plus de 40000 $, son taux de commission passe à 40%:
Assez facile à comprendre dans le monde réel, mais dans la cellule B2, notre formule devient plus complexe. Si vous regardez attentivement la formule, vous verrez que le troisième paramètre de la fonction IF d’origine (la) est maintenant une fonction IF entière à part entière. C’est ce qu’on appelle une (une fonction dans une fonction). C’est parfaitement valide dans Excel (cela fonctionne même!), Mais c’est plus difficile à lire et à comprendre.
Nous n’allons pas entrer dans les détails de comment et pourquoi cela fonctionne, ni d’examiner les nuances des fonctions imbriquées. Ceci est un tutoriel sur RECHERCHEV, pas sur Excel en général.
Bref, ça empire! Qu’en est-il lorsque nous décidons que s’ils gagnent plus de 50 000 $, ils ont droit à une commission de 50 p. 100, et s’ils gagnent plus de 60 000 $, ils ont droit à une commission de 60 p.
Maintenant, la formule de la cellule B2, bien que correcte, est devenue pratiquement illisible. Personne ne devrait avoir à écrire des formules où les fonctions sont imbriquées sur quatre niveaux! Il doit sûrement y avoir un moyen plus simple?
Il y en a certainement. RECHERCHEV à la rescousse!
Redessinons un peu la feuille de calcul. Nous garderons tous les mêmes chiffres, mais l’organiserons d’une manière nouvelle, plus:
Prenez un moment et vérifiez par vous-même que le nouveau Tableau des taux fonctionne exactement de la même manière que la série de seuils ci-dessus.
Conceptuellement, ce que nous sommes sur le point de faire est d’utiliser RECHERCHEV pour rechercher le total des ventes du vendeur (à partir de B1) dans le tableau des taux et nous renvoyer le taux de commission correspondant. Notez que le vendeur peut en effet avoir créé des ventes correspondant à l’une des cinq valeurs du tableau des tarifs (0 $, 30 000 $, 40 000 $, 50 000 $ ou 60 000 $). Ils peuvent avoir généré des ventes de 34 988 $. Il est important de noter que 34 988 $ apparaissent dans le tableau des tarifs. Voyons si VLOOKUP peut résoudre notre problème de toute façon…
Nous sélectionnons la cellule B2 (l’emplacement où nous voulons mettre notre formule), puis insérons la fonction RECHERCHEV à partir du Formules languette:
Le Arguments de fonction La boîte de recherche RECHERCHEV apparaît. Nous remplissons les arguments (paramètres) un par un, en commençant par le Lookup_value, qui est, dans ce cas, le total des ventes de la cellule B1. Nous plaçons le curseur dans le Lookup_value champ puis cliquez une fois sur la cellule B1:
Ensuite, nous devons spécifier à VLOOKUP dans quelle table rechercher ces données. Dans cet exemple, il s’agit bien sûr de la table des taux. Nous plaçons le curseur dans le Tableau_table , puis mettez en surbrillance l’intégralité du tableau des tarifs -:
Ensuite, nous devons spécifier quelle colonne du tableau contient les informations que nous voulons que notre formule nous renvoie. Dans ce cas, nous voulons le taux de commission, qui se trouve dans la deuxième colonne du tableau, nous entrons donc un 2 dans le Col_index_num domaine:
Enfin, nous saisissons une valeur dans le Range_lookup domaine.
Pour être explicite, nous entrerons une valeur de vrai dans le Range_lookup domaine. Ce serait également bien de le laisser vide, car il s’agit de la valeur par défaut:
Nous avons complété tous les paramètres. Nous cliquons maintenant sur le d’accord et Excel construit notre formule RECHERCHEV pour nous:
Si nous expérimentons avec quelques montants totaux de ventes différents, nous pouvons nous assurer que la formule fonctionne.
Conclusion
Dans la version «base de données» de RECHERCHEV, où le Range_lookup le paramètre est FAUX, la valeur passée dans le premier paramètre (Lookup_value) être présent dans la base de données. En d’autres termes, nous recherchons une correspondance exacte.
Mais dans cette autre utilisation de RECHERCHEV, nous ne cherchons pas nécessairement une correspondance exacte. Dans ce cas, «assez près est assez bon». Mais qu’entend-on par «assez près»? Prenons un exemple: lors de la recherche d’un taux de commission sur un total de ventes de 34 988 $, notre formule RECHERCHEV nous renverra une valeur de 30%, ce qui est la bonne réponse. Pourquoi a-t-il choisi la ligne du tableau contenant 30%? Que signifie en fait «assez près» dans ce cas? Soyons précis:
Lorsque Range_lookup est réglé sur VRAI (ou omis), RECHERCHEV cherchera dans la colonne 1 et correspondra au Lookup_value paramètre.
Il est également important de noter que pour que ce système fonctionne,!
Si vous souhaitez vous entraîner avec RECHERCHEV, le fichier d’exemple illustré dans cet article peut être téléchargé à partir d’ici.
