Excel séance 2 : le tableau croisé, et la formule qu’on demande à l’IA

Guide en main, assistant à côté, et le chiffre qui prouve

À lire une fois, avant le premier atelier

Ce guide est écrit pour être suivi sans rien savoir à l’avance. Si vous êtes à l’aise avec un tableur, ne lisez pas les guides : allez directement à la consigne de chaque atelier. Si vous ne l’êtes pas, suivez-les ligne par ligne, ils fonctionnent.

Une convention pour tout le document : 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 ; chaque guide se termine par ce qui change sur Excel pour Mac et sur Google Sheets, et par ce qu’il faut faire si ça ne marche pas.

Le fichier est propre. kertavel-ventes-propre.xlsx est le fichier de la séance 1, nettoyé pour vous : lignes en double retirées, régions et clients écrits d’une seule façon, nombres et dates convertis, commerciaux manquants complétés. L’onglet « Journal » dit exactement ce qui lui a été fait. Vous n’avez rien à refaire, et tout le monde part du même point.

Un assistant à côté du tableur. Ouvrez, dans un autre onglet du navigateur, l’assistant que vous utilisez déjà, Claude, ChatGPT, Gemini ou Copilot, en compte gratuit. Trois prompts servent aujourd’hui, et ils sont dans la bibliothèque remise avec ce guide : la formule que vous ne savez pas écrire, la formule qui renvoie une erreur, le chemin clic par clic pour votre tableur. Quand vous êtes bloqué, demandez le chemin à l’assistant avant de lever la main : il connaît votre tableur mieux que ce guide.

Ce que vous devez obtenir est écrit. Chaque guide se termine par « Comment savoir que c’est réussi », avec les chiffres exacts. Tant que vous n’obtenez pas ces chiffres, ce n’est pas fini ; quand vous les obtenez, vous n’avez pas besoin de demander si c’est bon.

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, une par croisement, et y passer la matinée. 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.

À la fin de la séance, vous saurez répondre à « combien de quoi, par qui, sur quelle période » en trente secondes, dire de quoi un chiffre est la somme avant de le communiquer, obtenir une formule de l’assistant et prouver qu’elle est juste, et écrire une note d’une page qu’on peut défendre.

Guide 2A. Ouvrir le fichier propre, et le lire

À quoi ça sert. À partir d’un fichier dont vous savez ce qu’il contient, et à vérifier en trente secondes que vous avez le bon.

Ce qu’il vous faut. Le fichier kertavel-ventes-propre.xlsx, et cinq minutes.

A. Enregistrer une copie

1. Ouvrez kertavel-ventes-propre.xlsx. Si une bande jaune « Mode protégé » apparaît en haut, cliquez « Activer la modification ».
2. « Fichier » puis « Enregistrer sous ». Donnez-lui votre nom : kertavel-ventes-propre-AB.xlsx, avec vos initiales. Le fichier d’origine reste intact.

B. Lire le Journal

1. En bas de la fenêtre, cliquez sur l’onglet « Journal ». Il compte neuf lignes. Lisez-les : 11 doublons supprimés, 19 écritures de région ramenées à 4, 43 écritures de client ramenées à 20, 18 quantités, 12 prix et 14 dates convertis, 15 commerciaux complétés. C’est tout ce qui a été fait, et rien d’autre.
2. Dernière ligne du Journal : « Lignes reçues : 1372. Lignes conservées : 1361. » Notez ces deux nombres.

C. Compter les lignes

1. Cliquez sur l’onglet « Ventes ». Cliquez sur la cellule A1, celle qui contient « Date ».
2. Appuyez sur « Ctrl » et « Flèche bas » en même temps. Le curseur saute à la dernière ligne remplie : la ligne 1362, soit 1 361 lignes de données sous la ligne de titres.
3. « Ctrl » et « Flèche haut » pour remonter.

D. Le test de l’alignement

1. Regardez les colonnes L « Quantité », M « Prix unitaire » et N « Montant » : les chiffres sont collés à droite de leur cellule. Un tableur aligne les nombres à droite et le texte à gauche, sans qu’on lui demande rien. Un chiffre collé à gauche est un texte, et une somme l’ignore. Sur ce fichier, il n’y en a plus aucun.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. L’onglet Journal existe et annonce 11 doublons supprimés. La colonne A descend jusqu’à la ligne 1362. Les colonnes L, M et N sont alignées à droite.

Guide 2B. Le tableau croisé dynamique, de zéro

À quoi ça sert. À répondre en trente secondes à toute question de la forme « combien de quoi, par qui, sur quelle période ». C’est l’outil sur lequel se prend la majorité des décisions commerciales, et savoir le construire vaut plus, à l’embauche, que citer trois modèles stratégiques.

