Excel séance 3 : graphiques et tableau de bord commercial

Le graphique se déduit de la question, et le tableau de bord tient sur un écran

À lire une fois, avant le premier atelier

Ce guide se suit comme celui de la séance 2 : les mots entre guillemets sont les libellés que vous cherchez à l’écran, le mot « puis » sépare deux clics, les étapes sont écrites pour Excel sur Windows, et chaque guide se termine par ce qui change sur Excel pour Mac et sur Google Sheets, par ce qu’il faut faire si ça ne marche pas, et par le chiffre qui prouve que c’est réussi. Si vous êtes à l’aise, allez directement à la consigne de chaque atelier.

Le fichier est le même qu’à la séance 2, kertavel-ventes-propre.xlsx. Si vous avez gardé votre classeur avec ses tableaux croisés, ouvrez-le ; sinon, le guide 3A vous fait reconstruire en dix minutes les trois croisés dont la séance a besoin. Personne n’est en retard.

Le périmètre du jour ne change pas de toute la séance : l’année 2025, hors commandes annulées, soit 619 667,13 € sur 854 lignes. C’est la seule année complète du fichier, et les objectifs des commerciaux portent sur elle. Ce chiffre est votre contrôle : chaque graphique, chaque indicateur doit retomber dessus.

L’assistant sert trois fois aujourd’hui : pour la formule qui rapatrie l’objectif de chaque commercial, pour la formule qui compte les commandes distinctes, et pour le titre d’un graphique quand le vôtre ne dit rien. Les prompts sont dans la bibliothèque remise à la séance 2, et le prompt 2.3, le chemin clic par clic pour votre tableur, reste la première réponse à « je suis bloqué ».

Le problème du jour

Vous avez remis, la dernière fois, une note d’une page avec trois constats. Votre direction l’a lue. Elle vous répond :

« C’est juste, mais je n’ai pas le temps de lire ça tous les mois. Faites-moi un tableau de bord. Une page, que je regarde en deux minutes, et qui me dit si ça va ou si ça ne va pas. »

Une note explique. Un tableau de bord alerte. Son lecteur ne cherche pas à comprendre, il cherche à savoir où regarder. S’il doit réfléchir plus de quelques secondes pour savoir si la situation est bonne ou mauvaise, le tableau de bord a échoué.

À la fin de la séance, vous saurez déduire le graphique de la question posée, construire un graphique juste depuis un tableau croisé avec un titre qui énonce un constat, repérer un graphique qui ment, et mettre trois indicateurs sur un écran avec des seuils qui ont un sens.

Guide 3A. Le fichier et ses trois croisés

À quoi ça sert. Un graphique ne se construit jamais sur 1 361 lignes brutes : il se construit sur une synthèse. Les trois tableaux croisés de ce guide sont la matière de tous les graphiques de la séance.

Ce qu’il vous faut. kertavel-ventes-propre.xlsx, le guide 2B de la séance 2 si le geste du tableau croisé vous a échappé, et dix minutes.

A. Le mensuel 2025

1. Ouvrez kertavel-ventes-propre.xlsx, ou votre copie de la séance 2. « Fichier » puis « Enregistrer sous », avec vos initiales et la mention s3.
2. Cliquez A1 sur l’onglet « Ventes », « Insertion » puis « Tableau croisé dynamique », « Nouvelle feuille de calcul », « OK ». Nommez la feuille Mensuel 2025.
3. « Date » dans « Lignes », « Montant » dans « Valeurs ». Si Excel n’a pas groupé tout seul : clic droit sur une date du tableau, « Grouper », cochez « Mois » et « Années », « OK ».
4. Faites glisser « Années » de « Lignes » vers « Filtres ». Dans le menu déroulant apparu au-dessus du tableau, choisissez « 2025 », « OK ».
5. « Statut » dans « Filtres » aussi. Cliquez sa flèche, cochez « Sélectionner plusieurs éléments », décochez « Annulée », « OK ».
6. Le « Total général » affiche 619 667,13 €. Douze lignes, de janvier à décembre.

B. Les familles 2025, et les commerciaux 2025

