Aller au contenu principal

Qu'est-ce que la fonction PIVOTER.PAR ?

Définition
La fonction PIVOTER.PAR regroupe et agrège des données en tableau croisé dynamique via une formule. Elle identifie les valeurs uniques dans les colonnes de lignes et de colonnes, puis applique la fonction d'agrégation choisie sur les valeurs correspondantes. Exclusive à Excel 365.

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 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?.

Lucas, la mascotte du Dojo, inspecte les arguments de la fonction à la loupe

Comprendre chaque paramètre de la fonction PIVOTER.PAR

Les quatre premiers arguments sont obligatoires et tiennent dans l'ordre : la colonne que tu regroupes en lignes, celle que tu découpes en colonnes, la colonne de chiffres à résumer, puis la fonction d'agrégation (SOMME, MOYENNE, NB…) écrite sans parenthèses. Avec trois arguments seulement, Excel refuse la formule, et sans découpage en colonnes tu laisses la deuxième place vide ou tu passes par GROUPER.PAR.

Les six suivants sont facultatifs, et pour atteindre l'un d'eux tu gardes la place des précédents avec un point-virgule vide. Pour trier les lignes par valeur décroissante, écris =PIVOTER.PAR(A2:A13;B2:B13;C2:C13;SOMME;;;-2), où les deux places vides sautent en_têtes et totaux_lignes pour aller droit à tri_lignes.

1

champs_lignes

: 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.

2

champs_colonnes

: la colonne ou plage qui contient les étiquettes à regrouper en colonnes, par exemple ta colonne Trimestre ou Produit

PIVOTER.PAR crée autant de colonnes de résultats que de valeurs uniques dans cette plage.

Si tu ne veux pas découper en colonnes, laisse cette place vide entre deux points-virgules, et tu obtiens une seule colonne de résultats, une ligne par groupe. GROUPER.PAR donne le même tableau avec une syntaxe plus courte.

3

valeurs

: la colonne ou plage contenant les valeurs numériques à agréger

C'est le chiffre que tu veux résumer : montant des ventes, nombre d'heures, budget dépensé, etc.

champs_lignes, champs_colonnes et valeurs 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 champs_lignes, Excel renvoie #VALEUR! sans prévenir. Vérifie toujours que les trois plages couvrent exactement les mêmes lignes.

4

fonction

: la fonction d'agrégation à appliquer aux valeurs de chaque groupe : SOMME, MOYENNE, NB, MAX, MIN, etc

Tu 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 %.

5

[en_têtes]

: paramètre facultatif : il indique si tes plages commencent par une ligne d'en-tête, et avec `0` elles n'en ont pas(facultatif)

En pratique, tu ne le renseignes que pour garder sa place quand tu veux atteindre les arguments qui le suivent.

6

[totaux_lignes]

: paramètre facultatif : il règle la ligne `Total` que la fonction ajoute en bas du tableau(facultatif)

Avec 0, cette ligne disparaît, comme dans le troisième exemple.

7

[tri_lignes]

: paramètre facultatif : un nombre qui fixe l'ordre des lignes(facultatif)

Sans lui, les étiquettes ressortent de A à Z ; -1 les trie de Z à A, et -2 les classe par valeur décroissante.

Une LAMBDA de tri ne marche pas à cette place, et Excel renvoie alors #VALEUR!.

8

[totaux_colonnes]

: paramètre facultatif : il règle la colonne `Total` de droite(facultatif)

Avec 0, elle disparaît, et avec 0 aussi dans totaux_lignes, le tableau n'affiche plus aucun total.

9

[tri_colonnes]

(facultatif)

Ce paramètre facultatif suit le même principe que tri_lignes et s'applique aux en-têtes de colonnes du tableau croisé.

10

[filtre]

: ce paramètre facultatif pose une condition sur les lignes source et ne garde que celles où elle vaut VRAI(facultatif)

A2:A13="Nord" ne pivote que la région Nord, sans passer par FILTRE.

Le savais-tu ?
Contrairement à un tableau croisé dynamique classique qu'il faut actualiser à la main, PIVOTER.PAR se recalcule tout seul dès qu'une donnée source change, et s'intègre dans une chaîne de formules. Elle reste exclusive à Excel 365.
Lucas, la mascotte du Dojo, fait une démonstration pas à pas de la fonction dans Excel

Comment utiliser PIVOTER.PAR

Exemple 1

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.

