Name: Tremplin Numérique
Price range: $$$

La fonction agrégée d'Excel vous permet d'effectuer des calculs tout en ignorant des lignes, des erreurs ou d'autres fonctions cachées qui apparaissent dans les données. Il est similaire à la fonction sous-totale mais fournit plus d'options de calcul et vous donne plus de contrôle sur ce que vous voulez exclure du calcul.

Sommaire

La syntaxe agrégée

Avant de regarder quelques exemples de la fonction agrégée utilisée, voyons comment cela fonctionne. La fonction agrégée a deux syntaxes, une pour les références et une pour les tableaux, bien que vous n'ayez pas besoin de vous attacher en nœuds sur lequel vous utilisez, car Excel sélectionne celui pertinent en fonction des arguments que vous saisissez. Vous pouvez voir les deux syntaxes utilisées lorsque je vous montre bientôt quelques exemples.

La syntaxe du formulaire de référence

La syntaxe pour la forme de référence de la fonction agrégée est:

=AGGREGATE(a,b,c,d)

où

un (requis) est un nombre qui représente la fonction que vous souhaitez utiliser dans le calcul,
b (requis) est un nombre qui définit ce que vous voulez que le calcul ignore,
c (requis) est la plage de cellules sur lesquelles la fonction sera appliquée, et
d (Facultatif) est le premier de 252 arguments supplémentaires qui spécifient d'autres gammes.

La syntaxe du formulaire de tableau

D'un autre côté, si vous travaillez avec des tableaux, la syntaxe est:

=AGGREGATE(a,b,c,d)

où

un (requis) est un nombre qui représente la fonction que vous souhaitez utiliser dans le calcul,
b (requis) est un nombre qui définit ce que vous voulez que le calcul ignore,
c (requis) est le tableau de valeurs sur lesquelles la fonction sera appliquée, et
d est le deuxième argument requis par les fonctions de tableau comme les grands, petits, centiles, et autres.

Fonctions et exclusions (arguments un et b)

Lorsque vous saisissez des arguments un et b Dans les deux formulaires de syntaxe ci-dessus, vous aurez différentes options à choisir.

Le tableau ci-dessous montre les différentes fonctions que vous pouvez utiliser dans le calcul agrégé (argument un). Même si vous pouvez être tenté de taper le nom de la fonction, n'oubliez pas que cet argument doit être un nombre qui représente la fonction que vous souhaitez utiliser. Les fonctions 1 à 13 sont destinées à la syntaxe du formulaire de référence, et les fonctions 14 à 19 sont à utiliser avec la syntaxe du formulaire de tableau.

Nombre	Fonction	Ce qu'il calcule
1	MOYENNE	La moyenne arithmétique
2	COMPTER	Le nombre de cellules contenant des valeurs numériques
3	Comte	Le nombre de cellules qui ne sont pas vides
4	Max	La plus grande valeur
5	Min	La plus petite valeur
6	PRODUIT	Une multiplication
7	Stdev.s	L'écart type simple
8	Stdev.p	L'écart type basé sur la population
9	SOMME	Un ajout
10	Var.	La variation simple
11	Var.p	La variance basée sur la population
12	MÉDIAN	La valeur moyenne
13	Mode.snglg	Le nombre le plus fréquemment produit
14	GRAND	Le nla plus grande valeur
15	PETIT	Le nCette plus petite valeur
16	Centile.	Le nle centile, avec les premières et dernières valeurs incluses
17	Quartile.inc	Le nle quartile, avec les premières et dernières valeurs incluses
18	Centile.exc	Le nth centile, avec les premières et dernières valeurs exclues
19	Quartile.exc	Le nle quartile, avec les premières et dernières valeurs exclues

Ce tableau répertorie les nombres que vous pouvez saisir pour exclure certaines valeurs lors de la création de votre formule agrégée (argument b):

Nombre	Qu'est-ce qui est ignoré
0	Fonctions subtotales et agrégées imbriquées
1	Lignes cachées et fonctions sous-totales et agrégées imbriquées
2	Erreurs et fonctions subtotales et agrégées imbriquées
3	Lignes cachées, valeurs d'erreur et fonctions subtotales et agrégées imbriquées
4	Rien
5	Lignes cachées uniquement
6	Erreurs uniquement
7	Lignes et erreurs cachées

Voyons maintenant quelques exemples de la façon dont vous pouvez utiliser la fonction agrégée dans les scénarios du monde réel.

Exemple 1: Utilisation d'agrégats pour ignorer les erreurs

Cette feuille de calcul Excel contient une liste de joueurs de football, le nombre de jeux qu'ils ont joués, le nombre de buts qu'ils ont marqués et leurs ratios de jeu par objectif. Votre objectif est de déterminer le rapport match par objectif moyen pour tous les joueurs combinés.

Si vous deviez utiliser la fonction moyenne seule en tapant:

=AVERAGE(Player_Goals(Games per goal))

Dans la cellule C1, cela renverrait une erreur, car la plage référencée contient # div / 0! erreurs.

En rapport

Comment corriger les erreurs de formule courantes dans Microsoft Excel

Découvrez ce que signifie cette erreur et comment le réparer.

