Fonctions statistiques : AVERAGE, MEDIAN, STDEV

Lorsque vous travaillez avec de vastes jeux de données, vous contenter de regarder des lignes de nombres donne rarement des informations pertinentes. Que vous analysiez des chiffres de vente, évaluiez les notes d'étudiants ou passiez en revue les dépenses trimestrielles, vous avez besoin de méthodes fiables pour synthétiser et interpréter vos données. C'est là que les fonctions statistiques intégrées d'Excel entrent en jeu.

Dans ce guide complet, nous allons explorer en détail les fonctions statistiques de base d'Excel : AVERAGE, MEDIAN, MODE et STDEV. En maîtrisant ces outils, vous passerez du simple stockage de données à une analyse complète et exploitable.

Comprendre les mesures de tendance centrale

Les mesures de tendance centrale sont des indicateurs statistiques utilisés pour trouver le centre ou la valeur « typique » d'un ensemble de données. Bien que l'on emploie souvent le mot « moyenne » dans le langage courant, l'analyse statistique divise la tendance centrale en trois concepts distincts : la moyenne arithmétique (AVERAGE), la valeur centrale (MEDIAN) et la valeur la plus fréquente (MODE).

La fonction AVERAGE (La moyenne)

La fonction AVERAGE calcule la moyenne arithmétique d'un groupe de nombres. Excel additionne tous les nombres d'une plage spécifiée et divise le total par la quantité de ces nombres.

Syntaxe : =AVERAGE(number1, [number2], ...)

• number1 : Le premier nombre, référence de cellule ou plage dont vous souhaitez calculer la moyenne (obligatoire).
• number2 : Nombres, références de cellules ou plages supplémentaires (facultatif).

Par exemple, si les cellules A1 à A5 contiennent les valeurs 10, 20, 30, 40 et 50, la formule =AVERAGE(A1:A5) renverra 30. La fonction AVERAGE ignore automatiquement les cellules vides et les chaînes de texte, garantissant que votre calcul ne soit pas faussé par des données non numériques.

La fonction MEDIAN (La valeur centrale)

La fonction MEDIAN trouve le nombre qui se situe exactement au milieu d'une liste de nombres triée. La moitié des nombres sera supérieure à la médiane, et l'autre moitié sera inférieure.

Syntaxe : =MEDIAN(number1, [number2], ...)

Pourquoi utiliser MEDIAN plutôt que AVERAGE ? La fonction AVERAGE est très sensible aux valeurs aberrantes : des valeurs extrêmes anormalement élevées ou basses. Par exemple, si vous calculez le revenu moyen d'une petite ville et qu'un milliardaire s'y installe, le revenu AVERAGE (la moyenne) montera en flèche, bien que le niveau de vie de tous les autres habitants n'ait pas changé. La fonction MEDIAN (la médiane), en revanche, reste stable, offrant un reflet plus précis du résident « typique ».

La fonction MODE (La valeur la plus fréquente)

Le mode représente la valeur qui apparaît le plus fréquemment dans votre jeu de données. Les versions modernes d'Excel proposent deux fonctions distinctes pour cela :

• MODE.SNGL : Renvoie la valeur unique la plus fréquente. En cas d'égalité, elle renvoie la première valeur rencontrée.
• MODE.MULT : Renvoie une matrice des valeurs les plus fréquentes (utile si plusieurs nombres sont à égalité pour la fréquence la plus élevée).

Syntaxe : =MODE.SNGL(number1, [number2], ...)

Comprendre les mesures de dispersion

Alors que la tendance centrale vous indique où se situe le centre de vos données, les mesures de dispersion vous indiquent comment vos données sont réparties autour de ce centre. Deux ensembles de données peuvent avoir exactement la même moyenne tout en étant complètement différents.