Les étapes
  1. 1
    Dans une cellule, écris =PIVOTER.PAR(.
  2. 2
    En 1er argument : sélectionne la colonne qui deviendra les lignes du tableau croisé, A2:A13. La fonction y repère les valeurs distinctes (Est, Nord et Sud) et consacre une ligne à chacune.
  3. 3
    En 2ᵉ argument : sélectionne la colonne qui deviendra les colonnes, B2:B13. Elle reçoit le même traitement mais à l'horizontale, T1 et T2 devenant deux colonnes du résultat.
  4. 4
    En 3ᵉ argument : sélectionne la colonne de chiffres à résumer, C2:C13. C'est la seule des trois plages qui contient des montants.
  5. 5
    En 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.
  6. 6
    Ferme la parenthèse et appuie sur Entrée.
Au final, ta formule devrait ressembler à ça :=PIVOTER.PAR(A2:A13; B2:B13; C2:C13; SOMME)
Explication
Chaque croisement additionne toutes les ventes qui partagent la même région et le même trimestre : 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.
Exemple 2

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.

Les étapes
  1. 1
    Dans une cellule, écris =PIVOTER.PAR(.
  2. 2
    En 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.
  3. 3
    En 2ᵉ argument : sélectionne la colonne des trimestres, B2:B13. Elle forme les colonnes, T1 et T2 restant les deux mêmes en-têtes.
  4. 4
    En 3ᵉ argument : sélectionne la colonne des montants, C2:C13. Ce sont les chiffres à agréger, exactement les mêmes que pour le total.
  5. 5
    En 4ᵉ argument : saisis MOYENNE à la place de SOMME. La fonction applique désormais la moyenne à chaque groupe, sans que tu touches à la structure du tableau.
  6. 6
    Ferme la parenthèse et appuie sur Entrée.
Voici la formule que tu obtiens à la fin :=PIVOTER.PAR(A2:A13; B2:B13; C2:C13; MOYENNE)
Explication
Chaque case donne la moyenne des ventes du groupe : 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.
Exemple 3

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.

Les étapes
  1. 1
    Dans une cellule, écris =PIVOTER.PAR(.
  2. 2
    En 1er argument : sélectionne la colonne des régions à mettre en lignes, A2:A13.
  3. 3
    En 2ᵉ argument : sélectionne la colonne des trimestres à mettre en colonnes, B2:B13.
  4. 4
    En 3ᵉ argument : sélectionne la colonne des montants à agréger, C2:C13.
  5. 5
    En 4ᵉ argument : saisis la fonction d'agrégation, SOMME. Ces quatre premiers arguments sont identiques à ceux du premier exemple.
  6. 6
    En 5ᵉ argument : saisis 0 pour 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.
  7. 7
    En 6ᵉ argument : saisis 0 pour supprimer la ligne Total que la fonction ajoute en bas du tableau. La colonne Total de droite, elle, reste en place, car elle dépend d'un argument situé plus loin dans la liste.
  8. 8
    Ferme la parenthèse et appuie sur Entrée.
Une fois les morceaux assemblés, ta formule donne ça :=PIVOTER.PAR(A2:A13; B2:B13; C2:C13; SOMME; 0; 0)
Explication
Les chiffres sont identiques à ceux du premier exemple, seule la ligne 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.
Lucas, la mascotte du Dojo, l'air gêné face à une erreur Excel

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 #PROPAGATION! ; si champs_lignes, champs_colonnes et valeurs 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, et #VALEUR! encore quand tu lui passes une fonction qui n'agrège pas (un SI ou un RECHERCHEV à la place de SOMME). Une combinaison ligne et colonne absente de tes données, elle, laisse simplement une case vide.

#PROPAGATION! : 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 champs_lignes, champs_colonnes et valeurs 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.

#VALEUR! : fonction d'agrégation invalide

Le paramètre fonction doit être une fonction qui résume un groupe de valeurs (SOMME, MOYENNE, MAX, MIN, NB, etc.). Avec SI, le tableau se déverse mais chaque case affiche #VALEUR!, et avec RECHERCHEV la formule entière renvoie #VALEUR!. Quant à SOMME() écrite avec ses parenthèses, Excel la refuse dès la saisie.

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.

Une case vide au croisement d'une ligne et d'une colonne

Quand une combinaison ligne et colonne n'existe pas dans les données source, PIVOTER.PAR laisse la case vide. C'est un comportement normal, pas une erreur de formule.

Solution : N'enveloppe pas la formule dans SIERREUR, puisqu'il n'y a aucune erreur à intercepter. Vérifie plutôt dans tes données source que l'absence est normale, une région qui n'a rien vendu au T2 par exemple.

Le conseil du pro
Passe la fonction d'agrégation par son nom seul, sans parenthèses ni guillemets : =PIVOTER.PAR(A2:A13; B2:B13; C2:C13; SOMME). Avec SOMME(), Excel refuse la formule dès la saisie, et une fonction qui n'agrège pas, comme SI ou RECHERCHEV, renvoie #VALEUR!.

Les fonctions liées à PIVOTER.PAR

Tu as peut-être encore une question

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.

Pour aller plus loin

Tout ce dont tu as besoin pour maîtriser la fonction PIVOTER.PAR se trouve ici.

Besoin d’aide sur Excel ?

Rejoins le Dojo pour poser tes questions, obtenir de l’aide sur tes fichiers Excel et participer chaque mois à un cours collectif en direct.

Gratuit · Sans carte bancaire