人事信息數(shù)據(jù)統(tǒng)計(jì)分析第一頁，共八十頁，2022年，8月28日第4章人事信息數(shù)據(jù)統(tǒng)計(jì)分析本章要點(diǎn)：4.1人事信息數(shù)據(jù)表4.2人事數(shù)據(jù)的條件求和計(jì)數(shù)4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對(duì)4.5員工人事信息數(shù)據(jù)查詢表（員工信息卡片）4.6統(tǒng)計(jì)不同年齡段員工信息4.7人力資源月報(bào)動(dòng)態(tài)圖表第二頁，共八十頁，2022年，8月28日第4章人事信息數(shù)據(jù)統(tǒng)計(jì)分析第4章人事信息數(shù)據(jù)統(tǒng)計(jì)分析

4.1人事信息數(shù)據(jù)表

創(chuàng)建人事信息數(shù)據(jù)表

利用數(shù)據(jù)有效性防止工號(hào)重復(fù)輸入

4.1.3身份證號(hào)中提取生日、性別等有效信息

4.1.4應(yīng)用DATEDIF函數(shù)計(jì)算員工年齡

設(shè)置每頁頂端標(biāo)題行和底端標(biāo)題行

美化表格

4.2人事數(shù)據(jù)的條件求和計(jì)數(shù)

人事數(shù)據(jù)的單字段單條件求和計(jì)數(shù)

人事數(shù)據(jù)的單字段多條件求和計(jì)數(shù)

人事數(shù)據(jù)的多字段多條件求和計(jì)數(shù)

DSUM數(shù)據(jù)庫函數(shù)的應(yīng)用

4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平

編制員工學(xué)歷透視表

制作員工學(xué)歷透視圖

4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對(duì)

利用“條件格式”比照核對(duì)兩表格數(shù)據(jù)

利用“數(shù)據(jù)透視表”比照核對(duì)兩表格數(shù)據(jù)

利用VLOOKUP函數(shù)比照核對(duì)兩表數(shù)據(jù)

4.5員工人事信息數(shù)據(jù)查詢表（員工信息卡片）

4.5.1VLOOKUP函數(shù)查詢?nèi)藛T信息

4.5.2美化和打印表格（員工信息卡片）

4.6統(tǒng)計(jì)不同年齡段員工信息

4.6.1應(yīng)用COUNTIF函數(shù)統(tǒng)計(jì)分段信息

4.6.2使用FREQUENCY數(shù)組公式法統(tǒng)計(jì)分段信息

4.7人力資源月報(bào)動(dòng)態(tài)圖表

4.7.1創(chuàng)建數(shù)據(jù)源工作表

4.7.2數(shù)據(jù)分析匯總

4.7.3建立窗體控件

4.7.4對(duì)數(shù)據(jù)自動(dòng)降序排列

4.7.5定義動(dòng)態(tài)數(shù)據(jù)區(qū)域名稱

4.7.6繪制柏拉圖

4.7.7美化圖表區(qū)

第三頁，共八十頁，2022年，8月28日4.1人事信息數(shù)據(jù)表案例背景企業(yè)里由于人員較多且流動(dòng)變化大，因而人力資源部應(yīng)及時(shí)做好人事數(shù)據(jù)的整理、匯總分析等工作，并且這些數(shù)據(jù)常常也是企業(yè)做各項(xiàng)決策的參考依據(jù)，因此處理好人事數(shù)據(jù)的整理工作意義重大。人事信息數(shù)據(jù)表是企業(yè)進(jìn)行人事信息管理的基礎(chǔ)和依據(jù)，因此，人事信息管理表格一定要科學(xué)、準(zhǔn)確、詳細(xì)，并且有利于查找、利用，這樣才能真正輔助企業(yè)管理者進(jìn)行人事信息管理工作。

創(chuàng)建人事信息數(shù)據(jù)表

利用數(shù)據(jù)有效性防止工號(hào)重復(fù)輸入

4.1.3身份證號(hào)中提取生日、性別等有效信息

4.1.4應(yīng)用DATEDIF函數(shù)計(jì)算員工年齡

設(shè)置每頁頂端標(biāo)題行和底端標(biāo)題行

美化表格第四頁，共八十頁，2022年，8月28日4.1人事信息數(shù)據(jù)表最終效果展示

某某有限公司員工人事信息表序號(hào)工號(hào)姓名隸屬部門學(xué)歷身份證號(hào)生日性別計(jì)算年齡（-年-月-日）年齡職稱現(xiàn)任職務(wù)聯(lián)系電話居住地址168胡生產(chǎn)部本科1201051976101200341976-10-12男35年6個(gè)月26天35年工程師部長12345678杭州市某區(qū)某路1號(hào)214徐生產(chǎn)部專科1201021969080331681969-8-3女42年9個(gè)月4天42年助工科員12345679杭州市某區(qū)某路2號(hào)355楊生產(chǎn)部碩士1201021947100111701947-10-1男64年7個(gè)月6天64年無科員12345680杭州市某區(qū)某路3號(hào)4106劉生產(chǎn)部?？?201101959031418291959-3-14女53年1個(gè)月24天53年工程師科員12345681杭州市某區(qū)某路4號(hào)5107李銷售部本科1201026811151711968-11-15男43年5個(gè)月23天43年工程師部長12345682杭州市某區(qū)某路5號(hào)6114林銷售部本科1201021982022811641982-2-28女30年2個(gè)月10天30年助工科員12345683杭州市某區(qū)某路6號(hào)7118童行政部專科1201107505153921975-5-15女36年11個(gè)月23天36年助工部長12345684杭州市某區(qū)某路7號(hào)869王行政部本科1201051983032530361983-3-25男29年1個(gè)月13天29年無科員12345685杭州市某區(qū)某路8號(hào)9236李生產(chǎn)部本科1201051983032530361983-3-25男29年1個(gè)月13天29年工程師部長12345686杭州市某區(qū)某路9號(hào)10237趙生產(chǎn)部?？?201021963012011691963-1-20女49年3個(gè)月18天49年工程師科員12345687杭州市某區(qū)某路10號(hào)11238劉生產(chǎn)部高中1201017702221511977-2-22男35年2個(gè)月16天35年工程師科員12345688杭州市某區(qū)某路11號(hào)12239馬生產(chǎn)部高中1201021974050707261974-5-7女38年0個(gè)月0天38年工程師科員12345689杭州市某區(qū)某路12號(hào)13240胡生產(chǎn)部本科1201011978050207251978-5-2女34年0個(gè)月5天34年助工科員12345690杭州市某區(qū)某路13號(hào)14241林生產(chǎn)部本科1201051972011933181972-1-19男40年3個(gè)月19天40年助工科員12345691杭州市某區(qū)某路14號(hào)15242童生產(chǎn)部碩士1201021958122316291958-12-23女53年4個(gè)月15天53年助工科員12345692杭州市某區(qū)某路15號(hào)第五頁，共八十頁，2022年，8月28日創(chuàng)建人事信息數(shù)據(jù)表人事信息數(shù)據(jù)表包括項(xiàng)目有：姓名、性別、年齡、身份證號(hào)（或社會(huì)保障號(hào)）、學(xué)歷、現(xiàn)任職務(wù)、聯(lián)系電話、E-mail和居住地等有效信息。其中性別、年齡、出生日期等項(xiàng)目均不需要手工錄入，利用函數(shù)計(jì)算即可。第六頁，共八十頁，2022年，8月28日身份證號(hào)中提取生日、性別等有效信息建立人事信息數(shù)據(jù)表時(shí)，性別和出生日期兩個(gè)項(xiàng)目可以不必手工錄入，而是利用函數(shù)通過身份證號(hào)碼來自動(dòng)提取即可。第七頁，共八十頁，2022年，8月28日分析身份證號(hào)碼其實(shí)，身份證號(hào)碼與一個(gè)人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號(hào)碼，其中都保存了相關(guān)的個(gè)人信息。15位身份證號(hào)碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。例如:某員工的身份證號(hào)碼(15位)是320521720807024，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號(hào)碼中將上述個(gè)人信息提取出來，不僅快速簡便，而且不容易出錯(cuò)，核對(duì)時(shí)也只需要對(duì)身份證號(hào)碼進(jìn)行檢查，肯定可以大大提高工作效率。第八頁，共八十頁，2022年，8月28日分析身份證號(hào)碼18位身份證號(hào)碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女，第18位為校驗(yàn)位。第九頁，共八十頁，2022年，8月28日4.1人事信息數(shù)據(jù)表關(guān)鍵技術(shù)點(diǎn)（方法1）

要實(shí)現(xiàn)本例中的功能，讀者應(yīng)當(dāng)掌握以下Excel技術(shù)點(diǎn)。DATEIF函數(shù)RIGHT函數(shù)LEFT函數(shù)MID函數(shù)LEN函數(shù)第十頁，共八十頁，2022年，8月28日編號(hào)姓名身份證號(hào)碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(F3,7,6+(LEN(F3)=18)*2),"#-00-00")結(jié)果顯示形式：19XX-XX-XX分析：1、前面的兩個(gè)減號(hào)表示要返回?cái)?shù)值型結(jié)果

2、函數(shù)公式中的“*”表示IF函數(shù)P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2第十一頁，共八十頁，2022年，8月28日ABCD1編號(hào)姓名身份證號(hào)碼出生日期2A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(c2,7,6+(LEN(c2)=18)*2),"#-00-00")=--TEXT(MID(c2,7,6+0*2),"#-00-00")=--TEXT(MID(c2,7,6),"#-00-00")=--TEXT(800913,"#-00-00")結(jié)果顯示形式：19XX-XX-XX分析：1、前面的兩個(gè)減號(hào)表示要返回?cái)?shù)值型結(jié)果

2、函數(shù)公式中的“*”表示IF函數(shù)P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2第十二頁，共八十頁，2022年，8月28日ABCD1編號(hào)姓名身份證號(hào)碼出生日期2A001陳雙雙3427011980091388411980年09月13日從身份證中提取出生日期=--TEXT(MID(c2,7,6+(LEN(c2)=18)*2),"#-00-00")=--TEXT(MID(c2,7,6+1*2),"#-00-00")=--TEXT(MID(c2,7,8),"#-00-00")=--TEXT(19800913,"#-00-00")結(jié)果顯示形式：19XX-XX-XX分析：1、前面的兩個(gè)減號(hào)表示要返回?cái)?shù)值型結(jié)果

2、函數(shù)公式中的“*”表示IF函數(shù)P136LEN(F3)=18返回0或返回1

