Marges variables et prix de vente 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 :


Marges variables et prix de vente

Ce troisième exercice Excel niveau intermédiaire est l'occasion d'améliorer la maîtrise des références absolues. Comme nous l'avons appris dans les mises en situation précédentes, elles permettent de figer des cellules dans un calcul. En effet, dans des contextes particuliers, certaines références ne doivent pas suivre le déplacement de la formule lors de la réplication.

Source et présentation de la problématique
Comme toujours, nous proposons de débuter les manipulations depuis un classeur préconçu.

• Télécharger le classeur simulation-marges-ventes.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur n'est constitué que d'une seule feuille nommée Simulation. Elle offre la structure du tableau de simulation des marges sur les ventes. Des articles sont énumérés en colonne B entre les lignes 7 et 12. Pour chacun et donc pour chaque ligne, le prix d'achat est inscrit en colonne C.

Entre les colonnes D et H, il convient de calculer le prix de vente pour chaque article en tenant compte d'une marge variable. Ces marges sont inscrites en ligne 4 entre les colonnes D et H.

Comme nous le répétons à chaque occasion, on ne refait jamais deux fois le même calcul dans Excel. L'enjeu consiste donc à poser l'unique formule en cellule D7 et à la répliquer sur l'ensemble du tableau. Et nous allons le voir, il va s'agir d'exploiter les subtilités des références absolues.

Calcul du prix de vente
Prenons pour exemple le tout premier résultat à livrer en cellule D7. La marge correspond au pourcentage en D4 à appliquer au prix d'achat en C7. Le calcul de la marge est donc le suivant : C7*D4. Et cette marge doit être ajoutée au prix d'origine, ce qui conduit à la formule suivante : C7 + C7*D4. Pour des raisons de clarté, nous proposons de factoriser ce calcul.

• Sélectionner la toute première cellule à calculer soit la cellule D7,
• Taper le symbole égal (=) pour débuter la formule,
• Cliquer sur le premier prix d'achat pour intégrer sa référence C7 dans le calcul,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Ouvrir la parenthèse pour la factorisation,
• Taper le chiffre 1 suivi du symbole plus (+), soit : 1+,
• Cliquer sur le premier pourcentage pour intégrer sa référence D4 dans la formule,
• Fermer la parenthèse de la factorisation,
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,

Comme vous le savez désormais, cette combinaison de touches permet de valider une formule tout en conservant la cellule active. L'objectif est de l'exploiter dans la foulée sans devoir la resélectionner préalablement.



La formule que nous avons bâtie est donc la suivante : =C7*(1+D4). Décomposée, elle revient bien au calcul que nous avions premièrement proposé : C7 + C7*D4. Le prix de vente est bien supérieur de 50% au prix de revient.

Cette logique doit désormais être répercutée sur l'ensemble du tableau afin de livrer tous les résultats. Chaque marge doit être appliquée sur chaque prix d'achat. Comme vous le savez, c'est la poignée d'une cellule qui permet de reproduire une formule en adaptant les références des cellules impliquées. Mais une poignée ne peut pas être tirée sur la diagonale. Dans un premier temps, nous proposons donc de livrer les résultats pour la première ligne. La poignée est matérialisée par le petit carré placé en bas à droite de la cellule active.

• Pointer sur la poignée de la cellule D7 sélectionnée,

Le curseur de la souris se transforme en une croix noire.

• Cliquer et glisser cette poignée à l'horizontale jusqu'en colonne H,

Nous obtenons tous les résultats numériques pour la première ligne de calcul, soit la ligne 7. Mais ces valeurs ne semblent pas cohérentes. Il n'est pas possible de proposer un prix de vente à 1417 Euros pour un produit acheté seulement à 18 Euros. C'est pourtant bien le résultat livré en cellule H7, la faute aux références relatives.

En effet, en l'absence d'indication contraire, les références des cellules impliquées dans la formule suivent le déplacement imposé par la poignée.

• Cliquer sur la cellule H7 pour la sélectionner,

Cette anomalie surgit en consultant la barre de formule de la cellule active. Le calcul d'origine : =C7*(1+D4), s'est transformé selon la syntaxe suivante : =G7*(1+H4). Pour la même ligne, la formule s'est déplacée quatre colonnes plus loin. Les références des cellules impliquées ont suivi ce mouvement. Excel ne considère plus la marge de 50% en D4 mais la marge de 250% quatre colonnes plus loin en H4. Ce raisonnement est parfait. C'est exactement ce que nous souhaitions. En revanche, pour le prix d'achat, ce n'est plus la cellule C7 qui est considérée mais la cellule G7, située quatre rangées plus à droite. Or G7 correspond au calcul du précédent prix de vente. Le prix d'achat est toujours situé en colonne C. Donc, nous devons indiquer à Excel de ne pas déplacer sa cellule lorsque la formule est répliquée.

Figer une cellule dans un calcul
Nous l'avons compris dans les précédents exercices, c'est la touche F4 du clavier qui permet de figer les références d'une cellule désignée dans un calcul. Cette touche doit être enfoncée juste après avoir cliqué sur la cellule à intégrer. Elle a pour effet d'encadrer ses références avec des dollars. Mais il est aussi possible d'agir a posteriori.

