如何在ARRAYFORMULA()中使用INDEX()? [英] How to use INDEX() inside ARRAYFORMULA()?

问题描述

我正在尝试在ARRAYFORMULA()中使用INDEX()公式.作为一个简单的(无意义的)示例，在A列中有4个元素，我希望在B1中输入的以下数组公式将在B列中显示A中的所有四个元素:

I am trying to use the INDEX() formula inside an ARRAYFORMULA(). As a simple (non-sense) example, with 4 elements in column A, I expected that the following array formula entered in B1 would display all four elements from A in column B:

=ARRAYFORMULA(INDEX($A$1:$A$4,ROW($A$1:$A$4)))

但是，这只会用A1中找到的值填充B1字段.

However, this only fills field B1 with a the value found in A1.

当我输入

=ARRAYFORMULA(ROW($A$1:$A$4))

B1中的

，那么我确实看到所有数字1至4都出现在B列中.为什么我的第一个数组公式不能像第二个数组公式那样扩展?

in B1, then I do see all numbers 1 to 4 appear in column B. Why does my first array formula not expand similar like the second one does?

推荐答案

如果将数组用作其参数之一，则INDEX函数不支持对数组进行迭代".据我所知，没有任何文献记录.它就是它的本质.因此，第二个参数将始终默认为数组的第一个元素，即ROW(A1).

The INDEX function is one that does not support "iteration" over an array if an array is used as one of its arguments. There is no documentation of this that I know of; it simply is what it is. So the second argument will always default to the first element of the array, which is ROW(A1).

一种笨拙的解决方法来实现您的要求，取决于源数据旁边的第二个相邻列*(尽管无关紧要的是第二列中的实际值)

One clumsy workaround to achieve what you require relies on a second adjacent column existing next to the source data* (although it is unimportant what values are actually in that second column):

=ArrayFormula(HLOOKUP(IF(ROW($A$1:$A$4);$A$1);$A$1:$B$4;ROW($A$1:$A$4);0))

或者实际上是这样的:

=ArrayFormula(HLOOKUP(IF({3;2;4;1};$A$1);$A$1:$B$4;{3;2;4;1};0))

编辑2015-06-09

*最新版的表格中不再需要此功能； HLOOKUP中的第二个参数可以只是$ A $ 1:$ A $ 4.

* This is no longer a requirement in the newest version of Sheets; the second argument in the HLOOKUP can just be $A$1:$A$4.

这篇关于如何在ARRAYFORMULA()中使用INDEX()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！