任务情景

每个学期期末都是学院各个部门最忙的时候,学院的财务部门更是忙得不可开交,统计各种经费的支出结果,分析各项支出的具体情况并作比较,这些数据的处理分析往往会让很多工作人员头疼,但是使用Excel处理这些事务就轻松多了。

任务目标及效果

完成学院一学期各月的各种经费支出统计,做出详细的对比图。如图4-78、图4-79所示。

图4-78 数据透视表

图4-79 数据透视图

任务分析

建立经费支出统计表,将各个系部的经费支出情况全部录入Excel表中。

1.以经费支出统计表作为数据源做出各系经费支出统计的数据透视表。

2.以经费支出统计表作为数据源做出各系经费支出统计的数据透视图。

知识准备

数据透视表是一种具有创造性和交互性的报表。使用数据透视表,可以汇总、分析、浏览与提供汇总数据。而数据透视表强大的功能主要体现在可以使杂乱无章、数据庞大的数据表快速有序地显示出来,是Excel2010用户不可缺少的数据分析工具。

1.创建数据透视表

选择需要创建数据透视表的数据区域,该数据区域要包含列标题。执行“插入”选项标签|“表格”功能区|“数据透视表”选项|“数据透视表”命令,如图4-80所示。

图4-80 插入数据透视表

该对话框主要包括以下选项。

(1)选择一个表或区域:选中该单选按钮,表示可以在当前工作簿中选择创建数据透视表的数据。

(2)使用外部数据源:选中该单选按钮后单击“选择链接”按钮,在弹出的“现有链接”对话框中选择链接数据即可。

(3)新工作表:选中该单选按钮,可以将创建的数据透视表显示在新的工作表中。

(4)现有工作表:选中该单选按钮,可以将创建的数据透视表显示在当前工作表所指定的位置中。

对话框中单击“确定”按钮,即可在工作表中插入数据透视表,并在窗口右侧自动弹出“数据透视表字段列表”任务窗格。用户在“选择要添加到报表的字段”列表框中选择要添加的字段即可,如图4-81所示。

图4-81 “数据透视表字段列表”任务窗口

用户也可以在数据透视表中,像创建图表那样创建以图形形状显示数据的透视表透视图。选中数据透视表,执行“选项”|“工具”|“数据透视图”命令,在弹出的“插入图表”对话框中选择需要插入的图标类型即可,如图4-82所示。

图4-82 “插入图表”对话框

特别提示

用户可以执行“插入”选项标签|“表”功能区|“数据透视表”命令,选择“数据透视图”选项,创建数据透视图。

2.编辑数据透视表

创建数据透视表之后,为了适应分析数据的需求,需要编辑数据透视表。其编辑内容主要包括更改数据的计算类型、筛选数据等内容。

(1)在“数据透视表字段列表”任务窗格中的“数值”列表框中,单击“数值类型”,选择“值字段设置”选项,在弹出的“值字段设置”对话框中的“计算类型”列表框中选择计算类型即可,如图4-83所示。

图4-83 “值字段设置”对话框

特别提示

用户也可以通过执行“选项”|“计算”|“按值汇总”命令,在其列表中选择相应的方法来更改计算类型。

(2)设置数据透视表样式。

Excel2010为用户提供了浅色、中等深浅、深色三种类型共85种样式。选择数据透视表,执行“设计”选项标签|“数据透视表样式”功能区命令,在下拉列表中选择一种样式即可,如图4-84所示。

(www.daowen.com)

图4-84 数据透视表样式

(3)筛选数据

选择数据透视表,在“数据透视表字段列表”任务窗格中,将需要筛选数据的字段名称拖动到“报表筛选”列表框中。此时,在数据透视表上方将显示筛选列表,用户可单击“筛选”按钮,对数据进行筛选。

另外,用户还可以在“行标签”“列标签”或“数值”列表框中单击需要筛选的字段名称后面的下三角按钮,在下拉列表中选择“移动到报表筛选”选项,将该值字段设置为可筛选的字段。

任务实施

步骤一 制作学院经费统计表

制作“学院3～6月份各系经费支出统计表”工作簿,并在Sheet1工作表中录入如图4-85所示的数据,将工作表Sheet1重新命名为“经费支出统计表”,设置好单元格格式。

图4-85 经费支出统计表

步骤二 以经费支出统计表作为数据源做出各系经费支出统计的数据透视表

打开“学院3～6月份各系经费支出统计表”工作簿,在工作表“经费支出统计表”中选定单元格A2至D18区域,执行“插入”选项标签|“表格”功能区|“数据透视表”命令,弹出“创建数据透视表”对话框,如图4-86所示。

图4-86 “创建数据透视表”对话框

在打开的“创建数据透视表”对话框中“选择一个表或区域”选项下的数据导入处显示“经费支出统计表! ＄A＄2:＄D＄18”字样,它代表创建数据透视表的数据源,将“选择放置数据透视表的位置”选项设置为默认的“新工作表(N)”,单击“确定”按钮,将新建如图4-87所示的工作表。

图4-87 新建的“数据透视表”工作表

在工作表右边“数据透视表字段列表”任务窗格中将“选择要添加到报表的字段”中的“费用明目”拖入“在以下区域间拖动字段”中的“行标签”下方的区域中,工作表中显示如图4-88所示的数据透视表。

图4-88 未完成的数据透视表

按照上面的方法将“系别”和“月份”拖入“列标签”区域中,将“金额”拖入“数值”区域中,完成操作后,工作表中出现如图4-89所示的数据透视表。

图4-89 完成的数据视表

步骤三 以经费支出统计表作为数据源做出各系经费支出统计的数据透视图

打开“学院3～6月份各系经费支出统计表”工作簿,在工作表“经费支出统计表”中选定单元格A2至D18区域,执行“插入”选项标签|“表格”功能区|“数据透视表”选项|“数据透视图”命令,弹出“创建数据透视表及数据透视图”对话框,如图4-90所示。

图4-90 “创建数据透视表及数据透视图”对话框

在打开的“创建数据透视表及数据透视图”对话框中“选择一个表或区域”选项下的数据导入处显示“经费支出统计表! ＄A＄2:＄D＄18”字样,它代表创建数据透视表的数据源,将“选择放置数据透视表的位置”选项设置为默认的“新工作表(N)”,单击“确定”按钮,将新建如图4-91所示的工作表。

图4-91 未设置数据透视图工作表

单击数据透视表区域,在工作表右边“数据透视表字段列表”任务窗格中将“选择要添加到报表的字段”中的“费用明目”拖入“在以下区域间拖动字段”中的“行标签”下方的区域中,依此方法将“系别”和“月份”拖入“列标签”区域中,将“金额”拖入到“数值”区域中,完成操作后,工作表中出现如图4-92所示的数据透视图。

图4-92 数据透视图

单击数据透视图任意位置,执行“布局”选项标签|“坐标轴”功能区|“网线格”|“主要横网线格”|“无”命令取消横向网线格,执行“布局”选项标签|“坐标轴”功能区|“网线格”|“主要纵网线格”|“主要网线格”命令添加纵向主要网线格,为图标添加标题为“学院3～6月份各系经费支出统计”,完成设置的数据统计图如图4-93所示。

图4-93 数据透视图设置效果

实战演练

一、操作题

在Excel工作簿中录入如图4-94所示的数据,根据表中数据创建透视表,其中,报表筛选中为“平均值”,行标签为“月份”,数值“一车间、二车间”。

图4-94 车间数据