Excel : Travailler avec plusieurs tables de rabais de volumes

Publié le 13 février 2017
par Sophie Marchand M.Sc.
Rabais volumes

Il y a quelques jours, Ian a posé une question sur notre forum. Il se demandait quelle fonction Excel utiliser pour calculer des rabais de volumes basés sur plusieurs matrices de rabais. La principale difficulté, dans le cas que Ian a soumis, c’est que chacune des matrices présente des rabais avec des échelles de volumes à des granularités très différentes. Cet article vise à répondre à la question de Ian en utilisant une fonction CHOISIR imbriquée dans une fonction RECHERCHEV.

 

Question concernant le calcul de rabais de volumes

Voici la question que Ian a posée sur notre forum. Vous pouvez récupérer son fichier en vous rendant sur notre forum.Forum Ian Marcotte

 

Fonction CHOISIR imbriquée dans une fonction RECHERCHEV

Ci-dessous, j’ai inséré les deux tables de rabais du fichier de Ian. À noter que nous aurions pu ajouter plusieurs autres tables et la procédure demeurerait la même.

Rabais volumes

 

Chacune des tables porte un nom. La table Échelle1 a été nommée Échelle1A et la table Échelle2 a été nommée Échelle2A. Pour savoir comment nommer une table, vous pouvez relire l’article Excel: Aller plus loin avec les champs nommés.

Plage de cellules nommées

 

L’usager du fichier doit choisir un volume de ventes et une échelle de rabais et notre formule doit renvoyer le rabais correspondant. Dans notre fonction, nous utiliserons aussi le contenu de la cellule M7, qui elle, se réfère au contenu des cellules R7 et R8.

Fonction equiv

 

La cellule M7 contient une fonction EQUIV (MATCH) qui permet de retourner la position de l’échelle de rabais sélectionnée en L7, dans la plage de cellule R7 à R8. Ainsi, la valeur Échelle1A en L7 retournera la valeur 1 (puisque c’est la première valeur dans la page de cellules R7 à R8), la valeur Échelle2A en L7 retournera la valeur 2 et ainsi de suite.

Fonction equiv

 

Voici la fonction que je suggère pour calculer le rabais. À noter que CHOOSE se traduit par CHOISIR en français et VLOOKUP par RECHERCHEV.

 

Fonction CHOISIR imbriquée dans une fonction RECHERCHEV

VLOOKUP AND CHOOSE

 

D’abord, la fonction CHOISIR (CHOOSE) permet de rapporter  Échelle1A ou Échelle2A, selon ce que l’usager a sélectionné en L7 (ce qui aura modifié la valeur de la cellule M7 en conséquence). En effet, la cellule M7 rapporte 1 ou 2 (dans le cas ci-dessus, elle rapporte 2, grâce au choix effectué par l’usager en L7) et s’arrête dans ce cas, à la deuxième valeur de l’énumération qui suit, i.e Échelle2A. Nous aurions pu remplacer la fonction CHOISIR (CHOOSE) par la fonction INDIRECT (cela aurait été ironiquement… plus direct :)). Nous aurions obtenu =VLOOKUP(L6,INDIRECT(L7),3,TRUE). Toutefois, comme INDIRECT est une fonction volatile, quand nous pouvons la remplacer par une autre fonction, il est préférable de le faire, à moins que le fichier Excel ne soit pas très complexe et pas très volumineux.

 

Ensuite, la fonction RECHERCHEV (VLOOKUP) permet de chercher la valeur spécifiée en L6 (i.e. le volume de vente) dans la table Échelle1A ou Échelle2A (selon ce que la fonction CHOISIR (CHOOSE) aura rapporté) et retourne la valeur de la troisième colonne, i.e. le % de rabais. À noter que le dernier paramètre est un paramètre TRUE(ou VRAI). Ce faisant, dans l’exemple illustré ci-dessus, Excel cherche la valeur 545,823.49$ dans la table Échelle2A, ne trouve pas la valeur et s’arrête donc sur la valeur la plus proche mais inférieure, qui est 500,000$ et retourne donc la valeur 8.40%. Pour bien comprendre le VRAI dans la fonction RECHERCHEV, vous pouvez aussi relire l’article Excel : Quand mettre un vrai dans un recherchev?

 

Vous aimeriez pouvoir régler ce genre de problématique plus aisément?

Nous vous conseillons de suivre nos formations en ligne (à votre rythme) ou en classe:

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut