Combining different fees columns to create PivotTa

问题:

This question already has an answer here:

• Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize') 3 answers

I have a table which records revenue for each product, split into type of revenue: price, fees, tax etc.

To analyze the data I'd like to build a PivotTable to show the revenue breakdown, e.g.:

So to build this PivotTable I need to somehow create an intermediate table of the following format so the PivotTable can be used:

My question is, how do I create a PivotTable from the original table automatically without manually constructing the second table?

回答1:

Here are the steps you have to do:

(1) Enter the data.

(2) Go to the Data tab and select From Table/Range.

(3) A new window will open up with the Query Editor. Here you need to go to the Transform tab, select the three columns you wish to unpivot and then click Unpivot Columns.

(4) The result will be the following (shown in the Query Editor)

(5) Now you can Load this data to the sheet using Close & Load To... on the Home tab

(6) Select the option to show the data in a pivot table and indicate where the pivot table should be placed before clicking OK

(7) The final result should look something like this once you have dragged the various field into the respective boxes: product and attribute into rows while values goes into the values box