Exercice Excel pour calculer les remises clients

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


Sujets que vous pourriez aussi aimer :


Exercice Excel pour calculer les remises Clients

Dans ce premier exercice Excel, niveau intermédiaire, nous entrons immédiatement dans le vif du sujet. C'est en effet une notion cruciale du tableur qui est au centre des débats. Il s'agit des références absolues. Dans les exercices pour débutants, nous avons exploité le mode de fonctionnement d'Excel sur les références relatives, pour pouvoir reproduire un calcul sans ne jamais le refaire.



Mais des cas spécifiques comme ici, mettent à mal ces fameuses références relatives. Et ces cas, sont beaucoup plus fréquents qu'on ne le croit. Pourtant l'objectif est toujours le même. Une fois un calcul bâti, il ne doit pas être reconstruit, il doit être reproduit.

Source et présentation de la problématique
Ce petit classeur Excel est proposé au téléchargement. Nous devons donc le récupérer.

• Télécharger le classeur calculs-des-remises-clients.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué d'une seule feuille nommée Remises. En colonne B, le tableau liste les articles de sport en vente, de la ligne 8 à la ligne 15. En colonne C et pour les mêmes lignes, sont énumérés les prix de vente respectifs pour le grand public. Un défaut de mise en forme est à noter dans ces deux colonnes. L'alignement dans la hauteur n'est pas conforme au reste de la présentation. Les informations sont calées en bas des cellules. Il s'agira de les remonter.

Un taux de remise est inscrit en cellule D5 (12%). Il doit être exploité dynamiquement pour calculer les prix de vente concédés aux clients privilégiés.

Calculer un prix remisé
Comme vous le savez, chaque formule doit être bâtie sur les références des cellules. C'est ainsi que nous obtenons des résultats dynamiques. Ils varient si les cellules impliquées dans le calcul changent de valeur. Une simple modification du pourcentage de remise en cellule D5 doit actualiser tous les prix revendeurs.

Le prix public annoncé pour le premier article en cellule C8 est de 320 Euros. La remise de 12% inscrite en D5 doit lui être appliquée pour obtenir le prix revendeur en D8. Le calcul de la remise est donc le suivant :

=C8*D5

Cette remise doit être soustraite au montant de départ. Donc le calcul décomposé du prix remisé (revendeur), est le suivant :

=C8 -C8*D5

Un calcul doit être écrit dans la cellule où le résultat est attendu. Et comme vous le savez, il doit nécessairement débuter par le symbole égal (=).

• Sélectionner la cellule du premier résultat à trouver, soit D8,
• Taper le symbole égal (=) pour débuter le calcul,
• Cliquer sur le prix public de la ligne en cours pour intégrer la cellule C8 dans la syntaxe,
• Taper le symbole moins (-) du pavé numérique pour enclencher la soustraction,
• Cliquer de nouveau sur la cellule C8,
• Taper le symbole étoile (*) du pavé numérique pour déclencher la multiplication,
• Sélectionner le taux de remise pour inclure la cellule D5 dans la syntaxe de la formule,
• Puis, valider le calcul à l'aide du raccourci clavier CTRL + Entrée,

Nous l'avons exploitée à maintes reprises dans les exercices Excel pour débutants, cette combinaison de touches permet de valider une cellule tout en la conservant active. L'intérêt est de pouvoir l'exploiter dans la foulée sans devoir la resélectionner.



Le résultat obtenu est tout à fait cohérent. Il est bien dynamique.

• En D5, saisir 10 à la place de 12 puis valider par la touche Entrée,

Instantanément, le résultat s'actualise en effet. La remise étant moins importante, le prix revendeur augmente.

• En D5, saisir de nouveau 12 à la place de 10,

Nous récupérons ainsi la valeur d'origine. A chaque occasion nous insistons sur une notion fondamentale. Dans Excel, on ne refait jamais deux fois le même calcul. Pour reproduire une formule, nous avons pris l'habitude d'exploiter la poignée de la cellule du résultat.

• Cliquer sur la cellule D8 pour sélectionner de nouveau le résultat du calcul,

Cette poignée est matérialisée par le petit carré situé en bas à droite de la cellule active. Si vous pointez dessus, le curseur de la souris se transforme en une croix noire. Excel indique que la poignée est prête à être exploitée.

• Pointer sur la poignée de la cellule D8,
• Lorsque le curseur est une croix noire, double cliquer avec le bouton gauche de la souris,

Nous aurions aussi pu accompagner le mouvement en cliquant et en glissant cette poignée à la verticale jusqu'à la ligne 15. Avec le double clic, et dans le cas d'un calcul à reproduire sur la hauteur, Excel détecte les bornes du tableau. De fait, il reproduit automatiquement la logique du calcul.



Mais cette fois et à notre grande surprise, la technique que nous avons adulée jusqu'alors ne semble plus fonctionner. Les montants calculés sont soit incohérents soit des erreurs. La raison est pourtant évidente selon le fonctionnement des références relatives sur lesquelles nous avons longuement insisté. Les références des cellules impliquées dans la formule s'adaptent pour suivre le déplacement imposé par la poignée.

