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 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 aider à 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’avez 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 et comment utiliser MOYENNE.SI et MOYENNE.SI.ENS ?!!

Ç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, lorsque vous utilisez une fonction Moyenne et si la plage de cellules référencée dans cette fonction contient des cellules vides, vous pouvez remarquer 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 vous allez chercher le critère souhaité.
  • Critères : c’est votre condition à réaliser.
  • plage_moyenne : c’est la plage qui contient les valeurs dont vous voulez calculer la moyenne. Si la plage de critères est elle-même la plage_moyenne, vous pouvez omettre cette dernière.

Utilisez 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 souhaité.
Moyenne sans 0



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

Mais avant d'entamer cette découverte, jetez rapidement un coup d'œil sur cette vidéo pour avoir une idée sur l'utilisation de cette fonction :

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 nous cherchons les cellules contenant notre critère « Alain ».
  • B2:B8 : représente les valeurs numérique dont nous voulons 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 nous voulons 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 que j'ai mis uniquement l’opérateur de comparaison entre guillemets "<", puis j'ai 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 la formule utilisée précédemment =MOYENNE.SI(A2:A8;"<03/03/2017";C2:C8), vous pouvez 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 : >23/02/2017, <26/02/2017 et Montre femme.

=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  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 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.
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 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)

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.
  • 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 :
  • 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