数据透视表计数不对_将丢失的数据透视表数据计数为零

数据透视表计数不对

How can you get missing data to show up in your Excel pivot table, showing a count of zero? AlexJ encountered this problem recently, and sent me his solution, to share with you.

您如何才能将丢失的数据显示在Excel数据透视表中 (计数为零)？ AlexJ最近遇到了这个问题，并向我发送了他的解决方案，与您分享。

One problem with pivot tables is that you can't use them to report on data that is not there. What if you need to report on a category of data when that has not been reported to your data set?

数据透视表的一个问题是您不能使用它们来报告不存在的数据。 如果尚未报告数据类别的数据时需要报告该怎么办？

健康与安全报告 (Health and Safety Report)

For example, in this Health and Safety incidents data, we record the date, department and type of report for each incident.

例如，在此健康与安全事件数据中，我们记录每个事件的日期，部门和报告类型。

This data is from the first quarter, and when we create a pivot table from the data, two of the departments are missing, because no incidents occurred there. That's good news, of course, and you'd like to highlight that, by including the departments in the pivot table.

该数据来自第一季度，当我们根据该数据创建数据透视表时，缺少两个部门，因为那里没有发生任何事件。 当然，这是个好消息，您想通过在数据透视表中包含部门来突出显示这一点。

添加丢失的数据 (Add the Missing Data)

The departments will only appear in the pivot table if they're in the source data, so the first step is to add some dummy records to the data. You could add one dummy record for each department, or just the missing departments, as shown below.

仅当部门位于源数据中时，它们才会出现在数据透视表中，因此第一步是向数据中添加一些虚拟记录。 您可以为每个部门或缺失的部门添加一个虚拟记录，如下所示。

Now, when the pivot table is refreshed, the missing departments appear, but with blank cells in the Count of ID column. That's close to what we'd like, but it would be preferable to show a zero there.

现在，刷新数据透视表时，将显示缺少的部门，但“计数”列中的单元格为空白。 这接近我们想要的值，但是最好在此处显示零。

We don't want to create an ID number for these dummy items, but the pivot table can't count the blank cells. To give the pivot table something to count, AlexJ entered a letter in the ID column, instead of a number.

我们不想为这些虚拟项目创建ID号，但是数据透视表无法计算空白单元格。 为了给数据透视表计数，AlexJ在ID栏中输入了字母，而不是数字。

With that change, a number shows up in the Count of ID column, but we want the number to be 0, not 1. Close, but those departments, don't want dummy records besmirching their incident-free safety records.

进行此更改后，“ ID计数”列中将显示一个数字，但我们希望该数字为0，而不是1。关闭，但那些部门不希望虚拟记录覆盖其无事故安全记录。

更改摘要功能 (Change the Summary Function)

The worksheet functions include COUNTA, which counts all non-blank cells in a range, and COUNT, which only counts cells with numbers. There are similar functions in a pivot table, but it's confusing, because the pivot table COUNT is like the worksheet COUNTA, and counts text too.

工作表功能包括COUNTA(计数一个范围内的所有非空白单元格)和COUNT(仅计数带数字的单元格)。 枢纽分析表中有类似的功能，但令人困惑，因为枢纽分析表COUNT与工作表COUNTA一样，也对文字进行计数。

Instead of using Count, you can change the summary function to one that only counts numbers. Then, those letters in the ID column won't be counted.

您可以将摘要功能更改为仅对数字进行计数的功能，而不是使用“计数”。 这样一来，ID列中的字母将不会计入。

To change the summary function:

更改摘要功能：

• Select a cell in the Count of ID column of the pivot table.
  在数据透视表的ID计数列中选择一个单元格。
• On the Ribbon, under PivotTable Tools, click the Options tab
  在功能区上，在“数据透视表工具”下，单击“选项”选项卡
• In the Active Field group, click Field Settings
  在活动字段组中，单击字段设置

In the Value Field Settings dialog box, in the list of functions, click on Count Numbers, then click OK

在“值字段设置”对话框的功能列表中，单击“计数编号”，然后单击“确定”。

The pivot table now shows a zero for the departments with missing data, instead of a blank cell or an incorrect count.

对于数据缺失的部门，数据透视表现在显示为零，而不是空白单元格或计数不正确。

Thanks AlexJ, for sharing this solution to the problem of missing pivot table data.

感谢AlexJ，感谢您分享此解决方案，以解决数据透视表数据丢失的问题。

翻译自: https://contexturesblog.com/archives/2010/06/14/count-missing-pivot-table-data-as-zero/

数据透视表计数不对