人事信息数据统计分析课件

Excel在商务中的应用任课教师：李磊Excel在商务中的应用任课教师：李磊第4章人事信息数据统计分析本章要点：4.1人事信息数据表4.2人事数据的条件求和计数4.3用数据透视表和数据透视图分析员工学历水平4.4人事数据表的两表数据核对4.5员工人事信息数据查询表（员工信息卡片）4.6统计不同年龄段员工信息4.7人力资源月报动态图表第4章人事信息数据统计分析本章要点：第4章人事信息数据统计分析第4章人事信息数据统计分析

4.1人事信息数据表

4.1.1创建人事信息数据表

4.1.2利用数据有效性防止工号重复输入

4.1.3身份证号中提取生日、性别等有效信息

4.1.4应用DATEDIF函数计算员工年龄

4.1.5设置每页顶端标题行和底端标题行

4.1.6美化表格

4.2人事数据的条件求和计数

4.2.1人事数据的单字段单条件求和计数

4.2.2人事数据的单字段多条件求和计数

4.2.3人事数据的多字段多条件求和计数

4.2.4

DSUM数据库函数的应用

4.3用数据透视表和数据透视图分析员工学历水平

4.3.1编制员工学历透视表

4.3.2制作员工学历透视图

4.4人事数据表的两表数据核对

4.4.1利用“条件格式”比照核对两表格数据

4.4.2利用“数据透视表”比照核对两表格数据

4.4.3利用VLOOKUP函数比照核对两表数据

4.5员工人事信息数据查询表（员工信息卡片）

4.5.1VLOOKUP函数查询人员信息

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

4.6统计不同年龄段员工信息

4.6.1应用COUNTIF函数统计分段信息

4.6.2使用FREQUENCY数组公式法统计分段信息

4.7人力资源月报动态图表

4.7.1创建数据源工作表

4.7.2数据分析汇总

4.7.3建立窗体控件

4.7.4对数据自动降序排列

4.7.5定义动态数据区域名称

4.7.6绘制柏拉图

4.7.7美化图表区

第4章人事信息数据统计分析第4章人事信息数据统计分析

4.1人事信息数据表案例背景企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事数据的整理、汇总分析等工作，并且这些数据常常也是企业做各项决策的参考依据，因此处理好人事数据的整理工作意义重大。人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事信息管理表格一定要科学、准确、详细，并且有利于查找、利用，这样才能真正辅助企业管理者进行人事信息管理工作。

4.1.1创建人事信息数据表

4.1.2利用数据有效性防止工号重复输入

4.1.3身份证号中提取生日、性别等有效信息

4.1.4应用DATEDIF函数计算员工年龄

4.1.5设置每页顶端标题行和底端标题行

4.1.6美化表格4.1人事信息数据表案例背景4.1人事信息数据表最终效果展示

某某有限公司员工人事信息表序号工号姓名隶属部门学历身份证号生日性别计算年龄（-年-月-日）年龄职称现任职务联系电话居住地址168胡生产部本科1201051976101200341976-10-12男35年6个月26天35年工程师部长12345678杭州市某区某路1号214徐生产部专科1201021969080331681969-8-3女42年9个月4天42年助工科员12345679杭州市某区某路2号355杨生产部硕士1201021947100111701947-10-1男64年7个月6天64年无科员12345680杭州市某区某路3号4106刘生产部专科1201101959031418291959-3-14女53年1个月24天53年工程师科员12345681杭州市某区某路4号5107李销售部本科1201026811151711968-11-15男43年5个月23天43年工程师部长12345682杭州市某区某路5号6114林销售部本科1201021982022811641982-2-28女30年2个月10天30年助工科员12345683杭州市某区某路6号7118童行政部专科1201107505153921975-5-15女36年11个月23天36年助工部长12345684杭州市某区某路7号869王行政部本科1201051983032530361983-3-25男29年1个月13天29年无科员12345685杭州市某区某路8号9236李生产部本科1201051983032530361983-3-25男29年1个月13天29年工程师部长12345686杭州市某区某路9号10237赵生产部专科1201021963012011691963-1-20女49年3个月18天49年工程师科员12345687杭州市某区某路10号11238刘生产部高中1201017702221511977-2-22男35年2个月16天35年工程师科员12345688杭州市某区某路11号12239马生产部高中1201021974050707261974-5-7女38年0个月0天38年工程师科员12345689杭州市某区某路12号13240胡生产部本科1201011978050207251978-5-2女34年0个月5天34年助工科员12345690杭州市某区某路13号14241林生产部本科1201051972011933181972-1-19男40年3个月19天40年助工科员12345691杭州市某区某路14号15242童生产部硕士1201021958122316291958-12-23女53年4个月15天53年助工科员12345692杭州市某区某路15号4.1人事信息数据表最终效果展示

