Clevere Matrixformel: Verweisen Sie auf die Spaltenüberschrift in der letzten nicht leeren Zelle einer Zeile

Kombinieren Sie die vier Funktionen WENNFEHLER(), INDEX(), MAX() und WENN()

Der Geschäftsführer im Vertrieb schickt Ihnen eine Aufstellung über die pro Monat abgeschlossenen Verträge auslaufender Produkte ①. Sie sollen mit einer Formel in der Spalte N pro Produkt den letzten Verkaufsmonat angeben – und zwar ohne Hilfszeilen und -spalten. Wurde kein Vertrag mehr abgeschlossen, geben Sie in der Spalte N eine leere Zelle aus.

Dieses auf den ersten Blick banale Beispiel entpuppt sich ohne die Verwendung von Hilfszeilen oder -spalten als harte Nuss. Als Excel für die Praxis-Leser knacken Sie die Nuss! Wir haben für die Problemlösung die folgende Matrixformel in der Zelle N2 kreiert ②:

{=WENNFEHLER(INDEX($B$1:$M$1; MAX(WENN(B2:M2<>"";SPALTE(B2:M2)-1;-1)));"")}

Schauen Sie sich die Funktionsweise Schritt für Schritt an

Starten Sie mit der WENN()-Bedingung, die in die Matrixformel integriert ist: {=WENNFEHLER(INDEX($B$1:$M$1;MAX(WENN(B2:M2<> "";SPALTE(B2:M2)-1;-1)));"")}

Die WENN()-Bedingung erzeugt eine fiktive Hilfszeile in der Matrixformel und prüft für die Zellen B2 bis M2, ob diese leer sind oder nicht. Ist eine Zelle leer, dann wird der Wert -1 sonst über die Funktion SPALTE() die jeweilige Spaltennummer abzüglich des Wertes 1 zurückgegeben.
Die Subtraktion von 1 wird in der Formel benötigt, da die erste Spalte in der Tabelle keinen Monatsnamen, sondern die Produktbezeichnung beinhaltet. Weiter unten erfahren Sie, wie Sie mit der Funktion INDEX() den entsprechenden Monatsnamen anzeigen lassen, der – würden Sie die 1 nicht abziehen – wegen der zusätzlichen verwendeten Spalte A um genau eine Spalte nicht stimmen würde.

Falls alle Zellen im Bereich B2:M2 leer sind, erzeugt der Wert -1 (keine Produktverkäufe) einen Fehler, den wir nutzen, um eine leere Zelle darzustellen. Die aktive Hilfszeile ist in Bild ③ in der Zeile 3 zu sehen.

Im nächsten Schritt lesen Sie mit der MAX()-Funktion, in die die WENN()-Bedingung verschachtelt ist, den größten Wert aus. Dies ist in der Zeile 3 der Wert 12 (Spalte 13 abzüglich 1; siehe Hilfszeile in Abbildung ③):

{=WENNFEHLER(INDEX($B$1:$M$1;MAX(WENN(B2:M2<> "";SPALTE(B2:M2)-1;-1)));"")}

Diesen MAX-Wert übergeben Sie an die Funktion INDEX(). Damit wird der entsprechende Kalendermonat in der Zeile 1 ausgelesen. Der Datenbereich der Funktion INDEX() ist der Bereich $B$1:$M$1. Der übergebene MAX-Wert – im Beispiel 12 – besagt, dass der zwölfte Wert in der Liste, also Dez für den Monat Dezember, übergeben wird:

{=WENNFEHLER(INDEX($B$1:$M$1;MAX(WENN(B2:M2<> "";SPALTE(B2:M2)-1;-1)));"")}

Sind alle Zellen im Monatsbereich der Tabelle leer, dann beträgt der größte Wert -1 (siehe WENN()-Bedingung am Anfang). Wird der Wert -1 an die Funktion INDEX() übergeben, führt dies unweigerlich zu einem Fehlerwert, da der Listenbereich der Funktion INDEX() nur zwölf Einträge beinhaltet und somit den Eintrag -1 nicht finden kann. Sie fangen diesen Fehlerwert über die Funktion WENNFEHLER() ab und geben stattdessen eine leere Zeichenkette zurück. Im Beispiel ist dies bei Produkt C in der Zeile 4 der Fall, denn für dieses Produkt konnte kein Vertrag mehr abgeschlossen werden:

{=WENNFEHLER(INDEX($B$1:$M$1;MAX(WENN(B2:M2<> "";SPALTE(B2:M2)-1;-1)));"")}

Da es sich um eine Matrixformel handelt, schließen Sie die Eingabe der Formel mit der Tastenkombination Strg+Umschalt+Eingabe ab.