Le calcul que nous avons bâti en D8 est le suivant : =C8-C8*D5. Il fait donc intervenir le prix public sur la ligne 8 (C8) et la remise en ligne 5 (D5).

• Double cliquer sur la cellule D9 du dessous pour activer la modification de la cellule,

Dans le cas d'une formule, cette technique met en évidence par des jeux de couleurs, les cellules intervenant dans la construction.



Pour le calcul du prix de vente suivant, Excel a fort logiquement considéré le prix public suivant, soit la cellule C9 au lieu de la cellule C8. La logique étant répercutée une ligne plus bas, il implique les cellules situées une ligne plus bas. C'est ce que l'on appelle les références relatives. Jusque là tout va bien. Mais comme vous le remarquez, la remise a elle aussi subi le déplacement. Le prix n'est plus multiplié par la cellule D5. Il est désormais multiplié par la cellule D6, soit celle de la ligne du dessous. Or, cette cellule est vide. C'est la raison pour laquelle le prix revendeur est identique au prix public dans ce cas.

• Enfoncer la touche Echap du clavier pour annuler la saisie de la cellule,
• Puis, double cliquer sur celle du dessous, soit D10,

Le même protocole est respecté une ligne plus bas. Le prix public considéré est désormais situé en C10, soit deux lignes en dessous du calcul d'origine. De fait, la remise impliquée est elle aussi considérée deux lignes plus bas, soit en D7. Et cette fois, il s'agit du titre de la colonne, soit d'un texte. C'est la raison pour laquelle la multiplication échoue. Excel renvoie une erreur sous forme de message : #VALEUR!.

Figer une cellule dans un calcul
Dans ce problème à résoudre, le taux de remise est une cellule de référence. Excel doit la considérer comme telle. Il serait absurde de répliquer ce même taux de remise dans une colonne supplémentaire pour l'impliquer dans une formule où les références relatives fonctionneraient. Cette répétition de valeurs alourdirait la présentation. Une modification du taux de remise impliquerait le changement dans toutes les cellules concernées.

Dans la formule, nous devons indiquer à Excel que la cellule du taux de remise ne doit pas bouger. Nous devons la figer pour qu'elle ne suive pas le déplacement lors de la répercussion du calcul. C'est ce que l'on appelle les références absolues. Cette notion est fondamentale et incontournable dans Excel. C'est la touche F4 du clavier qui permet de figer une cellule désignée dans la formule.

• Enfoncer la touche Echap du clavier pour désactiver la précédente saisie,
• Sélectionner tous les précédents résultats, soit la plage de cellules D8:D15,
• Enfoncer la touche Suppr du clavier pour éliminer tous les calculs sélectionnés,
• Puis, sélectionner seulement la cellule D8,
• Taper le symbole égal (=) pour démarrer la formule,
• Sélectionner le premier prix public pour intégrer C8 dans la syntaxe,
• Taper le symbole moins (-) du pavé numérique pour la soustraction,
• Cliquer de nouveau sur C8,
• Taper le symbole de l'étoile (*) du pavé numérique pour la multiplication,
• Sélectionner alors le taux de remise en D5,
• Puis, enfoncer la touche F4 du clavier,

Instantanément, des dollars viennent encadrer les références de la cellule du taux de remise. Un dollar se positionne devant l'indice de colonne ($D). Un autre se place devant l'indice de ligne ($5). Dans la syntaxe Excel, cette notation interdit à la cellule de se déplacer en ligne et en colonne. Elle est complètement figée. Il est donc possible de ne conserver qu'un seul des dollars pour empêcher le déplacement seulement dans l'une des deux directions. Nous y reviendrons en temps voulu. Ces dollars peuvent aussi s'inscrire manuellement. Mais la touche F4 du clavier est plus efficace. Elle doit être enfoncée juste après avoir désigné la cellule à figer.

• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Cette fois, les résultats sont parfaitement cohérents. Plus aucune erreur n'est à signaler. Le pourcentage est bien appliqué sur chaque montant de départ.

• Double cliquer sur le troisième résultat, soit la cellule D10 pour afficher sa formule,

C'est bien le prix public correspondant, soit deux lignes plus bas, qui est considéré. Mais cette fois, la remise qui lui est appliquée n'a pas bougé. Immuablement et grâce aux références absolues, il s'agit désormais de la cellule D5, figée par les dollars dans la syntaxe.

• Enfoncer la touche Echap du clavier pour annuler la saisie de la cellule,
• En D5, taper 10 à la place de 12,

Tous les résultats s'actualisent instantanément. La cellule D5 du taux de remise est désormais considérée comme une cellule de référence. Nous venons donc de démontrer la solution qui permet de dénouer bien des problématiques dans Excel. Grâce aux références absolues, nous serons désormais capables de bâtir un seul calcul dynamique à reproduire, même lorsque des cellules de référence sont à considérer.

