Qu'est-ce que la fonction PIVOTER.
PIVOTER.PAR (PIVOTBY en anglais) te sert dès que ton tableau croisé doit vivre à l'intérieur d'une autre formule, ou qu'il doit se reconstruire tout seul dans un tableau de bord sans qu'un utilisateur pense à cliquer sur Actualiser.
C'est la réponse d'Excel 365 à un problème classique : les tableaux croisés dynamiques classiques nécessitent une actualisation manuelle, ne s'intègrent pas dans une chaîne de formules, et s'arrachent à déplacer. PIVOTER.PAR s'affranchit de ces limites. Résumé des ventes par région et par produit, suivi des effectifs par département et type de contrat, analyse budgétaire par campagne et par mois : tout ce qui demandait un TCD s'écrit désormais en une ligne.
Syntaxe
=PIVOTER.PAR(lignes_par; valeurs; fonction_valeurs; [colonnes_par]; [fonction_lignes]; [fonction_colonnes])Clique sur un argument pour aller à son explication.
PIVOTER.PAR est exclusive à Excel 365 (abonnement actif). Elle n'existe ni dans Excel 2021 ni dans les versions antérieures : utiliser la formule sur une version incompatible renvoie #NOM?.
Comprendre chaque paramètre de la fonction PIVOTER.PAR
Les trois premiers arguments sont obligatoires et tiennent dans l'ordre : d'abord la colonne que tu regroupes en lignes, puis la colonne de chiffres à résumer, enfin la fonction d'agrégation (SOMME, MOYENNE, NB…) écrite sans parenthèses. Les trois suivants sont facultatifs.
Dès que tu sautes l'un des optionnels pour atteindre le suivant, garde sa place avec un point-virgule vide. Pour trier les lignes sans découper en colonnes, c'est le fameux ;; : =PIVOTER.PAR(A:A;B:B;SOMME;;LAMBDA(x;TRIER(x;-1))) saute colonnes_par et va directement à fonction_lignes.
lignes_par
: la colonne ou plage qui contient les étiquettes à regrouper en lignes dans ton tableau croiséPar exemple, si tu veux un tableau Région × Produit, c'est ici que tu mets ta colonne Région.
PIVOTER.PAR identifie automatiquement toutes les valeurs uniques de cette plage et crée une ligne par valeur distincte dans le résultat. Si tu passes plusieurs colonnes (par exemple Région et Ville), chaque combinaison unique devient une ligne.
valeurs
: la colonne ou plage contenant les valeurs numériques à agrégerC'est le chiffre que tu veux résumer : montant des ventes, nombre d'heures, budget dépensé, etc.
lignes_par, valeurs et colonnes_par (si présent) doivent avoir exactement le même nombre de lignes. Une taille différente renvoie l'erreur #VALEUR!.
Attention : Si ta plage valeurs a un nombre de lignes différent de lignes_par, Excel renvoie #VALEUR! sans prévenir. Vérifie toujours que les trois plages couvrent exactement les mêmes lignes.
fonction_valeurs
: la fonction d'agrégation à appliquer aux valeurs de chaque groupe : SOMME, MOYENNE, NB, MAX, MIN, etcTu passes la fonction par son nom sans parenthèses ni guillemets.
Tu peux aussi utiliser une fonction LAMBDA personnalisée pour des calculs plus complexes, par exemple LAMBDA(x;SOMME(x)/NB(x)*1,1) pour calculer une moyenne majorée de 10%.
[colonnes_par]
: paramètre facultatif : la colonne ou plage qui contient les étiquettes à regrouper en colonnes dans ton tableau croisé(facultatif)Sans ce paramètre, PIVOTER.PAR produit un tableau à une seule dimension (une colonne de résultats, une ligne par groupe).
Si tu passes une colonne ici (par exemple Produit), PIVOTER.PAR crée autant de colonnes de résultats que de valeurs uniques dans cette plage.
[fonction_lignes]
: paramètre facultatif : une fonction ou LAMBDA appliquée aux étiquettes de lignes pour les trier ou les filtrer avant l'affichage(facultatif)Par exemple, LAMBDA(x;TRIER(x)) trie les lignes par ordre alphabétique, et LAMBDA(x;TRIER(x;-1)) les trie en ordre décroissant.
Si tu omets ce paramètre, les lignes apparaissent dans leur ordre naturel d'occurrence dans les données source.
[fonction_colonnes]
: paramètre facultatif : même principe que `fonction_lignes`, appliqué cette fois aux étiquettes de colonnes(facultatif)Utilise LAMBDA(x;TRIER(x)) pour forcer un ordre alphabétique des en-têtes de colonnes du tableau croisé.
Exemples pratiques pas à pas
Comment croiser les ventes par région et par trimestre avec les fonctions PIVOTER.PAR et SOMME
La réunion mensuelle arrive et on attend de toi le chiffre par région et par trimestre, pas la liste brute des douze ventes. Tes trois colonnes contiennent la région, le trimestre et le montant, une ligne par vente, avec plusieurs lignes pour la même région. La fonction PIVOTER.PAR croise tout ça et sort le tableau à double entrée d'un coup, sans passer par un tableau croisé dynamique.
- 1Dans une cellule, écris
=PIVOTER.PAR(. - 2En 1er argument : sélectionne la colonne qui deviendra les lignes du tableau croisé,
A2:A13. La fonction y repère les valeurs distinctes (Est,NordetSud) et consacre une ligne à chacune. - 3En 2ᵉ argument : sélectionne la colonne qui deviendra les colonnes,
B2:B13. Elle reçoit le même traitement mais à l'horizontale,T1etT2devenant deux colonnes du résultat. - 4En 3ᵉ argument : sélectionne la colonne de chiffres à résumer,
C2:C13. C'est la seule des trois plages qui contient des montants. - 5En 4ᵉ argument : saisis la fonction qui agrège chaque groupe,
SOMME. Tu la passes par son nom seul, sans parenthèses ni arguments, car c'est la fonction PIVOTER.PAR qui l'applique elle-même à chaque croisement de région et de trimestre. - 6Ferme la parenthèse et appuie sur Entrée.
=PIVOTER.PAR(A2:A13; B2:B13; C2:C13; SOMME)Nord et T1 apparaissent deux fois dans les données (15000 et 8000), d'où les 23000 affichés. La fonction ajoute d'elle-même une ligne et une colonne Total, et le grand total 125000 correspond bien à la somme des douze ventes. Les régions ressortent classées de A à Z, et non dans leur ordre d'apparition.
Comment calculer la moyenne des ventes par région avec les fonctions PIVOTER.PAR et MOYENNE
Le total par région récompense mécaniquement celles qui ont fait le plus de ventes, et tu veux savoir combien pèse une vente typique dans chacune. Les données ne changent pas, la question si. La fonction PIVOTER.PAR garde exactement la même structure : c'est l'argument d'agrégation qui décide de ce qu'on lit.
- 1Dans une cellule, écris
=PIVOTER.PAR(. - 2En 1er argument : sélectionne à nouveau la colonne des régions,
A2:A13. Elle forme les lignes du tableau, exactement comme dans l'exemple précédent. - 3En 2ᵉ argument : sélectionne la colonne des trimestres,
B2:B13. Elle forme les colonnes,T1etT2restant les deux mêmes en-têtes. - 4En 3ᵉ argument : sélectionne la colonne des montants,
C2:C13. Ce sont les chiffres à agréger, exactement les mêmes que pour le total. - 5En 4ᵉ argument : saisis
MOYENNEà la place deSOMME. La fonction applique désormais la moyenne à chaque groupe, sans que tu touches à la structure du tableau. - 6Ferme la parenthèse et appuie sur Entrée.
=PIVOTER.PAR(A2:A13; B2:B13; C2:C13; MOYENNE)Est et T1 regroupent deux ventes (9000 et 5500), d'où les 7250. La ligne Total moyenne les six ventes du trimestre, ce qui donne 9416,66667 pour T1 et explique ses décimales. Le tableau produit n'hérite d'aucun format monétaire, les montants s'affichant bruts, sans euro ni séparateur de milliers.
Comment supprimer la ligne de total avec la fonction PIVOTER.PAR
Ton tableau croisé part dans un rapport qui calcule déjà ses propres totaux plus bas, et la ligne Total que la fonction PIVOTER.PAR ajoute d'office fait doublon. Elle fausserait même la lecture si quelqu'un additionnait la colonne sans la remarquer. Deux arguments facultatifs suffisent à la retirer.
- 1Dans une cellule, écris
=PIVOTER.PAR(. - 2En 1er argument : sélectionne la colonne des régions à mettre en lignes,
A2:A13. - 3En 2ᵉ argument : sélectionne la colonne des trimestres à mettre en colonnes,
B2:B13. - 4En 3ᵉ argument : sélectionne la colonne des montants à agréger,
C2:C13. - 5En 4ᵉ argument : saisis la fonction d'agrégation,
SOMME. Ces quatre premiers arguments sont identiques à ceux du premier exemple. - 6En 5ᵉ argument : saisis
0pour indiquer que tes plages ne contiennent pas de ligne d'en-tête. C'est déjà le comportement par défaut, mais les arguments sont positionnels et tu ne peux pas atteindre le sixième sans renseigner celui-ci. - 7En 6ᵉ argument : saisis
0pour supprimer la ligneTotalque la fonction ajoute en bas du tableau. La colonneTotalde droite, elle, reste en place, car elle dépend d'un argument situé plus loin dans la liste. - 8Ferme la parenthèse et appuie sur Entrée.
=PIVOTER.PAR(A2:A13; B2:B13; C2:C13; SOMME; 0; 0)Total a disparu : le tableau passe de 5 lignes à 4. Le 0 en cinquième position ne change rien à lui seul, il ne sert qu'à occuper la place pour atteindre le sixième. La colonne Total de droite subsiste, car elle se pilote par un autre argument encore.
Les erreurs fréquentes avec la fonction PIVOTER.PAR
Comme PIVOTER.PAR déverse un tableau entier d'un coup, ses ratés viennent surtout de deux fronts : l'espace de sortie et l'alignement des plages d'entrée. Si une cellule à droite ou en dessous est déjà occupée, tu récoltes un #DEVERSER! ; si lignes_par, valeurs et colonnes_par ne couvrent pas exactement les mêmes lignes, c'est #VALEUR!.
Le reste se devine à l'œil : #NOM? quand ta version n'a pas la fonction, #CALC! quand tu lui passes une fonction qui n'agrège pas (un SI ou un RECHERCHEV à la place de SOMME), et le #N/A qui pointe simplement une combinaison ligne/colonne absente de tes données.
#DEVERSER! : conflit de déversement dans la zone de résultat
PIVOTER.PAR retourne un tableau dynamique qui a besoin d'espace libre pour se déverser. Si des cellules de la zone cible contiennent déjà des données (même une valeur cachée), la formule est bloquée.
Solution : Libère les cellules à droite et en dessous de la cellule où tu as saisi la formule. Supprime ou déplace les données qui occupent la zone de déversement. Si tu ne veux pas supprimer les données, place ta formule dans une zone de la feuille avec suffisamment d'espace vide autour.
#VALEUR! : plages de tailles différentes
Les plages lignes_par, valeurs et colonnes_par doivent couvrir exactement le même nombre de lignes. Une différence même d'une seule ligne déclenche l'erreur.
Solution : Vérifie que toutes tes plages ont le même nombre de lignes. Préfère des références de colonnes entières (A:A) ou des tableaux structurés (Tableau[Colonne]) : ils s'adaptent automatiquement quand tu ajoutes des lignes et éliminent ce problème.
#NOM? : fonction non disponible
PIVOTER.PAR est exclusive à Excel 365 avec abonnement actif. Elle n'existe pas dans Excel 2021, Excel 2019 ou les versions antérieures.
Solution : Vérifie ta version d'Excel dans Fichier, Compte. Si tu n'as pas Excel 365, utilise un tableau croisé dynamique classique via Insertion, Tableau croisé dynamique. Pour Excel 365, mets à jour ton installation si la fonction n'est pas encore disponible.
#CALC! : fonction d'agrégation invalide
Le paramètre fonction_valeurs doit être une fonction qui accepte un tableau d'arguments (SOMME, MOYENNE, MAX, MIN, NB, etc.). Passer une fonction comme RECHERCHEV ou SI directement renvoie cette erreur.
#N/A dans certaines cellules du tableau résultant
Quand une combinaison ligne/colonne n'existe pas dans les données source, PIVOTER.PAR affiche #N/A. C'est un comportement normal, pas une erreur de formule.
Solution : Enveloppe la formule dans SIERREUR pour remplacer les #N/A par une valeur lisible : =SIERREUR(PIVOTER.PAR(...);"-") affiche un tiret, =SIERREUR(PIVOTER.PAR(...);0) affiche zéro.
Questions fréquentes
PIVOTER.PAR est une formule qui se recalcule automatiquement dès que les données source changent, sans manipulation manuelle. Un TCD classique nécessite une actualisation (clic droit, Actualiser) à chaque modification. PIVOTER.PAR s'intègre aussi dans une chaîne de formules, ce qu'un TCD ne permet pas.
Non, PIVOTER.PAR est exclusive à Excel 365 avec abonnement actif. Elle fait partie des fonctions de tableau dynamique les plus récentes et n'est pas disponible dans Excel 2021 ni dans les versions antérieures. Sur ces versions, utilise un tableau croisé dynamique classique.
Tu peux utiliser LAMBDA dans le paramètre fonction_valeurs pour créer des agrégations personnalisées. Par exemple, LAMBDA(x;SOMME(x)/NB(x)*1,1) calcule une moyenne majorée de 10%. Pour afficher à la fois SOMME et MOYENNE dans le même tableau, il faut faire deux formules PIVOTER.PAR séparées et les disposer côte à côte.
PIVOTER.PAR se recalcule automatiquement dès que les données source sont modifiées, contrairement aux TCD qui nécessitent une actualisation manuelle. Si tu ajoutes une ligne dans la plage source, le tableau croisé se met à jour immédiatement. C'est l'un de ses principaux avantages pour les dashboards.
Oui, via les paramètres fonction_lignes et fonction_colonnes. Passe LAMBDA(x;TRIER(x)) pour un tri alphabétique ascendant, ou LAMBDA(x;TRIER(x;-1)) pour un tri décroissant. Le tri s'applique aux étiquettes de lignes ou de colonnes du tableau résultant.
Combine PIVOTER.PAR avec FILTRE : filtre d'abord tes données brutes avec FILTRE(plage; condition), puis utilise INDEX sur le résultat pour extraire chaque colonne. Par exemple : =LET(d;FILTRE(A:C;A:A="Nord");PIVOTER.PAR(INDEX(d;;1);INDEX(d;;3);SOMME;INDEX(d;;2))) pivote uniquement les données de la région Nord.
Pour aller plus loin
Continue sur ta lancée après la fonction PIVOTER.PAR : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
