Remplacer les TCD avec une fonction Excel c'est maintenant possible (et même plutôt facile !) : la fonction GROUPER.PAR

Dans ce tutoriel, je vais vous montrer comment utiliser la nouvelle fonction GROUPER.PAR pour analyser vos données sans avoir à créer d’intimidant tableaux croisés dynamiques complexes. Cette fonction vous permettra de réaliser des analyses sophistiquées directement à partir de formules, ce qui vous fera gagner un temps précieux.

Nous allons donc explorer ensemble toutes les possibilités offertes cette fonction, des analyses les plus simples aux plus avancées, avec des exemples concrets que vous pourrez facilement reproduire.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous avons des données de ventes d'une librairie en ligne au cours des mois de janvier et février 2025, avec différentes catégories de livres, des auteurs variés, et des clients répartis dans plusieurs régions de France.

Traditionnellement, pour analyser ces données, nous aurions créé un tableau croisé dynamique, mais aujourd'hui nous allons découvrir une alternative plus souple et plus puissante.

2. Introduction à la fonction GROUPER.PAR

   2.1. Qu'est-ce que GROUPER.PAR et pourquoi l'utiliser ?

La fonction GROUPER.PAR est une fonction relativement récente dans Excel, introduite avec la suite de fonctions dynamiques. Cette fonction permet de regrouper des données selon un ou plusieurs critères et d'effectuer des opérations de calcul sur les groupes ainsi formés. C'est exactement ce que fait un tableau croisé dynamique, mais directement dans une formule !

Pourquoi utiliser GROUPER.PAR plutôt qu'un tableau croisé dynamique ? Tout d'abord, cette fonction s'intègre parfaitement dans les formules Excel. Nous pouvons donc la combiner avec d'autres fonctions pour créer des analyses complexes. De plus, contrairement aux tableaux croisés dynamiques, les résultats de GROUPER.PAR se mettent à jour automatiquement lorsque les données sources changent, sans avoir besoin d'actualiser manuellement.

Autre avantage : sa simplicité. Nous n'avons pas besoin de passer par l'interface des tableaux croisés dynamiques, qui peut parfois être déroutante pour les débutants.

En revanche, celle-ci n’étant disponible que sur les dernières versions d’Excel, si vous souhaiter souhaitez exploiter votre classeur sur une versions plus ancienne d’Excel, celle-ci retournera une erreur, alors qu’un TCD est compatible sur toutes les versions.

   2.2. Structure de base de la formule

La syntaxe de base de la fonction GROUPER.PAR est la suivante :

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

Note : comme vous pouvez le constater, étant donné qu’il s’agit d’une fonction très récente, les arguments n’ont pas encore été traduits en français.

Où :

- row_fields (obligatoire) : Plage utilisée pour regrouper les données

- values (obligatoire) : Données numériques à analyser

- function (obligatoire) : Opération d’agrégation à mettre en place : "SOMME", "MOYENNE", "NB" (compte non vide), "NBVAL" (compte toutes valeurs), …

- [field_headers] (optionnel) : Permet de spécifier si les données contiennent des en-têtes. Par défaut, la fonction détecte automatiquement la présence ou l’absence d’en-tête

- [total_depth] (optionnel) : Permet de spécifier si nous souhaiter obtenir une ligne de total

- [sort_order] (optionnel) : Permet de sélectionner la colonne sur laquelle appliquer le tri

- [filter_array] (optionnel) : Permet d’appliquer un filtre sur les données

- [field_relationship] (optionnel) : Permet d’établir une relation entre plusieurs colonnes lors du tri

3. Les analyses de base avec GROUPER.PAR

   3.1. Calcul du total des ventes par catégorie

Pour notre premier exemple, nous allons simplement référencer le nombre de livres vendus par catégorie.

Pour cela, nous utiliserons la formule suivante dans la cellule K9 :

=GROUPER.PAR(B9:B23;F9:F23;SOMME)

Nous demandons à Excel de regrouper les catégories de ventes (les cellules B9 à B23), afin d’obtenir un dénombrement des unités vendues (F9 à F23), en utilisant la fonction SOMME :

Après avoir validé cette formule en appuyant sur [Entrée], nous obtenons un tableau dynamique à deux colonnes : la première colonne affiche les catégories de livres, et la seconde le nombre d’unités vendues correspondante.

Excel nous a même ajouté le nombre total d’unités que cela représente !

   3.2. Calcul du CA par catégorie

Maintenant, si nous avions souhaité connaître non pas le nombre d’unités vendues, mais le chiffre d’affaires correspondant, la formule serait identique, sauf que nous souhaiterons multiplier les valeurs de la colonne des unités avec celle du prix unitaire :

=GROUPER.PAR(B9:B23;E9:E23*F9:F23;SOMME)

   3.3. Analyse du nombre de ventes par région

Maintenant, examinons combien de ventes ont été réalisées dans chaque région.

Pour cela, nous utilisons la formule suivante en cellule N9 :

=GROUPER.PAR(H9:H23;H9:H23;NBVAL)

Dans cette formule, nous regroupons nos données selon la colonne Région (H9:H23) et nous comptons le nombre de lignes pour chaque région en utilisant l'opération « NBVAL » sur n'importe quelle colonne (ici, nous avons choisi de réutiliser la colonne Région).