則：6+1*2=8或6+0*2第十三頁，共八十頁，2022年，8月28日=IF(c2="","",IF(MOD(RIGHT(LEFT(c2,17)),2),"男","女"))分析:left(C17,17)：截取單元格C17的前17位數(shù)right(left(C17,17))其實(shí)省略了一個(gè)參數(shù)，完整的應(yīng)該是：right(left(C17,17),1)意思是：截取left()所得的結(jié)果的最后一位數(shù)。MOD(RIGHT(LEFT(C17,17)),2)是以上面的結(jié)果除以2的余數(shù)最后的if()函數(shù)是用來判斷的，如果余數(shù)為有余數(shù)則返回“男”，沒有余數(shù)則返回“女”。其實(shí)該公式被簡化了，完整的應(yīng)該是：=IF(MOD(RIGHT(LEFT(C17,17),1),2)<>0,"男","女")從身份證中提取性別ABCD1編號(hào)姓名身份證號(hào)碼出生日期2A001陳雙雙3427018009138841980年09月13日第十四頁，共八十頁，2022年，8月28日RIGHT(LEFT(c2,17))RIGHT(LEFT(c3,17))RIGHT(342701820213857,1)7從身份證中提取性別ABC1編號(hào)姓名身份證號(hào)碼2A019吳仕3427011976021385783A020孫國成342701820213857第十五頁，共八十頁，2022年，8月28日LEFT函數(shù)用途：得到字符串左部指定個(gè)數(shù)的字符。語法形式：LEFT(string,n)參數(shù)：string指定要提取子串的字符串。

n指定子串長度返回值String。說明：函數(shù)執(zhí)行成功時(shí)返回string字符串左邊n個(gè)字符，發(fā)生錯(cuò)誤時(shí)返回空字符串（""）。如果任何參數(shù)的值為NULL，Left()函數(shù)返回NULL。如果n的值大于string字符串的長度，那么Left()函數(shù)返回整個(gè)string字符串，但并不增加其它字符。第十六頁，共八十頁，2022年，8月28日LEFT函數(shù)實(shí)例：如果A1=安徽省蚌埠市固鎮(zhèn)縣楊廟鄉(xiāng)，則公式“=LEFT(A1,FIND("省",A1))”返回安徽省。

DimAnyString,MyStr

AnyString="HelloWorld"'定義字符串。

MyStr=Left(AnyString,1)'返回"H"。

MyStr=Left(AnyString,7)'返回"HelloW"。

MyStr=Left(AnyString,10)'返回"HelloWorl"。第十七頁，共八十頁，2022年，8月28日4.1人事信息數(shù)據(jù)表關(guān)鍵技術(shù)點(diǎn)（方法2）

要實(shí)現(xiàn)本例中的功能，讀者應(yīng)當(dāng)掌握以下Excel技術(shù)點(diǎn)。IF（）函數(shù)CHOOSE（）函數(shù)MOD（）函數(shù)LEN（）函數(shù)RIGHT（）函數(shù)MID（）函數(shù)DATE（）函數(shù)第十八頁，共八十頁，2022年，8月28日IF函數(shù)的應(yīng)用IF函數(shù)是根據(jù)指定的條件來判斷其“真”（TRUE）、“假”（FALSE），從而返回其相對(duì)應(yīng)得內(nèi)容。語法形式：IF(logical_test,value_if_true,value_if_false)其中：logical_test邏輯判斷表達(dá)式value_if_true當(dāng)判斷條件為邏輯“真”（TRUE）時(shí)，顯示該處給定的內(nèi)容。如忽略，返回TRUE。value_if_false當(dāng)判斷條件為邏輯“假”（FALSE）時(shí)，顯示該處給定的內(nèi)容。如忽略，返回FALSE。IF(0=1,"男","女")結(jié)果為女第十九頁，共八十頁，2022年，8月28日LEN函數(shù)LEN函數(shù)用于返回字符串的長度。語法形式：LEN(text)其中，text表示要查找其長度的字符串文本。ABC1員工姓名身份證號(hào)碼位數(shù)2趙榮378501601517897153王勇3425076508053247164吳小平364501197804058246185陳果342501199010182233819=LEN(B2)第二十頁，共八十頁，2022年，8月28日練習(xí)1、判斷身份證號(hào)碼的長度，顯示不同信息身份證號(hào)碼的長度為15位的，顯示“老身份證號(hào)長度”身份證號(hào)碼的長度為18位的，顯示“新身份證號(hào)長度”ABCD1員工姓名身份證號(hào)碼位數(shù)結(jié)果2趙榮37850160151789715老身份證號(hào)長度3吳小平36450119780405824618新身份證號(hào)長度第二十一頁，共八十頁，2022年，8月28日MID函數(shù)MID函數(shù)用于從文本字符串中提取指定位置開始的特定字符。語法格式：

MID（text，start_num，num_chars）text：要提取字符的文本字符串start_num：從文本字符串中要提取第一個(gè)字符的開始位置num_chars：提取字符的個(gè)數(shù)=MID(A3,4,8)ABC1文本字符串提取條件提取結(jié)果201062791976從開始位置提取3個(gè)字符010301062791976從第4個(gè)字符位置提取8個(gè)字符62791976第二十二頁，共八十頁，2022年，8月28日練習(xí)獲取身份證號(hào)碼中的“年份”數(shù)值員工姓名身份證號(hào)碼位數(shù)結(jié)果完整形式趙榮378501601517897從第7位提取2個(gè)字符601960年吳小第7位提取4個(gè)字符19781978年第二十三頁，共八十頁，2022年，8月28日MOD函數(shù)MOD函數(shù)用于求兩個(gè)數(shù)值相除后的余數(shù)，其結(jié)果的正負(fù)號(hào)與除數(shù)相同。語法格式：MOD(number,divisor)Number:指定的被除數(shù)數(shù)值Diisor:指定的除數(shù)數(shù)值,并且不能為0值A(chǔ)BC1被除數(shù)除數(shù)結(jié)果2113231543=MOD(A2,B2)=MOD(A3,B3)第二十四頁，共八十頁，2022年，8月28日練習(xí)通過獲取身份證號(hào)碼中的“性別”的數(shù)值來判斷“性別”15位身份證號(hào)碼：第15位代表性別，奇數(shù)為男，偶數(shù)為女。18位身份證號(hào)碼：第17位代表性別，奇數(shù)為男，偶數(shù)為女。員工姓名身份證號(hào)碼位數(shù)“性別”數(shù)值奇偶性判斷性別趙榮378501601517897從第15位提取1個(gè)字符71男吳小第17位提取1個(gè)字符40女第二十五頁，共八十頁，2022年，8月28日RIGHT函數(shù)的應(yīng)用作用：從字符串右端取指定個(gè)數(shù)字符。語法形式：RIGHT(text,num_chars)其中：Text是包含要提取字符的文本字符串，可以直接輸入含有目標(biāo)文字的單元格名稱。Num_chars指定希望RIGHT提取的字符數(shù)。它

必須大于或等于0。如果num_chars大于文本長度，則RIGHT返回所有文本。如果忽略num_chars，則假定其為1。例：字符串公式要求結(jié)果HelloWorld

=RIGHT(A3,5)最后5個(gè)字符World寧夏理工學(xué)院經(jīng)濟(jì)管理系

=RIGHT(A4,5)最后5個(gè)字符經(jīng)濟(jì)管理系第二十六頁，共八十頁，2022年，8月28日DATE函數(shù)的應(yīng)用作用：返回代表特定日期的序列號(hào)。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。

語法形式：DATE(year,month,day)

其中：Year參數(shù)year可以為一到四位數(shù)字。MicrosoftExcel將根據(jù)所使用的日期系統(tǒng)來解釋year參數(shù)。默認(rèn)情況下，MicrosoftExcelforWindows將使用1900日期系統(tǒng)，而MicrosoftExcelforMacintosh將使用1904日期系統(tǒng)。Month代表一年中從1月到12月（一月到十二月）各月的正整數(shù)或負(fù)整數(shù)。

Day代表一月中從1日到31日各天的正整數(shù)或負(fù)整數(shù)。例如A5為2010，B5為3，C5為5DATE(A5,B5,C5)結(jié)果：表示轉(zhuǎn)為日期格式的2010-3-5第二十七頁，共八十頁，2022年，8月28日編號(hào)姓名身份證號(hào)碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期DATE("19"&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2))結(jié)果顯示形式：19XX-XX-XXMID函數(shù)：從文本字符串中提取指定位置開始的特定字符MID(C3,7,2),分析：從C3單元格中數(shù)值的第7位開始取2位，取得出生日期的年份信息。第二十八頁，共八十頁，2022年，8月28日=IF(LEN(C4)=15,DATE("19"&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2)),IF(LEN(C4)=18,DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)),""))編號(hào)姓名身份證號(hào)碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期第二十九頁，共八十頁，2022年，8月28日=IF(LEN(C3)=15,IF(MOD(MID(C3,15,1),2)=1,"男","女"),IF(MOD(MID(C3,17,1),2)=1,"男","女"))分析:MID(C3,15,1)從C3單元格中數(shù)值的第15位開始取1位（取4）MOD(4,2)返回4除以2的余數(shù)，結(jié)果為0IF(0=1,"男","女")結(jié)果為女從身份證中提取性別編號(hào)姓名身份證號(hào)碼出生日期HSR1003陳雙雙3427018009138841980年09月13日第三十頁，共八十頁，2022年，8月28日應(yīng)用DATEDIF函數(shù)計(jì)算員工年齡編制提取員工年齡公式（格式為“多少年多少月多少天”）=DATEDIF(G3,TODAY(),"y")&"年"&DATEDIF(G3,TODAY(),"ym")&"個(gè)月"&DATEDIF(G3,TODAY(),"md")&"天"編制提取員工年齡公式（格式為“多少年”）=DATEDIF(G3,TODAY(),"y")&"年"第三十一頁，共八十頁，2022年，8月28日補(bǔ)充：CHOOSE函數(shù)的應(yīng)用作用：從參數(shù)列表中選擇并返回一個(gè)值。函數(shù)CHOOSE可以使用index_num返回?cái)?shù)值參數(shù)清單中的數(shù)值。使用函數(shù)CHOOSE可以基于索引號(hào)返回多達(dá)29個(gè)待選數(shù)值中的任一數(shù)值。語法形式：CHOOSE(index_num,value1,value2,...)其中：Index_num用以指明待選參數(shù)序號(hào)的參數(shù)值。Index_num必須為1到29之間的數(shù)字、或者是包含數(shù)字1到29的公式或單元格引用。Value1,value2,...為1到29個(gè)數(shù)值參數(shù)，函數(shù)CHOOSE基于index_num，從中選擇一個(gè)數(shù)值或執(zhí)行相應(yīng)的操作。參數(shù)可以為數(shù)字、單元格引用，已定義的名稱、公式、函數(shù)或文本。例如公式“=CHOOSE(2，”電腦“，”愛好者“)返回“愛好者”。公式“=SUM(A1:CHOOSE(3，A10，A20，A30))”與公式“=SUM(A1:A30)”等價(jià)(因?yàn)镃HOOSE(3，A10，A20，A30)返回A30)。第三十二頁，共八十頁，2022年，8月28日=IF(LEN(C4)=15,DATE("19"&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2)),IF(LEN(C4)=18,DATE(MID(C4,7,4),MID(C4,11,2),MID(C4,13,2)),""))編號(hào)姓名身份證號(hào)碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期第三十三頁，共八十頁，2022年，8月28日=IF(C4="","",CHOOSE(MOD(IF(LEN(C4)=15,RIGHT(C4,1),IF(LEN(C4)=18,MID(C4,17,1),"")),2)+1,"女","男"))分析:MID(C3,17,1)從C4單元格中數(shù)值的第15位開始取1位（取4）RIGHT(C4,1)取C4單元格中數(shù)值的1位（取4）

IF(1,"女","男")結(jié)果為女從身份證中提取性別編號(hào)姓名身份證號(hào)碼出生日期A001陳雙雙3427018009138841980年09月13日第三十四頁，共八十頁，2022年，8月28日補(bǔ)充：CONCATENATE函數(shù)CONCATENATE函數(shù)用于將多個(gè)文本字符串合并為一個(gè)文本字符串。語法格式：

CONCATENATE（text1，text12text13，…）其中，text1，text12text13，…表示將要合并成單個(gè)文本項(xiàng)的文本項(xiàng)（這些文本項(xiàng)可以為文本字符串、數(shù)字或?qū)蝹€(gè)單元格的引用）。ABC1姓名姓名2趙榮趙榮3王勇王勇4吳小平吳小平5陳果陳果=CONCATENATE(A5,B5)第三十五頁，共八十頁，2022年，8月28日4.2人事數(shù)據(jù)的條件求和計(jì)數(shù)案例背景

Excel的條件求和計(jì)數(shù)在工作中的應(yīng)用很廣泛。人力資源管理者需要經(jīng)常對(duì)員工信息進(jìn)行統(tǒng)計(jì)分析，比如需要獲知人事數(shù)據(jù)表中學(xué)歷為本科的員工人數(shù)有多少，又比如查詢某個(gè)年齡段的員工人數(shù)等，這些都可以通過活用函數(shù)條件求和計(jì)數(shù)加以解決，進(jìn)而提高工作的效率。

人事數(shù)據(jù)的單字段單條件求和計(jì)數(shù)

人事數(shù)據(jù)的單字段多條件求和計(jì)數(shù)

人事數(shù)據(jù)的多字段多條件求和計(jì)數(shù)

DSUM數(shù)據(jù)庫函數(shù)的應(yīng)用

第三十六頁，共八十頁，2022年，8月28日4.2人事數(shù)據(jù)的條件求和計(jì)數(shù)最終效果展示

序號(hào)姓名隸屬部門學(xué)歷性別年齡獎(jiǎng)金1胡生產(chǎn)部本科男304002徐生產(chǎn)部?？婆?72003楊生產(chǎn)部碩士男592004劉生產(chǎn)部專科女482005李銷售部本科男382006林銷售部本科女252007童行政部?？婆?22008王行政部本科男244009李生產(chǎn)部本科男2440010趙生產(chǎn)部?？婆?440011劉生產(chǎn)部博士男3040012馬生產(chǎn)部博士女3340013胡生產(chǎn)部本科女2940014林生產(chǎn)部本科男3530015童生產(chǎn)部碩士女48300第三十七頁，共八十頁，2022年，8月28日4.2人事數(shù)據(jù)的條件求和計(jì)數(shù)關(guān)鍵技術(shù)點(diǎn)

要實(shí)現(xiàn)本例中的功能，讀者應(yīng)當(dāng)掌握以下Excel技術(shù)點(diǎn)。COUNTIF函數(shù)SUMPRODUCT函數(shù)DSUM函數(shù)第三十八頁，共八十頁，2022年，8月28日人事數(shù)據(jù)的單字段單條件求和計(jì)數(shù)例：統(tǒng)計(jì)所有本科學(xué)歷人數(shù)例：統(tǒng)計(jì)年齡大于等于40歲人數(shù)方法：使用COUNTIF函數(shù)統(tǒng)計(jì)數(shù)據(jù)第三十九頁，共八十頁，2022年，8月28日COUNTIF函數(shù)用于計(jì)算單元格區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。語法形式：COUNTIF（range,criteria)COUNTIF（數(shù)據(jù)區(qū)域,條件表達(dá)式)第四十頁，共八十頁，2022年，8月28日COUNTIF函數(shù)舉例ABC1姓名性別職務(wù)2劉勇男經(jīng)理3李南男職員4陳雙雙女經(jīng)理5葉小來男職員6林佳女經(jīng)理7彭力男主管8范琳琳女職員9易呈亮男經(jīng)理10黃海燕女職員11男性員工人數(shù)512女性員工人數(shù)413年齡在30歲以下的人數(shù)3=COUNTIF(B2:B10,"男")=COUNTIF(B2:B10,"女")第四十一頁，共八十頁，2022年，8月28日人事數(shù)據(jù)的單字段多條件求和計(jì)數(shù)例：統(tǒng)計(jì)年齡在30歲至40歲的人數(shù)方法1：=COUNTIF(F2:F18,">=30")-COUNTIF(F2:F18,">40")方法2：=SUM((F2:F18>=30)*(F2:F18<=40))方法3：=SUMPRODUCT((F2:F18>=30)*(F2:F18<=40))第四十二頁，共八十頁，2022年，8月28日SUM函數(shù)舉例ABC1姓名性別年齡2劉勇男183李南男264陳雙雙女255葉小來男31=SUM((C2:C5>=20)*(C2:C5<=30))20歲至30歲(0,1,1,1)*(1,1,1,0)(0,1,1,0)=SUM(0,1,1,0)例：統(tǒng)計(jì)年齡在20歲至30歲的人數(shù)第四十三頁，共八十頁，2022年，8月28日SUMPRODUCT函數(shù)作用：計(jì)算工作表內(nèi)多列中對(duì)應(yīng)值相乘之后的和，即乘積之和。其語法為：SUMPRODUCT(array1,array2,array3,…)其中，Array1,array2,array3,…為2到30個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯(cuò)誤值#VALUE!。該函數(shù)將非數(shù)值型的數(shù)組元素作為0處理。第四十四頁，共八十頁，2022年，8月28日人事數(shù)據(jù)的多字段多條件求和計(jì)數(shù)例：統(tǒng)計(jì)性別為男性且年齡在30歲以上的人數(shù)方法：

=SUMPRODUCT(($E$2:$E$18=I11)*($F$2:$F$18>I12))第四十五頁，共八十頁，2022年，8月28日DSUM數(shù)據(jù)庫函數(shù)的應(yīng)用例：統(tǒng)計(jì)生產(chǎn)部學(xué)歷為本科的員工的獎(jiǎng)金合計(jì)方法：=DSUM(A1:G18,I19,I15:J16)第四十六頁，共八十頁，2022年，8月28日DSUM函數(shù)DSUM函數(shù)，按條件求和語法形式:DSUM(database,field,criteria)其中：Database是區(qū)域field是列數(shù)criteria是條件第四十七頁，共八十頁，2022年，8月28日DSUM函數(shù)例如：ABC1產(chǎn)品名稱產(chǎn)品單價(jià)（單位：元）總銷售提成2A111003B100104C200305D20406E45507F888089條件產(chǎn)品單價(jià)（單位：元）結(jié)果10>50120單價(jià)大于50元的產(chǎn)品的銷售提成總額=DSUM(A1:C7,3,B9:B10)第四十八頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平案例背景

Excel的圖表分析功能十分強(qiáng)大，其中的數(shù)據(jù)透視圖更是聚集了強(qiáng)大的智能。本案例以人事基礎(chǔ)數(shù)據(jù)圖為例，介紹如何在紛雜的數(shù)據(jù)中提取所需要的數(shù)據(jù)，同時(shí)制作合乎需求的數(shù)據(jù)透視表以及數(shù)據(jù)透視圖。

編制員工學(xué)歷透視表

制作員工學(xué)歷透視圖

第四十九頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平最終效果展示員工學(xué)歷透視表員工學(xué)歷透視圖

計(jì)數(shù)項(xiàng):學(xué)歷學(xué)歷

隸屬部門本科博士碩士專科總計(jì)生產(chǎn)部622313銷售部22行政部112總計(jì)922417第五十頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平關(guān)鍵技術(shù)點(diǎn)

要實(shí)現(xiàn)本例中的功能，讀者應(yīng)當(dāng)掌握以下Excel技術(shù)點(diǎn)。數(shù)據(jù)透視表的應(yīng)用數(shù)據(jù)透視圖的應(yīng)用第五十一頁，共八十頁，2022年，8月28日編制員工學(xué)歷透視表案例背景

Excel的圖表分析功能十分強(qiáng)大，其中的數(shù)據(jù)透視圖更是聚集了強(qiáng)大的智能。本案例以人事基礎(chǔ)數(shù)據(jù)圖為例，介紹如何在紛雜的數(shù)據(jù)中提取所需要的數(shù)據(jù)，同時(shí)制作合乎需求的數(shù)據(jù)透視表以及數(shù)據(jù)透視圖。

制作員工學(xué)歷透視圖

第五十二頁，共八十頁，2022年，8月28日制作員工學(xué)歷透視圖案例背景

Excel的圖表分析功能十分強(qiáng)大，其中的數(shù)據(jù)透視圖更是聚集了強(qiáng)大的智能。本案例以人事基礎(chǔ)數(shù)據(jù)圖為例，介紹如何在紛雜的數(shù)據(jù)中提取所需要的數(shù)據(jù)，同時(shí)制作合乎需求的數(shù)據(jù)透視表以及數(shù)據(jù)透視圖。

第五十三頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表1、數(shù)據(jù)透視表：一種交互的、交叉制表的Excel報(bào)表，用于對(duì)多種來源（包括Excel的外部數(shù)據(jù)）的數(shù)據(jù)（如數(shù)據(jù)庫記錄）進(jìn)行匯總和分析。正源于其交互式動(dòng)態(tài)匯總報(bào)表的特性，數(shù)據(jù)透視表即可快速合并和比較大量數(shù)據(jù)，也可以創(chuàng)建頻率分布和多個(gè)不同數(shù)據(jù)維的交叉制表。也就是說，創(chuàng)建一個(gè)數(shù)據(jù)透視表后，可以旋轉(zhuǎn)其行和列以看到源數(shù)據(jù)的不同匯總結(jié)果，而且可以顯示不同頁面的篩選數(shù)據(jù)，即根據(jù)需要顯示區(qū)域中的明細(xì)數(shù)據(jù)。

