Moyenne pondérée avec condition

Après avoir vu comment calculer une moyenne pondérée en utilisant une formule qui se compose des deux fonctions SOMMEPROD et SOMME, aujourd’hui et dans cette formation Excel, vous allez découvrir comment calculer une moyenne pondérée qui répond à certains critères en utilisant différemment la fonction SOMMEPROD.

SOMMEPROD conditionnelle

Nous allons d’abord expliquer une deuxième façon d’utiliser la fonction SOMMEPROD qui va nous permettre ensuite de créer la formule que nous allons utiliser pour calculer une moyenne pondérée avec condition.

Comme vous l’avez vu dans l’article Formule de la moyenne pondérée, la fonction SOMMEPROD permet de calculer la somme des produits des valeurs par leurs poids, en outre, vous pouvez utiliser la fonction SOMMEPROD également pour n’effectuer ce calcul que sur quelques valeurs qui répondent à certaines conditions.

Regardez l’exemple suivant, c’est un tableau qui représente une liste des accessoires des smartphones avec leurs quantités et prix de vente. Téléchargez-le ici : Accessoires smartphones

Nous voulons par exemple trouver le nombre de Housses achetées en utilisant la fonction SOMMEPROD, c’est à dire calculer la somme des quantités correspondant à l’accessoire Housse.

Si nous appliquons un filtre sur les données de ce tableau par Housse et calculer la somme de ses quantités, nous aurons cet affichage :

Le nombre de Housses achetées est donc 210.

Utilisons maintenant SOMMEPROD.

Dans ce cas nous devons utiliser la deuxième syntaxe de la fonction SOMMEPROD (SOMMEPROD conditionnelle) que nous pouvons l’écrire de cette façon :

SOMMEPROD(TEST_LOGIQUE1 * TEST_LOGIQUE2 * PlageValeur)

Les arguments Test_logiques doivent être des formules qui, en les effectuant, renvoient soit la valeur Vrai, soitla valeur Faux.
PlageValeur : c’est la référence de la plage de cellules qui contient les valeurs numériques dont on veut calculer la somme.
* : bien sûr c’est l’opérateur de multiplication.

Note : notez bien que nous n’avons pas utiliser les points-virgules dans cette syntaxe.

Passons à la pratique :

Sélectionnez une cellule de votre feuille de calcul.
Tapez la formule suivante :

=SOMMEPROD((A2:A13="Housse")*B2:B13)

Le résultat est donc : 210

Expliquons bien les choses :

La fonction SOMMEPROD vérifie en premier l’existence des cellules qui contiennent la valeur Housse dans la plage de cellules A2:A13 et ceci en effectuant la formule A2:A13="Housse".
Et puisqu’il en existe 3, elle renvoie la valeur logique Vrai pour chacune. Regardez l’exemple suivant dans lequel nous avons appliqué uniquement la formule =A2:A13="Housse".

Ensuite, SOMMEPROD passe au deuxième argument B2:B13 qui est ici la plage des valeurs à sommer, et bien sûr ce sont les quantités correspondant aux 3 valeurs trouvées. (Housse)
Enfin, la fonction SOMMEPROD calcule la somme des produits comme ça :

(Vrai*50)+(Vrai*60)*(Vrai*100)

Dans Excel, la valeur Vrai est égale à 1 et la valeur Faux est égale à 0.

Alors, notre formule s’effectuera de cette façon : (1*50)+(1*60)+(1*100), ce qui donne le résultat 210.

Et voilà donc, nous avons montré comment calculer la somme des produits des valeurs par leurs poids en répondant à un seul critère.

Vous pouvez aussi utiliser cette fonction pour effectuer un calcul qui répond à plusieurs conditions ou critères.

Dans l’exemple suivant, nous voulons calculer le nombre total des Housses achetées du smartphone iPhone.

Sélectionnez une cellule vide de votre feuille de calcul et tapez la formule suivante :

=SOMMEPROD((A2:A13=A2)*(B2:B13=B2)*C2:C13)

Utilisation de SOMMEPROD avec plusieurs conditions

Comme vous pouvez le remarquez, au lieu d’écrire le mot iPhone dans le premier test_logique, nous avons tapez la référence de la cellule qui le contient : A2
Aussi, nous avons introduit la référence de la cellule B2 dans le deuxième test_logique au lieu de taper Housse.

Moyenne pondérée avec condition

Maintenant vous allez voir un exemple qui vous montrera comment calculer une moyenne pondérée en répondant à une condition donnée.

En reprenant l'exemple du tableau précédent, nous souhaitons calculer le prix de vente moyen de l’article Coque en utilisant la fonction SOMMEPROD et en appliquant ce qui est vu en haut.

Le principe alors est de calculer la somme des produits Qte*Prix de vente de l’article Coque puis de diviser cette somme par le total des quantités correspondant au même article.

Sélectionnez la cellule qui va afficher le résultat du calcul.
Tapez la formule suivante :

=SOMMEPROD((A2:A13="Coque")*B2:B13*C2:C13)/SOMMEPROD((A2:A13="Coque")*B2:B13)

Calcul d'une moyenne pondérée avec condition

Notre formule se compose et comme vous le remarquez de deux parties :

SOMMEPROD((A2:A13="Coque")*B2:B13*C2:C13) qui calcule la somme des produits Qte*Prix de vente de l’article Coque.
SOMMEPROD((A2:A13="Coque")*B2:B13) : calcule la somme des quantités correspondant à l’article Coque.

En effectuant la formule, Excel affiche le prix moyen de vente suivant: 17,39

En suivant la même procédure, calculons à présent le prix de vente moyen de l’accessoire Coque du smartphone iPhone :