Rapport de ventes mensuel sur Excel : la structure et les formules à copier

Construisez un rapport de ventes mensuel sur Excel à partir d'un export : structure, formules SOMME.SI.ENS, comparaison au mois précédent et graphiques.

Fondateur, Glow Reports. , 4 min de lecture
  • Excel
  • Reporting commercial
  • Reporting
Une pile de tickets de commande qui devient un rapport mensuel d'une page avec un histogramme et un graphique à barres
Sur cette page
  1. Que mettre dans un rapport de ventes mensuel ?
  2. Comment préparer l'export de commandes ?
  3. Comment calculer le chiffre d'affaires et les commandes du mois ?
  4. Comment comparer avec le mois précédent et l'an dernier ?
  5. Comment montrer la tendance, les meilleurs produits et les canaux ?
  6. Comment réutiliser le rapport le mois suivant ?
  7. Où se place Glow Reports ?
  8. Questions fréquentes

Un rapport de ventes mensuel répond à quatre questions : combien vous avez vendu, en combien de commandes, comment ce mois se compare au précédent et au même mois l'an dernier, et ce qui explique l'écart. Dans Excel, un seul export de commandes suffit pour le construire, avec un tableau, quelques formules SOMME.SI.ENS et deux tableaux croisés dynamiques. Voici la structure, les formules à copier, les graphiques à ajouter et les contrôles qui gardent des chiffres justes quand l'export du mois suivant arrive.

Que mettre dans un rapport de ventes mensuel ?

Un rapport de ventes mensuel contient le chiffre d'affaires, le nombre de commandes, le panier moyen, l'évolution par rapport au mois précédent et au même mois de l'an dernier, puis la répartition par produit et par canal ou commercial. Six chiffres et deux ventilations couvrent l'essentiel des questions.

Voici une structure à reproduire sur une seule feuille. Chaque ligne correspond à un bloc du rapport.

BlocCe qu'il montreComment le construire
SynthèseChiffre d'affaires, commandes, panier moyenSOMME.SI.ENS et NB.SI.ENS
ÉvolutionPar rapport au mois précédent et à l'an dernierLes mêmes formules, décalées
TendanceChiffre d'affaires des 12 derniers moisTableau croisé et histogramme
Meilleurs produitsLes 10 produits au plus fort chiffre d'affairesTableau croisé, filtre 10 premiers
Par canal ou commercialChiffre d'affaires et commandes par canalTableau croisé et graphique à barres
CommentaireDeux ou trois phrases sur ce qui a changéRédigé par vous

Gardez les définitions à côté des chiffres. Le « chiffre d'affaires » peut s'entendre avant ou après remboursements, HT ou TTC, avec ou sans frais de port. Écrivez celle que vous retenez sous la synthèse, pour que le lecteur n'ait rien à deviner.

Comment préparer l'export de commandes ?

Placez l'export dans sa propre feuille, mettez-le sous forme de tableau Excel et vérifiez que les dates sont de vraies dates et les montants de vrais nombres. Un tableau s'agrandit avec les lignes collées plus tard, donc chaque formule et chaque tableau croisé construits dessus reprennent les données du mois suivant.

  1. Collez ou ouvrez l'export dans une feuille nommée . Une ligne par ligne de commande, un en-tête par colonne.
  2. Cliquez dans les données et sélectionnez InsertionTableau. Microsoft explique comment un tableau s'étend quand vous ajoutez des lignes Support Microsoft.
  3. Nommez-le dans Création de tableauNom du tableau. Les formules ci-dessous utilisent ce nom.
  4. Contrôlez la colonne de dates avec dans une colonne d'aide. FAUX signale une date stockée comme du texte.

Les exemples supposent les colonnes , , , et . Renommez-les dans les formules selon votre export. Les dates en texte, les espaces en trop et les nombres stockés comme du texte sont traités dans nettoyer vos données avant un rapport. Quand le mois arrive en plusieurs fichiers, fusionnez-les d'abord avec Power Query.

Comment calculer le chiffre d'affaires et les commandes du mois ?

