Leere Excel Zelle nicht als Null werten?
Hey Leute, ich habe folgendes Problem:
Ich wollte in einer Exceltabelle alle Werte innerhalb einer Spalte, die 2 oder mehr von einander abweichen gelb markieren. Also in der Reihe "2,3,3,4,2" würden die beiden 2er und die 4, nicht aber die 3er markiert.
Dies habe ich über bedingte Formatierung mit der Formel =ODER(ABS(B1-B2)>=2;ABS(B3-B2)>=2;...) hinbekommen.
Klappt alles soweit wie am Schnürchen. Nur leider werter Excel leere Zellen als Null, was dazu führt, dass wenn eine einzige Zelle leer bleibt, meistens alle anderen Zellen gelb eingefärbt werden. Ich würde das gerne vermeiden und leere Zellen einfach ignorieren (bzw. in einer anderen Farbe, z.B. rot, markieren, aber das ist erstmal zweitrangig).
Die meistens Lösungen die ich online gefunden haben, beziehen sich darauf die Null auszublenden, was mir natürlich in meinem Fall wenig bringt. Auch habe ich häufiger gelesen, dass man die Null durch (unsichtbare) Standhaltervariablen ersetzten kann, das funktioniert aber leider bei mir auch nicht, da neben der Markierung auch ein Durchschnitt berechnet werden soll, wobei die Standhalter dann stören würden.
Natürlich könnte ich jetzt alle einzelnen Formeln noch einmal bearbeiten um für den Fall "Null" eine eigene Entscheidung einzufügen, aber das würde ich gerne vermeiden wenn es eine einfachere Lösung gibt.
Ich hoffe hier kann mir jemand helfen :D
MfG
Manuel
4 Antworten
Hallo,
es ist egal wie weit zwei Zahlen, die markiert werden sollen, auseinanderliegen oder? In dem Fall würde es ausreichen jeweils nur die höchste und niedrigste Zahl der Spalte zu betrachten und man umgeht das Problem der Leerzeilen, da diese nicht in die einzelnen Differenzen einfließen müssen:
- =UND(B1<>"";ODER(MAX(B:B)-B1>=2;B1-MIN(B:B)>=2))
Der Und()-Teil ist dafür da, das die Leerzellen nicht selbst markiert werden.
Bitte :D
Das bisherige Problem entsteht ja dadurch das du die Differenzen einzeln berechnest und Excel somit gezwungen ist den Leerwerten den Zahlenwert 0 zuzuordnen. Aber falls du keine genaue Differenz suchst, ist es nicht nötig alle Differenzen einzeln zu betrachten, das wird vielleicht an einem Beispiel deutlicher:
- Liste 1; 2; 3; 4; 5; 6; ...
Damit deine Oder()-Differenzenkette das Ergebnis WAHR ausgibt wird nur eine Differenz >=2 benötigt. Da 3-1>=2 und 4>3 ist, muss auch 4-1>=2 gelten. Dasselbe gilt entsprechend auch für die 5 usw. bis zum höchsten Wert. Die Differenzen 3-1; 4-1 etc sind somit für die Oder()-Differenzenkette irrelevant.
Der Vollständigkeit halber, falls jemand mal auf die Frage stößt und es für eine genaue Differenz braucht:
- =ODER((ABS(WENN($H$1:$H$6="";0,1;$H$1:$H$6)-H1))=2)
Ahhh. Ich glaube ich habe es verstanden. Das klingt ja ganz schön genial. Vielen Dank. Echt ein schlaues Kerlchen 🤓
Hast du es mit der =WENN(ISTLEER) versucht?
- =WENN(D3="";"";IhreFormel())
- WENN(D3 nichts ist, gib nichts zurück, andernfalls berechne die Formel).
Du könntest die Datenmenge verdichten.
- Spalte mit Daten markieren
- Strg+g
- Inhalte...
- (x) Leerzeillen → OK
- Start → Zellen→ Löschen → Zellen löschen... → Zellen nach oben verschieben → OK
Das geht leider auch nicht. Die fehlenden Werte müssen schon angezeigt werden. Nur eben nicht in die Markierung mit einbezogen...
X-Y Plots übersrpingen normalerweise die Lücken in den Daten. (ggf bewusst =NA() durch die Formel erstellen lassen)
Mal mit dieser Variante des Graph spielen.
Okay. Ich kann nicht behaupten, dass wirklich zu verstehen, aber ich werde es Mal ausprobieren und wenn es klappt schonmal vielen Dank