Moyenne réduite

Aujourd’hui dans cette formation Excel, vous allez apprendre à utiliser la fonction MOYENNE.REDUITE pour calculer la moyenne réduite d’une plage de données.
Moyenne réduite


La moyenne réduite ou la moyenne tronquée permet de calculer la moyenne de la partie centrale des données dans une plage de cellules tout en en excluant les valeurs les plus extrêmes considérées comme aberrantes, c’est-à-dire les valeurs les plus petites et les plus élevées.

Exemple d’utilisation de la moyenne réduite

Le tableau suivant présente le nombre de tasses de café bues dans une journée :

Nombre de tasses de café bues


Remarquez les nombres aberrants dans cette plage de cellules ou utilisez l’outil de Mise en forme conditionnelle pour les déterminer :

Valeurs aberrantes dans un tableau de données


  • Sélectionnez la plage de cellules A2:A21 puis dans le groupe Style sous l’onglet Accueil, cliquez sur Mise en forme conditionnelle et choisissez Barres de données puis cliquez sur Barre de données rouge par exemple.

Appliquer une mise en forme conditionnelle



Voilà donc, ces barres de données vont vous aidez à trouver les valeurs les plus petites et les valeurs les plus élevées facilement:

Barre de données Mise en forme conditionnelle


  • Les barres les plus courtes se trouvent dans les deux cellules A7 qui contient la valeur 5 et A15 contenant le nombre 4.
  • Quant aux barres les plus longues, on les trouve dans les deux cellules A11 qui contient le nombre 98 et A18 qui contient le nombre 100.

Alors, vous avez maintenant les deux valeurs les plus petites et les deux valeurs les plus grandes, ce qui donne en total 4.

On dit donc qu’on a 4 valeurs parmi 20 observations (20 lignes).

Note : si vous avez un tableau trop grand, vous pouvez savoir le nombre de lignes de la plage de cellules contenant les valeurs dont vous voulez calculer la moyenne réduite, en utilisant la fonction NB.

Comment utiliser la fonction NB

  • Alors pour déterminer le nombre de lignes de la plage de cellules A2:A21 dans notre cas, sélectionnez cette plage à l’aide de la souris ou sélectionnez la première cellule A2 puis composez Ctrl+Maj+Flèche pointant vers le bas.
  • La barre d’état affiche alors le nombre de lignes en utilisant la fonction NB.
Utilisation rapide de la fonction NB



Comment utiliser la fonction MOYENNE.REDUITE

Excel fournit une fonction statistique qui permet de calculer la moyenne réduite ou la moyenne tronquée nommée MOYENNE.REDUITE.

Note : la moyenne réduite dans Excel anglais est TRIMMEAN

Voici la syntaxe de la fonction MOYENNE.REDUITE
MOYENNE.REDUITE(matrice, pourcentage)

  • Matrice : c’est la plage de cellules contenant les valeurs dont on veut calculer leur moyenne réduite. Dans notre exemple c’est A2:A21
  • Pourcentage : le nombre fractionnaire d’observations à exclure du calcul. Dans notre exemple nous avons trouvé 4 valeurs à exclure des 20 observations, et pour calculer le pourcentage, on procède à l’opération de division 4/20 ce qui donne 0,2 (20%).

Note : Pourcentage doit être supérieur ou égale à 0 (>=0) et strictement inférieur à 1 (<1), si non la fonction MOYENNE.REDUITE renvoie la valeur d’erreur #NOMBRE!.

Appliquons maintenant la fonction MOYENNE.REDUITE :

  • Sélectionnez une cellule vide, par exemple C6, et entrez la formule suivante : =MOYENNE.REDUITE(A2:A21;0,2)
Fonction MOYENNE REDUITE

  • La moyenne réduite est donc 34,38

Vous pouvez effectuer un calcul de moyenne arithmétique sur ces valeurs en excluant les éléments les plus volatiles pour vous assurer que ce résultat est correct :
  • Sélectionnez la plage de cellules A2:A21 et appliquez en un tri croissant par exemple.
Bouton Tri sous l'onglet Données

  • Vous remarquez alors que les valeurs les plus petites occupent les deux premières cellules et les deux valeurs les plus grandes occupent les deux dernières cellules en bas.