1. Nouvelle feuille, nouveau tableau croisé, nommez-la Familles 2025. « Famille » dans « Lignes », « Montant » dans « Valeurs », puis les deux mêmes filtres qu’aux étapes 4 et 5 : « Années » sur 2025, « Statut » sans « Annulée ». Même total.
2. Encore une, nommée Commerciaux 2025 : « Commercial » dans « Lignes », « Montant » dans « Valeurs », les deux filtres. Clic droit sur un montant, « Trier », « Trier du plus grand au plus petit ». Même total.

Le filtre par année se pose sur le champ « Années » que le regroupement a créé. Si votre tableau n’a pas de champ « Années », c’est que les dates n’ont pas été groupées : reprenez l’étape 3.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Trois feuilles nommées, trois tableaux croisés qui affichent chacun 619 667,13 € de total général. Le mensuel a douze lignes, avril à 80 890 € en tête ; les familles ont Équipement cuisine à 296 601,22 € ; les commerciaux ont L. Peyre à 152 596,64 € devant A. Bonnet à 149 846,58 €.

Guide 3B. Le graphique se déduit de la question

À quoi ça sert. À ne plus jamais choisir un graphique parce qu’il est joli. On le choisit parce que la question posée a une forme, et cinq formes couvrent l’immense majorité des besoins d’un tableau de bord commercial.

Les barres sont l’outil par défaut. L’œil compare des longueurs avec précision ; il ne sait comparer ni des angles ni des surfaces. Deux réglages changent tout : trier par valeur décroissante, sans quoi le lecteur fait le classement lui-même ; et préférer les barres horizontales quand les libellés sont longs, « Provence-Alpes-Côte d’Azur » à la verticale est illisible.

Les secteurs ont une seule utilité : montrer qu’une part domine un ensemble. Sur les cinq familles de produits, le message est fort, Équipement cuisine pèse 47,9 % de 2025, un demi-cercle, c’est immédiat. Sur vingt clients, ce serait une roue de couleurs illisible : pour les clients, des barres triées.

Les courbes, pour le temps et rien d’autre. Une courbe suppose une continuité entre les points. Douze mois se suivent ; entre M. Faure et S. Leclerc, il n’existe rien, et relier leurs chiffres par une ligne signale que le graphique a été choisi au hasard.

Le titre fait la moitié du travail. « Chiffre d’affaires par commercial » informe. « Deux commerciaux sous leur objectif 2025 » alerte. C’est le même graphique, et le titre est la seule partie que tout le monde lit. Si vous ne savez pas le titrer, demandez à l’assistant, prompt de la partie « Demander à l’IA » de ce guide, et jugez sa proposition.

Atelier 1, choisir le graphique

En binôme, sans ordinateur. Pour chacune de ces six questions, écrivez le type de graphique et la justification en une phrase. Ne construisez rien.

1. Quel commercial réalise le plus gros chiffre d’affaires ?
2. Comment le chiffre d’affaires a-t-il évolué mois par mois en 2025 ?
3. Quelle part de l’activité chaque famille de produits représente-t-elle ?
4. Chaque commercial atteint-il son objectif ?
5. Les clients qui achètent le plus souvent sont-ils ceux qui dépensent le plus ?
6. Comment le premier semestre 2026 se compare-t-il au premier semestre 2025 ?

Comment savoir que c’est réussi : chaque justification dit ce qu’on compare, des catégories entre elles, une évolution dans le temps, des parts d’un tout, deux mesures l’une contre l’autre, ou un écart à une cible. Le nom du graphique se déduit ; c’est la raison qui est évaluée.

Version courte si le temps manque : les questions 1 à 4.

Activité bonus : pour la question 6, deux graphiques sont défendables. Trouvez-les tous les deux et dites lequel vous retiendriez pour une direction pressée.

Guide 3C. Le graphique depuis un croisé

À quoi ça sert. Un graphique construit directement sur un tableau croisé se met à jour avec lui, et il hérite de ses filtres. Ce n’est pas une image figée, c’est un instrument.

Ce qu’il vous faut. Le tableau croisé « Commerciaux 2025 » du guide 3A.

A. Construire