La syntaxe du calcul que nous avons construit peut-être simplifiée. Pour la première ligne par exemple, retrancher 10% sur les 320 Euros de départ, revient à payer (100%-10%) du montant. Le calcul factorisé est donc le suivant : 320*(1-10%). Nous proposons de le mettre en application en exploitant la technique de présélection de toutes les cellules concernées. Cette méthode permet de reproduire la formule sur l'ensemble des cellules par le raccourci CTRL + Entrée. Tous les précédents résultats doivent d'abord être éliminés.

• Sélectionner tous les prix revendeurs, soit la plage de cellules D8:D15,
• Enfoncer la touche Suppr du clavier pour vider les cellules de leur contenu,

La première cellule d'une plage présélectionnée n'apparaît pas grisée. Elle est pourtant bien incluse. Et justement, Excel indique qu'elle est active par défaut. C'est ainsi que nous allons pouvoir construire la formule comme si elle lui était dédiée.

• Taper le symbole égal (=) pour débuter le calcul,
• Sélectionner le prix public de la première ligne, soit la cellule C8,
• Taper l'étoile du pavé numérique (*) pour enclencher la multiplication,
• Ouvrir la parenthèse pour la factorisation,
• Taper le chiffre 1 pour les 100% de départ, suivi du symbole moins, soit 1 -,
• Sélectionner le taux de remise à retrancher, soit la cellule D5,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul,
• Fermer la parenthèse et valider nécessairement par le raccourci CTRL + Entrée,

Nous obtenons fort logiquement les mêmes résultats que précédemment. Mais nous simplifions la syntaxe de la formule. De plus, nous exploitons les références absolues avec une technique de réplication on ne peut plus efficace. Nous exploitons donc Excel de façon professionnelle.



La formule que nous avons créée pour la première cellule de la plage en ligne 8, fait intervenir les références de la ligne 8 et de la ligne 5 :

=C8*(1-$D$5)

En répliquant cette logique par le raccourci clavier sur l'ensemble des cellules, en ligne 15 finale, le calcul fait intervenir les références de la ligne 15 et de la ligne 5 :

=C15*(1-$D$5)

Comme précédemment donc, les références des prix publics ont suivi le sens du déplacement. Et comme précédemment aussi, grâce aux références absolues, nous avons figé la cellule du taux de remise. Celle-ci n'a donc pas bougé pour être appliquée à tous les prix, malgré le déplacement.

• En cellule D5, saisir 12 à la place de 10,

Etait-il nécessaire de le souligner, nos calculs demeurent fort logiquement dynamiques. Notre modèle est donc portable et exploitable pour d'autres cas, d'autres produits ou encore d'autres enseignes. Tous les résultats se sont parfaitement recalculés, sur la base de cette remise de référence.

Dans nos entraînements futurs, nous constaterons que ces fameuses références absolues décuplent la puissance et les possibilités offertes par Excel.

Formater les cellules
La mise en forme d'un tableau ne doit jamais être négligée. L'interprétation des données en dépend. Nous devons corriger le petit défaut d'alignement que nous avons évoqué en préambule de cette formation. Mais nous devons aussi formater les prix en Euros. Rappelez-vous de cette notion fondamentale : Une devise ne doit jamais être inscrite manuellement dans une cellule. Un format doit lui être appliqué.

• Sélectionner tous les prix, soit la plage de cellules C8:D15,
• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Format Nombre Comptabilité,

Nous aurions pu réaliser le raccourci clavier CTRL + M. Les montants sont désormais formatés en Euros. Ils affichent tous deux décimales pour des raisons d'homogénéité dans la présentation. L'alignement à droite de ces valeurs facilite leur lecture. Nous avons abordé cette notion dans les exercices Excel pour débutants, il est possible d'agir sur la précision de ces décimales. C'est pourquoi sont proposés les boutons Ajouter une décimale et Réduire les décimales, sur la droite de la section Nombre. Rappelez-vous que si une valeur est arrondie à l'affichage, il s'agit simplement d'une question de présentation. Excel continuera toujours de considérer le nombre d'origine, à la décimale près.

Pour l'alignement au centre de la cellule, nous devons réaliser deux sélections dissociées. Les désignations en colonne B sont alignées à gauche. Les montants en colonne C et D sont alignés à droite. Cette différence d'alignement horizontal impose que leur alignement vertical soit défini tour à tour, notamment du fait de la présence de retraits.

• Sélectionner les désignations, soit la plage de cellules B8:B15,
• Dans la section Alignement du ruban Accueil, cliquer sur le bouton Aligner au centre,
• Sélectionner ensuite la plage de cellules C8:D15,
• Cliquer de nouveau sur le bouton Aligner au centre dans le ruban Accueil,

La présentation est désormais homogène. Toutes les informations sont parfaitement centrées dans la hauteur des cellules. Si nous avions regroupé ces trois colonnes dans une même sélection, nous aurions perdu les retraits qui permettent de décaler les données par rapport au bord de la case. Cette technique aère la présentation et propose une lecture plus agréable des tableaux Excel.