下载掌阅APP，畅读海量书库
立即打开
可能您曾经听到过身边的同事提起“数据透视表”这个词语，或者也可能您从来没听说过它，可能在您的心里一直对数据透视表是什么东西感到迷惑。无论如何都不重要，因为本章将彻底为您揭开数据透视表的神秘面纱，让您清楚了解数据透视表到底是什么、能够做什么、什么时候该使用它。阅读完本章后，以上问题都将迎刃而解。
本章要点
❖数据透视表的4个核心问题
❖数据透视表的组成结构
❖在不同的Excel版本下使用数据透视表
❖数据透视表缓存
为什么本书首先列出了一些与数据透视表有关的问题呢？因为这些问题可能是您曾经遇到过的，也可能是您以后会遇到的。而透过这些问题，您可能会发现数据透视表在处理这些问题上是具有一定价值的。然而，在您阅读完本书后将可以非常轻松地回答本节中的所有问题。
通常出现这类问题的原因是在Excel 2010中打开了使用Excel早期版本创建的数据透视表，此时将自动进入兼容模式并且禁用了Excel 2010对数据透视表添加的所有新功能，这样才能保证与Excel早期版本的兼容性。相关内容请参考本章1.5节。
默认情况下，Excel会对数据透视表中的数值进行求和计算，但是假如数据区域中包含一个空白单元格，那么Excel将认为待统计区域中不完全都是数值，因此采用计数而非求和进行统计。因此如果发现数据的统计结果比预计的结果要小很多，那么就要提高警惕了，因为Excel可能使用了计数代替了预期的求和。
遇到这种情况最可能的原因是待分组的日期列虽然从外观上看是日期格式，但是其本质是文本而不是日期格式。因此，无法按照日期来对这样的数据列进行分组。有两种解决方法，一种是手工分组；另一种是先将此列数据转换为日期格式，然后再对这列数据进行分组。
如果出现这种情况，首先需要确保工作表没有处于保护状态。其次，可能是因为使用推迟布局更新的功能。因此当开启该功能时，数据透视表的大部分选项都将暂时无法使用，例如排序、筛选、分组和计算等功能，直到关闭该功能为止。关于推迟布局更新功能的详细介绍请参考本书第3章。
可以在每次打开工作簿时自动刷新数据透视表中的数据。只要在【数据透视表选项】对话框的【数据】选项卡中选中【打开文件时刷新数据】复选框即可。另外，也可以使用其他多种方法来自动刷新数据透视表中的数据，具体内容请参考本书第4章。
如果一个工作簿中包含多个数据透视表并希望自动刷新它们，一种方法是分别对每个数据透视表设置打开时刷新功能；另一种方法就是编写VBA代码来自动刷新数据透视表，这种方法更灵活，因为可以在任何希望的时候对数据透视表进行刷新。关于刷新数据透视表的详细内容请参考本书第4章。
可以将数据透视表中的计算错误隐藏起来。只需打开【数据透视表选项】对话框，在【显示】选项卡中选中【对于错误值，显示】复选框，然后在右侧的文本框中输入一个想要显示的内容，用此内容代替错误值的显示，具体方法请参考本书第5章。
如果通过布局字段而得到一个想要的报表后，可以将此报表中的数据保存下来，以便进行其他分析和处理，那么此时可以将数据透视表转换为静态数据。操作其实很简单，只需对数据透视表数据进行复制和选择性粘贴操作即可，具体方法请参考本书第8章。
本节将要探讨的4个问题，可能是每一个初次使用数据透视表的用户最关心的问题。因为这4个问题指明了使用数据透视表的原因、时机和方法。
数据透视表可能是Excel中最为强大的工具，然而到目前为止，仍有很多人并不了解它。数据透视表是一个数据分析利器，可以快速实现对复杂数据的分析任务，从而创建具有不同意义的业务报表。数据透视表具有非常强大的交互性，用户只需单击几下鼠标即可在几秒钟快速创建出一份非常专业且具有重要意义的数据报表，甚至根本不需要使用公式。然而，更为神奇的是，在创建一份报表后，用户只需通过拖动字段来随意改变报表的布局结构，像搭积木一样在几秒钟内创建多份具有不同意义的报表。
很多用户在从复杂数据提取有意义的业务信息时仍然在使用公式，甚至是通过手工的复制/粘贴来得到最终的报表。但其实数据透视表是最好的工具，一旦掌握了数据透视表的基本使用方法，就会给自己的工作带来事半功倍的效果，也避免了出现很多不必要的错误。更好的是，对于没有太多函数使用经验的用户来说，使用数据透视表还可以避开公式函数的使用，从而只需单击鼠标即可轻松创建专业的报表。
也许您对自己的函数水平有十足的把握和信心，也可能由于您从未使用过数据透视表而对它不屑一顾。但是相信在您看过下面这个示例后，会对数据透视表的易用和强大的功能有一个非常深刻的了解和认识。如图1-1所示为一个公司部门-职位-员工的工资明细表，现在要罗列出这个公司各部门的名称，并统计出每个部门的工资总额是多少。
图1-1 要进行数据统计的原始表格
以上需求涉及两个任务，一个就是提取F列中的不重复值，另一个则是根据提取出来的结果进行条件求和。下面看一看使用公式函数法如何完成这些任务。
1 单击单元格J2，然后输入下面任意一个数组公式，按Ctrl+Shift+Enter组合键结束后，此时会得到公司部门的一个名称。
2 将单元格J2中的公式向下拖动填充，直到单元格显示＃N/A为止，此时将提取出公司各部门的名称，如图1-2所示。
3 提取出不重复的部门名称后，在单元格 K2 中输入下面的数组公式，按Ctrl+Shift+Enter组合键结束，此时将计算出第一个部门的工资总和。
图1-2 使用公式提取出公司各部门名称的唯一值列表
4 将单元格K2中的公式向下拖动填充，直到单元格显示＃N/A为止，此时将统计出公司各部门的工资总和，如图1-3所示。
通过使用上面介绍的公式函数法，虽然只需4步即可得到最终结果，但是其中的操作过程相对来说是比较复杂的。因为在提取各部门名称时，无论使用哪个公式，都要掌握多个函数的综合运用。例如，使用第一个公式需要掌握LOOKUP、MATCH和COUNTIF这几个函数的用法，而使用第二个公式也至少需要掌握LOOKUP和COUNTIF这两个函数的用法，而且还要了解数组公式。在提取出各部门名称后，还要使用一个公式来统计工资总和，这就还需要再掌握SUM和IF函数的用法。因此，要完成此项任务，用户至少要掌握4～5个函数的用法，以及数组公式的相关知识。
对于初级用户而言，这些函数和复杂的公式已经形成了很大的难度，而且在输入公式时可能还很容易出错。更可怕的是，公司领导要求重新组织数据并提交一份统计各部门不同职位的工资情况，这就需要重新设计公式。如果换一个角度考虑，使用数据透视表来完成前面的示例，那么情况将截然不同。具体操作如下。
1 单击数据区域中的任意一个单元格，然后单击功能区中的【插入】⇨【表格】⇨【数据透视表】命令，打开【创建数据透视表】对话框，如图1-4所示。
图1-3 统计公司各部门工资总和
图1-4 创建数据透视表前的基本设置
2 无须对选项进行修改，直接单击【确定】按钮，将在一个新工作表中创建一个空白的数据透视表。在右侧的【数据透视表字段列表】窗格中，将【部门】字段拖动到下方的【行标签】列表框中，将【工资】字段拖动到下方的【数值】列表框中，这样就立刻统计出了各部门的工资总和，如图1-5所示。
由此可以看出，对于本例而言，使用数据透视表只需要两步就完成本需复杂公式和函数4步才能完成的任务。更重要的是，使用数据透视表不需要复杂的函数和公式就可以对数据进行统计和分析，这样即使对于初级用户而言，在使用上基本没有难度限制，只需熟悉数据透视表的基本操作即可。另外，即使后期改变了对数据分析的需求，只需通过拖动字段到不同的列表框中，即可制作出新的报表。
图1-5 使用数据透视表统计各部门的工资总和
对于现代社会时刻变化的信息、庞大的数据业务、不断变化的数据要求，如果全部采用手工的方式来处理，不但容易出错，而且将无任何效率可言。那么究竟在哪些情况下应该使用数据透视表这一分析工具呢？下面列举了一些非常适合使用数据透视表来分析数据的情况：
· 包含大量错综复杂数据的表格，希望快速整理出一份具有实际意义的报表。
· 希望找出同类数据在不同时期的某种特定关系。
· 希望对数据进行合理有效的分组。
· 需要经常查询分析数据的变化趋势。
· 数据源经常变化，然而又需要经常分析和处理最新的数据源。
如何使用数据透视表正是本书的主题，在后续的章节中，您将会详细了解到使用数据透视表的方法、技巧，以及在实际工作中的具体应用。
在学习后续大量关于数据透视表的操作和技巧之前，有必要先来了解一下数据透视表的结构，希望您能和我达成共识，这样便于交流，也便于理解后面内容中的一些术语。对于任何一个数据透视表来说，可以将其整体结构划分为4大区域。
行区域位于数据透视表的左侧，如图1-6所示深灰色底纹的区域就是行区域。每个字段中的每一项显示在行区域的每一行中。例如，名为“部门”的字段中的各部门名称显示在第6、11、16等行的A列中，而名为“职位”的字段中的每一项则分别显示在各部门的下方。这种具有层次结构的显示方式是由各字段的位置决定的。通常在行区域中放置一些可用于进行分组或分类的内容，例如，部门、职业、产地、日期等。
图1-6 深灰色底纹所在区域为行区域
列区域位于数据透视表的顶部，由数据透视表各列顶端的标题组成。每个字段中的每一项显示在列区域的每一列中。例如，在如图1-7所示的数据透视表中，【性别】字段中的“男”和“女”显示在B列和C列中。通常在列区域中放置一些可以随时间变化的内容，例如，年份、月份等，这样可以很明显地看出数据随时间变化的趋势。
图1-7 深灰色底纹所在区域为列区域
在数据透视表中，包含数值的大面积区域就是值区域。例如，在如图1-8所示的数据透视表中，值区域中的数据表示的是每个部门不同职位男女员工的工资情况。值区域中的数据是对数据透视表中行字段和列字段数据的计算和汇总，该区域中的数据一般都是可以进行运算的。默认情况下，Excel对数值区域中的数值型数据进行求和，对文本型数据进行计数。
报表筛选区域位于数据透视表的最上方，由一个或多个下拉列表组成，通过选择下拉列表中的选项，可以一次性对整个数据透视表中的数据进行筛选。例如，在如图1-9所示的数据透视表中，通过将【学历】设置为【大本】得到的数据，它表示各部门学历为“大本”的员工的工资情况。通常在报表筛选区域中放置一些要重点统计的内容类型。
图1-8 深灰色底纹所在区域为值区域
图1-9 深灰色底纹所在区域为报表筛选区域
由于数据透视表的结构比较复杂，因此对数据透视表中各元素有一个统一的描述将变得非常重要，尤其对于初学者来说，前后不统一的术语将会使读者在学习时更加混乱。因此，本节将对数据透视表中的常用术语进行详细解释，以便做到全书统一，也可便于读者在不明白或忘记某个术语时随时到此处来查看。除了上一节介绍的数据透视表中的4个区域外，还包括以下一些常用术语。为了便于演示，本节中的数据透视表使用了表格形式的布局，通过功能区中的【设计】⇨【布局】⇨【报表布局】⇨【以表格形式显示】即可使用表格形式布局。
用于创建数据透视表的数据来源，可以是单元格区域、定义的名称、另一个数据透视表数据或其他外部数据来源，例如可以是文本文件、Access数据库或SQL Server数据库等。
数据透视表中的字段就是数据源中各列顶部的标题，每一个字段代表了一类数据。例如，在如图1-10所示的数据透视表中，包含深灰色底纹的单元格都是字段，即“学历”、“籍贯”、“求和项：工资”、“部门”、“职位”和“性别”都是字段。
图1-10 数据透视表中的字段
根据字段位于的区域不同，可以将字段分为报表筛选字段、行字段、列字段、值字段。
· 报表筛选字段：位于数据透视表报表筛选区域中的字段，可以对整个数据透视表进行筛选，从而显示单个项或所有项的数据。
· 行字段：位于数据透视表行区域中的字段。当一个数据透视表中包含多个行字段时，根据行字段的层次关系从左到右依次展开，例如图1-10中的“部门”和“职位”行字段。为了便于识别不同层次上的行字段，可以将靠近值区域的字段称为内部行字段，而远离值区域的字段称为外部行字段。
· 列字段：位于数据透视表列区域中的字段。
· 值字段：位于最外部行字段上方的字段。值字段中的项包含了可用于计算的数据。如果数据是数字，那么 Excel 将自动对其求和；如果数据是文本，Excel则对其进行计数。
项是每个字段中包含的数据，表示数据源中字段的唯一条目。例如，在如图1-11所示的数据透视表中，包含深灰色底纹的单元格都是项，其中：
· 两个“（全部）”分别属于“学历”和“籍贯”字段中的项。
·“财务部”和“工程部”属于“部门”字段中的项。
·“总经理”、“部门经理”、“高级职员”和“普通职员”属于“职位”字段中的项。
·“男”和“女”属于“性别”字段中的项。
图1-11 数据透视表中的项
您可能希望在Excel 2010中使用由早期Excel版本创建的数据透视表，或者可能希望将早期版本的数据透视表升级到Excel 2010中。本节将介绍在不同Excel版本中使用数据透视表的方法。
如果您在不同Excel版本中使用数据透视表，那么需要特别注意不同Excel版本之间在数据透视表功能上的差别，以便确保数据透视表能够正常使用。表1-1列出了不同Excel版本之间在数据透视表方面的差别。
表1-1 不同Excel版本在数据透视表方面的差别
由于不同Excel版本之间对数据透视表各项功能的支持并不完全相同，因此在使用Excel 2010版本创建数据透视表后，想要将这些数据透视表用于Excel早期版本，很可能会引发兼容性问题。不过幸运的是，可以通过两种方法来避免此类问题的发生。
Excel兼容模式
如果在Excel 2010中打开Excel 2003以及更早Excel版本所创建的工作簿时，将自动进入兼容模式，并且在Excel标题栏中显示“兼容模式”字样，如图1-12所示。在兼容模式下，即使当前编辑环境是Excel 2010，但Excel中可用的功能将自动降为适合于Excel 2003及其更早Excel版本。换句话说，在兼容模式下，不能使用Excel 2010中提供的任何新功能。
图1-12 Excel兼容模式
兼容性检查
Excel 2010包含了一项兼容性检查的功能，使用该功能可以检查当前工作簿中已经使用的功能是否在Excel 2003或更早的Excel版本中受到限制。要检查工作簿的兼容性，可以单击【文件】按钮并选择【信息】命令，在展开的面板中单击【检查问题】按钮并选择【检查兼容性】命令，打开如图1-13所示的对话框，其中自动列出了当前工作簿中与早期版本不兼容的功能，可以根据实际情况进行处理。
图1-13 兼容性检查
如果想在Excel早期版本中创建的数据透视表使用Excel 2010中提供的新功能，那么就需要将数据透视表升级到Excel 2010版本。升级的方法很简单，只需升级包含数据透视表的工作簿即可。在Excel 2010中打开工作簿，然后单击【文件】按钮并选择【信息】命令，在展开的面板中单击【转换】按钮，如图1-14所示。
图1-14 将早期Excel工作簿转换为Excel 2010版本
本节将介绍数据透视表的幕后，即数据透视表的缓存。另外，还将介绍如何使用同一个数据透视表缓存来创建多个不同结构的数据透视表，同时也分析了共享数据透视表缓存的一些隐患。
在Excel 2003中每创建一个数据透视表都会占用一定的磁盘空间和内存空间，为什么会出现这种情况呢？这是因为在创建数据透视表时，Excel会记录下数据源的整个范围并存入一个被称为数据透视表缓存的地方。以后对数据透视表的各种操作，其实都是在与数据透视表缓存进行交互，而与用于创建数据透视表的数据源并不连接。在Excel 2003中，只要创建一个新的数据透视表，就会新建一个与该数据透视表对应的数据透视表缓存。也正因为如此，包含数据透视表的工作簿的体积也会不断增加，而其内存用量也会逐步递增。
但是在Excel 2007和Excel 2010中对这一问题进行了改善。因为在Excel 2007和Excel 2010中只要创建的数据透视表所使用的数据源都来自于同一个数据区域，那么它们将共同使用同一个数据透视表缓存，而不会增加额外的内存用量。
提示：本书第4章将介绍在不同情况下，使用不同方式更新数据透视表缓存的方法。在本书第13章中将介绍使用VBA来创建数据透视表缓存的方法。
上一节曾经提到过，在Excel 2003中使用同一个数据源创建的每一个数据透视表都会使用一个独立的数据透视表缓存，而在Excel 2007和Excel 2010中，多个来自于相同数据源的数据透视表则会使用同一个数据透视表缓存。换句话说，Excel 2003在默认情况下多个数据透视表是不共享同一个数据透视表缓存的，而Excel 2007和Excel 2010在默认情况下多个数据透视表是共享同一个数据透视表缓存的。
当在Excel 2003中使用相同的数据源创建第2个或更多的数据透视表时，在数据透视表和数据透视图向导第2个步骤中单击【下一步】按钮后，会弹出如图1-15所示的对话框，由用户选择是否共享数据透视表缓存。单击【是】按钮将共享数据透视表缓存，单击【否】按钮则使用独立的数据透视表缓存。
图1-15 由用户选择是否共享数据透视表缓存
而在Excel 2007和Excel 2010中，由于创建数据透视表是一步到位的，因此并没有提供给用户选择是否共享数据透视表缓存的选项，而是默认共享数据透视表缓存。如果希望使用相同数据源的每一个数据透视表都使用相对独立的数据透视表缓存，那么只能使用其他方法。为此可以按照以下步骤来实现。
1 单击数据源中的任意一个单元格，然后按Alt+D组合键，在出现快捷键提示信息时按P键，打开【数据透视表和数据透视图向导】对话框，如图1-16所示。
图1-16【数据透视表和数据透视图向导】对话框
2 单击【下一步】按钮，进入向导第2步的设置界面，Excel自动填入数据源区域，如图1-17所示。
图1-17 选择数据源
3 单击【下一步】按钮，弹出对话框，通过单击【是】或【否】按钮来允许由用户指定是否共享数据透视表缓存。
如果不喜欢使用快捷键来启动【数据透视表和数据透视图向导】对话框，还可以将【数据透视表和数据透视图向导】按钮添加到快速访问工具栏中。具体操作如下。
1 右击功能区并选择【自定义快速访问工具栏】命令，打开【Excel选项】对话框并自动进入快速访问工具栏自定义界面。
2 在【从下列位置选择命令】下拉列表中选择【不在功能区中的命令】选项，然后在下方的列表框中找到并选择【数据透视表和数据透视图向导】命令，单击【添加】按钮，将其添加到右侧的列表框中，如图1-18所示。
图1-18 添加【数据透视表和数据透视图向导】命令
3 单击【确定】按钮，即可在快速访问工具栏中看到【数据透视表和数据透视图向导】按钮
，此后只要单击该按钮，即可打开【数据透视表和数据透视图向导】对话框。
共享数据透视表缓存的好处是显而易见的，因为它可以减少内存的额外开支。但它也存在一些明显的缺陷，具体介绍如下。
· 第一，由于多个数据透视表共享相同的数据透视表缓存，所以一旦刷新其中任何一个数据透视表时，也会同时刷新其他数据透视表。
· 第二，在一个数据透视表中添加计算字段或计算项后，在其他数据透视表中也会自动出现该计算字段或计算项。
· 第三，在一个数据透视表中对某些字段进行组合后，组合后的结果也会作用于其他数据透视表的相同字段上。
因此，可能需要根据实际情况选择是否使用共享的数据透视表缓存。