Comment utiliser la fonction SOMMEPROD d’Excel
Dans cet article, je vais vous présenter la fonction SOMMEPROD(), et nous verrons comment l’utiliser au travers d’exemples. Nous verrons tout d'abord l’usage classique de cette fonction, qui paradoxalement est beaucoup moins utilisée que l’usage étendue que l’on peut en faire et que nous découvrirons dans la seconde partie de cet article.
Vidéo 1 : Comment utiliser la fonction SOMMEPROD d'Excel ?
Vidéo 2 : Comment utiliser la fonction SOMMEPROD étendue pour analyser des bases de données ?
Vidéo 3 : Comment utiliser un critère partiel avec la fonction SOMMEPROD ?
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
1. Présentation de la fonction SOMMEPROD
La fonction SOMMEPROD() permet d’effectuer la somme de produits d’éléments de matrices les uns avec les autres.
Si cette explication peut paraître assez obscure, ne vous en faîtes pas, nous allons voir tout de suite ce que cela signifie.
2. Les paramètres
La fonction SOMMEPROD s’utilise de la manière suivante :
=SOMMEPROD(matrice1, [matrice2], [matrice3], ...)
Où chacune des matrices insérées en paramètre correspond aux plages des cellules dans lesquels chaque élément va être multiplié l’un à l’autre. Il est possible de saisir jusqu’à 255 matrices au sein de la fonction SOMMEPROD().
Attention, voici deux points à prendre en compte lorsque l’on utilise la fonction SOMMEPROD() :
- Si une matrice est constituée d’éléments non numériques, ces derniers auront pour valeur zéro.
- Les différentes matrices utilisées doivent impérativement être de taille identique, sous peine d’avoir pour retour une erreur #VALEUR
3. Exemples
Maintenant que nous avons vu l’aspect théorique de la fonction SOMMEPROD, nous allons pouvoir l’illustrer à travers l’exemple suivant :
Nous retrouver ici une liste de matériel informatique dont une entreprise souhaite faire l’acquisition. Pour connaître le montant total de ce devis, nous disposons de plusieurs possibilités :
Soit utiliser des opérateurs d’ajout et de multiplications, en utilisant le principe de la priorité de la multiplication sur l’addition (les parenthèses ne sont donc pas une obligation) :
=B7*C7+B8*C8+B9*C9+B10*C10+B11*C11+B12*C12
Le coût d’acquisition de ce matériel est alors de 18515€
Mais cela nécessite d’écrire une fonction assez lourde, et lorsque le nombre de lignes devient important cette opération devient vite fastidieuse.
Soit d’effectuer les calculs de coûts unitaires dans une nouvelle colonne, et d’en faire ressortir un coût total :
Cela permet évidemment de retrouver le même résultat (18615€), mais nécessite d’ajouter une colonne et d’y insérer des fonctions, ce qui demande d’y consacrer un temps non négligeable.
Soit d’utiliser la fonction SOMMEPROD(), ce qui est justement le titre de cet article ! Pour cela rien de plus simple, il nous suffit en effet de sélectionner en paramètre les colonnes qui correspondent au prix de vente unitaire du matériel, et aux quantités désirées (l’ordre de sélection n’a aucune importance) :
=SOMMEPROD(B7:B12;C7:C12)
Excel va alors réaliser en une seule fonction la même opération que ce que nous venons de faire dans l’exemple précédent, c’est-à-dire effectuer le produit de chaque éléments des matrices (élément 1 de la matrice A multiplié par l’élément 1 de la matrice B, élément 2 de la matrice A multiplié par l’élément 2 de la matrice B,…), avant d’additionner chacun de ces éléments les uns avec les autres.
4. SOMMEPROD sur une matrice unique
Lorsque l’on utilise une seule matrice dans la fonction SOMMEPROD(), Excel réalise alors la somme de chacun de ses éléments. Cela revient donc au même qu’utiliser la fonction SOMME :
5. Utiliser SOMMEPROD avec plus de deux matrices
Nous l’avons déjà abordé dans la présentation de la fonction SOMMEPROD, mais il est possible d’utiliser jusque 255 matrices dans la fonction SOMMEPROD. Cela permet d’obtenir des informations complexes rapidement, sans qu’il ne soit nécessaire de passer par des calculs intermédiaires.
Par exemple, pour obtenir le montant TTC du devis, nous pourrions simplement utiliser SOMMEPROD sur une troisième colonne :
=SOMMEPROD(B8:B13;C8:C13;D8:D13)
6. Bonus : Effectuer des calculs directement dans la fonction SOMMEPROD()
Enfin, pour finir sur ce tour d’horizon concernant la fonction SOMMEPROD, sachez qu’il est tout à fait possible de réaliser des calculs très simple sur les matrices de la fonction SOMMEPROD().
Par exemple, il va être possible de calculer une remise à la ligne :
=SOMMEPROD(B8:B13;C8:C13;100%-D8:D13)
Pour cela, nous soustrayons ligne par ligne le montant de la remise accordée à 100%.
Il est également possible d’ajouter les montants exprimés en pourcentages à 100%, ce qui permet par exemple de calculer simplement un montant TTC :
=SOMMEPROD(B8:B13;C8:C13;100%-D8:D13;100%+E8:E13)
7. … et maintenant la fonction SOMMEPROD étendue
Maintenant que nous avons analysé dans le détail le fonctionnement de la fonction SOMMEPROD, nous allons voir qu’il est possible de la détourner de son usage principal pour en décupler ses possibilités.
L’usage que nous allons voir maintenant n’est malheureusement pas documenté dans l’aide proposée par Microsoft, alors que celui-ci devient vite indispensable dès lors que l’on a connaissance. En effet, nous allons ici pouvoir effectuer de très nombreux traitements sur de grosses bases de données, en utilisant cette seule fonction !
Pour illustrer ce tutoriel, nous disposons du document suivant, que je vous invite fortement à télécharger en utilisant le formulaire disponible au début de cet article :
Dans ce document, nous retrouvons les factures émises par une société au cours des années 2015 à 2018, avec les dates d’émissions de ces factures, les clients concernés, le magasin de vente et les montants facturés.
Dès lors que nous souhaitons extraire directement des informations précises sur ces données, les choses vont se corser. Nous pourrions évidemment utiliser le panel des fonctions écrites justement pour effectuer des traitements sur les bases de données (en commençant par la fonction BDSOMME()), mais celles-ci sont complexes à mettre en place, et nécessiterait tout un tutoriel.
La solution est alors, vous pouvez vous en douter, d’utiliser SOMMEPROD, au travers de la structure suivante :
=SOMMEPROD(([plage à tester1]=[condition1])*(([plage à tester2]=[condition2])*…*[plage de retour])
Nous retrouvons alors à peu près le fonctionnement d’une fonction comme SOMME.SI.ENS(), mais nous allons voir que allons pouvoir aller beaucoup loin.
7.1. Utiliser un fitre simple (textuel)
Pour accélérer la construction des fonctions, nous allons commencer par transformer la base de données, en un tableau de données (cliquez ici pour en savoir plus).
Puis nous allons nommer ce tableau « _base » :
Ce premier exemple va nous permettre de rapidement comprendre le fonctionnement de la fonction SOMMEPROD() :
=SOMMEPROD((_base[Magasin]="Paris")*_base[Montant])
Cette fonction est alors très claire, nous pourrions également utiliser la fonction SOMME.SI() :
=SOMME.SI(_base[Magasin];"Paris";_base[Montant])
Pour bien comprendre la manière de fonctionner, nous pouvons jeter un œil à l’inspecteur de fonction (Menu fonction > Évaluer la fonction) :
Tout d’abord la fonction commence par analyse la matrice à tester :
Puis lorsque la valeur répond à la condition posée, chaque élément va prendre pour valeur VRAI, et FAUX dans le cas contraire :
Ensuite, Excel va multiplier cette matrice avec la matrice de retour, en prenant pour valeur :
- 1 pour chaque élément VRAI,
- 0 pour chaque élément FAUX
Ne reste alors plus qu’à retourner la somme de ces valeurs :
7.2. Utiliser un filtre complexe, avec une fonction
L’intérêt de la fonction SOMMEPROD() par rapport à une fonction plus classique comme SOMME.SI() est de pouvoir effectuer des tests matriciels directement dans la fonction !
Nous allons ainsi pouvoir déterminer directement les ventes réalisées au cours de l’année 2018 :
=SOMMEPROD((ANNEE(_base[Date])=2018)*_base[Montant])
7.3. Combiner plusieurs filtres
Nous pouvons également utiliser plusieurs conditions :
=SOMMEPROD((_base[Magasin]="Paris")*(ANNEE(_base[Date])=2018)*_base[Montant])
7.4. Utiliser l’opérateur OU pour filtrer les données
Depuis le début de ce tutoriel, nous utilisons le symbole étoile (« * ») pour combiner les conditions. Celui-ci a pour but de multiplier les matrices contenues entre parenthèses, et donc de cumuler les conditions.
Mais il est également possible d’utiliser le symbole plus (« + ») pour additionner les matrices ! Le schéma d’addition est alors le suivant :
- VRAI + VRAI = VRAI
- FAUX + VRAI = VRAI
- FAUX + FAUX = FAUX
=SOMMEPROD((ANNEE(_base[Date])=2018)*((MOIS(_base[Date])=1)+(MOIS(_base[Date])=2)+(MOIS(_base[Date])=3))*_base[Montant])
Attention, la difficulté ici réside dans l’utilisation des parenthèses qui permettent de bien délimiter les différentes constructions de matrices !
7.5. Dénombrer avec SOMMEPROD
Pour réaliser un dénombrement avec la fonction SOMMEPROD(), il suffit d’appliquer une valeur de un sur la plage de retour, ce qui aura pour effet de simuler une matrice complète remplie de « 1 » :
=SOMMEPROD((ANNEE(_base[Date])=2018)*1)
8. Effectuer une somme avec un critère partiel
8.1. Compter le nombre de modèles
Maintenant que nous connaissons le fonctionnement de la fonction SOMMEPROD(), nous allons pouvoir l’utiliser pour effectuer des calculs plus complexes.
Le but va en effet être d’extraire des informations à partir de données partielles de la base.
Imaginons par exemple que nous souhaitions obtenir des informations sur les ventes d’une marque donnée, pour tous les modèles confondus.
Pour cela, nous retrouvons une fois de plus un menu déroulant de sélection du modèle de voiture souhaité :
Pour commencer, nous allons souhaité obtenir le nombre modèles apparaissant dans ce TOP100 :
Nous allons donc devoir effectuer une recherche partielle dans les cellules de la colonne Modèle.
Dans la plupart des langages de programmation, nous pourrions utiliser les jokers, c’est-à-dire des symboles comme l’astérisque ou le point d’interrogation qui permettent de remplacer une ou plusieurs caractères dans une requête.
Nous reviendrons un peu tard sur l’utilisation de ces jokers, dont l’usage n’est pas permis dans la fonction SOMMEPROD().
Mais bien entendu, il existe une fonction qui va nous permettre d’effectuer une opération relativement proche.
Il s’agit de la fonction TROUVE(), qui est chargée de récupérer la position d’une chaîne au sein d’une chaîne principale.
Pour comprendre cette fonction, nous allons nous placer sur la cellule F15, qui est située à droite d’une cellule contenant effectivement un modèle de Peugeot :
Dans celle-ci, nous allons saisir la fonction TROUVE(), laquelle n’attend que deux arguments dans le cas présent :
- Tout d’abord le texte que nous souhaitons rechercher, nous pourrons donc simplement pointer sur la cellule G4 dans laquelle nous avons sélectionné la marque Peugeot,
- Puis en seconde position, la fonction TROUVE() le texte principal dans lequel la recherche doit être lancée, il s’git ici de la cellule B15
=TROUVE(G4;B15)
Excel nous retourne alors comme résultat la première position de la chaîne :
Maintenant, plaçons-nous sur la cellule située juste en dessous afin saisir à nouveau la fonction :
=TROUVE(G4;B16)
Cette fois-ci, Excel nous retourne une erreur, car il ne trouve pas le mot Peugeot dans la cellule B16 :
Grâce à la fonction TROUVE() nous pouvons donc déterminer si une cellule contient un texte donné ou non. Seulement, rappelez-vous, nous avons vu un peu tôt que la fonction SOMMEPROD() a besoin de valeurs VRAI et FAUX pour fonctionner.
Pour cela, nous allons devoir encapsuler la fonction TROUVE() au sein de la fonction ESTERREUR() qui va nous renvoyer la valeur VRAI lorsque la fonction passée en argument est effectivement une erreur, et FAUX dans le cas contraire.
Il suffira ensuite d’encapsuler à nouveau ce résultat dans la fonction NON() qui permet d’inverser les valeurs VRAI et FAUX :
=NON(ESTERREUR(TROUVE(G4;B15)))
Et voilà, il ne reste plus qu’à intégrer cette fonction dans la fonction SOMMEPROD() pour mettre en place la requête :
=SOMMEPROD((NON(ESTERREUR(TROUVE(G4;_base3[Modèle]))))*1)
Dans cette fonction, nous effectuons l’analyse sur l’ensemble des cellules de la colonne Modèle, et nous multiplions la matrice retournée par 1 afin qu’Excel effectue la somme des valeurs égales à VRAI.
8.2. Compter le nombre d'immatriculation
Maintenant que nous avons pu calculer le nombre de modèles de la marque sélectionnée, nous allons pouvoir adapter la fonction pour dénombrer le nombre de ventes correspondant.
Pour cela, nous effectuons un copier-coller de la fonction dans la cellule G7 :
Et il suffira ensuite de modifier la fin de la fonction, pour remplacer le « *1 » par la colonne dans laquelle « Nombre » :
=SOMMEPROD((NON(ESTERREUR(TROUVE(G4;_base3[Modèle]))))*_base3[Nombre])
Peugeot a donc vendu un peu moins de 300000 véhicules au cours de l’année 2020.
8.3. Compter le nombre de modèles de voitures dans le TOP10 pour une marque
Maintenant, nous souhaitons déterminer combien de véhicules de la marque sélectionnée apparaissent dans le TOP10.
Pour cela, nous allons partir du calcul précédent, dans lequel nous avions déterminé le nombre total de la marque, et nous allons simplement ajouter un paramètre supplémentaire dans la requête.
Comme nous l’avons vu plus haut, chaque paramètre est à saisir entre parenthèses, et à multiplier avec les autres.
Ici, ce paramètre va consister à regarder si la valeur de la colonne Position est inférieure ou égale à 10 :
=SOMMEPROD((NON(ESTERREUR(TROUVE(G4;_base3[Modèle]))))*(_base3[Position]<=10)*1)
Quatre modèles de chez Peugeot apparaissent dans les dix premières positions.
8.4. Rechercher un modèle en particulierCompter le nombre de modèles
Maintenant, nous allons reprendre nos requêtes pour prendre en compte en plus de la marque, un modèle de véhicule en particulier, par exemple ici la 208 :
Comme nous venons de le voir à l’instant, pour ajouter un paramètre supplémentaire dans la requête, il suffit de le multiplier aux paramètres déjà mis en place.
Ce nouveau paramètre est plus de cela extrêmement simple à mettre en place, il s’agit en effet de dupliquer la recherche de marque, en pointant sur la cellule G5 au lieu de G4.
La fonction pour obtenir le nombre de modèles proposés devient donc :
=SOMMEPROD((NON(ESTERREUR(TROUVE(G4;_base3[Modèle]))))*(NON(ESTERREUR(TROUVE(G5;_base3[Modèle]))))*1)
Le nombre de véhicules immatriculés :
=SOMMEPROD((NON(ESTERREUR(TROUVE(G4;_base3[Modèle]))))*(NON(ESTERREUR(TROUVE(G5;_base3[Modèle]))))*_base3[Nombre])
Et le nombre de véhicules dans le TOP10 :
=SOMMEPROD((NON(ESTERREUR(TROUVE(G4;_base3[Modèle]))))*(NON(ESTERREUR(TROUVE(G5;_base3[Modèle]))))*(_base3[Position]<=10)*1)
Vous noterez que pour cette dernière fonction, le nombre de paramètres passe donc à trois.
Sans grande surprise, il n’existe qu’un seul modèle de Peugeot 208 dans le classement, mais nous pouvons par exemple sélectionner la Renault Clio pour obtenir les ventes des modèles IV et V combinés :
Nous pouvons également nous rendre compte qu’Audi a écoulé 19300 véhicules de la gamme Q :
Alors bien entendu, nous aurions pu obtenir les mêmes résultats en utilisant d’autres fonctions dédiées d’Excel (respectivement les fonctions NB.SI.ENS() et SOMME.SI.ENS()), mais il est toujours intéressant de savoir maîtriser la fonction SOMMEPROD() qui est un véritable couteau Suisse, tant les possibilités offertes sont importantes.
D’autant plus que sur les anciennes versions d’Excel les fonctions énoncées ci-dessus ne sont pas disponibles, et seules les versions permettant l’utilisant d’un seul critère sont proposées.
Article initialement publié le 15 juillet 2019