Comment résoudre efficacement les erreurs courantes de formules Microsoft Excel

Excel peut parfois être impitoyable, surtout avec des formules complexes. Même une petite faute de frappe ou la sélection d’une plage de cellules incorrecte peuvent provoquer des erreurs qui semblent surgir de nulle part. Généralement, ces erreurs sont facilement corrigibles une fois que vous avez compris ce qui ne va pas. Ce guide passe en revue certaines des erreurs les plus courantes, comme #VALEUR!, #NOM? ou #DIV/0!, et vous explique comment les résoudre comme un pro. Si vous avez un message d’erreur et que vous vous demandez ce que vous avez fait, ces conseils pourraient vous éclairer. Attention : la correction de ces erreurs nécessite parfois un peu d’essais et d’erreurs, surtout si votre feuille de calcul comporte de nombreux éléments mobiles.

Erreur 1.#VALEUR!

C’est un classique. Il est criant lorsqu’on essaie de faire des calculs avec du texte, ou parfois lorsque les paramètres de formule sont légèrement erronés. Par exemple, si C1 contient « 100 » (un nombre) mais D1 indique « MOT », leur addition déclenchera #VALEUR ! Car, bien sûr, Excel déteste mélanger les types de données sans conversion explicite. Ce problème peut également se produire si vous avez des virgules mal placées ou si votre formule ne référence pas les bonnes cellules.

Comment réparer

• Commencez par vérifier les formules de cette cellule. Examinez chaque cellule référencée et vérifiez si elle contient du texte au lieu de chiffres ou des caractères cachés, comme des espaces supplémentaires. Par exemple, vous pouvez effacer les espaces de fin avec =TRIM(cell). Parfois, les cellules ressemblent à des nombres, mais sont stockées sous forme de texte ; un problème fréquent lié aux données importées d’ailleurs.
• Si votre formule nécessite des nombres réels, assurez-vous de convertir le texte en nombres. Vous pouvez essayer =VALUE(cell)de faire en sorte qu’Excel le traite comme un nombre. Dans certaines configurations, une actualisation rapide ou un copier-coller en tant que valeurs peut s’avérer utile. Dans d’autres, vous devrez peut-être vérifier l’absence de caractères cachés, notamment si vous copiez-collez depuis des sites web ou des PDF.

Cela devrait résoudre la plupart de ces problèmes, mais notez que sur certaines feuilles, une cellule défectueuse ou un espace aléatoire peut entraîner des erreurs inattendues. C’est un peu étrange, mais si la formule semble toujours correcte, essayez de tout copier dans une nouvelle feuille pour voir si cela résout le problème.

Erreur 2.#NOM ?

Cette erreur se présente comme un message d’erreur d’Excel : « Hé, je ne reconnais pas cette fonction ni le nom que vous avez utilisé.» Elle est généralement due à une faute de frappe, comme une erreur de frappe =SUMM(A1:A10)au lieu de =SUM(A1:A10), ou une faute d’orthographe dans une plage nommée. Je ne sais pas pourquoi c’est si littéral, mais cela arrive régulièrement, surtout si vous saisissez des formules manuellement ou si vous les copiez depuis un autre emplacement.

Comment réparer

• Vérifiez l’orthographe : comparez votre formule à la liste officielle des fonctions Excel. Assurez-vous de ne pas avoir oublié de « S » ou de « E ».
• Si vous utilisez une plage nommée, vérifiez son existence dans le Gestionnaire de noms : Formules > Gestionnaire de noms. Supprimer ou renommer des plages peut parfois endommager les formules.
• Une autre chose qui aide : si la fonction est personnalisée ou provient d’un module complémentaire, assurez-vous qu’ils sont installés et activés.

Erreur 3.#NUM!

Ce message signifie généralement « Waouh, le nombre est trop grand ou impossible » – comme essayer de trouver la racine carrée de -5 ou calculer quelque chose avec des nombres énormes qui débordent de la cellule. Curieusement, il apparaît aussi si vous effectuez des calculs itératifs mal configurés.

