Tableau croisé dynamique Excel : le créer étape par étape

Un tableau croisé dynamique regroupe les lignes d'un tableau par catégorie, comme Region, et totalise un nombre, comme Sales, pour chaque groupe, sans formules. Pour en créer un, cliquez sur une cellule des données, allez dans Insertion > Tableau croisé dynamique, appuyez sur OK, et faites glisser Region vers Lignes et Sales vers Valeurs. Le tableau ci-dessous n'est pas un tableau croisé dynamique : il construit la même synthèse avec des formules, pour que vous voyiez les totaux changer. Il affiche les formules sous leur forme anglaise (SUMIF pour SOMME.SI), avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =SOMME.SI($A$2:$A$9;E2;$C$2:$C$9).

FormulesRéinitialiser

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Cliquez sur une cellule pour voir sa formule. Modifiez un nombre ou une formule et la feuille se recalcule.Dans Excel en français : =SOMME.SI($A$2:$A$9;E2;$C$2:$C$9)

UNIQUE liste chaque région une fois et SOMME.SI la totalise : North 455, South 305 et East 170, soit 49 %, 33 % et 18 % des 930 au total. Remplacez C3 par 185 et le total South ainsi que les trois pourcentages suivent aussitôt. Un tableau croisé dynamique afficherait les mêmes nombres, mais seulement après une actualisation.

Créer un tableau croisé dynamique

Avant de commencer, vérifiez les données sources : une ligne d'en-tête avec un nom dans chaque colonne, un enregistrement par ligne, aucune ligne ni colonne vide au milieu, et aucune ligne de sous-total.

1. Cliquez sur une cellule des données.
2. Allez dans Insertion > Tableau croisé dynamique (dans certaines versions, Insertion > Tableau croisé dynamique > À partir d'un tableau ou d'une plage).
3. Excel remplit la plage. Choisissez Nouvelle feuille de calcul et appuyez sur OK.
4. Un tableau croisé dynamique vide apparaît avec, à droite, le volet Champs de tableau croisé dynamique qui liste vos en-têtes de colonnes.
5. Faites glisser Region dans la zone Lignes et Sales dans la zone Valeurs. Le tableau croisé dynamique affiche chaque région une fois avec Somme de Sales à côté, et une ligne Total général.
6. Pour changer ce qui est affiché, déplacez des champs d'une zone à l'autre ou sortez-les du volet.

Si vous ne savez pas par où commencer, Insertion > Tableaux croisés dynamiques recommandés propose quelques dispositions toutes faites pour vos données. Sur un Mac, le menu est le même : Insertion > Tableau croisé dynamique.

Lignes, Colonnes, Valeurs et Filtres

Le volet Champs de tableau croisé dynamique comporte quatre zones, et chaque tableau croisé dynamique consiste à choisir quelle colonne va dans quelle zone :

• Lignes : les catégories sur le côté gauche, une ligne par valeur distincte (Region).
• Colonnes : les catégories en haut, une colonne par valeur distincte (Product).
• Valeurs : les nombres à calculer pour chaque combinaison. La somme est le calcul par défaut pour une colonne numérique ; Nombre, Moyenne, Max, Min et d'autres se trouvent dans Paramètres des champs de valeurs.
• Filtres : un champ qui filtre tout le tableau croisé dynamique, affiché sous forme de liste déroulante au-dessus.

Avec Region en Lignes, Product en Colonnes et Sales en Valeurs, le tableau croisé dynamique des données ci-dessus ressemble à ceci (étiquettes d'un Excel anglais) :

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

La version en formules de cette disposition liste les régions vers le bas avec UNIQUE, les produits en largeur avec TRANSPOSE(UNIQUE()), et calcule chaque cellule de la grille avec un seul SOMME.SI.ENS qui prend les deux listes comme critères :

FormulesRéinitialiser

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Cliquez sur une cellule pour voir sa formule. Modifiez un nombre ou une formule et la feuille se recalcule.Dans Excel en français : =SOMME.SI.ENS(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1)

E2 répand North, South et East vers le bas, F1 répand Apple et Pear en largeur, et le SOMME.SI.ENS de F2 remplit la grille de 3 sur 2 entre les deux : un total pour chaque couple région et produit. Remplacez Apple par Pear en B5 et les deux cellules East changent. L'ordre est ici celui de première apparition des valeurs ; un tableau croisé dynamique trie ses étiquettes par ordre alphabétique.

Nombre, moyenne ou pourcentage au lieu de la somme

Dans le tableau croisé dynamique, cliquez sur le champ de la zone Valeurs et choisissez Paramètres des champs de valeurs. L'onglet Synthèse des valeurs par permet de passer de Somme à Nombre, Moyenne, Max et Min ; l'onglet Afficher les valeurs transforme les nombres en % du total général, % du total de la colonne, en cumul et autres. Chaque formule a son équivalent direct :

FormulesRéinitialiser

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Cliquez sur une cellule pour voir sa formule. Modifiez un nombre ou une formule et la feuille se recalcule.Dans Excel en français : =UNIQUE(A2:A9)

North a 3 commandes d'une moyenne de 151.7, South 3 d'une moyenne de 101.7, East 2 d'une moyenne de 85.0. La colonne % du total général se trouve dans le premier tableau de cette page.

Filtrer la synthèse sur un produit

La zone Filtres place une liste déroulante au-dessus du tableau croisé dynamique. La version en formules est une cellule avec une liste déroulante et SOMME.SI.ENS, qui ajoute une condition à SOMME.SI. Choisissez un produit en F1 :

FormulesRéinitialiser

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Cliquez sur une cellule pour voir sa formule. Modifiez un nombre ou une formule et la feuille se recalcule.

Avec Apple choisi, North affiche 215, South 150 et East 60. Choisissez Pear et ils passent à 240, 155 et 110. Voir SOMME.SI.ENS pour d'autres conditions et la page liste déroulante pour ajouter la liste dans Excel.

Actualiser un tableau croisé dynamique

Un tableau croisé dynamique garde une copie des données sources (le cache du tableau croisé dynamique) et ne se recalcule pas quand une cellule de la source change. Après avoir modifié les données :

• Faites un clic droit n'importe où dans le tableau croisé dynamique et choisissez Actualiser, ou appuyez sur Alt+F5 sous Windows.
• Données > Actualiser tout (Ctrl+Alt+F5) actualise chaque tableau croisé dynamique du classeur.
• Pour actualiser à chaque ouverture du fichier, faites un clic droit sur le tableau croisé dynamique, choisissez Options du tableau croisé dynamique, et dans l'onglet Données cochez Actualiser les données lors de l'ouverture du fichier.

Les nouvelles lignes ajoutées sous la plage source ne sont pas incluses, même après une actualisation. Modifiez la plage dans Analyse du tableau croisé dynamique > Changer la source de données ou, mieux, transformez la source en tableau avant de créer le tableau croisé dynamique : sélectionnez les données et utilisez Insertion > Tableau (ou son raccourci clavier). Un tableau s'agrandit quand vous ajoutez des lignes, et le tableau croisé dynamique les intègre à l'actualisation suivante.

Totaliser chaque région avec une seule formule

FormulesRéinitialiser

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Cliquez sur une cellule pour voir sa formule. Modifiez un nombre ou une formule et la feuille se recalcule.

À vous : En F2, totalisez avec une seule formule les ventes de chaque région listée en E2:E4.

VérifierVoir la réponse

La réponse répand 455, 305 et 170. Donner à SOMME.SI toute la liste E2:E4 comme critère renvoie un total par région, donc il n'y a rien à recopier vers le bas. Excel 2019 et les versions plus anciennes n'ont ni UNIQUE ni propagation : tapez les régions en E2:E4 et recopiez =SOMME.SI($A$2:$A$9;E2;$C$2:$C$9) vers le bas. Sans les signes $, les plages descendent avec chaque ligne et les totaux sont faux.

GROUPER.PAR et PIVOTER.PAR : un tableau croisé dynamique en une formule

Excel pour Microsoft 365 a deux fonctions qui construisent toute une synthèse à partir d'une seule formule et se recalculent comme n'importe quelle formule, sans actualisation. Elles exigent un abonnement Microsoft 365 à jour. Pour les données ci-dessus :

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

Dans un Excel français, ces formules s'écrivent =GROUPER.PAR(A2:A9;C2:C9;SOMME) et =PIVOTER.PAR(A2:A9;B2:B9;C2:C9;SOMME). GROUPER.PAR (GROUPBY en anglais) prend le champ de lignes, les valeurs et la fonction (SOMME, NBVAL, MOYENNE, MAX, PERCENTOF). PIVOTER.PAR (PIVOTBY en anglais) ajoute un champ de colonnes entre les deux. Les deux trient les étiquettes et ajoutent des lignes de total, comme un tableau croisé dynamique.

Tableau croisé dynamique ou formules : que choisir

Utilisez un tableau croisé dynamique pour explorer des données et répondre une fois à une question ; utilisez des formules pour une synthèse qui figure dans un rapport, alimente d'autres formules et doit toujours être à jour. Pour vérifier les nombres d'un tableau croisé dynamique, reconstruisez une de ses cellules avec SOMME.SI.ENS : si les deux ne concordent pas, le tableau croisé dynamique a en général besoin d'une actualisation ou sa plage source est trop courte.