超實用 Excel 函式系列

剛入職場的小夥伴，很多時候都要面臨業務與環境的雙重考驗。如何在眾人間脫穎而出，成為夜空中最亮的那個仔？今天這篇文章，將帶您快速一覽人事崗必學的幾招 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 （）

首先建立“狀態”列，輸入公式“=IF （G7-TODAY （）

▲合同到期前自動提醒

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 （）函式顯得精簡很多。而且這樣處理後的表格，也方便使用者隨時調整考核線。