Ce qu’il vous faut. L’onglet « Ventes », et un quart d’heure la première fois.

A. Construire le tableau

1. Cliquez sur n’importe quelle cellule de l’onglet « Ventes », par exemple A1.
2. « Insertion » puis « Tableau croisé dynamique ». Une fenêtre s’ouvre. La plage de données est déjà remplie, Ventes!$A$1:$O$1362 : ne la changez pas. « Nouvelle feuille de calcul » est coché : laissez-le. Cliquez « OK ».
3. Vous arrivez sur une feuille vide avec, à droite, le volet « Champs de tableau croisé dynamique ». Il porte la liste de vos colonnes en haut, et quatre zones en bas : « Filtres », « Colonnes », « Lignes », « Valeurs ». C’est tout l’outil. C’est normal que le tableau soit vide : vous n’avez encore rien demandé.

B. Le premier croisement

1. Dans la liste des champs, faites glisser « Région » jusque dans la zone « Lignes ». Quatre lignes apparaissent.
2. Faites glisser « Montant » dans la zone « Valeurs ». Vous obtenez le chiffre d’affaires par région, et un « Total général » de 941 844,96 €.
3. Lisez l’en-tête de la colonne : il doit dire « Somme de Montant ». Le tableur choisit tout seul entre additionner et compter, et il se trompe dès qu’une cellule contient du texte. Sur ce fichier, il additionne.

La phrase à retenir, elle évite 80 % des tâtonnements : en « Lignes » et en « Colonnes », on met des étiquettes, Région, Commercial, Famille, Statut. En « Valeurs », on met des nombres, Montant, Quantité.

C. Trier, et lire en euros

1. Clic droit sur n’importe quel montant du tableau, puis « Trier », puis « Trier du plus grand au plus petit ». Occitanie passe en tête.
2. Clic droit sur un montant, puis « Format de nombre », puis « Monétaire », deux décimales, « OK ». Les montants se lisent en euros.

D. Nommer la feuille

1. Double-cliquez sur l’onglet de la feuille, en bas, qui s’appelle « Feuil2 » ou « Feuil3 ». Tapez Par région. Entrée. À la cinquième feuille, vous serez content de l’avoir fait.

E. Un second nombre à côté du premier

1. Faites glisser « Quantité » dans la zone « Valeurs », sous « Somme de Montant ». Une seconde colonne apparaît : le nombre d’articles vendus par région, à côté des euros.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Quatre régions, un « Total général » de 941 844,96 €, Occitanie en tête à 358 870,81 €. Si vous avez ajouté Quantité, Occitanie affiche 5 809 articles.

Atelier 1, les cinq questions de la direction

Sur votre fichier, en autonomie. Un tableau croisé par question, chacun sur sa propre feuille, nommée. Le guide 2B vous a fait le premier ; les quatre suivants sont le même geste avec un autre champ dans « Lignes ».

Notez vos résultats : nous les lirons ensemble.

Comment savoir que c’est réussi : les cinq tableaux affichent tous le même « Total général », 941 844,96 €, puisqu’ils découpent le même gâteau. Sur la question 3, Équipement cuisine fait 430 042,14 € avec 431 articles, et Arts de la table 139 473,72 € avec 6 006 articles.

Version courte si le temps manque : 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 colonne qui donne le prix moyen de vente par article, famille par famille. Une piste : ce n’est pas la moyenne des prix unitaires, et l’assistant sait vous dire comment obtenir « Montant divisé par Quantité » dans un tableau croisé.

Guide 2C. Le piège du total, et la matrice

À quoi ça sert. À ne jamais présenter un chiffre faux à une direction, et à passer d’un classement à une explication.

Ce qu’il vous faut. Le tableau croisé « Par statut » de l’atelier 1.

A. Ce que vous venez de calculer

Reprenez le tableau par statut. Une ligne « Annulée » y figure, pour 17 187,08 €, soit 1,8 % du total. Une commande annulée n’a jamais été encaissée : elle ne représente aucun euro, et elle est pourtant dans votre total, parce que vous avez additionné une colonne sans vous demander ce qu’il y avait dedans.

Trois chiffres pour la même entreprise, tous les trois exacts :

Entre le premier et le troisième, l’écart est de 323 470,30 €, un tiers. Présenter le mauvais chiffre en réunion, ce n’est pas une petite erreur : c’est raconter une autre entreprise.

La règle, à noter mot pour mot : avant de communiquer un chiffre, dites 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 ».

B. Exclure les annulées

