Convert matrix to 3-column table ('reverse piv

2018-12-31 00:38发布

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

3条回答
呛了眼睛熬了心
2楼-- · 2018-12-31 01:13

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.

enter image description here

Select any cell in the table and on the Power Query ribbon click "From Table".

enter image description here

This will open the table in the Power Query Editor window.

enter image description here

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.

enter image description here

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.

enter image description here

Now to the good part. Add some data to your source table, for example

enter image description here

Click on the sheet with the Power Query result table and on the Data ribbon click Refresh all. You will see something like:

enter image description here

Power Query is not just a one-time transformation. It is repeatable and can be linked to dynamically changing data.

查看更多
高级女魔头
3楼-- · 2018-12-31 01:18

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.

enter image description here

查看更多
回忆,回不去的记忆
4楼-- · 2018-12-31 01:23

To “reverse pivot”, “unpivot” or “flatten”:

  1. For Excel 2003: Activate any cell in your summary table and choose Data - PivotTable and PivotChart Report:

    SO20541905 first example

For later versions access the Wizard with Alt+D, P.

For Excel for Mac 2011, it's +Alt+P (See here).

  1. Select Multiple consolidation ranges and click Next.

    SO20541905 second example

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

    SO20541905 third example

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

    SO20541905 fourth example

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

    SO20541905 fifth example

  5. Click Finish to create the pivot table:

    SO20541905 sixth example

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

    SO20541905 seventh example

  7. The PT may now be deleted.

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

查看更多
登录 后发表回答