Remises commerciales variables avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Remises commerciales variables

A l'instar de la mise en pratique précédente, ce quatrième exercice Excel niveau intermédiaire permet d'exploiter les fameuses références absolues dans des conditions très spécifiques. Nous avions figé seulement l'une des références des cellules impliquées dans le calcul. De la sorte, sur la base d'une formule unique, nous avions réussi à répercuter la logique sur l'ensemble du tableau.

Source et présentation de la problématique
Pour cet exercice sur les remises commerciales, nous proposons une fois encore de récupérer un tableau offrant les données à manipuler.

• Télécharger le classeur calcul-des-remises-commerciales.xlsx en cliquant sur son lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur est constitué d'une seule feuille nommée Remises.



Son tableau liste des articles de parapharmacie en colonne B entre les lignes 7 et 15. Chacun d'entre eux possède un prix de vente public. Ils sont respectivement énumérés en colonne C pour les lignes respectives.

L'objectif consiste à concéder des prix remisés pour des revendeurs sélectionnés. Ces derniers sont identifiés en ligne 6, entre les colonnes D et H. La remise consentie est variable. Pour un grossiste, la déduction à appliquer est de 18% alors qu'elle n'est que de 8% pour un Discounter par exemple. Ces taux variables sont énumérés en ligne 4 pour les colonnes respectives.

Calcul d'un prix remisé
L'opération consistant à trouver le montant de la remise en fonction d'un taux est triviale. Elle consiste à appliquer ce taux sur le prix de vente. Il s'agit d'une simple multiplication. Pour livrer la première remise accordée pour les magasins Discount, l'opération est donc la suivante : C7*D4. Bien entendu, il est fondamental de bâtir le calcul sur les références des cellules pour produire des résultats dynamiques, capables d'évoluer si les données changent.

Cette remise doit être déduite du prix de vente. L'opération consiste en une soustraction : C7 - C7*D4. Comme la multiplication est prioritaire sur la soustraction, c'est bien le montant de la remise calculée qui est soustrait au prix de vente initial. Nous pourrions aussi factoriser ce calcul, comme nous l'avons fait dans l'exercice précédent. Sa syntaxe serait alors la suivante : C7*(1-D4). C'est à vous de choisir la méthode qui vous convient.

• Sélectionner la première cellule du prix de vente remisé à calculer, soit la cellule D7,
• Taper le symbole égal (=) pour débuter la formule,
• Cliquer sur le premier prix public pour intégrer la référence C7 dans le calcul,
• Taper le symbole moins (-) du pavé numérique pour enclencher la soustraction,
• Sélectionner de nouveau le prix public, soit la cellule C7,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Désigner alors le premier taux de remise pour intégrer la référence D4 dans la syntaxe,
• Enfin, valider le calcul à l'aide du raccourci clavier CTRL + Entrée,

Cette combinaison est précieuse pour valider une formule et conserver la cellule du calcul active. Le premier résultat tombe et il est cohérent. Le prix de vente livré est naturellement inférieur au prix de vente public.



La formule que nous avons construite est la suivante : =C7-C7*D4. Comme vous le savez, on ne refait jamais deux fois le même calcul dans Excel. Cette formule doit être répliquée sur l'ensemble du tableau afin de calculer tous les prix de vente remisés. Et c'est la poignée de la cellule qui permet de reproduire la logique d'un calcul. Cette poignée est matérialisée par le petit carré noir situé en bas à droite de la cellule D7 active. Lorsque vous pointez dessus, le curseur de la souris se transforme en une croix noire. Excel indique qu'il est prêt à répliquer le calcul. Souvenez-vous, une poignée ne peut pas être tirée en diagonale. Il faut donc décomposer l'opération sur les deux dimensions.

• Pointer sur la poignée de la cellule D7,
• Cliquer et glisser la souris à l'horizontale jusqu'en colonne H,

Tous les résultats numériques sont livrés pour cette première ligne. Ils proposent un grand nombre de décimales. Nous pallierons ce désagrément d'affichage en temps voulu avec un format adapté. A première vue, les résultats sont cohérents. Chaque montant remisé est bien inférieur au prix de vente public. Mais en y regardant de plus près, il n'en est rien. Le prix de vente concédé avec une remise de 12% est inférieur au prix de vente pour lequel la remise accordée est de 15%. Ce n'est pas logique.



Comme la poignée a déplacé le calcul pour reproduire la logique sur les autres cellules de la ligne, les références des cellules impliquées ont suivi le déplacement. Nous proposons de le constater.

• Sélectionner le dernier résultat livré, soit la cellule H7,

Comme l'indique sa barre de formule au-dessus de la feuille, le calcul initial : =C7-C7*D4 a effectivement été transformé pour suivre le déplacement imposé : =G7-G7*H4. La ligne ne change pas mais nous nous retrouvons quatre colonnes plus à droite. De fait, le prix de vente est considéré quatre rangées plus loin, soit G7 au lieu de C7. Or G7 est censée représenter le précédent prix de vente remisé. C'est la raison pour laquelle tous les résultats hormis le premier sont erronés.

Figer l'une des références
Ce prix public ne doit donc pas changer de colonne lorsque le calcul est reproduit. Comme nous l'avons appris, c'est la touche F4 du clavier qui permet de fixer une référence dans un calcul. Elle a pour effet d'encadrer les indices de la cellule avec des dollars : $C$7. Le dollar devant l'indice C de colonne empêche tout déplacement horizontal. Le dollar devant l'indice 7 de ligne empêche tout déplacement vertical. Or, comme nous l'avons compris lors du précédent exercice sur le calcul des prix de vente en fonction de marges variables, cette cellule doit être libre de se déplacer à la verticale. Pour tous les autres produits en effet, ce sont bien les prix respectifs qui doivent être considérés. Donc seule la colonne doit être figée :$C7.

