excel數(shù)據(jù)透視表值錯(cuò)誤 兩種解決數(shù)據(jù)透視表計(jì)算字段出現(xiàn)“錯(cuò)誤”的方法

excel數(shù)據(jù)透視表值錯(cuò)誤 兩種解決數(shù)據(jù)透視表計(jì)算字段出現(xiàn)“錯(cuò)誤”的方法，作為經(jīng)常和辦公軟件打交道的你這些問題是不是經(jīng)常困擾著你，小編今天這篇文章就是幫助你解決excel數(shù)據(jù)透視表值錯(cuò)誤這個(gè)問題，當(dāng)你再次遇到excel數(shù)據(jù)透視表值錯(cuò)誤 兩種解決數(shù)據(jù)透視表計(jì)算字段出現(xiàn)“錯(cuò)誤”的方法這個(gè)問題的時(shí)候，我相信你絕對(duì)不會(huì)再犯愁了，下面就跟我一起來學(xué)習(xí)一下這個(gè)知識(shí)吧！

數(shù)據(jù)透視表計(jì)算字段出現(xiàn)“錯(cuò)誤”的現(xiàn)象及原因

在Excel數(shù)據(jù)透視表中添加計(jì)算字段進(jìn)行計(jì)算時(shí)，有時(shí)會(huì)遇到計(jì)算“錯(cuò)誤”的情況。例如用下圖所示的數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表后，添加計(jì)算字段“字段1”，公式為“數(shù)量*單價(jià)”，目的是為了得到各種“商品”的合計(jì)金額。而在數(shù)據(jù)透視表中卻得到錯(cuò)誤的結(jié)果。

例如數(shù)據(jù)源中“商品編號(hào)”為“A51052”的記錄有3個(gè)，其合計(jì)金額應(yīng)為“50*100=500”，而“字段1”返回的金額為“15000”。

另外，總計(jì)行中計(jì)算字段的計(jì)算結(jié)果也是如此。這是因?yàn)?，?jì)算字段在計(jì)算時(shí)是先對(duì)字段中相關(guān)行的值求和，然后按照公式得到乘積，而不是先把兩個(gè)字段相關(guān)行的值相乘后再對(duì)積求和，如本例“商品編號(hào)”“A51052”的計(jì)算結(jié)果是“(10+20+20)*(100+100+100)=15000”，因而會(huì)返回“錯(cuò)誤”。

數(shù)據(jù)透視表計(jì)算字段出現(xiàn)“錯(cuò)誤”的解決辦法

首選在數(shù)據(jù)源中添加一個(gè)輔助列，如“金額”，輸入公式讓各行都返回“單價(jià)”與“數(shù)量”的乘積，再將該字段添加到數(shù)據(jù)透視表中就沒有上述問題了。如果由于某種原因不便在數(shù)據(jù)源中添加新的字段，可以采用下面的方法，以Excel2013和上圖數(shù)據(jù)為例。

方法一：用SQL

1、保存工作簿，單擊“數(shù)據(jù)→現(xiàn)有鏈接→瀏覽更多”，在彈出的對(duì)話框中找到并選擇當(dāng)前工作簿，單擊“打開”，彈出“選擇表格”對(duì)話框。如下圖，本例的數(shù)據(jù)在“Sheet1”工作表的A1:D29區(qū)域中，第一行為標(biāo)題。因而選擇“Sheet1$”，并保持勾選“數(shù)據(jù)首行包含列標(biāo)題”，單擊“確定”。

2、在彈出的“導(dǎo)入數(shù)據(jù)”對(duì)話框中選擇“數(shù)據(jù)透視表”，并設(shè)置數(shù)據(jù)的存放位置，單擊“屬性”按鈕。

3、接著在“鏈接屬性”對(duì)話框中選擇“定義”選項(xiàng)卡，在“命令文本”中輸入SQL命令：

返回“導(dǎo)入數(shù)據(jù)”對(duì)話框，單擊“確定”，然后在新創(chuàng)建的數(shù)據(jù)透視表中添加所需字段，可以看到，新增的“金額”字段可以返回正確的結(jié)果。

方法二：用PowerPivot

1、首先要在Excel中加載PowerPivot加載項(xiàng)：?jiǎn)螕?ldquo;文件→選項(xiàng)→加載項(xiàng)”，在“管理”右側(cè)的下拉列表中選擇“COM 加載項(xiàng)”并單擊“轉(zhuǎn)到”，彈出“COM加載項(xiàng)”對(duì)話框。或者單擊“開發(fā)工具→COM加載項(xiàng)”彈出該窗口。在其中選擇“Microsoft Office PowerPivot for Excel 2013”選項(xiàng)后確定。PowerPivot選項(xiàng)卡即會(huì)出現(xiàn)在Excel功能區(qū)中。

2、選擇數(shù)據(jù)源中的某個(gè)單元格，單擊“插入→數(shù)據(jù)透視表”創(chuàng)建數(shù)據(jù)透視表，在“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框中勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”，單擊“確定”。

Excel創(chuàng)建了一個(gè)模型表名稱為“區(qū)域”的數(shù)據(jù)透視表，如數(shù)據(jù)透視表字段列表。

3、選擇“PowerPivot→計(jì)算字段→新建計(jì)算字段”，彈出“計(jì)算字段”對(duì)話框，將“計(jì)算字段名稱”設(shè)置為一個(gè)有意義的名稱，如本例“金額”，在“公式”下方的文本框中輸入DAX公式：

=sumx('區(qū)域',[單價(jià)]*[數(shù)量])

SUMX函數(shù)為數(shù)據(jù)分析表達(dá)式(即DAX：用于PowerPivot中定義計(jì)算的公式表達(dá)式語言)中的一個(gè)函數(shù)，返回表中每一行計(jì)算的表達(dá)式之和。單擊“確定”，數(shù)據(jù)透視表字段列表中會(huì)添加一個(gè)新的字段“金額”。將所需的字段如“商品編號(hào)”、“單價(jià)”、“數(shù)量”、“金額”等添加到數(shù)據(jù)透視表，其中“單價(jià)”字段添加到行區(qū)域。

可以看到，數(shù)據(jù)透視表中的“金額”即為我們所需的結(jié)果。