Résultat du tri



  • Sélectionnez une cellule vide (par exemple A23) et tapez la formule suivante : =MOYENNE(A4:A19)
Utilisation de la fonction Moyenne

  • Remarquez la référence utilisée désignant la plage de cellules cible A4 :A19
  • Excel affiche le même résultat donc : 34,38


Moyenne des N valeurs les plus grandes

Calculer la moyenne sans condition est une opération très facile à effectuer et nous l’avons déjà expliquée en utilisant la fonction Moyenne dans l’article nommé : Calculer la moyenne des données filtrées ou non-filtrées en utilisant la fonction MOYENNE
Aujourd’hui nous allons voir dans cette formation Excel comment calculer la moyenne des N valeurs les plus grandes (par exemple la moyenne des cinq valeurs les plus grandes ou la moyenne des dix valeurs les plus grandes) et ceci en utilisant la fonction Moyenne.
Moyenne des N valeurs plus grandes


Voyons ces deux exemples, et découvrons ensemble comment les choses se dérouleront dans le reste de l’article :

Calculer la moyenne des cinq valeurs les plus grandes

Dans l’exemple suivant, nous avons dix nombres non triés dans la plage de cellules A1:A10 :

Tableau de 10 valeurs non triées


  • Téléchargez le classeur exemple ici : Moyenne des N valeurs les plus grandes
  • Ouvrez le classeur téléchargé et Sélectionnez une cellule vide, par exemple C2
  • Entrez la formule suivante : =MOYENNE(GRANDE.VALEUR(A1:A10; {1;2;3;4;5}))
  • Et appuyez sur Ctrl+Maj+Entrée puisqu’il s’agit d’une formule matricielle.
  • Excel affiche 362


Moyenne des cinq valeursles plus grandes


Calculer la moyenne des 10 valeurs les plus grandes

  • Passez maintenant à la deuxième feuille du classeur ouvert.
  • Entrez la même formule précédente mais cette fois en remplaçant la plage de cellules A1:A10 par A1:A20, et {1;2;3;4;5} par LIGNE(1 :10).
  • Votre formule s’écrira comme ça : =MOYENNE(GRANDE.VALEUR(A1:A20;LIGNE(1:10)))
  • Composez ensuite Ctrl+Maj+Entrée.
  • Excel vous affiche 3221,2


Moyenne des 10 valeurs plus grandes


Explication des formules utilisées:

Comment trouver les cinq valeurs les plus grandes dans une plage de cellules ?

Pour calculer donc la moyenne des cinq valeurs les plus grandes dans une plage de cellules, Excel suit la procédure suivante :
  • Il doit d’abord localiser les cinq grandes valeurs puis en calculer la moyenne.

C’est pour cela que nous avons utilisé la fonction GRANDE.VALEUR et nous l’avons intégrée dans la fonction MOYENNE.

Comment utiliser la fonction GRANDE.VALEUR ?

La fonction GRANDE.VALEUR est une fonction qui permet de trouver la valeur la plus élevée dans une plage de cellule.
Sa syntaxe s’écrit comme ça : GRANDE.VALEUR(matrice,k)
  • Matrice : La plage de cellules dans laquelle vous cherchez la k-ième plus grande valeur.
  • : représente la position de la valeur recherchée en partant de la valeur la plus grande.

Exemple

  • Sélectionnez la première feuille du classeur téléchargé, et entrez cette formule dans la cellule D1

=GRANDE.VALEUR(A1:A10;2)

Utiliser la fonction GRANDE VALEUR


  • Ici, on demande à Excel de nous renvoyer la deuxième valeur la plus grande dans la plage de cellules A1:A10 ; ce qui donne comme résultat 550.
Revenons maintenant à notre exemple de début et cherchons les cinq valeurs les plus grandes en utilisant GRANDE.VALEUR :
  • On doit apporter une petite modification au deuxième argument de la fonction GRANDE.VALEUR pour obtenir le résultat cherché, et ceci en entrant une matrice écrite de la façon suivante : {1;2;3;4;5}.
  • Dans ce cas Excel va renvoyer le résultat sous forme de tableau de cinq lignes et d’une colonne.
