Comment calculer la médiane tout en ignorant les zéros et les erreurs dans Excel ?
Dans de nombreuses tâches d’analyse de données sous Excel, le calcul précis de la médiane est essentiel pour comprendre la tendance centrale de votre jeu de données. Toutefois, il arrive que vos données contiennent des zéros ou des valeurs d’erreur (telles que)#DIV/0!, #N/A, etc.), ce qui peut fausser un calcul simple de la médiane. Par exemple, l’utilisation de la formule standard =MEDIANE(plage) inclut les zéros dans le calcul et renvoie une erreur dès qu’une cellule invalide est présente dans la plage, ce qui peut conduire à des résultats trompeurs ou à l’échec du calcul, comme illustré ci-dessous.
Pour remédier à cela, plusieurs solutions permettent de calculer la médiane tout en excluant les zéros et les erreurs, garantissant ainsi une analyse à la fois précise et robuste. Elles s’adaptent à divers scénarios — nettoyage de données d’enquête, de rapports financiers ou de mesures scientifiques — où l’élimination des zéros et des erreurs est essentielle pour obtenir des résultats significatifs. Vous trouverez ci-dessous des guides pratiques, étape par étape, pour chaque méthode disponible dans Excel, des formules directes aux techniques avancées d’automatisation.
Médiane en ignorant les erreurs
VBA : Médiane en ignorant les zéros et les erreurs (fonction personnalisée)
Power Query : Médiane après filtrage des zéros/erreurs
Médiane en ignorant les zéros
Lorsque votre plage contient des zéros que vous ne souhaitez pas inclure dans le calcul de la médiane — par exemple, des valeurs manquantes codées comme 0 —, optez pour une formule matricielle afin de les exclure. Cette solution s’avère particulièrement efficace avec les jeux de données où les zéros tiennent lieu de données manquantes plutôt que de véritables mesures.
Sélectionnez une cellule dans laquelle vous souhaitez afficher la médiane (par exemple, C2) et saisissez la formule suivante :
=MEDIAN(IF(A2:A17<>0,A2:A17))Après avoir saisi la formule, au lieu d’appuyer simplement sur Entrée, appuyez sur Ctrl + Maj + Entrée pour en faire une formule matricielle (des accolades apparaîtront autour de la formule dans la barre de formule). Cela garantit que seules les valeurs non nulles de la plage A2:A17 sont prises en compte pour le calcul de la médiane. Voir la capture d’écran :
Conseils :
- Si vous utilisez Excel 365, Excel 2021 ou une version ultérieure, appuyez simplement sur Entrée, grâce à la prise en charge des tableaux dynamiques.
- Assurez-vous qu’il y a au moins une valeur numérique non nulle dans la plage, sinon la formule renverra l’erreur #NOMBRE!.
- Cette solution est idéale pour nettoyer les réponses à des enquêtes, les notes de frais ou les données de ventes lorsque les zéros doivent être exclus de l’analyse.
Médiane en ignorant les erreurs
Les valeurs d’erreur telles que #N/A, #DIV/0! ou #VALEUR! peuvent faire échouer la fonction médiane standard, interrompant ainsi votre analyse des données. Pour calculer la médiane en toute sécurité tout en excluant ces erreurs, utilisez la formule matricielle suivante.
Sélectionnez n’importe quelle cellule où vous souhaitez afficher votre résultat et saisissez la formule ci-dessous :
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Après avoir saisi la formule, appuyez sur Ctrl + Maj + Entrée (sauf si vous utilisez Excel 365, Excel 2021 ou une version ultérieure, qui prend en charge les tableaux dynamiques). Cette formule inclut uniquement les valeurs de la plage F2:F17 qui sont de véritables nombres — en ignorant entièrement les cellules contenant des erreurs.
Conseils et mises en garde :
- Si toutes les cellules contiennent des valeurs d’erreur, le résultat renverra une erreur #NOMBRE! — assurez-vous que vos données incluent au moins un nombre valide.
- Vous pouvez combiner plusieurs critères d’exclusion — par exemple, exclure simultanément les zéros et les erreurs — en imbriquant des conditions.
- Cette formule s’avère particulièrement utile lorsqu’on travaille avec des données importées, des résultats d’enquête ou des états financiers susceptibles de contenir des calculs partiels ou erronés.
VBA : Médiane en ignorant les zéros et les erreurs (fonction personnalisée)
Lorsque vous devez fréquemment calculer la médiane tout en ignorant à la fois les zéros et les erreurs, ou lorsque vous souhaitez éviter la saisie manuelle de formules matricielles, optez pour une fonction VBA personnalisée (fonction définie par l’utilisateur, UDF). Cette solution allie souplesse et efficacité : elle intègre tous vos critères d’exclusion et s’utilise comme n’importe quelle fonction native, ce qui la rend particulièrement adaptée aux grands jeux de données ou à ceux mis à jour régulièrement.
Configuration de la fonction personnalisée :
- Cliquez sur l’onglet Développeur dans Excel. S’il n’est pas visible, activez-le via Fichier > Options > Personnaliser le ruban.
- Cliquez sur Visual Basic pour ouvrir l’éditeur VBA.
- Dans l’éditeur VBA, cliquez sur Insertion > Module pour créer un nouveau module.
- Copiez et collez le code suivant dans le module :
Function MedianIgnoreZeroError(rng As Range) As Variant
Dim cell As Range
Dim tempList() As Double
Dim count As Integer
count = 0
On Error Resume Next
xTitleId = "KutoolsforExcel"
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value <> 0 And Not IsError(cell.Value) Then
count = count + 1
ReDim Preserve tempList(1 To count)
tempList(count) = cell.Value
End If
End If
Next cell
On Error GoTo 0
If count = 0 Then
MedianIgnoreZeroError = CVErr(xlErrNum)
Else
MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
End If
End FunctionUtilisation de la fonction personnalisée :
Après être revenu(e) à Excel, saisissez simplement la formule =MedianIgnoreZeroError(A2:A17)dans n’importe quelle cellule (remplacez)A2:A17 par votre plage cible). Contrairement aux formules matricielles, il suffit d’appuyer sur Entrée — aucune combinaison Ctrl + Maj + Entrée n’est nécessaire.
- Cette méthode convient parfaitement aux très grands jeux de données, évite les particularités des formules matricielles et peut être adaptée pour ignorer d’autres valeurs indésirables en modifiant davantage le code.
- Si la plage ne contient que des zéros ou des erreurs, le résultat affichera #NOMBRE!
- Si vous obtenez une erreur #NOM?, vérifiez que la macro VBA est correctement installée et que les macros sont activées dans les paramètres d’Excel.
Power Query : Médiane après filtrage des zéros/erreurs
Power Query est un outil puissant d’Excel pour importer, transformer et analyser des données — idéal notamment pour nettoyer et prétraiter de grands jeux de données avant d’y appliquer des calculs tels que la médiane. Grâce à Power Query, vous filtrez aisément les zéros et les erreurs, en ne conservant que les valeurs numériques valides nécessaires à votre calcul. Cette approche s’avère particulièrement avantageuse lorsque vos données sources sont régulièrement mises à jour ou importées depuis des systèmes externes.
Étapes pour utiliser Power Query afin de calculer la médiane en ignorant les zéros et les erreurs :
- Sélectionnez n’importe quelle cellule de votre plage de données, puis accédez à l’onglet Données et cliquez sur À partir d’un tableau/plage. Si vos données ne sont pas déjà au format tableau, Excel vous proposera d’en créer un : cliquez sur OK.
- La fenêtre de l’Éditeur Power Query s’ouvre. Cliquez sur la flèche déroulante de la colonne concernée et décochez 0pour filtrer les valeurs nulles. (Pour filtrer les erreurs, cliquez avec le bouton droit sur l’en-tête de colonne et choisissez)Supprimer les erreurs.)
- Une fois le filtrage effectué, cliquez sur Accueil > Fermer et charger pour renvoyer les données nettoyées vers votre feuille de calcul.
- Appliquez désormais la formule standard
=MEDIANE()à la colonne contenant uniquement les valeurs filtrées, puisque les données excluent désormais tous les éléments indésirables.
Cette méthode garantit que vos données d’origine restent inchangées, offre une forte reproductibilité avec de nouvelles données ou des mises à jour, et est particulièrement efficace pour les tâches de reporting récurrentes ou lorsqu’on travaille avec de grands jeux de données ou des sources externes. Les flux Power Query peuvent être actualisés en un seul clic dès que vos Données source changent, minimisant ainsi les interventions manuelles et les risques d’erreur.
- Power Query est disponible dans Excel 2016 et les versions ultérieures (ou en tant que complément pour Excel 2010 et 2013).
- Après transformation, les calculs peuvent être effectués sur les données propres obtenues, renforçant ainsi la fiabilité des analyses en aval.
En cas de résultats inattendus, examinez attentivement vos étapes de filtrage dans Power Query et assurez-vous que vos données nettoyées contiennent encore des valeurs numériques valides.
En résumé, qu’il s’agisse d’utiliser directement des formules matricielles, de créer une solution VBA personnalisée pour automatiser les calculs ou de tirer parti de Power Query pour gérer des flux plus complexes, Excel vous offre plusieurs options pratiques pour calculer la médiane tout en ignorant les zéros et les erreurs. Choisissez celle qui correspond le mieux à la taille de votre jeu de données, à la fréquence de mise à jour et à vos préférences en matière de flux de travail afin d’obtenir des résultats fiables et précis.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels