Excel在人力資源管理中的應(yīng)用用好工具，高效工作現(xiàn)在是1頁\一共有43頁\編輯于星期日數(shù)據(jù)錄入單元格的引用數(shù)據(jù)有效性條件格式認(rèn)識(shí)Excel函數(shù)Excel函數(shù)的結(jié)構(gòu)常見的幾類函數(shù)應(yīng)用實(shí)例認(rèn)識(shí)數(shù)據(jù)透視表數(shù)據(jù)透視表是什么數(shù)據(jù)透視表的幾個(gè)簡單應(yīng)用實(shí)例課程大綱現(xiàn)在是2頁\一共有43頁\編輯于星期日1-1單元格的引用

1.相對(duì)引用

相對(duì)引用指公式中的單元格位置將隨著公式單元格的位置而改變2.絕對(duì)引用

絕對(duì)引用是指公式和函數(shù)中的位置是固定不變的.

絕對(duì)引用是在列字母和行數(shù)字之前都加上美元符號(hào)”$”,如$A$4,$C$63.混合引用GO混合引用是指在一個(gè)單元格引用中,既有絕對(duì)引用,也有相對(duì)引用.例:行變列不變:$A4列變行不變:A$4

現(xiàn)在是3頁\一共有43頁\編輯于星期日1-2數(shù)據(jù)的有效性控制單元格輸入文本長度GO選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---允許---文本長度可防止身份證/銀行卡號(hào)/手機(jī)號(hào)碼輸入多一位或者少一位數(shù)序列選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---序列---在“來源”輸入（A,B,C,D)注意逗號(hào)要半角格式禁止輸入重復(fù)數(shù)據(jù)

=countif(區(qū)域,起始單元格)=1選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---允許（自定義)---公式填寫=COUNTIF(A:A,A1)=1現(xiàn)在是4頁\一共有43頁\編輯于星期日1-2數(shù)據(jù)的有效性如何取消對(duì)于“數(shù)據(jù)有效性”的設(shè)置？選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---允許---任何值GO現(xiàn)在是5頁\一共有43頁\編輯于星期日1-3條件格式利用突出的格式，經(jīng)常能起到提醒的功能如：突出不滿勤的人GO

突出年齡大于30的人等如何取消條件格式？現(xiàn)在是6頁\一共有43頁\編輯于星期日認(rèn)識(shí)Excel函數(shù)現(xiàn)在是7頁\一共有43頁\編輯于星期日2-1EXCEL函數(shù)的結(jié)構(gòu)Excel函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。Excel函數(shù)結(jié)構(gòu):也有一些函數(shù)是沒有參數(shù)的,如NOW()左右括號(hào)成對(duì)出現(xiàn)單一結(jié)構(gòu)嵌套結(jié)構(gòu)參數(shù)與參數(shù)之間使用半角逗號(hào)進(jìn)行分隔現(xiàn)在是8頁\一共有43頁\編輯于星期日

現(xiàn)在是9頁\一共有43頁\編輯于星期日應(yīng)用實(shí)例從身份證號(hào)碼提取出生日期

在C2單元格中輸入=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)

公式解析：

MID(B2,7,4)表示提取B2單元格中從第7位開始連續(xù)4位的字符，即出生年；

MID(B2,11,2)表示提取B2單元格中從第11位開始連續(xù)2位的字符，即出生月；MID(B2,11,2)表示提取B2單元格中從第13位開始連續(xù)2位的字符，即出生日；在公式中加入&可以用來連接兩個(gè)文本字符串。

現(xiàn)在是10頁\一共有43頁\編輯于星期日

現(xiàn)在是11頁\一共有43頁\編輯于星期日常用函數(shù)之查找類函數(shù)函數(shù)VOOKUP=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)(以從8月工資表導(dǎo)入考勤到8月考核表為例）GO現(xiàn)在是12頁\一共有43頁\編輯于星期日常用函數(shù)之查找類函數(shù)函數(shù)VOOKUP選擇區(qū)域里最好不要有合并的單元格（先“取消單元格合并”）如果單元格里有空格，有可能導(dǎo)不出來

（提示：函數(shù)TRIM（A1)可以去除字符串首部和尾部的空格）注意重復(fù)的名字查找區(qū)域首列必須含有查找的內(nèi)容（第一個(gè)參數(shù)）介紹vookup函數(shù)與IFERROR函數(shù)組合使用(以從8月工資表導(dǎo)入考勤到8月考核表為例）GO=IFERROR(VLOOKUP(A4,'8月工資'!B:F,5,0),0)

如果VOOKUP函數(shù)運(yùn)行后找不到對(duì)應(yīng)的數(shù)據(jù)，就會(huì)顯示為#N/A，IFERROR的作用就是把這些#N/A都變?yōu)榈?個(gè)參數(shù)?，F(xiàn)在是13頁\一共有43頁\編輯于星期日

現(xiàn)在是14頁\一共有43頁\編輯于星期日

現(xiàn)在是15頁\一共有43頁\編輯于星期日應(yīng)用實(shí)例在C1輸入=A1&"+"&B1如何根據(jù)A1得出B1和C1？在B1輸入=LEFT(A1,FIND("+",A1)-1)分析：思路是從左邊開始，截取“+”號(hào)前的位數(shù)。在C1輸入=RIGHT(A1,LEN(A1)-FIND("+",A1))分析：思路是截取“+”號(hào)后的位數(shù)。如何根據(jù)A1、B1得出C1？現(xiàn)在是16頁\一共有43頁\編輯于星期日

現(xiàn)在是17頁\一共有43頁\編輯于星期日IF函數(shù)的應(yīng)用實(shí)例區(qū)域段自動(dòng)算出對(duì)應(yīng)等級(jí)=IF(K4>=90,"A",IF(K4>=80,"B",IF(K4>=70,"C","D")))嵌套了兩個(gè)IF函數(shù)現(xiàn)在是18頁\一共有43頁\編輯于星期日

現(xiàn)在是19頁\一共有43頁\編輯于星期日應(yīng)用實(shí)例從身份證號(hào)碼中提取性別

（身份證倒數(shù)第2位若為偶數(shù)為女性，奇數(shù)為男性）

在C2單元格中輸入=IF(MOD(MID(B2,17,1),2)=1,"男","女")公式分析：

MID(B2,17,1)是從身份證第17位起取1位字符；在本例即為數(shù)字5，MOD函數(shù)就是取余數(shù)的函數(shù)，在本例中就是MOD(5，2),5除以2的余數(shù)，最外面是一個(gè)IF函數(shù)，當(dāng)這個(gè)余數(shù)為1時(shí)，即為奇數(shù)，奇數(shù)就是男性，否則為女性。

現(xiàn)在是20頁\一共有43頁\編輯于星期日

現(xiàn)在是21頁\一共有43頁\編輯于星期日

現(xiàn)在是22頁\一共有43頁\編輯于星期日常用函數(shù)之日期類函數(shù)DATEDIF函數(shù)常用于計(jì)算兩日期的時(shí)間差=DATEDIF(較早的日期，較晚的日期，第三個(gè)參數(shù)）第三個(gè)參數(shù)：“Y”---兩日期間共多少年?！癕”----兩日期間共多少個(gè)月?！癉”----兩日期間共多少天?！癕D”是兩日期中天數(shù)的差。忽略日期中的月和年?！癥M”是兩日期中月數(shù)的差。忽略日期中的年。“YD”是兩日期中天數(shù)的差。忽略日期中的年?，F(xiàn)在是23頁\一共有43頁\編輯于星期日常用函數(shù)之日期類函數(shù)舉例:算兩個(gè)日期間差幾年、幾月、幾日開始時(shí)間是2012-2-21結(jié)束時(shí)間是2014-2-19C2=DATEDIF(A2,B2,"Y")D2=DATEDIF(A2,B2,"YM")E2=DATEDIF(A2,B2,"MD")F2=DATEDIF(A2,B2,"D")G2=DATEDIF(A2,B2,"M")H2=DATEDIF(A2,B2,“YD")I2=C2&"年"&D2&"個(gè)月"I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“個(gè)月"現(xiàn)在是24頁\一共有43頁\編輯于星期日常用函數(shù)之日期類函數(shù)應(yīng)用實(shí)例3從出生日得出年齡

在D2單元格中輸入=DATEDIF(C2,NOW(),“y”)

因?yàn)閺纳矸葑C提取出生日的C2=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)所以若直接從身份證得出年齡D2=DATEDIF(MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2),NOW(),"y")

現(xiàn)在是25頁\一共有43頁\編輯于星期日應(yīng)用實(shí)例培訓(xùn)協(xié)議服務(wù)期限完成提醒思路：過期了可以顯示“協(xié)議期限完成”沒過期的，顯示“沒過期”=IFERROR(IF(DATEDIF(B2,TODAY(),"d")>=0,"協(xié)議期限完成",),"沒過期")分析：DATEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在B2之前，就會(huì)產(chǎn)生錯(cuò)誤值，如果“今天”在B2之后，這個(gè)函數(shù)就會(huì)算出B2距離“今天”有多少天。當(dāng)這個(gè)天數(shù)≥0時(shí)，就會(huì)執(zhí)行IF函數(shù)，顯示“協(xié)議期限完成”，當(dāng)DATEDIF運(yùn)行是一個(gè)錯(cuò)誤值時(shí)，這時(shí)候就執(zhí)行IFERROR函數(shù)，顯示“沒過期”。如果我想到期前一個(gè)月就提醒呢？現(xiàn)在是26頁\一共有43頁\編輯于星期日思路：=IFERROR(IF(DATEDIF(TODAY(),B2,"D")<=31,"少于31天后就會(huì)過期","多于31天"),"已過期")DATEDIF函數(shù)能運(yùn)行，說明B2晚于今天能執(zhí)行IF函數(shù)當(dāng)B2晚于今天≤31天顯示“少于31天后就會(huì)過期”當(dāng)B2晚于今天＞31天顯示“多于31天”不能運(yùn)行，B2早于今天（培訓(xùn)期限過期了）DATEDIF函數(shù)會(huì)產(chǎn)生錯(cuò)誤值IF函數(shù)也會(huì)產(chǎn)生錯(cuò)誤值執(zhí)行IFERROR函數(shù)顯示“已過期”現(xiàn)在是27頁\一共有43頁\編輯于星期日

現(xiàn)在是28頁\一共有43頁\編輯于星期日常用函數(shù)之計(jì)數(shù)類函數(shù)COUNTIF函數(shù)

COUNTIF(range,criteria)

區(qū)域條件標(biāo)準(zhǔn)是一個(gè)數(shù)數(shù)的函數(shù)，可以用于數(shù)出參數(shù)1區(qū)域內(nèi)，符合參數(shù)2的條件的單元格個(gè)數(shù)。如：=countif(F3:F10,“女”)

統(tǒng)計(jì)F3:F10中女的個(gè)數(shù)。