某某有限公司员工人事信息创建人事信息数据表人事信息数据表包括项目有：姓名、性别、年龄、身份证号（或社会保障号）、学历、现任职务、联系电话、E-mail和居住地等有效信息。其中性别、年龄、出生日期等项目均不需要手工录入，利用函数计算即可。创建人事信息数据表人事信息数据表包括项目有：姓名、性别、年龄身份证号中提取生日、性别等有效信息建立人事信息数据表时，性别和出生日期两个项目可以不必手工录入，而是利用函数通过身份证号码来自动提取即可。身份证号中提取生日、性别等有效信息建立人事信息数据表时，性别分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。例如:某员工的身份证号码(15位)是320521720807024，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯分析身份证号码18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女，第18位为校验位。分析身份证号码18位身份证号码：第7、8、9、10位为出生年4.1人事信息数据表关键技术点（方法1）

要实现本例中的功能，读者应当掌握以下Excel技术点。DATEIF函数RIGHT函数LEFT函数MID函数LEN函数4.1人事信息数据表关键技术点（方法1）

要实现本例中编号姓名身份证号码出生日期A001陈双双3427018009138841980年09月13日从身份证中提取出生日期=--TEXT(MID(F3,7,6+(LEN(F3)=18)*2),"#-00-00")结果显示形式：19XX-XX-XX分析：1、前面的两个减号表示要返回数值型结果

2、函数公式中的“*”表示IF函数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")结果显示形式：19XX-XX-XX分析：1、前面的两个减号表示要返回数值型结果

2、函数公式中的“*”表示IF函数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")结果显示形式：19XX-XX-XX分析：1、前面的两个减号表示要返回数值型结果

2、函数公式中的“*”表示IF函数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位数right(left(C17,17))其实省略了一个参数，完整的应该是：right(left(C17,17),1)意思是：截取left()所得的结果的最后一位数。MOD(RIGHT(LEFT(C17,17)),2)是以上面的结果除以2的余数最后的if()函数是用来判断的，如果余数为有余数则返回“男”，没有余数则返回“女”。其实该公式被简化了，完整的应该是：=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函数用途：得到字符串左部指定个数的字符。语法形式：LEFT(string,n)参数：string指定要提取子串的字符串。

n指定子串长度返回值String。说明：函数执行成功时返回string字符串左边n个字符，发生错误时返回空字符串（""）。如果任何参数的值为NULL，Left()函数返回NULL。如果n的值大于string字符串的长度，那么Left()函数返回整个string字符串，但并不增加其它字符。LEFT函数用途：得到字符串左部指定个数的字符。LEFT函数实例：如果A1=安徽省蚌埠市固镇县杨庙乡，则公式“=LEFT(A1,FIND("省",A1))”返回安徽省。

DimAnyString,MyStr

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

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

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

MyStr=Left(AnyString,10)'返回"HelloWorl"。LEFT函数实例：4.1人事信息数据表关键技术点（方法2）

要实现本例中的功能，读者应当掌握以下Excel技术点。IF（）函数CHOOSE（）函数MOD（）函数LEN（）函数RIGHT（）函数MID（）函数DATE（）函数4.1人事信息数据表关键技术点（方法2）

要实现本例中IF函数的应用IF函数是根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），从而返回其相对应得内容。语法形式：IF(logical_test,value_if_true,value_if_false)其中：logical_test逻辑判断表达式value_if_true当判断条件为逻辑“真”（TRUE）时，显示该处给定的内容。如忽略，返回TRUE。value_if_false当判断条件为逻辑“假”（FALSE）时，显示该处给定的内容。如忽略，返回FALSE。IF(0=1,"男","女")结果为女IF函数的应用IF函数是根据指定的条件来判断其“真”（TRULEN函数LEN函数用于返回字符串的长度。语法形式：LEN(text)其中，text表示要查找其长度的字符串文本。ABC1员工姓名身份证号码位数2赵荣378501601517897153王勇3425076508053247164吴小平364501197804058246185陈果342501199010182233819=LEN(B2)LEN函数LEN函数用于返回字符串的长度。ABC1员工姓名身练习1、判断身份证号码的长度，显示不同信息身份证号码的长度为15位的，显示“老身份证号长度”身份证号码的长度为18位的，显示“新身份证号长度”ABCD1员工姓名身份证号码位数结果2赵荣37850160151789715老身份证号长度3吴小平36450119780405824618新身份证号长度练习1、判断身份证号码的长度，显示不同信息ABCD1员工姓名MID函数MID函数用于从文本字符串中提取指定位置开始的特定字符。语法格式：

