1. 實(shí)際需求

公司員工的原始登記表是按照員工入職時(shí)間依次登記的，每個(gè)部門(mén)使用不同的起始編號(hào)（需要保留離職員工的工號(hào)）。現(xiàn)在需要在I 列中計(jì)算起始工號(hào)段，L列中計(jì)算出分配給新員工的工號(hào)，M和N列中統(tǒng)計(jì)離職人數(shù)、在職人數(shù)，然后以此為依據(jù)和O列核準(zhǔn)人數(shù)對(duì)比，并在P列中判斷員工人數(shù)是否超編（圖1）。了解了需求后，下面根據(jù)這些需求使用不同的函數(shù)來(lái)統(tǒng)計(jì)和分析。

2. 分配工號(hào)

由圖1可以知道，由于每個(gè)部門(mén)的起始編號(hào)不同，因此需要先根據(jù)部門(mén)在C、D列中找出該部門(mén)的起始編號(hào)，然后根據(jù)結(jié)束編號(hào)+1，求得入職該部門(mén)的新員工的工號(hào)數(shù)據(jù)。

首先計(jì)算部門(mén)起始編號(hào)的數(shù)據(jù)。定位到J2單元格并輸入公式“=MINIFS（$D$2：$D$99，$C$2：$C$99，H2）”，然后下拉公式到J6單元格，這樣在J列中即可顯示每個(gè)部門(mén)的開(kāi)始編號(hào)（圖2）。

公式解釋?zhuān)?/p>

這里使用MINIFS函數(shù)判斷最小編號(hào)數(shù)值，$D$2：$D$99（絕對(duì)引用）是確定最小值所在的區(qū)域（即每個(gè)部門(mén)的工號(hào)牌數(shù)據(jù)，請(qǐng)根據(jù)實(shí)際情況設(shè)置區(qū)域）;$C$2：$C$99為用于條件的單元格區(qū)域（即與工號(hào)對(duì)應(yīng)的部門(mén)）;H2用于確定最小值的條件（即指定查詢(xún)的具體部門(mén)）。

繼續(xù)定位到K 2單元格并輸入公式“=MA XIFS（$D$2：$D$99，$C$2：$C$99，H2）”。這里使用MA XIFS函數(shù)查詢(xún)對(duì)應(yīng)部門(mén)員工的最大編號(hào)數(shù)值，公式的含義與MI N I FS函數(shù)類(lèi)似，下拉公式后可以將對(duì)應(yīng)部門(mén)的結(jié)束編號(hào)查詢(xún)出來(lái)（圖3）。

知道了每個(gè)部門(mén)的起始編號(hào)后，對(duì)應(yīng)部門(mén)的新員工編號(hào)就是“目前最大編號(hào)+1”，因此定位到L 2單元格并輸入公式“= K 2+1”并下拉即可。員工的號(hào)段數(shù)值則可以定位到I 2單元格，輸入公式“=J2&-K 2”并下拉獲得（圖4）。

在實(shí)際使用中，HR部門(mén)只要在A、B、C列中輸入員工的入職信息，然后根據(jù)L列顯示的信息，按照不同部門(mén)填入分配的工號(hào)數(shù)字即可。填入新員工的信息后，上述的數(shù)據(jù)會(huì)同步更新，后續(xù)員工的數(shù)據(jù)錄入操作類(lèi)似。

3. 統(tǒng)計(jì)離職和

在職人數(shù)在原始數(shù)據(jù)中，離職人員在E列進(jìn)行了備注，因此可以使用COUNTIFS函數(shù)來(lái)統(tǒng)計(jì)。定位到M2單元格并輸入公式“=COUNTIFS（$E$2：$E$ 3 4，"已離職"， C2：C34，H2）”，下拉后即可完成統(tǒng)計(jì)（圖5）。

公式解釋?zhuān)?/p>

這里使用COUNTIFS函數(shù)執(zhí)行條件計(jì)數(shù)，$E$2：$E$34為計(jì)數(shù)區(qū)域1，計(jì)數(shù)條件1為包含“已離職”字符的單元格;計(jì)數(shù)區(qū)域2為$C $2：$C$34（即部門(mén)區(qū)域），計(jì)數(shù)條件2則為H列顯示的部門(mén)。這樣可以根據(jù)部門(mén)計(jì)算出對(duì)應(yīng)的離職員工人數(shù)。

繼續(xù)定位到N2單元格并輸入公式“=COUNTIFS （$C$2：$C$34，H2）-M2”，下拉填充。同樣使用COUNTIFS函數(shù)對(duì)部門(mén)人數(shù)進(jìn)行統(tǒng)計(jì)（統(tǒng)計(jì)該部門(mén)總的人數(shù)），然后將其和離職人數(shù)相減，即為剩余的在職人數(shù)（圖6）。

4. 核編人數(shù)

先在O列中輸入公司規(guī)定的部門(mén)核編人數(shù)，接著在P2單元格中輸入公式“=IF（N2<=O2，"正常"，"超編"）”并下拉填充。這里使用IF函數(shù)對(duì)N列和O列（即在職和核編人數(shù)）進(jìn)行比較，并根據(jù)結(jié)果顯示“正?！边€是“超編”（圖7）。

為了能夠更醒目地顯示超編結(jié)果，選中P列數(shù)據(jù)，依次點(diǎn)擊“開(kāi)始→條件格式→突出顯示單元格規(guī)則→文本包含”，設(shè)置包含“超編”的單元格自動(dòng)填充紅色底紋。

5. 實(shí)際使用

完成上述的操作后，員工信息就一目了然了。由于上述的數(shù)據(jù)都使用了公式引用，這些數(shù)據(jù)會(huì)根據(jù)員工信息的變化同步變化，無(wú)論是錄入新員工編號(hào)、查看離職和在職員工人數(shù)，還是判斷是否超編（如超編需要裁員）都非常清晰（圖8）。