Comment masquer les erreurs Excel avec les fonctions if et iserror

Microsoft Excel est un tableur puissant et polyvalent, idéal pour le suivi et la gestion de tout, des inventaires d'entreprise aux budgets des petites entreprises en passant par la forme physique. Un des avantages d'Excel est que vous pouvez configurer des formules à l'avance qui seront automatiquement mises à jour lorsque vous entrez de nouvelles données. Malheureusement, certaines formules sont mathématiquement impossibles sans les données requises, ce qui entraîne des erreurs dans votre table, telles que # DIV / 0 !, #VALUE !, #REF !, et # NAME ?. Bien qu'elles ne soient pas nécessairement nuisibles, ces erreurs seront affichées dans votre feuille de calcul jusqu'à ce qu'elles soient corrigées ou que les données requises soient entrées, ce qui peut rendre le tableau global moins attrayant et plus difficile à comprendre. Heureusement, au moins en cas de données manquantes, vous pouvez masquer les erreurs Excel avec l'aide des fonctions IF et ISERROR. Voici comment le faire.
Nous utilisons un tableur suivi de perte de poids réduit comme exemple du type de tableau qui produirait une erreur de calcul (calcul du pourcentage de perte de poids) en attendant de nouvelles données (pesées ultérieures).

Notre exemple de feuille de calcul attend une entrée dans la colonne Pondération, puis met automatiquement à jour toutes les autres colonnes en fonction des nouvelles données. Le problème est que la colonne Pourcentage perdus s'appuie sur une valeur, Changer, qui n'a pas été mise à jour pour les semaines au cours desquelles le poids n'a pas encore été entré, ce qui entraîne un # DIV / 0! erreur, qui indique que la formule tente de diviser par zéro. Nous pouvons résoudre cette erreur de trois manières:

1. Nous pouvons supprimer la formule des semaines dans lesquelles aucun poids n'a été saisi, puis l'ajouter manuellement chaque semaine. Cela fonctionnerait dans notre exemple car la feuille de calcul est relativement petite, mais ne serait pas idéal dans des feuilles de calcul plus grandes et plus complexes.
2. Nous pouvons calculer le pourcentage de perte en utilisant une autre formule qui ne divise pas par zéro. Encore une fois, cela est possible dans notre exemple, mais peut ne pas toujours dépendre du tableur et du jeu de données.
3. Nous pouvons utiliser la fonction ISERROR qui, associée à une instruction IF, nous permet de définir une valeur alternative ou un calcul si le résultat initial renvoie une erreur. C'est la solution que nous allons vous montrer aujourd'hui.

La fonction ISERROR

En lui-même, ISERROR teste la cellule ou la formule désignée et renvoie «true» si le résultat du calcul ou la valeur de la cellule est une erreur et «false» dans le cas contraire. Vous pouvez utiliser ISERROR en entrant simplement le calcul ou la cellule entre parenthèses après la fonction. Par exemple:

ISERROR ((B5-B4) / C5)

Si le calcul de (B5-B4) / C5 renvoie une erreur, ISERROR renverra «true» lorsqu'il sera associé à une formule conditionnelle. Bien que cela puisse être utilisé de nombreuses manières différentes, son rôle le plus utile est sans doute celui qui est associé à la fonction SI.

La fonction SI

La fonction IF est utilisée en plaçant trois tests ou valeurs entre parenthèses, séparés par des virgules: IF (valeur à tester, valeur si vrai, valeur si faux). Par exemple:

SI (B5> 100, 0, B5)

Dans l'exemple ci-dessus, si la valeur de la cellule B5 est supérieure à 100 (ce qui signifie que le test est vrai), la valeur de la cellule est alors égale à zéro. Mais si B5 est inférieur ou égal à 100 (ce qui signifie que le test est faux), la valeur réelle de B5 sera affichée.

IF et ISERROR combinés

Nous combinons les fonctions IF et ISERROR en utilisant ISERROR comme test pour une instruction IF. Passons à notre feuille de calcul de perte de poids à titre d'exemple. La raison pour laquelle la cellule E6 renvoie un # DIV / 0! L'erreur est due au fait que sa formule tente de diviser le poids total perdu par le poids de la semaine précédente, qui n'est pas encore disponible pour toutes les semaines et qui agit effectivement comme une tentative de division par zéro.
Mais si nous utilisons une combinaison de IF et ISERROR, nous pouvons dire à Excel d'ignorer les erreurs et d'entrer simplement 0% (ou toute valeur souhaitée), ou simplement de compléter le calcul si aucune erreur n'est présente. Dans notre exemple, cela peut être accompli avec la formule suivante:

SI (ISERREUR (D6 / B5), 0, (D6 / D5))

Pour rappel, la formule ci-dessus dit que si la réponse à D6 / D5 donne lieu à une erreur, retourne une valeur de zéro. Mais si D6 / B5 n'entraîne pas d'erreur, affichez simplement la solution à ce calcul.

Lorsque cette fonction est en place, vous pouvez la copier dans les cellules restantes et les erreurs éventuelles seront remplacées par des zéros. Toutefois, à mesure que vous saisirez de nouvelles données à l'avenir, les cellules affectées se mettront automatiquement à jour avec leurs valeurs correctes car la condition d'erreur ne sera plus vraie.

N'oubliez pas qu'en essayant de masquer les erreurs Excel, vous pouvez utiliser à peu près n'importe quelle valeur ou formule pour les trois variables de l'instruction IF. il n'est pas nécessaire que ce soit un zéro ou un nombre entier, comme dans notre exemple. Les solutions de rechange incluent la référence à une formule entièrement séparée ou l'insertion d'un espace en utilisant deux guillemets («») comme valeur «vraie». Pour illustrer cela, la formule suivante afficherait un espace en cas d'erreur au lieu d'un zéro:

SI (ISERREUR (D6 / B5), "", (D6 / D5))

Rappelez-vous simplement que les instructions IF peuvent rapidement devenir longues et compliquées, en particulier lorsqu'elles sont associées à ISERROR, et qu'il est facile de perdre une parenthèse ou une virgule dans de telles situations. Les versions récentes des formules de code de couleur Excel lorsque vous les entrez pour vous aider à garder une trace des valeurs de cellule et des parenthèses.