La fonction GROUPER.PAR (ou GROUPBY en anglais)
est une révolution récente dans Microsoft Excel (version 2024). Elle permet de regrouper, filtrer, trier et agréger des données dynamiquement à l'aide d'une seule formule, agissant comme un Tableau Croisé Dynamique (TCD)
La formule comporte 3 arguments obligatoires et 5 facultatifs.
L'assistant vous sera d'un grand secours.
Arguments 1, 2, 3 :
Row_fields (Champs_ligne) : plage ou tableau des champs utilisés en étiquette de ligne.
Values (Valeurs) : Plage ou tableau des champs utilisés en tant que valeur à synthétiser.
Function (Fonction) : Fonction ou tableau de fonctions utilisé sur le(s) champ(s) de Valeur (SOMME, MOYENNE, ...).
Field_headers (en-tête_champs) : paramètre de prise en compte des étiquettes pour les champs
Non précisé : détection automatique d'étiquette (ne semble pas bien fonctionner).
0 : Non (la 1ʳᵉ ligne sera prise comme une donnée/reprise dans les étiquettes de ligne).
1 : Oui mais non affichée (la 1ʳᵉ ligne ne sera pas prise comme une donnée/non reprise dans les étiquettes de ligne).
2 : Non mais affichée (Excel affichera alors Champ de ligne 1, Champ de ligne 2...).
3 : Oui mais affichée (utilise les valeurs de la 1ʳᵉ ligne).
Total_Treatement (Traitement_total) : Paramètre d'affichage des totaux généraux et sous-totaux.
Non précisé : mode automatique.
0 : Aucun total.
1 : Totaux généraux en dessous des valeurs (pour la colonne entière).
2, 3, 4... : totaux généraux et sous-totaux en dessous des valeurs. La valeur indique le nombre de niveaux à activer (À condition d'un nombre suffisant de niveaux .
-1, -2, -3... : Identique à 1, 2, 3... mais place les totaux généraux et sous-totaux en haut des valeurs.
Sort_order (Ordre_tri) : appliquer un tri (positif = croissant, négatif = décroissant).
Filter_array (Filtre_tableau) : matrice de valeurs booléennes où la valeur VRAI représente les lignes à conserver.
Field_relationship (Relation_champ) : Respect de la hiérarchie des champs lors du tri des valeurs de l'argument Valeur (Total_Treatement doit être Non précisé, 0 ou 1).
Non précisé ou 0 : mode hiérarchique.
1 : Tableau.
Les champs obligatoires pour sortir un résultat
(Champs_ligne) : plage ou tableau des champs utilisés en étiquette de ligne.
(Valeurs) : Plage ou tableau des champs utilisés en tant que valeurs à synthétiser.
(Fonction) : Fonction ou tableau de fonctions utilisé sur le(s) champ(s) de Valeur (SOMME, MOYENNE, …).
Avec le tableau ci-contre, testons cette première formule. =GROUPER.PAR(B1:B100;E1:E100;SOMME)
Nous obtenons le nombre de véhicules livrés par modèle avec un total sur la dernière ligne.
Le fait de remplacer SOMME par POURCENTAGE.DE nous permet d'avoir la répartition en pourcentage des trois véhicules, par exemple. (liste complète des opérations disponible dans l'assistant…)
Argument 4 :
Row_fields (Champs_ligne) : plage ou tableau des champs utilisés en étiquette de ligne.
Le code "3" vous permet d'afficher le nom des champs. ce qui nous donne : =GROUPER.PAR(B1:B100;E1:E100;SOMME;3)
Argument 5 :
Total_Treatement (Traitement_total) : Paramètre d'affichage des totaux généraux et sous-totaux.
En élargissant les données sélectionnées avec =GROUPER.PAR(B1:C100;E1:E100;SOMME;3;-2) nous sélectionnons les modèles de véhicule et les chauffeurs. Le chiffre "2" dans l'argument 5 nous permet d'avoir des sous-totaux, "-2" nous permet d'avoir ces éléments en haut.
Argument 6 :
Sort_order (Ordre_tri) : appliquer un tri (positif = croissant, négatif = décroissant). Cet argument nous permet de définir un ordre croissant ou décroissant pour la première ou la seconde colonne dans l'exemple.
=GROUPER.PAR(C1:C100;E1:E100;SOMME;3;;2) Les en-têtes de colonnes sont toujours présents, les sous-totaux ont disparu, le 6ᵉ argument est donc "2", ce qui va classer par ordre croissant la seconde colonne.
Argument 7 :
Filter_array (Filtre_tableau) : la valeur VRAI représente les lignes à conserver.
Dans l'exemple, nous conservons la valeur "MARTIN" ? qui nous filtre le tableau sur place en associant les valeurs "Véhicules" et "Nbre Clients Livrés", avec les en-têtes de colonnes.
=GROUPER.PAR(B1:C100;E1:E100;SOMME;3;;;(C1:C100)="MARTIN")
Fichier du tuto à télécharger ici
Support Vidéo