Dans un article récent, nous avons introduit la fonction Excel appelée VLOOKUP et a 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 indiqué que VLOOKUP avait deux utilisations et qu'un seul d'entre elles concernait l'interrogation de 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 de VLOOKUP. Cet article supposera que de nombreux concepts déjà expliqués dans cet article sont déjà connus du lecteur.
Lorsqu’on utilise des bases de données, VLOOKUP se voit attribuer un «identifiant unique» servant à identifier l’enregistrement de données que 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 examinerons une façon d’utiliser VLOOKUP dans laquelle l’identifiant n’a pas besoin d’exister dans la base de données. C’est presque comme si VLOOKUP pouvait adopter une approche «assez juste, c'est assez bon» pour renvoyer les données que nous recherchions. Dans certaines circonstances, c'est exactement ce dont nous avons besoin.
Nous illustrerons cet article avec un exemple concret, à savoir le 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 consiste à utiliser VLOOKUP. Le scénario initial dans notre société fictive fonctionne comme suit: si un vendeur génère plus de 30 000 dollars de ventes au cours d’une année donnée, la commission qu’ils touchent sur ces ventes est de 30%. Sinon, leur commission n'est que de 20%. Jusqu'ici, c'est une feuille de calcul assez simple:
La cellule B2 contient la seule partie intéressante de cette feuille de calcul - la formule permettant de décider du taux de commission à utiliser: celui au dessous de le seuil de 30 000 $, ou celui au dessus de le seuil. Cette formule utilise la fonction Excel appelée SI. Pour les lecteurs qui ne sont pas familiers avec IF, cela fonctionne comme suit:
IF(condition,value if true,value if false)
Où le état est une expression qui évalue soit vrai ou faux. Dans l'exemple ci-dessus, le état est l'expression B1, qui peut être lu comme "Est-ce que B1 est inférieur à B5?" ou, autrement dit, "Le total des ventes est-il inférieur au seuil". Si la réponse à cette question est “oui” (vrai), alors nous utilisons le valeur si vrai paramètre de la fonction, à savoir B6 dans ce cas - le taux de commission si le total des ventes était au dessous de le seuil. Si la réponse à la question est “non” (faux), nous utilisons le valeur si faux paramètre de la fonction, à savoir B7 dans ce cas - le taux de commission si le total des ventes était au dessus de le seuil.
Comme vous pouvez le constater, l’utilisation d’un total de ventes de 20 000 USD nous donne un taux de commission de 20% dans la cellule B2. Si nous entrons une valeur de 40 000 $, nous obtenons un taux de commission différent:
Rendons-le plus complexe. Introduisons un deuxième seuil: si le vendeur gagne plus de 40 000 USD, 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 SI d'origine (le valeur si faux) est maintenant une fonction SI entière à part entière. Ceci s'appelle un fonction imbriquée (une fonction dans une fonction). C’est parfaitement valable dans Excel (cela fonctionne même!), Mais il est plus difficile à lire et à comprendre.
Nous n'allons pas entrer dans les détails et expliquer comment et pourquoi cela fonctionne, ni examiner les nuances des fonctions imbriquées. Ceci est un tutoriel sur VLOOKUP, pas sur Excel en général.
En tout cas, ç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 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. VLOOKUP à la rescousse!
Modifions un peu la feuille de calcul. Nous garderons tous les mêmes chiffres, mais nous l’organiserons de manière plus tabulaire façon:
Prenez un moment et vérifiez par vous-même que le nouveau Table de taux fonctionne exactement comme la série de seuils ci-dessus.
En principe, nous allons utiliser VLOOKUP pour rechercher le total des ventes du vendeur (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 qui sont ne pas une des cinq valeurs du tableau 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 $ ne pas apparaissent dans la table de taux. Voyons si VLOOKUP peut résoudre notre problème de toute façon…
Nous sélectionnons la cellule B2 (le lieu où nous voulons placer notre formule), puis insérons la fonction VLOOKUP à partir de Formules languette:
le Arguments de fonction La boîte de dialogue VLOOKUP apparaît. Nous complétons les arguments (paramètres) un à 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 puis cliquez une fois sur la cellule B1:
Ensuite, nous devons spécifier à VLOOKUP la table dans laquelle rechercher ces données. Dans cet exemple, il s’agit bien de la table de taux. Nous plaçons le curseur dans le Tableau_Array champ, puis mettez en surbrillance la table de taux complète - à l'exclusion des rubriques:
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, nous allons donc entrer un 2 dans le Col_index_num champ:
Enfin nous entrons une valeur dans le Range_lookup champ.
Important: c’est l’utilisation de ce champ qui différencie les deux manières d’utiliser VLOOKUP. Pour utiliser VLOOKUP avec une base de données, ce dernier paramètre, Range_lookup, doit toujours être réglé sur FAUX, mais avec cette autre utilisation de VLOOKUP, nous devons soit laisser vide ou entrer une valeur de VRAI. Lors de l'utilisation de VLOOKUP, il est essentiel de faire le bon choix pour ce paramètre final.
Pour être explicite, nous allons entrer une valeur de vrai dans le Range_lookup champ. Il serait également bon de laisser ce champ 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 bouton, et Excel construit notre formule VLOOKUP pour nous:
Conclusion
Dans la version «base de données» de VLOOKUP, où le Range_lookup paramètre est FAUX, la valeur passée dans le premier paramètre (Lookup_value) doit être présent dans la base de données. En d’autres termes, nous recherchons une correspondance exacte.
Mais dans cette autre utilisation de VLOOKUP, nous ne cherchons pas nécessairement une correspondance exacte. Dans ce cas, "assez proche est assez bon". Mais qu'entendons-nous par «assez proche»? Prenons un exemple: lorsque vous recherchez un taux de commission sur un total de ventes de 34 988 $, notre formule VLOOKUP nous renvoie 30%, ce qui est la réponse correcte. Pourquoi a-t-il choisi la ligne du tableau contenant 30%? Que signifie en fait «assez proche» dans ce cas? Soyons précis:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Il est également important de noter que pour que ce système fonctionne, la table doit être triée par ordre croissant sur la colonne 1!
Si vous souhaitez vous exercer à VLOOKUP, vous pouvez télécharger l'exemple de fichier illustré dans cet article à partir d'ici.
