你如何提取从工作表函数的数组子数组？ [英] How do you extract a subarray from an array in a worksheet function?

问题描述

有没有得到一个数组在Excel中一个较小的尺寸比在小区工作表函数起始数组的一些方法？

Is there some way of getting an array in Excel of a smaller size than a starting array in a cell worksheet function?

所以，如果我有：

{23, "", 34, 46, "", "16"}

我想结束了：

{23, 34, 46, 16}

，我可以再与其他一些功能操作。

which I could then manipulate with some other function.

结论：如果我是做了很多的这些我肯定会使用jtolle的UDF梳理解决方案。该PPC使用的公式是接近，但潜水和测试，我发现它给出了空槽的错误，错过了第一个值，而且还有一个更简单的方式来获得行号，所以这里是我的最终解决方案：

Conclusion: If I was to do a lot of these I would definitely use jtolle's UDF comb solution. The formula that PPC uses is close, but diving in and testing, I found it gives errors in the empty slots, misses the first value, and there is an easier way to get the row numbers, so here is my final solution:

=IFERROR(INDEX($A$1:$A$6, SMALL(IF(($A$1:$A$6<>""),ROW($A$1:$A$6)),ROW(1:6))),"")

必须输入作为数组公式（CTRL-SHIFT-ENTER）。如果正在显示的话，就必须至少在一个区域中输入一样大的结果集中显示所有结果。

Which must be entered as an array formula (CTRL-SHIFT-ENTER). If being displayed then it must be entered in at least an area as big as the resultset to show all results.

推荐答案

如果你想要做的就是抓住一个数组的一个子集，的你已经知道你想要的元素的位置，你可以使用首页与index参数数组。这就是：

If all you want to do is grab a subset of an array, and you already know the positions of the elements you want, you can just use INDEX with an array for the index argument. That is:

=INDEX({11,22,33,44,55},{2,3,5})

收益 {22,33,55} 。但是，这通常不是非常有用，因为你不知道的位置，我不知道有什么办法让他们没有UDF。

returns {22,33,55}. But that's usually not very useful because you don't know the positions, and I don't know any way to get them without a UDF.

我已经做了这种在工作表中的数组过滤是写有以下形式的UDF：

What I have done for this kind of in-worksheet array filtration is to write a UDF with the following form:

'Filters an input sequence based on a second "comb" sequence.
'Non-False-equivalent, non-error values in the comb represent the positions of elements
'to be kept.
Public Function combSeq(seqToComb, seqOfCombValues)

    'various library calls to work with 1xn or nx1 arrays or ranges as well as 1-D arrays

    'iterate the "comb" and collect positions of keeper elements

    'create a new array of the right length and copy in the keeper elements

End Function

我只贴伪code，因为我的实际code是对库函数的调用，包括收集，位置和复制从位操作。这可能会掩盖的基本理念，这是pretty简单。

I only posted pseudocode because my actual code is all calls to library functions, including the collect-positions and copy-from-positions operations. It would probably obscure the basic idea, which is pretty simple.

您会称这样的UDF像这样：

You'd call such a UDF like so:

=combSeq({23, "", 34, 46, "", "16"}, {23, "", 34, 46, "", "16"} <> "")

或

=combSeq(Q1:Q42, SIN(Z1:Z42) > 0.5)

和使用Excel的普通阵列力学产生梳。它是一个轻量级的，Excel中友好的方​​式获得了大量的更标准的好处过滤器（表到过滤器，测试功能）功能，您可以在其他见编程系统。

and use Excel's normal array mechanics to generate the "comb". It's a lightweight, Excel-friendly way to get a lot of the benefits of the more standard filter(list-to-filter, test-function) function you might see in other programming systems.

我使用的名称梳子，因为过滤器通常是指具有这种功能过滤器，并与Excel你调用过滤功能之前应用的测试功能。此外，它可以是有用的计算一个梳作为一个中间结果，然后用它来......呃，梳子......多个列表。

I use the name "comb" because "filter" usually means "filter with this function", and with Excel you have to apply the test function before calling the filtration function. Also it can be useful to compute one "comb" as an intermediate result and then use it to...er, comb...multiple lists.

这篇关于你如何提取从工作表函数的数组子数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！