Fonction Excel AGGREGATE

Fonction AGGREGATE dans Excel

AGGREGATE La fonction dans Excel renvoie l'agrégat d'une table de données ou de listes de données données, cette fonction a également le premier argument comme numéro de fonction et les autres arguments sont pour une plage d'ensembles de données, le numéro de fonction doit être mémorisé pour savoir quelle fonction utiliser .

Syntaxe

Il existe deux syntaxes pour la formule AGGREGATE:

1. Syntaxe de référence

= AGGREGATE (no_fonction, options, ref1, ref2, ref [3],…)

2. Syntaxe du tableau

= AGGREGATE (no_fonction, options, tableau, [k])

Function_num est un nombre qui désigne une fonction spécifique que nous voulons utiliser, c'est un nombre de 1 à 19

Option: c'est aussi une valeur numérique allant de 0 à 7 et détermine quelles valeurs doivent être ignorées lors des calculs

Ref1, ref2, ref [3]:  est l'argument lors de l'utilisation de la syntaxe de référence, c'est la ou les valeurs numériques sur lesquelles on veut effectuer le calcul, au moins deux arguments sont requis.

Array: est un tableau de valeurs sur lequel on veut effectuer l'opération, il est utilisé dans la syntaxe tableau de la fonction AGGREGATE dans Excel

K: est un argument facultatif et est une valeur numérique, il est utilisé lorsque la fonction comme LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC dans Excel est utilisée.

Exemples

Vous pouvez télécharger ce modèle Excel de fonction AGGREGATE ici - Modèle Excel de fonction AGGREGATE

Exemple 1

Supposons que nous ayons une liste de nombres et que nous calculerons la moyenne, le nombre qui est le nombre de cellules contenant une valeur, le nombre de cellules qui ne sont pas vides, le maximum, le minimum, le produit et la somme des valeurs numériques données. Les valeurs sont données ci-dessous dans le tableau:

Calculons d'abord la moyenne de la ligne 9, pour toutes les valeurs données. Pour la moyenne, la fonction num est

Dans la colonne C, toutes les valeurs sont données et nous n'aurons à ignorer aucune valeur, nous allons donc sélectionner l'option 4 (ne rien ignorer)

Et en sélectionnant la plage de valeurs C1: C8 sous forme de tableau de valeurs numériques

Puisque ' k' est un argument optionnel et est utilisé lorsqu'une fonction comme LARGE, SMALL dans Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC est utilisée mais dans ce cas, nous calculons la moyenne, nous omettra la valeur de k.

Ainsi, la valeur moyenne est

De même, pour la plage D1: D8, nous sélectionnerons à nouveau l'option 4.

Pour la plage E1: E8, une cellule E6 contient une valeur d'erreur, si nous utilisons la même formule AGGREGATE, nous obtiendrons une erreur, mais lorsqu'une option appropriée est utilisée, l'AGREGATE dans Excel donne la moyenne des valeurs restantes en négligeant l'erreur valeur en E6.

Afin d'ignorer les valeurs d'erreur, nous avons l'option 6.

De même, pour la plage G1: G8, nous utiliserons l'option 6 (ignorez les valeurs d'erreur)

Maintenant, pour la plage H3, si nous mettons une valeur 64, masquons la troisième ligne et utilisons l'option 5, pour ignorer la ligne masquée, l'AGREGATE dans Excel, nous donnerons la valeur moyenne pour les valeurs numériques visibles uniquement.

Sortie sans masquer la ligne 3

Sortie après masquage de la ligne 3

En appliquant la formule AGGREGATE pour d'autres opérations, nous avons

Exemple - # 2

Supposons que nous ayons un tableau pour les revenus générés à différentes dates à partir des différents canaux comme indiqué ci-dessous

Maintenant, nous voulons vérifier les revenus générés pour différents canaux. Ainsi, lorsque nous appliquons la fonction de somme, nous obtenons le revenu total généré, mais dans le cas où nous voulons vérifier les revenus générés pour le canal organique ou direct ou tout autre, lorsque nous appliquons des filtres dans Excel pour le même, la fonction de somme sera toujours donner la somme totale

Nous voulons que lorsque nous filtrons le canal, nous obtenions la somme des valeurs qui sont visibles, donc au lieu d'utiliser la fonction SOMME, nous utiliserons la fonction AGGREGATE afin d'obtenir la somme des valeurs qui sont visibles lorsqu'un filtre est appliqué.

Donc, en remplaçant la formule SUM par une fonction AGGREGATE avec le code d'option 5 (en ignorant les lignes et les valeurs cachées) nous avons,

Désormais, lorsque nous appliquerons le filtre pour différents canaux, les revenus de ce canal seront affichés uniquement lorsque le reste des lignes sera masqué.

Revenu total généré pour le canal direct:

Revenu total généré pour le canal organique:

Revenu total généré pour le canal payant:

Ainsi, nous pouvons voir que la fonction AGGREGATE calcule les différentes valeurs de somme pour les revenus générés pour différents canaux une fois qu'ils sont filtrés. Ainsi, la fonction AGGREGATE peut être utilisée dynamiquement pour le remplacement de différentes fonctions pour différentes conditions sans utiliser la formule conditionnelle.

Supposons que pour le même canal de table et les mêmes revenus, certaines de nos valeurs de revenus contiennent une erreur, maintenant nous devons ignorer les erreurs et en même temps, si nous voulons appliquer un filtre, la fonction AGGREGATE doit également ignorer les valeurs de ligne masquées.

Lorsque nous utilisons l'option 5, nous obtenons l'erreur pour la somme des revenus totaux, maintenant pour ignorer les erreurs, nous devons utiliser l'option 6

En utilisant l'option 6, nous obtenons la somme en ignorant les valeurs d'erreur, mais lorsque nous appliquons le filtre, par exemple, filtre par valeur de canal Direct, nous obtenons la même somme en ignorant les erreurs, mais en même temps, nous devons également ignorer les valeurs cachées.

Donc, dans ce cas, nous utiliserons l'option 7 qui ignore les valeurs d'erreur et en même temps les lignes cachées

Choses à retenir

• La fonction AGGREGATE ne reconnaît pas la valeur de la fonction _ num supérieure à 19 ou inférieure à 1 et de même pour le numéro d'option, elle ne reconnaît pas les valeurs supérieures à 7 et inférieures à 1, si nous fournissons d'autres valeurs, elle donne une #VALUE ! Erreur
• Il accepte toujours la valeur numérique et renvoie toujours une valeur numérique en sortie
• L'AGGREGATE dans Excel a une limitation; il ignore seulement les lignes cachées mais n'ignore pas les colonnes cachées.