孫煒

我們?cè)谏弦黄妒职咽纸棠阕龊霉べY表（中）》中解決了自動(dòng)生成應(yīng)發(fā)工資表問題，并留下了最后一個(gè)問題，即在員工存在異動(dòng)的情況下，如何讓工資表智能地抓取“發(fā)工資當(dāng)期”員工的最新數(shù)據(jù)的問題，我們?cè)谶@篇文章中做一個(gè)詳細(xì)的說(shuō)明。

員工異動(dòng)信息表的設(shè)計(jì)與維護(hù)

此處我們需要先回顧一個(gè)知識(shí)點(diǎn)：在前文中提到我們?cè)诠芾韱T工信息時(shí)，需要維護(hù)三個(gè)數(shù)據(jù)表：?jiǎn)T工個(gè)人信息表、員工企業(yè)信息表、員工異動(dòng)信息表。我們?cè)賮?lái)看一下這三個(gè)表的作用：

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

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

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

做好員工異動(dòng)信息表的設(shè)計(jì)和維護(hù)

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

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

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

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

自動(dòng)判斷抓取數(shù)據(jù)的計(jì)算過(guò)程

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

大家可以看到，我們?cè)谏弦黄恼轮薪鉀Q了應(yīng)發(fā)工資名單自動(dòng)生成的問題，現(xiàn)在我們需要解決上圖中紅色線框中抓取數(shù)據(jù)的問題。在這個(gè)例子中我們可以看到這樣一個(gè)具體的情況：

員工：何曉亮 工號(hào)：S004

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

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

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

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

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

B4單元格：取值ID

目的和意義：將員工工號(hào)引用至B4，使三個(gè)表格的取值ID位置相同

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

F19單元格：

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

計(jì)算公式：無(wú)。手動(dòng)錄入具體時(shí)間，如需計(jì)算7月，則填入：2017-7-1。

D13單元格：有效判斷

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

計(jì)算公式：

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

C13單元格：取值判斷

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

計(jì)算公式：

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

B13單元格：取值ID

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

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

D22單元格：取值判斷

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

計(jì)算公式：

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

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

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

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

B22單元格：取值ID

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

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

H19單元格：位置計(jì)算

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

計(jì)算公式：

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

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

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

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

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

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

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

3.職級(jí)影響試用期時(shí)長(zhǎng)問題等。

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

責(zé)編/寇斌

（完）endprint