Détecter les erreurs dans les formules

Le tableau suivant récapitule les erreurs qu’un utilisateur risque de rencontrer le plus fréquemment lors de la saisie d’une formule et explique comment les corriger.

Assurez-vous de	Informations complémentaires
Commencer toutes les fonctions par le signe égal (=)	Si vous omettez le signe égal, ce que vous tapez peut être affiché sous forme de texte ou de date. Par exemple, si vous tapez SOMME(A1:A10), Excel affiche la chaîne de texte SOMME(A1:A10) et n’effectue pas le calcul. Si vous tapez 2/11, Excel affiche la date 02-nov (en partant du principe que le format de cellule est Général) au lieu de diviser 2 par 11.
Faire correspondre toutes les parenthèses ouvrantes et fermantes	Vérifiez si toutes les parenthèses font effectivement partie d’une paire (ouvrante et fermante). Lorsque vous utilisez une fonction dans une formule, il est important que chaque parenthèse soit dans sa position correcte pour que la fonction fonctionne correctement. Par exemple, la formule =SI(B5<0);"Non valide";B5*1,05) ne fonctionnera pas, car elle contient deux parenthèses fermantes et une seule parenthèse ouvrante, alors qu’il ne devrait y en avoir qu’une de chaque. La formule doit se présenter comme suit : =SI(B5<0;"Non valide";B5*1,05).
Utiliser un signe deux-points pour indiquer une plage	Lorsque vous faites référence à une plage de cellules, utilisez les deux-points (:) pour séparer, dans la plage, la référence de la première cellule de la référence de la dernière cellule. Par exemple, =SOMME(A1:A5), et non =SOMME(A1 A5), ce qui retournerait une #NULL! Erreur.
Taper tous les arguments requis	Certaines fonctions requièrent des arguments précis. Vérifiez également que vous n’avez pas introduit trop d’arguments.
Entrer le type correct d’arguments	Certaines fonctions, telles que SOMME, requièrent des arguments numériques. D’autres fonctions, telles que REMPLACER, requièrent une valeur de texte pour au moins l’un de leurs arguments. Si vous utilisez le mauvais type de données comme argument, Excel peut retourner des résultats inattendus ou afficher une erreur.
Ne pas imbriquer plus de 64 fonctions	Vous ne pouvez pas entrer, ou imbriquer, plus de 64 niveaux de fonctions au sein d’une fonction.
Placer d’autres noms de feuilles entre des guillemets simples	Si une formule fait référence à des valeurs ou à des cellules d’autres feuilles de calcul ou d’autres classeurs dont le nom contient des espaces ou des caractères non alphabétiques, vous devez placer une apostrophe (') de part et d’autre du nom de cette feuille de calcul ou de ce classeur (par exemple, ='Données trimestrielles'!D3 ou ='123'!A1).
Placer un point d’exclamation (!) après le nom de la feuille de calcul lorsque vous y faites référence dans une formule	Par exemple, pour renvoyer la valeur de la cellule D3 dans une feuille de calcul nommée « Données trimestrielles » dans le même classeur, utilisez cette formule : =’Données trimestrielles’!D3.
Insérer le chemin d’accès pour les classeurs externes	Vérifiez que chaque référence externe contient un nom de classeur et le chemin d’accès correspondant. La référence à un classeur inclut le nom du classeur et doit être placée entre crochets ([NomClasseur.xlsx]). La référence doit également contenir le nom de la feuille de calcul dans le classeur. Si le classeur auquel vous voulez faire référence n’est pas ouvert dans Excel, vous pouvez toujours inclure une référence le concernant dans une formule. Spécifiez le chemin d’accès complet au fichier, comme dans l’exemple suivant : =LIGNES('C:\Mes documents\[Opérations.xlsx T2]Ventes'!A1:A8). Cette formule renvoie le nombre de lignes de la plage qui inclut les cellules A1 à A8 dans l’autre classeur (8). : Si le chemin d’accès complet contient des espaces, comme dans l’exemple précédent, utilisez des guillemets simples (au début du chemin d’accès et après le nom de la feuille de calcul, avant le point d’exclamation).
Saisir des nombres sans mise en forme	Ne mettez pas en forme les nombres lorsque vous les saisissez dans une formule. Par exemple, si vous devez entrer la valeur 1 000 €, entrez 1000 dans la formule. Si vous entrez une virgule dans un nombre, Excel la considère comme un caractère de séparation. Si vous souhaitez ajouter un séparateur de milliers ou de millions à un nombre, ou des symboles monétaires, mettez en forme les cellules après avoir entré les nombres. Par exemple, si vous voulez ajouter 3100 à la valeur de la cellule A3 et que vous entrez la formule =SOMME(3,100,A3),Excel ajoute les nombres 3 et 100, puis ajoute ce total à la valeur de A3, au lieu d’ajouter 3100 à A3, qui serait =SOMME(3100,A3). Autre exemple, si vous entrez la formule =ABS(-3100), Excel affiche une erreur, car la fonction ABS n’accepte qu’un seul argument : =ABS(-2134).

Vous pouvez implémenter certaines règles pour rechercher les erreurs dans les formules. Ces règles ne garantissent pas que votre feuille de calcul sera exempte d’erreurs, mais elles sont efficaces pour rechercher les erreurs courantes. Vous pouvez activer ou désactiver n’importe laquelle de ces règles individuellement.

Les erreurs peuvent être marquées et corrigées de deux façons : l’une après l’autre, à la manière d’un vérificateur d’orthographe, ou dès leur apparition dans la feuille de calcul au fur et à mesure de leur entrée.

Vous pouvez résoudre une erreur à l’aide des options affichées par Excel, ou vous pouvez ignorer l’erreur en sélectionnant Ignorer l’erreur. Si vous choisissez de l’ignorer dans une cellule en particulier, l’erreur de cette cellule ne s’affiche plus dans les contrôles d’erreur suivants. Cependant, vous pouvez réinitialiser toutes les erreurs précédemment ignorées afin de les afficher à nouveau.

1. Pour Excel sur Windows, accédez àOptions de > de fichiers > formules, ou
   pour Excel sur Mac, sélectionnez le menu Excel > Préférences > Vérification des erreurs.

2. Sous Vérification des erreurs, cochez la case Activer la vérification des erreurs d’arrière-plan. Toute erreur trouvée est marquée par un triangle dans le coin supérieur gauche de la cellule.

   

3. Pour changer la couleur du triangle qui indique où une erreur s’est produite, dans la zone Indiquer les erreurs à l’aide de cette couleur, sélectionnez la couleur souhaitée.

4. Sous Règles de vérification d’Excel, cochez ou décochez les cases correspondant à l’une des règles suivantes :

   • cellules contenant des formules qui génèrent une erreur: une formule n’utilise pas la syntaxe, les arguments ou les types de données attendus. Les valeurs d’erreur incluent #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! et #VALUE!. Chacune de ces valeurs d’erreur a des causes différentes et est résolue de différentes manières.

     : Si vous entrez une valeur d’erreur directement dans une cellule, elle est stockée comme valeur d’erreur, mais n’est pas marquée comme une erreur. Toutefois, si une formule d’une autre cellule fait référence à cette cellule, elle renvoie la valeur d’erreur de cette cellule.

   • formule de colonne calculée incohérente dans les tables: une colonne calculée peut inclure des formules individuelles différentes de la formule de colonne principale, ce qui crée une exception. Des exceptions de colonne calculée sont automatiquement créées lorsque vous effectuez l’une des opérations suivantes :

     • tapez des données autres qu’une formule dans une cellule de colonne calculée ;

     • Tapez une formule dans une cellule de colonne calculée, puis utilisez Ctrl +Z ou sélectionnez Annuler dans la barre d’outils Accès rapide.

     • tapez une nouvelle formule dans une colonne calculée qui contient déjà au moins une exception ;

     • copiez des données dans la colonne calculée qui ne correspond pas à la formule de colonne calculée. Si les données copiées contiennent une formule, celle-ci remplace le contenu de la colonne calculée.

     • déplacez ou supprimez une cellule dans une autre zone de la feuille de calcul, qui est référencée par l’une des lignes d’une colonne calculée.

   • Cellules contenant des années à deux chiffres : la cellule contient une date au format texte, qui peut être mal interprétée, comme un siècle incorrect, lorsqu’elle est utilisée dans des formules. Par exemple, dans la formule =ANNEE("1/1/31"), la date peut être 1931 ou 2031. Utilisez cette règle pour contrôler les dates ambiguës.

   • Nombres mis en forme en tant que texte ou précédés d’une apostrophe : les cellules contiennent des nombres stockés en tant que texte. Cela se produit généralement lorsque les données sont importées à partir d’autres sources. Les nombres stockés sous forme de texte peuvent entraîner des résultats de tri inattendus. Il est donc préférable de les convertir en nombres. '=SOMME(A1:A10) est considérée comme du texte.

   • Formules incohérentes avec les autres formules de la région: la formule ne correspond pas au modèle d’autres formules à proximité. Dans de nombreux cas, les formules adjacentes aux autres formules diffèrent uniquement par les références utilisées. Dans l’exemple suivant de quatre formules adjacentes, Excel affiche une erreur en regard de la formule =SOMME(A10:C10) dans la cellule D4, car les formules adjacentes s’incrémentent d’une ligne et qu’une formule incrémente par 8 lignes que Excel — attend la formule =SOMME(A4:C4).

     

     Si les références utilisées dans une formule ne sont pas cohérentes avec celles des formules adjacentes, Excel affiche une erreur.

   • Cellules omises dans une formule appliquée à une zone : une formule peut ne pas inclure automatiquement les références aux données que vous insérez entre la plage d’origine des données et la cellule contenant cette formule. Cette règle permet de comparer la référence dans une formule avec la plage de cellules adjacentes à la cellule qui contient la formule. Si les cellules adjacentes contiennent d’autres valeurs et qu’elles ne sont pas vides, Excel signale une erreur en regard de la formule.

     Par exemple, Excel insère une erreur en regard de la formule =SOMME(D2 :D4) lorsque cette règle est appliquée, car les cellules D5, D6 et D7 sont adjacentes aux cellules référencées dans la formule et la cellule qui contient la formule (D8), et ces cellules contiennent des données qui auraient dû être référencées dans la formule.

     

   • Cellules déverrouillées contenant des formules : la formule n’est pas verrouillée à des fins de protection. Par défaut, toutes les cellules d’une feuille de calcul sont verrouillées pour empêcher leur modification lorsque la feuille de calcul est protégée. Cela permet d’éviter les erreurs telles que la suppression ou la modification accidentelle des formules. Cette erreur indique que la cellule a été définie pour être déverrouillée, mais que la feuille n’est pas protégée. Vérifiez que vous ne souhaitez pas que la cellule soit verrouillée.

   • Formules faisant référence à des cellules vides : la formule contient une référence à une cellule vide. Cela peut provoquer des résultats inattendus, comme illustré dans l’exemple suivant.

     Supposons que vous souhaitiez calculer la moyenne des nombres dans la colonne suivante de cellules. Si la troisième cellule est vide, elle n’est pas incluse dans le calcul et le résultat est 22,75. Si la troisième cellule contient 0, le résultat est 18,2.

     

   • Données incorrectes dans un tableau : un tableau comporte une erreur de validation. Vérifiez le paramètre de validation appliqué à la cellule en accédant à l’onglet Données > groupe Outils de données > Validation des données.

1. Sélectionnez la feuille de calcul pour laquelle vous souhaitez vérifier les erreurs.

2. Si la feuille de calcul est calculée manuellement, appuyez sur F9 pour la recalculer.

   Si la boîte de dialogue Vérification des erreurs ne s’affiche pas, sélectionnez Formules > Audit desformules > Vérification des erreurs.

3. Si vous avez précédemment ignoré des erreurs, vous pouvez case activée pour ces erreurs en procédant comme suit : accédez à Options > de fichiers > Formules. Pour Excel sur Mac, sélectionnez le menu Excel > Préférences > Vérification des erreurs.

   Dans la section Vérification des erreurs , sélectionnez Réinitialiser les erreurs ignorées > OK.

   

   : La réinitialisation des erreurs ignorées entraîne la réinitialisation de toutes les erreurs dans les feuilles du classeur actif.

   : Cela peut vous aider de déplacer la boîte de dialogue Vérification des erreurs juste en dessous de la barre de formule.

   

4. Sélectionnez l’un des boutons d’action à droite de la boîte de dialogue. Les actions disponibles sont différentes pour chaque type d’erreur.

5. Sélectionnez Suivant.

1. En regard de la cellule, sélectionnez Vérification des erreurs , puis sélectionnez l’option souhaitée. Les commandes disponibles sont différentes pour chaque type d’erreur, et la première entrée décrit l’erreur.

   Si vous sélectionnez Ignorer l’erreur, l’erreur est marquée pour être ignorée pour chaque vérification consécutive.

   

Si une formule ne permet pas de calculer correctement un résultat, Excel affiche une valeur d’erreur, telle que #####, #DIV/0!, #N/A, #NOM?, #NULL!, #NOMBRE!, #REF! et #VALEUR!. Pour chaque type d’erreur, il existe des causes et des solutions différentes.

Le tableau suivant contient des liens vers des articles qui décrivent ces erreurs de façon plus détaillée ainsi qu’une courte description pour vous aider à commencer.

Rubrique	Description
Corriger une erreur #####	Excel affiche cette erreur pour indiquer qu’une colonne n’est pas suffisamment large pour accueillir tous les caractères d’une cellule ou qu’une cellule contient une date ou une heure négative. Par exemple, le résultat d’une formule qui soustraie une date future à une date passée, telle que =15/06/2008-01/07/2008, est une valeur de date négative. : Essayez d’ajuster automatiquement la cellule en double-cliquant entre les en-têtes de colonne. Si ### est affiché, car Excel ne peut pas afficher tous les caractères, cela le corrige.
Corriger une erreur #DIV/0! erreur	Excel affiche cette erreur lorsqu’un nombre est divisé par zéro (0) ou par une cellule qui ne contient aucune valeur. : Ajoutez un gestionnaire d’erreur comme dans l’exemple suivant, =SI(C2;B2/C2;0).
Corriger une erreur #N/A	Excel affiche cette erreur lorsqu’une valeur n’est pas disponible pour une fonction ou une formule. Si vous utilisez une fonction telle que RECHERCHEV, l’objet de votre recherche a-t-il une correspondance dans la plage de recherche ? La plupart du temps, il n’y en a pas. Essayez d’utiliser SIERREUR pour supprimer l’erreur #N/A. Dans ce cas, vous pouvez utiliser : =SIERREUR(RECHERCHEV(D2;$D$6:$E$8;2;TRUE);0)
Corriger une erreur #NOM! erreur	Cette erreur s’affiche lorsque Excel ne reconnaît pas le texte dans une formule, Par exemple, un nom de plage ou le nom d’une fonction peut être orthographié de manière incorrecte. : Si vous utilisez une fonction, vérifiez que le nom de la fonction est correctement orthographié. Dans ce cas, SOMME est mal orthographié. Supprimez le « e » et Excel le corrige.
Corriger une erreur #NULL! erreur	Excel affiche cette erreur lorsque vous spécifiez une intersection de deux zones qui, en réalité, ne se coupent pas. L’opérateur d’intersection est un caractère d’espacement qui sépare les références d’une formule. : Assurez-vous que vos plages sont correctement séparées : les zones C2:C3 et E4:E6 ne se croisent pas. Par conséquent, entrer la formule =SOMME(C2:C3 E4:E6) retourne la #NULL! . Placer une virgule entre les plages C et E corrige = SUM(C2 :C3,E4 :E6)
Corriger une erreur #NOMBRE! erreur	Excel affiche cette erreur lorsqu’une formule ou une fonction contient des valeurs numériques non valides. Vous utilisez une fonction qui produit une itération, telle que TRI ou TAUX ? Si c’est le cas, l’erreur #NUM! est probablement due au fait que la fonction ne trouve pas de résultat. Consultez la rubrique d’aide pour connaître la procédure de résolution.
Corriger une erreur #REF! erreur	Excel affiche cette erreur lorsqu’une référence de cellule n’est pas valide, Par exemple, vous avez peut-être supprimé des cellules référencées par d’autres formules, ou vous avez peut-être collé des cellules que vous avez déplacées sur des cellules auxquelles d’autres formules faisaient référence. Avez-vous supprimé accidentellement une ligne ou une colonne ? Nous avons supprimé la colonne B dans la formule =SOMME(A2;B2;C2) et voyez ce que cela a engendré. Vous pouvez utiliser la fonction Annuler (Ctrl+Z) pour annuler la suppression, recréer la formule ou utiliser une référence de plage continue, comme celle-ci : =SOMME(A2:C2). Cette formule aurait été mise à jour automatiquement suite à la suppression de la colonne B.
Corriger une erreur #VALEUR! erreur	Excel peut afficher cette erreur si votre formule inclut des cellules qui contiennent plusieurs types de données. Utilisez-vous des opérateurs mathématiques (+, -, *, /, ^) avec différents types de données ? Si c’est le cas, essayez d’utiliser une fonction à la place. Dans ce cas, =SOMME(F2:F5) devrait corriger le problème.

Lorsque les cellules ne sont pas visibles dans une feuille de calcul, vous pouvez watch ces cellules et leurs formules dans la barre d’outils Fenêtre espion. La fenêtre Espion facilite la réalisation d’inspections et d’audits ou la confirmation des résultats de formules, ainsi que des résultats dans de grandes feuilles de calcul. Cette fenêtre est pratique, car vous n’avez pas besoin de parcourir la feuille de calcul en permanence.

Cette barre d’outils peut être déplacée ou ancrée comme n’importe quelle autre barre d’outils. Par exemple, vous pouvez l’ancrer en bas de la fenêtre. La barre d’outils suit les propriétés de cellule suivantes : 1) Classeur, 2) Feuille, 3) Nom (si la cellule comporte une plage nommée correspondante), 4) Cellule, 5) Valeur et 6) Formule.

