GROUPER.PAR et PIVOTER.PAR dans Excel : alternative aux tableaux croisés dynamiques

📍 Article principal : Excel pour PME : tableaux structurés, fonctions dynamiques, Copilot et Python
Ce tutoriel utilise les fonctions GROUPER.PAR (GROUPBY) et PIVOTER.PAR (PIVOTBY) pour produire des agrégations par formule équivalentes à un TCD, avec mise à jour automatique.

Objectif

Construire des agrégations dynamiques par formule via GROUPER.PAR et PIVOTER.PAR à partir d’un tableau structuré, avec sous-totaux, tris et formats personnalisés.

Disponibilité officielle

Source : support.microsoft.com — Fonction PIVOTBY.

• Excel pour Microsoft 365 (Windows, Mac).
• Excel 2024, Excel 2024 pour Mac.
• Excel 2021, Excel 2021 pour Mac.

La fonction GROUPER.PAR partage la même disponibilité (page support GROUPBY function). Excel pour le web : confirmé disponible depuis l’annonce generally available d’octobre 2024 (Microsoft 365 Insider blog). Excel 2019 et antérieurs : non supportés — utiliser un TCD.

Différence avec un TCD

Prérequis

• Excel pour Microsoft 365, Excel 2024 ou Excel 2021.
• Une source de données en tableau structuré (Ctrl+T).
• Connaissances Excel : fonctions et plages dynamiques.
• Niveau : intermédiaire.
• Temps : 60 minutes.

Syntaxe officielle

GROUPER.PAR

=GROUPER.PAR(champ_lignes; valeurs; fonction; [en_tetes]; [total_general]; [profondeur_tri]; [filtre]; [filtre_champ])

PIVOTER.PAR

=PIVOTER.PAR(champ_lignes; champ_colonnes; valeurs; fonction; [en_tetes]; [total_lignes]; [tri_lignes]; [total_colonnes]; [tri_colonnes]; [filtre])

Arguments principaux :

• champ_lignes : colonne ou plage utilisée pour grouper les lignes.
• champ_colonnes : colonne utilisée pour étaler en colonnes (PIVOTER.PAR uniquement).
• valeurs : colonne agrégée.
• fonction : fonction d’agrégation — accepte SOMME, MOYENNE, NB, NBVAL, MAX, MIN, ECARTYPE, MEDIAN, et toute fonction LAMBDA personnalisée.

Étape 1 — Préparer la source

Créer un tableau structuré Ventes avec les colonnes : Date, Region, Commercial, Produit, Quantite, Montant. Ctrl+T, valider, renommer le tableau via Création de tableau → Nom du tableau en Ventes.

Étape 2 — Premier GROUPER.PAR : CA par région

Sur une nouvelle feuille, en cellule A1 :

=GROUPER.PAR(Ventes[Region]; Ventes[Montant]; SOMME)

Sortie attendue : un tableau qui se déverse à partir de A1, avec une ligne par région et le total de la colonne Montant. Excel ajoute un total général à la dernière ligne par défaut.

Pour ajouter les en-têtes de colonne :

=GROUPER.PAR(Ventes[Region]; Ventes[Montant]; SOMME; 3)

L’argument 3 = en-têtes affichés. Valeurs : 0 = aucun, 1 = en-têtes seulement, 2 = total général seulement, 3 = en-têtes + total général.

Étape 3 — Plusieurs niveaux de groupage

Pour grouper par région ET commercial (deux niveaux hiérarchiques) :

=GROUPER.PAR(Ventes[[Region]:[Commercial]]; Ventes[Montant]; SOMME; 3; 0; -2)

Le paramètre profondeur_tri = -2 trie par le total agrégé, du plus grand au plus petit, sur le 2e niveau. Valeurs courantes :

• 1 : tri ascendant alphabétique sur le 1er niveau.
• -1 : tri descendant.
• 2 / -2 : tri sur la 2e colonne.

Étape 4 — PIVOTER.PAR : croisé région × trimestre

Pour produire un tableau croisé région en lignes / trimestre en colonnes :

=PIVOTER.PAR(Ventes[Region]; ARRONDI.SUP(MOIS(Ventes[Date])/3;0); Ventes[Montant]; SOMME; 3; 0; 0; 3; 0)

L’expression ARRONDI.SUP(MOIS([Date])/3;0) calcule le numéro de trimestre (1 à 4). Pour passer par Année-Trimestre :

=PIVOTER.PAR(Ventes[Region]; ANNEE(Ventes[Date])&"-T"&ARRONDI.SUP(MOIS(Ventes[Date])/3;0); Ventes[Montant]; SOMME; 3; 0; 0; 3; 0)

