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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

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 :

Excel formation - La fonction AGREGAT - 01

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 :

Excel formation - La fonction AGREGAT - 02

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])

Excel formation - La fonction AGREGAT - 03

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 :

Excel formation - La fonction AGREGAT - 04

À 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 :

Valeur

Fonction équivalente

Type

1

MOYENNE()

Référentielle

2

NB()

Référentielle

3

NBVAL()

Référentielle

4

MAX()

Référentielle

5

MIN()

Référentielle

6

PRODUIT()

Référentielle

7

ECARTYPE.STANDARD()

Référentielle

8

ECARTYPE.PEARSON()

Référentielle

9

SOMME()

Référentielle

10

VAR.S()

Référentielle

11

VAR.P.N()

Référentielle

12

MEDIANE()

Référentielle

13

MODE.SIMPLE()

Référentielle

14

GRANDE.VALEUR()

Matricielle

15

PETITE.VALEUR()

Matricielle

16

CENTILE.INCLURE()

Matricielle

17

QUARTILE.INCLURE()

Matricielle

18

CENTILE.EXCLURE()

Matricielle

19

QUARTILE.EXCLURE()

Matricielle

 

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 :

Excel formation - La fonction AGREGAT - 05

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 » :

Excel formation - La fonction AGREGAT - 06

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 :

Excel formation - La fonction AGREGAT - 07

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

Excel formation - La fonction AGREGAT - 08

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])

Excel formation - La fonction AGREGAT - 09

 

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.

Excel formation - La fonction AGREGAT - 01

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() :

Excel formation - La fonction AGREGAT - 02

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

Excel formation - La fonction AGREGAT - 03

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 :

Excel formation - La fonction AGREGAT - 04

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€ :

Excel formation - La fonction AGREGAT - 05

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)

Excel formation - La fonction AGREGAT - 06

É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 :

Excel formation - La fonction AGREGAT - 07

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 :

Excel formation - La fonction AGREGAT - 08

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

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

Excel formation - La fonction AGREGAT - 09

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

Excel formation - La fonction AGREGAT - 10

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] :

Excel formation - La fonction AGREGAT - 11

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

Excel formation - La fonction AGREGAT - 12

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

Excel formation - La fonction AGREGAT - 13

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

Excel formation - La fonction AGREGAT - 14

  • 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 :

Excel formation - La fonction AGREGAT - 15

  • 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 :

Excel formation - La fonction AGREGAT - 16

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 :

Excel formation - La fonction AGREGAT - 17

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 :

Excel formation - La fonction AGREGAT - 18

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

Excel formation - La fonction AGREGAT - 19

 

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

Excel formation - La fonction AGREGAT - 20

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 ») :

Excel formation - La fonction AGREGAT - 21

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];))

Excel formation - La fonction AGREGAT - 22

 

 

 

 

 



Articles qui pourraient vous intéresser

Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?
Comment identifier la cellule qui se répète le plus de fois sur Excel ? (Fonction MODE)
Comment améliorer votre gestion de données avec les fonctions base de données d’Excel ?
Créer une somme intelligente et dynamique sur Excel sans VBA
Quelle fonction permet de lister le nom des feuilles de calcul Excel sans VBA ?
Comment créer un décompte entre deux dates (jours, heures, minutes et secondes) sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.