1. Cliquez dans votre tableau croisé « Par statut ». Dans le volet, faites glisser « Statut » de la zone « Lignes » vers la zone « Filtres ». Le tableau ne montre plus qu’un total.
2. Un menu déroulant est apparu au-dessus du tableau, avec « (Tous) ». Cliquez sa flèche, cochez « Sélectionner plusieurs éléments », décochez « Annulée », « OK ». Le total passe à 924 657,88 €.

Ce filtre a un défaut : il est discret. Trois semaines plus tard, personne ne se souvient qu’il est actif, et le lecteur du tableau ne le voit pas. Dans Excel, il existe une façon visible : le segment.

1. Cliquez dans le tableau croisé, puis onglet « Analyse du tableau croisé dynamique », puis « Insérer un segment », cochez « Statut », « OK ». Un cadre avec quatre boutons apparaît à côté du tableau.
2. Cliquez « Facturée », puis, en tenant « Ctrl », « Livrée » puis « En cours ». « Annulée » reste grisé : tout le monde voit ce qui est exclu.

Le segment n’est pas un gadget : il rend le filtre visible pour celui qui lit votre travail et qui n’était pas devant votre écran.

C. Croiser deux dimensions

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

1. Nouvelle feuille, nouveau tableau croisé, comme au guide 2B, étapes 1 à 3. Nommez la feuille Matrice.
2. « Commercial » dans « Lignes ». « Famille » dans « Colonnes ». « Montant » dans « Valeurs ». « Statut » dans « Filtres », et décochez « Annulée » comme à l’étape 2.
3. Vous obtenez une matrice de cinq lignes et cinq colonnes, avec un « Total général » de 924 657,88 €.

Lisez-la dans cet ordre, et pas autrement. Les totaux d’abord : qui est devant, qui est derrière, quel écart. Les lignes ensuite : comment se répartit l’activité de chacun. Les anomalies enfin : la case qui ne suit ni la logique de sa ligne ni celle de sa colonne.

D. Lire en pourcentages

1. Clic droit sur n’importe quel montant de la matrice, puis « Afficher les valeurs », puis « % du total de la ligne ». Chaque commercial se lit alors comme une répartition : quelle part de son activité chaque famille représente.
2. Même geste avec « % du total de la colonne » : qui vend chaque famille. Et « Aucun calcul » pour revenir aux euros.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Matrice à 924 657,88 € de total. L. Peyre en tête à 233 188 €, M. Faure en queue à 153 164 €. La case M. Faure, Équipement cuisine, vaut 61 507 € ; celle d’A. Bonnet, 105 740 €. Sur Mobilier, M. Faure fait 33 897 €, plus qu’A. Bonnet, S. Leclerc et T. Marchand.

Atelier 2, la matrice commercial et famille

Sur votre fichier, en autonomie. Construisez la matrice du guide 2C, hors commandes annulées, puis lisez-la en trois passages.

1. Les totaux. Qui est premier, qui est dernier, et de combien.
2. Les lignes. Les cinq commerciaux ont-ils le même profil de répartition, ou non ? Passez en « % du total de la ligne » pour le voir.
3. L’anomalie. Trouvez la case qui ne suit ni sa ligne ni sa colonne. Écrivez, en une phrase, la question qu’elle pose à la direction commerciale. Pas un jugement sur la personne : une question qu’on peut poser en réunion.

Comment savoir que c’est réussi : le total général affiche 924 657,88 €, et votre question nomme une famille de produits, pas seulement un commercial.

Version courte si le temps manque : la matrice et le premier passage.

Activité bonus : en « % du total de la colonne », qui vend l’Équipement cuisine ? Les cinq parts se lisent en une ligne.

Guide 2D. La formule, demandez-la

À quoi ça sert. Un tableau croisé répond à « combien par qui ». Une formule répond à une question unique, dans une cellule, à côté d’un autre tableau, ou ligne par ligne : la remise consentie sur chaque vente, par exemple. Vous n’avez pas à savoir écrire une formule. Vous devez savoir la demander, la coller, et prouver qu’elle est juste.

Ce qu’il vous faut. Votre assistant, ouvert à côté du tableur, et la bibliothèque de prompts.

A. Décrire votre feuille à l’assistant

Un assistant ne voit pas votre écran. Il écrit une formule juste s’il sait dans quel tableur vous êtes, comment s’appellent vos colonnes et quelle lettre elles portent. Copiez ce texte dans votre conversation, une fois, en remplaçant le tableur :

