【原】入職HR第一天，新同事因為一張不合格的Excel員工檔案表，氣得老板差點開了他……

Excel教程平臺 2021-07-06

展開全文

每天一點小技能

職場打怪不得慫

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

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

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

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

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

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

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

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

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

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

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

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

微信掃碼進群領(lǐng)取文章練習(xí)課件

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

四、修飾表格

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

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

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

掃一掃添加老師微信

在線咨詢Excel課程