Qu'est-ce que la fonction GROUPER.
GROUPER.PAR (GROUPBY en anglais) est la fonction qui fait ce qu'Excel n'avait jamais pu faire nativement avec des formules : regrouper des lignes par catégorie et calculer un agrégat (somme, moyenne, comptage, max...) en une seule formule. Disponible uniquement dans Excel 365, elle déverse son résultat automatiquement dans un tableau dynamique qui se met à jour dès que les données sources changent.
Concrètement, c'est l'équivalent d'un GROUP BY SQL directement dans ta feuille de calcul : total des ventes par région, moyenne des notes par classe, comptage de tickets par priorité, consolidation de dépenses par département. Et contrairement à un tableau croisé dynamique, tu n'as pas besoin de cliquer sur « Actualiser » : la formule recalcule toute seule.
Syntaxe
=GROUPER.PAR(lignes_à_grouper; valeurs_à_agréger; fonction_agrégation; [étiquettes_champs]; [profondeur_totaux]; [mode_tri])Clique sur un argument pour aller à son explication.
GROUPER.PAR est disponible uniquement dans Excel 365. Sur Excel 2019 ou versions antérieures, la fonction retourne #NOM?. Google Sheets ne la supporte pas non plus.
Comprendre chaque paramètre de la fonction GROUPER.PAR
Les trois premiers arguments sont obligatoires et se suivent dans cet ordre : la colonne qui sert de critère de regroupement, la colonne de chiffres à agréger, puis la fonction d'agrégation (SOMME, MOYENNE, NB...) passée sans parenthèses.
Les trois derniers sont facultatifs. Si tu veux atteindre mode_tri sans toucher aux deux du milieu, laisse-les vides avec des points-virgules : =GROUPER.PAR(A2:B5;C2:C5;SOMME;;;-1) saute étiquettes_champs et profondeur_totaux pour ne régler que le tri.
lignes_à_grouper
: la colonne (ou les colonnes) dont tu veux extraire les valeurs uniques pour construire les groupesC'est le critère de regroupement : région, catégorie, commercial, département, etc.
Pour grouper par plusieurs colonnes simultanément, utilise CHOISIRCOLS pour sélectionner les colonnes concernées dans ton tableau.
valeurs_à_agréger
: la colonne (ou les colonnes) contenant les valeurs numériques à agréger pour chaque groupeCe sont les chiffres que tu veux sommer, moyenner ou compter : montants, notes, heures, quantités.
Cette plage doit avoir le même nombre de lignes que lignes_à_grouper, sinon tu obtiendras une erreur #VALEUR!.
Attention : Les plages lignes_à_grouper et valeurs_à_agréger doivent couvrir exactement le même nombre de lignes. Une différence d'une seule ligne suffit à provoquer #VALEUR!.
fonction_agrégation
: la fonction à appliquer sur les valeurs de chaque groupeTu passes la référence de la fonction sans parenthèses : SOMME, MOYENNE, NB, MIN, MAX, ECARTYPE, etc.
Tu n'es pas limité à un seul calcul : réunis plusieurs fonctions avec ASSEMB.H pour les obtenir côte à côte, comme ASSEMB.H(SOMME; MOYENNE). Le résultat gagne une colonne par calcul, dans l'ordre où tu les as listées, et une ligne d'en-tête vient nommer chacune.
Tu peux aussi utiliser LAMBDA pour créer une agrégation personnalisée qui ne correspond pas aux fonctions standard : sa colonne s'intitule alors PERSONNALISÉ.
[étiquettes_champs]
: indique si tes plages sources ont une ligne d'en-tête et si le résultat doit en afficher une(facultatif)Par défaut, Excel repère tout seul les en-têtes de tes colonnes mais ne les reprend pas dans le résultat. Passe 3 pour les afficher, 1 pour les repérer sans les afficher, 2 pour générer des libellés génériques, 0 pour déclarer que tes plages n'ont pas d'en-tête.
[profondeur_totaux]
: règle les lignes de totaux que la fonction ajoute au résultat(facultatif)Par défaut (1), GROUPER.PAR pose d'office une ligne Total en bas de son tableau : c'est elle que tu vois apparaître sans l'avoir demandée. Passe 0 pour la supprimer, 2 pour obtenir en plus un sous-total par groupe quand tu regroupes sur plusieurs colonnes, ou -1 pour remonter le total en haut du résultat.
[mode_tri]
: le numéro de la colonne du résultat sur laquelle trier, le signe donnant le sens : `1` trie sur la première colonne en croissant (c'est le défaut), `-1` sur cette même colonne en décroissant(facultatif)2 et -2 visent la colonne suivante, celle des valeurs agrégées.
Le numéro doit désigner une colonne qui existe dans le résultat : 0, comme un numéro trop grand, renvoie #VALEUR!.
Exemples pratiques pas à pas
Comment totaliser des ventes par région avec la fonction GROUPER.PAR
La réunion commerciale est dans une heure et on te réclame le chiffre d'affaires de chaque région. Tes quatorze ventes sont saisies ligne par ligne avec leur région et leur vendeur, si bien que Nord ou Sud reviennent quatre fois chacun, et tu veux un total propre par région sans monter un tableau croisé dynamique pour si peu. La fonction GROUPER.PAR repère les régions toute seule, additionne les ventes de chacune et pose le résultat à côté de ton tableau.
- 1Dans une cellule, écris
=GROUPER.PAR(. - 2En 1er argument : sélectionne la colonne qui sert de critère de regroupement,
A2:A15. La fonction y repère les valeurs distinctes (Nord, Sud, Est, Ouest) et fabrique un groupe pour chacune, sans que tu aies à taper cette liste toi-même. - 3En 2ᵉ argument : sélectionne la colonne des ventes à additionner,
C2:C15. Cette plage doit couvrir exactement les mêmes lignes que la première, de la ligne 2 à la ligne 15, sinon la fonction renvoie#VALEUR!. - 4En 3ᵉ argument : écris
SOMME, sans parenthèses ni plage derrière. Tu passes la fonction elle-même en argument, et c'est GROUPER.PAR qui l'appliquera sur chaque groupe. RemplacerSOMMEparMOYENNEouMAXsuffirait à changer tout le calcul. - 5Ferme la parenthèse et appuie sur Entrée. Comme tu laisses les trois arguments facultatifs de côté, la fonction trie les groupes par région dans l'ordre croissant, Est en tête, et déverse son tableau sur les lignes du dessous sans aucune recopie.
=GROUPER.PAR(A2:A15; C2:C15; SOMME)1 000, 1 548, 2 096 et 2 644) donnent 7288. Excel complète tout seul avec la ligne Total (26467), le cumul des quatre groupes, et l'ensemble se recalcule dès qu'une vente bouge.
Comment compter le nombre de tickets par priorité avec la fonction GROUPER.PAR
Un client s'impatiente au téléphone et tu veux savoir combien de tickets urgents restent sur la pile avant de lui promettre un délai. Ta file d'attente liste un ticket par ligne avec son niveau de priorité, et tu ne cherches pas un montant cette fois, seulement le nombre de tickets de chaque niveau. La fonction GROUPER.PAR fait ce comptage aussi bien qu'une somme, parce que le calcul appliqué à chaque groupe n'est qu'un argument parmi les autres.
- 1Dans une cellule, écris
=GROUPER.PAR(. - 2En 1er argument : sélectionne la colonne qui sert de critère de regroupement,
A2:A9. La fonction en tire les trois niveaux présents (Basse, Haute, Moyenne) et regroupe les tickets derrière chacun. - 3En 2ᵉ argument : sélectionne la colonne des tickets à dénombrer,
B2:B9. Compter réclame quand même une colonne à examiner, et ce sont bien les références de tickets qu'on compte ici, pas des montants. - 4En 3ᵉ argument : écris
NBVAL, toujours sans parenthèses. Méfie-toi du réflexeNB: cette fonction ne compte que les nombres, or une référence commeT-1041est du texte, si bien que chaque groupe afficherait0. NBVAL compte toutes les cellules remplies, texte compris. - 5Ferme la parenthèse et appuie sur Entrée. La fonction ajoute d'elle-même la ligne Total, qui récapitule les 8 tickets de la file.
=GROUPER.PAR(A2:A9; B2:B9; NBVAL)4 parce que quatre lignes portent ce niveau, et la ligne Total récapitule les 8 tickets de la file.
Comment grouper par deux colonnes à la fois avec la fonction GROUPER.PAR
Le total par région ne suffit plus : on te demande maintenant quel produit porte les ventes dans chaque région, parce que le réassort se décide produit par produit. Tes ventes portent leur région et leur produit sur la même ligne, et tu veux un total pour chaque couple, Nord et Clavier ensemble, Nord et Souris ensemble, et ainsi de suite. La fonction GROUPER.PAR accepte plusieurs colonnes de groupement d'un coup, sans que tu aies à fabriquer une colonne intermédiaire qui colle les deux valeurs bout à bout.
- 1Dans une cellule, écris
=GROUPER.PAR(. - 2En 1er argument : sélectionne d'un seul tenant les deux colonnes de regroupement, Région et Produit,
A2:B9. La fonction ne compare plus une valeur mais un couple, et ne crée un groupe que pour les combinaisons réellement présentes dans tes données, quatre ici. Les deux colonnes doivent être voisines pour tenir dans une seule plage, et si elles ne le sont pas, rassemble-les d'abord avecCHOISIRCOLS. - 3En 2ᵉ argument : sélectionne la colonne des ventes à additionner,
C2:C9. Elle n'occupe qu'une colonne alors que le regroupement en prend deux, et c'est normal, car seul le nombre de lignes doit correspondre entre les deux plages, jamais le nombre de colonnes. - 4En 3ᵉ argument : écris
SOMME, exactement comme pour un regroupement simple. Élargir le critère de une à deux colonnes ne change rien aux arguments qui suivent. - 5Ferme la parenthèse et appuie sur Entrée. La fonction sort une ligne par couple région-produit et ajoute la ligne Total en bas du tableau.
=GROUPER.PAR(A2:B9; C2:C9; SOMME)Nord et Clavier comme une seule clé de regroupement, et additionne les deux ventes concernées (1 200 € et 900 €) pour sortir 2100. Le tri croissant s'applique d'abord sur la région, puis sur le produit à l'intérieur de chaque région.
Comment afficher un total et une moyenne côte à côte avec les fonctions GROUPER.PAR et ASSEMB.H
Ton rapport mensuel ne dit pas grand-chose tant qu'il n'oppose pas le volume au panier moyen, parce qu'une agence peut afficher le plus gros total tout en signant les plus petits contrats. Tes contrats sont listés un par ligne avec leur agence et leur montant, et tu veux le total et la moyenne de chaque agence sur la même ligne. Inutile d'écrire deux formules et de recoller leurs résultats : la fonction GROUPER.PAR accepte une liste de calculs dans son troisième argument, à condition de les réunir avec la fonction ASSEMB.H.
- 1Dans une cellule, écris
=GROUPER.PAR(. - 2En 1er argument : sélectionne la colonne des agences,
A2:A7. C'est le critère de regroupement, et la fonction en tire les deux agences distinctes, Lyon et Paris. - 3En 2ᵉ argument : sélectionne la colonne des montants à agréger,
C2:C7. Rien ne bouge de ce côté par rapport à un regroupement classique, c'est le troisième argument qui va porter tout le travail. - 4En 3ᵉ argument : écris la liste des calculs voulus réunie par
ASSEMB.Het referme-la,ASSEMB.H(SOMME; MOYENNE). Les deux fonctions s'écrivent sans parenthèses derrière elles, et l'ordre dans lequel tu les listes est celui des colonnes du résultat. - 5
=GROUPER.PAR(A2:A7; C2:C7; ASSEMB.H(SOMME; MOYENNE))ASSEMB.H : la fonction sort alors une colonne par calcul et pose au-dessus une ligne d'en-tête qui les nomme, SOMME puis MOYENNE. Paris totalise 12000 pour une moyenne de 4000 sur trois contrats, là où Lyon fait 7200 pour 2400. La colonne Agence n'apparaît qu'une seule fois, et la ligne Total reçoit les deux calculs : 19200 de cumul et 3200 de moyenne sur les six contrats.
Les erreurs fréquentes avec la fonction GROUPER.PAR
Avant de soupçonner ta formule, regarde ta version : #NOM? veut dire que ton Excel ne connaît tout simplement pas GROUPER.PAR (elle n'existe qu'en 365). Une fois ce verrou levé, les blocages restants tiennent presque toujours à la forme de tes plages.
Un #VALEUR! signale que lignes_à_grouper et valeurs_à_agréger n'ont pas le même nombre de lignes, un #DEVERSER! que des cellules occupées empêchent le tableau de s'étaler, et un #CALC! que la fonction d'agrégation passée n'accepte pas une plage de nombres. Les lignes vides dans ta colonne de groupement, elles, créent un groupe (vide) parasite.
#NOM? : la fonction n'est pas reconnue
GROUPER.PAR n'existe que dans Excel 365. Sur Excel 2019, Excel 2021 ou toute version antérieure, Excel ne connaît pas cette fonction et retourne #NOM?.
Solution : Vérifie ta version d'Excel via Fichier → Compte → À propos d'Excel. Si tu n'as pas Excel 365, utilise un tableau croisé dynamique ou des formules SOMME.SI.ENS / NB.SI.ENS comme alternative.
#VALEUR! : dimensions incompatibles entre les plages
Les plages lignes_à_grouper et valeurs_à_agréger n'ont pas le même nombre de lignes. Même une ligne de différence suffit à provoquer cette erreur.
Solution : Vérifie que les deux plages couvrent exactement les mêmes lignes : =GROUPER.PAR(A2:A5;B2:B5;SOMME) fonctionne, =GROUPER.PAR(A2:A5;B2:B10;SOMME) provoque #VALEUR!. Utilise des colonnes entières (A:A;B:B) pour éviter le problème si ton tableau s'agrandit.
#DEVERSER! : espace insuffisant pour le tableau résultat
GROUPER.PAR retourne un tableau dynamique qui doit se déverser dans les cellules à droite et en dessous. Si des données existantes occupent cet espace, Excel ne peut pas écrire le résultat.
Solution : Libère l'espace à droite et en dessous de la cellule qui contient la formule. Supprime ou déplace les données qui bloquent le déversement. Choisis une cellule de départ dans une zone vide de ta feuille.
#CALC! : fonction d'agrégation invalide
La fonction_agrégation doit être une fonction acceptant des plages numériques comme argument, telle que SOMME, MOYENNE, NB, MIN, MAX. Des fonctions comme SI, RECHERCHEV ou TEXTE ne sont pas valides ici.
Solution : Utilise uniquement des fonctions statistiques ou mathématiques standards : SOMME, MOYENNE, NB, NBVAL, MIN, MAX, ECARTYPE, VAR. Pour des agrégations personnalisées, passe par LAMBDA.
Un groupe vide apparaît dans le résultat
Les cellules vides dans la colonne de groupement créent un groupe supplémentaire vide. Si ta colonne Région a des lignes sans valeur, GROUPER.PAR les regroupe dans un groupe (vide).
Solution : Filtre les lignes vides avant d'appliquer GROUPER.PAR : =GROUPER.PAR(FILTRE(A2:A100;A2:A100<>"");FILTRE(B2:B100;A2:A100<>"");SOMME). FILTRE retire les lignes sans catégorie avant le regroupement.
GROUPER.PAR vs Tableau croisé dynamique vs SOMME.SI.ENS
GROUPER.PAR est une formule qui se recalcule automatiquement sans actualisation. Le tableau croisé dynamique offre une interface graphique plus riche mais nécessite un clic d'actualisation. SOMME.SI.ENS est compatible avec toutes les versions d'Excel mais nécessite une formule par combinaison de critères.
| Critère | GROUPER.PAR | Tableau croisé | SOMME.SI.ENS |
|---|---|---|---|
| Recalcul automatique | Oui (formule dynamique) | Non (actualisation manuelle) | Oui (formule classique) |
| Compatibilité | Excel 365 uniquement | Toutes versions | Toutes versions |
| Plusieurs métriques | Natif (ASSEMB.H en argument) | Natif (interface graphique) | Une formule par métrique |
| Combinable avec formules | Oui | Non | Oui |
| Cas d'usage idéal | Rapports auto-mis à jour | Exploration interactive | Calcul ciblé multi-critères |
Questions fréquentes
Non. GROUPER.PAR est exclusive à Excel 365 (abonnement Microsoft 365). Si tu utilises Excel 2019, Excel 2021 ou toute version sans abonnement, la fonction retourne #NOM?. Pour des analyses similaires, utilise les tableaux croisés dynamiques ou des combinaisons de SOMME.SI.ENS et NB.SI.ENS.
GROUPER.PAR est une formule qui se recalcule automatiquement dès que les données sources changent, sans aucune action de ta part. Un tableau croisé dynamique, lui, nécessite un clic sur « Actualiser » pour prendre en compte les nouvelles données. GROUPER.PAR est aussi plus flexible pour les calculs complexes car tu peux la combiner avec d'autres formules. Le TCD reste préférable pour l'exploration interactive avec une interface graphique.
Oui. Pour grouper par région ET par produit, passe les deux colonnes dans lignes_à_grouper. Tu peux utiliser CHOISIRCOLS(A1:D100;1;2) pour sélectionner les colonnes 1 et 2 d'un tableau, ou simplement référencer deux colonnes adjacentes directement : =GROUPER.PAR(A2:B100;C2:C100;SOMME).
Réunis les fonctions voulues avec ASSEMB.H directement dans fonction_agrégation : =GROUPER.PAR(A2:A100;B2:B100;ASSEMB.H(SOMME;MOYENNE)) sort le total et la moyenne côte à côte. Chaque colonne est coiffée du nom de son calcul, et la colonne de groupement n'est écrite qu'une fois.
Deux montages sont acceptés. Soit tu n'as qu'une colonne de valeurs, et chacune des fonctions s'y applique. Soit tu passes autant de fonctions que de colonnes de valeurs, et Excel les apparie une à une : =GROUPER.PAR(A2:A100;C2:D100;ASSEMB.H(SOMME;MOYENNE)) somme la colonne C et moyenne la colonne D. Tout autre décompte renvoie #VALEUR!.
Passe -2 en mode_tri : le 2 désigne la deuxième colonne du résultat, celle des sommes, et le signe moins demande l'ordre décroissant. =GROUPER.PAR(A2:A100;B2:B100;SOMME;;;-2) sort donc le groupe le plus gros en premier, et la ligne Total reste en bas.
Évite d'imbriquer le résultat dans TRIER : comme la ligne Total porte la plus grosse valeur de la colonne, elle remonterait en tête du tableau.
Pour aller plus loin
Continue sur ta lancée après la fonction GROUPER.PAR : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
