如何在工作表函数中从数组中提取子数组？

Question

如何在工作表函数中从数组中提取子数组？

11

有没有一种方法可以从单元格工作表函数中获取比起始数组更小的Excel数组？

因此，如果我有：

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

我最终会得到：

{23, 34, 46, 16}

然后我可以使用其他函数来操作它。

结论：如果我需要进行大量这样的操作，我肯定会使用jtolle的UDF comb解决方案。虽然PPC使用的公式接近，但经过测试，我发现它在空槽中会产生错误，错过第一个值，并且有一种更简单的方法来获取行号，因此这是我的最终解决方案：

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

必须输入为数组公式（CTRL-SHIFT-ENTER）。如果要显示，则必须至少输入与结果集一样大的区域，以显示所有结果。

- Lance Roberts

2

如果没有UDF，我认为这是不可能的。如果有人能证明我错了，我会非常高兴。 - Excellll

好问题。我会向一些极限公式的专家请教，但我同意Excellll的看法。 - brettdj

@Excellll - 下面包含了一种可能的公式变体，也可以在像SUM()这样的函数内使用。 - lori_m

4个回答

5

这个网站上有一个答案：http://www.mrexcel.com/forum/showthread.php?t=112002。虽然没有太多解释。

假设您的数据在A列中有空单元格，您可以将其放在B列中；这将按相同顺序跳过空白检索数据。

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

以下是解释：

ROW()-ROW($B$1) 是一个小技巧，可以给你一个递增的数字（如在B1中为1，在B2中为2...）
IF (... , ROW($A$2:$A$6) ) 是这个技巧的主要部分：它构建了一个数组，其中包含IF条件为真时行号（请注意，IF没有“else”值）
SMALL(..) 将返回该数组的第X个最小值（在我们的情况下是第X个非空行的数量），其中X是当前单元格的行号（在B1中为1 ...）
INDEX 然后将行号转换为其值
请注意，INDEX 和 ROW 从实际表格上方的一行开始，始终具有偏移量 > 0（INDEX不喜欢零）

- PPC

ROW() - ROW($B$1) 可以写成 ROWS($B$1:B1)。同样的功能，不同的写法。 - PPC

不错！如果Excel能够内置某种过滤功能，这种公式上的花招就不再必要了... - jtolle

@jtolle：在某些情况下，您可以将自定义公式添加为宏。所谓“在某些情况下”，我是指“如果您的客户听到这个词不会惊慌失措的话”。 - PPC

@PPC，终于回到这个问题并根据你的答案给出了我的结论。 - Lance Roberts

2

以上回答都给出了脆弱的公式，不能移动到工作表的不同位置，并且对插入的行和列非常敏感。

这里提供一个不敏感的版本，可以移动到任何行：

=INDEX($A$10:$A$40, SMALL(IF(B$10:B$40,ROW(INDIRECT("1:30"))),ROW(INDIRECT("1:30"))))

在这个例子中，原始数组的值被放置在$A$10:$A$40中（如果原始数据是一行而不是一列，则可以使用数组公式{TRANSPOSE(originalArray)}）。B$10:B$40列包含布尔标志（TRUE或FALSE），用于确定是否应该在结果中保留此数组元素（TRUE）或不保留（FALSE）。您可以使用任何函数填充此列。要创建OP中提到的测试，<>""，B$10应该填写：=A10<>""（然后通过B$40复制）。A列具有绝对列引用，B列具有相对列引用，因此可以将公式复制到右侧的其他列中，从而允许您创建其他类型的属性和子数组，这些属性和子数组将由您放置在C和D等列中的布尔测试控制。

这个例子将处理最多30个元素的原始数组。对于更大的数组，请调整$A$10:$A$40和B$10:B$40范围（表示30行），并相应地调整两个"1:30"出现的位置。

- Shlomo Swidler

1

一种可能的工作表函数解决方案：

=INDEX(A1:A6,N(IF(1,MODE.MULT(IF(A1:A6<>"",ROW(1:6)*{1,1})))))

< p > MODE.MULT 函数返回一个缩小的索引数组，插入 N(IF(1,.)) 以便通过引用将数组传递给 INDEX 函数。

- lori_m

这看起来非常有趣。到目前为止，唯一的问题是即使将其包装在IFERROR语句中，就像我的解决方案的INDEX一样，我仍然会在空白处得到#N/A错误。我肯定希望能够得到一个关于这个构造如何工作的好解释，链接也可以。 - Lance Roberts

将多个单元格输入到数组中，可以尝试使用如下公式：IFERROR(IF(1,<formula>),"")。在谷歌搜索“Excel 'N(IF(1,'”会返回一些链接，但我还没有找到对这种确切行为的良好解释，翻译一些中文帖子也可能会给出额外的见解。 - lori_m

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jtolle · Accepted Answer

如果你只想获取数组的子集，并且你已经知道你想要的元素的位置，那么你可以使用带有一个数组作为索引参数的INDEX函数。如下所示：

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

返回的是{22,33,55}。但通常这并不太有用，因为你不知道它们的位置，而且我也不知道如何在没有用户定义函数（UDF）的情况下获取它们。

对于这种工作表内数组过滤，我所做的是编写一个具有以下形式的UDF：

'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

我只发布了伪代码，因为我的实际代码都是调用库函数，包括收集位置和从位置复制的操作。这样做可能会掩盖基本思想，而该思想非常简单。

您可以像这样调用这样的UDF：

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

或者

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

可以使用Excel的标准数组来生成"comb"。这是一种轻量级的、适用于Excel的方式，能够获得大部分其他编程系统中更标准的filter(list-to-filter, test-function)函数的优点。

我用"comb"这个名称，因为"filter"通常意味着"使用此函数进行过滤"，而在Excel中，在调用过滤函数之前必须应用测试函数。此外，计算一个"comb"作为中间结果，然后将其用于......嗯，梳理...多个列表也可能很有用。