Excel教程下載和軟件下載中心,Microsoft技術社區聯盟成員,全球極具影響力的Excel門戶,Office技術培訓社區

Excel高階圖表,牛到不行了

2019-03-20  作者:ExcelHome  閱讀:
今天為大家分享一個非常實用的圖表技巧。
點擊左側省市二級聯動切片器,即可控制整個可視化儀表板。對于單個圖表,設有放大鏡功能,以便更清晰地查看圖表內容。
閑言少敘,先上效果圖:


涉及的知識點:
  • 切片器(鏈接數據透視表,切片器格式化)
  • Worksheet_PivotTableUpdate透視表更新事件
  • VBA自動篩選程序(AutoFilter代碼用于篩選該城市經銷商)
  • 定義名稱(offset函數動態引用區域,用于制作圖表時添加序列)
  • 通過添加序列的方式制作常規圖表
  • VBA代碼添加放大及返回的宏按鈕
之前在Excel連接SQL Server數據庫的文章中,曾經介紹過切片器及數據透視表更新事件的使用。本例中與其原理完全一致。

不太容易構思之處在于:
1)通過VBA自動篩選程序將該城市自動篩選后,粘貼進指定工作表,繼而通過定義好的名稱動態捕捉作圖數據,用于生成各種圖表。
2)用切片器替代了省市二級下拉菜單的制作,不僅制作過程簡單,而且選擇項目方便,目視設計美觀。

01

圖表構思和數據整理
根據待分析的數據源和期望呈現的可視化儀表板效果,勾勒草圖,以終為始,尋找到能實現和可實現兩者的平衡點。按此要求整合數據。
本例中用到的是汽車行業經銷商分析數據,整理格式如下:


02

準備數據透視表和切片器
2.1為儀表板準備由省市數據源制作的數據透視表,將城市拖入行標簽。


注:為了數據透視表更新事件能正常運行,這里建議將透視表與儀表板放在同一張工作表內。
2.2為儀表板準備選擇控件-切片器


2.3切片器自定義格式化
選中要設置格式的切片器-選項-新建切片器樣式。這里可以對整個切片器,以及頁面,已選擇的帶有/無數據的項目,懸停帶有/無數據的項目進行自定義格式化。本例中比較巧妙的一點是對于"已取消選擇的無數據的項目"均設置為黑色,相當于將城市切片器上不相關的選項卡"藏"了起來。

此外,還可以右鍵單擊切片器,在大小和屬性中進行設置。在框架-按鈕高度可設置按鈕的高度和寬度以及列數,設置好后勾選禁止調整大小和移動,這樣可以避免用戶更改你的自定義切片器格式。為了更好滴避免被誤操作,還需進行工作表保護,為了不影響切片器功能,還要在屬性中將說定復選框取消勾選。


通過數據透視表更新事件,在點擊切片器時,觸發事件,執行相應代碼,實現動態效果。在此過程中,切片器充當的是等同于常規控件的作用,但其交互效果更好,尤其是對切片器進行自定義格式化后。

03

設置數據透視表更新事件
數據透視表更新事件PivotTableUpdate:VBA中的事件就像是一個聲控開關,當有聲音響起時燈就會亮起,同理當數據透視表更新時,就會觸發事件代碼運行。這里切片器對數據透視表進行切片時,相當于一個聲控開關,Excel會捕捉到這一瞬間,并執行以下藍色字體的自定義代碼。本案中是對相應城市的所有經銷商進行自動篩選,并將篩選結果粘貼到新表中,用于定義名稱及生成動態圖表。



這段代碼實現的功能是將第一步準備的數據源表,以第一行為篩選行,以第31列(城市列)為篩選字段,以儀表板工作表的CB3單元格(數據透視表切片后結果單元格,即城市)為篩選條件進行自動篩選,將結果粘貼到“抓取”工作表中,用于下一步制圖。

04

定義名稱動態引用區域
定義名稱被廣泛地應用于動態交互式圖表中,通過OFFSET函數生成一個動態引用區域,并在生成圖表時將該定義好的名稱用于圖表的數據區域中,使圖表捕捉的制圖數據源區域具備自動延展的動態效果。


注:
因每個城市經銷商數量不同,COUNTA函數起到了計算非空行也即城市數量的作用;
對于沒有經銷商的城市,這里外套了一個if函數,即將行標題作為結果返回。否則會彈出定義名稱引用區域錯誤的對話框。

05

添加序列制作圖表
常規圖表中的圖形數據源,是由不同的序列組成的。以二手車銷量和置換的圖表為例,這里定義了兩個序列和一個水平分類軸標簽,分別為:
二手車置換量,數據引用區域='4.切片器控制儀表板.xlsm'!二手車置換量
二手車銷售量,數據引用區域='4.切片器控制儀表板.xlsm'!二手車銷售量
水平分類軸標簽,數據引用區域='4.切片器控制儀表板.xlsm'!經銷商名稱
注:這里的數據引用區域引用的是之前定義過的名稱,這一步非常關鍵。


通過以上操作,即可生成二手車的包含兩個序列的圖表,并且這個圖表的水平分類軸標簽和Y軸序列所對應的數據區域,均是根據該城市的經銷商數量自動延展的,沒有任何冗余。


06

添加放大和返回按鈕
通過選擇對應的range區域,設置合適的縮放級別,便可實現放大和返回的效果。
Sub 返回()
Application.ScreenUpdating = False '暫停刷新屏幕
Sheets("儀表板").Select
ActiveWindow.Zoom = 40
Application.ScreenUpdating = True '恢復刷新屏幕
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 '恢復刷新屏幕
End Sub

Sub 放大圖表2()
Application.ScreenUpdating = False '暫停刷新屏幕
Application.DisplayFullScreen = True
Sheets("儀表板").Select
Range("Q1:AF37").Select
ActiveWindow.Zoom = True
Application.ScreenUpdating = True '恢復刷新屏幕
End Sub

其他圖表放大代碼依此類推即可。在開發工具插入宏按鈕,將以上代碼指定給相應宏按鈕,將宏按鈕放置在每個圖表的右下角即可。
至此,大功告成。
寫到這,可能有人心里會有疑問:切片器和數據透視圖的組合,不是最經典的用法并且自帶交互效果嗎?
本方法與切片器常規應用方法的區別
常規用法:以數據透視表為橋梁,將切片器與數據透視圖進行關聯,讓切片器充當動態交互式圖表的控件,對透視表切片可在透視圖中取得動態交互效果。數據透視圖中展示的數據是按照切片器篩選條件,在透視表中運算后的聚合結果。

本例方法:并未將切片器與數據透視圖進行關聯,而是以切片器為開關,觸發了數據透視表更新事件,后續通過自動篩選和定義名稱,生成了系列圖表。這是兩者本質上的差別。

轉載請注明來源"ExcelHome"并保留原文鏈接。
固定鏈接:http://www.fvtgnn.tw/lesson/animation/excel/1944.html
分享到:
Copyright 1999 - 2017 Excel Home.All Rights Reserved.
本站特聘法律顧問:徐懷玉律師 李志群律師   滬ICP備11019229號

滬公網安備 31011702000001號

征信       
35选7摇奖器