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

Excel : Travailler avec plusieurs tables de rabais de volumes

Publié le : 13 février 2017

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.

 

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.

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:

 


NOTRE OFFRE DE FORMATIONS


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, Analytique d’affaires et Finance corporative. 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. De plus, nos formations peuvent mener à l’obtention d’une accréditation.

 

Découvrez quelles formations vous conviennent

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

 

Sophie Marchand

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA, d’un titre MVP (Most valuable professional) Excel et d'un titre MVP Data Platform de Microsoft, et cumule de nombreuses années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

Laisser un commentaire