Tabellenkalkulation

Mit Excel und Calc können Sie rechnen! Keine Office-Komponente bietet so viele ungenutzte Möglichkeiten wie das Kalkulationsmodul. Mit unseren Tipps erkennen Sie, was wirklich in den Anwendungen steckt.

Problem: Sie möchten in einem Zellbereich fehlerhafte Eingaben von vornherein ausschließen. Dabei soll Excel Ihre eigenen Kriterien berücksichtigen.

Lösung (Excel 97-2003): Mit der Gültigkeitsprüfung von Excel lässt sich das realisieren. Nach Markieren des gewünschten Zellbereichs – etwa einer gesamten Spalte – und Aufruf des Dialogs „Daten, Gültigkeit“ bietet Excel wesentliche Kriterien direkt an. So genügt es oft, die Vorgabe „Ganze Zahl“ mit Minimum und Maximum auszuwählen, um etwa negative und unrealistische Werte sowie Brüche zu verhindern. Bei Texteingaben kann die Vorgabe der „Textlänge“ nützlich sein, wenn die Tabelleninhalte später in ein festes Layout exportiert werden sollen.

Nützlich ist ferner die Vorgabe einer Liste (irgendwo auf demselben Tabellenblatt) als Quelle: Der markierte Bereich verweigert dann jede Eingabe, die nicht in dieser Liste steht, und bietet außerdem per Drop-down-Pfeil alle Einträge dieser Liste an. Die „Quelle“ kann als Bezug in der Form „=Z1:Z100“ oder als Name, etwa „=Produkte“, angegeben werden, sofern für den Zellbereich vorher ein Name definiert wurde.

Wenn die Kriterien komplizierter werden, kommt man unter „Zulassen“ nur noch mit dem Eintrag „Benutzerdefiniert“ und einer Formel weiter. Auf diesem Weg können Sie für einen Zellbereich zum Beispiel festlegen, dass nur Werte aus einer bestimmten Liste eingetragen werden dürfen und außerdem kein Eintrag doppelt auftreten darf. Die Formel dafür lautet:

=und(vergleich(D1;$L$1:$L$100;
0)>0;zählenwenn($D:$D;D1)<2)

Wie bei der vorgegebenen Option „Liste“ entscheidet Excel anhand einer Werteliste (L1:L100), ob der Eintrag in Spalte D erlaubt ist. Doppel sind ausgeschlossen, weil „Zählenwenn“ dann eine „2“ meldet. Den eingegebenen relativen Bezug – hier D1 – überträgt Excel automatisch auf den gesamten markierten Bereich, also etwa auf die gesamte Spalte D.

Ein weiteres Beispiel für eine benutzerdefinierte Gültigkeitsregel:

=und(Links(B1;3)="pcw";Rechts
(B1;4)=".zip”;länge(B1)>10)

Das erzwingt für die Eingabe von Dateinamen eine vorgegebene Systematik und Mindestlänge.

Problem: Excel-Tabellen sollen optimale Übersicht bieten, indem sie das Über- oder Unterschreiten bestimmter Werte optisch signalisieren.

Lösung (Excel 97-2003 / Calc): Excel bietet unter „Format, Bedingte Formatierung“ mit „Zellwert ist“ eine Reihe einfacher Kriterien für die automatische Formatierung. Wenn Sie etwa „Größer als“ wählen und rechts daneben eine Zahl eingeben, erhalten die vorher markierten Zellen beim Überschreiten dieses Grenzwerts das Format, das Sie über den „Format“-Button definieren. Da für einen Zellbereich insgesamt drei Bedingungen möglich sind, können Sie mühelos etwa hohe, niedrige und durchschnittliche Werte farblich abgrenzen.

Die Funktion „Zellwert ist“ stößt aber schnell an Grenzen: Erstens arbeitet Excel hierbei mit starren Konstanten, zweitens können Sie mehrere Bedingungen nicht im Sinn eines logischen „UND“ aneinander koppeln, drittens sind nur numerische Werte sinnvoll. Mit „Formel ist“ lösen Sie auch anspruchsvolle Aufgaben: Um beispielsweise in Spalte A jeden Wert optisch hervorzuheben, der über 8000 liegt (starre Konstante), den Durchschnittswert der Spalte um das Doppelte schlägt (intelligente Variable) oder das Maximum der Spalte darstellt, verwenden Sie folgende Formel:

=oder(a1>mittelwert(a:a)*2;a1=
max(a:a);a1>8000)

Bei der Hervorhebung niedriger Werte sollten Sie immer ausdrücklich vermeiden, dass leere Zellen (Wert „0“) mitformatiert werden, also etwa:

=und(A10)

Der relative Bezug (A1) wird wie üblich auf den gesamten vorher markierten Bereich übertragen. „Formel ist“ kann auch Textoder Datumswerte gezielt formatieren:

=und(A1>0;heute()-A1>730)

Diese Bedingung zeigt alle Zellen in einem definierten Format an, die ein Datum enthalten, das – vom aktuellen Datum ab gerechnet – länger als zwei Jahre zurückliegt. Eine Formatierung in den vier Stufen „Sehr alt – alt – aktuell – künftig“ sehen Sie in der Abbildung.