VLOOKUP in Excel, Teil 2 VLOOKUP ohne Datenbank verwenden
In einem kürzlich erschienenen Artikel haben wir die aufgerufene Excel-Funktion eingeführt VLOOKUP und erläuterte, auf welche Weise Informationen aus einer Datenbank in eine Zelle in einem lokalen Arbeitsblatt abgerufen werden könnten. In diesem Artikel haben wir erwähnt, dass es zwei Verwendungszwecke für VLOOKUP gibt und nur eine davon befasste sich mit der Abfrage von Datenbanken. In diesem Artikel, dem zweiten und letzten in der VLOOKUP-Serie, untersuchen wir diese andere, weniger bekannte Verwendung für die VLOOKUP-Funktion.
Wenn Sie dies noch nicht getan haben, lesen Sie bitte den ersten VLOOKUP-Artikel. Dieser Artikel geht davon aus, dass viele der in diesem Artikel erläuterten Konzepte dem Leser bereits bekannt sind.
Bei der Arbeit mit Datenbanken erhält VLOOKUP eine "eindeutige Kennung", mit der identifiziert wird, welcher Datensatz in der Datenbank gesucht werden soll (z. B. ein Produktcode oder eine Kundennummer). Diese eindeutige Kennung Muss in der Datenbank vorhanden, andernfalls gibt VLOOKUP einen Fehler zurück. In diesem Artikel wird eine Möglichkeit zur Verwendung von VLOOKUP untersucht, bei der der Bezeichner überhaupt nicht in der Datenbank vorhanden sein muss. Es ist fast so, als könnte VLOOKUP einen Ansatz „Nahe genug ist gut genug“ anwenden, um die gesuchten Daten zurückzugeben. Unter bestimmten Umständen ist das so genau was wir brauchen.
Wir werden diesen Artikel anhand eines realen Beispiels veranschaulichen - der Berechnung der Provisionen, die für eine Reihe von Verkaufszahlen generiert werden. Wir beginnen mit einem sehr einfachen Szenario und machen es dann schrittweise komplexer, bis die einzige vernünftige Lösung für das Problem die Verwendung von VLOOKUP ist. Das Anfangsszenario in unserem fiktiven Unternehmen funktioniert folgendermaßen: Wenn ein Verkäufer in einem bestimmten Jahr Verkäufe von mehr als 30.000 US-Dollar erzielt, beträgt die Provision, die sie für diese Verkäufe erhalten, 30%. Ansonsten beträgt ihre Provision nur 20%. Bisher ist dies ein ziemlich einfaches Arbeitsblatt:
Um dieses Arbeitsblatt zu verwenden, gibt der Verkäufer seine Verkaufszahlen in Zelle B1 ein, und die Formel in Zelle B2 berechnet den korrekten Provisionssatz, den sie erhalten dürfen. Dieser Wert wird in Zelle B3 verwendet, um die Gesamtprovision zu berechnen, die dem Verkäufer geschuldet wird (welche) ist eine einfache Multiplikation von B1 und B2).
Die Zelle B2 enthält den einzig interessanten Teil dieses Arbeitsblatts - die Formel zur Entscheidung, welcher Provisionssatz verwendet werden soll: derjenige unten die Schwelle von 30.000 Dollar oder die eine über der Grenzbereich. Diese Formel verwendet die aufgerufene Excel-Funktion OB. Für diejenigen Leser, die mit IF nicht vertraut sind, funktioniert es folgendermaßen:
OB(Bedingung, Wert bei Wahr, Wert bei Falsch)
Bei dem die Bedingung ist ein Ausdruck, der entweder ausgewertet wird wahr oder falsch. Im obigen Beispiel wird der Bedingung ist der Ausdruck B1
Wie Sie sehen, erhalten wir bei einem Gesamtumsatz von 20.000 US-Dollar einen Provisionssatz von 20% in Zelle B2. Wenn wir einen Wert von 40.000 $ eingeben, erhalten wir einen anderen Provisionssatz:
Unsere Kalkulationstabelle funktioniert also.
Machen wir es komplexer. Lassen Sie uns eine zweite Schwelle einführen: Wenn der Verkäufer mehr als 40.000 US-Dollar verdient, steigt der Provisionssatz auf 40%:
In der realen Welt leicht zu verstehen, aber in Zelle B2 wird unsere Formel komplexer. Wenn Sie sich die Formel genau ansehen, sehen Sie den dritten Parameter der ursprünglichen IF-Funktion (die Wert wenn falsch) ist jetzt eine komplette IF-Funktion. Dies nennt man a verschachtelte Funktion (eine Funktion innerhalb einer Funktion). Es ist perfekt in Excel (es funktioniert sogar!), Aber es ist schwerer zu lesen und zu verstehen.
Wir werden uns nicht mit den Grundsätzen, wie und warum dies funktioniert, befassen, noch werden wir die Nuancen verschachtelter Funktionen untersuchen. Dies ist ein Tutorial zu VLOOKUP, nicht zu Excel im Allgemeinen.
Jedenfalls wird es schlimmer! Was ist, wenn wir entscheiden, dass, wenn sie mehr als 50.000 US-Dollar verdienen, sie eine Provision von 50% haben und wenn sie mehr als 60.000 US-Dollar verdienen, haben Sie eine Provision von 60%??
Nun ist die Formel in Zelle B2, obwohl sie korrekt ist, praktisch unleserlich geworden. Niemand sollte Formeln schreiben müssen, bei denen die Funktionen vier Ebenen tief verschachtelt sind! Sicher muss es einen einfacheren Weg geben?
Das ist sicher so. VLOOKUP zur Rettung!
Lassen Sie uns das Arbeitsblatt ein wenig umgestalten. Wir behalten alle die gleichen Zahlen, organisieren sie aber auf eine neue Art und Weise tabellarisch Weg:
Nehmen Sie sich einen Moment Zeit und überzeugen Sie sich selbst davon, dass das Neue Rate Tabelle funktioniert genauso wie die Reihe von Schwellenwerten.
Konzeptionell wollen wir mit VLOOKUP die Umsatzsumme des Verkaufspersonals (von B1) in der Tariftabelle nachschlagen und uns den entsprechenden Provisionssatz zurücksenden. Beachten Sie, dass der Verkäufer tatsächlich Verkäufe erstellt hat, die sind nicht einer der fünf Werte in der Tariftabelle (0 USD, 30.000 USD, 40.000 USD, 50.000 USD oder 60.000 USD). Sie haben möglicherweise einen Umsatz von 34.988 US-Dollar erzielt. Es ist wichtig zu wissen, dass $ 34.988 dies tun nicht erscheinen in der Tariftabelle. Mal sehen, ob VLOOKUP unser Problem trotzdem lösen kann…
Wir wählen Zelle B2 (den Ort, an dem wir unsere Formel einfügen möchten) und fügen dann die VLOOKUP-Funktion aus der ein Formeln Tab:
Das Funktionsargumente Das Feld für VLOOKUP wird angezeigt. Die Argumente (Parameter) füllen wir nacheinander aus, beginnend mit dem Lookup-Wert, Dies ist in diesem Fall die Verkaufssumme aus Zelle B1. Wir setzen den Cursor in die Lookup-Wert Feld und klicken Sie einmal auf Zelle B1:
Als Nächstes müssen wir VLOOKUP angeben, in welcher Tabelle diese Daten nachgeschlagen werden sollen. In diesem Beispiel handelt es sich natürlich um die Gebührentabelle. Wir setzen den Cursor in die Table_array und markieren Sie dann die gesamte Tariftabelle - ohne die Überschriften:
Als Nächstes müssen wir angeben, welche Spalte in der Tabelle die Informationen enthält, die unsere Formel an uns zurückgeben soll. In diesem Fall möchten wir den Provisionssatz, der in der zweiten Spalte der Tabelle zu finden ist, und geben daher a ein 2 in die Col_index_num Feld:
Zum Schluss geben wir einen Wert in die ein Range_lookup Feld.
Wichtig: Die Verwendung dieses Feldes unterscheidet die zwei Arten der Verwendung von VLOOKUP. Dieser letzte Parameter dient zur Verwendung von VLOOKUP mit einer Datenbank, Range_lookup, muss immer auf gesetzt sein FALSCH, Bei dieser anderen Verwendung von VLOOKUP müssen wir es jedoch leer lassen oder einen Wert von eingeben WAHR. Wenn Sie VLOOKUP verwenden, ist es wichtig, dass Sie die richtige Wahl für diesen letzten Parameter treffen.
Um ausdrücklich zu sein, geben wir einen Wert von ein wahr in dem Range_lookup Feld. Es wäre auch in Ordnung, das Feld leer zu lassen, da dies der Standardwert ist:
Wir haben alle Parameter abgeschlossen. Wir klicken jetzt auf OK und Excel erstellt unsere VLOOKUP-Formel für uns:
Wenn wir mit ein paar unterschiedlichen Umsatzsummen experimentieren, können wir uns davon überzeugen, dass die Formel funktioniert.
Fazit
In der "Datenbank" -Version von VLOOKUP, wo die Range_lookup Parameter ist FALSCH, der im ersten Parameter übergebene Wert (Lookup-Wert) Muss in der Datenbank vorhanden sein. Mit anderen Worten, wir suchen nach einer exakten Übereinstimmung.
Bei dieser anderen Verwendung von VLOOKUP suchen wir jedoch nicht unbedingt nach einer exakten Übereinstimmung. In diesem Fall ist „nahe genug gut genug“. Aber was meinen wir mit „nahe genug“? Ein Beispiel: Bei der Suche nach einem Provisionssatz für eine Gesamtsumme von 34.988 US-Dollar liefert unsere VLOOKUP-Formel einen Wert von 30%. Dies ist die richtige Antwort. Warum wurde die Zeile mit 30% ausgewählt? Was bedeutet eigentlich "nahe genug" in diesem Fall? Seien wir genau:
Wann Range_lookup ist eingestellt auf WAHR (oder weggelassen), sucht VLOOKUP in Spalte 1 und stimmt überein der höchste Wert, der nicht größer als ist das Lookup-Wert Parameter.
Es ist auch wichtig zu beachten, dass dieses System funktioniert, Die Tabelle muss in Spalte 1 aufsteigend sortiert sein!
Wenn Sie mit VLOOKUP üben möchten, können Sie die in diesem Artikel dargestellte Beispieldatei hier herunterladen.
