合并不同的费用列以创建数据透视表损益表? [英] Combining different fees columns to create PivotTable income statement?

问题描述

我有一个表格，其中记录了每种产品的收入，并分为收入类型:价格，费用，税金等.

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?

推荐答案

以下是您必须执行的步骤:

Here are the steps you have to do:

(1)输入数据.

(2)转到Data选项卡，然后选择From Table/Range.

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

(3)使用查询编辑器将打开一个新窗口.在这里，您需要转到Transform选项卡，选择要unpivot的三列，然后单击Unpivot Columns.

(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)结果如下(在查询编辑器中显示)

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

(5)现在，您可以使用Home选项卡上的Close & Load To...将此数据Load放到工作表中

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

(6)选择选项以在数据透视表中显示数据，并指示在单击OK

(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)将各个字段拖到相应的框中后，最终结果应该类似于以下内容:乘积和属性到行中，而值进入值框中

(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

这篇关于合并不同的费用列以创建数据透视表损益表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！