取消透视Excel矩阵/透视表?

qnakjoqk  于 2023-04-13  发布在  其他
关注(0)|答案(6)|浏览(178)

是否有一种快速的方法来“取消透视”Excel矩阵/透视表(在Excel或其他地方),而无需编写宏或其他代码
同样,我可以编写代码(C#或VBA或其他)来自己完成。
我想知道是否有可能做到这一点没有代码,很快?
例如,我需要转换此权限矩阵(以Excel-table/matrix形式给出)

插入到这个半规范化的表中(这样我就可以将它插入到SQL数据库中):

例如,在SQL中,我可以这样做:

CREATE TABLE dbo.T_DocumentMatrix
(
    [Function] [varchar](255) NULL,
    [GROUP-Admin] [varchar](255) NULL,
    [GROUP-SuperUser] [varchar](255) NULL,
    [GROUP-Manager] [varchar](255) NULL,
    [GROUP-OLAP] [varchar](255) NULL,
    [GROUP-1] [varchar](255) NULL,
    [GROUP-2] [varchar](255) NULL,
    [GROUP-3] [varchar](255) NULL,
    [GROUP-4] [varchar](255) NULL,
    [GROUP-5] [varchar](255) NULL,
    [GROUP-6] [varchar](255) NULL,
    [GROUP-7] [varchar](255) NULL,
    [GROUP-8] [varchar](255) NULL,
    [Externals] [varchar](255) NULL
);

从excel中复制粘贴数据,然后

SELECT * 
FROM 
(
    SELECT 
         [Function]
        ,[GROUP-Admin]
        ,[GROUP-SuperUser]
        ,[GROUP-Manager]
        ,[GROUP-OLAP]
        ,[GROUP-1]
        ,[GROUP-2]
        ,[GROUP-3]
        ,[GROUP-4]
        ,[GROUP-5]
        ,[GROUP-6]
        ,[GROUP-7]
        ,[GROUP-8]
        ,[Externals]
    FROM T_DocumentMatrix
) AS p
UNPIVOT
(
    Rights FOR GroupName IN 
    (
         [GROUP-Admin]
        ,[GROUP-SuperUser]
        ,[GROUP-Manager]
        ,[GROUP-OLAP]
        ,[GROUP-1]
        ,[GROUP-2]
        ,[GROUP-3]
        ,[GROUP-4]
        ,[GROUP-5]
        ,[GROUP-6]
        ,[GROUP-7]
        ,[GROUP-8]
        ,[Externals]
    )
) AS unpvt
;

但是,这需要我为组中的每个更改更改更改表创建脚本和unpivot-script……

6yt4nkrj

6yt4nkrj1#

哦,好吧,这有点复杂。其中一个问题是,向导调用快捷方式在非英语版本的excels中不起作用(该死的,在家里我会有英语版本,但在这里工作...)
这里有一个很好的视频:https://www.youtube.com/watch?v=pUXJLzqlEPk
但YouTube视频可以删除,所以要使它成为一个坚实的SO答案:
首先,你需要去“选项”,并添加菜单项“数据透视表和数据透视图向导”。

创建多重合并透视表

并使用自定义变量

并选择范围,并在新工作表


然后删除行和列字段

双击数字(图中的54)

Excel会给予你半标准化的数据。

sirbozc5

sirbozc52#

虽然这是一个非常古老的问题,Stefan在过去找到了一个启发性的答案,但它可能值得重新审视。我自己也遇到了对这种无代码,动态Unpivot方法的需求,Google搜索把我带到了这里。是的,Power Query可以完成这项工作,但这并不是完全无代码的,因为在Power BI中运行了一个脚本化的后台解决方案,它需要用户刷新数据(因此,它不是自动在工作簿内),也不会在Excel for Mac(tmk)上运行。
以下是基于动态数组并使用LET函数的方法,因此需要Excel 2016或Microsoft 365。
假设Stefan的数据在单元格A1到N8中。用Power Query的说法,我们会说Stefan想要“UnpivotB1:N8ByA1:A8”。

  • 注意:以下方法也接受By的多列,例如您可能需要“UnpivotD1:N8ByA1:C8”。*
=LET( unPivMatrix, B1:N8,
      byMatrix, A1:A8,
       upC, COLUMNS( unPivMatrix ),
       byC, COLUMNS( byMatrix ),
       dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
       upCells, dmxR * upC,
       upSeq, SEQUENCE( upCells,, 0 ),
       upHdr, INDEX( INDEX( unPivMatrix, 1, ),  1,  SEQUENCE( upC ) ),
       upBody, INDEX( unPivMatrix,  SEQUENCE( dmxR ) + 1,  SEQUENCE( 1, upC ) ),
       byBody, INDEX( byMatrix,  SEQUENCE( dmxR ) + 1,  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 ),
       demux
     )

工作原理-阅读输入

输入是你想要反透视的范围,我称之为unPivMatrixB1:N8(可以是你需要的任何维度),以及你想要反透视的列,我称之为byMatrixA1:A8。

  • 作为一项规则,byMatrix的行需要与unPivMatrix相同,所以你必须从A1而不是A2开始。我决定采用这种约定,因为它们可能是A1中的头,并且对于一些小的mod,这个公式可以产生与Power Query完全相同的输出,但这不是Stefan要求的。*

该公式首先计算每个矩阵中的列数:upCunpivot columns和byCby columns。从这些计算dmxR(解复用的行):通过取unPivMatrix和byMatrix的行的最小值并减去1来确定在unpivot中将传递多少行值,因为unPivMatrix具有头部。取MIN是在输入具有不同行数的情况下的错误预防步骤,这根据定义是错误的。
这些值用于创建整形变量,稍后将用于形成输出。upCells是将被取消透视的值的数量,用于生成名为upSeq的索引模式,该模式从0开始计数值的数量,稍后将在INDEX函数内部使用。我们从0开始,因为upSeq将被调制以形成输入和输出的正确索引。
现在我们将分解矩阵的各个部分,以便我们可以对它们进行复用。各个部分看起来像这样:

有一个unpivot头(upHdr),包含值数据的标识符(在Stefan的情况下,GROUP-Admin、GROUP-SuperUser、这些将被多路复用到一列中,该列稍后将被放置在每个未旋转的值旁边。通过将整个unPivMatrix放入INDEX函数并阅读行1和所有列来创建upHdr。我将该INDEX函数嵌套到另一个INDEX中,后者使用大小为upC的垂直SEQUENCE将水平数组整形为垂直数组。

upBody包含我们想要通过多路复用进行反透视的值。它是通过将upMatrix放入INDEX并根据将输出的行数对其进行整形而创建的SEQUENCE(dmxR)+1**§**针对待读取的每一列(upC).byBody包含的数据将针对upBody中的每个值进行多路复用。它的创建方式与upBody相同。

  • § -加1跳过标题行 *
    工作原理-成形和写入输出

输出将如下所示:

现在,我们将upHdr复用到attr或属性(使用Power Query术语)中,方法是将upHdr放入INDEX中,并应用基于upSeq的调制序列,该序列每upC次重复一次(例如{1;2;3;4;5;6;7;8;1;2;...})。这就是在upSeq中从0开始的重要之处。* attr的输出看起来像(在Stefan的情况下){GROUP-Admin; GROUP-SuperUser, etc.}

mux是将针对每个属性和byBody行进行复用的值(使用Power Query术语)。它是通过将valBody放入INDEX中,然后将其重塑为由行创建的复用模式来创建的。

upSeq/upC + 1,生成一行{1,1,1,1,1,1,1,1,2,2,...}
和列
MOD( upSeq, upC ) + 1,其产生{1;2;3;4;5;6;7;8;1;...}的列。
mux的输出将是unPivMatrix的内容。在Stefan的例子中,这将有点特殊,因为他使用 * 和空格作为数据。这个公式将把空格转换为0。因此,如果这是一个问题,您可以将mux Package 到IF( ISBLANK( mux ), "", mux )中,但我没有添加它,因为我想进行一般的逆透视,因为我相信Stefan早就离开了。

工作原理-将器件解复用到输出

现在简单的部分已经完成了,是时候去追求更难的东西了-将所有这些放在一个动态数组中。将多个数组放在一起需要一个技巧,这个技巧必须应用两次,因为,正如你所看到的,我们将三个表放在一起。这个技巧就像有一个APPEND函数一样:
APPEND( APPEND( table1, table2 ), table3 )

若要合并两个数组,请将第一个数组放入INDEX中,然后引用数组外部的单元格以强制产生#REF!错误。例如,如果我有一个由字母A到F组成的3 x 2数组,并且我引用了单元格3,3,它将引发引用错误。

现在你可以利用这些错误,通过将INDEX Package 在IFERROR中,用你想要追加的表来替换它们。

IFERROR( INDEX( table1,
                SEQUENCE( table1.rows ),
                SEQUENCE( 1, table1.columns + table2.columns ) ),
         table2 )

在这个意义上,上面的公式相当于APPEND(table 1,table 2),其中APPEND是两个表的按行追加,这是我们想要的。切换序列模式,您可以进行列式追加。)*
所以,希望这个解释能清楚地说明在一个名为demux的变量的最后阶段发生了什么,这个变量提供了结果。我命名了结果,然后引用了它,这样你就可以轻松地探索,修改或优化公式。所以,demux实际上是这样的:

APPEND( byBody, APPEND( attr, mux ) )

我不会深入讨论这个最后阶段是如何工作的,因为这已经是一个很长的答案了,但是一个简短的总结是,这个附加使用了由upCells,upC和byC创建的维度来形成输出。
我已经测试过了,但我还没有优化它的性能,也没有使它达到#SwissEngineering标准。

byqmnocz

byqmnocz3#

我正在使用这个VBA代码

Sub Unpivot()
'
Dim Rowlabel As Range
Dim Columnlabel As Range
Dim Pap As Range
Dim Tabl As Range
Dim i As Integer
Dim j As Integer
Dim a As Integer
Dim b As Integer
Dim Data As Range
Dim k As Integer
Dim Label As Range
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim SrcData As String
'
ActiveSheet.Copy Before:=Worksheets(1)
Set Tabl = Selection
    For Each Pap In Tabl
     If Pap.MergeCells Then
        With Pap.MergeArea
            .UnMerge
            .Value = Pap.Value
        End With
    End If
    Next
i = Application.InputBox("Number of row contain label:", "Excel", i, Type:=2)
j = Application.InputBox("Number of column contain label:", "Excel", j, Type:=2)
On Error Resume Next
Sheets("Unpivot_Table").Delete
Sheets.Add.Name = "Unpivot_Table"
Set Pap = Range("Unpivot_Table!B2")
b = Tabl.Rows.Count
a = Tabl.Columns.Count
Set Data = Range(Tabl.Cells(i + 1, j + 1), Tabl.Cells(b, a))
Set Columnlabel = Range(Tabl.Cells(i + 1, 1), Tabl.Cells(b, j))
Set Rowlabel = Range(Tabl.Cells(1, j + 1), Tabl.Cells(i, a))
Pap.Select
For Each Column In Data.Columns
    Column.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Columnlabel.Copy
    Selection.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
    Column.Copy
    Selection.Offset(b - i, -1).Select
Next Column
Pap.Offset(0, j + 1).Select
For Each Column In Rowlabel.Columns
    Column.Copy
    Range(Selection, Selection.Offset(b - i - 1, 0)).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Selection.End(xlDown).Offset(1, 0).Select
Next Column
Set Label = Range(Pap.Offset(-1, 0), Pap.Offset(0, i + j + 1))
    For k = 1 To i + j + 1
    Label.Cells(1, k).Value = Application.InputBox(Label.Cells(2, k).Value & " is belong to Fieldname", "Hoang", k, Type:=2)
    Next
Range(Pap.End(xlUp), Pap.End(xlDown).End(xlToRight)).Select
SrcData = ActiveSheet.Name & "!" & Selection.Address
On Error Resume Next
    Sheets("Pivot").Delete
    Sheets.Add.Name = "Pivot"
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:="Pivot!" & Sheets("Pivot").Range("A3").Address(ReferenceStyle:=xlR1C1), _
    TableName:="PivotTable1")
