Excel séance 2 : faire parler les données avec les tableaux croisés dynamiques

Répondre à une question de gestion sans écrire une seule formule

Avant de commencer : reprendre la main

Deux semaines ont passé. Avant tout apport nouveau, cinq minutes sur trois questions. Répondez de mémoire, sans ouvrir le fichier.

1. Le fichier de départ affichait dix-neuf valeurs différentes dans la colonne Région. Combien y en a-t-il réellement, et quelles sont les trois causes de cet écart ?
2. Vous cliquez sur Supprimer les doublons et vous ne cochez que la colonne « N° commande ». Que se passe-t-il, et pourquoi est-ce une catastrophe ?
3. Une cellule affiche 1250 mais alignée à gauche, avec un petit triangle vert. Que vaut la somme de cette colonne, et pourquoi ?

Les réponses sont dans le guide de la séance 1. Si deux réponses sur trois vous échappent, relisez-le avant de continuer : la suite ne fonctionne que sur des données propres.

Point de départ commun. Si votre fichier nettoyé n’est pas exploitable, prenez le corrigé fourni. Vous devez disposer d’un tableau structuré nommé Ventes, avec les colonnes nettoyées Région nettoyée, Client nettoyé, des quantités et des prix en nombres, et aucun commercial manquant.

Le problème du jour

Votre direction commerciale vous demande, pour lundi prochain :

« Quelle région tire le chiffre d’affaires, quel commercial est en difficulté, et sur quelle famille de produits. Une page. »

Vous avez 1 361 lignes de ventes. Vous pourriez écrire quarante formules SOMME.SI.ENS, une par croisement, et les recopier. Vous y passeriez la matinée, et la moindre question complémentaire vous obligerait à tout recommencer.

Le tableau croisé dynamique répond à cette demande en trois minutes, et il répond aussi à la question suivante, celle que votre direction n’a pas encore posée. C’est l’outil le plus rentable d’Excel, et c’est celui que les recruteurs testent en entretien.

Objectif de la séance : à la fin, vous produisez seul une analyse croisée sur un fichier de ventes, vous savez la lire, et vous savez repérer ce qu’elle ne dit pas.

1. Ce qu’est un tableau croisé dynamique

1.1 L’idée

Un tableau croisé dynamique résume un grand tableau en regroupant les lignes selon les valeurs d’une ou plusieurs colonnes, et en calculant une synthèse pour chaque groupe.

Vos 1 361 lignes de ventes contiennent chacune une région. Le tableau croisé les regroupe par région et additionne les montants. Vous passez de 1 361 lignes à 4.

C’est exactement ce que vous feriez à la main : trier, regrouper, additionner. Excel le fait instantanément, et le refait à chaque fois que vous changez d’avis sur ce que vous voulez regrouper. D’où l’adjectif « dynamique ».

1.2 Le vocabulaire, une fois pour toutes

Quand vous créez un tableau croisé, Excel affiche à droite un volet avec quatre zones. C’est là que tout se joue, et c’est là que tout le monde se perd la première fois.

Une phrase à retenir, qui vous évitera de tâtonner :

En Lignes et en Colonnes, on met des étiquettes. En Valeurs, on met des nombres.

Région, Commercial, Famille, Statut : ce sont des étiquettes. Montant, Quantité : ce sont des nombres.

1.3 Créer votre premier tableau croisé

1. Cliquez sur une cellule quelconque de votre tableau Ventes
2. Onglet Insertion, bouton Tableau croisé dynamique
3. Excel propose automatiquement la plage. Si votre tableau est bien structuré, il affiche Ventes et non Feuil1!$A$1:$O$1362 : c’est le signe que la séance 1 a été bien faite
4. Choisissez Nouvelle feuille de calcul
5. OK

Vous obtenez une zone vide et le volet de droite. C’est normal. Un tableau croisé sans champ ne montre rien.

1.4 Le premier croisement

Faites glisser :

• Région nettoyée dans la zone Lignes
• Montant dans la zone Valeurs

Le tableau apparaît. Quatre lignes, un total.

Vérifiez immédiatement que le total vaut 941 844,96 €. Ce réflexe vous sauvera souvent : si le total du tableau croisé ne correspond pas au total de la colonne d’origine, quelque chose a été mal converti à la séance 1, et toute l’analyse qui suit est fausse.

Si Excel affiche « Nombre de Montant » et non « Somme de Montant », c’est le symptôme classique : au moins une cellule de la colonne Montant n’est pas un nombre. Excel ne sait pas additionner du texte, alors il compte. Corrigez la colonne avant de continuer.

2. Atelier 1, les cinq questions de la direction