MID（text，start_num，num_chars）text：要提取字符的文本字符串start_num：从文本字符串中要提取第一个字符的开始位置num_chars：提取字符的个数=MID(A3,4,8)ABC1文本字符串提取条件提取结果201062791976从开始位置提取3个字符010301062791976从第4个字符位置提取8个字符62791976MID函数MID函数用于从文本字符串中提取指定位置开始的特定练习获取身份证号码中的“年份”数值员工姓名身份证号码位数结果完整形式赵荣378501601517897从第7位提取2个字符601960年吴小第7位提取4个字符19781978年练习获取身份证号码中的“年份”数值员工姓名身份证号码位数MOD函数MOD函数用于求两个数值相除后的余数，其结果的正负号与除数相同。语法格式：MOD(number,divisor)Number:指定的被除数数值Diisor:指定的除数数值,并且不能为0值ABC1被除数除数结果2113231543=MOD(A2,B2)=MOD(A3,B3)MOD函数MOD函数用于求两个数值相除后的余数，其结果的正负练习通过获取身份证号码中的“性别”的数值来判断“性别”15位身份证号码：第15位代表性别，奇数为男，偶数为女。18位身份证号码：第17位代表性别，奇数为男，偶数为女。员工姓名身份证号码位数“性别”数值奇偶性判断性别赵荣378501601517897从第15位提取1个字符71男吴小第17位提取1个字符40女练习通过获取身份证号码中的“性别”的数值来判断“性别”员工姓RIGHT函数的应用作用：从字符串右端取指定个数字符。语法形式：RIGHT(text,num_chars)其中：Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。Num_chars指定希望RIGHT提取的字符数。它

必须大于或等于0。如果num_chars大于文本长度，则RIGHT返回所有文本。如果忽略num_chars，则假定其为1。例：字符串公式要求结果HelloWorld

=RIGHT(A3,5)最后5个字符World宁夏理工学院经济管理系

=RIGHT(A4,5)最后5个字符经济管理系RIGHT函数的应用作用：从字符串右端取指定个数字符。字符DATE函数的应用作用：返回代表特定日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。

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

其中：Year参数year可以为一到四位数字。MicrosoftExcel将根据所使用的日期系统来解释year参数。默认情况下，MicrosoftExcelforWindows将使用1900日期系统，而MicrosoftExcelforMacintosh将使用1904日期系统。Month代表一年中从1月到12月（一月到十二月）各月的正整数或负整数。

