Beitrag aus SmartTools Excel Weekly
Letzte Zeile im Überlaufbereich eines dynamischen Arrays formatieren
Excel 365 2024 2021 2019
FRAGE Vor kurzem hatten Sie beschrieben, wie sich eine Gesamtsumme in den Überlaufbereich einer dynamischen Arrayformel einbinden lässt. Das Prinzip konnte ich erfolgreich in meiner Tabelle anwenden. Jetzt würde ich gerne die Zeile mit der Gesamtsumme mit Rahmenlinien und einer Formatierung in Fett vom Rest des Überlaufbereichs abgrenzen. Die Höhe des Überlaufbereichs ist bei mir allerdings von einem Kriterium abhängig, weshalb ich leider nicht einfach ganz bestimmte Zellen formatieren kann. Wie kann ich die gewünschte Formatierung variabel auf die jeweils letzte Zeile des Überlaufbereichs anwenden?
H. Voigt
ANTWORT Für eine variable Formatierung des Überlaufbereichs einer dynamischen Arrayformel bietet sich die bedingte Formatierung an. Wenn Sie als Kriterium der bedingten Formatierung eine Formel einsetzen, die die letzte Zeile des Überlaufbereichs ermittelt, können Sie diese Zeile individuell formatieren. Die Formatzuweisung passt sich dann automatisch an die Höhe des Überlaufbereichs an.
Excel ist aber leider nicht in der Lage, den Anwendungsbereich der bedingten Formatierung dynamisch an den Überlaufbereich einer dynamischen Arrayformel anzupassen. Sie müssen also manuell bestimmen, in welchen Zellen die bedingte Formatierung gelten soll.
Um der letzten Zeile eines Überlaufbereichs eine spezielle Formatierung zuzuweisen, gehen Sie deshalb wie folgt vor:
- Markieren Sie den maximal möglichen Zellbereich, den der Überlaufbereich der dynamischen Arrayformel einnehmen kann.
- Wählen Sie Start-Formatvorlagen-Bedingte Formatierung-Neue Regel an.
- Aktivieren Sie den Regeltyp "Formel zur Ermittlung der zu formatierenden Zellen verwenden".
- Geben Sie eine Formel nach folgendem Muster in das Feld der Regelbeschreibung ein:
=ZEILE(<dyn. Arrayformelzelle>)=ZEILE(
$<dyn. Arrayformelzelle>)+MAX(ZEILEN(
INDIREKT("<dyn. Arrayformelzelle>#")))-1
- Wenn Ihre dynamische Arrayformel zum Beispiel in Zelle E5 steht, geben Sie folgende Formel ein:
=ZEILE(E5)=ZEILE($E$5)+MAX(ZEILEN(INDIREKT("E5#")))-1
- Klicken Sie auf die Schaltfläche Formatieren und stellen Sie die Formatierung ein, die Sie der letzten Zeile des Überlaufbereichs zuweisen wollen.
- Nachdem Sie den Formatierungsdialog mit Ok bestätigt haben, schließen Sie auch das Dialogfenster der neuen Formatierungsregel mit Ok.
Nun sollte immer die letzte Zeile des Überlaufbereichs Ihrer dynamischen Arrayformel ein spezielles Format aufweisen, auch wenn sich die Höhe des Überlaufbereichs ändert.
Eine Besonderheit der Formel in der bedingten Formatierung ist, dass sie die Anzahl der Zeilen des Überlaufbereichs unter Zuhilfenahme der Funktion INDIREKT ermittelt. Nur so ist es möglich, auf den Bereich bezugzunehmen. Einen direkten Verweis auf "E5#" (auf den Überlaufbereich der Formel in E5) würde Excel nicht akzeptieren.
Aber so kann die Formel prüfen, ob die aktuelle Zeilennummer der maximalen Zeilenanzahl des Überlaufbereichs unter Einbeziehung des Startzellenversatzes entspricht, und sie nimmt dann die gewünschte Formatierung vor.