SVERWEIS in Excel, Teil 2: Verwenden von SVERWEIS ohne eine Datenbank

In einem kürzlich erschienenen Artikel haben wir die Excel-Funktion mit dem Namen SVOOKUP eingeführt und erläutert, wie sie zum Abrufen von Informationen aus einer Datenbank in eine Zelle in einem lokalen Arbeitsblatt verwendet werden kann. In diesem Artikel erwähnten wir, dass es zwei Verwendungsmöglichkeiten für SVERWEIS gab und nur einer von ihnen sich mit der Abfrage von Datenbanken befasste. In diesem Artikel, der zweiten und letzten in der SVERWEIS-Reihe, untersuchen wir diese andere, weniger bekannte Verwendung für die SVERWEIS-Funktion.

Wenn Sie dies noch nicht getan haben, lesen Sie bitte den ersten SVERWEIS Artikel - dieser Artikel wird davon ausgehen, dass viele der in diesem Artikel erläuterten Konzepte dem Leser bereits bekannt sind.

Beim Arbeiten mit Datenbanken wird dem SVERWEIS eine "eindeutige Kennung" übergeben, die dazu dient, den Datensatz zu identifizieren, den wir in der Datenbank finden möchten( z. B. einen Produktcode oder eine Kunden-ID).Dieser eindeutige Bezeichner

muss in der Datenbank vorhanden sein, andernfalls gibt SVERWEIS einen Fehler zurück. In diesem Artikel werden wir eine Möglichkeit zur Verwendung von SVERWEIS untersuchen, bei der der Bezeichner überhaupt nicht in der Datenbank vorhanden sein muss. Es ist fast so, als ob SVERWEIS einen Ansatz "nahe genug ist gut genug" verwenden kann, um die gesuchten Daten zurückzugeben. Unter bestimmten Umständen ist das genau was wir brauchen.

Wir werden diesen Artikel mit einem realen Beispiel illustrieren - mit der Berechnung der Provisionen, die auf einer Reihe von Verkaufszahlen generiert werden. Wir werden mit einem sehr einfachen Szenario beginnen und es dann schrittweise komplexer machen, bis die einzige rationale Lösung des Problems die Verwendung von SVERWEIS ist. Das Ausgangsszenario in unserem fiktiven Unternehmen funktioniert folgendermaßen: Wenn ein Verkäufer in einem bestimmten Jahr einen Umsatz von mehr als 30.000 $ erzielt, beträgt die Provision, die er für diese Verkäufe erzielt, 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 die korrekte Provisionssumme, die sie erhalten möchten, die in Zelle B3 verwendet wirddie Gesamtprovision, die dem Verkäufer geschuldet wird( was eine einfache Multiplikation von B1 und B2 ist).

Die Zelle B2 enthält den einzigen interessanten Teil dieses Arbeitsblatts - die Formel zur Entscheidung, welche Provisionssätze zu verwenden sind: der eine unter der Schwellenwert von $ 30.000 oder der eine über der Schwellenwert. Diese Formel verwendet die Excel-Funktion namens IF .Für diejenigen Leser, die mit IF nicht vertraut sind, funktioniert es wie folgt:

IF( -Bedingung, Wert falls wahr, Wert wenn falsch )

Wo die -Bedingung ein Ausdruck ist, der entweder wahr oder falsch auswertet. Im obigen Beispiel ist die -Bedingung der Ausdruck B1 & lt; B5 , der wie folgt gelesen werden kann: "Ist B1 kleiner als B5?", Oder, anders ausgedrückt, "Sind die Gesamtverkäufe kleiner als der Schwellenwert".Wenn die Antwort auf diese Frage "Ja"( wahr) ist, dann verwenden wir den -Wert, wenn der -Parameter der Funktion B6 in diesem Fall - der Provisionssatz, wenn die Verkaufssumme unter der Schwellenwert war. Wenn die Antwort auf die Frage "Nein"( falsch) ist, dann verwenden wir den -Wert, wenn in diesem Fall der -Parameter der Funktion B7 falsch ist - der Provisionssatz, wenn die Umsatzsumme über lag.

Wie Sie sehen können, erhalten wir bei Verwendung eines Umsatzes von 20.000 US-Dollar eine Provisionssatz von 20% in Zelle B2.Wenn wir einen Wert von 40.000 $ eingeben, erhalten wir eine andere Provisionssatz:

So funktioniert unsere Tabelle.

Machen wir es komplexer. Lassen Sie uns einen zweiten Schwellenwert einführen: Wenn der Verkäufer mehr als $ 40.000 verdient, erhöht sich die Provisionsrate auf 40%:

Einfach genug, um in der realen Welt zu verstehen, aber in Zelle B2 wird unsere Formel immer komplexer. Wenn Sie sich die Formel genau ansehen, sehen Sie, dass der dritte Parameter der ursprünglichen IF-Funktion( der -Wert bei falscher ) nun eine eigene IF-Funktion ist. Dies wird als verschachtelte -Funktion ( eine Funktion innerhalb einer Funktion) bezeichnet. Es ist perfekt in Excel( es funktioniert sogar!), Aber es ist schwerer zu lesen und zu verstehen.

Wir gehen nicht auf das Wesentliche ein, wie und warum das funktioniert, noch werden wir die Nuancen verschachtelter Funktionen untersuchen. Dies ist ein Tutorial zu SVERWEIS, nicht zu Excel im Allgemeinen.