Je travaille dans [Excel pour Windows / Excel pour Mac / Google Sheets], en français, avec le point-virgule comme séparateur d’arguments. Ma feuille « Ventes » va de la ligne 2 à la ligne 1362. Colonnes : A Date, B N° commande, C Client, D Type client, E Région, F Matricule, G Commercial, H Code produit, I Produit, J Famille, K Canal, L Quantité, M Prix unitaire, N Montant, O Statut. La feuille « Produits » a en A le code produit et en D le prix catalogue, lignes 2 à 18.

B. Poser la question

Puis, dans la même conversation :

Écris-moi la formule qui donne le chiffre d’affaires de la région Occitanie sur les seules commandes dont le statut est « Facturée ». Donne d’abord la formule seule, prête à coller, avec les plages figées par des $. Puis explique chaque argument en une ligne. Si une fonction n’existe pas dans mon tableur, propose l’équivalent.

Vous devez recevoir quelque chose comme ceci :

=SOMME.SI.ENS($N$2:$N$1362;$E$2:$E$1362;"Occitanie";$O$2:$O$1362;"Facturée")

Elle se lit ainsi : additionne la colonne N, pour les lignes où la colonne E vaut « Occitanie » et où la colonne O vaut « Facturée ». Les signes $ bloquent les plages : elles ne se décaleront pas si vous recopiez la formule ailleurs. C’est l’erreur la plus fréquente des débutants, et elle est invisible.

C. Coller la formule

1. Créez une nouvelle feuille, nommez-la Formules.
2. En A1, tapez la question : CA Occitanie, commandes facturées.
3. En B1, collez la formule reçue, telle quelle. Entrée. Vous obtenez 205 259,75 €.
4. Clic droit sur B1, « Format de cellule », « Monétaire », pour lire en euros.

D. Prouver

Une formule reçue est plausible, pas juste. Elle devient juste quand un autre chemin donne le même chiffre.

1. Nouveau tableau croisé : « Région » dans « Lignes », « Statut » dans « Colonnes », « Montant » dans « Valeurs ». La case Occitanie, Facturée doit afficher 205 259,75 €. Si les deux chiffres coïncident, la formule est prouvée. S’ils diffèrent, c’est presque toujours le périmètre : un mot mal écrit dans la condition, une plage trop courte.

E. Si la formule renvoie une erreur

Collez-la telle quelle à l’assistant, avec le code d’erreur, et demandez : « explique-moi pourquoi, en une phrase, et donne-moi la formule corrigée, prête à coller ». Le prompt complet est dans la bibliothèque, 2.2.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. B1 affiche 205 259,75 €, et le tableau croisé Région par Statut affiche le même montant dans la case Occitanie, Facturée.

Atelier 3, quatre formules obtenues de l’assistant

Sur votre feuille « Formules », en autonomie. Quatre questions, quatre formules demandées à l’assistant, et pour chacune une preuve.

1. Le chiffre d’affaires de l’Occitanie sur les commandes facturées. Preuve : le tableau croisé Région par Statut. Attendu : 205 259,75 €.
2. Le nombre de lignes dont le montant dépasse 1 000 €. Preuve : sur l’onglet « Ventes », filtre de la colonne N, « Filtres numériques », « Supérieur à », 1000 : la barre d’état en bas de l’écran affiche le nombre de lignes trouvées. Attendu : 325.
3. Le prix catalogue de chaque ligne, rapatrié depuis la feuille « Produits », dans une colonne P de l’onglet « Ventes » nommée Prix catalogue. Demandez une formule RECHERCHEV, collez-la en P2, puis double-cliquez sur le petit carré en bas à droite de la cellule : elle descend jusqu’à la ligne 1362. Preuve : le test des trois lignes. Ligne 2, KV-5001, doit afficher 78 ; ligne 3, KV-1015, 38 ; ligne 152, KV-1002, 4,20. Ouvrez la feuille « Produits » pour le vérifier à l’œil.
4. La remise consentie sur chaque ligne, en colonne Q nommée Remise, en pourcentage. La formule tient en trois mots : un moins le prix unitaire divisé par le prix catalogue. Puis, dans la feuille « Formules », la remise moyenne : =MOYENNE(Ventes!Q2:Q1362). Attendu : 3,1 %. Preuve : ligne 2, la remise vaut 8 % ; ligne 3, 5 % ; ligne 152, 0 %.

Comment savoir que c’est réussi : les quatre chiffres attendus sont obtenus, et chacun par deux chemins.

Version courte si le temps manque : les questions 1 et 3.

Activité bonus : demandez la formule du chiffre d’affaires hors commandes annulées, toutes régions confondues, et prouvez-la avec votre tableau croisé du guide 2C. Attendu : 924 657,88 €.

Guide 2E. Les dates, par mois et par année