Day代表一月中从1日到31日各天的正整数或负整数。例如A5为2010，B5为3，C5为5DATE(A5,B5,C5)结果：表示转为日期格式的2010-3-5DATE函数的应用作用：返回代表特定日期的序列号。如果在输入编号姓名身份证号码出生日期A001陈双双3427018009138841980年09月13日从身份证中提取出生日期DATE("19"&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2))结果显示形式：19XX-XX-XXMID函数：从文本字符串中提取指定位置开始的特定字符MID(C3,7,2),分析：从C3单元格中数值的第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单元格中数值的第15位开始取1位（取4）MOD(4,2)返回4除以2的余数，结果为0IF(0=1,"男","女")结果为女从身份证中提取性别编号姓名身份证号码出生日期HSR1003陈双双3427018009138841980年09月13日=IF(LEN(C3)=15,从身份证中提取性别编号应用DATEDIF函数计算员工年龄编制提取员工年龄公式（格式为“多少年多少月多少天”）=DATEDIF(G3,TODAY(),"y")&"年"&DATEDIF(G3,TODAY(),"ym")&"个月"&DATEDIF(G3,TODAY(),"md")&"天"编制提取员工年龄公式（格式为“多少年”）=DATEDIF(G3,TODAY(),"y")&"年"应用DATEDIF函数计算员工年龄编制提取员工年龄公式（格式补充：CHOOSE函数的应用作用：从参数列表中选择并返回一个值。函数CHOOSE可以使用index_num返回数值参数清单中的数值。使用函数CHOOSE可以基于索引号返回多达29个待选数值中的任一数值。语法形式：CHOOSE(index_num,value1,value2,...)其中：Index_num用以指明待选参数序号的参数值。Index_num必须为1到29之间的数字、或者是包含数字1到29的公式或单元格引用。Value1,value2,...为1到29个数值参数，函数CHOOSE基于index_num，从中选择一个数值或执行相应的操作。参数可以为数字、单元格引用，已定义的名称、公式、函数或文本。例如公式“=CHOOSE(2，”电脑“，”爱好者“)返回“爱好者”。公式“=SUM(A1:CHOOSE(3，A10，A20，A30))”与公式“=SUM(A1:A30)”等价(因为CHOOSE(3，A10，A20，A30)返回A30)。补充：CHOOSE函数的应用作用：从参数列表中选择并返回一=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单元格中数值的第15位开始取1位（取4）RIGHT(C4,1)取C4单元格中数值的1位（取4）

IF(1,"女","男")结果为女从身份证中提取性别编号姓名身份证号码出生日期A001陈双双3427018009138841980年09月13日=IF(C4="","",从身份证中提取性别编补充：CONCATENATE函数CONCATENATE函数用于将多个文本字符串合并为一个文本字符串。语法格式：

CONCATENATE（text1，text12text13，…）其中，text1，text12text13，…表示将要合并成单个文本项的文本项（这些文本项可以为文本字符串、数字或对单个单元格的引用）。ABC1姓名姓名2赵荣赵荣3王勇王勇4吴小平吴小平5陈果陈果=CONCATENATE(A5,B5)补充：CONCATENATE函数CONCATENATE函数用4.2人事数据的条件求和计数案例背景

Excel的条件求和计数在工作中的应用很广泛。人力资源管理者需要经常对员工信息进行统计分析，比如需要获知人事数据表中学历为本科的员工人数有多少，又比如查询某个年龄段的员工人数等，这些都可以通过活用函数条件求和计数加以解决，进而提高工作的效率。

4.2.1人事数据的单字段单条件求和计数

4.2.2人事数据的单字段多条件求和计数

4.2.3人事数据的多字段多条件求和计数

4.2.4

DSUM数据库函数的应用

4.2人事数据的条件求和计数案例背景4.2人事数据的条件求和计数最终效果展示

序号姓名隶属部门学历性别年龄奖金1胡生产部本科男304002徐生产部专科女372003杨生产部硕士男592004刘生产部专科女482005李销售部本科男382006林销售部本科女252007童行政部专科女322008王行政部本科男244009李生产部本科男2440010赵生产部专科女4440011刘生产部博士男3040012马生产部博士女3340013胡生产部本科女2940014林生产部本科男3530015童生产部硕士女483004.2人事数据的条件求和计数最终效果展示

序号姓名隶属部门4.2人事数据的条件求和计数关键技术点

要实现本例中的功能，读者应当掌握以下Excel技术点。COUNTIF函数SUMPRODUCT函数DSUM函数4.2人事数据的条件求和计数关键技术点

要实现本例中的人事数据的单字段单条件求和计数例：统计所有本科学历人数例：统计年龄大于等于40岁人数方法：使用COUNTIF函数统计数据人事数据的单字段单条件求和计数例：统计所有本科学历人数COUNTIF函数用于计算单元格区域中满足给定条件的单元格的个数。语法形式：COUNTIF（range,criteria)COUNTIF（数据区域,条件表达式)COUNTIF函数用于计算单元格区域中满足给定条件的单元格的COUNTIF函数举例ABC1姓名性别职务2刘勇男经理3李南男职员4陈双双女经理5叶小来男职员6林佳女经理7彭力男主管8范琳琳女职员9易呈亮男经理10黄海燕女职员11男性员工人数512女性员工人数413年龄在30岁以下的人数3=COUNTIF(B2:B10,"男")=COUNTIF(B2:B10,"女")COUNTIF函数举例ABC1姓名性别职务2刘勇男经理3李南人事数据的单字段多条件求和计数例：统计年龄在30岁至40岁的人数方法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))人事数据的单字段多条件求和计数例：统计年龄在30岁至40岁的SUM函数举例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)例：统计年龄在20岁至30岁的人数SUM函数举例ABC1姓名性别年龄2刘勇男183李南男264SUMPRODUCT函数作用：计算工作表内多列中对应值相乘之后的和，即乘积之和。其语法为：SUMPRODUCT(array1,array2,array3,…)其中，Array1,array2,array3,…为2到30个数组，其相应元素需要进行相乘并求和。数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!。该函数将非数值型的数组元素作为0处理。SUMPRODUCT函数作用：计算工作表内多列中对应值相乘之人事数据的多字段多条件求和计数例：统计性别为男性且年龄在30岁以上的人数方法：

=SUMPRODUCT(($E$2:$E$18=I11)*($F$2:$F$18>I12))人事数据的多字段多条件求和计数例：统计性别为男性且年龄在30DSUM数据库函数的应用例：统计生产部学历为本科的员工的奖金合计方法：=DSUM(A1:G18,I19,I15:J16)DSUM数据库函数的应用例：统计生产部学历为本科的员工的奖金DSUM函数DSUM函数，按条件求和语法形式:DSUM(database,field,criteria)其中：Database是区域field是列数criteria是条件DSUM函数DSUM函数，按条件求和DSUM函数例如：ABC1产品名称产品单价（单位：元）总销售提成2A111003B100104C200305D20406E45507F888089条件产品单价（单位：元）结果10>50120单价大于50元的产品的销售提成总额=DSUM(A1:C7,3,B9:B10)DSUM函数例如：ABC1产品名称产品单价（单位：元）总销4.3用数据透视表和数据透视图分析员工学历水平案例背景

Excel的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。

4.3.1编制员工学历透视表

4.3.2制作员工学历透视图

4.3用数据透视表和数据透视图分析员工学历水平案例背景4.3用数据透视表和数据透视图分析员工学历水平最终效果展示员工学历透视表员工学历透视图

计数项:学历学历

