excel数据透视表_标准化Excel数据透视表的数据

excel数据透视表

If your Excel data is in monthly columns, like the worksheet shown below, you'll have trouble setting up a flexible pivot table. Instead of leaving the data like this, see how to normalize data for Excel pivot table setup.

如果您的Excel数据在每月列中，如下面所示的工作表，您将很难设置灵活的数据透视表。 而不是像这样保留数据，请参阅如何为Excel数据透视表设置标准化数据。

Data in Monthly Columns

每月列中的数据

切换到垂直布局 (Switch to a Vertical Layout)

Instead of multiple columns with sales amounts, rearrange your data into a single column of amounts. Adjacent columns will show the product name and month. This process will "normalize data" for Excel pivot table setup, and makes it easier to work with.

可以将您的数据重新排列到一个数量列中，而不是用多个列显示销售量。 相邻的列将显示产品名称和月份。 此过程将为Excel数据透视表设置“标准化数据”，并使其更易于使用。

The steps below explain how to do this quickly -- you don't have to manually rearrange the data!

以下步骤说明了如何快速执行此操作-您无需手动重新排列数据！

用数据透视技巧重新排列数据 (Rearrange the Data With a Pivot Trick)

Because the original data, with 12 columns for months, looks almost like a completed pivot table, we want to "unpivot" the data, or "normalize" it.

因为原始数据(包含12个月的列)看起来几乎像一个完整的数据透视表，所以我们想“取消数据透视”或“标准化”数据。

[Update] If you have Excel 2010 or later, use Get & Transform, or Power Query, to quickly "unpivot" the data. If those tools aren't available in your version of Excel, follow the instructions below.

[更新]如果您具有Excel 2010或更高版本，请使用Get＆Transform或Power Query快速“取消透视”数据。 如果您的Excel版本中没有这些工具，请按照以下说明进行操作。

To change the data layout, you can create a Multiple Consolidation Ranges pivot table, as shown in the video below. Usually that type of pivot table is used for combining data on different sheets, but it has the side benefit of changing horizontal data into a vertical layout.

要更改数据布局，您可以创建一个“ 多个合并范围”数据透视表 ，如下面的视频所示。 通常，这种类型的数据透视表用于组合不同工作表上的数据，但是它具有将水平数据更改为垂直布局的附带好处。

• After you create the pivot table from multiple consolidation ranges, double-click on the Grand Total cell.
  从多个合并范围创建数据透视表后，双击“总计”单元格。
• That will extract the source data onto a new worksheet, with all the monthly amounts in a single column.
  这样会将源数据提取到新的工作表中，所有月度金额都放在一个列中。
• You can change the column headings in the extracted data -- Product, Month and Amount are the new headings in this example.
  您可以在提取的数据中更改列标题-在此示例中，“产品”，“月”和“金额”是新标题。
• Then, build a new pivot table, from the normalized data. Instead of having 12 value fields (one for each month), you will have one value field -- Amount.
  然后，根据规范化数据构建新的数据透视表。 而不是拥有12个值字段(每个月一个)，您将拥有一个值字段-金额。

视频：标准化Excel数据透视表的数据 (Video: Normalize Data for Excel Pivot Table)

This Normalize Data for Excel Pivot Table video shows the steps for changing the data layout to create a flexible pivot table.

此“ Excel数据透视表的数据标准化”视频显示了更改数据布局以创建灵活的数据透视表的步骤。

演示地址

标准化Excel数据透视表工作簿的数据 (Normalize Data for Excel Pivot Table Workbook)

To test the Multiple Consolidation Ranges pivot table technique, you can download the sample file from my Contextures website. Go to the Fix Pivot Table Source Data Layout page, and go to the Download section.

若要测试“多个合并范围”数据透视表技术，可以从Contextures网站下载示例文件。 转到“ 固定数据透视表源数据布局”页面 ，然后转到“下载”部分。

The file is in xlsx format, and zipped. There are no macros in the workbook. _____________________

该文件为xlsx格式，并已压缩。 工作簿中没有宏。 _____________________

翻译自: https://contexturesblog.com/archives/2011/07/20/normalize-data-for-excel-pivot-table/

excel数据透视表