L'essentiel — GROUPBY et PIVOTBY partagent un jeu d'arguments facultatifs qui font des choses qu'un tableau croisé dynamique ne peut pas faire en un seul geste :
PERCENTOFdonne le pourcentage du total dans l'emplacementfonction(=GROUPBY(C2:C1000; D2:D1000; PERCENTOF)), un HSTACK de fonctions renvoie plusieurs métriques à la fois, uneLAMBDAdans ce même emplacement construit une moyenne pondérée, etmatrice_filtren'agrège que les lignes que vous choisissez — le tout en direct. Maîtrisez la grammaire defonctionet vous cesserez de recourir aux colonnes intermédiaires.
=GROUPBY(C2:C1000; D2:D1000; PERCENTOF) ' % de chaque région dans le total général
=GROUPBY(C2:C1000; D2:D1000; HSTACK(SUM; AVERAGE; COUNT)) ' trois métriques, côte à côte
=GROUPBY(C2:C1000; D2:D1000; LAMBDA(v; SUM(v)/COUNT(v))) ' une agrégation personnalisée
=GROUPBY(C2:C1000; D2:D1000; SUM; ; ; ; (E2:E1000="West")) ' seulement les lignes où région = West
L'appel de base de GROUPBY/PIVOTBY — grouper, agréger, déverser — remplace déjà le tableau
croisé dynamique du quotidien. Les arguments facultatifs sont là où ils prennent l'avantage
sur lui. Pourcentage du total, plusieurs agrégations dans une seule grille, une moyenne
pondérée, un pré-filtre sur les lignes : chacun est ici un argument intégré, alors que c'est une
corvée de clic droit et de configuration (ou une impossibilité) dans un tableau croisé
dynamique. La clé qui déverrouille tout cela, c'est de comprendre ce que l'argument
fonction accepte réellement.
Ce que vous allez apprendre
- Le pourcentage du total en un seul mot avec
PERCENTOF— et son piège numéro un - Plusieurs métriques à la fois en empilant les fonctions
- Une agrégation personnalisée en déposant une
LAMBDAdans l'emplacement fonction - Filtrer les lignes avant d'agréger avec
matrice_filtre - Lire les erreurs :
#CALC!,#FIELD!,#SPILL!pointent chacune vers un argument
L'emplacement fonction accepte plus que SUM
L'idée unique derrière chaque astuce de cet article : l'argument fonction ne se limite pas aux
noms intégrés. Il accepte toute fonction êta-réduite — un nom nu comme SUM, une pile de
noms, une fonction d'aide dédiée comme PERCENTOF, ou une LAMBDA complète que vous écrivez.
Dès que vous voyez cet emplacement comme « toute fonction qui transforme une colonne en
récapitulatif », les fonctionnalités avancées cessent d'être des fonctionnalités distinctes pour
devenir un seul argument flexible.
=GROUPBY(lignes; valeurs; SUM) ' un nom intégré
=GROUPBY(lignes; valeurs; PERCENTOF) ' une fonction d'aide dédiée
=GROUPBY(lignes; valeurs; HSTACK(SUM; AVERAGE)) ' plusieurs noms à la fois
=GROUPBY(lignes; valeurs; LAMBDA(v; MAX(v)-MIN(v))) ' votre propre fonction
Le pourcentage du total avec PERCENTOF
Le rapport le plus demandé — « quelle part du total représente chaque groupe ? » — tient en un
seul mot. PERCENTOF additionne chaque groupe et le divise par la somme de tout :
=GROUPBY(C2:C1000; D2:D1000; PERCENTOF) ' Région | % du total général (chaque ligne, p. ex. 0,32)
Le piège : le résultat est une fraction, pas un pourcentage mis en forme. 0,32 vaut
trente-deux pour cent, mais la cellule affiche 0,32 jusqu'à ce que vous appliquiez le format de
nombre Pourcentage (Ctrl+Maj+%) au déversement. Ça surprend tout le monde une fois — le
nombre est juste, l'affichage ne l'est pas, et les gens croient la formule cassée. Mettez la
plage déversée au format Pourcentage et elle affiche 32%. Vous voulez la part et le total
ensemble ? Empilez-les : HSTACK(SUM; PERCENTOF) donne le montant et son pourcentage dans des
colonnes adjacentes.
Plusieurs métriques à la fois en empilant les fonctions
Vous ne lancez pas GROUPBY trois fois pour la somme, la moyenne et le nombre — vous empilez les
fonctions avec HSTACK et obtenez trois colonnes de résultat à
partir d'une seule formule :
=GROUPBY(C2:C1000; D2:D1000; HSTACK(SUM; AVERAGE; COUNT))
' Région | total des ventes | vente moyenne | nombre de commandes
Chaque fonction de la pile devient une colonne, dans l'ordre. C'est le rapport qu'un tableau
croisé dynamique vous oblige à construire en glissant trois fois le même champ dans Valeurs et
en changeant à chaque fois son « Synthétiser les valeurs par » ; ici, c'est une seule expression,
et elle se lit comme la ligne d'en-tête que vous voulez : SUM; AVERAGE; COUNT. Notez la
différence avec l'élargissement de valeurs : plus de colonnes de valeurs agrègent des
données différentes de la même manière ; plus de fonctions agrègent les mêmes données de
différentes manières. Combinez les deux pour une grille de métriques complète.
Une agrégation personnalisée : LAMBDA dans l'emplacement fonction
Quand aucune fonction intégrée ne convient — une moyenne pondérée, une étendue, une valeur
tronquée — écrivez une LAMBDA. Elle reçoit les valeurs de chaque
groupe sous forme de matrice v, et tout ce qu'elle renvoie devient l'agrégat :
=GROUPBY(C2:C1000; D2:D1000; LAMBDA(v; MAX(v) - MIN(v))) ' l'étendue au sein de chaque groupe
=GROUPBY(C2:C1000; D2:D1000; LAMBDA(v; TEXTJOIN(", "; 1; v))) ' lister les membres, sans les totaliser
=GROUPBY(C2:C1000; D2:D1000; LAMBDA(v; SUMPRODUCT(v; w)/SUM(w))) ' une moyenne pondérée (w = poids)
C'est le plafond qu'un tableau croisé dynamique ne peut tout simplement pas atteindre : son menu
« Synthétiser les valeurs par » est une liste figée, tandis que l'emplacement LAMBDA exécute
n'importe quelle logique exprimable. La règle à retenir — votre LAMBDA reçoit les valeurs du
groupe comme un seul argument et doit renvoyer une valeur unique par groupe ; renvoyez une
matrice et vous obtiendrez #CALC!. Moyennes pondérées, médianes d'un sous-ensemble filtré,
« la valeur la plus élevée sous forme de texte » — tout cela vit dans cet emplacement.
Filtrer les lignes avant d'agréger
matrice_filtre (le septième argument de GROUPBY) prend une matrice booléenne de la même
hauteur que vos données et n'agrège que les lignes VRAI — pas de colonne intermédiaire, pas
d'étape de pré-filtrage :
=GROUPBY(C2:C1000; D2:D1000; SUM; ; ; ; (E2:E1000 = "West")) ' seulement les lignes de la région West
=GROUPBY(C2:C1000; D2:D1000; SUM; ; ; ; (YEAR(A2:A1000) = 2026)) ' seulement les lignes de 2026
=GROUPBY(C2:C1000; D2:D1000; SUM; ; ; ; (D2:D1000 >= 1000)) ' seulement les commandes >= 1000
La condition s'écrit comme un test FILTER — une comparaison qui
produit VRAI/FAUX le long de la colonne. La seule règle qui évite l'erreur : la
matrice_filtre doit avoir exactement la même hauteur que champs_lignes et valeurs.
Pointez-la sur E2:E1000 quand vos données vont de E2:E999 et vous obtenez #VALUE! ; gardez
toutes les plages à la même hauteur et ça fonctionne. C'est ainsi que vous construisez des
récapitulatifs « cette année seulement », « hors remboursements » ou « West et East seulement »
sans toucher aux données source.
Lire les erreurs
Les trois erreurs que ces fonctions renvoient pointent chacune vers une faute précise — apprenez la carte et le débogage devient instantané :
#CALC! ' l'emplacement fonction est faux : SUM() au lieu de SUM, ou une LAMBDA qui renvoie une matrice
#FIELD! ' une référence de champ/colonne est erronée — souvent en_têtes_champs mal réglé, donc les en-têtes s'infiltrent
#SPILL! ' le résultat est bloqué — dégagez les cellules en bas et à droite de la formule
#VALUE! ' en général matrice_filtre n'a pas la même hauteur que les données
#CALC! est presque toujours l'argument fonction — un SUM() appelé, ou une LAMBDA qui
renvoie plus d'une valeur par groupe. #FIELD! pointe vers la forme des données, souvent
l'argument en_têtes_champs : si vos plages incluent les cellules d'en-tête, réglez
en_têtes_champs sur 1 pour que les étiquettes ne soient pas groupées comme des données.
#SPILL! est spatial, jamais logique — la formule va bien, c'est la zone d'atterrissage qui ne
va pas. Et #VALUE! sur une agrégation filtrée, c'est le décalage de hauteur ci-dessus. Chaque
erreur est un panneau indicateur vers un argument, pas un mystère.
Le jugement
Dès qu'un récapitulatif réclame le pourcentage du total, plus d'une métrique, un calcul
personnalisé ou un sous-ensemble de lignes, ces arguments facultatifs battent un tableau croisé
dynamique à plate couture — chacun est ici un seul argument, là-bas une plongée dans les menus ou
une impasse. Gardez trois règles en main : l'emplacement fonction prend un nom nu, un ensemble
de noms empilés, ou une LAMBDA qui renvoie une valeur par groupe ; PERCENTOF renvoie une
fraction que vous devez mettre au format pourcentage ; et matrice_filtre doit avoir la
même hauteur que les données. Lisez les erreurs comme une carte — #CALC! désigne la fonction,
#FIELD! les en-têtes, #SPILL! l'espace, #VALUE! la hauteur du filtre — et la dernière excuse
pour garder un tableau croisé dynamique sous la main (qu'il fait les trucs sophistiqués) tombe en
grande partie.
Comment ExcelMaster aide
Les arguments avancés sont puissants et impitoyables — une seule LAMBDA qui renvoie une matrice
et le tout bascule en #CALC!. Dites à ExcelMaster « les ventes de chaque région, sa part
du total et le nombre de commandes » et il écrit
=GROUPBY(C2:C1000; D2:D1000; HSTACK(SUM; PERCENTOF; COUNT)) et met en forme la colonne de
pourcentage pour vous. Demandez « un prix moyen pondéré par catégorie, pondéré par la quantité »
et il construit la LAMBDA. Et quand une formule renvoie #CALC! ou #VALUE!, il lit l'erreur
comme le fait ce guide — en pointant l'argument exact qui est faux au lieu de vous laisser compter
les points-virgules.
Questions fréquentes
Comment afficher le pourcentage du total dans GROUPBY ou PIVOTBY ?
Utilisez PERCENTOF dans l'emplacement fonction : =GROUPBY(C2:C1000; D2:D1000; PERCENTOF).
Il divise la somme de chaque groupe par le total général. Le résultat est une fraction comme
0,32, appliquez donc le format Pourcentage (Ctrl+Maj+%) au déversement pour voir 32%. Pour
afficher le montant et sa part côte à côte, utilisez HSTACK(SUM; PERCENTOF).
GROUPBY peut-il renvoyer plusieurs agrégations à la fois ?
Oui — empilez les fonctions : =GROUPBY(C2:C1000; D2:D1000; HSTACK(SUM; AVERAGE; COUNT)) renvoie
trois colonnes. Chaque fonction devient une colonne de résultat, dans l'ordre. C'est différent de
l'élargissement de la plage valeurs, qui agrège différentes colonnes de la même manière ;
empiler les fonctions agrège la même colonne de plusieurs manières.
Comment utiliser une formule personnalisée comme agrégation ?
Placez une LAMBDA dans l'emplacement fonction. Elle reçoit les valeurs de chaque groupe sous
forme de matrice et doit renvoyer une valeur unique : =GROUPBY(C2:C1000; D2:D1000; LAMBDA(v; MAX(v)-MIN(v))). Cela couvre les moyennes pondérées, les étendues, et tout ce que la liste
intégrée « Synthétiser les valeurs par » ne sait pas faire. Si la LAMBDA renvoie une matrice au
lieu d'une seule valeur, vous obtiendrez #CALC!.
Comment filtrer les lignes que GROUPBY agrège ?
Utilisez l'argument matrice_filtre (le septième) : =GROUPBY(C2:C1000; D2:D1000; SUM; ; ; ; (A2:A1000="West")) n'agrège que les lignes correspondantes. La condition s'écrit comme un test
FILTER, et la matrice booléenne doit avoir la même hauteur que vos
données — un décalage renvoie #VALUE!.
Pourquoi mon GROUPBY renvoie-t-il #CALC! ou #FIELD! ?
#CALC! signifie presque toujours que l'argument fonction est faux : SUM() au lieu de SUM,
ou une LAMBDA qui renvoie une matrice plutôt qu'une valeur par groupe. #FIELD! signifie en
général que l'argument en_têtes_champs est mal réglé — mettez-le à 1 quand vos plages incluent
des cellules d'en-tête, pour que les étiquettes ne soient pas traitées comme des données.
#SPILL! n'a rien à voir avec la logique ; il signifie que des cellules en bas ou à droite
bloquent le résultat.
Testé dans
Testé dans : Excel 365 (Windows 11) — dernière vérification le 2026-07-18.
Guides connexes : Excel GROUPBY · Excel PIVOTBY · Excel LAMBDA · Fonctions d'aide LAMBDA · Excel FILTER