隶属部门本科博士硕士专科总计生产部622313销售部22行政部112总计9224174.3用数据透视表和数据透视图分析员工学历水平最终效果展示4.3用数据透视表和数据透视图分析员工学历水平关键技术点

要实现本例中的功能，读者应当掌握以下Excel技术点。数据透视表的应用数据透视图的应用4.3用数据透视表和数据透视图分析员工学历水平关键技术点

4.3.1编制员工学历透视表案例背景

Excel的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。

4.3.2制作员工学历透视图

4.3.1编制员工学历透视表案例背景4.3.2制作员工学历透视图案例背景

Excel的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。

4.3.2制作员工学历透视图案例背景4.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表1、数据透视表：一种交互的、交叉制表的Excel报表，用于对多种来源（包括Excel的外部数据）的数据（如数据库记录）进行汇总和分析。正源于其交互式动态汇总报表的特性，数据透视表即可快速合并和比较大量数据，也可以创建频率分布和多个不同数据维的交叉制表。也就是说，创建一个数据透视表后，可以旋转其行和列以看到源数据的不同汇总结果，而且可以显示不同页面的筛选数据，即根据需要显示区域中的明细数据。

4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表2、数据透视表的作用:能够改变数据表的行、列布局;能够快速汇总大量数据;能够基于原数据表创建数据分组，并对分组进行汇总统计。4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表包括：分页字段、数据项、行字段、列字段、项目等元素数据项列字段页字段行字段4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表解释:行：拖放到行中的数据字段中的每个数据项将占据透视表的一行。列：拖放到列中的数据字段中的每个数据项将占据透视表的一列。行和列确定一个二维表格.页：拖放在页中的字段，Excel将按该字段的数据项对透视表进行分页。数据：进行计数或汇总的字段名称。4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表3、数据透视表的建立点击菜单命令“数据→数据透视表和数据透视图”，打开“数据透视表和数据透视图向导”对话框。第一步，如下图所示，选择“MicrosoftExcel数据列表或数据库”及下面的“数据透视表”单选项。4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表3、数据透视表的建立第二步，如下图所示，在“选定区域”输入全部数据所在的单元格区域，或者点击输入框右侧的“压缩对话”按钮，在工作表中用鼠标选定数据区域。第三步，在对话框中选定“新建工作表”单选项，以便将创建的数据透视表放到一个新的工作表中，再点击“完成”按钮，如下图所示。4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表3、数据透视表的建立这样，就可以建立一个空的数据透视表，并同时显示“数据透视表”工具栏和“数据透视表字段列表”对话框，如图5所示。

4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表4、利用数据透视表得到需要的结果

（1）统计所有学生各学科成绩的汇总数据（2）查看所有学生“新闻学”成绩的平均值”,平均值保留两位小数位数（3）查看男生“新闻学”成绩的平均值”,平均值保留两位小数位数（4）查看“工商1010101班男生的“新闻学”成绩的平均值”,平均值保留两位小数位数（5）将第（2）题“行”与“列”字段互换，并设置“自动套用格式”样式4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表5、修改数据透视表显示或隐藏行、列中的数据项在行或列字段的下拉列表条中选择或取消复选标志。重新组织数据透视表行、列置换：鼠标移至行字段上按住拖动到列字段上在字段内移动个别项：鼠标按在要移动字段边框线上拖动到指定位置放开。在原有行列字段上增加或减少字段增加：单击数据透视表,在数据透视表工具栏上将所需字段拖入行或列的字段处.减少：选择数据透视表行或列的字段名，拖到表外4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表6、修改数据透视表的概要函数

概要函数：即数据透视表中使用的透视函数。如：求和、计数、平均、百分比、最大值、标准偏差等。操作方法：选择数据透视表中数据字段；在对象菜单或数据透视表工具栏中选择[字段设置]；在数据透视表对话框的汇总方式列表中选择所需函数。4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平例：数据列表是以列表形式存在的数据表格，它是一维的表格。而在实际工作中，用户的数据往往是以二维表格的形式存在的，如下图所示：纯牛奶乳酸饮料酸牛奶奶茶奶片大林店93165784837金井店12925224935179和平路店5167382114258新阳路店260738220554解放路店73358390113285延安路店39257248330186四桥店3601995230139古尖店48142165136105东单店5320233246664.3用数据透视表和数据透视图分析员工学历水平例：纯牛奶乳4.3用数据透视表和数据透视图分析员工学历水平对于这样的数据表，用户无法以它为数据源创建理想的数据透视表。只有把二维的数据表格转换为如下图所示的数据列表，才能成为数据透视表的数据源。能作为数据透视表数据源的一维数据列表店铺商品销量大林店纯牛奶93大林店乳酸饮料165大林店酸牛奶78大林店奶茶48大林店奶片37金井店纯牛奶129金井店乳酸饮料252金井店酸牛奶249金井店奶茶35金井店奶片179和平路店纯牛奶5和平路店乳酸饮料167（1）各店铺商品销量求和统计（2）各店铺商品中销量最大的商品销量4.3用数据透视表和数据透视图分析员工学历水平对于这样的数4.3用数据透视表和数据透视图分析员工学历水平4.3.1编制员工学历透视表7、删除数据透视表的方法利用数据透视表工具栏的“数据透视表”按钮→“选定”→“整张表格”，再利用“编辑”→“清除”→“全部”4.3用数据透视表和数据透视图分析员工学历水平4.3.14.3用数据透视表和数据透视图分析员工学历水平4.3.2制作员工学历透视图

数据透视表虽然具有可以准确计算和分析数据的优点，但是通常数据透视表的数据源较大，数据量非常多，数据排列也相当复杂，这时就可以利用数据透视图来更加直观地分析数据。数据透视图以图形形式表示数据表中的数据，和数据透视表一样，可以更改数据透视图的布局和显示的数据。与数据透视表相比，数据透视图展示数据之间关系的方式更加可视化、更易于理解。数据透视图是由系列、分类轴、坐标轴、数据标志、页字段、数据字段、页和分类字段等元素组成。两种创建方式：直接创建数据透视图和利用数据透视表创建数据透视图建立数据透视图表的方法单击数据透视表中的非空单元格，选择“插入”菜单的图表命令，或单击常用工具栏中的图表工具，Excel会自动在当前工作簿中插入一个新的数据透视图。4.3用数据透视表和数据透视图分析员工学历水平4.3.24.4人事数据表的两表数据核对案例背景在实际工作中人力资源部经常需要核对某些数据，除了核对工资外，坏需要核对员工身份证号码、职工姓名、银行账号等信息。这些工作很单调且麻烦。若被核对的字符串简单，直接进行人工处理还是可以的；一旦字符很多，且排列顺序不一，这是人工查找起来就显得比较麻烦。

借助Excel提供的条件格式或者数据透视表功能，复杂的比对难题就可以迎刃而解。此外人力资源工作者利用相关函数，同样也能完成该项工作。

4.4.1利用“条件格式”比照核对两表格数据

4.4.2利用“数据透视表”比照核对两表格数据

4.4.3利用VLOOKUP函数比照核对两表数据4.4人事数据表的两表数据核对案例背景4.4人事数据表的两表数据核对最终效果展示使用条件格式对比使用VLOOKUP函数对比使用数据透视表对比

4.4人事数据表的两表数据核对最终效果展示4.4人事数据表的两表数据核对关键技术点

要实现本例中的功能，读者应当掌握以下Excel技术点。条件格式的应用数据透视表的应用VLOOKUP函数的应用NOT函数OR函数4.4人事数据表的两表数据核对关键技术点

要实现本例中4.4人事数据表的两表数据核对4.4.1利用“条件格式”比照核对两表格数据将D列中的身份证号码与H列的身份证号码进行比较，若不一致则显示为黄色底纹。方法：设置条件格式，设置公式为=NOT(OR(D2=H$2:H$9))ABCDEFGH1工号隶属部门姓名身份证号码工号隶属部门姓名身份证号码225生产部张三33020219760428123388技术部Sun120302198806051611314生产部李四31030219810101012325生产部张三330202197604281233456生产部王五33090219771208213230行政部Apple3303021976042812335109生产部赵六21030219760428121114生产部李四310302198101110123676销售部Lemon32030219631111021756生产部王五33090219771208213230行政部Apple3303021976042812337行政部Gary3303021986033001357行政部Gary33030219860330013576销售部Lemon32030219631111021788技术部Sun120302198805061611109生产部赵六2103021967042812114.4人事数据表的两表数据核对4.4.1利用“条件格式”OR（）函数作用：OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。例如：对员工的考核成绩进行综合评定。本例使用OR函数来判断一组考评数据中是否有一个大于“60”，如果有，该员工就具备晋级的资格，否则取消资格。=IF(OR(C2>60,D2>60),"晋级资格","取消资格")，按下回车键，即可判断C2、D2单元格中的值，只要其中有一项大于60，利用IF函数显示“晋级资格”，如果两项都小于60，利用IF函数显示“取消资格”。OR（）函数作用：OR函数指在其参数组中，任何一个参数逻辑值NOT（）函数作用：NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE.语法：NOT（logical）公式：=NOT（FALSE）表示对FALSE求反（TRUE）例如：NOT(2+2=4)，由于2+2的结果的确为4，该参数结果为TRUE，由于是NOT函数，因此返回函数结果与之相反，为FALSE。NOT（）函数作用：NOT函数用于对参数值求反。当要确保一个4.4人事数据表的两表数据核对4.4.2利用“数据透视表”比照核对两表格数据步骤：1、将“数据表一”和“数据表二”中的内容复制至“合并表”中2、将“姓名”和“身份证号”字段拖至“行字段”4.4人事数据表的两表数据核对4.4.2利用“数据透视表4.4人事数据表的两表数据核对4.4.3利用VLOOKUP函数比照核对两表数据步骤：步骤:1、从“原始表”中查找数据=IF(ISERROR(VLOOKUP(A2,原始表!$A$1:$B$27,2,0)),"",

VLOOKUP(A2,原始表!$A$1:$B$27,2,0))2、判断两个表格中的数据是否一致=IF(B2<>D2,"有误","")4.4人事数据表的两表数据核对4.4.3利用VLOOKU4.5员工人事信息数据查询表案例背景在人事信息基础数据表的基础上可以实现每人一张员工信息卡片的制作，并且可以自定义相关的信息随时打印。

