用活函數(shù) 員工信息巧管理

1. 實際需求

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

2. 分配工號

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

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

公式解釋：

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

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

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

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

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

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

公式解釋：

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

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

4. 核編人數(shù)

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

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

5. 實際使用

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