Les sommes conditionnelles avec SOMME.SI ou SOMME.SI.ENS

Que ce soit en analyse de données ou en reporting, la somme conditionnelle est souvent incontournable. Connaître le total d'un indicateur en filtrant sur un ou plusieurs critères est essentiel pour tous ceux qui travaillent avec des données au quotidien.
Aussi, dans cet article, je vous propose de découvrir les fonctions SOMME.SI et SOMME.SI.ENS qui permettent de réaliser ces calculs simplement dans Excel. Voyons dans le détail comment les utiliser.


 

Comprendre le principe avec la fonction SOMME.SI

Les deux fonctions sont relativement proches. Dans un premier temps, nous allons expliquer le principe avec la fonction SOMME.SI. Celle-ci est plus ancienne dans Excel mais ne permet de prendre en compte qu'un critère.
Nous verrons ensuite les différences avec la fonction SOMME.SI.ENS.

 

A quoi sert la fonction SOMME.SI ?

Comme son nom l'indique, la fonction SOMME.SI est en quelque sorte la composante de 2 fonctions : SOMME et SI.

La première est très courante et calcule tout simplement le total des valeurs d'une plage. Retrouvez sa fiche dans notre guide des fonctions si vous voulez en savoir plus.

La seconde permet d'évaluer des conditions binaires ou booléennes, c'est-à-dire à résultat vrai ou faux. Le traitement diffère en fonction du résultat. Pour bien comprendre le principe et le fonctionnement des booléens, vous pouvez consulter cet article de présentation.

De façon logique, la combinaison des deux nous permet de vérifier un critère sur une plage, ajouter le nombre correspondant si la condition est vérifiée ou l'ignorer sinon et renvoyer la somme des nombres retenus comme résultat final.

Voici un exemple de ce que la fonction peut faire. Nous disposons d'une liste de commandes prises par des commerciaux (seules les premières lignes sont affichées). Nous cherchons le total des quantités et du chiffre d'affaires pour l'un d'entre eux.

Exemples sommes conditionnelles
 

Comment utiliser la fonction ?

La fonction SOMME.SI prend donc 3 arguments, respectivement :

  • La plage de cellules où doit être vérifiée la condition
  • La condition elle-même
  • La plage de cellules contenant les nombres à ajouter

Reprenons l'exemple précédent, la formule entrée pour obtenir la somme des quantités est la suivante :

Exemple somme conditionnelle fonction SOMME.SI

A noter que la deuxième plage est facultative. Si celle-ci n'est pas renseignée, la somme est automatiquement faite sur la première plage.
Sous réserve qu'elle contient bien des nombres, sinon le résultat sera 0 quoi qu'il arrive, comme avec l'exemple ci-dessus.

Fonction SOMME.SI sans plage somme
 

Comment fonctionne-t-elle ?

On peut illustrer le fonctionnement de SOMME.SI en le décomposant en plusieurs étapes.
La première peut être vue comme une succession de fonctions SI. La même condition est vérifiée sur chaque ligne. La 1ère plage est alors convertie en une plage de VRAI/FAUX.

Fonctionnement SOMME.SI verification condition

Ces valeurs sont ensuite appliquées aux nombres de la 2ème plage. Si la condition est vraie, le nombre est conservé, sinon il est remplacé par 0.

Fonctionnement SOMME.SI conversion condition plage somme

Enfin, la fonction termine par sommer toutes les valeurs de cette dernière plage.
Evidemment, toutes ces étapes sont ‘transparentes' pour nous mais elles permettent de mieux comprendre le traitement effectué en arrière-plan.

 

Quelles différences avec la fonction SOMME.SI.ENS ?

Le suffixe ENS à la fin du nom désigne ‘ensemble', comme un ensemble de conditions que l'on peut définir pour cette fonction.
Le principe est donc le même que pour la fonction SOMME.SI. La différence c'est qu'avec SOMME.SI.ENS on n'est plus limité à un seul critère. On peut même en renseigner 127 différents !

En revanche, la syntaxe des deux fonctions est différente. Comme la plage contenant les nombres à ajouter reste unique, il est plus pratique de l'insérer dès le début et de laisser ensuite la place pour les conditions.
3 arguments minimum sont alors obligatoires :

  • Plage pour la somme
  • Plage pour la 1ère condition
  • 1ère condition

Les arguments facultatifs fonctionnent ensuite par paire, le nombre total d'arguments est donc toujours un nombre impair : 5, 7, 9... jusqu'à 255 si on renseigne 127 critères.
La logique reste identique par la suite, la plage pour la condition venant toujours avant la condition elle-même.

Reprenons l'exemple précédent et ajoutons une condition pour ne plus prendre en compte que les commandes dont les quantités sont au moins égales à 10.

Exemple somme conditionnelle fonction SOMME.SI.ENS plusieurs criteres

Comme suggéré avec les 3 arguments, SOMME.SI.ENS fonctionne aussi avec un seul critère et le résultat est alors identique à celui de SOMME.SI, heureusement !
Avant la version 2007 d'Excel, seule SOMME.SI existait et elle avait donc un véritable intérêt, c'est moins le cas depuis quelques années. Consultez cet article dans lequel nous évoquons les différences et l'intérêt des fonctions .SI par rapport aux .SI.ENS en général (SOMME, NB, MOYENNE).

 

