是否可以将数组作为 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屋！