«

»

Fonction SOMME.SI.ENS

Dans notre exemple sur la gestion de stock de produits de beauté, nous voudrions savoir la quantité restante en fin de mois pour le rouge à lèvres ou encore le nombre de ventes réalisé au cours du mois pour un autre produit. La fonction SOMME.SI.ENS va nous permettre de retourner cette information très simplement.

Autant la fonction NB.SI.ENS ne pose généralement pas de problèmes de compréhension, autant il m’est arrivé de constater que l’apprentissage de la fonction SOMME.SI.ENS posait plus de difficulté. Pourtant, les 2 fonctions sont extrêmement proches dans leur construction. Si vous comprenez NB.SI.ENS, il n’y a aucune raison que SOMME.SI.ENS vous pose des problèmes.

Quand vous maîtriserez la création de la fonction SOMME.SI.ENS, nous verrons les fonctions MOYENNE.SI.ENS, MIN.SI.ENS et MAX.SI.ENS. 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 la fonction en utilisant Excel Online

La fonction SOMME.SI.ENS

Construction

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 dans les paramètres une colonne supplémentaire ; celle qui contient les éléments à additionner. Cette colonne supplémentaire se positionne en première position des paramètres de la fonction.

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

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.

Quantité 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

Plus qu’un résultat, la fonction NB.SI.ENS nous indique que nous avons correctement écrit nos critères 8)  Forts de ce constat, nous allons transformer la fonction NB.SI.ENS en fonction SOMME.SI.ENS. Déjà, nous remplaçons le terme NB par SOMME et nous rajoutons en première position, la colonne stock fin de mois. La fonction devient :

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

Les plages de critères et les critères étant les mêmes entre une fonction NB.SI.ENS et la fonction SOMME.SI.ENS, il est beaucoup plus simple de commencer par construire votre fonction NB.SI.ENS pour s’assurer que vos critères sont corrects et ensuite de rajouter le seul élément qui manque à la fonction SOMME.SI.ENS ; la colonne à additionner.

Combien de ventes réalisées dans le mois

Exactement de la même manière si nous voulons 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

Nouveaux critères

Maintenant, si vous voulez rajouter d’autres critères, il vous suffit de les rajouter après le dernier critère déjà présent dans votre formule. Pour connaître la quantité de vernis à ongle du fournisseur A vendu au cours du mois, il faut écrire la formule suivante

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

Tout le reste de la fonction reste exactement identique.

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 tube de rouge à lèvres, par référence, en stock fin de mois.

MAX.SI.ENS et MIN.SI.ENS

Mise à jour. Par rapport à la vidéo, les fonctions ont été renommées

Ces 2 nouvelles fonctions sont apparues en février 2016 et étaient attendues depuis très longtemps. Tout comme nous l’avons fait avec SOMME.SI.ENS et MOYENNE.SI.ENS, ces deux fonction pour nous permettre de retourner la valeur minimale ou maximale d’une colonne selon les critères que nous allons écrire en paramètre.

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)

1 ping

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

  1. Excel sur son téléphone portable

    […] Fonction SOMME.SI.ENS […]

Laisser un commentaire

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

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger