Synthèse des ventes et chiffres d'affaires Excel

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


Sujets que vous pourriez aussi aimer :


Calculer les chiffres d'affaires HT et TTC

Nous abordons ici le deuxième exercice Excel, niveau intermédiaire. Dans le précédent, nous avons appris à exploiter une notion fondamentale. Les références absolues permettent de figer une cellule dans le calcul.



Comme vous allez le constater, dans ce cas pratique au demeurant fort conventionnel, elles vont s'avérer indispensables afin de produire des calculs dynamiques réplicables.

Source et présentation de la problématique
Afin de concentrer les travaux sur la mise en oeuvre des calculs, nous proposons de récupérer un tableau existant.

• Télécharger le classeur chiffre-affaires-librairie.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 est constitué d'une seule feuille nommée CA. Son tableau consiste à réaliser la synthèse des chiffres d'affaires d'une librairie. Entre les lignes 5 et 10 pour les colonnes C à E, sont énumérées les ventes réalisées par catégories, au cours des trois premiers mois. Ces catégories sont renseignées en colonne B.



En ligne 11 et en colonne F, les totaux de ces chiffres d'affaires doivent être calculés dans un premier temps. Nous obtiendrons ainsi les résultats par catégorie et par mois.

Sur la base de ces montants hors taxes, nous devrons fournir les totaux TTC. C'est pourquoi il est demandé tout d'abord de calculer la valeur de la TVA en colonne G. Il s'agit d'une simple multiplication à réaliser entre chaque somme et le pourcentage de la TVA, inscrit en cellule de référence C13. Vous l'avez compris, les références absolues vont entrer en jeu.



Par simples additions avec les totaux hors taxes, ces résultats permettront d'obtenir les totaux TTC en colonne H.

Enfin, des calculs de synthèse sont attendus en colonnes I et J. La première consiste à restituer le pourcentage des ventes d'une catégorie par rapport à l'ensemble des catégories. Il suffit de diviser son total par la somme des ventes réalisées. Nous obtiendrons ainsi la proportion des chiffres. Cet indicateur est pertinent pour interpréter et optimiser les ventes. Et puis, la dernière colonne doit afficher la moyenne des chiffres réalisés par catégorie, pour les trois mois.



A l'issue des calculs, nous nous soucierons de la présentation à l'aide de quelques formats adaptés.

Synthèse des ventes réalisées
Nous savons parfaitement réaliser des calculs manuels. Nous pourrions additionner tour à tour les valeurs demandées, afin de produire la première somme à répliquer. Mais, lorsque les données à additionner sont nombreuses, nous avions compris que l'utilisation de la fonction Excel Somme automatique était plus pertinente. Et lorsque le contexte s'y prête comme ici, une méthode associée la rend redoutable d'efficacité. Elle consiste à présélectionner l'ensemble des cellules à sommer avec les cellules vides des résultats à placer. Ces dernières sont en effet collées au tableau.

• Sélectionner toutes les ventes à additionner avec les cellules vides adjacentes des résultats à produire, soit la plage C5:F11,

Sans ambiguïté, nous indiquons donc à Excel quelles sont les valeurs à sommer en lignes et en colonnes et à quels emplacements les résultats doivent être livrés.

• Tout à fait à droite du ruban Accueil, cliquer alors sur le bouton Somme automatique,

Instantanément, sans même devoir reproduire le calcul, tous les totaux hors taxes sont livrés. La ligne 11 offre les montants pour chaque mois. La colonne F propose les totaux pour chaque catégorie.

Ce bouton qui ressemble à une lettre M orientée à 90 degrés est en fait matérialisé par la lettre grecque Sigma. Comme le rappelle son info-bulle, sur la présélection, nous aurions pu obtenir le même résultat grâce au raccourci clavier alt + =.

Vous en conviendrez, cette méthode est redoutable d'efficacité. Voyons la logique appliquée par Excel.

• Sélectionner le premier résultat sur la ligne 11, soit la cellule C11,

En consultant la barre de formule, nous remarquons que l'addition a bien été réalisée sur les cellules de la colonne C, soit pour le mois de Janvier :

=SOMME(C5:C10)

Souvenez-vous, dans la syntaxe Excel, les deux points séparant les cellules signifient de C5 à C10. En les remplaçant par un point-virgule (;), nous réaliserions la somme seulement sur C5 et C10.

• Sélectionner la cellule D11 sur sa droite,

Les références relatives sont entrées en jeu. Excel a décalé les coordonnées des cellules impliquées dans l'addition, une colonne sur la droite. C'est ainsi qu'il produit la somme des ventes en colonne D, donc pour le mois de Février :

