近日我們的學(xué)習(xí)交流群中某學(xué)員提出了一個(gè)關(guān)于計(jì)算提成的問題。

通過簡(jiǎn)單的溝通大概了解了這位學(xué)員的問題。如下表：

1-5行區(qū)域是不同完成率以及不同簽單金額對(duì)應(yīng)的提成表。8-12行區(qū)域則是4位用戶的實(shí)際完成率以及簽單金額，現(xiàn)在需要根據(jù)實(shí)際完成率、簽單金額數(shù)據(jù)來計(jì)算這4位用戶的提成金額。

此例中主要涉及以下幾個(gè)問題點(diǎn)：

1.如何才能根據(jù)用戶的完成率、簽單金額數(shù)據(jù)查找對(duì)應(yīng)的完成率檔位？

2.提成對(duì)照表的排版方式是二維形式，給整個(gè)表格匹配增加難度。

下面我們分步來跟大家一起分析解決這個(gè)問題。

第一步：將完成率數(shù)據(jù)分別匹配到對(duì)應(yīng)的檔位。

在D9單元格輸入公式：

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

解析：

LOOKUP（查找值，查找區(qū)域，返回區(qū)域），其中第三參數(shù)可以省略，省略時(shí)第二參數(shù)就作為查找區(qū)域和返回區(qū)域。

注意：

第一參數(shù)和第二參數(shù)的數(shù)據(jù)必須按升序排列，否則函數(shù)LOOKUP不能返回正確的結(jié)果，文本不區(qū)分大小寫。

如果在查找區(qū)域中找不到查找值，則查找第二參數(shù)中小于等于查找值的最大數(shù)值。

如果查找值小于第二參數(shù)中的最小值，函數(shù)LOOKUP返回錯(cuò)誤值#N/A。

其實(shí)可以簡(jiǎn)單理解為當(dāng)X時(shí)，那么就取X值。

本例中函數(shù)公式可以理解為X時(shí)，A用戶的完成率為0.9992，通過X可以看到0.9是小于等于0.9992的最大值。那么按照lookup函數(shù)查找規(guī)則應(yīng)該返回0.9，這樣我們就完成了4個(gè)用戶完成率的分檔。

第二步：以同樣的方式完成簽單金額的分檔。

在E9單元格輸入公式：

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

解析：

這里的公式中，LOOKUP有三個(gè)參數(shù)，第一參數(shù)為查找值，第二參數(shù)為查找區(qū)域，第三參數(shù)為返回指定的文本。

第三步：根據(jù)用戶完成率和簽單金額所處的分檔來查找對(duì)應(yīng)的提成。

這一步很簡(jiǎn)單，根據(jù)D9在A1-H5區(qū)域找到提成所在行，根據(jù)E9在A1-H5區(qū)域找到提成所在列，即可得到對(duì)應(yīng)的提成結(jié)果。

F9單元格輸入公式：

=VLOOKUP(D9,$A$1:$H$5,MATCH(E9,$A$1:$H$1,0),0)，雙擊填充。

解析：

VLOOKUP（查找值，查找區(qū)域，返回第幾列，0）

Match（查找值，查找區(qū)域，0），需要注意的是，match函數(shù)的查找區(qū)域只能是單行單列。

上方公式的含義：使用VLOOKUP函數(shù)，在A1-H5區(qū)域內(nèi)查找D9單元格值在第幾行，再使用Match函數(shù)在A1-H1區(qū)域內(nèi)查找E9單元格值在第幾列，根據(jù)查找到的行號(hào)和列號(hào)即可得到對(duì)應(yīng)的提成。

第四步：最后我們使用INT函數(shù)將公式計(jì)算結(jié)果統(tǒng)計(jì)出來。

首先在G9單元格輸入="=INT("&F9&")"

然后將G9:G12選擇性黏貼為數(shù)值，隨后將=替換為=即可。

最終結(jié)果如下：

現(xiàn)在分步驟已經(jīng)完成用戶數(shù)的提成數(shù)據(jù)統(tǒng)計(jì)。如果不想使用輔助列，想一步得到結(jié)果，將上方公式組合在一起即可。

本例中若將函數(shù)公式都組合在一起稍微有點(diǎn)長(zhǎng)，但使用到的函數(shù)，除了LOOKUP函數(shù)需要鉆研以外，其他的函數(shù)都是最最基礎(chǔ)且常用的函數(shù)，即使是函數(shù)小白也可以輕松完成！其實(shí)今天的教程，主要是想告訴大家，在還沒變牛X前，遇到很難解決的大問題可以拆分成幾個(gè)小問題，挨個(gè)解決，最終大問題也就解決啦！

＊＊＊＊部落窩教育－excel多條件匹配查找＊＊＊＊

原創(chuàng)：龔春光/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）

更多教程：部落窩教育（www.itblw.com）

微信公眾號(hào)：exceljiaocheng