项目八使用Excel管理和分析表格数据

Office高效办公

案例教程项目八使用Excel管理和分析表格数据任务一分析员工人事工资管理表数据任务二管理和分析员工资料表数据任务三管理和分析空调销售表数据任务四合并计算生产和销售状况表数据任务五制作员工工资柱形图表任务六制作运送成本饼图任务一分析员工人事工资管理表数据制作思绪打开素材文献，先对工作表进行自动筛选，筛选出基本工资最高的10名员工，再筛选出学历为硕士的员工，然后进行高级筛选，筛选出1992年后来参与工作的员工，基本工资不小于等于1500的男员工，最终汇总各部门的基本工资。其中筛选成果均放在新工作表中。基本工资最高的10个学历为硕士的员工参与工作时间为1992年后来的员工性别为男，基本工资不小于等1500的记录按部门汇总基本工资合计本任务内容提纲一、自动筛选二、高级筛选三、分类汇总一、自动筛选

打开素材文献，分别将“Sheet2”和“Sheet3”工作表重命名为“筛选1”和“筛选2”，然后再新建2个工作表，并命名为“筛选3”和“筛选4”。然后单击要进行筛选操作工作表中的任意非空单元格，然后单击“数据”选项卡上“排序和筛选”组中的“筛选”按钮单击“基本工资”标题右侧的筛选箭头，在展开的列表中选择“数字筛选”>“10个最大的值”项，打开“自动筛选前10个”对话框，单击“确定”按钮，筛选出基本工资最高的前10名员工，将自动筛选的成果复制到“筛选1”工作表中

先对工作表进行自动筛选，然后单击“学历”标题右侧的筛选箭头，在展开的列表中单击“全选”项，取消所有复选框的选中，然后只选中“硕士”复选框，，单击“确定”按钮，筛选出学历为硕士的员工，将自动筛选的成果复制到“筛选2”工作表中

二、高级筛选

先对工作表进行自动筛选，单击“参与工作时间”标题右侧的筛选箭头，在展开的列表中选择“日期筛选”>“之后”项，在打开对话框的“在如下日期之后”右侧的编辑框输入“1992年1月1日”，确定筛选出1992年后来参与工作的员工，将筛选成果复制到“筛选3”工作表中

在工作表中输入筛选条件，然后单击工作表中的任意单元格，再单击“排序和筛选”组中的“高级”按钮

打开“高级筛选”对话框，确认“列表区域”的单元格引用与否对的，本例保持默认，然后选中“将筛选成果复制到其他位置”单项选择钮，在“条件区域”编辑框中单击，然后在工作表中选择输入的条件在“复制到”编辑框中单击，然后在工作表中单击A37单元格确定后筛选出基本工资不小于等于1500的男性员工，将筛选成果剪切到“筛选4”工作表中分类汇总是把数据表中的数据分门别类地记录处理，无需建立公式，Excel会自动对各类别的数据进行求和、求平均值、记录个数、求最大值（最小值）和总体方差等多种计算，并且分级显示汇总的成果，从而增长了工作表的可读性，使顾客能更快捷地获得需要的数据并做出判断。要进行分类汇总的数据表的第一行必须有列标签，并且在分类汇总之前必须先对数据进行排序，以使得数据中拥有同一类关键字的记录集中在一起，然后再对记录进行分类汇总操作。分类汇总分为简朴分类汇总、多重分类汇总和嵌套分类汇总3种。三、分类汇总

在工作表中对部门进行升序排序，然后打开分类汇总对话框并进行设置并确定，即可按部门汇总基本工资合计

任务二管理和分析员工资料表数据制作思绪首先打开素材文献，然后按条件筛选出需要的员工，接着对工作表数据进行嵌套分类汇总操作，最终创立数据透视表分析工作表数据。

性别为男，民族为汉，学历为大专的筛选记录对“员工资料表”分别按“部门”和“职称”字段进行基本工资的嵌套分类汇总创立按“学历”查看的数据透视表，然后筛选出学历为“大专”的基本工资数据本任务内容提纲一、筛选需要的数据二、嵌套分类汇总数据三、使用数据透视表分析数据一、筛选需要的数据

将“员工资料表”中性别为男，民族为汉，学历为大专的记录筛选出来：输入筛选条件，然后单击工作表中的任意单元格，再单击“高级”按钮确认数据区域选中“将筛选成果复制到其他位置”单项选择钮，然后在“条件区域”编辑框单击，在工作表中选择输入的条件在“复制到”编辑框中单击，然后在工作表中单击要放置筛选成果单元格区域的左上角单元格，最终确定即可按条件筛选出所需数据二、嵌套分类汇总数据

打动工作表后对“部门”和“职称”列进行排序

嵌套分类汇总用于对多种分类字段进行汇总。下面对“员工资料表”分别按“部门”和“职称”字段进行基本工资的嵌套分类汇总。

设置第一次分类汇总项及效果

设置第二次分类汇总项及效果三、使用数据透视表分析数据

打动工作表后单击工作表中任一非空单元格，再单击“插入”选项卡上“表格”组中的“数据透视表”按钮，在展开的列表中选择“数据透视表”选项创立按“学历”查看的数据透视表，然后筛选出学历为“大专”的基本工资数据。确认“表/区域”编辑框中的数据源区域，此处保持默认，然后选中“新工作表”单项选择钮，单击“确定”按钮，系统自动新建一工作表以放置插入的空白数据透视表，并自动显示“数据透视表工具选项”选项卡和“数据透视表字段列表”窗格按规定布局字段及效果

