En mathématiques, la moyenne est appelée la moyenne arithmétique, ou simplement la moyenne, et elle est calculée en additionnant un groupe de nombres, puis en divisant par le nombre de ces nombres.
Dans l’exemple ci-dessus, si le premier athlète a parcouru la distance en 10,5 secondes, le second en 10,7 secondes et le troisième en 11,2 secondes, le temps moyen serait de 10,8 secondes :
= (10,5+10,7+11,2)/3.
Pour calculer la moyenne dans Excel, vous n’aurez pas besoin d’écrire de telles expressions mathématiques, les puissantes fonctions Excel de la moyenne( ) feront le travail en arrière-plan.
1 Fonction MOYENNE( )
Vous utilisez la fonction MOYENNE( ) dans Excel pour renvoyer la moyenne (moyenne arithmétique) des cellules spécifiées. Sa syntaxe est
= MOYENNE(nombre1 ; [nombre2] ; …)
nombre1, nombre2 , … sont des valeurs numériques dont vous voulez trouver la moyenne. Le premier argument est obligatoire, les suivants sont facultatifs et jusqu’à 255 arguments peuvent être inclus dans une seule formule. Les paramètres peuvent être fournis sous forme de nombres, de références de cellules ou de plages.
1.1 Utilisation de la fonction MOYENNE( )
La fonction MOYENNE( ) est l’une des fonctions Excel les plus simples et les plus faciles à utiliser, et les exemples suivants le prouvent.
Exemple 1. Calcul d’une moyenne de nombres
Pour connaître une moyenne de certains nombres, vous pouvez les fournir directement dans votre formule de moyenne Excel. Par exemple, =MOYENNE(1 ; 2 ; 3 ; 4) renvoie 2,5 comme résultat.
Pour calculer une moyenne de colonne, fournissez une référence à la colonne entière :
=MOYENNE(A:A)
Pour obtenir une moyenne de ligne, saisissez la référence de ligne :
=MOYENNE(1:1)
Pour calculer une moyenne de nombres dans une plage donnée, spécifiez cette plage dans votre formule Moyenne :
=M0YENNE(B2:B11)
Supposons que Excel n’ait pas de fonction MOYENNE (). Dans ce cas, pour obtenir le résultat affiché dans la cellule B13 de la figure précédente, vous devrez entrer quelque chose comme ça dans B13:
= (B2 + B3 + B4 + B5 + B6 + B7 + B8 + B9 + B10 + B11) / 10
Ou, si Excel avait une fonction SOMME ()et une fonction NB ()mais pas MOYENNE (), vous pouvez utiliser ceci:
= SOMME (B2: B11)/NB(B2: B11)
Mais vous n’avez pas besoin de vous en soucier car Excel a une fonction MOYENNE (), et dans ce cas vous l’utilisez comme suit:
= MOYENNE(B2: B11)
Pour renvoyer une moyenne de cellules non adjacentes, vous fournissez chaque cellule individuellement, par exemple
=MOYENNE(A1 ; C1 ; D1)
Et naturellement, rien ne vous empêche d’inclure des valeurs, des références de cellules et des plages dans la même formule, comme votre logique métier l’exige. Par exemple, la formule de moyenne suivante calcule la moyenne de 2 plages et d’une cellule individuelle :
=MOYENNE (B3:B5; B7:B9; B12)
Si vous souhaitez arrondir le nombre renvoyé à l’entier le plus proche, utilisez l’une des fonctions d’arrondi d’Excel, par exemple :=ARRONDI(MOYENNE(B3:B5;B7:B9;B12);0)
Outre les nombres, vous pouvez utiliser la fonction Excel MOYENNE ( )pour calculer une moyenne d’autres valeurs numériques telles que des pourcentages et des heures, comme illustré dans les exemples suivants.
Veuillez noter que la fonction Excel MOYENNE( ) inclut des valeurs nulles lors du calcul d’une moyenne. Si vous préférez exclure les zéros, utilisez plutôt MOYENNE.SI( ), comme illustré dans l’exemple suivant.
1.2 Fonction MOYENNE( ): choses à retenir !
Comme vous venez de le voir, l’utilisation de la fonction MOYENNE( ) dans Excel est simple. Cependant, il a quelques spécificités dont vous devez être conscient.
■ Les cellules avec des valeurs nulles (0) sont incluses dans la moyenne.
■ Les cellules contenant des chaînes de texte, des valeurs booléennes VRAI et FAUX et des cellules vides sont ignorées. Si vous souhaitez inclure des valeurs booléennes et des représentations textuelles de nombres dans le calcul, utilisez la fonction AVERAGEA( ).
■ Les valeurs booléennes que vous tapez directement dans la formule Excel MOYENNE( ) sont comptées. Par exemple, la formule =MOYENNE(VRAI ; FAUX) renvoie 0,5, qui est la moyenne de 1 et 0.
Lorsque vous utilisez la fonction MOYENNE( ) dans des feuilles Excel, veuillez garder à l’esprit la différence entre les cellules contenant des valeurs nulles et les cellules vides – les 0 sont comptés, mais pas les cellules vides. Cela peut être particulièrement déroutant si l’option Afficher un zéro dans les cellules qui ont une valeur nullen’est pas cochée dans une feuille donnée. Vous pouvez trouver cette option sous Options Excel / Avancé / Options d’affichage pour cette feuille de calcul.
2. Fonction AVERAGEA( )
La fonction AVERAGEA( ) est similaire à MOYENNE( ) en ce sens qu’elle calcule la moyenne (moyenne arithmétique) des valeurs de ses arguments. La différence est que AVERAGEA( ) inclut toutes les cellules non vides dans un calcul, qu’elles contiennent des nombres, du texte, des valeurs booléennes VRAI et FAUX et des chaînes vides renvoyées par d’autres formules.
AVERAGEA(valeur1 ; [valeur2] ; …)
Valeur1, valeur2, … sont des valeurs, des tableaux, des références de cellules ou des plages dont vous souhaitez calculer la moyenne. Le premier argument est obligatoire, les autres (jusqu’à 255) sont facultatifs.
Fonction Excel AVERAGEA( ) – choses à retenir !
Comme mentionné ci-dessus, la fonction AVERAGEA( ) traite différents types de valeurs tels que des nombres, des chaînes de texte et des valeurs logiques VRAI et FAUX. Dans les formules AVERAGEA( ) :
■ Les cellules vides sont ignorées.
■ Les valeurs de texte, y compris les chaînes vides (“”) renvoyées par d’autres formules, sont évaluées comme 0.
■ La valeur booléenne de VRAI vaut 1 et FAUX vaut 0.
Par exemple, la formule =AVERAGEA(2 ;FAUX) renvoie 1, qui est la moyenne de 2 et 0. La formule =AVERAGEA (2 ;VRAI) renvoie 1,5, qui est la moyenne de 2 et 1.
3. Fonction MOYENNE.SI( )
La fonction MOYENNE.SI( )dans Excel calcule la moyenne (moyenne arithmétique) de toutes les cellules qui répondent à un critère spécifié.
=MOYENNE.SI (plage ; critères ; [plage_moyenne])
La fonction AMOYENNE.SIa les arguments suivants, les 2 premiers sont obligatoires, le dernier est facultatif :
■ Plage : la plage de cellules à tester par rapport aux critères donnés.
■ Critères : la condition utilisée pour déterminer les cellules à moyenner. Les critères peuvent être fournis sous la forme d’un nombre, d’une expression logique, d’une valeur textuelle ou d’une référence de cellule.
■ plage_moyenne :les cellules dont vous voulez réellement faire la moyenne (facultatif). S’il est omis, la formule calculera une moyenne des valeurs dans l’argument de plage.
Utilisation de la fonction MOYENNE.SI( )dans Excel – exemples de formules
Et maintenant, voyons comment vous pouvez utiliser la fonction MOYENNE.SI( )sur des feuilles de calcul réelles pour trouver une moyenne de cellules qui répondent à vos critères.
Exemple 1. Nombre moyen de cellules correspondant exactement aux critères
L’utilisation classique de la fonction MOYENNE.SI( )dans Excel consiste à trouver une moyenne de cellules qui correspondent exactement à un critère donné. Dans cet exemple, faisons la moyenne des ventes (B2:B9) pour les commandes Orange(A2:A9) :
= MOYENNE.SI(A2:A9;”Orange”;B2:B9)
Au lieu d’entrer la condition directement dans une formule, vous pouvez la saisir dans une cellule séparée et faire référence à cette cellule dans votre formule :
= MOYENNE.SI (A2:A9; E1; B2:B9)
Pour arrondir la valeur renvoyée à un certain nombre de décimales, utilisez soit l’une des fonctions d’arrondi d’Excel pour arrondir la valeur réelle stockée dans la cellule, soit la boîte de dialogue Format de cellule pour modifier uniquement la mise en forme de l’affichage.
Par exemple, pour arrondir la moyenne renvoyée par la formule ci-dessus à 2 décimales, vous pouvez l’envelopper dans la fonction ARRONDI( ) comme ceci :
=ARRONDI(MOYENNE.SI (A2:A9;”Orange”; B2:B9); 2)
Exemple 2. Nombre moyen de cellules correspondant partiellement aux critères (caractères génériques)
Dans vos formules MOYENNE.SI( ), vous pouvez utiliser des caractères génériques dans l’argument critère pour faire la moyenne des cellules en fonction d’une correspondance partielle :
■ Utilisez un point d’interrogation (?) pour correspondre à n’importe quel caractère.
■ Utilisez un astérisque (*) pour correspondre à n’importe quelle séquence de caractères.
■ Pour trouver un point d’interrogation ou un astérisque réel, tapez un tilde (~) avant le caractère dans les critères.
Dans l’exemple précédent, supposons que vous ayez 3 sortes de d’Orange différentes et que vous vouliez trouver leur moyenne.
=MOYENNE.SI(A2:A9;”Orange*”;B2:B9)
Si votre mot clé est susceptible d’être précédé et suivi d’autres caractères, ajoutez un astérisque devant et après le mot, comme ceci :
=MOYENNE.SI (A2:A8; “*Orange*”; B2:B8)
Pour trouver la moyenne de tous les articles ne contenant pas Orange, utilisez la formule suivante :
=MOYENNE.SI (A2:A8; “<>*Orange*”; B2:B8)
Exemple 3. Cellules moyennes basées sur des critères numériques et des opérateurs logiques
Très souvent, vous souhaiterez peut-être calculer la moyenne des cellules dont la quantité est supérieure ou inférieure à une certaine valeur. Par exemple, nous avons une liste de nombres dans la colonne A et nous voulons trouver une moyenne de ceux qui sont supérieurs à 20.
La manière correcte d’entrer un tel critère est de placer l’opérateur logique et le nombre entre guillemets doubles. Ainsi, votre formule de moyenne dans Excel serait la suivante :
=MOYENNE.SI (A2:A10 ; “>20”)
Une autre tâche courante consiste à faire la moyenne des nombres qui ne sont pas égaux à zéro. Pour cela, vous auriez besoin de l’opérateur « différent de » dans l’argument critère de votre formuleMOYENNE.SI :
=MOYENNE.SI (A2:A10 ; “<>0”)
Comme vous l’avez peut-être remarqué, nous n’utilisons pas le troisième argument dans l’une ou l’autre des formules ci-dessus puisque nous voulons trouver une moyenne dans la plage initiale.
Exemple 4. MOYENNE.SI( )pour les cellules vides ou non vides
Lors de l’analyse de données dans Excel, vous devrez souvent trouver une moyenne de nombres correspondant à des cellules vides ou non vides.
Pour inclure des cellules vides qui ne contiennent absolument rien (pas de formule, pas de chaîne de longueur nulle), entrez “=” dans l’argument critère.
Par exemple, la formule suivante calcule une moyenne des cellules C2 : C10 si une cellule de la colonne B de la même ligne est absolument vide :
= MOYENNE.SI (B2:B10; “=”; C2:C10)
Pour faire la moyenne des valeurs correspondant aux cellules visuellement vides, y compris celles qui contiennent des chaînes vides renvoyées par d’autres fonctions (par exemple, les cellules avec une formule comme =” “), utilisez “” dans les critères. Par exemple :
= MOYENNE.SI (B2:B10 ; “” ; C2:C10)
Pour trouver une moyenne des valeurs correspondant aux cellules non vides , tapez “<>” dans critères.
Par exemple, la formule MOYENNE.SI( )suivante calcule une moyenne des cellules C2 : C10 si une cellule de la colonne B de la même ligne n’est pas vide :
= MOYENNE.SI (B2:B10; “<>”; C2:C10)
3. Fonction MOYENNE.SI.ENS ( )
La fonction MOYENNE.SI.ENS( ) dans Excel est un équivalent pluriel de MOYENNE.SI( ) . Il permet plusieurs conditions et renvoie la moyenne (moyenne arithmétique) des cellules qui répondent à tous les critères spécifiés.
MOYENNE.SI.ENS (plage_moyenne ; plage_critères1 ; critères1 ; [plage_critères2 ; critères2] ; …)
La fonction MOYENNE.SI.ENS( ) a les arguments suivants :
■ Plage_moyenne :la plage de cellules dont vous souhaitez calculer la moyenne.
■ Plage_critères1, plage_critères2, … – 1 à 127 plages à tester par rapport aux critères spécifiés. Plage_critères1 est obligatoire, les suivants sont facultatifs.
■ Critères1, critères2, … – 1 à 127 critères qui déterminent les cellules à moyenner. Les critères peuvent être fournis sous la forme d’un nombre, d’une expression logique, d’une valeur de texte ou d’une référence de cellule. Le critère 1 est obligatoire, les critères supplémentaires sont facultatifs.
La fonction MOYENNE.SI.ENS( ) est disponible dans Excel 2016, Excel 2013, Excel 2011 pour Mac, Excel 2010 et 2007.
Exemples de formules avec MOYENNE.SI.ENS( )
Comme déjà mentionné, la fonction Excel MOYENNE.SI.ENS( ) trouve la moyenne des cellules qui répondent à tous les critères que vous spécifiez. Vous l’utilisez de la même manière que MOYENNE.SI( ), sauf que vous pouvez fournir plusieurs plage_critères et critères dans une formule.
Exemple 1. Cellules moyennes selon plusieurs critères (texte et nombre)
Supposons que vous ayez une liste d’articles dans la colonne A et les montants des ventes dans la colonne B, découvrons une moyenne des ventes de Oranges supérieures à 100 .
Dans cette formule MOYENNE.SI.ENS:
■ Plage_moyenne est B2:B10 (cellules dont vous souhaitez calculer la moyenne si les deux conditions sont remplies) ;
■ Plage_critères1 est A2:A10 (colonne Articles) et critère1 est “Oranges” ;
■ Plage_critères2 est B2:B10 (colonne Ventes) et critère2 est “>100”.
En assemblant les composants ci-dessus, nous obtenons la formule suivante :
=MOYENNE.SI.ENS(B2:B10; A2:A10 ; “Orange”; B2:B10; “>100”)
Et si vous remplacez les valeurs réelles par des références de cellules dans votre formule, vous obtiendrez quelque chose de similaire à ceci :
Comme vous le voyez, seules deux cellules (B2, B8 et B10) remplissent les deux conditions, et donc seules ces cellules sont moyennées.
Exemple 2. Cellules moyennes basées sur des critères de date
Dans cet exemple, recherchons une moyenne d’articles livrés avant le 21 août 2022 dont le statut est défini (Aucune cellule de la colonne correspondante n’est vide). Avec la quantité indiquée dans la colonne D (plage_moyenne), les dates dans la colonne B (Plage_critères1) et le statut dans la colonne C (plage_critères2), la formule est la suivante :
=MOYENNE.SI.ENS(D2:D10;B2:B10;”<21/08/2022″;C2:C10;”<>”)
Dans critère1, vous saisissez une date précédée d’un opérateur de comparaison. Dans le critère 2, vous tapez “<>” qui indique à la formule d’inclure uniquement les cellules non vides dans plage_critères2(colonne C dans cet exemple).
Lorsque vous utilisez un nombre ou une date en conjonction avec un opérateur logique dans les critères de MOYENNE.SI.ENS( ), vous placez cette combinaison entre guillemets doubles comme “<22/08/2022”.
4. Fonctions MOYENNE.SI( ) et MOYENNE.SI.ENS( ) : choses à retenir !
Les fonctions MOYENNE.SI( ) et MOYENNE.SI.ENS( )ont beaucoup en commun, notamment :
■ Dans l’argument plage_moyenne, les cellules vides, les valeurs booléennes VRAI/FAUX et les valeurs textuelles sont ignorées.
■ Dans l’argument critères / critères_plage, les cellules vides sont traitées comme des valeurs nulles (0).
■ Si la plage_moyenne ne contient que des cellules vides ou des valeurs de texte, les deux fonctions renvoient l’erreur #DIV0 ! .
■ Si aucune cellule ne répond aux critères (tous les critères en cas de MOYENNE.SI.ENS( ) ), l’erreur #DIV0! est également renvoyée.
MOYENNE SI spécificités
■ plage_moyenne ne doit pas nécessairement être de la même taille que critères_plage. Cependant, les cellules réelles à moyenner sont déterminées par la taille de l’argument de plage. En d’autres termes, la cellule supérieure gauche de la plage moyenne est traitée comme la cellule de début et comprend autant de colonnes et de lignes que celles contenues dans l’argument plage.
Spécificités MOYENNE.SI.ENS( )
■ Contrairement à la fonction MOYENNE.SI( ), MOYENNE.SI.ENS( ) exige que chaque critère_plage soit de la même taille que plage_moyenne.
5. Comment faire la moyenne des cellules selon plusieurs critères avec la logique OU
Étant donné que la fonction MOYENNE.SI.ENS( ) fonctionne avec la logique ET et que la fonction MOYENNE.SI ( ) ne permet qu’un seul critère, nous devrons créer notre propre formule pour faire la moyenne avec la logique OU. En d’autres termes, nous créerons une formule pour calculer la moyenne dans Excel si l’une des conditions spécifiées est remplie.
Exemple 1. Moyenne avec logique OU basée sur plusieurs critères de texte
Supposons que vous souhaitiez obtenir une moyenne des ventes (B2:B8) à la fois pour la banane et la pomme (A2:A8). Pour calculer cela, vous auriez besoin d’une formule matricielle comprenant quelques fonctions Excel :
=MOYENNE(SI(ESTNUM(EQUIV(A2:A8;{“Banane”;”Pomme”};0));B2:B8))
Pour plus de commodité, vous pouvez bien entendu remplacer les critères codés en dur par les références correspondantes :
=MOYENNE(SI(ESTNUM(EQUIV(A2:A8;E1:E2;0));B2:B8))
N’oubliez pas que les formules matricielles doivent être saisies via Ctrl + Maj + Entrée, pas seulement Entrée.
De toutes les formules de moyenne Excel discutées jusqu’à présent, c’est la plus délicate (bien qu’il reste encore deux exemples 😉 Le modèle suivant facilitera l’ajustement de la formule pour vos propres feuilles de calcul :
= MOYENNE(SI(ESTNUM(EQUIV ( plage , {” critères1 “, ” critères2 “,…}, 0)), plage_moyenne ))
Comment fonctionne cette formule :
Pour nos lecteurs curieux et réfléchis qui veulent non seulement utiliser une formule mais comprendre ce qu’ils font, voici une explication détaillée de la logique de la formule.
Au cœur de cette formule, la fonction SI( ) détermine quelles valeurs de la plage source correspondent à l’un des critères spécifiés et transmet ces valeurs à la fonction MOYENNE( ). Voici comment:
La fonction EQUIV( ) utilise les données source dans A2:A8 comme valeurs de recherche et compare chacune de ces valeurs au tableau de recherche représenté par les critères dans E1:E2. Le 3ème argument ( type ) défini sur 0 indique à la formule de rechercher des correspondances exactes :
EQUIV(A2:A8; E1:E2; 0)
Lorsqu’une correspondance est trouvée, la formule renvoie sa position relative dans le tableau de recherche, sinon une erreur #N/A.
{#N/A;1;#N/A;1;2;#N/A;2}
La fonction ESTNUM( ) convertit tous les nombres en VRAI et les erreurs en FAUX :
{FAUX;VRAI;FAUX;VRAI;VRAI;FAUX;VRAI}
Ce tableau va au test logique de la fonction SI. Dans la forme complète, le test logique doit être écrit comme ceci :
SI(ESTNUM(EQUIV(A2:A8; E1:E2; 0))=VRAI
Par souci de brièveté, nous omettons la partie =VRAI car elle est implicite. Quoi qu’il en soit, comme l’ argument valeurs_si_vrai de SI( ) est défini sur B2:B8 et que valeurs_si_faux est omis, la fonction SI( ) remplace les positions correspondant à VRAI dans le tableau renvoyé par ESTNUM (voir ci-dessus) par les valeurs réelles de B2:B8 :
{FAUX; 80; FAUX; 120; 70; FAUX; 90}
Ce dernier tableau est passé à la fonction MOYENNE( ) qui calcule une moyenne de nombres en ignorant les valeurs logiques.
Exemple 2. Moyenne avec logique OU basée sur des critères numériques avec des opérateurs de comparaison
Si vous souhaitez faire la moyenne des cellules en fonction de plusieurs critères numériques et de conditions supérieures / inférieures à combinées avec la logique OU, la formule décrite dans l’exemple précédent ne fonctionnera pas car vous ne pouvez pas insérer ces expressions logiques dans un tableau. La solution utilise la fonction SOMME( ) dans une formule matricielle.
Supposons que vous ayez Quantité. dans la colonne C et les ventes dans la colonne D, et souhaite faire la moyenne des ventes qui ont une valeur supérieure à 50 dans la colonne C ou D. À cela, vous voulez éviter les doublons, c’est-à-dire ne comptez pas une ligne deux fois car elle a une valeur supérieur à 50 dans les colonnes C et D.
Voici la formule qui fonctionne un régal:
=SOMME(SI(–((C2:C8>50) +(D2:D8>50)) >0;D2:D8;0)) / SOMME(–(((C2:C8>50)+(D2:D8>50))>0))
N’oubliez pas qu’il s’agit d’une formule matricielle et que vous devez donc appuyer sur Ctrl + Maj + Entrée pour le saisir correctement.
Comme vous pouvez le voir, la formule se compose de 2 parties. Dans la première partie, vous utilisez la fonction SI() avec l’instruction OU dans l’ argument test_logique (C2:C8>50)+(D2:D8>50). Comme vous le savez probablement, dans les formules matricielles, plus (+) agit comme un opérateur OU (pour plus de détails, veuillez consulter les opérateurs ET et OU dans les formules matricielles Excel ). Ainsi, la première partie de la formule additionne les valeurs de la colonne C si l’une ou l’autre des conditions est remplie. La deuxième partie renvoie le nombre de ces cellules, puis vous divisez la somme par le nombre pour trouver la moyenne.
Et naturellement, vous pouvez spécifier une condition différente pour chaque plage. Par exemple, pour calculer la moyenne des ventes si la colonne C est supérieure à 50 ou la colonne D est supérieure à 100, utilisez les expressions suivantes : (C2:C8>50)+(D2:D8>100). La formule entière ressemblerait à ceci :
=SOMME(SI(–((C2:C8>50) +(D2:D8>100)) >0;D2:D8;0)) / SOMME(–(((C2:C8>50)+(D2:D8>100))>0))
Exemple 3. Moyenne avec logique OU basée sur des cellules vides / non vides
La formule moyenne avec plusieurs critères OU correspondant à des cellules vides et non vides est très similaire à celle dont nous venons de parler.
Formule pour les cellules non vides
La formule matricielle suivante trouve la quantité moyenne (colonne B) si une date (colonne B) ou un statut (colonne C) est répertorié, c’est-à-dire si la colonne B ou C n’est pas vide .
=SOMME(SI(–((B2:B8<>””)+(C2:C8<>””))>0;D2:D8;0))/
SOMME(–(((B2:B8<>””)+(C2:C8<>””))>0))
Pour rendre la formule plus compacte, vous pouvez concaténer les plages à l’aide d’une esperluette (&) :
=SOMME(SI((B2:B8&C2:C8)<>””;D2:D8;0))/SOMME(–((B2:B8&C2:C8)<>””))
Formule pour les cellules vides
Pour calculer la moyenne des valeurs de la colonne D correspondant aux cellules vides de B ou C, remplacez l’opérateur non vide (<>””) par l’opérateur vide (=” “). La concaténation des plages avec une esperluette ne fonctionnera pas dans ce cas. Il s’agit également d’une formule matricielle, alors n’oubliez pas d’appuyer sur Ctrl + Maj + Entrée, pas seulement Entrée.
=SOMME(SI(–((B2:B8=””)+(C2:C8=””))>0;D2:D8;0))/
SOMME(–(((B2:B8=””)+(C2:C8=””))>0))
C’est ainsi que vous calculez la moyenne dans Excel.