Archiv des LibreOffice- und OpenOffice.org-Wiki

[ÜberSicht] [TitelIndex] [WortIndex] [SeiteFinden

In Calc-Tabellen soll oftmals die Darstellung einer Zelle in Abhängigkeit von ihrem Inhalt gebracht werden. Z.B. soll bei Kalenderanzeigen ein Datum, das auf einen Sonntag fällt, anders dargestellt werden als die Wochentage. Oder bestimmte Wertebereiche sollen beim Erreichen eines Minimal- oder Maximalwertes hervorgehoben werden.

In Calc gibt es dafür drei Ansätze:

A) Zahlenformate

B) Vorlagen wirken "im Hintergrund"

C) Vorlagenzuweisung innerhalb von Formeln


1. Zahlenformate

Editieren

Bestimmt hat jeder Calc-Benutzer schon einmal via Format → Zelle → Zahlen oder innerhalb einer Zellvorlage das Zahlenformat für eine Zelle geändert. Meist geht es dabei um die Anzahl anzuzeigender Nachkommastellen, Tausendertrennzeichen oder Währungskürzel.

Calc ist aber auch für weitere Formatwünsche empfänglich und bietet eine Syntax, mit der man eigene Formate definieren und sogar von bestimmten Bedingungen abhängig machen kann. Im Hilfetext findet man mit dem Stichwort Zahlenformate/Codes eine Reihe von Anwendungsvorschlägen. Im folgenden soll näher auf das Thema Formate je nach Wert eingegangen werden.

1.1. Wert größer oder kleiner Null?

1.1.1. Negative Werte rot anzeigen

Für diesen Fall bietet Calc bereits eine Auswahlbox. Aktiviert man unter Format → Zelle → Zahlen die Option negativ in Rot, so wird das benötigte Format vom Programm selbst eingestellt und kann je nach Kombination mit anderen Formatanforderungen z.B. so

0;[ROT]-0

oder so:

#.##0,00;[ROT]-#.##0,00

aussehen.
Was vor dem Semikolon steht, wird bei positiven, was nach dem Semikolon steht, bei negativen Werten angewendet.

1.1.2. Plus- und Minuszeichen vor den Zahlen

Bei negativen Zahlen zeigt Calc automatisch ein Minuszeichen an. Manchmal möchte man aber auch ein Pluszeichen bei positiven Zahlen haben. Im vorigen Beispiel haben wir gesehen, dass zwei verschiedene Formatanforderungen durch ein Semikolon getrennt werden. Dies kombinieren wir nun mit der Anforderung der einzusetzenden Plus-/Minus-Zeichen. Ein Leerzeichen zwischen Minus-/Plus-Zeichen und dem Zahlencode sorgt für einen Abstand vor der jeweiligen Zahl.

+ #.##0;- #.##0

Eigentlich haben wir ja nun Text in die Formatformel eingefügt. Texte in Formatformeln müssen normalerweise in Anführungszeichen eingeschlossen werden. Bei Plus- und Minuszeichen ist das Programm aber tolerant. Währungszeichen haben ein $ vorangestellt und müssen bei Sonderzeichen wie dem € mit einem Code kombiniert werden, Beispiel:

#.##0,-- [$€-407];[ROT]-#.##0,-- [$€-407]

Zurück zum Beispiel des Plus-/Minus-Zeichens.
Bei der Formel + #.##0;- #.##0 wird im Fall Null ebenfalls ein Pluszeichen gesetzt. Wenn das unerwünscht ist, fügt man einfach ein weiteres Semikolon an und dahinter das Format für Nullwerte:

+ #.##0;- #.##0;0

Auch so etwas ist denkbar:

+ #.##0;- #.##0;"+/- "0

wobei die Zeichenkette +/-, die im Fall Null vorangestellt werden soll, jetzt in Anführungszeichen gefasst werden muss, wie weiter oben erwähnt.

1.2. Unterschiedliche Schriftfarben für unterschiedliche Wertebereiche

In den vorigen Beispielen wurde erklärt, dass verschiedene Formate im gleichen Zahlenformat durch Semikolons zu trennen sind, wobei die Reihenfolge so interpretiert wird:
Positive Zahlen;Negative Zahlen;Nullwerte

In der ausführlichen Schreibweise sähe das so aus:

[>0]+ #.##0;[<0]- #.##0;"+/- "0

oder übersetzt:
WENN größer als 0, dann setze ein Plus-Zeichen
WENN kleiner als 0, dann setze ein Minus-Zeichen
SONST, setze ein Plus- und ein Minus-Zeichen

Diese Grenzen kann man aber auch selbst festlegen. Beispiel:
In einer Messtabelle sollen alle Werte unter -10 blau dargestellt werden, alle Werte über +50 rot und alles dazwischen soll grün sein:

[<-10][BLAU]0;[>50][ROT]0;[GRÜN]0

1.3. Einen bestimmten Wert hervorheben

Wenn nur ein bestimmter Wert hervorgehoben werden soll und keine negativen Werte vorkommen, die ein Minuszeichen benötigen würden, so genügt es, diese eine Bedingung zu formulieren:

[=8][ROT]0

Das Programm ergänzt die Eingabe automatisch zu:

[=8][ROT]0;Standard

1.4. Siehe auch

Nullwerte unterdrücken (Calc+Writer): ZahlenformatBedingt


KategorieCalc KategorieTipps


2. Vorlagen wirken „im Hintergrund“

Editieren

Mit einer bedingten Formatierung kann die Darstellung einer Zelle von Bedingungen abhängig gemacht werden. Ändert sich der Inhalt der Zelle oder des in einer entsprechenden Formel angegebenen Bezugs, so ändert sich auch automatisch die Darstellung.

3. Zellformat

Um mit der Definition einer bedingten Formatierung die Gestaltung von Zellen zu beeinflussen, müssen zunächst ZellVorlagen angelegt werden. Es können maximal 3 verschiedene Zellvorlagen in einer bedingten Formatierung angewendet werden. Jedoch können pro anzuwendender Zellvorlage mehrere in Frage kommende Bedingungen formuliert werden (siehe Abschnitt: Mit Formeln arbeiten).

Hinweis: Wenn mehr als 3 Vorlagenzuweisungen möglich sein müssen, kann man die Zellvorlagenzuweisung mit der Funktion VORLAGE() direkt in die Zelle integrieren.

4. Bedingungen

Es gibt grundsätzlich zwei Arten, eine Bedingung für die Zuordnung einer bestimmten Zellvorlage zu definieren. In der einfachen Variante wird der Inhalt der aktuellen Zelle überprüft, bei der zweiten Variante können Formeln benutzt werden, die auch Inhalte anderer Zellen berücksichtigen.

Eine bedingte Formatierung wird einer Zelle als harte Formatierung zugeordnet. Sie kann daher über den Aufruf Format → Standard wieder entfernt werden.

Wie andere Formate kann auch eine bedingte Formatierung kopiert und auf andere Zellen übertragen werden: Bearbeiten → Inhalte einfügen, dann alle Auswahlkästchen abwählen ausser: Formate.

4.1. Den aktuellen Zellinhalt überprüfen

Wenn die Bedingung auf einen ganzen Zellbereich angewendet werden soll, kann dieser zunächst markiert werden, dann wird die Bedingung von Anfang an auf den gesamten Bereich angewendet. Andernfalls kann man die Bedingung auch nachträglich kopieren (s.o.).

Als Wert kann ein konkreter Wert erfasst werden (Bsp: 999 oder "Sonntag") oder eine Zelladresse (Hinweis: Zahlen und Zelladressen ohne, Zeichenfolgen mit Anführungszeichen erfassen). Bei der Angabe einer Zelladresse muss diese als AbsoluteAdresse erfasst werden, wenn für die Formatdefinition ein ganzer Zellbereich ausgewählt wurde. Bei Auswahl einer einzelnen Zelle kann auch eine RelativeAdresse eingegeben werden, die dann beim Kopieren des Formates (s.o.) automatisch angepasst wird.

Aufgerufen wird das Bedingungsfenster über Format → Bedingte Formatierung

Folgende Operatoren sind möglich:
BedFormat2.png

BedFormat1.png

4.2. Mit Formeln arbeiten

Man kann für eine bedingte Formatierung die gleichen Formeln und Funktionen benutzen, wie man sie auch sonst in Calc benutzt. Im Fenster Format → Bedingte Formatierung muss bei den Bedingungen umgestellt werden von Zellwert ist auf Formel ist. Die Formeln werden ohne führendes = eingegeben. Bei der Eingabe einer bedingten Formatierung kann man das immer nur für eine Zelle tun. Anschliessend kann man die Bedingung kopieren (s.o.), wobei darauf geachtet werden muss, ob es sich bei den Zelladressen um relative oder AbsoluteAdressen handeln soll.

{OK} Tipp: Man kann die Formeln zunächst in einer Zelle mit Hilfe des FunktionsAutopiloten erfassen und dabei mit WENN() beginnen. Anschliessend kann man den inneren Teil von WENN() Kopieren (Ctrl+C) und in das Feld Formel ist einfügen (Ctrl+V).

Wenn mehrere Ergebnisse die Zuordnung einer Zellvorlage auslösen sollen, benutzt man die Funktion ODER().

BedForm4.png

4.2.1. Beispiel: Zeilen abwechselnd einfärben

Abgeleitet von der sonst verwendeten Zellen-Formatvorlage, wird eine Vorlage erstellt, die einen anderen Hintergrund hat.

Die Bedingung lautet dann:

ISTGERADE(ZEILE())
oder auch umgekehrt:
ISTUNGERADE(ZEILE()).

BedFormat5.png

4.2.2. Beispiel: Wochenenden farbig markieren

Erstellt man Tabellen mit Kalendarien kann man mit der bedingten Formatierung das Aussehen bestimmter Wochentage, z.B. der Wochenenden anpassen.

Die Formel lautet:

ODER(WOCHENTAG(A3)=1;WOCHENTAG(A3)=7) 1

Damit wählt man die Samstage (Ergebnis von Wochentag liefert 7) und die Sonntage (Ergebnis von Wochentag liefert 1)2 aus und weist Ihnen eine Zellvorlage mit der gewünschten Formatierung zu. Das Kopieren der Bedingunge erfolgt wie - siehe oben.

4.3. Wert-Abfrage und Formel-Bedingung mischen

Es ist auch möglich, die beiden Varianten zu mischen. Im nachstehenden Beispiel soll einerseits überprüft werden, ob der Zellinhalt ein unteres Limit erreicht hat und ausserdem eine andere Zellfarbe zugeordnet werden, wenn der Inhalt der Zelle keine Zahl ist.

BedFormat3.png


KategorieCalc KategorieTipps


5. Vorlagenzuweisung innerhalb von Formeln

Editieren

Mit der Funktion VORLAGE("Vorlagenname") bzw. T(VORLAGE("Vorlagenname"))3 kann die Anwendung einer bestimmten Zellvorlage erzwungen werden. Anders als bei der bedingten Formatierung ist man dabei nicht auf höchstens 3 Vorlagen beschränkt.

5.1. Mehr als 3 Bedingungen

Bei der normalen bedingten Formatierung können nur maximal 3 Bedingungen verwendet werden (Issue 8812). Benötigt man mehr Format-Zuweisungsregeln, so kann man die Zellvorlagen-Zuweisung direkt in eine Zellformel integrieren:

  • ="deine Formel" + VORLAGE("Name_deiner_Formatvorlage")

Dabei muss man unterscheiden zwischen Zellen, die einen numerischen Inhalt haben, und solchen, die einen alphanumerischen Inhalt haben. Im zweiten Fall muss die Vorlagenzuweisung so aussehen:

  • ="deine Formel" & T(VORLAGE("Name_deiner_Formatvorlage"))

5.1.1. Beispiel WENN()

="irgendeine_Formel"+Vorlage(wenn(aktuell()<0;"rot";wenn(aktuell()=0;"gelb";"grün")))

weist der Zelle die Vorlage „rot“ zu, wenn das Formelergebnis kleiner Null ist, „gelb“, wenn das Ergebnis genau Null ist, und „grün“, wenn das Ergebnis größer Null ist.

WENN(A4=1;
 A4+VORLAGE("1");
  WENN(A4=2;
   A4+VORLAGE("2");
    WENN(A4=3;
     A4+VORLAGE("3");
      WENN(A4=4;
       A4+VORLAGE("4");
 A4+VORLAGE("Standard")))))

funktioniert auch ohne das A4+ vor VORLAGE, ist aber nicht praktikabel.

<!> Die Anwendung dieser Vorgehensweise bei großen Tabellen kann zu einer erheblichen Prozessor-Belastung führen.

5.1.2. Beispiel SVERWEIS()

Im folgenden Beispiel soll je nach erteilter Schulnote ein Notentext und eine bestimmte Zellvorlage zugeordnet werden.

Für jede Note wird eine eigene Zellvorlage erstellt.
Sinnvoll kann es in diesem Fall sein, eine "Basis-Zellvorlage" zu erstellen, auf der die eigentlichen Noten-Zellvorlagen basieren, um gemeinsame Einstellungen nur einmal definieren zu müssen.

noten.png

Die Notentexte und die Namen der Zellvorlagen werden in einem freien Tabellenbereich erfasst.4 Für die Zuordnung ist es nicht notwendig, die Vorlagen hier auch anzuwenden, aber es dient der Übersichtlichkeit.

noten2.png

