如何在Excel中使用数据透视表计算百分比变化

古城微笑少年丶 2021-07-26 23:20 1940阅读 0赞

![excel logo][]

Pivot Tables are an amazing built-in reporting tool in Excel. While typically used to summarize data with totals, you can also use them to calculate the percentage of change between values. Even better: It is simple to do.

数据透视表是Excel中一个了不起的内置报表工具。 虽然通常用于汇总汇总数据，但您也可以使用它们来计算值之间的变化百分比。 更好的是：这很简单。

You could use this technique to do all kinds of things—pretty much anywhere you’d like to see how one value compares to another. In this article, we’re going to use the straightforward example of calculating and displaying the percent by which the total sales value changes month by month.

您可以使用这种技术来做各种各样的事情-您想在任何地方看到一个值如何与另一个值进行比较。 在本文中，我们将使用简单的示例来计算和显示总销售额逐月变化的百分比。

Here’s the sheet we’re going to use.

这是我们要使用的表。

![Two years of sales data for a PivotTable][]

It’s a pretty typical example of a sales sheet that shows the order date, customer name, sales rep, total sales value, and a few other things.

这是一个非常典型的销售表示例，其中显示了订单日期，客户名称，销售代表，总销售价值以及其他一些内容。

To do all this, we’re first going to format our range of values as a table in Excel and we’re then going to create a Pivot Table to make and display our percentage change calculations.

为此，我们首先将值的范围格式化为Excel中的表格，然后创建数据透视表以进行和显示百分比变化计算。

## 将范围格式化为表格 **(**Formatting the Range as a Table**)** ##

If your data range is not already formatted as a table, we’d encourage you to do so. Data stored in tables have multiple benefits over data in cell ranges of a worksheet, especially when using PivotTables ([read more about the benefits of using tables][]).

如果您的数据范围尚未格式化为表格，建议您这样做。 与工作表的单元格区域中的数据相比，存储在表中的数据具有多种好处，尤其是在使用数据透视表时( [更多有关使用表的好处][read more about the benefits of using tables] )。

To format a range as a table, select the range of cells and click Insert > Table.

要将范围格式化为表格，请选择单元格范围，然后单击插入>表格。

![Create Table dialog to specify the range of cells][]

Check that the range is correct, that you do have headers in the first row of that range, and then click “OK.”

检查范围是否正确，该范围的第一行中是否有标题，然后单击“确定”。

The range is now formatted as a table. Naming the table will make it easier to refer to in the future when creating PivotTables, charts, and formulas.

现在，范围已格式化为表格。 命名表将使将来创建数据透视表，图表和公式时更容易引用。

Click the “Design” tab under Table Tools, and enter a name in the box provided at the start of the Ribbon. This table has been named “Sales.”

单击“表格工具”下的“设计”选项卡，然后在功能区开头提供的框中输入名称。 该表已命名为“销售”。

![Name the Table in Excel][]

You can also change the style of the table here if you want.

您还可以根据需要在此处更改表格的样式。

## 创建数据透视表以显示百分比变化 **(**Create a PivotTable to Display Percentage Change**)** ##

Now let’s get on with creating the PivotTable. From within the new table, click Insert > PivotTable.

现在，让我们继续创建数据透视表。 在新表中，单击插入>数据透视表。

The Create PivotTable window appears. It will have automatically detected your table. But you could select the table or range you want to use for the PivotTable at this point.

出现“创建数据透视表”窗口。 它将自动检测到您的表。 但是，此时您可以选择要用于数据透视表的表或范围。

![The Create PivotTable window][]

### 将日期分组为月 **(**Group the Dates into Months**)** ###

We will then drag the date field that we want to group by into the rows area of the PivotTable. In this example, the field is named Order Date.

然后，将要分组的日期字段拖到数据透视表的行区域中。 在此示例中，该字段名为“订购日期”。

From Excel 2016 on, date values are automatically grouped into years, quarters and months.

从Excel 2016开始，日期值将自动分组为年，季度和月。

If your version of Excel does not do this, or you simply want to change the grouping, right-click a cell containing a date value and then select the “Group” command.

如果您的Excel版本没有这样做，或者您只是想更改分组，请右键单击包含日期值的单元格，然后选择“分组”命令。

![Group dates in a PivotTable][]

Select the groups you want to use. In this example, only Years and Months are selected.

选择您要使用的组。 在此示例中，仅选择了年和月。

![Specifying Years and Months in the Group dialog][]

The year and month are now fields which we can use for analysis. The months are still named as Order Date.

现在，年和月是我们可以用于分析的字段。 这些月份仍被命名为订购日期。

![Years and Order Date fields in Rows][]

### 将值字段添加到数据透视表 **(**Add the Value Fields to the PivotTable**)** ###

Move the Year field from Rows and into the Filter area. This enables the user to filter the PivotTable for a year, rather than clutter the PivotTable with too much information.

将“年份”字段从“行”移到“过滤器”区域。 这使用户可以过滤数据透视表一年，而不必将太多的信息弄乱数据透视表。

Drag the field containing the values (Total sales Value in this example) you want to calculate and present change into the Values area **twice**.

将包含要计算的值的字段(在此示例中为Total sales Value)拖动到Values区域**两次** 。

It might not look like much yet. But that will change very soon.

看起来可能还不多。 但这很快就会改变。

![Sales value field added twice to the PivotTable][]

Both value fields will have defaulted to sum and currently have no formatting.

这两个值字段都将默认为求和，并且当前没有格式。

The values in the first column we would like to keep as totals. They do however require formatting.

