|
閑言少敘，先上效果圖： 涉及的知識(shí)點(diǎn)：
之前在Excel連接SQL Server數(shù)據(jù)庫(kù)的文章中，曾經(jīng)介紹過(guò)切片器及數(shù)據(jù)透視表更新事件的使用。本例中與其原理完全一致。 不太容易構(gòu)思之處在于： 1）通過(guò)VBA自動(dòng)篩選程序?qū)⒃摮鞘凶詣?dòng)篩選后，粘貼進(jìn)指定工作表，繼而通過(guò)定義好的名稱動(dòng)態(tài)捕捉作圖數(shù)據(jù)，用于生成各種圖表。 2）用切片器替代了省市二級(jí)下拉菜單的制作，不僅制作過(guò)程簡(jiǎn)單，而且選擇項(xiàng)目方便，目視設(shè)計(jì)美觀。 01 — 圖表構(gòu)思和數(shù)據(jù)整理 根據(jù)待分析的數(shù)據(jù)源和期望呈現(xiàn)的可視化儀表板效果，勾勒草圖，以終為始，尋找到能實(shí)現(xiàn)和可實(shí)現(xiàn)兩者的平衡點(diǎn)。按此要求整合數(shù)據(jù)。 本例中用到的是汽車行業(yè)經(jīng)銷商分析數(shù)據(jù)，整理格式如下： 02 — 準(zhǔn)備數(shù)據(jù)透視表和切片器 2.1為儀表板準(zhǔn)備由省市數(shù)據(jù)源制作的數(shù)據(jù)透視表，將城市拖入行標(biāo)簽。 注：為了數(shù)據(jù)透視表更新事件能正常運(yùn)行，這里建議將透視表與儀表板放在同一張工作表內(nèi)。 2.2為儀表板準(zhǔn)備選擇控件-切片器 2.3切片器自定義格式化 選中要設(shè)置格式的切片器-選項(xiàng)-新建切片器樣式。這里可以對(duì)整個(gè)切片器，以及頁(yè)面，已選擇的帶有/無(wú)數(shù)據(jù)的項(xiàng)目，懸停帶有/無(wú)數(shù)據(jù)的項(xiàng)目進(jìn)行自定義格式化。本例中比較巧妙的一點(diǎn)是對(duì)于'已取消選擇的無(wú)數(shù)據(jù)的項(xiàng)目'均設(shè)置為黑色，相當(dāng)于將城市切片器上不相關(guān)的選項(xiàng)卡'藏'了起來(lái)。 此外，還可以右鍵單擊切片器，在大小和屬性中進(jìn)行設(shè)置。在框架-按鈕高度可設(shè)置按鈕的高度和寬度以及列數(shù)，設(shè)置好后勾選禁止調(diào)整大小和移動(dòng)，這樣可以避免用戶更改你的自定義切片器格式。為了更好滴避免被誤操作，還需進(jìn)行工作表保護(hù)，為了不影響切片器功能，還要在屬性中將說(shuō)定復(fù)選框取消勾選。 通過(guò)數(shù)據(jù)透視表更新事件，在點(diǎn)擊切片器時(shí)，觸發(fā)事件，執(zhí)行相應(yīng)代碼，實(shí)現(xiàn)動(dòng)態(tài)效果。在此過(guò)程中，切片器充當(dāng)?shù)氖堑韧诔Ｒ?guī)控件的作用，但其交互效果更好，尤其是對(duì)切片器進(jìn)行自定義格式化后。 03 — 設(shè)置數(shù)據(jù)透視表更新事件 數(shù)據(jù)透視表更新事件PivotTableUpdate：VBA中的事件就像是一個(gè)聲控開關(guān)，當(dāng)有聲音響起時(shí)燈就會(huì)亮起，同理當(dāng)數(shù)據(jù)透視表更新時(shí)，就會(huì)觸發(fā)事件代碼運(yùn)行。這里切片器對(duì)數(shù)據(jù)透視表進(jìn)行切片時(shí)，相當(dāng)于一個(gè)聲控開關(guān)，Excel會(huì)捕捉到這一瞬間，并執(zhí)行以下藍(lán)色字體的自定義代碼。本案中是對(duì)相應(yīng)城市的所有經(jīng)銷商進(jìn)行自動(dòng)篩選，并將篩選結(jié)果粘貼到新表中，用于定義名稱及生成動(dòng)態(tài)圖表。 這段代碼實(shí)現(xiàn)的功能是將第一步準(zhǔn)備的數(shù)據(jù)源表，以第一行為篩選行，以第31列（城市列）為篩選字段，以儀表板工作表的CB3單元格（數(shù)據(jù)透視表切片后結(jié)果單元格，即城市）為篩選條件進(jìn)行自動(dòng)篩選，將結(jié)果粘貼到“抓取”工作表中，用于下一步制圖。 04 — 定義名稱動(dòng)態(tài)引用區(qū)域 定義名稱被廣泛地應(yīng)用于動(dòng)態(tài)交互式圖表中，通過(guò)OFFSET函數(shù)生成一個(gè)動(dòng)態(tài)引用區(qū)域，并在生成圖表時(shí)將該定義好的名稱用于圖表的數(shù)據(jù)區(qū)域中，使圖表捕捉的制圖數(shù)據(jù)源區(qū)域具備自動(dòng)延展的動(dòng)態(tài)效果。 注：
05 — 添加序列制作圖表 常規(guī)圖表中的圖形數(shù)據(jù)源，是由不同的序列組成的。以二手車銷量和置換的圖表為例，這里定義了兩個(gè)序列和一個(gè)水平分類軸標(biāo)簽，分別為： 二手車置換量，數(shù)據(jù)引用區(qū)域='4.切片器控制儀表板.xlsm'!二手車置換量 二手車銷售量，數(shù)據(jù)引用區(qū)域='4.切片器控制儀表板.xlsm'!二手車銷售量 水平分類軸標(biāo)簽，數(shù)據(jù)引用區(qū)域='4.切片器控制儀表板.xlsm'!經(jīng)銷商名稱 注：這里的數(shù)據(jù)引用區(qū)域引用的是之前定義過(guò)的名稱，這一步非常關(guān)鍵。 通過(guò)以上操作，即可生成二手車的包含兩個(gè)序列的圖表，并且這個(gè)圖表的水平分類軸標(biāo)簽和Y軸序列所對(duì)應(yīng)的數(shù)據(jù)區(qū)域，均是根據(jù)該城市的經(jīng)銷商數(shù)量自動(dòng)延展的，沒(méi)有任何冗余。 06 — 添加放大和返回按鈕 通過(guò)選擇對(duì)應(yīng)的range區(qū)域，設(shè)置合適的縮放級(jí)別，便可實(shí)現(xiàn)放大和返回的效果。 Sub 返回() Application.ScreenUpdating = False '暫停刷新屏幕 Sheets('儀表板').Select ActiveWindow.Zoom = 40 Application.ScreenUpdating = True '恢復(fù)刷新屏幕 Range('A1').Select End Sub Sub 放大圖表1() Application.ScreenUpdating = False '暫停刷新屏幕 Application.DisplayFullScreen = True Sheets('儀表板').Select Range('A1:Q37').Select ActiveWindow.Zoom = True Application.ScreenUpdating = True '恢復(fù)刷新屏幕 End Sub Sub 放大圖表2() Application.ScreenUpdating = False '暫停刷新屏幕 Application.DisplayFullScreen = True Sheets('儀表板').Select Range('Q1:AF37').Select ActiveWindow.Zoom = True Application.ScreenUpdating = True '恢復(fù)刷新屏幕 End Sub 其他圖表放大代碼依此類推即可。在開發(fā)工具插入宏按鈕，將以上代碼指定給相應(yīng)宏按鈕，將宏按鈕放置在每個(gè)圖表的右下角即可。 至此，大功告成。 寫到這，可能有人心里會(huì)有疑問(wèn)：切片器和數(shù)據(jù)透視圖的組合，不是最經(jīng)典的用法并且自帶交互效果嗎？ 本方法與切片器常規(guī)應(yīng)用方法的區(qū)別 常規(guī)用法：以數(shù)據(jù)透視表為橋梁，將切片器與數(shù)據(jù)透視圖進(jìn)行關(guān)聯(lián)，讓切片器充當(dāng)動(dòng)態(tài)交互式圖表的控件，對(duì)透視表切片可在透視圖中取得動(dòng)態(tài)交互效果。數(shù)據(jù)透視圖中展示的數(shù)據(jù)是按照切片器篩選條件，在透視表中運(yùn)算后的聚合結(jié)果。
怎么樣，小伙伴們學(xué)會(huì)了嗎？如果你覺(jué)得有幫助，請(qǐng)記得給我好看哦。 |
|
|
本例方法：并未將切片器與數(shù)據(jù)透視圖進(jìn)行關(guān)聯(lián)，而是以切片器為開關(guān)，觸發(fā)了數(shù)據(jù)透視表更新事件，后續(xù)通過(guò)自動(dòng)篩選和定義名稱，生成了系列圖表。這是兩者本質(zhì)上的差別。