Sur votre fichier, en autonomie. Créez un tableau croisé par question, chacun sur sa propre feuille, nommée clairement.

Question 1. Quel est le chiffre d’affaires par région ? Classez de la plus forte à la plus faible.

Question 2. Quel est le chiffre d’affaires par commercial ?

Question 3. Quel est le chiffre d’affaires par famille de produits ? Ajoutez la quantité vendue à côté du montant.

Question 4. Quel est le chiffre d’affaires par canal de vente ?

Question 5. Quel est le chiffre d’affaires par statut de commande ?

Notez vos résultats. Nous les comparerons.

Version courte si le temps manque : faites les questions 1, 2 et 5. Ce sont celles qui portent la suite de la séance.

Activité bonus : dans la question 3, ajoutez une troisième colonne qui donne le prix moyen de vente par unité. Une piste : ce n’est pas la moyenne des prix unitaires.

Les résultats attendus

Chiffre d’affaires par région

Chiffre d’affaires par commercial

Chiffre d’affaires par famille

Chiffre d’affaires par canal

Chiffre d’affaires par statut

Ce que ces tableaux vous disent déjà

La famille Équipement cuisine pèse 45,7 % du chiffre d’affaires avec 431 unités vendues. La famille Arts de la table pèse 14,8 % avec 6 006 unités, soit quatorze fois plus d’articles. Une armoire réfrigérée et une assiette ne se pilotent pas de la même façon : la première fait le chiffre, la seconde fait le trafic et la fidélité. Retenez ce croisement volume-valeur, il revient dans toutes les analyses commerciales.

Les quatre canaux sont remarquablement équilibrés, de 21,8 % à 26,8 %. C’est en soi une information : aucun canal n’est en train de mourir, aucun ne porte l’entreprise à lui seul. Une entreprise qui ferait 70 % de son chiffre sur un seul canal aurait un problème de dépendance.

Et surtout : 17 187,08 € de commandes annulées. Nous y revenons tout de suite, parce que c’est là que se cache la première faute d’analyse.

3. Le piège du total : ce que vous venez de calculer est faux

Reprenez le tableau par statut. 1,8 % du chiffre d’affaires correspond à des commandes annulées.

Une commande annulée n’a jamais été encaissée. Elle ne représente aucun euro. Pourtant elle est dans votre total, parce que vous avez additionné toute la colonne Montant sans vous demander ce qu’il y avait dedans.

Vos 941 844,96 € ne sont pas le chiffre d’affaires de Kertavel. C’est la somme des lignes du fichier, ce qui n’est pas la même chose.

3.1 Ce que chaque statut signifie vraiment

Selon la définition retenue, vous obtenez trois chiffres différents pour la même entreprise :

Entre le premier et le troisième, l’écart est de 323 470,30 €. Un tiers. Si vous présentez le mauvais chiffre en réunion, vous ne faites pas une petite erreur : vous racontez une autre entreprise.

3.2 La règle professionnelle

Avant de communiquer un chiffre, dites toujours de quoi il est la somme.

Pas « le chiffre d’affaires est de 941 000 € », mais « sur la période, hors commandes annulées, les ventes représentent 924 658 €, dont 618 375 € déjà facturés ».

C’est plus long à dire. C’est infiniment plus solide à défendre. Et c’est exactement ce qui distingue un responsable du développement commercial d’un exécutant qui recopie un total.

3.3 Filtrer proprement dans un tableau croisé

Trois façons d’exclure les annulées, de la moins bonne à la meilleure.

La mauvaise : supprimer les lignes annulées du fichier. Vous perdez une information utile, et vous ne pourrez plus jamais analyser le taux d’annulation.

L’acceptable : faire glisser Statut dans la zone Filtres, puis décocher « Annulée » dans le menu déroulant. Simple, mais le filtre est discret : trois semaines plus tard, personne ne se souvient qu’il est actif, et le lecteur du tableau ne le voit pas.

La bonne : insérer un segment. Cliquez dans le tableau croisé, onglet Analyse de tableau croisé dynamique, bouton Insérer un segment, cochez Statut. Un cadre de boutons apparaît à côté du tableau. Vous cliquez sur les statuts à conserver, et surtout tout le monde voit lesquels sont actifs.

Le segment n’est pas un gadget esthétique. C’est un outil d’honnêteté : il rend le filtre visible pour celui qui lit votre travail.

4. Croiser deux dimensions

Une seule dimension donne un classement. Deux dimensions donnent une explication.

4.1 Le croisement commercial et famille

Dans un nouveau tableau croisé :

• Commercial en Lignes
• Famille en Colonnes
• Montant en Valeurs
• Un segment sur Statut, annulées décochées