单击数据透视表中“学历”右侧“所有”右侧的三角按钮，在展开的列表中选择某“大专”选项，确定后只显示“大专”学历的基本工资任务三管理和分析空调销售表数据制作思绪打开素材文献，然后对工作表数据按规定进行高级筛选操作、多重分类汇总，最终创立数据透视图分析工作表数据。

筛选出品牌为海尔，或“销售数量”不小于30的记录

对销售员进行销售额的求和与销售数量求平均值创立按“销售员”进行查看的数据透视图本任务内容提纲一、筛选需要的数据二、多重分类汇总数据三、使用数据透视图分析数据一、筛选需要的数据

筛选出品牌为海尔，或销售数量不小于30的记录。打开本素材工作簿，在工作表的空白单元格中输入列标题和对应的筛选条件，单击数据区域中任一单元格，也可先选中要进行高级筛选的数据区域，然后单击“数据”选项卡“排序和筛选”组中的“高级”按钮条件输入在不一样的行中，表达多选一条件筛选，即在查找时只要满足几种条件当中的一种即可

重新在工作表中选择数据区域筛选成果

选择筛选成果的放置位置选择条件区域选择筛选成果位置二、多重分类汇总数据

打开素材工作簿，然后对工作表中的“销售员”列进行升序排序

对工作表中的某列数据选择两种或两种以上的分类汇总方式或汇总项进行汇总，就叫多重分类汇总，也就是说，多重分类汇总每次用的“分类字段”总是相似的，而汇总方式或汇总项不一样，并且第2次汇总运算是在第1次汇总运算的成果上进行的。将“空调销售表”中的数据对销售员进行销售额的求和与销售数量求平均值。

设置第1次分类汇总选项及成果

设置第2次分类汇总选项及成果三、使用数据透视图分析数据创立一种按“销售员”进行查看的数据透视图：打开素材工作簿，然后单击工作表中的任意非空单元格，然后单击“插入”选项卡上“表格”组中的“数据透视表”按钮，在展开的列表中选择“数据透视图”项，打开“创立数据透视表和数据透视图”对话框，确认表/区域的单元格引用，然后选择数据透视图的放置位置

按规定布局字段，然后在数据透视表外单击，即可创立好数据透视图，同步在数据透视图的上方显示与之有关联的数据透视表任务四合并计算生产和销售状况表数据制作思绪打开素材文献后在源工作表中分别制作一种求合计和平均值的工作表格并进行格式化，然后运用合并计算功能进行合计和平均值的计算。

本任务内容提纲一、计算合计数据二、计算平均数据合并计算是指用来汇总一种或多种源区域中数据的措施。合并计算不仅可以进行求和汇总，还可以进行求平均值、计数记录和求原则差等运算，运用它可以将各单独工作表中的数据合并计算到一种主工作表中。其中，单独工作表可以与主工作表在同一种工作簿中，也可位于其他工作簿中。要想合并计算数据，首先必须为合并数据定义一种目的区，用来显示合并后的信息，此目的区域可位于与源数据相似的工作表中，也可在另一种工作表中；另一方面，需要选择要合并计算的数据源，此数据源可以来自单个工作表、多种工作表或多种工作簿。Excel提供了两种合并计算数据的措施：一是按位置合并计算，二是按分类合并计算。一、计算合计数据

打开素材工作翻开，然后在既有表格的右侧制作的两个表格

计算合计项。单击G3单元格，再单击“数据”选项卡上“数据工具”组中的“合并计算”按钮，打开“合并计算”对话框。在“函数”下拉列表中选择“求和”函数，然后在“引用位置”编辑框单击，在大新分厂表格中选择要进行合并计算的数据区域，松开鼠标左键返回“合并计算”对话框，然后单击“添加”按钮

分别在罗门分厂和龙门分厂表格中选择要进行合并计算的数据区域，将其添加到“所有引用位置”列表中，最终单击“确定”按钮

二、计算平均数据

计算平均值。单击G11单元格，然后打开“合并计算”对话框，选择“平均值”函数，保持“所有引用位置”列表框中的单元格引用不变（由于要添加的数据区域与列表中“所有引用位置”的相似），最终单击“确定”按钮

任务五制作员工工资柱形图表制作思绪打开素材文献，根据规定创立柱形图，再将其转换为独立图表，然后对创立的图表进行编辑和美化。

本任务内容提纲一、创立图表二、编辑图表三、美化图表一、创立图表

打开素材工作表，按住【Ctrl】键的同步选中要创立图表的数据区域，选择图表类型及详细的图表，此时，系统将在工作表中插入一张嵌入式图表将图表移动到指定的工作表中

二、编辑图表

单击图表将其激活，在“图表工具布局”选项卡的“标签”组中单击“图表标题”按钮，在展开的列表中选择“图表上方”，然后将图表标题修改为“实发工资柱形图表”

下面为图表添加标题、重要横纵坐标轴标题，并将图例隐藏。

为图表添加重要横纵坐标轴标题并设置其字符格式

在“图例”下拉列表选择“无”，在“数据标签”下拉列表选择“显示”，并为图表设置图案背景墙三、美化图表

为绘图区设置填充颜色和线条

为图表区设置纹理填充任务六制作运送成本饼图制作思绪打开素材文献，首先创立各木材资源区运送成本饼图，然后进行编辑和美化，再创立各市场运送成本饼图，并编辑和美化

本任务内容提纲一、创立并编辑各木材资源区运送成本饼图图表二、创立并编辑各市场运送成本饼图图表一、创立并编辑各木材资源区运送成本饼图图表

打开素材工作表，选中要创立图表的单元格区域，然后在“饼图”列表中选择“三维分离型饼图”，系统将在工作表中插入一张嵌入式图表

在“图表工具设计”选项卡的“图表