第七章 人事薪資系統應用 學習重點 建立勞健保、薪資所得稅參考表格 定義範圍名稱 使用IF()函數判斷是否有全勤獎金

Presentation on theme: "第七章 人事薪資系統應用 學習重點 建立勞健保、薪資所得稅參考表格 定義範圍名稱 使用IF()函數判斷是否有全勤獎金"— Presentation transcript:

1 第七章 人事薪資系統應用 學習重點 建立勞健保、薪資所得稅參考表格 定義範圍名稱 使用IF()函數判斷是否有全勤獎金
運用VLOOKUP()函數 共用活頁簿 追蹤修訂

2 章節大綱 7-1 人事基本資料建立 7-2 共用活頁簿 7-3 追蹤修訂 備註：可依進度點選小節

3 7-1 人事基本資料建立 「人事薪資計算表」：此表的功用是彙整其他表格的資料，在此計算出每位員工的薪資狀況。
「調薪紀錄表」：記錄每位員工從進公司到目前為止的薪資調整狀況、扶養人數、健保人數及員工的銀行帳號等等資料。 「健保費用扣繳表」：列出依照薪資的不同，健保費用扣繳的明細。 「勞保費用扣繳表」：列出各個等級的勞保扣繳費用表。 「薪資所得扣繳表」：列出各個薪資等級差距的所得扣繳費用表。

4 7-1 人事基本資料建立 人事薪資計算表架構(1)
員工薪資＝「員工底薪」 + 「全勤金額」 －「代扣所得稅」－「代扣勞保費」－「代扣健保費」 員工底薪－此員工底薪資料是由「調薪紀錄」工作表中查得此人最近的底薪紀錄。 全勤金額－此資料是以「調薪紀錄」工作表中查出此員工的全勤金額，再查詢是否有請假、遲到等紀錄，若沒有這些記錄才會發給全勤金額。

5 7-1 人事基本資料建立 人事薪資計算表架構(2)
代扣所得稅－首先在「調薪紀錄」工作表查出此員工是否有扶養人數，然後依照此員工的底薪及扶養人數，再去「薪資所得扣繳表」中查詢此員工的應扣金額。 代扣勞保費－依照此員工的底薪薪資，再去「勞保費用扣繳表」中查詢此員工的應扣金額為多少。 代扣健保費－首先在「調薪紀錄」工作表查出此員工的健保人數，然後再依照此員工的底薪薪資及健保人數，再去「健保費用扣繳表」中查詢此員工的應扣金額為多少。

6 7-1 人事基本資料建立 定義儲存格範圍名稱 1 2 3.執行此指令 2. 選取A1：N30儲存格 選取的工作表及儲存格的範圍
1. 切換至「調薪紀錄」工作表

7 7-1 人事基本資料建立 定義儲存格範圍名稱 3 在此出現新增的定義名稱

8 7-1 人事基本資料建立 所有需要定義的資料範圍名稱整理如下表 定義名稱 範圍 薪資資料 =調薪紀錄!$A$1:$N$30 健保費用扣繳表
=健保費用扣繳表!$A$3:$F$100 勞保費用扣繳表 =勞保費用扣繳表!$A$2:$C$100 薪資所得扣繳表 =薪資所得扣繳表!$A$3:$F$100 薪資總額 =計算!$I$4:$I$17

9 7-1 人事基本資料建立 IF()函數 語法：IF (Logical_test, Value_if_true, Value_if_false ) 說明：IF()函數可用來測試數值和公式條件，並傳回不同的結果 相關引數說明如下： 引數名稱 說明 Logical_test 此為判斷式。用來判斷測試條件是否成立 Value_if_true 此為條件成立時，所執行的程序 Value_if_false 此為條件不成立時，所執行的程序

10 7-1 人事基本資料建立 VLOOKUP()函數
語法：VLOOKUP(lookup_value,Table_array,Col_index_num,Range_looKup) 說明：VLOOKUP()函數可在陳列或表格中尋找其特定值的欄位，並傳回同一列的某一指定儲存格中的值 相關引數說明如下： 引數名稱 說明 Lookup_value 搜尋資料的條件依據 Table_array 搜尋資料範圍 Col_index_num 指定傳回範圍中符合條件的那一欄 Range_lookup 此為邏輯值，若設為True或省略，則會找出部分符合的值；若設為False，則會找出全部符合的值

11 7-1 人事基本資料建立 填入部門名稱 1 2 切換至此「計算」工作表 選此D4儲存格並以滑鼠拖曳填滿控點至D17儲存格後

12 7-1 人事基本資料建立 填入部門名稱 3 放開滑鼠所有員工的部門資料都填入了！

13 7-1 人事基本資料建立 計算員工底薪 選取E4儲存格並在此輸入公式「=ROUND(VLOOKUP(B4,薪資資料,5,0),0)」後，按下「Enter」鍵 1 2 選此儲存格並以滑鼠拖曳此填滿控點至E17儲存格後

14 7-1 人事基本資料建立 計算員工底薪 3 放開滑鼠，所有員工底薪資料都建立好了！

15 7-1 人事基本資料建立 計算全勤獎金 1 選擇F4儲存格並在此輸入「=IF(IF(R4<=5,1,0)+IF(P4+Q4=0,1,0)=2,2000,0)」後，按下「Enter」鍵 2 滑鼠拖曳F4儲存格的填滿控點至F17儲存格

16 7-1 人事基本資料建立 計算全勤獎金 3 放開滑鼠顯示出這個月的全勤獎金紀錄

17 7-1 人事基本資料建立 扣請假款計算 遲到不超過10分鐘就不用扣遲到費用，超過10分鐘，則每分鐘要扣10元；請事假則是以底薪除以30天，然後再乘上事假天數，就是此月的請事假扣款額；請病假則是事假的一半扣款，公式如下：