Vous obtenez une matrice de cinq lignes et cinq colonnes. Voici ce qu’elle donne, hors commandes annulées :

Notez que ces montants sont inférieurs à ceux de l’atelier 1 : c’est le segment qui fait son travail. Le total de 924 658 € est bien le chiffre hors annulées calculé au chapitre précédent. Quand deux tableaux se recoupent, vous savez que vous ne vous êtes pas trompé de périmètre.

4.2 Lire une matrice : la méthode en trois passages

Un tableau croisé à deux dimensions ne se lit pas au hasard. Trois passages, dans cet ordre.

Premier passage, les totaux. Qui est en haut, qui est en bas, quel est l’écart. Ici, L. Peyre et A. Bonnet devant, M. Faure derrière.

Deuxième passage, les lignes. Comment se répartit l’activité de chacun. Tous ont le même profil : Équipement cuisine domine, Linge et Hygiène ferment la marche. Cette régularité est une information : elle indique que la structure de la demande s’impose aux vendeurs plus que leurs préférences personnelles.

Troisième passage, les anomalies. Cherchez la case qui ne suit pas la logique de sa ligne et de sa colonne.

Ici, elle saute aux yeux quand on la cherche : M. Faure réalise 61 507 € sur Équipement cuisine, contre 105 740 € pour A. Bonnet et 98 535 € pour L. Peyre. Sur Mobilier, en revanche, M. Faure fait 33 897 €, soit davantage qu’A. Bonnet, S. Leclerc et T. Marchand. C’est donc sur la famille la plus contributive, et sur elle seule, qu’il décroche.

Cela ne prouve rien, et c’est important de le dire. Trois hypothèses au moins expliquent le même chiffre : son portefeuille de clients contient moins d’établissements équipables, il est moins à l’aise sur une vente technique à cycle long, ou son secteur a été démarché par un concurrent spécialisé. Les données du fichier ne permettent pas de trancher.

Ce que vous produisez, c’est une question bien posée, pas une conclusion. Formulez-la ainsi : « L’écart de M. Faure se concentre sur Équipement cuisine, la famille la plus contributive. Est-ce un effet de portefeuille ou un besoin d’accompagnement sur cette gamme ? » Cette phrase-là, vous pouvez la défendre. « M. Faure est moins bon », vous ne pouvez pas.

4.3 Afficher des pourcentages plutôt que des euros

Les euros permettent de comparer des volumes. Les pourcentages permettent de comparer des structures.

Clic droit sur une valeur du tableau, Afficher les valeurs, puis :

Essayez % du total de la ligne sur la matrice précédente. Les cinq commerciaux affichent alors des profils très proches, ce qui confirme la lecture du deuxième passage. La différence entre eux est de volume, pas de spécialisation.

5. Grouper les dates

Le fichier couvre du 6 janvier 2025 au 29 juin 2026, soit dix-huit mois. Il contient 331 dates distinctes. Un tableau de 331 lignes n’est pas une analyse : il faut regrouper.

5.1 La manipulation

• Date en Lignes
• Montant en Valeurs

Excel regroupe souvent automatiquement par année et par mois. Sinon : clic droit sur une date dans le tableau, Grouper, puis cochez Mois et Années.

Cochez toujours Années avec Mois. Si vous ne groupez que par mois, Excel additionne le janvier 2025 et le janvier 2026 dans une seule ligne « janv ». C’est l’erreur la plus fréquente sur les fichiers pluriannuels, et elle est silencieuse : le tableau reste parfaitement présentable, il est simplement faux.

5.2 La saisonnalité de 2025

Deux pics, deux creux. Le premier pic va de février à mai, le second en septembre-octobre. Les creux tombent en juillet-août et en janvier.

Cette forme n’a rien d’aléatoire, et un responsable du développement commercial doit savoir l’expliquer. Kertavel vend à des hôtels et à des restaurants. Un établissement saisonnier s’équipe avant sa saison, pas pendant : d’où le pic de printemps, qui prépare l’été, et le pic d’automne, qui prépare les fêtes et remplace le matériel usé par la saison. En juillet et en août, les clients sont en plein service : ils n’ont ni le temps ni la trésorerie pour recevoir un commercial.

Conséquence opérationnelle : un plan d’action commercial pour Kertavel ne se conçoit pas en douze mois uniformes. Les efforts de prospection se placent en janvier et en juillet-août, quand les clients sont disponibles, pour que les commandes tombent en février et en septembre.

5.3 Comparer deux années : le piège du mois isolé

Le fichier permet de comparer les six premiers mois de 2025 et de 2026.

Regardez février : -53,5 %. Puis mars : +36,8 %. Pris isolément, chacun de ces deux mois justifierait une réunion de crise ou une note de félicitations.

