Vous voulez calculer une moyenne arithmétique dans Excel ? Une moyenne pondérée ? Une moyenne sur des données répondant à plusieurs conditions ? Une médiane, une moyenne harmonique ou géométrique ? Suivez le guide !

La moyenne est l'un des indicateurs statistiques les plus simples pour dégager une tendance. Que vous souhaitiez calculer la moyenne d'un élève ou de la classe toute entière, la vitesse moyenne lors d'un long parcours ou même le salaire médian du personnel d'une entreprise, avec Excel et ses centaines de fonctions, vous n'aurez aucun mal à trouver la formule magique. Le tableur sait bien sûr écarter les valeurs nulles ou non significatives, voire les extrêmes si vous le préférez, ne retenir que les données répondant à une ou plusieurs conditions, ou encore pondérer les données par une suite de coefficients. Et parce que la moyenne arithmétique simple (qui correspond à la somme des valeurs divisée par le nombre de valeurs) n'est pas toujours l'indicateur le plus pertinent, Excel vous propose aussi le calcul de la médiane et de quelques autres moyennes qui intéresseront les statisticiens.

Notre fiche pratique vous propose d'explorer les principales fonctions pour calculer ces moyennes. Même si vous n'êtes pas très à l'aise avec Excel, nos explications vous permettront de rédiger la bonne formule, adaptée à vos données. Nos conseils valent indifféremment pour Excel pour Windows, Excel pour Mac, la version gratuite d'Excel pour le Web, ainsi que tous les tableurs compatibles Microsoft Office, dont le logiciel libre et gratuit LibreOffice pour Windows, macOS et Linux. Nous vous indiquons le cas échéant les particularités de votre logiciel, si vous n'utilisez pas la dernière version du tableur de la suite bureautique Office, dite Excel pour Microsoft 365.

Comment obtenir la moyenne d'une plage de cellules sans formule ?

Sans calcul ni formule, Excel pour Windows, pour Mac et pour le Web offrent un moyen rapide de connaître la moyenne arithmétique ou la somme d'une plage de cellules, simplement en la sélectionnant. Mais une formule reste préférable pour garder le résultat affiché dans une cellule, par exemple au bas de la colonne ou à droite d'une ligne de chiffres.

► Sélectionnez une plage de cellules : la moyenne s'affiche au bas de la fenêtre, dans la barre d'état d'Excel.

► Dans Excel pour Windows (mais pas sur Mac), un clic sur cette zone de la barre d'état copie la valeur dans le presse-papiers, pour la coller dans n'importe quelle application d'une pression sur les deux touches Ctrl+V.

► Le résultat calculé par Excel et le comportement sont identiques à ceux de la fonction MOYENNE expliquée plus loin pour calculer la moyenne arithmétique avec une formule.

► Si vous ne voyez pas la moyenne s'afficher, cliquez avec le bouton droit de la souris n'importe où dans la barre d'état d'Excel puis cochez l'option Moyenne.

► La moyenne affichée dans la barre d'état prend le même format d'affichage que la première cellule sélectionnée dans la plage (la cellule en blanc, les autres étant grises). Cela peut avoir une incidence sur le résultat affiché quand le calcul donne un nombre à virgule. Ci-dessous, la moyenne est de 3,5 (21/6=3,5) mais la barre d'état affiche 4 € car la première cellule de la plage sélectionnée (cellule A1) a un format d'affichage qui arrondit la valeur à l'entier le plus proche.

► Si l'on resélectionne exactement la même plage de cellules, mais en commençant cette fois par sélectionner A6, la moyenne affichée dans la barre d'état est de 3,50 €, car c'est le format d'affichage de la cellule A6.

Comment calculer une moyenne avec une formule Excel ?

Excel comporte plusieurs fonctions pour calculer une moyenne. Voyons ici la plus fréquente, la moyenne arithmétique, et comment accéder aux autres moyennes (géométrique, harmonique…) prévues par le tableur.

► Si vos données se suivent, en ligne ou en colonne, placez-vous au bas de la colonne, ou à droite de la ligne, sur la première cellule vide où inscrire la formule.

