手把手教你做好工資表（中）

孫煒

在工資表技術中，需要解決兩個核心的操作問題：

1.如何按照指定的月份，自動生成應發(fā)工資員工名單的問題。

2.如何在員工存在異動（調(diào)崗、調(diào)薪、轉(zhuǎn)正、參保）的情況下，按照指定月份，準確取得員工當期最新數(shù)據(jù)（當期調(diào)整的部門、薪酬、職級等）的問題。

解決這兩個問題，是高效制作工資表的核心問題，我在工資表技術中給出了一個解決方案，希望拋磚引玉，對廣大讀者有所啟發(fā)和幫助。在這篇文章中我們先來談一下第一個問題：如何利用公司的員工信息表，自動獲取指定月份的應發(fā)工資名單：

我在上一篇文章中（詳見本刊2017年7月刊第34至36頁）提到過，如果用EXCEL處理員工信息數(shù)據(jù)，應該有三個表，即“員工個人信息表”、“員工企業(yè)信息表”、“員工異動信息表”。對于制作工資表而言，我們重點利用的是后兩個表的內(nèi)容。我們先來看一下這兩個表在使用過程中的注意事項。

員工企業(yè)信息表的設計與維護

設計員工企業(yè)信息表時，應包含以下內(nèi)容（如下圖所示）：

1.工號、姓名（離職員工的工號原則上不再重復使用）。

2.部門信息：此處可以展開多列，包含分公司、一級部門、二級部門等信息。

3.職級信息：包含了員工的崗位名稱、職位、職位類別、級別等信息。

4.固定薪酬信息：包括了基本工資、崗位工資等非彈性薪酬信息。

5.保險信息：五險一金個人應繳納部分。

6.特殊補貼信息：企業(yè)給予特定員工或崗位的其他固定補貼。

維護注意事項：

1.新增員工直接在末行錄入即可（之后可以按部門信息為關鍵詞排序）。

2.部門信息、職級信息、保險和特殊補貼信息可以依據(jù)企業(yè)具體情況自行設定展開。

3.固定薪酬信息主要指的是基本工資、崗位工資、固定補貼等一些非考核性固定薪酬項目，這些項目一般只與員工考勤狀況相關。

4.一定要詳細記錄入職時間和離職時間信息，錄入的時間格式必須正確。

員工企業(yè)信息表的計算準備

將表格設計好后，增加輔助列（輔助列就是為了下一步計算而增加的，不屬于源數(shù)據(jù)內(nèi)容的列），增加后的效果如下圖所示：

其中H列至L列為判斷計算輔助列，B列為選擇計算輔助列，具體解釋如下：

1.表格中C2與E2存放的是“指定月份”的開始和結(jié)束時間，示例中指定的是2017年7月份這個特定的時間

2.H列“已離職判斷”：該列的作用是判斷出在本期之前就離職的員工。

計算邏輯：離職時間不為空，同時離職時間在指定月份1號之前。

計算公式：H6=IF（AND（G6<>""，G6<$C$2），1，0），向下復制即可。

計算結(jié)果：為1表示期初之前已離職。

3.I列“當期離職判斷”：該列的作用是判斷出在本期之內(nèi)離職的員工。這個輔助列單獨存在有利于以后快速地取得當期離職人員名單。

計算邏輯：離職時間不為空，同時離職時間在指定月份1號之后，月末之前。

計算公式：I6=--AND（G6>=$C$2，G6<=$E$2），向下復制即可。

計算結(jié)果：為 1表示當期離職。

4.J列“當期入職判斷”：該列的作用是判斷出在指定月份內(nèi)入職的員工。這個輔助列單獨存在有利于以后快速地取得當期入職人員名單。

計算邏輯：入職時間在指定月份1號之后，月末之前。

計算公式：J6=--AND（F6>=$C$2，F(xiàn)6<=$E$2），向下復制即可。

計算結(jié)果：為 1表示在指定月份當期在職。

