Somme et dénombrement conditionnel:
NB.SI, SOMME.SI, NB.SI.ENS, SOMME.SI.ENS, SOMMEPROD

Date de publication : 23 avril 2009

Par Pierre Fauconnier (http://fauconnier.developpez.com) (Blog)

La fonction SOMMEPROD: Utiliser des conditions multiples pour dénombrer ou sommer des données

Version PDF (Miroir) Version hors-ligne (Miroir)

I. Introduction
I-A. A quoi sert SOMMEPROD?
I-B. Prérequis
I-C. Illustrations
II. NB.SI, SOMME.SI (Versions 2003 et 2007)
II-A. NB.SI
II-A-1. Dénombrer sur une condition liée à du texte
II-A-2. Compter les valeurs plus petites ou égales à une date donnée
II-A-3. Compter les données comprises entre deux valeurs
II-A-4. Comparaisons sur des parties de texte, expressions régulières
II-B. SOMME.SI
III. NB.SI.ENS, SOMME.SI.ENS (Version 2007 uniquement)
III-A. NB.SI.ENS
III-B. SOMME.SI.ENS
III-C. Conclusions
IV. SOMMEPROD: Syntaxes
IV-A. Syntaxe "officielle"
IV-A-1. Syntaxe
IV-A-2. Utilisation
IV-B. Syntaxe courante
IV-C. Utilisation
V. Etude d'un cas
V-A. Tester certaines conditions
V-B. Retenir les montants pour les lignes qui satisfont aux conditions
V-C. Utiliser SOMMEPROD
V-D. Explications de la formule utilisée
VI. Création des tests logiques, tests sur des dates, valeurs entre bornes, ...
VI-A. Rappel sur les opérateurs de comparaison et l'utilisation des jokers
VII. Remerciements

I. Introduction

I-A. A quoi sert SOMMEPROD?

SOMMEPROD, contraction de SOMME des PRODUITS, est, au départ, une "pseudo-matricielle", qui permet de calculer la somme des produits des valeurs correspondantes de plusieurs matrices de mêmes dimensions.
Elle est plus couramment utilisée pour retourner la somme de valeurs d'une plage en fonction de conditions testées dans différentes plages.

I-B. Prérequis

Etre à l'aise avec les manipulations basiques d'Excel
Connaître l'utilisation des références relatives et absolues (utilisation du signe $ dans les références de cellules)
Avoir des notions de l'utilisation des formulations de condition

I-C. Illustrations

Les illustrations ont été créées sur la version 2007 d'Excel, mais SOMMEPROD est utilisable sans problème et de la même façon sur les versions antérieures qui disposent de la fonction.

II. NB.SI, SOMME.SI (Versions 2003 et 2007)

Avant de détailler l'utilisation de SOMMEPROD pour sommer ou dénombrer sous conditions, il me semble utile de mentionner que, pour des conditions simples, Excel met à notre disposition deux fonctions en Excel 2003.

II-A. NB.SI

Il existe deux fonctions qui permettent de dénombrer ou de sommer sous conditions.

Ces fonctions sont NB.SI(Plage;Critère) (dénombrement) et SOMME.SI(Plage;Critère;[Somme_Plage]) (1) (somme).

Ces fonctions permettent l'utilisation d'un critère passé sous forme de chaîne de caractères et sont assez basiques. Elles ne permettent de tester qu'un seul critère et il faut dès lors utiliser plusieurs fois la fonction pour tester par exemple des valeurs avec bornes inférieure et supérieure.

Le critère est simple à rédiger. Il repose sur l'utilisation d'un opérateur de comparaison, et lorsque celui-ci est omis, c'est l'égalité stricte qui est vérifiée.

Le tableau suivant indique les opérateurs logiques utilisables.

II-A-1. Dénombrer sur une condition liée à du texte

Dans le tableau suivant, si je veux dénombrer les ventes qui ont eu lieu au Luxembourg, je peux utiliser NB.SI

II-A-2. Compter les valeurs plus petites ou égales à une date donnée

Dans le même tableau, on pourra dénombrer les données de janvier 2009. J'ai supposé ici qu'il n'y avait pas de dates antérieures au 1er janvier 2009.

II-A-3. Compter les données comprises entre deux valeurs

Les choses se compliquent un peu lorsque l'on veut dénombrer les valeurs comprises entre deux seuils. Il faut alors être "logique" et décomposer le test.

Pour compter les valeurs comprises entre 20 et 40, bornes comprises, nous allons d'abord dénombrer celles qui sont inférieures ou égales à 40.

Bien sûr, cette formule renvoie trop de valeurs puisque les valeurs inférieures à 20 sont comptées. Il faut donc les soustraire, en complétant la formule avec un nouveau NB.SI.

II-A-4. Comparaisons sur des parties de texte, expressions régulières

On peut utiliser les jokers * ou ? lorsque l'on cherche des parties de texte ainsi que certaines expressions régulières.

Nombre de ventes pour les régions dont le nom commence par 'L'

Nombre de ventes pour les régions dont le nom finit par 'bourg'

Nombre de ventes pour les produits dont le nom contient 'o' en deuxième position

II-B. SOMME.SI

La création des critères est identique pour la fonction SOMME.SI. Lorsque le critère est appliqué sur la même plage que celle qui contient les éléments à sommer, il n'est pas besoin de le préciser.

On notera que la somme peut être effectuée sur une autre plage que la plage servant au critère.

Lorsque le critère est appliqué sur une autre plage, la plage renseignée en premier paramètre est celle sur laquelle s'appliquera le critère, celle renseignée en troisième paramètre est celle qui contient les valeurs à sommer.

III. NB.SI.ENS, SOMME.SI.ENS (Version 2007 uniquement)

La version 2007 a été enrichie de nombreuses fonctions, dont NB.SI.ENS et SOMME.SI.ENS

Ces deux fonctions permettent d'utiliser plusieurs conditions. Elles apportent de la souplesse dans les formules, même si l'utilisation de certaines conditions conjointement reste impossible et oblige à passer par SOMMEPROD.

III-A. NB.SI.ENS

La fonction NB.SI.ENS utilise deux paramètres par condition: la plage sur laquelle appliquer le critère et le critère lui-même.

NB.SI.ENS renvoie le nombre de lignes qui correspondent à TOUS les critères.

Cette fonction permet bien entendu de définir plusieurs critères sur la même plage, par exemple pour définir les bornes inférieure et supérieure de valeurs à compter.

Comme dit plus haut, toutes les conditions doivent être remplies, ce qui empêche de tester (une condition OU une autre condition) avec une seule fonction.

Il faudra dans ce cas utiliser une addition de plusieurs NB.SI.ENS.

.

III-B. SOMME.SI.ENS

Les conditions se créent bien sûr de la même façon avec SOMME.SI.ENS. Il faut cependant ajouter, en premier paramètre, la plage contenant les valeurs à sommer.

Cette syntaxe est inversée par rapport à SOMME.SI. Dans SOMME.SI, la plage servant à la somme doit se trouver en troisième paramètre. De plus, dans SOMME.SI, cette plage peut ne pas être renseignée si elle est identique à celle qui sert à la vérification du critère.

Bien entendu, on peut utiliser la plage servant au calcul de la somme pour tester un ou plusieurs critères.

III-C. Conclusions

Comme on peut le voir, la combinaison de tests conditionnels devient complexe, voire impossible à réaliser. Pour utiliser des combinaisons de conditions, il sera souvent plus aisé de passer par SOMMEPROD.

Il ne sera pas possible de combiner certaines conditions utilisant OU ou OU exclusif avec les quatre fonctions que nous venons de voir. De plus, l'utilisation des fonctions 2007 n'est bien entendu pas envisageable en version 2003, alors que SOMMEPROD est utilisable dans toutes les versions d'Excel.

C'est pour ces raisons que j'aborderai la fonction SOMMEPROD dans le prochain chapitre.

IV. SOMMEPROD: Syntaxes

Il existe deux syntaxes pour la fonction SOMMEPROD, la syntaxe officielle, documentée dans l'aide d'Excel, et une syntaxe qui n'est pas mentionnée dans l'aide, mais qui est celle que nous utiliserons pour la somme ou le dénombrement conditionnel.

IV-A. Syntaxe "officielle"

Je mentionne cette utilisation de SOMMEPROD pour mémoire, car pour ce qui me concerne, je n'ai jamais eu à l'utiliser de cette manière, et sous cette forme, elle ne convient pas pour la somme ou le dénombrement conditionnel.

IV-A-1. Syntaxe


					=SOMMEPROD(Matrice1;Matrice2;...)

Les matrices doivent être de mêmes dimensions (même nombre de lignes et même nombre de colonnes).
Avec Excel 2003, la fonction nécessite l'utilisation d'au moins deux matrices, et peut utiliser jusqu'à 30 matrices.
Avec Excel 2007, la fonction nécessite l'utilisation d'au moins deux matrices, et peut utiliser jusqu'à 255 matrices.

IV-A-2. Utilisation

L'utilisation de SOMMEPROD sous cette syntaxe permet de calculer la somme des produits des valeurs correspondantes de plusieurs matrices de mêmes dimensions.

Soit les matrices suivantes:

SOMMEPROD permet de calculer les produits A2 * D2 * G2, B2 * E2 * H2, A3 * D3 * G3, B3 * E3 * H3, puis de calculer la somme de tous les produits.

A la main, cela donnerait la décomposition suivante:
- Calcul des produits (Observez la formule en B5):

- Calcul de la somme:

La création de la formule complète au sein d'une seule cellule donne quelque chose qui n'est pas "trop" compliqué ni "trop" illisible pour nos matrices 2 * 2.

On aurait pu utiliser la formule suivante, qui n'est probablement pas beaucoup plus lisible, mais dont la conception s'apparente plus à SOMMEPROD (SOMME de PRODUITS) que la formule précédente qui utilisait l'opérateur arithmétique d'addition.


					=SOMME(A2*D2*G2;B2*E2*H2;A3*D3*G3;B3*E3*H3)

Je vous laisse imaginer la lisibilité d'une telle formule avec dix matrices de 20 * 20...

SOMMEPROD va permettre d'obtenir une formule plus lisible, comme l'illustre l'écran suivant:

On remarque ici que la longueur de la fonction ne dépend que du nombre de matrices utilisées, quelles que soient leurs dimensions.

IV-B. Syntaxe courante

Dénombrement conditionnel, avec autant de tests logiques que nécessaire


				=SOMMEPROD(TEST_LOGIQUE1 * TEST_LOGIQUE2)

Somme conditionnelle, avec autant de tests logiques que nécessaire. Habituellement, on place les tests logiques avant la plage contenant les valeurs à sommer.


				=SOMMEPROD(TEST_LOGIQUE1 * TEST_LOGIQUE2 * PlageValeur)

Les paramètres TEST_LOGIQUE doivent être des "formules" qui renvoient VRAI ou FAUX. Il n'est pas possible d'utiliser les fonctions SI, ET, OU dans les formules des tests logiques.

IV-C. Utilisation

L'utilisation de SOMMEPROD dans ce cas va être illustrée et détaillée ci-dessous.

V. Etude d'un cas

Sur base du tableau ci-dessous, nous allons entreprendre d'extraire les montants correspondants à des conditions basées sur les autres colonnes du tableau.

Comme il a été précisé plus haut que nous ne pouvons utiliser les fonctions SI, ET, OU ou toute autre fonction logique, nous devrons exprimer les tests logiques autrement.

V-A. Tester certaines conditions

Nous allons d'abord détailler le processus en décomposant le fonctionnement de SOMMEPROD.

On veut trouver les lignes qui concernent Martine. Dans une colonne à droite du Tableau, on va donc placer une condition. Nous avons évidemment le réflexe d'utiliser la fonction SI:


					=SI(B2="Martine";VRAI;FAUX)

Remarquons que les "mots" VRAI et FAUX ne sont pas encadrés par des guillemets. Excel les considère donc comme des constantes, et c'est ce qui nous intéresse ici.

Cette utilisation de SI étant proscrite au sein de la fonction SOMMEPROD, nous allons devoir biaiser un peu.

Lorsque le résultat de SI doit être simplement VRAI ou FAUX, nous pouvons poser un postulat, et Excel nous informera de sa justesse. Au lieu d'utiliser SI, nous allons affirmer que B2 est égal à "Martine" et laisser Excel nous répondre.


					=(B2="Martine")

Procédons de manière identique pour tester la région "Luxembourg", puis tirons les formules vers le bas pour vérifier que tout se calcule comme voulu.

Nous identifions ainsi les lignes qui remplissent les deux conditions.

V-B. Retenir les montants pour les lignes qui satisfont aux conditions

Les constantes VRAI et FAUX renvoyées par Excel sont en fait des valeurs numériques.

VRAI = 1
VAUX = 0.

Si nous multiplions une valeur numérique par VRAI, nous récupérons cette valeur numérique (1 est neutre pour la multiplication dans R)

Si nous multiplions une valeur numérique par FAUX, nous récupérons 0 (0 est absorbant pour la multiplication dans R)

Pour retenir les montants des lignes satisfaisant aux conditions, nous allons donc multiplier le montant par les réponses à nos postulats.

En copiant les formules de F2:H2 sur l'ensemble de nos lignes, nous obtenons en H soit 0, soit le montant mentionné en colonne E, en fonction des conditions remplies. Il nous suffit de sommer les valeurs en H pour obtenir le montant des opérations réalisées par Martine au Luxembourg.

Si nous analysons ce que nous venons de réaliser, nous avons réalisé la somme des différents produits effectués sur chaque ligne.

V-C. Utiliser SOMMEPROD

SOMMEPROD nous permet de réaliser cela en une seule cellule, sans devoir développer les tests intermédiaires.

Voici la syntaxe pour réaliser le même calcul

V-D. Explications de la formule utilisée

Regardons en détails la formule utilisée. La formule utilise la fonction SOMMEPROD, en lui passant trois paramètres. Nous remarquons que les paramètres passés utilisent des plages de données (matrices) possédant le même nombre de lignes.

VI. Création des tests logiques, tests sur des dates, valeurs entre bornes, ...

VI-A. Rappel sur les opérateurs de comparaison et l'utilisation des jokers

VII. Remerciements

Merci