La fonction GROUPER.PAR : Pour un sommaire instantané de vos données!

La fonction Grouper.Par (Groupby) dans Excel permet de regrouper les données à un niveau supérieur et ainsi créer un résumé de vos données.

Exemple d’usage de la fonction Grouper.Par

Vous pourriez par exemple récupérer la moyenne des notes pour une liste d’examen. Et les trier par ordre décroissant de cette moyenne.

De plus, lorsqu’un 7^e examen est ajouté à l’ensemble des données, la fonction Grouper.Par s’adapte automatiquement. En considérant que la moyenne de ce 7^e examen est de 95%, il viendra s’ajouter tout au haut de la liste sans modification à la fonction Grouper.Par.

Vous pourrez créer diverses agrégations, notamment une somme, une moyenne, la valeur maximale et minimale, l’écart-type, etc.

Il est à noter que l’on pourrait arriver à un résultat semblable en passant par un Tableau croisé dynamique (voici une introduction aux tableaux croisés dynamiques dans Excel) ou par Power Query (Consolider les données à un niveau supérieur). Toutefois, il est intéressant d’explorer cette nouvelle fonction pour les raisons énumérées ci-dessous.

Voici quelques avantages d’utiliser la fonction Grouper.Par :

• Aucune actualisation n’est nécessaire. La fonction se recalcule automatiquement lors de l’ajout d’éléments.
• Très rapide à mettre en place pour une analyse ponctuelle.
• Davantage de fonction d’agrégats que ceux disponibles dans un tableau croisé dynamique, notamment des fonctions texte comme TABLEAU.EN.TEXTE dont on voit un exemple plus loin.

Voyons ensemble chacun des paramètres de la fonction Grouper.Par

=GROUPER.PAR(row_fields; values; function; [field_headers]; [total_treatment]; [sort_order]; [filter_array]; [Field_Relationship])

• row_fields : Quelles données utiliser pour le regroupement
• values :Valeurs à agréger
• function : Le calcul à effectuer (somme, moyenne,…)
• [field_headers] : Est-ce que les données contiennent des en-têtes (facultatif)
• [total_treatment] : Est-ce que l’on désire afficher les Totaux et sous-totaux (facultatif)
• [sort_order] : Identifier la colonne et l’ordre du Tri (facultatif)
• [filter_array] : Filtre des données (facultatif)
• [Field_Relationship]  : Indiquer la relation des champs s’il y a plusieurs colonnes dans le paramètre row_fields (facultatif)

Regardons quelques exemples pour mieux saisir chacun de ces paramètres

Paramètres obligatoires et valeurs par défaut

Commençons par illustrer les paramètres obligatoires de la fonction en laissant la valeur par défaut aux paramètres facultatifs.

Reprenons l’exemple de la série de données sur les résultats à des examens.

La table de données utilisée est sous format tableau (Les tableaux Excel, tout simplement indispensables et magiques) dans Excel et se nomme Resultats.

▶ row_fieds

Dans le paramètre row_fieds nous devons indiquer les données utilisées pour le groupement. C’est l’équivalent des valeurs que l’on met en ligne dans un Tableau croisé dynamique.

Comme je veux regarder par numéro d’examen, j’ai sélectionné la colonne Examen de la table de donnée Resultats en m’assurant d’inclure l’en-tête dans la sélection. J’obtiens ceci :

