Comment utiliser la fonction agrégée d'Excel pour affiner les calculs

Liens rapides

• La syntaxe agrégée

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

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

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

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

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.

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:

 = Agrégat ( <em>a</em> , <em>b</em> , <em>c</em> , <em>d</em> )

où

• A (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 plages.

La syntaxe du formulaire de tableau

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

 = Agrégat ( <em>a</em> , <em>b</em> , <em>c</em> , <em>d</em> )

où

• A (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 a et b )

Lors de la saisie des arguments A et B dans l'un ou l'autre formulaire 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 de l'agrégat (argument A ). 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	La n ème plus grande valeur
15	PETIT	La plus petite valeur
16	Centile.	Le n ème centile, avec les premières et dernières valeurs incluses
17	Quartile.inc	Le n ème quartile, avec les premières et dernières valeurs incluses
18	Centile.exc	Le n e centile, avec les premières et dernières valeurs exclues
19	Quartile.exc	Le n ème 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:

 = Moyen (joueur_goals [matchs par but])

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.

Publications

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:

 = Agrégat (1,6, joueur_goals [matchs par but])

où

• 1 (l'argument a ) représente la fonction moyenne,
• 6 (l'argument b ) dit à Excel d'ignorer les erreurs, et
• Player_Goals [Jeux par but] est la référence.

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.

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:

 = Agrégat (14,5, joueur_goals [buts marqués], {1; 2})

où

• 14 (l'argument a ) représente la grande fonction,
• 5 (l'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 les valeurs les plus importantes ( 1 ) et les plus grandes ( 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 car vous avez dit à Excel de renvoyer les deux premières 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.

Publications

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.
• L'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.

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!