1Aug
Dans un article récent, nous avons présenté la fonction Excel appelée VLOOKUP et expliqué comment il pourrait être utilisé pour extraire 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 pour VLOOKUP, et une seule d'entre elles traitait de l'interrogation des bases de données. Dans cet article, le deuxième et dernier de la série VLOOKUP, nous examinons cet autre usage, moins connu, de la fonction VLOOKUP.
Si vous ne l'avez pas déjà fait, veuillez lire le premier article VLOOKUP - cet article supposera que bon nombre 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 quel enregistrement de données nous souhaitons trouver dans la base de données( par exemple un code de produit ou un identifiant client).Cet identifiant unique doit existe dans la base de données, sinon VLOOKUP nous renvoie une erreur. Dans cet article, nous allons examiner une façon d'utiliser VLOOKUP où l'identificateur n'a pas du tout besoin d'exister dans la base de données. C'est presque comme si VLOOKUP pouvait adopter une approche «assez proche de la réalité» pour retourner les données que nous recherchons. Dans certaines circonstances, c'est
exactement ce dont nous avons besoin.Nous illustrerons cet article par un exemple concret, celui du calcul des commissions générées sur un ensemble de chiffres de ventes. 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 VLOOKUP.Le scénario initial dans notre société fictive fonctionne comme ceci: Si un vendeur crée plus de 30 000 $ de ventes dans une année donnée, la commission qu'ils gagnent sur ces ventes est de 30%.Sinon, leur commission n'est que de 20%.Pour l'instant, il s'agit d'une feuille de calcul assez simple:
Pour utiliser cette feuille de calcul, le commercial entre ses chiffres de ventes dans la cellule B1 et la formule dans la cellule B2 calcule le bon taux de commission qu'elle a le droit de recevoir.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 quel taux de commission utiliser: l'un en dessous d' le seuil de 30 000 $, ou l' au dessus d' le seuil. Cette formule utilise la fonction Excel appelée IF .Pour les lecteurs qui ne sont pas familiers avec IF, cela fonctionne comme ceci:
IF( condition , valeur si vrai, valeur si faux )
Où la condition est une expression qui évalue true ou false .Dans l'exemple ci-dessus, la condition est l'expression B1
Comme vous pouvez le voir, l'utilisation d'un total des ventes de 20 000 $ nous donne un taux de commission de 20% dans la cellule B2.Si nous saisissons une valeur de 40 000 $, nous obtenons un taux de commission différent:
Donc, notre feuille de calcul fonctionne.
Faisons en sorte que ce soit plus complexe. Introduisons un deuxième seuil: Si le vendeur gagne plus de 40 000 $, alors 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 de près la formule, vous verrez que le troisième paramètre de la fonction IF d'origine( la valeur si false ) est maintenant une fonction IF entière en elle-même. C'est ce qu'on appelle une fonction imbriquée( une fonction dans une fonction).C'est parfaitement valide dans Excel( ça marche même!), Mais c'est plus difficile à lire et à comprendre.
Nous n'allons pas aller dans les rouages de comment et pourquoi cela fonctionne, ni nous allons examiner les nuances de fonctions imbriquées. Ceci est un tutoriel sur VLOOKUP, pas sur Excel en général.
De toute façon, ça devient pire! Qu'en est-il lorsque nous décidons que s'ils gagnent plus de 50 000 $, ils ont droit à une commission de 50%, et s'ils gagnent plus de 60 000 $, ils ont droit à une commission de 60%?
Maintenant la formule dans la cellule B2, bien que correcte, est devenue pratiquement illisible. Personne ne devrait avoir à écrire des formules où les fonctions sont imbriquées à quatre niveaux de profondeur! Sûrement il doit y avoir un moyen plus simple?
Il est certainement. VLOOKUP à la rescousse!
Redessinons un peu la feuille de calcul. Nous garderons tous les mêmes chiffres, mais organisez-le d'une manière plus tabulaire :
Prenez un moment et vérifiez par vous-même que la nouvelle table de taux fonctionne exactement comme la série de seuils ci-dessus.
Conceptuellement, ce que nous allons faire est d'utiliser VLOOKUP pour rechercher le total des ventes du vendeur( à partir de B1) dans la table des taux et nous retourner le taux de commission correspondant. Notez que le vendeur a peut-être créé des ventes qui sont et non l'une des cinq valeurs de la table des taux( 0 $, 30 000 $, 40 000 $, 50 000 $ ou 60 000 $).Ils ont peut-être créé des ventes de 34 988 $.Il est important de noter que 34 988 $ pas apparaissent dans la table des taux. Voyons si VLOOKUP peut résoudre notre problème de toute façon. ..
Nous sélectionnons la cellule B2( l'emplacement où nous voulons placer notre formule), puis insérons la fonction VLOOKUP depuis l'onglet :
La boîte de dialogue Function Arguments pour VLOOKUP apparaît. Nous remplissons les arguments( paramètres) un par un, en commençant par Lookup_value , qui est, dans ce cas, le total des ventes de la cellule B1.Nous plaçons le curseur dans le champ Lookup_value , puis une fois sur la cellule B1:
Ensuite, nous devons spécifier à VLOOKUP quelle table rechercher dans cette donnée. Dans cet exemple, c'est la table des taux, bien sûr. Nous plaçons le curseur dans le champ Table_array , puis soulignons la table de taux entier - en excluant les en-têtes :
Ensuite, nous devons spécifier quelle colonne de la table 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, donc nous entrons un 2 dans le champ Col_index_num :
Enfin, nous entrons une valeur dans le champ Range_lookup.
Important: C'est l'utilisation de ce champ qui différencie les deux façons d'utiliser VLOOKUP.Pour utiliser VLOOKUP avec une base de données, ce dernier paramètre, Range_lookup , doit toujours être défini sur FALSE , mais avec cette autre utilisation de VLOOKUP, nous devons soit laisser vide, soit entrer une valeur de TRUE .Lorsque vous utilisez VLOOKUP, il est essentiel que vous fassiez le bon choix pour ce dernier paramètre.
Pour être explicite, nous entrerons une valeur de true dans le champ Range_lookup .Il serait également bien de laisser vide, car il s'agit de la valeur par défaut:
Nous avons terminé tous les paramètres. Nous cliquons maintenant sur le bouton OK , et Excel construit notre formule VLOOKUP 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 "database" de VLOOKUP, où le paramètre Range_lookup est FALSE , la valeur transmise dans le premier paramètre( Lookup_value ) doit être présente dans la base de données. En d'autres termes, nous recherchons une correspondance exacte.
Mais dans cette autre utilisation de VLOOKUP, nous ne recherchons pas forcément une correspondance exacte. Dans ce cas, "assez près est assez bon".Mais qu'entend-on par "assez proche"?Prenons un exemple: Lorsque vous recherchez un taux de commission sur un total de 34 988 $, notre formule VLOOKUP nous renvoie une valeur de 30%, ce qui est la bonne réponse. Pourquoi a-t-il choisi la ligne de la table contenant 30%?Que signifie, en effet, «assez proche» dans ce cas? Soyons précis:
Lorsque Range_lookup est défini sur TRUE ( ou omis), VLOOKUP recherche dans la colonne 1 et correspond à la valeur la plus élevée qui n'est pas supérieure à le paramètre Lookup_value .
Il est également important de noter que pour que ce système fonctionne, la table doit être triée dans l'ordre croissant sur la colonne 1 !
Si vous souhaitez vous exercer avec VLOOKUP, le fichier exemple illustré dans cet article peut être téléchargé ici.