在实际工作中为了保证信息及时、准确，应注意一下两点。

1、人事信息填写完整，不缺页、漏页。2、人事信息应根据人员增减和信息变化随时进行动态调整。

4.5.1VLOOKUP函数查询人员信息

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

4.5员工人事信息数据查询表案例背景4.5员工人事信息数据查询表最终效果展示

员工信息卡片所属部门生产部工号106姓名刘身份证号别女职称工程师现任职务科员联系电话12345681联系地址杭州市某区某路4号4.5员工人事信息数据查询表最终效果展示员工信息卡片所属部4.5员工人事信息数据查询表关键技术点

要实现本例中的功能，读者应当掌握以下Excel技术点。VLOOKUP函数的应用4.5员工人事信息数据查询表关键技术点

要实现本例中的4.5员工人事信息数据查询表4.5.1VLOOKUP函数查询人员信息=IF(ISERROR(VLOOKUP(B3,人事数据表!$B$3:$D$33,2,0)),"",VLOOKUP(B3,人事数据表!$B$3:$D$33,2,0))4.5.2美化和打印表格（员工信息卡片）4.5员工人事信息数据查询表4.5.1VLOOKUP函数4.6统计不同年龄段员工信息案例背景人力资源部在人事信息统计的过程中经常需要按照不同的年龄段分组职工，从而获知不同年龄段职工的人数情况。当员工人数众多时，人力资源工作者借助Excel里的相关函数就能够精确、轻松地完成该项工作。

4.6.1应用COUNTIF函数统计分段信息

4.6.2使用FREQUENCY数组公式法统计分段4.6统计不同年龄段员工信息案例背景4.6统计不同年龄段员工信息最终效果展示

4.6统计不同年龄段员工信息最终效果展示4.6统计不同年龄段员工信息关键技术点

要实现本例中的功能，读者应当掌握以下Excel技术点。COUNTIF函数的应用FREQUENCY数组公式的应用4.6统计不同年龄段员工信息关键技术点

要实现本例中的4.6统计不同年龄段员工信息4.6.1应用COUNTIF函数统计分段信息=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数组公式法统计分段=FREQUENCY(F3:F17,H13:H16)按ctrl+shift+enter组合键确认4.6统计不同年龄段员工信息4.6.1应用COUNTIFExcel在商务中的应用任课教师：李磊Excel在商务中的应用任课教师：李磊第4章人事信息数据统计分析本章要点：4.1人事信息数据表4.2人事数据的条件求和计数4.3用数据透视表和数据透视图分析员工学历水平4.4人事数据表的两表数据核对4.5员工人事信息数据查询表（员工信息卡片）4.6统计不同年龄段员工信息4.7人力资源月报动态图表第4章人事信息数据统计分析本章要点：第4章人事信息数据统计分析第4章人事信息数据统计分析

4.1人事信息数据表

4.1.1创建人事信息数据表

4.1.2利用数据有效性防止工号重复输入

4.1.3身份证号中提取生日、性别等有效信息

4.1.4应用DATEDIF函数计算员工年龄

4.1.5设置每页顶端标题行和底端标题行

4.1.6美化表格

4.2人事数据的条件求和计数

4.2.1人事数据的单字段单条件求和计数

4.2.2人事数据的单字段多条件求和计数

4.2.3人事数据的多字段多条件求和计数

4.2.4

DSUM数据库函数的应用

4.3用数据透视表和数据透视图分析员工学历水平

4.3.1编制员工学历透视表

4.3.2制作员工学历透视图

4.4人事数据表的两表数据核对

4.4.1利用“条件格式”比照核对两表格数据

4.4.2利用“数据透视表”比照核对两表格数据

4.4.3利用VLOOKUP函数比照核对两表数据

4.5员工人事信息数据查询表（员工信息卡片）

4.5.1VLOOKUP函数查询人员信息

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

4.6统计不同年龄段员工信息

4.6.1应用COUNTIF函数统计分段信息

4.6.2使用FREQUENCY数组公式法统计分段信息

4.7人力资源月报动态图表

4.7.1创建数据源工作表

4.7.2数据分析汇总

4.7.3建立窗体控件

4.7.4对数据自动降序排列

4.7.5定义动态数据区域名称

