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:
- Syntaxe de référence
= AGGREGATE (no_fonction, options, ref1, ref2, ref (3),…)
- 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 les valeurs à ignorer 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. Les arguments de repos sont facultatifs.
Array: est un tableau de valeurs sur lesquelles nous voulons opérer; il est utilisé dans la syntaxe de tableau de la fonction AGGREGATE dans Excel.
K: est un argument optionnel et une valeur numérique; il est utilisé lorsque la fonction telle que GRAND, PETIT, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC dans Excel est utilisée.
Exemples
Exemple 1
Supposons que nous ayons une liste de nombres, et nous allons calculer la moyenne, le nombre qui est le nombre de cellules qui contiennent une valeur, le nombre de cellules qui ne sont pas vides, maximum, minimum, produit et 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 1
Dans la colonne C, toutes les valeurs sont données, et nous n'aurons à ignorer aucune valeur afin de 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 facultatif 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 à omettez la valeur de k.
Ainsi, la valeur moyenne est
De même, pour la plage D1: D8, encore une fois, nous sélectionnerons 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. Néanmoins, lorsqu'une option appropriée est utilisée, AGGREGATE dans Excel donne la moyenne des valeurs restantes en négligeant la valeur d'erreur dans E6.
Pour 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, AGGREGATE 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 donnera toujours la somme totale
Nous voulons que lorsque nous filtrons le canal, nous obtenions la somme des valeurs visibles, donc au lieu d'utiliser la fonction SOMME, nous utiliserons la fonction AGGREGATE pour obtenir la somme des valeurs visibles lorsqu'un filtre est appliqué.
Donc, en remplaçant la formule SOMME 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 appliquons le filtre pour différents canaux, les revenus de ce canal s'affichent uniquement lorsque le reste des lignes est 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:
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 remplacer différentes fonctions pour différentes conditions sans utiliser la formule conditionnelle.
Supposons que pour la même table, certaines de nos valeurs de revenus contiennent une erreur, nous devons maintenant 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. 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. Pourtant, 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 ignorer les valeurs masquées également.
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 dont il faut se rappeler
- La fonction AGGREGATE ne reconnaît pas la valeur de la fonction _ num supérieure à 19 ou inférieure à 1. De même, pour le numéro d'option, elle n'identifie pas les valeurs supérieures à 7 et inférieures à 1; si nous fournissons d'autres valeurs, cela donne une #VALUE! Erreur
- Il accepte toujours la valeur numérique et renvoie toujours une valeur numérique en sortie
- L'AGRÉGAT dans Excel a une limitation; il ignore seulement les lignes cachées mais n'ignore pas les colonnes cachées.
