Fonction Groupby d'Excel: outils de regroupement de données et d'agrégation puissants

La fonction GROUPBY d'Excel vous permet de regrouper et d'agrégation des données basées sur des champs spécifiques dans le tableau de données. Il fournit également des paramètres qui vous permettent de trier et de filtrer les données afin que vous puissiez personnaliser la sortie à vos besoins spécifiques.

Syntaxe de fonction GROUPBY

La fonction GROUPBY contient huit paramètres:

 <code>=GROUPBY(a,b,c,d,e,f,g,h)</code>

Les paramètres A à C sont requis:

• A (champ de ligne): une plage (une colonne ou plusieurs colonnes) contenant la valeur ou la catégorie à laquelle les données sont regroupées.
• B (valeur): la plage de valeurs contenant des données agrégées (une colonne ou plusieurs colonnes).
• C (fonction): une fonction utilisée pour agréger les valeurs du paramètre b .

Les paramètres D à H sont facultatifs, et vous pouvez en savoir plus sur ces paramètres dans la dernière partie de cet article:

• D (Titre de champ): un nombre qui spécifie si vous avez sélectionné le titre dans les paramètres A et B , et s'ils devraient être affichés dans la sortie.
• E (profondeur totale): un nombre qui détermine si la sortie doit afficher le total.
• F (Ordre de tri): un nombre qui indique comment les résultats sont triés.
• G (tableau de filtre): une formule axée sur le tableau utilisé pour filtrer les informations inutiles.
• H (relation de champ): un nombre qui spécifie la relation de champ lorsque plusieurs colonnes sont fournies dans le paramètre a .

Fonction GROUPBY pratique: seuls les paramètres requis sont utilisés

Si vous êtes submergé par un grand nombre de paramètres de la fonction GROUPBY , il est important de noter que GROUPBY fonctionne parfaitement même si vous ne remplissez que les paramètres A , B et C. Donc, je vais d'abord vous montrer comment GROUPBY ne peut utiliser que ces trois paramètres.

Supposons que vous ayez une cha?ne de restaurants qui sert différents plats de différentes cuisines et que vous avez calculé le total des ventes et les cotes de clientèle moyennes pour chaque combinaison de cuisine.

Bien que ces données soient utiles, vous pouvez être plus intéressé par les comparaisons de différentes catégories de données. Plus précisément, vous voudrez peut-être conna?tre les revenus totaux par cuisine et la note moyenne des clients pour chaque plat.

Ensuite, puisque vous souhaitez voir les ventes totales pour chaque cuisine, sélectionnez la cellule qui contient ces données et ajoutez une autre virgule:

 <code>=GROUPBY(TabFood[Cuisine],TabFood[Sales],</code>

Le dernier paramètre requis est la fonction utilisée pour les données agrégées. Dans cet exemple, comme vous souhaitez découvrir les ventes totales de chaque cuisine, vous devez insérer la fonction SUM et désactiver les supports:

 <code>=GROUPBY(TabFood[Cuisine],TabFood[Sales],SUM)</code>

Après avoir appuyé sur Entrée, Excel calcule la note moyenne du client pour chaque type de plat. Encore une fois, sans aucun paramètre facultatif, les données sont triées par défaut par défaut par les valeurs de la colonne de gauche, avec une ligne totale pratique en bas.

étant donné que les valeurs de la colonne J sont des moyennes décimales, vous pouvez organiser les décimales affichées en cliquant sur les boutons "augmenter les décimales" et "diminuer les boutons de la décimale" du groupe "Nombres" sur l'onglet Démarrer.

Fonction GROUPBY pratique: utilisez des paramètres facultatifs

Bien que la fonction GROUPBY ait cinq paramètres facultatifs en plus des trois paramètres requis, ce qui le rend plus compliqué, ces options supplémentaires sont vraiment juste pour vous aider à créer une sortie plus conforme à vos besoins. Plus important encore, vous pouvez choisir les paramètres facultatifs à utiliser et ignorer les paramètres indésirables.

Ci-dessous, je couvrirai chaque paramètre facultatif afin que vous puissiez voir comment ils affecteront vos données lors de la sélection pour les inclure.

Titre de terrain

Dans mon exemple ci-dessus, j'ai tapé manuellement les en-têtes de colonne de sortie car par défaut, ils ne sont pas inclus dans le résultat. Cependant, si vous souhaitez que les données de sortie contiennent le titre de colonne et les données qu'il contient, utilisez le paramètre de titre de champ.

Tapez d'abord votre formule GROUPBY , y compris les trois premiers paramètres (requis). Dans ce cas, supposons que vous souhaitez regrouper les cuisines par note moyenne des clients:

 <code>=GROUPBY(A1:A21,D1:D21,AVERAGE</code>

Notez que la ligne de titre est incluse dans la sélection. En fait, lors de la sélection des données pour les deux premiers paramètres, vous devez considérer à l'avance si vous souhaitez publier le titre de copie de données dans le tableau.

Profondeur totale

Le paramètre de profondeur totale vous permet de décider si vous souhaitez que les résultats affichent un total, et si oui, s'ils devraient être en haut ou en bas des données. Ce paramètre vous permet également de choisir s'il faut afficher un sous-total.

Pour les paramètres de profondeur totale, type:

• 0, si vous ne voulez pas afficher de totaux ou de sous-totaux,
• 1. Si vous voulez juste afficher le total en bas du résultat,
• 2. Si vous voulez que le sous-total apparaisse en bas de chaque catégorie de résultats et affichez le total en bas du résultat entier,
• -1, si vous voulez juste afficher le total en haut du résultat,
• -2, si vous voulez que le sous-total apparaisse en haut de chaque catégorie de résultats et affichez le total en haut du résultat entier.

Tri

Le champ Ordre de tri vous permet de dire à Excel si et comment trier les résultats. L'utilisation de ce paramètre souligne pourquoi la fonction GROUPBY est plus utile que l'utilisation d'une table de pivot: tant que vous modifiez toutes les données dans la table d'origine, les données de sortie entières sont réorganisées en fonction des paramètres d'ordre de tri et que le tableau pivot doit être actualisé manuellement.

Le nombre que vous entrez pour ce paramètre représente la colonne dans le résultat. Par exemple, si vous tapez 1, cela triera les résultats de la première colonne dans l'ordre croissant ou alphabétique. D'un autre c?té, la saisie -1 trie les résultats de la première colonne dans l'ordre alphabétique descendant ou inverse.

Dans cet exemple, j'ai tapé:

 <code>=GROUPBY(A1:A21,C1:C21,SUM,,,,-2)</code>

Cela triera la deuxième colonne (ventes) dans l'ordre descendant.

Tableaux de filtre

Il est peu probable que les paramètres du tableau de filtrage soient utilisés comme les paramètres facultatifs précédents, bien qu'il puisse aider si votre tableau de données d'origine contient des lignes qui peuvent casser les données.

Dans cet exemple, les années dans les cellules A2, A8 et A17 interrompent le résultat de GROUPBY .

Je peux utiliser le paramètre du tableau de filtre pour dire à Excel d'ignorer n'importe quelle cellule contenant des numéros dans la colonne A via la fonction ISNUMBER :

 <code>=GROUPBY(A1:A24,C1:C24,SUM,,,,ISNUMBER(A1:A24)=FALSE)</code>

Relation sur le terrain

Enfin, le paramètre de relation de champ contr?le la fa?on dont les données sont regroupées lorsque le paramètre de champ de ligne fait référence à plusieurs colonnes.

Dans cet exemple, lorsque le paramètre de relation de champ contient 0 (la valeur par défaut si le paramètre est omis), GROUPBY renvoie un tableau de résultat hiérarchique, où chaque colonne est représentée par une ligne de données distincte.

 <code>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)</code>

D'un autre c?té, lorsque le paramètre de relation de champ contient 1, GROUPBY renverra une table de résultats qui ignore la hiérarchie et trie chaque colonne indépendamment. En d'autres termes, les catégories ne sont pas imbriquées, c'est pourquoi vous ne pouvez pas inclure de sous-totaux dans le résultat lorsque vous sélectionnez cette option de relation sur le terrain.

 <code>=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)</code>

En plus d'utiliser SUM et AVERAGE dans les paramètres de fonction GROUPBY , vous pouvez également utiliser la fonction PERENTOF , qui convertit les données en pourcentages pour afficher la proportion du sous-ensemble qui constitue l'ensemble de données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!