Wie auch immer, es wird schlimmer! Was ist, wenn wir entscheiden, dass wenn sie mehr als $ 50.000 verdienen, sie Anspruch auf 50% Provision haben, und wenn sie mehr als $ 60.000 verdienen, dann haben sie Anspruch auf 60% Provision?

Nun ist die Formel in Zelle B2 zwar korrekt, aber praktisch unlesbar geworden. Niemand sollte Formeln schreiben müssen, in denen die Funktionen vier Ebenen tief verschachtelt sind! Sicherlich muss es einen einfacheren Weg geben?

Es gibt sicherlich. SVERWEIS zur Rettung!

Lassen Sie uns das Arbeitsblatt ein wenig neu gestalten. Wir behalten alle die gleichen Zahlen, aber organisieren es auf eine neue Art und Weise, mehr tabellarisch Weg:

Nehmen Sie sich Zeit und überprüfen Sie selbst, dass die neue -Tariftabelle genauso funktioniert wie die obige Reihe von Schwellenwerten.

Was wir vorhaben, ist konzeptionell, mithilfe von VLOOKUP die Umsatzsumme des Verkäufers( aus B1) in der Tariftabelle nachzuschlagen und uns die entsprechende Provisionssumme zurückzugeben. Beachten Sie, dass der Verkäufer möglicherweise Verkäufe erstellt hat, die nicht einer der fünf Werte in der Tariftabelle( 0, 30.000, 40.000, 50.000 oder 60.000) sind. Sie haben möglicherweise einen Umsatz von 34.988 $ erzielt. Es ist wichtig zu beachten, dass $ 34.988 nicht in der Tariftabelle erscheint. Lassen Sie uns sehen, ob SVERWEIS unser Problem trotzdem lösen kann. ..

Wir wählen Zelle B2( den Ort, an den wir unsere Formel setzen möchten) und fügen dann die Funktion SVERWEIS von der Registerkarte Formeln ein:

Das Feld Funktionsargumente für SVERWEIS erscheint. Wir füllen die Argumente( Parameter) nacheinander aus und beginnen mit dem Lookup_value , was in diesem Fall die Verkaufssumme aus der Zelle B1 ist. Wir setzen den Cursor in das Feld Lookup_value und klicken dann einmal auf Zelle B1:

Als nächstes müssen wir angeben, nach welcher Tabelle diese Daten gesucht werden sollen. In diesem Beispiel ist es natürlich die Tariftabelle. Wir platzieren den Cursor in das Feld Table_array und markieren 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 die Provisionssatz, die in der zweiten Spalte in der Tabelle gefunden wird, also geben wir ein 2 in das Col_index_num Feld:

Schließlich geben wir einen Wert in das Range_lookup Feld.

Wichtig: Es ist die Verwendung dieses Felds, die die zwei Arten der Verwendung von SVERWEIS unterscheidet. Um SVERWEIS mit einer Datenbank zu verwenden, muss dieser letzte Parameter Range_lookup immer auf FALSE festgelegt werden, aber mit dieser anderen Verwendung von SVERWEIS müssen wir ihn entweder leer lassen oder einen Wert von TRUE eingeben. Bei der Verwendung von SVERWEIS ist es wichtig, dass Sie die richtige Wahl für diesen letzten Parameter treffen.

Um explizit zu sein, geben wir einen Wert von true in das Feld Range_lookup ein. Es wäre auch gut, es leer zu lassen, da dies der Standardwert ist:

Wir haben alle Parameter abgeschlossen. Wir klicken jetzt auf die Schaltfläche OK , und Excel erstellt unsere SVERWEIS-Formel für uns:

Wenn wir mit ein paar verschiedenen Verkaufsgesamtbeträgen experimentieren, können wir uns davon überzeugen, dass die Formel funktioniert.

Zusammenfassung

In der "Datenbank" -Version von SVERWEIS, in der der -Parameter Range_lookup FALSE ist, muss der im ersten Parameter( Lookup_value ) übergebene Wert in der Datenbank vorhanden sein. Mit anderen Worten, wir suchen nach einer genauen Übereinstimmung.

Aber in dieser anderen Verwendung von SVERWEIS suchen wir nicht unbedingt nach einer genauen Übereinstimmung. In diesem Fall ist "nahe genug ist gut genug".Aber was verstehen wir unter "nahe genug"?Nehmen wir ein Beispiel: Bei der Suche nach einem Provisionssatz für einen Gesamtumsatz von 34.988 US-Dollar gibt unsere SVERWEIS-Formel einen Wert von 30% zurück, was die richtige Antwort ist. Warum hat er die Zeile in der Tabelle mit 30% gewählt? Was bedeutet "nahe genug" in diesem Fall? Lassen Sie uns genau sein:

Wenn Range_lookup auf TRUE ( oder nicht angegeben) gesetzt ist, sucht SVERWEIS in Spalte 1 und entspricht dem höchsten Wert, der nicht größer als der Lookup_value Parameter ist.

Es ist auch wichtig zu beachten, dass die Tabelle in aufsteigender Reihenfolge in Spalte 1 sortieren muss, damit dieses System funktioniert.

Wenn Sie mit SVERWEIS üben möchten, können Sie die in diesem Artikel dargestellte Beispieldatei von hier herunterladen.