編按：小伙伴們，你們好呀！開工后，大家一定都很忙吧？有部分小伙伴可能忙著做年初計(jì)劃，開始一年中使用表格頻次最高的時(shí)刻。提到計(jì)劃一定少不了數(shù)據(jù)分析，表格中數(shù)據(jù)分析用到最多的就是數(shù)據(jù)透視表。今天跟大家聊一下使用數(shù)據(jù)透視表過程中遇到的經(jīng)典案例。

首先我們來復(fù)習(xí)一下：如何在表格中插入數(shù)據(jù)透視表？

鼠標(biāo)放在數(shù)據(jù)區(qū)域，在插入選項(xiàng)卡中找到數(shù)據(jù)透視表點(diǎn)擊插入，隨后會(huì)出現(xiàn)新建工作表存儲(chǔ)（也可以自定義在當(dāng)前工作表空白單元格存儲(chǔ)）在新建的數(shù)據(jù)透視表中拖動(dòng)透視字段到行和值中，會(huì)自動(dòng)進(jìn)行計(jì)算。

常規(guī)的數(shù)據(jù)字段項(xiàng)拖動(dòng)到值字段時(shí)，會(huì)自動(dòng)顯示成求和，但是今天我們遇到的問題是所有的數(shù)據(jù)都顯示計(jì)數(shù)項(xiàng)，這是為什么呢？

我們來手動(dòng)選中值計(jì)數(shù)字段，將計(jì)算類型更改為求和看一下，發(fā)現(xiàn)更改求和項(xiàng)后結(jié)果顯示為0，很明顯數(shù)據(jù)存在異常的現(xiàn)象。

回到數(shù)據(jù)源檢查數(shù)據(jù)發(fā)現(xiàn)原來數(shù)字是文本格式，我們都知道文本數(shù)字求和的結(jié)果會(huì)是0，對(duì)應(yīng)數(shù)據(jù)透視表也會(huì)默認(rèn)文本數(shù)據(jù)是計(jì)數(shù)，所以不會(huì)是求和。

我們?nèi)x數(shù)字所在單元格區(qū)域，將單元格格式設(shè)置為常規(guī)格式，然后復(fù)制數(shù)據(jù)，打開剪貼板，利用剪貼板所見即所得的原理，將數(shù)字由文本格式轉(zhuǎn)換為常規(guī)可以求和的格式。

小互動(dòng)：除了使用剪貼板將文本數(shù)據(jù)轉(zhuǎn)換為數(shù)值，還有什么方法？(提示：數(shù)據(jù)*1)

緊接著我們回到剛剛的數(shù)據(jù)透視表里，打開數(shù)據(jù)透視表分析選項(xiàng)卡，找到刷新功能，選擇全部刷新一下數(shù)據(jù)，會(huì)發(fā)現(xiàn)前面更改求和的字段數(shù)值不再是0了，證明前面的數(shù)據(jù)格式確實(shí)對(duì)數(shù)據(jù)透視表求和存在影響。

再次將其他值字段由計(jì)數(shù)項(xiàng)更改為求和即可，但是這里我們會(huì)發(fā)現(xiàn)一個(gè)問題就是遇到的字段項(xiàng)如果比較多的時(shí)候，如何批量將數(shù)據(jù)透視表的計(jì)數(shù)字段更改為求和字段呢？

可能此時(shí)有小伙伴們會(huì)將字段值全部取消勾選，然后再次選擇到值字段中就會(huì)全部變成求和項(xiàng)了，操作如下圖：

這樣如果值字段只有少量時(shí)一定是最優(yōu)選擇，遇到非常多的字段需要全部點(diǎn)擊選擇一遍很明顯就不簡(jiǎn)便了。

以下是小編手動(dòng)調(diào)整將計(jì)數(shù)字段為求和字段后的效果圖示：

既然聊到這里，小編也不藏著掖著了，直接上代碼，使用VBA編程可以快速將數(shù)據(jù)透視表值字段中的計(jì)數(shù)字段更改為求和字段。代碼如下：

Sub?數(shù)據(jù)透視表字段轉(zhuǎn)換()

Dim i As PivotField

With Selection.PivotTable

??? .ManualUpdate = True

??????? For Each i In .DataFields

??????????? With i

??????????????? .Function = xlSum'計(jì)數(shù)改求和調(diào)整這里；xlSum是求和、xlCount是計(jì)數(shù)

??????????????? .NumberFormat = "#,##0"

??????????? End With

??????? Next i

??? .ManualUpdate = False

End With

End Sub

代碼操作注意事項(xiàng)：

第一步先復(fù)制代碼，第二步從開發(fā)工具中打開VBE，插入一個(gè)新的模塊，雙擊模塊進(jìn)入后粘貼代碼，然后點(diǎn)擊運(yùn)行，運(yùn)行前注意上面代碼的計(jì)數(shù)和求和狀態(tài)。

總結(jié)一下：我們介紹了如何插入數(shù)據(jù)透視表，常見的數(shù)據(jù)為文本型在數(shù)據(jù)透視表中不求和的解決辦法是將文本數(shù)據(jù)轉(zhuǎn)換為數(shù)值型，給大家推薦了剪貼板的所見即所得的原理，調(diào)整后的數(shù)據(jù)記得刷新數(shù)據(jù)透視表哦!使用VBA批量設(shè)置數(shù)據(jù)透視表字段時(shí)注意備份數(shù)據(jù)，因?yàn)閂BA代碼操作后不可以撤銷的哦！

以上就是今天跟大家分享的內(nèi)容，感謝大家耐心看完，希望大家能夠喜歡!

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù)，非得用VBA才能實(shí)現(xiàn)嗎？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

4種刪除excel重復(fù)值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能！

版權(quán)申明：

本文作者花花；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。