#Excel函数#2.“威力强大”的Index函数( 二 ) _知识经验

如果需要返回国家度量列表的多个列，那么速度的优势是相当大的。假设GDP在数据的第2列，资本（Capital）在第11列。
如果在一个新工作表中，想为每个国家输出数据中的3列，人口（Population）、GDP和资本（Capital），那么最有效的方式是在一列中创建共同的索引，然后以数组形式输入INDEX公式到输出的3列中。
在本例中，列B是国家列表，列A将放置公共的索引。在A2中，输入：
=MATCH(B2,Country, )
然后，在列B中向下复制该公式至国家列表的最后一列。
接着，在列C、D和E中以数组形式输入公式到整个单元格区域，一直向下到国家列表的最后一列：
{=INDEX(d,A2:A1 ,{4,2,11})}
更清楚地说，这意味着如果在列B中有99个国家，那么将选择单元格区域C2:E1 ，然后在公式栏中输入上面的公式，接着按Ctrl+Shift+Enter组合键完成输入。
最终的结果是一个公式只执行一次，并且在模型中非常快速地留下很轻便的、非易失性的“脚印” 。
求和
结合SUMPRODUCT，INDEX的这种用途是一种极好的方式。例如：
=SUMPRODUCT( (Left(Country,1)=”U”)*Population )
返回以字母U开头的所有国家的总人口。
当命名公式后，下面的公式也很好：
=SUMPRODUCT( (Left(INDEX(d,,1),1)=”U”)*INDEX(d,,4) )
动态区域
可以更改命名公式d以便结果在动态区域而不是在固定区域。此时，INDEX是至高无上的。
动态区域通常与OFFSET或INDIRECT函数一起构造。
不巧的是，这两个函数都是易失性函数，这意味着每次工作表中有改变时，包含这些函数的公式都将重新计算。例如，在任意空单元格中添加值将导致所有易失性公式（以及任何依赖它们的其它公式）重新计算。取决于模型设计和大小，可能对模型的扩展性和响应能力产生较大的影响。
INDEX函数可以用于创建动态区域，不仅仅是非易失性，而且比OFFSET或INDIRECT更快。事实上，性能方面的改进是如此大，以致于INDEX是专业模型中所有动态区域的基础。
创建动态区域的方式与使用OFFSET或INDIRECT的方式不同。对于那些函数，在函数里创建动态区域。而使用INDEX，在区域操作符（冒号）的一侧（有时是两侧）产生动态区域。
例如，考虑下面普通的区域引用：
=A2:A1
引用是对国家列表中国家名称的硬引用。假设在列表中没有空，并且在A1中有列标题，可能是“Country”，下面的公式返回列表中最后一个国家的名字：
=INDEX(A:A,COUNTA(A:A))
这里，INDEX函数真正返回带有国家名的列A中最后一个单元格的引用，等于符号强制返回单元格的值。在我们举的例子中，INDEX公式真正地返回引用A1。
因此，下面的两个公式指向完全相同的区域：
=A2:A1
=A2:INDEX(A:A,COUNTA(A:A))
但是有实质上的不同。第一个公式是硬编码、静态的引用；第二个公式是非易失的、动态的区域，随着列表中国家数量的变化而扩展或收缩。注意，代替“指向”使用INDEX建立的动态区域的命名公式（Country）的值之前，需要使用绝对引用。如下所示：
Country: =$A$2:INDEX($A:$A,COUNTA($A:$A))
可以使用相同的技术创建二维动态区域，因此列数也是动态的：
d: =$A$2:INDEX($1:$65535,COUNTA($A:$A),COUNTA($1:$1))
这个动态方式使用d来定义，仍然可以执行上面展示的精彩的行和列引用，例如：
{=INDEX(d,$A2,{4,2,11})}
说明，当在三个引用操作符的任意一个的一侧或两侧使用函数时，在工作簿打开时总会重新计算结果公式。因此虽然INDEX是非易失性的，但是当用于动态区域时它变成半易失性的－但这比易失性更好，因此迄今为止该函数是最好的动态区域基础。

#Excel函数#2.“威力强大”的Index函数( 二 )

猜你喜欢