用活函數 員工信息巧管理

1. 實際需求

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

2. 分配工號

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

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

公式解釋：

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

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

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

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

3. 統計離職和

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

公式解釋：

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

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

4. 核編人數

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

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

5. 實際使用

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