Ablesehilfe

Die COVID-19-Pandemie hat im Internet eine Unmenge an Tabellen hervorgebracht. Dabei ist mir angenehm aufgefallen, dass zumeist eine Ablesehilfe aktiv war, also diejenige Zeile mit dem Fokus hervorgehoben wurde.
Im Zuge eines Projektes hätte ich einst genau so etwas gebraucht. Da Excel das nicht anbot, hatte ich VBA zu Hilfe genommen und der ganzen Zeile der selektierten Zelle oben und unten einen Rahmen verpasst. Die beiden Linien störten die vorhandenen bedingten Formatierungen nicht, die ein Muster und Hintergrundfarben verwendeten. Ein Office-Update stieß sich aber offenbar daran, insbesondere wenn ein Filter die Zeile ausblendete, die beim Selektionswechsel wieder vom Rahmen befreit wurde. Beim Speichern hieß es dann, dass die Datei beschädigt sei!

Die Ablesehilfe war für die User aber so vorteilhaft, dass deren Weiterbestehen quasi Pflicht war! Die bereits vorhandenen bedingten Formatierungen waren die Lösung, auch wenn die Rahmen nicht mehr ganz so dick sein konnten. Im Nachhinein könnte man natürlich sagen, dass das gleich der bessere Ansatz gewesen wäre. Wie auch immer, die Robustheit dieser eingebauten Funktionalität sollte überdies jedem weiteren Update standhalten!

Eine gewisse Ablesehilfe wird seit Excel 2007 angeboten, da man seither Tabellen mit vordefinierten oder individuell definierbaren Tabellenformaten verwenden kann. Dabei hilft eine Abfolge von Zeilen mit heller oder keiner und dunklerer oder heller Füllfarbe. Das ließe sich ebenso mittels bedingter Formatierung erledigen, bis zu dieser Version standen allerdings “nur” drei zur Verfügung. Des Weiteren ist in Kompatibilitätsprobleme bei der bedingten Formatierung zu lesen: In Excel 97-2003 stellt die bedingte Formatierung ohne Beendigung bei Erreichen der Bedingung keine Option dar. D. h. die Formatierung endet nach der ersten erfüllten Bedingung, also je nach Reihenfolge “überdeckt” entweder die Ablesehilfe etwaige andere Formatierungen oder letztere “schlucken” erstere.

Die folgende Datei enthält zur Veranschaulichung meiner Lösung 9 Tabellen:

  • Für die Realisierung der bedingten Formatierungen wird die Funktion ZELLE(Infotyp; [Bezug]) verwendet, mit Infotyp “spalte” bzw. “zeile”, der optionale Bezug entfällt.
  • Damit die bedingten Formatierungen gesichert aktualisiert werden, braucht es in VBA die Subroutine Worksheet_SelectionChange mit dem Befehl Application.ScreenUpdating = True, bei mehreren Tabellenblättern kann man stattdessen auch Workbook_SheetSelectionChange verwenden.
  • Beim Einfügen von Zeilen oder Spalten wird die bedingte Formatierung bei allen Tabellen verlässlich angepasst. Beim Anfügen von Spalten bei vordefinierten Tabellen braucht die Formel gewöhnlich eine manuelle Anpassung!
  • Die linken Tabellen sind klassisch einfach, von oben nach unten ergänzt um bedingte Formatierungen mit (oben) Hintergrundfarbe, (mittig) oberem und unterem Rahmen und (unten) Schriftschnitt fett.
  • Die mittleren Tabellen enthalten mittels Bedingter Formatierung in den ungeraden Zeilen graue Hintergrundfarben sowie von oben nach unten eine dunklere Hintergrundfarbe, ein “Fadenkreuz” aus sich kreuzenden Rahmen und ein Muster. Das “Fadenkreuz” könnte im Falle eines hochgestellten Monitors oder/und kleiner Spaltenbreiten von Vorteil sein?!
  • Die rechten Tabellen sind jene, die seit Excel 2007 zur Verfügung stehen, wodurch man sich unterschiedliche Zeilenformatierungen sparen kann. Die ergänzenden bedingten Formatierungen entsprechen jenen der mittleren Tabellen, mit einem Unterschied: Sie “reagieren” nur noch, wenn sich die selektierte Zelle tatsächlich innerhalb des formatierten Bereichs befindet, was bei mehreren Tabellen sicherlich sinnvoll ist.