1. Cliquez dans le tableau croisé « Commerciaux 2025 ».
2. Onglet « Analyse du tableau croisé dynamique », bouton « Graphique croisé dynamique ». Une fenêtre propose les types.
3. Choisissez « Barres », le premier modèle, « Barres groupées », « OK ». Le graphique apparaît, relié au tableau.
4. Le tri se fait dans le tableau, pas dans le graphique : clic droit sur un montant du tableau, « Trier », « Trier du plus grand au plus petit ». Le graphique suit tout seul.

B. Les cinq réglages

1. La légende. Cliquez sur la légende, appuyez sur « Suppr ». Il n’y a qu’une série, elle n’apprenait rien.
2. Le quadrillage. Cliquez sur une ligne du quadrillage, « Suppr ». Il concurrençait les données.
3. Les étiquettes. Clic droit sur une barre, puis « Ajouter des étiquettes de données ». Le lecteur veut le chiffre, pas sa position approximative.
4. Le titre. Cliquez sur le titre du graphique, effacez, tapez un constat : L. Peyre et A. Bonnet réalisent la moitié du chiffre 2025. Entrée.
5. La couleur. Clic droit sur une barre, puis « Remplissage », gris. Puis cliquez une seule fois sur la barre de M. Faure, pour ne sélectionner qu’elle, « Remplissage », orange. Tout en gris sauf ce qui pose problème : l’œil va immédiatement là où il faut.

C. Les boutons de champ

1. Les boutons gris « Somme de Montant », « Commercial » posés sur le graphique sont des filtres. Clic droit sur l’un d’eux, puis « Masquer tous les boutons de champ du graphique ». Un tableau de bord ne les montre pas.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Un graphique à cinq barres horizontales, L. Peyre en haut, sans légende ni quadrillage, chaque barre avec son montant, un titre qui énonce un constat, quatre barres grises et une orange.

Atelier 2, trois graphiques justes

Sur votre fichier, en autonomie, périmètre 2025 hors annulées. Trois graphiques, un par tableau croisé du guide 3A, chacun avec ses cinq réglages et un titre qui énonce un constat.

1. Le chiffre d’affaires par commercial, en barres horizontales triées. Guide 3C, tel quel.
2. L’évolution mensuelle 2025, en courbe, depuis « Mensuel 2025 ». Une seule couleur : si Excel colorie chaque segment, clic droit sur la courbe, « Remplissage », une couleur.
3. La part de chaque famille, en secteurs, depuis « Familles 2025 ». Gardez la légende, elle nomme les parts, et affichez les étiquettes en pourcentage : clic droit sur une part, « Ajouter des étiquettes de données », puis clic droit sur une étiquette, « Mettre en forme les étiquettes de données », cochez « Pourcentage », décochez « Valeur ».

Comment savoir que c’est réussi : chaque graphique retombe sur 619 667,13 € ; le premier a L. Peyre en haut à 152 596,64 € ; le deuxième a son point haut en avril à 80 890 € et son point bas en août à 21 112 € ; le troisième montre Équipement cuisine à 47,9 %, près d’un demi-cercle. Et chacun a un titre qui dit ce qu’il montre, pas ce qu’il contient.

Version courte si le temps manque : les graphiques 1 et 2.

Activité bonus : dans Excel, cliquez dans un croisé, « Analyse du tableau croisé dynamique », « Insérer une chronologie », cochez « Date » : une barre glissante pour choisir la période. Clic droit sur la chronologie, « Connexions de rapport », cochez les trois tableaux croisés : un seul curseur pilote alors les trois graphiques. Sur Google Sheets, un segment, « Données » puis « Ajouter un segment », joue ce rôle.

Guide 3D. Les graphiques qui mentent

À quoi ça sert. Vous en produirez sans le vouloir, par un réglage par défaut. Vous en recevrez, parfois volontairement. Savoir les voir vaut de l’argent.

L’axe tronqué. Un axe vertical qui ne part pas de zéro amplifie les écarts. L’atteinte des objectifs 2025 va de 87 % à 109 % : avec un axe partant de zéro, les cinq barres se ressemblent, et c’est la vérité, les cinq commerciaux sont dans un mouchoir de poche. Avec un axe partant de 85 %, la barre de M. Faure devient minuscule et celle de L. Peyre écrasante. Mêmes chiffres, deux histoires opposées. Ce n’est pas toujours une manipulation : une série qui varie de 2 % autour d’une valeur élevée disparaît sur un axe partant de zéro. La règle honnête : si vous tronquez un axe, écrivez-le sur le graphique.

Les secteurs à douze parts. Au-delà de cinq ou six parts, l’œil ne compare plus rien.

La troisième dimension. La perspective donne plus de surface aux parts placées devant : deux valeurs égales cessent d’avoir l’air égales. Pas de 3D, sans exception, et elle revient toujours par le menu des styles rapides.

Les deux axes verticaux. Deux séries d’unités différentes sur deux axes disent presque n’importe quoi, puisque la position relative des courbes dépend du choix des échelles.

Le piège de la loi de Pareto. On entend partout que 20 % des clients font 80 % du chiffre d’affaires. Sur Kertavel en 2025, les 4 premiers clients, soit 20 % du portefeuille, pèsent 30,1 % du chiffre d’affaires, et il faut descendre jusqu’au quinzième client, 75 % du portefeuille, pour franchir les 80 %. La règle ne s’applique pas ici, et c’est une information stratégique : l’entreprise est peu dépendante d’un client, ce qui la protège, mais elle n’a aucun grand compte, donc aucune relation ne porte la croissance. Une règle toute faite se vérifie sur vos données avant d’être citée. Le vérifier tient en un tableau croisé, « Client » en Lignes, « Montant » en Valeurs, trié, avec « % du total général » puis un cumul lu à l’œil.

Guide 3E. La mise en forme conditionnelle

À quoi ça sert. Un tableau de bord contient des chiffres qu’il faut lire d’un coup d’œil. La mise en forme conditionnelle fait ce travail, à une condition : que les seuils soient les vôtres, pas ceux du tableur.

Ce qu’il vous faut. Le tableau croisé « Commerciaux 2025 », votre assistant, et la feuille « Commerciaux » du classeur, qui porte l’objectif 2025 de chacun.

A. Le tableau d’atteinte

1. Nouvelle feuille, nommez-la Objectifs. En A1 Commercial, B1 Réalisé 2025, C1 Objectif, D1 Atteinte.
2. Copiez les cinq noms et les cinq montants du croisé « Commerciaux 2025 » : sélectionnez-les, « Ctrl » et « C », cliquez A2, clic droit, « Collage spécial », « Valeurs ». Cinq lignes, de A2 à B6, dans l’ordre du chiffre d’affaires.
3. L’objectif se rapatrie depuis la feuille « Commerciaux » : demandez la formule à l’assistant, prompt de la partie « Demander à l’IA » ci-dessous. Collez-la en C2, puis double-cliquez sur le petit carré en bas à droite de la cellule : elle descend jusqu’à C6.
4. En D2, tapez =B2/C2, Entrée, et recopiez jusqu’à D6. Sélectionnez D2 à D6, clic droit, « Format de cellule », « Pourcentage », une décimale.
5. Ligne 7 : Ensemble en A7, =SOMME(B2:B6) en B7, =SOMME(C2:C6) en C7, =B7/C7 en D7.

B. Les icônes, avec des seuils qui ont un sens

1. Sélectionnez D2 à D6. « Accueil », « Mise en forme conditionnelle », « Jeux d’icônes », les trois flèches colorées. Regardez le résultat : les seuils sont placés aux tiers de la plage, ce qui n’a aucun sens commercial.
2. « Mise en forme conditionnelle », « Gérer les règles », « Modifier la règle ». Dans les deux lignes de seuil, remplacez le « Type » « Pourcentage » par « Nombre », et saisissez 1 pour la flèche verte, 0,9 pour la jaune. « OK », « OK ».

Les trois seuils viennent d’une décision commerciale, pas du tableur : objectif atteint à 100 % et plus, proche et à surveiller entre 90 et 99 %, écart significatif en dessous de 90 %. Une autre entreprise en retiendra d’autres.

Ne pas surcharger. Un tableau où tout est signalé ne signale plus rien. Deux mises en forme conditionnelles au maximum par tableau de bord.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. L. Peyre 109,0 % en vert, A. Bonnet 103,3 % et S. Leclerc 101,9 % en vert, T. Marchand 90,5 % en orange, M. Faure 87,0 % en rouge. Ensemble : 619 667,13 € réalisés sur 625 000 €, soit 99,1 %.

