Excel-Funktionslern-Suchfunktion, Multi-Condition-Matching-Suchanwendung

Dieser Artikel enthält eine Excel-Formel zur Berechnung der Verkaufsprovision. Er verwendet hauptsächlich die Suchfunktion, um Übereinstimmungen mit mehreren Bedingungen zu finden.

Kürzlich stellte ein Student unserer Lernaustauschgruppe eine Frage zur Provisionsberechnung.

Ich habe das Problem dieses Schülers durch einfache Kommunikation ungefähr verstanden. Wie in der folgenden Tabelle gezeigt:

Der Zeilenbereich 1–5 ist die Provisionstabelle, die unterschiedlichen Abschlussraten und unterschiedlichen Unterzeichnungsbeträgen entspricht. Der Bereich in den Zeilen 8–12 stellt die tatsächliche Abschlussrate und den Bestellbetrag der vier Benutzer dar. Nun muss der Provisionsbetrag dieser vier Benutzer auf der Grundlage der tatsächlichen Abschlussrate und Bestellbetragsdaten berechnet werden.

Dieses Beispiel betrifft hauptsächlich die folgenden Probleme:

1 Wie finde ich die entsprechende Abschlussrate basierend auf der Abschlussrate und den Bestellmengendaten des Benutzers?

2. Das Layout der Provisionsvergleichstabelle ist zweidimensional, was den Abgleich der gesamten Tabelle erschwert.

Jetzt analysieren und lösen wir gemeinsam mit Ihnen Schritt für Schritt dieses Problem.

Schritt eins: Ordnen Sie die Abschlussratendaten den entsprechenden Zahnrädern zu.

Geben Sie die Formel in Zelle D9 ein:

=LOOKUP(B9,{0.7,0.8,0.9,1})=LOOKUP(B9,{0.7,0.8,0.9,1})

解析：

LOOKUP（查找值，查找区域，返回区域），其中第三参数可以省略，省略时第二参数就作为查找区域和返回区域。

注意：

第一参数和第二参数的数据必须按升序排列，否则函数LOOKUP不能返回正确的结果，文本不区分大小写。

如果在查找区域中找不到查找值，则查找第二参数中小于等于查找值的最大数值。

如果查找值小于第二参数中的最小值，函数LOOKUP返回错误值#N/A。

其实可以简单理解为当X查找值时，那么就取X值。

本例中函数公式可以理解为X时，A用户的完成率为0.9992，通过X可以看到0.9是小于等于0.9992的最大值。那么按照lookup函数查找规则应该返回0.9，这样我们就完成了4个用户完成率的分档。

第二步：以同样的方式完成签单金额的分档。

在E9单元格输入公式：

=LOOKUP(C9/10000,{0,30,50,80,100,150,200},{"30万以下","30-50","50-80","80-100","100-150","150-200","200万以上"})，双击填充公式。

解析：

这里的公式中，LOOKUP有三个参数，第一参数为查找值，第二参数为查找区域，第三参数为返回指定的文本。

第三步：根据用户完成率和签单金额所处的分档来查找对应的提成。

这一步很简单，根据D9在A1-H5区域找到提成所在行，根据E9在A1-H5区域找到提成所在列，即可得到对应的提成结果。

F9单元格输入公式：

=VLOOKUP(D9,$A:$H,MATCH(E9,$A:$H,0),0)，双击填充。

解析：

VLOOKUP（查找值，查找区域，返回第几列，0）

Match（查找值，查找区域，0），需要注意的是，match函数的查找区域只能是单行单列。

上方公式的含义：使用VLOOKUP函数，在A1-H5区域内查找D9单元格值在第几行，再使用Match函数在A1-H1区域内查找E9单元格值在第几列，根据查找到的行号和列号即可得到对应的提成。

第四步：最后我们使用INT函数将公式计算结果统计出来。

首先在G9单元格输入="=INT("&F9&")"

Analyse:

🎜LOOKUP (Suchwert, Suchbereich , Rückgabebereich), der dritte Parameter kann weggelassen werden, und wenn er weggelassen wird, wird der zweite Parameter als Suchbereich und Rückgabebereich verwendet. 🎜🎜🎜Hinweis: 🎜🎜🎜Die Daten des ersten Parameters und des zweiten Parameters müssen in aufsteigender Reihenfolge angeordnet sein, sonst kann die Funktion LOOKUP nicht das richtige Ergebnis zurückgeben und der Text unterscheidet nicht zwischen Groß- und Kleinschreibung. 🎜🎜Wenn der Suchwert nicht im Suchbereich gefunden wird, suchen Sie nach dem Maximalwert im zweiten Parameter, der kleiner oder gleich dem Suchwert ist. 🎜🎜Wenn der Suchwert kleiner als der Mindestwert im zweiten Parameter ist, gibt die Funktion LOOKUP Fehlerwert zurück. a>#N /A. 🎜🎜Tatsächlich kann es einfach so verstanden werden, dass, wenn X nach dem Wert sucht, der X-Wert genommen wird. 🎜🎜Wenn in diesem Beispiel die Funktionsformel als X verstanden werden kann, beträgt die Abschlussrate von Benutzer A 0,9992 Dann sollte gemäß der Suchregel der Suchfunktion 0,9 zurückgegeben werden. Auf diese Weise haben wir die Bewertung von 4 Benutzerabschlussraten abgeschlossen. 🎜🎜🎜🎜Schritt 2: Führen Sie auf die gleiche Weise die Staffelung des Signierbetrags durch. 🎜🎜🎜🎜Geben Sie die Formel in Zelle E9 ein: 🎜🎜=LOOKUP(C9/10000,{0,30,50,80,100,150,200},{"under 300.000","30-50","50-80 " ,„80-100“, „100-150“, „150-200“, „Mehr als 2 Millionen“}), doppelklicken Sie, um die Formel auszufüllen. 🎜🎜🎜🎜🎜 Analyse: 🎜🎜🎜In der Formel hier hat LOOKUP drei Parameter. Der erste Parameter ist der Suchwert, der zweite Parameter ist der Suchbereich und der dritte Parameter dient zur Rückgabe des angegebenen Textes. 🎜🎜🎜🎜Schritt 3: Finden Sie die entsprechende Provision basierend auf der Abschlussrate des Benutzers und der Höhe des Bestellbetrags. 🎜🎜🎜🎜Dieser Schritt ist sehr einfach. Suchen Sie anhand von D9 die Zeile, in der sich die Provision im Bereich A1-H5 befindet, und suchen Sie anhand von E9 die Spalte, in der sich die Provision im Bereich A1-H5 befindet erhalten Sie das entsprechende Provisionsergebnis. 🎜🎜🎜Geben Sie die Formel in Zelle F9 ein: 🎜🎜🎜=VLOOKUP(D9,$A$1:$H$5,MATCH(E9,$A$1:$H$1,0),0) , Zum Ausfüllen doppelklicken. 🎜🎜🎜🎜🎜 Analyse: 🎜🎜🎜VLOOKUP (Wert finden, Suchbereich, Rückgabespalte, 0)🎜🎜Übereinstimmung (Wert finden, Suchbereich, 0), muss aufgepasst werden Darüber hinaus kann der Suchbereich der Match-Funktion nur eine einzelne Zeile und eine einzelne Spalte umfassen. 🎜🎜Die Bedeutung der obigen Formel: Verwenden Sie die VLOOKUP-Funktion, um herauszufinden, in welcher Zeile sich der Wert von Zelle D9 im Bereich A1-H5 befindet, und verwenden Sie dann die Match-Funktion, um herauszufinden, in welcher Spalte sich der Wert von Zelle E9 befindet Bereich A1-H1. Entsprechend der gefundenen Zeilennummer und Spaltennummer können Sie die entsprechende Provision erhalten. 🎜🎜🎜🎜Schritt 4: Schließlich verwenden wir die INT-Funktion, um die Ergebnisse der Formelberechnung zu zählen. 🎜🎜🎜🎜Geben Sie zuerst ="=INT("&F9&")" in Zelle G9 ein. Fertig. 🎜

Das Endergebnis ist wie folgt:

Nun wurde die Provisionsdatenstatistik der Nutzerzahl Schritt für Schritt vervollständigt. Wenn Sie die Hilfsspalte nicht verwenden möchten und das Ergebnis in einem Schritt erhalten möchten, kombinieren Sie einfach die obigen Formeln miteinander.

In diesem Beispiel ist es etwas langwierig, die Funktionsformeln miteinander zu kombinieren, aber die verwendeten Funktionen, mit Ausnahme der LOOKUP-Funktion, die studiert werden muss, sind die grundlegendsten und am häufigsten verwendeten Funktionen, selbst wenn Sie ein Anfänger im Umgang mit Funktionen sind . Leicht gemacht! Tatsächlich besteht der Hauptzweck des heutigen Tutorials darin, Ihnen zu sagen, dass Sie, bevor Sie ein Meister werden, ein großes Problem, das schwer zu lösen ist, in mehrere kleine Probleme aufteilen und diese am Ende eines nach dem anderen lösen können , das große Problem wird gelöst!

Verwandte Lernempfehlungen: Excel-Tutorial

Das obige ist der detaillierte Inhalt vonExcel-Funktionslern-Suchfunktion, Multi-Condition-Matching-Suchanwendung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!