Bemerkung: Wegen des obig erwähnten Kompatibilitätsproblems nicht als 97-2003-Version.

ODER für Matrixformeln

Der Funktion ODER(Wahrheitswert1;[Wahrheitswert2];…) können bis zu 255 (30 in Excel 97 – 2003) Bedingungen übergeben werden, die entweder als WAHR oder als FALSCH bewertet werden können. Diese Argumente dürfen auch als Arrays (Matrizen) vorliegen, was einen verleiten könnte, ODER auch in einer Matrixformel zu verwenden, was aber daran scheitert, dass immer nur ein Wert zurückgegeben wird!

Abhilfe
Anstatt ODER(Wahrheitsarray1;Wahrheitsarray2;…) nehme man bei Matrixformeln den logischen Ausdruck {=(Wahrheitsarray1+Wahrheitsarray2+…>0)}. Die geschlungenen Klammern ergeben sich durch Eingabe mittels STRG+UMSCHALT+EINGABE.

Alternative
Die Benutzerdefinierte Funktion OR4AF(Wahrheitswert1;[Wahrheitswert2];…) (Abkürzung für “OR for Array Formulas”) funktioniert wie das normale ODER (allerdings werden beliebig viele Argumente entgegengenommen), wenn sie von einer “gewöhnlichen” Formel aufgerufen wird, in allen anderen Fällen (Aufruf durch Matrixformel, VBA,…) werden die Wahrheitswerte wie folgt abgearbeitet:

  • 2-dimensionale Matrix führt zum Fehler #WERT!
  • 1-dimensionale Matrizen können gemischt als Spalten- und Zeilenvektoren vorliegen
  • Der größte Vektor bestimmt den Ergebnisumfang
  • Fehlende Werte eines kleineren Vektors werden ignoriert
  • Ein Einzelwert wird zu einem Vektor mit einer Komponente
  • Dabei werden wie beim normalen ODER Text und fast alle Zahlen ignoriert, denn 0 entspricht FALSCH

Die folgende Datei zeigt die Verwendung der Benutzerdefinierten Funktion OR4AF im Vergleich zum normalen ODER anschaulich für die verschiedensten Fälle:

Bereich invers durchlaufen

In VBA verwendet man zum Durchlaufen von Aufzählungen (z. B. Workbooks, Worksheets,…) gewöhnlich die For Each…Next-Anweisung.
Will man dies in umgekehrter Reihenfolge tun, dann geht das mittels For *.Count To 1 Step -1…Next, wenn für die jeweilige Aufzählung die Eigenschaft Count vorliegt und auf die einzelnen Elemente per Index zugegriffen werden kann.
Ein Zellbereich bietet das mit Cells.Count zwar auch an, aber die Items beziehen sich nicht direkt auf den gesamten Bereich, sondern zuerst auf Areas, die sich schließlich auf den Bereich selbst beziehen. Da ein Bereich aus mehreren Areas bestehen kann, müssen also diese und die darin enthaltenen Items abgearbeitet werden.

Die Lösung sind 2 verschachtelte, invers laufende “For…Next”-Schleifen, die Areas und Cells abarbeiten und einer Collection übergeben. Anstatt der Aufzählung könnte auch ein temporärer Name mit den Bezügen gefüttert werden, was aber einen Eingriff im Tabellenblatt oder in der Arbeitsmappe bedeuten würde.

Die Benutzerdefinierte Funktion RANGEINVERSION(Bereich) erwartet einen Bereich, der sich aus einer Selektion, einem Namen oder aus VBA-Code heraus ergeben kann.
Das Tabellenblatt RANGEINVERSION der folgenden Arbeitsmappe bietet dazu einen Test mittels des Namens Testrange an:

UND für Matrixformeln

Der Funktion UND(Wahrheitswert1;[Wahrheitswert2];…) können bis zu 255 (30 in Excel 97 – 2003) Bedingungen übergeben werden, die entweder als WAHR oder als FALSCH bewertet werden können. Diese Argumente dürfen auch als Arrays (Matrizen) vorliegen, was einen verleiten könnte, UND auch in einer Matrixformel zu verwenden, was aber daran scheitert, dass immer nur ein Wert zurückgegeben wird!

Abhilfe

Anstatt UND(Wahrheitsarray1;Wahrheitsarray2;…) nehme man bei Matrixformeln das Produkt Wahrheitsarray1*Wahrheitsarray2*…

Alternative

Die Benutzerdefinierte Funktion AND4AF(Wahrheitswert1;[Wahrheitswert2];…) (Abkürzung für “AND for Array Formulas”) funktioniert wie das normale UND (allerdings werden beliebig viele Argumente entgegengenommen), wenn sie von einer “gewöhnlichen” Formel aufgerufen wird, in allen anderen Fällen (Aufruf durch Matrixformel, VBA,…) werden die Wahrheitswerte wie folgt abgearbeitet:

  • 2-dimensionale Matrix führt zum Fehler #WERT!
  • 1-dimensionale Matrizen können gemischt als Spalten- und Zeilenvektoren vorliegen
  • Der größte Vektor bestimmt den Ergebnisumfang
  • Fehlende Werte eines kleineren Vektors werden ignoriert
  • Ein Einzelwert wird zu einem Vektor mit einer Komponente
  • Dabei werden wie beim normalen UND Text und fast alle Zahlen ignoriert, denn 0 entspricht FALSCH

Die folgende Datei zeigt die Verwendung der Benutzerdefinierten Funktion AND4AF im Vergleich zum normalen UND anschaulich für die verschiedensten Fälle:

Signifikante Stellen

Soll in Excel eine Zahl abweichend vom “Standard” in bestimmter Art und Weise anzeigt werden, dann kann man mit Zahlenformaten, insbesondere der Kategorie “Benutzerdefiniert”, einiges erreichen.
Selbst Signifikante Stellen lassen sich mit der Kategorie “Wissenschaft” realisieren. Wenn mit diesem Wert noch weitergerechnet werden soll, dann dürfte man die gewöhnungsbedürftige Darstellung in Kauf nehmen, da eine Rundung erst möglichst spät innerhalb des Rechnungsgangs durchgeführt werden sollte.
Bei einem Endergebnis, das ohnehin auf mehrere unsichere Werte aufbaut, kann allerdings eine gerundet Darstellung anderer Art erwünscht sein!

So wie ich wurden offensichtlich viele auf Excelformeln fündig. Übrigens eine äußerst empfehlenswerte Seite! Ich habe alle 3 Bücher erstanden und kann sie jedem Wissbegierigen nur ans Herz legen!
Der leider schon verstorbene Klaus Kühnlein hat die Aufgabe eindrucksvoll mittels Formel umgesetzt, das prinzipielle Ziel erfüllend, ohne VBA auszukommen.

Das erste, was mich bei Formeln stört, ist das mehrfache Vorkommen ein und desselben Terms, besonders wenn wegen dessen Länge ein Zwischenergebnis quasi erzwungen wird. Dies deswegen, weil ansonsten mehrere identische Berechnungen erfolgen.
Für mich stehen Aufwand zu Nutzen im Vordergrund und nicht das hehre Ziel, VBA zu vermeiden, selbst wenn auch ich gerne ohne auskomme. Deswegen habe ich auf Basis der vorgefundenen Formel eine Benutzerdefinierte Funktion programmiert, die natürlich auch mit Matrixformeln zurecht kommt.
Die folgende Datei zeigt die Umsetzung via Formel und Matrixformel mit und ohne VBA: