iT邦幫忙

0

excel 薪資表單

匿名 2014-01-17 14:34:2035675 瀏覽

請問一下大家,該怎麼利用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開始。

1 個回答

8
slime
iT邦大師 1 級 ‧ 2014-01-18 09:07:18
最佳解答
  1. vlookup 比較適合"資料完全一致"時來找資料並對照, 薪資這種是"級距"而不是"資料相同", 就不適合.

  2. 要從薪資求"代扣勞/健保費", 就需要分成幾個步驟:
    a. 定義好應計算勞/健保的所有薪資與津貼.
    b. 由 a. 計算出的全部薪資, 找到對應的級距. (注意: 勞/健保的級距"可能"不同, 建議分兩張表.)
    c. 由對應的級距, 查出應代扣的勞/健保費. (注意: 只是代扣, 會計帳上要注意.)
    d. 計算 a 與 c , 算出實際支付的金額.

  3. 由 2. , 可以找到對應的公式:
    a. sum
    b. 建議用 match
    c. 建議用 index
    d. sum 或 =a-b-c 即可

  4. 寫了一個例子: (註: 偷懶, 勞健保還是沒有分開, 而且勞健保費並不是正確的.)
    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. 稍微排版或註記, 產生給員工/會計/老闆看的版本.

看更多先前的回應...收起先前的回應...
匿名 檢舉

感謝您的回應,但不知道是否能夠依照原本的欄位去設定公式呢?

slime iT邦大師 1 級 ‧ 2014-01-22 11:44:29 檢舉

可以啊....原本拆開寫只是因為組合了幾個不同功能(函式), 拆開比較方便說明, 如果要直接產生圖二的結果, 就是把公式合併起來就可以了.

匿名 檢舉

了解,感謝,不過該怎麼設定對應高的級距啊?
已例子來說,27500,所應該對應的級距應該是30000,而不是25000,
也就是說,勞保費(員工)應為500而不是400

匿名 檢舉

我找到解決方法了,把級距從大排到小就行了,感謝!

我要發表回答

立即登入回答