PIVOTER.PAR (PIVOTBY en anglais) crée un tableau croisé dynamique directement dans une formule, sans aucune manipulation manuelle. Tu lui donnes tes colonnes de lignes, tes valeurs et une fonction d'agrégation : elle regroupe, calcule et déverse le résultat en un seul geste. Le tout se recalcule automatiquement dès que tes données source changent.
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 de la fonction PIVOTER.PAR
=PIVOTER.PAR(lignes_par; valeurs; fonction_valeurs; [colonnes_par]; [fonction_lignes]; [fonction_colonnes])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
Responsable commercial : analyse des ventes par région et produit
Tu es responsable commercial et tu dois présenter les ventes par région et par produit à la réunion mensuelle. Ton équipe a saisi toutes les transactions dans un tableau plat, et tu veux une vue croisée en quelques secondes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Région | Produit | Ventes | Résultat pivot | ||
| 2 | Nord | Laptop | 15000 | Laptop | Souris | |
| 3 | Sud | Souris | 2500 | Nord | 15000 | 2000 |
| 4 | Nord | Souris | 2000 | Sud | 12000 | 2500 |
| 5 | Sud | Laptop | 12000 | Est | 18000 | 3000 |
| 6 | Est | Laptop | 18000 | |||
| 7 | Est | Souris | 3000 |
=PIVOTER.PAR(A2:A7;C2:C7;SOMME;B2:B7)La fonction prend la colonne Région pour les lignes, la colonne Ventes pour les valeurs, applique SOMME et pivote par Produit en colonnes. Contrairement à un TCD classique, le tableau se recalcule automatiquement à chaque nouvelle vente saisie.
RH : suivi des effectifs par département et type de contrat
Tu travailles aux RH et tu dois créer un tableau récapitulatif du nombre d'employés par département et par type de contrat pour la direction.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Département | Contrat | Employé | Résultat pivot | ||
| 2 | IT | CDI | Alice | CDI | CDD | |
| 3 | IT | CDD | Bob | IT | 5 | 2 |
| 4 | Finance | CDI | Claire | Finance | 3 | 1 |
| 5 | IT | CDI | David | Marketing | 4 | 2 |
| 6 | Marketing | CDI | Eve |
=PIVOTER.PAR(A2:A10;C2:C10;NB;B2:B10)La colonne Employé contient les noms et NB compte les occurrences par croisement, ce qui crée automatiquement la matrice Département × Type de contrat avec un compte par case. Tu visualises instantanément que l'IT a 5 CDI et 2 CDD.
Responsable marketing : budget par campagne et par mois
Tu gères le budget marketing et tu veux suivre les dépenses moyennes par type de campagne et par mois pour optimiser l'allocation budgétaire.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Mois | Campagne | Budget | Résultat pivot | ||
| 2 | Jan | SEO | 5000 | SEO | Social | |
| 3 | Jan | Social | 3000 | Jan | 5000 | 3000 |
| 4 | Fév | SEO | 5500 | Fév | 5500 | 3200 |
| 5 | Fév | Social | 3200 | Mar | 6000 | 3500 |
| 6 | Mar | SEO | 6000 | |||
| 7 | Mar | Social | 3500 |
=PIVOTER.PAR(A2:A7;C2:C7;MOYENNE;B2:B7)MOYENNE calcule ici la moyenne des dépenses quand plusieurs lignes existent par mois et par campagne, et produit la matrice Mois × Campagne. Tu détectes que le budget SEO augmente régulièrement (5000 en janvier, 6000 en mars) tandis que le budget Social reste stable.
Analyste commercial : identifier les pics de vente par vendeur
Tu es analyste commercial et tu veux identifier la vente maximale réalisée par chaque vendeur sur chaque trimestre pour repérer les meilleures performances individuelles.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Vendeur | Trimestre | Vente | Résultat pivot | |||
| 2 | Marie | Q1 | 5000 | Q1 | Q2 | Q3 | |
| 3 | Marie | Q2 | 6000 | Marie | 5000 | 6000 | 7000 |
| 4 | Paul | Q1 | 4500 | Paul | 4500 | 5500 | 6500 |
| 5 | Paul | Q2 | 5500 |
=PIVOTER.PAR(A2:A9;C2:C9;MAX;B2:B9)MAX retourne la valeur la plus haute de chaque groupe plutôt que la somme, et révèle rapidement que Marie performe mieux avec un pic à 7000 en Q3. Tu peux ensuite utiliser ces données pour calibrer les objectifs du prochain trimestre.
Astuce de pro : Remplace MAX par SOMME pour obtenir le total par vendeur et par trimestre, ou par NB pour compter le nombre de transactions. Il suffit de changer un seul mot dans la formule.
Contrôleur de gestion : pivot simple avec tri décroissant
Tu es contrôleur de gestion et tu veux un résumé des ventes par produit, trié du plus vendu au moins vendu pour faciliter la lecture.
| A | B | C | |
|---|---|---|---|
| 1 | Produit | Ventes | Résultat trié |
| 2 | A | 1000 | C: 3000 |
| 3 | B | 500 | A: 2000 |
| 4 | C | 3000 | B: 500 |
| 5 | A | 1000 | |
| 6 | C | 1500 |
=PIVOTER.PAR(A2:A6;B2:B6;SOMME;;LAMBDA(x;TRIER(x;-1)))Le cinquième paramètre fonction_lignes accepte une LAMBDA qui trie les étiquettes de lignes : ici, les ventes sont agrégées par produit puis le tableau résultant est trié par ordre décroissant. Le double point-virgule ;; est obligatoire pour sauter le paramètre colonnes_par et atteindre fonction_lignes.
Production : gérer les cellules vides avec SIERREUR
Tu surveilles les défauts de production par ligne de fabrication et par équipe. Quand une combinaison ligne/équipe n'existe pas dans les données (par exemple L2 Soir), le tableau devient difficile à lire.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ligne | Équipe | Défauts | Résultat pivot | ||
| 2 | L1 | Matin | 3 | Matin | Soir | |
| 3 | L1 | Soir | 1 | L1 | 3 | 1 |
| 4 | L2 | Matin | 5 | L2 | 5 | - |
| 5 | L3 | Soir | 2 | L3 | - | 2 |
=SIERREUR(PIVOTER.PAR(A2:A10;C2:C10;SOMME;B2:B10);"-")Quand une combinaison ligne/équipe n'existe pas dans les données (par exemple L2 Soir), le pivot renvoie #N/A. L'enveloppe SIERREUR remplace alors chaque erreur par un tiret lisible, ce qui met en évidence les combinaisons inexistantes sans bloquer la lecture des données valides.
Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
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.
Solution : Utilise uniquement des fonctions d'agrégation natives : SOMME, MOYENNE, NB, NBVAL, MAX, MIN, PRODUIT. Pour une logique personnalisée, enveloppe-la dans LAMBDA : LAMBDA(x;SOMME(x)/NB(x)) calcule par exemple une moyenne manuelle.
#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 sur la fonction PIVOTER.PAR
Quelle différence entre PIVOTER.PAR et un tableau croisé dynamique classique ?
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.
PIVOTER.PAR fonctionne-t-elle avec Excel 2021 ?
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.
Comment gérer plusieurs fonctions d'agrégation simultanément ?
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.
Que se passe-t-il si mes données source changent ?
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.
Peut-on trier les résultats d'un PIVOTER.PAR ?
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.
Comment filtrer les données avant de les pivoter ?
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
Les fonctions similaires : FILTRE, TRIER, UNIQUE, SOMME.SI.ENS, LET
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois