excel數(shù)據(jù)透視表動態(tài)刷新數(shù)據(jù)的三種方法:VBA自動刷新透視表、超級表、現(xiàn)有連接
?
作者:夏雪來源:部落窩教育發(fā)布時間:2018-09-07 10:00:04點擊:39590
小編有話說:好多小伙伴深知數(shù)據(jù)透視表好用,但是卻不愿意用,主要就是這個功能按鈕太多,不知道該點哪里,今天給大家做一個數(shù)據(jù)透視表的數(shù)據(jù)刷新匯總,幾種刷新方式總有一個適合你,操作也是超簡單的,都是點幾下按鈕就可以完成動態(tài)刷新,記著收藏喲!
數(shù)據(jù)透視表是EXCEL中常用的技能,它能幫助我們快速統(tǒng)計分析大量數(shù)據(jù)。并且隨著布局的改變,數(shù)據(jù)透視表會立即按照新的布置重新計算數(shù)據(jù),在日常工作中非常實用。但是數(shù)據(jù)源如果有新增,數(shù)據(jù)透視表是無法同步更新的。那今天就給大家介紹幾個Excel數(shù)據(jù)透視表動態(tài)刷新數(shù)據(jù)的方法。
如圖,這個數(shù)據(jù)源列出了不同城市的銷售額。
一、數(shù)據(jù)透視表基礎刷新
1.選擇表區(qū)域任一單元格,點擊插入選項卡下的“數(shù)據(jù)透視表”。
2.在“創(chuàng)建數(shù)據(jù)透視表”窗口,表區(qū)域就自動選擇了所有連續(xù)區(qū)域,為了方便查看,把數(shù)據(jù)透視表位置放在同一個工作表下。點擊確定。
3.現(xiàn)在把“城市”放到行標簽,“銷售額”放到值區(qū)域范圍。為了方便對比,后續(xù)也按照同樣的方式創(chuàng)建。
完成如下:
4.接下來表格最后一行新增數(shù)據(jù)如下,這時合計值由原本的255418變成了258418。
5.選中數(shù)據(jù)透視表,菜單欄上方就會出現(xiàn)數(shù)據(jù)透視表工具,點擊“分析”選項卡下的“刷新”。
但是數(shù)據(jù)透視表沒有變化 。
6.這種情況是因為數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域沒有把新增的這行添加進來,那就需要修改數(shù)據(jù)源。選中數(shù)據(jù)透視表,點擊數(shù)據(jù)透視表工具下方“分析”選項卡下的“更改數(shù)據(jù)源”。
然后在“更改數(shù)據(jù)透視表數(shù)據(jù)源”窗口的表區(qū)域里重新選擇區(qū)域,把新增的行也選中。區(qū)域修改成“Sheet1!$A$1:$D$71”。
再次點擊刷新就可以了。
通過這個例子,我們發(fā)現(xiàn)如果數(shù)據(jù)增加,數(shù)據(jù)透視表就需要進行更改數(shù)據(jù)源來更新,但是實
際工作中如果遇到頻繁的數(shù)據(jù)變動,有沒有什么方法可以快速實現(xiàn)Excel數(shù)據(jù)透視表刷新呢?
二、Excel數(shù)據(jù)透視表動態(tài)刷新數(shù)據(jù)
1)VBA自動刷新透視表
1.選中工作表數(shù)據(jù)的A到D列,添加數(shù)據(jù)透視表放在同一個工作表中。
設置完成如下:
2.點擊“開發(fā)工具”選項卡下的插入,ActiveX控件里的命令按鈕, 在工作表創(chuàng)建一個按鈕。
如果表格沒有開發(fā)工具這個選項卡,點擊文件—選項,在“EXCEL選項”窗口左側的“自定義功能區(qū)”,從“主選項卡”選擇“開發(fā)工具”添加到右側自定義功能區(qū)。
3.在工作表右鍵點擊剛添加的按鈕,選擇“查看代碼”。在調(diào)出的VBA窗口輸入下列代碼。
Private Sub CommandButton1_Click()
ActiveSheet.PivotTables("數(shù)據(jù)透視表9").PivotCache.Refresh
End Sub
代碼中數(shù)據(jù)透視表9是數(shù)據(jù)透視表的名稱。
4.然后再點擊開發(fā)工具選項卡的“設計模式”,取消按鈕的設計模式。按鈕就能正常點擊了。
5.在工作表數(shù)據(jù)源最后添加一行數(shù)據(jù)如下,添加之后合計值是258418
6.然后點擊按鈕進行刷新,數(shù)據(jù)透視表就能實時更新了。
小結:這個方法是在選擇數(shù)據(jù)源的時候就囊括了其他空白區(qū)域,后續(xù)再添加數(shù)據(jù)也能動態(tài)更新。并且通過VBA添加按鈕,更加方便的進行刷新操作。不過問題是一旦選擇的其他區(qū)域出現(xiàn)了無效數(shù)據(jù)的時候,數(shù)據(jù)透視表也會將其納入進來。
2)現(xiàn)有連接刷新數(shù)據(jù)透視表
1.點擊數(shù)據(jù)選項卡下的“現(xiàn)有連接”。在彈出窗口點擊“瀏覽更多”。
2.在“選取數(shù)據(jù)源”窗口找到該工作簿,點擊打開
在“選擇表格”窗口找到放數(shù)據(jù)的工作表,點擊“確定”。
3.在“導入數(shù)據(jù)”窗口選擇數(shù)據(jù)以數(shù)據(jù)透視表方式顯示,為了方便查看效果,這里放在現(xiàn)有工作表 。
完成如下:
4.同樣在最后一行添加數(shù)據(jù)如下,添加之后合計值變成了258418
5.選中數(shù)據(jù)透視表,在數(shù)據(jù)透視表工具下的“分析”選項卡,點擊“刷新”。數(shù)據(jù)透視表就能自動刷新數(shù)據(jù)了。
小結:這個方法是把EXCEL工作表變成一個連接,通過連接來插入數(shù)據(jù)透視表。優(yōu)點是工作表的變動可以及時更新,但同樣,當我們選擇這種方法的時候,工作表就不能放其他數(shù)據(jù),數(shù)據(jù)透視表也盡量建立在其他工作表,避免錯誤。
3)超級表實現(xiàn)Excel數(shù)據(jù)透視表刷新
1.選中工作表區(qū)域的任一單元格,按住Ctrl+T,如下窗口中表數(shù)據(jù)來源會自動把工作表區(qū)域選中,這里的表格首行就是標題,所以勾選“表包含標題”。
2.根據(jù)這個超級表插入數(shù)據(jù)透視表。選擇表區(qū)域任一單元格,在同一工作表插入數(shù)據(jù)透視表。表區(qū)域會設置為超級表的名稱:表5。
同樣把“城市”放到行標簽,“銷售額”放到值區(qū)域范圍。完成如下:
3.在表格最后一行添加數(shù)據(jù)如下,添加之后合計值是258418
4.選中數(shù)據(jù)透視表,在數(shù)據(jù)透視表工具下的“分析”選項卡,點擊“刷新”。這樣就實現(xiàn)了動態(tài)更新。
超級表是從Excel2007開始增加的功能,它解決了前兩種方法無法智能選擇數(shù)據(jù)源區(qū)域的問題。超級表能夠自動增減數(shù)據(jù)源區(qū)域,這是它作為動態(tài)數(shù)據(jù)源最大的優(yōu)勢。
方法介紹完了,以上三種各有優(yōu)劣,希望大家根據(jù)工作中的實際需求來靈活選擇。覺得不錯的話,給我點贊吧!
本文配套的練習課件請加入QQ群:264539405下載。
如果您因工作所需使用到Excel,不妨關注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學習。
如果您想要隨時隨地學習excel,掃下方二維碼,可關注公眾號,每日為您推送優(yōu)質(zhì)excel教程:
Excel教程 相關推薦:
《Excel教程:打敗查找函數(shù),pq合并查詢一次搞定多表匹配》最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!