☰

Excel - 如何“合并”某一行的列，并为合并列中的值创建重复行？

tag-icon • tag-icon microsoft-excel worksheet-function
Excel - 如何“合并”某一行的列，并为合并列中的值创建重复行？

我有一张如下表：

Account# Code1 Code2 Code3    
123      A     B     C

我想删除“Code2”和“Code3”列，并创建如下所示的新行：

Account# Code    
123      A    
123      B    
123      C

不幸的是，我不知道这应该叫什么，每次搜索“将列转换为行”都会出现转置结果。是否有一些 excel 函数可以用来代替复制行并手动执行此操作？

答案1

您的数据在 cols 中A通过C， 在F1进入：

=INDEX($A$2:$A$9999,ROUNDUP(ROW()/3,0))

并抄下来。在G1进入：

=OFFSET($B$2,ROUNDUP(ROWS($2:2)/3,0)-1,MOD(ROWS($2:2)-1,3))

并向下复制：

在此处输入图片描述

答案2

这是一个使用 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
乙
C
德
埃
F
...

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

解复用 col1
123
123
123
456
456
456
789

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

解复用 col1 附加列
123 #参考！
123 #参考！
123 #参考！
456 #参考！
456 #参考！
456 #参考！
789 #参考！

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

解复用 col1 附加列
123 A
123 乙
123 C
456 德
456 埃
456 F
789 G

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

相关内容