À quoi ça sert. À voir la saisonnalité d’une activité, et à ne jamais conclure sur un mois isolé.

Ce qu’il vous faut. L’onglet « Ventes ». Le fichier couvre du 6 janvier 2025 au 29 juin 2026 : 331 dates différentes. Un tableau de 331 lignes n’est pas une analyse.

A. Le mensuel

1. Nouvelle feuille, nouveau tableau croisé. Nommez-la Mensuel.
2. « Date » dans « Lignes ». « Montant » dans « Valeurs ». « Statut » dans « Filtres », « Annulée » décochée.
3. Excel regroupe le plus souvent tout seul par « Années », « Trimestres » et « Mois », et affiche des lignes « 2025 », « 2026 » qui se déplient. Faites glisser « Trimestres » hors du volet pour ne garder qu’Années et Mois. Si rien n’a été regroupé : clic droit sur une date du tableau, « Grouper », cochez « Mois » et « Années », « OK ».
4. Cochez toujours « Années » avec « Mois ». Groupé par mois seul, le tableur additionne le janvier 2025 et le janvier 2026 dans une seule ligne « janv. ». Le tableau reste parfaitement présentable, et il est faux.

B. Deux années côte à côte

1. Dans le volet, faites glisser « Années » de « Lignes » vers « Colonnes ». Vous avez maintenant les douze mois en lignes, 2025 et 2026 en colonnes, et les six premiers mois se comparent d’un coup d’œil.

Et dans les autres tableurs

Si ça ne marche pas

Comment savoir que c’est réussi. Avril 2025 affiche 80 890 € et août 2025 21 112 €, hors annulées. Février 2026 affiche 28 808 € contre 61 948 € en février 2025.

Atelier 4, la saisonnalité et le cumul

Sur votre tableau « Mensuel », en autonomie.

1. Sur 2025, repérez les deux pics et les deux creux. Kertavel vend à des hôtels et à des restaurants : expliquez ces quatre mois en une phrase.
2. Comparez février 2025 à février 2026, puis mars 2025 à mars 2026. Calculez l’évolution en pourcentage de chacun, à la main ou par formule.
3. Calculez le cumul de janvier à juin pour 2025 et pour 2026, et son évolution.
4. Répondez par écrit : entre février, mars et le cumul, lequel des trois chiffres communiquez-vous à votre direction, et pourquoi ?

Comment savoir que c’est réussi : février 2026 recule d’environ 53 %, mars 2026 progresse d’environ 37 %, et le cumul du semestre recule de 11,8 %.

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

Activité bonus : ajoutez « Famille » dans « Colonnes » du mensuel. Quelle famille fait le creux de l’été ?

Atelier 5, la note d’une page

En binôme. Vous produisez ce que la direction a demandé : 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, elles ne se négocient pas :

• 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 soit pas formulée comme une hypothèse à vérifier.
• Une action proposée par constat, réalisable, datée.

Comment savoir que c’est réussi : un lecteur qui n’était pas dans la salle peut refaire chacun de vos trois chiffres avec votre fichier, et aucune phrase de la note ne juge quelqu’un.

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

Activité bonus : un quatrième constat sur le taux d’annulation par canal de vente. Tableau croisé avec « Canal » en Lignes, « Statut » en Colonnes, « N° commande » en Valeurs, réglé sur « Nombre », puis « % du total de la ligne ». Un canal annule-t-il plus que les autres, et que feriez-vous ?

Mémo de la séance

Le tableau croisé : cliquez dans les données, « Insertion » puis « Tableau croisé dynamique », « Nouvelle feuille ». En « Lignes » et « Colonnes », des étiquettes ; en « Valeurs », des nombres.

Vérifiez le total : il doit égaler celui de la colonne d’origine, 941 844,96 € sur ce fichier. « Nombre de » au lieu de « Somme de » signale du texte dans une colonne de nombres.

Dites de quoi votre chiffre est la somme. Toutes lignes, hors annulées, facturé seulement : trois chiffres pour la même entreprise.

Excluez les annulées avec « Statut » dans « Filtres », ou un segment, qui rend le filtre visible.

Une matrice se lit en trois passages : les totaux, les lignes, les anomalies. Ce que vous en tirez est une question bien posée, pas un jugement.

La formule se demande à l’assistant, avec le tableur, le séparateur et les colonnes avec leur lettre. Elle se colle, puis elle se prouve par un tableau croisé ou par le test des trois lignes.

Les dates se groupent par Mois et Années, jamais par mois seul. On ne conclut jamais sur un mois isolé : on regarde le cumul.

Un tableau croisé ne se met pas à jour tout seul quand les données changent : clic droit, « Actualiser », avant toute communication.