Suivi des ventes sur Excel : tableau de saisie, chiffre d’affaires par mois et par canal, panier moyen et objectifs
Mettre en place un suivi des ventes sur Excel : table de saisie des commandes avec HT, TVA et TTC, synthèse mensuelle par formules ou tableau croisé dynamique, chiffre d'affaires par canal et par produit, panier moyen, commissions de marketplace, suivi des objectifs et exemple d'une boutique en ligne sur un trimestre.
Réponse rapide : un bon suivi des ventes sur Excel repose sur une seule table de saisie, une ligne par commande ou par ligne de facture : date, n° de commande, canal, client, produit, quantité, prix unitaire HT, remise, taux de TVA. Tout le reste se calcule : chiffre d’affaires HT et TTC, synthèse par mois, répartition par canal et par produit, panier moyen et atteinte des objectifs. Formule de base d’une synthèse :
=SOMME.SI.ENS(Ventes[Montant HT];Ventes[Mois];A2;Ventes[Canal];B$1)
Ce guide présente la structure, les formules en français et un exemple chiffré d’une petite boutique en ligne. Les chiffres sont fictifs.
Étape 1 : la table de saisie
| Colonne | Contenu | Formule ou conseil |
|---|---|---|
| Date | Date de la vente | Validation : date |
| Mois | Pour les regroupements | =FIN.MOIS([@Date];-1)+1 (1er jour du mois) |
| N° commande / facture | Identifiant unique | Évite les doublons |
| Canal | Site, marketplace, boutique, salon | Liste déroulante |
| Client | Nom ou code client | |
| Produit / référence | Depuis l’onglet Produits | Liste déroulante |
| Quantité, PU HT, Remise % | Saisie | |
| Montant HT | Calculé | =[@Quantité]*[@[PU HT]]*(1-[@Remise]) |
| Taux TVA | Selon le produit | =RECHERCHEX([@Produit];Produits[Réf];Produits[TVA]) |
| TVA et TTC | Calculés | =[@[Montant HT]]*[@[Taux TVA]] et =[@[Montant HT]]+[@TVA] |
| Statut | Payée, expédiée, retournée, annulée | Pour exclure les annulations des totaux |
Transformez la plage en tableau (Ctrl+L) nommé Ventes : les formules se recopient seules et les synthèses s’étendent automatiquement. Le taux de TVA dépend de la nature du produit ou du service ; renseignez-le dans l’onglet Produits plutôt que de le saisir à chaque vente. Si vous êtes en franchise en base de TVA, mettez 0 % et la mention adaptée sur vos factures.
Étape 2 : la synthèse mensuelle
Deux méthodes :
- Formules : CA HT du mois
=SOMME.SI.ENS(Ventes[Montant HT];Ventes[Mois];A2;Ventes[Statut];"<>Annulée"); nombre de commandes=NB.SI.ENS(Ventes[Mois];A2;Ventes[Statut];"<>Annulée")si une ligne = une commande, ou=NBVAL(UNIQUE(FILTRE(Ventes[N° commande];Ventes[Mois]=A2)))si une commande peut avoir plusieurs lignes (Excel 365). - Tableau croisé dynamique : Insertion → Tableau croisé dynamique, Mois en lignes, Canal en colonnes, Somme de Montant HT en valeurs, et un segment sur le Statut.
Exemple : boutique en ligne de cosmétiques naturels, premier trimestre
| Mois | Commandes | CA HT | Panier moyen HT | Objectif | Atteinte |
|---|---|---|---|---|---|
| Janvier | 312 | 14 040 € | 45,00 € | 14 000 € | 100,3 % |
| Février | 280 | 12 880 € | 46,00 € | 14 000 € | 92,0 % |
| Mars | 355 | 17 040 € | 48,00 € | 16 000 € | 106,5 % |
| Trimestre | 947 | 43 960 € | 46,42 € | 44 000 € | 99,9 % |
Formules : panier moyen =C2/B2 ; atteinte =C2/E2 ; évolution mensuelle =C4/C3-1 (+32,3 % de février à mars). Le panier moyen trimestriel se calcule sur les totaux (43 960 ÷ 947), pas en faisant la moyenne des trois paniers mensuels.
Étape 3 : analyse par canal
| Canal (mars) | CA HT | Part | Commission (hypothèse) | CA net de commission |
|---|---|---|---|---|
| Site e-commerce | 9 940 € | 58,3 % | frais de paiement à part | — |
| Marketplace | 5 100 € | 29,9 % | 15 % | 4 335 € |
| Salon / boutique éphémère | 2 000 € | 11,7 % | — | — |
| Total | 17 040 € | 100 % |
Le chiffre d’affaires ne dit pas tout : un canal qui pèse 30 % des ventes mais prélève une commission importante contribue moins à la marge. Ajoutez une colonne « Coût du canal » (commission, frais d’expédition offerts, frais de paiement) dans la table de saisie ou dans l’onglet Canaux, puis calculez la marge par canal. Pour la marge par produit, voir le calcul du taux de marge.
Étape 4 : produits, clients et retours
- Top produits :
=TRIER(...)sur un récapitulatif par référence, ou un TCD trié par CA décroissant. Une analyse ABC (80 % / 15 % / 5 %) montre les quelques références qui font l’essentiel du chiffre. - Clients fidèles : nombre de commandes par client avec
=NB.SI(Ventes[Client];A2); part du CA réalisée par les clients ayant commandé plus d’une fois. - Taux de retour :
=NB.SI.ENS(Ventes[Statut];"Retournée";Ventes[Mois];A2)/NB.SI.ENS(Ventes[Mois];A2). Un taux qui grimpe sur une référence signale un problème de produit ou de description.
Les quantités vendues alimentent directement votre gestion de stock sur Excel : reliez les deux fichiers par la référence produit.
Étape 5 : tableau de bord et objectifs
Sur un onglet de synthèse, regroupez : CA du mois et cumul annuel, atteinte de l’objectif avec une jauge (barre de données), évolution par rapport au mois précédent et à l’an dernier, panier moyen, répartition par canal en graphique en anneau, et top 5 des produits. Les indicateurs clés peuvent rejoindre votre tableau de bord KPI.
Pour une prévision simple du mois suivant, partez de la moyenne des trois derniers mois ajustée de la saisonnalité connue (soldes, fêtes) ; une fonction comme PREVISION.ETS peut aider si vous avez au moins deux ans d’historique.
Bonnes pratiques et obligations
- Saisissez les ventes au fil de l’eau ou importez l’export de votre plateforme e-commerce chaque semaine.
- Rapprochez le CA du fichier avec les encaissements bancaires et les reversements des marketplaces.
- Le fichier de suivi ne remplace pas les obligations comptables : les micro-entrepreneurs tiennent un livre des recettes, les sociétés une comptabilité complète, et les factures doivent comporter les mentions obligatoires.
- Protégez les colonnes calculées pour éviter les erreurs de saisie.
Erreurs fréquentes
- Un onglet par mois : les analyses annuelles deviennent laborieuses.
- Mélanger HT et TTC dans les totaux.
- Compter les commandes annulées dans le CA.
- Moyenne de moyennes pour le panier moyen.
- Oublier les commissions en comparant les canaux.
Questions fréquentes
Quelle différence entre suivi des ventes et facturation ?
La facturation produit les documents légaux pour chaque client ; le suivi des ventes agrège ces données pour piloter l’activité. Vous pouvez saisir le numéro de facture dans la table de suivi pour faire le lien.
À partir de quand passer à un logiciel ?
Quand les commandes se comptent en milliers par mois ou quand plusieurs canaux doivent se synchroniser en temps réel avec le stock, un outil connecté à votre boutique devient plus fiable.
Est-ce compatible avec Google Sheets ?
Oui : SOMME.SI.ENS, NB.SI.ENS, RECHERCHEX, FIN.MOIS, FILTRE et UNIQUE existent en français dans Google Sheets.
Plus de ressources dans Vente en ligne et gestion de stock.