[[397966]]

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

1. 身份證號(hào)提取性別　　

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

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

身份證號(hào)自動(dòng)生成性別

2. 身份證號(hào)提取生日　　

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

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

身份證號(hào)自動(dòng)提取生日

注：身份證號(hào)碼中第7-14位為出生日期。

3. 計(jì)算年齡、司齡　　

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

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

快速計(jì)算年齡、司齡

4. 防止身份證號(hào)多輸少輸　　

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

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

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

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

5. 手機(jī)號(hào)碼自動(dòng)加“-”　　

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

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

手機(jī)號(hào)碼自動(dòng)加“-”

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

6. 合同到期前自動(dòng)提醒　　

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

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

合同到期前自動(dòng)提醒

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

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

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

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

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

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

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

自動(dòng)標(biāo)亮周末

9. 讓表格自動(dòng)拓展　　

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

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

Ctrl+T實(shí)現(xiàn)表格自動(dòng)拓展

10. 隔行換色　　

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

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

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

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

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

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

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

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

12. 自動(dòng)打序號(hào)　　

公式：=ROW()-1

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

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

自動(dòng)序列

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

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

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

清除下拉列表里的空值

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

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

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

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

簡(jiǎn)單的分級(jí)考核公式

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

寫(xiě)在最后　　

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