幾乎所有的用人單位在招聘要求上都會(huì)寫(xiě)一句：熟練掌握Office。那什么程度才算得上熟練呢？
Excel大神可能從來(lái)都不用Excel來(lái)做表格，比如77歲日本老人堀內(nèi)辰男用Excel來(lái)作畫(huà)。
這也難怪，其實(shí)我們大多數(shù)人對(duì)excel的了解只停留在簡(jiǎn)單的加減乘除畫(huà)表格的階段。
不如先從學(xué)一些避免加班的技巧開(kāi)始吧！
數(shù)據(jù)透視表是EXCEL中常用的技能，它能幫助我們快速統(tǒng)計(jì)分析大量數(shù)據(jù)。并且隨著布局的改變，數(shù)據(jù)透視表會(huì)立即按照新的布置重新計(jì)算數(shù)據(jù)，在日常工作中非常實(shí)用。但是數(shù)據(jù)源如果有新增，數(shù)據(jù)透視表是無(wú)法同步更新的。那今天就給大家介紹幾個(gè)實(shí)現(xiàn)數(shù)據(jù)透視表動(dòng)態(tài)刷新的方法。
如圖，這個(gè)數(shù)據(jù)源列出了不同城市的銷(xiāo)售額。
一、數(shù)據(jù)透視表基礎(chǔ)刷新
1.選擇表區(qū)域任一單元格，點(diǎn)擊插入選項(xiàng)卡下的“數(shù)據(jù)透視表”。
2.在“創(chuàng)建數(shù)據(jù)透視表”窗口，表區(qū)域就自動(dòng)選擇了所有連續(xù)區(qū)域，為了方便查看，把數(shù)據(jù)透視表位置放在同一個(gè)工作表下。點(diǎn)擊確定。
3.現(xiàn)在把“城市”放到行標(biāo)簽，“銷(xiāo)售額”放到值區(qū)域范圍。為了方便對(duì)比，后續(xù)也按照同樣的方式創(chuàng)建。
完成如下：
4.接下來(lái)表格最后一行新增數(shù)據(jù)如下，這時(shí)合計(jì)值由原本的255418變成了258418。
5.選中數(shù)據(jù)透視表，菜單欄上方就會(huì)出現(xiàn)數(shù)據(jù)透視表工具，點(diǎn)擊“分析”選項(xiàng)卡下的“刷新”。
但是數(shù)據(jù)透視表沒(méi)有變化 。
6.這種情況是因?yàn)閿?shù)據(jù)透視表的數(shù)據(jù)源區(qū)域沒(méi)有把新增的這行添加進(jìn)來(lái)，那就需要修改數(shù)據(jù)源。選中數(shù)據(jù)透視表，點(diǎn)擊數(shù)據(jù)透視表工具下方“分析”選項(xiàng)卡下的“更改數(shù)據(jù)源”。
然后在“更改數(shù)據(jù)透視表數(shù)據(jù)源”窗口的表區(qū)域里重新選擇區(qū)域，把新增的行也選中。區(qū)域修改成“Sheet1!$A$1:$D$71”。
再次點(diǎn)擊刷新就可以了。
通過(guò)這個(gè)例子，我們發(fā)現(xiàn)如果數(shù)據(jù)增加，數(shù)據(jù)透視表就需要進(jìn)行更改數(shù)據(jù)源來(lái)更新，但是實(shí)際工作中如果遇到頻繁的數(shù)據(jù)變動(dòng)，有沒(méi)有什么方法可以快速實(shí)現(xiàn)動(dòng)態(tài)刷新呢？
二、數(shù)據(jù)透視表動(dòng)態(tài)刷新1）VBA代碼配合多選范圍
1.選中工作表數(shù)據(jù)的A到D列，添加數(shù)據(jù)透視表放在同一個(gè)工作表中。
設(shè)置完成如下：
2.點(diǎn)擊“開(kāi)發(fā)工具”選項(xiàng)卡下的插入，ActiveX控件里的命令按鈕, 在工作表創(chuàng)建一個(gè)按鈕。
如果表格沒(méi)有開(kāi)發(fā)工具這個(gè)選項(xiàng)卡，點(diǎn)擊文件—選項(xiàng)，在“EXCEL選項(xiàng)”窗口左側(cè)的“自定義功能區(qū)”，從“主選項(xiàng)卡”選擇“開(kāi)發(fā)工具”添加到右側(cè)自定義功能區(qū)。
3.在工作表右鍵點(diǎn)擊剛添加的按鈕，選擇“查看代碼”。在調(diào)出的VBA窗口輸入下列代碼。
Private Sub CommandButton1_Click()
ActiveSheet.PivotTables("數(shù)據(jù)透視表9").PivotCache.Refresh
End Sub
代碼中數(shù)據(jù)透視表9是數(shù)據(jù)透視表的名稱(chēng)。
4.然后再點(diǎn)擊開(kāi)發(fā)工具選項(xiàng)卡的“設(shè)計(jì)模式”，取消按鈕的設(shè)計(jì)模式。按鈕就能正常點(diǎn)擊了。
5.在工作表數(shù)據(jù)源最后添加一行數(shù)據(jù)如下，添加之后合計(jì)值是258418
6.然后點(diǎn)擊按鈕進(jìn)行刷新，數(shù)據(jù)透視表就能實(shí)時(shí)更新了。
小結(jié)：這個(gè)方法是在選擇數(shù)據(jù)源的時(shí)候就囊括了其他空白區(qū)域，后續(xù)再添加數(shù)據(jù)也能動(dòng)態(tài)更新。并且通過(guò)VBA添加按鈕，更加方便的進(jìn)行刷新操作。不過(guò)問(wèn)題是一旦選擇的其他區(qū)域出現(xiàn)了無(wú)效數(shù)據(jù)的時(shí)候，數(shù)據(jù)透視表也會(huì)將其納入進(jìn)來(lái)。
2）現(xiàn)有連接
1.點(diǎn)擊數(shù)據(jù)選項(xiàng)卡下的“現(xiàn)有連接”。在彈出窗口點(diǎn)擊“瀏覽更多”。
2.在“選取數(shù)據(jù)源”窗口找到該工作簿，點(diǎn)擊打開(kāi)
在“選擇表格”窗口找到放數(shù)據(jù)的工作表，點(diǎn)擊“確定”。
3.在“導(dǎo)入數(shù)據(jù)”窗口選擇數(shù)據(jù)以數(shù)據(jù)透視表方式顯示，為了方便查看效果，這里放在現(xiàn)有工作表 。
完成如下：
4.同樣在最后一行添加數(shù)據(jù)如下，添加之后合計(jì)值變成了258418
5.選中數(shù)據(jù)透視表，在數(shù)據(jù)透視表工具下的“分析”選項(xiàng)卡，點(diǎn)擊“刷新”。數(shù)據(jù)透視表就能自動(dòng)刷新數(shù)據(jù)了。
小結(jié):這個(gè)方法是把EXCEL工作表變成一個(gè)連接，通過(guò)連接來(lái)插入數(shù)據(jù)透視表。優(yōu)點(diǎn)是工作表的變動(dòng)可以及時(shí)更新，但同樣，當(dāng)我們選擇這種方法的時(shí)候，工作表就不能放其他數(shù)據(jù)，數(shù)據(jù)透視表也盡量建立在其他工作表，避免錯(cuò)誤。
3）超級(jí)表
1.選中工作表區(qū)域的任一單元格，按住Ctrl+T，如下窗口中表數(shù)據(jù)來(lái)源會(huì)自動(dòng)把工作表區(qū)域選中，這里的表格首行就是標(biāo)題，所以勾選“表包含標(biāo)題”。
2.根據(jù)這個(gè)超級(jí)表插入數(shù)據(jù)透視表。選擇表區(qū)域任一單元格，在同一工作表插入數(shù)據(jù)透視表。表區(qū)域會(huì)設(shè)置為超級(jí)表的名稱(chēng)：表5。
同樣把“城市”放到行標(biāo)簽，“銷(xiāo)售額”放到值區(qū)域范圍。完成如下：
3.在表格最后一行添加數(shù)據(jù)如下，添加之后合計(jì)值是258418
4.選中數(shù)據(jù)透視表，在數(shù)據(jù)透視表工具下的“分析”選項(xiàng)卡，點(diǎn)擊“刷新”。這樣就實(shí)現(xiàn)了動(dòng)態(tài)更新。
超級(jí)表是從Excel2007開(kāi)始增加的功能，它解決了前兩種方法無(wú)法智能選擇數(shù)據(jù)源區(qū)域的問(wèn)題。超級(jí)表能夠自動(dòng)增減數(shù)據(jù)源區(qū)域，這是它作為動(dòng)態(tài)數(shù)據(jù)源最大的優(yōu)勢(shì)。
方法介紹完了，以上3種各有優(yōu)劣，希望大家根據(jù)工作中的實(shí)際需求來(lái)靈活選擇。只要撐握一種自己拿手的，其它的也要了解，畢竟哪天有什么小故障，就要換個(gè)方法做事了。
＊＊＊＊部落窩教育－excel數(shù)據(jù)透視表動(dòng)態(tài)刷新＊＊＊＊
原創(chuàng)：夏雪/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）
聯(lián)系客服
