Découvrir la fonction AGREGAT d’Excel pour effectuer des calculs en tenant compte des filtres en place sur Excel

Aujourd’hui, nous allons découvrir une fonction très puissante d’Excel : la fonction AGREGAT(). Celle-ci permet d’effectuer des calculs, en utilisant des fonctions classiques (SOMME(), MOYENNE(), NB()…) en tenant compte des filtres effectifs sur un tableau, ou encore en définissant des conditions matricielles complexes.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Qu’est-ce que la fonction AGREGAT ?

La fonction AGREGAT() d’Excel permet comme son nom l’indique d’agréger une fonction à partir d’une base de données ou d’une matrice, ce qui permettra donc de réaliser des calculs assez complexes, sans avoir besoin d’utiliser les calculs matriciels tant redoutés.

Il s’agit d’une fonction assez récente, qui a été introduite avec Excel 2010.

Si celle-ci reste méconnue mais peut cependant se révéler très pratique, comme nous allons pouvoir nous en rendre compte dans cette présentation.

2. Les arguments

Il existe deux formes possibles d’utilisation de la fonction AGREGAT(), dont le choix dépendra de ce que nous allons souhaiter effectuer :

• La forme référentielle tout d’abord est la forme la plus simple qui va nous permettre de réaliser des calculs simples (moyenne, somme, dénombrement…)
• La forme matricielle ensuite permet de réaliser des opérations bien plus complexe en jouant sur les calculs de matrices

Dans les faits, le fonctionnement des deux méthodes est assez similaire

Voici comment utiliser cette forme matricielle :

AGREGAT(no_fonction; options; matrice; [k]) 

Où :

• No_fonction : est l’argument qui permet de spécifier le type de traitement que nous souhaitons effectuer (calcul de moyenne, de dénombrement … nous verrons juste après comment choisir la valeur de cet argument),
• Options : permets de spécifier si nous souhaitons exclure certaines données du calcul (nous verrons également le fonctionnement de cet argument dans la suite de ce tutoriel)
• Matrice : il s’agit ici de spécifier une matrice que nous souhaitons utiliser dans le cadre de l’utilisation de la fonction AGREGAT.
• K : il s’agit ici d’un argument facultatif utilisé par certaines fonctions de la fonction AGREGAT pour définir le type d’information à extraire (par exemple le rang de la donnée à extraire : premier, deuxième…)

Maintenant que nous avons vu dans les grandes lignes le fonctionnement global de la fonction AGREGAT, je vous propose de commencer à voir quelques exemples très simples d’utilisation de cette fonction.

Pour cela, nous allons pouvoir utiliser la base de données suivante :

Nous y retrouvons la liste du personnel d’une entreprise avec les informations classiques (Prénom, nom, sexe, établissement de rattachement, date de naissance, revenu annuel).

Ensuite, nous allons souhaiter extraire un certain nombre d’informations statistiques :

Il s’agit ici de statistiques classiques et généralistes, nous allons pouvoir utiliser la forme référentielle de la fonction.

Tout d’abord nous allons souhaiter connaître le nombre de personnes au sein de l’entreprise.

Nous pourrions bien entendu utiliser la fonction dédiée NBVAL() qui permettrait d’obtenir la même information :

=NBVAL(basePersonnel[Nom])

Mais nous verrons un peu plus tard que la fonction AGREGAT() que nous découvrons ici a tout de même quelques avantages, comme nous allons le voir (notamment la possibilité de choisir des options).

Nous revenons alors sur la cellule I9, et nous appelons la fonction AGREGAT() pour en découvrir les arguments :

À ce moment-là, Excel nous propose une liste d’arguments à utiliser pour renseigner l’argument nb_fonction.

En fonction du numéro que nous allons choisir, le résultat retourné par la fonction AGREGAT() sera différent.

Voici l’ensemble des fonctions proposées par la fonction :

Comme nous pouvons le constater ici, c’est en fonction de la valeur que nous allons attribuer à cet argument, que le type de la fonction AGREGAT() sera déterminé.

