La fonction AGREGAT ou le Robot-multifonction d’Excel

Difficulté: ToqueToqueIntermédiaire

Ustensiles: Excel

Ingrédients: AGREGAT, SOMME, MOYENNE, PETITE.VALEUR

Bases requises: Fonctions matricielles


AGREGAT

Cette fonction est assez incroyable et peu connue. Elle permet de remplacer 19 fonctions existantes avec en plus des options complémentaires.

Il existe deux formes différentes pour cette fonction:

  • La forme matricielle
  • La forme Référence

Agregat 01

Voici les 19 fonctions

Agregat 02

 

Les Options

Comme vous le voyez dans la première image, après avoir choisi le numéro de fonction (correspondant à la formule choisie), vous devez choisir une option parmi les 8 proposées:

Agregat 03

Les options possibles apportent  une vraie plus-value aux fonctions connues comme SOMME, MOYENNE, GRANDE.VALEUR. Elles permettent de définir comment on agit sur les références ou matrices. Par exemple, avec l’option 5, vous allez ignorer les cellules masquées (Avec le bouton droit, masquer ou en filtrant) ou les cellules contenant des erreurs.

Nous verrons des exemples concrets en montrant les différences entre les fonctions classique et AGREGAT.

 

AGREGAT permet gérer les tableaux

Pour la forme matricielle, vous pouvez réaliser des opérations sans avoir recours à la particularité des fonctions matricielles qui consiste à valider la formule avec CTRL + SHIFT + ENTREE.

 

La Forme Référence

La syntaxe est la suivante:

=AGREGAT ( no_fonction ; options ; réf1 ; …)

Pour bien comprendre, nous allons réaliser quelques cas permettant de bien comprendre les différences entre les fonctions classiques (SOMME) et la fonction AGREGAT.

 

Prenons par exemple la fonction SOMME.

Dans notre cas, nous utiliserons un tableau Excel que j’ai nommé T_CA.

Agregat 04

Nous pouvons voir ici que le résultat (94972) est le même quel que soit la fonction.

Mais si à présent, nous filtrons notre tableau sur la Région ‘R2A’, vous pouvez voir que le résultat est différent car la fonction SOMME classique fait la somme de toutes les valeurs du tableau tandis qu’AGREGAT ne prend que les valeurs “visibles”.

Agregat 05

Notez que nous avons filtré mais nous aurions pu masquer directement les lignes. Le résultat de la fonction AGREGAT aurait été le même.

 

Option Ignorer erreurs

Dans un autre exemple, nous souhaitons connaître le prix unitaire, en divisant le Chiffre d’Affaire par la quantité vendue.

Si nous n’avons pas fait de vente, nous aurons donc une division par zéro qui remontera donc une erreur.

Agregat 06

Avec la fonction classique MOYENNE, le résultat remonte une erreur tandis qu’avec AGREGAT et l’option 6 (ignorer les erreurs), nous calculons la moyenne sur les cellules contenant une valeur.

 

La Forme Matricielle

Ce coup-ci, la syntaxe est la suivante:

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

 

Pour notre exemple, nous allons utiliser la fonction PETITE.VALEUR

Reprenons le même tableau que précédemment afin de trouver le Chiffre d’Affaire le plus petit.

Pour rappel, la fonction PETITE.VALEUR a la syntaxe suivante:

=PETITE.VALEUR ( matrice ; k )

Avec matrice contenant la liste des valeurs et k le coefficient permettant de définir si nous souhaitons la plus petite valeur (1) ou la seconde plus petite (2) , etc…

Agregat 07

Nous voyons ici que nous avons le même résultat quel que soit la fonction. En revanche, tout comme tout à l’heure, nous pouvons filtrer sur la région R2A. Le résultat diffère car la fonction classique prend toutes les valeurs, même celles non visibles.

Agregat 08

 

Application de l’option 6

Nous pouvons imaginer alors toutes sortes d’application de cette fonction très puissante.

Nous souhaitons trouver le valeur minimale de la région R2A. Avec les fonctions classiques, il faudrait utiliser une fonction matricielle de la fonction MIN.

Agregat 09

Voyons de plus près la fonction AGREGAT afin de bien comprendre comment elle fonctionne.

 

=AGREGAT( 15 ; 6 ; T_CA[CA] / (T_CA[Région] = “R2A”) ; 1)

 

L’argument “Matrice” prend toutes les valeurs de la colonne Chiffre d’affaire du tableau (T_CA[CA]) et utilise une logique booléenne (T_CA[Région] = “R2A”), VRAI ou FAUX, pour tester la région “R2A”.

Quand cette dernière est évaluée, elle retourne une série de valeurs VRAI ou FAUX comme ceci:

 

=AGREGAT( 15 ; 6 ; T_CA[CA] / {VRAI;FAUX;FAUX;VRAI;FAUX;VRAI;FAUX;FAUX;VRAI;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;FAUX;FAUX }; 1)

 

Pour bien comprendre le fonctionnement, vous pouvez utiliser “Evaluer la formule” comme ci-dessous.

Agregat 10

Agregat 11

 

Dans Excel, quand nous utilisons une opération mathématique (Division, Multiplication, …) avec une valeur booléenne (VRAI ou FAUX), les valeurs booléennes sont converties en leur équivalent numérique:

  • VRAI: 1
  • FAUX: 0

De ce fait, lorsque les valeurs de la  matrice  T_CA[CA]  sont divisés soit par 1 (si VRAI) soit par 0 (FAUX); nous avons ceci:

=AGREGAT( 15 ; 6 ; {7383,49;…;687,75} / {1;0;0;1;0;1;0;0;1;0;0;0;0;1;1;1;0;0}; 1)

 

Lorsqu’ensuite nous divisons par 0, nous avons une erreur. Nous pouvons voir des #DIV/0! Partout où nous avions la valeur 0:

Agregat 12

Et puisque nous avons choisi l’option 6 (ignorer les erreurs), la formule se simplifie:

=AGREGAT( 15 ; 6 ; {7 383,49 ; 8 044,54 ; 7 382,76 ; 8 432,78 ; 1 063,58 ; 1 000,00 ; 8 847,26 }; 1)

Et donc la valeur la plus petite est 1000,00 !

 

Remarques

  1. Attention, les fonctions classiques ne doivent pas disparaître car il est souvent plus aisé de lire une fonction classique qu’une fonction AGREGAT. Cette dernière devra être utilisée lorsque nous souhaiterons utiliser les options que nous avons vues dans cet article.
  2. Les options d’AGREGAT ne fonctionnent que verticalement. Les opérations seront réalisables horizontalement mais sans tenir compte des options.
  3. Nous avons vu la semaine dernière la fonction SOUS.TOTAL qui ressemble beaucoup à la fonction AGREGAT. Toutefois cette dernière est  plus puissante du fait d’options supplémentaires, sans compter le nombre de fonction qu’elle peut remplacer.
  4. AGREGAT n’existe qu’à partir d’EXCEL 2010.
  5. Souvent pour montrer les formules, j’utilise la fonction FORMULETEXTE. Celle-ci est apparue avec EXCEL 2013.

 

Cet article a été fortement inspiré de l’article du blog www.myonlinetraininghub.com

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *