Table des matières
Nous avons récemment consacré un article à l'une des fonctions Excel les plus utiles appelées VPR et a montré comment il peut être utilisé pour extraire les informations requises d'une base de données dans une cellule de feuille de calcul. Nous avons également mentionné qu'il existe deux cas d'utilisation pour la fonction VPR et un seul d'entre eux traite des requêtes de base de données. Dans cet article, vous apprendrez une autre façon moins connue d'utiliser la fonction VPR dans Excel.
Si vous ne l'avez pas encore fait, assurez-vous de lire le dernier article sur la fonction VPR, car toutes les informations ci-dessous supposent que vous connaissiez déjà les principes décrits dans le premier article.
Lorsque vous travaillez avec des bases de données, les fonctions VPR un identifiant unique est passé, qui est utilisé pour identifier les informations que nous voulons trouver (par exemple, un code produit ou un numéro d'identification client). Ce code unique doit être présent dans la base de données, sinon VPR signalera une erreur. Dans cet article, nous verrons cette façon d'utiliser la fonction VPRlorsque l'identifiant n'existe pas du tout dans la base de données. Comme si la fonction VPR est passé en mode approximatif et choisit les données à nous fournir lorsque nous voulons trouver quelque chose. Dans certaines circonstances, c'est exactement ce qu'il faut.
Un exemple tiré de la vie. Nous fixons la tâche
Illustrons cet article avec un exemple concret : le calcul des commissions en fonction d'un large éventail de mesures de vente. Nous commencerons par une option très simple, puis nous la compliquerons progressivement jusqu'à ce que la seule solution rationnelle au problème soit d'utiliser la fonction VPR. Le scénario initial de notre tâche fictive est le suivant : si un vendeur réalise plus de 30000 30 $ de ventes par an, sa commission est de 20 %. Sinon, la commission n'est que de XNUMX%. Mettons-le sous la forme d'un tableau :
Le vendeur saisit ses données de vente dans la cellule B1 et la formule de la cellule B2 détermine le taux de commission correct auquel le vendeur peut s'attendre. À son tour, le taux résultant est utilisé dans la cellule B3 pour calculer la commission totale que le vendeur devrait recevoir (en multipliant simplement les cellules B1 et B2).
La partie la plus intéressante du tableau est contenue dans la cellule B2 - c'est la formule pour déterminer le taux de commission. Cette formule contient une fonction Excel appelée IF (SI). Pour les lecteurs qui ne connaissent pas cette fonction, je vais expliquer comment cela fonctionne :
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
État est un argument de fonction qui prend la valeur soit VRAI CODE (Vrai ou FAUX (FAUX). Dans l'exemple ci-dessus, l'expression B1
Est-il vrai que B1 est inférieur à B5 ?
Ou vous pouvez le dire différemment:
Est-il vrai que le montant total des ventes pour l'année est inférieur à la valeur seuil ?
Si nous répondons à cette question OUI (TRUE), alors la fonction renvoie valeur si vrai (valeur si VRAI). Dans notre cas, ce sera la valeur de la cellule B6, c'est-à-dire le taux de commission lorsque le total des ventes est inférieur au seuil. Si nous répondons à la question NON (FAUX) puis renvoie valeur si faux (valeur si FALSE). Dans notre cas, il s'agit de la valeur de la cellule B7, c'est-à-dire du taux de commission lorsque le total des ventes dépasse le seuil.
Comme vous pouvez le voir, si nous prenons les ventes totales de 20000 2 $, nous obtenons un taux de commission de 20 % dans la cellule B40000. Si nous saisissons une valeur de 30 XNUMX $, le taux de commission changera de XNUMX % :
C'est ainsi que fonctionne notre tableau.
Nous compliquons la tâche
Rendons les choses un peu plus difficiles. Fixons un autre seuil : si le vendeur gagne plus de 40000 40 $, alors le taux de commission passe à XNUMX % :
Tout semble être simple et clair, mais notre formule dans la cellule B2 devient sensiblement plus compliquée. Si vous regardez attentivement la formule, vous verrez que le troisième argument de la fonction IF (SI) transformé en une autre fonction à part entière IF (SI). Cette construction est appelée imbrication des fonctions les unes dans les autres. Excel autorise volontiers ces constructions, et elles fonctionnent même, mais elles sont beaucoup plus difficiles à lire et à comprendre.
Nous n'entrerons pas dans les détails techniques - pourquoi et comment cela fonctionne, et nous n'entrerons pas dans les nuances de l'écriture des fonctions imbriquées. Après tout, ceci est un article dédié à la fonction VPR, pas un guide complet d'Excel.
Quoi qu'il en soit, la formule se complique ! Et si nous introduisions une autre option pour un taux de commission de 50 % pour les vendeurs qui réalisent plus de 50000 60000 $ de ventes ? Et si quelqu'un a vendu plus de 60 XNUMX $, paiera-t-il une commission de XNUMX % ?
Maintenant, la formule dans la cellule B2, même si elle a été écrite sans erreur, est devenue complètement illisible. Je pense qu'il y en a peu qui veulent utiliser des formules à 4 niveaux d'imbrication dans leurs projets. Il doit y avoir un moyen plus simple ? !
Et il y a un tel moyen! La fonction nous aidera VPR.
Nous appliquons la fonction VLOOKUP pour résoudre le problème
Modifions un peu le design de notre table. Nous conserverons tous les mêmes champs et données, mais les organiserons d'une nouvelle manière plus compacte :
Prenez un moment et assurez-vous que la nouvelle table Tableau des tarifs inclut les mêmes données que le tableau des seuils précédent.
L'idée principale est d'utiliser la fonction VPR pour déterminer le taux tarifaire souhaité selon le tableau Tableau des tarifs en fonction du volume des ventes. Veuillez noter que le vendeur peut vendre des biens pour un montant qui n'est pas égal à l'un des cinq seuils du tableau. Par exemple, il pourrait vendre pour 34988 XNUMX $, mais ce montant n'existe pas. Voyons comment la fonction VPR peut faire face à une telle situation.
Insertion d'une fonction VLOOKUP
Sélectionnez la cellule B2 (où nous voulons insérer notre formule) et trouvez RECHERCHEV (RECHERCHEV) dans la bibliothèque de fonctions Excel : Formules (formules) > Bibliothèque de fonctions (Bibliothèque de fonctions) > Recherche et référence (Références et tableaux).
Une boîte de dialogue apparaît Arguments de fonction (Arguments de la fonction). Nous remplissons les valeurs des arguments un par un, en commençant par Valeur_de recherche (Recherche_valeur). Dans cet exemple, il s'agit du montant total des ventes de la cellule B1. Placer le curseur dans le champ Valeur_de recherche (Lookup_value) et sélectionnez la cellule B1.
Ensuite, vous devez spécifier les fonctions VPRoù chercher les données. Dans notre exemple, il s'agit d'une table Tableau des tarifs. Placer le curseur dans le champ Tableau_tableau (Tableau) et sélectionnez l'ensemble du tableau Tableau des tarifssauf pour les en-têtes.
Ensuite, nous devons spécifier de quelle colonne extraire les données à l'aide de notre formule. Nous nous intéressons au taux de commission, qui se trouve dans la deuxième colonne du tableau. Par conséquent, pour l'argument Col_index_num (Column_number) saisissez la valeur 2.
Et enfin, nous introduisons le dernier argument - Range_lookup (Intervalle_lookup).
Important: c'est l'utilisation de cet argument qui fait la différence entre les deux manières d'appliquer la fonction VPR. Lorsque vous travaillez avec des bases de données, l'argument Range_lookup (range_lookup) doit toujours avoir une valeur FAUX (FALSE) pour rechercher une correspondance exacte. Dans notre utilisation de la fonction VPR, nous devons laisser ce champ vide ou entrer une valeur VRAI CODE (VRAI). Il est extrêmement important de choisir cette option correctement.
Pour le rendre plus clair, nous présenterons VRAI CODE (VRAI) dans le champ Range_lookup (Interval_lookup). Cependant, si vous laissez le champ vide, ce ne sera pas une erreur, puisque VRAI CODE est sa valeur par défaut :
Nous avons rempli tous les paramètres. Maintenant, nous appuyons OK, et Excel crée une formule pour nous avec une fonction VPR.
Si nous expérimentons plusieurs valeurs différentes pour le montant total des ventes, nous nous assurerons que la formule fonctionne correctement.
Conclusion
Lorsque la fonction VPR fonctionne avec des bases de données, argument Range_lookup (range_lookup) doit accepter FAUX (FAUX). Et la valeur entrée comme Valeur_de recherche (Lookup_value) doit exister dans la base de données. En d'autres termes, il recherche une correspondance exacte.
Dans l'exemple que nous avons examiné dans cet article, il n'est pas nécessaire d'obtenir une correspondance exacte. C'est le cas lorsque la fonction VPR doit passer en mode approximatif pour retourner le résultat souhaité.
Par exemple : Nous voulons déterminer le taux à utiliser dans le calcul de la commission pour un vendeur avec un volume de ventes de 34988 XNUMX $. Fonction VPR nous renvoie une valeur de 30%, ce qui est tout à fait correct. Mais pourquoi la formule a-t-elle sélectionné la ligne contenant exactement 30 % et non 20 % ou 40 % ? Qu'entend-on par recherche approximative ? Soyons clairs.
Lorsque l'argument Range_lookup (interval_lookup) a une valeur VRAI CODE (VRAI) ou omis, fonction VPR parcourt la première colonne et sélectionne la plus grande valeur qui ne dépasse pas la valeur de recherche.
Point important: Pour que ce schéma fonctionne, la première colonne du tableau doit être triée par ordre croissant.