Comment calculer la médiane en ignorant les zéros ou les erreurs dans Excel ?
Dans de nombreuses tâches d'analyse de données dans Excel, le calcul précis de la médiane est essentiel pour comprendre la tendance centrale de votre jeu de données. Cependant, parfois votre jeu de données contient des zéros ou des valeurs d'erreur (telles que #DIV/0!, #N/A, etc.), qui peuvent interférer avec un calcul simple de la médiane. Par exemple, l'utilisation de la formule standard =MEDIAN(range) inclura les zéros dans le calcul et retournera une erreur s'il y a des cellules non valides présentes dans la plage, ce qui pourrait conduire à des résultats trompeurs ou à des échecs de calcul, comme illustré ci-dessous.
Pour résoudre cela, plusieurs solutions peuvent vous aider à calculer la médiane tout en excluant les zéros ou les erreurs, garantissant ainsi que votre analyse est à la fois précise et robuste. Ces solutions sont adaptées à divers scénarios, tels que le nettoyage des données d'enquête, les rapports financiers ou les mesures scientifiques où les zéros ou les erreurs doivent être supprimés pour obtenir des résultats significatifs. Ci-dessous, vous trouverez des guides pratiques étape par étape pour chaque méthode disponible dans Excel, allant 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 (FPU)
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 prendre en compte dans le calcul de la médiane — comme des valeurs manquantes représentées par 0 — vous pouvez utiliser une formule matricielle pour exclure les zéros. Cela est particulièrement utile dans les jeux de données où les zéros sont des espaces réservés pour des données indisponibles plutôt que des mesures réelles.
Sélectionnez une cellule où vous souhaitez afficher la médiane (par exemple, C2) et entrez la formule suivante :
=MEDIAN(IF(A2:A17<>0,A2:A17))Après avoir saisi la formule, au lieu de simplement appuyer sur Entrée, appuyez sur Ctrl + Maj + Entrée pour en faire une formule matricielle (vous verrez des accolades apparaître autour de la formule dans la barre de formule). Cela garantit que seules les valeurs non nulles dans A2:A17 sont prises en compte pour le calcul de la médiane. Voir la capture d'écran :
Conseils :
- Si vous utilisez Excel 365 ou Excel 2021 et versions ultérieures, appuyer sur Entrée seul suffit, grâce à la prise en charge des tableaux dynamiques.
- Assurez-vous qu'il y ait au moins une valeur numérique non nulle dans la plage, sinon la formule retournera une erreur #NOMBRE!
- Cette solution est idéale pour nettoyer les réponses aux enquêtes, les rapports de dépenses ou les données de ventes où 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 provoquer une erreur de la fonction médiane standard, interrompant ainsi votre analyse de données. Pour calculer la médiane en toute sécurité en excluant ces erreurs, vous pouvez utiliser la formule matricielle suivante.
Sélectionnez n'importe quelle cellule où vous souhaitez afficher votre résultat, puis entrez la formule ci-dessous :
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Après avoir entré la formule, appuyez sur Ctrl + Maj + Entrée (sauf si vous utilisez Excel 365/Excel 2021 ou versions ultérieures, qui permettent les tableaux dynamiques). Cette formule n'inclut que les valeurs dans F2:F17 qui sont de véritables nombres — ignorant complètement toutes les cellules d'erreur.
Conseils et précautions :
- Si toutes les cellules sont des valeurs d'erreur, le résultat retournera une erreur #NOMBRE! — assurez-vous que vos données incluent au moins un nombre valide.
- Vous pouvez combiner les critères d'exclusion (par exemple, exclure à la fois les zéros et les erreurs) en imbriquant des conditions.
- Cette formule est particulièrement utile lors du travail avec des données importées, des résultats d'enquêtes ou des états financiers qui peuvent contenir des calculs partiels ou ayant échoué.
VBA : Médiane en ignorant les zéros et les erreurs (FPU)
Pour les scénarios où vous devez fréquemment calculer la médiane en ignorant à la fois les zéros et les erreurs, ou nécessitez une solution qui évite de saisir manuellement des formules matricielles, vous pouvez utiliser une fonction VBA personnalisée (User-Defined Function, FPU). Cette approche offre une flexibilité supplémentaire car la fonction personnalisée peut encapsuler tous les critères d'exclusion et être utilisée comme n'importe quelle formule intégrée, ce qui la rend adaptée aux grands ensembles de données ou aux données fréquemment mises à jour.
Comment configurer la FPU :
- Cliquez sur l'onglet Développeur dans Excel. S'il n'est pas disponible, 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 FunctionComment utiliser la FPU :
Après être revenu dans 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 — il n'est pas nécessaire d'utiliser Ctrl + Maj + Entrée.
- Cette méthode fonctionne bien pour des ensembles de données très volumineux, é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 recevez une erreur #NOM?, vérifiez que la macro VBA est correctement installée et que les macros sont activées dans vos paramètres Excel.
Power Query : Médiane après filtrage des zéros/erreurs
Power Query est un outil puissant dans Excel pour importer, transformer et analyser des données — surtout lorsque votre objectif est de nettoyer et de prétraiter de grands ensembles de données avant d'effectuer des calculs comme la médiane. Avec Power Query, vous pouvez facilement filtrer les zéros et les erreurs, garantissant ainsi que seuls les nombres valides restent dans votre calcul. Cette approche est particulièrement bénéfique si 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 dans votre plage de données, puis allez dans l'onglet Données et cliquez sur À partir du tableau/plage. Si vos données ne sont pas déjà en format tableau, Excel vous invitera à créer un tableau — cliquez sur OK.
- La fenêtre Éditeur Power Query s'ouvrira. Cliquez sur la flèche déroulante pour la colonne concernée et décochez 0 pour filtrer les valeurs nulles. (Pour le filtrage des erreurs, faites un clic droit sur l'en-tête de colonne, choisissez Supprimer les erreurs.)
- Une fois filtré, cliquez sur Accueil > Fermer et charger pour renvoyer les données nettoyées vers votre feuille de calcul.
- Maintenant, appliquez la formule standard
=MEDIANE()à la colonne avec les valeurs filtrées uniquement, car les données excluent désormais tous les éléments indésirables.
Cette méthode garantit que vos données originales restent inchangées, offre une forte reproductibilité avec de nouvelles données ou des données mises à jour, et est particulièrement efficace pour des tâches de rapport récurrentes ou lorsque vous travaillez avec de grands ensembles de données ou des données externes. Les flux de travail Power Query peuvent être actualisés d'un seul clic dès que vos données sources changent, minimisant ainsi l'intervention manuelle et le risque d'erreurs.
- Power Query est disponible dans Excel 2016 et versions ultérieures (ou en tant qu'add-in pour Excel 2010 et 2013).
- Après transformation, les calculs peuvent être effectués sur les données nettoyées résultantes, offrant une plus grande fiabilité pour l'analyse en aval.
En cas de résultats inattendus, revérifiez vos étapes de filtrage dans Power Query et confirmez qu'il reste des valeurs numériques valides dans vos données nettoyées.
En résumé, que vous préfériez utiliser directement des formules matricielles, créer une solution VBA personnalisée pour l'automatisation, ou tirer parti de Power Query pour l'automatisation des grands flux de travail, Excel propose plusieurs options pratiques pour calculer la médiane tout en ignorant les zéros ou les erreurs. Choisissez la méthode qui convient le mieux à la taille de votre ensemble de données, à la fréquence des mises à jour et à vos préférences de workflow pour obtenir des résultats fiables et précis.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels