編按：

對(duì)于HR來(lái)說(shuō)，一張專(zhuān)業(yè)的Excel檔案表，其中包含了很多Excel實(shí)用技巧，也必不可少的會(huì)應(yīng)用到一些函數(shù)和公式。一份足夠?qū)I(yè)的Excel表可以讓HR的日常工作效率提高數(shù)倍，減輕辦公壓力！今天小E和大家介紹的就是這樣一份Excel員工檔案表的制作方法！

員工檔案是用人單位了解員工情況的重要資料。因此一個(gè)企業(yè)在人事管理中，首先要制作員工檔案表，這樣才能提供人員調(diào)動(dòng)和分配的基礎(chǔ)數(shù)據(jù)。

筆者下面做了一張又規(guī)范又省時(shí)的員工檔案表，表中有公式和函數(shù)，也涉及到數(shù)據(jù)有效性。大家一起來(lái)學(xué)習(xí)！

一、規(guī)劃表格框架

在制作員工檔案表時(shí)，首先要?jiǎng)?chuàng)建檔案數(shù)據(jù)的記錄表格，即基礎(chǔ)數(shù)據(jù)表，制作時(shí)注意不要合并單元格。

1.在第一行輸入制表時(shí)間和制表人，在第二行輸入表格各列標(biāo)題。

選擇A1：R1單元格區(qū)域，單擊“開(kāi)始”選項(xiàng)卡，選擇“對(duì)齊方式”組中的“合并后居中”按鈕，在彈出的下拉列表中選擇“合并單元格”選項(xiàng)。選擇A1單元格，把“字體”字號(hào)設(shè)置為10磅，加粗，字體顏色設(shè)置為淡藍(lán)色。

2.設(shè)置表頭。選擇A2：R2單元格區(qū)域，設(shè)置字體字號(hào)為12磅、加粗，設(shè)置字體顏色為白色，為單元格填充藍(lán)色，設(shè)置對(duì)齊方式為居中，調(diào)整第二行單元格的高度至合適。

3.選擇可能輸入員工檔案數(shù)據(jù)的單元格區(qū)域，點(diǎn)擊“開(kāi)始”選項(xiàng)卡，選擇“字體”組中右下角的“對(duì)話(huà)框啟動(dòng)器”，選擇“邊框”，在“顏色”下拉列表中選擇“藍(lán)色”，對(duì)外邊框設(shè)置“粗線(xiàn)”，對(duì)內(nèi)邊框設(shè)置“細(xì)線(xiàn)”。

二、設(shè)置數(shù)據(jù)有效性

在完成表格框架的制作后，需要輸入數(shù)據(jù)，為了保證表格中輸入數(shù)據(jù)的準(zhǔn)確性和統(tǒng)一性，可以為這些有規(guī)律的數(shù)據(jù)分類(lèi)設(shè)置單元格的數(shù)據(jù)有效性。

1.在A3單元格輸入文本數(shù)據(jù)類(lèi)型的第一個(gè)員工編號(hào)0001，向下拖動(dòng)填充柄填充其它員工的編號(hào)數(shù)據(jù)，此時(shí)點(diǎn)擊出現(xiàn)的“自動(dòng)填充選項(xiàng)”按鈕，在彈出的下拉列表中選擇“不帶格式填充”，再繼續(xù)向下填充。

2.選擇“所在部門(mén)”列中D3:D100單元格區(qū)域，選擇“數(shù)據(jù)”選項(xiàng)卡下的“數(shù)據(jù)工具”組中的“數(shù)據(jù)驗(yàn)證”按鈕，在“設(shè)置”選項(xiàng)卡下的“允許”下拉列表中選擇“序列”，在“來(lái)源”參數(shù)框中輸入“總經(jīng)辦,人事部,財(cái)務(wù)部,銷(xiāo)售部,生產(chǎn)部,技術(shù)部,行政辦,市場(chǎng)部”。這樣能夠規(guī)范D列的輸入內(nèi)容。

3.選擇“身份證號(hào)”列中的G3:G100單元格區(qū)域，點(diǎn)擊“數(shù)據(jù)驗(yàn)證”，選擇“設(shè)置”，在“允許”下拉列中選擇“自定義”，在“公式”參數(shù)框中輸入“=LEN(G3)=18”，在“輸入信息”中輸入“請(qǐng)輸入18位的身份證號(hào)碼！”。

4.選擇“最高學(xué)歷”列中的J3:J100單元格區(qū)域，點(diǎn)擊“數(shù)據(jù)驗(yàn)證”，選擇“設(shè)置”，在“允許”下拉列中選擇“序列”，在“來(lái)源”參數(shù)框中輸入“中專(zhuān),大專(zhuān),本科,碩士,碩士以上,高中及以下”。