Le cumul dit tout autre chose : -11,8 %. Le décrochage de février et le rebond de mars se compensent en grande partie. Ce qui s’est probablement produit est banal : des commandes attendues en février sont tombées début mars. Un décalage de quelques jours à la charnière de deux mois suffit à produire ces deux chiffres spectaculaires.

Règle à retenir : ne concluez jamais sur un mois isolé. Regardez le cumul, ou glissez sur trois mois.

Le vrai constat est ailleurs, et il est plus sérieux : le premier semestre 2026 recule de 11,8 %, et le recul touche cinq mois sur six. Ce n’est pas un accident de calendrier, c’est une tendance. Voilà ce qui mérite la réunion.

6. Atelier 2, préparer la note d’une page

Vous produisez ce que la direction a demandé. En binôme, 25 minutes, puis restitution.

Consigne. Une page. Trois constats, chacun appuyé sur un tableau croisé que vous savez refaire devant témoin. Pour chaque constat : ce que montre le chiffre, ce qu’il ne montre pas, et l’action que vous proposez.

Contraintes de forme :

• Le périmètre du chiffre est annoncé en une phrase, en tête de note (période, statuts retenus)
• Aucun pourcentage sans son montant en euros à côté
• Aucune affirmation sur une personne qui ne serait pas formulée comme une hypothèse à vérifier
• Une action proposée par constat, réalisable, datée

Version courte si le temps manque : deux constats au lieu de trois.

Activité bonus : ajoutez un quatrième constat sur le taux d’annulation par canal de vente. Un canal annule-t-il plus que les autres, et si oui, que feriez-vous ?

Pour ce dernier point, construisez le tableau avec Canal en Lignes, Statut en Colonnes et le nombre de lignes en Valeurs, puis affichez en % du total de la ligne. Vous devriez trouver ceci :

La commande directe annule presque cinq fois moins que les autres canaux. L’explication la plus probable est la présence d’un commercial au moment de la décision : le besoin est qualifié, le devis est discuté, l’engagement est réel. Sur un salon ou sur le site, la commande se prend dans l’enthousiasme et se défait à la relecture du budget.

Attention à ce que vous en concluez. Ce constat ne dit pas « supprimons le site web ». Le site représente 234 575 € de ventes, dont 97 % aboutissent. Il dit plutôt : sur les canaux à taux d’annulation élevé, un appel de confirmation dans les 48 heures suivant la commande vaut probablement son coût. C’est une action concrète, chiffrable, et testable.

Ce qu’un bon rendu contient

Les trois constats les plus solides que le fichier permet, à ce stade :

1. Concentration produit. Hors annulées, Équipement cuisine porte 420 741 € sur 924 658 €, soit 45,5 % du chiffre d’affaires, avec 420 unités vendues. La performance de l’entreprise repose sur une famille et sur quelques références lourdes. Action : sécuriser les stocks et les délais sur ces références avant le pic de septembre.
2. Recul du premier semestre 2026. -11,8 % sur le cumul, soit 40 671 € de moins qu’au premier semestre 2025, et cinq mois sur six en baisse. Action : analyser la composition du portefeuille client sur ces six mois avant d’engager un plan de prospection.
3. Écart concentré sur une famille. L’écart de M. Faure porte sur Équipement cuisine, pas sur l’ensemble de son activité : il est au contraire au premier rang sur Mobilier. Action : examiner la composition de son portefeuille avant toute conclusion sur sa performance.

Les erreurs qui reviennent chaque année

Mémo de la séance

Créer : Insertion, Tableau croisé dynamique, Nouvelle feuille.

Les quatre zones : Lignes et Colonnes reçoivent des étiquettes, Valeurs reçoit des nombres, Filtres restreint.

Vérifier : le total du tableau croisé doit égaler le total de la colonne d’origine. « Nombre de » au lieu de « Somme de » signale du texte dans une colonne de nombres.

Filtrer visiblement : un segment plutôt qu’un filtre caché.

Dates : Grouper, cocher Mois et Années.

Pourcentages : clic droit, Afficher les valeurs, % du total général, de la ligne ou de la colonne.

Actualiser : clic droit, Actualiser. À faire systématiquement avant de communiquer un chiffre.

Et la règle qui vaut plus que toutes les manipulations : dites toujours de quoi votre chiffre est la somme.

Pour la séance 3

La séance suivante construit un tableau de bord commercial : graphiques, comparaison au réalisé et à l’objectif, mise en forme conditionnelle.

Conservez vos tableaux croisés : ils en seront la matière première. Un graphique ne se construit pas sur des données brutes, il se construit sur une synthèse.