Étape 5 — Filtre intégré

Pour ne grouper que les lignes ayant Statut = « Validé » :

=GROUPER.PAR(Ventes[Region]; Ventes[Montant]; SOMME; 3; 0; -2; Ventes[Statut]="Validé")

L’argument filtre est une expression booléenne évaluée par ligne. Pour combiner plusieurs critères :

=GROUPER.PAR(Ventes[Region]; Ventes[Montant]; SOMME; 3; 0; -2; (Ventes[Statut]="Validé")*(Ventes[Date]>=DATE(2026;1;1)))

Étape 6 — Agrégation personnalisée via LAMBDA

Pour une mesure non standard, par exemple Marge moyenne en pourcentage :

=GROUPER.PAR(Ventes[Region]; Ventes[[Montant]:[Cout]]; LAMBDA(plage; MOYENNE((INDEX(plage;;1)-INDEX(plage;;2))/INDEX(plage;;1))); 3)

La fonction LAMBDA reçoit la plage agrégée et calcule (Montant − Coût) / Montant moyen sur le groupe. La syntaxe peut sembler dense ; elle expose toute la flexibilité de l’agrégation.

Étape 7 — Mise en forme du résultat

Le résultat de GROUPER.PAR et PIVOTER.PAR est une plage déversée (« spilled range »). On peut la référencer entièrement via A1#.

Mise en forme conditionnelle : Accueil → Mise en forme conditionnelle → Nouvelle règle. Appliquer à la plage =$A$1# pour que la mise en forme suive automatiquement la taille du résultat.

Pour le format de nombre (montants en EUR), sélectionner les cellules de valeur, Accueil → Format → Format de cellule → Comptabilité → Symbole : € EUR. Le format reste appliqué même quand le tableau s’agrandit.

Étape 8 — Récupération de valeurs spécifiques

Pour exploiter le résultat dans une autre formule (par exemple part de la première région) :

=INDEX(A1#;2;2)/SOMME(INDEX(A1#;;2))

Le INDEX sur une plage déversée extrait une cellule précise. Le 2;2 = ligne 2 (1ère région après l’en-tête), colonne 2 (Total).

Vérification

• La somme des sous-totaux d’un GROUPER.PAR doit égaler la SOMME(Ventes[Montant]) sur la même source.
• Ajouter une ligne au tableau source. Le résultat de GROUPER.PAR doit se mettre à jour sans actualisation manuelle.
• Comparer un PIVOTER.PAR avec un TCD basé sur la même source : les totaux doivent être identiques.

Erreurs fréquentes

Tutoriels associés

• Tableaux croisés dynamiques Excel — comprendre l’alternative interactive.
• Copilot dans Excel — générer ces formules en langage naturel.
• Python dans Excel — pandas pivot_table comme alternative externe.

Références officielles

• 🔝 Retour au guide de référence
• Fonction GROUPBY / GROUPER.PAR
• Fonction PIVOTBY / PIVOTER.PAR
• Fonction LAMBDA
• Tableaux dynamiques et plages déversantes

FAQ

GROUPER.PAR remplace-t-il le TCD ?
Non. Le TCD reste pertinent pour l’analyse exploratoire interactive (drill-down, segments multiples, mise en forme rapide). GROUPER.PAR est adapté aux tableaux de bord automatisés et aux résultats à intégrer dans d’autres formules.

Limite pratique de volume ?
Recalcul à chaque modification de la source. Pour des sources de plus de 100 000 lignes, la performance se dégrade. Au-delà, basculer vers un TCD avec cache ou un modèle Power Pivot.

Compatible avec Excel pour le web ?
Oui pour Microsoft 365 — la fonction est exposée sur Excel pour le web sur les éditions commerciales éligibles.

Comment trier sur un total décroissant ?
Argument profondeur_tri avec valeur négative. -2 trie sur la 2e colonne (le total) du plus grand au plus petit.

Combiner plusieurs colonnes de regroupement ?
Utiliser une plage de plusieurs colonnes contigües : Ventes[[Region]:[Commercial]]. Pour des colonnes non contigües, créer une colonne calculée combinant les valeurs.

Différence GROUPER.PAR vs PIVOTER.PAR ?
GROUPER.PAR n’a que des lignes de regroupement. PIVOTER.PAR ajoute un axe colonnes pour étaler en tableau croisé.

Tutoriel Excel adapté aux PME et professionnels du Sénégal, Côte d’Ivoire, Mali, Burkina Faso, Niger, Togo, Bénin, Guinée et Mauritanie. Devise FCFA, plan comptable SYSCOHADA, contexte ouest-africain.