人事信息數(shù)據(jù)統(tǒng)計分析課件

Excel在商務中的應用任課教師：李磊Excel在商務中的應用任課教師：李磊第4章人事信息數(shù)據(jù)統(tǒng)計分析本章要點：4.1人事信息數(shù)據(jù)表4.2人事數(shù)據(jù)的條件求和計數(shù)4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.5員工人事信息數(shù)據(jù)查詢表（員工信息卡片）4.6統(tǒng)計不同年齡段員工信息4.7人力資源月報動態(tài)圖表第4章人事信息數(shù)據(jù)統(tǒng)計分析本章要點：第4章人事信息數(shù)據(jù)統(tǒng)計分析第4章人事信息數(shù)據(jù)統(tǒng)計分析

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

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

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

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數(shù)計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格

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

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

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

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

4.2.4

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

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

4.3.1編制員工學歷透視表

4.3.2制作員工學歷透視圖

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

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

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

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

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

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

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

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

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

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

4.7人力資源月報動態(tài)圖表

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

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

4.7.3建立窗體控件

4.7.4對數(shù)據(jù)自動降序排列

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

4.7.6繪制柏拉圖

4.7.7美化圖表區(qū)

第4章人事信息數(shù)據(jù)統(tǒng)計分析第4章人事信息數(shù)據(jù)統(tǒng)計分析

4.1人事信息數(shù)據(jù)表案例背景企業(yè)里由于人員較多且流動變化大，因而人力資源部應及時做好人事數(shù)據(jù)的整理、匯總分析等工作，并且這些數(shù)據(jù)常常也是企業(yè)做各項決策的參考依據(jù)，因此處理好人事數(shù)據(jù)的整理工作意義重大。人事信息數(shù)據(jù)表是企業(yè)進行人事信息管理的基礎和依據(jù)，因此，人事信息管理表格一定要科學、準確、詳細，并且有利于查找、利用，這樣才能真正輔助企業(yè)管理者進行人事信息管理工作。

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

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

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數(shù)計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格4.1人事信息數(shù)據(jù)表案例背景4.1人事信息數(shù)據(jù)表最終效果展示

某某有限公司員工人事信息表序號工號姓名隸屬部門學歷身份證號生日性別計算年齡（-年-月-日）年齡職稱現(xiàn)任職務聯(lián)系電話居住地址168胡生產(chǎn)部本科1201051976101200341976-10-12男35年6個月26天35年工程師部長12345678杭州市某區(qū)某路1號214徐生產(chǎn)部?？?201021969080331681969-8-3女42年9個月4天42年助工科員12345679杭州市某區(qū)某路2號355楊生產(chǎn)部碩士1201021947100111701947-10-1男64年7個月6天64年無科員12345680杭州市某區(qū)某路3號4106劉生產(chǎn)部?？?201101959031418291959-3-14女53年1個月24天53年工程師科員12345681杭州市某區(qū)某路4號5107李銷售部本科1201026811151711968-11-15男43年5個月23天43年工程師部長12345682杭州市某區(qū)某路5號6114林銷售部本科1201021982022811641982-2-28女30年2個月10天30年助工科員12345683杭州市某區(qū)某路6號7118童行政部?？?201107505153921975-5-15女36年11個月23天36年助工部長12345684杭州市某區(qū)某路7號869王行政部本科1201051983032530361983-3-25男29年1個月13天29年無科員12345685杭州市某區(qū)某路8號9236李生產(chǎn)部本科1201051983032530361983-3-25男29年1個月13天29年工程師部長12345686杭州市某區(qū)某路9號10237趙生產(chǎn)部?？?201021963012011691963-1-20女49年3個月18天49年工程師科員12345687杭州市某區(qū)某路10號11238劉生產(chǎn)部高中1201017702221511977-2-22男35年2個月16天35年工程師科員12345688杭州市某區(qū)某路11號12239馬生產(chǎn)部高中1201021974050707261974-5-7女38年0個月0天38年工程師科員12345689杭州市某區(qū)某路12號13240胡生產(chǎn)部本科1201011978050207251978-5-2女34年0個月5天34年助工科員12345690杭州市某區(qū)某路13號14241林生產(chǎn)部本科1201051972011933181972-1-19男40年3個月19天40年助工科員12345691杭州市某區(qū)某路14號15242童生產(chǎn)部碩士1201021958122316291958-12-23女53年4個月15天53年助工科員12345692杭州市某區(qū)某路15號4.1人事信息數(shù)據(jù)表最終效果展示

