Dans le précédent article nous avons listé les 7 erreurs que peut renvoyer une formule dans Excel. Nous allons aujourd’hui voir comment les éviter ou les apprivoiser.
Les erreurs rédhibitoires
Les erreurs #REF!, #NUL! (sauf calcul dynamique de plages), #NOM? sont des erreurs issues directement d’une mauvaise manipulation ou de l’écriture d’une formule erronée. Il n’y a pas à tortiller, il faut corriger l’erreur, c’est tout !
Occupons-nous des autres.
Éviter l’erreur
Les erreurs #VALEUR!, #DIV/O!, #NOMBRE! interviennent lorsqu’une valeur impropre est utilisée dans une formule, soit qu’elle n’est pas numérique, soit qu’elle est nulle ou encore qu’elle occasionne un dépassement de capacité.
Deux stratégies peuvent être mises en œuvre. Si la valeur provoquant l’erreur est saisie par l’utilisateur, on peut utiliser la fonction de Validation de données. Si on ne peut pas contrôler la saisie (par exemple, quand on travaille sur un fichier exporté d’un logiciel), on peut tester les valeurs utilisées dans les formules.
La fonction de validation
Reprenons l’exemple des erreurs #VALEUR! et #DIV/O! du précédent article et listons les conditions que doit remplir la cellule B1 pour que le calcul soit possible. B1 doit contenir une valeur numérique (disons décimale) différente de zéro.
Sélectionnons la cellule B1. Puis, dans l’onglet Données du ruban cliquons sur Validation des données.
Examinons la fenêtre ci-contre. Dans Autoriser, on a limité la valeur que peut prendre la cellule aux seules valeurs décimales. Dans Données et Valeur, on a exclu la valeur zéro des valeurs saisissables dans la cellule.
A présent, si l’utilisateur saisit « TOTO » ou 0, un message s’affiche pour lui signaler que la valeur saisie est incompatible avec la règle de validation. Pas de TOTO et pas de zéro, pas d’erreur.
On teste avant !
Voyons comment tester les valeurs pour éviter l’erreur.
Qui dit test dans Excel dit fonction SI() : si la cellule vaut ça alors on affiche rien (par exemple) sinon on calcule.
Rappel : Rien en Excel s’écrit « » (Deux double-guillemets)
Traitons le cas O (zéro). Si la cellule B1 contient zéro alors on renvoie rien (« ») sinon on calcule.
On constate que ça marche. Les lecteurs réguliers de nos articles ne s’en étonneront pas.
Traitons à présent le cas « TOTO ». Il nous faut tester la numéricité du contenu de la cellule B1. Dans les fonctions d’information, nous trouvons la fonction ESTNUM() qui renvoie VRAI si la valeur fournie est numérique et FAUX dans le cas contraire.
ou,
Dans cette dernière illustration, l’utilisation de la fonction NON() qui change le VRAI en FAUX et réciproquement, nous permet de retrouver l’ordre des paramètres du cas zéro (avec rien en valeur si vrai et calcul en valeur si faux), ce qui nous permet d’écrire la formule ultime :
Je suis d’accord avec vous, écrire une formule de ce type au débotté demande une certaine habitude. Aussi, nous tirons de cela une conclusion simple, c’est que, dès que c’est possible, on a intérêt à utiliser la Validation de données pour qu’il n’y ait dans nos cellules que des données valides c’est dire permettant le calcul.
On peut généraliser en disant qu’il faut aller chasser l’erreur le plus en amont possible. Dans le cas où une erreur apparaît dans le traitement d’un fichier exporté d’un logiciel, il est fréquent que cette erreur soit issue d’un problème de saisie dans le logiciel d’amont, soit que la données y est fausse ou absente, soit que son domaine de valeur n’a pas été correctement défini. L’erreur qui se manifeste techniquement en aval est l’indice que quelque chose est erroné en amont.
Mais revenons à nos moutons …
Transmuter l’erreur
Cette stratégie semble aller rigoureusement à l’encontre de ce qui est dit au paragraphe précédent car on traite l’erreur après qu’elle se produit.
Il existe une fonction appelée SIERREUR() admettant deux paramètres, le premier étant l’expression à calculer (par exemple A1/B1) le second étant la valeur qu’il faut renvoyer si la formule précédente renvoie une erreur.
Dans l’illustration ci-dessus on transforme l’erreur, quelle qu’elle soit, en une réflexion peu amène (mais je voulais vous montrer qu’on pouvait renvoyer autre chose que rien).
Comparez les deux tableaux, celui de gauche avec le traitement de l’erreur et celui de droite sans. On constate que le total peut se calculer s’il n’y a pas de valeur d’erreur dans la plage totélisée alors qu’en cas de présence d’une valeur d’erreur, le total est lui-même en erreur.
Attention : il ne faut jamais écrire une formule dans une cellule directement avec son traitement d’erreur au moyen de la fonction SIERREUR() car l’erreur qui se produit n’est pas toujours l’erreur attendue ! On écrit d’abord =A1/B1, on recopie sur la colonne, on observe puis après, seulement après, on ajoute le SIERREUR().
Masquer ou au contraire mettre en évidence
Le masquage ou la mise en évidence sont des traitements d’aval : on laisse faire et on planque ou au contraire on met en couleur « flashy ». Pour ce faire, utilisons les mises en formes conditionnelles.
On commence par sélectionner la colonne G dans l’exemple illustré ci-dessus. Dans l’onglet Accueil, on clique successivement sur Mise en forme conditionnelle puis sur Règles de mise en surbrillance des cellules et enfin sur Autres règles …
Ce qui nous intéresse ici, c’est d’appliquer un certain format aux cellules en erreur, on sélectionne Erreurs. Ensuite on clique sur Format …
Si on souhaite rendre visible, on va choisir un remplissage rouge vif par exemple.
Si on souhaite masquer (le fond de cellule étant blanc) on choisit d’écrire le texte en blanc. Pour ce faire, on clique sur l’onglet Police (ouvrez !) et on sélectionne le blanc dans la liste Couleur. C’est la technique dite du lapin blanc courant sur la neige.
Ne restent plus pour témoigner de l’erreur que les triangles verts situés en haut à gauche des cellules. Cliquer sur l’icône qui vous donne accès au menu d’erreur (voir ci-contre) vous permet par exemple d’Ignorer l’erreur.
Petite précision : vous pouvez faire disparaître d’un seul coup tous les triangles verts en agissant sur les Options (solution définitive) ou en sélectionnant toute la plage contenant des erreurs mais, attention, l’icône du menu erreur ne s’affiche que si la cellule active est elle-même en erreur. Pour que ce soit le cas, sélectionnez la plage à partir d’une cellule en erreur.
#N/A
L’erreur #N/A se produit lorsqu’une fonction de recherche ne trouve pas la valeur cherchée.
Cette erreur peut être grave ou non suivant le contexte ce qui induit des stratégies de traitement différentes.
Si la recherche ne ramène rien et que c’est un cas possible et peu préoccupant. Par exemple, vous recherchez le titre d’un livre dans une liste. Il est possible que ce livre soit absent. On va informer l’utilisateur en transformant l’erreur en message au moyen de la fonction SIERREUR().
Il est des cas, au contraire, où l’échec d’une recherche est ennuyeux voire grave. Par exemple, dans un tableau d’articles, vous cherchez le taux de remise pratiqué par le fournisseur et le fournisseur d’un article n’existe pas dans le tableau des fournisseurs. L’erreur #N/A signifie ici que vous gérez des articles provenant d’un fournisseur qui n’existe pas. Ce type erreur mérite d’être rendu très visible car il appelle une action immédiate. On laisse l’affichage de l’erreur qu’on souligne en appliquant un beau rouge vif.
On peut traiter l’erreur #N/A en testant l’existence de la valeur cherchée. La syntaxe classique était est la suivante.
Dans cette syntaxe un peu lourdingue on utilise la fonction ESTNA() qui renvoie VRAI si l’expression passée en paramètre renvoie elle-même #N/A. On voit qu’on est tout de même obligé de provoquer l’erreur pour la tester.
Depuis la création de la fonction SIERREUR(), j’utilise plus volontiers la syntaxe illustrée ci-dessous mais en écrivant d’abord la recherche puis en ajoutant la fonction SIERREUR() quand je suis sûr que la recherche est bien écrite.
Conclusion
La survenue d’une erreur est toujours intéressante. Il s’agit bien sûr de l’éliminer mais non sans avoir bien analysé les conditions sa survenue. Comme il est dit plus haut, il est important de remonter l’erreur jusqu’à sa cause première qui peut être de nature purement technique mais qui parfois est la conséquence d’une mauvaise « mise en informatique » de la réalité.
Ainsi, après avoir remonté le fil de l’erreur, on agira exactement à sa source ou, à défaut, le plus près possible, évitant ainsi, filons la métaphore, de devoir installer à l’aval de la rivière de lourds dispositifs d’épuration et de filtration pour retirer les polluants qu’on aurait jamais dû y déverser.
Commentaires récents