第五十四頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表2、數(shù)據(jù)透視表的作用:能夠改變數(shù)據(jù)表的行、列布局;能夠快速匯總大量數(shù)據(jù);能夠基于原數(shù)據(jù)表創(chuàng)建數(shù)據(jù)分組，并對(duì)分組進(jìn)行匯總統(tǒng)計(jì)。第五十五頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表包括：分頁字段、數(shù)據(jù)項(xiàng)、行字段、列字段、項(xiàng)目等元素?cái)?shù)據(jù)項(xiàng)列字段頁字段行字段第五十六頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表解釋:行：拖放到行中的數(shù)據(jù)字段中的每個(gè)數(shù)據(jù)項(xiàng)將占據(jù)透視表的一行。列：拖放到列中的數(shù)據(jù)字段中的每個(gè)數(shù)據(jù)項(xiàng)將占據(jù)透視表的一列。行和列確定一個(gè)二維表格.頁：拖放在頁中的字段，Excel將按該字段的數(shù)據(jù)項(xiàng)對(duì)透視表進(jìn)行分頁。數(shù)據(jù)：進(jìn)行計(jì)數(shù)或匯總的字段名稱。第五十七頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表3、數(shù)據(jù)透視表的建立點(diǎn)擊菜單命令“數(shù)據(jù)→數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂?duì)話框。第一步，如下圖所示，選擇“MicrosoftExcel數(shù)據(jù)列表或數(shù)據(jù)庫”及下面的“數(shù)據(jù)透視表”單選項(xiàng)。第五十八頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表3、數(shù)據(jù)透視表的建立第二步，如下圖所示，在“選定區(qū)域”輸入全部數(shù)據(jù)所在的單元格區(qū)域，或者點(diǎn)擊輸入框右側(cè)的“壓縮對(duì)話”按鈕，在工作表中用鼠標(biāo)選定數(shù)據(jù)區(qū)域。第三步，在對(duì)話框中選定“新建工作表”單選項(xiàng)，以便將創(chuàng)建的數(shù)據(jù)透視表放到一個(gè)新的工作表中，再點(diǎn)擊“完成”按鈕，如下圖所示。第五十九頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表3、數(shù)據(jù)透視表的建立這樣，就可以建立一個(gè)空的數(shù)據(jù)透視表，并同時(shí)顯示“數(shù)據(jù)透視表”工具欄和“數(shù)據(jù)透視表字段列表”對(duì)話框，如圖5所示。

第六十頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表4、利用數(shù)據(jù)透視表得到需要的結(jié)果

（1）統(tǒng)計(jì)所有學(xué)生各學(xué)科成績的匯總數(shù)據(jù)（2）查看所有學(xué)生“新聞學(xué)”成績的平均值”,平均值保留兩位小數(shù)位數(shù)（3）查看男生“新聞學(xué)”成績的平均值”,平均值保留兩位小數(shù)位數(shù)（4）查看“工商1010101班男生的“新聞學(xué)”成績的平均值”,平均值保留兩位小數(shù)位數(shù)（5）將第（2）題“行”與“列”字段互換，并設(shè)置“自動(dòng)套用格式”樣式第六十一頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表5、修改數(shù)據(jù)透視表顯示或隱藏行、列中的數(shù)據(jù)項(xiàng)在行或列字段的下拉列表?xiàng)l中選擇或取消復(fù)選標(biāo)志。重新組織數(shù)據(jù)透視表行、列置換：鼠標(biāo)移至行字段上按住拖動(dòng)到列字段上在字段內(nèi)移動(dòng)個(gè)別項(xiàng)：鼠標(biāo)按在要移動(dòng)字段邊框線上拖動(dòng)到指定位置放開。在原有行列字段上增加或減少字段增加：單擊數(shù)據(jù)透視表,在數(shù)據(jù)透視表工具欄上將所需字段拖入行或列的字段處.減少：選擇數(shù)據(jù)透視表行或列的字段名，拖到表外第六十二頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表6、修改數(shù)據(jù)透視表的概要函數(shù)

