如何在 Excel 中将多列合并为一列？

Question 1

您要尝试执行的操作是 Unpivot 或 Melt。我能想到三种方法可以做到这一点：

创建一个 OFFSET 公式。
像李一样在 Power BI 引擎中使用 PowerQuery。
创建一个动态数组，我将在下面展示。

每种方法都有优点和缺点。Offset 的优点是输入数据后可以立即给出答案。缺点是，您必须将公式复制到目标位置，因此如果输入数据的大小发生变化，您必须相应地复制公式。此外，OFFSET 是一个易失性函数，可能会也可能不会影响工作簿的性能。

Power Query 方法要求您每次更改数据时都刷新数据。

动态数组与偏移量类似，它们会立即计算，但它们的优点是可以随着数据的变化而重新调整形状，并且它们是非易失性的。它们的缺点是它们会动态地将结果写入工作表本身，因此如果您有任何占用的单元格，公式就会溢出。

也就是说，这里有一种使用 LET 的动态数组方法，可以使其更容易理解、修改和调试。

=LET( unPivMatrix, A2:A4,
      byMatrix, B2:D4,
       upC, COLUMNS( unPivMatrix ),
       upCells, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) * upC,
       upSeq, SEQUENCE( upCells,, 0 ),
       byC, COLUMNS( byMatrix ),
       mux, INDEX( unPivMatrix, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
       demux, IFERROR( INDEX( byMatrix,
                              IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1,
                                       MOD( upSeq, upC ) + 1 ),
                              SEQUENCE( 1, byC + 1 ) ),
                        mux ),
       demux
     )

这矩阵是你想要分配的列 - 因此这些将是类型在您的示例中。通过Matrix您想要根据哪些列进行逆透视？这些列是对象在您的示例中。考虑一下 Excel 中的数据透视表。通过Matrix是您要旋转数据的行，以及矩阵是要取消透视的数据。

此公式首先为逆透视创建一个形状，以便可以将其复用到单个列中（称为多路复用器）像这样：

多路复用器
A
b
C
埃
F
G
...

结果被放入解复用首先创建一个数组通过Matrix重复通过Matrix中每列的列值矩阵

解复用 col1
1
1
1
2
2
2
3

通过引用比实际存在的列多一列通过Matrix，INDEX 函数将引发错误。

解复用 col1	附加列
1	#参考！
1	#参考！
1	#参考！
2	#参考！
2	#参考！
2	#参考！
3	#参考！

利用这个错误可以有效地融合两个数组（解复用的通过Matrix和多路复用器) 一起。通过将结果包装在 IFERROR 中，我们能够将 #REF! 错误替换为多路复用器。

解复用 col1	附加列
1	A
1	b
1	C
2	d
2	埃
2	F
3	G

此公式现在是基于动态数组的通用反透视。即，您可以将任何范围放入矩阵和通过Matrix并以与 Power Query 中的 UNPIVOT 工作方式类似的方式获取您想要的结果。

Answer