La fonction PIVOTER.PAR d’Excel

La fonction PIVOTER.PAR d’Excel
Dernière mise à jour le 24/02/2024
Temps de lecture : 4 minutes

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 :

  1. Le ou les champs à mettre en ligne

    Ici, il suffit d'indiquer sur quel champ 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 qui va être utilisé pour les calculs

    Sélectionner le champ contenant les valeurs numériques à agréger

  4. 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, ...

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

  6. Total et Sous-Total

    Autre option, la possibilité d'afficher le Total général ou les Sous-Totaux si vous avez plusieurs champs

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

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

Fonction PIVOTER.PAR pour faire la synthese des donnees en 1 formule

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

Synthese des ventes par client avec PIVOTER.PAR

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
Parametre NB de PIVOTER.PAR pour denombrer
  • Quelle est la moyenne des commandes pour chacun des produits ?
    • Paramètre MOYENNE
Moyenne des commandes par produit 1

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.

Option pourcentage de la fonction PIVOTER.PAR

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

Utiliser deux champs comme critere de repartition

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)

Selection de 2 colonnes non contigues pour les champs en ligne

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
Afficher les entetes de la fonction PIVOTER PAR

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)
Total et sous totaux avec PIVOTER.PAR

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

La fonction PIVOTER.PAR d’Excel

Reading time: 4 minutes
Dernière mise à jour le 24/02/2024

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 :

  1. Le ou les champs à mettre en ligne

    Ici, il suffit d'indiquer sur quel champ 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 qui va être utilisé pour les calculs

    Sélectionner le champ contenant les valeurs numériques à agréger

  4. 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, ...

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

  6. Total et Sous-Total

    Autre option, la possibilité d'afficher le Total général ou les Sous-Totaux si vous avez plusieurs champs

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

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

Fonction PIVOTER.PAR pour faire la synthese des donnees en 1 formule

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

Synthese des ventes par client avec PIVOTER.PAR

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
Parametre NB de PIVOTER.PAR pour denombrer
  • Quelle est la moyenne des commandes pour chacun des produits ?
    • Paramètre MOYENNE
Moyenne des commandes par produit 1

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.

Option pourcentage de la fonction PIVOTER.PAR

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

Utiliser deux champs comme critere de repartition

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)

Selection de 2 colonnes non contigues pour les champs en ligne

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
Afficher les entetes de la fonction PIVOTER PAR

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)
Total et sous totaux avec PIVOTER.PAR

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.