Fonction PIVOTBY dans Excel — Construire un tableau croisé dynamique avec une formule

L'essentiel — PIVOTBY(champs_lignes; champs_colonnes; valeurs; fonction) est GROUPBY doté d'une seconde dimension. Donnez-lui un champ pour les lignes, un champ pour les colonnes, les valeurs à agréger et l'agrégation — =PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM) — et il déverse un tableau croisé complet : les régions sur le côté, les mois en haut, les totaux dans la grille et les totaux généraux sur les deux bords. C'est un tableau croisé dynamique sous forme de formule : il se recalcule en direct sans Actualiser, et d'autres formules peuvent le référencer. Passez la fonction par son nom (SUM, pas SUM()). Exige Excel 365 (déployée en 2024–2025).

=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM)        ' Région (lignes) x Mois (colonnes), sommé
=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; AVERAGE)    ' même grille, en moyenne
=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; COUNT)      ' combien de commandes dans chaque cellule
=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM; ; 1; ; 1)   ' totaux généraux sur les deux axes

Tout le monde connaît le tableau croisé dynamique : l'objet glisser-déposer que vous insérez, configurez dans un volet latéral et actualisez dès que les données bougent. PIVOTBY vous donne la même grille lignes par colonnes — mais sous la forme d'une seule formule qui vit dans la feuille et se met à jour toute seule. Si GROUPBY répond à « total par une chose », PIVOTBY répond à « total par une chose croisée avec une autre » : les ventes par région et par mois, l'effectif par service et par niveau, le classique rapport en matrice.

Ce que vous allez apprendre

Le modèle mental : PIVOTBY = GROUPBY plus une dimension de colonnes
Les quatre arguments obligatoires, et pourquoi lignes et colonnes ne sont pas interchangeables
Ajouter des totaux sur les deux axes avec les deux arguments profondeur_totaux
Là où PIVOTBY l'emporte sur un tableau croisé dynamique — et là où celui-ci gagne encore
Les pièges : l'ordre des champs, SUM() contre SUM, et #SPILL!

Le modèle mental : GROUPBY avec une seconde dimension

GROUPBY réduit les données le long d'un seul axe — une liste de groupes avec un nombre à côté de chacun. PIVOTBY ajoute un second axe, transformant cette liste en matrice. La même agrégation s'exécute, mais elle est désormais ventilée dans deux sens à la fois :

=GROUPBY(C2:C1000; D2:D1000; SUM)                 ' Région -> une colonne de totaux
=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM)       ' Région x Mois -> une grille de totaux
'         └ lignes    └ colonnes └ valeurs └ comment

Cet argument supplémentaire — champs_colonnes — fait toute la différence. Tout ce que vous savez de GROUPBY (passer la fonction par son nom, le déversement, le recalcul en direct, le tri et les totaux via des arguments) se reporte tel quel. PIVOTBY n'est pas une nouvelle idée à apprendre ; c'est GROUPBY avec un champ de plus pour dire ce qui court en haut du tableau.

Les quatre arguments obligatoires

=PIVOTBY( champs_lignes ; champs_colonnes ; valeurs ; fonction )
'          le long du       en travers du     quoi      comment
'          côté             haut              agréger   agréger

champs_lignes — ce qui court vers le bas le long du bord gauche (Région).
champs_colonnes — ce qui court en travers en haut (Mois).
valeurs — les nombres qui remplissent la grille (Ventes).
fonction — l'agrégation, par son nom (SUM).

Le résultat est une matrice étiquetée dont les deux axes sont dédoublonnés et triés, avec des totaux généraux par défaut. Quatre arguments, et vous avez remplacé tout le parcours Insérer un tableau croisé dynamique → glisser → configurer.

Lignes et colonnes ne sont pas interchangeables

L'erreur la plus fréquente avec PIVOTBY consiste à intervertir les deux premiers arguments et à obtenir une grille techniquement correcte mais illisible — 40 mois sur le côté et 4 régions en travers, ou pire, le même champ des deux côtés.

=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM)   ' 4 régions vers le bas, 12 mois en travers — lisible
=PIVOTBY(B2:B1000; C2:C1000; D2:D1000; SUM)   ' 12 mois vers le bas, 4 régions en travers — transposé

