excel 薪資表單

office excel vlookup 薪資系統級距

匿名 2014-01-17 14:34:20 ‧ 53205 瀏覽

分享至

請問一下大家，該怎麼利用excel，做出一個簡易的薪資系統，

有兩個sheet：薪資以及勞健保級距，如下：

請問一下大家，該用甚麼樣的公式，才可以讓使用者輸入投保金額和眷屬這兩個欄位，

就可以根據勞健保級距表，自動帶出勞保本人負擔、勞保雇主負擔、健保本人負擔以及健保雇主負擔這

四個欄位，有上網查了一下資料，應該是用vlookup函數，但不太清楚該怎麼樣設定，

附檔如下：http://www.funp.net/47150

謝謝大家

蟹老闆 iT邦大師 1 級 ‧ 2014-01-19 15:41:15 檢舉

G2輸入

=VLOOKUP($F2,級距!$A:$E,COLUMN()-5,TRUE)

然後向下及向右複製公式

Vlookup 說明
•如果 range_lookup 為 TRUE 或被省略，則傳回完全符合或大約符合的值。如果找不到完全符合的值，將會傳回僅次於 lookup_value 的值。

PS.被比對的資料必需要由小至大排序

匿名檢舉

您好：
我有依照您的公式設定，不過投保金額20800，應該對應的級距應該是21000，但卻跳到了20100，想請問一下該怎麼修改呢?

蟹老闆 iT邦大師 1 級 ‧ 2014-01-22 17:11:19 檢舉

投保金額20800，應該對應的級距應該是21000

投保的級距是如何我不是很了解,以引用的例子就公式來說小於21000的部份都會算20100的級距,也就是說如果投保金額是20999還是會算在20100的級距,若20800應是在21000的級距,那要將改成21000的級距改成從20800開始。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

slime

iT邦大師 1 級 ‧ 2014-01-18 09:07:18

最佳解答

vlookup 比較適合"資料完全一致"時來找資料並對照, 薪資這種是"級距"而不是"資料相同", 就不適合.
要從薪資求"代扣勞/健保費", 就需要分成幾個步驟:
a. 定義好應計算勞/健保的所有薪資與津貼.
b. 由 a. 計算出的全部薪資, 找到對應的級距. (注意: 勞/健保的級距"可能"不同, 建議分兩張表.)
c. 由對應的級距, 查出應代扣的勞/健保費. (注意: 只是代扣, 會計帳上要注意.)
d. 計算 a 與 c , 算出實際支付的金額.
由 2. , 可以找到對應的公式:
a. sum
b. 建議用 match
c. 建議用 index
d. sum 或 =a-b-c 即可
寫了一個例子: (註: 偷懶, 勞健保還是沒有分開, 而且勞健保費並不是正確的.)
https://docs.google.com/spreadsheet/ccc?key=0Ar10y-TeUk90dFR1N29jUXp0czBIcVB2ejBRSFdIOVE&usp=sharing

a. 首先看到資料表"勞健保清單", 這邊參考一般使用的表格, 要注意的是排列方式, 牽涉到後面公式的寫法.
b. 看到資料表"員工薪資"的儲存格 B9 , 是所有應該被計算勞健保的薪資總額.
c. 資料表"員工薪資"的儲存格 B11 , 由薪資總額, 用 match 公式, 從勞健保總表, 查出對應的級距. (公式寫法在 C11 )
d. 資料表"員工薪資"的儲存格 B12 與 B13 , 由級距找到應代扣的勞/健保費. (公式在 C12 與 C13 , 再次提醒, 勞健保應該分開.... XD)
e. 最後再把所有薪資(含不扣勞健保的紅利)與代扣勞健保的部份算出來, 見 B16 .
f. 稍微排版或註記, 產生給員工/會計/老闆看的版本.