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

《人事信息数据统计分析.ppt》由会员分享，可在线阅读，更多相关《人事信息数据统计分析.ppt（80页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、人事信息数据统计分人事信息数据统计分析析现在学习的是第1页，共80页第第4章章 人事人事信息数据统计分析信息数据统计分析w本章要点本章要点 ：n4.1人事信息数据表人事信息数据表 n4.2人事数据的条件求和计数人事数据的条件求和计数 n4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平 n4.4人事数据表的两表数据核对人事数据表的两表数据核对 n4.5员工人事信息数据查询表（员工信息卡片）员工人事信息数据查询表（员工信息卡片） n4.6 统计不同年龄段员工信息统计不同年龄段员工信息 n4.7人力资源月报动态图表人力资源月报动态图表 现在学习的是第2页，共80

2、页第第4章章 人事信息数据统计分析人事信息数据统计分析w第第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人事数据的单字段单条

3、件求和计数人事数据的单字段单条件求和计数 4.2.2人事数据的单字段多条件求和计数人事数据的单字段多条件求和计数 4.2.3人事数据的多字段多条件求和计数人事数据的多字段多条件求和计数 4.2.4DSUM数据库函数的应用数据库函数的应用 4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平 4.3.1编制员工学历透视表编制员工学历透视表 4.3.2制作员工学历透视图制作员工学历透视图 w4.4人事数据表的两表数据核对人事数据表的两表数据核对 4.4.1利用利用“条件格式条件格式”比照核对两表格数据比照核对两表格数据 4.4.2利用利用“数据透视表数据透视表”比

4、照核对两表格数据比照核对两表格数据 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 创建

5、数据源工作表创建数据源工作表 4.7.2 数据分析汇总数据分析汇总 4.7.3 建立窗体控件建立窗体控件 4.7.4 对数据自动降序排列对数据自动降序排列 4.7.5 定义动态数据区域名称定义动态数据区域名称 4.7.6 绘制柏拉图绘制柏拉图 4.7.7 美化图表区美化图表区现在学习的是第3页，共80页4.1人事信息数据表人事信息数据表w案例背景案例背景w 企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事数据企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事数据的整理、汇总分析等工作，并且这些数据常常也是企业做各项决策的参考依据的整理、汇总分析等工作，并且这些数据常常也是

6、企业做各项决策的参考依据，因此处理好人事数据的整理工作意义重大。，因此处理好人事数据的整理工作意义重大。w 人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事信息管理表格人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事信息管理表格一定要科学、准确、详细，并且有利于查找、利用，这样才能真正辅助企业管理者进行一定要科学、准确、详细，并且有利于查找、利用，这样才能真正辅助企业管理者进行人事信息管理工作。人事信息管理工作。w w4.1.14.1.1创建人事信息数据表创建人事信息数据表 4.1.24.1.2利用数据有效性防止工号重复输入利用数据有效性防止工号重复输入 4.1.3 4

7、.1.3 身份证号中提取生日、性别等有效信息身份证号中提取生日、性别等有效信息 4.1.4 4.1.4 应用应用DATEDIFDATEDIF函数计算员工年龄函数计算员工年龄 4.1.54.1.5设置每页顶端标题行和底端标题行设置每页顶端标题行和底端标题行 4.1.64.1.6美化表格美化表格 现在学习的是第4页，共80页4.1人事信息数据表人事信息数据表w 最终效果展示最终效果展示某某有限公司员工人事信息表序号工号姓名隶属部门学历身份证号生日 性别计算年龄（-年-月-日）年龄职称现任职务联系电话居住地址168胡生产部本科1201051976101200341976-10-12男35年6个月26

8、天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杭州市某区

9、某路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赵生产部专科1201

10、021963012011691963-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林生产部本科1201051972011933

11、181972-1-19男40年3个月19天40年助工科员12345691杭州市某区某路14号15242童生产部硕士1201021958122316291958-12-23女53年4个月15天53年助工科员12345692杭州市某区某路15号现在学习的是第5页，共80页创建人事信息数据表创建人事信息数据表w 人事信息数据表包括项目有：姓名、性别、年龄、身份证号人事信息数据表包括项目有：姓名、性别、年龄、身份证号（或社会保障号）、学历、现任职务、联系电话、（或社会保障号）、学历、现任职务、联系电话、E-mailE-mail和居和居住地等有效信息。住地等有效信息。w 其中性别、年龄、出生日期等项目均

