是否有一种快速的方法来“取消透视”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……
6条答案
按热度按时间6yt4nkrj1#
哦,好吧,这有点复杂。其中一个问题是,向导调用快捷方式在非英语版本的excels中不起作用(该死的,在家里我会有英语版本,但在这里工作...)
这里有一个很好的视频:https://www.youtube.com/watch?v=pUXJLzqlEPk
但YouTube视频可以删除,所以要使它成为一个坚实的SO答案:
首先,你需要去“选项”,并添加菜单项“数据透视表和数据透视图向导”。
创建多重合并透视表
并使用自定义变量
并选择范围,并在新工作表
中
然后删除行和列字段
双击数字(图中的54)
Excel会给予你半标准化的数据。
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”。
工作原理-阅读输入
输入是你想要反透视的范围,我称之为unPivMatrixB1:N8(可以是你需要的任何维度),以及你想要反透视的列,我称之为byMatrixA1:A8。
该公式首先计算每个矩阵中的列数: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相同。
工作原理-成形和写入输出
输出将如下所示:
现在,我们将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中,用你想要追加的表来替换它们。
在这个意义上,上面的公式相当于APPEND(table 1,table 2),其中APPEND是两个表的按行追加,这是我们想要的。切换序列模式,您可以进行列式追加。)*
所以,希望这个解释能清楚地说明在一个名为demux的变量的最后阶段发生了什么,这个变量提供了结果。我命名了结果,然后引用了它,这样你就可以轻松地探索,修改或优化公式。所以,demux实际上是这样的:
我不会深入讨论这个最后阶段是如何工作的,因为这已经是一个很长的答案了,但是一个简短的总结是,这个附加使用了由upCells,upC和byC创建的维度来形成输出。
我已经测试过了,但我还没有优化它的性能,也没有使它达到#SwissEngineering标准。
byqmnocz3#
我正在使用这个VBA代码
bzzcjhmw4#
我相信你可以使用一种模运算如下。把你的数据到这个UDF的参数与cols和rows图例。
vsaztqbk5#
还有一种使用Power Query的方法:
Data
〉From a table or a range
Transform
〉Unpivot
Home
〉Close and load
Refresh
如果你的原始表被更新s3fp2yjn6#
多年以后,Excel确实有了一种更快的方法,可以从一个包含公式的单元格中完成此操作(或如所描述的“快速”)。它需要一些更新的函数,如ByRow and ByCol,但这些函数计划对每个人都可用(总有一天)。
回顾一下:
将以下数据集粘贴到单元格
A1
中,您可以使用此Lambda函数来取消透视或展平数据:启动数据集
See sample file here
| (cell A1)|||扬|二月|马尔|四月|五月|
| --------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
| | | | 实际|实际|实际|预算|预算|
| 迪士尼|门票|收入|五零七|六零七|七零七|八零七|九零七|
| 迪士尼|电影|费用|五零八|六零八|七零八|八零八|九零八|
| 星球大战|促销|收入|五零九|六零九|七零九|八零九|九零九|
| 星球大战|维德|税收|510|六百一十|七一零|八一零|九一零|
| 惊奇|HR|费用|五一一|六一一|七一一|八一一|九一一|
独立配方
λ公式