我们希望将第一列中的值保留为总计。 但是，它们确实需要格式化。

Right-click on a number in the first column and select “Number Formatting” from the shortcut menu.

右键单击第一列中的数字，然后从快捷菜单中选择“数字格式”。

Choose the “Accounting” format with 0 decimals from the Format Cells dialog.

从“格式单元格”对话框中选择带有0位小数的“会计”格式。

The PivotTable now looks like this:

现在，数据透视表如下所示：

![Formatting the first column][]

### 创建百分比变化列 **(**Create the Percentage Change Column**)** ###

Right-click on a value in the second column, point to “Show Values,” and then click the “% Difference from” option.

右键单击第二列中的值，指向“显示值”，然后单击“差异百分比”选项。

![Show values as a percentage difference][]

Select “(Previous)” as the Base Item. This means that the current month value is always compared to the previous months (Order Date field) value.

选择“(上一个)”作为基本项目。 这意味着始终将当前月份的值与上个月(“订购日期”字段)的值进行比较。

![Select Previous as the base item to compare to][]

The PivotTable now shows both the values and the percentage change.

数据透视表现在同时显示值和百分比变化。

![Show values and percentage change][]

Click in the cell containing Row Labels and type “Month” as the header for that column. Then click in the header cell for the second values column and type “Variance”.

单击包含“行标签”的单元格，然后键入“月”作为该列的标题。 然后单击第二个值列的标题单元格，然后输入“ Variance”。

![Rename the headers of the PivotTable][]

### 添加一些方差箭头 **(**Add Some Variance Arrows**)** ###

To really polish off this PivotTable, we would like to visualize the percentage change better by adding some green and red arrows.

为了真正完善此数据透视表，我们希望通过添加一些绿色和红色箭头来更好地可视化百分比变化。

These will provide us with a lovely way of seeing whether a change has been positive or negative.

这些将为我们提供一种观察变化是正面还是负面的好方法。

Click on any one of the values in the second column and then click Home > Conditional Formatting > New Rule. In the Edit Formatting Rule window that opens, take the following steps:

单击第二列中的任何值，然后单击“主页”>“条件格式”>“新规则”。 在打开的“编辑格式规则”窗口中，执行以下步骤：

1.  Select the “All cells showing “Variance” values for Order Date” option.
    
    选择“所有显示“订单日期差异”值的单元格”选项。
2.  Select “Icon Sets” from the Format Style list.
    
    从格式样式列表中选择“图标集”。
3.  Select the red, amber and green triangles from the Icon Style list.
    
    从“图标样式”列表中选择红色，琥珀色和绿色三角形。
4.  In the Type column, change the list option to say “Number” instead of Percentage. This will change the Value column to 0’s. Exactly what we want.
    
    在“类型”列中，将列表选项更改为“数字”，而不是“百分比”。 这会将“值”列更改为0。 正是我们想要的。

![Applying variance icons with Conditional Formatting][]

Click “OK” and the Conditional Formatting is applied to the PivotTable.

单击“确定”，将条件格式应用于数据透视表。

![The completed variance PivotTable][]

PivotTables are an incredible tool and one of the simplest ways to display the percentage change over time for values.

数据透视表是一种不可思议的工具，也是显示值随时间变化百分比的最简单方法之一。

> 翻译自: [https://www.howtogeek.com/407781/calculate-percentage-change-to-previous-with-pivottables/][https_www.howtogeek.com_407781_calculate-percentage-change-to-previous-with-pivottables]

[excel logo]: /images/20210726/e460f674d6b54ff6a9adb4fad95d933c.png
[Two years of sales data for a PivotTable]: /images/20210726/e28aaca2f56d432884bd539c8ca50e48.png
[read more about the benefits of using tables]: https://www.linkedin.com/pulse/9-reasons-you-need-using-excel-tables-alan-murray/
[Create Table dialog to specify the range of cells]: /images/20210726/a4df7d8c61584684922eea73398c1eb5.png
[Name the Table in Excel]: /images/20210726/3224bf2af809448c80357b4635c04f75.png
[The Create PivotTable window]: /images/20210726/3746bf9be40740cdaec44d75cbc07f3f.png
[Group dates in a PivotTable]: /images/20210726/2ca083036f544cb3a0f94908e1511a6f.png
[Specifying Years and Months in the Group dialog]: /images/20210726/6da822d3ed3b4b62b7915ae65445b12e.png
[Years and Order Date fields in Rows]: /images/20210726/cb327ce257f2454f95aac9950b5aa302.png
[Sales value field added twice to the PivotTable]: /images/20210726/dad5c5c6081f47389c69c07c31620c62.png
[Formatting the first column]: /images/20210726/04543be1457f4b83bfc48fc0d1cc5ee4.png
[Show values as a percentage difference]: /images/20210726/8192bb2ec728475c8beb4a7e44d27d4f.png
[Select Previous as the base item to compare to]: /images/20210726/e183cd2c6f55418ab21cf83dd8b410c6.png
[Show values and percentage change]: /images/20210726/a3f69d0054ac4cb7946c5ab703454fa5.png
[Rename the headers of the PivotTable]: /images/20210726/1799a38937e94ad0bcf5bdf59885e880.png
[Applying variance icons with Conditional Formatting]: /images/20210726/ac42b0fc46284787857b8953383009a2.png
[The completed variance PivotTable]: /images/20210726/59a4c6fa81704de79d93bc7579ff8e23.png
[https_www.howtogeek.com_407781_calculate-percentage-change-to-previous-with-pivottables]: https://www.howtogeek.com/407781/calculate-percentage-change-to-previous-with-pivottables/