Tableau de bord commercialCas pratique Excel pas à pas
Le contexte
Crée un tableau de bord commercial complet pour suivre tes ventes, ton pipeline et tes objectifs avec des indicateurs visuels.
Les données de départ
Crée un tableau avec toutes les ventes : date, commercial, client, produit, montant, statut (gagne/perdu/en cours).
| 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é |
Solution pas à pas
2Calculer le CA par commercial
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.
3Calculer le taux de conversion
Divise le nombre de ventes gagnées par le nombre total d'opportunités. Utilise NB.SI pour compter chaque statut. Formate la cellule en pourcentage pour un affichage lisible.
=NB.SI(E:E;"Gagné")/NB.SI(E:E;"<>")Résultat attendu : Le taux de conversion en décimal (ex: 0,67 soit 67% si 2 ventes gagnées sur 3 opportunités).
Attention
- Le critère "<>" compte toutes les cellules non vides, y compris les en-têtes. Commence ta plage à E2 (pas E:E) pour exclure l'en-tête.
4Classer les commerciaux
Utilise RANG pour classer les commerciaux par CA généré. Affiche le top 3 dans le dashboard. Le classement se met à jour automatiquement quand les montants changent.
=RANG(B2;$B$2:$B$10)Résultat attendu : Le rang du commercial dans l'équipe (ex: 1 pour le meilleur vendeur, 2 pour le deuxième, etc.).
Attention
- Si deux commerciaux ont exactement le même CA, ils obtiennent le même rang et le rang suivant est sauté. Par exemple, deux commerciaux à la deuxième place, et personne à la troisième. Gère les ex-aequo explicitement si c'est un problème dans ton équipe.
- La plage $B$2:$B$10 doit être en référence absolue (avec les $) pour que la formule reste correcte quand tu la copies vers le bas.
5Suivre l'atteinte des objectifs
Utilise SI imbriqué pour afficher un statut visuel : objectif atteint, en bonne voie ou en retard. Le seuil de 80% sert de zone intermédiaire pour les commerciaux qui s'en approchent.
=SI(B2>=Objectif;"Atteint";SI(B2>=Objectif*0.8;"En voie";"En retard"))Résultat attendu : "Atteint" si le CA dépasse l'objectif, "En voie" entre 80% et 100%, "En retard" en dessous de 80%.
Attention
- "Objectif" doit être une plage nommée ou une référence absolue (ex: $C$1). Si tu le renommes ou déplaces la cellule, toutes les formules qui y font référence se cassent.
6Ajouter les stats globales
Calcule le panier moyen avec MOYENNE, la plus grosse vente avec MAX et la plus petite avec MIN. La formule matricielle MOYENNE(SI(...)) permet de ne moyenner que les ventes gagnées. Valide avec Ctrl+Shift+Entrée si tu n'es pas en Microsoft 365.
=MOYENNE(SI(E2:E500="Gagné";D2:D500))Résultat attendu : Le montant moyen des affaires gagnées (ex: 15 166 euros).
Attention
- Cette formule est matricielle. Dans les versions d'Excel avant Microsoft 365, tu dois valider avec Ctrl+Shift+Entrée. Sinon elle renvoie une erreur ou un mauvais résultat.
Questions fréquentes
Divise le nombre d'affaires gagnées par le nombre total d'opportunités. =NB.SI(Statut;"Gagné")/NBVAL(Statut). Multiplie par 100 pour avoir un pourcentage. Un bon taux de conversion B2B est entre 15% et 30%.
Ajoute une colonne 'Étape' avec les valeurs : Prospection, Qualification, Proposition, Négociation, Gagné, Perdu. Utilise NB.SI pour compter les affaires à chaque étape et SOMME.SI pour totaliser les montants. Tu obtiens un entonnoir de vente.
Un graphique à barres groupées fonctionne bien : une barre pour l'objectif, une pour le réalisé, par commercial. Ajoute une ligne de seuil à 100% pour repérer immédiatement qui est en retard.
Crée une ligne par produit vendu, avec un identifiant de commande commun. Utilise SOMME.SI pour totaliser par commande. Pour le dashboard, agrège par commande (pas par ligne de produit) pour éviter de gonfler le nombre de ventes.
Oui. Ajoute une colonne 'Trimestre' calculée avec =ARRONDI.SUP(MOIS(A2)/3;0). Puis utilise SOMME.SI.ENS pour filtrer par trimestre et par commercial. Tu peux ensuite créer un tableau croisé dynamique pour comparer.
Ajoute un identifiant unique par affaire (ex: VTE-001, VTE-002). Utilise la mise en forme conditionnelle pour surligner les doublons : Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance > Valeurs en double.
Cas pratiques similaires
Analyse des ventes par région
Construis un tableau d'analyse des ventes par région pour identifier tes zones fortes, tes zones en retard et prioriser tes efforts commerciaux.
Pipeline commercial CRM
Construis 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.
Comparaisons liées
RECHERCHEV vs RECHERCHEX
RECHERCHEX remplace RECHERCHEV mais n'est pas disponible partout.
INDEX vs RECHERCHEV
INDEX/EQUIV est plus flexible que RECHERCHEV, mais plus complexe à écrire.
SI vs SI.CONDITIONS
SI.CONDITIONS remplace les SI imbriqués par une syntaxe plus lisible.
NB.SI vs NB.SI.ENS
NB.SI.ENS gère plusieurs critères là où NB.SI n'en accepte qu'un seul.
SOMME.SI vs SOMME.SI.ENS
SOMME.SI.ENS permet de sommer avec plusieurs critères simultanément.
SOMME.SI vs NB.SI
SOMME.SI additionne des valeurs selon un critère, NB.SI se contente de les compter.
CONCATENER vs CONCAT
CONCAT remplace CONCATENER avec en bonus la prise en charge des plages.
CONCATENER vs JOINDRE.TEXTE
JOINDRE.TEXTE fait tout ce que CONCATENER fait, avec un séparateur intégré et la gestion des vides.
MOYENNE vs MOYENNE.SI
MOYENNE calcule la moyenne de tout, MOYENNE.SI ne prend que les valeurs qui correspondent à un critère.
GRANDE.VALEUR vs MAX
MAX renvoie le plus grand nombre, GRANDE.VALEUR renvoie le Nième plus grand.
EXACT vs SI
EXACT compare deux textes en respectant la casse, SI teste une condition et renvoie un résultat.
NB.SI vs FILTRE
NB.SI compte les lignes qui matchent, FILTRE les extrait toutes dans un tableau.
Envie de pratiquer plus ?
Rejoins Le Dojo Club pour accéder à des dizaines de cas pratiques corrigés, des formations complètes et une communauté de pros qui s'entraident.
Essayer pendant 30 jours