編按：

今天遇到一個工齡補助的問題，讓人腦洞大開，于是一次性寫出了20個函數(shù)公式，趕緊來學(xué)習(xí)一下吧！相信大家可以學(xué)到更多的函數(shù)思路，多角度去理解函數(shù)的應(yīng)用方式，達到活學(xué)活用的境界！

曾經(jīng)發(fā)表過一篇教程，因為一個簡單問題，引發(fā)出一大波的公式函數(shù)用法。

可以戳鏈接查看：

一個四舍五入的問題竟然連LOOKUP都用上了，你們也太會玩了……

今天再來看一個計算工齡補助的問題，領(lǐng)略一題多解的樂趣。

注意：本篇不涉及函數(shù)的基本用法講解。

我們的工齡補助計算規(guī)則為：

①  工齡不足5年無補助；

②  工齡滿5年不足10年補助100元；

③  工齡滿10年不足15年補助200元；

④  工齡滿15年一律補助300元。

下圖是模擬數(shù)據(jù)和結(jié)果。

對于這類問題，最容易想到的就是IF函數(shù)，所以先來看兩個使用IF函數(shù)的公式。

IF解法1：=IF(B2<5,0,IF(B2<10,100,IF(B2<15,200,300)))

一共有四種情況：無補助、補助100元、補助200元、補助300元，所以用了三個IF嵌套解決，公式的具體原理就不多說了。

將解法1的邏輯倒過來，就得到了解法2。

IF解法2：=IF(B2>=15,300,IF(B2>=10,200,IF(B2>=5,100,0)))

使用多個IF嵌套的時候，一定要理清邏輯順序，對比這兩個公式相信可以加深對IF函數(shù)的理解。

在實際應(yīng)用中，經(jīng)常會使用LOOKUP來取代IF函數(shù)處理這種區(qū)間匹配的問題，所以下面的幾個公式都是用LOOKUP來解決問題的。

LOOKUP解法1：=LOOKUP(B2,{0,0;5,100;10,200;15,300})

這種用法中LOOKUP用到了兩個參數(shù)，第二參數(shù){0,0;5,100;10,200;15,300}等于這樣的一個4行2列的數(shù)組。

這樣就把一個多次邏輯判斷的問題變成了一個數(shù)據(jù)匹配的問題，不過這里用的是模糊匹配的二分法原理。

詳情可以戳鏈接：二分法

也可以使用三個參數(shù)的用法，這就有了下面這個公式。

LOOKUP解法2：=LOOKUP(B2,{0,5,10,15},{0,100,200,300})

接下來的三個lookup公式都是在數(shù)組的構(gòu)造上玩起了花樣。

LOOKUP解法3：=LOOKUP(B2,{0,1,2,3}*5,{0,1,2,3}*100)

LOOKUP解法4：=LOOKUP(B2,{0,5,10,15},{0,1,2,3}/1%)

LOOKUP解法5：=LOOKUP(B2/5,{0;1;2;3})/1%

公式變得越來越簡短，但是越來越難以理解，尤其是解法5的思路，確實值得玩味。

再來看一個VLOOKUP的公式，=VLOOKUP(B2,{0,0;5,100;10,200;15,300},2,1)

這個公式中VLOOKUP的第四參數(shù)使用了1，表示模糊匹配，而我們平時用的更多的則是精確匹配，當(dāng)使用模糊匹配時，第四參數(shù)還可以直接省略，公式就變成了：

=VLOOKUP(B2,{0,0;5,100;10,200;15,300},2)，注意，這種省略是連同第三參數(shù)后面的逗號一起省略的。

還可以將那個經(jīng)典的INDEX-MATCH組合也用到這個例子里，公式是：

=INDEX({0;100;200;300},MATCH(B2,{0;5;10;15}))

更甚者可以直接使用MATCH函數(shù)來解決這個問題。

MATCH解法1：=(MATCH(B2/5,{0,1,2,3})-1)*100

要注意的是，這個公式里MATCH只用了兩個參數(shù)，省略第三參數(shù)是MATCH大致匹配的用法，具體原理可以參考之前的相關(guān)教程。公式還可以這樣寫=(MATCH(B2,{0,5,10,15})-1)/1%，結(jié)果同樣正確，這就有點數(shù)字游戲的感覺了，有興趣的同學(xué)可以自己琢磨一下兩個公式的異同點。

下面這兩個公式中的主角是一個我們平時用的不多的CHOOSE函數(shù)，當(dāng)然需要和其他函數(shù)組合起來才好用。

CHOOSE解法1：=CHOOSE(MATCH(B2,{0,5,10,15}),0,100,200,300)

還可以將這個公式中的MATCH(B2,{0,5,10,15})這部分換一個思路，就有了CHOOSE解法2：

=CHOOSE(MIN(INT(B2/5)+1,4),0,100,200,300)

以上的這些公式中，都用了查找引用類的函數(shù)，但是在一些參數(shù)的構(gòu)造中感覺開始玩數(shù)字游戲了。

下面的這幾個公式之間就是數(shù)字邏輯，都是很基礎(chǔ)的函數(shù)，不妨試試你能理解幾個。

MIN-INT解法1：=MIN(INT(B2/5)*100,300)

MIN-INT解法2：=MIN(INT(B2/5),3)*100

MIN-INT解法3：=MIN(INT(B2/5),3)/1%

MIN-FLOOR解法：=MIN(FLOOR(B2,5),15)*20

除此之外還有兩個更燒腦的公式。

SUM-FREQUENCY解法：=SUM(FREQUENCY(B2,{4;9;14})*{0;1;2;3}/1%)

MID-MATCH解法：=MID("0123",MATCH(B2,{0,5,10,15}),1)*100

最后再來一波Excel365新增函數(shù)的解法。

XLOOKUP 解法：=XLOOKUP(B2,{0,5,10,15},{0,100,200,300},,-1)

SWITCH-MATCH解法：=SWITCH(MATCH(B2,{0,5,10,15}),1,0,2,100,3,200,4,300)

當(dāng)然少不了IFS函數(shù)，IFS解法1：=IFS(B2<5,0,B2<10,100,B2<15,200,B2>=15,300)

IFS解法2：=IFS(B2<5,0,B2<10,100,B2<15,200,1,300)

IFS解法3：=IFS(B2>=15,300,B2>=10,200,B2>=5,100,B2<5,0)

IFS解法4：=IFS(B2>=15,300,B2>=10,200,B2>=5,100,1,0)

怎么樣，看了這么多解法，你的腦洞開了嗎？

以上就是今天的所有內(nèi)容，感謝你的觀看。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

10種職場人最常用的excel多條件查找方法！（建議收藏）

別怕，VBA入門級教程來了，條件語句很簡單！

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者老菜鳥；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。