Comment corriger une erreur dans une formule Excel ?

Vous êtes vous déjà retrouvé avec une formule longue de 5 lignes qui renvoie systématiquement une erreur, et vous ne savez pas ou ça coince? Dans cet article je vous explique ma méthode systématique pour tordre le cou aux erreurs de formules.

La possibilité que donne Excel d’utiliser des formules complexes, et de les emboîter l’une dans l’autre en fait un logiciel d’une puissance redoutable. En une seule cellule, vous pouvez calculer un résultat dépendant de nombreux paramètre selon un processus compliqué. Cette force d’Excel est aussi l’une de ses principales difficultés pour l’utilisateur en cas d’erreur du fichier. En effet vous pouvez vous trouver avec une formule incompréhensible sans avoir aucune idée de comment la corriger. Voici la méthode que j’utilise pour corriger un fichier.

Décomposer la formule

La première étape est de décomposer la formule pour savoir ce qui coince.

Par exemple si votre formule est =SI(A,B,C), en considérant que A B et C sont d’autres formules,vous allez calculer séparément les formules A, B et C pour voir le résultat retourné par chaque partie. Vous pouvez faire cela facilement en copiant et collant chacune de ces formules dans une cellule inutilisée de votre classeur Excel.

Vous allez ainsi pouvoir examiner si les résultats retournés par les formules A, B, et C sont cohérents, et pouvoir examiner laquelle de ces formules est fausse en fonction de leur résultat intermédiaire.

Si ces formules sont toujours trop complexes, alors il vous faut faire de même en décomposant chaque formule A B C en une sous formule. En répétant ce processus, vous devriez aboutir à une suite de résultats intermédiaires qui composent le calcul de votre formule principale. Vous serez alors capable de remonter toute la chaine de calcul pour savoir à quel endroit le calcul est faux.

Vérifier les données

Le deuxième point à vérifier est que vous considérez bien les bonnes données.

La destination

Votre formule pointe-t-elle au bon endroit sur votre feuille? Faites en particulier attention aux références relatives (sans le signe $).  Si vous copiez la formule, la cellule cible sera décalée de la même manière que la cellule source, ce qui peut vous générer des erreurs ( par exemple dans le cas d’une recherche dans un tableaux de données qui reste fixe).

Le type des données

Faites attention au type de données que vous comparez, par exemple si vous cherchez un chiffre dans un tableau de texte ( en particulier, un chiffre peut être formaté en tant que nombre ou texte).

Considérez le type d’erreur

Si une cellule vous retourne une erreur, le type d’erreur peut vous aiguiller sur la manière de résoudre le problème.

Les erreurs #DIV/0!

Cette erreur est assez facile à résoudre, elle signifie qu’à un moment dans votre formule, vous avez une division par zéro. Il vous suffit de cibler les parties de votre formule qui impliquent des divisions et, avec la méthode expliquée plus haut, de trouver pourquoi le diviseur est zéro.

Les erreurs #NUL!

Cette erreur se produit typiquement quand les plages cibles sont mal référencées, avec une mauvaise syntaxe. Par exemple =SOMME(A1 A5) renvoie #NUL! car vous avez oublié les  » : « , la syntaxe correcte est =SOMME(A1:A5)

Pour résoudre cette erreur, relisez bien votre formule et regardez si vous avez bien utilisé la syntaxe correcte.

Les erreurs #VALEUR!

Ce type d’erreur se produit quand vous comparez des choux et des carottes 😉

Elle signifie que vous essayez de réaliser une opération sur une donnée qui est du mauvais type, par exemple:

• Additionner un chiffre et une chaine de caractères
• Utiliser une chaine de caractères dans une formule qui utilise un chiffre comme cible
• Utiliser une plage de données comme cible d’une formule qui s’attend à recevoir une seule cellule ou donnée

Pour résoudre ce type d’erreur, vérifiez bien le type des données qui sont envoyées dans chaque formule.

Les erreurs #REF!

Ce type d’erreur se produit lorsque votre formule fait appel à une référence qui n’existe pas.

C’est typiquement le cas si vous supprimez cette cellule de référence. Vous pouvez facilement voir quelle partie de la formule coince, car excel affiche #REF! non seulement en résultat, mais également à l’intérieur de la formule, à la place de la référence qui a disparu. Il vous reste à retrouver ou se trouve la valeur cible dans le classeur (si elle a été deplacée) ou bien à la recalculer.

Les erreurs #NOM?

Cette erreur signifie que vous avez rentré une formule qu’Excel ne reconnait pas. cela peut venir des cas suivants:

• Vous avez rentré du texte dans une formule sans l’entourer de guillemets. Dans ce cas, Excel croit de base qu’il s’agit d’une formule
• Vous avez mal orthographié une formule
• Vous utilisez une formule qui n’est pas définie (par exemple si elle fait partie d’un Add-in que vous n’avez pas chargé)

Les erreurs #NOMBRE!

Cela se produit si vous calculez un nombre trop grand pour qu’Excel puisse le représenter. Vous pouvez aller jusqu’a 10^308 (1 suivi de 308 zéros… vous avez un peu de marge quand même 😉 )

Les erreurs #NA!

Cette erreur va apparaitre quand une valeur cherchée est manquante, typiquement:

• Il manque un argument obligatoire dans votre formule
• Vous faire une recherche d’une valeur cible exacte (RECHERCHEV, RECHERCHEH, INDEX) qui ne se trouve pas dans la plage de recherche

Les erreurs ###########

Cette erreur est une erreur de format de valeur numérique, c’est à dire qu’Excel manque de place pour afficher la valeur. Pour résoudre cette erreur, essayez soit d’élargir la cellule soit de changer le format du nombre pour qu’il puisse rentrer dans la cellule.

Comment éviter les erreurs?

J’espère que vous avez pu trouver d’ou venait votre erreur, et maintenant que vous avez bien lutté pour la trouver, je suis sur que vous êtes prêts à étudier comment éviter d’en produire d’autres à l’avenir!

Scindez les étapes

Au lieu de faire une grosse formule barbare, séparez votre gros calcul en plusieurs étapes intermédiaires. Cela vous fera gagner en temps et en clarté si vous devez la débugger par la suite. De plus si vous utilisez Excel au bureau, votre fichier sera peut-etre utilisé par un autre collègue qui aura besoin de comprendre comment il fonctionne. Simplifiez lui la vie!

• Effectuez dans plusieurs cellules les calculs intermédiaires. 
• Nommez vos plages de données et les valeurs que vous allez réutiliser souvent. Cela vous aidera a comprendre la logique de la formule. Quel est le plus facile a comprendre selon vous : =RECHERCHEV(O63;Feuil3!$K$25:$M$29;2;0) ou =RECHERCHEV(client;tableau_clients;2;0)
• Si besoin, vous pouvez définir des fonctions personnalisées si vous faites souvent le même calcul. La encore vous allez gagner en clarté lorsque vous allez décortiquer la formule.

Soyez prévoyants

Lorsque vous développez votre fichier, prévoyez que des changements vont être apportés. Notamment, les formules vont être dupliquées, étendues, etc.

Lorsque vous faites des opérations sur toute une ligne / colonne, faites attention à ne pas rentrer un second tableau en dessous, dans ces mêmes colonnes. De même si vous renseignez une plage fixe de données, prévoyez que cette liste va surement s’allonger, il arrivera un jour ou les données déborderont de la plage que vous avez sélectionné.

Voilà les méthodes que j’utilise pour corriger les méthodes de mes fichiers, avez vous d’autres astuces à partager? N’hésitez pas à les proposer en commentaires ci-dessous!