End Sub
bzzcjhmw

bzzcjhmw4#

我相信你可以使用一种模运算如下。把你的数据到这个UDF的参数与cols和rows图例。

Function MyUnpivot(matice As Range) As Variant
    Dim I As Integer
    Dim J As Integer

    Dim radka As Integer
    Dim sloupec As Integer

    I = matice.Rows.Count - 1
    J = matice.Columns.Count - 1

    Dim returnVal()
    ReDim Preserve returnVal(1 To I * J, 1 To 3)

    For x = 1 To I * J
        radka = ((x - 1) Mod I) + 2
        sloupec = WorksheetFunction.Floor_Math((x - 1 / 2) / I) + 2
        returnVal(x, 1) = matice.Cells(1, sloupec)
        returnVal(x, 2) = matice.Cells(radka, 1)
        returnVal(x, 3) = matice.Cells(radka, sloupec)
    Next

    MyUnpivot = returnVal
End Function
vsaztqbk

vsaztqbk5#

还有一种使用Power Query的方法:

  • 选择单元格
  • 菜单DataFrom a table or a range

  • 在PowerQuery编辑器中,选择所有列(保存第一列),然后选择TransformUnpivot

  • 该表是unpivoted.转到HomeClose and load

  • 你的unpivoted表在这里.右键单击它并选择Refresh如果你的原始表被更新

s3fp2yjn

s3fp2yjn6#

多年以后,Excel确实有了一种更快的方法,可以从一个包含公式的单元格中完成此操作(或如所描述的“快速”)。它需要一些更新的函数,如ByRow and ByCol,但这些函数计划对每个人都可用(总有一天)。
回顾一下:

  • 无VBA
  • 在行和列数据范围中的多个轴上为动态
  • 可转换为lambda(桌面版)

将以下数据集粘贴到单元格A1中,您可以使用此Lambda函数来取消透视或展平数据:

启动数据集

See sample file here
| (cell A1)|||扬|二月|马尔|四月|五月|
| --------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
| | | | 实际|实际|实际|预算|预算|
| 迪士尼|门票|收入|五零七|六零七|七零七|八零七|九零七|
| 迪士尼|电影|费用|五零八|六零八|七零八|八零八|九零八|
| 星球大战|促销|收入|五零九|六零九|七零九|八零九|九零九|
| 星球大战|维德|税收|510|六百一十|七一零|八一零|九一零|
| 惊奇|HR|费用|五一一|六一一|七一一|八一一|九一一|

独立配方

=LET(dataRng,D3:H7,  rowAxis,A3:C7, colAxis,D1:H2,
   iCol,COLUMN(INDEX(rowAxis,1,1)),   amountCol,TOCOL(dataRng),  totalCells,COUNTA(amountCol),
          HSTACK(
              INDEX(rowAxis,
                     INT(SEQUENCE(totalCells,1,0,1)/COLUMNS(dataRng))+1,
                     BYCOL(INDEX(rowAxis,1,),  LAMBDA(aCol,COLUMN(aCol) -iCol +1))),
              INDEX(colAxis,
                      SEQUENCE(1,ROWS(colAxis),1,1),
                      MOD(SEQUENCE(totalCells,1,0,1),COLUMNS(dataRng))+1),
               amountCol))

λ公式

=LAMBDA(dataRng,rowAxis,colAxis,
   LET(iCol,COLUMN(INDEX(rowAxis,1,1)), amountCol,TOCOL(dataRng), totalCells,COUNTA(amountCol),
          HSTACK(
              INDEX(rowAxis,
                     INT(SEQUENCE(totalCells,1,0,1)/COLUMNS(dataRng))+1,
                     BYCOL(INDEX(rowAxis,1,),  LAMBDA(aCol,COLUMN(aCol) -iCol +1))),
              INDEX(colAxis,
                      SEQUENCE(1,ROWS(colAxis),1,1),
                      MOD(SEQUENCE(totalCells,1,0,1),COLUMNS(dataRng))+1),
               amountCol
                      )))(D3:H7,A3:C7,D1:H2)

相关问题