Atelier 3, le tableau d’atteinte en couleurs

Sur votre fichier, en autonomie : construisez le tableau d’atteinte du guide 3E, avec ses icônes et vos seuils. Puis répondez par écrit, en deux phrases : que montre ce tableau, et que ne montre-t-il pas ?

Comment savoir que c’est réussi : les cinq lignes affichent les taux du guide 3E avec trois couleurs, et votre seconde phrase parle des objectifs, qui ne sont pas identiques.

Version courte si le temps manque : le tableau sans la réponse écrite.

Guide 3F. Le tableau de bord sur un écran

À quoi ça sert. Assembler ce que vous avez construit en une page qu’une direction lit en deux minutes.

Les cinq principes. Une page : si votre direction doit faire défiler, ce n’est plus un tableau de bord. Le plus important en haut à gauche : l’œil commence là. Quatre à six blocs, pas plus. Un périmètre annoncé en tête, en une ligne : période, statuts retenus, date de mise à jour. Aucune décoration : pas de dégradé, pas d’ombre, pas de logo qui prend un quart de la page.

Une structure qui fonctionne, de haut en bas : la ligne de périmètre ; trois indicateurs en gros caractères, chiffre d’affaires, nombre de commandes, panier moyen ; le tableau d’atteinte des objectifs avec ses icônes ; l’évolution mensuelle à gauche et la répartition par famille à droite ; en bas, les filtres visibles, chronologie ou segments. Les trois chiffres du haut répondent en cinq secondes à « est-ce que ça va ». Le reste explique.

A. Les trois indicateurs

1. Nouvelle feuille, nommez-la Tableau de bord. En A1, la ligne de périmètre : Ventes 2025, hors commandes annulées, 854 lignes de commande.
2. En A3 Chiffre d'affaires, en A4 la valeur : =SOMME(...) sur les montants du croisé « Commerciaux 2025 », ou le total recopié en valeur. Attendu : 619 667,13 €.
3. En C3 Commandes, en C4 le nombre de commandes distinctes. Une commande occupe plusieurs lignes : compter les lignes donnerait 854, et c’est faux. Demandez la formule à l’assistant, prompt de la partie « Demander à l’IA ». Attendu : 456. Sans assistant : nouveau tableau croisé, « N° commande » dans « Lignes », les deux filtres, et le nombre de lignes obtenues se lit en sélectionnant la colonne, dans la barre d’état en bas de l’écran.
4. En E3 Panier moyen, en E4 =A4/C4. Attendu : 1 358,92 €.
5. Mettez les trois valeurs en gros : sélectionnez-les, « Accueil », taille de police 20, gras.

B. Assembler

1. Copiez le tableau d’atteinte de la feuille « Objectifs » sous les indicateurs, à partir de A6 : « Collage spécial », « Valeurs et formats » ; la mise en forme conditionnelle se recopie avec.
2. Cliquez sur le graphique mensuel, « Ctrl » et « X », cliquez A14 sur la feuille « Tableau de bord », « Ctrl » et « V ». Même geste pour le graphique des familles, posé à droite, en F14. Redimensionnez par les poignées d’angle pour que les deux tiennent côte à côte.
3. Regardez la page en entier : « Affichage » puis « Zoom », 75 %. Tout doit tenir sur un écran, sans faire défiler.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Une feuille « Tableau de bord » avec, sur un écran : la ligne de périmètre, 619 667,13 €, 456 commandes, 1 358,92 € de panier moyen, le tableau d’atteinte en trois couleurs, la courbe mensuelle et les secteurs des familles.

Atelier 4, votre tableau de bord

En binôme. Vous produisez ce que la direction a demandé : une page, lisible en deux minutes, périmètre 2025 hors commandes annulées, feuille nommée Tableau de bord.

Contenu minimal, guide 3F :

1. Une ligne de périmètre en tête
2. Trois indicateurs de synthèse : chiffre d’affaires, nombre de commandes, panier moyen
3. Le tableau d’atteinte des objectifs avec son jeu d’icônes et vos seuils
4. Le graphique d’évolution mensuelle
5. Le graphique de répartition par famille
6. Un filtre visible : chronologie ou segment