La règle empirique qui garde les rapports lisibles : le champ ayant le moins de valeurs distinctes va dans champs_colonnes (en travers, en haut), et celui qui en a le plus va dans champs_lignes (vers le bas, sur le côté), car une page défile confortablement vers le bas mais n'affiche que quelques colonnes en largeur. Les régions (peu nombreuses) en travers, les dates ou les clients (nombreux) vers le bas. Inversez les deux et la grille se calcule quand même — elle se déverse simplement dans le mauvais sens et déborde de l'écran.

Des totaux sur les deux axes

Un tableau croisé dynamique vous donne des totaux de lignes et des totaux de colonnes ; PIVOTBY aussi, via deux arguments profondeur_totaux distincts — un pour les lignes, un pour les colonnes :

=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM; ; 1; ; 1)   ' ligne de total général ET colonne de total général
=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM; ; 0; ; 0)   ' aucun total sur l'un ou l'autre axe
=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM; ; 1; ; 0)   ' total de lignes seul (colonne de total, pas de ligne de total)

L'ordre des arguments est en_têtes_champs, puis profondeur_totaux_lignes, puis ordre_tri_lignes, puis profondeur_totaux_colonnes — de sorte que les deux totaux qui vous intéressent se trouvent aux positions 6 et 8, d'où les points-virgules de saut. 1 signifie « afficher le total général », 0 signifie « le masquer ». Pouvoir totaliser un axe mais pas l'autre est quelque chose qu'un vrai tableau croisé dynamique rend fastidieux et que PIVOTBY réduit à un seul chiffre.

Là où PIVOTBY gagne — et là où le tableau croisé dynamique gagne encore

C'est un vrai compromis, pas un remplacement, et se tromper de choix fait perdre du temps dans les deux cas.

PIVOTBY l'emporte quand le rapport doit rester vivant et connecté. Il se recalcule à l'instant où les données changent — pas d'Actualiser, pas de cache périmé — c'est donc le bon outil pour un tableau de bord qui se met à jour à mesure que les chiffres arrivent. Comme c'est une formule, d'autres formules peuvent référencer ses cellules, et elle vit dans le contrôle de version et se compare (diff) comme n'importe quel texte. Elle n'a aucun volet de configuration à mal régler.

Un tableau croisé dynamique classique gagne encore quand vous voulez explorer. Glisser des champs entre lignes et colonnes à la volée, déposer des segments et des chronologies, double-cliquer sur une cellule pour descendre dans les lignes source, regrouper des dates en trimestres d'un clic droit, ou croiser des dizaines de millions de lignes depuis le modèle de données — rien de tout cela n'est le rôle de PIVOTBY. PIVOTBY restitue un rapport que vous avez déjà décidé ; un tableau croisé dynamique est un outil interactif pour décider ce que le rapport devrait être.

La formule honnête : PIVOTBY pour le reporting, les tableaux croisés dynamiques pour l'exploration. Si la disposition est arrêtée et doit rester à jour, utilisez la formule. Si vous fouillez encore les données pour voir ce qui compte, utilisez l'objet.

Tous les pièges réunis

Trois échecs couvrent presque tous les PIVOTBY qui « ne marchent pas » :

=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM())   ' FAUX — passez SUM, pas SUM()
=PIVOTBY(C2:C1000; C2:C1000; D2:D1000; SUM)      ' FAUX — même champ en lignes ET en colonnes = un fouillis diagonal
=PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM)      ' #SPILL! si une cellule dont la grille a besoin est occupée

D'abord, la fonction est un nom nu — la même règle SUM et non SUM() que pour GROUPBY. Ensuite, champs_lignes et champs_colonnes doivent être des champs différents ; passer le même donne une diagonale dénuée de sens. Enfin, PIVOTBY déverse tout un rectangle : donc si quoi que ce soit occupe l'emplacement où la grille doit se poser, vous obtenez #SPILL! — dégagez l'espace en bas et à droite, et souvenez-vous que la grille grandit à mesure que de nouveaux mois ou de nouvelles régions apparaissent dans les données.

Le jugement

