孫煒

我們在上一篇《手把手教你做好工資表（中）》中解決了自動生成應(yīng)發(fā)工資表問題，并留下了最后一個問題，即在員工存在異動的情況下，如何讓工資表智能地抓取“發(fā)工資當期”員工的最新數(shù)據(jù)的問題，我們在這篇文章中做一個詳細的說明。

員工異動信息表的設(shè)計與維護

此處我們需要先回顧一個知識點：在前文中提到我們在管理員工信息時，需要維護三個數(shù)據(jù)表：員工個人信息表、員工企業(yè)信息表、員工異動信息表。我們再來看一下這三個表的作用：

員工個人信息表：記錄員工的學歷、地址、聯(lián)系方式等信息，在我們進行工資表計算過程中除了有特殊補貼（如學歷補貼等）之外，一般用不到此表。

員工企業(yè)信息表：記錄了員工剛?cè)肼殨r的部門、職務(wù)、薪酬、入職時間等狀況，后期我們把離職時間也補充在該表內(nèi)，它更像是原始數(shù)據(jù)，是靜態(tài)的。我們用它來篩選解決“自動生成工資表名單”的問題。

員工異動信息表：持續(xù)記錄員工每一次的異動信息，包括轉(zhuǎn)正、晉升、調(diào)崗、調(diào)薪、降職等等動作帶來的變化，我們需要在其中找到距離當期最近一次的員工數(shù)據(jù)來計算工資才是準確的。比如一個員工2月入職，5月轉(zhuǎn)正，7月調(diào)崗，我們計算8月的工資表時，就必須抓取到該員工7月的數(shù)據(jù)；而我們做6月份工資表的時候，就必須使用5月份的數(shù)據(jù)。在此有些同行可能會感到奇怪，身處8月份我只需要做7月的工資表，6月份的工資表已經(jīng)在上個月結(jié)束了，我為什么還要關(guān)心過去的工資表呢？在這里我說明一下：智能工資表是可以生成（包括還原）任意一個月的應(yīng)發(fā)工資基本表的，既可正向也可逆向，這就意味著它具備強大的模擬功能，只有這樣才能具備支持企業(yè)人力成本預(yù)算的可能性。

做好員工異動信息表的設(shè)計和維護

員工異動信息表字段和順序應(yīng)該與員工企業(yè)信息表一致，并在末尾增加“異動性質(zhì)”和“異動時間”兩個字段（圖1）。

員工的每一次異動應(yīng)單獨記錄，不得刪掉或在之前的異動信息基礎(chǔ)上修改，錄入數(shù)據(jù)時必須填寫異動性質(zhì)和異動時間。

員工的異動性質(zhì)一般包括轉(zhuǎn)正、晉升、調(diào)崗、調(diào)薪、降職等，可根據(jù)自身企業(yè)狀況進行設(shè)定。

在錄入員工異動信息表時，只需填寫異動后該員工的最新數(shù)據(jù)信息（比如新的部門、職級、工資、補貼等信息），同時務(wù)必將員工的工號填寫正確，否則將嚴重影響到后面的計算。

自動判斷抓取數(shù)據(jù)的計算過程

實際工作中，應(yīng)發(fā)工資基礎(chǔ)表、員工企業(yè)信息表、員工異動信息表這三個表應(yīng)該在三個Sheet中建立，但是為了便于本內(nèi)容的講解，我把這三個表集中在了一個頁面上進行講解（圖2），以便于讀者了解其中的邏輯關(guān)系，希望大家在明白它們之間的關(guān)聯(lián)后，根據(jù)自己的具體表格情況進行跨頁應(yīng)用。

大家可以看到，我們在上一篇文章中解決了應(yīng)發(fā)工資名單自動生成的問題，現(xiàn)在我們需要解決上圖中紅色線框中抓取數(shù)據(jù)的問題。在這個例子中我們可以看到這樣一個具體的情況：

員工：何曉亮 工號：S004

1.于2017年5月2日入職，任初級經(jīng)理，基本工資3500元

2.于2017年6月1日提前轉(zhuǎn)正，任中級經(jīng)理，基本工資4000元

3.于2017年8月1日獲得晉升，任高級經(jīng)理，基本工資5000元

目前需制作2017年8月的工資表，計算步驟如下：

