计算机应用基础课件第7章 Excel高级应用1-人事档案管理分析

7.1项目分析7.2任务一：设计制作职工档案模板任务要求与效果：1）美化并规范职工档案模板。2）自定义单元格格式3）利用VLOOKUP函数动态抽取员工各项信息并根据身份证号码提取归属地4）利用SUMIF函数的计算各学院所有新入职员工的安置费总和以及所得科研启动资金的总额。知识点解析1.完善工作表格式2.VLOOKUP函数的应用VLOOKUP函数的功能：在数据区域的第一列中查找指定的数值，并返回数据区域当前行中指定列处的数值。VLOOKUP函数的语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)3.SUMIF函数的应用SUMIF函数与COUNTIF函数用法相似，SUMIF主要是针对单个条件的统计求和。SUMIF函数的功能：用于按指定条件在查找区域进行查找，并返回对应于查找区域的另外一个数据区域中数值的和。SUMIF函数语法是：SUMIF(range，criteria，sum_range)SUMIF函数的参参数的函义：第一个参数：Range为条件区域，用于条件判断的单元格区域。第二个参数：Criteria是求和条件，为确定哪些单元格将被相加求和的条件，其形式可以由数字、逻辑表达式等组成的判定条件。例如，条件可以表示为32、"32"、">32"或"木材"。第三个参数：Sum_range为实际求和区域，需要求和的单元格、区域或引用。是真正进行求和计算的区域。

任务实现步骤1.制作员工档案模板2.利用VLOOKUP函数动态抽取员工档案各项信息3.利用VLOOKUP函数查询出员工身份归属地4.利用SUMif函数统计各部门员工获得的安置费用及科研启动资金的总额总结与提高1.VLOOKUP近似查询：2.SUMIFS函数：7.3任务二：分类汇总任务要求与效果1）利用分类汇总的操作。统计新入职男女员工的平均年龄及各职称等的平均年龄，各部门的入职人数。结果如图所示。2）利用分类汇总统计各二级学院新入职员工的安置费和科研启动资金总额，并找出科研启动资金最高的学院知识点解析分类汇总是以某一个字段做为分类的项，对工作表数据区域的其他字段进行各种统计计算，如求和、计数、求平均值、最大值等。

任务实现步骤1.

统计新入职男女员工的平均年龄及各职称的平均年龄。2.利用分类汇总统计各二级学院新入职员工的安置费和科研启动资金总额，并找出科研启动资金最高的学院总结与提高多重分类汇总。实际工作中有时需要汇总多种结果，此时需要进行多重分类汇总，可以是多字段的也可以是多重计算的多重分类汇总。7.4任务三：自动筛选

任务要求与效果需要从新入职的员工记录中筛选出具有高级职称而且笔试成绩在80-90（包含90）的女职工的记录。效果如图知识点解析Excel的自动筛选功能可以根据需要（即筛选的条件）大工作表中只显示想要查看的内容，而隐藏其余内容。筛选不会以任何方式更改数据。一旦移除筛选，所有数据就会重新显示。从而达到根据不同需求只查看到要关注的记录。

任务实现步骤筛选特别关注的员工信息从新入职的员工记录中筛选出具有高级职称而且笔试成绩在80-90（包含90）的女职工的记录。总结与提高当进行筛选条件比较复杂的多条件查询或者需要将筛选结果复制到其它位置时需要利用高级筛选完成。7.5任务三：数据透视表任务要求与效果1）能够分析出各学院各种职称等级的学历层次，2）改变数据透视表的布局、透视关系，分析各不同学历层次不同职称级别的笔试成绩。3）设置字段显示方式，分析各职称在新入职员工的占比情况。知识点解析1.什么是数据透视表数据透视表是用来从excel数据列表、关系数据库或OLAP多维数据集中的字段中总结信息的分析工具。是一种交互式报表，可以快速汇总、分析大量数据表格的交互式工具。按照数据表格的不同字段从多个角度进行透视，并建立交叉表格，用以查看数据表格不同层面。只需动动鼠标移动字段位置便可变换出各种类型的报表。2.数据透视表的用途数据透视表功能，可以被认为是Excel的精华所在。使用数据透视表可以深入分析数值数据，以帮助用户发现关键数据，并做出有关企业中关键数据的决策。数据透视表有机综合了数据排序、筛选、分类汇总等数据分析的优点，具有所谓“透视”功能。可以将纷繁的数据化为有价值的信息，提供研究和决策所用。3.数据透视表的结构如图7-46所示。对于“员工信息表2”中大量员工流水数据，如果需要按二级学院统计各级别职称中员工的学历层次情况，可以建如图7-46所示的透视表。从结构看，数据透视表分为四个部分。行区域、列区域、数据区域、报表筛选区域。4.几个名词的含义行标签：作为横向分类依据的字段列标签：作为纵向分类依据的字段。数值：拖至此处的字段是计算的依据，Excel将用某种算法，对其进行计算，然后将计算结果（汇总数值）显示出来报表筛选：作为分类显示（筛选）依据的字段，可以将一个或多个字段拖至此处。此区域中的字段是分类筛选的首要条件。

任务实现步骤1.

创建数据透视表2.改变数据透视表的布局、透视关系3.改变数据透视表的值显示方式4.添加切片器用于查看透视表中不同部门的职称学历分布总结与提高制作数据透视图直观显示各职称类别人数的占比情况。7.6任务四：制作动态图表

任务要求与效果1）利用自动筛选制作动态图表以更方便查看调查结果，效果如图2）利用VLOOKUP函数制作动态图表效果如图知识点解析1.什么是动态图表动态图表，即交互图表，可以让用户通过鼠标来选择不同的预设项目从而在图表中动态显示对应的数据。这里学习两种动态图表的设计方法2.利用自动筛选法制作动态图表利用自动筛选是实现动态图表最简单的方法，只要选择全部数据制作图表再利用自动筛选挑出感兴趣的数据项即可实现动态查看。3.利用函数公式法制作动态图表在数据区域以外通过添加辅助行，利用VLOOKUP(也可以利用其他查询和引用函数)函数取得项目的数据。再利用EXCEL的数据有效性功能设置下拉列表来选择项目从而实现动态选择数据制作动态图表。

任务实现步骤1.自动筛选法制作动态图表2.公式法制作动态图表总结与提高利用定义名称法制作动态图表，图表引用的数据区域由OFFSET函数生成，不必出现在工作表中，这时工作表页面简洁，数据也不会被查看到。使用OFFSET函数定义一个名称，图表的数据区域指向名称所在位置，当OFFSET中参数值发生变化，那么名称所指定的区域也发生变化，