Excel公式技巧03： INDEX函數(shù)，給公式提供數(shù)組

2020.02.09

關(guān)注

excelperfect

INDEX函數(shù)雖然可以生成數(shù)組，但如果不用數(shù)組公式，似乎只能返回其生成的數(shù)組中的第一個(gè)元素。然而，可以使用INDEX函數(shù)來給公式提供數(shù)組。例如：

=SUM(INDEX(A1:A10,N(IF(1,{1,4,8}))))

將計(jì)算單元格A1、A4和A8的和。

下面的公式也可以得到相同的結(jié)果：

=SUM(INDEX(A1:A10,N(INDEX({1,4,8},,))))

可以將其擴(kuò)展到二維單元格區(qū)域：

=SUM(INDEX(A1:C10,N(IF(1,{1,4,8})),N(IF(1,{1,2,3}))))

計(jì)算單元格A1、B4和C8的和，即：

=SUM(A1, B4, C8)

此外，可以采取轉(zhuǎn)置其中一個(gè)數(shù)組常量的標(biāo)準(zhǔn)技術(shù)來生成更多不同的結(jié)果：

=SUM(INDEX(A1:C10,N(IF(1,{1,4,8})),N(IF(1,{1;2}))))

得到的結(jié)果與下面的公式相同：

=SUM(A1, A4, A8, B1, B4, B8)

甚至生成我們通常可能會(huì)采用OFFSET函數(shù)來生成的數(shù)組：

=SUM(INDEX(A1:C10,N(IF(1,{1,2,3})),N(IF(1,{1;2;2;3}))))

得到的結(jié)果與下面的公式相同：

=SUM(SUBTOTAL(9,OFFSET(A1,,{0,1},3,2)))

實(shí)際上等價(jià)于：

=SUM(A1:B3) + SUM(B1:C3)

當(dāng)然，上述內(nèi)容只是舉例，實(shí)際上可以通過很多方法來實(shí)現(xiàn)上述結(jié)果。最關(guān)鍵的是，通過這些例子發(fā)現(xiàn)了有用的技術(shù)，即可以使用INDEX函數(shù)生成多個(gè)返回值。不像OFFSET函數(shù)，其第一個(gè)參數(shù)必須是對(duì)工作表中實(shí)際單元格區(qū)域的引用，INDEX函數(shù)可以接受和處理其引用的數(shù)組，該數(shù)組由公式中其他函數(shù)生成的值組成。

并且，這種強(qiáng)制返回多個(gè)值的技術(shù)的使用不限于INDEX函數(shù)。例如，試圖找到A1:A10中第一次出現(xiàn)“A”、“B”和“C”所對(duì)應(yīng)的相同行中B1:B10的最大值，如果使用公式：

=MAX(VLOOKUP({'A','B','C'},A1:B10,2,0))

不一定會(huì)得出正確的結(jié)果，原因是Excel會(huì)將該公式轉(zhuǎn)換為：

=MAX(VLOOKUP('A',A1:B10,2,0))

也就是說，傳遞給VLOOKUP函數(shù)的數(shù)組中的3個(gè)元素僅第1個(gè)元素被處理。

然而，使用上文所講解的強(qiáng)制生成多個(gè)值的技術(shù)，其公式為：

=MAX(VLOOKUP(T(IF(1,{'A','B','C'})),A1:B10,2,0))

可以得到正確的結(jié)果，如下圖1所示。