Le raisonnement est similaire pour le taux de remise. Mais le degré de liberté concerné n'est pas le même. Pour que chaque taux soit respectivement appliqué, la cellule D4 doit être libre de se déplacer horizontalement. Donc sa colonne ne doit pas être figée. Mais ces taux n'existent qu'en ligne 4. Les références des cellules ne doivent donc pas descendre lorsque la formule est répliquée sur les lignes du dessous. Donc la ligne doit être figée : D$4.

Pour construire la bonne formule, nous devons commencer par éliminer les précédents résultats.

• Sélectionner l'ensemble des données calculées, soit la plage de cellules D7:H7,
• Enfoncer la touche Suppr du clavier pour supprimer les calculs,
• Cliquer alors sur le premier résultat à trouver, soit la cellule D7 pour la sélectionner,
• Taper le symbole égal (=) pour débuter l'écriture de la formule,
• Désigner le premier prix public pour intégrer la référence C7 dans la syntaxe,
• Enfoncer la touche F4 du clavier pour figer la cellule,

Deux dollars apparaissent, empêchant tout déplacement. Comme nous l'avons expliqué, ce prix public doit être libre de se déplacer à la verticale. Le dollar ne doit pas intervenir devant l'indice de ligne.

• Enfoncer de nouveau la touche F4 du clavier,

Un seul dollar persiste mais il est placé devant l'indice de ligne et non devant l'indice de colonne.

• Enfoncer une troisième fois la touche F4 du clavier,

Cette fois, l'unique dollar est placé devant l'indice de colonne ($C7) interdisant tout déplacement horizontal.

• Taper le symbole moins (-) du pavé numérique pour annoncer la soustraction,
• Désigner de nouveau le prix public, soit la cellule C7,
• Enfoncer trois fois de suite la touche F4 pour ne figer que la colonne,
• Taper le symbole de l'étoile (*) du pavé numérique pour déclencher la multiplication,
• Désigner alors le premier taux de remise pour intégrer la référence D4 dans la formule,
• Enfoncer deux fois la touche F4 pour ne figer que la ligne,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,
• Cliquer et glisser la poignée de la cellule du résultat à l'horizontale jusqu'en colonne H,

Cette fois, les résultats sont cohérents. Le prix remisé à 12% est bien supérieur au prix remisé à 15%.

• Enfin, cliquer et glisser la poignée de la sélection à la verticale jusqu'en ligne 15,

Ces deux étapes nous ont permis de répliquer la logique du calcul sur l'ensemble du tableau. De fait, nous livrons tous les prix remisés pour chacun des produits énumérés.



Nous proposons de constater que les références des cellules impliquées dans le calcul se sont adaptées comme nous l'avons ordonné.

• Double cliquer sur le tout dernier résultat en cellule H15 pour afficher sa formule,

Excel livre la syntaxe et repère les cellules dans la feuille par des jeux de couleurs. Pour ce dernier prix de vente remisé en ligne 15, le prix public considéré a bien suivi le déplacement sur la hauteur. Il s'agit de la cellule C15 pour le dernier produit sur la même ligne. Et malgré le déplacement à l'horizontale, le prix public est resté dans sa colonne : $C15. Inversement, malgré le déplacement à la verticale, le taux de remise est resté sur sa ligne. En revanche, il s'est bien déplacé à l'horizontale pour appliquer chaque taux respectif : H$4.

Cette technique avancée sur les références absolues nous a donc permis de bâtir une formule unique pour répliquer la logique du calcul sur l'ensemble du tableau. Ce procédé est précieux pour livrer des modèles dynamiques. Les calculs sont bien bâtis sur des variables. La moindre modification de prix public ou de taux engendrera la mise à jour du résultat concerné.

Nous aurions pu mettre en oeuvre une méthode professionnelle pour reproduire encore plus simplement la formule sur l'ensemble du tableau. Elle consiste à présélectionner toutes les cellules des résultats à trouver. Il s'agit ensuite de construire le calcul normalement et de le valider par le raccourci clavier CTRL + Entrée. Cette méthode professionnelle est enseignée dans le livre gratuit pour débuter avec les calculs dans Excel.

Formater les calculs
La présentation des résultats ne doit jamais être négligée. La lecture et la compréhension d'un tableau en dépendent. La précision apportée par les décimales est trop importante. Les données calculées doivent être affichées en Euro. Il s'agit simplement de leur appliquer le format monétaire.

• Cliquer sur le premier prix soit la cellule C7 pour la sélectionner,
• Tout en maintenant la touche Maj enfoncée, cliquer sur le dernier soit la cellule H15,

Cette technique que nous avons démontrée dans les exercices pour débutants permet d'intégrer dans la sélection, toutes les cellules comprises entre la première et la dernière. La touche Maj est généralement matérialisée par une flèche verticale orientée vers le haut. Elle est située au-dessus de la touche Ctrl. Sa désignation anglaise est Shift.

• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Format Nombre Comptabilité,

Instantanément, l'affichage de tous les prix est normalisé. Les données numériques sont formatées en Euro. Les décimales sont limitées à deux chiffres. La présentation est désormais homogène et cohérente. Nous aurions aussi pu exploiter le raccourci clavier CTRL + M. Dans le ruban, les deux boutons situés à l'extrémité de la section Nombre, permettent de jouer sur la précision des décimales.