数据透视表缺失值_更改数据透视表中的值

最新推荐文章于 2021-10-19 20:22:23 发布

culiao2169

最新推荐文章于 2021-10-19 20:22:23 发布

阅读量1.3k

点赞数

文章标签： java 大数据 python 数据分析机器学习

原文链接：https://contexturesblog.com/archives/2011/03/16/change-values-in-a-pivot-table/

版权

数据透视表缺失值

One of my clients uses a pivot table to summarize product sales, using sales data from their accounting system. Occasionally, they'd like to type a number in the pivot table, but Excel won't let you change values in a pivot table. Here is a workaround for that limitation.

我的一位客户使用数据透视表来汇总产品销售，并使用其会计系统中的销售数据。有时，他们想在数据透视表中键入数字，但Excel不允许您更改数据透视表中的值。这是解决该限制的方法。

数据透视表销售报告 (Pivot Table Sales Report)

Occasionally, the client's salespeople send product samples to a customer, and those sample quantities aren't included in the sales data. They'd like to have those numbers in the printed report though, which is based on a pivot table.

有时，客户的销售人员将产品样本发送给客户，而这些样本数量不包含在销售数据中。他们希望将这些数字显示在基于透视表的打印报告中。

For a quick solution, they'd like to enter the sample quantities in the Values area of the pivot table, instead of creating records in the source data.

为了快速解决方案，他们希望在数据透视表的“值”区域中输入样本数量，而不是在源数据中创建记录。

However, if you try to type in a cell in the Values area, an error message appears -- Cannot change this part of a PivotTable report.

但是，如果您尝试在“值”区域中键入单元格，则会出现错误消息-无法更改数据透视表报表的此部分。

创建计算项目 (Create a Calculated Item)

Although you can't type in most cells in the Values area, you can type in cells that contain calculated items. To allow manual entries for the sample quantities, you could create a calculated item with the name Samples, as described below.

尽管您无法在“值”区域中键入大多数单元格，但可以键入包含计算项的单元格。要允许手动输入样品数量，您可以创建一个名为“样品”的计算项目，如下所述。

要为样本创建计算项目： (To create a calculated item for Samples:)

Select one of the label cells for the product category field. If you don't select one of these cells, you won't be able to create a calculated item for that field.
为产品类别字段选择标签单元之一。如果您没有选择这些单元格之一，则将无法为该字段创建计算项。
On the Ribbon's Options tab, in the Tools group, click Formulas, and then click Calculated Item.
在功能区的“选项”选项卡上的“工具”组中，单击“公式”，然后单击“计算项”。
As a name for the calculated item, type Samples.
作为计算项目的名称，键入Samples。
Leave the default formula of =0, and then click OK.
保留默认公式= = 0，然后单击“确定”。

更改值 (Change the Values)

After you create the calculated item, it's automatically added to the pivot table, and you can change the values.

创建计算出的项目后，它会自动添加到数据透视表中，并且您可以更改值。

更改值： (To change a value:)

In the pivot table, select one of the calculated item cells
在数据透视表中，选择一个计算的项目单元格
Type the number of samples you sent to that store, and press the Enter key
输入您发送到该商店的样本数量，然后按Enter键
The totals will change, to include the typed numbers.
总数将改变，包括键入的数字。

NOTE: If you delete the number in a calculated item's cell, you won't be able to make any further changes to that cell. Type a zero instead of pressing the Delete key, and you will be able to edit the cell again later.

注意：如果删除计算项目的单元格中的数字，则将无法对该单元格进行任何进一步的更改。 键入零而不是按Delete键，以后您将可以再次编辑该单元格。