[[397966]]

剛?cè)肼殘龅男』锇?，很多時候都要面臨業(yè)務(wù)與環(huán)境的雙重考驗。如何在眾人間脫穎而出，成為夜空中最亮的那個仔?今天這篇文章，將帶您快速一覽人事崗必學(xué)的幾招Excel函數(shù)。難度不高，卻超級超級實用!

1. 身份證號提取性別　　

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

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

身份證號自動生成性別

2. 身份證號提取生日　　

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

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

身份證號自動提取生日

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

3. 計算年齡、司齡　　

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

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

快速計算年齡、司齡

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

做法：通過“數(shù)據(jù)有效性”限定單元格長度

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

通過數(shù)據(jù)有效性限定身份證列位數(shù)

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

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

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

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

手機號碼自動加“-”

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

6. 合同到期前自動提醒　　

公式：=IF(G7-TODAY()<30,"合同應(yīng)續(xù)簽","")

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

合同到期前自動提醒

7. 避免錄入重復(fù)值　　

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

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

如果錄入時出現(xiàn)重復(fù)數(shù)值將禁止錄入

8. 自動標(biāo)亮周末　　

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

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

自動標(biāo)亮周末

9. 讓表格自動拓展　　

方法：Ctrl+T轉(zhuǎn)成“超級表”

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

Ctrl+T實現(xiàn)表格自動拓展

10. 隔行換色　　

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

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

公式+條件格式實現(xiàn)“隔行換色”

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

11. 自動標(biāo)記遲到、早退　　

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

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

自動標(biāo)記遲到、早退

12. 自動打序號　　

公式：=ROW()-1

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

盡管自動打序號已經(jīng)實現(xiàn)，但此時仍然不夠智能?？梢栽诖嘶A(chǔ)上再嵌套一個IF()函數(shù)，只有當(dāng)右側(cè)有數(shù)據(jù)時才會顯示序號，沒有的話直接顯示空白。

自動序列

13. 去除數(shù)據(jù)有效性列表里的空值　　

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

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

清除下拉列表里的空值

14. 分級考核統(tǒng)計　　

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

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

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

簡單的分級考核公式

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

寫在最后　　

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