Contraintes de forme, elles ne se négocient pas : aucune 3D ; un titre par graphique qui énonce un constat, pas un contenu ; deux mises en forme conditionnelles au maximum ; si un axe est tronqué, c’est écrit sur le graphique.

Comment savoir que c’est réussi : 619 667,13 €, 854 lignes, 456 commandes, 1 358,92 € de panier moyen, 20 clients actifs, 47,9 % pour Équipement cuisine, 99,1 % d’atteinte globale. Si vos chiffres diffèrent, cherchez d’abord le périmètre : dans neuf cas sur dix, c’est un filtre. Et un lecteur qui n’était pas dans la salle sait, en deux minutes, si ça va ou si ça ne va pas.

Version courte si le temps manque : les points 1, 2, 3 et 4.

Activité bonus : un septième bloc qui compare le premier semestre 2026 au premier semestre 2025, avec un titre qui dit la vérité sans dramatiser. Le bon titre ressemble à « Premier semestre 2026 en retrait de 11,8 %, cinq mois sur six en baisse ».

Demander à l’IA

Trois prompts servent aujourd’hui. Ils supposent que vous avez décrit vos colonnes à l’assistant une fois, comme au guide 2D de la séance 2 : le tableur, le séparateur, et les colonnes de la feuille « Ventes » avec leur lettre.

L’objectif de chaque commercial, guide 3E :

Ma feuille « Commerciaux » a en B le nom du commercial, en C sa région et en D son objectif 2025, lignes 2 à 6. Dans une feuille « Objectifs », j’ai les noms en A2 à A6. Écris-moi la formule à mettre en C2 qui rapatrie l’objectif 2025 du commercial de la ligne, prête à coller, recopiable vers le bas, avec les plages figées par des $.

Attendu : une formule RECHERCHEV qui cherche A2 dans Commerciaux!$B$2:$D$6 et rapporte la troisième colonne, avec FAUX. En C2, pour L. Peyre : 140 000.

Les commandes distinctes, guide 3F :

Sur la feuille « Ventes », colonne B les numéros de commande, colonne A les dates, colonne O les statuts, lignes 2 à 1362. Écris-moi la formule qui compte le nombre de numéros de commande distincts pour l’année 2025, en excluant les lignes dont le statut est « Annulée ». Si mon tableur ne connaît pas une fonction, propose l’équivalent.

Attendu : 456. Sur Google Sheets, une fonction COUNTUNIQUE avec FILTER ; sur Excel 365, UNIQUE et FILTRE ; sur un Excel plus ancien, l’assistant proposera un tableau croisé, et c’est la bonne réponse.

Le titre qui dit le constat, ateliers 2 et 4 :

Voici les cinq valeurs d’un graphique en barres, chiffre d’affaires 2025 par commercial : [collez les cinq noms et montants]. Propose-moi trois titres de graphique qui énoncent un constat lisible par une direction, en moins de douze mots chacun, sans adjectif inutile. Puis dis-moi lequel est le plus honnête, et pourquoi.

On garde celui qu’on peut défendre devant témoin, jamais celui qui dramatise.

Mémo de la séance

Choisir : comparaison, barres triées. Temps, courbe. Répartition, secteurs si peu de parts. Deux mesures, nuage de points. Écart à une cible, barres avec ligne de référence.

Construire depuis un croisé : « Analyse du tableau croisé dynamique », « Graphique croisé dynamique ». Le tri se fait dans le tableau, le graphique suit.

Les cinq réglages : pas de légende inutile, pas de quadrillage, les étiquettes de données, un titre qui énonce le constat, une seule couleur sauf pour signaler.

Les seuils sont les vôtres, jamais ceux du tableur : 1 et 0,9, « Type » sur « Nombre ». Deux mises en forme conditionnelles au maximum.

Ne pas mentir : pas de 3D, pas de secteurs à douze parts, et si un axe est tronqué, c’est écrit.

Vérifier avant de citer : une règle toute faite, Pareto compris, se contrôle sur vos données.

Le tableau de bord : une page, le plus important en haut à gauche, quatre à six blocs, le périmètre en tête, aucune décoration. Commandes distinctes, jamais des lignes ; panier moyen, chiffre d’affaires divisé par les commandes.