Les erreurs de formule dans Excel et leurs solutions
« Les erreurs sont une opportunité d’amélioration ». Dans n’importe quelle tâche ou système, des erreurs se produisent. Excel ne fait pas exception. En essayant de faire quelque chose avec une formule, vous rencontrerez divers types d’erreurs Excel. Ces erreurs peuvent faire de votre formule / tableau de bord / rapports un gaspillage absolu. Et si vous ne savez pas pourquoi un type d’erreur spécifique s’est produit, vous devrez peut-être passer des heures à les résoudre.
En travaillant sur Excel, j’ai rencontré de nombreuses erreurs Excel. Avec quelques difficultés et des recherches sur Google, j’ai contourné ces erreurs. Dans cet article, je vais expliquer certaines erreurs Excel courantes et ennuyeuses qui se produisent dans Excel. Nous discuterons pourquoi ces erreurs se produisent et comment les résoudre.
Que sont les erreurs de formule Excel Lors de l’application d’une formule qui aboutit à une erreur définie par Excel (#NA, #VALUE, #NAME, etc.) sont appelées erreurs de formule Excel. Ces erreurs sont détectées par Excel et imprimées sur les feuilles. Les raisons de ces erreurs peuvent être des valeurs indisponibles, un type d’arguments incorrect, une division par 0, etc.
Ils sont faciles à attraper et à réparer.
Les erreurs logiques ne sont pas détectées par Excel et elles sont plus difficiles à corriger. L’incohérence des données, la mauvaise saisie des données, les erreurs humaines, etc. sont des raisons courantes derrière ces erreurs. Ils peuvent également être corrigés, mais ils demandent du temps et des efforts. Il est préférable de préparer parfaitement vos données avant de les manipuler.
Attraper les erreurs de formule Excel:
Il existe des fonctions dédiées dans Excel pour détecter et gérer des types d’erreurs spécifiques (comme la fonction ISNA). Mais link: / excel-formule-and-function-iserror-function [fonction ISERROR] et link: / logical-formules-excel-iferror-function [fonction IFERROR] sont deux fonctions qui peuvent attraper n’importe quel type d’Excel Erreur (sauf logique).
Erreur #NA Excel #NA Une erreur se produit dans Excel lorsqu’une valeur n’est pas trouvée. Cela signifie simplement NON DISPONIBLE. Une erreur #NA est souvent rencontrée avec la fonction Excel VLOOKUP.
Dans l’image ci-dessus, nous obtenons une erreur #NA lorsque nous recherchons « Divya » dans la colonne A. C’est parce que « Divya n’est pas dans la liste.
Résolution de l’erreur #NA:
Si vous êtes sûr que la valeur de recherche doit exister dans la liste de recherche, le la première chose que vous devez faire est de vérifier la valeur de la recherche. Vérifiez si la valeur de la recherche est correctement orthographiée. Sinon, corrigez-la.
Deuxièmement, vous pouvez faire un lien: / lookup-formulas-partial-match -with-vlookup-function [correspondance partielle avec RECHERCHEV] `ou toute fonction de recherche. Si vous êtes sûr qu’une partie du texte doit correspondre, utilisez-la.
Si vous n’êtes pas sûr que cette valeur existe ou non peut être utilisé pour lier: / lookup-formulas-using-a-vlookup-formula-to-check-if-a-value-exists [vérifier si une valeur existe dans la liste ou non]. Dans l’image ci-dessus, nous pouvons dire que Divya n’est pas dans la liste.
Si vous voulez attraper l’erreur #NA et imprimer ou faire autre chose au lieu d’imprimer l’erreur #NA, vous pouvez utiliser link: / logical-formulas-excel -isna-function [Fonction Excel ISNA] `. ISNA functi on renvoie TRUE si une fonction renvoie une erreur #NA. En utilisant cela, nous pouvons éviter l’erreur #NA. ISNA fonctionne à merveille avec la fonction RECHERCHEV. Vérifiez-le `link: / tips-how-to-use-if-isna-and-vlookup-function-in-excel [here].
Erreur Excel #VALUE La #VALUE se produit lorsque l’argument fourni n’est pas de type pris en charge. Par exemple, si vous essayez d’ajouter deux textes à l’aide de l’opérateur arithmétique plus (+), vous obtiendrez une erreur #VALUE. La même chose se produira si vous essayez d’obtenir l’année d’un format de date non valide à l’aide de la fonction YEAR.
Comment corriger l’erreur #VALUE?
Tout d’abord, confirmez le type de données auquel vous faites référence. Si votre fonction a besoin d’un numéro, assurez-vous de vous référer à un numéro. Si un nombre est formaté sous forme de texte, utilisez link: / excel-generals-excel-value-function [fonction VALUE] pour les convertir en nombre. Si une fonction nécessite du texte (comme la fonction DATEVALUE) et que vous vous référez à un nombre ou un type de date, convertissez-les en texte.
Si vous prévoyez qu’il peut y avoir une erreur #VALUE et que vous voulez les attraper, alors vous pouvez utiliser ISERR, link: / excel-formula-and-function-iserror-function [ISERROR]
ou lien: / formule-logique-excel-iferror-function [IFERROR]
attraper et faire autre chose.
Erreur #REF Excel Le ‘ref’ dans #REF signifie référence. Cette erreur se produit lorsqu’une formule fait référence à un emplacement qui n’existe pas. Cela se produit lorsque nous supprimons des cellules de plages auxquelles une formule fait également référence.
En dessous de gif, la formule de somme fait référence à A2 et B2. Lorsque je supprime A2, la formule se transforme en erreur #REF.
Résoudre l’erreur Excel #REF:
La meilleure chose à faire est de faire attention avant de supprimer des cellules dans une donnée. Assurez-vous qu’aucune formule ne fait référence à cette cellule. Si vous avez déjà une erreur #REF, tracez-la, supprimez-la de la formule.
Par exemple, une fois que vous obtenez une erreur #REF, votre formule ressemblera à ceci.
|
=A2+#REF! |
Vous pouvez simplement supprimer le #REF! De la formule pour obtenir une formule sans erreur. Si vous souhaitez le faire en masse, utilisez la fonction de recherche et de remplacement.
Appuyez sur CTRL + H pour ouvrir la recherche et le remplacement. Dans la zone de recherche, écrivez #REF. Laissez la boîte de remplacement vide. Appuyez sur le bouton Remplacer tout.
Si vous souhaitez réajuster la référence à une nouvelle cellule, faites-le manuellement et remplacez le #REF! Avec cette référence valide.
Erreur #NAME Excel Le #NAME se produit dans Excel lorsqu’il ne peut pas identifier un texte dans une formule.
Par exemple, si vous avez mal orthographié le nom d’une fonction, Excel affichera l’erreur #NAME. Si une formule fait référence à un nom qui n’existe pas sur la feuille, elle affichera l’erreur #NAME.
Dans l’image ci-dessus, la cellule B2 a la formule = POUVOIRS (A2,2). POWERS n’est pas une fonction valide dans Excel, il renvoie donc une erreur #NAME.
Dans la cellule B3, nous avons = SUM (nombres). La somme est une fonction valide d’Excel mais « nombres »
lien: / excel-range-name-all-about-named-ranges-in-excel [named range] n’existe pas sur la feuille. Par conséquent, Excel renvoie #NAME? Erreur.
Comment éviter l’erreur #NAME dans Excel?
Pour éviter l’erreur #NAME dans Excel, épelez toujours correctement les noms des fonctions. Vous pouvez utiliser la suggestion Excel pour vous assurer que vous utilisez une fonction valide.
Chaque fois que nous tapons des caractères après le signe égal, excel affichez les fonctions et les plages nommées sur la feuille, à partir de ce ou ces caractères. Faites défiler vers le bas jusqu’au nom de la fonction ou au nom de la plage dans la liste de suggestions, appuyez sur la touche tab pour utiliser cette fonction.
Excel # DIV / 0! Erreur Comme son nom l’indique, cette erreur se produit lorsqu’une formule se traduit par une division par zéro. Cette erreur peut également se produire lorsque vous supprimez une valeur d’une cellule dont dépend une formule de division.
|
= |
Nous aurons une erreur DIV / 0 uniquement si le diviseur est 0 ou vide. Par conséquent, nous vérifions le diviseur (B2), s’il est nul, alors imprimons A2 sinon divisons A2 par B2. Cela fonctionnera également pour les cellules vides.
Erreur Excel #NUM Cette erreur se produit lorsqu’un nombre ne peut pas être affiché à l’écran. La raison peut être que le nombre est trop petit ou trop grand pour être affiché.
Une autre raison pourrait être qu’un calcul ne peut pas être effectué avec un nombre donné.
Dans l’image ci-dessus, dans la cellule C2, nous essayons d’obtenir une valeur de -16309. La valeur est si petite qu’elle ne peut pas être affichée.
Dans la cellule C3, nous essayons d’obtenir la racine carrée de la valeur -16. Comme il n’y a pas de valeur telle que la racine carrée d’une valeur négative (à l’exception des nombres imaginaires), Excel affiche donc un #NOMBRE! Erreur.
Comment résoudre #NOMBRE! Erreur?
Pour résoudre l’erreur #NUM, la première chose que vous pouvez faire est de vérifier également chaque valeur que vous faites référence. Vérifiez si ce sont les nombres valides avec lesquels votre formule peut fonctionner.
Utilisez les fonctions de compteur pour résoudre le format des nombres. Par exemple, dans l’exemple ci-dessus, si vous voulez obtenir la racine carrée de -16 mais que vous ne voulez pas changer la valeur de la cellule, nous pouvons utiliser la fonction ABS.
Cela renverra 4. Si vous souhaitez obtenir la valeur négative, utilisez le signe négatif avant la fonction. Ensuite, bien sûr, vous pouvez utiliser la fonction de gestion des erreurs bien sûr.
Sur la résolution de l’erreur #NUM, nous avons un article dédié. Vous pouvez le vérifier link: / tips-how-to-correct-a-num-error [here].
Erreur #NULL dans Excel Il s’agit d’un type d’erreur rare. Erreur #NULL causée par un référencement de cellule incorrect. Par exemple si vous voulez donner une référence à une plage A2: A5 dans la fonction SOMME, mais par erreur vous tapez A2 A5. Cela générera une erreur #NULL. Comme vous pouvez le voir dans l’image ci-dessous, la formule renvoie l’erreur #NULL. Si vous remplacez l’espace par la colonne (:), l’erreur #NULL disparaîtra et vous obtiendrez la somme de A2: A5. Si vous remplacez l’espace par une virgule (,), vous obtiendrez la somme de A2 et A5.
Comment résoudre l’erreur #NULL?
Comme nous savons que l’erreur #NULL est causée par une faute de frappe. Pour éviter cela, essayez de sélectionner des plages à l’aide du curseur au lieu de le saisir manuellement.
Il y aura des moments où vous devrez taper une adresse de plage à partir du clavier.
Faites toujours attention à la colonne de symboles de connexion (: *) ou à la virgule (,) pour éviter.
Si vous avez une erreur #NULL existante, vérifiez les références. Vous avez probablement manqué une colonne (:) ou une virgule (,) entre deux références de cellule. Remplacez-les par le symbole approprié et vous êtes prêt à partir.
Erreur dans Excel Parfois, nous pensons que cette erreur est due à un espace insuffisant pour afficher une valeur, mais ce n’est pas le cas. Dans ce cas, vous pouvez simplement étendre la largeur de la cellule pour afficher la valeur dans la cellule. Je n’appellerai pas ça une erreur.
En fait, cette erreur s’est produite lorsque nous essayons d’afficher une valeur de temps négative (il n’y a pas de temps négatif). Par exemple, si nous essayons de soustraire 1 de 12:21:00 PM, il renverra . Dans Excel, la première date est le 1/1/1900 00:00. Si vous essayez de soustraire le temps qui précède, Excel vous montrera une erreur . Plus vous augmentez la largeur, plus vous obtiendrez #. Je l’ai élaboré dans link: / excel-date-time-formules-convert-date-and-time-from-gmt-greenwich-mean-time-to-cst-central-standard-time [cet article].
Comment éviter l’erreur Excel?
Avant de faire des calculs arithmétiques dans Excel avec la valeur temps, gardez ces choses à l’esprit.
La valeur minimale de l’heure est 1/1/1900 00:00. Vous ne pouvez pas avoir de date valide avant que cela dans Excel 1 ne soit égal à 24 heures (1 jour) dans Excel. Tout en soustrayant les heures, les minutes et les secondes, convertissez-les en valeurs équivalentes. Par exemple, pour soustraire 1 heure de 12h21, vous devez en soustraire 1/24.
Suivi des erreurs dans Excel Nous savons maintenant quelles sont les formules Excel courantes et pourquoi elles se produisent. Nous avons également discuté de la solution possible pour chaque type d’erreurs Excel.
Parfois, dans les rapports Excel, nous obtenons des erreurs et nous ne pouvions pas savoir d’où l’erreur se produit réellement. Il devient difficile de résoudre ce genre d’erreurs. Pour traquer ces erreurs, Excel fournit une fonctionnalité de suivi des erreurs dans l’onglet Formule.
J’ai discuté de link: / tips-how-to-tracer-and-fix-formula-errors-in-excel [error tracing in Excel in detail].
Résolution des erreurs logiques dans la formule
Les erreurs logiques sont difficiles à trouver et à résoudre. Aucun massage montré par excel lorsque vous avez une erreur logique dans votre fonction. Tout va bien.
Mais vous seul savez que quelque chose ne va pas. Par exemple, tout en obtenant le pourcentage d’une partie du tout, vous diviseriez une partie par le total (= (partie / total) * 100). Il doit toujours être égal ou inférieur à 100%.
Lorsque vous obtenez plus de 100%, vous savez que quelque chose ne va pas.
C’était une simple erreur logique. Mais parfois, vos données proviennent de plusieurs sources, dans ce cas, il devient difficile de résoudre des problèmes logiques.
Une façon est d’évaluer votre formule.
Dans l’onglet formule, l’option évaluer la formule est disponible. Sélectionnez votre formule et cliquez dessus. Chaque étape de votre calcul vous sera montrée qui mène à votre résultat final. Ici, vous pouvez vérifier où le problème est survenu ou où le calcul a mal tourné.
Vous pouvez également suivre les dépendants et les précédents pour voir de quelles références dépend votre formule et quelles formules dépendent d’une cellule particulière.
Alors oui les gars, ce sont des types d’erreur courants auxquels tous les utilisateurs d’Excel sont confrontés.
Nous avons appris pourquoi chaque type d’erreur se produit et comment les éviter. Nous avons également appris la fonction Excel dédiée à la gestion des erreurs. Dans cet article, nous avons des liens vers des pages connexes qui traitent du problème en détail. Vous pouvez les consulter. Si vous rencontrez un type d’erreur spécifique qui vous ennuie, mentionnez-le dans la section commentaires ci-dessous. Vous obtiendrez la solution en quelque sorte.
Articles liés:
`lien: / conseils-comment-corriger-une-num-erreur [Comment corriger un #NOMBRE! Erreur] `
link: / tips-how-to-create-custom-error-bars-in-microsoft-excel-2010 [Create Custom Error Bars In Excel 2016]
link: / tips-value-error-and-how-to-fix-it-in-excel [#VALUE Error And How to Fix It in Excel]
link: / tips-how-to-tracer-and-fix-formula-errors-in-excel [Comment tracer et corriger les erreurs de formule dans Excel]
Articles populaires:
link: / formulas-and-functions-introduction-of-vlookup-function [La fonction RECHERCHEV dans Excel]
lien: / tips-countif-in-microsoft-excel [COUNTIF dans Excel 2016]
link: / excel-formule-et-fonction-excel-sumif-function [Comment utiliser la fonction SUMIF dans Excel]