Aller au contenu principal
Le DojoLe Dojo
CommerciauxIntermédiaire40 min

Analyse des ventes par région sur Excel

Un exercice corrigé pour réussir ton analyse des ventes par région avec SOMME.SI.ENS, classer les zones et calculer leur part du chiffre d'affaires, avec le fichier Excel à télécharger.

Quand une force commerciale est répartie sur plusieurs régions, comparer les performances à la main vire vite au casse-tête, et un total recopié de travers fausse toute la revue. Dans cet exercice, on va voir ensemble comment confier cette agrégation à Excel, région par région et période par période.

L'objectif est de bâtir une vue qui totalise, classe et pondère les zones toute seule, pour repérer d'un coup d'œil qui porte le chiffre d'affaires et qui décroche. Une fois ce réflexe d'agrégation par critères acquis, il ressert sur n'importe quel découpage, par commercial, par produit ou par trimestre, bien au-delà des régions !

Dans la vraie vie
En revue commerciale, la région qui signe le plus de contrats n'est pas toujours celle qui rapporte le plus. Sur le premier trimestre du journal de l'exercice, l'Île-de-France conclut 5 ventes pour 15 000 euros, quand l'Occitanie en signe 2 pour 16 000 euros. Seul un récapitulatif qui croise CA, nombre de ventes et panier moyen le montre d'un coup d'œil.

Ce que tu vas construire

  • Totaliser le chiffre d'affaires de chaque région sur un trimestre avec SOMME.SI.ENS, en lisant les dates de début et de fin dans deux cellules.
  • Compter le nombre de ventes de chaque région sur la même période avec NB.SI.ENS.
  • Calculer le panier moyen régional sans erreur pour une région qui n'a rien vendu, grâce à SIERREUR.
  • Classer les régions automatiquement du plus gros au plus petit CA avec RANG.
  • Calculer la part de chaque région dans le chiffre d'affaires de la période.
  • Relancer toute l'analyse sur un autre trimestre en changeant seulement les deux dates de l'onglet Période.

À connaître avant de commencer

  • Savoir recopier une formule vers le bas le long d'un tableau.
  • Savoir figer une plage avec les $ pour qu'un total ou un classement reste juste quand tu le recopies.
  • Connaître SOMME.SI.ENS au moins de nom. On la déroule pas à pas pour filtrer par région et par période.

Voici les données de départ, réparties sur 3 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.

ABCDEF
1RégionCANb ventesPanier moyenRangPart du CA
2Île-de-France
3Auvergne-Rhône-Alpes
4Nouvelle-Aquitaine
5Occitanie
6Hauts-de-France

Résoudre l'exercice étape par étape

Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.

Ta progression
0/5
1
Totaliser le CA par région sur la périodeDans l'onglet Analyse régionale, en colonne B (CA), totalise pour chaque région le montant des ventes dont la date tombe entre le début et la fin de la période, bornes comprises.
2
Compter les ventes par régionDans l'onglet Analyse régionale, en colonne C (Nb ventes), compte pour chaque région le nombre de ventes conclues sur la même période.
3
Calculer le panier moyenDans l'onglet Analyse régionale, en colonne D (Panier moyen), divise le CA de chaque région par son nombre de ventes, en affichant 0 pour une région qui n'a rien vendu.
4
Classer les régionsDans l'onglet Analyse régionale, en colonne E (Rang), classe les régions selon leur CA, la plus forte prenant le rang 1.
5
Calculer la part de chaque régionDans l'onglet Analyse régionale, en colonne F (Part du CA), calcule le poids de chaque région dans le CA total de la période, puis affiche la colonne au format pourcentage.
Le défi
Une fois l'analyse en place, passe l'onglet Période au deuxième trimestre, du 01/04/2026 au 30/06/2026, sans toucher à une seule formule. Tout se recalcule. Auvergne-Rhône-Alpes prend alors la tête grâce à un contrat de 12 000 euros, la Nouvelle-Aquitaine et les Hauts-de-France partagent le 2e rang avec 4 000 euros chacune, et l'Île-de-France tombe au 5e rang. Le même réflexe se transpose ensuite à un découpage par commercial ou par produit.

Astuces pour aller plus loin

Transpose la même analyse par commercial ou par produit

Le tableau ne tient qu'à la plage que compare SOMME.SI.ENS. Écris les noms des commerciaux en colonne A à la place des régions, remplace Ventes!$C:$C par Ventes!$B:$B dans les formules des colonnes B et C, et l'analyse se refait par vendeur, 12 000 euros pour Dupont sur le trimestre avec ses trois Pack Pro. Avec Ventes!$D:$D et les quatre produits en colonne A, le Pack Pro pèse à lui seul 28 000 euros sur les 50 000 du trimestre. Le panier moyen ne change pas, et le rang comme la part suivent dès que leur plage $B$2:$B$6 couvre toutes les lignes.

Contrôle que tes cinq régions couvrent tout le journal

Pose sous le tableau =SOMME.SI.ENS(Ventes!$E:$E;Ventes!$A:$A;">="&Période!$A$2;Ventes!$A:$A;"<="&Période!$B$2), le CA de la période sans critère de région, et compare-le à =SOMME(B2:B6). Les deux doivent afficher 50 000 euros. Si une vente du journal est classée dans une région absente de ta liste, ou écrite autrement, le premier total la garde et le second l'ignore, et l'écart te dit combien d'euros ne sont rattachés à aucune ligne de l'analyse.

Colore les régions qui décrochent

Sélectionne A2:F6, puis Accueil > Mise en forme conditionnelle > Nouvelle règle, « Utiliser une formule », et écris =$F2<0,15 avec un remplissage orange. Les régions sous 15 % du chiffre d'affaires de la période ressortent sur toute leur ligne, Auvergne-Rhône-Alpes à 14 % et les Hauts-de-France à 0 % sur le premier trimestre. Le $ devant F garde la colonne de la part quand la règle parcourt les six colonnes, et le seuil se change dans la règle sans toucher au tableau.

S'entraîner sur 3 exercices similaires à l'analyse des ventes par région

01

Tableau de bord commercial

Construire un tableau de bord commercial pour suivre le CA, le taux de conversion, le classement et l'atteinte des objectifs de chaque vendeur.
Voir l'exercice

02

Pipeline commercial CRM

Construire un pipeline commercial dans Excel pour suivre tes opportunités de la prospection à la signature, calculer la valeur pondérée et prévoir ton CA.
Voir l'exercice

03

Retrouver un prix avec RECHERCHEV

Utiliser RECHERCHEV pour relier une commande à un catalogue produit : retrouver le nom, le prix et gérer les codes introuvables.
Voir l'exercice

Tu as peut-être encore une question

Pose la liste des régions dans un tableau récapitulatif, puis calcule le CA de chacune avec SOMME.SI.ENS, en combinant un critère sur la région et deux critères sur la date. Si ces deux dates vivent dans des cellules, comme dans l'onglet Période de l'exercice, il suffit de les changer pour relancer toute l'analyse sur un autre trimestre. NB.SI.ENS, RANG et une simple division complètent ensuite le tableau avec le nombre de ventes, le classement et la part de chaque région.

Pour aller plus loin

Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.

Besoin d’aide sur Excel ?

Rejoins le Dojo pour poser tes questions, obtenir de l’aide sur tes fichiers Excel et participer chaque mois à un cours collectif en direct.

Rejoindre gratuitement

Gratuit · Sans carte bancaire