☰

简而言之

tag-icon • tag-icon microsoft-excel worksheet-function array range
简而言之

简而言之

我有四列数据，每列都是一个动态/溢出数组。我想将它们“堆叠”到另一列，以便生成一个由四个原始数据“拼接”而成的动态/溢出数组。

我想在不使用 VBA 的情况下完成此操作，并且如果四个初始数组中的任何一个的大小发生变化，则只需要对用于进行拼接的公式等进行最少的更改，理想情况下不需要进行任何更改。

TL;DR 详细信息

我已经有一个方法可以做到这一点，即创建一个“辅助”列，但这并不理想。它的工作原理如下（示例这里)：

  1. 首先，我为原始数组选择一个最大“维度”；即四个数组中最大的一个所需要的最大尺寸。
  2. 然后，我创建了四个原始数组的“手动”堆叠作为辅助列。但我这样做的目的是，它们现在占用的不是原始大小，而是我选择的最大尺寸。（因此，至少在我的典型用例中，四个原始数组的大小通常差别很大，辅助列中会有很多空白。）
  3. 最后，我使用 FILTER() 修剪间隙。这不仅可以修剪间隙，还可以将整个混乱的局面转换为动态/溢出数组形式的连续范围。这就是我想要的。

这是可行的，但它（至少）有两个明显的局限性：

  1. 需要硬编码最大维度。如果初始数组大小发生变化，它无法满足我的要求，即不必更改公式。是的，我使用最大维度可以让我免受这种调整大小引起的变化的影响，但前提是大小保持在总体最大值之内。当然，我可以通过增加限制本身来增加保持在该限制范围内的机会，但这会牺牲大小和速度。请参阅下一点。
  2. 事实上，a) 我的四个初始数组的大小可能相差很大，b) 对于任何给定的用例，我可能只想查看整个数据集的一小部分，这种方法非常浪费空间。辅助列可能非常非常大，尽管最终我只想查看那一小部分。这种方法很笨拙，更重要的是，速度很慢。

我想喜欢是，给定四个初始数组，其根分别位于 C8、D8、E8 和 F8（按照我所附的示例），以便能够将类似这样的内容推送到单个单元格中：

=(C8#,D8#,E8#,F8#)

并拥有一切只是工作。

有任何想法吗？

PS 我提供了使用 DropBox 中 Excel 文件的链接的示例。但如果人们像我一样，他们不会热衷于点击互联网上随机人发来的随机链接。我知道我不会！（话虽如此，这将是试图向你们投放恶意软件的一种冗长的方式，所以我想总会有这种情况。:-)）因此，如果有更好的方法来附加或以其他方式提供此类文件，我将不胜感激。

答案1

尝试：

B1: =FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE($C$8:$F$17)),",","</s><s>")&"</s></t>","//s")
  • TEXTJOIN创建范围内非空白条目的逗号分隔列表。
  • 我们TRANSPOSE首先对范围进行排序，以便按列而不是按行进行排序（这是此类范围引用的通常情况）。
  • 构造一个XML，其中节点以逗号分隔。
  • FILTERXML然后将每个节点拆分为一个单独的数组元素，使用动态数组功能，该元素将SPILL沿着列向下移动

在此处输入图片描述

笔记： 在公式中，我使用了硬编码的范围引用。但是，根据我没有的信息，可以使用动态范围引用。但这取决于范围可能如何变化。

最简单的方法是将其制作成表格。在这种情况下，当您添加/删除/插入行/列时，引用会自动扩展/收缩

表格与溢出数组不能很好地兼容。

  • 因此，我将示例表第一个单元格中的公式更改为：
    C12: =IFERROR(INDEX(SEQUENCE(C$6,1,IF(COLUMNS($A:A)=1,1,COLUMNS($A:A)*6),1),ROWS($1:1)),"")

并且我也使用Table1与上述相同的公式作为范围参考。

当然，公式会Fill Down自动计算。

在您的示例表上：

在此处输入图片描述

如果您确实想使用动态溢出数组（这阻止使用表），您可以在示例工作表上动态定义数组，如下所示：

=$C$11:INDEX($A:$XFD,MAX($6:$6)+10,COUNT($6:$6)+2)

公式如下：

=FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE($C$11:INDEX($1:$1048576,MAX($6:$6)+10,COUNT($6:$6)+2))),",","</s><s>")&"</s></t>","//s")

我注意到 Excel 已更改A:XFD为，1:1048576但它们指的是同一区域（整个工作表）。

答案2

我明白你想避免虚拟专用网络。然而，如果你重新考虑：

如果您使用 VBA 用户定义函数，则可以按照堆叠标准数组公式的方式堆叠动态数组。

假设我们从列中的数据开始A和乙我们希望将独特的项目堆叠在列中乙直接位于列中的唯一项目下A：

在此处输入图片描述

首先在标准模块中输入此 UDF：

Public Function stack(arr1 As Variant, arr2 As Variant)
    Dim i As Long, a

    ReDim temp2(1 To UBound(arr1) + UBound(arr2))
    i = 1
    For Each a In arr1
        temp2(i) = a
        i = i + 1
    Next a

    For Each a In arr2
        temp2(i) = a
        i = i + 1
    Next a
    stack = Application.Transpose(temp2)

End Function

然后在C1进入：

=stack(UNIQUE(A1:A7),UNIQUE(B1:B8))

在此处输入图片描述

笔记：

  1. 这C1公式将自动溢出，因此不需要数组输入。
  2. 对于多列溢出，细节会复杂得多，但方法是相同的。

相关内容