X
    Categories: Intermédiaire

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.

Frédéric LE GUEN :