現(xiàn)在是29頁\一共有43頁\編輯于星期日COUNTIF函數(shù)的應(yīng)用實(shí)例應(yīng)用實(shí)例在9月流動(dòng)表中找出有相同名字的人步驟1：打開9月流動(dòng)表，找出9月在職的工作表，在姓名右邊新建一列，插入函數(shù)，找到COUNTIF函數(shù)，選擇“確定”?，F(xiàn)在是30頁\一共有43頁\編輯于星期日COUNTIF函數(shù)的應(yīng)用實(shí)例步驟2：點(diǎn)擊Range的框格，選中E列，點(diǎn)擊Criteria的框格，點(diǎn)擊E2單元格，點(diǎn)擊“確定”。步驟3：點(diǎn)擊F2單元格，把鼠標(biāo)移到F2右下角，鼠標(biāo)箭頭變?yōu)楹谏值臅r(shí)候雙擊。這個(gè)的意思就是在9月在職的人中只有一個(gè)叫嚴(yán)邦平的現(xiàn)在是31頁\一共有43頁\編輯于星期日COUNTIF函數(shù)的應(yīng)用實(shí)例步驟4：選中F列---排序與篩序---篩選---點(diǎn)擊F1左上角小箭頭---去掉1前的小勾---確定GO現(xiàn)在是32頁\一共有43頁\編輯于星期日COUNTIF函數(shù)的應(yīng)用實(shí)例統(tǒng)計(jì)一定區(qū)間的個(gè)數(shù)如：統(tǒng)計(jì)9月流動(dòng)表“9月在職”工作表中，≥50歲以上的人數(shù)：=COUNTIF(F:F,">=50")20歲≤年齡＜40歲的人數(shù)：=COUNTIF(F:F,">=20")-COUNTIF(F:F,">=40")（注意：如果要統(tǒng)計(jì)的數(shù)據(jù)是有一個(gè)綠色的左角的，

要先選擇要統(tǒng)計(jì)的數(shù)據(jù)，點(diǎn)擊符號(hào)，選擇

“轉(zhuǎn)換為數(shù)字”，不然是統(tǒng)計(jì)不了個(gè)數(shù)的）GO現(xiàn)在是33頁\一共有43頁\編輯于星期日認(rèn)識(shí)數(shù)據(jù)透視表現(xiàn)在是34頁\一共有43頁\編輯于星期日認(rèn)識(shí)數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互，交叉制作的報(bào)表，一組具有多個(gè)字段的數(shù)據(jù)進(jìn)行多立體的分析匯總，用于對(duì)多種來源的數(shù)據(jù)進(jìn)行匯總和分析，從而可以快速合并和比較大量數(shù)據(jù)。當(dāng)數(shù)據(jù)規(guī)模比較大時(shí)，這種分析的意義就顯得尤為突出。說人話一種快速分析數(shù)據(jù)的工具現(xiàn)在是35頁\一共有43頁\編輯于星期日前提這些數(shù)據(jù)使用數(shù)段表進(jìn)行管理現(xiàn)在是36頁\一共有43頁\編輯于星期日統(tǒng)計(jì)9月入職的招聘來源分布統(tǒng)計(jì)離職原因分布個(gè)數(shù)統(tǒng)計(jì)各部門平均工資數(shù)據(jù)透視表可以快速解決這些事情統(tǒng)計(jì)各部門學(xué)歷分布情況認(rèn)識(shí)數(shù)據(jù)透視表現(xiàn)在是37頁\一共有43頁\編輯于星期日統(tǒng)計(jì)9月入職的招聘來源分布步驟1：打開工作表---選擇數(shù)據(jù)來源區(qū)域（A1：i79）---插入---數(shù)據(jù)透視表---確定統(tǒng)計(jì)9月入職的招聘來源分布現(xiàn)在是38頁\一共有43頁\編輯于星期日步驟2：把招聘來源拖到下面的“行標(biāo)簽”框，把序號(hào)拖到下面的“數(shù)值”框，就能快速生成下圖?，F(xiàn)在是39頁\一共有43頁\編輯于星期日統(tǒng)計(jì)離職原因分布個(gè)數(shù)同理，可根據(jù)前面介紹的步驟在辭工名單的表格中快速做出離職原因分布的表格GO拖“離職原因”到“行標(biāo)簽”框，拖“序號(hào)”到“數(shù)值”框注意：要留意“數(shù)值”框，是求和，還是計(jì)數(shù)，還是其他，左圖的默認(rèn)是“求和”，而