Présentation de la fonction Excel GrouperPar

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Présentation de la fonction Excel Grouper.Par

La fonction Excel Grouper.Par permet de réaliser de très judicieuses consolidations sur des tableaux. Elle est redoutable sur les données volumineuses pour y voir rapidement plus clair. Elle est en effet très simple de mise en oeuvre. En outre, elle court-circuite allègrement les tableaux croisés dynamiques.

Classeur Excel à télécharger
Nous proposons d'appuyer la découverte sur un classeur Excel existant.

• Télécharger le classeur presentation-grouper-par.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification dans le bandeau de sécurité,

Nous découvrons un classeur fait de deux feuilles. Nous allons tout d'abord nous intéresser à la première. Elle livre un tableau de candidats appartenant à des équipes représentées par des couleurs différentes. Chaque candidat a obtenu individuellement des points.

Grouper et sommer
L'un des intérêts majeurs de cette fonction Grouper.Par est qu'en même temps qu'elle permet de réunir les données communes, elle autorise des opérations de synthèse sur les consolidations. Ici l'idée est de pouvoir sommer tous les points pour chaque équipe consolidée. Ainsi nous connaîtrons la plus vaillante des trois car oui, malgré le nombre de lignes, il n'y a que trois équipes. Remarque : Nous avons nommé la colonne des équipes : equipes et celle des points : points. Nous allons utiliser ces noms dans la formule de consolidation.

• Cliquer sur la première case vide du tableau sur la droite pour sélectionner la cellule G4,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Inscrire la fonction de consolidation, suivie d'une parenthèse, soit : Grouper.Par(,
• En premier paramètre, taper la colonne à consolider par son nom, soit : equipes,
• Puis, taper un point-virgule (;) pour passer dans l'argument suivant,

Ce paramètre concerne les valeurs numériques à synthétiser sur les données regroupées depuis la colonne mentionnée en premier argument.

• Inscrire la plage des scores par son nom, soit : points,
• Taper un point-virgule (;) pour passer dans l'argument de l'opération de synthèse,
• Taper le nom de la fonction d'addition, soit : Somme,

C'est ainsi que nous allons pouvoir réaliser tous les cumuls sur les équipes regroupées.

• Fermer la parenthèse de la fonction Grouper.Par,
• Enfin, valider la formule par la touche Entrée du clavier,

En un clin d'oeil grâce à cette fabuleuse fonction Excel Grouper.Par, nous apprenons que la meilleure des équipes est la rouge, cumulant un total de 778 points. Elles ne sont effectivement qu'au nombre de trois. Et pour cela, nous avons bâti une syntaxe absolument triviale :

=GROUPER.PAR(equipes;points;SOMME)

La richesse des enseignements est redoutable. Quel gain de temps !

Trier les données consolidées
Mais pour y voir encore plus clair, il apparaît opportun d'organiser les informations consolidées dans l'ordre décroissant, de la meilleure équipe à la moins bonne. Choix d'autant plus judicieux lorsque les informations en retour sont nombreuses. Et pour cela, il suffit simplement d'actionner le sixième paramètre de la fonction Grouper.Par. Il se nomme sort_order.

• Cliquer sur la cellule G4 pour resélectionner la cellule du calcul,
• Dans sa barre de formule, cliquer juste avant la parenthèse fermante,
• Inscrire trois points-virgules (;;;) pour atteindre directement le sixième argument,
• Taper la valeur -2 et valider l'adaptation par la touche Entrée du clavier,

=GROUPER.PAR(equipes;points;SOMME;;;-2)

Cette fois, les données consolidées, accompagnées de leurs résultats de synthèse, sont parfaitement organisées dans l'ordre décroissant. C'est fabuleux !



Consolider et compter sur les paires
Grâce à cette merveilleuse fonction Excel Grouper.Par, nous allons voir qu'il est aussi possible de fournir des résultats de synthèse sur des paires de colonnes et pas seulement sur une seule, comme nous venons de le voir.

• En bas de la fenêtre Excel, cliquer sur l'onglet bd_sorties pour activer sa feuille,

Nous découvrons une base de données des activités de sorties recensées par départements et villes, par effet entonnoir. Forcément, étant donnée la masse d'informations, de nombreux départements se répètent mais aussi de nombreuses villes dans les départements. Nous souhaiterions connaître les paires Département/Ville les plus représentées, sans redondance et avec les décomptes des activités qui y sont inscrites.



Nous avons nommé les colonnes Id, Département et Ville respectivement comme suit : id, dep et villes. La colonne id est celle des identifiants. Elle est donc importante pour les décomptes uniques, sans ambiguïté.

• Cliquer sur la première case vide à droite du tableau pour sélectionner la cellule H2,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Inscrire la fonction de consolidation, suivie d'une parenthèse, soit : Grouper.Par(,
• Désigner les paires à synthétiser comme suit : dep:Villes,

Nous séparons les deux plages par le symbole deux points (:) pour les considérer ensemble.

• Taper un point-virgule (;) pour passer dans l'argument des valeurs à compter,

Le décompte doit se faire sur les identifiants.

• Désigner cette colonne par son nom, soit : id,
• Taper un nouveau point-virgule pour passer dans l'argument de l'opération de synthèse,
• Dans les propositions, choisir la fonction de décompte, soit : NBVAL,
• Fermer la parenthèse de la fonction Grouper.Par,
• Puis, valider la formule par la touche Entrée du clavier,

Nous obtenons bien les décomptes par paires, c'est plutôt fantastique. Par contre, les données résultantes sont organisées par défaut sur les noms des départements. Dans le cas de données volumineuses comme ici, il est donc très compliqué de se faire une idée sur les lieux les mieux représentés.

Organiser les paires consolidées
Pour organiser le décompte sur la troisième colonne, nous ne pouvons pas utiliser le même artifice que précédemment en actionnant le sixième paramètre de la fonction Grouper.Par. En effet, cette colonne de décompte id, n'est pas une colonne naturellement numérique sur laquelle nous avons engagé la somme ou la moyenne par exemple. Mais une astuce existe. Elle consiste à imbriquer cette fonction Grouper.Par dans la fonction Excel Trier.

• Cliquer sur la cellule H2 pour la resélectionner,
• Dans la barre de formule, cliquer juste après le symbole égal pour placer le point d'insertion,
• Inscrire la fonction d'organisation, suivie d'une parenthèse, soit : Trier(,

Ainsi, les données consolidées sont désormais encapsulées dans cette fonction de tri.

• Cliquer à la toute fin de la syntaxe pour y replacer le point d'insertion,
• Puis, la terminer comme suit : ;3;-1,

Ainsi, nous commandons un tri décroissant (-1) sur la troisième colonne (3) générée à la volée.

• Fermer la parenthèse de la fonction Trier,
• Enfin, valider la formule par la touche Entrée du clavier,

Cette fois, nous obtenons un rendu beaucoup plus explicite. C'est ainsi que nous apprenons facilement que la ville de Grenoble dans l'Isère est la plus représentée devant la ville de Valence dans la Drôme.

Enfin, vous notez la présence d'une ligne de total général en entête. Elle a été générée automatiquement. Pour ne pas la voir apparaître, il suffit de l'indiquer dans le cinquième argument de la fonction Grouper.Par, comme suit : =TRIER(GROUPER.PAR(dep:Villes; id; NBVAL; ;0);3; -1).