De 1 à 13 la fonction sera de type référentielle, et de 14 à 19, celle-ci sera matricielle.

Ici, nous allons nous intéresser à la fonction NBVAL(), nous allons donner la valeur « 3 » à l’argument no_function :

Ensuite, Excel nous demande quelles options nous souhaitons mettre en place.

Ces options seront particulièrement utiles lorsque nous traiterons des données issues d’un tableau dans lequel se trouvent des sous totaux, ou encore lorsque nous souhaitons afficher des résultats en fonctions de filtres mis en place dans un tableau.

Nous pourrons également choisir d’écarter des lignes lorsque celles-ci sont des erreurs, comme nous le verrons plus tard dans ce tutoriel.

Ici, pour simplifier, nous n’ignorons aucune donnée, donc nous choisissons l’option « 4 » :

La fonction MOYENNE étant référentielle, il suffira simplement de venir saisir la plage des cellules que nous souhaitons dénombrer en tant que troisième argument :

=AGREGAT(3;4;basePersonnel[Nom])

Ici le résultat est exactement identique à celui que nous avions juste avant (87 personnes), mais la force de la fonction AGREGAT réside dans le fait que nous pouvons simplement choisir les valeurs à exclure de l’analyse.

Pour cela, revenons modifier le second argument pour choisir d’ignorer les lignes masquées (valeur 5) :

=AGREGAT(3;5;basePersonnel[Nom])

Ensuite, nous pouvons utiliser les fonctionnalités de filtrage du tableau pour n’afficher que les femmes :

À ce moment-là, le résultat va être modifier pour tenir compte du filtre mis en place !

Nous pouvons ensuite calculer les autres éléments, en modifiant uniquement le premier paramètre ainsi que la référence à la colonne (nous nous intéressons maintenant aux revenus) de chacune des lignes du tableau

=AGREGAT(1;5;basePersonnel[Revenu]) 
=AGREGAT(5;5;basePersonnel[Revenu]) 
=AGREGAT(4;5;basePersonnel[Revenu])

3. La forme matricielle de la formule AGREGAT

Maintenant que nous avons comment fonctionne la fonction AGREGAT() dans sa forme la plus simple, voyons comment procéder lorsque nous souhaiterons appeler des fonctions un peu plus complexes, et surtout avec des filtres.

Tout d’abord, nous allons souhaiter calculer le montant de salaire le plus élevé par établissement.

1tant donné que nous souhaitons mettre en place un filtre, en fonction de l’établissement, nous ne pourrons pas utiliser la fonction MAX() comme vue précédemment, mais la fonction GRANDE.VALEUR() :

Ensuite, nous choisirons d’ignorer les valeur filtrées (pour n’avoir que les données concernant les femmes) :

Et c’est à partir de maintenant que les choses vont se complexifier légèrement étant donné que nous allons souhaiter mettre en place un filtre sur les données.

Si cela n’était pas le cas, il suffirait ici de simplement venir sélectionner la colonne des revenus :

Avant de valider la fonction, nous allons devoir préciser l’argument k, qui correspond au paramètre à utiliser dans la fonction GRANDE.VALEUR(), à savoir ici le rang que nous souhaitons obtenir.

Pour avoir la plus grande des valeurs, nous saisissons simplement la valeur 1 (première valeur la plus grande) :

=AGREGAT(14;5;basePersonnel[Revenu];1)

Ensuite, nous pouvons valider et Excel va nous retourner le salaire le plus élevé des femmes, soit 97 898€ :

Maintenant, comme nous l’avons vu, nous allons souhaiter obtenir non pas le salaire le plus élevé parmi toutes les femmes, mais uniquement pour celles qui relèvent de l’établissement de Bordeaux !

Pour cela, nous allons devoir modifier la matrice utilisée pour y insérer un filtre de données.

Les règles à utiliser sont alors les mêmes que celles que nous avons déjà pu découvrir dans le cadre de la découverte de la fonction SOMMEPROD()