=SOMME(D5:D10)

Naturellement, cette logique se répercute pour la somme du mois de Mars en colonne E. Et bien entendu, ce raisonnement est respecté pour les sommes en lignes. Le premier résultat pour les Romans en ligne 5 implique les ventes de la même ligne :

=SOMME(C5:E5)

Pour les Thrillers en ligne 10, soit 5 lignes plus bas, les coordonnées des cellules sont donc déplacées de 5 lignes.

=SOMME(C10:E10)

Calculer le montant de la TVA
Le calcul qui suit n'est pas anodin. Il consiste à livrer le montant dû à la TVA de 5,5%, sur chaque somme hors taxes calculée en colonne G. Certes, il s'agit de réaliser une simple multiplication entre les deux valeurs. Cependant, ce pourcentage de TVA est situé dans une cellule isolée, soit une cellule de référence. Nous proposons de commettre l'erreur dans un premier temps, pour mieux comprendre comment aboutir au résultat dans un deuxième temps.

• Sélectionner le premier résultat à calculer, soit la cellule G5,
• Taper le symbole égal (=) pour débuter la formule,
• Sélectionner le premier total hors taxes, soit la cellule F5 pour l'intégrer dans la syntaxe,
• Inscrire l'étoile du pavé numérique (*) pour enclencher la multiplication,
• Puis, désigner le pourcentage de TVA, soit la cellule C13 pour l'intégrer dans la formule,
• Valider la syntaxe par le raccourci clavier CTRL + Entrée,

Le résultat produit apparaît tout à fait cohérent.

• Cliquer et glisser la poignée de ce calcul à la verticale jusqu'en ligne 10,

Rien ne va plus. Toutes les valeurs produites sont nulles. Aucune TVA hormis la première n'est valable. La raison est pourtant simple sur la base du raisonnement des références relatives, que nous avons démontré précédemment avec les sommes. Le calcul que nous avons bâti en ligne 5 fait intervenir le total HT de la même ligne et le taux de TVA en ligne 13 : =F5*C13

• Double cliquer sur la cellule G6 juste en dessous pour visualiser sa formule,

De cette manière, Excel surbrille dans des couleurs différentes, les cellules impliquées dans le calcul répliqué. Et la raison de cet échec saute instantanément aux yeux. En répliquant la logique une ligne plus bas, Excel a naturellement intégré dans la formule les cellules situées une ligne plus bas. Certes, le montant HT à considérer est bien celui de la ligne 6 désormais, soit la cellule F6. Mais le taux de TVA quant à lui n'est inscrit que dans une cellule, il s'agit de la cellule de référence C13. En impliquant la cellule vide située juste en dessous, le calcul produit fort logiquement un résultat vierge.

La cellule F5 du montant hors taxes d'origine doit rester libre de ses mouvements. Mais nous devons indiquer que le taux de TVA ne doit pas suivre le déplacement. Ce sont les références absolues qui permettent de figer une cellule dans un calcul. Et pour ce faire, nous devons exploiter la touche F4 du clavier après avoir désigné la cellule à figer. Mais avant cela, nous devons commencer par supprimer les précédents résultats.

• Sélectionner tous les résultats précédents, soit la plage de cellules G5:G10,
• Enfoncer la touche Suppr du clavier pour effacer les formules,
• Puis, cliquer sur la cellule G5 pour la sélectionner indépendamment,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Désigner le premier total hors taxes, soit la cellule F5,
• Inscrire le symbole de l'étoile (*) pour enclencher la multiplication,
• Puis, désigner le taux de TVA, soit la cellule C13 pour inclure sa référence dans la syntaxe,
• Enfoncer alors la touche F4 du clavier pour la figer dans le calcul,

Instantanément, des dollars viennent encadrer les coordonnées de la cellule. Elle est complètement figée désormais. Elle ne peut se déplacer ni en colonne ($ devant le C), ni en ligne ($ devant le 13). Dans les applications futures, nous verrons qu'il peut s'avérer nécessaire de libérer l'une de ces coordonnées.

Nous venons d'appliquer les références absolues. Et nous proposons de constater l'incidence.

• Valider la formule par le raccourci clavier CTRL + Entrée,

Pour l'instant rien ne change. Le premier résultat était déjà correct. Fort logiquement, nous obtenons le même montant que précédemment.

• Cliquer et glisser la poignée du résultat à la verticale, jusqu'en ligne 10,

