La fonction PIVOTER.PAR permet de créer des rapports de synthèse de vos données avec une seule formule. Le principe est similaire à un tableau croisé dynamique. Voici les paramètres de la fonction :

1. Le ou les champs à mettre en ligne. Indiquez le champ sur lequel vous voulez effectuer votre synthèse.
2. [Optionnel] Le ou les champs à mettre en colonne. Comme pour un TCD, vous pouvez ajouter un champ en colonne pour enrichir votre synthèse, mais ce n'est pas obligatoire.
3. Le champ pour les calculs. Sélectionnez le champ contenant les valeurs numériques à agréger.
4. Le type de calcul. Définissez si vous voulez une somme, un dénombrement, une moyenne, un minimum, etc.
5. Afficher le titre des colonnes. Précisez si la fonction doit retourner les entêtes du document source.
6. Total et Sous-Total. Activez l'affichage du total général ou des sous-totaux si vous avez plusieurs champs.
7. Option de tri des champs en ligne. Choisissez de trier ou non les champs en ligne en ordre croissant ou décroissant.

=PIVOTER.PAR(champs en ligne;champs en colonne;champ à aggréger;type de calcul;en-têtes;total en ligne;tri des lignes;total en colonne;tri des colonnes)

Présentation de la fonction PIVOTER.PAR

• La fonction est accessible pour tous les utilisateurs d'Excel 365 version 2403 (depuis le 23/02/2024)

Le principe est de créer un tableau de synthèse en une seule formule. Prenons les données suivantes

Dans une situation comme celle-ci, le seul moyen qui existait dans Excel pour construire un tableau de synthèse c'était de

• Extraire chaque valeur à analyser, sans doublon, grâce à la fonction UNIQUE
• Puis ajouter une colonne de calcul
  • NB.SI.ENS pour faire des calculs de dénombrement
  • SOMME.SI.ENS pour faire des sommes sous conditions
  • MOYENNES.SI.ENS pour faire la moyenne
  • .....

Or maintenant, il n'est plus nécessaire de manipuler plusieurs formules, une seule suffit.

Comment construire la fonction PIVOTER.PAR ?

Commençons par un cas d'étude très simple. Nous voulons faire la synthèse des ventes par régions.

1. Nous commençons par écrire le nom de la fonction
2. Ensuite, nous sélectionnons la colonne qui contient le découpage par Régions
3. Nous laissons l'argument du champs en colonne vide
4. Puis nous indiquons le champ qui contient les données à aggréger (la colonne Ventes)
5. Enfin, le type de calcul depuis le menu déroulant ; ici nous voulons faire une somme

Et voilà, en une seule formule, nous avons la synthèse des ventes par régions ???

Si maintenant nous voulons faire la synthèse des ventes par clients, il suffit de remplacer le champ Régions par le champ Client.

Changer le type de calcul

Le 4e argument de la fonction retourne la nature du calcul, SOMME, NB, MAX, MIN, .... Voyons quelques cas d'application

• Combien de fois les articles ont-ils été commandés ?
  • Utiliser le paramètre NB

• Quelle est la moyenne des commandes pour chacun des produits ?
  • Paramètre MOYENNE

En ainsi de suite pour les autres fonctions Statistiques de base comme MIN, MAX, MEDIANE, MODE.SIMPLE, ECARTYPE.STANDARD, PRODUIT

La nouvelle fonction POURCENTAGE.DE

Parmi tous les choix proposées, une nouvelle fonctionnalité permet de retourner le pourcentage par rapport au Total Général ; c'est l'option POURCENTAGE.DE

Simplement en indiquant cette option dans le type de calcul, la fonction PIVOTER.PAR va automatiquement faire le calcul du pourcentage pour chaque ligne.

• A la différence d'un TCD, cette option ne permet pas de faire des pourcentages parents.

Mettre plusieurs champs en ligne

La fonction PIVOTER.PAR accepte plusieurs champs comme premier argument. Si nous voulons faire la synthèse par Régions et par Client, nous allons écrire

Maintenant, si les colonnes ne sont pas contiguës (l'une à côté de l'autre) dans votre source, la solution c'est de passer par la fonction CHOISIRCOLS. Dans cette situation, vous sélectionnez les colonnes Régions, Client et Produit et vous indiquez à la fonction que vous ne conserver que la 1e et la 3e colonne.

=CHOISIRCOLS(Sélection de 3 colonnes;1;3)

Afficher les entêtes

Avant de pouvoir indiquer que vous voulez afficher les entêtes des colonnes de vos champs, il faut bien sûr les sélectionner ?

• Sélectionnez les champs en ligne avec leurs entêtes
• N'oubliez pas non plus de sélectionner l'en-tête des valeurs pour le résultat. Si les plages sélectionnées ne sont pas de même taille, la fonction PIVOTER.PAR va retourner une erreur
• Choisissez l'option 3

Total et Sous-Totaux

• Par défaut, le Total Général est toujours affiché avec la fonction PIVOTER.PAR. Mais vous pouvez modifier ce comportement en mettant 0 pour le 7e argument.
• Mais si vous avez plusieurs champs en Ligne, vous pouvez également afficher les sous-totaux (valeur 2)

Articles liés

• Fonction CHOISIRCOLS dans Excel
• Dépivoter les colonnes avec Power Query
• Pivoter les colonnes – Créer un tableau à double-entrée
• Tout sur la Manipulation de Données – Excel Exercice