人事信息数据统计分析

1、ExcelExcel在商务中的应用在商务中的应用任课教师：李磊任课教师：李磊ExcelExcel在商务中的在商务中的应用应用第第4章章 人事人事信息数据统计分析信息数据统计分析w 本章要点本章要点 ：n4.1人事信息数据表人事信息数据表 n4.2人事数据的条件求和计数人事数据的条件求和计数 n4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平 n4.4人事数据表的两表数据核对人事数据表的两表数据核对 n4.5员工人事信息数据查询表（员工信息卡片）员工人事信息数据查询表（员工信息卡片） n4.6 统计不同年龄段员工信息统计不同年龄段员工信息 n4.7人力资源月

2、报动态图表人力资源月报动态图表 ExcelExcel在商务中的在商务中的应用应用第第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

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

4、核对两表格数据比照核对两表格数据 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数组公式法统计分段数组公式法统计分

5、段信息信息 4.7 人力资源月报动态图表人力资源月报动态图表 4.7.1 创建数据源工作表创建数据源工作表 4.7.2 数据分析汇总数据分析汇总 4.7.3 建立窗体控件建立窗体控件 4.7.4 对数据自动降序排列对数据自动降序排列 4.7.5 定义动态数据区域名称定义动态数据区域名称 4.7.6 绘制柏拉图绘制柏拉图 4.7.7 美化图表区美化图表区ExcelExcel在商务中的在商务中的应用应用4.1人事信息数据表人事信息数据表w案例背景案例背景w 企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事数据的整理、汇总分析等

6、工作，并且这些数据常常也是企业做各项决策的数据的整理、汇总分析等工作，并且这些数据常常也是企业做各项决策的参考依据，因此处理好人事数据的整理工作意义重大。参考依据，因此处理好人事数据的整理工作意义重大。w 人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事信息管理表格一定要科学、准确、详细，并且有利于查找、利用，这样才信息管理表格一定要科学、准确、详细，并且有利于查找、利用，这样才能真正辅助企业管理者进行人事信息管理工作。能真正辅助企业管理者进行人事信息管理工作。w w4.1.14.1.1创建人事信息数据表创建人事信息数据表

7、 4.1.24.1.2利用数据有效性防止工号重复输入利用数据有效性防止工号重复输入 4.1.3 4.1.3 身份证号中提取生日、性别等有效信息身份证号中提取生日、性别等有效信息 4.1.4 4.1.4 应用应用DATEDIFDATEDIF函数计算员工年龄函数计算员工年龄 4.1.54.1.5设置每页顶端标题行和底端标题行设置每页顶端标题行和底端标题行 4.1.64.1.6美化表格美化表格 ExcelExcel在商务中的在商务中的应用应用4.1人事信息数据表人事信息数据表w 最终效果展示最终效果展示某某有限公司员工人事信息表序号工号姓名隶属部门学历身份证号生日 性别计算年龄（-年-月-日）年龄职

8、称现任职务联系电话居住地址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李销售

9、部本科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李生产部本/p>

10、1983-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女3

11、4年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号ExcelExcel在商务中的在商务中的应用应用创建人事信息数据表创建人事信息数据表w 人事信息数据表包括项目有：姓名、性别、年龄、身份人事信息数据表包括项目有：姓名、性别、年龄、身份证号（或社会保障号）、学历、现任职务、联系电话、证号（或社

12、会保障号）、学历、现任职务、联系电话、E-mailE-mail和居住地等有效信息。和居住地等有效信息。w 其中性别、年龄、出生日期等项目均不需要手工录入，其中性别、年龄、出生日期等项目均不需要手工录入，利用函数计算即可。利用函数计算即可。ExcelExcel在商务中的在商务中的应用应用身份证号中提取生日、性别等有效信息身份证号中提取生日、性别等有效信息w 建立人事信息数据表时，性别和出生日期两个项目可以建立人事信息数据表时，性别和出生日期两个项目可以不必手工录入，而是利用函数通过身份证号码来自动提不必手工录入，而是利用函数通过身份证号码来自动提取即可。取即可。ExcelExcel在商务中的在商

