EXCEL教學 | 如何查找員工薪資的勞保級距
勞保級距的查找是會計與人資常常會遇到的一個需求，比較小規模的公司通常都是使用EXCEL來管理員工的勞保與健保，這篇要分享如何使用EXCEL函數來快速將員工正確的級距找出來。
EXCEL至今版本五花八門，不同的版本可以使用的函數不同，處理的難易度也不太相同，但不用擔心，這篇各種版本的作法都會收錄在此。
查找數據通常第一個想到的就是VLOOKUP函數，但勞保級距VLOOKUP只能在旁邊涼快
📖延伸閱讀
EXCEL查找神器VLOOKUP 十大常見錯誤
<📂練習檔下載>
看教學之前可以先下載練習檔，學中做、做中學效果更好哦。
▶️影音教學
📝圖文教學
<2021以上版本>
2021以上的EXCEL版本，有一個函數XLOOKUP可以使用，可以很輕鬆的查找出個員工的勞保級距。
✍🏾XLOOKUP查找函數
函數說明=VLOOKUP(查找內容,查找範圍,回傳範圍,查找不到時顯示,比對方法,查找模式)
G4=MIN(XLOOKUP(F4,C:C,C:C,,1,-1),45800)
📝函數說明
G4=XLOOKUP(F4,C:C,C:C,,1,-1)
- 第一個引數:F4→要查找的薪資
- 第二個引數:C:C→查找的範圍
- 第三個引數:C:C→要回傳的範圍(勞保投保金額)
- 第四個引數:(省略)
- 第五個引數:1→完全符合。 如果找不到就傳回下一個較大的內容，資料必須遞增排序
- 第六個引數:-1→從最後一個項目開始執行反向搜尋
這樣輸入完後90%的資料查找都不會有問題，但是如果薪資超過45800(勞保最大級距)，就會出現標題列，這是因為XLOOKUP如果找不到資料，去找下一個比較大的內容，文字跟數字相比，文字等於無限大，所以就會顯示整列只有文字的標題內容。
G4=MIN(XLOOKUP(F4,C:C,C:C,,1,-1),45800)
在XLOOKUP的結果上加上MIN(XLOOUP結果,45800)，這樣就會用45800跟XLOOKUP產生的結果去比大小，會回傳比較小的數值。
例1:薪資29317→勞保級距是30300，去跟45800比較
MIN(30300,45800)→30300
例1:薪資54315→已超過勞保級距上限，所以XLOOKUP的結果是:金額，用金額去跟45800比較。
MIN(金額,45800)→45800 (文字>數字)
- 所以只要搭配MIN進行使用，就能順利查找出正確的勞保級距囉!
📖延伸閱讀
EXCEL 利用MIN與MAX快速設定數值的上下限
<全版本通用>
【輔助欄+INDEX】
輔助 I6=LARGE($C$6:$C$18,ROW(A1))
G6=IFERROR(INDEX(I:I,MATCH(F6,I:I,-1)),45800)
📝函數說明
輔助 I6=LARGE($C$6:$C$18,ROW(A1))
勞保級距是要查找大於薪資的級距，而使用MATCH如果數據是第增，只能查找小於薪資的級距，所以要利用LARGE函數把勞保級距由遞增改為遞減的數列。
📖延伸閱讀
EXCEL 如何用函數排序數值
G6=INDEX(I:I,MATCH(F6,I:I,-1))
MATCH:有了遞減數據後，就可以用薪資(F6)，去查找輔助欄(I:I)，查找方式(-1:查找遞減數據)得到正確勞保級距的位置。
INDEX:利用MTACH找到的位置，回傳正確的勞保級距
但會發現薪資只要超過45800勞保級距上限，就會出現#NA
G6=IFERROR(INDEX(I:I,MATCH(F6,I:I,-1)),45800)
利用IFERROR把出現#NA也就是薪資超過45800，都把他們變成45800。
利用輔助欄就可以很輕鬆的查找到正確的級距囉
【INDEX+IF】
G4=IF(INDEX(C:C,MATCH(F4,C:C,1)+1)=0,45800,INDEX(C:C,MATCH(F4,C:C,1)+1))
📝函數說明
G4=INDEX(C:C,MATCH(F4,C:C,1))
MATCH:使用薪資(F4)，去查找級距的金額(C:C)，查找方式(1:查找遞增數據)得到小於薪資勞保級距的位置
INDEX:利用MTACH找到的位置，回傳勞保級距
- 這時候勞保級距的金額是錯誤的，因為得到得金額是小於薪資的級距
G4=INDEX(C:C,MATCH(F4,C:C,1)+1)
把MATCH的結果+1，那麼勞保級距的位置就會變成大於薪資的那個級距，INDEX函數抓取到的金額就會是正確的級距金額。
- 但是如果薪資大於勞保級距上限45800就會出現0的數字
G4=IF(INDEX(C:C,MATCH(F4,C:C,1)+1)=0,45800,INDEX(C:C,MATCH(F4,C:C,1)+1))
用IF來判斷INDEX+MATCH得到的結果是不是等於0，如果等於0就顯示勞保級距上限48500，如果不是0就顯示INDEX+MATCH的結果。
這樣就可以不用輔助欄輕鬆得到結果囉
【INDEX+TEXT】
G4=--TEXT(INDEX(C:C,MATCH(F4,C:C,1)+1),"0;;45800")
📝函數說明
G4=INDEX(C:C,MATCH(F4,C:C,1)+1)
這邊的公式跟【INDEX+IF】前面是一樣的，可以往前看。
- 得到的結果的BUG就是薪資大於45800就會顯示0
G4=--TEXT(INDEX(C:C,MATCH(F4,C:C,1)+1),"0;;45800")
利用TEXT將0的結果顯示為45800(正數;負數;零;文字)，但TEXT的結果會是文字，所以保險起見可以將結果加上不會改變數值的四則運算--(兩個負號，負負得正)
這樣就會得到正確的級距囉
📖延伸閱讀
EXCEL自訂儲存格格式代號大全
這篇介紹了各種不同版本的作法，可以依據自己的版本與喜好挑選適合自己的方法來提升工作效率哦
歡迎訂閱贊助效率職人沙龍解鎖更多文章
<訂閱沙龍BONUS>
- 贊助訂閱:🔖99元/月 (3.3/天) | 🔖999/年(2.73/天)
- 限閱文章:4篇文章/月
- 解鎖房間:職場設計新思維
- 解鎖可閱讀內容:
1️⃣ EXCEL特殊圖表
2️⃣ POWER QUERY從0到1
3️⃣ 素材分享(ICON、簡報元素)
4️⃣ 全自動抽獎系統模
5️⃣ 直播分享錄影檔:❌不用函數的日期處理術
- 👍喜歡的話可以幫忙案個讚、分享來幫助更多人或是右下珍藏起來哦
- 💭留言回復「職場生存讚」讓我知道你把這個小技巧學起來了
- ❤️追蹤我的方格子，學習更多職場小技巧
- ☕請我喝杯咖啡，鼓勵我更有動力分享更多優質內容
- 📈訂閱EXCEL設計新思維，學習更多更深更廣的職場技能
😎可以找到我的地方
raw-image
你可能也想看
