返回数组的非本机函数——如何显示输出

I apologise if this is a very simple question, but I am at a bit of a loss here.

如果这是一个非常简单的问题，我很抱歉，但我在这里有点不知所措。

A bespoke formula I want to use returns an array of values, as seen here:

我想使用的定制公式返回一个值数组，如下所示:

But I cannot find a way to present this output in a cell separated format, only the first cell (39478) is returned.

但是我无法找到一种以单元格分离格式表示输出的方法，只返回第一个单元格(39478)。

There is a note included in the documentation: Hint: This function is a multiple result function. You MUST set an array for the output.

文档中包含一个注释:提示:这个函数是一个多结果函数。您必须为输出设置一个数组。

Whilst I understand I am going to need an array to display multiple results, I cannot find the method of doing so. Any tips?

虽然我知道我需要一个数组来显示多个结果，但是我找不到这样做的方法。任何建议吗?

1 个解决方案

#1

If the bespoke formula wants to return an array of values, there are a couple of ways to get the results into multiple cells.

如果定制的公式想要返回一个值数组，有几种方法可以将结果转换为多个单元格。

Put the formula into a cell and hit Enter↵. Next, select that cell along with several cells below it. Tap F2 then hit Ctrl+Shift+Enter↵. The successive values should fill the cells selected until an error (no more returns) is reached.

把公式为细胞和回车↵。接下来，选择该单元格及其下面的几个单元格。利用F2然后点击Ctrl + Shift + Enter键↵。连续的值应该填充所选的单元格，直到到达错误(不再返回)为止。
Put that formula into a cell and hit Ctrl+Shift+Enter↵. The formula should be wrapped in braces (e.g. { and }). If the correct relative and absolute cell addresses were used (e.g. $**A$1 or $**A1, etc) then you should be able to fill, copy or drag down the formula into successive rows.

把这个公式放在一个单元格,点击Ctrl + Shift + Enter↵。公式应该用括号括起来(例如{和})。如果使用了正确的相对地址和绝对地址(例如$* A$1或$* A1等)，那么您应该能够将公式填充、复制或拖放到连续的行中。
Use an INDEX function to contain the array of returned values from the bespoke formula and peel off successive values using the row_num parameter.
=INDEX(<bespoke formula>, ROW(1:1))
Filled down.

使用索引函数从定制的公式中包含返回值的数组，并使用row_num参数去除连续的值。=指数( <定制公式> 行(1:1))了。

Sooner or later, you will run out of rows to fill. An IFERROR function used as a wrapper can help avoid he display of errors.

迟早你会用完所有的行来填满。作为包装器使用的IFERROR函数可以帮助避免显示错误。

If you want to put all of the values into a single cell, then a User defined Function (aka UDF) could concatenate the array into a single string. This last method is generally not recommended as it renders the values useless for anything other than display purposes.

如果您想将所有的值放在一个单元中，那么用户定义函数(UDF)可以将数组连接到一个字符串中。通常不推荐使用最后一种方法，因为它会使值除了显示目的之外没有任何用处。

_{Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.}

数组公式需要用Ctrl + Shift + Enter↵定稿。一旦正确地进入第一个单元格，就可以像其他公式一样填充或复制它们。

_{Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum.}

数组公式对计算周期进行对数运算，因此最好将引用的范围缩小到最小。

_{See Guidelines and examples of array formulas for more information.}

有关更多信息，请参见数组公式的指导方针和示例。

#1