► Sous l'onglet Accueil, cliquez sur le chevron à droite du bouton Σ Somme automatique, puis sur Moyenne.

► Excel inscrit =MOYENNE( et sélectionne une plage de cellules. Vérifiez bien qu'elle englobe toutes les cellules qui doivent participer à la moyenne. En particulier, la sélection opérée par Excel s'arrête à la première cellule vide ; si ce n'est pas ce que vous souhaitez, effectuez vous-même la sélection à la souris ou au clavier. Pour valider la formule, cliquez sur la coche ou pressez la touche Entrée.

► L'opération fonctionne aussi avec des données sur une ou plusieurs lignes.

► Vous pouvez, bien sûr, taper vous-même =MOYENNE( et sélectionner une plage de cellules qui vous convient, ou plusieurs cellules ou plages discontinuées, séparées par des points-virgules. L'ajout de la parenthèse fermante est facultatif : s'il n'y a pas d'ambiguïté, Excel l'ajoute d'office quand vous pressez la touche Entrée.

► Si les cellules ne se trouvent pas toutes dans une même ligne ou colonne, pressez la touche Ctrl et/ou la touche Maj tout en cliquant sur ces cellules pour les ajouter à votre sélection multiple.

► Si, parmi les cellules sélectionnées, certaines sont vides ou ne contiennent pas de nombres (texte, valeurs logiques VRAI ou FAUX…), elles ne sont pas prises en compte dans le calcul.

► Dans la version française d'Excel, notez aussi que les nombres à virgule doivent s'écrire avec une virgule et non un point (3,8 et non 3.8), sinon ils seront considérés comme du texte et ne seront donc pas pris en compte dans le calcul.

► Les cellules peuvent contenir des heures du type 06:25:38, par exemple pour calculer la durée moyenne mise par des candidats pour boucler une épreuve sportive. Ou même des dates, pour connaître la date moyenne d'apparition d'un événement.

► Pour plus d'explications en français, reportez-vous à l'aide en ligne sur la fonction MOYENNE.

► Quand vous tapez le mot MOYENNE dans une formule (une formule doit toujours commencer par le signe =), Excel vous présente la liste de toutes les fonctions contenant cette expression. Sélectionnez une fonction en cliquant dessus ou avec les touches fléchées Haut/Bas du clavier.

► Pour obtenir une page d'aide sur une fonction qui s'inscrit dans la barre de formule, cliquez par exemple sur le nom de la fonction dans la bulle d'aide qui s'affiche juste en dessous.

► Dans Excel et ses concurrents, une aide à la rédaction de formule vous est également proposée en cliquant sur l'icône fx (Insérer une fonction).

► La fenêtre qui s'ouvre vous indique les arguments à fournir à la fonction en cours de rédaction, avec une explication succincte en français de chaque argument quand vous cliquez dedans. Notez aussi, à droite de chaque argument, la ou les valeurs sur lesquelles la formule va s'appliquer. Sur notre exemple, on demande à Excel d'effectuer la moyenne arithmétique des cellules situées entre A19 et A29, en ne tenant compte que de celles dont le contenu est supérieur à 50 : ">50"

► La fonction MOYENNE d'Excel en français s'appelle AVERAGE dans Excel en anglais. Vous n'avez pas besoin de traduire les fonctions si vous transmettez un fichier Excel à un utilisateur de la version anglaise ou de toute autre version étrangère : toutes les fonctions sont traduites automatiquement dans la langue de l'application Excel qui ouvre le fichier.

► Si les nombres tapés dans les cellules ne sont pas considérés par Excel comme des valeurs numériques mais comme du texte (par exemple parce que les valeurs décimales comportent un point au lieu d'une virgule), reportez-vous à nos explications Convertir du texte en nombre, date ou heure et Nettoyer des données Excel.

► Si vous souhaitez calculer un autre type de moyenne, voici quelques fonctions prévues par Excel :

► SOMMEPROD : une fonction d'Excel qui facilite le calcul d'une moyenne pondérée, détaillée plus loin.

► MEDIANE : le calcul de la médiane que nous vous expliquons plus loin.

► MODE.SIMPLE, MODE.MULTIPLE et (la fonction obsolète mais toujours utilisable) MODE : renvoient la valeur la plus fréquente ou répétitive dans une plage de données.

► MOYENNE.GEOMETRIQUE : la moyenne géométrique d'une matrice ou d'une plage de données positives.

► MOYENNE.HARMONIQUE : l'inverse de la moyenne arithmétique des inverses des termes, parfois utilisée pour les vitesses et les temps, par exemple pour calculer la vitesse moyenne d'un aller-retour alors que le trajet aller s'est effectué à pied et le trajet retour à vélo.

► MOYENNE.RANG : renvoie le rang d'un nombre dans une liste de nombres.

► MOYENNE.REDUITE : calcule la moyenne d'une série après élimination d'un pourcentage d'observations aux extrémités inférieure et supérieure.

► BDMOYENNE : moyenne des valeurs d'une liste ou d'une base de données qui remplissent les critères précisés.

Comment calculer la moyenne de plusieurs colonnes ou plusieurs lignes ?

Comme beaucoup de fonctions d'Excel, la fonction MOYENNE accepte jusqu'à 255 arguments séparés par des points-virgules dans Excel en français (et par des virgules dans Excel en anglais). Ces arguments peuvent être des cellules, des plages de cellules, des valeurs numériques (128,5), des plages nommées (Montant_HT), des résultats de fonctions, etc.

► Si les données à calculer se trouvent par exemple dans plusieurs colonnes, vous pouvez sélectionner un bloc de cellules englobant toutes les colonnes, à condition que les cellules non pertinentes soient vides ou ne risquent pas d'être prises en compte dans la moyenne.

► Une méthode plus sûre : quand vous rédigez la formule, sélectionnez à la souris plusieurs cellules et plages de cellules en pressant la touche Ctrl. Et au besoin, ajoutez également un nombre, un nom de plage, etc. Toutes ces données à prendre en compte dans la formule de la moyenne doivent être séparées par un point-virgule dans Excel en français (ou par des virgules dans la version anglaise).

Comment calculer une moyenne en tenant compte du texte et des valeurs logiques ?

Si vous souhaitez que la moyenne arithmétique tienne compte des cellules contenant du texte ou des valeurs logiques du type VRAI/FAUX, utilisez la fonction AVERAGEA plutôt que la fonction MOYENNE. Le tableur de la suite bureautique gratuite LibreOffice en français traduit automatiquement la fonction AVERAGEA d'Excel en MOYENNEA.

► La fonction AVERAGEA d'Excel calcule la moyenne arithmétique, mais contrairement à la fonction MOYENNE, elle prend en compte les cellules contenant du texte, par exemple "Absent", ou contenant un texte vide (""). Ces données textuelles prennent part au calcul de la moyenne et ont pour valeur 0 (zéro).

► Les fonctions MOYENNE et AVERAGEA ne tiennent pas compte des cellules vides, mais elles tiennent compte des cellules contenant la valeur zéro.

► La valeur VRAI vaut 1, et la valeur FAUX vaut 0. Ces valeurs logiques comptent dans le calcul de la moyenne avec la fonction AVERAGEA, mais pas avec MOYENNE.

► Dans l'exemple ci-dessous, la formule =MOYENNE(A1:A8) donne un résultat de 2 car les cellules A4, A6 et A7 sont ignorées et :
((0 + 1 + 2 + 3 + 4) / 5) = 10 / 5 = 2

La formule =AVERAGEA(B1:B8) (ou seule la cellule vide B6 est ignorée) donne pour résultat 1,571428571, qui équivaut à :
((0 + 1 + 2 + 1[valeur logique VRAI] + 3 + 0[texte "non dispo"] + 4) / 7), soit 11 / 7.

► Si vous tapez juste une apostrophe ' en cellules A6 et B6 (ce qui, dans Excel, signifie que tout ce qui suit doit être considéré comme du texte), ces cellules sembleront vides mais comporteront en fait un texte... vide. Ce qui, pour Excel, est différent d'une cellule vide ! Cela n'aura pas d'incidence dans la formule =MOYENNE(), qui ne tient jamais compte du texte. En revanche, la formule =AVERAGEA() tiendra, elle, compte de ce texte vide, qui sera égal à zéro. Et le résultat de la formule sera donc cette fois 11/8=1,375 puisque 8 cellules prennent part à la moyenne, contre 7 cellules précédemment. Un texte vide peut également être renvoyé par une formule.

► Si, contrairement à vos attentes, les nombres tapés dans les cellules ne semblent pas considérés comme des valeurs numériques mais comme du texte (par exemple parce que les nombres décimaux comportent un point au lieu d'une virgule dans Excel en français), reportez-vous à nos explications Convertir du texte en nombre, date ou heure et Nettoyer des données Excel.

Comment calculer une moyenne sur un ou plusieurs critères ?

Excel propose deux fonctions : MOYENNE.SI qui calcule la moyenne arithmétique de toutes les cellules répondant à un seul critère, et MOYENNE.SI.ENS qui calcule la moyenne arithmétique des cellules répondant à plusieurs conditions.

Calculer une moyenne en fonction d'un critère dans Excel

► Voici la syntaxe de la fonction MOYENNE.SI avec ses trois arguments :

• MOYENNE.SI(plage; critère; [plage_moyenne])
• plage : obligatoire. C'est la plage de cellules dans laquelle le critère sera recherché. Si l'argument plage_moyenne n'est pas précisé, plage est aussi la plage de cellules numériques sur laquelle effectuer la moyenne arithmétique.
• critère : obligatoire. C'est le critère qui détermine si les cellules de la zone plage seront retenues ou non pour le calcul.
• plage_moyenne : facultatif. Représente la plage de cellules dont la moyenne arithmétique doit être calculée. Si cet argument est omis, l'argument plage est utilisé.

► Ci-dessous, quelques exemples de formules…

► =MOYENNE.SI(B2:B10;">=50") n'effectue la moyenne que sur les cellules supérieures ou égales à 50, soit (50+54+61)/3=55. La formule omet le troisième argument plage_moyenne.

► =MOYENNE.SI(A2:A10;"France";B2:B10) ne retient dans la plage A2:A10 que les cellules contenant exactement le mot "France" (notez que ce texte est inscrit entre guillemets), et effectue la moyenne sur la plage de valeurs numériques B2:B10. Soit (31+50+54)/3=45.

► =MOYENNE.SI(A2:A10;A15;B2:B10) est une autre façon d'écrire la même formule que ci-dessus, mais cette fois le mot France à rechercher est inscrit dans la cellule juste à gauche, en A15.

► Si la formule doit être recopiée, il est indispensable d'indiquer les deux plages (de critère et de calcul) en référence absolue, $A$2:$A$10 et $B$2:$B$10 : lors de la sélection de ces plages dans votre formule, il suffit de presser une seule fois la touche F4 (ou Fn+F4 sur certains PC/Mac portables). Ainsi, quand vous recopierez la formule de B15 dans les cellules B16 et B17, les deux plages resteront identiques, seule la référence A15 variera et deviendra A16 ou A17.

► Voici quelques exemples de critères (les guillemets doivent être tapés) :

• 3 (la cellule Région en C2:C12 doit contenir la valeur 3 pour que le Chiffre d'affaires en D2:D12 participe à la moyenne arithmétique)

  

• ">=100" (supérieur ou égal à 100)
• "<>0" (différent de zéro)
• "<16,5" (inférieur à 16,5)
• C14 (égal au contenu de la cellule C14 qui peut être un nombre, une date, une heure, du texte, le résultat d'une formule…)

  

• "<"&C5 (inférieur à la valeur de la cellule C5, qui peut contenir un nombre, une date, une heure, du texte)
• "France" (contient exactement le texte France ou FRANCE ou FRAnce, la fonction ne tient pas compte des majuscules, mais ce critère ne retient pas une cellule contenant France métropolitaine)
• "élève" (retient les cellules contenant élève ou Élève, mais pas élèves (texte différent) ni Elève car la fonction tient compte des accents)
• "p?m" (le caractère générique ? remplace un seul caractère quelconque (une lettre, un chiffre, un signe ou autre), donc retient PIM et Pam, mais pas poum).
• "France*" (le caractère générique * représente une suite de caractères de longueur quelconque, donc retient ici France, France Inter, Franceinfo et France Culture, mais pas Radio France)
• "*France*" (retient France, France Inter, Radio France, Radios de FRANCE et d'EUROPE)
• "*~?" (si vous souhaitez recherche les deux caractères ? et *, qui correspondent aux caractères génériques, faites-les précéder du tilde ~, ici on retient les cellules commençant par n'importe quel texte et finissant par un point d'interrogation)
• ">01/01/2022" (strictement supérieur à la date indiquée du type JJ/MM/AAAA)

  

• ">="&B8 (supérieur ou égal à la valeur indiquée en cellule B8, qui peut être un nombre, une date, une heure, du texte)
• "<"&AUJOURDHUI() (inférieur à la fonction AUJOURDHUI(), qui renvoie la date du jour)

Calculer une moyenne en fonction de plusieurs critères dans Excel

Si les cellules à prendre en compte dans la moyenne arithmétique doivent répondre à plusieurs critères, utilisez la fonction MOYENNE.SI.ENS.

► Voici la syntaxe de la fonction MOYENNE.SI.ENS avec ses arguments, séparés par des points-virgules :

• MOYENNE.SI.ENS(plage_moyenne;plage_critère1;critère1;[plage_critère2;critère2];...)
• plage_moyenne : obligatoire. Cellules dont la moyenne doit être calculée, comprenant des nombres, des noms, des tableaux, des références contenant des nombres, etc.
• plage_critère1, plage_critère2, … : plage_critère1 est obligatoire, les autres plage_critère (jusqu'à 127) sont facultatives. Il s'agit d'une plage de cellules dans laquelle vérifier le critère qui suit.
• critère1, critère2, … : critère1 est obligatoire, les critères supplémentaires (jusqu'à 127) sont facultatifs. Pour préciser vos critères, reportez-vous à nos explications plus haut sur les critères de la fonction MOYENNE.SI, par exemple 10, ">=100" (supérieur ou égal à 100), "<>0" (différent de zéro), "*France*", etc.

► Voici un exemple avec trois critères. On calcule la moyenne des chiffres d'affaires si la Région est 3, la Date est supérieure ou égale au 1^er juillet 2021, et inférieure ou égale au 31 décembre 2021. Ce qui correspond à la moyenne des CA de Léo et Gabriel.

► Notez qu'en plus des fonctions MOYENNE.SI et MOYENNE.SI.ENS, Excel propose les fonctions SOMME.SI et SOMME.SI.ENS, NB.SI et NB.SI.ENS, MIN.SI.ENS et MAX.SI.ENS.

Comment calculer une moyenne en ignorant les valeurs 0 ?

Pour calculer la moyenne sans tenir compte des valeurs 0, utilisez par exemple la fonction MOYENNE.SI d'Excel.

► Dans une feuille de calcul Excel, si les cellules contenant la valeur 0 semblent vides, c'est que dans Fichier > Options > Options avancées > section Options de la feuille de calcul, la case Afficher un zéro dans les cellules qui ont une valeur nulle a été décochée. Si vous n'avez touché à rien, cette case est cochée. Il s'agit juste d'une option d'affichage, la cellule sera de toute façon prise en compte dans les calculs avec sa valeur zéro.

► Pour ne pas tenir compte des valeurs nulles dans le calcul d'une moyenne, utilisez la fonction MOYENNE.SI et le critère "<>0" (différent de zéro), par exemple :
=MOYENNE.SI(B2:B10;"<>0")

► Ci-dessous, la formule =MOYENNE(B2:B10) tient compte de la valeur 0 associée à la Grèce et divise donc le total (360) par 9 (pays), ce qui donne 40. En revanche, la formule =MOYENNE.SI(B2:B10;"<>0") exclut la Grèce (car B6=0) et divise donc 360 par 8 (pays), ce qui donne 45.

►Vous pouvez éviter le message #DIV/0! en imbriquant votre formule dans une fonction SIERREUR(), nous vous l'expliquons dans la section suivante.

Que faire si la moyenne renvoie une erreur #DIV/0 ou #VALEUR ou #NOMBRE ?

Une formule Excel renvoie l'erreur #DIV/0! si l'on tente d'effectuer une division par zéro. Dans le cas des fonctions MOYENNE, MOYENNE.SI et MOYENNE.SI.ENS, cela signifie sûrement que la plage de cellules indiquée ne comporte aucune valeur numérique pour calculer la moyenne arithmétique. Par exemple si aucune cellule ne remplit le critère indiqué par MOYENNE.SI. La fonction SIERREUR permet de régler élégamment le problème en affichant un message ou une valeur de votre choix. Autre cas que nous allons régler : quand la moyenne renvoie l'erreur #DIV/0! ou l'erreur #VALEUR! (ou une autre erreur comme #NOMBRE!) car l'une des cellules prenant part au calcul renvoie cette même erreur.

► Dans l'exemple ci-dessous, la formule en B18 renvoie une erreur #DIV/0! car le Portugal n'apparaît jamais dans la plage de données A2:A10.

► La formule en B19 recherche la Grèce dans la même plage de données A2:A10 et ne la trouve pas non plus, mais la fonction SIERREUR permet d'afficher le message Introuvable plutôt qu'une erreur #DIV/0!.

► Au lieu de renvoyer un message textuel en cas d'erreur, vous pouvez bien sûr renvoyer une valeur numérique ou le résultat d'une formule que vous indiquez, par exemple :
=SIERREUR(MEDIANE(B2:B10);0) renvoie la valeur zéro en cas d'erreur.

► Voici la syntaxe de la fonction SIERREUR et de ses deux arguments :

• SIERREUR(valeur;valeur_si_erreur)
• valeur : obligatoire. Représente la formule vérifiée.
• valeur_si_erreur : obligatoire. C'est la valeur à renvoyer si la formule génère une erreur. Les types d'erreurs possibles sont : #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM? et #NUL!.

► Concrètement, SIERREUR renvoie le résultat de la formule indiquée dans valeur si cette formule ne produit pas une erreur. Si son résultat est une erreur, SIERREUR renvoie le message ou la valeur indiquée dans valeur_si_erreur.

► Si la traduction automatique en français de l'aide en ligne vous irrite, reportez-vous aux explications en anglais de la fonction IFERROR.

►Autre cas : si l'une des cellules qui prennent part au calcul de la moyenne renvoie la valeur #VALEUR! ou #DIV/0! (ou une autre erreur), alors la moyenne renverra cette même erreur. Réglons ce problème…

► Dans l'exemple ci-dessous, les moyennes en cellules E2 et E4 renvoient une erreur car l'une des notes sur la ligne comporte une erreur (#VALEUR! ou #DIV/0!).

► Notre formule en F2 (que nous avons recopiée en F3 et F4) ne tient plus compte des cellules en erreur et renvoie donc la bonne moyenne. Il s'agit toutefois d'une "formule matricielle", qui implique une validation différente selon la version d'Excel que vous utilisez.

► Si vous utilisez la version la plus récente du tableur, Excel pour Microsoft 365, il vous suffit de taper notre formule en pressant comme d'habitude la touche Entrée, Excel comprend d'office qu'il s'agit d'une formule matricielle :
=MOYENNE(SIERREUR(B2:D2;""))

► Si vous utilisez une version d'Excel plus ancienne (ou un tableur compatible Excel comme LibreOffice Calc), vous devez valider cette formule matricielle en tapant Ctrl+Maj+Entrée au lieu de la touche Entrée, sans quoi la formule renverra toujours une erreur. Certains sites Web vous indiquent qu'il faut valider les formules matricielles par la combinaison Ctrl+Shift+Entrée, cela revient au même : Shift est le nom anglais de la touche Maj des claviers français azerty.

► Ci-dessous, notre même tableau de chiffres, mais dans une ancienne version du tableur, en l'occurrence Excel 2007 : après validation de la formule =MOYENNE(SIERREUR(B2:D2;"")) par Ctrl+Maj+Entrée, la formule est automatiquement entourée de signes {} (visibles dans la barre de formule) et elle donne le bon résultat. Nous avons inscrit ces signes {} en rouge car vous ne devez jamais taper au clavier les signes {} qui encadrent une formule matricielle : c'est Excel qui les ajoute (dans la barre de formule) pour vous indiquer qu'il a compris que vous vouliez que la formule soit interprétée sous forme matricielle.

► Si vous éditez la cellule pour modifier la formule, Excel n'affiche pas les signes {}. Mais n'oubliez pas de revalider par Ctrl+Maj+Entrée, sinon la formule ne sera plus interprétée comme une formule matricielle !

► Les versions les plus récentes d'Excel acceptent que vous validiez une formule par Ctrl+Maj+Entrée, même si ce n'est plus nécessaire dans la plupart des cas pour l'interpréter comme une formule matricielle. Dans ce cas, la formule débute et se termine par des signes {} comme dans les versions précédentes d'Excel.

► Si vous créez un classeur dans une version récente d'Excel, qui ne réclame plus la validation des formules matricielles par Ctrl+Maj+Entrée, puis que vous ouvrez ce fichier dans une ancienne version d'Excel (ou dans LibreOffice, par exemple), les formules matricielles donneront le résultat correct et seront encadrées de signes {}.

Comment calculer une moyenne pondérée dans Excel ?

Voici comment calculer facilement une moyenne arithmétique avec coefficients, dans toutes les versions d'Excel. Notre premier exemple vous est proposé à des fins didactiques : préférez la méthode présentée dans notre deuxième exemple, celle dont la formule s'appuie sur la fonction SOMMEPROD d'Excel.

► Dans une moyenne pondérée, à chaque valeur est associé un coefficient multiplicateur. Sur l'exemple ci-dessous, vous devrez donc d'abord multiplier la valeur de chaque matière par son coefficient, additionner ces résultats puis diviser le tout par la somme des coefficients.

► Cela revient à écrire la formule :

=((B2*C2)+(B3*C3)+(B4*C4)+(B5*C5))/(C2+C3+C4+C5)

► Comme on le voit sur notre exemple, la moyenne pondérée (13,08) est différente de la moyenne arithmétique (12,75), qui ne tient pas compte des coefficients. Les deux moyennes seraient évidemment identiques si tous les coefficients étaient égaux à 1.

► Il existe une manière plus simple de rédiger cette formule dans Excel. Utilisez la fonction SOMMEPROD qui, dans la plage de cellules, va multiplier chaque note par son coefficient. La fonction SOMME permettra ensuite d'additionner les coefficients pour effectuer la division.

► Sur notre exemple, la formule est donc :

=SOMMEPROD(B2:B5;C2:C5)/SOMME(C2:C5)

► Si une matière n'est pas renseignée, par exemple parce qu'un élève ne suit pas de cours d'anglais, laissez le coefficient à 0 (ou laissez vide cette cellule du coefficient, ici en cellule C3) afin que la matière ne soit pas prise en compte dans la moyenne pondérée. Si vous vous contentez de laisser la note en anglais vide ou égale à zéro (en B3), la formule tiendra quand même compte du coefficient de l'anglais et la moyenne pondérée sera différente.

► Autre astuce : si vous souhaitez dupliquer la formule d'une cellule à l'autre, afin qu'elle fasse toujours référence à la même plage de coefficients, utilisez une référence absolue grâce au signe $. Le mieux est de prendre un exemple…

► En cellule F3, quand vous rédigez la formule =SOMMEPROD(, sélectionnez à la souris la plage des coefficients B2:E2 puis pressez une (seule) fois la touche de fonction F4 pour que la référence se transforme en référence "absolue" $B$2:$E$2. Faites de même pour la fonction SOMME(. Sur certains portables Mac et PC, vous devrez presser les deux touches Fn+F4 pour que cela fonctionne. Si vous n'y parvenez pas, tapez vous-même la référence de la plage avec les signes $.

► L'exemple ci-dessus vous montre la formule située en F3 que l'on a copiée-collée en cellule F4 : dans SOMMEPROD, la référence "relative" B3:E3, qui correspond aux notes de Pierre, se transforme automatiquement en B4:E4 pour tenir compte cette fois des notes de Paul, c'est parfait. Et la référence "absolue" $B$2:$E$2 reste inchangée d'une formule à l'autre, on fait donc bien toujours référence aux coefficients. Idem dans la fonction SOMME qui effectue la somme des coefficients.

► La fonction SOMMEPROD est très puissante, vous pouvez remplacer la multiplication par une addition, soustraction ou division. Explorez les possibilités de SOMMEPROD dans l'aide en français ou, encore mieux (car sans erreurs de traduction ni contresens), dans l'aide en anglais SUMPRODUCT.

Comment calculer la médiane dans Excel ?

Si le calcul de la médiane vous semble plus instructive ou pertinente que la moyenne arithmétique pour interpréter une tendance centrale, tournez-vous vers la fonction MEDIANE d'Excel. Quand vous rédigez votre formule, écrivez indifféremment =médiane(…) avec ou sans accent, Excel transformera la formule en =MEDIANE(…).

► La fonction MEDIANE s'utilise comme la fonction MOYENNE, elle tient compte des cellules contenant la valeur zéro, mais pas des cellules vides ou contenant du texte ou des valeurs logiques de type VRAI/FAUX.

► Dans une cellule, inscrivez =mediane( puis sélectionnez une plage de cellules à la souris, ou indiquez jusqu'à 255 cellules ou plages de cellules ou plages nommées, séparées par des points-virgules.

► Si la plage de cellules sélectionnées ne contient aucune donnée numérique, la fonction MEDIANE renvoie l'erreur #NOMBRE!. Dans votre formule, imbriquez alors la fonction MEDIANE dans une fonction SIERREUR comme expliqué plus haut.
Par exemple =SIERREUR(MEDIANE(xx:xx);"Votre message")
Ou =SIERREUR(MEDIANE(xx:xx);0)

► Excel ne propose pas de fonction MEDIANE.SI mais il est possible par formule de ne retenir que les cellules répondant à un ou plusieurs critères en exploitant la fonction SI.

Exemples de "médiane si avec une seule condition"

Si vous utilisez une version d'Excel antérieure à Excel pour Microsoft 365 ou d'un tableur compatible Excel, vous devez valider les formules qui suivent par Ctrl+Maj+Entrée au lieu de Entrée, car il s'agit de formules matricielles.

► Les trois premières formules permettent respectivement de calculer le salaire médian si un critère est respecté :

► =MEDIANE(SI(A2:A6="Strasbourg";D2:D6)) La Succursale doit être le texte "Strasbourg".

► =MEDIANE(SI(A2:A6=B10;D2:D6)) La Succursale doit être égale au contenu de la cellule B10, qui contient ici le texte Strasbourg.

► =MEDIANE(SI(C2:C6>30;D2:D6)) L'Âge doit être strictement supérieur à 30.

Exemples de "médiane si avec plusieurs conditions"

Notre quatrième formule (ci-dessus) calcule la médiane si plusieurs critères sont remplis. Dans la fonction SI, il suffit d'indiquer chaque critère entre parenthèses, autant de fois que vous le souhaitez. Si vous utilisez une version d'Excel antérieure à Excel pour Microsoft 365, vous devez valider cette formule matricielle par Ctrl+Maj+Entrée au lieu de Entrée.

► Dans la fonction SI, utilisez le signe * (la multiplication) pour préciser qu'absolument toutes les conditions doivent être respectées.
=MEDIANE(SI((A2:A6="Strasbourg")*(C2:C6>=30)*(D2:D6>2000);D2:D6))

► Dans la fonction SI, utilisez le signe + (l'addition) pour préciser que l'une au moins des conditions doit être respectée, mais pas forcément toutes.
=MEDIANE(SI((A2:A6="Lille")+(C2:C6>=30)+(D2:D6>2000);D2:D6))

► Si aucune des conditions n'est respectée, la formule renvoie une erreur #NOMBRE!, que vous pouvez neutraliser comme expliqué plus haut grâce à la fonction SIERREUR.