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.
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.
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.
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.
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.
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.
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:
- Excel – Mise à niveau
- Excel – Modélisation financière 1
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre un vaste choix de formations Excel, notamment en tableaux de bord et en modélisation financière, ainsi qu’une série de formations en BI libre-service. 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 [email protected]
Jeu de briques
Snake
Pacman
Bubble