为了创建出格式正确且能够得到准确分析结果的数据透视表,通常在创建数据透视表之前,需要检验一下数据透视表数据源的结构和格式。在本章中,您将了解到数据透视表数据源的设计原则,并学会如何将不规范的数据源整理为规范格式。另外,本章还介绍了可用于创建数据透视表的有效数据来源,并在最短的时间内带您一起创建出一个简单的数据透视表。
本章要点
❖数据透视表数据源的设计原则
❖整理数据源的方法
❖可用于创建数据透视表的有效来源
❖创建数据透视表的基本方法
数据源就是指用于创建数据透视表的数据来源,即数据透视表是使用哪些数据创建出来的。为了能够创建出有效的数据透视表,那么需要确保数据源符合一些默认的原则。这些原则通常指的是数据源的结构。
在用于创建数据透视表的数据源中,首先应该确保数据源中的每列数据的第一行包含该列的标题。只有这样的结构,才能在创建数据透视表后正确显示出分类明确的标题,从而便于后期的排序和筛选等操作。例如,如图2-1所示的数据源第一行中不包含列标题,因此这类形式的数据源是无法正确创建出数据透视表的。
如图2-2所示的是通过图2-1中的数据源创建的数据透视表。可以在右侧的【数据透视表字段列表】窗格中发现,每个分类字段使用的是数据源中每一列的第一个数据,无法代表每一列数据的分类含义,这将给数据透视表的进一步分析工作带来很大麻烦。因此,我们一定要确保数据源的第一行包含列标题。
图2-1 缺少列标题的数据源
同类字段是指在数据源中不同列中包含了类型相同的内容。例如,在如图2-3所示的数据源中,B列~G列代表了一年中的6个月份,这些具体的月同属于月份类别。这就是数据源中包含多个同类字段的一个典型实例。这种布局结构的数据源是不规范的,唯一做法就是将这些具体的月重新排列而同属于一个名为“月份”的列中,并将每个月对应的销售数据也重组为一列。具体方法请参考本章2.2.1节。
提示:如何判断数据源中哪些字段是同类字段,这里提供一个判定依据:看上去既可作为标题,又可作为具体数据的内容,在数据源中确保这类内容不应该分布在多个列中。
图2-2 没有列标题的数据源与创建后数据透视表
图2-3 包含同类字段的数据源
数据源中不能包含空行和空列,如果在数据源中存在空行或者空列,那么在创建数据透视表时千万要小心,因为默认情况下将无法使用完整的数据区域来创建数据透视表。例如,在如图2-4所示中包含数据区域A1:I12,但是第8行是一个空行,如果单击空行上方的数据区域中的任意一个单元格,那么在创建数据透视表时,系统只能选择空行以上的数据区域,而空行下方的数据区域将被遗弃。这样将在创建的数据透视表中无法包含完整的数据。
与空行的情况类似,如果数据源中包含空列,那么将会导致同样的问题。如图2-5所示为在创建数据透视表时系统自动选中了有空列隔开的左侧数据区域,而右侧数据区域未包含进来。
图2-4 在创建数据透视表时无法自动使用包含空行的数据源区域的完整部分
图2-5 在创建数据透视表时无法自动使用包含空列的数据源区域的完整部分
解决以上问题的方法之一就是在选择数据源时使用手工选择,即单击【创建数据透视表】对话框中的 按钮,这样可以人为决定使用哪些数据来创建数据透视表。而另一种更好的解决方案是在创建数据透视表之前将数据源中的所有空行和空列彻底删除,具体方法请参考本章2.2.2节。
除了空行和空列的问题以外,还应该注意数据源中是否存在空单元格。这里需要说明的是,即使数据源中包含空单元格,也仍然可以创建数据透视表,而且并不影响数据源区域的完整性。但是由于空单元格的存在,在对数据透视表数据进行后期处理时很可能会出现一些问题。所以对于数据源存在空单元格的情况,则应尽量使用同类型的默认值来填充空单元格。例如,对于数值类型的单元格来说,可以将空单元格以数字0进行填充。关于批量填充单元格的方法请参考本章2.2.3节。
上一节介绍了用于创建数据透视表的数据源的设计原则,在遵循这些原则的情况下所创建出的数据透视表通常都具有良好的结构,便于进一步数据分析和处理。本节将针对这些设计原则来详细介绍如何快速整理不规范数据,使这些数据的结构完全符合设计原则。
如果在一个数据源的多个列中包含了多个同类型的字段,那么就需要将这些同类字段重组在一个它们的父类别下,然后重新调整与这些字段相关的数据。正如在如图2-6所示中看到的,上图中是一个不规范的数据源,其中月份名称分布在多个列的标题中,而下图是经过整理后的数据源,新增了一个名为“月份”的列,然后将所有月份名称存放于该列中,并重新调整与每个月份有关的数据,把这些数据也单独划分到一个新列中。
如果数据源中包含了多个空行或空列,那么在创建数据透视表之前,需要将这些空行和空列删除,否则将可能在创建的数据透视表中无法包含完整的数据。删除空行的方法有很多种,有的操作起来很简单,但是效率可能不高;而有的方法效率很高,但是对于初学者来说可能有点难度。当然,最直接的方法是按住Ctrl键,然后依次选择要删除的空白行,再右击选中行,并在弹出的菜单中选择【删除】命令。这里介绍另外两种删除空行的方法:手工排序法和VBA排序法。
图2-6 整理后的表格
手工排序法
手工排序法删除空行的原理:首先新增一个辅助列,然后在该列中输入自然数序号,即为数据源的每一行进行自然数编号。然后以数据源任意一列进行升序或降序排列,删除排序后只包含自然数序号而不包含实际内容的行,最后对辅助列进行升序排列,使数据源中的数据恢复为最初的顺序。具体操作如下所示。
1 右击数据源中A列的列标,在弹出菜单中选择【插入】命令。
2 在A列左侧插入一个空列,然后在A2:A12中输入自然数1~11,如图2-7所示。
图2-7 在新增的辅助列中输入自然数编号
3 单击B:J列中的任意一个包含数据的单元格(例如“姓名”列中的单元格),然后单击功能区中的【开始】⇨【编辑】⇨【排序和筛选】按钮,在弹出菜单中选择【升序】命令,得到如图2-8所示的结果。
图2-8 对数据源排序
4 删除数据源底部多出的A列单元格中的数字。然后单击A列中包含数据的任意一个单元格,对整个区域进行升序排列,结果如图2-9所示。
图2-9 重新对数据排序使其恢复原状
VBA排序法
这里只给出删除空行的VBA代码,关于在Excel使用VBA的方法请参考本书第13章,其中的内容足以使没有任何VBA经验的用户理解。删除数据源中空行的VBA代码如下:
上面的代码限于数据源第一行位于工作表中第一行的情况下,如果数据源的第一行是从其他行开始的,那么运行上面的代码将无法得到正确结果。此时需要使用下面的代码(下面的代码也同样适用于数据源第一行位于工作表第一行的情况,代码具有通用性):
还可以使用下面的代码删除空行。与上面代码的唯一区别是确定工作表中数据区域最后一行的方法,即lLastRow变量。
删除空列的方法与删除空行的方法是类似的,这里直接给出删除空列的VBA代码,具体如下:
如果在数据源中包含了多个空单元格,那么为了避免在使用数据透视表时发生意想不到的问题,应该将这些空值替换为0。具体操作如下所示。
1 单击数据源内的任意一个单元格,然后按Ctrl+A组合键选择当前数据源区域。
2 按F5键打开【定位】对话框,单击【定位条件】按钮打开【定位条件】对话框,选中【空值】单选框,如图2-10所示。
图2-10 在【定位条件】对话框中选中【空值】单选框
3 单击【确定】按钮,自动选中数据源区域中的所有空单元格,如图2-11所示。
图2-11 选择数据区域中的所有空单元格
4 保持选区不变,然后输入数字0,再按Ctrl+Enter组合键将0自动填充到所有选中的单元格中,如图2-12所示。
技巧:对于类似如图2-13所示的内容,如果希望在空单元格中输入与它上面相同的内容,那么首先使用【定位条件】对话框快速选择多个空单元格,然后输入一个等号(=),再按一次↑键,最后按Ctrl+Enter组合键完成输入。
图2-12 自动以0填充空单元格
图2-13 自动填充同列中的相同内容
如果某些包含数字的单元格中存在一些看不见的空格,那么在执行某些操作时,Excel会将这类单元格作为文本看待,而不是数字。因此在一些计算中就会发生错误。为此,可以使用Trim函数清除这些看不见的隐藏在单元格中的空格。
一般情况下,创建数据透视表所使用的数据源通常存储于Excel工作表中,但是有些时候数据源存在于外部数据库中,例如像Access、SQL Server等专门的数据库,这时就需要使用这些数据库中的数据来创建数据透视表。另外,Excel提供了一种称为“表格”的工具,如果已经将普通数据区域转换为表格,那么可以直接在表格上创建数据透视表。本节将简要介绍可用于创建数据透视表的各种数据来源。
Excel工作表中的标准数据,就是指在本章2.1节中讨论的符合数据透视表数据源设计原则的普通数据,由于在2.1节中已经对如何构建标准数据进行了详细讲解,这里就不做过多介绍了。
在创建数据透视表之前,如果已经将数据区域转换为了Excel表格,那么就可以直接由表格创建数据透视表。单击数据区域中的任意一个单元格,然后单击功能区中的【插入】⇨【表格】⇨【表格】按钮,打开如图2-14所示的【创建表】对话框,其中模板选中了当前数据区域,可以单击【创建表】对话框中的折叠按钮 ,重新选择数据区域。单击【确定】按钮,即可将当前数据区域转换为Excel表格,如图2-15所示。单击表格内的任意一个单元格,激活功能区中的【设计】选项卡,通过单击【工具】组中的【通过数据透视表汇总】按钮,将以表格中的数据创建数据透视表。
图2-14【创建表】对话框
图2-15 将普通数据转换为Excel表格
除了使用 Excel 工作簿内的不同数据源形式来创建数据透视表外,还可以导入Excel 外部的多种类型的数据来创建数据透视表。在功能区中的【数据】⇨【获取外部数据】组中可以看到可以导入到Excel中的所有外部数据类型,如图2-16所示。
根据数据类型的不同,主要包括以下几类。
· Access数据库中的数据:由Access应用程序创建的数据库文件,在Excel中可以导入该类型的数据,并选择其中的表数据来创建数据透视表。
· SQL Server数据库中的数据:如果在计算机中安装了SQL Server程序,并在其中创建了数据库,那么可以通过Excel来导入SQL Server数据库中的数据,直接将其创建为数据透视表。
· OLAP多维数据集:使用Excel 2010中提供的【来自Analysis Services】连接(如图2-17所示),可以连接到联机分析处理(OLAP,OnLine Analytical Processing)数据库,然后将其中的多维数据集导入到Excel工作表中,并可选择创建为数据透视表或数据透视图。
图2-16 可导入到Excel中的外部数据
图2-17【来自Analysis Services】连接
当然,在Excel 2010中还可以导入其他的数据类型,如文本文件和网站中的数据等,但是最常导入的外部数据主要是前面介绍的三种方法。关于如何通过导入外部数据来创建数据透视表的内容,请参考本书第12章。
如果没有特殊的要求,那么在Excel 2010中创建数据透视表将变得非常简单。几乎只用一步即可创建出一个基本数据透视表。而对于数据透视表中字段的布局,也可以完全让Excel代劳,这一切使创建数据透视表变得方便快捷。
在早期Excel版本如Excel 2003中,创建透视表的过程是通过一个向导完成的,这对于初学者来说,常常让人心生敬畏,感觉数据透视表的创建之路非常漫长。而在Excel 2007及Excel 2010中极大简化了创建数据透视表的过程,只需使用一个对话框并可在不做任何额外设置的情况下,直接单击【确定】按钮即可创建出一个数据透视表。具体操作如下所示。
1 单击数据源中的任意一个单元格,然后单击功能区中的【插入】⇨【表格】⇨【数据透视表】按钮。
注意:【数据透视表】按钮是一个组合按钮,它包含上下两部分。单击上半部分可直接启动相应命令,而单击下半部分中的箭头,则可以打开一个菜单,从中可以选择要执行的命令,如图2-18所示。
图2-18 数据透视表的按钮
2 打开的【创建数据透视表】对话框如图2-19所示,其中自动选中了光标所在的整个数据区域,并自动指定将数据透视表创建到新建的工作表中。
图2-19【创建数据透视表】对话框
3 单击【确定】按钮,即可在一个新建的工作表中生成一个基本的数据透视表,如图2-20所示。左侧为数据透视表区域,右侧自动显示【数据透视表字段列表】窗格,其中包含的字段就是数据源中各列的列标题。
默认情况下将创建一个空白的数据透视表,需要手动将字段添加到数据透视表中。如果不知道如何安排字段的位置,那么我们可以让Excel代劳。选中【数据透视表字段列表】窗格中的不同字段,可以看到左侧的数据透视表将自动更新以显示最新的数据,如图2-21所示。同时,还可以在【数据透视表字段列表】窗格下方的4个列表框中看到相应的字段被添加进来。这就是Excel对字段的自动识别功能。
图2-20 创建空白数据透视表
图2-21 让Excel自动布局数据透视表中的字段
提示:如果没有显示出【数据透视表字段列表】窗格,则可以单击数据透视表区域,此时便会自动打开【数据透视表字段列表】窗格。如果即便这样仍没有显示【数据透视表字段列表】窗格,那么先确保光标位于数据透视表区域内,然后单击功能区中的【选项】⇨【显示】⇨【字段列表】按钮以便打开【数据透视表字段列表】窗格。