Aller au contenu principal
Le DojoLe Dojo
CommerciauxIntermédiaire45 min

Tableau de bord commercial sur Excel

Un exercice corrigé pour bâtir un tableau de bord commercial Excel, calculer le CA par vendeur avec SOMME.SI.ENS, le taux de conversion et le classement, avec le fichier Excel à télécharger.

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.

Dans la vraie vie
Sans tableau de bord, 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. Ici, une simple base de ventes alimente tous les indicateurs, et tu pilotes quelques vendeurs avec des chiffres fiables sans payer un CRM par siège.

Ce que tu vas construire

  • Construire un tableau récapitulatif par commercial à partir d'une base de ventes avec statuts (Gagné, En cours, Perdu).
  • Calculer le chiffre d'affaires gagné de chaque commercial avec SOMME.SI.ENS.
  • Mesurer le taux de conversion de chaque membre de l'équipe avec NB.SI.ENS et 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 SI imbriqués.
  • Calculer le panier moyen des ventes gagnées de chaque commercial avec MOYENNE.SI.ENS.

À connaître avant de commencer

  • Savoir recopier une formule le long d'une colonne.
  • 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, réparties sur 2 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é.

ABCDEFG
1CommercialObjectifCA gagnéTaux de conversionRangStatut objectifPanier moyen
2Dupont40 000
3Martin30 000
4Garcia35 000
5Bernard30 000
6Moreau20 000

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
Calculer le CA par commercialDans l'onglet Tableau de bord, en colonne C (CA gagné), calcule pour chaque commercial le total des montants de ses ventes au statut Gagné.
2
Calculer le taux de conversionToujours dans l'onglet Tableau de bord, en colonne D (Taux de conversion), divise pour chaque commercial son nombre de ventes gagnées par son nombre total d'opportunités, tous statuts confondus.
3
Classer les commerciauxDans l'onglet Tableau de bord, en colonne E (Rang), classe les commerciaux du plus gros au plus petit CA gagné, le rang 1 revenant au meilleur.
4
Suivre l'atteinte des objectifsDans l'onglet Tableau de bord, en colonne F (Statut objectif), affiche Atteint quand le CA gagné atteint ou dépasse l'objectif, En voie quand il en représente au moins 80 %, et En retard en dessous.
5
Calculer le panier moyenDans l'onglet Tableau de bord, en colonne G (Panier moyen), calcule pour chaque commercial le montant moyen de ses ventes gagnées, arrondi à l'euro.
Le savais-tu ?
SOMME.SI.ENS attend la plage à additionner en premier, alors que SOMME.SI la met en dernier. L'ordre des arguments est inversé entre les deux fonctions, et c'est le piège qui fait trébucher presque tout le monde en passant de l'une à l'autre. MOYENNE.SI.ENS, MAX.SI.ENS et MIN.SI.ENS suivent la même règle que SOMME.SI.ENS.

Astuces pour aller plus loin

Ajoute la plus grosse et la plus petite affaire

MAX.SI.ENS et MIN.SI.ENS s'écrivent comme SOMME.SI.ENS, avec la plage à examiner en premier. =MAX.SI.ENS(Ventes!$D:$D;Ventes!$B:$B;A2;Ventes!$E:$E;"Gagné") renvoie 22 000 euros pour Dupont, et MIN.SI.ENS avec les mêmes arguments renvoie 7 300 euros. Garde bien le critère Gagné, car MIN seule sur toute la base, =MIN(Ventes!D2:D16), renvoie 4 700 euros, une affaire que Martin a perdue.

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 suivant. Avec 44 300, 31 000, 31 000 et 20 000 euros, on obtient 1, 2, 2 et 4, et personne n'est 3e. Pour un classement strict, =RANG(C2;$C$2:$C$6)+NB.SI($C$2:C2;C2)-1 départage les ex-aequo dans l'ordre de la liste, le premier des deux garde le rang 2 et le second prend le 3.

Ajoute une analyse trimestrielle

Pour comparer les trimestres, ajoute à l'onglet Ventes une colonne Trimestre avec =ARRONDI.SUP(MOIS(A2)/3;0), qui renvoie 1 pour une vente de janvier à mars et 2 pour une vente d'avril à juin. SOMME.SI.ENS peut ensuite filtrer sur cette colonne pour mettre le T1 face au T2. Un tableau croisé dynamique sur la même base donne cette analyse sans écrire de formule.

S'entraîner sur 3 exercices similaires au tableau de bord commercial

01

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

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

Pars d'une base de ventes, avec la date, le commercial, le client, le montant et le statut, et d'un tableau récapitulatif qui porte une ligne par commercial avec son objectif. SOMME.SI.ENS y calcule le CA gagné, NB.SI.ENS et NB.SI le taux de conversion, RANG le classement, et deux SI imbriqués le statut d'objectif. Chaque formule est détaillée pas à pas dans le corrigé de cet exercice.

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