«

»

Fonction SOMME.SI.ENS

Sujets traités dans l’article


Nous allons finir notre exemple sur la gestion de stock de produits de beauté, en déterminant

  • La quantité restante en fin de mois pour le rouge à lèvres
  • Le nombre de ventes réalisé au cours du mois

La fonction SOMME.SI.ENS va nous permettre de retourner cette information très simplement. Ensuite, nous verrons comment utiliser les fonctions MOYENNE.SI.ENS, MIN.SI.ENS et MAX.SI.ENS. pour enrichir nos tableaux de bords.

Ces 2 dernières fonctions étaient très attendues depuis de nombreuses années et sont apparues seulement depuis la dernière version d’Excel 2016. Si vous n’avez pas la dernière version d’Excel sur votre ordinateur, vous pouvez néanmoins utiliser ces nouvelles fonctions en utilisant Excel Online



La fonction SOMME.SI.ENS

Construction


Pour rappel, la fonction NB.SI.ENS s’écrit de la façon suivante :

=NB.SI.ENS(Plage de critères 1;Critère 1;Plage de critères 2;Critère 2;…)

La fonction SOMME.SI.ENS se construit exactement comme la fonction NB.SI.ENS. La seule chose qui change, c’est que vous allez indiquer en premier paramètre, la colonne qui contient les éléments à additionner.

=SOMME.SI.ENS(Plage à additionner;Plage de critères 1;Critère 1;Plage de critères 2;Critère 2;…)

Utilisez le document suivant pour y copier toutes les formules qui vont suivre afin d’y afficher les différents résultats retournés par les différentes fonctions. Ca fonctionne nickel 😉

Quelle est la quantité en stock en fin de mois ?



Nous souhaitons déterminer la quantité de rouge à lèvres en stock à la fin du mois.

Depuis notre tableau des ventes nous allons construire une fonction NB.SI.ENS qui va comptabiliser le nombre de lignes qui contiennent le mot rouge à lèvres.

=NB.SI.ENS(B2:B41;”Rouge à lèvres”)    => Résultat 8

Maintenant, il nous suffit de

  • remplacer le terme NB par SOMME
  • rajouter en première position la colonne stock fin de mois

pour déterminer le nombre de Rouges à lèvres en stock. La fonction devient :

=SOMME.SI.ENS(H2:H41;B2:B41;”Rouge à lèvres”)    => Résultat 189

Combien de ventes réalisées dans le mois ?

Exactement de la même manière, pour connaître le nombre de vernis à ongle que nous avons vendus au cours du mois, nous allons d’abord faire une fonction NB.SI.ENS sur le critère vernis à ongle et ensuite, rajouter la colonne des ventes.

La première formule s’écrit

=NB.SI.ENS(B2:B41;”Vernis à ongle”)    => Résultat 12

La transformation avec SOMME.SI.ENS s’écrit

=SOMME.SI.ENS(G2:G41;B2:B41;”Vernis à ongle”)    => Résultat 137

Rajouter de nouveaux critères

Pour connaître uniquement la quantité de vernis à ongle du fournisseur A vendu au cours du mois, il suffit de rajouter un nouveau critère à la suite des paramètres précédents.

=SOMME.SI.ENS(G2:G41;B2:B41;”Vernis à ongle”;D2:D41;”A”)    => Résultat 63



La fonction MOYENNE.SI.ENS

La fonction MOYENNE.SI.ENS est en tout point identique à la fonction SOMME.SI.ENS à la différence qu’ici ce n’est pas une somme qui va être retournée mais la moyenne des données de votre colonne en reprenant vos critères.

Reprenons la première formule qui nous a donné le nombre de rouge à lèvres en stock.

Si maintenant nous voulons connaître la moyenne des rouges à lèvres en stock, il nous suffit de replacer le mot SOMME par MOYENNE et sans changer le moindre paramètre, la fonction nous retourne cette fois la valeur suivante

=MOYENNE.SI.ENS(H2:H41;B2:B41;”Rouge à lèvres”)

Résultat : 23,6 tubes de rouge à lèvres, en stock fin de mois.

Les fonctions MAX.SI.ENS et MIN.SI.ENS


Ces 2 nouvelles fonctions sont apparues en février 2016 et étaient attendues depuis très longtemps 😘😘😘

Présentation des fonctions

  • MIN.SI.ENS va retourner la valeur minimale selon plusieurs critères
  • MAX.SI.ENS va retourner la valeur maximale selon plusieurs critères

Les paramètres de ces fonctions sont exactement les mêmes que pour SOMME.SI.ENS

Exemples d’applications

Par exemple si vous voulons connaître la quantité minimale de vernis à ongle vendue au cours du mois nous allons écrire

