将矩阵转换为 3 列表（“反向透视”、“逆透视”、“展平”、“标准化”）
我需要转换表LATER中的Excel矩阵FIRST：
FIRST：
P1 P2 P3 P4
F1 X
F2 X X
F3 X X
F4 X X
LATER：
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
I need to convert the Excel matrix FIRST in the table LATER:
FIRST:
P1 P2 P3 P4
F1 X
F2 X X
F3 X X
F4 X X
LATER:
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
要“反向透视”、“逆透视”或“展平”：
对于 Excel 2003：激活汇总表中的任意单元格并选择“数据 - 数据透视表和数据透视图报表”：
对于更高版本访问向导使用 Alt+D、P。
对于 Excel for Mac 2011，它是 ⌘+Alt+P (请参阅此处 ）。
选择多个合并范围，然后单击下一步。
在“第 2a 步（共 3 步）”中，选择我将创建页面字段 并单击下一步。
在“Step 2b of 3”中，在范围 字段（示例数据为 A1:E5），然后单击添加，然后单击下一步。
在“第 3 步，共 3 步”中，选择数据透视表的位置（现有工作表应服务，因为 PT 只是暂时需要的）：
单击完成创建数据透视表：
向下钻取（即双击）总计的相交（此处为单元格 V7 或
7）：现在可以删除 PT。
Launch Excel 上有一个关于同一主题的视频，我认为质量非常好。
To “reverse pivot”, “unpivot” or “flatten”:
For Excel 2003: Activate any cell in your summary table and choose Data - PivotTable and PivotChart Report:
For later versions access the Wizard with Alt+D, P.
For Excel for Mac 2011, it's ⌘+Alt+P (See here).
Select Multiple consolidation ranges and click Next.
In “Step 2a of 3”, choose I will create the page fields and click Next.
In “Step 2b of 3” specify your summary table range in the Range field (A1:E5 for the sample data) and click Add, then Next.
In “Step 3 of 3”, select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily):
Click Finish to create the pivot table:
Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or
7):The PT may now be deleted.
There is a video on the same subject at Launch Excel which I consider excellent quality.
另一种不使用 VBA 反转数据的方法是使用 PowerQuery，这是 Excel 2010 及更高版本的免费插件，可在此处获取：http://www.microsoft.com/en-us/download/details.aspx?id=39379
安装并激活 Power Query插件。然后按照以下步骤操作：
向数据源添加列标签，并通过“插入”>“将其转换为 Excel 表格”。表或 Ctrl - T。
选择表中的任意单元格，然后在 Power Query 功能区上单击“来自表”。
这将在 Power Query 编辑器窗口中打开表。
单击第一列的列标题将其选中。然后，在转换功能区上，单击取消透视列下拉列表并选择取消透视其他列。
对于没有逆透视其他列命令的 Power Query 版本，请选择除第一列之外的所有列（按住 Shift 键并单击列标题），然后使用逆透视 > 命令。
结果是一个平面表格。单击主页功能区上的关闭并加载，数据将加载到新的 Excel 工作表中。
现在到了精彩的部分。将一些数据添加到源表中，例如
单击包含 Power Query 结果表的工作表，然后单击在数据功能区中单击全部刷新。您将看到类似以下内容：
Power Query 不仅仅是一次性转换。它是可重复的，并且可以链接到动态变化的数据。
Another way to unpivot data without using VBA is with PowerQuery, a free add-in for Excel 2010 and higher, available here: http://www.microsoft.com/en-us/download/details.aspx?id=39379
Install and activate the Power Query add-in. Then follow these steps:
Add a column label to your data source and turn it into an Excel Table via Insert > Table or Ctrl - T.
Select any cell in the table and on the Power Query ribbon click "From Table".
This will open the table in the Power Query Editor window.
Click the column header of the first column to select it. Then, on the Transform ribbon, click the Unpivot Columns drop-down and select Unpivot other columns.
For versions of Power Query that don't have the Unpivot other columns command, select all columns except the first one (using Shift-click on the column headers) and use the Unpivot command.
The result is a flat table. Click Close and Load on the Home ribbon and the data will be loaded onto a new Excel sheet.
Now to the good part. Add some data to your source table, for example
Click on the sheet with the Power Query result table and on the Data ribbon click Refresh all. You will see something like:
Power Query is not just a one-time transformation. It is repeatable and can be linked to dynamically changing data.
到目前为止，所有解决方案都涉及 VBA、PowerQuery 等，这些都很棒，但都是“一次性”事件。为了使其更加动态，请考虑使用 INDEX(MATCH(...))。这将允许动态更新表。
All of the solutions so far involve VBA, PowerQuery, etc. which are great, but are "one-time" events. To make it more dynamic, consider using INDEX(MATCH(...)). This will allow for dynamic updates to the table.
添加了 LET 函数 &动态数组允许这种非 VBA 解决方案。
这将显示所有项目，包括具有空白数据的项目。要消除空白，请将最后一个参数更改为已过滤。
The addition of the LET function & dynamic arrays allows for this non-VBA solution.
This will show all items including those with blank data. To eliminate the blanks change the last parameter to filtered.
又一个要添加到 BoK 中。这需要 Excel 365。它将 B1:E5 按 A1:A5 逆透视。
One more to add to the BoK. This requires Excel 365. It unpivots B1:E5 by A1:A5.
现在，有了最新的功能，这个问题就得到了很好的解决，甚至可以创建一个中间有间隔的动态范围。我在不同的数组中使用 ByCol 时遇到了一些限制，因此我使用的索引和序列超出了可能需要的范围。
之前的数据
a1a1公式
从技术上讲，您可以避免指定数据范围，但公式会变得更加不稳定并降低性能。
输出
This is a pretty solved situation now with the latest functions, even to create a dynamic range with spacers in between. I have run into some limitations with using ByCol in a different array so I use index and sequence more than might be considered necassary.
Dataset Before
a1a1Formula
Technically you could avoid specifying the data range but the formula becomes more volatile and reduces performance.
Ouput