Comment obtenir une moyenne fiable malgré la présence de valeurs manquantes dans Excel ?
Lorsque vous travaillez avec des données dans Excel, il est courant de rencontrer des plages contenant des valeurs manquantes ou des cellules remplies d’indicateurs d’erreur tels que #N/A ou #DIV/0!. Si vous utilisez la fonction standard MOYENNE d’Excel sur une telle plage, la formule renvoie une erreur au lieu d’ignorer les cellules problématiques. Vos calculs deviennent alors invalides ou inutilisables dès que vos données sont incomplètes ou contiennent des erreurs, comme le montre la capture d’écran ci-dessous. Être capable de calculer précisément la moyenne tout en ignorant ces valeurs manquantes ou erronées est essentiel — notamment dans les rapports commerciaux, la recherche universitaire ou tout scénario impliquant des jeux de données volumineux et complexes. Découvrez ci-dessous des solutions pratiques et des instructions étape par étape pour obtenir une moyenne fiable dans ces situations, et ainsi préserver l’intégrité de votre analyse de données.
Moyenne avec valeurs manquantes
Pour calculer une moyenne dans Excel tout en ignorant les valeurs manquantes ou les cellules d’erreur, vous pouvez utiliser plusieurs formules conçues pour écarter automatiquement les cellules non numériques et celles contenant des erreurs. Cette approche est essentielle, notamment pour analyser des résultats d’enquêtes (lorsque certaines réponses sont incomplètes), traiter des données de ventes sur une période donnée (avec des mois sans chiffres) ou exploiter des relevés scientifiques (où certaines mesures peuvent être absentes ou invalides). Voici deux solutions fiables fondées sur des formules, accompagnées de leurs détails.
Option 1 : Fonction AGREGAT
La fonction AGREGAT est particulièrement adaptée aux situations où vous vous attendez à des erreurs ou à des valeurs masquées dans votre plage de données, car elle vous permet de définir précisément les types de données à ignorer. Elle s’avère indispensable pour consolider des données provenant de sources multiples ou lorsque votre jeu de données est fréquemment mis à jour avec des entrées incohérentes.
1. Sélectionnez la cellule où vous souhaitez afficher la moyenne, puis saisissez la formule suivante :
=AGGREGATE(1,6,A2:C2)Ici, 1 indique l’opération MOYENNE, et 6 indique à Excel d’ignorer les valeurs d’erreur dans la plage A2:C2.
2. Après avoir saisi la formule, appuyez sur Maj + Ctrl + Entrée (pour les anciennes versions d’Excel) ou simplement sur Entrée si vous utilisez Excel 365 ou Excel 2019, car ces versions prennent en charge les tableaux dynamiques sans combinaison de touches supplémentaire.
Avantages : Facile à utiliser ; ignore automatiquement les erreurs et renvoie des résultats précis. Idéal pour les plages contenant des valeurs d’erreur.
Limites : Disponible uniquement à partir d’Excel 2010. Les versions antérieures d’Excel ne prennent pas en charge la fonction AGREGAT.
Option 2 : Formule matricielle MOYENNE avec SI et ESTNUM
Vous pouvez également calculer la moyenne des seules valeurs numériques — en ignorant automatiquement les erreurs et les cellules vides — à l’aide d’une formule matricielle. Cette approche est idéale lorsque vous souhaitez garantir que seuls les nombres valides sont pris en compte, une exigence courante dans les rapports mensuels, les systèmes de notation ou les feuilles financières.
1. Dans la cellule où vous souhaitez afficher votre moyenne, saisissez cette formule :
=AVERAGE(IF(ISNUMBER(A2:C2),(A2:C2)))Cette formule analyse chaque cellule de la plage A2:C2, conserve uniquement les valeurs numériques et en calcule la moyenne.
2. Une fois saisie, maintenez les touches Ctrl et Maj enfoncées, puis appuyez sur Entrée pour la transformer en formule matricielle dans les anciennes versions d’Excel (confirmé par l’apparition d’accolades dans la barre de formule). Dans Excel 365 et Excel 2019, il suffit d’appuyer sur Entrée.
Astuce : Si vos données s’étendent sur plusieurs lignes et que vous avez besoin de résultats pour chacune d’elles, utilisez la poignée de recopie (le petit carré en bas à droite de la cellule sélectionnée) pour étirer la formule vers le bas. Celle-ci ajuste automatiquement les références ligne par ligne.
Avantages : Compatible avec toutes les versions d’Excel ; ignore les valeurs non numériques et les erreurs ; fiable, même en cas d’erreurs ou de données manquantes.
Limites : Nécessite d’être saisie sous forme de formule matricielle dans les anciennes versions d’Excel, ce qui peut prêter à confusion. Une erreur de frappe entraîne une erreur #VALEUR! si la formule n’est pas entrée correctement.
Quelle que soit la méthode choisie, veillez à ce que la plage ciblée contienne exclusivement les données souhaitées : des lignes ou colonnes supplémentaires non intentionnelles pourraient fausser les résultats. En cas d’erreurs persistantes, vérifiez soigneusement votre saisie de formule (en particulier pour les formules matricielles dans les anciennes versions d’Excel), assurez-vous d’avoir sélectionné la bonne plage et confirmez l’absence de cellules fusionnées ou masquées susceptibles de perturber le calcul.
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