Nichts zurückgeben, wenn zwei Werte leer sind (Microsoft Excel)
Graham verwendet eine Formel, die auf zwei Zellen verweist, B1 und C1. Die Formel gibt lediglich den Wert einer der Zellen zurück, wie in = IF (A1 = 1, IF (B1> C1, B1, C1)). Sowohl B1 als auch C1 enthalten normalerweise Datumsangaben, aber manchmal können eine oder beide leer sein. Wenn beide leer sind, wird der zurückgegebene Wert (der 0 ist, weil sie leer sind) als 1/0/1900 angezeigt. Wenn beide Zellen leer sind, möchte Graham, dass die Formel einen leeren Wert zurückgibt, keine 0.
Es gibt verschiedene Möglichkeiten, wie dieser Bedarf angegangen werden kann. Beginnen wir jedoch zunächst mit Grahams Beispielformel:
=IF(A1=1, IF(B1>C1, B1, C1))
Die Formel ist etwas „unvollständig“, da sie nicht angibt, was zurückgegeben werden soll, wenn A1 nicht den Wert 1 enthält. Wenn A1 wie geschrieben 2 (oder einen anderen Wert außer 1) enthält, gibt die Formel „FALSE“ zurück „. Ändern wir die Formel also ein wenig, sodass, wenn A1 nicht 1 ist, ein „leerer“ Wert zurückgegeben wird, wie folgt:
=IF(A1=1, IF(B1>C1, B1, C1), "")
Als nächstes ist es wichtig zu verstehen, was in „B1> C1“
passiert Vergleich. Daten werden natürlich intern als numerische Werte gespeichert – als Seriennummern. Wenn B1 ein Datum enthält, das jünger als C1 ist, ist die Seriennummer in B1 größer als die Seriennummer in C1.
Darüber hinaus bestimmen die Eigenschaften der Daten in B1 und C1, wie der Vergleich (>) funktioniert.
-
Wenn beide Zellen numerische Werte (einschließlich Datumsangaben) enthalten, funktioniert der Vergleich wie erwartet.
-
Wenn eine der Zellen einen Textwert enthält, werden beide Zellen so behandelt, als ob sie Textwerte enthalten, selbst wenn eine von ihnen einen numerischen Wert enthält.
-
Wenn eine der Zellen leer ist, wird diese Zelle so behandelt, als ob sie den Wert 0 enthält.
Wenn eine Zelle ein Datum enthält und die andere leer ist, entspricht dies dem Vergleich einer Seriennummer (des Datums) mit einer 0 (der leeren Zelle), sodass die Seriennummer immer größer als die leere Zelle ist. Wenn beide Zellen leer sind, werden beide als 0 enthaltend behandelt, und daher sind beide gleich.
Nehmen wir also an, dass in Grahams Formel Zelle A1 den Wert 1 enthält, Zelle B1 leer ist und Zelle C1 leer ist. So wird die Formel von Excel „übersetzt“:
=IF(A1=1, IF(B1>C1, B1, C1), "") =IF(1=1, IF(B1>C1, B1, C1), "") =IF(B1>C1, B1, C1) =IF(0>0, B1, C1) =C1 =0
Aus diesem Grund wird in Graham 0 von der Formel zurückgegeben. Wenn 0 als Datums-Seriennummer betrachtet wird, wird sie als 1/0/00 (oder 1/0/1900) angezeigt.
Um dies zu vermeiden, müssen Sie überprüfen, ob sowohl B1 als auch C1 leer sind.
Es gibt verschiedene Möglichkeiten, dies zu tun. Betrachten Sie diese Variation seiner Formel:
=IF(A1=1, IF((B1+C1=0), "", IF(B1>C1, B1, C1)), "")
Diese Variante fügt eine IF-Anweisung hinzu, um festzustellen, ob B1 zu C1 gleich 0 ist. Dies ist der Fall, wenn beide leer sind, da Excel die Leerzeichen und 0 in diesem Zusammenhang als gleichwertig betrachtet. Der Nachteil ist, dass, wenn entweder B1 oder C1 einen Textwert enthält, der Fehler #VALUE von der Formel zurückgegeben wird.
Eine bessere Variante kann die folgende sein:
=IF(A1=1, IF(AND(B1=0,C1=0), "", IF(B1>C1, B1, C1)), "")
In dieser Inkarnation verwendet die IF-Anweisung die AND-Funktion, um zu bestimmen, ob sowohl B1 als auch C1 0 sind. Dies löst auch das potenzielle # VALUE-Fehlerproblem.
Wenn Sie wirklich überprüfen möchten, ob sowohl B1 als auch C1 leer sind, müssen Sie sich auf einen anderen Ansatz verlassen. Eine Möglichkeit ist die Verwendung der COUNTA-Funktion:
=IF(A1=1, IF(COUNTA(B1:C1)=0, "", IF(B1>C1, B1, C1)), "")
Wenn Sie sicher sind, dass sowohl B1 als auch C1 niemals gleichzeitig Textwerte enthalten, können Sie auch die COUNT-Funktion anstelle der COUNTA-Funktion in der obigen Formel verwenden.
Ein anderer Ansatz besteht darin, die COUNTBLANK-Funktion auf die gleiche Weise zu verwenden. Es gibt eine Anzahl der Zellen in einem Bereich zurück, die leer sind:
=IF(A1=1, IF(COUNTBLANK(B1:C1)=2, "", IF(B1>C1, B1, C1)), "")
Eine ähnliche Variante besteht darin, die ISBLANK-Funktion (die TRUE zurückgibt, wenn eine Zelle leer ist) zusammen mit der AND-Funktion zu verwenden:
=IF(A1=1, IF(AND(ISBLANK(B1), ISBLANK(C1)), "", IF(B1>C1, B1, C1)), "")
Sie können Excel auch B1 und C1 so auswerten lassen, als ob sie Text enthalten, wie hier beschrieben:
=IF(A1=1, IF(B1&C1="", "", IF(B1>C1, B1, C1)), "")
Diese Formel funktioniert nicht wie erwartet, wenn entweder B1 oder C1 ein einzelnes Leerzeichen enthalten. Daher möchten Sie möglicherweise die TRIM-Funktion in die Mischung einfügen:
=IF(A1=1, IF(TRIM(B1&C1)="", "", IF(B1>C1, B1, C1)), "")
In einer der bisher diskutierten Formeln möchten Sie möglicherweise auch die IF-Anweisung, die B1 oder C1 mit der MAX-Funktion zurückgibt, folgendermaßen ändern:
=IF(A1=1, IF(TRIM(B1&C1)="", "", MAX(B1:C1)), "")
Es gibt jedoch eine Einschränkung, wenn Sie sich dazu entschließen: Die MAX-Funktion betrachtet alle Textwerte als äquivalent zu 0. Wenn also Zelle B1 „abc“ und Zelle C1 1 enthält, verwenden Sie den Operator größer als der Vergleich (> ) betrachtet „abc“ als größer als 1, aber MAX betrachtet 1 als größer als „abc“.
Es gibt auch eine Möglichkeit, die ursprüngliche Formel von Graham (die nicht nach zwei leeren Zellen sucht) zu verwenden und einfach die Zelle zu formatieren, die die Formel enthält. Erstellen Sie einfach ein benutzerdefiniertes Format wie das folgende:
m/d/yyyy;;
Beachten Sie die beiden Semikolons am Ende des Formats. Diese weisen Excel an, nichts anzuzeigen, wenn der Wert in der Zelle negativ oder null ist. Bei Verwendung dieses Formats wird das Datum 1/0/1900 niemals angezeigt. Die Zelle würde leer angezeigt.
Sie können Excel natürlich so ändern, dass alle Nullwerte im Arbeitsblatt ausgeblendet werden – wie in anderen ExcelTips beschrieben -, dies entspricht jedoch möglicherweise nicht Ihren Zwecken, wenn Sie keine Ergebnisse aus anderen Formeln anzeigen müssen.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (10386) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.