Excel - leere Zelle soll sich nicht mitfärben?
Hey Leute,
und zwar habe ich folgendes Problem in Excel: Ich habe die bedingte Formatierung in einer Spalte angewendet, in der auch eine Formel vorliegt. Also in der Spalte wird ein Datum ausgeworfen, das mithilfe einer Formel aus anderen Daten berechnet wird. Da ich nun aber die Formel und die bedingte Formatierung nicht jedes Mal neu eingeben will, habe ich die gesamte Spalte ausgewählt. Die bedingte Formatierung soll Daten, die in der Vergangenheit liegen, rot markieren. Das tut sie auch. nun markiert sie aber auch die ganzen Zellen, die noch leer stehen, also wo noch kein Datum ausgeworfen werden kann, da mir die dafür nötigen Werte noch fehlen, die ich eben im Laufe der Zeit weiter ausfülle, sobald mir diese Informationen vorliegen. Wie kann ich es aber nun so einstellen, dass die Zellen, die noch keinen Wert auswerfen können, nicht rot gefärbt werden?
Zusätzlich würde ich gerne wissen, wie ich Formeln und Formatierungen automatisch auf Zeilen übertrage, die ich im Nachhinein zwischen anderen Zeilen einfüge. Also ich meine jetzt nicht die leeren Zeilen, die ich bereits in der Tabelle erweitert habe, sondern wenn ich zwischen den Daten einfach mal eine komplett neue Zeile einfüge, die es vorher nicht gab, zum Beispiel zwischen Zeile 10 und 11.
6 Antworten
Oh, das ist lange her, dass ich mich mit sowas beschäftigt habe, daher meine Antwort unter Vorbehalt:
Ich denke, Du musst für Deine bedingte Formatierung eine geschachtelte oder verknüpfte Bedingung verwenden und so die Abfrage, ob das Feld leer ist mit einbinden. Ich habe jetzt die Codierungen dafür nicht mehr parat, aber "umgangssprachlich" ausgedrückt, musste Deine bedingte Formatierung so aussehen:
WENN 'Feld nicht leer' DANN (WENN 'Datum < heute' DANN 'Färbe rot')
Oder
WENN ('Feld nicht leer' UND 'Datum < heute') DANN 'Färbe rot'
Zur zweiten Frage: Kopiere eine komplette Zeile und verwende dann an der passenden Stelle den Befehl "Kopierte Zeile einfügen" (der müsste über Rechtsklick erreichbar sein, wenn ich mich recht erinnere) - dann müsste eigentlich alles automatisch an die Verschiebung angepasst werden.
Vielleicht musst Du aber auch erst mal eine leere Zeile einfügen und dann die kopierte Zeile da hinein einfügen, das weis ich nicht mehr genau.
Außerdem musst Du vermutlich die Zeile direkt über oder unter der Stelle kopieren, an der die neue Zeile rein soll, das weiß ich aber auch nicht mehr genau - probiers einfach mal aus...
Irgendwie hast Du doch die bedingete Formatierung programmiert - das ist doch eine WENN-DANN-Funktion, oder nicht?
Wenn es dafür inzwischen einen anderen Weg gibt (oder wenn es den schon immer gab und ich das vergessen habe ;-) ...), hast Du doch irgendwo die Bedingung eingeben, wann das Feld gefärbt werden soll - an dieser Stelle nennst Du dann beide Bedingungen und verknüpfst sie mit AND (bzw. UND, je nachdem, in welcher Sprache Deine Excelversion arbeitet).
Evtl. musst Du auch nicht überprüfen, ob die Zelle leer ist, sondern ob sie <>0 ist, das hängt davon ab, wie die Zelle sonst formatiert ist...
Also ich habe =($T10-HEUTE())<=0 angegeben. Das heißt, dass das Datum in Spalte T subtrahiert mit dem heutigen Datum entweder gleich sein muss oder bereits abgelaufen sein muss und unter Formatierung habe ich dann eingestellt, dass bei dieser Bedingung die Zellen rot gefärbt werden. Ich habe keine derartige Wenn-Dann-Funktion angegeben, wie die in deinem Beispiel steht, dadurch verstehe ich sie nicht ganz :/
In die WENN-DANN-Funktion kann man sowohl als Bedingung als auch als Ergebnis beliebige andere Funktionen einbauen - und die Bedingungen, wie gesagt, auch noch beliebig tief verschachtelt und/oder mit logischen Operatoren verknüpft.
Damit kannst Du so ziemlich alles als Ergebnis erzeugen, was in Excel überhaupt technisch möglich ist...
OK, dann läuft das also über die andere Schiene, es gibt also eine spezielle Stelle (Menü-Punkt) im Programm, wo man solche bedingten Formatierungen einstellen kann, das hatte ich so nicht mehr im Kopf...
Setze die Bedingung, die Du schon angegeben hast, in Klammern und dann dahinter etwas wie
AND (xxx <> "")
wobei das 'xxx' für den Verweis auf die konkrete(n) Zelle(n) steht - ich weiß jetzt nicht mehr, wie man das so schreibt, dass es für alle betroffenen Zellen zutrifft.
Ich gehe davon aus, dass in den Zellen, um die es geht, bisher auf dem Bildschirm gar nichts erscheint, daher der Vergleich mit dem leeren String "". Sollte in den Zellen bisher eine Null o. ä. erscheinen, müsstest Du stattdessen
<> 0
schreiben
Ich habe es jetzt so gemacht, dass ich meine bestehende Wenn-Funktion in eine andere Wenn-Funktion eingesetzt habe. Dadurch, dass Excel die Zelle bei einem Leerzeichen wieder so färbt, wie ich es mir wünsche, habe ich jetzt gesagt, so, dann soll doch Excel bitte die leeren Felder prüfen und wenn diese mit dem Prüfungskriterium übereinstimmen, soll da ein Leerzeichen eingefügt werden. "Sonst" gibt mir dann die Wenn-Funktion wieder, die ich zuvor da stehen hatte. Damit klappt das jetzt alles. Es ist sehr kompliziert, aber alle Formeln und Formatierungen sind jetzt so miteinander verknüpft, dass die Spalten und Zeilen auf die Eingaben richtig reagieren.
Na, das ist doch schön, wenn's klappt ;-)
Das hört sich jetzt für mich zwar erst mal etwas nach einem "Work Around" an (d. h., es gibt vermutlich auch einen schnelleren, eleganteren Weg), aber das ist im Grunde unwichtig, denn Du hast wieder was dazu gelernt :-) - und überprüfen kann ich das auch nicht, weil ich keinen Zugriff mehr auf Excel habe.
Aber zu Deiner Beruhigung: es gibt bei der Formulierung von Bedingungen tatsächlich Sachen, die sich erst mal ganz einfach anhören, aber dann doch nur auf ziemlich komplizierten Wegen zu lösen sind...
Das war auch mein erster Gedanke, als ich dann die gesamte Übersicht hatte :D Also, dass es vermutlich viel einfacher und schöner geht als mein Lösungsweg :P .. Aber letztendlich sieht das jetzt zum Glück auch ordentlich aus
Nachtrag: Ein kleiner Tipp zur Performance (heute wahrscheinlich nicht mehr so interessant, aber als ich mit sowas gearbeitet habe, wurde die Größe von Festplatten und Arbeitsspeichern noch in MB angegeben, da hatte das schon Bedeutung):
Lass bei geschachtelten oder verknüpften Bedingungen immer die als erste prüfen, die amwahrscheinlichsten NICHT erfüllt ist - dann brauchen die anderen Bedingungen meist gar nicht überprüft zu werden, und man spart rechtlich Rechenzeit...
Hallo,
anbei die Abbildung für die "bedingte Formatierung" (Zelle A1 als Basis angenommen), wenn das Datum älter ist als Heute (aber nicht Gleich) und Zellen mit keinen Einträgen werden nicht berücksichtigt werden.
In B1 steht das heutige Datum =HEUTE() als Formel.
Die "Dollar"-Zeichen und die ohne beachten !
Zuletzt von Zelle A1 nach unten kopieren, aber nur das Format.
Gruß Tron
Falls es nicht funktioniert, bedingt durch eine Formelgebung, dann folgende Formel.
=(UND(A1<$B$1;A1<>0))
Also die Anführungszeichen durch eine Null ersetzen.
Wenn ich es richtig verstehe, dann werden leere Felder als 0, also als 00.01.1990 gelesen.
Damit das nicht passiert einfach ein zusätzliches "Größer als 0" angeben, geht in der Norm mit einer UND Verkettung.
Nur um das klar zu stellen, falls du es anders machst, Bedingte Formatierung > Regeln zum Hervorheben > Zwischen > 01.01.1900 und der größere Wert auf eine Zelle, die das heutige Datum-1 hat.
Zu
wenn ich zwischen den Daten einfach mal eine komplett neue Zeile einfüge,
Mir ist da zumindest nichts bekannt, neue Zeilen wurden bei mir immer ignoriert. Ich kopiere diese auch normal einfach rein. Eine andere Möglichkeit wäre natürlich die bedingte Formatierung einfach als Makro zu hinterlegen und per Knopfdruck neu anwenden zu lassen (oder bei bestimmten Aktionen, wie Zell-Änderungen). Dann löscht das Makro die alte bedingte Formatierung und setzt sie neu über die komplette Spalte.
Aber es steht ja so gesehen keine 0 in der Zeile. Da steht ja eine Formel drin, bloß wirft die keinen Wert aus, da die dafür nötigen Daten in der zugehörigen Zeile noch nicht vorhanden sind. Deswegen kann ich es nicht so formatieren, dass leere Felder wieder weiß werden, da eben die Formel bereits da steht und die Zelle nicht leer ist.
Sie wirft keinen Wert aus, ist somit nicht leer, somit kann es nur als 0 interpretiert werden.
Solange du es "Zwischen 1 und heute" hast in der bedingten Formatierung sollten diese nicht formatiert werden.
Ob die Zelle leer ist, kannst du ja einfach in deiner bedingten Formatierung abfragen!
Menn das Datum zum Beispiel in Zelle M327 steht, lautet die Formel dafür:
=UND(M317<>"";M317<HEUTE())
Und die Formatierung überträgst du auf andere Zellen entweder mit dem "Pinsel" oder indem du den Bereich in der bedingten Formatierung unter "Wird angewendet auf" mit dazufügst.
Oder "einfacher":
Zellenformatierung: "Regeln zum HErvorheben von Zellen" / "Zwischen..."
und dann die beiden Werte 1 und HEUTE()-1 eingeben
Also es war mein Fehler in der Aufgabenstellung, habe es falsch formuliert. Es ist so: Meine Formel, die das Datum aus anderen Daten berechnet, steht in Spalte T. Nun geht es darum, dass die Daten in Spalte T, die kleiner gleich 0 als das heutige Datum sind, rot gefärbt werden. Mein erstes Datum steht in T10... Ich habe jetzt eine weitere bedingte Formatierung mit der oben genannten Funktion =UND(T10<>"";T10<=(T10-HEUTE())) eingefügt und danach einfach den Bereich bis zum Tabellenboden erweitert. Die roten Zellen in den leeren Feldern sind jedoch weiterhin da :/ Was habe ich falsch gemacht? Habe die Funktion nicht ganz so verstanden im Bezug auf meine Werte.
Ich habe jetzt eine weitere bedingte Formatierung...
Wenn du eine weitere Formatierung einfügst, ändert das nichts an der Formatierung durch die andere bedingte Formatierung. Du musst sie anstatt der anderen Formatierung verwenden.
Wie immer: du schreibst sehr detailreich, lässt aber die wichtigen Informationen aus. Zum Beispiel, wie deine bedingte Formatierung denn lautet.
Meine aktuelle bedingte Formatierung lautet einfach =($T10-HEUTE())<=0 und unter Einhaltung dieser Bedingung färben sich die Zellen rot. Aber dadurch halt auch die Zellen, die gar kein Datum auswerfen.
ich habe dir oben zwei Möglichkeiten für die bedingte Formatierung anstatt deiner gegeben.
Ich habe es jetzt probiert, dass ich meine bedingte Formatierung durch deine ersetzt habe. Die Zellen bleiben weiterhin rot (Habe folgendes angegeben: =UND($T10<>"";($T10-HEUTE())<=0)). Ich bin mir nicht sicher, aber ich denke dies funktioniert aus dem Grund nicht, weil ja in den rotgefärbten Zellen so gesehen etwas steht, und zwar die Formel. Bloß spuckt diese Formel in diesen Zellen noch keinen Wert aus, wodurch da kein Wert steht. Tut mir leid, ich stehe gerade etwas auf dem Schlauch. Bin seit 2 Wochen jeden Tag damit beschäftigt, neue Funktionen in Excel kennen zu lernen, wobei ich zuvor nie richtig mit Excel zu tun hatte und mir dementsprechend die Kenntnisse fehlen. Das war sehr viel Input die letzten zwei Wochen, muss das alles etwas im Kopf sortieren.
Das wäre mir auch so gegangen. Du brauchst einfach die Zeit und die Übung, du kannst nichts ins Hirn reinZWINGEN.
Und versuchs mal anstatt mit <>"" mit <>0 oder mit Nicht(istzahl($T10))
(weiß jetzt nicht auswendig, ob auch Datum als Zahl interpretiert wird, nach meinem Dafürhalten müsste es das (weiß man aber bei xl nie im voraus...)
Du kannst in deine Formel zusätzlich eine Abfrage auf "nicht leer" einbauen.
=UND(NICHT(ISTLEER(A1));A1<HEUTE())
Wenn ich bei mir Blattzeilen einfüge, wird die bed. Formatierung automatisch auf die Zelle angewandt.
Falls du Blattzeilen unterhalb des Bereichs der bed. Formatierung einfügst, kannst du den Bereich, auf den die bed. Formatierung wirkt, erweitern:
Start → Formatvorlagen → Bedingte Formatierung → Wird angewendet auf
Zu dem bedingte Formatierung: Ich bin gerade selbst ein bisschen verwirrt, denn ich weiß ich hatte mal exakt das selbe Problem, aber selbst wenn ich nicht einfach die komplette Spalte definiere (also D:D) sondern D1:D500, dann erhöht er 500 einfach um 10, wenn ich 10 Zeilen einfüge. Heißt ich kann es nicht replizieren, eventuell ein Problem älterer Excel-Versionen.
Ich weiß nämlich noch ganz genau, wie mein schönes X1:X500 einfach tausend Mal aufgeteilt wurde, also X1:X3, X5:X10, X20:X25 usw. nach dem Einfügen oder Löschen von Zeilen. Ja, ich denke das lag an einer älteren Excel-Version.
Ich habe es mit Excel 2016 ausprobiert. Der Bereich wurde nicht gesplittet.
Ja, ich hatte damals auf der Arbeit zwangsweise ich glaube 2003, jetzt haben wir 2013. Ich glaube 2003 konnte das noch nicht
Nein, genau das Aufteilen passiert auch bei mir.
Ich habe als eigenen Kommentar kommentiert, du kannst das Ganze per Makro umgehen (so hatte ich es gelöst).
Welche Excel-Version hast du denn?
Solange ich das hier nicht falsch verstehe ist das das Problem, aber wie gesagt: Lässt sich sehr simpel per Makro lösen
Hab die Wenn-Dann-Funktion nicht ganz verstanden... Dachte die prüft nur nach einem Wert und wenn dieser stimmt, dann gibt er in einer anderen Zelle etwas anderes wieder und falls nicht, dann gibt er einen anderen Wert wieder? Inwiefern soll das jetzt mit der Zellfärbung klappen? Dachte es geht nur um das Auswerfen von Werten bei dieser Funktion?