4.7.6绘制柏拉图

4.7.7美化图表区

第4章人事信息数据统计分析第4章人事信息数据统计分析

4.1人事信息数据表案例背景企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事数据的整理、汇总分析等工作，并且这些数据常常也是企业做各项决策的参考依据，因此处理好人事数据的整理工作意义重大。人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事信息管理表格一定要科学、准确、详细，并且有利于查找、利用，这样才能真正辅助企业管理者进行人事信息管理工作。

4.1.1创建人事信息数据表

4.1.2利用数据有效性防止工号重复输入

4.1.3身份证号中提取生日、性别等有效信息

4.1.4应用DATEDIF函数计算员工年龄

4.1.5设置每页顶端标题行和底端标题行

4.1.6美化表格4.1人事信息数据表案例背景4.1人事信息数据表最终效果展示

某某有限公司员工人事信息表序号工号姓名隶属部门学历身份证号生日性别计算年龄（-年-月-日）年龄职称现任职务联系电话居住地址168胡生产部本科1201051976101200341976-10-12男35年6个月26天35年工程师部长12345678杭州市某区某路1号214徐生产部专科1201021969080331681969-8-3女42年9个月4天42年助工科员12345679杭州市某区某路2号355杨生产部硕士1201021947100111701947-10-1男64年7个月6天64年无科员12345680杭州市某区某路3号4106刘生产部专科1201101959031418291959-3-14女53年1个月24天53年工程师科员12345681杭州市某区某路4号5107李销售部本科1201026811151711968-11-15男43年5个月23天43年工程师部长12345682杭州市某区某路5号6114林销售部本科1201021982022811641982-2-28女30年2个月10天30年助工科员12345683杭州市某区某路6号7118童行政部专科1201107505153921975-5-15女36年11个月23天36年助工部长12345684杭州市某区某路7号869王行政部本科1201051983032530361983-3-25男29年1个月13天29年无科员12345685杭州市某区某路8号9236李生产部本科1201051983032530361983-3-25男29年1个月13天29年工程师部长12345686杭州市某区某路9号10237赵生产部专科1201021963012011691963-1-20女49年3个月18天49年工程师科员12345687杭州市某区某路10号11238刘生产部高中1201017702221511977-2-22男35年2个月16天35年工程师科员12345688杭州市某区某路11号12239马生产部高中1201021974050707261974-5-7女38年0个月0天38年工程师科员12345689杭州市某区某路12号13240胡生产部本科1201011978050207251978-5-2女34年0个月5天34年助工科员12345690杭州市某区某路13号14241林生产部本科1201051972011933181972-1-19男40年3个月19天40年助工科员12345691杭州市某区某路14号15242童生产部硕士1201021958122316291958-12-23女53年4个月15天53年助工科员12345692杭州市某区某路15号4.1人事信息数据表最终效果展示

某某有限公司员工人事信息创建人事信息数据表人事信息数据表包括项目有：姓名、性别、年龄、身份证号（或社会保障号）、学历、现任职务、联系电话、E-mail和居住地等有效信息。其中性别、年龄、出生日期等项目均不需要手工录入，利用函数计算即可。创建人事信息数据表人事信息数据表包括项目有：姓名、性别、年龄身份证号中提取生日、性别等有效信息建立人事信息数据表时，性别和出生日期两个项目可以不必手工录入，而是利用函数通过身份证号码来自动提取即可。身份证号中提取生日、性别等有效信息建立人事信息数据表时，性别分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。例如:某员工的身份证号码(15位)是320521720807024，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯分析身份证号码18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女，第18位为校验位。分析身份证号码18位身份证号码：第7、8、9、10位为出生年4.1人事信息数据表关键技术点（方法1）

要实现本例中的功能，读者应当掌握以下Excel技术点。DATEIF函数RIGHT函数LEFT函数MID函数LEN函数4.1人事信息数据表关键技术点（方法1）

要实现本例中编号姓名身份证号码出生日期A001陈双双3427018009138841980年09月13日从身份证中提取出生日期=--TEXT(MID(F3,7,6+(LEN(F3)=18)*2),"#-00-00")结果显示形式：19XX-XX-XX分析：1、前面的两个减号表示要返回数值型结果

2、函数公式中的“*”表示IF函数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")结果显示形式：19XX-XX-XX分析：1、前面的两个减号表示要返回数值型结果

2、函数公式中的“*”表示IF函数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")结果显示形式：19XX-XX-XX分析：1、前面的两个减号表示要返回数值型结果

2、函数公式中的“*”表示IF函数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位数right(left(C17,17))其实省略了一个参数，完整的应该是：right(left(C17,17),1)意思是：截取left()所得的结果的最后一位数。MOD(RIGHT(LEFT(C17,17)),2)是以上面的结果除以2的余数最后的if()函数是用来判断的，如果余数为有余数则返回“男”，没有余数则返回“女