某某有限公司員工人事信息創(chuàng)建人事信息數(shù)據(jù)表人事信息數(shù)據(jù)表包括項目有：姓名、性別、年齡、身份證號（或社會保障號）、學歷、現(xiàn)任職務、聯(lián)系電話、E-mail和居住地等有效信息。其中性別、年齡、出生日期等項目均不需要手工錄入，利用函數(shù)計算即可。創(chuàng)建人事信息數(shù)據(jù)表人事信息數(shù)據(jù)表包括項目有：姓名、性別、年齡身份證號中提取生日、性別等有效信息建立人事信息數(shù)據(jù)表時，性別和出生日期兩個項目可以不必手工錄入，而是利用函數(shù)通過身份證號碼來自動提取即可。身份證號中提取生日、性別等有效信息建立人事信息數(shù)據(jù)表時，性別分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號碼，其中都保存了相關的個人信息。15位身份證號碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。例如:某員工的身份證號碼(15位)是320521720807024，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯，核對時也只需要對身份證號碼進行檢查，肯定可以大大提高工作效率。分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女，第18位為校驗位。分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年4.1人事信息數(shù)據(jù)表關鍵技術點（方法1）

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。DATEIF函數(shù)RIGHT函數(shù)LEFT函數(shù)MID函數(shù)LEN函數(shù)4.1人事信息數(shù)據(jù)表關鍵技術點（方法1）

要實現(xiàn)本例中編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(F3,7,6+(LEN(F3)=18)*2),"#-00-00")結(jié)果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數(shù)值型結(jié)果

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

則：6+1*2=8或6+0*2編號姓名身份證號碼出生日期A001陳雙雙342701ABCD1編號姓名身份證號碼出生日期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、前面的兩個減號表示要返回數(shù)值型結(jié)果

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

則：6+1*2=8或6+0*2ABCD1編號姓名身份證號碼出生日期2A001陳雙雙ABCD1編號姓名身份證號碼出生日期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、前面的兩個減號表示要返回數(shù)值型結(jié)果

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

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

