精通Excel数组公式15：使用INDEX函数和OFFSET函数创建动态单元格区域（续）

excelperfect

导语：本文为《精通Excel数组公式14：使用INDEX函数和OFFSET函数创建动态单元格区域》的后半部分。

将动态单元格区域公式定义为名称

创建动态单元格区域的公式不能直接用于创建数据有效性下拉列表。然而，可以将其定义为名称，然后在创建数据有效性下拉列表时使用这个名称。

定义名称有很多好处，包括：

1.在有动态单元格区域公式时，使用定义名称是很有用的，因为定义名称可用于许多情形，诸如公式、图表、数据透视表、VBA、以及其他接受单元格区域或公式的地方。

2.如果对包含需要按Ctrl+Shift+Enter的公式创建定义名称，然后在单元格公式中使用该定义名称，那么不需要按Ctrl+Shift+Enter。

当数据不一致时的动态表公式

有时，在数据集中可能存在空行或者有一列或多列缺失数据，但仍然想要使用公式定义包括最后数据所在行的单元格区域，如下图6所示。

图6：部分数据缺失，需要公式来定义单元格区域A2:C6

可以使用数组公式：

=$A$2:INDEX($C$2:$C$7,MAX((ROW($A$2:$C$7)-ROW($A$2)+1)*($A$2:$C$7<>'')))

也可以使用数组公式：

=$A$2:INDEX($C$2:$C$7,AGGREGATE(14,4,(ROW($A$2:$C$7)-ROW($A$2)+1)*($A$2:$C$7<>''),1))

如果数据集中某些空单元格中存在空格，则可以在公式中加上TRIM函数：

=$A$2:INDEX($C$2:$C$7,MAX((ROW($A$2:$C$7)-ROW($A$2)+1)*(TRIM($A$2:$C$7<>''))))

使用OFFSET函数创建动态单元格区域

OFFSET函数的使用非常直接：告诉OFFSET函数动态单元格区域的起始位置，想要从该位置偏移多少个单元格（行数和列数），以及高和宽，那么OFFSET函数就定义了一个单元格区域。

如下图7所示，在列A中只输入数据且在最后一个数字之前没有空单元格且范围不超过单元格A10。公式中，动态单元格区域的起始位置为单元格A2，没有向下和向右偏移（参数rows和cols均为空），高度（参数height）为2，宽度（参数width）为1（公式中省略了该参数，取默认值）。因此，返回的单元格区为A2:A3。

图7：OFFSET函数定义了单元格区域A2:A3

使用动态单元格区域定义在表里的表：OFFSET或INDEX？

如下图8所示的数据集，在第一列是城市名，由于在每个城市中有多个代表，因此有些城市是重复的。现在，要根据单元格E2中的城市名，创建由代表姓名组成的动态单元格区域。例如，如果单元格E2中是“Seattle”，那么需要公式定义单元格区域B4:B6；如果单元格E2中是“SF”，那么需要公式定义单元格区域B2:B3。

图8：根据城市名获取相应的单元格区域

下面是可以实现上述目的的3个数组公式。

公式1：

=OFFSET($B$1,MATCH($E$2,$A$2:$A$6,0),,COUNTIF($A$2:$A$6,$E$2))

公式2：

=INDEX($B$2:$B$6,MATCH($E$2,$A$2:$A$6,0)):INDEX($B$2:$B$6,MATCH($E$2,$A$2:$A$6,0)+COUNTIF($A$2:$A$6,$E$2)-1)

公式3：

=INDEX($B$2:$B$6,MATCH($E$2,$A$2:$A$6,0)):INDEX($B$2:$B$6,MATCH(2,1/($A$2:$A$6=$E$2)))

使用OFFSET和INDEX函数创建动态单元格区域的比较

OFFSET是易失性函数，而INDEX不是。如果在解决方案面临的情形下，易失性不重要，那么是使用OFFSET还是INDEX看个人喜好。OFFSET函数使用定义起始位置的输入和单元格大小来创建动态单元格区域，而INDEX函数通过查找单元格引用或行列引用来创建动态单元格区域。

使用动态单元格区域定义的名称的图表

动态单元格区域公式的一个用处是创建图表。例如：

1.如果有一个可能添加或删除记录的数据集，那么当数据变化时图表会自动更新。

2.基于单元格中的条件，为不同的数据绘制图表。

使用动态单元格区域创建图表的一般步骤如下：

1.创建动态单元格区域公式。

2.使用动态单元格区域公式定义名称。

3.创建图表。

4.将定义的名称插入到图表中。

如下图9所示，当单元格F1中选择城市名时，会绘制该城市相应的代表及销售额。

图9：根据选择的数据来绘制图表

定义的名称如下：

名称：SalesDynamic

引用位置：

=OFFSET($C$1,MATCH($F$1,$A$2:$A$6,0),,COUNTIF($A$2:$A$6,$F$1))

名称：RepsDynamic

引用位置：

=INDEX($B$2:$B$6,MATCH($F$1,$A$2:$A$6,0)):INDEX($B$2:$B$6,MATCH($F$1,$A$2:$A$6,0)+COUNTIF($A$2:$A$6,$F$1)-1)

然后，选择数据区域，绘制图表。接着，在公式栏中，将代表的数据区域修改为上面定义的名称。

示例：总是获取一列中倒数5个数字

如下图10所示，在单元格区域A2:A10中包含一些数字和空单元格。现在，想要使用公式求倒数5个数据之和。（这里的倒数几个数据，是指从最后一个数据开始向前数）

图10：计算一列中倒数5个数字之和

很显然，最后一个数据是单元格A8中的1，倒数5个数据，如果包含空单元格，则为A4:A8；如果不包含空单元格，则为A3:A8。

在单元格C5中的公式：

=SUM(INDEX($A$2:$A$10,MATCH(9.99E+307,$A$2:$A$10)-$C$2+1):INDEX($A$2:$A$10,MATCH(9.99E+307,$A$2:$A$10)))

在单元格C7中的数组公式：

=SUM(INDEX($A$2:$A$10,LARGE(IF(ISNUMBER($A$2:$A$10),ROW($A$2:$A$10)-ROW($A$2)+1),$C$2)):INDEX($A$2:$A$10,MATCH(9.99E+307,$A$2:$A$10)))

注：本文为电子书《精通Excel数组公式（学习笔记版）》中的一部分内容节选。你可以到知识星球App的完美Excel社群下载这本电子书的完整中文版。