Ajouter des cellules à la fenêtre Espion

1. Sélectionnez les cellules que vous souhaitez examiner.

   Pour sélectionner toutes les cellules d’une feuille de calcul avec des formules, accédez à Accueil > Modification > sélectionnez Rechercher & Sélectionner (ou vous pouvez utiliser Ctrl+G ou Ctrl+G sur le Mac) > Accéder aux formules> spéciales.

   

2. Accédez à Formules > Audit des formules > sélectionnez Fenêtre Espion.

3. Sélectionnez Ajouter une montre.

   

4. Vérifiez que vous avez sélectionné toutes les cellules que vous souhaitez watch, puis sélectionnez Ajouter.

   

5. Pour changer la largeur d’une colonne de la fenêtre Espion, faites glisser la limite du côté droit de l’en-tête de la colonne.

6. Pour afficher la cellule à laquelle une entrée de la barre d’outils Fenêtre Espion fait référence, double-cliquez sur cette entrée.

   : Les cellules contenant des références externes à d’autres classeurs ne s’affichent dans la barre d’outils Fenêtre Espion que lorsque les autres classeurs sont ouverts.

Supprimer des cellules de la fenêtre Espion

1. Si la barre d’outils Fenêtre Espion n’est pas affichée, accédez à Formules > Audit desformules > sélectionnez Fenêtre Espion.

