有没有快速的方法来解开Excel矩阵/数据透视表(在Excel或其他地方),而不需要编写宏或其他代码? 再次,我可以编写自己做的代码(C#或VBA或其他)。 我想知道是否可以轻松地执行而不使用代码?
例如。我需要转换这个权限矩阵(以Excel表格/矩阵形式)
到这个半正规化表(所以我可以把它插入一个SQL数据库):
例如在SQL中,我可以这样做:
CREATE TABLE dbo.T_DocumentMatrix ( [功能] [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, [ 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复制粘贴数据,然后
$ $ $ $ $ $ $ $ $ $ [$] $ b,[GROUP-SuperUser] ,[GROUP-Manager] ,[GROUP-OLAP] ,[GROUP-1] ,[GROUP-2] ,[GROUP-3] ,[GROUP-4] ,[GROUP-5] ,[GROUP-6] ,[GROUP-7] ,[GROUP-8] ,[外部] FROM T_DocumentMatrix )AS p UNPIVOT (权限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] ,[外部] ))AS unpvt ;但是,这需要更改表创建脚本和组间每个变化的不透明脚本...
解决方案哦,这有点复杂。 其中一个问题是,向导 - 调用快捷键不工作在非英语版本的excels(在家里,我会有英文版,但在这里工作...)
这是一个很好的视频:
创建多个合并数据透视表
和使用自定义变体
并选择范围,并在新的工作表中
然后删除行和列字段
双击NUMBER(图片中的54)
,excel将为您提供中间标准化数据。
Is there a quick way to "unpivot" an Excel matrix/pivot-table (in Excel or elsewhere), without writing macros or other code ? Again, I can write code (C# or VBA or whatever) that does that myselfs. I want to know if it is possible to do it without code, quickly ?
E.g. I need to convert this permission matrix (given as Excel-table/matrix)
into this half-normalized table (so I can insert it into a SQL database):
e.g. in SQL I could do it like this:
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 );copy-paste the data from excel, and then
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 ;However, that requires I change the table-create script and the unpivot-script for every change in groups...
解决方案Oh, well, it's a little complicated. One of the problems is, the wizard-callup shortcuts don't work in non-english versions of excels (damn, at home I would have the English version, but here at work...)
Here's a good video: www.youtube/watch?v=pUXJLzqlEPk
But youtube videos can be deleted, so to make it a solid SO answer:
First, you need to go to "Options", and add the menuband-item "Pivot table and PivotChart Wizard".
Create a multiple consolidation pivot table
and use the custom variant
and select the range, and in new work sheet
then delete rows and columns fields
Double click on the NUMBER (54 in the picture)
and excel will give you the halfway normalized data.
更多推荐
拆分Excel矩阵/数据透视表?
发布评论