第一步，給員工企業(yè)信息表及異動表區(qū)域命名（利用名稱管理器手動起名功能，如圖3，圖4所示的操作步驟）。

B4單元格：取值ID

目的和意義：將員工工號引用至B4，使三個表格的取值ID位置相同

計算公式：B4=F4，向下復(fù)制單元格。

F19單元格：

目的和意義：控制工資表計算的時間，只填寫每月1日的具體時間

計算公式：無。手動錄入具體時間，如需計算7月，則填入：2017-7-1。

D13單元格：有效判斷

目的和意義：在工資表計算時間之前產(chǎn)生的異動才是對工資計算有效的數(shù)據(jù)，利用公式將其篩選出來，符合條件的值為1，否則為0。

計算公式：

D13=（N13<=$F$19）*（F13<>0），向下復(fù)制公式。

C13單元格：取值判斷

目的和意義：在工資表計算時間之前可能產(chǎn)生多次異動，需要找出最近的一次異動數(shù)據(jù)，它需要滿足三個條件：（1）同一個員工；（2）異動時間滿足計算時間條件；（3）離當期最近的一次數(shù)據(jù)記錄。符合條件的值為1，否則為0。

計算公式：

C13={IF（F13=""，""，--（N13=MAX（（$G$13：$G$17=G13）*（$D$13：$D$17=1）*$N$13：$N$17）））}，此處為數(shù)組公式，寫完后同時按住shift+ctr再回車，公式外側(cè)即可自行出現(xiàn){ }符號。向下復(fù)制公式。

B13單元格：取值ID

目的和意義：將員工的工號與C列的判斷結(jié)果組合起來，形成每個員工唯一的取值ID，以便于在最后一步中工資基礎(chǔ)表使用VLOOKUP抓取數(shù)據(jù)。

計算公式：B13 =IF（C13=1，F(xiàn)13&"-"&C13，""），向下復(fù)制公式，不符合條件的值為空。

D22單元格：取值判斷

目的和意義：判斷員工在異動表中是否存在有效的異動信息，如果有顯示1。

計算公式：

D22=--（COUNTIF（$F$13：$F$17，F(xiàn)22）>0），向下復(fù)制公式。

C22單元格：取值區(qū)域

目的和意義：如果D列的值為1，計算結(jié)果為“異動表”，意味著要去異動表中抓取數(shù)據(jù)，否則就應(yīng)該在“企業(yè)表”中抓取數(shù)據(jù)，這里的“異動表”和“企業(yè)表”與之前我們給區(qū)域命名的字段一致。

計算公式：C22=IF（D22=0，"企業(yè)表"，"異動表"），向下復(fù)制公式。

B22單元格：取值ID

目的和意義：如果前面的判斷要去異動表中抓取數(shù)據(jù)，則工號和D列合并，形成一個新的ID，否則就直接把工號引用過來。

計算公式：B22 =IF（D22=0，F(xiàn)22，F(xiàn)22&"-"&D22），向下復(fù)制公式。

H19單元格：位置計算

目的和意義：把“部門信息”、“職級信息”這些字段在上述表格中的位置用MATCH計算出來，為下面一次性使用VLOOKUP函數(shù)拓展道路。

計算公式：

H19 =MATCH（H21，$B$3：$N$3，0），向右復(fù)制公式。

H22單元格：抓取數(shù)據(jù)

目的和意義：根據(jù)所需計算的工資表時間，自動從《員工企業(yè)信息表》《員工異動信息表》兩個表內(nèi)抓取準確的員工信息。

計算公式：H22=VLOOKUP（$B22，INDIRECT（$C22），H$19，0），向右復(fù)制公式，向下復(fù)制公式。

這樣我們就基本完成了工資基礎(chǔ)表的智能抓取工作。其實在工作中可能有些同行還會遇見諸如以下的問題：

1.異動按天數(shù)折算問題；

2.時間影響工資是否當期發(fā)放問題；

3.職級影響試用期時長問題等。

這些問題其實都可以在工資表技術(shù)中得到非常妥善的解決，希望大家也能集思廣益提出更好的解決辦法。工資表技術(shù)就先介紹到這里，下一期我們將進行《職級體系》模型技術(shù)的詳細介紹。

責編/寇斌

（完）endprint