Saisissez le premier jour du mois dans une cellule, par exemple , puis additionnez les montants entre cette date et la fin du mois avec SOMME.SI.ENS. NB.SI.ENS compte les lignes de la même façon. Chaque chiffre de la synthèse lit , donc changer une seule date met tout le rapport à jour.

Chiffre d'affaires du mois en B1
=SOMME.SI.ENS(Commandes[Montant]; Commandes[DateCommande]; ">=" & B1; Commandes[DateCommande]; "<" & FIN.MOIS(B1; 0) + 1)

renvoie le dernier jour du mois Support Microsoft. Ajouter 1 et utiliser plutôt que garde les commandes passées le dernier jour après minuit, qu'un horodatage comme 31/10 14:32 ferait sinon disparaître. SOMME.SI.ENS exige des plages de même taille, ce que garantit une colonne de tableau Support Microsoft.

Le nombre de commandes dépend de la forme de l'export. Quand chaque ligne est une commande, comptez les lignes :

Commandes du mois, une ligne par commande
=NB.SI.ENS(Commandes[DateCommande]; ">=" & B1; Commandes[DateCommande]; "<" & FIN.MOIS(B1; 0) + 1)

Beaucoup d'exports de boutique ont une ligne par produit : une commande de trois articles occupe trois lignes. L'export de commandes de Shopify fonctionne ainsi et laisse de nombreux champs vides sur les lignes supplémentaires Centre d'aide Shopify. Compter les lignes surestime alors les commandes, et un total de commande répété ou vide sur ces lignes fausse le chiffre d'affaires. Additionnez les montants des lignes, ou le total de la première ligne seulement. Dans Microsoft 365, comptez les identifiants de commande distincts :

Commandes du mois, une ligne par article
=LIGNES(UNIQUE(FILTRE(Commandes[IDCommande]; (Commandes[DateCommande] >= B1) * (Commandes[DateCommande] < FIN.MOIS(B1; 0) + 1))))

Le panier moyen se calcule en divisant le chiffre d'affaires par le nombre de commandes. Le détail, remboursements et commandes à zéro compris, est dans calculer le panier moyen.

Comment comparer avec le mois précédent et l'an dernier ?

Reprenez la formule du chiffre d'affaires avec une date de début décalée : pour le mois précédent et pour le même mois l'an dernier. Divisez ensuite l'écart par le chiffre le plus ancien. La comparaison sur un an compte le plus pour des ventes saisonnières.

Placez le début du mois précédent en avec et celui de l'an dernier en avec . Copiez les formules de chiffre d'affaires et de commandes à côté du mois en cours, en pointant vers et . L'évolution s'écrit alors :

Évolution par rapport au mois précédent
=SI(C5 = 0; ""; (B5 - C5) / C5)

Mettez la cellule au format pourcentage. Le laisse la cellule vide quand le mois précédent n'a eu aucune vente, au lieu d'une erreur #DIV/0!. Écrivez « vs mois précédent » et « vs même mois N-1 » dans les en-têtes, pour qu'un écart mensuel ne passe pas pour un écart annuel.

Comment montrer la tendance, les meilleurs produits et les canaux ?

Construisez des tableaux croisés dynamiques sur le tableau  : un avec les mois en lignes pour la tendance, un avec les produits triés par chiffre d'affaires, un avec les canaux ou les commerciaux. Ajoutez un graphique à la tendance et aux canaux, et un segment pour tout filtrer d'un clic.

  1. Sélectionnez InsertionTableau croisé dynamique avec comme source Support Microsoft. Glissez dans Lignes et dans Valeurs. Groupez les dates par Mois et Années, comme dans grouper un tableau croisé dynamique par mois.
  2. Pour les meilleurs produits, placez dans Lignes et dans Valeurs. Ouvrez la flèche de filtre de Produit et sélectionnez Filtres s'appliquant aux valeurs10 premiers.
  3. Pour les canaux, placez dans Lignes. Ajoutez et dans Valeurs.
  4. Cliquez dans un tableau croisé et sélectionnez InsertionSegment, puis cochez . Un segment filtre les tableaux et les tableaux croisés d'un clic et affiche le filtre actif Support Microsoft.

