如何将数据透视表中的列名称视为数据透视图中的整数(Excel) [英] How to treat pivot table column names as integer in pivot chart (Excel)

问题描述

我正在尝试在条形图中绘制数据.在x轴上，我要显示答案类别(1-9)，在y轴上，我要显示选择答案的次数.并非所有类别都被选中，因此会产生以下数据透视表:

I am trying to plot data in a bar-chart. On the x-axis I want to display the answer category (1-9) and on the y-axis the amount of times it has been chosen. Not every category has been selected, which results in the following pivot table:

        Column Labels                       
        1   4   5   6   7   8   Grand Total
Count   2   12  90  48  56  8   216

当我绘制这些数据时，我得到下图:

When I plot this data, I get the following figure:

问题是，数据透视表忽略了两个类别之间的距离.换句话说，该图忽略了X轴上应该有2、3和9(计数= 0)的事实.

The problem is, the pivot table ignores the distance between two categories. In other words, the graph ignores the fact that there are supposed to be a 2, 3, and 9 (with count = 0) on the X-axis.

如何在不牺牲数据透视图和表格的多功能性的情况下获取数据透视表以添加缺少的类别?

原始数据

我只是将数据组织在表的列中.在该列(工作负荷)中，值可以出现一次，多次或根本不出现.我确实找到了一个非透视表解决方案，其中仅使用了Countif.

I simply organized the data in a column of a table. In that column (workload), a value can occurs once, multiple times, or not at all. I did find a non-pivot-table solution in which I simply used Countif.

Department   Workload     ...    Col_n
2            5            ...
3            5
2            7
1            1
2            6
1            4
...          ...

推荐答案

这是一个好问题，要正确回答，将需要访问您的原始数据(数据透视表将它们取到那里).但是，我将为您提供一个一般情况，我认为您可以对其进行一些更改以适应您的需求.

This is a good question and to answer it correvtly will need access to your original data (where the pivot takes them). However, I will give you a general case and I think you could change it a little to fit your needs.

数据透视表的问题在于您不能使用它们来报告不存在的数据.因此，您可以采取的措施就是添加缺少的数据.

The problem with pivot tables is that you can’t use them to report on data that is not there. So what you can do to fix that is to add the Missing Data.

因此，第一步是向数据中添加一些虚拟记录.您可以为每个缺少的类别添加一个虚拟记录.

So the first step is to add some dummy records to the data. You could add one dummy record for each category that is missing.

现在，刷新数据透视表时，应显示缺少的类别，但计数"中将包含空白单元格.这接近我们想要的值，但最好在此处显示零.

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

我们应该给数据透视表一些计数，以便您可以添加"X"或-".

We should give the pivot table something to count so you could add "X" or "-".

进行此更改后，"ID计数"列中将显示一个数字，但我们希望该数字为0，而不是1.

With that change, a number shows up in the Count of ID column, but we want the number to be 0, not 1.

现在您应该更改更改摘要功能

Now you should change change the Summary Function

工作表功能包括COUNTA(计数一个范围内的所有非空白单元格)和COUNT(仅计数带数字的单元格).数据透视表中有类似的功能，但令人困惑，因为数据透视表COUNT与工作表COUNTA类似，并且也对文本进行计数.

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.

您可以将汇总功能更改为仅对数字进行计数的功能，而不是使用计数".然后，您计数的列中的那些字符将不计算在内.

Instead of using Count, you can change the summary function to one that only counts numbers. Then, those characters in the column you count will not be counted.

要更改摘要功能，请执行以下操作:

To change the summary function:

1. 在数据透视表的计数"列中选择一个单元格.
2. 在功能区上，在数据透视表工具"下，单击选项"选项卡
3. 在活动字段"组中，单击字段设置"

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

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 categories with missing data, instead of a blank cell or an incorrect count.

这篇关于如何将数据透视表中的列名称视为数据透视图中的整数(Excel)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！