SVERWEIS ist eine der am häufigsten verwendeten Funktionen in Excel. Es sucht nach einem Wert in einem Bereich und gibt einen entsprechenden Wert in einer angegebenen Spaltennummer zurück.
Jetzt stieß ich auf ein Problem, bei dem ich die gesamte Zeile nachschlagen und die Werte in allen Spalten dieser Zeile zurückgeben musste (anstatt einen einzelnen Wert zurückzugeben).
Also hier war, was ich tun musste. Im folgenden Datensatz hatte ich die Namen der Vertriebsmitarbeiter und die Verkäufe, die sie in vier Quartalen im Jahr 2012 erzielten. Ich hatte eine Dropdown-Liste mit ihren Namen und wollte den maximalen Umsatz für diesen Vertriebsmitarbeiter in diesen vier Quartalen extrahieren.
Ich könnte mir zwei verschiedene Möglichkeiten einfallen lassen - mit INDEX oder SVERWEIS.
Ganze Zeile/Spalte mit INDEX-Formel suchen
Hier ist die Formel, die ich dafür mit Index erstellt habe
=GROSS(INDEX($B$4:$F$13,MATCH(H3,$B$4:$B$13,0),0),1)
Wie es funktioniert:
Betrachten Sie zunächst die INDEX-Funktion, die in die LARGE-Funktion eingeschlossen ist.
=INDEX($C$4:$F$13,MATCH(H3,$B$4:$B$13,0),0)
Lassen Sie uns die Argumente der INDEX-Funktion genau analysieren:
- Reihe - $B$4:$F$1
- Zeilennummer - MATCH(H3,$B$4:$B$13,0)
- Spaltennummer - 0
Beachten Sie, dass ich die Spaltennummer als 0 verwendet habe.
Der Trick dabei ist, dass, wenn Sie die Spaltennummer als 0 verwenden, alle Werte in allen Spalten zurückgegeben werden. Wenn ich also John in der Dropdown-Liste auswähle, würde die Indexformel alle 4 Verkaufswerte für John {91064,71690.67574,25427} zurückgeben.
Jetzt kann ich die Large-Funktion verwenden, um den größten Wert zu extrahieren
Profi-Tipp - Verwenden Sie die Spalten-/Zeilennummer als 0 in der Indexformel, um alle Werte in Spalten/Zeilen zurückzugeben.
Ganze Zeile / Spalte mit der SVERWEIS-Formel suchen
Während die Index-Formel ordentlich, sauber und robust ist, ist der SVERWEIS-Weg etwas komplex. Es endet auch damit, dass die Funktion flüchtig ist. Es gibt jedoch einen erstaunlichen Trick, den ich in diesem Abschnitt teilen möchte. Hier ist die Formel:
=GROSS(SVERWEIS(H3,B4:F13, ROW(INDIRECT("2:"&COUNTA($B$4:$F$4))), FALSE),1)
Wie es funktioniert
- ROW(INDIRECT(“2:”&COUNTA($B$4:$F$4))) – Diese Formel gibt ein Array {2;3;4;5} zurück. Beachten Sie, dass diese Formel volatil wird, da sie INDIRECT verwendet.
- SVERWEIS(H3,B4:F13,ROW(INDIRECT(“2:”&COUNTA($B$4:$F$4))),FALSE) - Hier ist der beste Teil. Wenn Sie diese zusammenfügen, wird es SVERWEIS(H3,B4:F13,{2;3;4;5},FALSE). Beachten Sie nun, dass ich ihm anstelle einer einzelnen Spaltennummer ein Array von Spaltennummern gegeben habe. Und SVERWEIS sucht gehorsam nach Werten in all diesen Spalten und gibt ein Array zurück.
- Verwenden Sie jetzt einfach die Funktion LARGE, um den größten Wert zu extrahieren.
Denken Sie daran, Strg + Umschalt + Eingabetaste zu verwenden, um diese Formel zu verwenden.
Profi-Tipp - Wenn Sie in SVERWEIS eine einzelne Spaltennummer verwenden, wird ein Array von Nachschlagewerten zurückgegeben, wenn Sie ein Array von Spaltennummern verwenden.
