Tutoriel Excel : comment utiliser la fonction AGGREGATE ?

Cours et tutoriel excel / cours excel gratuit / tutoriel excel gratuit

Bienvenue dans ce cours Excel qui vous montre comment utiliser la fonction AGGREGATE

A quoi sert la fonction AGGREGATE ?

AGGREGATE applique plusieurs fonctions à une liste avec la possibilité d’ignorer les lignes cachées et les valeurs d’erreur

La fonction AGGREGATE appartient à la catégorie des fonctions trigonométriques et mathématiques et est disponible à partir de la version 2010 d’Excel.
La fonction AGGREGATE vous permet d’appliquer une série de 19 fonctions à une liste ou à une base de données en considérant une série d’options pour ignorer les lignes cachées et les codes d’erreur. La Fonction AGGREGATE vous permet donc de prendre en charge l’application de certaines Fonctions qui, sous certaines conditions, ne pourraient pas être appliquées.
La Fonction AGGREGATE, à toutes fins pratiques, peut être considérée comme une évolution de la Fonction SOUS-TOTAL par rapport à laquelle le nombre de Fonctions et d’options applicables est étendu. D’autre part, les deux fonctions partagent certains codes de réglage d’option.

La fonction AGGREGATE

Syntaxe de la fonction.
La fonction AGGREGATE est disponible dans deux syntaxes différentes appelées respectivement Référence et Matrice.
Reference = AGGREGATE (num_fonction, options, ref1, [ref2], …)
Array = AGGREGATE (num_fonction ; options, matrice [k])

Dans la syntaxe de référence, les arguments de la fonction représentent :
Num_function : argument obligatoire, il est représenté par un nombre compris entre 1 et 19 qui précise la Fonction à utiliser. (1 = MOYENNE; 2 = COMPTER LES VALEURS; 3 = COMPTER LES VALEURS … etc.)
L’image de gauche liste les 19 Fonctions associées à des valeurs numériques comprises entre 1 et 19.

Options : argument obligatoire, c’est un nombre compris entre 1 et 7 qui indique les valeurs à ignorer ou considérées comme présentes dans l’intervalle d’évaluation de la Fonction (la signification des codes est illustrée dans le tableau ci-dessous).
Réf1 argument obligatoire. Représente le premier argument numérique (valeur ; référence à une seule cellule ou plage de cellules).
Ref2, … argument optionnel. 2 à 253 arguments numériques dont on souhaite la valeur agrégée.

Dans la syntaxe Matrice, la zone de la feuille qui contient la Matrice sur laquelle vous voulez la valeur agrégée doit être définie

Les principales options de la fonction AGGREGATE

Comme indiqué dans la description de la syntaxe, l’option est un argument obligatoire et fondamental de la Fonction AGGREGATE et elle exprime ce qu’il faut ignorer ou considérer dans la matrice ou dans la référence de zone à agréger à l’une des 19 Fonctions.
Par exemple, le code 1 (valable à la fois pour la fonction SOUS-TOTAL et pour AGGREGATE) permet à la Fonction d’ignorer les lignes masquées, ou dans le cas du code 2 d’ignorer les éventuelles valeurs d’erreur présentes dans les cellules prévues dans la plage d’une Fonction .
La fonction AGGREGATE, comme mentionné précédemment, est une évolution de la fonction SUBTOTAL, à tel point que les deux partagent le même réglage de certains codes.
Pour mieux illustrer la fonction AGGREGATE, quelques exemples simples sont présentés ci-dessous.

Appliquer la fonction AGGREGATE d’Excel pour ignorer les lignes masquées dans une somme

L’image ci-dessous montre une feuille de calcul Excel avec un tableau dont les lignes 7 à 15 ont été masquées.
En appliquant une fonction de sommation, les cellules présentes dans la plage de lignes cachées sont toujours considérées par la fonction.
(La fonction SOMME est saisie dans la cellule D23 du tableau).
Pour empêcher Excel de prendre en compte le contenu des cellules masquées, vous pouvez utiliser la fonction AGGREGATE et définir le code d’option 5 (ignorer les lignes masquées).
La syntaxe suivante a été saisie dans la cellule D27 : = AGREGAT (9 ; 5 ; D2 : D21)
Dans lequel le code 9 a été inséré comme Function_Num, ce qui équivaut à SUM ; en option la valeur 5 qui indique de ne pas tenir compte des lignes cachées et enfin quelle référence la zone de D2 à D21 a été affectée.

:: Pour masquer un groupe de lignes, après les avoir sélectionnées, dans le groupe de boutons Cellules de l’onglet Accueil, cliquez sur Format. Choisissez l’option Masquer et afficher, puis Masquer les lignes. Une fois les lignes sélectionnées, il est également possible de faire un clic droit sur la zone sélectionnée et d’accéder directement à l’option Masquer.

Ignorer les codes d’erreur avec la fonction AGGREGATE

L’image ci-contre montre un tableau dans lequel, dans la colonne E, en tant que résultat final d’une fonction de recherche, des montants sont calculés.
L Le fait de ne pas entrer un code produit génère le message d’erreur de données non disponibles par la fonction de recherche verticale # N/A
La fonction SOMME n’accepte pas qu’il y ait des codes d’erreur dans la plage de cellules à ajouter. Du fait de la présence du code #N/A, la fonction SOMME insérée dans la cellule E11 renvoie également le même code d’erreur.

Pour surmonter cette limitation de la fonction SUM, il est possible d’utiliser AGGREGATE. Dans la cellule E13, la fonction AGGREGATE a été insérée avec la syntaxe suivante : = AGGREGATE (9 ; 6 ; E2 : E9)
Dans lequel le code 9 a été inséré comme Function_Num, qui équivaut à SUM et en option, la valeur 6 qui indique d’ignorer les messages d’erreur. Enfin, la zone de E2 à E9 a été indiquée comme référence de calcul.