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

,按一下以编辑母片,第二层,第三层,第四层,第五层,按一下以编辑母片标题样式,Excel,在商务中的应用,按一下以编辑母片,第二层,第三层,第四层,第五层,按一下以编辑母片标题样式,Excel,在商务中的应用,任课教师：李磊,第4章,人事,信息数据统计分析,本章要点,：,4.1,人事信息数据表,4.2,人事数据的条件求和计数,4.3,用数据透视表和数据透视图分析员工学历水平,4.4,人事数据表的两表数据核对,4.5,员工人事信息数据查询表（员工信息卡片）,4.6,统计不同年龄段员工信息,4.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.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.1 VLOOKUP,函数查询人员信息,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.1人事信息数据表,案例背景,企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事数据的整理、汇总分析等工作，并且这些数据常常也是企业做各项决策的参考依据，因此处理好人事数据的整理工作意义重大。,人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事信息管理表格一定要科学、准确、详细，并且有利于查找、利用，这样才能真正辅助企业管理者进行人事信息管理工作。,4.1.1,创建人事信息数据表,4.1.2,利用数据有效性防止工号重复输入,4.1.3,身份证号中提取生日、性别等有效信息,4.1.4,应用,DATEDIF,函数计算员工年龄,4.1.5,设置每页顶端标题行和底端标题行,4.1.6,美化表格,4.1人事信息数据表,最终效果展示,某某有限公司员工人事信息表,序号,工号,姓名,隶属部门,学历,身份证号,生日,性别,计算年龄（,-,年,-,月,-,日）,年龄,职称,现任职务,联系电话,居住地址,1,68,胡,生产部,本科,120105197610120034,1976-10-12,男,35,年,6,个月,26,天,35,年,工程师,部长,12345678,杭州市某区某路,1,号,2,14,徐,生产部,专科,120102196908033168,1969-8-3,女,42,年,9,个月,4,天,42,年,助工,科员,12345679,杭州市某区某路,2,号,3,55,杨,生产部,硕士,120102194710011170,1947-10-1,男,64,年,7,个月,6,天,64,年,无,科员,12345680,杭州市某区某路,3,号,4,106,刘,生产部,专科,120110195903141829,1959-3-14,女,53,年,1,个月,24,天,53,年,工程师,科员,12345681,杭州市某区某路,4,号,5,107,李,销售部,本科,120102681115171,1968-11-15,男,43,年,5,个月,23,天,43,年,工程师,部长,12345682,杭州市某区某路,5,号,6,114,林,销售部,本科,120102198202281164,1982-2-28,女,30,年,2,个月,10,天,30,年,助工,科员,12345683,杭州市某区某路,6,号,7,118,童,行政部,专科,120110750515392,1975-5-15,女,36,年,11,个月,23,天,36,年,助工,部长,12345684,杭州市某区某路,7,号,8,69,王,行政部,本科,120105198303253036,1983-3-25,男,29,年,1,个月,13,天,29,年,无,科员,12345685,杭州市某区某路,8,号,9,236,李,生产部,本科,120105198303253036,1983-3-25,男,29,年,1,个月,13,天,29,年,工程师,部长,12345686,杭州市某区某路,9,号,10,237,赵,生产部,专科,120102196301201169,1963-1-20,女,49,年,3,个月,18,天,49,年,工程师,科员,12345687,杭州市某区某路,10,号,11,238,刘,生产部,高中,120101770222151,1977-2-22,男,35,年,2,个月,16,天,35,年,工程师,科员,12345688,杭州市某区某路,11,号,12,239,马,生产部,高中,120102197405070726,1974-5-7,女,38,年,0,个月,0,天,38,年,工程师,科员,12345689,杭州市某区某路,12,号,13,240,胡,生产部,本科,120101197805020725,1978-5-2,女,34,年,0,个月,5,天,34,年,助工,科员,12345690,杭州市某区某路,13,号,14,241,林,生产部,本科,120105197201193318,1972-1-19,男,40,年,3,个月,19,天,40,年,助工,科员,12345691,杭州市某区某路,14,号,15,242,童,生产部,硕士,120102195812231629,1958-12-23,女,53,年,4,个月,15,天,53,年,助工,科员,12345692,杭州市某区某路,15,号,创建人事信息数据表,人事信息数据表包括项目有：姓名、性别、年龄、身份证号（或社会保障号）、学历、现任职务、联系电话、,E-mail,和居住地等有效信息。,其中性别、年龄、出生日期等项目均不需要手工录入，利用函数计算即可。,分析身份证号码,18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女，第18位为校验位。,4.1人事信息数据表,关键技术点（方法,1,）,要实现本例中的功能，读者应当掌握以下,Excel,技术点。,DATEIF,函数,RIGHT,函数,LEFT,函数,MID,函数,LEN,函数,编 号,姓 名,身份证号码,出生日期,A001,陈双双,342701800913884,1980,年,09,月,13,日,从身份证中提取出生日期,=-TEXT(MID(F3,7,6+(LEN(F3)=18)*2),#-00-00),结果显示形式：,19XX-XX-XX,分析：,1,、,前面的两个减号表示要返回数值型结果,2,、函数公式中的“,*”,表示,IF,函数,P136,LEN(F3)=18,返回,0,或返回,1,则：,6+1*2=8,或,6+0*2,A,B,C,D,1,编 号,姓 名,身份证号码,出生日期,2,A001,陈双双,342701800913884,1980,年,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,函数,P136,LEN(F3)=18,返回,0,或返回,1,则：,6+1*2=8,或,6+0*2,A,B,C,D,1,编 号,姓 名,身份证号码,出生日期,2,A001,陈双双,342701198009138841,1980,年,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(19,800913,#-00-00),结果显示形式：,19XX-XX-XX,分析：,1,、,前面的两个减号表示要返回数值型结果,2,、函数公式中的“,*”,表示,IF,函数,P136,LEN(F3)=18,返回,0,或返回,1,则：,6+1*2=8,或,6+0*2,=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,男,女,),从身份证中提取性别,A,B,C,D,1,编 号,姓 名,身份证号码,出生日期,2,A001,陈双双,342701800913884,1980,年,09,月,13,日,RIGHT(LEFT(c2,17),RIGHT(34270119760213857,1)-7,RIGHT(LEFT(c3,17),RIGHT(342701820213857,1),7,从身份证中提取性别,A,B,C,1,编 号,姓 名,身份证号码,2,A019,吴 仕,342701197602138578,3,A020,孙国成,342701820213857,LEFT,函数,用途：得到字符串左部指定个数的字符。,语法形式：,LEFT(string,n),参数：,string,指定要提取子串的字符串。,n,指定子串长度返回值,String,。,说明：函数执行成功时返回,string,字符串左边,n,个字符，发生错误时返回空字符串（,）。如果任何参数的值为,NULL,，,Left(),函数返回,NULL,。如果,n,的值大于,string,字符串的长度，那么,Left(),函数返回整个,string,字符串，但并不增加其它字符。,LEFT,函数,实例：,如果,A1=,安徽省,蚌埠市固镇县,杨庙乡,，则公式“,=LEFT(A1,FIND(,省,A1)”,返回安徽省。,Dim AnyString,MyStr,AnyString=Hello World,定义字符串。,MyStr=Left(AnyString,1),返回,H,。,MyStr=Left(AnyString,7),返回,Hello W,。,MyStr=Left(AnyString,10),返回,Hello Worl,。,4.1人事信息数据表,关键技术点（方法,2,）,要实现本例中的功能，读者应当掌握以下,Excel,技术点。,IF,（）,函数,CHOOSE,（）函数,MOD,（）函数,LEN,（）函数,RIGHT,（）函数,MID,（）函数,DATE,（）函数,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,男,女,),结果为女,LEN,函数,LEN函数用于返回字符串的长度。,语法形式：LEN(text),其中，text表示要查找其长度的字符串文本。,A,B,C,1,员工姓名,身份证号码,位 数,2,赵 荣,378501601517897,15,3,王 勇,3425076508053247,16,4,吴小平,364501197804058246,18,5,陈 果,3425011990101822338,19,=LEN(B2),练习,1,、判断身份证号码的长度，显示不同信息,身份证号码的长度为,15,位的，显示“,老身份证号长度,”,身份证号码的长度为,18,位的，显示“,新身份证号长度,”,A,B,C,D,1,员工姓名,身份证号码,位 数,结果,2,赵 荣,378501601517897,15,老身份证号长度,3,吴小平,364501197804058246,18,新身份证号长度,MID,函数,MID,函数用于从文本字符串中提取指定位置开始的特定字符。,语法格式：,MID,（,text,，,start_num,，,num_chars,）,text,：要提取字符的文本字符串,start_num,：从文本字符串中要提取第一个字符的开始位置,num_chars,：提取字符的个数,=MID(A3,4,8),A,B,C,1,文本字符串,提取条件,提取结果,2,01062791976,从开始位置提取,3,个字符,010,3,01062791976,从第,4,个字符位置提取,8,个字符,62791976,练习,获取身份证号码中的“年份”数值,员工姓名,身份证号码,位 数,结果,完整形式,赵 荣,378501601517897,从第,7,位提取,2,个字符,60,1960,年,吴小平,364501197804058246,从第,7,位提取,4,个字符,1978,1978,年,MOD,函数,MOD,函数用于求两个数值相除后的余数，其结果的正负号与除数相同。,语法格式：,MOD(number,divisor),Number:,指定的被除数数值,Diisor:,指定的除数数值,并且不能为,0,值,A,B,C,1,被除数,除数,结果,2,11,3,2,3,15,4,3,=MOD(A2,B2),=MOD(A3,B3),练习,通过获取身份证号码中的“性别”的数值来判断“性别”,15,位身份证号码,：第,15,位代表性别，奇数为男，偶数为女。,18,位身份证号码：,第,17,位代表性别，奇数为男，偶数为女。,员工姓名,身份证号码,位 数,“,性别,”,数值,奇偶性,判断性别,赵 荣,378501601517897,从第,15,位提取,1,个字符,7,1,男,吴小平,364501197804058246,从第,17,位提取,1,个字符,4,0,女,RIGHT,函数的应用,作用：从字符串右端取指定个数字符。,语法形式：,RIGHT(text,num_chars),其中：,Text,是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。,Num_chars,指定希望,RIGHT,提取的字符数。它,必须大于或等于,0,。如果,num_chars,大于文本长度，则,RIGHT,返回所有文本。如果忽略,num_chars,，则假定其为,1,。,例：,字符串,公式,要求,结果,Hello World,=RIGHT(A3,5),最后,5,个字符,World,宁夏理工学院经济管理系,=RIGHT(A4,5),最后,5,个字符,经济管理系,DATE,函数的应用,作用：,返回代表特定日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。,语法形式：,DATE(year,month,day),其中：,Year,参数,year,可以为一到四位数字。,Microsoft Excel,将根据所使用的日期系统来解释,year,参数。默认情况下，,Microsoft Excel for Windows,将使用,1900,日期系统，而,Microsoft Excel for Macintosh,将使用,1904,日期系统。,Month,代表一年中从,1,月到,12,月（一月到十二月）各月的正整数或负整数。,Day,代表一月中从,1,日到,31,日各天的正整数或负整数。,例如,A5,为,2010,，,B5,为,3,，,C5,为,5,DATE(A5,B5,C5),结果：表示转为日期格式的,2010-3-5,编 号,姓 名,身份证号码,出生日期,A001,陈双双,342701800913884,1980,年,09,月,13,日,从身份证中提取出生日期,DATE(19&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2),结果显示形式：,19XX-XX-XX,MID,函数：从文本字符串中提取指定位置开始的特定字符,MID(C3,7,2),分析：从,C3,单元格中数值的第,7,位开始取,2,位，取得出生日期的年份信息。,=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,陈双双,342701800913884,1980,年,09,月,13,日,从身份证中提取出生日期,=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,的余数，结果为,0,IF(,0=1,男,女,),结果为女,从身份证中提取性别,编 号,姓 名,身份证号码,出生日期,HSR1003,陈双双,342701800913884,1980,年,09,月,13,日,应用,DATEDIF,函数计算员工年龄,编制提取员工年龄公式（格式为“多少年多少月多少天”）,=DATEDIF(G3,TODAY(),y)&,年,&DATEDIF(G3,TODAY(),ym)&,个月,&DATEDIF(G3,TODAY(),md)&,天,编制提取员工年龄公式（格式为“多少年”）,=DATEDIF(G3,TODAY(),y)&,年,补充：,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),。,=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,陈双双,342701800913884,1980,年,09,月,13,日,从身份证中提取出生日期,=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,陈双双,342701800913884,1980,年,09,月,13,日,补充：,CONCATENATE,函数,CONCATENATE,函数用于将多个文本字符串合并为一个文本字符串。,语法格式：,CONCATENATE,（,text1,，,text12 text13,，,）,其中，,text1,，,text12 text13,，,表示将要合并成单个文本项的文本项（这些文本项可以为文本字符串、数字或对单个单元格的引用）。,A,B,C,1,姓,名,姓 名,2,赵,荣,赵荣,3,王,勇,王勇,4,吴,小平,吴小平,5,陈,果,陈果,=CONCATENATE(A5,B5),4.2 人事数据的条件求和计数,案例背景,Excel,的条件求和计数在工作中的应用很广泛。人力资源管理者需要经常对员工信息进行统计分析，比如需要获知人事数据表中学历为本科的员工人数有多少，又比如查询某个年龄段的员工人数等，这些都可以通过活用函数条件求和计数加以解决，进而提高工作的效率。,4.2.1,人事数据的单字段单条件求和计数,4.2.2,人事数据的单字段多条件求和计数,4.2.3,人事数据的多字段多条件求和计数,4.2.4,DSUM,数据库函数的应用,4.2 人事数据的条件求和计数,最终效果展示,序号,姓名,隶属部门,学历,性别,年龄,奖金,1,胡,生产部,本科,男,30,400,2,徐,生产部,专科,女,37,200,3,杨,生产部,硕士,男,59,200,4,刘,生产部,专科,女,48,200,5,李,销售部,本科,男,38,200,6,林,销售部,本科,女,25,200,7,童,行政部,专科,女,32,200,8,王,行政部,本科,男,24,400,9,李,生产部,本科,男,24,400,10,赵,生产部,专科,女,44,400,11,刘,生产部,博士,男,30,400,12,马,生产部,博士,女,33,400,13,胡,生产部,本科,女,29,400,14,林,生产部,本科,男,35,300,15,童,生产部,硕士,女,48,300,4.2 人事数据的条件求和计数,关键技术点要实现本例中的功能，读者应当掌握以下,Excel,技术点。,COUNTIF,函数,SUMPRODUCT,函数,DSUM,函数,人事数据的单字段单条件求和计数,例：统计所有本科学历人数,例：统计年龄大于等于40岁人数,方法：使用COUNTIF函数统计数据,COUNTIF,函数,用于计算单元格区域中满足给定条件的单元格的个数。,语法形式：,COUNTIF（range,criteria),COUNTIF（数据区域,条件表达式),COUNTIF,函数举例,A,B,C,1,姓名,性别,职务,2,刘勇,男,经理,3,李南,男,职员,4,陈双双,女,经理,5,叶小来,男,职员,6,林佳,女,经理,7,彭力,男,主管,8,范琳琳,女,职员,9,易呈亮,男,经理,10,黄海燕,女,职员,11,男性员工人数,5,12,女性员工人数,4,13,年龄在,30,岁以下的人数,3,=COUNTIF(B2:B10,男,),=COUNTIF(B2:B10,女,),人事数据的单字段多条件求和计数,例：统计年龄在,30,岁至,40,岁的人数,方法,1,：,=COUNTIF(F2:F18,=30)-COUNTIF(F2:F18,40),方法,2,：,=SUM(F2:F18=30)*(F2:F18=30)*(F2:F18=20)*(C2:C5I12),DSUM,数据库函数的应用,例：统计生产部学历为本科的员工的奖金合计,方法：,=DSUM(A1:G18,I19,I15:J16),DSUM,函数,DSUM,函数，按条件求和,语法形式,:DSUM(database,field,criteria),其中：,Database,是区域,field,是列数,criteria,是条件,DSUM,函数,例如：,A,B,C,1,产品名称,产品单价（单位：元）,总销售提成,2,A,11,100,3,B,100,10,4,C,200,30,5,D,20,40,6,E,45,50,7,F,88,80,8,9,条件,产品单价（单位：元）,结果,10,50,120,单价大于50元的产品的销售提成总额,=DSUM(A1:C7,3,B9:B10),4.3,用数据透视表和数据透视图分析员工学历水平,案例背景,Excel,的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。,4.3.1,编制员工学历透视表,4.3.2,制作员工学历透视图,4.3,用数据透视表和数据透视图分析员工学历水平,最终效果展示,员工学历透视表,员工学历透视图,计数项,:,学历,学历,隶属部门,本科,博士,硕士,专科,总计,生产部,6,2,2,3,13,销售部,2,2,行政部,1,1,2,总计,9,2,2,4,17,4.3,用数据透视表和数据透视图分析员工学历水平,关键技术点要实现本例中的功能，读者应当掌握以下,Excel,技术点。,数据透视表的应用,数据透视图的应用,4.3.1,编制员工学历透视表,案例背景,Excel,的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。,4.3.2,制作员工学历透视图,4.3.2,制作员工学历透视图,案例背景,Excel,的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,1,、数据透视表：,一种交互的、交叉制表的,Excel,报表，用于对多种来源（包括,Excel,的外部数据）的数据（如数据库记录）进行汇总和分析。正源于其交互式动态汇总报表的特性，数据透视表即可快速合并和比较大量数据，也可以创建频率分布和多个不同数据维的交叉制表。也就是说，创建一个数据透视表后，可以旋转其行和列以看到源数据的不同汇总结果，而且可以显示不同页面的筛选数据，即根据需要显示区域中的明细数据。,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,2,、数据透视表的作用,:,能够改变数据表的行、列布局,;,能够快速汇总大量数据,;,能够基于原数据表创建数据分组，并对分组进行汇总统计。,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,包括：分页字段、数据项、行字段、列字段、项目等元素,数据项,列字段,页字段,行字段,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,解释,:,行：拖放到行中的数据字段中的每个数据项将占据透视表的一行。,列：拖放到列中的数据字段中的每个数据项将占据透视表的一列。行和列确定一个二维表格,.,页：拖放在页中的字段，,Excel,将按该字段的数据项对透视表进行分页。,数据：进行计数或汇总的字段名称。,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,3,、数据透视表的建立,点击菜单命令“数据数据透视表和数据透视图”，打开“数据透视表和数据透视图向导”对话框。,第一步，如下图所示，选择“,Microsoft Excel,数据列表或数据库”及下面的“数据透视表”单选项。,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,3,、数据透视表的建立,第二步，如下图所示，在“选定区域”输入全部数据所在的单元格区域，或者点击输入框右侧的“压缩对话”按钮，在工作表中用鼠标选定数据区域。,第三步，在对话框中选定“新建工作表”单选项，以便将创建的数据透视表放到一个新的工作表中，再点击“完成”按钮，如下图所示。,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,3,、数据透视表的建立,这样，就可以建立一个空的数据透视表，并同时显示“数据透视表”工具栏和“数据透视表字段列表”对话框，如图,5,所示。,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,4,、利用数据透视表得到需要的结果,（,1,）统计所有学生各学科成绩的汇总数据,（,2,）查看所有学生,“,新闻学,”,成绩的平均值,”,平均值保留两位小数位数,（,3,）查看男生,“,新闻学,”,成绩的平均值,”,平均值保留两位小数位数,（,4,）查看,“,工商,1010101,班男生的,“,新闻学,”,成绩的平均值,”,平均值保留两位小数位数,（,5,）将第（,2,）题,“,行,”,与,“,列,”,字段互换，并设置,“,自动套用格式,”,样式,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,5,、修改数据透视表,显示或隐藏行、列中的数据项,在行或列字段的下拉列表条中选择或取消复选标志。,重新组织数据透视表,行、列置换：鼠标移至行字段上按住拖动到列字段上,在字段内移动个别项：鼠标按在要移动字段边框线上拖动到指定位置放开。,在原有行列字段上增加或减少字段,增加：单击数据透视表,在数据透视表工具栏上将所需字段拖入行或列的字段处.,减少：选择数据透视表行或列的字段名，拖到表外,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,6,、修改数据透视表的概要函数,概要函数：即数据透视表中使用的透视函数。如：求和、计数、平均、百分比、最大值、标准偏差等。,操作方法：,选择数据透视表中数据字段；,在对象菜单或数据透视表工具栏中选择字段设置；,在数据透视表对话框的汇总方式列表中选择所需函数。,4.3,用数据透视表和数据透视图分析员工学历水平,例：,数据列表是以列表形式存在的数据表格，它是一维的表格。而在实际工作中，用户的数据往往是以二维表格的形式存在的，如下图所示：,纯牛奶,乳酸饮料,酸牛奶,奶茶,奶片,大林店,93,165,78,48,37,金井店,129,252,249,35,179,和平路店,5,167,382,114,258,新阳路店,260,73,82,205,54,解放路店,73,358,390,113,285,延安路店,392,57,248,330,186,四桥店,360,19,95,230,139,古尖店,48,142,165,136,105,东单店,53,20,233,246,66,4.3,用数据透视表和数据透视图分析员工学历水平,对于这样的数据表，用户无法以它为数据源创建理想的数据透视表。只有把二维的数据表格转换为如下图所示的数据列表，才能成为数据透视表的数据源。,能作为数据透视表数据源的一维数据列表,店铺,商品,销量,大林店,纯牛奶,93,大林店,乳酸饮料,165,大林店,酸牛奶,78,大林店,奶茶,48,大林店,奶片,37,金井店,纯牛奶,129,金井店,乳酸饮料,252,金井店,酸牛奶,249,金井店,奶茶,35,金井店,奶片,179,和平路店,纯牛奶,5,和平路店,乳酸饮料,167,（,1,）各店铺商品销量求和统计,（,2,）各店铺商品中销量最大的商品销量,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.1,编制员工学历透视表,7,、删除数据透视表的方法,利用数据透视表工具栏的,“,数据透视表,”,按钮,“,选定,”,“,整张表格,”,，再利用,“,编辑,”,“,清除,”,“,全部,”,4.3,用数据透视表和数据透视图分析员工学历水平,4.3.2,制作员工学历透视图,数据透视表虽然具有可以准确计算和分析数据的优点，但是通常数据透视表的数据源较大，数据量非常多，数据排列也相当复杂，这时就可以利用数据透视图来更加直观地分析数据。数据透视图以图形形式表示数据表中的数据，和数据透视表一样，可以更改数据透视图的布局和显示的数据。与数据透视表相比，数据透视图展示数据之间关系的方式更加可视化、更易于理解。,数据透视图是由系列、分类轴、坐标轴、数据标志、页字段、数据字段、页和分类字段等元素组成。,两种创建方式：,直接创建数据透视图,和,利用数据透视表创建数据透视图,建立数据透视图表的方法,单击数据透视表中的非空单元格，选择“插入”菜单的图表命令，或单击常用工具栏中的图表工具，,Excel,会自动在当前工作簿中插入一个新的数据透视图。,4.4 人事数据表的两表数据核对,案例背景,在实际工作中人力资源部经常需要核对某些数据，除了核对工资外，坏需要核对员工身份证号码、职工姓名、银行账号等信息。这些工作很单调且麻烦。若被核对的字符串简单，直接进行人工处理还是可以的；一旦字符很多，且排列顺序不一，这是人工查找起来就显得比较麻烦。,借助,Excel,提供的条件格式或者数据透视表功能，复杂的比对难题就可以迎刃而解。此外人力资源工作者利用相关函数，同样也能完成该项工作。,4.4.1,利用“条件格式”比照核对两表格数据,4.4.2,利用“数据透视表”比照核对两表格数据,4.4.3,利用,VLOOKUP,函数比照核对两表数据,4.4 人事数据表的两表数据核对,最终效果展示,使用条件格式对比,使用,VLOOKUP,函数对比 使用数据透视表对比,4.4 人事数据表的两表数据核对,关键技术点要实现本例中的功能，读者应当掌握以下,Excel,技术点。,条件格式的应用,数据透视表的应用,VLOOKUP,函数的应用,NOT,函数,OR,函数,4.4,人事数据表的两表数据核对,4.4.1,利用“条件格式”比照核对两表格数据,将,D,列中的身份证号码与,H,列的身份证号码进行比较，若不一致则显示为黄色底纹。,方法：设置条件格式，设置公式为,=NOT(OR(D2=H$2:H$9),A,B,C,D,E,F,G,H,1,工号,隶属部门,姓名,身份证号码,工号,隶属部门,姓名,身份证号码,2,25,生产部,张三,330202197604281233,88,技术部,Sun,120302198806051611,3,14,生产部,李四,310302198101010123,25,生产部,张三,330202197604281233,4,56,生产部,王五,330902197712082132,30,行政部,App