Sans tableau de bord commercial, tu arrives à la réunion du vendredi sans savoir vraiment où en est l'équipe, entre les extractions du CRM, les copier-coller et les additions à la main qui laissent toujours un doute sur les chiffres. Dans cet exercice, on va voir ensemble comment centraliser les ventes et faire calculer tous les indicateurs en temps réel !
L'objectif est d'apprendre à partir d'une simple base de ventes pour en tirer les bons signaux, qui performe, qui convertit, qui approche de son objectif. Une fois ce réflexe acquis, tu pilotes une équipe de quelques vendeurs avec des chiffres fiables, sans payer un CRM par siège, et la méthode tient quel que soit le secteur.
Ce que tu vas construire
Structurer une base de ventes complète avec statuts (Gagné, En cours, Perdu) pour alimenter tous les indicateurs.- Calculer le chiffre d'affaires par commercial sur les ventes gagnées avec SOMME.SI.ENS.
- Mesurer le taux de conversion de chaque membre de l'équipe avec NB.SI.
- Classer automatiquement les commerciaux du meilleur au moins performant avec RANG.
- Afficher le statut d'atteinte d'objectif (Atteint, En voie, En retard) avec des formules SI imbriquées.
À connaître avant de commencer
- Savoir recopier une formule le long de ta base de ventes.
- Savoir figer une plage avec les $ pour qu'un classement RANG reste correct en le copiant.
- Connaître SOMME.SI.ENS au moins de nom : on l'utilise pas à pas pour croiser commercial et statut.
Voici les données de départ de cet exercice. Copie-les ou télécharge le fichier Excel, puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Commercial | Client | Montant | Statut |
| 2 | 05/01/2026 | Dupont | Acme Corp | 15 000 | Gagné |
| 3 | 12/01/2026 | Martin | Tech SA | 8 500 | En cours |
| 4 | 20/01/2026 | Dupont | PME Plus | 22 000 | Gagné |
| 5 | 25/01/2026 | Garcia | Groupe Alma | 6 200 | Perdu |
| 6 | 02/02/2026 | Martin | Innov SAS | 12 800 | Gagné |
| 7 | 08/02/2026 | Dupont | Retail Express | 9 400 | En cours |
| 8 | 14/02/2026 | Garcia | BTP Horizon | 31 000 | Gagné |
| 9 | 20/02/2026 | Martin | Cabinet Leblanc | 4 700 | Perdu |
| 10 | 01/03/2026 | Garcia | Logistik Pro | 18 500 | En cours |
| 11 | 10/03/2026 | Dupont | Santé Plus | 7 300 | Gagné |
Exercice guidé
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Utilise SOMME.SI.ENS pour totaliser les ventes gagnées par commercial. Le premier argument est la plage de montants, les suivants sont les critères (nom du commercial + statut 'Gagné').
=SOMME.SI.ENS(D:D;B:B;"Dupont";E:E;"Gagné")Résultat attendu : Le CA total des affaires gagnées par Dupont (ex: 37 000 euros).
Attention
- SOMME.SI.ENS est différent de SOMME.SI : l'ordre des arguments change (plage de somme en premier). Ne les confonds pas.
Astuces pour aller plus loin
Sépare SOMME.SI et SOMME.SI.ENS
SOMME.SI.ENS est l'outil pour croiser plusieurs critères (commercial + statut). Attention : l'ordre des arguments est inversé par rapport à SOMME.SI. SOMME.SI.ENS commence par la plage à sommer, SOMME.SI la met en dernier. Ce piège fait planter presque tout le monde la première fois.
Gère les ex-aequo dans le classement RANG
Si deux commerciaux ont exactement le même CA, RANG leur donne le même rang et saute le rang suivant : deux personnes en 2e position, personne en 3e. Si tu as besoin d'un classement strict, ajoute un critère de départage (nombre de ventes, ancienneté) et utilise une formule RANG + NB.SI pour décaler les ex-aequo.
Ajoute une analyse trimestrielle
Calcule le trimestre de chaque vente avec =ARRONDI.SUP(MOIS(A2)/3;0) dans une colonne auxiliaire. Tu peux ensuite filtrer par trimestre avec SOMME.SI.ENS pour comparer T1 vs T2 et détecter les tendances. Un tableau croisé dynamique sur cette même base te donne la même analyse en quelques clics.
Questions fréquentes
Crée une base de ventes avec Date, Commercial, Montant et Statut. Utilise SOMME.SI.ENS pour le CA par commercial, NB.SI pour le taux de conversion, RANG pour le classement, et SI imbriqués pour l'atteinte d'objectif. Le fichier corrigé de cet exercice est téléchargeable.
Le fichier corrigé de cet exercice est disponible en téléchargement en haut de page. Il contient la base de ventes, les formules SOMME.SI.ENS, le classement RANG et les indicateurs d'objectif déjà configurés. Remplace les noms et les montants par tes propres données.
SOMME.SI.ENS avec deux critères : =SOMME.SI.ENS(D:D;B:B;"Dupont";E:E;"Gagné") totalise les montants dont le commercial est Dupont ET le statut est Gagné. Attention à l'ordre des arguments : la plage à sommer vient en premier, contrairement à SOMME.SI.
Divise le nombre d'affaires gagnées par le nombre total d'opportunités : =NB.SI(E2:E500;"Gagné")/NBVAL(E2:E500). Formate la cellule en pourcentage. Pour le taux par commercial, ajoute un deuxième critère avec NB.SI.ENS.
Utilise RANG : =RANG(B2;$B$2:$B$10) renvoie la position du commercial dans l'équipe. La plage doit être en référence absolue avec les $ pour rester fixe quand tu copies la formule vers le bas. Le classement se met à jour automatiquement quand les montants changent.
Utilise des SI imbriqués : =SI(B2>=Objectif;"Atteint";SI(B2>=Objectif*0.8;"En voie";"En retard")). Objectif doit être une plage nommée ou une référence absolue. Un graphique à barres groupées (objectif vs réalisé) complète l'analyse visuellement.
3 exercices similaires au tableau de bord commercial
Analyse des ventes par région
Construire un tableau d'analyse des ventes par région pour identifier tes zones fortes, tes zones en retard et prioriser tes efforts commerciaux.
Voir l'exercice
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
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
Envie de t'entraîner plus ?
Des dizaines de cas pratiques Excel corrigés pour progresser pour de vrai.
Voir tous les exercices