Min-/Max-Wert nachschlagen – Excel & Google Sheets
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie in Excel und Google Sheets nach Min- und Max-Werten suchen können.
INDEX-VERGLEICH mit MIN
Wir können die Kombination der Funktionen INDEX, VERGLEICH und MIN verwenden, um die niedrigste Zahl zu ermitteln.
=INDEX(B3:B7;VERGLEICH(MIN(C3:C7);C3:C7;0))
Schauen wir uns die Formel an:
Die MIN-Funktion
Die MIN-Funktion gibt die kleinste Zahl aus einer Liste zurück. Sie ignoriert nicht-numerische Werte (z.B. leere Zellen, Textzeichenketten oder boolesche Werte).
=MIN(C3:C7)
Die VERGLEICH-Funktion
Anschließend wird das Ergebnis der MIN-Funktion als Suchwert in die VERGLEICH-Funktion eingegeben, um die Position oder Koordinate des niedrigsten Wertes in der Liste zu finden.
=VERGLEICH(E3;C3:C7;0)
Hinweis: Wir setzen das 3. Argument (Vergleichstyp) der VERGLEICH-Funktion auf 0, was exakte Übereinstimmung bedeutet.
Die INDEX-Funktion
Die Ausgangskoordinate der VERGLEICH-Funktion wird dann an die INDEX-Funktion weitergegeben, um den entsprechenden Wert aus der Liste zu extrahieren, an dem wir interessiert sind (z. B. B3:B7).
=INDEX(B3:B7;F3)
Hinweis: Wenn das erste Argument (d.h. Array) der INDEX-Funktion eine 1D-Liste ist, kann das zweite Argument (d.h. [Zeile]) die Zeilenkoordinate oder die Spaltenkoordinate sein, je nach Anordnung der 1D-Liste. Bei einer vertikalen Liste ist es die Zeilenkoordinate, während es bei einer horizontalen Liste die Spaltenkoordinate ist.
Insgesamt extrahiert die MIN-Funktion den niedrigsten Wert. Die VERGLEICH-Funktion sucht den niedrigsten Wert und gibt seine Position zurück. Anschließend extrahiert die INDEX-Funktion den entsprechenden Wert aus der Liste, die uns interessiert. Kombiniert man all diese Funktionen, erhält man unsere ursprüngliche Formel:
=INDEX(B3:B7;VERGLEICH(MIN(C3:C7);C3:C7;0))
SVERWEIS mit MIN
Wir können auch die SVERWEIS-Funktion anstelle der INDEX-VERGLEICH-Formel als Nachschlageformel verwenden, aber wir müssen die Struktur unserer Tabelle ändern, indem wir die Nachschlagespalte (z. B. Gesamtumsatz) zur ersten Spalte machen.
Genau wie bei INDEX-VERGLEICH geben wir die MIN-Funktion als Nachschlagewert für den SVERWEIS ein.
=SVERWEIS(MIN(B3:B7);B3:C7;2;FALSCH)
Hinweis: Die Funktion SVERWEIS sucht den Nachschlagewert in der ersten Spalte der Tabelle und gibt den entsprechenden Wert aus der Spalte zurück, die durch den Spaltenindex (d.h. das dritte Argument) definiert ist.
XVERWEIS mit MIN
Eine weitere Alternative für die Nachschlageformel ist die XVERWEIS-Funktion, die die bequemste Lösung darstellt, aber eine neuere Version von Excel erfordert. Diese Lösung erfordert die wenigsten Funktionen und es ist dabei nicht notwendig, Daten umzustrukturieren.
Wir können die MIN-Funktion in XLOOKUP verschachteln, um nach dem niedrigsten Wert zu suchen.
=XVERWEIS(MIN(C3:C7);C3:C7;B3:B7)
Hinweis: Standardmäßig sucht die XVERWEIS-Funktion nach einer exakten Übereinstimmung vom obersten Punkt des Nachschlage-Arrays (2. Argument) nach unten (d.h. von oben nach unten). Sobald sie eine Übereinstimmung findet, gibt sie den entsprechenden Wert aus dem Rückgabe-Array (3. Argument) zurück. Andernfalls gibt sie einen Fehler zurück.
Wie bereits erwähnt, ist die Struktur der Lösungen die gleiche. Daher werden wir in den folgenden Abschnitten eine der Nachschlageformeln (d. h. XVERWEIS) verwenden.
XVERWEIS mit KKLEINSTE
Wenn Sie den zweit- oder drittkleinsten Wert nachschlagen möchten, können Sie die Funktion KKLEINSTE verwenden. In dem folgenden Beispiel verwenden wir die Funktion KKLEINSTE, um den kleinsten Wert zu ermitteln.
=XVERWEIS(KKLEINSTE(C3:C7;1);C3:C7;B3:B7)
Schauen wir uns die Formel an:
Die KKLEINSTE-Funktion
Wir können den niedrigsten Wert zurückgeben, indem wir das zweite Argument der KKLEINSTE-Funktion auf 1 setzen, d.h. den ersten Eintrag, wenn die Liste aufsteigend sortiert ist.
=KKLEINSTE(C3:C7;1)
Hinweis: Stattdessen könnten wir auch 2 für den zweitkleinsten oder 3 für den drittkleinsten Wert verwenden.
Dann wird das Ergebnis der Funktion KKLEINSTE der Nachschlagewert für unsere Nachschlageformel sein, die in diesem Fall die XVERWEIS-Funktion ist:
Die Kombination der beiden Funktionen führt zu unserer ursprünglichen Formel:
=XVERWEIS(KKLEINSTE(C3:C7;1);C3:C7;B3:B7)Den Höchsten Wert nachschlagen
Wir können auch den höchsten Wert finden, indem wir das Gegenteil von MIN und KKLEINSTE verwenden, und zwar die MAX- bzw. die KGRÖSSTE-Funktion.
XVERWEIS mit MAX
Wir können die MAX-Funktion als Suchwert für die XVERWEIS-Funktion verschachteln, um den höchsten Wert zu finden.
=XVERWEIS(MAX(C3:C7);C3:C7;B3:B7)
Schauen wir uns die Formel an:
MAX-Funktion
Wir brauchen die MAX-Funktion, um den höchsten Wert in der Liste zurückzugeben.
Das Ergebnis der MAX-Funktion wird dann an die XVERWEIS-Funktion weitergeleitet, die den Wert extrahiert, der uns interessiert:
Kombiniert man die beiden Funktionen, erhält man unsere ursprüngliche Formel:
=XVERWEIS(MAX(C3:C7);C3:C7;B3:B7)XVERWEIS mit KGRÖSSTE
Die Alternative zur MAX-Funktion ist die KGRÖSSTE-Funktion. Die KGRÖSSTE-Funktion gibt die n-te größte Zahl in einer Liste zurück. In diesem Beispiel geben wir die größte Zahl zurück.
=XVERWEIS(KGRÖSSTE(C3:C7;1);C3:C7;B3:B7)
Wir wollen die Formel aufschlüsseln und veranschaulichen:
Die KGRÖSSTE-Funktion
Indem wir das zweite Argument der KGRÖSSTE-Funktion auf 1 setzen, können wir den größten Wert aus der Liste extrahieren.
Das Ergebnis der KGRÖSSTE-Funktion wird dann als Nachschlagewert für XVERWEIS verwendet:
Die Kombination der beiden Funktionen führt zu unserer ursprünglichen Formel:
=XVERWEIS(KGRÖSSTE(C3:C7;1);C3:C7;B3:B7)Den n-ten niedrigsten/höchsten Wert nachschlagen
XVERWEIS mit KKLEINSTE – N-ter kleinster Wert
Wir können das zweite Argument der Funktion KKLEINSTE auf einen Wert größer als 1 setzen, um den n-ten niedrigsten Wert der Liste zu finden.
=XVERWEIS(KKLEINSTE(C3:C7;2);C3:C7;B3:B7)
XVERWEIS mit KGRÖSSTE – N-ter grösster Wert
Das Gleiche können wir auch für den n-ten größten Wert mit der Funktion KGRÖSSTE tun.
=XVERWEIS(KGRÖSSTE(C3:C7;2);C3:C7;B3:B7)
Min/Max mit Bedingungen nachschlagen
In komplizierten Szenarien, in denen es Kriterien gibt, um den Min-/Max-Wert zu extrahieren, können wir die WENN-Varianten von MIN und MAX verwenden (MINWENNS und MAXWENNS).
XVERWEIS mit MINWENNS
Die Kriterien, die in MINWENNS verwendet werden, müssen auch als UND-Kriterien für den Suchprozess verwendet werden.
=XVERWEIS(MINWENNS(D3:D7;C3:C7;"A")&"A";D3:D7&C3:C7;B3:B7)
Schauen wir uns die Formel an:
MINWENNS-Funktion
Die MINWENNS-Funktion gibt die kleinste Zahl zurück, die den angegebenen Kriterien (z. B. Abteilung) entspricht.
Hinweis: Die MINWENNS-Funktion erfordert mindestens drei Argumente: Min_Bereich (1. Argument), Kriterienbereich1 (2. Argument) und Kriterien1 (3. Argument). Die übrigen sind optionale Argumente, wenn es mehr als einen Satz von Kriterienbereich und Kriterien gibt. Eine wichtige Eigenschaft der MINWENNS-Funktion besteht darin, dass die Bereichsargumente (d. h. Min_Bereich, Kriterienbereich) ein Bereich von Zellen sein müssen und keine Array-Konstanten (z. B. {1;2;3}) oder Array-Ausgaben anderer Funktionen (z. B. SEQUENZ(3)) sein können.
UND-Kriterien nachschlagen
Im Wesentlichen müssen wir den niedrigsten Wert suchen, der unsere vorgegebenen Kriterien erfüllt. Das bedeutet auch, dass unser Nachschlageprozess die Kriterien erfüllen muss. Daher wird unser Szenario zu einer Suche mit mehreren Kriterien (z. B. Gesamteinnahmen und Abteilung), wobei alle Kriterien erfüllt sein müssen (d. h. UND-Kriterien).
Die gängigste Methode zur Anwendung der UND-Kriterien ist die Verkettung der Kriterien mit dem Operator &, der zwei Werte zu einem Text zusammenfügt. Wir müssen auch die Listen verketten, in denen die Nachschlagewerte gesucht werden sollen.
=G3&"A"
Die verketteten Kriterien werden zum neuen Nachschlagewert (z. B. H3) und die verketteten Listen zum neuen Nachschlagefeld (z. B. E3:E7).
=XVERWEIS(H3;E3:E7;B3:B7)
Kombiniert man all dies miteinander, erhält man unsere ursprüngliche Formel:
=XVERWEIS(MINWENNS(D3:D7;C3:C7;"A")&"A";D3:D7&C3:C7;B3:B7)XVERWEIS mit MAXWENNS
Wir wenden dieselben Konzepte aus dem vorigen Abschnitt an (d. h. XVERWEIS mit MINWENNS), um den höchsten Wert zu ermitteln, der die vorgegebenen Kriterien erfüllt.
=XVERWEIS(MAXWENNS(D3:D7;C3:C7;"A")&"A";D3:D7&C3:C7;B3:B7)
Hinweis: Die MAXWENNS-Funktion funktioniert ähnlich wie die MINWENNS-Funktion.
Min/Max-Werte in Google Sheets nachschlagen
Die Formeln funktionieren in Google Sheets auf die gleiche Weise. Der einzige Unterschied ist die Tatsache, dass die XLOOKUP-Funktion in Google Sheets nicht existiert.