=MIN.SI.ENS(G2:G41;B2:B41;”Vernis à ongle”)  => Résultat 0

Et si nous voulons connaître la quantité maximale de vernis à ongle vendue au cours du mois nous écrirons

=MAX.SI.ENS(G2:G41;B2:B41;”Vernis à ongle”)  => Résultat 28

Dans ces 2 exemples, nous n’avons utilisé qu’un seul critère mais nous pouvons bien évidemment en rajouter si votre tableau de bord le nécessite.




Lien Permanent pour cet article : https://www.excel-exercice.com/somme-si-ens/

(12 commentaires)

Passer au formulaire de commentaire

  1. Michael Ipert

    Bonjour,
    Pour les dates vous demandez à excel de chercher les dates supérieures à la dates de début et inférieures à la date de fin mais cela ne risque pas d’additionné toutes les dates supérieures au début c’est à dire même celles supérieures à la date de fin et toutes les dates inférieures à la date de fin c’est à dire même celles antérieures à la date de début. ou la fonction fait en sorte que toutes les conditions soit réunies sinon elle n’effectue pas l’addition?

  2. Anonyme

    vraiment top. merci !

  3. Anonyme

    Bonjour. j’utilise pour ma part depuis longtemps la fonction “sommeprod()” et j’ai du mal à voir la différence de comportement entre ces deux fonctions. Pourriez-vous m’éclairer ?

    Merci de votre aide.

  4. Cacaille76

    Bonjour, Le tuto est bon toute fois une petite question: est-il possible de nommer les plages?
    =somme.si.ens(chemin_repertoire\Nom_fichier’!nom_feuille(mon_calcul);(chemin_repertoire\Nom_fichier’!nom_feuille(ma_plage1)critère1;(chemin_repertoire\Nom_fichier’!nom_feuille(ma_plage2)critère2
    Merci de votre reponce

    1. Frédéric LE GUEN

      Bonjour,
      C’est possible mais il faut que le classeur qui contiennent les données soit ouvert ; c’est impératif.

  5. danbo52danbo

    c’est mieux si les entetes sont dans les exemples colonnes et lignes

  6. Alain Roussel

    dans la formule Somme.si.ens est-ce possible d’insérer un critère avec une comparaison d’une valeur qui se retrouve sur une autre feuille. Voici un exemple :
    La formule Somme.si.ens est dans une cellule de la Feuille1, mais une valeur de comparaison pour un critère se retrouve dans la Feuille2.

    =SOMME.SI.ENS(B1:B5;C1:C5;”=LABELLE”;D1:D5;”=Feuil2!A1″)

  7. Jessy

    Bonjour

    Merci pour ce tutoriel. De mon côté j’aimerai savoir si il est possible d’utiliser à deux reprises la même plage de critère. Je vous montre mon cas: voici ma base

    A B C D E
    Groupe Spécial Coût unitaire Quantité Coût total
    Fruits En spécial $0,50 502 $251,00
    Poisson En spécial $0,70 426 $298,20
    Fruits Prix réguliers $2,50 583 $1 457,50
    Poissons En spécial $2,00 254 $508,00
    Légumes Prix réguliers $1,40 532 $744,80
    Fruits Prix réguliers $1,90 628 $1 193,20
    Légumes En spécial $1,20 502 $602,40
    Légumes Prix réguliers $2,30 498 $1 145,40
    Fruits En spécial $1,00 504 $504,00
    Légumes En spécial $1,40 555 $777,00
    Légumes Prix réguliers $1,70 467 $793,90
    Poisson En spécial $2,80 611 $1 710,80
    Fruits Prix réguliers $3,20 289 $924,80
    Légumes En spécial $2,00 420 $840,00

    J’aimerai donc savoir le coût total pour les fruit et de légumes dans la même cellule sachant que mes deux critères se trouve dans la colonne A.
    Quelqu’un aurait-il une solution adéquate ,la j’ai donné un exemple , mais dans mon cas réel en entreprise ( contrôle de gestion) il s’agit d’un fichier avec plus de 3000

    1. Laurie

      Bonjour Jessy, avez vous trouvé une solution pour utiliser la même plage de cellules avec plusieurs critères ? car moi aussi je souhaite faire somme.si.ens avec un critère dans une plage A et plusieurs critères dans une plage B.

      Merci de votre réponse

    2. Chico

      Vous pourriez utiliser un controle qui se met à vrai ou faux selon vos critères et utiliser ce dernier dans votre somme.si.ens.

  8. Anonyme

    cool

    1. Anonyme

      merci de ton intervention mimmimimimimi

Laisser un commentaire

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