Excel動態圖表制作技巧

石曉玲　宋　越

（泰州職業技術學院信息技術學院　泰州　225300）1（泰州市人民檢察院　泰州　225300）2

?

Excel動態圖表制作技巧

石曉玲1宋越2

（泰州職業技術學院信息技術學院泰州225300）1
（泰州市人民檢察院泰州225300）2

摘要本文重點介紹了創建Excel動態圖表的三類方法，對制圖過程中涉及的關鍵技術、技巧和注意點做了深入的探討。

關鍵詞Excel動態圖表函數VBA

信息社會，從商業組織的市場分析、金融部門的成本控制、生產企業的質量管理，到各行各業的考勤統計和薪資計算，數據化管理已成為大數據時代的必然選擇。Excel作為PC機普及以來使用最為廣泛的軟件之一，集成了最優秀的數據計算與分析功能。除此之外，Excel還能將枯燥乏味的數字報表轉化為生動形象、有說服力的圖表，使數據關系得到最直接的表達。

動態圖表是利用Excel的函數、名稱、空間等功能實現的交互展示圖表［1］。與普通圖表相比，動態圖表突破了空間限制，能夠展示出更多的數據信息；重新定義數據輸入輸出的方式，將靜態的傳統的圖表以更靈活、交互、實時的方式展現出來；實現對靜態圖表的批量制作，大大節省制圖的時間。

本文對excel動態圖表的在制作中涉及的方法及技巧作出深入探討。

一、利用公式和控件構建動態圖表

使用公式和控件創建動態圖表，需要四個控件即數據源、函數、控件、圖表共同合作。其中數據源的形式決定了數據的組織方式并決定了動圖的交互展示的維度；從數據源中提取建圖數據常用的函數有VLOOKUP、INDEX、OFFSET、CHOOSE、MATCH等函數；為使圖表具有交互性，需要在數據選擇時設置有效性或在圖表中插入進行數據切換和展示的觸發組件，如單選按鈕、復選框、滾動條等控件；圖表是數據展現的載體，應根據數據的特點選擇合適的圖表類型來展現，如柱狀圖、折線圖、餅狀圖、條形圖、面積圖、散點圖、股價圖、曲面圖、圓環圖、氣泡圖、雷達圖等，不同的圖表還可以根據布局和樣式進行設置，也可以幾種圖表組合表示［2］。

1、利用VLOOKUP函數組織數據源創建動態圖表

本方法的關鍵步驟是創建如圖3所示的動態數據源表。首先，將B3單元格設置數據有效性為月份（定義名稱“月份”為1月～12月），生成如圖4所示的動態下拉列表。而圖中的C6～C8中使用查找函數VLOOKUP返回當月的銷售額，如“C6==VLOOKUP（$B$3，$A$20：$D$31，A6+1，FALSE）”，即可生成動態數據源。由動態的數據源，即可創建銷售金額簇狀柱形圖如圖2所示。

2、利用控件創建簡單的動態圖表

原始數據如圖5所示：

動態查詢效果圖，如下圖6所示：

創建思路：

（1）該動態圖表使用了Activex控件-組合框控件控制月份的顯示。在此需添加輔助月份信息$K$2：$K$7，設置LinkedCell到輔助單元格$K$1，最大值為6、最小值為1，與月份選擇相對應。

（2）在編輯圖表的數據源時，軟件對數據源格式有限制，不允許直接插入公式。故須事先定義好兩個名稱“銷售額”、“銷售表月份”，作為“系列值”“系列名稱”的數據源。

（3）在定義數據源名稱時，應使用INDEX或OFFSET函數建立動態數據區域。如：“銷售表月份=INDEX（銷售表！$D$1：$I$1，，銷售表！$K$1）”。

3、多控件聯動創建復雜動態圖表

控件之間的配合還可以實現相互聯動，從而實現更靈活的查詢。如對同樣的數據源圖5進行查詢時，通過單選按鈕可以決定組合框鏈接哪個選項列表，進而決定以哪個維度來展現數據。

創建思路：

（1）使用單選按鈕實現按人員查詢和按月份查詢，并用

IF來定義名稱“查詢方式”，映射到輔助列：“查詢方式=IF（銷售表！$K$11=1，銷售表！$L$2：$L$9，銷售表！$K$2：$K $7）”，其中$K$11是單選按鈕鏈接到的單元格，$L$2：$L$9 和$K$2：$K$7為輔助列，存放人員名單和月份信息。

（2）使用組合框窗體控件，進行具體顯示項的選擇。如圖7，在確認“按銷售員查詢”后，選擇“徐琴”查詢各月份銷售數據。

（3）定義名稱“銷售數據源”、“橫坐標”，對數據源的圖表項系列、水平軸標簽的設定。如“銷售數據源=IF（$K $11=1，OFFSET（$C$1，$L$11，1，1，6），OFFSET（$C$1，1，$L$11，8，1））”，其中$L$11為組合框窗體控件鏈接到的單元格，實現數據源的動態選擇。

在動態圖表中，控件相當于“發射器”，在控件中做出選擇時，控件就發出“信號”。函數則起著“定位器”的作用，根據控件的信號定位出作圖需要引用的數據。其中，查找與引用類的函數的熟練使用是動圖創建的基礎。

二、使用切片器創建動態圖表

Excel 2010版以后切片器的出現，使動態圖表的制作變得簡單輕松。對于清單類型的數據表，直接生成透視表（如圖9所示），通過透視表變化出多個需要的表格或圖表，最后插入切片器（如圖10所示），用這個切片器驅動多個透視圖。

特別指出，Excel 2010的切片器不支持表，需要使用透視表。對于非清單數據的二維表格制作透視表的步驟這里不做贅述。

三、使用VBA創建動態圖表

VBA的全稱是Visual Basic for Application，是基于Visual Basic for Windows發展而來的可視化編程工具。Exce VBA是指應用于Excel中、可擴展功能的VBA編程語言［3］。通過Exce VBA編程語言，能夠實現Excel中所有工具的功能，創建圖表也不例外。

對A1：D6區域的數據，使用VBA代碼創建柱形圖。當選擇B1：D1中任一單元格時（如C1），圖表顯示如圖12所示柱形圖：

在工作表中選擇A2：A6中的任一單元格（如A6），圖圖13表顯示如所示柱形圖：

由此實現圖表的動態顯示。行數據選擇關鍵代碼如圖14所示，列數據選擇類推。對于2010以上版本的Excel，包含VBA代碼的文檔不再允許被保存為.XLSX擴展名，而要保存為基于XML且啟用宏的.XLSM格式。

VBA代碼創建動態圖表的方法具有良好的可擴展性，更利于在其他的平臺上輸出。

四、結語

1、無論哪種方法創建動態圖表，都需要有：決定顯示方式的控件或操作、定位數據源的公式或語句，才能在傳統平淡的圖表上加載神奇的動態效果。

2、插入表單控件、Activex控件及錄制宏等操作需要首先啟用功能區的開發工具選項卡。打開VBA窗口還可以使用快捷鍵操作。

3、圖表的展示在PPT應用中比較頻繁。Excel動態圖表可以導出為Flash格式，插入PPT中進行動態演示。也可以將包含動態圖表的Excel文件選擇性鏈接入PPT（或者嵌入），在演示狀態下點擊該鏈接（或圖標），即可進入excel進行圖表的動態演示。

參考文獻

［1］徐軍泰著.Excel動態圖表制作與職場數據溝通［M］.北京：機械工業出版社，2014.1.

［2］張偉等著.基于EXCEL圖表的專題圖制作技巧［J］.測繪與空間地理信息，2014.10.

［3］ExcelHome著. EXCEL2010實戰技巧精粹［M］.北京：人民郵電出版社，2013.4.

石曉玲，女，1973.10，碩士，副教授。主要研究領域：計算機數據挖掘。

宋越，男，36，江蘇泰州人，研究方向：計算機技術。

Skills on Making Dynamic Excel Chart

Shi Xiaoling1Song Yue2
（College of Information Technology，Taizhou Polytechnic College Taizhou 225300）1
（Taizhou Municipal People's Procuratorate Taizhou 225300）2

AbstractThis paper introduces how to create dynamic Excel charts by three kinds of methods，and makes a deep discussion on the techniques of drawing process.

KeywordsDynamic Excel charts Function VBA

中圖分類號TP317.3

文獻標識碼B

文章編號160518-7283

作者簡介