概要函數(shù)：即數(shù)據(jù)透視表中使用的透視函數(shù)。如：求和、計(jì)數(shù)、平均、百分比、最大值、標(biāo)準(zhǔn)偏差等。操作方法：選擇數(shù)據(jù)透視表中數(shù)據(jù)字段；在對(duì)象菜單或數(shù)據(jù)透視表工具欄中選擇[字段設(shè)置]；在數(shù)據(jù)透視表對(duì)話框的匯總方式列表中選擇所需函數(shù)。第六十三頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平例：數(shù)據(jù)列表是以列表形式存在的數(shù)據(jù)表格，它是一維的表格。而在實(shí)際工作中，用戶的數(shù)據(jù)往往是以二維表格的形式存在的，如下圖所示：純牛奶乳酸飲料酸牛奶奶茶奶片大林店93165784837金井店12925224935179和平路店5167382114258新陽路店260738220554解放路店73358390113285延安路店39257248330186四橋店3601995230139古尖店48142165136105東單店532023324666第六十四頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平對(duì)于這樣的數(shù)據(jù)表，用戶無法以它為數(shù)據(jù)源創(chuàng)建理想的數(shù)據(jù)透視表。只有把二維的數(shù)據(jù)表格轉(zhuǎn)換為如下圖所示的數(shù)據(jù)列表，才能成為數(shù)據(jù)透視表的數(shù)據(jù)源。能作為數(shù)據(jù)透視表數(shù)據(jù)源的一維數(shù)據(jù)列表店鋪商品銷量大林店純牛奶93大林店乳酸飲料165大林店酸牛奶78大林店奶茶48大林店奶片37金井店純牛奶129金井店乳酸飲料252金井店酸牛奶249金井店奶茶35金井店奶片179和平路店純牛奶5和平路店乳酸飲料167（1）各店鋪商品銷量求和統(tǒng)計(jì)（2）各店鋪商品中銷量最大的商品銷量第六十五頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平編制員工學(xué)歷透視表7、刪除數(shù)據(jù)透視表的方法利用數(shù)據(jù)透視表工具欄的“數(shù)據(jù)透視表”按鈕→“選定”→“整張表格”，再利用“編輯”→“清除”→“全部”第六十六頁，共八十頁，2022年，8月28日4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學(xué)歷水平制作員工學(xué)歷透視圖

數(shù)據(jù)透視表雖然具有可以準(zhǔn)確計(jì)算和分析數(shù)據(jù)的優(yōu)點(diǎn)，但是通常數(shù)據(jù)透視表的數(shù)據(jù)源較大，數(shù)據(jù)量非常多，數(shù)據(jù)排列也相當(dāng)復(fù)雜，這時(shí)就可以利用數(shù)據(jù)透視圖來更加直觀地分析數(shù)據(jù)。數(shù)據(jù)透視圖以圖形形式表示數(shù)據(jù)表中的數(shù)據(jù)，和數(shù)據(jù)透視表一樣，可以更改數(shù)據(jù)透視圖的布局和顯示的數(shù)據(jù)。與數(shù)據(jù)透視表相比，數(shù)據(jù)透視圖展示數(shù)據(jù)之間關(guān)系的方式更加可視化、更易于理解。數(shù)據(jù)透視圖是由系列、分類軸、坐標(biāo)軸、數(shù)據(jù)標(biāo)志、頁字段、數(shù)據(jù)字段、頁和分類字段等元素組成。兩種創(chuàng)建方式：直接創(chuàng)建數(shù)據(jù)透視圖和利用數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖建立數(shù)據(jù)透視圖表的方法單擊數(shù)據(jù)透視表中的非空單元格，選擇“插入”菜單的圖表命令，或單擊常用工具欄中的圖表工具，Excel會(huì)自動(dòng)在當(dāng)前工作簿中插入一個(gè)新的數(shù)據(jù)透視圖。第六十七頁，共八十頁，2022年，8月28日4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對(duì)案例背景在實(shí)際工作中人力資源部經(jīng)常需要核對(duì)某些數(shù)據(jù)，除了核對(duì)工資外，壞需要核對(duì)員工身份證號(hào)碼、職工姓名、銀行賬號(hào)等信息。這些工作很單調(diào)且麻煩。若被核對(duì)的字符串簡單，直接進(jìn)行人工處理還是可以的；一旦字符很多，且排列順序不一，這是人工查找起來就顯得比較麻煩。

借助Excel提供的條件格式或者數(shù)據(jù)透視表功能，復(fù)雜的比對(duì)難題就可以迎刃而解。此外人力資源工作者利用相關(guān)函數(shù)，同樣也能完成該項(xiàng)工作。

利用“條件格式”比照核對(duì)兩表格數(shù)據(jù)

利用“數(shù)據(jù)透視表”比照核對(duì)兩表格數(shù)據(jù)

利用VLOOKUP函數(shù)比照核對(duì)兩表數(shù)據(jù)第六十八頁，共八十頁，2022年，8月28日4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對(duì)最終效果展示使用條件格式對(duì)比使用VLOOKUP函數(shù)對(duì)比使用數(shù)據(jù)透視表對(duì)比

第六十九頁，共八十頁，2022年，8月28日4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對(duì)關(guān)鍵技術(shù)點(diǎn)

要實(shí)現(xiàn)本例中的功能，讀者應(yīng)當(dāng)掌握以下Excel技術(shù)點(diǎn)。條件格式的應(yīng)用數(shù)據(jù)透視表的應(yīng)用VLOOKUP函數(shù)的應(yīng)用NOT函數(shù)OR函數(shù)第七十頁，共八十頁，2022年，8月28日4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對(duì)利用“條件格式”比照核對(duì)兩表格數(shù)據(jù)將D列中的身份證號(hào)碼與H列的身份證號(hào)碼進(jìn)行比較，若不一致則顯示為黃色底紋。方法：設(shè)置條件格式，設(shè)置公式為=NOT(OR(D2=H$2:H$9))ABCDEFGH1工號(hào)隸屬部門姓名身份證號(hào)碼工號(hào)隸屬部門姓名身份證號(hào)碼225生產(chǎn)部張三33020219760428123388技術(shù)部Sun120302198806051611314生產(chǎn)部李四31030219810101012325生產(chǎn)部張三330202197604281233456生產(chǎn)部王五33090219771208213230行政部Apple3303021976042812335109生產(chǎn)部趙六21030219760428121114生產(chǎn)部李四310302198101110123676銷售部Lemon32030219631111021756生產(chǎn)部王五3309021977120821