Nous obtenons désormais des valeurs de TVA calculées sur la base des montants hors taxes respectifs. Nous n'avons pas double cliqué sur la poignée du calcul comme nous le faisons souvent. Par cette technique, Excel détectant les bornes du tableau, reproduit automatiquement la logique jusqu'à la fin. Mais ici, la dernière cellule située en ligne 11 doit afficher la somme de ces montants de TVA. Le calcul change. C'est la raison pour laquelle nous avons accompagné la poignée, précisément jusqu'à l'emplacement spécifié.

Les valeurs obtenues semblent donc cohérentes. Nous proposons de vérifier la bonne réplication de la logique.

• Double cliquer sur le dernier résultat, soit la cellule G10, pour afficher sa syntaxe,

Le calcul bâti à l'origine en ligne 5 implique le HT de la même ligne (F5) et le taux de TVA en ligne 13 (C13) : =F5*$C$13. 5 lignes plus bas en cellule G10, c'est fort naturellement que la TVA est calculée sur la base du HT situé lui aussi en ligne 10 (F10). Mais comme vous le constatez, désormais le taux de TVA est bien considéré en référence de la cellule C13 qui n'a pas suivi le déplacement imposé par la poignée de la cellule : =F10*$C$13.

• Enfoncer la touche Echap du clavier pour annuler la saisie de la cellule,

Les totaux TCC en colonne H peuvent maintenant être déduits des deux précédents calculs. Il suffit d'ajouter le montant dû à la TVA, au total hors taxes de la même ligne. Toutes les coordonnées impliquées dans ce calcul doivent naturellement suivre le déplacement imposé. Les références absolues ne sont donc pas de mise. Pour produire ces résultats, nous proposons d'employer une méthode efficace que nous préconisons souvent. Elle consiste à présélectionner l'ensemble des cellules des résultats, puis à bâtir le calcul comme s'il s'agissait seulement du premier et à valider par le raccourci CTRL + Entrée pour le répliquer.

• Sélectionner tous les TTC à calculer, soit la plage de cellules H5:H10,

La première cellule de la plage, H5, n'apparaît pas grisée. Elle est pourtant bien sélectionnée. Excel indique qu'il s'agit de la cellule active par défaut dans la sélection. C'est pourquoi nous allons pouvoir construire la formule comme si elle lui était vouée.

• Taper le symbole égal (=) pour débuter la construction,
• Cliquer sur le premier montant HT pour intégrer la référence F5 dans la syntaxe,
• Inscrire le symbole plus (+) du pavé numérique pour enclencher l'addition,
• Puis, désigner le premier montant de TVA pour inclure sa référence G5 dans la formule,
• Enfin, valider le calcul par le raccourci clavier CTRL + Entrée,

En effet, tous les montants TTC sont calculés et ils sont cohérents.



Cette méthode est particulièrement intéressante d'autant qu'elle est valable pour tout type de formule. Mais dans ce cas précis, nous aurions pu aller encore plus vite. L'addition est une somme. Les résultats sont accolés aux valeurs à additionner. En conséquence, nous aurions pu exploiter la première méthode que nous avons présentée pour la somme automatique.

• Sélectionner tous ces résultats, soit la plage de cellules H5:H10,
• Enfoncer la touche Suppr du clavier pour effacer les calculs,
• Sélectionner désormais tous les montants à additionner avec les cellules vides pour placer les résultats, soit la plage F5:H10,
• Puis, cliquer sur le bouton Somme automatique, tout à fait droite dans le ruban Accueil,

Une sélection suivie d'un clic a suffi pour livrer tous les résultats.

Calculer les parts de pourcentage
Le résultat statistique proposé en colonne I est tout à fait intéressant. Il doit rendre compte de la proportion des ventes réalisées pour chaque catégorie. Instantanément, nous saurons quelles sont les rubriques qui fonctionnent le mieux. Il s'agit de comparer chaque montant HT avec la somme des ventes en F11. Le calcul est donc une division. Mais encore une fois, chaque montant HT doit se déplacer avec la formule. En revanche, la somme des ventes pour la division est placée dans une unique cellule. Elle doit être figée par les références absolues pour ne pas se déplacer avec le calcul répliqué.

• Sélectionner la cellule du premier résultat à trouver, soit I5,
• Taper le symbole égal (=) pour débuter la formule,
• Désigner le premier total hors taxes pour intégrer sa référence F5 dans le calcul,
• Inscrire le symbole slash (/) du pavé numérique pour enclencher la division,
• Désigner la somme des totaux HT pour intégrer sa référence F11 dans la syntaxe,
• Enfoncer la touche F4 du clavier pour la figer dans le calcul,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Puis, cliquer et glisser la poignée de la cellule à la verticale jusqu'à la ligne 10,