Le résultat est un tableau à deux colonnes affichant le nom de chaque région et le nombre de ventes correspondant.

Pour rendre ce tableau plus lisible, Excel a également appliqué un tri alphabétique sur les données !

Si nous souhaitons modifier la colonne sur laquelle appliqué ce tri, il suffit d’utiliser l’argument facultatif « sort_order » :

=GROUPER.PAR(H9:H23;H9:H23;NBVAL;;;2)

Maintenant, les données sont triées en fonction du nombre de vente.

Pour un tri inversé, il suffit d’utiliser une valeur négative :

=GROUPER.PAR(H9:H23;H9:H23;NBVAL;;;-2)

Cette formule trie le résultat selon la deuxième colonne (le nombre de ventes) dans l'ordre décroissant, ce qui permet de voir immédiatement quelles sont les régions avec le plus grand nombre de ventes.

Ce qui est impressionnant avec GROUPER.PAR, c'est que nous n'avons pas eu besoin de créer plusieurs tableaux croisés dynamiques différents pour effectuer ces analyses. Nous avons simplement saisi deux formules qui nous ont donné les résultats instantanément. Et si nos données sources changent, ces résultats seront automatiquement mis à jour.

4. Analyses avancées avec des critères multiples

   4.1. Regroupement par plusieurs critères

Jusqu'à présent, nous avons regroupé nos données selon un seul critère. Mais GROUPER.PAR nous permet également de regrouper selon plusieurs critères simultanément.

Prenons un exemple concret : analysons le chiffre d'affaires par catégorie et par région.

Pour cela, nous utilisons la formule suivante :

=GROUPER.PAR(ASSEMB.H(B8:B23;H8:H23);E9:E23*F9:F23;SOMME)

Comme vous pouvez le constater, ici nous faisons intervenir la fonction ASSEMB.H() qui permet de retourner un tableau contenant plusieurs colonnes assemblées les unes à la suite des autres.

Le résultat est un tableau à trois colonnes : la première pour la catégorie, la deuxième pour la région, et la troisième pour le chiffre d'affaires.

Pour facilité la lecture, nous allons maintenant souhaiter inclure les en-têtes du tableau.

Pour cela, nous agrandissons les plages de cellules utilisées à partir de la ligne 8 :

=GROUPER.PAR(CHOISIRCOLS(B8:H23;1;7);E8:E23*F8:F23;SOMME;3)

Bien entendu, nous avons une erreur, car Excel ne peut pas déterminer le titre à donner à la colonne de CA, étant donné que cette information n’apparaît pas dans le tableau source.

Maintenant, nous pouvons également insérer des sous-totaux intermédiaires, pour obtenir le montant du CA par catégorie, en utilisant l’argument « total_traitment » :

=GROUPER.PAR(CHOISIRCOLS(B8:H23;1;7);E8:E23*F8:F23;SOMME;3;2)

   4.2. Filtrer les données

Si le tableau retourné devient trop grand et difficile à lire, nous pouvons le filtrer pour n'afficher que les informations qui nous intéressent en utilisant l’argument « filter_array ».

Par exemple, supposons que nous voulions uniquement voir les auteurs qui ont vendus des BD.

Tout d’abord, pour obtenir le nombre de vente par auteur, nous utiliserons la formule suivante :

=GROUPER.PAR(D9:D23;F9:F23;SOMME)

Maintenant, pour filtrer les lignes, nous modifions la formule en ajoutant l’argument suivant :

=GROUPER.PAR(D9:D23;F9:F23;SOMME;;;;B9:B23="BD")

Si nous souhaitons maintenant connaître uniquement les auteurs qui ont vendu au moins 5 livres, toutes catégories confondu, nous ne pourront appliquer directement un filtre sur un argument, et nous devront faire intervenir deux fonction supplémentaires (FILTRE() et CHOISIRCOLS()) :

=FILTRE(GROUPER.PAR(D9:D23;F9:F23;SOMME);CHOISIRCOLS(GROUPER.PAR(D9:D23;F9:F23;SOMME);2)>5)

Dans cette formule, nous utilisons trois fonctions qui travaillent ensemble :

- GROUPER.PAR(D9:D23;F9:F23;SOMME) effectue l'agrégation initiale des ventes par auteur.

- CHOISIRCOLS(GROUPER.PAR(D9:D23;F9:F23;SOMME);2) extrait la deuxième colonne du résultat, qui contient les sommes des ventes. Cette fonction est nécessaire car nous avons besoin d'accéder spécifiquement à la colonne des sommes pour appliquer notre critère de filtrage.

- FILTRE() applique la condition (>5) sur les résultats de l'agrégation, ne conservant que les lignes où le nombre total de ventes dépasse 5.

En suivant le même raisonnement, nous pourront ainsi obtenir le montant des ventes des auteurs ayant réalisé au moins 50€ de CA :

=FILTRE(GROUPER.PAR(D9:D23;E9:E23*F9:F23;SOMME);CHOISIRCOLS(GROUPER.PAR(D9:D23;E9:E23*F9:F23;SOMME);2)>50)

Le résultat est un tableau filtré qui ne montre que les auteurs ayant dépassé le seuil des 50 € de chiffre d'affaires. Cette capacité à combiner GROUPER.PAR avec d'autres fonctions est l'un de ses principaux avantages par rapport aux tableaux croisés dynamiques.