Utilisation de la fonction AGREGAT : équivalent de SOMME.SI.ENS sans formule matricielle

Bonjour,

Agregat est une fonction qui a été introduite avec Excel 2010. Elle permet d'agréger des données d'une liste ou d'un tableau avec toute une série de fonctions disponibles, et aussi la possibilité de gérer les lignes masquées et les valeurs d'erreurs.

Jusqu'à présent, je n'avais pas eu à l'utiliser, mais la nécessité de faire fonctionner un équivalent de MIN.SI.ENS (disponible seulement avec XL 2016) en voulant éviter une formule matricielle m'a forcée à l'utiliser.

L'idée générale est de faire un agrégat en utilisant la fonction Petite valeur (no_fonction = 15) tout en évitant les zéros.
On veut ici retourner la plus petite valeur comprise entre une date de début et une date de fin.

Vous pouvez télécharger le fichier ici.

La formule est donc :
=AGREGAT(15;6;(B2:B14)/(A2:A14< =E4)/(A2:A14>=E3);1)
Si nous décomposons, nous avons donc :
15 : No_Fonction qui est petite.valeur

6 : qui permet d'ignorer les valeurs d'erreur. Comme le fait de diviser par FALSE (donc =0) renvoit une valeur d'erreur, on va donc ignorer toutes les valeurs divisée par zéro, donc toutes les valeurs en dehors des dates.

Le troisième argument reprend le tableau, ici présenté sous forme matricielle, avec les valeurs du tableau, ou un #DIV/0 en cas d'erreur :

On peut améliorer la lisibilité en utilisant les coordonnées sous forme de tableau et en nommant les cellules de référence:
=AGREGAT(15;6;(TabData[Montant])/(TabData[Date]< =ParamDateFin)/(TabData[Date]>=ParamDateDebut);1)

Finalement, le dernier argument nous permet de spécifier quelle plus petite valeur on veut retourner. Ici, on choisir 1 pour la première des plus petites valeurs !
Si on avait choisi 2, on aurait retourné 75, qui est la deuxième plus petite valeur :

A bientôt.

Pour aller plus loin :
Le lien vers le site de microsoft : https://support.office.com/fr-fr/article/agregat-agregat-fonction-43b9278e-6aa7-4f17-92b6-e19993fa26df
Un autre exemple d'utilisation de la fonction Agregat pour Min.Si.Ens (en Anglais) : https://a4accounting.com.au/excel-how-to-create-a-minif-and-a-maxif/

PS : Ne manquez aucune nouvelle astuce sur Excel, abonnez-vous à notre newsletter gratuite !
Pour marque-pages : permalien.

4 réactions à Utilisation de la fonction AGREGAT : équivalent de SOMME.SI.ENS sans formule matricielle

  1. hamidi a écrit:

    Bonjour

    on peut utiliser la formule matricielle suivante
    pour trouver la seconde grande valeur
    ou la petite selon notre choix grande v ou petite v

    =GRANDE.VALEUR(SI((A2:A14>=E3)*(A2:A14<=E4);B2:B14);2)

    à vous

    cordialement

  2. Gaetan Mourmant a écrit:

    Bonjour Hamidi,
    Merci pour votre commentaire, mais comme indiqué, je voulais y arriver sans utiliser de formule matricielle :-). Les formules matricielles sont en général plus lentes, et aussi prône à la loi de Murphy – quelqu’un va forcément valider la formule en oubliant d’appuyer sur CTRL+MAJ+Entrée…
    A bientôt.
    Gaetan

  3. Christophe Vouilloux a écrit:

    Voilà une fonction que je ne connaissais pas et qui va solutionner de nombreux cas complexes ! La gestion des valeurs en erreur est toujours difficile.
    Donc grand merci

  4. Gaetan a écrit:

    Oui, je pense que pour un certain nombre d’utilisateurs, ca devrait ouvrir des perspectives. N’hésitez pas à en faire part !

Laisser un commentaire

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