Vous découvrirez dans ce tuto à certaines fonctions (AGREGAT / MAX.SI.ENS) et formules matricielles
Si vous faites des analyses sur des tableaux, vous avez probablement déjà utilisé les fonctions de type XXX.SI.ENS (NB.SI.ENS et SOMME.SI.ENS en particulier)
Vous avez très éventuellement été confronté à la problématique de trouver une valeur maximale (ou minimale – qui peut le plus peut le moins) en fonction de plusieurs critères.
Depuis la version Excel 2016 (ou sous Office 365), sont apparues les tant attendues fonctions MAX.SI.ENS et MIN.SI.ENS.
Mais ne vous inquiétez pas ! Cet article va vous montrer que l’on passer outre ces fonctions.
MAX.SI.ENS
Cette fonction est organisée de la même manière que la fonction NB.SI.ENS :
- plage_max: plage de valeurs dont on souhaite obtenir la valeur maximale
- plage_critères1: plage de valeurs qui comporte un critère souhaité
- critères1: la valeur qui définit le « filtre » de la première plage
Et il est possible d’ajouter plusieurs couples plage_critères / critères (jusqu’à 126).
Nous obtenons donc pour cet exemple :
=MAX.SI.ENS(Tableau1[Montant];Tableau1[Responsable];F3;Tableau1[Date]; »>= »&F4;Tableau1[Date]; »<= »&F5)
Fonction AGREGAT
Cette fonction est apparue sous Excel 2010. Elle comporte 4 paramètres :
- no_fonction: Définie le numéro de la fonction souhaitée
- 1 MOYENNE
- 2 NB
- 3 NBVAL
- 4 MAX
- 5 MIN
- 6 PRODUIT
- 7 ECARTYPE.STANDARD
- 8 ECARTYPE.PEARSON
- 9 SOMME
- 10 VAR S
- 11 VAR P.N
- 12 MEDIANE
- 13 MODE.SIMPLE
- 14 GRANDE.VALEUR
- 15 PETITE.VALEUR
- 16 CENTILE.INCLURE
- 17 QUARTILE.INCLURE
- 18 CENTILE.EXCLURE
- 19 QUARTILE.EXCLURE
- option: Valeur numérique qui détermine les valeurs à ignorer dans la plage d’évaluation de la fonction
- 0 Ignore les fonctions SOUS.TOTAL et AGREGAT imbriquées
- 1 Ignore les lignes masquées, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées
- 2 Ignore les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées
- 3 Ignore les lignes masquées, les valeurs d’erreur, ainsi que les fonctions SOUS.TOTAL et AGREGAT imbriquées
- 4 N’ignore rien
- 5 Ignore les lignes masquées
- 6 Ignore les valeurs d’erreur
- 7 Ignore les lignes masquées et les valeurs d’erreur
- matrice: Plage de données
- [k] :indique la position de la matrice (kième plus grande valeur, kième centile ou kième quartile)
On obtient donc :
=AGREGAT(14;3;(Tableau1[Montant]*(Tableau1[Responsable]=F3)*(Tableau1[Date]>=F4)/(Tableau1[Date]<=F5));1)
Cette fonction est beaucoup plus puissante que la MAX.SI.ENS car elle permet d’omettre certaines valeurs comme par exemple les sous-totaux, les autres fonctions AGREGAT et/ou les valeurs erronées. De plus, cette fonction va pouvoir fonctionner comme une fonction SOUS.TOTAL en calculant les données en fonction d’un filtre d’une table !
Fonction matricielle
Pour les plus anciens d’entre nous (ceux qui ont connus Excel 2003 ou antérieur), ces fonctions n’étaient disponibles. Il fallait donc écrire des fonctions dites « matricielle ». Au lieu de faire des fonctions SI imbriquées sur chaque ligne, vous pouvez faire des conditions sur une plage de données complète.
En revanche, ces fonctions ne sont pas toujours très intuitives. Et vous devez valider la formule en effectuant un Ctrl+Alt+Entrée.
Cela donne dans notre exemple :
=MAX(Tableau1[Montant]*(Tableau1[Responsable]=F3)*(Tableau1[Date]>=F4)*(Tableau1[Date]<=F5))
Conclusion
Il est donc très souvent possible de répondre à une problématique de différentes manières.
La fonction AGREGAT est assez puissante car elle permet d’effectuer des calculs de type XXX.SI.ENS en évitant les formules matricielles et en combinant la puissances des fonctions SOUS.TOTAL.
Les Experts Excel vous proposent des formations, du développement, de l’expertise et de l’assistance sur Microsoft Excel. N’hésitez plus et contactez-les dès maintenant !