Au lieu de cela, l'utilisation de la fonction agrégée vous donne la possibilité d'ignorer ces erreurs et de renvoyer la moyenne des données restantes. Pour ce faire, dans la cellule C2, vous devez taper:

=AGGREGATE(1,6,Player_Goals(Games per goal))

où

1 (argument un) représente la fonction moyenne,
6 (argument b) dit à Excel d'ignorer les erreurs, et
Player_goals (matchs par but) est la référence.

Une autre façon d'atteindre le même résultat serait d'utiliser la fonction IFERROR dans la colonne D pour remplacer toutes les erreurs par une valeur vierge.

Exemple 2: Utilisation de l'agrégat pour ignorer les lignes cachées (référence)

En utilisant la même feuille de calcul, votre prochaine cible est de calculer le nombre total de buts que l'équipe a marqués.

Une façon d'afficher les totaux consiste à vérifier la « ligne totale » dans l'onglet de conception de la table sur le ruban, qui place les totaux en bas de la table. Cependant, si vous travaillez avec un grand ensemble de données, faites défiler constamment vers le bas pour voir les totaux pourraient perdre du temps. Pensez plutôt, pensez à placer les totaux en haut de la feuille de calcul à l'extérieur de la table formatée, afin qu'ils soient toujours affichés.

Plus précisément, vous souhaitez afficher deux totaux. Le premier est le total global lorsque vous combinez les buts marqués par tous les joueurs, mais le second est le total des seuls joueurs montrant dans le tableau après avoir appliqué des filtres.

Pour calculer le total global, dans la cellule C1, type:

=SUM(Player_Goals(Goals scored))

Maintenant, même après avoir appliqué un filtre à l'une des colonnes, comme l'affichage uniquement des joueurs qui ont joué à 15 jeux ou plus, la formule SUM que vous venez d'appliquer comprend toujours les lignes qui sont filtrées.

C'est là que la fonction aggreuse sauvera la journée, car elle permet à votre calcul d'ignorer les lignes qui sont filtrées. En fait, la fonction agrégée fonctionnerait également si vous vouliez ignorer les lignes que vous avez cachées en cliquant avec le bouton droit sur l'en-tête de la ligne et en cliquant sur « Masquer ».

Dans la cellule C2, type:

=AGGREGATE(9,5,Player_Goals(Goals scored))

où

9 (argument un) représente la fonction de somme,
5 (argument b) dit à Excel d'ignorer les lignes cachées, et
Player_goals (buts marqués) est la référence.

Maintenant, notez que le résultat de cette formule diffère du résultat de la formule de somme que vous avez utilisée dans la cellule C1, car elle ne considère que les lignes affichées.

Exemple 3: Utilisation de l'agrégat pour ignorer les lignes cachées (tableau)

Ensuite, disons que vous vouliez répertorier les deux plus grands goûts pour les joueurs qui ont joué 20 matchs ou moins.

Vous pouvez d'abord appliquer le filtre, puis générer votre formule, mais aux fins de cette démonstration, créons d'abord la formule.

Dans la cellule C1, type:

=AGGREGATE(14,5,Player_Goals(Goals scored),{1;2})

où

14 (argument un) représente la grande fonction,
5 (argument b) dit à Excel d'ignorer les lignes cachées,
Player_goals (buts marqués) est le tableau de valeurs, et
{1; 2} dit à Excel que vous voulez qu'il renvoie le plus grand (1) et les deuxièmes valeurs (2) sur des lignes séparées (;).

Lorsque vous appuyez sur Entrée, remarquez que le résultat est un réseau renversé couvrant les cellules C1 et C2 parce que vous avez dit à Excel de retourner le haut deux valeurs.

En rapport

Tout ce que vous devez savoir sur le déversement dans Excel

Cela ne vaut pas la peine de pleurer pour des références renversées.

Maintenant, filtrez les jeux joués en colonne pour inclure uniquement les joueurs qui ont joué 20 matchs ou moins, et voyez que le résultat de la formule agrégée que vous avez saisie plus tôt pour ignorer les lignes cachées.

Choses à noter lors de l'utilisation de la fonction agrégée

Avant d'aller de l'avant et d'utiliser la fonction agrégée dans vos propres classeurs Excel, prenez un moment pour noter les pointeurs suivants:

La fonction agrégée d'Excel fonctionne uniquement avec les plages verticales, et non les gammes horizontales. Ainsi, lorsque vous référez une plage horizontale, l'agrégat n'ignorera pas les lignes dans des colonnes cachées.
Argument c Dans la formule agrégée, ne peut pas être la même cellule ou la même gamme de cellules sur plusieurs feuilles de calcul (également appelées références 3D).
Même si la fonction agrégée est un excellent moyen de contourner les erreurs dans les calculs, n'aurez pas l'habitude d'ignorer complètement les erreurs. Ils sont là pour une raison et pourraient vous aider à résoudre les problèmes avec vos données.
La forme du tableau de la fonction agrégée n'ignorera pas les lignes cachées, les sous-totaux imbriqués ou les agrégats imbriqués si l'argument du tableau comprend un calcul.

Une autre façon de masquer les lignes dans les tables Excel afin que la fonction agrégée comprenne uniquement ce qui montre est en insérant des tranchers, des boutons interactifs que vous pouvez cliquer pour rendre le filtrage beaucoup plus simple.