石曉玲　宋　越

（泰州職業(yè)技術(shù)學(xué)院信息技術(shù)學(xué)院　泰州　225300）1（泰州市人民檢察院　泰州　225300）2

?

Excel動(dòng)態(tài)圖表制作技巧

石曉玲1宋越2

（泰州職業(yè)技術(shù)學(xué)院信息技術(shù)學(xué)院泰州225300）1
（泰州市人民檢察院泰州225300）2

摘要本文重點(diǎn)介紹了創(chuàng)建Excel動(dòng)態(tài)圖表的三類方法，對制圖過程中涉及的關(guān)鍵技術(shù)、技巧和注意點(diǎn)做了深入的探討。

關(guān)鍵詞Excel動(dòng)態(tài)圖表函數(shù)VBA

信息社會(huì)，從商業(yè)組織的市場分析、金融部門的成本控制、生產(chǎn)企業(yè)的質(zhì)量管理，到各行各業(yè)的考勤統(tǒng)計(jì)和薪資計(jì)算，數(shù)據(jù)化管理已成為大數(shù)據(jù)時(shí)代的必然選擇。Excel作為PC機(jī)普及以來使用最為廣泛的軟件之一，集成了最優(yōu)秀的數(shù)據(jù)計(jì)算與分析功能。除此之外，Excel還能將枯燥乏味的數(shù)字報(bào)表轉(zhuǎn)化為生動(dòng)形象、有說服力的圖表，使數(shù)據(jù)關(guān)系得到最直接的表達(dá)。

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

本文對excel動(dòng)態(tài)圖表的在制作中涉及的方法及技巧作出深入探討。

一、利用公式和控件構(gòu)建動(dòng)態(tài)圖表

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

1、利用VLOOKUP函數(shù)組織數(shù)據(jù)源創(chuàng)建動(dòng)態(tài)圖表

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

2、利用控件創(chuàng)建簡單的動(dòng)態(tài)圖表

原始數(shù)據(jù)如圖5所示：

動(dòng)態(tài)查詢效果圖，如下圖6所示：

創(chuàng)建思路：

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

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

（3）在定義數(shù)據(jù)源名稱時(shí)，應(yīng)使用INDEX或OFFSET函數(shù)建立動(dòng)態(tài)數(shù)據(jù)區(qū)域。如：“銷售表月份=INDEX（銷售表！$D$1：$I$1，，銷售表！$K$1）”。

3、多控件聯(lián)動(dòng)創(chuàng)建復(fù)雜動(dòng)態(tài)圖表

控件之間的配合還可以實(shí)現(xiàn)相互聯(lián)動(dòng)，從而實(shí)現(xiàn)更靈活的查詢。如對同樣的數(shù)據(jù)源圖5進(jìn)行查詢時(shí)，通過單選按鈕可以決定組合框鏈接哪個(gè)選項(xiàng)列表，進(jìn)而決定以哪個(gè)維度來展現(xiàn)數(shù)據(jù)。

創(chuàng)建思路：

（1）使用單選按鈕實(shí)現(xiàn)按人員查詢和按月份查詢，并用

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

（2）使用組合框窗體控件，進(jìn)行具體顯示項(xiàng)的選擇。如圖7，在確認(rèn)“按銷售員查詢”后，選擇“徐琴”查詢各月份銷售數(shù)據(jù)。

（3）定義名稱“銷售數(shù)據(jù)源”、“橫坐標(biāo)”，對數(shù)據(jù)源的圖表項(xiàng)系列、水平軸標(biāo)簽的設(shè)定。如“銷售數(shù)據(jù)源=IF（$K $11=1，OFFSET（$C$1，$L$11，1，1，6），OFFSET（$C$1，1，$L$11，8，1））”，其中$L$11為組合框窗體控件鏈接到的單元格，實(shí)現(xiàn)數(shù)據(jù)源的動(dòng)態(tài)選擇。

在動(dòng)態(tài)圖表中，控件相當(dāng)于“發(fā)射器”，在控件中做出選擇時(shí)，控件就發(fā)出“信號”。函數(shù)則起著“定位器”的作用，根據(jù)控件的信號定位出作圖需要引用的數(shù)據(jù)。其中，查找與引用類的函數(shù)的熟練使用是動(dòng)圖創(chuàng)建的基礎(chǔ)。

二、使用切片器創(chuàng)建動(dòng)態(tài)圖表

Excel 2010版以后切片器的出現(xiàn)，使動(dòng)態(tài)圖表的制作變得簡單輕松。對于清單類型的數(shù)據(jù)表，直接生成透視表（如圖9所示），通過透視表變化出多個(gè)需要的表格或圖表，最后插入切片器（如圖10所示），用這個(gè)切片器驅(qū)動(dòng)多個(gè)透視圖。

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

三、使用VBA創(chuàng)建動(dòng)態(tài)圖表

VBA的全稱是Visual Basic for Application，是基于Visual Basic for Windows發(fā)展而來的可視化編程工具。Exce VBA是指應(yīng)用于Excel中、可擴(kuò)展功能的VBA編程語言［3］。通過Exce VBA編程語言，能夠?qū)崿F(xiàn)Excel中所有工具的功能，創(chuàng)建圖表也不例外。

對A1：D6區(qū)域的數(shù)據(jù)，使用VBA代碼創(chuàng)建柱形圖。當(dāng)選擇B1：D1中任一單元格時(shí)（如C1），圖表顯示如圖12所示柱形圖：

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

由此實(shí)現(xiàn)圖表的動(dòng)態(tài)顯示。行數(shù)據(jù)選擇關(guān)鍵代碼如圖14所示，列數(shù)據(jù)選擇類推。對于2010以上版本的Excel，包含VBA代碼的文檔不再允許被保存為.XLSX擴(kuò)展名，而要保存為基于XML且啟用宏的.XLSM格式。

VBA代碼創(chuàng)建動(dòng)態(tài)圖表的方法具有良好的可擴(kuò)展性，更利于在其他的平臺(tái)上輸出。

四、結(jié)語

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

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

3、圖表的展示在PPT應(yīng)用中比較頻繁。Excel動(dòng)態(tài)圖表可以導(dǎo)出為Flash格式，插入PPT中進(jìn)行動(dòng)態(tài)演示。也可以將包含動(dòng)態(tài)圖表的Excel文件選擇性鏈接入PPT（或者嵌入），在演示狀態(tài)下點(diǎn)擊該鏈接（或圖標(biāo)），即可進(jìn)入excel進(jìn)行圖表的動(dòng)態(tài)演示。

參考文獻(xiàn)

［1］徐軍泰著.Excel動(dòng)態(tài)圖表制作與職場數(shù)據(jù)溝通［M］.北京：機(jī)械工業(yè)出版社，2014.1.

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

［3］ExcelHome著. EXCEL2010實(shí)戰(zhàn)技巧精粹［M］.北京：人民郵電出版社，2013.4.

石曉玲，女，1973.10，碩士，副教授。主要研究領(lǐng)域：計(jì)算機(jī)數(shù)據(jù)挖掘。

宋越，男，36，江蘇泰州人，研究方向：計(jì)算機(jī)技術(shù)。

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

文獻(xiàn)標(biāo)識(shí)碼B

文章編號160518-7283

作者簡介