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

excelperfect

动态单元格区域是指当添加或删除源数据时，或者随着包含单元格区域的公式被向下复制时根据某条件更改，可以自动扩展或收缩的单元格区域，可以用于公式、图表、数据透视表和其他位置。

那么，如何创建动态单元格区域呢？可以使用INDEX函数或者OFFSET函数。许多人倾向于使用INDEX函数，因为OFFSET函数是一个易失性函数。

什么是易失性函数？

每当Excel重新计算电子表格时，无论其引用的单元格有无变化，易失性函数都会重新计算。许多操作都会触发重新计算，例如在单元格中输入数据、插入行等。这样，易失性函数会增加公式的计算时间。下面列出了一些触发重新计算的操作：

1.输入新的数据

2.删除/插入行/列

3.执行自动筛选

4.双击行列分隔线

5.重命名工作表

6.改变工作表的位置

下面列出了一些易失性函数：CELL函数，INDIRECT函数，INFO函数，NOW函数，OFFSET函数，RAND函数，TODAY函数。

INDEX：查找行或列的公式

创建动态单元格区域的最基本的公式类型是基于条件来查找整行或整列值，可以使用INDEX函数实现。

INDEX函数有3个参数：

=INDEX(array,row_num,column_num)

通常，给参数row_num指定行号，给参数column_num指定列号，INDEX函数执行双向查找返回行列号交叉处的值。如果要获取整列，那么只需要给INDEX函数指定代表列号的参数column_num的值，忽略参数row_num（为空）或者指定其值为0。通过指定参数row_num为空或0，告诉Excel返回所选列的所有行。

同理，想要获取整行，则需要指定参数row_num的值代表行号，将参数column_num指定为空或0。这告诉Excel需要返回所选行的所有列。

图1：查找并求2月份的数值之和

注意，图1所示的公式并不需要按Ctrl+Shift+Enter组合键，虽然INDEX函数返回的是一个单元格区域，其原因是没有执行直接数组操作。下面两种情况需要按Ctrl+Shift+Enter组合键：

1.如果放置需要Ctrl +Shift + Enter进入公式的直接数组操作，则需要使用Ctrl +Shift + Enter。

2.如果想要传递多个值到多个单元格，则必须使用Ctrl +Shift + Enter。

用于处理扩大和缩小单元格区域的动态单元格区域公式

在创建动态单元格区域公式之前，必须问清楚下列问题：

1.是垂直单元格区域（一列）吗？

2.是水平单元格区域（一行）吗？

3.是双向单元格区域（行列）吗？

4.是数字、文本，还是混合数据？

5.是否存在空单元格？

对这些问题的答案决定可能使用哪种公式。

MATCH：确定数据集中的最后一个相对位置

下图2展示了4列不同的数据类型：单元格区域A5:A10在最后一项前包含混合数据，其中没有空单元格；单元格区域A16:A21在最后一项前包含带有空单元格的混合数据；单元格区域C5:C10在最后一项前包含带有空单元格的数字数据；单元格区域C16:C21在最后一项前包含带有空单元格的文本数据。在所有这4种情形下，要使用公式创建在添加或减少数据时扩充或缩减的动态单元格区域，需要确定该列中最后一个相对位置。图2中展示了6种可能的公式。

图2：对于不同数据类型查找最后一行

在图2所示的公式[2]至[6]中，展示了一种近似查找值的技术：当要查找的值比单元格区域中的任何值都大且执行近似匹配（即MATCH函数的第3个参数为空）时，将总是获取列表中最后一个相对位置，即便存在空单元格。

INDEX和MATCH函数：获取单元格区域中的最后一项

下图3和图4展示了如何使用MATCH和INDEX函数在单元格区域中查找最后一项。

图3：当有4条记录时查找单元格区域中的最后一项

图4：当有6条记录时查找单元格区域中的最后一项

使用INDEX和MATCH函数创建可以扩展和缩小的动态单元格区域

如下图5所示，在单元格E2中是一个数据有效性下拉列表，其内容来源于单元格区域A2:A5，在单元格F2中的VLOOKUP公式从单元格区域A2:C5中查找并返回相应的数据。

图5：下拉列表和VLOOKUP公式

问题是，当在单元格区域A2:C5的下方添加更多的数据时，数据有效性下拉列表和VLOOKUP公式中的相应单元格区域都不会更新。当前，在“成本”列中的最后一项是单元格C5，如果添加新记录，在“成本”列中最新的最后一项应该是单元格C6，这意味着在VLOOKUP公式中的查找区域需要从$A$2:$C$5改变为$A$2:$C$6。注意到，这两个区域都开始于相同的单元格$A$2。我们现在的任务，就是找到一种方法，当添加或删除记录时，其最后一个单元格引用能够相应更新。此时，可以使用INDEX函数。

静态的单元格区域如下：

$A$2:$C$5

创建的动态单元格区域如下：

$A$2:INDEX($C$2:$C$8,MATCH(9.99E+307,$C$2:$C$8))

注意，由于INDEX函数位于一个起始单元格引用和冒号之后，因此不再获取该区域中的最后一项，而是获取该区域中最后一项的单元格地址（单元格引用）。

此时，你在图5的数据区域中添加或删除记录，创建的动态单元格区域会自动更新。

下面是创建动态单元格区域公式的关键点：

1.足够的行以容纳所有潜在数据。

（1）如果含有数字的数据集在列C中并决不会超过50条记录，可使用：

=$A$2:INDEX($C$2:$C$51,MATCH(9.99E+307,$C$2:$C$51))

（2）如果含有数字的数据集在列C中并决不会超过500条记录，可使用：

=$A$2:INDEX($C$2:$C$501,MATCH(9.99E+307,$C$2:$C$501))

（3）如果含有数字的数据集在列C中并且不确定有多少条记录，可使用：

=$A$2:INDEX($C:$C,MATCH(9.99E+307,$C:$C))

2.不要在公式使用的单元格区域的下方输入无关数据，因为会导致公式创建不正确的区域。例如，如果公式使用潜在单元格区域$C$2:$C$50，并且最后一个数据位于单元格C25，那么不要再在单元格C49中输入数据，因为公式会将其考虑为该列的最后一个单元格。