有很多同学在使用数据透视表汇总数据时,添加数值类型的字段到数据透视表的数值区域时,值汇总方式总是默认为“计数”,可是我们明明想要的是“求和”啊!有十几个甚至更多个字段,难道要让我添加完之后再一个一个地改么?
其实出现这样的问题跟我们的数据源有关。
如果我们的数据源中某一列存在空白单元格、文本型数字或者错误值,那么你在把这列数据添加到数据透视表的值区域的时候,它默认的汇总方式就是“计数”。
看下图,分别列出了文本型数字、错误值、空白单元格的示例。
注意,文本型数字所在的单元格左上角有个绿色的小三角形。
明白了问题所在,那么我们就很容易解决了。
1、 处理错误值
对于查找和替换符合一定特征的内容,我们可以尝试使用”定位“的方式。
首先,我们选择数据源的数值区域,然后按F5键或者Ctrl G。
在弹出的“定位”对话框中点击“定位条件”按钮来选择定位条件。
在图片所示的选项中,选择第1个框中的“常量”单选按钮,同时勾选第2个框中的“错误”复选框,点“确定”按钮就可以找出并选中常量型错误值所在的单元格。
这里的“常量”是指固定值,而不是公式的计算结果。
类似的道理,选择第1个框中的“公式”,同时勾选第2个框中的“错误”,点“确定”按钮就可以找出并选中公式计算结果为错误值的单元格。
然后我们紧接着输入0,再同时按Ctrl Enter键,这样所有的错误值都全部替换为0了。
这里有一个小技巧,同时按Ctrl Enter键可以快速复制、填充内容。
2、 处理空白单元格
同样用”定位“操作,我们在”定位条件“中选择第3个框中的”空值“,点”确定“按钮后就可以找到并选中所有的空白单元格,我们同样可以直接输入0,然后按Ctrl Enter键来将选中的单元格快速填充成0。
3、 处理文本型数据
对于文本型数据,在数据量庞大的时候,我们可能不太好发现这种数据。
我同样可以用”定位“的方式来查找。
在“定位条件”中,我们在上图的第1个框中选择”常量“或”公式“,在第二个框中选择”文本“,点”确定“按钮,如果找到了文本型数据,我们就需要把这些数据转换成数值;
如果提示”未找到单元格“,那么就说明数据区域不存在文本型数据,我就可以继续进行下一步。
经过以上步骤处理完之后,我们再创建数据透视表,添加数值字段的时候,它们就全部跑到”值“区域并且默认的汇总方式都变成”求和“了。
有同学要说了,数值区域我都处理完了之后,为啥还是默认为”计数“呢?
注意,如果你先创建了数据透视表,再处理源数据,那么在处理完源数据之后,一定要刷新数据透视表,否则的话那个透视表里的数据还是你更改之前的内容,默认的汇总方式也就不是”求和“了。
4、另一个快捷处理的方法
如果你的数据源区域没有错误值,没有文本型数字,只有空白单元格,我相信这是很多人最常遇到的情况,那么我们就不需要上面那么复杂了。
确认第一行数据内容都是数值,如果第一行数据中有空白单元格,那就用上面的方法(定位到空白单元格,输入0,同时按Ctrl Enter)填充为0;
以前两行(一行标题、一行数据)作为数据源来创建数据透视表,然后将需要汇总的数值字段添加到数据透视表的值区域,这时默认的数据汇总方式就是”求和“;
手动把数据透视表的数据源调整为全部数据区域,刷新数据透视表。
这样我们就完成了。