图表与数据透视表

课题：图表与数据透视表 【复习旧课】 1．复习：排序、分类汇总和高级筛选 2．注意分类汇总操作一定要先分类（排序）后汇总（分类汇总）， 3．注意高级筛选条件区域的创建：同一行是与的关系，不同行是或的关系。 【讲授新课】 一、数据的有效性 在使用Excel的过程中，经常要录入大量的数据，有些重复输入的数据往往还要注意输入的数据、数据格式是否有效的问题，为了保证录入数据的正确性，并提高录入数据的效率，可以利用Excel的数据有效性功能。 示例1：打开“SxEg4－5.xls”文件，完成下列操作后以原文件名存盘。 （1）对Sheet1中的数据清单进行设置，当用户选中“部门”列的任一单元格时，在其右侧显示一个下拉列表框箭头，并提供“基础部”、“研发部”、“财务部”、和“销售部”供用户选择。 （2）对Sheet1中的数据清单进行设置，当用户选中“职务”列的任一单元格时，在其右侧显示一个下拉列表框箭头，并提供“高级工程师”、“ 工程师”和“助手”供用户选择。 （3）对Sheet1中的数据清单进行设置，当用户选中“基本工资”列的任一单元格时，显示“请输入1000－7000的有效基本工资”，其标题为“基本工资”，当用户输入某一基本工资值时，即进行检查，如所输入的基本工资不在指定的范围内，错误信息提示“基本工资必须在1000－7000之间”，“停止（中止）”样式，同时标题为“基本工资非法”。以上单元格均忽略空值。 （4）为I2单元格插入批注：所有扣款之和。 二、图表的制作 图表是工作表中数据的一种图形表示，使数据显示更直观，便于数据的分析。当工作表中的数据发生变化时，图表中对应的数据也自动更新。Excel提供了非常强的图表处理功能，利用Excel提供的“图表向导”来完成图表的制作。 1. 图表创建方法 （1）图表的组成图表主要由图表标题、数值轴、分类轴、数据系列、图例等组成。 2．图表的创建 图表主要用于表现一般常见的统计图形，Excel提供了图表的创建向导，向导分为4个步骤，依次为：图表类型、图表源数据、图表选项和图表位置。 示例2：打开Eg5-43.xls，创建簇状柱形图，要求系列产生在行，嵌入在当前工作表中，最后以原文件名存盘。 3．图表工具栏 单击已创建好的图表，一般可以显示图表工具栏，若没有显示工具栏，则可以选择菜单“视图”→“工具栏”→“图表”命令或鼠标右击任意工具栏，选择“图表”命令。 三、图表的编辑 图表的编辑是指对已生成的图表的各个对象的编辑，包括图表类型的修改、数据系列的修改、增加与删除、图表标题、图例、坐标轴标题等的增加、删除与修改。要对图表进行编辑，首先必须选定图表。 1．图表类型的修改 对已创建的图表可以根据需要更改图表的类型，选定图表后，有以下两种修改方法： ● 右击图表，在弹出的快捷菜单中选择“图表类型”，在“图表类型”对话框中选择所需的图表类型和子类型。 ● 选择菜单“图表”→“图表类型”命令，在“图表类型”对话框中选择所需的图表类型和子类型。 ● 单击图表工具栏中的【图表类型】按钮来快速更改图表类型，但不能选择子类型。 2．数据的编辑 图表一旦创建，图表和创建图表的数据源之间就建立了联系，当数据源的数据发生变化时，图表中对应的数据会自动更新。 3．图表其它对象的编辑 图表中其它对象的编辑，只需右击或双击要编辑的对象，选择相应的内容进行编辑即可。图表创建向导的4个步骤中任意一个步骤的内容均可修改，有两种方法可进入图表创建向导： ● 右击图表，在快捷菜单中选择“图表类型”、“数据源”、“图表选项”、“位置”命令，按要求进行修改。 ● 选定图表，选择菜单“图表”→“图表类型”、“数据源”、“图表选项”、“位置”命令，选择要修改的菜单进行相应内容的修改。 如图表标题、图例、坐标轴标题等的增加、删除与修改可以在“图表选项”对话框中进行修改。 上机练习：打开Eg5-43 .xls，完成以下操作后以Eg5-44.xls存盘。（1）删除上例中的“平价超市”数据系列；（2）练习添加“平价超市”数据系列；（3）将数值轴的主刻度单位改为100000；（4）显示主网格线。（5）将“平价超市”的服装销售额改180000（用两种方法，注意工作表和图表的变化情况）；（6）删除分类轴标题；（7）将簇状柱形图改为分离型三维饼图；（8）图例放在图表底部显示并在图表中显示百分比及数据标志；（9）将图表位置到Sheet3中。 操作步骤如下： 1）单击“平价超市”数据系列，按〈Delete〉键。 2）右击图表，在快捷菜单中选择“数据源”，单击数据区域右边的折叠按钮，重新选择数据区域B7:F7，“添加单元格为”选中 ，单击【确定】按钮。 3）双击数值轴，弹出“坐标轴格式”对话框。选择“刻度”选项卡，在“主要刻度单位”后面的文本框中输入100000。 4）右击图表，选择“图表选项”，选择“网格线”选项卡，选中分类（X）轴和数值（Y）轴的“主要网格线”复选框。 5）两种方法如下： ● 单击“平价超市”数据系列，再次单击“服装”类的“平价超市”数据点，将鼠标指针移到上面中间的最大控制点处变成上下箭头时往上拖至180000，会显示数据标志的值。 ● 直接将工作表中“平价超市”中的“服装”销售额由51500改为180000。 6）直接选定分类轴标题，按〈Delete〉键。 7）右击图表，在快捷菜单中选择“图表类型”，选择“饼图”中的子类型“分离型三维饼图”，单击【确定】按钮。 8）右击图表，在快捷菜单中选择“图表选项”，选择“图例”选项卡，选中“显示图例”复选框，选中 。 在“图表选项”对话框中，选择“数据标志”选项卡。 9）右击图表，选择“位置” ，选中 。 四、图表的格式化 图表的格式化包括图案、刻度、字体、数字、对齐设置。对图表进行格式化有四种方法： ● 双击要设置格式的对象，即可弹出该对象的格式对话框，在对话框中进行相应设置。 ● 右击要设置格式的对象，同上方法设置。 ● 单击要设置格式的对象，单击“格式”菜单，即会出现选定对象的格式菜单，同上方法设置。 ● 在图表工具栏中选择要格式的对象，单击【对象格式】按钮，同上方法设置。 示例4：打开Eg5- 44.xls，完成以下操作后以Eg5- 45.xls存盘。（1）将图表标题设置为蓝色楷体字、字号为14；（2）设置图例字体为宋体、字号为10，无边框；（3）设置数据系列“服装”、“鞋帽”、“电器”、“化妆品”的颜色分别为“红色”、“黄色”、“蓝色”和“绿色”。并将第一扇区起始角度设置为60度。（4）设置背景墙的颜色为白色，图表区的颜色为浅绿色。 上机练习：打开Eg5- 44.xls，完成以下操作后以Eg5- 45.xls存盘。（1）将图表标题设置为蓝色楷体字、字号为14；（2）设置图例字体为宋体、字号为10，无边框；（3）设置数据系列“服装”、“鞋帽”、“电器”、“化妆品”的颜色分别为“红色”、“黄色”、“蓝色”和“绿色”。并将第一扇区起始角度设置为60度。（4）设置背景墙的颜色为白色，图表区的颜色为浅绿色。 操作步骤如下： 1）双击图表标题“各商场部分商品销售图”，弹出“图表标题格式”对话框，选择“字体”选项卡，设置字体为楷体，字号为16，颜色为蓝色。 2）双击图例，弹出“图例格式”对话框，选择“字体”选项卡，设置字体为宋体，字号为12；选择“图案”选项卡，选中“边框”中的“无”单选按钮。 3）依次双击“服装” 、“鞋帽”、“电器”、“化妆品”数据系列，弹出“数据系列格式”对话框中的“图案”选项卡，在“内部”框架中依次选择“红色”、“黄色”、“蓝色”和“绿色”。在“数据系列格式”对话框中选择“选项”选项卡，在“第一扇区起始角度”中选择或输入60。 4）双击背景墙，弹出“绘图区格式”对话框，在“图案”选项卡的“区域”框架中选择“白色”；双击图表区，在弹出“图表区格式”的“图案”选项卡的“区域”框架中选择“浅绿”。 五、数据透视表 数据透视表实际上就是一种汇总表，是一种对大量数据快速汇总和建立交叉列表的交互式。 1．创建数据透视表 一般按向导来创建数据透视表。下面通过一个实例来讲述数据透视表的创建过程。 示例5：打开Eg5-39.xls，对Sheet1中的数据清单创建数据透视表，班级放在页字段，性别放在行字段，数据项为语文成绩的平均成绩、英语成绩的最大值。 分析： “页”字段：将右边的字段名拖至该区域，则该字段名的每个值构成一张汇总表。 “行”字段：将右边的字段名拖至该区域，则该字段名的所有值以行的形式显示。 “列”字段：将右边的字段名拖至该区域，则该字段名的所有值以列的形式显示。 “数据”区：将右边的字段名拖至该区域，则会对该字段进行汇总，双击字段可以选择汇总方式。凡是要进行汇总（计算）的字段都要拖至该区域。 本例中将“班级”字段拖至页字段区，“性别”字段拖至行字段区，“语文”、“英语”字段由于要进行汇总，因此拖至数据区。双击数据区中的“语文”字段，选择“平均值”汇总方式，双击数据区中的“英语”字段，选择“最大值”汇总方式。 5）单击【确定】按钮，返回到“数据透视表和数据透视图向导——3步骤之3”对话框。 6）单击【完成】按钮。 2．编辑数据透视表 数据透视表创建好以后，若跟要求不符还可以对数据透视表进行修改编辑。编辑方法是： 1）在数据透视表中的任一单元格单击，此时会出现“数据透视表”工具栏，没有出现的话则选择菜单“视图”→“工具栏”→“数据透视表”命令，“数据透视表”命令前会出现“√”可以调出“数据透视表”工具栏。 2）选择菜单“数据透视表”工具栏中的 ，弹出“数据透视表和数据透视图向导—3步骤之3”对话框，单击【布局】按钮，在 “数据透视表版式效果”中进行修改。 3）修改完后单击【确定】按钮。 示例6：（1）将上例中的“性别”字段拖至列字段区效果如何？比较放在行字段区与放在列字段区中的区别。（2）将“性别”字段放行，“班级”字段放列，效果如何？ 示例7： 打开Eg5-41.xls，根据Sheet1中的数据清单，作一个由三行组成的数据透视表，其中第一行为年龄，第二行为该年龄人员对应的最高身高，第三行为该年龄人员对应的平均体重，置于Sheet1中的F1单元格为左上角的区域中。 分析：该数据透视表共有三个字段：年龄、身高、体重。由于身高、体重字段要进行汇总，必须放到数据区，由于第一行为各年龄的值，即所有的年龄段放在同一行，因些年龄字段拖至列字段，行字段区为空。 六、工作表的打印设置 1．打印预览 在打印输出之前一般要进行打印预览，看看打印输出的效果是否符合要求。 单击常用工具栏中的打印预览按钮 ，或选择菜单“文件”→“打印预览”命令。 下一页/上一页：当打印页面超过一页时【下一页/上一页】按钮可用。 缩放：单击该按钮，在整页显示与放大状态进行切换。 打印：单击该按钮弹出“打印”对话框。 设置：单击该按钮可直接进入“页面设置”，但“工作表”选项卡的部分功能不能使用。 页边距：单击该按钮在页面以虚线直观上显示页边距、距页眉/页脚的距离的位置及表格之间的列宽，可在此视图中通过拖拽鼠标直接调整页边距、距页眉/页脚的距离及表格之间的列宽。再次单击【页边距】按钮，可以取消虚线的显示。 分页预览：单击该按钮打开“分页预览”视图，可以方便地调节蓝色框的位置来调节每页的内容。 关闭：关闭打印预览窗口，回到普通视图。 2．打印设置 当工作表设置好符合要求后，即可进行打印输出。选择菜单“文件”→“打印”命令，或按快捷键〈Ctrl+P〉，可弹出“打印”对话框。 3．设置打印区域 对于工作表的数据，用户可以有选择性地选择有关数据进行打印，要想打印工作表中的一部分数据，必须要设置打印区域。 【巩固新课】 通过本次课的讲解让学生掌握数据的有效性、图表及数据透视表的灵活运用。 【课间练习】 1． 数据的有效性 请打开“100048.xls”工作簿，完成下列操作后以原文件名存盘： （1）在Sheet1中，使用数据有效性工具完成以下的操作。(没有要求操作的项目请不要更改） A．当用户选中“间隔”列的第2行至第12行(含)中的某一行时，在其右侧显示一个下拉列表框箭头，并提供“2房1厅”、“3房1厅”和“3房2厅”的选择项供用户选择。 B．当用户选中“租价”列的第2行至第12行(含)中的某一行时，在其右侧显示一个输入信息“介于1000与5000之间的整数”，标题为“请输入租价”，如果输入的值不是介于1000与5000之间的整数，会有出错警告，错误信息为“不是介于1000与5000之间的整数”，标题为“请重新输入”。 (选择项必须按题述的顺序列出，A操作的有效性条件为序列，B操作的有效性条件为介于整数之间） （2）在Sheet2中，当用户选中“职务”列的任一单元格时，在其右则显示一个下拉列表框箭头，并提供“副处长”，“处长”和“科长”的选择项供用户选择；当选中“年龄”列的任一单元格时，显示“请输入1-100的有效年龄”，其标题为“年龄”，当用户输入某一年龄值时，即进行检查，如果所输入的年龄不在指定的范围内，错误信息提示“年龄必须在1-100之间”，“停止（中止）”样式，同时标题为“年龄非法”。以上单元格均忽略空值。 2． 图表 打开“xls\ 100054.xls”工作薄，完成下列操作后以原文件名存盘： （1）根据下列要求对工作表“sheet1”完成操作： 在范围A2:E6 基础上建立图表(圆柱图-条形圆柱图)，标题为“08年硬件销售额”，图表嵌入原表内。 （2）在Sheet2中完成下列操作： 建立数据点折线图以显示各个区在第八个月和第十个月的二手楼成交均价（即图表的数据为A3:A8,C3:C8,E3:E8），数据系列产生在行，图表标题为“二手楼价走势图”，分类轴标题为“月份”，数值轴标题为“均价”，数值轴刻度的最小值为2000，最大值为6000，主要刻度单位为500，建立的图嵌入在原工作表中。 （3）在Sheet3中完成下列操作： 按下列要求作图： 图表类型为--三维簇状条形图； 图表的数据区域--B2:E2,B7:E7，系列按行； 图表标题--硬件部销售额； 有分类（X）主要网格线及数值（Z）主要网格线； 数据标志显示值； 图表放在/嵌入当前工作表中。 3.  数据透视表 请打开xls\101006.xls工作簿，完成下列操作后以原文件名存盘： （1）在Sheet1中，利用“工资表1”工作表作为数据源创建数据透视表，以反映不同职称的最高基本工资情况职称,将“职称”设置为行字段。请把所创建的透视表放在当前工作表的D12开始的区域中，并命名为“职位最高工资”，取消列总计。 （2）在Sheet2中，利用“工资表2”工作表作为数据源创建数据透视表，以反映不同性别（在前)、不同职称(在后)的平均基本工资情况,将“性别”和“职称”设置为行字段。请把所创建的透视表放在当前工作表的D13开始的区域中，并命名为“工资透视表”。 （3）根据Sheet3中的数据清单，作一个由三行组成的数据透视表，其中第一行为年龄，第二行为该年龄人员对应的最高身高，第三行为该年龄人员对应的平均体重，置于Sheet1中的F1单元格为左上角的区域中。