精通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.是否存在空单元格？
对这些问题的答案决定可能使⽤哪种公式。excel数组函数的实例
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中输⼊数据，因为公式会将其考虑为该列的最后⼀个单元格。
格。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。