• Double cliquer sur la cellule D7 pour activer sa modification,
• Cliquer ensuite entre la lettre C et le chiffre 7 pour désigner la cellule C7 dans la syntaxe,
• Enfoncer alors la touche F4 du clavier pour la figer dans le calcul,

Deux dollars apparaissent. Le premier se positionne devant l'indice C de colonne. Il indique que la cellule n'a pas l'autorisation de se déplacer à l'horizontale. Le second se place devant l'indice 7 de ligne. Il indique que la cellule n'a pas le droit de se déplacer à la verticale. La formule est désormais la suivante : =$C$7*(1+D4).

• Valider cette modification par le raccourci clavier CTRL + Entrée,
• Puis, tirer de nouveau la poignée à l'horizontale jusqu'en colonne H,

Nous obtenons ainsi tous les prix de vente pour la première ligne, soit pour le premier article. Et cette fois les résultats sont cohérents.

• Cliquer sur le dernier résultat, soit la cellule H7 pour l'activer,

En consultant la barre de formule, nous constatons comme précédemment que le pourcentage de la marge a bien suivi le déplacement imposé par la poignée. Tandis que le premier prix de vente est calculé en tenant compte d'une marge à 50%, le dernier est calé sur la marge à 250%, quatre colonnes plus loin. Et contrairement au constat précédent, cette fois le prix d'achat de l'article reste figé dans sa cellule. Quel que soit le calcul, c'est toujours le prix de revient en C7 qui est désormais considéré. Avec le déplacement, le prix unique de l'article est multiplié avec chacune des marges en colonne respective : =$C$7*(1+H4).



Il est temps de répliquer cette logique sur l'ensemble du tableau pour produire les prix de vente de tous les articles en considérant chacune des marges. Nous l'avions appris, il est possible de reproduire plusieurs calculs en même temps.

• Sélectionner tous les résultats, soit la plage de cellules D7:H7,

La poignée est désormais située en bas à droite de la sélection, soit en bas à droite de la dernière cellule H7.

• Cliquer et glisser cette poignée à la verticale jusqu'en ligne 12,

Malgré la correction apportée par les références absolues, nous obtenons une série de résultats complètement farfelus. Il est même à noter la présence d'erreurs de calculs. Et pourtant, ce phénomène s'explique fort logiquement.

• Double cliquer sur le tout dernier résultat, soit la cellule H12,

Nous visualisons ainsi son contenu en lieu et place. Les cellules impliquées dans le calcul s'affichent explicitement par leurs références. De plus, Excel déclenche un jeu de couleur pour mieux les repérer dans le tableau.



Ce prix de vente doit considérer la dernière marge placée en H4. Mais comme nous avons reproduit la logique sur les lignes du dessous, la cellule concernée a suivi le mouvement. Contrairement au prix d'achat de l'article, nous ne l'avons pas figée. Mais ce n'est pas tout, le prix d'achat considéré n'est pas correct. Pour ce dernier résultat, c'est la cellule C12 qui doit être impliquée et non la cellule C7. Comme nous l'avions figée, elle a suivi les indications et ne s'est pas déplacée. Nous allons comprendre que les deux degrés de liberté d'une cellule (ligne et colonne) peuvent se manipuler indépendamment.

Degrés de liberté d'une cellule
Cette notion est absolument fondamentale pour profiter de la pleine puissance d'Excel. Nous devons apporter la solution permettant de calculer l'ensemble des données du tableau sur la base d'une seule formule. Il n'est pas acceptable d'envisager de réécrire la syntaxe sur chaque ligne ou colonne pour pallier les défauts de déplacement.

Comme nous l'avons constaté, les dollars des références absolues figent les deux degrés de liberté d'une cellule. Ils l'empêchent de se déplacer à l'horizontale comme à la verticale. Il suffit simplement de conserver un seul des deux dollars pour accepter le déplacement dans une direction mais pas dans l'autre.

Le prix d'achat doit être considéré pour les calculs respectifs des prix de vente. La cellule C7 doit donc pouvoir suivre le déplacement vers le bas tout en restant figée dans sa colonne. Le dollar doit donc être inscrit seulement devant l'indice de colonne : $C7.

Inversement, les marges doivent être appliquées respectivement pour chaque colonne mais ne doivent pas se déplacer en hauteur. En d'autres termes, les références ne doivent pas changer de ligne. Nous devons seulement figer la ligne du premier pourcentage : D$4. Les dollars peuvent s'inscrire indépendamment au clavier. Mais l'emploi récursif de la touche F4 est conseillé.

• Enfoncer la touche Echap du clavier pour abandonner la modification de la dernière formule,
• Puis, double cliquer sur le tout premier calcul à reproduire, soit la cellule D7,
• Cliquer juste après la lettre C de la première référence pour la désigner,
• Enfoncer la touche F4 du clavier,

Un seul dollar subsiste en effet mais ce n'est pas le bon. Il est désormais placé devant l'indice de ligne. Or, nous devons figer la colonne et libérer la ligne.

