人力資源實(shí)用表格函數(shù)運(yùn)用技巧

1、 1、表格結(jié)構(gòu)設(shè)計(jì)   根據(jù)人力資源中對(duì)員工信息管理和分析的需要，創(chuàng)建一個(gè)工作表，命名為“員工信息”，數(shù)據(jù)列標(biāo)題如下圖。   2、工號(hào)的自動(dòng)連續(xù)填充  在單元格A2輸入公式“="G"&TEXT(ROW(A1),"0000")”，就得到能夠往下連續(xù)填充工號(hào)的計(jì)算公式。這里，ROW函數(shù)是獲取指定單元格的行號(hào)（ROW(A1)的結(jié)果就是1，ROW(A2)的結(jié)果就是2，以此類(lèi)推），TEXT函數(shù)是把一個(gè)數(shù)字按照指定的格式轉(zhuǎn)換為文本。那么這個(gè)公式的結(jié)果就是G0001。如果

2、把該公式復(fù)制到單元格A3，該公式就變成了“="G"&TEXT(ROW(A2),"0000")”，結(jié)果就是G0002。   3、規(guī)范姓名的輸入，不允許在姓名文字中輸入空格  選中單元格B2，設(shè)置有效性，其有效性的自定義公式為“=SUBSTITUTE(B2," ","")=B2”，如下圖。這里，先使用SUBSTITUTE函數(shù)把輸入的姓名中的所有空格替換掉，然后再跟輸入的姓名進(jìn)行比較，如果兩者相等，表明輸入的姓名中沒(méi)有空格，否則就是有空格，就不允許輸入到

3、單元格。   4、規(guī)范快速輸入部門(mén)名稱  公司部門(mén)是確定的，在一定時(shí)期是不會(huì)變化的，因此可以使用數(shù)據(jù)有效性來(lái)快速規(guī)范輸入部門(mén)名稱。假如企業(yè)的部門(mén)有：總經(jīng)辦、財(cái)務(wù)部、人力資源部、貿(mào)易部、后勤部、技術(shù)部、生產(chǎn)部、銷(xiāo)售部、信息部、質(zhì)檢部、市場(chǎng)部，那么選擇單元格C2，設(shè)置數(shù)據(jù)有效性，即選擇“序列”，來(lái)源為“總經(jīng)辦,財(cái)務(wù)部,人力資源部,貿(mào)易部,后勤部,技術(shù)部,生產(chǎn)部,銷(xiāo)售部,信息部,質(zhì)檢部,市場(chǎng)部”，如下圖。這樣，就為單元格設(shè)置了一個(gè)下拉列表，從下拉列表里快速選擇輸入某個(gè)部門(mén)名稱。   5、快速輸入學(xué)歷名稱 

4、60;員工的學(xué)歷也是固定的幾種。假若是以下幾個(gè)：博士、碩士、本科、大專、中專、高中，那么也可以使用數(shù)據(jù)有效性來(lái)快速規(guī)范輸入學(xué)歷名稱。選擇單元格D2，設(shè)置數(shù)據(jù)有效性，即選擇“序列”，來(lái)源為“博士,碩士,本科,大專,中專,高中”。   6、快速輸入婚姻狀況  單元格E2輸入員工的婚姻狀況?；橐鰻顩r也就兩種數(shù)據(jù)：已婚和未婚，因此也可以使用數(shù)據(jù)有效性來(lái)控制輸入，也就是在數(shù)據(jù)有效性對(duì)話框的“來(lái)源”中輸入“已婚,未婚”。   7、輸入不重復(fù)的18位身份證號(hào)碼  每個(gè)員工的身份證號(hào)碼是不重復(fù)的，并且必須是18

5、位，因此單元格F2輸入身份證號(hào)碼時(shí)也要使用數(shù)據(jù)有效性來(lái)控制。首先將F列的單元格格式設(shè)置成文本，然后選擇單元格F2，其有效性的自定義公式     =AND(LEN(F2)=18,COUNTIF($F$2:F2,F2)=1)  這里，使用LEN函數(shù)判斷輸入的身份證號(hào)碼是不是18位，即LEN(F2)=18；使用COUNTIF統(tǒng)計(jì)在前面已經(jīng)輸入的身份證號(hào)碼中，即將輸入的身份證號(hào)碼是不是還沒(méi)有輸過(guò)，即COUNTIF($F$2:F2,F2)=1；然后用AND函數(shù)把這兩個(gè)條件組合起來(lái)。如果兩個(gè)條件都成立，表明輸入的身份證號(hào)碼有效。

6、0;  8、自動(dòng)輸入性別  員工性別從身份證號(hào)碼中自動(dòng)提取，不需要人工輸入。選擇單元格G2，輸入公式     =IF(ISEVEN(MID(F2,17,1),"女","男")  這里，先用MID函數(shù)提取身份證號(hào)碼的第17位數(shù)字，再用ISEVEN函數(shù)判斷是否為偶數(shù)，如果是偶數(shù)，該員工性別就是女，否則就是男，判斷處理則使用了IF函數(shù)。   9、自動(dòng)輸入出生日期  員工的出生日期也是從身份證號(hào)碼中自動(dòng)提

7、取，不需要人工輸入。選擇單元格H2，輸入公式     =1*TEXT(MID(F2,7,8),"0000-00-00")  這里，先用MID函數(shù)提取身份證號(hào)碼的中間8位生日數(shù)字，再用TEXT函數(shù)把這8位數(shù)字按照日期的格式轉(zhuǎn)換成文本型日期格式，最后把TEXT函數(shù)的結(jié)果乘以數(shù)字1，將文本型日期轉(zhuǎn)換為真正的日期。   10、自動(dòng)計(jì)算年齡  有了出生日期，我們就可以使用DATEDIF函數(shù)自動(dòng)計(jì)算年齡。選擇單元格I2，輸入下面的公式，就自動(dòng)得到員工的實(shí)際年齡：

8、60;    =DATEDIF(H2,TODAY(),"Y")   11、規(guī)范輸入入職時(shí)間  入職時(shí)間是一個(gè)非常重要的數(shù)據(jù)，因?yàn)橐鶕?jù)這列日期計(jì)算工齡，分析流動(dòng)性。由于這列日期要手工輸入，就必須規(guī)范輸入的入職時(shí)間數(shù)據(jù)合法有效，也就是要輸入正確格式的日期。選擇J2單元格，設(shè)置數(shù)據(jù)有效性，如下圖所示   12、自動(dòng)計(jì)算本公司工齡  有了入職時(shí)間，我們就可以使用DATEDIF函數(shù)自動(dòng)計(jì)算本公司工齡。選擇單元格K2，輸入下面的公式，就自動(dòng)得到員

9、工的本公司工齡：     =DATEDIF(J2,TODAY(),"Y")   13、保證員工基本信息的完整性  由于B列至K列是員工的最基本信息，是不能缺少這些數(shù)據(jù)的，因此需要保證每個(gè)員工基本信息完整不缺。選擇B2單元格，把數(shù)據(jù)有效性的條件修改為     =AND(SUBSTITUTE(B2," ","")=B2,COUNTA($B1:$K1)=10)  

10、也就是增加了一個(gè)條件COUNTA($B1:$K1)=10，它用來(lái)判斷上一行的B列至K列的數(shù)據(jù)是否都完整了（共有10列數(shù)據(jù)）   14、規(guī)范輸入離職時(shí)間  離職時(shí)間是一個(gè)非常重要的數(shù)據(jù)，因?yàn)橐鶕?jù)這列日期來(lái)分析離職。由于這列日期要手工輸入，就必須規(guī)范輸入的離職時(shí)間數(shù)據(jù)，也就是要輸入正確格式的日期。選擇L2單元格，設(shè)置日期數(shù)據(jù)的有效性。   15、規(guī)范輸入離職原因  離職原因用來(lái)分析員工的流動(dòng)性和離職狀態(tài)，因此必須規(guī)范離職原因的表述文字。假如企業(yè)對(duì)離職原因的描述是下述的文字：  

11、合同到期但個(gè)人不愿續(xù)簽  合同到期但公司不愿續(xù)簽  因個(gè)人原因辭職  因公司原因辭職  違反公司規(guī)定辭退  生產(chǎn)任務(wù)變化辭退  考核不合要求辭退  退休  死亡  其他  那么就可以使用有效性來(lái)快速準(zhǔn)確輸入這些描述文字。   16、創(chuàng)建表格，自動(dòng)復(fù)制有效性和公式，并自動(dòng)美化表格  前面我們做的數(shù)據(jù)有效性、設(shè)置公式等，都是僅僅設(shè)置了第2行的單元格，并沒(méi)有選擇整列來(lái)做，因?yàn)檫@樣的話會(huì)使文件變得很大，復(fù)制很多公式也是很不方便。Excel提供了表格功能，不僅可以自動(dòng)復(fù)制有效性和公式，還可以自動(dòng)美化表格。  先在第1行輸入第一個(gè)人的信息，然后單擊數(shù)據(jù)區(qū)域的任意單元格，在單擊“插入”選項(xiàng)卡里的“表”命令（Excel 2007）或“表格”命令（Excel 2010），如圖2-20所示，即可把普通的數(shù)據(jù)區(qū)域變成了一個(gè)表格，然后再在“設(shè)計(jì)”選項(xiàng)卡中的“表格樣式”選擇一個(gè)自己喜歡的樣式，就可以把表格自動(dòng)美化。  這樣，我們就得到了一個(gè)