萬萬沒想到，數(shù)據(jù)透視表計(jì)算出現(xiàn)“Bug”，原因竟這么簡單！
??小E還為同學(xué)們準(zhǔn)備了Excel插件安裝包，
??獲取直接在公種號【秋葉Excel】回復(fù)【B站】即可~
大家好，我是在研究 Excel 各種功能，方便日常排坑的小E~
我們今天來看看同事小張的問題！
下面是一張以前的員工基礎(chǔ)工資表，為了方便統(tǒng)計(jì)，小張做了一個數(shù)據(jù)透視表進(jìn)行匯總統(tǒng)計(jì)。
臨近年底，老板跟她說：剛好那天是三八婦女節(jié)，給女同胞多發(fā) 100 元的福利，男同事增加 10 塊錢工資。
01發(fā)現(xiàn)問題
會點(diǎn) Excel 的小張，她點(diǎn)擊「數(shù)據(jù)透視表」任意單元格-選擇【數(shù)據(jù)透視表分析】選項(xiàng)卡-【字段、項(xiàng)目和集】-【計(jì)算字段】。
名稱為：增加的工資
公式為：
=if(性別="女"，100,10)
點(diǎn)擊【確定】按鈕，搞定！
咦，什么回事？結(jié)果怎么不對？？怎么不管是男是女都是 10 塊錢，而且總計(jì)的金額也是 10 塊錢？？計(jì)算字段出 Bug 了？這到底怎么回事呢？
這個問題還沒來得及解決，老板又說，還需要求銷售表的總額平均值。
小張又做了一個透視表，繼續(xù)用計(jì)算字段，同樣的先插入計(jì)算字段，名稱為總額，公式為：
=單價(jià)*數(shù)量
拖入到透視表中，值匯總依據(jù)為平均值。
不可思議的事情發(fā)生了！平均值項(xiàng)的總額居然跟求和項(xiàng)總額一毛一樣？
小張百度了很久，但還是沒有明白該怎么解決這兩個問題，于是她找到了我
02分析問題
小張之所以出現(xiàn)上述問題，其實(shí)是由于對計(jì)算字段的運(yùn)算原理不熟悉。
我們先來看看什么是計(jì)算字段？
計(jì)算字段是指：通過對「數(shù)據(jù)透視表」中現(xiàn)有的字段執(zhí)行計(jì)算后的得到的新字段。
它可以對「數(shù)據(jù)透視表」中的現(xiàn)有數(shù)據(jù)（包括其他的計(jì)算字段和計(jì)算項(xiàng)生成的數(shù)據(jù)）進(jìn)行運(yùn)算，但無法引用數(shù)據(jù)透視表之外的工作表數(shù)據(jù)。
這里需要注意的是：
??計(jì)算字段可以使用運(yùn)算符，表達(dá)式，可以使用數(shù)據(jù)源中已有存在的數(shù)據(jù)，但是不能使用需要將單元格引用或定義的名稱作為參數(shù)的工作表函數(shù)。
??數(shù)據(jù)透視表的計(jì)算字段是依據(jù)字段之和來計(jì)算的。數(shù)據(jù)透視表使用各個值字段分類求和的結(jié)果來應(yīng)用計(jì)算字段。即使數(shù)值字段的匯總方式別設(shè)置為平均值，計(jì)算字段也會將其看作求和。
文字理解起來有點(diǎn)復(fù)雜，我們用實(shí)際例子來說明：
首先，我們以小張的第一個數(shù)據(jù)透視表為例子進(jìn)行解釋。
名稱為：增加的工資
公式為：
=if(性別="女"，100,10)
??運(yùn)算邏輯：
??透視表篩選：老板
雙擊老板增加的工資的單元格，我們可以得到相關(guān)的數(shù)據(jù)源子集。
??對老板的姓名字段進(jìn)行求和：Sum(姓名)=0
??套入計(jì)算字段的公式：
=if(0="女",100,10)
由于 0 不等于女，所以結(jié)果為 10。
??值匯總為求和，結(jié)果也為 10。
同理其他單元格的值都為 10，總計(jì)行中計(jì)算字段的計(jì)算結(jié)果也是一樣的道理。
我們來看看小張的第二個數(shù)據(jù)透視表的計(jì)算字段：
名稱為總額，公式為：
=單價(jià)*數(shù)量
??運(yùn)算邏輯：
??透視表篩選：老板
雙擊老板增總額的單元格，我們可以得到相關(guān)的數(shù)據(jù)源子集。
??先對單價(jià)和數(shù)量字段進(jìn)行求和：
Sum（單價(jià)）=26?
Sum（數(shù)量）=43
??套入計(jì)算字段的公式：
總額=單價(jià)*數(shù)量=26*43=1118
??值匯總為平均值，由于總額就只有 1118 一個數(shù)據(jù)，所以結(jié)果依舊是 1118。
同理，其他的單元格的結(jié)果也和求和項(xiàng)一樣。
到這里，大家應(yīng)該都知道小張計(jì)算字段結(jié)果出錯的原因了吧。
前面我們知道了計(jì)算字段出錯的原因，那我們就來說說具體的解決方法。
下面介紹常見的三種方法：
??創(chuàng)建輔助列；
??利用 SQL 添加字段；
??利用 Power Pivot 添加度量值。
▋利用輔助列
既然使用計(jì)算字段出錯誤，那我們可以直接做一個輔助列進(jìn)行操作，在數(shù)據(jù)源中添加輔助列，再插入數(shù)據(jù)透視表，搞定！
公式為：
=IF(B2="女",100,10)
不過如果老板要求不能使用輔助列，這該怎么辦？
下面介紹兩種做法：
▋利用 SQL 進(jìn)行添加數(shù)據(jù)
我們以小張的第一個例子來講述 SQL 的做法。
? 選擇數(shù)據(jù)源。
選擇【數(shù)據(jù)】選項(xiàng)卡-單擊【現(xiàn)有連接】。
選擇【瀏覽更多】-選擇工作簿的路徑。
選擇數(shù)據(jù)源中該工作簿的路徑-選擇【打開】。
選擇【數(shù)據(jù)$】-點(diǎn)擊【確定】。
導(dǎo)入數(shù)據(jù)-選擇【數(shù)據(jù)透視表】-選擇【屬性】。
? 編寫 SQL 語句。
點(diǎn)擊屬性后-出現(xiàn)連接屬性對話框-選擇【定義】-在下面進(jìn)行編寫 SQL 語句-選擇【確定】。
輸入 SQL 語句：
select *,iif(性別="女",100,10) as 增加的工資 from [數(shù)據(jù)$]
SQL 語句中 iif 函數(shù)相當(dāng)于工作表中的 if 函數(shù)。*星號代表所有字段。
? 創(chuàng)建數(shù)據(jù)透視表。
上一步操作后，會返回如下界面-選擇【確定】-即可創(chuàng)建數(shù)據(jù)透視表。
從透視表中，我們可以看到出現(xiàn)了增加的工資的字段，拖拽做成數(shù)據(jù)透視表。
▋利用 PowerPivot 添加度量值
? 加載 PowerPivot 選項(xiàng)卡。
Excel2013 以上版本，Powerpivot 已經(jīng)作為加載項(xiàng)內(nèi)嵌在 Excel 中，我們只需要在加載項(xiàng)中調(diào)用出來。
①?轉(zhuǎn)到「文件」>「選項(xiàng)」>「加載項(xiàng)」。
②?在「管理」框中，單擊「COM 加載項(xiàng)」>「轉(zhuǎn)到」。
③?選中「Microsoft Office Power Pivot」框，然后單擊「確定」。
④?現(xiàn)在，功能區(qū)出現(xiàn)一個「Power Pivot 選項(xiàng)卡」。
? 勾選數(shù)據(jù)模型。
插入數(shù)據(jù)透視表，勾選數(shù)據(jù)模型-【確定】。
? 添加度量值。
我們以小張第二個例子為例，說明一下 powerpivot 的做法。
選擇【power pivot】選項(xiàng)卡-選擇【度量值】-【新建度量值】。
度量值名稱為：總額平均值。
Dax 公式為：
=AVERAGEX('區(qū)域 1','區(qū)域 1'[單價(jià)]*'區(qū)域 1'[數(shù)量])
選擇【確定】之后，拖動數(shù)據(jù)透視表字段，搞定！
下一步，檢查一下數(shù)據(jù)是否正確，雙擊總額平均值老板對應(yīng)的單元格，通過驗(yàn)算得知結(jié)果為 307，與透視表結(jié)果一致。
好啦~本文就到這里結(jié)束啦~（才怪）
04總結(jié)一下
▋計(jì)算字段需要注意
??由于數(shù)據(jù)透視表的計(jì)算是在透視表的緩存中進(jìn)行的，所以計(jì)算字段可以使用已有的數(shù)據(jù)，不能使用單元格引用和名稱引用的數(shù)據(jù)。
比如只能使用簡單的函數(shù)運(yùn)算（如：Sum，If，Text， Aad， Not，Or，Count， Average）；
不能使用需要單元格引用或定義名稱的參數(shù)的函數(shù)（如 Match?等）。
??數(shù)據(jù)透視表是使用各個值字段分類求和的結(jié)果來應(yīng)用計(jì)算字段的。
在計(jì)算時是先對字段中相關(guān)行的值求和，然后按照公式得到乘積，而不是先把兩個字段相關(guān)行的值相乘后再對積求和。
即使數(shù)值字段的匯總方式別設(shè)置為平均值，計(jì)算字段也會將其看作求和。
▋解決計(jì)算字段出現(xiàn)問題的方法
??添加輔助列（推薦）；
??利用 SQL 語句進(jìn)行添加需要的字段；
??利用 Power pivot 新建度量值。
關(guān)于數(shù)據(jù)透視表，或者其他有關(guān) Excel 的功能，大家出現(xiàn)過哪些困擾許久的問題？歡迎在文下留言~
??小E還為同學(xué)們準(zhǔn)備了Excel插件安裝包，
??獲取直接在公種號【秋葉Excel】回復(fù)【B站】即可~
