辦公小技巧:圖表中的交互 數據鉆取還能這么用

CFan 電腦愛好者 2019-06-17 09:57應用 標簽:圖表 技巧 辦公 數據

簡單的數據可以用一個圖表表達出來,但復雜些的數據用一個圖表來表達就顯得捉襟見肘了。若將圖表做成動態可鉆取數據的效果,點擊交互控件時,總圖表發生相應變化,點擊總圖表中的系列時就會得到和這個系列相關的其他數據,再利用其他數據生成相應的子圖表,問題就能得到解決。這樣的效果利用控件、少量VBA代碼外加一些公式即可實現(圖1)。

1814A-SJZQ-1

1. 設置交互控件

在工作表的F1:F3分別輸入月份、地區、產品,H1:H2分別輸入地區、產品,I1:I2分別輸入月份、產品,J1:J2分別輸入月份、地區。

①構建下拉列表1

點擊“開發工具→插入→表單控件→組合框”,在合適位置畫出一個組合框控件,將組合框的數據源區域設置為F1:F3,單元格鏈接為G1。

②構建下拉列表2

點擊“公式→名稱管理器→新建”,名稱為“z_cd”,引用位置處為“=CHOOSE(Sheet1!$G$1,Sheet1!$H$1:$H$2,Sheet1!$I$1:$I$2,Sheet1!$J$1:$J$2)”;再畫出一個組合框控件,數據源區域為“z_cd”,單元格鏈接為G2。

在G4單元格輸入“=INDIRECT(CHOOSE($G$1,"H","I","J") & $G$2)”(圖2)。

1814A-SJZQ-2

2. 編寫數據鉆取VBA代碼

點擊“開發工具→Visual Basic”進入VBA編輯器,點擊“插入→類模塊”,選中插入的“類1”,選擇“視圖→屬性窗口”,將類的名稱改為“CEventChart”,雙擊這個類進入編輯窗口,輸入如圖3所示代碼(圖3)。

1814A-SJZQ-3

接下來,點擊“插入→模塊”,雙擊“模塊1”,在代碼編輯窗口輸入如圖4所示代碼(圖4)。

1814A-SJZQ-4

接下來,雙擊“ThisWorkBook”,在右側編輯窗口輸入如圖5所示代碼(圖5)。

1814A-SJZQ-5

這樣,當點擊圖表中的系列時,系列名稱就會出現在G3單元中。

小提示:

上述代碼也可以通過網盤下載(鏈接:https://pan.baidu.com/s/1vZhfg86pAHg5tc4UB9E4lw 密碼:2zba),直接復制粘貼使用。

3. 構建圖表數據源

圖表的數據源是根據上述控件的當前索引值獲得的。

①構建主圖表數據源

分別新建名為lb_11、lb_22、lb_33的3個名稱,lb_11的引用位置為“=INDEX(Sheet1!$A$1:$A$41,N(IF({1},SMALL(99*(MATCH(Sheet1!$A$2:$A$41,Sheet1!$A$1:$A$41,)<ROW(Sheet1!$2:$41))+ROW(Sheet1!$2:$41),ROW(INDIRECT("Sheet1!1:"&SUM(1/COUNTIF(Sheet1!$A$2:$A$41,Sheet1!$A$2:$A$41))))))))&""”;lb_22、lb_33的引用位置與lb_11的類似,只不過需要將公式中的A分別替換成B、C。

新建名“lb_tmp” 的名稱,引用位置處輸入“=CHOOSE(Sheet1!$G$1,lb_11,lb_22,lb_33)”;

新建名“sum_1” 的名稱,引用位置處輸入“=SUMIF(Sheet1!$A$2:$D$41,lb_11,Sheet1!$D$2:$D$41)”;

新建名“sum_2” 的名稱,引用位置處輸入“=SUMIF(Sheet1!$B$2:$D$41,lb_22,Sheet1!$D$2:$D$41)”;

新建名“sum_3” 的名稱,引用位置處輸入“=SUMIF(Sheet1!$C$2:$D$41,lb_33,Sheet1!$D$2:$D$41)”;

新建名“sum_tmp” 的名稱,引用位置處輸入“=CHOOSE(Sheet1!$G$1,sum_1,sum_2,sum_3)”;

其中,lb_11,lb_22,lb_22分別獲取各列中的不重復值,從而形成數組;lb_tmp確定使用哪個數組作為主圖表的類別圖例;sum_1、sum_2、sum_3分別對各類別數組所對應的數量求和;sum_tmp確定使用哪個求和結果作為主圖表的數據源。

②構建子圖表數據源

新建名“lb_tmp_tmp”的名稱,引用位置為“=IF(Sheet1!$G$4="月份",lb_11,IF(Sheet1!$G$4="地區",lb_22,lb_33))”;

新建名“sum_tmp_tmp” 的名稱,引用位為“=SUMIFS(Sheet1!$D$2:$D$41,IF(Sheet1!$G$1=1,Sheet1!$A$2:$A$41,IF(Sheet1!$G$1=2,Sheet1!$B$2:$B$41,Sheet1!$C$2:$C$41)),Sheet1!$G$3,IF(Sheet1!$G$4="月份",Sheet1!$A$2:$A$41,IF(Sheet1!$G$4="地區",Sheet1!$B$2:$B$41,Sheet1!$C$2:$C$41)),lb_tmp_tmp)”;

其中,lb_tmp_tmp確定使用哪個數組作為子圖表的類別圖例;sum_tmp_tmp根據主圖表中列表及子圖表中列表的選擇來對數量求和,從而作為子圖表的數據源。

4. 插入并設置圖表

插入兩個餅狀圖。右擊第1個餅圖,選擇“選擇數據”,在彈出窗口的“圖例項”處,點擊“編輯”,在系列值處輸入“=Sheet1!sum_tmp”;在“水平(分類)軸標簽”處,點擊“編輯”,在彈出窗口中輸入“=Sheet1!lb_tmp”。右擊第2個餅圖,選擇“選擇數據”,在彈出窗口的“圖例項”處,點擊“編輯”,在系列值處輸入“=Sheet1!sum_tmp_tmp”;在“水平(分類)軸標簽”處,點擊“編輯”,在彈出窗口中輸入“=Sheet1!lb_tmp_tmp”。

最后,將每個下拉列表框都置于頂層,拖放到圖表的各自位置,選中G5單元格,輸入“=CONCATENATE(G3,"各",G4,"銷售情況統計")”,選擇圖表標題,在公式編輯欄中輸入“=Sheet1!$G$5”。這樣,圖表標題就會跟隨控件的選擇而進行變化了。不一樣。

 

内蒙古十一选五基本走势图