2. Sélectionnez les cellules à supprimer.

   Pour sélectionner plusieurs cellules, appuyez sur Ctrl, puis sélectionnez les cellules.

3. Sélectionnez Supprimer l’espion.

   

Il est parfois difficile de comprendre comment une formule imbriquée calcule un résultat final, étant donné la nécessité de plusieurs calculs intermédiaires et de tests logiques. Toutefois, vous pouvez utiliser la boîte de dialogue Évaluation de formule pour visualiser les différentes parties d’une formule imbriquée évaluée dans l’ordre de calcul de la formule. Par exemple, la formule =SI(MOYENNE(D2 :D5)>50,SOMME(E2 :E5),0) est plus facile à comprendre lorsque vous pouvez voir les résultats intermédiaires suivants :

Dans la boîte de dialogue Évaluation de formule	Description
=SI(MOYENNE(D2:D5)>50;SOMME(E2:E5);0)	La formule imbriquée s’affiche au départ. La fonction MOYENNE et la fonction SOMME sont imbriquées dans la fonction SI. La plage de cellules D2:D5 contient les valeurs 55, 35, 45 et 25. Par conséquent, le résultat de la fonction MOYENNE(D2:D5) est 40.
=SI(40>50;SOMME(E2:E5);0)	La plage de cellules D2:D5 contient les valeurs 55, 35, 45 et 25. Par conséquent, le résultat de la fonction MOYENNE(D2:D5) est 40.
=SI(FAUX;SOMME(E2:E5);0)	Étant donné que 40 est inférieur à 50, l’expression du premier argument de la fonction SI (l’argument test_logique) est Faux. La fonction SI renvoie la valeur du troisième argument (argument valeur_si_faux). La fonction SOMME n’est pas évaluée, car il s’agit du deuxième argument de la fonction SI (argument valeur_si_vrai) et elle ne sera renvoyée que si l’expression est Vrai.

1. Sélectionnez la cellule à évaluer. Il n’est possible d’évaluer qu’une seule cellule à la fois.

2. Accédez à Formules > Audit des formules > Évaluer la formule.

3. Sélectionnez Évaluer pour examiner la valeur de la référence soulignée. Le résultat de l’évaluation apparaît en italique.

   Si la partie soulignée de la formule est une référence à une autre formule, sélectionnez Pas à pas pour afficher l’autre formule dans la zone Évaluation . Sélectionnez Sortir pour revenir à la cellule et à la formule précédentes.

   Le bouton Pas à pas détaillé n’est pas disponible pour une référence la deuxième fois que celle-ci apparaît dans la formule, ou si la formule fait référence à une cellule dans un classeur distinct.

4. Continuez à sélectionner Évaluer jusqu’à ce que chaque partie de la formule ait été évaluée.

5. Pour afficher à nouveau l’évaluation, sélectionnez Redémarrer.

6. Pour terminer l’évaluation, sélectionnez Fermer.