Monsieur Excel
Pour tout savoir faire sur Excel !

03 juin 2014

Somme.Si.Ens() et Nb.Si.Ens()

Avec Excel 2007, Microsoft nous a donné deux nouvelles fonctions de calcul, qui enrichissent les fonctions Somme.Si() et Nb.Si() que nous vous avons présentées le 25 février 2006 et le 2 mars 2006.

Il s’agit des fonctions Somme.Si.Ens() et Nb.Si.Ens() dont l’aide ne dit pas ce que signifie le « Ens » ainsi ajouté : Ensemble ?

L’exemple ci-dessous illustre leur utilisation avec à titre de comparaison le même résultat obtenu à chaque fois avec une fonction BD…().


Les formules utilisées sont les suivantes :

Personnes de 27 ans :
I5 : =BDSOMME(A1:F26;E1;H1:H2)
I6 : =SOMME.SI.ENS(E:E;D:D;27)
I11 : =BDNB(A1:F26;E1;H1:H2)
I12 : =NB.SI.ENS(D:D;27)
Hommes de 27 ans :
I8 : =BDSOMME(A1:F26;E1;H1:I2)
I9 : =SOMME.SI.ENS(E:E;D:D;27;A:A;"m")
I14 : =BDNB(A1:F26;E1;H1:I2)
I15 : =NB.SI.ENS(D:D;27;A:A;"m")

Remarque 1 – En fait, ces nouvelles fonctions ne font rien que l’on ne sache déjà faire avec une fonction BD…() mais elles évitent la création d’une zone de critères comme H1:H2 ou H1:I2, selon le cas. Ceci dit, cette simplification a un inconvénient car elle aboutit à une moindre lisibilité du modèle : il faut en effet voir la formule pour comprendre d’où vient le résultat.

Remarque 2 – Faites très attention, car le champ sur lequel on fait la somme est en première position dans Somme.Si.Ens() alors qu’il était en troisième position dans Somme.Si(). Microsoft a été obligé de changer sa place car il n’y a qu’un champ de sommation alors qu’il peut y avoir plusieurs doublons « colonne;valeur ».

Remarque 3 – Cet article a déjà été publié le 27 février 2009. Je le reproduis ici pour deux raisons : d'une part, j'ai constaté chez mes clients que ces deux fonctions sont encore peu connues à ce jour. D'autre part, elles vont nous servir dans le prochain article.

2 Commentaire(s):

  • Bonjour,

    Si je lis bien vos formules, je dirais que les fonctions ens ont un autre avantage par rapport aux fonction BD... : celui de faire référence dans la formule aux colonnes directement plutôt qu'au tableau et de permettre ainsi la saisie de nouvelles données sans avoir besoin de créer une zone de nom dynamique.

    Cordialement,
    Joseph

    By Blogger Joseph Neron, sur 9:37 AM  

  • Bonjour,
    J'ai mis en pratique la fonction somme.si.ens avec 2 critères dont l'un est "cellule vide" (= je fais référence dans la formule à une cellule vide comme critère 2).
    La formule me renvoie dans tous les cas la valeur 0.
    Y a t il un moyen de solutionner ce problème ? (en solution de contournement, je mets un X dans les cellules vides de ma colonne et mets X comme critère 2).
    Merci pour votre aide et bravo pour votre blog dont je suis un lecteur assidu.
    Fabien

    By Anonymous Fabien R, sur 11:09 AM  

Enregistrer un commentaire

<< Accueil