Fonction SOMMEPROD

Description de la fonction SOMMEPROD

 

Multiplie les valeurs correspondantes des matrices spécifiées et calcule la somme de ces produits.

Cette fonction est très puissante et très intéressante.

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, elle fait la somme d’une ou plusieurs colonnes lorsque les conditions sont vraies dans d’autres colonnes.

 

Je vais vous expliquer tout cela mais tout d'abord nous allons construire un fichier de données très rapidement comme exemple de travail.

Donc dans votre fichier Excel :

1) En cellule A1 écrivez  COMMERCIAL

    En plage de cellules I1:I8 écrivez par exemple les noms suivant :

LAMBERT

MARTIN

DURAND

DUBOIS

DUPONT

ROUX

MOREL

En cellule A2 insérez la formule suivante :

=SIERREUR(INDEX($I$1:$I$7;PETITE.VALEUR(SI($I$1:$I$7<>"";LIGNE($I$1:$I$7));ENT(ALEA()*NBVAL($I$1:$I$7)+1)));"")

 

Validez-la avec CTRL+MAJ+ENTREE et incrémenter cette cellule vers le bas jusqu'à la ligne 100.

Les noms des commerciaux vont apparaître en aléatoire dans cette colonne.

2) En B1 écrivez DATE

    En J1 inscrivez la date du  01/01/2016

    En J2 inscrivez la date du  31/12/2017

    

    En B2 insésez la formule suivante

    =ALEA.ENTRE.BORNES($J$1;$J$2)

 

Incrémenter cette cellule vers le bas jusqu'à la ligne 100. 

Ensuite appliquez le format cellule DATE à cette plage qui mentionnera des dates aléatoires.

3) En C1 mentionnez CHIFFRE

     En K1 inscrivez le chiffre 500

     En K2 inscrivez le chiffre 5000

 

     En C2 insérez la formule suivante =ALEA.ENTRE.BORNES($K$1;$K$2)

Incrémenter cette cellule vers le bas jusqu'à la ligne 100. 

Cette plage de cellules mentionnera des chiffres aléatoires.

 

4) Et pour finir sélectionnez l'ensemble des données construites (Plage A1:C100) puis Copier et effectuer un Collage spécial en choisissant valeurs. L'ensemble des données ne feront plus référence à des formules.

 

Voici votre fichier prêt à exploiter la fonction SOMMEPROD.

 

La structure de la fonction est :
= SOMMEPROD((Plage = Condition)*(plage à additionner))

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

Exemple concret (simple soit une seule condition) :

Dans une celle adjacente aux données, nous allons calculer le chiffre d'affaire de MARTIN  soit

 

a) Plage = Condition

Plage => La colonne Commercial (A2:A100)

Condition => Nom du commercial entre guillemets "MARTIN"

soit  :  = SOMMEPROD((A2:A100 = "MARTIN")

b) Plage à additionner

    Egale à la colonne Chiffre (C2:C100)

Soit au final  = SOMMEPROD((A2:A100 = "MARTIN")*(C2:C100))

Nous pouvons aussi faire une référence à une cellule par MARTIN par exemple
= SOMMEPROD((A2:A100 = E2)*(C2:C100))

Si MARTIN est placé en E2

 

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 ».

 

Comme vous pouvez le devinez on peut réaliser des sommes de données en fonction de critères, par exemple :

* le chiffre de MARTIN en général (exemple précédent)

* le chiffre de MARTIN en 2016 ou en 2017

* le chiffre de MARTIN au 1er ou 2ème ou 3ème ou 4ème trimestre d'une année donnée (2016 ou 2017)

* le chiffre de MARTIN pour un mois donnée d'une année donnée (Janvier 2016 par exemple)

* Le chiffre de MARTIN inférieur ou supérieur à chiffre calculé  par an ou par trimestre ou par mois voire même semaine

 

et s'il y avait d'autres colonnes d'informations, on pourrait ajouter des critères supplémentaires... Wouha ! ça décoiffe.

 

