将矩阵转换为 3 列表（'反向透视'、'取消透视'、'展平'、'归一化'）-解网

问：

我需要转换表中的Excel矩阵：FIRSTLATER

第一：

    P1  P2  P3  P4
F1  X
F2  X   X
F3      X       X
F4      X   X

后来：

F   P   VALUE
F1  P1  X
F1  P2
F1  P3
F1  P4
F2  P1  X
F2  P2  X
F2  P3
F2  P4
F3  P1
F3  P2  X
F3  P3
F3  P4  X
F4  P1
F4  P2  X
F4  P3  X
F4  P4

Excel 矩阵数据取消透视 PowerQuery

要“反向枢轴”、“取消枢轴”或“展平”：

对于 Excel 2003：激活汇总表中的任意单元格，然后选择“数据 - 数据透视表”和“数据透视图报表”：

对于更高版本，请使用 +、访问向导。AltDP

对于 Excel for Mac 2011，它是 ++（请参阅此处）。⌘AltP

选择多个合并范围，然后单击。Next
在“第 2a 步（共 3 步）”中，选择“我将创建页面字段”，然后单击。Next
在“步骤 2b（共 3 步）”中，在“范围”字段中指定汇总表范围（示例数据为 A1：E5），然后单击，然后单击。AddNext
在“第 3 步（共 3 步）”中，选择数据透视表的位置（应使用现有工作表，因为 PT 只是暂时需要）：
单击以创建数据透视表：Finish
向下钻取（即双击）总计的交叉点（此处为单元格 V7 或）：7
现在可以删除 PT。
通过在“快捷菜单”中选择“表”（在“表”中单击鼠标右键）并“转换为范围”，可以将生成的表格转换为传统的单元格数组。

在Launch Excel上有一个关于同一主题的视频，我认为质量非常好。

=LET(data,B2:E5,
     dataRows,ROWS(data),
     dataCols,COLUMNS(data),
     rowHeaders,OFFSET(data,0,-1,dataRows,1),
     colHeaders,OFFSET(data,-1,0,1,dataCols),
     dataIndex,SEQUENCE(dataRows*dataCols),
     rowIndex,MOD(dataIndex-1,dataRows)+1,
     colIndex,INT((dataIndex-1)/dataRows)+1,
     dataColumn, IF(INDEX(data,rowIndex,colIndex)="","",INDEX(data,rowIndex,colIndex)),
     unfiltered, CHOOSE({1,2,3},INDEX(rowHeaders,rowIndex),INDEX(colHeaders,colIndex), dataColumn),
     filtered, FILTER(unfiltered, dataColumn<>""),
     unfiltered)

这将显示所有项目，包括那些具有空白数据的项目。若要消除空白，请将最后一个参数更改为 filtered。

0赞 mark fitzpatrick 6/26/2021 #5

还有一个要添加到BoK中。这需要 Excel 365。它将 B1：E5 的轴心取消为 A1：A5。

=LET( unPivMatrix, B1:E5,
      byMatrix, A1:A5,
        upC, COLUMNS( unPivMatrix ),
        byC, COLUMNS( byMatrix ),
        dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
        dmxSeq, SEQUENCE( dmxR ) + 1,
        upCells, dmxR * upC,
        upSeq, SEQUENCE( upCells,, 0 ),
        upHdr, INDEX( INDEX( unPivMatrix, 1, ),  1,  SEQUENCE( upC ) ),
        upBody, INDEX( unPivMatrix,  dmxSeq,  SEQUENCE( 1, upC ) ),
        byBody, INDEX( byMatrix,  dmxSeq,  SEQUENCE( 1, byC ) ),
        attr, INDEX( upHdr, MOD( upSeq, upC ) + 1 ),
        mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
        demux, IFERROR( INDEX(
                              IFERROR( INDEX( byBody,
                                              IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
                                                       SEQUENCE( 1, byC + 1 ) ),
                                        attr ),
                              upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
                         mux ),
        FILTER(demux, mux<>"")
 )

注意：byMatrix 可以是一个包含多列的范围，它将复制列的行值。例如，你可以有 byMatrix 的 A1：C5 和 D1：H5 的 unPivMatrix，它将复制 A2：C5 列值（忽略 A1）。

上一个：使用VBA循环浏览文件夹中的文件？

下一个：从 C 读取 Excel 文件#

将矩阵转换为 3 列表（'反向透视'、'取消透视'、'展平'、'归一化'）

Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

评论

要“反向枢轴”、“取消枢轴”或“展平”：

评论

评论

评论