首頁 > 專題 > excel > 主體

Excel函數學習之lookup函數多條件匹配查找應用

青灯夜游
發布: 2023-01-14 19:03:08
轉載
5100 人瀏覽過

這篇文章分享一個Excel算銷售提成公式,主要使用到了lookup函數多條件查找匹配,希望對大家有幫助!

Excel函數學習之lookup函數多條件匹配查找應用

最近我們的學習交流群中某學員提出了一個關於計算提成的問題。

Excel函數學習之lookup函數多條件匹配查找應用

Excel函數學習之lookup函數多條件匹配查找應用

透過簡單的溝通大概了解了這位學員的問題。如下表:

Excel函數學習之lookup函數多條件匹配查找應用

1-5行區域是不同完成率以及不同簽單金額對應的提成表。 8-12行區域則是4位用戶的實際完成率以及簽單金額,現在需要根據實際完成率、簽單金額數據來計算這4位用戶的提成金額。

此範例主要涉及以下幾個問題點:

1.如何才能根據使用者的完成率、簽單金額資料尋找對應的完成率檔位?

2.提成對照表的排版方式是二維形式,給整個表格配對增加難度。

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

第一步:將完成率資料分別配對到對應的檔位。

在D9單元格輸入公式:

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

Excel函數學習之lookup函數多條件匹配查找應用

#解析:

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萬以上"}),雙擊填滿公式。

Excel函數學習之lookup函數多條件匹配查找應用

解析:

這裡的公式中,LOOKUP有三個參數,第一參數為查找值,第二參數為尋找區域,第三參數為傳回指定的文字。

第三步:根據使用者完成率和簽單金額所處的分檔來找出對應的提成。

這一步很簡單,根據D9在A1-H5區域找到提成所在行,根據E9在A1-H5區域找到提成所在列,即可得到對應的提成結果。

F9儲存格輸入公式:

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

Excel函數學習之lookup函數多條件匹配查找應用

解析:

#VLOOKUP(尋找值,尋找區域,傳回第幾列,0)

Match(尋找值,尋找區域,0),需要注意的是,match函數的尋找區域只能是單行單列。

上方公式的意義:使用VLOOKUP函數,在A1-H5區域內查找D9單元格值在第幾行,再使用Match函數在A1-H1區域內查找E9單元格值在第幾列,根據查找到的行號和列號即可得到對應的提成。

第四步:最後我們使用INT函數將公式計算結果統計出來。

先在G9儲存格輸入="=INT("&F9&")"

Excel函數學習之lookup函數多條件匹配查找應用

然後將G9:G12選擇性貼上為數值,隨後將=替換為=即可。

Excel函數學習之lookup函數多條件匹配查找應用

最終結果如下:

Excel函數學習之lookup函數多條件匹配查找應用

現在分步驟已經完成使用者數的提成資料統計。如果不想使用輔助列,想一步得到結果,將上方公式組合在一起即可。

本例若將函數公式都組合在一起稍微有點長,但使用到的函數,除了LOOKUP函數需要鑽研以外,其他的函數都是最最基礎且常用的函數,即使是函數小白也可以輕鬆完成!其實今天的教程,主要是想告訴大家,在還沒變牛X前,遇到很難解決的大問題可以拆分成幾個小問題,挨個解決,最終大問題也就解決啦!

相關學習推薦:excel教學

以上是Excel函數學習之lookup函數多條件匹配查找應用的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:itblw.com
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板