Den höchsten oder tiefsten Wert in einem Bereich finden, ist eine immer wiederkehrende Problematik im Reporting. Normalerweise kann man sich den tiefsten Wert in einer Tabelle mit der Formel =MIN(DATENBEREICH) und den höchsten Wert mit der Formal =MAX(DATENBEREICH) anzeigen lassen. Allerdings wird dabei nur der Wert angezeigt, nicht aber der Name der zugehörigen Spalte und Zeile. In diesem Artikel erkläre ich euch anhand eines Beispiels, wie ihr euch den tiefsten und höchsten Wert in einem Bereich mit dazugehöriger Spalte und Zeile anzeigen lassen könnt.

Ein Beispiel

Neulich fragte mich ein Arbeitskollege, wie er in folgender Tabelle automatisch das Datum anzeigen kann, an dem der Wert am tiefsten und höchsten ist.

Die Lösung mit dem tiefsten und höchsten Wert kannst du dir hier anschauen:

Vorgehensweise:

1. Namen für Bereiche festlegen (um die Formeln zu vereinfachen)

WertFinden =Tabelle1!$A$10
Monate =Tabelle1!$B$2:$I$2
Jahre =Tabelle1!$A$3:$A$8
Daten =Tabelle1!$B$3:$I$8

2. Formeln erstellen

Herleitung der Formeln:
Wir benötigen die SUMMENPRODUKT Funktion, da diese Funktion als einzige wie eine Matrixformel funktioniert, ohne dass man eine Matrix eingeben muss. Zum Beispiel gibt die folgende Formel an, wie oft ein Wert im Datenbereich dem Wert WertFinden entspricht:

=SUMMENPRODUKT((Daten=WertFinden)*1)

Der Teil (Daten=WertFinden) liefert eine Matrix mit wahren und falschen Werten. Wir müssen diese Matrix mit 1 multiplizieren, um die Werte der Matrix in 1 und 0 zu konvertieren, die gezählt werden können.
Nehmen wir der Einfachheit halber erst einmal an, dass nur ein Wert dem gesuchten Wert WertFinden entspricht. Mit folgender Formel können wir die dazugehörige Zeile finden:

=SUMMENPRODUKT(ZEILE(Daten)*(Daten=WertFinden))

Diese Formel funktioniert, da die Matrix, die durch die Funktion (Daten=WertFinden) geliefert wird, nur einen wahren Wert hat. Wenn wir die Zeilennummer des wahren Wertes mit dem wahren Wert multiplizieren und die Ergebnisse addieren, liefert die Formel die Zeilennummer des gesuchten Wertes.

Aber wir möchten nicht die Zeilennummer, sondern die Index Nummer innerhalb des Datenbereiches … welches gleichzeitig die Index Nummer des Jahresbereiches ist. Deshalb subtrahieren wir die Zeilennummer der obersten Zeile des Datenbereiches und addieren 1:

=SUMMENPRODUKT(ZEILE(Daten)*(Daten=WertFinden))-Zeile(Daten)+1

Diese Formel ähnelt der VERGLEICH Funktion. Aber wenn der Datenbereich zwei Werte aufweist, die dem Wert WertFinden entsprechen, liefert diese Formel einen falschen Wert. Deshalb müssen wir einen Test zur Formel hinzufügen, um sicherzugehen, dass nur ein wahrer Wert existiert.

=WENN(SUMMENPRODUKT((Daten=WertFinden)*1)<>1;NV();SUMMENPRODUKT(ZEILE(Daten)*(Daten=WertFinden))-Zeile(Daten)+1)

3. INDEX Funktionen erstellen

Nun können wir diesen Wert in eine INDEX Funktion hinzufügen, um den gesuchten Jahreswert zu erhalten:

A11:=INDEX(Jahre;WENN(SUMMENPRODUKT((Daten=WertFinden)*1)<>1;NV();SUMMENPRODUKT(ZEILE(Daten)*(Daten=WertFinden))-ZEILE(Daten)+1))
Folgende INDEX Funktion liefert den Monatswert. In der Formel müssen wir beachten, dass sich die Monate in einer Spalte befinden.

A12:=INDEX(Monate;WENN(SUMMENPRODUKT((Daten=WertFinden)*1)<>1;NV();SUMMENPRODUKT(SPALTE(Daten)*(Daten=WertFinden))-SPALTE(Daten)+1))

4. Tiefsten oder höchsten Wert ermitteln

Um den tiefsten Wert in unserem Beispiel zu ermitteln, müssen wir in der Zeile A10 =MIN(Daten) eingeben.

Um den höchsten Wert anzuzeigen, nutzen wir die Funktion =MAX(Daten).

Die Datei mit dem Beispiel und der Lösung für den tiefsten und höchsten Wert kannst du dir hier anschauen.

Wann und wo setzt ihr diese Funktion ein?