如何使FLATTEN适用于非连续范围？

Question

如何使FLATTEN适用于非连续范围？

arraysexcellambdaexcel-formulaflatten

3

我有一个FLATTEN LAMBDA函数，可以将数组中的数据展平。这个函数很有效，但我想要集成另一个数组参数，以便我可以使用非连续范围。

在我的例子中，范围A1:B6存储在array中并返回展平的数据。

如何包含一个array2参数来接受D1:D6作为附加范围?

公式:

FLATTEN =

LAMBDA(array,

LET(
    rows,ROWS(array),
    columns,COLUMNS(array),
    sequence,SEQUENCE(rows*columns),
    quotient,QUOTIENT(sequence-1,columns)+1,
    mod,MOD(sequence-1,columns)+1,

    INDEX(IF(array="","",array),quotient,mod)

    )   
)

- Statto

3个回答

2

这是一个小技巧，但是：

FLATTEN =

LAMBDA(array,

LET(
    rows,ROWS(array),
    columns,COLUMNS(array),
    sequence,SEQUENCE(rows*columns),
    quotient,QUOTIENT(sequence-1,columns)+1,
    mod,MOD(sequence-1,columns)+1,
    unpiv, INDEX(array,quotient,mod),
    FILTER(unpiv, unpiv<>"")

    )   
)

如果您的数组已经扩展到A1:D6作为输入，则可以执行以下操作：

我认为JvdV的答案将是最好的，具体取决于您想要的输入格式，但我已经写出了这个，所以继续吧...

您可以执行以下操作：

=LET( array1, A1:B6, array2, D1:D6,

      rows1,ROWS(array1),     rows2,ROWS(array2),
      columns1,COLUMNS(array1),   columns2,COLUMNS(array2),
      rows, MIN(rows1, rows2),
      columns, columns1 + columns2,
      sequence,SEQUENCE(rows*columns),
      quotient,QUOTIENT(sequence-1,columns)+1,
      mod,MOD(sequence-1,columns)+1,

      IFERROR(INDEX( IF( ISBLANK(array1),"",array1),quotient,mod),
              INDEX(IF( ISBLANK(array2),"",array2),quotient,MOD(sequence-1,columns2)+1) )

)

它将需要将多列/行输入到两个数组中。

- mark fitzpatrick

感谢您的努力，但那只是一个临时解决方案。我需要找到一种方法来添加一个额外的“数组”参数。 - Statto

@Statto - 当数组中有空白时，您希望它显示为""还是跳过？ - mark fitzpatrick

我希望它显示为“”。 - Statto

@Statto - 好的，这样做。我真的很喜欢JvdV的解决方案，因为它在处理上非常简洁，但输入必须逐列进行。对于LAMBDA函数，最好建立一个按列追加并使用APPENDC作为输入调用FLATTEN的方法。例如，FLATTEN(APPENDC(A1:A6,D1:D6))。 - mark fitzpatrick

@Statto - 上次的评论不是很清楚：好的，这就做到了。我非常喜欢JvdV的解决方案，因为它在处理上非常简洁，但输入必须按列进行。对于LAMBDA函数来说，这很繁琐。也许最好保持你当前的FLATTEN不变，并构建一个按列附加的LAMBDA函数（我们称之为APPENDC），然后使用APPENDC作为输入调用你的FLATTEN。例如：FLATTEN(APPENDC(A1:A6,D1:D6)) - mark fitzpatrick

2

从这里的文章开始，并根据数组中空值的观察和允许不同大小的数组进行更新，我们可以得到两个公式，您应该能够将其翻译为用于“堆叠”和“搁置”数组的Named LAMBDA函数。

堆叠数组

=LET(rngA, A1:C5, rngB, A9:D11,
rowsA, ROWS(rngA), rowsB, ROWS(rngB),
NumCols, MAX(COLUMNS(rngA), COLUMNS(rngB)),
SeqRow, SEQUENCE(rowsA + rowsB), SeqCol, SEQUENCE(1, NumCols),
Result, IF(SeqRow <= rowsA, INDEX(IF(rngA="","",rngA), SeqRow, SeqCol), 
    INDEX(IF(rngB="","",rngB), SeqRow-rowsA, SeqCol)),
arr, IFERROR(Result,""), arr)

存储数组

=LET(rngA, A1:C5, rngB, B8:D12,
colsA, COLUMNS(rngA), colsB, COLUMNS(rngB),
NumRows, MAX(ROWS(rngA), ROWS(rngB)),
SeqRow, SEQUENCE(NumRows), SeqCol, SEQUENCE(1, colsA + colsB),
Result, IF(SeqCol <= colsA, INDEX(IF(rngA="","",rngA), SeqRow, SeqCol),
    INDEX(IF(rngB="","",rngB), SeqRow, SeqCol-colsA ) ),
arr, IFERROR(Result,""), arr)

一旦您拥有一个连续的数组，您可以应用您已经有的公式：

更新为使用溢出范围以便于测试...

=LET(data, A1#,
rows, ROWS(data), cols, COLUMNS(data),
seq, SEQUENCE(rows*cols,,0),
list, INDEX(IF(data="", "", data), QUOTIENT(seq, cols)+1, MOD(seq, cols)+1),
FILTER(list, LEN(list)>0))

这种方法真正适用于命名的LAMBDA函数，否则你将会得到庞大的公式，其他方法在这种情况下可能更好。

- Tragamor

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

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

- JvdV · Accepted Answer

编辑 7/4/22:

现在，ms365引入了一个名为 VSTACK() 和 TOCOL() 的函数，它允许我们实现GS的 FLATTEN() 缺失的功能（而且表现得更加流畅）

在您的情况下，公式可能变成：

=TOCOL(A1:D6,1)

这个小公式（第二个参数告诉函数忽略空单元格）将替换下面的所有内容。如果您不想合并C1:C6中的值，可以尝试以下方法：

=VSTACK(TOCOL(A1:B6),D1:D6)

之前的回答:

在事先不知道要包含多少个数组的情况下，你无法真正创建一个具有 LAMBDA() 的“展平函数”。你有多列数组的事实将增加“棘手性”。在这种特定方式中“展平”多个列的一种方法是:

G1 单元格中的公式:

=LET(X,CHOOSE({1,2,3},A1:A6,B1:B6,D1:D6),Y,COLUMNS(X),Z,SEQUENCE(COUNTA(X)),INDEX(X,CEILING(Z/Y,1),MOD(Z-1,Y)+1))

编辑：根据您的评论，您可以将其扩展为以下内容：

=LET(X,CHOOSE({1,2,3},IF(A1:A6="","",A1:A6),IF(B1:B6="","",B1:B6),IF(D1:D6="","",D1:D6)),Y,COLUMNS(X),Z,SEQUENCE(ROWS(X)*Y),FLAT,INDEX(X,CEILING(Z/Y,1),MOD(Z-1,Y)+1),FILTER(FLAT,FLAT<>""))