Comment utiliser la formule SOMMEPROD d’Excel

Dans cet article, je vais vous présenter la formule 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 formule SOMMEPROD d'Excel ?

 

Vidéo 2 : Comment utiliser la formule SOMMEPROD étendue pour analyser des bases de données ?

 

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 formule SOMMEPROD

La formule 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 formule 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 formule SOMMEPROD().

Attention, voici deux points à prendre en compte lorsque l’on utilise la formule 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 formule SOMMEPROD, nous allons pouvoir l’illustrer à travers l’exemple suivant :

Excel formation - La formule SOMMEPROD - 01

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) :

Excel formation - La formule SOMMEPROD - 02

 

 =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 formule 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 :

Excel formation - La formule SOMMEPROD - 03

Cela permet évidemment de retrouver le même résultat (18615€), mais nécessite d’ajouter une colonne et d’y insérer des formules, ce qui demande d’y consacrer un temps non négligeable.

Soit d’utiliser la formule 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) :

Excel formation - La formule SOMMEPROD - 04

 

=SOMMEPROD(B7:B12;C7:C12) 

Excel va alors réaliser en une seule formule 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 formule SOMMEPROD(), Excel réalise alors la somme de chacun de ses éléments. Cela revient donc au même qu’utiliser la formule SOMME :

Excel formation - La formule SOMMEPROD - 05

 

5. Utiliser SOMMEPROD avec plus de deux matrices

Nous l’avons déjà abordé dans la présentation de la formule SOMMEPROD, mais il est possible d’utiliser jusque 255 matrices dans la formule 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 :

Excel formation - La formule SOMMEPROD - 06

 

 =SOMMEPROD(B8:B13;C8:C13;D8:D13) 

 

 

6. Bonus : Effectuer des calculs directement dans la formule SOMMEPROD()

Enfin, pour finir sur ce tour d’horizon concernant la formule SOMMEPROD, sachez qu’il est tout à fait possible de réaliser des calculs très simple sur les matrices de la formule SOMMEPROD().

Par exemple, il va être possible de calculer une remise à la ligne :

Excel formation - La formule SOMMEPROD - 07

 =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 :

Excel formation - La formule SOMMEPROD - 08

=SOMMEPROD(B8:B13;C8:C13;100%-D8:D13;100%+E8:E13)

 

7. … et maintenant la formule 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 formule !

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 :

Excel formation - La formule SOMMEPROD étendue - 02

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 formules écrites justement pour effectuer des traitements sur les bases de données (en commençant par la formule 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 formule 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 formules, nous allons commencer par transformer la base de données, en un tableau de données (cliquez ici pour en savoir plus).

Excel formation - La formule SOMMEPROD étendue - 03

Puis nous allons nommer ce tableau « _base » :

Excel formation - La formule SOMMEPROD étendue - 04

Ce premier exemple va nous permettre de rapidement comprendre le fonctionnement de la formule SOMMEPROD() :

Excel formation - La formule SOMMEPROD étendue - 05

 =SOMMEPROD((_base[Magasin]="Paris")*_base[Montant]) 

Cette formule est alors très claire, nous pourrions également utiliser la formule 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 formule (Menu Formule > Évaluer la formule) :

Tout d’abord la formule commence par analyse la matrice à tester :

Excel formation - La formule SOMMEPROD étendue - 06

Puis lorsque la valeur répond à la condition posée, chaque élément va prendre pour valeur VRAI, et FAUX dans le cas contraire :

Excel formation - La formule SOMMEPROD étendue - 07

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

Excel formation - La formule SOMMEPROD étendue - 08

Ne reste alors plus qu’à retourner la somme de ces valeurs :

Excel formation - La formule SOMMEPROD étendue - 09

 

    7.2. Utiliser un filtre complexe, avec une formule

 

L’intérêt de la formule SOMMEPROD() par rapport à une formule plus classique comme SOMME.SI() est de pouvoir effectuer des tests matriciels directement dans la formule !

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 formule 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) 

 

Excel formation - La formule SOMMEPROD étendue - 10

 

 



Articles qui pourraient vous intéresser

Comment utiliser la formule SOMMEPROD d’Excel

Les formules logiques ET(), OU() et OUX() d’Excel pour combiner des tests

Comment arrondir un nombre sur Excel (mise-en-forme ou formule : la bonne méthode)

Comment utiliser la formule MOD() pour calculer le reste d'une division euclidienne sur Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.