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

Office2010高效办公案例教程项目八使用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】键的同时选中要创建图表的数据区域，选择图表类型及具体的图表，此时，系统将在工作表中插入一张嵌入式图表将图表移动到指定的工作表中二、编辑图表单击图表将其激活，在“图表工具布局”选项卡的“标签”组中单击“图表标题”按钮，在展开的列表中选择“图表上方”，然后将图表标题修改为“实发工资柱形图表”下面为图表添加标题、主要横纵坐标轴标题，并将图例隐藏。为图表添加主要横纵坐标轴标题并设置其字符格式在“图例”下拉列表选择“无”，在“数据标签”下拉列表选择“显示”，并为图表设置图案背景墙三、美化图表为绘图区设置填充颜色和线条为图表区设置纹理填充任务六制作运输成本饼图制作思路打开素材文件，首先创建各木材资源区运输成本饼图，然后进行编辑和美化，再创建各市场运输成本饼图，并编辑和美化本任务内容提要一、创建并编辑各木材资源区运输成本饼图图表二、创建并编辑各市场运输成本饼图图表一、创建并编辑各木材资源区运输成本饼图图表打开素材工作表，选中要创建图表的单元格区域，然后在“饼图”列表中选择“三维分离型饼图”，系统将在工作表中插入一张嵌入式图表在“图表工具设计”选项卡的“图表布局”组中为图表选择布局后输入图表标题为图表选择图表样式填充图表区二、创建并编辑各市场运输成本饼图图表选中要创建图表的单元格区域，然后在“饼图”列表中选择“复合饼图”，系统将在工作表中插入一张嵌入式复合图表在“图表工具设计”选项卡的“图表布局”组中为图表选择布局后输入图表标题将图表区填充“橙色，强调文字颜色6，淡色60%”将图例填充为“白色，背景1”为图表选择样式后将图表移到合适位置谢谢观赏！