两种解决数据透视表计算字段出现“错误”的方法
发布时间:2022-12-22 21:52:17
数据透视表计算字段出现“错误”的现象及原因
在Excel数据透视表中添加计算字段进行计算时,有时会遇到计算“错误”的情况。例的数据源创建数据透视表后,添加计算字段“字段1”,公式为“数量*单价”,目的是为了得到各种“商品”的合计金额。而在数据透视表中却得到错误的结果。
例如数据源中“商品编号”为“A51052”的记录有3个,其合计金额应为“50*100=500”,而“字段1”返回的金额为“15000”。
另外,总计行中计算字段的计算结果也是如此。这是因为,计算字段在计算时是先对字段中相关行的值求和,然后按照公式得到乘积,而不是先把两个字段相关行的值相乘后再对积求和,如本例“商品编号”“A51052”的计算结果是“(10+20+20)*(100+100+100)=15000”,因而会返回“错误”。
数据透视表计算字段出现“错误”的解决办法
首选在数据源中添加一个辅助列,如“金额”,输入公式让各行都返回“单价”与“数量”的乘积,再将该字段添加到数据透视表中就没有上述问题了。如果由于某种原因不便在数据源中添加新的字段,可以采用下面的方法,以Excel2013和上图数据为例。
方法一:用SQL
1、保存工作簿,单击“数据→现有链接→浏览更多”,在弹出的对话框中找到并选择当前工作簿,单击“打开”,弹出“选择表格”对话框。如下图,本例的数据在“Sheet1”工作表的A1:D29区域中,第一行为标题。因而选择“Sheet1$”,并保持勾选“数据首行包含列标题”,单击“确定”。
2、在弹出的“导入数据”对话框中选择“数据透视表”,并设置数据的存放位置,单击“属性”按钮。
3、接着在“链接属性”对话框中选择“定义”选项卡,在“命令文本”中输入SQL命令:
返回“导入数据”对话框,单击“确定”,然后在新创建的数据透视表中添加所需字段,可以看到,新增的“金额”字段可以返回正确的结果。
方法二:用PowerPivot
1、首先要在Excel中加载PowerPivot加载项:单击“文件→选项→加载项”,在“管理”右侧的下拉列表中选择“COM 加载项”并单击“转到”,弹出“COM加载项”对话框。或者单击“开发工具→COM加载项”弹出该窗口。在其中选择“Microsoft Office PowerPivot for Excel 2013”选项后确定。PowerPivot选项卡即会出现在Excel功能区中。
2、选择数据源中的某个单元格,单击“插入→数据透视表”创建数据透视表,在“创建数据透视表”对话框中勾选“将此数据添加到数据模型”,单击“确定”。
Excel创建了一个模型表名称为“区域”的数据透视表,如数据透视表字段列表。
3、选择“PowerPivot→计算字段→新建计算字段”,弹出“计算字段”对话框,将“计算字段名称”设置为一个有意义的名称,如本例“金额”,在“公式”下方的文本框中输入DAX公式:
=sumx('区域',[单价]*[数量])
SUMX函数为数据分析表达式(即DAX:用于PowerPivot中定义计算的公式表达式语言)中的一个函数,返回表中每一行计算的表达式之和。单击“确定”,数据透视表字段列表中会添加一个新的字段“金额”。将所需的字段如“商品编号”、“单价”、“数量”、“金额”等添加到数据透视表,其中“单价”字段添加到行区域。
可以看到,数据透视表中的“金额”即为我们所需的结果。
猜你喜欢
- 最近有Win10用户反映,想让一些自己常用的软件在开机的时候便能自动启动,但用户不知道要如何增加系统启动项,为此非常苦恼。那么,Win10如
- 下面小编为大家介绍将Excel中的文本算式转换为实际结果的三种方法,教程很不错,推荐到脚本之家,喜欢的朋友可以跟着教程一起来学习,希望大家喜
- 我们在职场工作中,每天都会与各种文件打交道,尤其是Word文件最常见。眼看快要下班了,老板突然发来300份Word文档,让我合并成为一份文档
- Word最近使用的字体怎么删除?近期有用户想要删除字体下拉框中“最近使用的字体”记录,那么应该如何操作呢?下面小编就以 Word 2016
- 在工作的时候,可能经常会需要使用到远程控制功能,但是在默认情况下远程桌面连接是需要账户密码才能登录的,那要取消远程桌面连接账户密码登录需要怎
- 在Excel2010中为了显示效果的需要,有时候需要将操作界面中的基本组成部分(如编辑栏、网格线、航标和列表)隐藏起来,或将隐藏的部分显不出
- excel折线图/饼图怎么忽略数据源中的0值?excel中绘制折线图或者饼图的时候,发现表格中有空值,该怎么忽略空值呢?下面我们就来看看详细
- 希沃白板一款专业的教学辅助软件,这款软件为用户提供了丰富的教学功能,本文中介绍的使用这款软件添加文本的方法希沃白板如何添加文本?希沃白板是一
- 首先说明一下 “分节符”作用,它就是用来将整个文档分节的,添加一个分节符,文档就分成1、2两节;添加两个分节符,文档就分成1、2、3节。当前
- 很多原因都可能导致设备无法正常的连接无线网络,其中便包括了未开启无线网络服务。那么,Win10 1909系统中的无线网络服务该怎么开启呢?下
- 在WORD中编辑了一段文字,你想有多少个文字,不可能一个个去数字吧!WORD有自动统计字数的功能,藏的有一点有深而以,下面我就来演示word
- ①编辑文档的时候出现下图所示的错误提示。 ②打开Word,单击工具--模板和加载项。 ③
- Excel20106表格中怎么显示星期几?excel2016表格中显示星期几,该怎么设置呢?这就需要我们掌握text函数,下面我们就来看看E
- Excel快速提取公式表达式
- 如何在Excel一列数据中找出数据最小的值?下面一起学习excel中获取最小值的方法。1、在空白的单元格内输入公式=MIN(区域范围),本例
- Offic办公软件有一项功能,可以帮助我们将未保存的文档及时的恢复。比如:当出现电脑蓝屏、断电、死机等一些无法控制的意外,这时候在使用off
- Word文档编辑时,很多情况下都是使用一行标题,部分特殊类文件就需要双行标题,这类的文件抬头应该如何制作排版呢?今天教你小技巧。1、双行合一
- 在创建好数据透视表后,为了使其满足对数据分析的需要,可对excel2010表格中的数据透视表的布局进行修改。当数据透视表中的分析要点不明确时
- 当Excel表格中含有合并单元格时,如果直接删除单元格,下面的单元格可能上移或者右边的单元格左移。如图:删除E4单元格
- word样式和模板的区别: 样式是被包含在模板中的重要元素之一,而且是最重要的元素。模板就是能把在制作某个文档中所创建的样式都保存