La fonction PIVOTER.PAR vous permet de créer des rapports de synthèses de vos données avec une seule formule. C'est le même principe que de faire un tableau croisé dynamique.
Les paramètres de la fonction sont :
- Le ou les champs à mettre en ligne
Ici, il suffit d'indiquer sur quel champ vous voulez effectuer votre synthèse
- [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
- Le champ qui va être utilisé pour les calculs
Sélectionner le champ contenant les valeurs numériques à agréger
- Le type de calcul
Juste en renseignant cet argument, vous allez indiquer à la fonction si vous voulez faire une somme, un dénombrement, une moyenne, le minimum, ...
- Afficher le titre des colonnes
Vous pouvez préciser si vous voulez que la fonction PIVOTER.PAR retourne les entêtes de votre document source, ou pas
- Total et Sous-Total
Autre option, la possibilité d'afficher le Total général ou les Sous-Totaux si vous avez plusieurs champs
- Option de tri des champs en ligne
Possibilité 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.
- Nous commençons par écrire le nom de la fonction
- Ensuite, nous sélectionnons la colonne qui contient le découpage par Régions
- Nous laissons l'argument du champs en colonne vide
- Puis nous indiquons le champ qui contient les données à aggréger (la colonne Ventes)
- 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 contigües (l'une à coté 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 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'entê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)
20/09/2024 @ 15:36
Merci pour ce partage et grosse force à toi pour tout ce que tu fais pour nous aider à mieux maitriser les fonctions excel.