n指定子串長度返回值String。說明：函數(shù)執(zhí)行成功時返回string字符串左邊n個字符，發(fā)生錯誤時返回空字符串（""）。如果任何參數(shù)的值為NULL，Left()函數(shù)返回NULL。如果n的值大于string字符串的長度，那么Left()函數(shù)返回整個string字符串，但并不增加其它字符。LEFT函數(shù)用途：得到字符串左部指定個數(shù)的字符。LEFT函數(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"。LEFT函數(shù)實例：4.1人事信息數(shù)據(jù)表關鍵技術點（方法2）

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。IF（）函數(shù)CHOOSE（）函數(shù)MOD（）函數(shù)LEN（）函數(shù)RIGHT（）函數(shù)MID（）函數(shù)DATE（）函數(shù)4.1人事信息數(shù)據(jù)表關鍵技術點（方法2）

要實現(xiàn)本例中IF函數(shù)的應用IF函數(shù)是根據(jù)指定的條件來判斷其“真”（TRUE）、“假”（FALSE），從而返回其相對應得內(nèi)容。語法形式：IF(logical_test,value_if_true,value_if_false)其中：logical_test邏輯判斷表達式value_if_true當判斷條件為邏輯“真”（TRUE）時，顯示該處給定的內(nèi)容。如忽略，返回TRUE。value_if_false當判斷條件為邏輯“假”（FALSE）時，顯示該處給定的內(nèi)容。如忽略，返回FALSE。IF(0=1,"男","女")結(jié)果為女IF函數(shù)的應用IF函數(shù)是根據(jù)指定的條件來判斷其“真”（TRULEN函數(shù)LEN函數(shù)用于返回字符串的長度。語法形式：LEN(text)其中，text表示要查找其長度的字符串文本。ABC1員工姓名身份證號碼位數(shù)2趙榮378501601517897153王勇3425076508053247164吳小平364501197804058246185陳果342501199010182233819=LEN(B2)LEN函數(shù)LEN函數(shù)用于返回字符串的長度。ABC1員工姓名身練習1、判斷身份證號碼的長度，顯示不同信息身份證號碼的長度為15位的，顯示“老身份證號長度”身份證號碼的長度為18位的，顯示“新身份證號長度”ABCD1員工姓名身份證號碼位數(shù)結(jié)果2趙榮37850160151789715老身份證號長度3吳小平36450119780405824618新身份證號長度練習1、判斷身份證號碼的長度，顯示不同信息ABCD1員工姓名MID函數(shù)MID函數(shù)用于從文本字符串中提取指定位置開始的特定字符。語法格式：

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

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

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

=RIGHT(A4,5)最后5個字符經(jīng)濟管理系RIGHT函數(shù)的應用作用：從字符串右端取指定個數(shù)字符。字符DATE函數(shù)的應用作用：返回代表特定日期的序列號。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設為日期格式。

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

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

Day代表一月中從1日到31日各天的正整數(shù)或負整數(shù)。例如A5為2010，B5為3，C5為5DATE(A5,B5,C5)結(jié)果：表示轉(zhuǎn)為日期格式的2010-3-5DATE函數(shù)的應用作用：返回代表特定日期的序列號。如果在輸入編號姓名身份證號碼出生日期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位，取得出生日期的年份信息。編號姓名身份證號碼出生日期A001陳雙雙342701=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)),""))編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=IF(LEN(C4)=15,編號姓名身份證號碼出生=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é)果為女從身份證中提取性別編號姓名身份證號碼出生日期HSR1003陳雙雙3427018009138841980年09月13日=IF(LEN(C3)=15,從身份證中提取性別編號應用DATEDIF函數(shù)計算員工年齡編制提取員工年齡公式（格式為“多少年多少月多少天”）=DATEDIF(G3,TODAY(),"y")&"年"&DATEDIF(G3,TODAY(),"ym")&"個月"&DATEDIF(G3,TODAY(),"md")&"天"編制提取員工年齡公式（格式為“多少年”）=DATEDIF(G3,TODAY(),"y")&"年"應用DATEDIF函數(shù)計算員工年齡編制提取員工年齡公式（格式補充：CHOOSE函數(shù)的應用作用：從參數(shù)列表中選擇并返回一個值。函數(shù)CHOOSE可以使用index_num返回數(shù)值參數(shù)清單中的數(shù)值。使用函數(shù)CHOOSE可以基于索引號返回多達29個待選數(shù)值中的任一數(shù)值。語法形式：CHOOSE(index_num,value1,value2,...)其中：Index_num用以指明待選參數(shù)序號的參數(shù)值。Index_num必須為1到29之間的數(shù)字、或者是包含數(shù)字1到29的公式或單元格引用。Value1,value2,...為1到29個數(shù)值參數(shù)，函數(shù)CHOOSE基于index_num，從中選擇一個數(shù)值或執(zhí)行相應的操作。參數(shù)可以為數(shù)字、單元格引用，已定義的名稱、公式、函數(shù)或文本。例如公式“=CHOOSE(2，”電腦“，”愛好者“)返回“愛好者”。公式“=SUM(A1:CHOOSE(3，A10，A20，A30))”與公式“=SUM(A1:A30)”等價(因為CHOOSE(3，A10，A20，A30)返回A30)。補充：CHOOSE函數(shù)的應用作用：從參數(shù)列表中選擇并返回一=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)),""))編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=IF(LEN(C4)=15,編號姓名身份證號碼出生=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é)果為女從身份證中提取性別編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日=IF(C4="","",從身份證中提取性別編補充：CONCATENATE函數(shù)CONCATENATE函數(shù)用于將多個文本字符串合并為一個文本字符串。語法格式：

CONCATENATE（text1，text12text13，…）其中，text1，text12text13，…表示將要合并成單個文本項的文本項（這些文本項可以為文本字符串、數(shù)字或?qū)蝹€單元格的引用）。ABC1姓名姓名2趙榮趙榮3王勇王勇4吳小平吳小平5陳果陳果=CONCATENATE(A5,B5)補充：CONCATENATE函數(shù)CONCATENATE函數(shù)用4.2人事數(shù)據(jù)的條件求和計數(shù)案例背景

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

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

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

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

4.2.4

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

4.2人事數(shù)據(jù)的條件求和計數(shù)案例背景4.2人事數(shù)據(jù)的條件求和計數(shù)最終效果展示

序號姓名隸屬部門學歷性別年齡獎金1胡生產(chǎn)部本科男304002徐生產(chǎn)部專科女372003楊生產(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)部碩士女483004.2人事數(shù)據(jù)的條件求和計數(shù)最終效果展示

序號姓名隸屬部門4.2人事數(shù)據(jù)的條件求和計數(shù)關鍵技術點

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。COUNTIF函數(shù)SUMPRODUCT函數(shù)DSUM函數(shù)4.2人事數(shù)據(jù)的條件求和計數(shù)關鍵技術點

要實現(xiàn)本例中的人事數(shù)據(jù)的單字段單條件求和計數(shù)例：統(tǒng)計所有本科學歷人數(shù)例：統(tǒng)計年齡大于等于40歲人數(shù)方法：使用COUNTIF函數(shù)統(tǒng)計數(shù)據(jù)人事數(shù)據(jù)的單字段單條件求和計數(shù)例：統(tǒng)計所有本科學歷人數(shù)COUNTIF函數(shù)用于計算單元格區(qū)域中滿足給定條件的單元格的個數(shù)。語法形式：COUNTIF（range,criteria)COUNTIF（數(shù)據(jù)區(qū)域,條件表達式)COUNTIF函數(shù)用于計算單元格區(qū)域中滿足給定條件的單元格的COUNTIF函數(shù)舉例ABC1姓名性別職務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,"女")COUNTIF函數(shù)舉例ABC1姓名性別職務2劉勇男經(jīng)理3李南人事數(shù)據(jù)的單字段多條件求和計數(shù)例：統(tǒng)計年齡在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))人事數(shù)據(jù)的單字段多條件求和計數(shù)例：統(tǒng)計年齡在30歲至40歲的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)計年齡在20歲至30歲的人數(shù)SUM函數(shù)舉例ABC1姓名性別年齡2劉勇男183李南男264SUMPRODUCT函數(shù)作用：計算工作表內(nèi)多列中對應值相乘之后的和，即乘積之和。其語法為：SUMPRODUCT(array1,array2,array3,…)其中，Array1,array2,array3,…為2到30個數(shù)組，其相應元素需要進行相乘并求和。數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯誤值#VALUE!。該函數(shù)將非數(shù)值型的數(shù)組元素作為0處理。SUMPRODUCT函數(shù)作用：計算工作表內(nèi)多列中對應值相乘之人事數(shù)據(jù)的多字段多條件求和計數(shù)例：統(tǒng)計性別為男性且年齡在30歲以上的人數(shù)方法：

=SUMPRODUCT(($E$2:$E$18=I11)*($F$2:$F$18>I12))人事數(shù)據(jù)的多字段多條件求和計數(shù)例：統(tǒng)計性別為男性且年齡在30DSUM數(shù)據(jù)庫函數(shù)的應用例：統(tǒng)計生產(chǎn)部學歷為本科的員工的獎金合計方法：=DSUM(A1:G18,I19,I15:J16)DSUM數(shù)據(jù)庫函數(shù)的應用例：統(tǒng)計生產(chǎn)部學歷為本科的員工的獎金DSUM函數(shù)DSUM函數(shù)，按條件求和語法形式:DSUM(database,field,criteria)其中：Database是區(qū)域field是列數(shù)criteria是條件DSUM函數(shù)DSUM函數(shù)，按條件求和DSUM函數(shù)例如：ABC1產(chǎn)品名稱產(chǎn)品單價（單位：元）總銷售提成2A111003B100104C200305D20406E45507F888089條件產(chǎn)品單價（單位：元）結(jié)果10>50120單價大于50元的產(chǎn)品的銷售提成總額=DSUM(A1:C7,3,B9:B10)DSUM函數(shù)例如：ABC1產(chǎn)品名稱產(chǎn)品單價（單位：元）總銷4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平案例背景

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

4.3.1編制員工學歷透視表

4.3.2制作員工學歷透視圖

4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平案例背景4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平最終效果展示員工學歷透視表員工學歷透視圖

計數(shù)項:學歷學歷

隸屬部門本科博士碩士?？瓶傆嬌a(chǎn)部622313銷售部22行政部112總計9224174.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平最終效果展示4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平關鍵技術點

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。數(shù)據(jù)透視表的應用數(shù)據(jù)透視圖的應用4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平關鍵技術點

4.3.1編制員工學歷透視表案例背景

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

4.3.2制作員工學歷透視圖

4.3.1編制員工學歷透視表案例背景4.3.2制作員工學歷透視圖案例背景

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

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

4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表2、數(shù)據(jù)透視表的作用:能夠改變數(shù)據(jù)表的行、列布局;能夠快速匯總大量數(shù)據(jù);能夠基于原數(shù)據(jù)表創(chuàng)建數(shù)據(jù)分組，并對分組進行匯總統(tǒng)計。4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表包括：分頁字段、數(shù)據(jù)項、行字段、列字段、項目等元素數(shù)據(jù)項列字段頁字段行字段4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表解釋:行：拖放到行中的數(shù)據(jù)字段中的每個數(shù)據(jù)項將占據(jù)透視表的一行。列：拖放到列中的數(shù)據(jù)字段中的每個數(shù)據(jù)項將占據(jù)透視表的一列。行和列確定一個二維表格.頁：拖放在頁中的字段，Excel將按該字段的數(shù)據(jù)項對透視表進行分頁。數(shù)據(jù)：進行計數(shù)或匯總的字段名稱。4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表3、數(shù)據(jù)透視表的建立點擊菜單命令“數(shù)據(jù)→數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂υ捒颉５谝徊剑缦聢D所示，選擇“MicrosoftExcel數(shù)據(jù)列表或數(shù)據(jù)庫”及下面的“數(shù)據(jù)透視表”單選項。4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表3、數(shù)據(jù)透視表的建立第二步，如下圖所示，在“選定區(qū)域”輸入全部數(shù)據(jù)所在的單元格區(qū)域，或者點擊輸入框右側(cè)的“壓縮對話”按鈕，在工作表中用鼠標選定數(shù)據(jù)區(qū)域。第三步，在對話框中選定“新建工作表”單選項，以便將創(chuàng)建的數(shù)據(jù)透視表放到一個新的工作表中，再點擊“完成”按鈕，如下圖所示。4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表3、數(shù)據(jù)透視表的建立這樣，就可以建立一個空的數(shù)據(jù)透視表，并同時顯示“數(shù)據(jù)透視表”工具欄和“數(shù)據(jù)透視表字段列表”對話框，如圖5所示。

4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表4、利用數(shù)據(jù)透視表得到需要的結(jié)果

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

概要函數(shù)：即數(shù)據(jù)透視表中使用的透視函數(shù)。如：求和、計數(shù)、平均、百分比、最大值、標準偏差等。操作方法：選擇數(shù)據(jù)透視表中數(shù)據(jù)字段；在對象菜單或數(shù)據(jù)透視表工具欄中選擇[字段設置]；在數(shù)據(jù)透視表對話框的匯總方式列表中選擇所需函數(shù)。4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平例：數(shù)據(jù)列表是以列表形式存在的數(shù)據(jù)表格，它是一維的表格。而在實際工作中，用戶的數(shù)據(jù)往往是以二維表格的形式存在的，如下圖所示：純牛奶乳酸飲料酸牛奶奶茶奶片大林店93165784837金井店12925224935179和平路店5167382114258新陽路店260738220554解放路店73358390113285延安路店39257248330186四橋店3601995230139古尖店48142165136105東單店5320233246664.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平例：純牛奶乳4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平對于這樣的數(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)計（2）各店鋪商品中銷量最大的商品銷量4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平對于這樣的數(shù)4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.1編制員工學歷透視表7、刪除數(shù)據(jù)透視表的方法利用數(shù)據(jù)透視表工具欄的“數(shù)據(jù)透視表”按鈕→“選定”→“整張表格”，再利用“編輯”→“清除”→“全部”4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.14.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.3.2制作員工學歷透視圖

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

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

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

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

4.4.3利用VLOOKUP函數(shù)比照核對兩表數(shù)據(jù)4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對案例背景4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對最終效果展示使用條件格式對比使用VLOOKUP函數(shù)對比使用數(shù)據(jù)透視表對比

4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對最終效果展示4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對關鍵技術點

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。條件格式的應用數(shù)據(jù)透視表的應用VLOOKUP函數(shù)的應用NOT函數(shù)OR函數(shù)4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對關鍵技術點