12、不需要手工录入，利其中性别、年龄、出生日期等项目均不需要手工录入，利用函数计算即可。用函数计算即可。现在学习的是第6页，共80页身份证号中提取生日、性别等有效信息身份证号中提取生日、性别等有效信息w 建立人事信息数据表时，性别和出生日期两个项目可以不建立人事信息数据表时，性别和出生日期两个项目可以不必手工录入，而是利用函数通过身份证号码来自动提取即必手工录入，而是利用函数通过身份证号码来自动提取即可。可。现在学习的是第7页，共80页分析身份证号码w 其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。 w 15位身份

13、证号码位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。例如例如:w 某员工的身份证号码(15位)是320521720807024，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。现在学习的是第8页，共80页分析身份证号码w 18位身份证号码：第位身份证号码：第7、8、9、10位为出生年份位为出生年份(四位数四位数)，第，第11、第、第12位为出生月份，第位为出生月份，第

14、13、14位代表出生日期，第位代表出生日期，第17位代表性别，奇数为男位代表性别，奇数为男，偶数为女，第，偶数为女，第18位为校验位。位为校验位。现在学习的是第9页，共80页4.1人事信息数据表人事信息数据表w 关键技术点（方法关键技术点（方法1）要实现本例中的功能，读者应当掌握以下要实现本例中的功能，读者应当掌握以下Excel技术点。技术点。lDATEIF函数函数lRIGHT函数函数lLEFT函数函数lMID函数函数lLEN函数函数现在学习的是第10页，共80页编 号姓 名身份证号码出生日期A001陈双双3427018009138841980年09月13日从身份证中提取出生日期=-TEXT(

15、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现在学习的是第11页，共80页ABCD1编 号姓 名身份证号码出生日期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(c

16、2,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现在学习的是第12页，共80页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(

17、MID(c2,7,8),#-00-00)=-TEXT(19800913,#-00-00)结果显示形式：19XX-XX-XX分析：1、前面的两个减号表示要返回数值型结果 2、函数公式中的“*”表示IF函数 P136 LEN(F3)=18 返回0 或返回1 则：6+1*2=8 或 6+0*2现在学习的是第13页，共80页=IF(c2= , ,IF(MOD(RIGHT(LEFT(c2,17),2),男,女)分析:nleft(C17,17)：截取单元格C17的前17位数 nright(left(C17,17)其实省略了一个参数，完整的应该是：right(left(C17,17),1)意思是：截取lef

18、t()所得的结果的最后一位数。 nMOD(RIGHT(LEFT(C17,17),2)是以上面的结果除以2的余数n最后的if()函数是用来判断的，如果余数为有余数则返回“男”，没有余数则返回“女”。 n其实该公式被简化了，完整的应该是： =IF(MOD(RIGHT(LEFT(C17,17),1),2)0,男,女) 从身份证中提取性别ABCD1编 号姓 名身份证号码出生日期2A001陈双双3427018009138841980年09月13日现在学习的是第14页，共80页RIGHT(LEFT(c2,17)RIGHT(34270119760213857,1) - 7RIGHT(LEFT(c3,17)R

19、IGHT(342701820213857,1) 7从身份证中提取性别ABC1编 号姓 名身份证号码2A019吴 仕3427011976021385783A020孙国成342701820213857现在学习的是第15页，共80页LEFT函数函数w 用途：用途： 得到字符串左部指定个数的字符。得到字符串左部指定个数的字符。 w 语法形式：语法形式：LEFT( string, n ) w 参数：参数： string 指定要提取子串的字符串。指定要提取子串的字符串。 w n 指定子串长度返回值指定子串长度返回值String。 w 说明：函数执行成功时返回说明：函数执行成功时返回string字符串左边字

20、符串左边n个字符，发生个字符，发生错误时返回空字符串（错误时返回空字符串（）。如果任何参数的值为）。如果任何参数的值为NULL，Left()函数返回函数返回NULL。如果。如果n的值大于的值大于string字符串的长度，那么字符串的长度，那么Left()函数返回整个函数返回整个string字符串，但并不增加其它字符。字符串，但并不增加其它字符。 现在学习的是第16页，共80页LEFT函数函数w 实例：实例： w 如果如果A1=安徽省安徽省蚌埠市固镇县蚌埠市固镇县杨庙乡杨庙乡，则公式，则公式“=LEFT(A1,FIND(省省,A1)”返回安徽省。返回安徽省。 w Dim AnyString, M

21、yStr w AnyString = Hello World 定义字符串。定义字符串。 w MyStr = Left(AnyString, 1) 返回返回 H。 w MyStr = Left(AnyString, 7) 返回返回 Hello W。 w MyStr = Left(AnyString, 10) 返回返回 Hello Worl。 现在学习的是第17页，共80页4.1人事信息数据表人事信息数据表w 关键技术点（方法关键技术点（方法2）要实现本例中的功能，读者应当掌握以下要实现本例中的功能，读者应当掌握以下Excel技术点。技术点。lIF（）（）函数函数lCHOOSECHOOSE（）函数

22、（）函数lMODMOD（）函数（）函数lLENLEN（）函数（）函数lRIGHTRIGHT（）函数（）函数lMIDMID（）函数（）函数lDATEDATE（）函数（）函数现在学习的是第18页，共80页IF函数的应用函数的应用w IF函数是根据指定的条件来判断其函数是根据指定的条件来判断其“真真”（TRUE）、）、“假假”（FALSE），从而返回其相对应得内容。），从而返回其相对应得内容。w 语法形式：语法形式：IF(logical_test,value_if_true,value_if_false)w 其中：其中：nlogical_test 逻辑判断表达式逻辑判断表达式nvalue_if_tr

23、ue当判断条件为逻辑当判断条件为逻辑“真真”（TRUE）时）时，显示该处给定的内容。如忽略，返回，显示该处给定的内容。如忽略，返回TRUE。nvalue_if_false当判断条件为逻辑当判断条件为逻辑“假假”（FALSE）时）时，显示该处给定的内容。如忽略，返回，显示该处给定的内容。如忽略，返回FALSE 。IF( 0=1 ,男,女) 结果为女现在学习的是第19页，共80页LEN函数函数w LEN函数用于返回字符串的长度。函数用于返回字符串的长度。w 语法形式：语法形式：LEN(text)w 其中，其中，text表示要查找其长度的字符串文本。表示要查找其长度的字符串文本。ABC1员工姓名身份

24、证号码位 数2赵 荣378501601517897153王 勇3425076508053247164吴小平364501197804058246185陈 果342501199010182233819=LEN(B2)现在学习的是第20页，共80页练习练习w 1、判断身份证号码的长度，显示不同信息、判断身份证号码的长度，显示不同信息w 身份证号码的长度为身份证号码的长度为15位的，显示位的，显示“老身份证号长度老身份证号长度”w 身份证号码的长度为身份证号码的长度为18位的，显示位的，显示“新身份证号长度新身份证号长度”ABCD1员工姓名身份证号码位 数结果2赵 荣37850160151789715

25、老身份证号长度3吴小平36450119780405824618新身份证号长度现在学习的是第21页，共80页MID函数函数w MID函数用于从文本字符串中提取指定位置开函数用于从文本字符串中提取指定位置开始的特定字符。始的特定字符。w 语法格式：语法格式： MID（text，start_num，num_chars）ntext：要提取字符的文本字符串：要提取字符的文本字符串nstart_num：从文本字符串中要提取第一个字符的开始位置：从文本字符串中要提取第一个字符的开始位置nnum_chars：提取字符的个数：提取字符的个数=MID(A3,4,8)ABC1文本字符串提取条件提取结果2010627

26、91976从开始位置提取3个字符010301062791976从第4个字符位置提取8个字符62791976现在学习的是第22页，共80页练习练习w 获取身份证号码中的获取身份证号码中的“年份年份”数值数值员工姓名身份证号码位 数结果完整形式赵 荣378501601517897从第7位提取2个字符601960年吴小平364501197804058246从第7位提取4个字符19781978年现在学习的是第23页，共80页MOD函数函数w MOD函数用于求两个数值相除后的余数，其结函数用于求两个数值相除后的余数，其结果的正负号与除数相同。果的正负号与除数相同。w 语法格式：语法格式：MOD(numb

27、er,divisor)w Number:指定的被除数数值指定的被除数数值w Diisor:指定的除数数值指定的除数数值,并且不能为并且不能为0值值ABC1被除数除数结果2113231543=MOD(A2,B2)=MOD(A3,B3)现在学习的是第24页，共80页练习练习w 通过获取身份证号码中的通过获取身份证号码中的“性别性别”的数值来判断的数值来判断“性别性别”w15位身份证号码：第位身份证号码：第15位代表性别，奇数为男，偶数为女。位代表性别，奇数为男，偶数为女。w18位身份证号码：第位身份证号码：第17位代表性别，奇数为男，偶数为女。位代表性别，奇数为男，偶数为女。员工姓名身份证号码位

28、数“性别”数值奇偶性判断性别赵 荣378501601517897从第15位提取1个字符71男吴小平364501197804058246从第17位提取1个字符40女现在学习的是第25页，共80页RIGHT函数的应用函数的应用w 作用：从字符串右端取指定个数字符。作用：从字符串右端取指定个数字符。 w 语法形式：语法形式：RIGHT(text,num_chars) w 其中：其中：nText 是包含要提取字符的文本字符串，可以直接输入是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。含有目标文字的单元格名称。 nNum_chars 指定希望指定希望 RIGHT 提取的字符数。提取

29、的字符数。 它它 必须必须大于或等于大于或等于 0。 如果如果 num_chars 大于文本长度，则大于文本长度，则 RIGHT 返回所有文本。返回所有文本。 如果忽略如果忽略 num_chars，则假，则假定其为定其为 1。n例：例：字符串公式要求结果Hello World =RIGHT(A3,5)最后 5 个字符World宁夏理工学院经济管理系 =RIGHT(A4,5)最后 5 个字符经济管理系现在学习的是第26页，共80页DATE函数的应用函数的应用w作用：作用：返回代表特定日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。 w语法形式：语法形式：DATE(y

30、ear,month,day) w其中：其中：nYear 参数 year 可以为一到四位数字。Microsoft Excel 将根据所使用的日期系统来解释 year 参数。默认情况下，Microsoft Excel for Windows 将使用 1900 日期系统，而 Microsoft Excel for Macintosh 将使用 1904 日期系统。 nMonth 代表一年中从 1 月到 12 月（一月到十二月）各月的正整数或负整数。n Day 代表一月中从 1 日到 31 日各天的正整数或负整数。w例如例如 nA5为为2010，B5为为3，C5为为5nDATE(A5,B5,C5)n结果

31、：表示转为日期格式的结果：表示转为日期格式的2010-3-5现在学习的是第27页，共80页编 号姓 名身份证号码出生日期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位，取得出生日期的年份信息。现在学习的是第28页，共80页=IF(LEN(C4)=15,DATE(19&MID(C4,7,2),MID(C4,9,2),M

32、ID(C4,11,2),IF(LEN(C4)=18,DATE( MID(C4,7,4), MID(C4,11,2), MID(C4,13,2), )编 号姓 名身份证号码出生日期A001陈双双3427018009138841980年09月13日从身份证中提取出生日期现在学习的是第29页，共80页=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 ,男,女

33、) 结果为女从身份证中提取性别编 号姓 名身份证号码出生日期HSR1003陈双双3427018009138841980年09月13日现在学习的是第30页，共80页应用应用DATEDIFDATEDIF函数计算员工年龄函数计算员工年龄w 编制提取员工年龄公式（格式为编制提取员工年龄公式（格式为“多少年多少多少年多少月多少天月多少天”）w=DATEDIF(G3,TODAY(),y)&年年&DATEDIF(G3,TODAY(),ym)&个月个月&DATEDIF(G3,TODAY(),md)&天天w 编制提取员工年龄公式（格式为编制提取员工年龄公式（格式为“多少年多少年”）w=DATEDIF(G3,TO

34、DAY(),y)&年年现在学习的是第31页，共80页补充：补充： CHOOSE函数的应用函数的应用w作用：从参数列表中选择并返回一个值。函数作用：从参数列表中选择并返回一个值。函数CHOOSE可以使用可以使用 index_num 返回数值参数清单中的数值。使用函数返回数值参数清单中的数值。使用函数 CHOOSE 可以基于索引号返回多达可以基于索引号返回多达 29 个个待选数值中的任一数值。待选数值中的任一数值。 w语法形式：语法形式：CHOOSE(index_num,value1,value2,.) w其中：其中：nIndex_num用以指明待选参数序号的参数值。用以指明待选参数序号的参数值。

35、Index_num 必须为必须为 1 到到 29 之间的数字、或者是包含数字之间的数字、或者是包含数字 1 到到 29 的公式或单元格引用。的公式或单元格引用。nValue1,value2,. 为为 1 到到 29 个数值参数，函数个数值参数，函数 CHOOSE 基于基于 index_num，从中选择一个数值或执行相应的操作。参数可以为数字、单元格引，从中选择一个数值或执行相应的操作。参数可以为数字、单元格引用，已定义的名称、公式、函数或文本。用，已定义的名称、公式、函数或文本。w例如例如 n公式公式“=CHOOSE(2，”电脑电脑“，”爱好者爱好者“)返回返回“爱好者爱好者”。n公式公式“=

36、SUM(A1:CHOOSE(3，A10，A20，A30)”与公式与公式“=SUM(A1:A30)”等价等价(因为因为CHOOSE(3，A10，A20，A30)返回返回A30)。现在学习的是第32页，共80页=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日从身份证中提取出生日期现在学习的是第33页，共80页=

37、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日现在学习的是第34页，共80页补充：补充：CONCATENATE函数函数wCONCATENATE函数用于将多个文本字符串合并为一个

38、文本字符串。函数用于将多个文本字符串合并为一个文本字符串。w语法格式：语法格式： CONCATENATE（text1， text12 text13，）w其中，其中， text1， text12 text13，表示将要合并成单个文本项的文本项（这些文本项可以为表示将要合并成单个文本项的文本项（这些文本项可以为文本字符串、数字或对单个单元格的引用）。文本字符串、数字或对单个单元格的引用）。ABC1姓名姓 名2赵荣赵荣3王勇王勇4吴小平吴小平5陈果陈果=CONCATENATE(A5,B5)现在学习的是第35页，共80页4.2 人事数据的条件求和计数人事数据的条件求和计数w案例背景案例背景w Exce

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

40、据的单字段多条件求和计数 4.2.3人事数据的多字段多条件求和计数人事数据的多字段多条件求和计数 4.2.4DSUM数据库函数的应用数据库函数的应用 现在学习的是第36页，共80页4.2 人事数据的条件求和计数人事数据的条件求和计数w 最终效果展示最终效果展示序号姓名隶属部门学历性别年龄奖金1胡生产部本科男304002徐生产部专科女372003杨生产部硕士男592004刘生产部专科女482005李销售部本科男382006林销售部本科女252007童行政部专科女322008王行政部本科男244009李生产部本科男2440010赵生产部专科女4440011刘生产部博士男3040012马生产部博士女

41、3340013胡生产部本科女2940014林生产部本科男3530015童生产部硕士女48300现在学习的是第37页，共80页4.2 人事数据的条件求和计数人事数据的条件求和计数w 关键技术点关键技术点要实现本例中的功能，读者应当掌握以下要实现本例中的功能，读者应当掌握以下Excel技术点。技术点。lCOUNTIF函数函数lSUMPRODUCT函数函数lDSUM函数函数现在学习的是第38页，共80页人事数据的单字段单条件求和计数人事数据的单字段单条件求和计数w 例：统计所有本科学历人数例：统计所有本科学历人数w 例：统计年龄大于等于例：统计年龄大于等于40岁人数岁人数w 方法：使用方法：使用CO

42、UNTIF函数统计数据函数统计数据现在学习的是第39页，共80页COUNTIF函数函数w 用于计算单元格区域中满足给定条件的单元格用于计算单元格区域中满足给定条件的单元格的个数。的个数。w 语法形式：语法形式：w COUNTIF（range,criteria)w COUNTIF（数据区域（数据区域,条件表达式条件表达式)现在学习的是第40页，共80页COUNTIF函数举例函数举例ABC1姓名性别职务2刘勇男经理3李南男职员4陈双双女经理5叶小来男职员6林佳女经理7彭力男主管8范琳琳女职员9易呈亮男经理10黄海燕女职员11男性员工人数512女性员工人数413年龄在30岁以下的人数3=COUNTI

43、F(B2:B10,男男)=COUNTIF(B2:B10,女女)现在学习的是第41页，共80页人事数据的单字段多条件求和计数人事数据的单字段多条件求和计数w 例：统计年龄在例：统计年龄在30岁至岁至40岁的人数岁的人数w 方法方法1：w =COUNTIF(F2:F18,=30)-COUNTIF(F2:F18,40)w 方法方法2：w =SUM(F2:F18=30)*(F2:F18=30)*(F2:F18=20) * (C2:C5I12)现在学习的是第45页，共80页DSUM数据库函数的应用数据库函数的应用w 例：统计生产部学历为本科的员工的奖金合计例：统计生产部学历为本科的员工的奖金合计w 方法

44、：方法：w =DSUM(A1:G18,I19,I15:J16)现在学习的是第46页，共80页DSUM函数函数w DSUM函数，按条件求和函数，按条件求和 w 语法形式语法形式:DSUM(database,field,criteria) w 其中：其中：nDatabase是区域是区域nfield是列数是列数ncriteria是条件是条件 现在学习的是第47页，共80页DSUM函数函数w 例如：例如： ABC1产品名称产品单价（单位：元）总销售提成2A111003B100104C200305D20406E45507F888089条件产品单价（单位：元）结果1050120单价大于50元的产品的销售提

45、成总额=DSUM(A1:C7,3,B9:B10)现在学习的是第48页，共80页4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 案例背景案例背景w ExcelExcel的图表分析功能十分强大，其中的数的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。透视表以及数据透视图。w w 4.3.1编制员工学历透视表编制员工

46、学历透视表 4.3.2制作员工学历透视图制作员工学历透视图 现在学习的是第49页，共80页4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 最终效果展示最终效果展示w 员工学历透视表员工学历透视表w 员工学历透视图员工学历透视图w计数项:学历学历隶属部门本科博士硕士专科总计生产部622313销售部22行政部112总计922417现在学习的是第50页，共80页4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 关键技术点关键技术点要实现本例中的功能，读者应当掌握以下要实现本例中的功能，读者应当掌握以下Excel技术点。

47、技术点。l数据透视表的应用数据透视表的应用l数据透视图的应用数据透视图的应用现在学习的是第51页，共80页4.3.1编制员工学历透视表编制员工学历透视表w 案例背景案例背景w ExcelExcel的图表分析功能十分强大，其中的数的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。透视表以及数据透视图。w w 4.3.2制作员工学历透视图制作员工学历透视图

48、现在学习的是第52页，共80页4.3.2制作员工学历透视图制作员工学历透视图w 案例背景案例背景w ExcelExcel的图表分析功能十分强大，其中的数的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。透视表以及数据透视图。现在学习的是第53页，共80页4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编

49、制员工学历透视表编制员工学历透视表w 1 1、数据透视表：、数据透视表：w 一种交互的、交叉制表的一种交互的、交叉制表的 Excel Excel 报表，用于对多种来源报表，用于对多种来源（包括（包括 Excel Excel 的外部数据）的数据（如数据库记录）进行汇总的外部数据）的数据（如数据库记录）进行汇总和分析。正源于其交互式动态汇总报表的特性，数据透视表即可和分析。正源于其交互式动态汇总报表的特性，数据透视表即可快速合并和比较大量数据，也可以创建频率分布和多个不同数据快速合并和比较大量数据，也可以创建频率分布和多个不同数据维的交叉制表。也就是说，创建一个数据透视表后，可以旋转其维的交叉制表

50、。也就是说，创建一个数据透视表后，可以旋转其行和列以看到源数据的不同汇总结果，而且可以显示不同页面的行和列以看到源数据的不同汇总结果，而且可以显示不同页面的筛选数据，即根据需要显示区域中的明细数据。筛选数据，即根据需要显示区域中的明细数据。 现在学习的是第54页，共80页4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 2 2、数据透视表的作用、数据透视表的作用: :n能够改变数据表的行、列布局能够改变数据表的行、列布局; ;n能够快速汇总大量数据能够快速汇总大量数据; ;n能够基于原数据表创建数据分组，