Comment réparer

• Regardez vos chiffres : sont-ils ridiculement grands ou petits ? Parfois, réduire les valeurs peut aider. Vérifiez également les formules : il n’est peut-être pas nécessaire de calculer la racine carrée d’un nombre négatif, sauf si vous effectuez des calculs complexes.
• Pour les calculs itératifs (comme la recherche d’objectif ou les solveurs itératifs), sélectionnez Fichier > Options > Formules et activez « itérations ».Parfois, un petit coup de pouce suffit pour que cela fonctionne correctement.

Remarque : sur certaines configurations, l’erreur #NUM! apparaît lors de la première exécution, mais disparaît ensuite après un redémarrage rapide ou un recalcul.

Erreur 4.#REF!

Ce problème est agaçant. Il se produit lorsque votre formule recherche une cellule ou une plage qui n’existe plus, par exemple parce que vous avez supprimé une ligne, une colonne ou une feuille. Ainsi, si votre formule indique «=A1+B1 mais vous avez supprimé la ligne 1 ou renommé les feuilles », Excel génère cette erreur au lieu de s’arrêter silencieusement.

Comment réparer

• Tout d’abord, annulez la suppression (Ctrl+Z) si vous venez de la voir apparaître. Ensuite, examinez attentivement la formule : cliquez sur la cellule contenant #REF! et examinez la barre de formule. Elle vous indiquera les références rompues.
• Mettez à jour manuellement les références aux cellules valides ou corrigez votre plage si vous en avez supprimé une partie par inadvertance. Parfois, remplacer les références #REF! par des références correctes résout le problème.

Erreur 5.#DIV/0!

La plus facile à repérer est la division par zéro. C’est comme essayer de diviser un nombre par zéro, ce qui est mathématiquement impossible et à proscrire également dans Excel. Cela se produit généralement lorsqu’une cellule censée contenir un nombre s’avère vide ou nulle.

Comment réparer

• Trouvez où la division a lieu, puis vérifiez si le diviseur est nul ou vide. Vous pouvez ajouter une instruction IF : =IF(B1=0, "", A1/B1)— qui ignorera le calcul si B1 est nul.
• Cela permet à votre feuille de fonctionner sans problème, sans messages d’erreur interminables qui pourraient tout gâcher.

Erreur 6.#CALC!

Ce problème survient généralement lorsqu’une formule ne dispose pas de suffisamment d’informations pour fonctionner, ou en cas de conflit interne. Par exemple, si vous utilisez la nouvelle fonction LAMBDA mais oubliez de spécifier une plage appropriée, vous obtenez #CALC! au lieu d’un résultat fonctionnel. Il en va de même avec FILTRE si vous oubliez de spécifier l’action à effectuer en l’absence de données correspondantes.

Comment réparer

• Assurez-vous que les fonctions comme =LAMBDA()ou =FILTER()reçoivent des données valides. Si vous utilisez =FILTER(), incluez toujours un argument if_empty=FILTER(range, condition, "No data"), comme.
• Décomposez les formules complexes étape par étape pour identifier les points faibles du calcul. Parfois, simplifier temporairement les choses révèle le problème.

Erreur 7.#NULL!

C’est étrange, mais courant : cela se produit lorsque vous saisissez accidentellement un espace au lieu d’une virgule ou de deux points dans une formule. Imaginez qu’Excel vous dise : « Je ne sais pas ce que vous voulez que je fasse, car votre syntaxe est erronée.» Par exemple, une mauvaise utilisation des plages de cellules =SUM(B2:C5 D2:D4)(par exemple, une virgule ou deux points manquants) peut perturber Excel et entraîner un résultat #NULL !

Comment réparer

• Vérifiez que vos formules utilisent des séparateurs appropriés : virgules, deux-points et points-virgules, selon les paramètres régionaux. Les virgules sont généralement la norme ; remplacez donc les espaces par des virgules dans vos plages et fonctions.