5.K列“在職判斷”：該列的作用是判斷出在指定月份1號之前正常在職的員工（在指定月份1日之前入職并且不存在任何離職行為）。

計算邏輯：離職、當期離職、檔期入職判斷同時為0。

計算公式：K6=--AND（H6=0，I6=0， J6=0），向下復制即可。

計算結(jié)果：為 1表示正常在職。

6.L列“工資表人員判斷”：該列的作用是判斷出在指定月份內(nèi)應發(fā)工資的員工。

計算邏輯：正常在職、當月入職、當月離職的人都應該計算工資。

計算公式：L6=--OR（K6=1，J6=1，I6=1） ，向下復制即可。

計算結(jié)果：為 1表示屬于當期應發(fā)工資員工。

7.B列：該列的作用是取得應發(fā)工資員工名單的“序號”與“數(shù)量”，為下一步使用VLOOKUP生成指定月份應發(fā)工資人員清單提供“ID”。

計算邏輯：利用混合引用技巧，動態(tài)統(tǒng)計到目前為止我是第幾個滿足條件的人。

計算公式：B6=COUNTIF（$L$6：L6，1） ，向下復制即可。

B4= MAX（B6：B10）

計算結(jié)果：B4結(jié)果為應發(fā)工資總?cè)藬?shù)，B6結(jié)果為滿足條件的序號，不滿足條件的員工序號，就會出現(xiàn)重復值（該重復值將被下一步使用VLOOKUP函數(shù)查找引用時忽略）。

生成指定月份的應發(fā)工資員工名單

如上圖所示，左邊就是剛才我們處理的“員工企業(yè)信息表”（此處我們隱藏了H至L列的輔助列）。我們重點來看一下右邊（N至U列）的區(qū)域，重點講解如下：

1.N4單元格。其目的是將符合條件人員總數(shù)引用過來，便于生成新表的序號。

計算公式：N4=B4

2.N7單元格。其目的是根據(jù)總?cè)藬?shù)，自動生成人員序號，比如：本期滿足發(fā)工資的人員一共20人，則序號自動從1開始，到20結(jié)束。后面的均為控制。N6永遠為1，所以不做公式，從N7開始做公式，并向下復制（應根據(jù)企業(yè)人數(shù)多預留一些位置）。

計算公式：N7=IF（N6>=$N$4，""，N6+1），向下復制即可。

3.O6與P6單元格。其目的是使用VLOOKUP函數(shù)，查找和引用“員工序號為1”的員工的工號和姓名，公式向下復制后，逐一找出序號2、3……直至最后一個符合條件員工的工號與姓名（O6是工號，P6是姓名）。

計算公式：O6=IFERROR（VLOOKUP（N6，$B$6：$D$999，2，0），""），向下復制即可。

P6=IFERROR（VLOOKUP（N6，$B$6：$D$999，3，0），""），向下復制即可。

4.取值區(qū)域。根據(jù)已經(jīng)取得的工號，把員工的基礎數(shù)據(jù)引用過來，比如部門、基本工資等等信息。

在維護“員工企業(yè)信息表”的時候，絕大多數(shù)HR錄入的都是員工剛進入公司時的數(shù)據(jù)，它是一個靜態(tài)的數(shù)據(jù)記錄，但隨著時間的推移，員工就會出現(xiàn)類似轉(zhuǎn)正、調(diào)崗、調(diào)薪這樣的正常變化，所以在計算工資的時候，很難甚至不可能從原始記錄（“員工企業(yè)信息表”）中取得當下員工的相關數(shù)據(jù)，我們就需要將“員工異動表”一起考慮進來，綜合地取值。所以在上圖中，“取值區(qū)域”在指定的月份內(nèi)，要先行掃描“異動信息”后才能最終取到正確的值，這個技術我們在下一篇文章中再詳細闡述。 責編/寇斌

（未完待續(xù)）endprint