Monsieur Excel
Pour tout savoir faire sur Excel !

27 février 2009

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 un nombre variable de doublons « colonne;valeur ».

rédigé par Hervé Thiriez | 15 commentaire(s)

15 Commentaire(s):

  • Le pb est qu'il faut créer une plage de critères, pas du tout commode en multi critères. La fonction plus proche c'est plutot SOMMEPROD, je pense.

    Autre avantage de somme.si.ens, c'est la vitesse de calcul : environ 20 fois plus rapide.

    By Anonymous Anonyme, sur 11:28 AM  

  • sauriez-vous, quelle est la traduction anglaise de ces deux formules?

    merci d'avance

    By Blogger Unknown, sur 7:18 PM  

  • Maude, pour connaître l'équivalent en anglais d'une formule Excel, il suffit d'enregistrer une macro d'une ligne où l'on valide la formule en question.

    Allez alors voir le code et vous aurez la solution :)

    By Blogger Hervé Thiriez, sur 8:48 AM  

  • Somme.Si.Ens = Sumifs
    Nb.Si.Ens = Countifs.

    en fait, en anglais, MS a seulement ajouté un "S" à la fin.

    By Blogger Ferdhy, sur 2:48 PM  

  • Bonjour,

    Voilà je suis actuellement en stage en qualité d'assitant controleur de gestion et j'avoue que j'ai certaines difficultés sur excel notamment avec la formule somme.si.Ens.

    Mon problème est le suivant:

    j'ai deux feuilles de calculs ( 2 bases de données).

    je veux dans ma première feuille de calcul remplir une colonne nommée "heures pointées".
    Mias pour remplir cette colonne je me sers des matricule SAP ( se trouvant juste à coté).
    dans la deuxième base de données (Deuxième feuille de calcul). Il y a aussi ces mêmes matricules SAP (en désordre) associées à des heures. Pour remarque on peut retrouver le même matricule plusieurs fois avec des heures différentes ( Chose tout à fait normal)!

    Cependant, je veux une formule , qui prend le matricule de la premiere base de données,qui va le rechercher dans la deuxième base de données et une fois trouvé, elle additionne les différentes heures associées à ce même matricule. Un fois terminé, qu'elle vienne me le reclaquer dans la celule de la colonne à remplir.

    Voilà, j'espère être clair.

    nb: j'ai essayer La fonction recherche V , mais elle ne somme pas donc impossible , et les macros sous VBA, mon chef ne veut pas.

    Vous remerciant d'avance.

    Mohamed

    By Anonymous kleit, sur 3:03 PM  

  • Pour que je puisse vous répondre, SVP mailez-moi un fichier où je peux voir le pbm : thiriez@hec.fr

    By Blogger Hervé Thiriez, sur 3:52 PM  

  • Bonjour,
    Si tous mes critères sont sur la même colonne (A en l'occurence dans mon cas), ça ne marche pas?
    Ex de formule:
    =SOMME.SI.ENS(B:B;A:A;A2;A:A;A4;A:A;A7).
    Le résultat que j'ai est 0.
    merci

    By Blogger Wet, sur 11:17 AM  

  • la fonction NB.SI.ENS me renvoit 0 quand un NB.SI(plageA;"y")+NB.SI(plageA;"z") me renvoit bien le nombre de y + les z. Comment expliquez-vous cela ?
    Merci

    By Anonymous Anonyme, sur 10:27 AM  

  • SVP mailez-moi un fichier que je puisse voir ce qui cloche...

    thiriez@hec.fr

    By Blogger Hervé Thiriez, sur 5:56 PM  

  • Bonjour,

    Je souhaite utiliser la fonction NB.SI.ENS pour calculer sur une plage donnée le nombre de valeur différente de "x" et non vide.
    Je ne parviens pas à trouver la syntaxe correcte de la formule.

    Quelqu'un saurait-il m'aider svp ?

    Cordialement,

    Isa,

    By Blogger Yeza86, sur 2:47 PM  

  • Re-bonjour,

    J'ai trouvé un moyen de parvenir à mon résultat mais de façon détournée.

    Voici la formule que j'ai créée :
    =(NB.SI.ENS($F$2:$F$23;$H29;DT$2:DT$23;"<>PAC"))-(NB.SI.ENS($F$2:$F$23;$H29;DT$2:DT$23;"")).

    Cela fonctionne mais il y a peut-être plus direct, et plus sûr.

    Cordialement,

    Isa,

    By Blogger Yeza86, sur 4:57 PM  

  • Bonjour,
    Je prépare un tableau de vente et la formule ne fonctionne pas si je prends la plage entière en prenant en compte les lignes vides....
    ex : =SOMME.SI.ENS(D:D;B:B;F2;C:C;G1)
    Quelqu'un peut-il m'aider ?
    Merci.

    Peantoine

    By Blogger Pierre-Emmanuel, sur 5:58 PM  

  • Même réponse que d'habitude : mailez-moi votre fichier à thiriez@hec.fr que je puisse voir ce qui ne va pas...

    By Blogger Hervé Thiriez, sur 12:36 AM  

  • Bonjour,

    je viens de découvrir la fonction SOMME.SI.ENS

    Je l'ai testé une première fois mais je rencontre le problème suivant:

    J'ai un onglet de suivi budgétaire récap qui applique cette formule à des plages de données situées dans un autre onglet. Le résultat du calcul est #VALEUR alors que tout fonctionne lorsque j'applique la formule dans l'onglet où se trouvent les plages de données.

    Merci par avance pour vos conseils avisés.

    Johan Nicolas

    By Anonymous Anonyme, sur 9:39 AM  

  • Bonjour,

    J’ai un problème avec la formule NB.SI.ENS.
    Quand j’utilise une plage située dans un onglet différent, la formule me donne un résultat 0, j’ai constaté qu’elle fonctionne uniquement quand la plage se situe dans le même onglet.
    Y aurait il une solution svp, pour que la formule fonctionne pour le premier cas ?

    By Anonymous Anonyme, sur 6:40 PM  

Enregistrer un commentaire

<< Accueil