[[397966]]

剛入職場的小伙伴，很多時候都要面臨業務與環境的雙重考驗。如何在眾人間脫穎而出，成為夜空中最亮的那個仔?今天這篇文章，將帶您快速一覽人事崗必學的幾招Excel函數。難度不高，卻超級超級實用!

1. 身份證號提取性別　　

公式：= IF(MOD(MID(B2,17,1),2)=1,"男","女")

首先通過MID()函數提取身份證號中的第17位，然后通過MOD()函數判斷奇偶性，其中奇數為男，偶數為女。最終在IF()函數的配合下，即可得到性別值。

身份證號自動生成性別

2. 身份證號提取生日　　

公式：=TEXT(MID(B2,7,8),"0000-00-00")

首先通過MID()函數提取身份證號中的第7-14位，然后使用TEXT()函數對結果格式化(即轉換成“xxxx-xx-xx”的樣式)，最終便得到了我們平時習慣的生日信息。

身份證號自動提取生日

注：身份證號碼中第7-14位為出生日期。

3. 計算年齡、司齡　　

公式：=YEAR(TODAY())-MID(B2,7,4)

首先通過TODAY()函數獲得當前日期，然后使用YEAR()函數提取日期的年份值。接下來通過MID()函數獲取員工出生年份，兩者之差即為年齡。類似的方法也可用在司齡計算上，只要將生日替換成入職日期即可。

快速計算年齡、司齡

4. 防止身份證號多輸少輸　　

做法：通過“數據有效性”限定單元格長度

選中身份證號列，點擊“數據”→“數據有效性”，將“文本長度”指定為“18”位。當錄入的身份證號碼超過或不足18位時，Excel便會自動提醒，以降低前端錄入可能導致的錯誤。

通過數據有效性限定身份證列位數

注：通過將“數據有效性”→“出錯警告”中的樣式修改為“警告”，可以實現只提醒不禁止效果。

5. 手機號碼自動加“-”　　

做法：通過“單元格格式”，修改顯示格式

右擊手機號碼字段，按下Ctrl+1進入單元格格式面板，點擊最下方“自定義”，輸入“000-0000-0000”。確定之后，手機號碼即以“XXX-XXXX-XXXX”格式顯示。

手機號碼自動加“-”

注：這種方法只是修改了字段顯示樣式，并沒有修改實際數據，因此后續仍然可以進行統計，這也是和Replace()函數最大一個區別。

6. 合同到期前自動提醒　　

公式：=IF(G7-TODAY()<30,"合同應續簽","")

首先建立“狀態”列，輸入公式“=IF(G7-TODAY()<30,"合同應續簽","")”。當合同到期日低于設定值(本例中為“30”)時，便自動顯示“合同應續簽”。此外也可將提醒天數單獨設成單元格，通過公式調取單元格數值，以方便用戶自行修改提醒期限。

合同到期前自動提醒

7. 避免錄入重復值　　

公式：=COUNTIF(B:B,B1)=1

選中數據列，點擊“數據”→“數據工具”→“數據驗證”，將驗證條件改為“自定義”，然后輸入公式“=COUNTIF(B:B,B1)=1”。這里COUNTIF()函數的作用，是判斷B列中是否有重復記錄(如果有則值值一定大于1)，如果有中止錄入，如果沒有繼續錄入。

如果錄入時出現重復數值將禁止錄入

8. 自動標亮周末　　

公式：=WEEKDAY(E$6,2)>5

首先建立星期行，選中考勤表里的數值區域，點擊“開始”→“樣式”→“條件格式”→“新建規則”，然后在選框內輸入公式“=WEEKDAY(E6,2)>5”，最后選中E6，按兩下F4鍵鎖定行不鎖定列(即轉換成E$6)，再設置一個特別的顏色即可。這里WEEKDAY()函數的作用是判斷當前列是否大于5，當WEEKDAY()結果>5時(即周六、周日)，條件成立，表格自動為該列刷上顏色，不成立時不處理，從而最終形成周末自動上色效果。