5.選擇H、L、M三列單元格，設(shè)置為“短日期”；選擇G列單元格，設(shè)置為“文本”；選擇所有包含數(shù)據(jù)的列，設(shè)置為“自動(dòng)調(diào)整列寬”。

三、使用公式返回相關(guān)信息

檔案中部分基礎(chǔ)數(shù)據(jù)存在聯(lián)系，當(dāng)某一信息填入后，另一個(gè)信息即可通過(guò)公式計(jì)算出來(lái)。比如，可以通過(guò)函數(shù)提取身份證號(hào)中的數(shù)據(jù)得到性別、生日、年齡。

1.選擇C3單元格，輸入公式“=IF(MOD(MID(G3,17,1),2)=0,"女","男")”，拖動(dòng)鼠標(biāo)，填充公式，即可判斷該表所有員工的性別。

公式解析：身份證號(hào)的倒數(shù)第二位數(shù)為性別編碼。當(dāng)性別編碼為奇數(shù)時(shí)，代表男性，為偶數(shù)則代表女性。使用MIN函數(shù)截取號(hào)碼中相應(yīng)的位數(shù)，再使用MOD函數(shù)判斷所截取的位數(shù)的奇偶性。

2.選擇H3單元格，輸入公式“=DATE(MID(G3,7,4),MID(G3,11,2),MID(G3,13,2))”，提取員工的出生日期。然后拖動(dòng)鼠標(biāo)，向下填充公式。

公式解析：MIN函數(shù)可以截取出生日期信息，DATE函數(shù)可以將這些信息轉(zhuǎn)變?yōu)槿掌跀?shù)據(jù)。

3.選擇I3單元格，輸入公式“=INT((NOW()-H3)/365)”，可以計(jì)算出員工當(dāng)前的年齡。然后拖動(dòng)鼠標(biāo)，向下填充公式。

公式解析：NOW函數(shù)返回系統(tǒng)當(dāng)前的日期，然后INT函數(shù)對(duì)計(jì)算后的數(shù)據(jù)取整處理。

4.選擇N3單元格，輸入公式“=IF(M3<>"",YEAR(M3)-YEAR(L3),(INT((NOW()-L3)/365)))”，計(jì)算員工工齡。然后拖動(dòng)鼠標(biāo)，向下填充公式。

公式解析：首先使用IF函數(shù)判斷員工是否離職，即M3單元格中是否填入了離職日期；然后通過(guò)YEAR函數(shù)把兩個(gè)日期相減；或者先用NOW函數(shù)返回系統(tǒng)日期，然后使用INT函數(shù)向下取整。

5.QQ號(hào)加上“@qq.com”就是QQ郵箱地址，選擇Q3單元格，輸入公式“=P3&"@qq.com"”，就可得到員工的QQ郵箱地址。拖動(dòng)鼠標(biāo)，填充公式。

四、修飾表格

1.由于數(shù)據(jù)較多，使用凍結(jié)窗格將前兩行和前兩列進(jìn)行凍結(jié)，方便查看表頭與距離表頭較遠(yuǎn)的數(shù)據(jù)的關(guān)系。選擇C3單元格，用鼠標(biāo)點(diǎn)擊“視圖”選項(xiàng)卡下“窗口”組中的“拆分”按鈕，點(diǎn)擊“凍結(jié)窗格”按鈕，在彈出的下拉列表中選擇“凍結(jié)拆分窗格”選項(xiàng)。

2.選擇包含數(shù)據(jù)的A2：R100單元格區(qū)域，用鼠標(biāo)點(diǎn)擊“開(kāi)始”選項(xiàng)卡下“樣式”組中的“套用表格格式”按鈕，選擇需要的樣式。打開(kāi)“套用表格格式”，在表格工具下的設(shè)計(jì)選項(xiàng)中的 “表格樣式選項(xiàng)”組里，取消選中“篩選按鈕”復(fù)選卡，完成表格制作。

OK，創(chuàng)建員工檔案記錄表其實(shí)不難，但這里面涉及到很多知識(shí)點(diǎn)，包括，數(shù)據(jù)有效性、公式、凍結(jié)窗格、套用表格樣式等內(nèi)容，都是大家會(huì)經(jīng)常用到的內(nèi)容，一定要掌握。小伙伴們，還有別的什么想法，歡迎掃描下方二維碼，到公眾號(hào)下留言。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

Excel全自動(dòng)調(diào)查問(wèn)卷表怎么做最炫酷

7個(gè)例子全面掌握數(shù)據(jù)有效性

2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏）-上篇

2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏）-下篇

版權(quán)申明：

本文作者賦春風(fēng)；同時(shí)部落窩教育享有專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。