Fort logiquement, nous obtenons des valeurs inférieures à 1. Il s'agit en effet de parts de pourcentage. Ces résultats doivent donc être formatés en tant que tels. Nous pouvons profiter de la sélection sur l'intégralité de la plage, grâce à l'action menée précédemment avec la poignée.

• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Style de pourcentage,

La présentation des résultats est désormais plus conforme et plus simple à lire. Instantanément, nous remarquons que les Thrillers réalisent les meilleures proportions de ventes. Nous pourrions ajouter des décimales à ces valeurs, grâce aux boutons de la section Nombre. Mais dans le cas de parts de pourcentage, ce sont les ordres de grandeur qui comptent.

Si nous n'avions pas figé la cellule de la somme dans ce calcul, au fur et à mesure de la réplication, nous aurions divisé chaque montant HT par la cellule située en dessous soit F12 puis F13 etc... Ces cellules étant vides, elles auraient provoqué une erreur retournée sous la forme suivante par Excel : #Div0!. La division par zéro est en effet impossible en mathématiques.

La formule que nous avons bâtie pour obtenir le premier résultat fait donc intervenir les cellules de la ligne 5 et de la ligne 11 : =F5/$F$11. Par réplication et étant donné que la cellule F11 est figée, le dernier résultat construit la formule impliquant les cellules de la ligne 10 et toujours de la ligne 11 : =F10/$F$11.



Calculer la moyenne des chiffres d'affaires
Pour obtenir la moyenne des chiffres d'affaires par catégorie, nous pourrions simplement diviser la somme de chaque ligne par trois. Chaque total est en effet consolidé sur trois valeurs. Mais comme nous l'avons dit, tout calcul Excel doit être bâti sur des variables. Or le chiffre 3 est une constante. Et les constantes sont à bannir. C'est pourquoi, nous proposons simplement d'exploiter la fonction Excel Moyenne. Son utilisation et sa syntaxe sont identiques à la somme automatique. Le résultat diffère complètement bien entendu. Comme il s'agit d'une fonction automatique, nous ne devons pas inscrire le symbole égal. Excel le fera pour nous.

• Sélectionner la cellule de la première moyenne à trouver, soit J5,
• Tout à fait à droite du ruban Accueil, cliquer sur la flèche du bouton Somme automatique,
• Dans la liste, choisir la Moyenne,

A l'instar de la somme, la fonction Moyenne est en attente de validation par l'utilisateur. Et c'est tant mieux. Pour ce calcul, Excel intègre naturellement toutes les cellules numériques adjacentes de la même ligne. Or, il doit seulement être bâti sur les trois premières, celles des ventes pour chaque mois.

• A la souris, sélectionner uniquement ces trois valeurs, soit la plage de cellules C5:E5,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,
• Dans la foulée, tirer la poignée du résultat à la verticale jusqu'en ligne 10,

Nous obtenons bien la moyenne des chiffres d'affaires réalisés pour le trimestre. Et à ce titre, puisque le calcul consiste en une division, vous notez l'apparition d'un grand nombre de décimales. C'est un défaut que nous corrigerons avec le formatage des cellules.



La formule que nous avons construite à l'origine en ligne 5 intègre donc les chiffres d'affaires sur cette même ligne : =MOYENNE(C5:E5). Comme toujours par le jeu des références relatives, en répliquant ce calcul sur les lignes du dessous, la moyenne est effectuée sur les chiffres d'affaires correspondants : =MOYENNE(C10:E10).

En ligne 11, il nous reste à calculer la somme des montants TTC et des parts de pourcentage. Concernant ces dernières, si elles sont cohérentes, leur addition doit conduire à 100%.

• Sélectionner toutes les valeurs à sommer avec les deux cellules vides des résultats à fournir, soit la plage de cellules H5:I11,

De cette manière, nous indiquons à Excel sans ambiguïté que les sommes doivent être réalisées en colonnes.

• Cliquer alors sur le bouton Somme automatique du ruban Accueil,

La somme de toutes les parts de pourcentage conduit bien au résultat attendu, confirmant la cohérence des précédents calculs.

Présentation des données
La mise en forme globale du tableau est déjà fort avancée. Mais les données monétaires doivent être affichées en Euro. Et comme vous le savez pertinemment, le symbole de la devise ne doit jamais être écrit dans la cellule. Nous devons appliquer un format monétaire. Toutes ne sont pas concernées. Une rupture intervient en colonne I du fait de la présence des pourcentages. Et nous devons les conserver en l'état. Nous proposons néanmoins de regrouper toutes les cellules ciblées dans une même sélection. Ainsi, nous optimiserons les opérations de formatage. Il s'agit de techniques que nous avons répétées à maintes reprises dans les exercices Excel pour débutants. C'est la touche CTRL du clavier qui permet d'additionner des sélections pour les regrouper.