Resultats[[#Tout];[Examen]]

▶ values

Pour le paramètre values, il faut indiquer les valeurs à agréger. Comme nous voulons calculer la moyenne de la colonne Résultat, il faut sélectionner cette colonne en incluant l’en-tête.

Resultats[[#Tout];[Résultat]]

▶ function

Finalement, le dernier paramètre obligatoire, function est l’endroit où l’on indique le type d’agrégation que l’on veut calculer. L’on choisit alors la MOYENNE dans le menu déroulant qui s’affiche dans ce paramètre.

Comme les autres paramètres sont restés à leur valeur par défaut, on obtient :

• Un tableau sans en-tête
• Une ligne Total au bas du tableau
• Aucun tri spécifique n’a été effectué. Il semble que, par défaut, les données soient triées en fonction de la donnée entrée dans le paramètre row_fields.

=GROUPER.PAR(Resultats[[#Tout];[Examen]];Resultats[[#Tout];[Résultat]];MOYENNE)

Regardons maintenant les paramètres facultatifs

▶ [field_headers]

Le paramètre field_headers indique si les données contiennent des en-têtes et comment les utiliser dans les résultats.

Les options possibles :

Les options 0 et 2 s’appliquent lorsque les données sources n’ont pas d’en-tête, alors que les options 1 et 3 s’appliquent dans le cas contraire.

Commençons par illustrer les options 1 et 3.

L’option 1 – Oui, mais ne pas afficher

Dans notre exemple, nous ne l’avons pas spécifié. Mais comme les données sont sous format tableau, et que j’ai sélectionné l’en-tête dans le paramètre row_fieds (Resultats[[#Tout]]), la fonction reconnait les en-têtes et c’est comme si l’on avait inscrit l’option 1 – Oui, mais ne pas afficher dans le paramètre.

L’option 3 – Oui et afficher

Avec l’option 3, l’on spécifie que les données ont des en-têtes et que l’on veut les afficher. C’est ce que nous avons dans l’image ici. Remarquez que nous avons formaté les 2 cellules pour que les en-têtes soient bien définis.

L’option 0 – Non

Cette option est à utiliser lorsque les données n’ont pas d’en-tête et que l’on ne désire pas non plus en afficher.

Dans l’image ci-dessous, l’on voit un exemple où les données à gauche n’ont pas d’en-tête et où l’option 0 est insérée dans le paramètre.

L’option 2 – Non, mais générer

Cette option permet de gérer des en-têtes génériques au résultat. Soit Champ de ligne et Valeur. Ces nouveaux en-têtes pourraient s’avérer utiles dans le cas où l’on voudrait effectuer des calculs sur les résultats obtenus dans cette matrice.

Avec le même exemple, si j’utilise plutôt l’option 2 – Non, mais générer.

▶ [total_treatment]

Celle-ci permet de spécifier de quelle façon l’on souhaite afficher les totaux et sous-totaux.

Les options possibles :

Les différentes options parlent d’elles-mêmes. Par contre, notez que, pour utiliser les sous-totaux, il faut alors avoir plus d’une colonne dans le premier paramètre row_fields .

Par exemple, si j’ajoute une colonne d’années aux données d’examen, je pourrais alors inclure la colonne Année et Examen dans le 1^er paramètre. Puis inclure un 2 dans le paramètre [total_treatment] et ainsi obtenir les sous-totaux et le total général dans la matrice résultante.

=GROUPER.PAR(Resultats[[#Tout];[Année]:[Examen]];Resultats[[#Tout];[Résultat]];MOYENNE;3;2)

Remarquez que le format des sous-totaux laisse à désirer, puisqu’il faut bien regarder pour les trouver! Le sous-total de l’année 2020 a été encadré dans l’image ci-dessus.

▶ [sort_order]

Ce paramètre vous permet de spécifier l’ordre de tri souhaité. Il faut d’abord indiquer la colonne à trier à l’aide d’un chiffre. Si celui-ci est positif, le tri sera croissant et s’il est négatif, le tri sera décroissant.

Si l’on veut trier la colonne année en ordre décroissant, il faut alors indiquer -1.

=GROUPER.PAR(Resultats[[#Tout];[Année]:[Examen]];Resultats[[#Tout];[Résultat]];MOYENNE;3;2;-1)

Dans le cas où l’on voudrait appliquer un tri pour plus d’une colonne, nous pouvons utiliser les accolades pour créer une liste de valeur.

=GROUPER.PAR(Resultats[[#Tout];[Année]:[Examen]];Resultats[[#Tout];[Résultat]];MOYENNE;3;2;{-1;-2})

Indiquant ainsi que l’on désire trier la 1^ere colonne (Année) en décroissant et la 2^e colonne (Examen) en ordre décroissant également.

▶ [filter_array]

Ce paramètre permet de filtrer les données sources. Par exemple, pour garder seulement les années 2023 et suivantes, l’on peut insérer Resultats[[#Tout];[Année]]>=2023.

=GROUPER.PAR(Resultats[[#Tout];[Année]:[Examen]];Resultats[[#Tout];[Résultat]];MOYENNE;3;2;-1;Resultats[[#Tout];[Année]]>=2023)

On obtient alors le résultat pour les années 2023 et suivantes.

▶ [Field_Relationship] 

Ce paramètre permet d’indiquer la relation des champs s’il y a plusieurs colonnes dans le paramètre row_fields.(facultatif).

Il faut au préalable avoir identifié les liens entre plusieurs tables de notre fichier Excel avec Power Pivot.

Si vous avez besoin de travailler avec plusieurs tables de données, je vous invite à vous familiariser avec ce complément.

De plus, si vous utilisez Power Pivot, il sera alors nécessaire d’actualiser les données lors de l’ajout de données et j’opterais alors plutôt pour un tableau croisé dynamique pour effectuer un sommaire des données.

Plusieurs colonnes agrégées

Il est possible d’inclure plusieurs colonnes dans le paramètre values de la fonction Grouper.Par.

Dans l’exemple suivant, nous avons inclus les colonnes Résultat et Reprise pour obtenir la moyenne des résultats initiaux aux examens et la moyenne des résultats de reprise d’examen.

=GROUPER.PAR(Resultats[[#Tout];[Année]:[Examen]];Resultats[[#Tout];[Résultat]:[Reprise]];MOYENNE;3)

La fonction TABLEAU.EN.TEXTE

Comme indiqué précédemment, la fonction Grouper.Par ne se limite pas au calcul que l’on voit dans le tableau croisé dynamique.

Entre autres, la fonction TABLEAU.EN.TEXTE, peut être intéressante pour créer une liste d’éléments.

Dans l’exemple suivant, j’ai des données qui indiquent les professeurs qui sont responsables des examens. Dans certains cas, il y a plusieurs professeurs pour un examen. Je peux en obtenir la liste par examen avec la fonction Gouper.Par en utilisant la fonction d’agrégat TABLEAU.EN.TEXTE comme ceci :

=GROUPER.PAR(H102:H112;I102:I112;TABLEAU.EN.TEXTE;3;0)

Voyez le tout en action dans ce tutoriel

🎞️ Abonnez-vous à notre chaîne YouTube

Bon visionnement !

Conclusion sur la fonction Grouper.Par dans Excel

La fonction Grouper.Par dans Excel vous permet de regrouper et d’agréger vos données de manière efficace et intuitive. Que vous souhaitiez calculer des moyennes, des sommes, des valeurs maximales ou minimales, ou même créer des listes de texte, Grouper.Par vous offre une multitude de possibilités pour analyser vos données rapidement et sans effort.

Passez à l’action maintenant !

• Ouvrez Excel, sélectionnez vos données, et commencez à utiliser la fonction Grouper.Par pour transformer vos analyses de données.
• Puis partagez avec nous vos commentaires et expériences avec la fonction Grouper.Par dans les commentaires ci-dessous.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.