可以将数组作为INDIRECT（）的参数，所以INDIRECT（）返回数组吗？ [英] Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array?

问题描述

假设我有一个单元格向量 A1：A5 ，每个字符串都是另一个单元格的地址。

Suppose, that I have a vector of cells A1:A5, each with string that is an address of the other cell.

我想做的是使用一个函数，返回这些引用单元格的数组。看到这个例子 - 我想要公式返回15（它返回1）。

What I want to do, is to have a function, that returns an array of values from these referenced cells. See the example - I want the formula to return 15 (it returns 1).

在生产设置中，此向量要长得多（约2000行）。

In production setting, this vector is much longer (ca. 2000 rows).

可以解决问题的是具有阵列能力的INDIRECT（）。

What would solve the problem, is array-capable INDIRECT().

（我意识到，我可能会推动Excel超出限制）...

(I realize, that I might be pushing Excel beyond its the limits)...

我对此主题的进一步思考：

My further thoughts on this topic:

由于INDIRECT（）甚至可以从单个单元格输入生成数组，理论上的INDIRECTARR（）公式将要求在Excel中嵌套（即多维）数组计算支持，或者本质上限于表示一维范围的参数。

Because the INDIRECT() is capable of producing arrays even from a single-cell input, such theoretical "INDIRECTARR()" formula would either require nested (i.e. multidimensional) array computation support in Excel, or will be intrinsically limited to arguments that denote one-dimensional ranges.

推荐答案

最后我找到了解决方法。只要值在一张纸上，我们就可以利用这个事实：当INDEX的第二个和/或第三个参数是数组时，结果也是一个数组：

Finally I've found a way to solve it. As long as the values are on one sheet, we can take advantage of the fact, that when the second and/or the third argument to INDEX is an array, the result is an array as well:

这有点黑客，但它肯定是有效的，不需要任何VBA和插件。而且速度很快

It is a bit hackish, but it certainly works and doesn't need any VBA nor addons. And it is fast.

这篇关于可以将数组作为INDIRECT（）的参数，所以INDIRECT（）返回数组吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！