Recourez à PIVOTBY dès que vous avez besoin d'un tableau croisé qui reste à jour et alimente le reste de la feuille — une matrice vivante sur un tableau de bord, une grille dont d'autres formules lisent les cellules. Gardez le tableau croisé dynamique pour l'exploration à la main : segments, descente dans les détails (drill-through), glisser-déposer, sources énormes du modèle de données. Les règles mécaniques qui préviennent les erreurs habituelles : les catégories les moins nombreuses vont en travers (champs_colonnes) et les plus nombreuses vont vers le bas (champs_lignes) ; la fonction est un nom nu ; et les deux arguments profondeur_totaux (les lignes en position 6, les colonnes en position 8) activent et désactivent les totaux généraux axe par axe. Apprenez la grammaire des arguments une bonne fois — ce n'est que GROUPBY avec un champ de colonnes — et le rapport en matrice cesse d'être un objet que vous reconstruisez pour devenir une formule qui se maintient honnête toute seule.

Comment ExcelMaster aide

La puissance de PIVOTBY se cache derrière un mur d'arguments positionnels à points-virgules de saut — les totaux que vous voulez sont aux positions 6 et 8, et il est facile de régler le mauvais. Dites à ExcelMaster « les ventes par région croisées avec les mois, avec des totaux des deux côtés » et il écrit =PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM; ; 1; ; 1) avec chaque point-virgule à sa place. Demandez-lui « d'intervertir les lignes et les colonnes » ou « de compter les commandes au lieu de sommer les ventes » et il modifie le bon argument. Et si votre grille renvoie #SPILL! ou ressort transposée, il vous explique quel argument changer — sans deviner laquelle des onze positions vous avez manquée.

Questions fréquentes

Que fait PIVOTBY dans Excel ?

PIVOTBY(champs_lignes; champs_colonnes; valeurs; fonction) construit un tableau croisé : il groupe les données vers le bas par un champ pour les lignes et en travers par un autre pour les colonnes, agrège les valeurs de chaque cellule avec fonction, et déverse une grille étiquetée avec des totaux généraux. C'est un tableau croisé dynamique exprimé sous forme d'une seule formule, et il se recalcule automatiquement quand les données changent.

En quoi PIVOTBY diffère-t-il d'un tableau croisé dynamique ordinaire ?

PIVOTBY est une formule vivante — elle se met à jour instantanément sans Actualiser, ses cellules peuvent être référencées par d'autres formules, et il n'y a aucun volet de configuration. Un tableau croisé dynamique classique est un objet interactif : vous pouvez glisser des champs, ajouter des segments, descendre jusqu'aux lignes source et croiser des sources du modèle de données de dizaines de millions de lignes. Utilisez PIVOTBY pour un reporting qui doit rester à jour ; utilisez un tableau croisé dynamique pour explorer.

Pourquoi PIVOTBY renvoie-t-il une erreur ou une diagonale bizarre ?

Deux causes habituelles. S'il renvoie une erreur du genre #CALC!, vous avez probablement écrit SUM() au lieu de SUM — l'argument fonction est un nom nu. Si le résultat ressemble à une diagonale dénuée de sens, vous avez passé le même champ à champs_lignes et à champs_colonnes ; ils doivent être des champs différents. Un #SPILL! signifie que quelque chose bloque la grille — dégagez les cellules en bas et à droite.

Comment ajouter des totaux de lignes et de colonnes dans PIVOTBY ?

Utilisez les deux arguments de profondeur de totaux : =PIVOTBY(C2:C1000; B2:B1000; D2:D1000; SUM; ; 1; ; 1). Le premier 1 (position 6) ajoute une colonne de total général pour les lignes ; le second 1 (position 8) ajoute une ligne de total général pour les colonnes. Mettez l'un ou l'autre à 0 pour masquer ce total. Les points-virgules vides sautent en_têtes_champs et les deux arguments de tri.

Quelles versions d'Excel ont PIVOTBY ?

PIVOTBY est une fonction Microsoft 365 sortie aux côtés de GROUPBY au fil de 2024–2025 sur le Canal actuel. Elle n'existe pas dans Excel 2021, 2019 ou 2016. Si =PIVOTBY(...) renvoie #NAME?, votre version ne l'inclut pas encore ; un tableau croisé dynamique classique est la solution de repli.

Testé dans

Testé dans : Excel 365 (Windows 11) — dernière vérification le 2026-07-18.

Guides connexes : Excel GROUPBY · GROUPBY et PIVOTBY — Avancé · Excel SUMIFS · Excel FILTER · Excel UNIQUE