VLOOKUP dans Excel, partie 2 Utilisation de VLOOKUP sans base de données
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 suppose que de nombreux concepts expliqués dans cet article sont déjà connus du lecteur..
Lorsqu’on utilise des bases de données, VLOOKUP reçoit 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 ID 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’existe plus du tout 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 de 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’il perçoit sur ces ventes est de 30%. Sinon, leur commission n'est que de 20%. Jusqu'ici, il s'agit d'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 dans la cellule B2 calcule le taux de commission correct auquel il a droit, qui est utilisé dans la cellule B3 pour calculer la commission totale versée 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 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 connaissent pas IF, cela fonctionne comme suit:
SI(condition, valeur si vrai, valeur si faux)
Où le état est une expression qui évalue soit vrai ou faux. Dans l'exemple ci-dessus, le état est l'expression B1
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:
Donc, notre tableur fonctionne.
Rendons cela 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 (ça marche même!), Mais c'est plus difficile à lire et à comprendre.
Nous n'allons pas entrer dans les détails qui expliquent 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 dans 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 travail. Nous allons garder les mêmes chiffres, mais nous allons l’organiser d’une manière nouvelle. 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.
Sur le plan conceptuel, 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 peuvent avoir 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 toute la table de taux - à 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. Lorsque vous utilisez 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:
Si nous expérimentons avec différents montants totaux des ventes, nous pouvons nous assurer que la formule fonctionne.
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 une valeur de 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:
Quand Range_lookup est réglé sur VRAI (ou omis), VLOOKUP recherchera dans la colonne 1 et la plus haute valeur qui n'est pas supérieure à la Lookup_value paramètre.
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 avec VLOOKUP, vous pouvez télécharger l'exemple de fichier illustré dans cet article à partir d'ici..