Comment utiliser les conditions ?

Les possibilités avec les conditions sont nombreuses, on peut aussi bien faire référence à une cellule ou entrer la condition directement dans la fonction. On peut utiliser des égalités, inégalités, nombres, textes. L'utilisation est exactement la même avec SOMME.SI et SOMME.SI.ENS.
Voyons ci-dessous quelques exemples et les règles à retenir pour la rédaction selon les différents cas.

 

Avec une référence

Pour une égalité, nombre ou texte, il suffit d'entrer l'adresse de la cellule. C'est ce que nous avions utilisé dans notre premier exemple.

Fonction SOMME.SI condition egalite reference

Pour une inégalité, il faut alors placer le symbole entre guillemets et rajouter une esperluette (&) avant la référence à la cellule contenant la valeur. Exemple ci-dessous avec la somme des commandes dont le volume est d'au moins 16.

Fonction SOMME.SI condition inegalite composee

Sans les guillemets ou l'esperluette, la validation est impossible.
L'autre possibilité est de faire figurer toute l'inégalité dans la cellule de référence, l'adresse est alors suffisante dans la fonction.

Fonction SOMME.SI condition inegalite reference

Pour un texte, l'inégalité équivaut à une recherche avec un joker, en utilisant un astérisque ou un point d'interrogation. On le place comme le symbole d'une inégalité mathématique : entre guillemets et avec une esperluette.

Fonction SOMME.SI condition texte joker

On peut également insérer une fonction pour déterminer le critère.

Fonction SOMME.SI condition fonction reference
 

Avec une valeur entrée dans la fonction

Pour utiliser le critère directement en argument de la fonction, on distingue un texte d'un nombre. Le nombre est entré directement, le texte est lui placé entre guillemets.

Fonction SOMME.SI condition saisie argument egalite

Pour une inégalité ou un texte avec joker, l'ensemble de la condition est entre guillemets, y compris un nombre si elle en contient un.

Fonction SOMME.SI condition saisie argument inegalite
 

Comment utiliser les plages ?

Définir la plage

Dans les exemples que nous avons vus jusqu'ici, les plages sont délimitées par un nombre précis de lignes.
Il est aussi possible de sélectionner l'ensemble de la colonne pour aller plus vite. Le résultat est alors inchangé.

Fonction SOMME.SI plages colonnes entieres

Les données sont généralement placées en colonnes, c'est le cas le plus courant. Mais cela fonctionnerait de la même façon si elles étaient en lignes.

Par contre, il faut que les plages aient la même taille pour que le calcul soit correct.
En fait, les fonctions .SI.ENS ont un contrôle à ce niveau. Si les plages de calcul et de condition n'ont pas la même taille, Excel renvoie une erreur #VALEUR! C'est pratique pour éviter des imprécisions dans le calcul.

Erreur fonction SOMME.SI.ENS plages tailles differentes

Dans un tel contexte, la fonction SOMME.SI procède au calcul et elle adapte la taille de la plage de somme à celle de la plage de condition. Que cette dernière soit plus grande ou plus petite, c'est toujours elle qui sert de référence.

Fonction SOMME.SI plages tailles differentes correction

En revanche, il n'y a pas de contrôle de cohérence, pour vérifier que les lignes prises en comptes sont les mêmes.

Fonction SOMME.SI plages decalees

Si on reprend la décomposition du calcul que l'on a vue précédemment, les lignes ajoutées vont être décalées. La 1ère ligne de la plage de condition est toujours rapprochée de la 1ère ligne de la plage de somme.

Fonctionnement SOMME.SI plages decalees

Utiliser une colonne entière permet d'éviter aussi ce problème. C'est donc souvent une bonne solution

 

Modifier la plage de vérification du critère

Les possibilités et combinaisons sur les critères sont multiples. En revanche, il n'est pas possible d'utiliser des fonctions sur les plages pour affiner les critères.
Par exemple, on ne peut pas utiliser la fonction ANNEE sur une plage de dates pour l'utiliser comme condition.

Retraitement plages fonction SOMME.SI

La première alternative consiste à créer une plage intermédiaire, dans laquelle on effectue l'opération avant de l'utiliser comme plage de condition.

Retraitement plage intermediaire fonction SOMME.SI

La seconde est de décomposer la condition lorsque c'est possible. Par exemple avec les dates, une condition sur l'année 2017 équivaut à une double condition : supérieur ou égal au 1er janvier 2017 et inférieur ou égal au 31 décembre 2017.

Decomposition condition fonction SOMME.SI

La dernière possibilité est de préférer la fonction SOMMEPROD qui permet d'utiliser des conditions élaborées, y compris pour la plage de référence. Nous y reviendrons dans un prochain article.

Laissez vos questions ou vos conseils pour ces deux fonctions dans les commentaires !

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , .Lien pour marque-pages : Permaliens.

Laisser un commentaire