On va développer la fonction SOMMEPROD avec les critères précédemment cités.

 

1) Somme du chiffre de MARTIN pour 2016 et  2017

Dans les cellules suivantes :

E3 ecrivez => MARTIN

F2 inscrivez la date =>  01/01/2016 

G2 inscrivez la date =>  01/01/2017

Maintenant insérez la formule suivante en F3 :

=SOMMEPROD((ANNEE($B$2:$B$100)=ANNEE(F2))*($A$2:$A$100 =$E$3)*($C$2:$C$100))

Ensuite incrémentez cette cellule vers la droite en G3

Maintenant vous avez le chiffre de Martin pour les années 2016 et 2017.

 

2) Somme du chiffre de MARTIN pour les différents trimestre de 2016 

Dans les cellules suivantes :

E3 ecrivez => MARTIN

F2 inscrivez la date =>  01/01/2016 

Maintenant insérez : 

 

La formule suivante en F3 :

=SOMMEPROD(((MOIS($B$2:$B$100)<4)*(ANNEE($B$2:$B$100)=ANNEE($F$2))*($A$2:$A$100 =$E$3)*($C$2:$C$100)))

Pour info mois inférieurs à 4

 

La formule suivante en G3 :

=SOMMEPROD(((MOIS($B$2:$B$100)>3)*(MOIS($B$2:$B$100)<7)*(ANNEE($B$2:$B$100)=ANNEE($F$2))*($A$2:$A$100 =$E$3)*($C$2:$C$100)))

Mois supérieurs à 3 et inférieurs à 7

 

La formule suivante en H3 :

=SOMMEPROD(((MOIS($B$2:$B$100)>6)*(MOIS($B$2:$B$100)<10)*(ANNEE($B$2:$B$100)=ANNEE($F$2))*($A$2:$A$100 =$E$3)*($C$2:$C$100)))

Mois supérieurs à 6 et inférieurs à 10

 

La formule suivante en I3 :

=SOMMEPROD(((MOIS($B$2:$B$100)>9)*(ANNEE($B$2:$B$100)=ANNEE($F$2))*($A$2:$A$100 =$E$3)*($C$2:$C$100)))

Mois supérieurs à 9

 

Maintenant vous avez le chiffre de Martin pour les années 2016 et par trimestre. Vous pouvez opter pour les résultats de 2017 dans d'autres cellules sur la base présenté ci-dessus.

3) Somme du chiffre de MARTIN pour les différents mois de 2016 

Dans les cellules suivantes :

E3 ecrivez => MARTIN

F2 inscrivez la date =>  01/01/2016

Maintenant insérez La formule suivante en F3 :

=SOMMEPROD(((MOIS($B$2:$B$5000)=1)*(ANNEE($B$2:$B$5000)=ANNEE(F2))*($C$2:$C$5000)))

MOIS($B$2:$B$5000)=1 correspond à 1 soit janvier.

Pour les autres cellules changer ce chiffre pour obtenir les résultats du mois souhaité.

 

4) Somme du chiffre de MARTIN inférieur à une certaine somme  pour une année définie 

Dans les cellules suivantes :

E3 ecrivez => MARTIN

F2 inscrivez le chiffre de référence  =>  1000

G2 inscrivez une date   =>  01/01/2016

Maintenant insérez La formule suivante en F3 :

=SOMMEPROD((A2:A100=E3)*(C2:C100<F2)*(ANNEE($B$2:$B$100)=ANNEE(G2)))

Ce qui correspond au nombre de chiffre inférieur à 1000

Maintenant insérez La formule suivante en G3 :

=SOMMEPROD((A2:A100=E3)*($C$2:$C$100<F2)*(ANNEE($B$2:$B$100)=ANNEE(G2))*($C$2:$C$100))

Ce qui correspond à la somme des chiffres inférieurs à 1000

 

Donc on peut aussi paramétrer la fonction avec >=  ou <= ou = ou <> et combiner avec les sommes par trimestre ou autres.