Tout d'abord, suivez l'exemple suivant pour tester l’utilisation de la matrice {1;2;3;4;5} :
  • Sélectionnez cinq cellules verticalement D1:D5, et tapez dans la barre de formule : ={1;2;3;4;5} puis appuyez sur Ctrl+Maj+Entrée


Exemple d'une formule matricielle


  • Vous obtenez donc un tableau contenant des chiffres de 1 à 5.

Alors, en utilisant la fonction GRANDE.VALEUR de la façon suivante : =GRANDE.VALEUR(A1:A10; {1;2;3;4;5}) et dans la même plage de cellules D1:D5, Excel va chercher les cinq grandes valeurs dans la plage de cellules A1:A10 et va les afficher triées de la première grande valeur à la  cinquième grande valeur.
  • Dans la cellule D1, Excel affichera la première grande valeur.
  • Dans la cellule D2, il affichera la deuxième grande valeur…
  • Et ainsi de suite jusqu’à la cinquième grande valeur.

Sélectionnez donc cinq cellules verticalement comme vous l’a fait avant (D1:D5) et entrez la formule précédente puis tapez Ctrl+Maj+Entrée

utiliser GRANDE VALEUR pour afficher les cinq grandes valeurs


Remarquez donc qu’Excel affiche les nombres trouvés et les trie du plus grand au plus petit comme nous l’avons mentionné.

Il ne reste à ce stade qu’introduire la fonction MOYENNE pour calculer la moyenne de ces valeurs trouvées.
  • Entrez enfin la formule suivante =MOYENNE(GRANDE.VALEUR(A1:A10; {1;2;3;4;5}))
  • Et eppuyez sur Ctrl+Maj+Entrée
  • Voilà donc le résultat souhaité.


Moyenne des cinq valeursles plus grandes


Passons à présent à la deuxième formule pour Calculer la moyenne des 10 valeurs les plus grandes

La formule utilisée était =MOYENNE(GRANDE.VALEUR(A1:A20;LIGNE(1:10)))

C’est pareil à la première formule, à l’exception d’une petite différence, outre la référence de la plage de cellules, qui est l’intégration de la fonction LIGNE à la place de la matrice {1;2;3;4;5}.

