Difficulté: 
Intermé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
Voici les 19 fonctions
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:
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.
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 ».
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.
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…
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.
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.
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.
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:
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
- 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.
- Les options d’AGREGAT ne fonctionnent que verticalement. Les opérations seront réalisables horizontalement mais sans tenir compte des options.
- 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.
- AGREGAT n’existe qu’à partir d’EXCEL 2010.
- 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