Ne rien renvoyer si deux valeurs sont vides (Microsoft Excel)

Graham utilise une formule qui fait référence à deux cellules, B1 et C1. Tout ce que fait la formule est de renvoyer la valeur de l’une des cellules, comme dans = IF (A1 = 1, IF (B1> C1, B1, C1)). B1 et C1 contiennent normalement des dates, mais parfois l’une ou l’autre ou les deux peuvent être vides. S’ils sont tous les deux vides, la valeur renvoyée (qui est 0 car ils sont vides) apparaît comme 1/0/1900. Lorsque les deux cellules sont vides, Graham souhaite que la formule renvoie une valeur vide, pas un 0.

Il existe plusieurs manières d’aborder ce besoin. Tout d’abord, commençons par l’exemple de formule de Graham:

La formule est un peu « incomplète », car elle ne fournit pas ce qui doit être retourné si A1 ne contient pas la valeur 1. Tel qu’écrit, si A1 contient 2 (ou toute autre valeur sauf 1), alors la formule renvoie « FALSE « . Donc, modifions un peu la formule pour que si A1 n’est pas 1, il renvoie une valeur « vide », comme ceci:

Ensuite, il est essentiel de comprendre ce qui se passe dans le « B1> C1 »

Comparaison. Les dates sont bien entendu stockées en interne sous forme de valeurs numériques, sous forme de numéros de série. Si B1 contient une date plus récente que C1, le numéro de série en B1 sera plus grand que le numéro de série en C1.

Au-delà, les caractéristiques des données en B1 et C1 déterminent le fonctionnement de la comparaison (>).

Sur cette base, si une cellule contient une date et l’autre est vide, cela équivaut à comparer un numéro de série (la date) à un 0 (la cellule vide), de sorte que le numéro de série sera toujours supérieur à la cellule vide. Si les deux cellules sont vides, les deux sont traitées comme contenant 0, et donc les deux sont égales.

Donc, disons que dans la formule de Graham, la cellule A1 contient la valeur 1, la cellule B1 est vide et la cellule C1 est vide. Voici comment la formule est « traduite » par Excel:

C’est pourquoi Graham voit 0 renvoyé par la formule, et lorsque 0 est considéré comme un numéro de série de date, il est affiché comme 1/0/00 (ou 1/0/1900).

Pour éviter cela, vous devez vérifier si B1 et C1 sont vides.

Il y a plusieurs façons d’y parvenir. Considérez cette variation sur sa formule:

Cette variante ajoute une instruction IF pour voir si B1 ajouté à C1 est égal à 0, ce qu’il sera si les deux sont vides car Excel considère que les espaces et 0 sont équivalents dans ce contexte. L’inconvénient est que si B1 ou C1 contient une valeur de texte, l’erreur #VALUE est renvoyée par la formule.

Une meilleure variation peut être la suivante:

Dans cette incarnation, l’instruction IF utilise la fonction AND pour déterminer si B1 et C1 valent 0. Cela résout également le problème d’erreur potentiel #VALUE.

Si vous voulez vraiment vérifier si B1 et C1 sont vides, vous devrez alors vous fier à une approche différente. Une manière consiste à utiliser la fonction COUNTA:

Si vous savez que B1 et C1 ne contiendront jamais de valeurs de texte en même temps, vous pouvez également utiliser la fonction COUNT à la place de la fonction COUNTA dans la formule précédente.

Une autre approche consiste à utiliser la fonction COUNTBLANK à peu près de la même manière; il retourne un décompte du nombre de cellules dans une plage qui sont vides:

Une variante similaire consiste à utiliser la fonction ISBLANK (qui retourne TRUE si une cellule est vide) avec la fonction AND:

Vous pouvez également demander à Excel d’évaluer B1 et C1 comme s’ils contenaient du texte, comme c’est le cas ici:

Cette formule ne fonctionnera pas comme prévu si B1 ou C1 contiennent un seul espace, vous pouvez donc ajouter la fonction TRIM dans le mélange:

Dans l’une des formules discutées jusqu’à présent, vous pouvez également modifier l’instruction IF qui renvoie B1 ou C1 avec la fonction MAX, de cette manière:

Cependant, il y a une mise en garde si vous décidez de le faire: la fonction MAX considère que toutes les valeurs de texte sont équivalentes à 0. Ainsi, si la cellule B1 contient « abc » et la cellule C1 contient 1, alors utiliser l’opérateur de comparaison supérieur à (> ) considère « abc » comme supérieur à 1, mais MAX considère 1 comme supérieur à « abc ».

Il existe également un moyen de revenir en arrière et d’utiliser la formule originale de Graham (celle qui ne vérifie pas deux cellules vides) et de simplement vous fier au formatage de la cellule contenant la formule. Créez simplement un format personnalisé tel que le suivant:

Notez les deux points-virgules à la fin du format. Ceux-ci indiquent à Excel de ne rien afficher si la valeur de la cellule est négative ou égale à zéro. En utilisant ce format, vous ne verriez jamais apparaître la date 1/0/1900; la cellule apparaîtrait vide.

Vous pouvez, bien sûr, modifier Excel afin qu’il masque toutes les valeurs nulles dans la feuille de calcul – comme cela a été couvert dans d’autres ExcelTips – mais cela peut ne pas convenir à vos objectifs si vous devez afficher des résultats nuls à partir d’autres formules.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (10386) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.