Inhalte
Wir haben kürzlich einen Artikel einer der nützlichsten Excel-Funktionen gewidmet, die aufgerufen werden VPR und zeigte, wie es verwendet werden kann, um die erforderlichen Informationen aus einer Datenbank in eine Arbeitsblattzelle zu extrahieren. Wir haben auch erwähnt, dass es zwei Anwendungsfälle für die Funktion gibt VPR und nur einer von ihnen befasst sich mit Datenbankabfragen. In diesem Artikel lernen Sie eine andere, weniger bekannte Möglichkeit kennen, die Funktion zu verwenden VPR in Excel.
Wenn Sie dies noch nicht getan haben, lesen Sie unbedingt den letzten Artikel über die Funktion VPR, da alle folgenden Informationen davon ausgehen, dass Sie bereits mit den im ersten Artikel beschriebenen Prinzipien vertraut sind.
Bei der Arbeit mit Datenbanken funktionieren Funktionen VPR Es wird ein eindeutiger Identifikator übergeben, der verwendet wird, um die Informationen zu identifizieren, die wir finden möchten (z. B. ein Produktcode oder eine Kundenidentifikationsnummer). Andernfalls muss dieser eindeutige Code in der Datenbank vorhanden sein VPR wird einen Fehler melden. In diesem Artikel werden wir uns diese Art der Verwendung der Funktion ansehen VPRwenn die ID überhaupt nicht in der Datenbank existiert. Als ob die Funktion VPR in den Näherungsmodus geschaltet und wählt aus, welche Daten uns zur Verfügung gestellt werden, wenn wir etwas finden möchten. Unter bestimmten Umständen ist dies genau das, was benötigt wird.
Ein Beispiel aus dem Leben. Wir stellen die Aufgabe
Lassen Sie uns diesen Artikel mit einem Beispiel aus der Praxis veranschaulichen – der Berechnung von Provisionen basierend auf einer Vielzahl von Verkaufsmetriken. Wir beginnen mit einer sehr einfachen Option und komplizieren sie dann schrittweise, bis die einzige vernünftige Lösung des Problems darin besteht, die Funktion zu verwenden VPR. Das Ausgangsszenario für unsere fiktive Aufgabe sieht folgendermaßen aus: Wenn ein Verkäufer in einem Jahr mehr als 30000 $ Umsatz macht, dann beträgt seine Provision 30 %. Ansonsten beträgt die Provision nur 20%. Lassen Sie es uns in Form einer Tabelle ausdrücken:
Der Verkäufer gibt seine Verkaufsdaten in Zelle B1 ein und die Formel in Zelle B2 bestimmt den korrekten Provisionssatz, den der Verkäufer erwarten kann. Der resultierende Kurs wird wiederum in Zelle B3 verwendet, um die Gesamtprovision zu berechnen, die der Verkäufer erhalten sollte (einfaches Multiplizieren der Zellen B1 und B2).
Der interessanteste Teil der Tabelle ist in Zelle B2 enthalten – dies ist die Formel zur Bestimmung des Provisionssatzes. Diese Formel enthält eine Excel-Funktion namens IF (WENN). Für diejenigen Leser, die mit dieser Funktion nicht vertraut sind, werde ich erklären, wie sie funktioniert:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Anforderungen ist ein Funktionsargument, das den Wert von beiden annimmt ECHTER CODE (Wahr oder FALSCH (FALSCH). Im obigen Beispiel ist der Ausdruck B1
Stimmt es, dass B1 kleiner ist als B5?
Oder man kann es anders sagen:
Stimmt es, dass der Gesamtumsatz des Jahres unter dem Schwellenwert liegt?
Wenn wir diese Frage beantworten JA (TRUE), dann kehrt die Funktion zurück Wert, wenn wahr (Wert wenn TRUE). In unserem Fall ist dies der Wert der Zelle B6, dh der Provisionssatz, wenn der Gesamtumsatz unter dem Schwellenwert liegt. Wenn wir die Frage beantworten NEIN (FALSE) kehrt dann zurück Wert, wenn falsch (Wert wenn FALSE). In unserem Fall ist dies der Wert der Zelle B7, dh der Provisionssatz, wenn der Gesamtumsatz über dem Schwellenwert liegt.
Wie Sie sehen können, erhalten wir, wenn wir den Gesamtumsatz von 20000 $ nehmen, einen Provisionssatz von 2 % in Zelle B20. Wenn wir einen Wert von 40000 $ eingeben, ändert sich der Provisionssatz um 30 %:
So funktioniert unser Tisch.
Wir erschweren die Aufgabe
Machen wir es etwas schwieriger. Lassen Sie uns einen weiteren Schwellenwert festlegen: Wenn der Verkäufer mehr als 40000 $ verdient, steigt die Provisionsrate auf 40 %:
Alles scheint einfach und klar zu sein, aber unsere Formel in Zelle B2 wird merklich komplizierter. Wenn Sie sich die Formel genau ansehen, sehen Sie, dass das dritte Argument der Funktion ist IF (IF) wurde zu einer weiteren vollwertigen Funktion IF (WENN). Diese Konstruktion nennt man Verschachtelung von Funktionen ineinander. Excel erlaubt diese Konstrukte gerne, und sie funktionieren sogar, aber sie sind viel schwieriger zu lesen und zu verstehen.
Wir werden uns nicht mit den technischen Details befassen – warum und wie es funktioniert, und wir werden nicht auf die Nuancen des Schreibens verschachtelter Funktionen eingehen. Schließlich ist dies ein Artikel, der der Funktion gewidmet ist VPR, keine vollständige Anleitung zu Excel.
Wie dem auch sei, die Formel wird komplizierter! Was wäre, wenn wir eine weitere Option für einen Provisionssatz von 50 % für Verkäufer einführen, die mehr als 50000 $ Umsatz machen? Und wenn jemand mehr als 60000 $ verkauft hat, zahlt er dann 60 % Provision?
Jetzt ist die Formel in Zelle B2, auch wenn sie fehlerfrei geschrieben wurde, völlig unlesbar geworden. Ich denke, dass es nur wenige gibt, die in ihren Projekten Formeln mit 4 Verschachtelungsebenen verwenden möchten. Das muss doch einfacher gehen?!
Und es gibt einen solchen Weg! Die Funktion wird uns helfen VPR.
Wir wenden die SVERWEIS-Funktion an, um das Problem zu lösen
Lassen Sie uns das Design unserer Tabelle ein wenig ändern. Wir behalten dieselben Felder und Daten bei, ordnen sie aber auf eine neue, kompaktere Weise an:
Nehmen Sie sich einen Moment Zeit und stellen Sie sicher, dass die neue Tabelle Tariftabelle enthält die gleichen Daten wie die vorherige Schwellenwerttabelle.
Die Hauptidee ist, die Funktion zu verwenden VPR um den gewünschten Tarifsatz gemäß der Tabelle zu ermitteln Tariftabelle je nach Verkaufsvolumen. Bitte beachten Sie, dass der Verkäufer Waren für einen Betrag verkaufen kann, der nicht einem der fünf Schwellenwerte in der Tabelle entspricht. Zum Beispiel könnte er für 34988 $ verkaufen, aber es gibt keinen solchen Betrag. Mal sehen, wie die Funktion VPR kann mit einer solchen Situation umgehen.
Einfügen einer VLOOKUP-Funktion
Wählen Sie Zelle B2 (wo wir unsere Formel einfügen möchten) und suchen Sie SVERWEIS (SVERWEIS) in der Excel-Funktionsbibliothek: Formeln (Formeln) > Funktionsbibliothek (Funktionsbibliothek) > Lookup & Referenz (Referenzen und Arrays).
Ein Dialogfeld wird angezeigt Funktionsargumente (Funktionsargumente). Wir füllen die Werte der Argumente einzeln aus, beginnend mit Lookup-Wert (Lookup-Wert). In diesem Beispiel ist dies der Gesamtumsatz aus Zelle B1. Setzen Sie den Cursor in das Feld Lookup-Wert (Lookup_value) und wählen Sie Zelle B1 aus.
Als nächstes müssen Sie die Funktionen angeben VPRwo nach Daten zu suchen ist. In unserem Beispiel ist dies eine Tabelle Tariftabelle. Setzen Sie den Cursor in das Feld Table_array (Tabelle) und wählen Sie die gesamte Tabelle aus Tariftabelleaußer Kopfzeilen.
Als Nächstes müssen wir mit unserer Formel angeben, aus welcher Spalte Daten extrahiert werden sollen. Uns interessiert der Provisionssatz, der in der zweiten Spalte der Tabelle steht. Daher zur Argumentation Spalte_index_num (Spaltennummer) geben Sie den Wert 2 ein.
Und schließlich führen wir das letzte Argument ein – Range_lookup (Intervall_Suche).
Wichtig: es ist die Verwendung dieses Arguments, die den Unterschied zwischen den beiden Arten der Anwendung der Funktion ausmacht VPR. Beim Arbeiten mit Datenbanken ist das Argument Range_lookup (range_lookup) muss immer einen Wert haben FALSCH (FALSCH), um nach einer exakten Übereinstimmung zu suchen. In unserer Verwendung der Funktion VPR, müssen wir dieses Feld leer lassen oder einen Wert eingeben ECHTER CODE (STIMMT). Es ist äußerst wichtig, diese Option richtig auszuwählen.
Um es klarer zu machen, werden wir vorstellen ECHTER CODE (TRUE) im Feld Range_lookup (Intervall_Suche). Wenn Sie das Feld jedoch leer lassen, ist dies kein Fehler, da ECHTER CODE ist sein Standardwert:
Wir haben alle Parameter ausgefüllt. Jetzt drücken wir OK, und Excel erstellt für uns eine Formel mit einer Funktion VPR.
Wenn wir mit mehreren verschiedenen Werten für den Gesamtumsatz experimentieren, stellen wir sicher, dass die Formel korrekt funktioniert.
Zusammenfassung
Wenn die Funktion VPR arbeitet mit Datenbanken, Argument Range_lookup (range_lookup) muss akzeptieren FALSCH (FALSCH). Und der eingegebene Wert als Lookup-Wert (Lookup_value) muss in der Datenbank vorhanden sein. Mit anderen Worten, es wird nach einer genauen Übereinstimmung gesucht.
In dem Beispiel, das wir uns in diesem Artikel angesehen haben, ist es nicht erforderlich, eine exakte Übereinstimmung zu erhalten. Dies ist der Fall, wenn die Funktion VPR muss in den Näherungsmodus wechseln, um das gewünschte Ergebnis zurückzugeben.
Beispielsweise: Wir möchten bestimmen, welcher Satz in der Provisionsberechnung für einen Verkäufer mit einem Verkaufsvolumen von 34988 $ verwendet werden soll. Funktion VPR gibt uns einen Wert von 30 % zurück, was absolut richtig ist. Aber warum hat die Formel die Zeile ausgewählt, die genau 30 % enthält und nicht 20 % oder 40 %? Was versteht man unter ungefährer Suche? Damit wir uns verstehen.
Wenn der Streit Range_lookup (interval_lookup) hat einen Wert ECHTER CODE (TRUE) oder ausgelassen, Funktion VPR durchläuft die erste Spalte und wählt den größten Wert aus, der den Suchwert nicht überschreitet.
Wichtiger Punkt: Damit dieses Schema funktioniert, muss die erste Spalte der Tabelle aufsteigend sortiert werden.