制作数据透视表的步骤和方法
(一)概述
1、什么是数据透视表
数据透视表是Excel中功能十分强大的数据分析工具,用它可以快速形成能够进行交互的报表,在报表中不仅可以分类汇总、比较大量的数据,还可以随时选择其中页、行和列中的不同元素,以快速查看源数据的不同统计结果。
“数据透视表”这一术语中最核心的字眼是“透视”,那么为什么叫做“透视”呢?打个简单的比方,当我们想研究某个现实中的物品时,就会从不同的角度去观察它,从左到右、从上到下、从里到外,从一切可以观察的角度去查看和了解。数据透视表就是这样一种工具,只不过它的分析对象不是实际的物体,而是Excel工作表中的数据。数据透视表的出现,给我们从不同的角度去查看和分析数据提供了极大的方便。
2、数据透视表结构
数据透视表的结构:页字段(左上角)、行字段(左侧)、列字段(上面)、数据项(右下角的大部分区域)。
字段除了可以直接从源数据中提取以外,也可以根据使用者的需要自行定义(详见讲座下的内容)。
字段默认是提取据清单(源数据)的第一行标题。
计算项是字段的具体组成内容或成员。
数据透视表的大部分的工作,可以通过数据透视表工具栏或鼠标右键选项进行。
(二)整理数据清单
为了更准确的使用数据透视表,对于数据清单的要求:不要有空行和空列,最好不要有小计和累计,尽量不要有合并的单元格。
1、删除所有空行
1.1 方法一:手工法
手工选择空白行,右键——删除
1.2 方法二:数字序号辅助列法
在数据清单的最右侧增加辅助列,自动填充序号。选中数据清单,数据——排序,选择姓名作为主要关键字,升/降序皆可(学习:注意选择有无标题行)。排序完毕以后,就可以把多余的空白行一起删除。然后根据辅助列进行排序,就恢复了。
1.3 方法三:自动筛选+定位法
选中数据清单,数据——筛选——自动筛选(如仅仅选择第一行的标题单元格,自动筛选会默认第一行至空白单元格,空白单元格以下的部分则不被选中),选择姓名下拉菜单的非空白项。
在选中数据区域,编辑——定位——定位条件——可见单元格,这时所有的可见单元格便选定,然后复制——粘贴,即可完成。
(学习:关于定位的整理,详见学习体会上-1的内容)。
1.4 方法四:公式法,详见2.4。
2、删除所有空列
2.1-2.3 同删除空行相同
2.4 方法四:公式法
在最下一行,输入公式:=1/COUNTA(A1:A6),空白列会显示出#DIV/0!,选中最下一行,编辑——定位——定位条件——公式——错误,然后再选编辑——删除,就完成了。
(学习:counta函数,常用的统计函数之一,用于统计列表中含数字或文本的单元格个数)。
3、删除所有小计行
选中数据清单,编辑——查找,输入“计”,点击查找全部,在对话框中CTRL+A,选中查找到的全部,关闭查找对话框以后,编辑——删除——整行,就完成了。
4、填充空白单元格
数据清单中有合并单元格,影响了字段、数据的提取。
选中合并单元格,格式——单元格——对齐——合并单元格,取消了合并的单元格,
编辑——定位——定位条件——空值,输入=A2,CTRL+回车,输入了全部空白的单元格(相当于向下填充)。
选中A2:A15,复制——选择性粘贴——数值(也可以点击粘贴按钮下方的值)。
然后再点击数据透视表工具栏中的刷新按钮,即可完成。
如果在前面的操作之前,先保留了原合并单元格的格式(格式刷刷空白处,保留),在前面的操作完成以后再恢复格式(格式刷刷回来),即不影响数据清单的原显示格式。
(三)制作数据透视表的方法和步骤
1、利用向导制作数据透视表
数据——数据透视表和数据透视图——向导3步骤之1(选择数据源类型和数据透视表类型)——向导3步骤之2(选择数据清单的位置或名称)——向导3步骤之3(显示位置,布局,选项等)
布局,可以方便的将字段项拖至各自的位置,同时也可以看出目前的透视表的各字段的位置。
2、在数据透视表中进行布局
与1次序不同,看各人习惯了。
3、进行布局的注意事项
3.1 页、行、列字段里面放置的字段不能重复,具有唯一性和排他性;
3.2 数据字段中没有3.1的限制,例如可以有一个以上的销售额字段。其作用可以改变相同数据字段的不同汇总方式,例如平均值和求和。
(四)没有数值的工作表数据创建数据透视表
源数据没有数值,建立一个统计不同职务的数据透视表。
行字段和数据字段各使用一次,数据字段汇总方式选为计数。