要實現(xiàn)本例中4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.4.1利用“條件格式”比照核對兩表格數(shù)據(jù)將D列中的身份證號碼與H列的身份證號碼進行比較，若不一致則顯示為黃色底紋。方法：設置條件格式，設置公式為=NOT(OR(D2=H$2:H$9))ABCDEFGH1工號隸屬部門姓名身份證號碼工號隸屬部門姓名身份證號碼225生產(chǎn)部張三33020219760428123388技術部Sun120302198806051611314生產(chǎn)部李四31030219810101012325生產(chǎn)部張三330202197604281233456生產(chǎn)部王五33090219771208213230行政部Apple3303021976042812335109生產(chǎn)部趙六21030219760428121114生產(chǎn)部李四310302198101110123676銷售部Lemon32030219631111021756生產(chǎn)部王五33090219771208213230行政部Apple3303021976042812337行政部Gary3303021986033001357行政部Gary33030219860330013576銷售部Lemon32030219631111021788技術部Sun120302198805061611109生產(chǎn)部趙六2103021967042812114.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.4.1利用“條件格式”O(jiān)R（）函數(shù)作用：OR函數(shù)指在其參數(shù)組中，任何一個參數(shù)邏輯值為TRUE，即返回TRUE。它與AND函數(shù)的區(qū)別在于，AND函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個為真即可為真。例如：對員工的考核成績進行綜合評定。本例使用OR函數(shù)來判斷一組考評數(shù)據(jù)中是否有一個大于“60”，如果有，該員工就具備晉級的資格，否則取消資格。=IF(OR(C2>60,D2>60),"晉級資格","取消資格")，按下回車鍵，即可判斷C2、D2單元格中的值，只要其中有一項大于60，利用IF函數(shù)顯示“晉級資格”，如果兩項都小于60，利用IF函數(shù)顯示“取消資格”。OR（）函數(shù)作用：OR函數(shù)指在其參數(shù)組中，任何一個參數(shù)邏輯值NOT（）函數(shù)作用：NOT函數(shù)用于對參數(shù)值求反。當要確保一個值不等于某一特定值時，可以使用NOT函數(shù)。簡言之，就是當參數(shù)值為TRUE時，NOT函數(shù)返回的結(jié)果恰與之相反，結(jié)果為FALSE.語法：NOT（logical）公式：=NOT（FALSE）表示對FALSE求反（TRUE）例如：NOT(2+2=4)，由于2+2的結(jié)果的確為4，該參數(shù)結(jié)果為TRUE，由于是NOT函數(shù)，因此返回函數(shù)結(jié)果與之相反，為FALSE。NOT（）函數(shù)作用：NOT函數(shù)用于對參數(shù)值求反。當要確保一個4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.4.2利用“數(shù)據(jù)透視表”比照核對兩表格數(shù)據(jù)步驟：1、將“數(shù)據(jù)表一”和“數(shù)據(jù)表二”中的內(nèi)容復制至“合并表”中2、將“姓名”和“身份證號”字段拖至“行字段”4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.4.2利用“數(shù)據(jù)透視表4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.4.3利用VLOOKUP函數(shù)比照核對兩表數(shù)據(jù)步驟：步驟:1、從“原始表”中查找數(shù)據(jù)=IF(ISERROR(VLOOKUP(A2,原始表!$A$1:$B$27,2,0)),"",

VLOOKUP(A2,原始表!$A$1:$B$27,2,0))2、判斷兩個表格中的數(shù)據(jù)是否一致=IF(B2<>D2,"有誤","")4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.4.3利用VLOOKU4.5員工人事信息數(shù)據(jù)查詢表案例背景在人事信息基礎數(shù)據(jù)表的基礎上可以實現(xiàn)每人一張員工信息卡片的制作，并且可以自定義相關的信息隨時打印。

在實際工作中為了保證信息及時、準確，應注意一下兩點。

1、人事信息填寫完整，不缺頁、漏頁。2、人事信息應根據(jù)人員增減和信息變化隨時進行動態(tài)調(diào)整。

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

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

4.5員工人事信息數(shù)據(jù)查詢表案例背景4.5員工人事信息數(shù)據(jù)查詢表最終效果展示

員工信息卡片所屬部門生產(chǎn)部工號106姓名劉身份證號別女職稱工程師現(xiàn)任職務科員聯(lián)系電話12345681聯(lián)系地址杭州市某區(qū)某路4號4.5員工人事信息數(shù)據(jù)查詢表最終效果展示員工信息卡片所屬部4.5員工人事信息數(shù)據(jù)查詢表關鍵技術點

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。VLOOKUP函數(shù)的應用4.5員工人事信息數(shù)據(jù)查詢表關鍵技術點

要實現(xiàn)本例中的4.5員工人事信息數(shù)據(jù)查詢表4.5.1VLOOKUP函數(shù)查詢?nèi)藛T信息=IF(ISERROR(VLOOKUP(B3,人事數(shù)據(jù)表!$B$3:$D$33,2,0)),"",VLOOKUP(B3,人事數(shù)據(jù)表!$B$3:$D$33,2,0))4.5.2美化和打印表格（員工信息卡片）4.5員工人事信息數(shù)據(jù)查詢表4.5.1VLOOKUP函數(shù)4.6統(tǒng)計不同年齡段員工信息案例背景人力資源部在人事信息統(tǒng)計的過程中經(jīng)常需要按照不同的年齡段分組職工，從而獲知不同年齡段職工的人數(shù)情況。當員工人數(shù)眾多時，人力資源工作者借助Excel里的相關函數(shù)就能夠精確、輕松地完成該項工作。

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

4.6.2使用FREQUENCY數(shù)組公式法統(tǒng)計分段4.6統(tǒng)計不同年齡段員工信息案例背景4.6統(tǒng)計不同年齡段員工信息最終效果展示

4.6統(tǒng)計不同年齡段員工信息最終效果展示4.6統(tǒng)計不同年齡段員工信息關鍵技術點

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。COUNTIF函數(shù)的應用FREQUENCY數(shù)組公式的應用4.6統(tǒng)計不同年齡段員工信息關鍵技術點

要實現(xiàn)本例中的4.6統(tǒng)計不同年齡段員工信息4.6.1應用COUNTIF函數(shù)統(tǒng)計分段信息=COUNTIF($F$3:$F$17,H5)-SUM(I$4:I4)或=COUNTIF($F$3:$F$17,K5)-COUNTIF($F$3:$F$17,H4)4.6.2使用FREQUENCY數(shù)組公式法統(tǒng)計分段=FREQUENCY(F3:F17,H13:H16)按ctrl+shift+enter組合鍵確認4.6統(tǒng)計不同年齡段員工信息4.6.1應用COUNTIFExcel在商務中的應用任課教師：李磊Excel在商務中的應用任課教師：李磊第4章人事信息數(shù)據(jù)統(tǒng)計分析本章要點：4.1人事信息數(shù)據(jù)表4.2人事數(shù)據(jù)的條件求和計數(shù)4.3用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析員工學歷水平4.4人事數(shù)據(jù)表的兩表數(shù)據(jù)核對4.5員工人事信息數(shù)據(jù)查詢表（員工信息卡片）4.6統(tǒng)計不同年齡段員工信息4.7人力資源月報動態(tài)圖表第4章人事信息數(shù)據(jù)統(tǒng)計分析本章要點：第4章人事信息數(shù)據(jù)統(tǒng)計分析第4章人事信息數(shù)據(jù)統(tǒng)計分析

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

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

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

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數(shù)計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格

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

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

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

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

4.2.4

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

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

4.3.1編制員工學歷透視表

4.3.2制作員工學歷透視圖

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

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

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

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

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

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

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

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

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

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

4.7人力資源月報動態(tài)圖表

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

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

4.7.3建立窗體控件

4.7.4對數(shù)據(jù)自動降序排列

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

4.7.6繪制柏拉圖

4.7.7美化圖表區(qū)

第4章人事信息數(shù)據(jù)統(tǒng)計分析第4章人事信息數(shù)據(jù)統(tǒng)計分析

4.1人事信息數(shù)據(jù)表案例背景企業(yè)里由于人員較多且流動變化大，因而人力資源部應及時做好人事數(shù)據(jù)的整理、匯總分析等工作，并且這些數(shù)據(jù)常常也是企業(yè)做各項決策的參考依據(jù)，因此處理好人事數(shù)據(jù)的整理工作意義重大。人事信息數(shù)據(jù)表是企業(yè)進行人事信息管理的基礎和依據(jù)，因此，人事信息管理表格一定要科學、準確、詳細，并且有利于查找、利用，這樣才能真正輔助企業(yè)管理者進行人事信息管理工作。

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

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

4.1.3身份證號中提取生日、性別等有效信息

4.1.4應用DATEDIF函數(shù)計算員工年齡

4.1.5設置每頁頂端標題行和底端標題行

4.1.6美化表格4.1人事信息數(shù)據(jù)表案例背景4.1人事信息數(shù)據(jù)表最終效果展示