• Sélectionner tout d'abord la plage de cellules C5:H11,
• Tout en maintenant la touche CTRL enfoncée, sélectionner la plage de cellules J5:J10,
• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Format Nombre Comptabilité,

Toutes les valeurs numériques, hormis les pourcentages, sont ainsi formatées en Euros. Un séparateur de millier facilite la lecture des grandes valeurs. Toutes sont dotées de deux décimales. Ce formatage est légitime pour le montant de la TVA, le TTC et la moyenne du CA. Mais pour les quatre premières colonnes, de C à F, cette précision n'est pas nécessaire. Elle alourdit la présentation.

• Sélectionner la plage de cellules C5:F11,
• Dans la section Nombre du ruban Accueil, cliquer deux fois de suite sur le bouton Réduire les décimales,

Nous allégeons ainsi la présentation pour plus de clarté.

Pour terminer en beauté et conférer encore plus de dynamisme à ce tableau de suivi des chiffres d'affaires, nous souhaitons mettre en place une mise en forme conditionnelle. Comme nous l'avons appris au travers des exercices précédents, il s'agit de faire varier la couleur de certaines cellules en fonction des valeurs qu'elles portent. Rien à voir donc avec les réglages traditionnels de mise en forme. Mais cette fois, nous ne souhaitons pas faire simplement ressortir les meilleurs chiffres ou les pires.

Nous souhaitons que toute la ligne soit concernée. Nous pourrons ainsi facilement repérer les catégories de livres qui se vendent le mieux. Un tel critère se construit par le biais d'une formule. Et c'est nouveau pour nous.

• Sélectionner la plage de cellules B5:J10,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir Nouvelle règle,

• Dans la boîte de dialogue qui suit, sélectionner le dernier type : Utiliser une formule pour déterminer...,
• Dans la zone de saisie juste en-dessous, taper le symbole égal (=) pour initier la syntaxe du critère,
• Sur la feuille, cliquer sur le premier total hors taxes pour intégrer la référence F5 dans la règle,

Vous le remarquez, les coordonnées de la cellule désignée sont automatiquement figées par Excel. Et justement, nous souhaitons que ce critère soit bâti sur le montant HT de chaque ligne. La colonne ne doit donc pas bouger. La ligne en revanche doit être libre.

• Enfoncer deux fois de suite la touche F4 du clavier de manière à ne conserver le dollar que devant l'indice de colonne, ce qui donne : $F5,
• Taper de nouveau le symbole égal (=) pour l'égalité à vérifier,
• Puis, saisir la fonction Max suivie d'une parenthèse, soit max(,
• Sélectionner alors l'ensemble des totaux hors taxes et fermer la parenthèse, ce qui donne : max($F$5:$F$10),

Pour cette plage, nous prenons soin de conserver les références absolues. Chaque montant doit être comparé à la plus grande valeur (Max) sur cette plage de cellules, dont les bornes ne bougent pas. Si ce critère est vérifié, une couleur de fond verte doit être enclenchée.

• Cliquer sur le bouton Format situé en bas de la boîte de dialogue,
• Dans la nouvelle boîte de dialogue, activer l'onglet Remplissage,
• Puis, choisir une couleur verte dans la palette,
• Valider par Ok pour revenir sur la première boîte de dialogue,

L'aperçu indique la mise en forme conditionnelle qui sera déclenchée sur les cellules sélectionnées, lorsque le critère saisi juste au-dessus sera vérifié.

• Valider de nouveau par Ok pour revenir sur la feuille Excel,

Instantanément la dernière ligne est repérée. Nous l'avions déjà constaté grâce aux parts de pourcentage, les thrillers représentent en effet les meilleures ventes.

• En cellule C10, saisir 1000 à la place de 2944 et valider par Entrée,

Aussitôt la couleur verte se déplace pour repérer la catégorie Jeunesse. A cause de cette modification, les Thrillers ne constituent plus les meilleures ventes. Vous comprenez donc l'intérêt de la mise en forme conditionnelle. Elle réagit dynamiquement sur les résultats des calculs qui eux-mêmes sont dynamiques. Elle facilite grandement la lecture et l'interprétation des tableaux de synthèse.

Pour parfaire l'application, nous pourrions bâtir une règle de format dynamique similaire mais consistant à repérer la catégorie la moins lucrative.



Dans ce cas, le critère à poser implique la fonction Min pour comparer chaque total hors taxes avec le plus petit résultat : =$F5=MIN($F$5:$F$10). Bien entendu, il s'agit d'adapter la symbolique de couleur.