什么是Google表格中的数据透视表以及如何使用它们

Pivot tables let you analyze large amounts of data and narrow down large data sets to see the relationships between data points. Google Sheets uses pivot tables to summarize your data, making it easier to understand all the information contained in your spreadsheet.

数据透视表可让您分析大量数据并缩小大型数据集以查看数据点之间的关系。 Google表格使用数据透视表来汇总您的数据，从而更易于理解电子表格中包含的所有信息。

什么是数据透视表？ (What Are Pivot Tables?)

Pivot tables are handy for analyzing massive amounts of data. Where a regular spreadsheet uses only two axes—columns and rows—pivot tables help us make sense of the information in your spreadsheet by summarizing any selected columns and rows of data. For example, a pivot table could be used to analyze sales brought in by divisions of a company for a specific month, where all the information is randomly entered into a dataset.

数据透视表非常适合分析大量数据。 在常规电子表格仅使用两个轴(列和行)的情况下，数据透视表通过汇总数据的任何选定列和行来帮助我们理解电子表格中的信息。 例如，数据透视表可用于分析公司各部门在特定月份的销售量，其中所有信息都随机输入到数据集中。

Creating a pivot table from the information in the picture above displays a neatly formatted table with information from selected columns, sorted by division.

根据上图中的信息创建数据透视表，将显示格式整齐的表，其中包含来自选定列的信息，并按分区进行排序。

如何创建数据透视表 (How to Create a Pivot Table)

Fire up Chrome and open a spreadsheet in Google Sheets.

启动Chrome并在Google表格中打开电子表格。

Next, select any of the cells you want to use in your pivot table. If you’re going to use everything in your dataset, you can click anywhere on the spreadsheet, you don’t have to select every cell first.

接下来，选择要在数据透视表中使用的任何单元格。 如果要使用数据集中的所有内容，则可以单击电子表格上的任意位置，而不必先选择每个单元格。

Note: Each column selected must have a header associated with it to create a pivot table with those data points.

注意：所选的每一列都必须具有与之关联的标题，以使用这些数据点创建数据透视表。

On the menu bar at the top of the page, click “Data,” then click “Pivot Table.”

在页面顶部的菜单栏上，单击“数据”，然后单击“数据透视表”。

If the new table doesn’t open automatically, click “Pivot Table,” located at the bottom of your spreadsheet.

如果新表格未自动打开，请单击电子表格底部的“数据透视表”。

如何编辑数据透视表 (How to Edit a Pivot Table)

From the pivot table sheet, the side panel lets you add rows, columns, values, and filters for viewing your data. Sometimes, Sheets offers up suggestions based on the information you chose. Click a suggestion or click “Add,” located next to any of the other options below.

在数据透视表中，侧面板使您可以添加行，列，值和过滤器以查看数据。 有时，Sheets会根据您选择的信息提供建议。 单击建议或单击下面任何其他选项旁边的“添加”。

When you click on any of the suggestions, Sheets automatically builds your pivot table using the option you selected from the list given.

单击任何建议时，Sheets都会使用从给定列表中选择的选项自动构建数据透视表。

If you’d rather customize a pivot table to for your own needs, click any of the “Add” buttons next to the four options below. Each option has a different purpose, here’s what they mean:

如果您要根据自己的需要自定义数据透视表，请点击下面四个选项旁边的“添加”按钮。 每个选项都有不同的用途，它们的含义如下：

• Rows: Adds all unique items of a specific column from your dataset to your pivot table as row headings. They are always the first data points you see in your pivot table in light grey on the left.

  行：将数据集中特定列的所有唯一项添加到数据透视表中作为行标题。 它们始终是您在数据透视表中看到的第一个数据点，左侧为浅灰色。

• Columns: Adds selected data points (headers) in aggregated form for each column in your table, indicated in the dark grey along the top of your table.

  列：以汇总的形式为表中的每一列添加选定的数据点(标题)，并以表顶部的深灰色表示。

• Values: Adds the actual values of each heading from your dataset to sort on your pivot table.

  值：添加数据集中每个标题的实际值，以对数据透视表进行排序。

• Filter: Adds a filter to your table to show only data points meeting specific criteria.

  过滤器：将过滤器添加到表中以仅显示满足特定条件的数据点。

Click on “Add” next to Rows and add in the rows you want to display in your pivot table. For this example, we’ll be adding division and subdivision.

单击行旁边的“添加”，然后添加要显示在数据透视表中的行。 对于此示例，我们将添加除法和细分。

Next, click “Add” next to Values As and insert the values you want to sort information. We’ll be using the sum of the number of units sold and the average price per unit.

接下来，单击“值作为”旁边的“添加”，然后插入要对信息进行排序的值。 我们将使用已售出的单位数量和每单位平ASP格的总和。

To change the sorting of each unit, click the drop-down menu, located under the heading “Summarise by.” You can choose from the sum, count, average, min, max, among others listed below.

要更改每个单位的排序，请点击位于“汇总依据”标题下的下拉菜单。 您可以从总和，计数，平均值，最小值，最大值中选择，以及以下列出的其他内容。

After adding all rows, columns, values, etc. what we’re left with is an easy to read pivot table that outlines which division sold the most units and the average cost of all units sold.

在添加所有行，列，值等之后，剩下的是易于阅读的数据透视表，该数据透视表概述了哪个部门售出了最多的单位以及所有售出的单位的平均成本。

If you’d prefer to make your own formula, click “Add” next to the Values as heading, then click “Calculated Field.”

如果您希望自己创建公式，请单击“值”标题旁边的“添加”，然后单击“计算字段”。

From the new value field, enter a formula that best summarises the data in your pivot table.

在新值字段中，输入最能汇总数据透视表中数据的公式。

If you want to add a filter to your table, click “Add,” located next to the Filters heading.

如果要向表中添加过滤器，请单击“过滤器”标题旁边的“添加”。

When adding a filter to your table, select—or deselect—the values you want to show on your table, then click “OK” to apply the filter.

在向表添加过滤器时，选择(或取消选择)要在表上显示的值，然后单击“确定”以应用过滤器。

That’s all there is to it. Although this is just an introduction to using pivot tables, there is a seemingly endless amount of possibilities for utilizing this feature that not many people know much about.

这里的所有都是它的。 尽管这只是使用数据透视表的介绍，但似乎没有多少人知道很多利用这种功能的可能性。

翻译自: https://www.howtogeek.com/407943/what-are-pivot-tables-in-google-sheets-and-how-do-i-use-them/