自動標亮周末

9. 讓表格自動拓展　　

方法：Ctrl+T轉成“超級表”

想讓表格自動擴展，可以利用Excel里的“超級表”功能。具體方法是：選中數據區域，按下Ctrl+T，然后修改“表格工具”→“表格樣式”為“無”(即不使用超級表默認樣式)。右擊隱藏新彈出的篩選行，即可實現表格的自動擴展。

Ctrl+T實現表格自動拓展

10. 隔行換色　　

公式：=MOD(ROW(),2)=0

隔行換色有兩種方法，一是轉換成“超級表”(Ctrl+T)，二是借助公式與條件格式配合完成。以公式法為例，首先選中數據區域，點擊“開始”→“樣式”→“條件格式”→“新建規格”→“使用公式確定要設置格式的單元格”，然后在選框內輸入公式“=MOD(ROW(),2)=0”。這里ROW()函數的作用是獲取當前行號，和2取余后，便得到了1、0、1、0、1、0……這樣的數列。由于公式整體位于條件格式內，因此當行號取余結果=0時，條件成立，表格自動為該行刷上顏色，不成立時不處理，最終形成了隔行換色的效果。

公式+條件格式實現“隔行換色”

注：與傳統的格式刷法相比，超級表和公式法都可以實現周末自動填色。且后期無論如何添刪，都不會導致色條出現混亂。

11. 自動標記遲到、早退　　

公式：=IF(B2>TIME(8,0,0),"遲到","") 和 =IF(C2

首先在表格后建立一個“遲到”列，輸入公式“=IF(B2>TIME(8,0,0),"遲到","")”，再建立一個“早退”列，輸入公式“=IF(C2

自動標記遲到、早退

12. 自動打序號　　

公式：=ROW()-1

首先點擊序號列第一組單元格，輸入公式=ROW()，此時函數返回的是當前行數，根據實際行數計算(比如本文為“1”)，發現兩者差值為“1”。接下來對公式進行修改，將計算后的差值減到公式后方()，填充后即可實現自動打序號效果。

盡管自動打序號已經實現，但此時仍然不夠智能。可以在此基礎上再嵌套一個IF()函數，只有當右側有數據時才會顯示序號，沒有的話直接顯示空白。

自動序列

13. 去除數據有效性列表里的空值　　

公式：=OFFSET($O$6,,,COUNTA($O$6:$O$19))

如果你經常使用“數據有效性”制作下拉列表，就會發現這樣一個尷尬，當我們前期為序列留出大量空白時，下拉列表也會出現大量空白，日常操作很不方便。其實這個問題可以這樣解決，首先點擊“數據”→“數據驗證”→“序列”，在“來源”框中填入公式。這里COUNTA()函數的作用，是求出當前數據源中的有效記錄數，然后通過OFFSET()函數確定取值范圍。由于公式限定了下拉列表的取值域，因此我們會得到一個完全沒有空值的菜單。同時新記錄也將自動添加到列表中，不會影響后續操作。

清除下拉列表里的空值

14. 分級考核統計　　

公式：=LOOKUP(J6,N6:N9,M6:M9)

傳統分級統計是通過COUNT()函數與IF()函數配合計算，雖然簡單，可一旦條件過多，就會導致公式異常復雜。類似情況，其實也能借助LOOKUP()函數解決。

首先建立一個分級副表，左側為等級，右側為達到該等級的考核線。接下來在評級框內輸入公式“=LOOKUP(J6,N6:N9,M6:M9)”，將兩組取值域按F4鍵全部轉為絕對地址。這里LOOKUP()的作用是通過目標值，直接到副表中查找對應等級，相比IF()函數顯得精簡很多。而且這樣處理后的表格，也方便用戶隨時調整考核線。

簡單的分級考核公式

注：使用LOOKUP()函數時，要注意副表考核值必須由小到大排列，否則將導致結果異常。

寫在最后　　

以上這些，是人事工作者每天都要遇到的，很多復雜的操作其實往往來源于日常積累。希望對您的工作有所幫助吧!