Excel数据透视表从新手到高手最新章节_宋翔著

3.2　创建动态的数据透视表

创建数据透视表之后，如果改变数据源的范围，为了在数据透视表中反映新增范围内的数据，用户无法通过刷新操作来完成，而是必须重新指定数据源的范围。如果想让Excel自动监测数据源范围的变化，并及时被数据透视表捕获，那么需要为数据源定义为一个名称，在名称中创建一个可以动态捕获数据源范围的公式。此外，还可以利用导入数据的方法来创建动态的数据透视表。本节将介绍创建动态数据透视表的方法，并介绍实现该功能所需掌握的相关技术。

3.2.1　使用名称

在Excel中可以为常量、单元格区域、公式等内容创建名称，然后使用名称代替这些内容，这样不但可以减少输入量，还能让公式更易理解，并减少错误的发生。在Excel定义名称时需要注意以下几点：

●　一个名称最多可以包含255个字符，英文字母不区分大小写。

●　名称的第一个字符必须是汉字、英文字母、下画线或反斜杠“\”，名称的其他部分可以是汉字、英文字母、数字、句点和下画线。

●　名称中不能包含空格。如果必须对名称中的字符进行分隔处理，可以使用下画线和句点作为分隔符。

●　名称不能与工作表中的单元格引用相同，不能将字母R、r、C、c定义为名称，因为R、C在R1C1引用方式中表示工作表的行和列。

在Excel中创建名称的方法有很多种，由于创建动态的数据透视表需要为公式创建名称，因此这里只介绍为公式创建名称的方法。在功能区的“公式”选项卡中单击“定义名称”按钮，如图3-23所示。

图3-23　单击“定义名称”按钮

打开“新建名称”对话框，在该对话框中创建名称并进行相关设置：

●　在“名称”文本框中输入名称，比如“销量”。

●　在“范围”下拉列表中选择名称的级别，选择“工作簿”将创建工作簿级名称，选择特定的工作表名将创建工作表级名称。

●　在“引用位置”文本框中输入要创建名称的公式。

●　在“备注”文本框中输入对名称的简要说明。

完成以上设置后，单击“确定”按钮，将为用户在“引用位置”文本框中输入的公式创建名称。

如图3-24所示，在“新建名称”对话框中创建了一个名为“动态数据源”的名称，在“引用位置”文本框中输入下面的公式，然后单击“确定”按钮创建该名称。

图3-24　为公式创建名称

提示： 在“引用位置”文本框中输入公式与在单元格中输入公式的方法类似，也包括“输入”“编辑”和“点”3个模式。按F2键可在“输入”和“编辑”模式之间切换。

3.2.2　COUNTA和OFFSET函数

如果要创建动态的数据透视表，首先需要将数据源定义为一个动态名称。这里所说的“动态”是指改变数据源的范围时，Excel可以自动捕获数据源的最新范围。创建这样的动态名称时，需要使用COUNTA和OFFSET两个函数。下面介绍这两个函数的功能和语法。

1．COUNTA函数

COUNTA函数用于计算参数中包含非空值的个数。

value1,value2表示要计算非空值个数的1～255个参数，可以是直接输入的数字、单元格引用或数组。

提示： 如果使用单元格引用或数组作为COUNTA函数的参数，COUNTA将统计除空白单元格以外的其他所有值，包括错误值和空文本（""）。

2．OFFSET函数

OFFSET函数用于以指定的引用为参照，通过给定偏移量得到新的引用。返回的引用可以是一个单元格、一个单元格区域，而且可以指定返回区域的大小。

第1参数为原始区域地址；第2参数为相对于偏移量参照系的左上角单元格向上（下）偏移的行数；第3参数为相对于偏移量参照系的左上角单元格向左（右）偏移的列数；第4参数为要返回的区域的行数；第5参数为要返回的区域的列数。

提示： 如果省略row和cols两个参数，那么将其当作0处理，即新基点与原始基点位于同一个位置，OFFSET函数不进行任何偏移。当省略row和cols参数时，需要保留它们的逗号分隔符，比如OFFSET(B2,,,3,4)。如果省略height或width参数，那么假设其高度或宽度与reference参数表示的区域相同，即新的区域与原区域大小相同。

COUNTA函数比较简单，但是OFFSET函数相对比较复杂，因此这里详细介绍一下OFFSET函数的用法。OFFSET函数的工作原理可以分解为以下两步：

（1）对原始基点（reference参数）进行偏移操作，偏移的方向和距离由OFFSET函数中的第2参数（rows参数）和第3参数（cols参数）指定。如果这两个参数是正数，则向下和向右偏移；如果是负数则向上和向左偏移。在第（1）步中，原始基点移动到了由rows和cols参数值确定的新位置。

（2）在确定了基点的新位置后，通过height和width参数的值来返回指定行数和列数的区域。

例如，公式OFFSET(B2,3,2,4,2)从单元格B2开始，将单元格B2向下偏移3行，向右偏移2列，原始基点移动到了单元格D5。然后以单元格D5为新基点，向下扩展4行，向右扩展2列，组成一个4行2列的区域。

3.2.3　创建动态的数据源和数据透视表

了解了COUNTA和OFFSET函数的用法后，即可使用这两个函数定义动态名称。如图3-25所示，为数据源创建动态名称的操作步骤如下：

（1）在功能区的“公式”选项卡中单击“定义名称”按钮，打开“新建名称”对话框，在“名称”文本框中输入一个名称（如输入Data），在“引用位置”文本框中输入下面的公式，如图3-26所示。

图3-25　要创建动态名称的数据源

图3-26　定义名称

公式解析： COUNTA($A:$A)统计A列中非空单元格的个数，即判断在添加或减少数据行后，区域内当前包含数据的总行数。公式COUNTA($1:$1)统计第一行中非空单元格的个数，即判断当添加或减少数据列后，区域内当前包含数据的总列数。

（2）单击“确定”按钮，创建名为Data的动态名称。

定义好动态名称后，可以对动态名称的功能进行测试。数据源中除去标题行之外，实际数据共有15行。假设在数据区域的底部添加一行新数据，然后在名称框中输入定义的名称Data并按Enter键。如果Excel能自动选中包括新添加的行在内的数据区域，就说明定义的动态名称正常工作。打开“编辑名称”对话框，单击“引用位置”文本框内部，数据区域四周会出现虚线，新增的数据也会位于虚线之内，如图3-27所示。

图3-27　测试动态名称是否正常工作

接下来可以使用上面定义的名称作为数据源来创建数据透视表。在功能区的“插入”选项卡中单击“数据透视表”按钮，打开“创建数据透视表”对话框，在“表／区域”文本框中输入前面定义的名称Data，如图3-28所示。单击“确定”按钮，将创建动态的数据透视表。

图3-28　将数据源指定为已创建的动态名称

3.2.4　刷新动态数据透视表

使用动态名称作为数据源创建数据透视表后，如果要改变数据源的范围，用户不再需要在功能区的“数据透视表工具|分析”选项卡中单击“更改数据源”按钮，来重新指定数据源的范围，因为数据透视表可以自动捕获数据源范围的最新变化，并将新增数据反映到数据透视表中。用户只需在功能区的“数据透视表工具|分析”选项卡中单击“刷新”按钮，来反映数据的最新修改，而不再需要担心数据源范围的变化。

关于刷新数据透视表的更多内容请参考第4章。