Mettre ses données sous forme de tableau

Le format liste, condition de tout le reste

Un tableau croisé dynamique (TCD) ne fait que résumer une liste de données. Si la liste est mal construite, le TCD sera faux ou impossible à créer. Une bonne source respecte le format liste :

une seule ligne d’en-têtes, avec des noms uniques et non vides ;
une ligne par enregistrement (une vente, une facture, une écriture) ;
une colonne par information (Date, Région, Commercial, Catégorie, Quantité, Prix) ;
aucune ligne ni colonne vide, aucune cellule fusionnée ;
aucun sous-total ni total dans les données : c’est le TCD qui les calculera ;
un seul type par colonne : de vraies dates, de vrais nombres.

Un défaut fréquent est le tableau « de présentation », avec les mois en colonnes (Janvier, Février, Mars…). Il se lit bien mais se résume mal. Le format liste, avec une colonne Date, est celui qu’il faut fournir au TCD.

Transformer la plage en tableau

Cliquez dans les données, puis Insertion > Tableau (raccourci Ctrl+L dans la version française d’Excel, Ctrl+T dans la version anglaise). Vérifiez que la case Mon tableau comporte des en-têtes est cochée. La plage devient un tableau structuré : lignes en bandes, flèches de filtre, et un onglet Création de tableau dans le ruban.

Première action : donner un nom au tableau, dans Création de tableau > Nom du tableau, par exemple Ventes (sans espace). Ce nom servira dans les formules et comme source du TCD.

Avantages décisifs :

le tableau s’étend automatiquement quand vous saisissez une ligne juste en dessous ou une colonne juste à droite ;
un TCD construit sur Ventes prendra en compte les nouvelles lignes à la prochaine actualisation, sans modifier sa source ;
les en-têtes restent visibles à la place des lettres de colonnes quand vous faites défiler.

Colonnes calculées et références structurées

Ajoutez l’en-tête Montant en G1 : la colonne rejoint le tableau. En G2, tapez = puis cliquez sur E2, *, puis F2. Excel écrit :

=[@Quantite]*[@PrixUnitaire]

C’est une référence structurée : [@Quantite] signifie « la valeur de la colonne Quantite sur cette ligne ». À la validation, la formule est automatiquement recopiée dans toute la colonne : c’est une colonne calculée. Elle se lit comme une phrase et ne dépend pas de l’emplacement des cellules.

Hors du tableau, on désigne une colonne entière par NomDuTableau[Colonne] :

=SOMME(Ventes[Montant])                               -> 22820
=SOMME.SI.ENS(Ventes[Montant];Ventes[Region];"Nord")  -> 10680

Ces formules restent justes quand le tableau grandit : plus besoin de modifier G2:G21 en G2:G22.

La ligne de total

Cochez Création de tableau > Ligne Total. Une ligne apparaît sous le tableau ; chaque cellule propose une liste (Somme, Moyenne, Nombre, Max…). Elle utilise SOUS.TOTAL : elle suit donc les filtres du tableau. Décochez-la avant de créer un TCD si vous préférez, mais elle n’est pas comptée dans les données du TCD.

Vérifier avant de croiser

Avant de passer au TCD, contrôlez : les dates sont alignées à droite (vraies dates), les quantités et prix aussi (vrais nombres), aucun libellé ne varie (Nord et nord donneraient deux lignes distinctes dans le TCD). Un filtre sur chaque colonne permet de repérer en un coup d’œil les valeurs mal saisies.

Exercices

Les solutions et les tests restent sur le serveur. Dans l'espace étudiant, ton code est exécuté et corrigé, et le tuteur donne des indices avant la solution.

TâcheDifficulté 12 indices
Créer le tableau Ventes
Créez avec le Bloc-notes le fichier C:\Formation\ventes_s1.csv, collez-y le contenu ci-dessous, enregistrez-le, puis ouvrez-le dans Excel par un double-clic. Les données occupent la plage A1:F21. Si tout arrive dans la colonne A, utilisez Données > Convertir avec le séparateur point-virgule. Enregistrez ensuite le classeur sous ventes_s1.xlsx.
```
Date;Region;Commercial;Categorie;Quantite;PrixUnitaire
06/01/2026;Nord;Martin;Logiciel;4;300
12/01/2026;Sud;Bernard;Formation;2;450
19/01/2026;Est;Petit;Materiel;15;80
27/01/2026;Nord;Leroy;Maintenance;5;120
03/02/2026;Sud;Moreau;Logiciel;3;300
10/02/2026;Est;Petit;Formation;1;450
17/02/2026;Nord;Martin;Materiel;20;80
24/02/2026;Sud;Bernard;Maintenance;8;120
04/03/2026;Nord;Leroy;Logiciel;6;300
11/03/2026;Est;Petit;Logiciel;2;300
18/03/2026;Sud;Moreau;Formation;3;450
25/03/2026;Nord;Martin;Maintenance;4;120
07/04/2026;Est;Petit;Materiel;10;80
14/04/2026;Sud;Bernard;Logiciel;5;300
21/04/2026;Nord;Leroy;Formation;2;450
05/05/2026;Sud;Moreau;Materiel;12;80
12/05/2026;Nord;Martin;Logiciel;7;300
19/05/2026;Est;Petit;Maintenance;6;120
09/06/2026;Sud;Bernard;Formation;4;450
23/06/2026;Nord;Leroy;Materiel;25;80
```
1. Transformez la plage en tableau avec en-têtes et nommez-le Ventes.
2. Ajoutez en G1 la colonne Montant calculée par =[@Quantite]*[@PrixUnitaire].
3. Activez la ligne de total et affichez la somme de Montant et de Quantite.
4. Saisissez sous le tableau la ligne 30/06/2026;Est;Petit;Logiciel;1;300 (une valeur par colonne) et observez la ligne de total.
5. Supprimez cette ligne de test (clic droit > Supprimer > Lignes de tableau), désactivez la ligne de total et enregistrez sous ventes_s1.xlsx.
- Le tableau s'appelle Ventes (Création de tableau > Nom du tableau)
- G2 contient =[@Quantite]*[@PrixUnitaire] et la formule s'est propagée jusqu'à G21
- La ligne de total affiche 22820 pour Montant et 144 pour Quantite
- Après ajout de la ligne de test, la ligne Montant de cette vente affiche 300 et le total 23120, sans recopie manuelle
TâcheDifficulté 22 indices
Formules avec références structurées
Partez du classeur ventes_s1.xlsx préparé à la leçon 1 : un tableau nommé Ventes (A1:G21) avec la colonne calculée Montant (total 22 820). Si vous ne l’avez pas, refaites l’exercice 1 de la leçon 1.

Dans une zone libre à droite du tableau (par exemple I1:J4) :
1. En I1, saisissez Total et en J1, calculez la somme de la colonne Montant avec une référence structurée.
2. En I2, saisissez Nord et en J2, calculez le total des montants de la région Nord avec SOMME.SI.ENS et des références structurées.
3. En I3, saisissez Ventes Logiciel et en J3, comptez les ventes de la catégorie Logiciel.
4. En I4, saisissez Prix moyen pondéré et en J4, divisez la somme des montants par la somme des quantités.
- Aucune formule ne contient d'adresse de type G2:G21 : seulement Ventes[...]
- J1 affiche 22820 et J2 affiche 10680
- J3 affiche 6
- J4 affiche 158,47 (avec deux décimales)
QCMDifficulté 11 indice
Une source bien construite
Lequel de ces défauts empêche de grouper les dates par mois dans un tableau croisé dynamique ?
- Le tableau a des lignes en bandes
- Certaines dates sont saisies comme du texte
- Le tableau s'appelle Ventes
- La colonne Montant est calculée

Quiz de fin de leçon

Quel raccourci crée un tableau dans la version française d’Excel ?
- Ctrl+L
- Ctrl+Maj+L
- Alt+F5
Que signifie [@Quantite] dans une formule de tableau ?
- La somme de la colonne Quantite
- La valeur de la colonne Quantite sur la même ligne
- La première cellule de la colonne
Pourquoi ne pas laisser de sous-totaux dans les données sources ?
- Ils ralentissent Excel
- Le TCD les additionnerait avec les lignes de détail et doublerait les montants
- Ils empêchent l'enregistrement

La suite du cours

Créer et organiser un tableau croisé dynamique20 min
Champs calculés et regroupements20 min
Segments et chronologies15 min
Graphiques croisés dynamiques15 min

Créer mon parcours Voir le programme complet

Excel : tableaux croisés dynamiques · Intermédiaire · 1 h 25

Objectifs