13、务中的应用应用分析身份证号码w 其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。 w 15位身份证号码位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。例如例如:w 某员工的身份证号码(15位)是320521720807024，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。ExcelExc

14、el在商务中的在商务中的应用应用分析身份证号码w 18位身份证号码：第位身份证号码：第7、8、9、10位为出生年份位为出生年份(四位数四位数)，第，第11、第、第12位为出生月份，第位为出生月份，第13、14位代表出生日期，第位代表出生日期，第17位代表性别，奇数为男位代表性别，奇数为男，偶数为女，第，偶数为女，第18位为校验位。位为校验位。ExcelExcel在商务中的在商务中的应用应用4.1人事信息数据表人事信息数据表w 关键技术点（方法关键技术点（方法1）要实现本例中的功能，读者应当掌握以下要实现本例中的功能，读者应当掌握以下Excel技术点。技术点。lDATEIF函数函数lRIGHT函

15、数函数lLEFT函数函数lMID函数函数lLEN函数函数ExcelExcel在商务中的在商务中的应用应用编 号姓 名身份证号码出生日期A001陈双双3427018009138841980年09月13日从身份证中提取出生日期=-TEXT(MID(F3,7,6+(LEN(F3)=18)*2),#-00-00)结果显示形式：结果显示形式：19XX-XX-XX19XX-XX-XX分析：分析：1 1、前面的两个减号表示要返回数值型结果前面的两个减号表示要返回数值型结果 2 2、函数公式中的、函数公式中的“* *”表示表示IFIF函数函数 P136P136 LEN(F3)=18 返回返回0 或返回或返回1

16、 则：则：6+16+1* *2=8 2=8 或或 6+06+0* *2 2ExcelExcel在商务中的在商务中的应用应用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(c2,7,6),#-00-00)=-TEXT(800913,#-00-00)结果显示形式：结果显示形式：19XX-XX-XX19XX-XX-XX分析：分析：1 1、前面的两个减号表

17、示要返回数值型结果前面的两个减号表示要返回数值型结果 2 2、函数公式中的、函数公式中的“* *”表示表示IFIF函数函数 P136P136 LEN(F3)=18 返回返回0 或返回或返回1 则：则：6+16+1* *2=8 2=8 或或 6+06+0* *2 2ExcelExcel在商务中的在商务中的应用应用ABCD1编 号姓 名身份证号码出生日期2A001陈双1980年09月13日从身份证中提取出生日期=-TEXT(MID(c2,7,6+(LEN(c2)=18)*2),#-00-00)=-TEXT(MID(c2,7,6+1*2),#-00-00)=-

18、TEXT(MID(c2,7,8),#-00-00)=-TEXT(19800913,#-00-00)结果显示形式：结果显示形式：19XX-XX-XX19XX-XX-XX分析：分析：1 1、前面的两个减号表示要返回数值型结果前面的两个减号表示要返回数值型结果 2 2、函数公式中的、函数公式中的“* *”表示表示IFIF函数函数 P136P136 LEN(F3)=18 返回返回0 或返回或返回1 则：则：6+16+1* *2=8 2=8 或或 6+06+0* *2 2ExcelExcel在商务中的在商务中的应用应用=IF(c2= , ,IF(MOD(RIGHT(LEFT(c2,17),2),男,女)

19、分析: 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陈双双34270180091

20、38841980年09月13日ExcelExcel在商务中的在商务中的应用应用RIGHT(LEFT(c2,17)RIGHT(34270119760213857,1) - 7RIGHT(LEFT(c3,17)RIGHT(342701820213857,1) 7从身份证中提取性别ABC1编 号姓 名身份证号码2A019吴 仕3427011976021385783A020孙国成342701820213857ExcelExcel在商务中的在商务中的应用应用LEFT函数函数w 用途：用途： 得到字符串左部指定个数的字符。得到字符串左部指定个数的字符。 w 语法形式：语法形式：LEFT( string,

21、n ) w 参数：参数： string 指定要提取子串的字符串。指定要提取子串的字符串。 w n 指定子串长度返回值指定子串长度返回值String。 w 说明：函数执行成功时返回说明：函数执行成功时返回string字符串左边字符串左边n个字符，个字符，发生错误时返回空字符串（发生错误时返回空字符串（）。如果任何参数的值为）。如果任何参数的值为NULL，Left()函数返回函数返回NULL。如果。如果n的值大于的值大于string字字符串的长度，那么符串的长度，那么Left()函数返回整个函数返回整个string字符串，但字符串，但并不增加其它字符。并不增加其它字符。 ExcelExcel在商务

22、中的在商务中的应用应用LEFT函数函数w 实例：实例： w 如果如果A1=安徽省安徽省蚌埠市固镇县蚌埠市固镇县杨庙乡杨庙乡，则公式，则公式“=LEFT(A1,FIND(省省,A1)”返回安徽省。返回安徽省。 w Dim AnyString, MyStr w AnyString = Hello World 定义字符串。定义字符串。 w MyStr = Left(AnyString, 1) 返回返回 H。 w MyStr = Left(AnyString, 7) 返回返回 Hello W。 w MyStr = Left(AnyString, 10) 返回返回 Hello Worl。 ExcelEx

23、cel在商务中的在商务中的应用应用4.1人事信息数据表人事信息数据表w 关键技术点（方法关键技术点（方法2）要实现本例中的功能，读者应当掌握以下要实现本例中的功能，读者应当掌握以下Excel技术点。技术点。lIF（）（）函数函数lCHOOSECHOOSE（）函数（）函数lMODMOD（）函数（）函数lLENLEN（）函数（）函数lRIGHTRIGHT（）函数（）函数lMIDMID（）函数（）函数lDATEDATE（）函数（）函数ExcelExcel在商务中的在商务中的应用应用IF函数的应用函数的应用w IF函数是根据指定的条件来判断其函数是根据指定的条件来判断其“真真”（TRUE）、）、“假假

24、”（FALSE），从而返回其相对应得内容。），从而返回其相对应得内容。w 语法形式：语法形式：IF(logical_test,value_if_true,value_if_false)w 其中：其中：nlogical_test 逻辑判断表达式逻辑判断表达式nvalue_if_true当判断条件为逻辑当判断条件为逻辑“真真”（TRUE）时）时，显示该处给定的内容。如忽略，返回，显示该处给定的内容。如忽略，返回TRUE。nvalue_if_false当判断条件为逻辑当判断条件为逻辑“假假”（FALSE）时，显示该处给定的内容。如忽略，返回时，显示该处给定的内容。如忽略，返回FALSE 。IF( 0

25、=1 ,男,女) 结果为女ExcelExcel在商务中的在商务中的应用应用LEN函数函数w LEN函数用于返回字符串的长度。函数用于返回字符串的长度。w 语法形式：语法形式：LEN(text)w 其中，其中，text表示要查找其长度的字符串文本。表示要查找其长度的字符串文本。ABC1员工姓名员工姓名身份证号码身份证号码位位 数数2赵 荣378501601517897153王 勇3425076508053247164吴小平364501197804058246185陈 果342501199010182233819=LEN(B2)ExcelExcel在商务中的在商务中的应用应用练习练习w 1、判断身

26、份证号码的长度，显示不同信息、判断身份证号码的长度，显示不同信息w 身份证号码的长度为身份证号码的长度为15位的，显示位的，显示“老身份证号长度”w 身份证号码的长度为身份证号码的长度为18位的，显示位的，显示“新身份证号长度”ABCD1员工姓名员工姓名身份证号码身份证号码位位 数数结果2赵 荣37850160151789715老身份证号长度3吴小平36450119780405824618新身份证号长度ExcelExcel在商务中的在商务中的应用应用MID函数函数w MID函数用于从文本字符串中提取指定位置开函数用于从文本字符串中提取指定位置开始的特定字符。始的特定字符。w 语法格式：语法格式

27、： MID（text，start_num，num_chars）ntext：要提取字符的文本字符串：要提取字符的文本字符串nstart_num：从文本字符串中要提取第一个字符的开始位置：从文本字符串中要提取第一个字符的开始位置nnum_chars：提取字符的个数：提取字符的个数=MID(A3,4,8)ABC1文本字符串文本字符串提取条件提取条件提取结果提取结果201062791976从开始位置提取3个字符010301062791976从第4个字符位置提取8个字符62791976ExcelExcel在商务中的在商务中的应用应用练习练习w 获取身份证号码中的获取身份证号码中的“年份年份”数值数值员工

28、姓名员工姓名身份证号码身份证号码位位 数数结果结果完整形式完整形式赵 荣378501601517897从第7位提取2个字符601960年吴小第7位提取4个字符19781978年ExcelExcel在商务中的在商务中的应用应用MOD函数函数w MOD函数用于求两个数值相除后的余数，其结函数用于求两个数值相除后的余数，其结果的正负号与除数相同。果的正负号与除数相同。w 语法格式：语法格式：MOD(number,divisor)w Number:指定的被除数数值指定的被除数数值w Diisor:指定的除数数值指定的除数数值,并且不能为并且不能为0值值A AB

29、BC C1 1被除数被除数除数除数结果结果2113231543=MOD(A2,B2)=MOD(A3,B3)ExcelExcel在商务中的在商务中的应用应用练习练习w 通过获取身份证号码中的通过获取身份证号码中的“性别性别”的数值来判断的数值来判断“性别性别”w15位身份证号码位身份证号码：第15位代表性别，奇数为男，偶数为女。w18位身份证号码：位身份证号码：第17位代表性别，奇数为男，偶数为女。员工姓名员工姓名身份证号码身份证号码位位 数数“性别性别”数数值值奇偶性奇偶性判断性别判断性别赵 荣378501601517897从第15位提取1个字符71男吴小/p>

30、从第17位提取1个字符40女ExcelExcel在商务中的在商务中的应用应用RIGHT函数的应用函数的应用w 作用：从字符串右端取指定个数字符。作用：从字符串右端取指定个数字符。 w 语法形式：语法形式：RIGHT(text,num_chars) w 其中：其中：nText 是包含要提取字符的文本字符串，可以直接输入是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。含有目标文字的单元格名称。 nNum_chars 指定希望指定希望 RIGHT 提取的字符数。提取的字符数。 它它 必须必须大于或等于大于或等于 0。 如果如果 num_chars 大于文本长度，则大于文本长度，则

31、 RIGHT 返回所有文本。返回所有文本。 如果忽略如果忽略 num_chars，则假，则假定其为定其为 1。n例：例：字符串公式要求结果Hello World =RIGHT(A3,5)最后 5 个字符World宁夏理工学院经济管理系 =RIGHT(A4,5)最后 5 个字符经济管理系ExcelExcel在商务中的在商务中的应用应用DATE函数的应用函数的应用w 作用：作用：返回代表特定日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。 w 语法形式：语法形式：DATE(year,month,day) w 其中：其中：nYear 参数 year 可以为一到四位数字。

32、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结果：表示转为日期格式的结果：表示转为日期格式的2010-3-5ExcelExcel在商务中的

33、在商务中的应用应用编 号姓 名身份证号码出生日期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位，取得出生日期的年份信息。ExcelExcel在商务中的在商务中的应用应用=IF(LEN(C4)=15,DATE(19&MID(C4,7,2),MID(C4,9,2),MID(C4,11,2),IF(LEN(

34、C4)=18,DATE( MID(C4,7,4), MID(C4,11,2), MID(C4,13,2), )编 号姓 名身份证号码出生日期A001陈双双3427018009138841980年09月13日从身份证中提取出生日期ExcelExcel在商务中的在商务中的应用应用=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 ,男,女) 结果为女从身份证

35、中提取性别编 号姓 名身份证号码出生日期HSR1003陈双双3427018009138841980年09月13日ExcelExcel在商务中的在商务中的应用应用应用应用DATEDIFDATEDIF函数计算员工年龄函数计算员工年龄w 编制提取员工年龄公式（格式为编制提取员工年龄公式（格式为“多少年多少多少年多少月多少天月多少天”）w =DATEDIF(G3,TODAY(),y)&年年&DATEDIF(G3,TODAY(),ym)&个月个月&DATEDIF(G3,TODAY(),md)&天天w 编制提取员工年龄公式（格式为编制提取员工年龄公式（格式为“多少年

36、多少年”）w =DATEDIF(G3,TODAY(),y)&年年ExcelExcel在商务中的在商务中的应用应用补充：补充： CHOOSE函数的应用函数的应用w 作用：从参数列表中选择并返回一个值。函数作用：从参数列表中选择并返回一个值。函数CHOOSE可以使用可以使用 index_num 返回数值参数清单中的数值。使用函数返回数值参数清单中的数值。使用函数 CHOOSE 可以基可以基于索引号返回多达于索引号返回多达 29 个待选数值中的任一数值。个待选数值中的任一数值。 w 语法形式：语法形式：CHOOSE(index_num,value1,value2,.) w 其中：其中：nIn

37、dex_num用以指明待选参数序号的参数值。用以指明待选参数序号的参数值。Index_num 必须为必须为 1 到到 29 之间的数字、或者是包含数字之间的数字、或者是包含数字 1 到到 29 的公式或单元格引的公式或单元格引用。用。nValue1,value2,. 为为 1 到到 29 个数值参数，函数个数值参数，函数 CHOOSE 基于基于 index_num，从中选择一个数值或执行相应的操作。参数可以为，从中选择一个数值或执行相应的操作。参数可以为数字、单元格引用，已定义的名称、公式、函数或文本。数字、单元格引用，已定义的名称、公式、函数或文本。w 例如例如 n公式公式“=CHOOSE(

38、2，”电脑电脑“，”爱好者爱好者“)返回返回“爱好者爱好者”。n公式公式“=SUM(A1:CHOOSE(3，A10，A20，A30)”与公式与公式“=SUM(A1:A30)”等价等价(因为因为CHOOSE(3，A10，A20，A30)返回返回A30)。ExcelExcel在商务中的在商务中的应用应用=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陈双双34

39、27018009138841980年09月13日从身份证中提取出生日期ExcelExcel在商务中的在商务中的应用应用=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日Excel

40、Excel在商务中的在商务中的应用应用补充：补充：CONCATENATE函数函数wCONCATENATE函数用于将多个文本字符串合并为一个文本字符串。函数用于将多个文本字符串合并为一个文本字符串。w语法格式：语法格式： CONCATENATE（text1， text12 text13，）w其中，其中， text1， text12 text13，表示将要合并成单个文本项的文本项（表示将要合并成单个文本项的文本项（这些文本项可以为文本字符串、数字或对单个单元格的引用）。这些文本项可以为文本字符串、数字或对单个单元格的引用）。ABC1姓姓名名姓姓 名名2赵荣赵荣3王勇王勇4吴小平吴小平5陈果陈果=C

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

42、件求和计数加以解决，进而提高工作的效率。w w4.2.1人事数据的单字段单条件求和计数人事数据的单字段单条件求和计数 4.2.2人事数据的单字段多条件求和计数人事数据的单字段多条件求和计数 4.2.3人事数据的多字段多条件求和计数人事数据的多字段多条件求和计数 4.2.4DSUM数据库函数的应用数据库函数的应用 ExcelExcel在商务中的在商务中的应用应用4.2 人事数据的条件求和计数人事数据的条件求和计数w 最终效果展示最终效果展示序号姓名隶属部门学历性别年龄奖金1胡生产部本科男304002徐生产部专科女372003杨生产部硕士男592004刘生产部专科女482005李销售部本科男382

43、006林销售部本科女252007童行政部专科女322008王行政部本科男244009李生产部本科男2440010赵生产部专科女4440011刘生产部博士男3040012马生产部博士女3340013胡生产部本科女2940014林生产部本科男3530015童生产部硕士女48300ExcelExcel在商务中的在商务中的应用应用4.2 人事数据的条件求和计数人事数据的条件求和计数w 关键技术点关键技术点要实现本例中的功能，读者应当掌握以下要实现本例中的功能，读者应当掌握以下Excel技术点。技术点。lCOUNTIF函数函数lSUMPRODUCT函数函数lDSUM函数函数ExcelExcel在商务中的

44、在商务中的应用应用人事数据的单字段单条件求和计数人事数据的单字段单条件求和计数w 例：统计所有本科学历人数例：统计所有本科学历人数w 例：统计年龄大于等于例：统计年龄大于等于40岁人数岁人数w 方法：使用方法：使用COUNTIF函数统计数据函数统计数据ExcelExcel在商务中的在商务中的应用应用COUNTIF函数函数w 用于计算单元格区域中满足给定条件的单元格用于计算单元格区域中满足给定条件的单元格的个数。的个数。w 语法形式：语法形式：w COUNTIF（range,criteria)w COUNTIF（数据区域（数据区域,条件表达式条件表达式)ExcelExcel在商务中的在商务中的应

45、用应用COUNTIF函数举例函数举例ABC1姓名性别职务2刘勇男经理3李南男职员4陈双双女经理5叶小来男职员6林佳女经理7彭力男主管8范琳琳女职员9易呈亮男经理10黄海燕女职员11男性员工人数512女性员工人数413年龄在30岁以下的人数3=COUNTIF(B2:B10,男男)=COUNTIF(B2:B10,女女)ExcelExcel在商务中的在商务中的应用应用人事数据的单字段多条件求和计数人事数据的单字段多条件求和计数w 例：统计年龄在例：统计年龄在30岁至岁至40岁的人数岁的人数w 方法方法1：w =COUNTIF(F2:F18,=30)-COUNTIF(F2:F18,40)w 方法方法2

46、：w =SUM(F2:F18=30)*(F2:F18=30)*(F2:F18=20) * (C2:C5I12)ExcelExcel在商务中的在商务中的应用应用DSUM数据库函数的应用数据库函数的应用w 例：统计生产部学历为本科的员工的奖金合计例：统计生产部学历为本科的员工的奖金合计w 方法：方法：w =DSUM(A1:G18,I19,I15:J16)ExcelExcel在商务中的在商务中的应用应用DSUM函数函数w DSUM函数，按条件求和函数，按条件求和 w 语法形式语法形式:DSUM(database,field,criteria) w 其中：其中：nDatabase是区域是区域nfiel

47、d是列数是列数ncriteria是条件是条件 ExcelExcel在商务中的在商务中的应用应用DSUM函数函数w 例如：例如： ABC1产品名称产品名称产品单价（单位：元）产品单价（单位：元）总销售提成总销售提成2A11111001003B10010010104C20020030305D202040406E454550507F8888808089条件条件产品单价（单位：元）产品单价（单位：元）结果结果105050120单价大于单价大于50元的产品的销售提成总额元的产品的销售提成总额=DSUM(A1:C7,3,B9:B10)ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据

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

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

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

51、学历透视图制作员工学历透视图 ExcelExcel在商务中的在商务中的应用应用4.3.2制作员工学历透视图制作员工学历透视图w 案例背景案例背景w ExcelExcel的图表分析功能十分强大，其中的数的图表分析功能十分强大，其中的数据透视图更是聚集了强大的智能。本案例以人据透视图更是聚集了强大的智能。本案例以人事基础数据图为例，介绍如何在纷杂的数据中事基础数据图为例，介绍如何在纷杂的数据中提取所需要的数据，同时制作合乎需求的数据提取所需要的数据，同时制作合乎需求的数据透视表以及数据透视图。透视表以及数据透视图。ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图分析员

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

53、布和多个不同数据维的交叉制表。也就是说，创建一个数据透视表后，可以旋转其行和列以看是说，创建一个数据透视表后，可以旋转其行和列以看到源数据的不同汇总结果，而且可以显示不同页面的筛到源数据的不同汇总结果，而且可以显示不同页面的筛选数据，即根据需要显示区域中的明细数据。选数据，即根据需要显示区域中的明细数据。 ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 2 2、数据透视表的作用、数据透视表的作用: :n能够改变数据表的行、列布局能够改变数据表的行、列布局;

54、 ;n能够快速汇总大量数据能够快速汇总大量数据; ;n能够基于原数据表创建数据分组，并对分组进行汇能够基于原数据表创建数据分组，并对分组进行汇总统计。总统计。ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 包括：分页字段、数据项、行字段、列字段、项目等元素包括：分页字段、数据项、行字段、列字段、项目等元素数据项数据项列字段列字段页字段页字段行字段行字段ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图分析员工学历水平用数据透视表

55、和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 解释解释: :n行：拖放到行中的数据字段中的每个数据项将占据行：拖放到行中的数据字段中的每个数据项将占据透视表的一行。透视表的一行。n列：拖放到列中的数据字段中的每个数据项将占据列：拖放到列中的数据字段中的每个数据项将占据透视表的一列。行和列确定一个二维表格透视表的一列。行和列确定一个二维表格. .n页：拖放在页中的字段，页：拖放在页中的字段，ExcelExcel将按该字段的数据项将按该字段的数据项对透视表进行分页。对透视表进行分页。n数据：进行计数或汇总的字段名称。数据：进行计数或汇总的字段名称。ExcelEx

56、cel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 3 3、数据透视表的建立、数据透视表的建立n点击菜单命令点击菜单命令“数据数据数据透视表和数据透视图数据透视表和数据透视图”，打开，打开“数数据透视表和数据透视图向导据透视表和数据透视图向导”对话框。对话框。n第一步，如下图所示，选择第一步，如下图所示，选择“Microsoft Excel数据列表或数据数据列表或数据库库”及下面的及下面的“数据透视表数据透视表”单选项。单选项。 ExcelExcel在商务中的在商务中的应用

57、应用4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 3 3、数据透视表的建立、数据透视表的建立n第二步，如下图所示，在第二步，如下图所示，在“选定区域选定区域”输入全部数据所在的单输入全部数据所在的单元格区域，或者点击输入框右侧的元格区域，或者点击输入框右侧的“压缩对话压缩对话”按钮，在工作按钮，在工作表中用鼠标选定数据区域。表中用鼠标选定数据区域。n第三步，在对话框中选定第三步，在对话框中选定“新建工作表新建工作表”单选项，以便将创建单选项，以便将创建的数据透视表放到一个新的工作表中，再点击的数据透视

58、表放到一个新的工作表中，再点击“完成完成”按钮，按钮，如下图所示。如下图所示。ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 3 3、数据透视表的建立、数据透视表的建立n这样，就可以建立一个空的数据透视表，并同时显示这样，就可以建立一个空的数据透视表，并同时显示“数据透数据透视表视表”工具栏和工具栏和“数据透视表字段列表数据透视表字段列表”对话框，如图对话框，如图5所示。所示。 ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图

59、分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 4 4、利用数据透视表得到需要的结果、利用数据透视表得到需要的结果 w （1 1）统计所有学生各学科成绩的汇总数据）统计所有学生各学科成绩的汇总数据w （2 2）查看所有学生）查看所有学生“新闻学新闻学”成绩的平均值成绩的平均值”, ,平均值保留两位小平均值保留两位小数位数数位数w （3 3）查看男生）查看男生“新闻学新闻学”成绩的平均值成绩的平均值”, ,平均值保留两位小数位平均值保留两位小数位数数w （4 4）查看）查看“工商工商10101011010101班男生的班男生的“新闻学

60、新闻学”成绩的平均值成绩的平均值”, ,平平均值保留两位小数位数均值保留两位小数位数w （5 5）将第（）将第（2 2）题）题“行行”与与“列列”字段互换，并设置字段互换，并设置“自动套用格自动套用格式式”样式样式ExcelExcel在商务中的在商务中的应用应用4.3用数据透视表和数据透视图分析员工学历水平用数据透视表和数据透视图分析员工学历水平w 4.3.1编制员工学历透视表编制员工学历透视表w 5 5、修改数据透视表、修改数据透视表w 显示或隐藏行、列中的数据项显示或隐藏行、列中的数据项 n在行或列字段的下拉列表条中选择或取消复选标志。在行或列字段的下拉列表条中选择或取消复选标志。w 重新组织数据透视表重新组织数据透视表 n行、列置换行、列置换 ：鼠标移