Mit der Funktion SVERWEIS() werden nun sowohl Notentext – SVERWEIS(A1;Tabelle2.A2:C7;2) – wie auch Vorlage – &T(VORLAGE(SVERWEIS(A1;Tabelle2.A2:C7;3)) – zugeordnet.

noten3.png

Standardmäßig geht die Funktion SVERWEIS von einer sortierten Liste aus. Wenn der gesuchte Wert in so einer sortierten Liste nicht gefunden wird, wird der davor liegende Wert zurückgegeben.

noten4.png

Wünscht man eine andere Art der Zuordnung, so kann man z.B. mit einer Rundungsformel für eine gezielte Zuordnung sorgen, und statt den Wert aus A1 direkt zu übernehmen, ihn erst umrechnen: =RUNDEN(A1-0,1).

5.2. Beispiel: „Viele“ Bedingungen

Hat man wirklich viele verschiedene Bedingungen zu unterscheiden, wird es sehr unübersichtlich, wenn man die Funktion VORLAGE() zusammen mit dem eigentlichen Inhalt der Zelle unterbringen muss:

bf2.png

Auch die WENN-Verschachtelungen werden mit wachsender Länge immer schwieriger zu handhaben.

Der Ausweg: Man nimmt die Vorlagen-Zuordnung mit SVERWEIS() vor und benutzt nicht die Originaltabelle, sondern eine eigene Tabelle, die sich nur um die Formatierung kümmert.

In einem unbenutzten Tabellenbereich legt man die Verweistabelle für die Vorlagen-Zuordnung an:
In der ersten Spalte werden die Bedingungen hinterlegt, daneben in der zweiten Spalte die Namen der jeweils anzuwendenden Vorlagen. Wenn man den Zellen mit den Vorlagennamen auch gleich noch die richtige Vorlage zuweist, hat man stets einen guten Überblick.
Man markiert beide Spalten und nimmt sie in einen Bereich5 auf: Daten → Bereich festelegen; in unserem Fall nennen wir ihn „Farben“. Wenn sich die ganzen Spalten in diesem Bereich befinden, kann man jederzeit weitere Bedingungen und Vorlagen anfügen, ohne die Formeln selbst anpassen zu müssen.

bf1.png

In der Annahme, unsere Daten befinden sich in Tabelle 1, dann könnte Tabelle 2 so aufgebaut werden:

In Zelle Tabelle2.A1 fügt man die Formel ein:

=Tabelle1.A1&T(VORLAGE(SVERWEIS(Tabelle1.A1;Farben;2;0)))

und „zieht“ diese Zelle nach rechts und unten, bis man den gesamten Tabellenbereich der Originaltabelle abdeckt.

Wenn es im Originalbereich Zellen gibt, die leer sind und/oder es Zellen gibt, für deren Inhalt keine Bedingung formuliert wurde, braucht man eine WENN-Unterscheidung:

=Tabelle1.A1&WENN(ISTNV(SVERWEIS(Tabelle1.A1;Farben;2;0));
             T(VORLAGE("Standard"));
             T(VORLAGE(SVERWEIS(Tabelle1.A1;Farben;2;0))))

In diesem Beispiel werden die Inhalte in der formatierten Tabelle durch die Verbindung mit & zu Texten. Wenn man das nicht wünscht, benutzt man statt &T(VORLAGE()) die Formel +VORLAGE().

Eventuell muss man dann noch mit einer weiteren WENN-Formel unterscheiden:

=WENN(ISTZAHL(Tabelle1.A1);
  Tabelle1.A1+WENN(ISTNV(SVERWEIS(Tabelle1.A1;Farben;2;0));
                   VORLAGE("Standard");
                   VORLAGE(SVERWEIS(Tabelle1.A1;Farben;2;0)));
  Tabelle1.A1&WENN(ISTNV(SVERWEIS(Tabelle1.A1;Farben;2;0));
                   T(VORLAGE("Standard"));
                   T(VORLAGE(SVERWEIS(Tabelle1.A1;Farben;2;0)))))

5.3. Beispiel: „Viele“ Bedingungen in Kombination mit der bedingten Formatierung

Es ist auch möglich, die VORLAGE()-Formel nicht in der Zelle, sondern als bedingte Formatierung zu definieren (Daten → Bereich festelegen). Hier wählt man dann "Formel ist" und trägt die VORLAGE()-Formel ein. Die zu verwendende Vorlage läßt man hier dann einfach auf Standard stehen.

Auf diese Weise kommen einem die VORLAGE()-Formeln bei der normalen Arbeit nicht mehr in die Quere. Eine Formel als bedingte Formatierung könnte etwa so aussehen:

VORLAGE(SVERWEIS(Tabelle1.A1;Farben;2;0))

5.4. Siehe auch

KategorieCalc


KategorieCalc KategorieTipps

  1. Für das spätere Kopieren der Adresse kann es sinnvoll sein, wenn man keine direkten Zelladressen verwendet, sondern indirekt adressiert. So bedeutet z.B. INDIREKT(ADRESSE(ZEILE();SPALTE()-2)): „die Zelle zwei Spalten links von der aktuellen“. (1)

  2. Die Funktion WOCHENTAG() hat einen zweiten Parameter, über den man steuern kann, ob die Zählung am Sonntag oder am Montag mit 1 beginnt. Definiert man die Formel als =WOCHENTAG(Datum;2), so wäre der Wochentag für Samstag =6 und für Sonntag =7. (2)

  3. Wenn man es mit Zahlen zu tun hat, ordnet man eine Vorlage mit +VORLAGE("Vorlagenname") zu. Verwendet man diese Formel mit einem Zellinhalt, der nicht aus Zahlen besteht, so erscheint in der Zelle zusätzlich zum eigentlichen Inhalt eine Null. Das kann man vermeiden, indem man bei Zeichenketten die Formel verwendet: &T(VORLAGE("Vorlagenname")). Wenn man die zweite Variante zusammen mit einem Zahlen-Inhalt verwendet, so ändert man den Inhalt zu Text, was dazu führt, dass er – wenn er in weiteren Formeln zum Rechnen benutzt wird – den Wert 0 annimmt. (3)

  4. Tipp: Verwenden Sie Zell- oder Bereichsnamen. (4)

  5. Statt einer Bereichsdefinition kann man auch einfach einen Namen vergeben: Einfügen → Namen. Der Vorteil des Bereichs besteht darin, dass man über Daten → Bereich auswählen immer schnell feststellen kann, ob die Bereichsdefinition stimmt. (5)


LizenzBedingungen | AnbieterKennzeichnung | DatenschutzErklärung | Stand: 2013-04-28