La fonction matricielle SOMMEPROD d'Excel

La fonction SOMMEPROD dans Excel cache quelques secrets. En effet, la version décrite et affichée par Excel n'est en fait qu'une seule de ses formes; cette fonction possédant une autre forme pour une utilité toute autre.

La fonction SOMMEPROD telle qu'affichée par Excel

La fonction Sommeprod sous sa forme de base est peu utilisée, à moins d'être dans des domaines bien précis de sciences et de mathématiques, dû à ce qu'elle fait.

En effet, celle-ci permet de multiplier deux matrices, bien que ce type est peu intéressant.

 

Sa structure est:

=SOMMEPROD (Plage1 ; Plage2)

 

Exemple de calcul :

=SOMMEPROD(A2:A3 ; B2:B3)

Le résultat de cette formule serait donc égal à:

(A2 x B2) + (A3 x B3)

La face cachée de la fonction SOMMEPROD

Cette variante de la fonction SOMMEPROD est vraiment très utile pour les utilisateurs d’Excel, bien qu’elle soit méconnue par la plupart des utilisateurs et presque jamais enseignée. Lorsque nous avons une base de données qu’elle soit saisie ou bien importée dans Microsoft Excel à partir d’Access Fichier Txt ou de la plupart des entrepôts de données et de systèmes comptables, elle fait la somme d’une ou plusieurs colonnes lorsque les conditions sont vraies dans d’autres colonnes.

 

Sa structure est:

= SOMMEPROD((Plage = Condition)*(plage à additionner))

 

Dans cette fonction le signe * est un séparateur d’arguments et non un signe de multiplication.

 

Il n’y a pas de limite au nombre de conditions utilisées.

= SOMMEPROD((Plage = Condition)*(Plage2 = Condition2)*(Plage3 = Condition3)*(Plage4 = Condition4)*(Plage5 = Condition5)*(plage à additionner))

D’autres opérateurs mathématiques peuvent être utilisés dans les conditions tels que ( < > = ).

 

Il est très important que les différentes plages faisant partie des conditions et la plage à additionner incluent les mêmes nombres d’enregistrements « Nombre de lignes ».

Prenons par exemple cette base de données des ventes pour un magasin de vêtements :

Exemple fonction SOMMEPROD base de données - Excel Québec

Exemple 1:

Si nous voulons avoir le total des ventes de chandails:

 

SOMMEPROD((B2 :B22= " Chandail ")*(F2 :F22))

Résultat = 112,75 $

 Ce qui équivaut à dire:

Fait la somme de la plage F2 à F22 chaque fois que le produit vendu dans la plage B2 à B22 est un « Chandail ».

Exemple 2:

Si nous voulons avoir le total des achats de François:

 

SOMMEPROD((C2 :C22)= " François ")*(F2 :F22))

Résultat = 35,69 $

 Ce qui équivaut à dire:

Fait la somme de la plage F2 à F22 chaque fois que le client dans la plage C2 à C22 est « François ».

Exemple 3:

Si nous voulons avoir les achats de chandails de François:

 

SOMMEPROD((B2 :B22= " Chandail "*(C2 :C22= " François ")*(F2 :F22))

Résultat = 20,50 $

 Ce qui équivaut à dire:

 Fait la somme de la plage F2 à F22 chaque fois que le client dans la plage C2 à C22 est «François» et la plage B2 à B22 est un chandail.

 

Veuillez noter que dans cet exemple, il y a deux conditions.

Exemple 4:

Nous pouvons aller plus loin en remplaçant des conditions par une liste déroulante qui inclut plusieurs possibilités.

 

Imaginez que dans un autre onglet « feuil2 », la cellule C3 de l’onglet, nous avons inséré une liste déroulante qui inclut tous nos clients, et que, dans le bas de cette page, nous pouvons préparer le rapport suivant :

Exemple fonction SOMMEPROD utilisation - Excel Québec

La formule dans la cellule D7 :

=SOMMEPROD((Feuil1!$C$2:$C$22=$C$3)*(Feuil1!$B$2:$B$22=C7)*(Feuil1!$D$2:$D$22))

 

La formule dans la cellule E7 :

=SOMMEPROD((Feuil1!$C$2:$C$22=$C$3)*(Feuil1!$B$2:$B$22=C7)*(Feuil1!$F$2:$F$22))

 

Vous remarquerez que chaque fois que l’usager choisit un client dans la liste déroulante en C3 le rapport est mis à jour automatiquement.

Si le nombre d’enregistrements dans la base de données est en croissance constante comme dans notre exemple où il s’agit de ventes, il suffit d’augmenter d’une manière considérable les différentes plages dans les formules.

 

Par exemple la formule :

SOMMEPROD((Feuil1!$C$2:$C$22=$C$3)*(Feuil1!$B$2:$B$22=C7)*(Feuil1!$D$2:$D$22))

 

Devient

SOMMEPROD((Feuil1!$C$2:$C$2200=$C$3)*(Feuil1!$B$2:$B$2200=C7)*(Feuil1!$D$2:$D$2200))