• Enfoncer de nouveau la touche F4 du clavier,

Cette fois l'unique dollar vient se positionner devant l'indice de colonne pour empêcher tout déplacement horizontal ($C7).

• Cliquer ensuite juste après la lettre D de la seconde cellule impliquée,
• Enfoncer une première fois la touche F4 du clavier,

De cette manière, les deux dollars apparaissent. En l'état, la cellule du pourcentage de marge est complètement figée. Or nous devons la libérer en colonne.

• Enfoncer une seconde fois la touche F4 du clavier,

Seul le dollar devant l'indice de ligne persiste (D$4). Le déplacement vertical est donc interdit.

• Valider ces modifications par le raccourci clavier CTRL + Entrée,
• Tirer la poignée de la cellule à l'horizontale jusqu'en colonne H,

Fort logiquement, nous obtenons exactement les mêmes résultats que précédemment. Nous avions certes pallié le problème pour la première ligne mais pas pour l'ensemble du tableau.

• Tirer la poignée de la sélection à la verticale jusqu'en ligne 12,

Cette fois, tout rentre dans l'ordre en effet.

• Double cliquer sur le tout dernier résultat en H12 pour visualiser la formule déplacée,

Pour le prix de vente en colonne H et ligne 12, c'est bien le pourcentage en colonne H qui est appliqué tout en conservant sa ligne 4. Et c'est bien le prix d'achat en ligne 12 qui est impliqué tout en conservant sa colonne C.

Voilà donc un contexte dans lequel il est indispensable de figer des cellules spécifiquement. Seule l'une des deux références doit être fixée. Dans un cas il s'agit de la colonne et dans l'autre de la ligne.

Forts de ces acquis, nous pourrons désormais bâtir des modèles de tableaux complètement dynamiques, quel que soit le contexte. Il suffit de modifier un prix de vente et/ou un pourcentage pour constater la mise à jour des calculs respectifs.

Et pour une exploitation optimale du tableur, il est important de rappeler la technique de reproduction de calcul la plus professionnelle. Rappelez-vous, c'est celle qui consiste à présélectionner toutes les cellules des résultats à trouver. Il s'agit ensuite de bâtir l'unique formule comme si elle était seulement dédiée au premier calcul puis à la répliquer par raccourci clavier. Pour cela, nous devons commencer par effacer les précédents résultats.

• Sélectionner la plage de cellule D7:H12,
• Enfoncer la touche Suppr du clavier pour éliminer les calculs,

Dans cette plage désormais vierge, vous constatez que la première cellule n'est pas grisée. Comme nous l'avons déjà expliqué, elle est pourtant bien intégrée dans la sélection. Excel indique simplement qu'il s'agit de la cellule de référence. C'est pourquoi nous allons pouvoir bâtir le calcul sur cette base, bien que l'ensemble du tableau soit concerné.

• Taper le symbole égal (=) pour débuter la formule,

Il s'inscrit en effet seulement dans la première cellule de la plage,

• Cliquer sur le premier prix d'achat pour intégrer sa référence C7 dans la syntaxe,
• Enfoncer trois fois de suite la touche F4 du clavier pour fixer la colonne et libérer la ligne ($C7),
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Ouvrir la parenthèse pour factoriser le calcul,
• Taper le chiffre 1 suivi du symbole plus (+), soit 1+,
• Cliquer sur le premier pourcentage pour intégrer sa référence D4 dans la syntaxe,
• Enfoncer deux fois de suite la touche F4 du clavier pour fixer la ligne et libérer la colonne (D$4),
• Fermer la parenthèse de la factorisation,
• Enfin, valider la formule par la combinaison CTRL + Entrée,

Ce raccourci clavier est absolument nécessaire pour parfaire le procédé. Le calcul est certes strictement identique au précédent. Mais grâce à la présélection, il est automatiquement déployé et répliqué sur l'ensemble des cellules désignées. Nous n'avons pas eu besoin de tirer la poignée dans un premier temps à l'horizontale pour la tirer ensuite à la verticale.

Cette technique est à la fois précise, puissante et rapide.

Pour finaliser le tableau, il convient d'afficher les prix en Euros. Il s'agit d'un formatage. Nous insistons sur ce point à chaque reprise. En aucun cas une unité de mesure ou une devise ne doit être écrite dans la cellule. En associant une donnée textuelle à une valeur numérique, nous la transformerions en un texte. Et aucune opération de calcul ne peut être réalisée sur du texte. Nous devons donc appliquer le format de l'Euro.

• Sélectionner tous les prix, soit la plage de cellules C7:H12,
• Cliquer alors sur le bouton Format Nombre Comptabilité dans la section Nombre du ruban Accueil,

Les données numériques sont désormais formatées en Euro avec deux décimales. La lecture et la compréhension du tableau s'en trouvent renforcées. Si vous sélectionnez le prix d'achat en C7 qui n'est pas le fruit d'un calcul, vous constatez que la barre de formule continue d'afficher le nombre brut tandis que la cellule restitue le formatage.