La notion la plus importantes que nous avions alors découvertes, c’est que pour combiner des matrices entre elles, nous les multiplions.

À partir de là nous allons pouvoir ajouter à la matrice précédente une matrice de filtre :

 =AGREGAT(14;5;basePersonnel[Revenu]*(basePersonnel[Établissement]=K9);1)

Étant donné que pour récupérer le nom de l’établissement nous avons utilisé une référence à la cellule K9, nous pouvons maintenant étendre la formule en utilisant simplement la poignée de recopie vers le bas :

Ensuite, sur la colonne située juste à droite, nous allons souhaiter calculer le revenu minimum de chaque établissement.

Pour cela, nous allons copier la formule, et modifier le premier argument :

Ici, nous choisissons la valeur 15 pour appeler la fonction PETITE.VALEUR().

Par contre, lorsque nous validons, la valeur retournée est de 0 !

Pour en comprendre la raison, nous allons utiliser l’outil d’évaluation de formule (Formule > Évaluer la formule) :

Nous allons ensuite appuyer sur le bouton Évaluer pour qu’Excel nous présente les différentes étapes de résolution de la formule :

• Excel commence par récupérer les valeurs de la matrice basePersonnel[Revenu] :

• Ensuite, il procède de la même manière pour récupérer les noms des établissements :

• Ensuite, il va comparer chacun de ces établissements avec le terme « Bordeaux » :

• Lorsque l’établissement correspond effectivement à Bordeaux, Excel insère des VRAI dans la matrice, et des FAUX dans le cas contraire :

• Ensuite, Excel combine les deux matrices en multipliant chaque terme les uns entre eux : un nombre multiplié par VRAI permet de conserver ce nombre, tandis que celui-ci sera remplacé par zéro lorsque le terme de la matrice de test sera FAUX :

• Pour finir, et c’est là que réside le problème, Excel récupère la valeur minimale de cette nouvelle matrice, il s’agit donc de la valeur zéro…

Pour contourner ce problème, nous devrons donc trouver un moyen d’ignorer les zéros.

Or rappelez-vous, dans la liste des données à ignorer, il n’est nulle part fait mention d’ignorer les zéros :

En revanche, il est possible d’ignorer les erreurs !

Et c’est justement ce qui va nous intéresser ici.

En effet, comme vous le savez, une des règles fondamentales en mathématiques, est qu’il est impossible de diviser un nombre par zéro. C’est pourquoi, lorsque tel est le cas, Excel nous renvoie systématiquement une erreur #DIV/0 :

Il faut savoir que dans un calcul, Excel considère la valeur Booléenne comme un zéro, donc diviser 1 par FAUX aura le même effet :

=1/FAUX

En revanche, un VRAI sera traité comme un 1, donc diviser 1 par 1 donne toujours 1 comme résultat :

Ce qui nous permet donc de résoudre notre calcul :

4. Établir un TOP 5 ou un FLOP 5

Maintenant que nous savons comment récupérer les valeurs minimales et maximales d’une base de données, il va être simple de déterminer les 5 plus gros revenus de l’entreprise :

=AGREGAT(14;5;basePersonnel[Revenu];O9)

Ici, nous utilisons la fonction GRANDE.VALEUR() (« 14 »):

• En excluant les valeurs filtrées,
• En utilisant la plage des cellules dans laquelle se trouve les revenus en tant que matrice,
• Et en utilisant la cellule située sur la gauche, dans laquelle se trouve la position de la ligne en cours dans le classement en tant que paramètre k

Pour calculer les salaires les plus bas, nous procédons de la même manière, mais en appelant cette fois-ci la fonction PETITE.VALEUR() (« 15 ») :

Une fois ces montants mis en évidence, nous pouvons récupérer les noms correspondant en utilisant la fonction INDEX-EQUIV que connaissons bien :

 =INDEX(basePersonnel[Nom];EQUIV(P9;basePersonnel[Revenu];))