Côté graphiques, un histogramme convient au chiffre d'affaires par mois et un graphique à barres horizontales aux produits ou aux canaux, dont les libellés sont longs. Quel graphique Excel choisir détaille le bon graphique pour chaque chiffre. Limitez la tendance à 12 ou 13 mois pour garder le même mois de l'an dernier en vue.

Comment réutiliser le rapport le mois suivant ?

Collez les nouvelles lignes sous le tableau, changez la date en , puis sélectionnez DonnéesActualiser tout. Les formules passent au nouveau mois, et les tableaux croisés comme les graphiques reprennent les nouvelles lignes. Seul le commentaire est à réécrire.

Avant d'envoyer le rapport, faites trois contrôles :

  • Le chiffre d'affaires de la synthèse égale le total du mois dans le tableau croisé de tendance. Un écart signale un filtre actif ou des dates en texte.
  • La colonne d'aide n'affiche aucun FAUX sur les nouvelles lignes.
  • Le nombre de commandes correspond à celui du back-office de la boutique ou du CRM pour le même mois, aux remboursements et commandes test près.

La même construction fonctionne dans Google Sheets. InsertionTableau croisé dynamique crée les tableaux croisés Aide Éditeurs Google Docs, et SOMME.SI.ENS prend les mêmes arguments Aide Éditeurs Google Docs. Remplacez les noms de tableau par des plages de colonnes comme .

Où se place Glow Reports ?

Une fois construit, le classeur ci-dessus vous sert longtemps. Le coût revient chaque mois : coller l'export, contrôler les dates, réécrire le commentaire, exporter le PDF. Quand cette routine prend une bonne partie de la matinée, lisez remplacer les rapports Excel manuels.

Glow Reports part du même export. Vous importez le fichier CSV ou Excel et demandez le rapport de ventes mensuel en langage courant, avec votre définition du chiffre d'affaires. L'IA écrit le SQL, construit les graphiques et rédige le texte. Le SQL figure sous chaque chiffre : vous vérifiez comment les commandes ont été comptées et modifiez la requête si une définition ne convient pas. Le rapport s'exporte en PDF et reste dans l'espace de travail. L'offre gratuite donne 300 crédits une seule fois, et un rapport coûte 100 crédits.

Questions fréquentes

Existe-t-il un modèle gratuit de rapport de ventes mensuel pour Excel ?

La galerie de modèles de Microsoft propose des modèles de rapport de ventes. Ils aident pour la mise en page, mais il reste à y faire entrer votre export. La structure de cet article part de l'export, donc les formules collent à vos colonnes.

Faut-il compter le chiffre d'affaires HT ou TTC ?

Décidez une fois pour toutes et écrivez-le sous la synthèse. En France, on présente le plus souvent le chiffre d'affaires HT, après remboursements, avec les frais de port à part. L'essentiel est d'appliquer la même règle chaque mois.

Pourquoi SOMME.SI.ENS ne donne pas le même total que le tableau croisé ?

Le plus souvent à cause de dates stockées comme du texte, que SOMME.SI.ENS ignore, ou d'un filtre resté actif dans le tableau croisé. Cherchez les FAUX dans la colonne d'aide et effacez les filtres du tableau croisé.

Comment faire un rapport de ventes quotidien ?

Utilisez le même tableau. Remplacez les bornes du mois par une seule date : .

El Mehdi Benane
Fondateur, Glow Reports

Je suis ingénieur produit et fondateur de Glow Reports. J'ai passé plus de 6 ans à construire des produits dans la tech, les données et la cybersécurité, pour des grands groupes, des startups et des PME. Dans la plupart des organisations, j'ai vu les données traitées comme un sujet technique, alors qu'elles guident les décisions. J'ai construit l'outil de reporting que je cherchais, pour que les dirigeants des petites et moyennes structures lisent leurs chiffres sans équipe data.

Importez votre export CSV ou Excel dans Glow Reports et obtenez le rapport de ventes avec le SQL derrière chaque chiffre.

À lire aussi