人事信息数据统计分析

Excel在商务中的应用任课教师：李磊Excel在商务中的应用第4章人事信息数据统计分析本章要点：4.1人事信息数据表4.2人事数据的条件求和计数4.3用数据透视表和数据透视图分析员工学历水平4.4人事数据表的两表数据核对4.5员工人事信息数据查询表（员工信息卡片）4.6统计不同年龄段员工信息4.7人力资源月报动态图表Excel在商务中的应用第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.4DSUM数据库函数的应用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美化图表区Excel在商务中的应用4.1人事信息数据表案例背景企业里由于人员较多且流动变化大，因而人力资源部应及时做好人事数据的整理、汇总分析等工作，并且这些数据常常也是企业做各项决策的参考依据，因此处理好人事数据的整理工作意义重大。人事信息数据表是企业进行人事信息管理的基础和依据，因此，人事信息管理表格一定要科学、准确、详细，并且有利于查找、利用，这样才能真正辅助企业管理者进行人事信息管理工作。4.1.1创建人事信息数据表4.1.2利用数据有效性防止工号重复输入4.1.3身份证号中提取生日、性别等有效信息4.1.4应用DATEDIF函数计算员工年龄4.1.5设置每页顶端标题行和底端标题行4.1.6美化表格Excel在商务中的应用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号Excel在商务中的应用创建人事信息数据表人事信息数据表包括项目有：姓名、性别、年龄、身份证号（或社会保障号）、学历、现任职务、联系电话、E-mail和居住地等有效信息。其中性别、年龄、出生日期等项目均不需要手工录入，利用函数计算即可。Excel在商务中的应用身份证号中提取生日、性别等有效信息建立人事信息数据表时，性别和出生日期两个项目可以不必手工录入，而是利用函数通过身份证号码来自动提取即可。Excel在商务中的应用分析身份证号码其实，身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。例如:某员工的身份证号码(15位)是320521720807024，那么表示1972年8月7日出生，性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错，核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。Excel在商务中的应用分析身份证号码18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女，第18位为校验位。Excel在商务中的应用4.1人事信息数据表关键技术点（方法1）要实现本例中的功能，读者应当掌握以下Excel技术点。DATEIF函数RIGHT函数LEFT函数MID函数LEN函数Excel在商务中的应用编号姓名身份证号码出生日期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*2Excel在商务中的应用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-XX分析：1、前面的两个减号表示要返回数值型结果2、函数公式中的“*”表示IF函数P136LEN(F3)=18返回0或返回1则：6+1*2=8或6+0*2Excel在商务中的应用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*2Excel在商务中的应用=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日Excel在商务中的应用RIGHT(LEFT(c2,17))RIGHT(34270119760213857,1)--7RIGHT(LEFT(c3,17))RIGHT(342701820213857,1)7从身份证中提取性别ABC1编号姓名身份证号码2A019吴仕3427011976021385783A020孙国成342701820213857Excel在商务中的应用LEFT函数用途：得到字符串左部指定个数的字符。语法形式：LEFT(string,n)参数：string指定要提取子串的字符串。n指定子串长度返回值String。说明：函数执行成功时返回string字符串左边n个字符，发生错误时返回空字符串（）。如果任何参数的值为NULL，Left()函数返回NULL。如果n的值大于string字符串的长度，那么Left()函数返回整个string字符串，但并不增加其它字符。Excel在商务中的应用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。Excel在商务中的应用4.1人事信息数据表关键技术点（方法2）要实现本例中的功能，读者应当掌握以下Excel技术点。IF（）函数CHOOSE（）函数MOD（）函数LEN（）函数RIGHT（）函数MID（）函数DATE（）函数Excel在商务中的应用IF函数的应用IF函数是根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），从而返回其相对应得内容。语法形式：IF(logical_test,value_if_true,value_if_false)其中：logical_test逻辑判断表达式value_if_true当判断条件为逻辑“真”（TR