En effet, dans cet exemple nous voulons calculer la moyenne des dix plus grandes valeurs, alors nous devons utiliser la matrice {1;2;3;4;5;6;7;8;9;10} . Cette écriture pareille trop longue. (N'est-ce pas !!)

Alors pour éviter cette façon d’écrire la matrice et surtout si le nombre des valeurs recherchées est élevé, on a recouru à l’utilisation de la fonction LIGNE

Comment utiliser la fonction LIGNE ?

La fonction LIGNE renvoie le numéro de ligne d’une référence.
  • Sa syntaxe est : LIGNE([référence])

Référence : peut référencer une cellule, une plage de cellules ou une matrice (tableau).

Exemple

  • Sélectionnez la feuille 2 du classeur téléchargé.
  • Tapez dans la cellule D2 la formule suivante : =LIGNE(A2)


utilisation de la fonction LIGNE


  • Excel renvoie donc le numéro de la cellule référencée qui est 2.

Sélectionnez maintenant la plage de cellules D1:D10 et entrez la formule suivante dans la barre de formule =LIGNE(1:10) pour connaître ce qu'elle va donner.
Puis tapez Ctrl+Maj+Entrée

Utilisation de la fonction LIGNE avec matrice


Excel affiche donc un tableau vertical de dix chiffres consécutifs de 1 à 10.

Ce tableau, sera utilisé comme deuxième argument de la fonction GRANDE.VALEUR pour chercher les 10 valeurs les plus élevées dans la plage de cellules A1:A20
  • Sélectionnez la plage de cellules D1:D10 et tapez dans la barre de formule la formule suivante : =GRANDE.VALEUR(A1:A20;LIGNE(1:10))
  • Appuyez ensuite sur Ctrl+Maj+Entrée
  • Voici le résultat affiché


La fonction LIGNE intégrée dans la fonction GRANDE VALEUR


  • A l’instar du premier exemple des 5 valeurs les plus grandes, introduisez à présent la fonction MOYENNE et tapez dans la cellule C3 :
=MOYENNE(GRANDE.VALEUR(A1:A20;LIGNE(1:10)))
  • Et voici le résultat donné.


Moyenne des 10 valeurs plus grandes


Note : dans la barre de formule sélectionnez la partie LIGNE(1:10) et tapez F9, vous découvrez donc les valeurs renvoyées par la fonction LIGNE et qui sont utilisées dans cette formule.

Matrice introduite dans la fonction LIGNE


Moyenne si différent de 0 ou Moyenne sans 0 !!

Ça peut vous paraître un peu étrange ce titre, cependant ce sont des questions qui se posent très souvent par des utilisateurs qui veulent savoir comment calculer la moyenne des nombres en ignorant le zéro 0, et c’est la réponse à ces questions que vous allez découvrir dans l’article présent de la formation Excel. Ce sujet va nous mener aussi à découvrir comment calculer la moyenne des valeurs en répondant à une ou plusieurs conditions tout en étudiant les deux fonctions MOYENNE.SI et MOYENNE.SI.ENS.
Moyenne si différent de 0 ou moyenne sans 0


Nous avons vu dans l’article : Calculer la moyenne des données filtrées ou non-filtrées en utilisant la fonction MOYENNE, comment calculer une moyenne arithmétique d’une plage de cellules. Mais si cette plage de cellules contient des zéros que va-t-il se passer ?

Suivons cet exemple :(lien de téléchargement du fichier est en bas de l'article)

Moyenne avec 0


En effectuant la fonction Moyenne, Excel calcule la moyenne de tous les nombres en incluant le zéro aussi. Alors que moi je veux que mon calcul ignore cette valeur nulle.

La solution donc est d’employer la fonction MOYENNE.SI.

Mais avant d’expliquer la façon d’utiliser cette fonction, j’aimerais attirer votre attention sur deux choses à propos de la valeur 0 :

La première :quand une cellule ne contient rien, cela ne signifie pas qu’elle contient une valeur nulle. Remarquez dans l’illustration suivante que lorsque je supprime le zéro de la cellule A5, la fonction Moyenne ne prend pas en compte cette cellule devenue vide.

Moyenne avec cellule vide


La deuxième, vous pouvez remarquer que lorsque vous utilisez une fonction Moyenne et que la plage de cellules référencée dans cette fonction contient des cellules vides, vous remarquez que votre moyenne prend en compte ces cellules vides. Ceci explique alors qu’Excel les traite comme des cellules contenant des valeurs nulles même s’il n’affiche pas un zéro 0.

Cellule contenant une valeur nulle mais elle est vide


Dans ce cas vérifiez si l’option Afficher un zéro dans les cellules qui ont une valeur nulle est désactivée.
  • Et pour l’activer, cliquez sur le menu Fichier et choisissez Options.
  • Sélectionnez ensuite Options avancées.
  • Puis dans la catégorie Options d’affichage de la feuille de calcul, cochez Afficher un zéro dans les cellules qui ont une valeur nulle.
Afficher un zéro dans les cellules qui ont une valeur nulle



Comment utiliser la fonction MOYENNE.SI ?

La présence de SI dans une fonction Excel, signifie toujours que cette fonction va traiter des valeurs pour afficher un résultat qui répond à une ou plusieurs conditions.

En reprenant l’exemple précédent, notre but était de calculer la moyenne des valeurs de la plage de cellules A1:A5 en ignorant la valeur nulle (Zéro).

Syntaxe de la fonction MOYENNE.SI 

La syntaxe de la fonction MOYENNE.SI comme elle est définie par Excel est :
MOYENNE.SI(plage; critères; [plage_moyenne])

Comme vous le voyez, la fonction MOYENNE.SI contient trois arguments :
  • Plage : vous devez définir pour cet argument la référence de la plage de cellules contenant les valeurs parmi lesquelles on va chercher le critère souhaité.
  • Critères : c’est votre condition à réaliser.
  • plage_moyenne : c’est la plage qui contient les valeurs dont on veut calculer la moyenne. Si la plage de critères est elle-même la plage_moyenne, vous pouvez omettre cette dernière.

Appliquez maintenant cette syntaxe pour calculer la moyenne de la plage de cellules A1:A5 en ignorant le zéro :
  • Sélectionnez par exemple la cellule A6 et tapez =Moyenne.si(
  • Sélectionnez la plage de cellules A1:A5 et tapez point-virgule : =MOYENNE.SI(A1:A5;
  • Tapez "<>0". (N’oubliez pas les guillemets !)
  • Fermez la parenthèse et validez.
  • Voici alors le résultat désiré.
Moyenne sans 0



Découvrons ensemble d’autres exemples d’utilisation de la fonction MOYENNE.SI :

Exemple 1 : calculer la moyenne des nombres supérieurs à 200

  • C’est simple : tapez =MOYENNE.SI(A1:A5;">200")
  • Le résultat est : 433,33
Moyenne si avec condition



Note : implicitement ,le zéro est ignoré en utilisant cette formule !

Exemple 2 : Calculer la moyenne de vente par vendeur

Le tableau suivant présente des données concernant les ventes réalisées par 3 vendeurs :
On veut par exemple calculer la moyenne des ventes réalisées par Alain :
  • Alors, sélectionnez une cellule vide et tapez la formule qui suit :

=MOYENNE.SI(A2:A8;"Alain";B2:B8)

Utiliser fonction Moyenne.si avec un critère


Remarquez ici que nous avons défini les deux plages : plage de critères et plage_moyenne :
  • A2:A8 : représente la plage de critères dans laquelle on cherche les cellules contenant notre critère « Alain ».
  • B2:B8 : représente les valeurs numérique dont on veut calculer la moyenne.

Vous pouvez aussi utiliser une référence de cellule contenant votre critère au lieu de le taper à la main :
=MOYENNE.SI(A2:A8;A3;B2:B8)

Utiliser référence de cellule comme critère dans moyenne si

  • A3 est la référence de la cellule qui contient « Alain ».

Exemple 3 : Moyenne si date 

Dans cet exemple on veut trouver la moyenne des ventes réalisées avant le 3/3/2017 :
La formule à utiliser est : =MOYENNE.SI(A2:A8;"<03/03/2017";C2:C8)

Moyenne si avec date comme critère


Si vous estimez trouver la moyenne des ventes réalisées avant aujourd’hui, dans ce cas vous pouvez introduire la fonction AUJOURDHUI () comme ceci :
=MOYENNE.SI(A2:A8;"<"&AUJOURDHUI();C2:C8)

Moyenne si date avant aujourdhui


Remarquez qu’on a mis uniquement l’opérateur de comparaison entre guillemets "<", puis on a fait appel à l’esperluette pour concaténer cet opérateur et la fonction AUJOURDHUI() non mise entre guillemets : "<"&AUJOURDHUI()

La même chose aussi se dit lorsque vous définissez une référence de cellule avec un opérateur de comparaison.
Par exemple notre formule utilisée précédemment =MOYENNE.SI(A2:A8;"<03/03/2017";C2:C8), on peut l’écrire comme ça : =MOYENNE.SI(A2:A8;"<"&A5;C2:C8)

Moyenne si date en utilisant référence de cellule pour le critère


Utiliser la fonction MOYENNE.SI.ENS pour calculer une moyenne à plusieurs conditions

La fonction MOYENNE.SI.ENS permet de calculer la moyenne des nombres en répondant à un ou plusieurs critères.

Voici la syntaxe de la fonction MOYENNE.SI.ENS:

MOYENNE.SI.ENS(plage_moyenne; plage_critères1; critère1; [plage_critères2; critère2]; ...)

Faites attention lorsque vous voulez introduire les arguments de cette fonction, en effet,  le premier argument doit être la référence de la plage de cellules contenant les valeurs à calculer leur moyenne, suivie de la première plage de critère puis du premier critère, et ensuite de la deuxième plage de critères et du deuxième critère et ainsi de suite….

Si nous reprenons notre premier exemple sur comment calculer la moyenne des nombres sans prendre en compte les valeurs nulles, notre formule sera la suivante :

=MOYENNE.SI.ENS(A1:A5;A1:A5;"<>0")

Moyenne si ens sans 0


Note: Ici, notre plage de moyenne et plage de critères sont les mêmes.

Exemples d’utilisation de la fonction  MOYENNE.SI.ENS :

MOYENNE.SI.ENS avec deux conditions

Dans cet exemple nous désirons calculer la moyenne des quantités des « Montres femmes » qui se sont vendues dans plusieurs magasins et ceci avant le 26/02/2017:

  • Sélectionnez donc une cellule vide et tapez la formule suivante :

=MOYENNE.SI.ENS(C2:C20;A2:A20;"<"&A15;B2:B20;B5)

Moyenne Si Ens Exemple

MOYENNE.SI.ENS avec trois conditions

Vous pouvez aussi calculer la moyenne des quantités des « Montres femmes » vendues entre deux dates, par exemple entre le 23/02/2017 et le 26/02/2017.

  • Voici la formule utilisant MOYENNE.SI.ENS avec trois critères :

=MOYENNE.SI.ENS(C2:C20;A2:A20;">"&A5;A2:A20;"<"&A15;B2:B20;B6)

Moyenne Si Ens avec trois conditions



Note: les exemples traités dans cet article sont contenus dans un seul classeur Excel que vous pouvez le télécharger ici : Moyenne.Si et Moyenne.si.ens

Moyenne pondérée avec condition

Après avoir vu comment calculer une moyenne pondérée en utilisant une formule qui se compose de 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.
Moyenne pondérée avec condition


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

Accessoires des 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 :

Somme des cellules filtrées


Le nombre de Housses achetées est donc 210.

Utilisons maintenant SOMMEPROD.

  • Dans ce cas on doit utiliser la deuxième syntaxe de la fonction SOMMEPROD (SOMMEPROD conditionnelle) qu’on peut 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 ou la 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)

Exemple SOMMEPROD conditionnelle

  • 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".
Exemple de test logique



  • 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.
  • Donc 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 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 le 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 :
  • Sélectionnez une cellule vide.
  • Tapez la formule qui suit :

=SOMMEPROD((A2:A13=A7)*(B2:B13=B7)*C2:C13*D2:D13)/SOMMEPROD((A2:A13=A7)*(B2:B13=B7)*C2:C13)

Calcul d'une moyenne pondérée avec deux conditions



Formule de la moyenne pondérée

L’article présent de la Formation Excel vous explique comment créer une formule pour calculer la moyenne pondérée en vous servant de la fonction SOMMEPROD. La deuxième partie de l’article vous montre comment faire pour que votre formule créée calcule automatiquement une moyenne pondérée lorsque vous insérez de nouvelles valeurs ou vous en supprimez quelques-unes.
Formule moyenne pondérée


Une moyenne arithmétique simple est différente d’une moyenne pondérée. Dans l’article précédent (Calculer la moyenne des données filtrées ou non-filtrées en utilisant la fonction MOYENNE) nous avons montré comment calculer une moyenne arithmétique en utilisant la fonction Moyenne. Et pour calculer une moyenne pondérée, Excel ne fournit aucune fonction spécifique jusqu’à la publication de cet article (26/02/2017). Cependant vous pouvez calculer cette moyenne en utilisant une fonctionne très importante qu’on appelle SOMMEPROD.

La moyenne pondérée

Une moyenne pondérée est une moyenne que l’on obtient en multipliant chaque valeur à son effectif appelé aussi poids, puis on additionne tous les produits obtenus et enfin on divise la somme obtenue par le total des poids.

Dans l’exemple suivant, un étudiant a eu les notes suivantes pour les matières : Maths, physique et anglais où chaque matière possède son propre coefficient :

Tableau notes par matières


  • Pour calculer la moyenne pondérée de cet étudiant, on écrit :

(15x6)+(13x5)+(17x3)/6+5+3
  • Ce qui donne 206/14
  • La moyenne donc est 14,71

Si vous voulez faire ce calcul dans Excel, vous pouvez procéder ainsi :
  • Sélectionnez par exemple C5 dans le tableau suivant et tapez la formule suivante :

=((B2*C2)+(B3*C3)+(B4*C4))/(C2+C3+C4)
  • Et vous obtenez alors :  14,71
Formule simple moyenne pondérée



Cette formule sera très gênante surtout si votre tableau contient de multiples lignes de données. Alors pour réduire ce travail, utilisez les deux fonctions SOMMEPROD et SOMME.

Présentation de la fonction SOMMEPROD

La fonction SOMMEPROD (Somme des Produits) permet de calculer la somme des produits des valeurs par leurs poids. C’est-à-dire que le travail que vous avez fait de calculer le produit de chaque note par son coefficient puis de calculer le total de ces produits, vous pouvez le faire aisément avec SOMMEPROD.

La syntaxe de la fonction SOMMEPROD

SOMMEPROD(matrice1, [matrice2], [matrice3], ...)

  • En l’appliquant pour notre exemple, vous allez taper à la place de l’argument matrice1 la référence de la plage de cellules B2:B4 qui contient les notes, et à la place de l’argument matrice2 tapez la référence de la plage de cellules C2:C4
  • Votre fonction sera écrite donc de cette façon : =SOMMEPROD(B2:B4;C2:C4)
Utiliser SOMMEPROD

  • Ce qui donne 206
  • Il vous reste enfin de diviser ce total par la somme des coefficients qui est calculée par la formule suivante : SOMME(C2:C4)
  • Alors votre formule pour calculer la moyenne pondérée est la suivante :

=SOMMEPROD(B2:B4;C2:C4)/SOMME(C2:C4)

Formule de la moyenne pondérée

  • Et c’est le même résultat obtenu précédemment : 14,71

Les matrices doivent avoir les mêmes dimensions :

Oui, effectivement, les matrices de la fonction SOMMEPROD doivent avoir le même nombre de lignes et de colonnes, si non votre formule renvoie une erreur de type VALEUR. (pour plus de détails sur cette erreur consultez l’article suivant : Les erreurs dans Excel)

Dans notre exemple dernier, la matrice B2:B4 se compose d’une colonne et de trois lignes et aussi la matrice C2:C4.

Regardez maintenant l’exemple suivant :

matrices de sommeprod


  • Les deux matrices contiennent chacune deux colonnes et trois lignes.
  • Alors, pour trouver la somme des produits des valeurs de la première matrice par celles de la deuxième matrice, suivez la procédure illustrée dans cette image animée :
Introduire des matrices dans SOMMEPROD

  • Le travail donc effectué par cette fonction suit l’ordre suivant :

(A1*D1)+(A2*D2)+(A3*D3)+(B1*E1)+(B2*E2)+(B3*E3)

Note : Vous pouvez utiliser jusqu’à 255 matrices dans la fonction SOMMEPROD

Calculer automatiquement la moyenne pondérée lors d’une insertion ou suppression de valeurs :

Dans cet exemple, on veut calculer automatiquement le prix de vente moyen lorsque de nouveaux produits se sont ajoutés au stock mais qui se sont achetés à des prix différents.
  • Dans la cellule B7, tapez la formule pour calculer la moyenne pondérée comme suit :

=SOMMEPROD(A2:A6;B2:B6)/SOMME(A2:A6)

Exemple 2 de formule pour calculer moyenne pondérée

  • Faites maintenant de petites modifications, en imbriquant la fonction DECALER :
  • Remplacez la cellule A6 dans SOMMEPROD et SOMME par DECALER(A7;-1;0), et la cellule B6 par DECALER(B7;-1;0)
Fonction Decaler imbriquée dans SOMMEPROD

  • Insérez maintenant une nouvelle ligne juste en dessus de la cellule B7 qui contient votre formule, puis tapez les nouvelles valeurs : Quantité et Prix de vente.
Calcul automatique de moyenne pondérée lors d'insertion de nouvelles valeurs

  • Remarquez donc que la moyenne pondérée s’est calculée automatiquement.
  • Essayez de supprimer une ligne de ce tableau et remarquez aussi que votre formule moyenne pondérée s’effectue aussi automatiquement.
Calcul automatique de moyenne pondérée lors de suppression de valeurs