La fonction STDEV (L'écart type)

L'écart type mesure la distance moyenne entre vos points de données et la moyenne arithmétique. Un écart type faible signifie que les points de données sont regroupés étroitement autour de la moyenne (grande régularité). Un écart type élevé indique que les données sont dispersées sur une plus large plage de valeurs (grande volatilité).

Excel exige que vous définissiez si vos données représentent une population entière ou seulement un échantillon de cette population :

• STDEV.S (Échantillon) : Utilisée lorsque votre ensemble de données est un échantillon d'une population plus large. C'est le cas de figure le plus courant. Formule : =STDEV.S(range)
• STDEV.P (Population) : Utilisée lorsque votre ensemble de données inclut chaque membre du groupe cible sans exception. Formule : =STDEV.P(range)

Par exemple, si une machine fabrique des boulons qui doivent mesurer exactement 10 cm de long, un écart type faible indique une fabrication précise. Un écart type élevé signifie que la machine produit des boulons de longueurs imprévisibles, ce qui signale un besoin de maintenance.

Les fonctions MIN et MAX (L'étendue)

Pour comprendre la dispersion totale de vos données, vous pouvez utiliser les fonctions MAX et MIN afin de trouver respectivement la valeur la plus élevée et la valeur la plus basse. Soustraire le MIN du MAX vous donne l'« Étendue » (ou la plage) totale de votre jeu de données.

Exemple : =MAX(B2:B100) - MIN(B2:B100)

Statistiques conditionnelles : AVERAGEIF et AVERAGEIFS

Souvent, vous ne souhaitez pas calculer des statistiques pour une colonne entière ; vous voulez seulement analyser les lignes qui répondent à des critères spécifiques. De la même manière que vous utiliseriez SUMIF et SUMIFS pour des totaux, Excel propose AVERAGEIF et AVERAGEIFS pour des moyennes conditionnelles.

La fonction AVERAGEIFS vous permet de calculer la moyenne des cellules qui répondent à de multiples critères. Par exemple, faire la moyenne du chiffre d'affaires uniquement pour la région « Est » pendant le « T1 ».

Exercice pratique : Analyse de données RH

Pour voir ces fonctions statistiques en action, réalisons un exercice pratique. Ce scénario est extrêmement courant lorsque vous effectuez une analyse de données d'employés et RH sur Excel.

Imaginez que vous disposiez du jeu de données suivant représentant les salaires d'employés :

Cellule	Nom de l'employé	Service	Salaire
A2 / B2 / C2	John Doe	Informatique	60 000 $
A3 / B3 / C3	Jane Smith	Ventes	85 000 $
A4 / B4 / C4	Bob Johnson	Informatique	55 000 $
A5 / B5 / C5	Alice Williams	Direction	250 000 $
A6 / B6 / C6	Tom Davis	Ventes	62 000 $

Nous voulons comprendre la répartition des salaires au sein de l'entreprise. Écrivons les formules :

=AVERAGE(C2:C6)      // Returns $102,400
=MEDIAN(C2:C6)       // Returns $62,000
=STDEV.S(C2:C6)      // Returns $83,383
=MAX(C2:C6)          // Returns $250,000
=MIN(C2:C6)          // Returns $55,000

Analyse des résultats :
Regardez la différence entre AVERAGE (102 400 $) et MEDIAN (62 000 $). Pourquoi la moyenne est-elle si élevée ? Parce que le salaire de direction d'Alice, à hauteur de 250 000 $, est une valeur aberrante qui tire la moyenne vers le haut de manière significative. Si un candidat demande « quel est le salaire typique ici ? », lui répondre 102 400 $ serait trompeur. La médiane de 62 000 $ est une représentation bien plus honnête de la rémunération d'un employé typique.

De plus, l'écart type (STDEV) est très élevé (83 383 $), ce qui confirme mathématiquement ce que nous pouvons observer à l'œil nu : il y a une variance massive dans la façon dont les employés sont rémunérés.

Astuce de pro : Lors de la création de tableaux de bord avec ces formules, assurez-vous de bien comprendre les références de cellules Excel (en utilisant le signe $ pour verrouiller des plages comme $C$2:$C$6) si vous prévoyez de copier ces formules statistiques sur plusieurs colonnes.

Gérer les cellules vides, les erreurs et les zéros

Lors de l'utilisation de fonctions statistiques, des données imprécises peuvent conduire à des résultats inattendus. Voici comment Excel gère les problèmes de saisie de données courants :

• Cellules vides : AVERAGE, MEDIAN et STDEV ignorent complètement les cellules vides. Elles ne seront pas comptabilisées dans le dénominateur.
• Zéros : Contrairement aux cellules vides, le nombre 0 est comptabilisé. Si le salaire d'un employé est saisi à 0 au lieu d'être laissé vide, cela tirera artificiellement votre moyenne vers le bas. Si vous souhaitez faire la moyenne uniquement des nombres non nuls, utilisez =AVERAGEIF(range, ">0").
• Texte : Les fonctions statistiques standards ignorent les chaînes de texte. Cependant, si des nombres sont accidentellement formatés en tant que texte (souvent reconnaissable par un triangle vert dans le coin de la cellule), Excel les ignorera. Vous devez d'abord les convertir en nombres.
• Erreurs : Si une seule cellule de votre plage contient une erreur (comme #N/A ou #DIV/0!), toute la formule AVERAGE ou STDEV aboutira à une erreur. Utilisez la fonction AGGREGATE pour contourner les erreurs dans les plages.

Utiliser l'IA pour l'analyse statistique complexe

À mesure que vos jeux de données s'agrandissent, l'analyse statistique peut devenir mathématiquement complexe. Combiner des calculs d'écart type avec une logique conditionnelle (par exemple, « Trouver l'écart type des salaires uniquement pour le service Informatique, en excluant les zéros et les erreurs ») nécessite traditionnellement des formules matricielles difficiles ou des imbrications alambiquées.

C'est là que les outils modernes brillent. Tirer parti de l'analyse de données assistée par l'IA dans Excel transforme votre façon d'aborder la logique de données complexe. Au lieu de lutter pour vous rappeler s'il faut utiliser STDEV.P ou STDEV.S, ou comment imbriquer correctement AVERAGEIFS, vous pouvez simplement décrire votre besoin en langage naturel et laisser GPTExcel générer la formule exacte instantanément. Il gère la syntaxe, les parenthèses et la logique à la perfection.

Pour découvrir comment l'intelligence artificielle révolutionne la manière dont nous rédigeons des formules et analysons des métriques, consultez notre guide ChatGPT pour Excel : Rédiger des formules avec l'IA.

Foire aux questions

Pourquoi ma fonction AVERAGE renvoie-t-elle une erreur #DIV/0! ?

L'erreur #DIV/0! se produit dans une fonction AVERAGE lorsque la plage à laquelle vous faites référence ne contient aucune valeur numérique. Excel essaie de diviser la somme par zéro (le nombre de valeurs), ce qui est mathématiquement impossible. Assurez-vous que les cellules référencées contiennent de vrais nombres, et non des nombres stockés sous forme de texte.

Dois-je utiliser STDEV.S ou STDEV.P ?

Dans 95 % des situations réelles, vous devriez utiliser STDEV.S (Échantillon). Vous n'utilisez STDEV.P (Population) que si vous avez recueilli des données pour absolument tous les membres du groupe que vous analysez. Si vous analysez un échantillon d'une population plus vaste pour en tirer des conclusions, STDEV.S applique la correction mathématique appropriée.

La fonction MEDIAN peut-elle analyser des valeurs textuelles ?

Non, MEDIAN est une fonction purement mathématique et requiert des données numériques. Si vous tentez de calculer la médiane d'une plage composée entièrement de texte, Excel renverra une erreur #NUM!. Si vous avez besoin de trouver la chaîne de texte la plus fréquente, vous pouvez utiliser les fonctions INDEX et MATCH combinées à MODE.

Comment calculer une moyenne en ignorant les zéros ?

Étant donné que la fonction AVERAGE standard inclut les zéros dans son calcul (contrairement aux cellules vides), vous devez utiliser la fonction AVERAGEIF pour les exclure. La formule est =AVERAGEIF(A1:A100, "<>0"). Cela indique à Excel de ne calculer la moyenne que pour les cellules de la plage qui ne sont pas égales à zéro.