18 7-1 人事基本資料建立 扣請假款計算 1 2 以滑鼠拖曳H4儲存格的填滿控點至H17儲存格後
選取H4儲存格並在此輸入公式「=ROUND(VLOOKUP (B4,薪資資料 ,5,0)/30*(P4/2+Q4)+IF(R4>10,R4*10,0),0)」後，按下「Enter」鍵

19 7-1 人事基本資料建立 扣請假款計算 3 放開滑鼠，顯示出每位員工應扣的金額！

20 7-1 人事基本資料建立 計算薪資總額 1 2 選取I4儲存格並在此輸入公式「=SUM(E4:F4)-H4」後，按下「Enter」鍵
設定小數位數為「0」、符號為「$」

21 7-1 人事基本資料建立 計算薪資總額 3 所有的數值前都加上「$」符號了！

22 7-1 人事基本資料建立 代扣所得稅計算 1 選取J4儲存格並在此輸入公式「=VLOOKUP(薪資總額,薪資所得扣繳表,VLOOKUP(B4,薪資資料,12,0)+2,1)」後，按下「Enter」鍵

23 7-1 人事基本資料建立 代扣所得稅計算 2 以滑鼠拖曳J4儲存格的填滿控點至J17儲存格後，放開滑鼠即可

24 7-1 人事基本資料建立 代扣健保費之計算 1 2 以滑鼠拖曳K4儲存格的填滿控點至K17儲存格後，放開滑鼠即可
選取K4儲存格並在此輸入「」後，按下「Enter」鍵

25 7-1 人事基本資料建立 代扣勞保費計算 1 2 以滑鼠拖曳L4儲存格的填滿控點至L17儲存格後，放開滑鼠即可
選此L4儲存格並在此輸入「=VLOOKUP(薪資總額,勞保費用扣繳表,2)」後，按下「Enter」鍵

26 7-1 人事基本資料建立 減項小計與應付薪資之計算 1 2 2. 選取N4至O17儲存格 1. 切換至此「計算」工作表
選取N4儲存格並在此輸入公式「=SUM(J4:L4)」後，按下「Enter」鍵

27 7-1 人事基本資料建立 減項小計與應付薪資之計算 3 以滑鼠拖曳N4:O4儲存格的填滿控點至N17:O17儲存格後 4
按「減少小數位數」鈕2次

28 7-1 人事基本資料建立 減項小計與應付薪資之計算 5

29 7-2 共用活頁簿 保護活頁簿 1 1 2 勾選此項 3

30 7-2 共用活頁簿 啟用共用活頁簿

31 7-2 共用活頁簿 啟用共用活頁簿 1 在此顯示出「共用」二字，表示此活頁簿已經是「共用」狀態！ 勾選此項 2

32 7-2 共用活頁簿 分享活頁簿之資源 1 選擇活頁簿所在之資料夾位置

33 7-2 共用活頁簿 分享活頁簿之資源 2 選擇開啟公用資料夾

34 7-2 共用活頁簿 分享活頁簿之資源 3 檔案呈現「共用」狀態

35 7-2 共用活頁簿 檢視共用人員 目前正在使用的人員名稱 可按此鈕移除使用者

36 7-2 共用活頁簿 共用活頁簿的特性 活頁簿限制：活頁簿共用後，依然可以新增工作表、刪除一整欄或一整列的儲存格、或者插入儲存格，但是不可使用刪除整個工作表、合併儲存格及套用格式化等功能。 記錄修訂：當此活頁簿被修訂之後，Excel會將修訂動作記錄下來，但不是所有的修訂都會被記錄下來，例如：在活頁簿中進行格式化動作，就不會被記錄下來。 保留修訂記錄：此活頁簿的所有者，可以設定此共用活頁簿要保留修訂記錄的狀態。

37 7-2 共用活頁簿 取消活頁簿之共用 1. 切換至此「編輯」索引標籤 2. 取消此項的勾選

38 7-3 追蹤修訂 「追蹤修訂」功能，可標示出哪些人曾經對此共用活頁簿進行過修訂、什麼時候修訂過及修訂內容。

39 7-3 追蹤修訂 標示共用活頁簿之修訂處 1

40 7-3 追蹤修訂 標示共用活頁簿之修訂處 1 2 將滑鼠指標移至修訂地方時，就會顯示出修訂者、修訂時間及修訂內容

41 7-3 追蹤修訂 接受或拒絕修訂 1

42 7-3 追蹤修訂 接受或拒絕修訂 勾選此項並選擇「尚未被檢閱過」 2 3 顯示出修訂內容！使用者可選擇接受或拒絕修訂！

43 7-3 追蹤修訂 接受或拒絕修訂 「接受」鈕：表示接受此筆修訂資料，將會保留此筆修訂資料。
「拒絕」鈕：表示不接受此筆修訂資料，此修訂記錄則會被取消。 「接受所有修訂」鈕：會將所有的修訂記錄保留下來，並自動關閉此修訂記錄對話視窗。 「拒絕所有修訂」鈕：會將所有的修訂紀錄取消，並自動關閉此修訂記錄對話視窗。 若按下「接受」鈕或「拒絕」鈕，將會一筆一筆讓使用者選擇修訂記錄，直到所有修訂記錄處理完畢為止。

44 7-3 追蹤修訂 解決修訂衝突 1 切換至此索引標籤 此為預設值

45 7-3 追蹤修訂 解決修訂衝突 2 按此鈕將選擇使用者自己的修訂 按此鈕則以別人的修訂為主

46 本章結束 Q&A討論時間