某某有限公司員工人事信息表序號工號姓名隸屬部門學歷身份證號生日性別計算年齡（-年-月-日）年齡職稱現(xiàn)任職務聯(lián)系電話居住地址168胡生產(chǎn)部本科1201051976101200341976-10-12男35年6個月26天35年工程師部長12345678杭州市某區(qū)某路1號214徐生產(chǎn)部?？?201021969080331681969-8-3女42年9個月4天42年助工科員12345679杭州市某區(qū)某路2號355楊生產(chǎn)部碩士1201021947100111701947-10-1男64年7個月6天64年無科員12345680杭州市某區(qū)某路3號4106劉生產(chǎn)部?？?201101959031418291959-3-14女53年1個月24天53年工程師科員12345681杭州市某區(qū)某路4號5107李銷售部本科1201026811151711968-11-15男43年5個月23天43年工程師部長12345682杭州市某區(qū)某路5號6114林銷售部本科1201021982022811641982-2-28女30年2個月10天30年助工科員12345683杭州市某區(qū)某路6號7118童行政部?？?201107505153921975-5-15女36年11個月23天36年助工部長12345684杭州市某區(qū)某路7號869王行政部本科1201051983032530361983-3-25男29年1個月13天29年無科員12345685杭州市某區(qū)某路8號9236李生產(chǎn)部本科1201051983032530361983-3-25男29年1個月13天29年工程師部長12345686杭州市某區(qū)某路9號10237趙生產(chǎn)部?？?201021963012011691963-1-20女49年3個月18天49年工程師科員12345687杭州市某區(qū)某路10號11238劉生產(chǎn)部高中1201017702221511977-2-22男35年2個月16天35年工程師科員12345688杭州市某區(qū)某路11號12239馬生產(chǎn)部高中1201021974050707261974-5-7女38年0個月0天38年工程師科員12345689杭州市某區(qū)某路12號13240胡生產(chǎn)部本科1201011978050207251978-5-2女34年0個月5天34年助工科員12345690杭州市某區(qū)某路13號14241林生產(chǎn)部本科1201051972011933181972-1-19男40年3個月19天40年助工科員12345691杭州市某區(qū)某路14號15242童生產(chǎn)部碩士1201021958122316291958-12-23女53年4個月15天53年助工科員12345692杭州市某區(qū)某路15號4.1人事信息數(shù)據(jù)表最終效果展示

某某有限公司員工人事信息創(chuàng)建人事信息數(shù)據(jù)表人事信息數(shù)據(jù)表包括項目有：姓名、性別、年齡、身份證號（或社會保障號）、學歷、現(xiàn)任職務、聯(lián)系電話、E-mail和居住地等有效信息。其中性別、年齡、出生日期等項目均不需要手工錄入，利用函數(shù)計算即可。創(chuàng)建人事信息數(shù)據(jù)表人事信息數(shù)據(jù)表包括項目有：姓名、性別、年齡身份證號中提取生日、性別等有效信息建立人事信息數(shù)據(jù)表時，性別和出生日期兩個項目可以不必手工錄入，而是利用函數(shù)通過身份證號碼來自動提取即可。身份證號中提取生日、性別等有效信息建立人事信息數(shù)據(jù)表時，性別分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號碼，其中都保存了相關的個人信息。15位身份證號碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。例如:某員工的身份證號碼(15位)是320521720807024，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯，核對時也只需要對身份證號碼進行檢查，肯定可以大大提高工作效率。分析身份證號碼其實，身份證號碼與一個人的性別、出生年月、籍貫分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女，第18位為校驗位。分析身份證號碼18位身份證號碼：第7、8、9、10位為出生年4.1人事信息數(shù)據(jù)表關鍵技術點（方法1）

要實現(xiàn)本例中的功能，讀者應當掌握以下Excel技術點。DATEIF函數(shù)RIGHT函數(shù)LEFT函數(shù)MID函數(shù)LEN函數(shù)4.1人事信息數(shù)據(jù)表關鍵技術點（方法1）

要實現(xiàn)本例中編號姓名身份證號碼出生日期A001陳雙雙3427018009138841980年09月13日從身份證中提取出生日期=--TEXT(MID(F3,7,6+(LEN(F3)=18)*2),"#-00-00")結(jié)果顯示形式：19XX-XX-XX分析：1、前面的兩個減號表示要返回數(shù)值型結(jié)果

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

則：6+1*2=8或6+0*2編號姓名身份證號碼出生日期A001陳雙雙342701ABCD1編號姓名身份證號碼出生日期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、前面的兩個減號表示要返回數(shù)值型結(jié)果

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

則：6+1*2=8或6+0*2ABCD1編號姓名身份證號碼出生日期2A001陳雙雙ABCD1編號姓名身份證號碼出生日期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、前面的兩個減號表示要返回數(shù)值型結(jié)果

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

則：6+1*2=8或6+0*2ABCD1編號姓名身份證號碼出生日期2A001陳雙雙=IF(c2="","",IF(MOD(RIGHT(LEFT(c2,17)),2),"男","女"))分析:left(C17,17)：截取單元格C17的前17位數(shù)right(left(C17,17))其實省略了一個參數(shù)，完整的應該是：right(left(C17,17),1)意思是：截取left()所得的結(jié)果的最后一位數(shù)。MOD(RIGHT(LEFT(C17,17)),2)是以上面的結(jié)果除以2的余數(shù)最后的if()函數(shù)是用來判斷的，如果余數(shù)為有余數(shù)則返回“男”，沒有余數(shù)則返回“女