Lernen Fortgeschrittene XLOOKUP-Anwendungen | XLOOKUP-Grundlagen und Fortgeschrittene Techniken

Swipe um das Menü anzuzeigen

Verwendung der ungefähren Übereinstimmung mit XLOOKUP

Die exakte Übereinstimmung funktioniert, wenn Kennungen exakt übereinstimmen müssen. In einigen Geschäftsszenarien ist jedoch das Zuordnen von Werten innerhalb von Bereichen erforderlich.

Beispiel: Provisionssätze basierend auf Umsatzschwellenwerten.

Sales Table

Minimum Sales	Commission Rate
0	2%
5,000	5%
10,000	8%
20,000	12%

Wenn ein Vertriebsmitarbeiter 12.000 Umsatz erzielt, sollte der Provisionssatz 8 % betragen. Dies erfordert eine ungefähre Übereinstimmung.

Syntax mit Übereinstimmungsmodus

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], match_mode)

Optionen für match_mode:

0: genaue Übereinstimmung;
-1: genaue Übereinstimmung oder nächstkleinerer Wert;
1: genaue Übereinstimmung oder nächstgrößerer Wert;
2: Platzhalter-Übereinstimmung.

Für bereichsbasierte Suchen verwenden Sie -1. Das bedeutet, wenn keine genaue Übereinstimmung gefunden wird, wird der nächstkleinere Wert zurückgegeben.

Verwendung der ungefähren Übereinstimmung

Angenommen, Sie haben eine Notentabelle. Column A enthält die erforderliche Mindestpunktzahl. Column B enthält die entsprechende Buchstabennote. Die Punktzahl eines Schülers wird in Zelle D2 eingegeben. Das Ziel ist es, die korrekte Buchstabennote basierend auf der Punktzahl zurückzugeben. Die Spalte Mindestpunktzahl muss aufsteigend sortiert sein.

=XLOOKUP(D2; A3:A8; B3:B8; "Not Found"; -1)

D2: enthält die numerische Punktzahl des Schülers;
A3:A8: enthält die Mindestpunktzahl-Schwellenwerte;
B3:B8: enthält die Buchstabennoten;
"Not Found": definiert, was angezeigt wird, wenn keine Übereinstimmung existiert;
-1: weist Excel an, den nächstkleineren Wert zurückzugeben, wenn keine genaue Übereinstimmung gefunden wird.

Funktionsweise

Wenn D2 gleich 73 ist, durchsucht Excel die Spalte mit der Mindestpunktzahl;
Es findet nicht exakt 73;
Es findet den nächstkleineren Wert, nämlich 70;
Es gibt die entsprechende Note B zurück.

Dies ist eine ungefähre Übereinstimmung basierend auf Bereichen.

Mehrere Spalten zurückgeben

Angenommen, es gibt eine Mitarbeitertabelle: Employee ID | Salary | Bonus. Es sollen sowohl Salary als auch Bonus mit einer einzigen Abfrage zurückgegeben werden.

=XLOOKUP(A3;E3:E7;F3:G7)

A3: enthält die Employee ID;
E3:E7: enthält die Employee IDs in der Nachschlagetabelle;
F3:G7: enthält die beiden Spalten Salary und Bonus.

Funktionsweise

Excel findet die übereinstimmende Employee ID in Spalte E;
Es gibt sowohl Salary als auch Bonus aus den Spalten F und G zurück;
Die Ergebnisse werden automatisch auf zwei benachbarte Zellen verteilt;

Eine Formel gibt mehrere zusammenhängende Felder zurück.

Suche von unten

Angenommen, die Mitarbeitertabelle enthält im Laufe der Zeit doppelte Employee IDs. Der aktuellste Eintrag wird immer am Ende hinzugefügt. Standardmäßig sucht XLOOKUP von oben nach unten. Um den neuesten Eintrag zu erhalten, muss von unten nach oben gesucht werden.

=XLOOKUP(A2; E3:E7; F3:F7; "Not Found"; 0; -1)

A2: zu suchende Employee ID;
E3:E7: Suchspalte;
F3:F7: Rückgabespalte;
0: erzwingt genaue Übereinstimmung;
-1: sucht von unten nach oben.

Funktionsweise

Excel beginnt mit der Suche am unteren Ende der Spalte E;
Es findet die erste übereinstimmende ID von unten;
Es gibt den Wert aus Spalte F in dieser Zeile zurück.

Dies stellt sicher, dass bei Duplikaten der neueste Datensatz zurückgegeben wird.

Szenario

Die Arbeitsmappe enthält drei separate Tabellenblätter.

Das Ziel ist es, fortgeschrittene XLOOKUP-Funktionen anzuwenden, um die spezifischen Ergebnisse zu erhalten.

War alles klar?

Danke für Ihr Feedback!

Abschnitt 2. Kapitel 2

Fragen Sie AI

Fragen Sie alles oder probieren Sie eine der vorgeschlagenen Fragen, um unser Gespräch zu beginnen

Abschnitt 2. Kapitel 2