将矩阵转换为 3 列表('reverse pivot'、'unpivot'、'flatten'、'normalize') [英] Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

问题描述

我需要转换表格LATER中的Excel矩阵FIRST:

第一:

 P1 P2 P3 P4F1 XF2 X XF3 X XF4 X X

稍后:

F P 值F1 P1 XF1 P2F1 P3F1 P4F2 P1 XF2 P2 XF2 P3F2 P4F3 P1F3 P2 XF3 P3F3 P4 XF4 P1F4 P2 XF4 P3 XF4 P4

解决方案

要反向旋转"、反旋转"或展平":

1. 对于 Excel 2003:激活汇总表中的任何单元格并选择数据 - 数据透视表和数据透视图:

对于更高版本，使用 Alt+D、P 访问向导.

对于 Mac 2011 的 Excel，它是 ⌘+Alt+P ( 上有一个关于同一主题的视频，我认为它的质量非常好.

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

解决方案

To "reverse pivot", "unpivot" or "flatten":

1. 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).

2. Select Multiple consolidation ranges and click Next.

3. In "Step 2a of 3", choose I will create the page fields and click Next.

4. 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.

5. In "Step 3 of 3", select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily):

6. Click Finish to create the pivot table:

7. Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or 7):

8. The PT may now be deleted.

9. The resulting Table may be converted to a conventional array of cells by selecting Table in the Quick Menu (right-click in the Table) and Convert to Range.

---

There is a video on the same subject at Launch Excel which I consider excellent quality.

这篇关于将矩阵转换为 3 列表('reverse pivot'、'unpivot'、'flatten'、'normalize')的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！