excel數據透視表計算字段出現“出錯”的因素及處理辦法

現象及原因：在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命令：

??? select *,數量*單價 as 金額 from [Sheet1$]

???

?? 返回“導入數據”對話框，單擊“確定”，然后在新創建的數據透視表中添加所需字段，可以看到，新增的“金額”字段可以返回正確的結果。

???

??? 方法二：用PowerPivot

?1.首先要在Excel中加載PowerPivot加載項：單擊“文件→選項→加載項”，在“管理”右側的下拉列表中選擇“COM 加載項”并單擊“轉到”，彈出“COM加載項”對話框。或者單擊“開發工具→COM加載項”彈出該窗口。在其中選擇“Microsoft Office PowerPivot for Excel 2013”選項后確定。PowerPivot選項卡即會出現在Excel功能區中。