Contact: 514-605-7112 / info@lecfomasque.com

Excel : Quand la fonction Lookup (Recherche) vient en renfort!

Publié le : 1 décembre 2014

Si vous travaillez souvent avec Excel, vous utilisez sans aucun doute les fonctions Vlookup (Recherchev) et Hlookup (Rechercheh) et probablement la fonction Index, combinée avec la fonction Match (Equiv). Mais utilisez-vous la fonction Lookup (Recherche)? Non? Pourtant, cette fonction pourrait vous être très utile dans certains contextes, comme celui illustré ci-bas.

 

Problématique

Supposons que vous avez une table de commissions. Cette table vous informe des taux de commissions à verser à vos représentants des ventes. La problématique, c’est que les taux évoluent au fil du temps et ne sont pas les mêmes, d’un représentant à un autre et d’un client à un autre, tel qu’illustré ci-bas.

Lookup / Recherche

 

À partir de cette table des commissions, vous devez insérer une formule dans votre table des ventes, pour calculer le taux de commissions approprié, pour chacune des transactions de vente. Vous devez donc utiliser une formule qui fournira le bon taux de commission, selon le représentant, le client et la date de la transaction.

Lookup / Recherche

 

Fonction Lookup (Recherche)

Dans ce contexte particulier, vous pourriez utiliser la fonction suivante:

Lookup / Recherche

 

Interprétation de la fonction

Lookup / Recherche

Concentrons-nous d’abord sur le deuxième paramètre de la fonction, soit le « lookup_vector ».

La formule divise 1 par une plage de données qui renvoie des valeurs True/False (Vrai/Faux):

  • Dans notre formule, la fonction vérifie si les données du champs « Date » de la table « Commissions » sont plus petites ou égales à la date de la transaction de vente
  • À la ligne 8 de la table de « Ventes », ce vecteur renvoie True (Vrai) pour les 6 premières dates de la table des commissions et False (Faux) pour les 4 dernières

puis, la formule divise ensuite le résultat par une autre plage de données qui renvoie des valeurs True/False (Vrai/Faux):

  • Dans notre formule, la fonction vérifie dans la plage de clients de la table « Commissions » les clients qui correspondent au client de la transaction
  • À la ligne 8 de la table de « Ventes », ce vecteur renvoie True (Vrai) pour les Client2 et False (Faux) pour les Client1

puis, la formule divise à nouveau par une autre plage de données qui renvoie des valeurs True/False (Vrai/Faux)

  • Dans notre formule, la fonction vérifie dans la plage de représentants de la table « Commissions » les représentants qui correspondent au représentant de la transaction
  • À la ligne 8 de la table de « Ventes », ce vecteur renvoie True (Vrai) pour les Rep1 et False (Faux) pour les Rep2

Autrement dit, pour la 1ère ligne de transactions de vente, la table de commissions retournait les résultats suivants, avec une seule ligne comprenant 3 valeurs True (Vrai), soit celle avec le 11% de commission.

Lookup / Recherche

Mais à quoi servent les 2 autres paramètres de la fonction?

D’abord, concernant le 2ième paramètre, il faut comprendre que les résultats possibles sont 1 ou #DIV/0!. Dans notre exemple, une seule réponse renvoie un 1 et il s’agit de la ligne avec les 3 valeurs True (Vrai). Cette ligne représente donc le vecteur dans lequel on va chercher notre résultat.

Le 3ième paramètre nous indique simplement que dans ce vecteur de résultats, on va aller chercher la valeur « Taux » de commission.

Finalement, en utilisant le 2, comme « lookup value », la formule va tenter de le faire correspondre avec la dernière valeur numérique de la plage de référence, i.e., la dernière ligne où les trois conditions sont vraies. On utilise ici le 2, mais ça pourrait être n’importe quelle valeur plus grande que 1. Évidemment, si l’équation ne peut être résolue, on obtiendra un #N/A.

 


NOTRE OFFRE DE FORMATIONS


Nos formations sont éligibles à la subvention PACME du Québec

et notre entreprise est datadockée en France

Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire