上機題7

1、用組合框控件控制圖表顯示

組合框是一個Excel表格中的下拉列表框，用戶可以在獲得的列表中選擇項

目，選擇的項目將出現在上方的文本框中。當需要選擇的項目較多時，使用選項

按鈕來進行選擇就不合適了，此時可以使用“組合框”控件來進行選擇。請使用

“組合框”控件選擇Excel圖表中需要顯示的數據，原始數據如圖所示：

ABCDEFG,

12014年2015年2016年2017年2018年2019年

2青義店296722493043243223583144

3龍門店275221302778244830603400

4方水店340028392523214233512381

5普明店331824822561292728532859

6松垓店216232902585255530133157

以下是操作步驟：

①啟動Excel并打開工作表，將B1:G1單元格區域中的內容復制到B8:G8

單元格區域中。在A9單元格中輸入公式“=OFFSET($A$l,$A$8,COLUMN(A$l)-

l)”，將該公式向右填充到G9單元格中，如圖所示。

SUM▼=OFFSET($A$1,$A$8,COLUMN(A$1)-1)

ABCDEFG

1]2014年2015年2016年2017年2018年2019年

青

店

2義

296722493043243223583144

店

龍

3門

275221302778244830603400

店

方

4水

340028392523214233512381

明

店

5普

怔

店

松331824822561292728532859

6

216232902585255530133157

7

8[2014年2015年2016年2017年2018年2019年

92014年2015年2016年2017年2018年2019年

該公式的含義是，以$人$1為參照，移動$A$8單元格中的數字所規定的行數,

和COLUMN(A$1)-1)所規定的歹U數，由于該單元格是需要向右填充的，所以獲取

的列號會依次變為Bl、C1等等，也就是說會從1依次變為2、3等等，而這個

數字總會減1,就是說OFFSET函數的偏移量會依次為0、1、2等等。整個公式

的意思是第9行各單元格會依據A8中的數據從A1開始移動到哪個單元格的數

字來填充到相應的單元格中。將來就依據這些數據作圖。

②在A8單元格中輸入一個小于7的數字(這只是針對本例的，因為本例

中要依據的數字就在第8行，所以不能大于等于8),選擇A8-G9單元格區域，

在工作表中創建柱形圖，如圖所示。

812014年2015年2016年2017年2018年2019年1

9.青義店296722493043243223583144』

10

11

121

131

141

15

16

17

18

19

2QJ

I21

22J

“

③在“開發工具”選項卡的“控件”組中單擊“插入”按鈕，在打開的列

表中選擇“表單控件”欄中的“組合框”控件，拖動鼠標在圖表中繪制一個組合

框，如圖所示。

O0

o

圖表標題e0

3500

2014年2015年2016年2017年2018年2019年

④鼠標右擊控件，選擇關聯菜單中的“設置控件格式”命令打開“設置控

件格式”對話框，在“控制”選項卡中將“數據源區域”設置為A2:A6單元格

區域，將“單元格鏈接”設置為A8單元格，將“下拉顯示項數”設置為5,如

圖所示。完成設置后單擊“確定”按鈕關閉對話框。

o

圖表標題

o

3500

3000

2500設置對象格式

2000

1500大小屬性可選文字控制

1000

500

0

取消

⑤在圖表中單擊控件將獲得一個下拉列表，在下拉列表中選擇相應的選項

即可在圖表中顯示對應的數據系列，如圖所示。

7

832014年2015年2016年2017年2018年2019年!

9.水店3400283925232142335123811

10—Q

11圖表標題方領

12

4000

13

3500

14

3000

15

16k2500

2000Q

17

1500

18

1000

19

20500

210

32014年2015年2016年2017年2018年2019年

22

----------------------------------O----------------------------------------------

思考：此題與第6章第3節的教學案例”簡單動態圖”制作是否相像?

2、用“選項按鈕”控件控制圖表顯示

在使用Excel創建圖表時，經常會遇到圖表中存在多個數據系列的情況。如

果需要一次只顯示其中的一個數據系列，除了可以使用“視圖管理器”和篩選功

能來實現之外，還可以通過“選項按鈕”控件來實現。請使用“選項按鈕”控件

選擇Excel圖表中需要顯示的數據，原始數據如圖所示：

1ABC

數碼產品季度銷量統計表

2

智能手機平板電腦

1207

341081

139509

5431333

一

60414111265

以下是操作步驟：

①啟動Excel并打開工作表，將表中A3:A6單元格的數據復制到E3:E6單

元格中。在F3單元格中輸入公式“=OFFSET(A3,0,$H$3)”，將公式向下復制到

其下的3個單元格中。在H3單元格中輸入數字“1"o此時，F3:F6單元格區域

中將顯示“智能手機”的數據，如圖所示。

SUM▼?X?A=OFFSET(A3,0,$H$3)

ABCD二GH

1I

2_..智能手機平板電腦

3)|01I1508150811_11

4021413915091439

±5413331543

1141』II

1265

②以E3:F6單元格區域中的數據為數據源來創建圖表，如圖7B-2b所示。

AlBICIDIEIFIGIH

11數碼產品季度銷量統計表

21智能手機平板電腦

31

451QI15081207QI15081

6一Q214391509Q21439

-

7Q315431333Q31543

Q414111265.041411

8-=

9

圖表標題

101

111600

121550

13

"1500

,1450

15師

1400

1350

1300

③在“開發工具”選項卡的“控件”組中單擊“插入”按鈕，在打開的列

表中選擇“表單控件”欄中的“選項按鈕”控件，拖動鼠標在圖表中添加該控件,

如圖所示。

文件開始插入開發工具幫助PowerPivot格式Q告訴我

加ExcelCOM加載項插入二

載項加載項

加

選項按鈕3“

ABC,D

數碼產品季度銷量統計表

2

智能手機平板電腦

3

415081207

O1喳16nq

5

6圖表標題

7

1550

91500

101450

iann

然后將插入點光標放置到控件中,將控件顯示的標題修改為“智能手機”，

如圖所示。

鼠標右擊控件，選擇“設置控件格式”命令打開“設置控件格式”對話框,

進行如圖所示的設置，完成設置后單擊“確定”按鈕關閉對話框。

④將“智能手機”選項按鈕復制一個（復制的方式最好是選中“智能手機”

選項按鈕后，按住Ctrl鍵，然后用鼠標左鍵拖拽，如果用通常方式復制很容易復

制成一個對象而不是控件），將其顯示的標題文字更改為“平板電腦”，按Ctrl

鍵同時選擇這兩個控件，鼠標右擊選擇后的對象，選擇關聯菜單中的“組合I組

合”命令將它們組合在一起，如圖所示。

78|@智刖―6圖表標題

9一

一

10|

一

11

12

13

141

151

161

完成設置后就可以通過單擊控件來選擇圖表顯示的數據了，如圖所示。

,ABCDEFG-H」

數碼產品季度銷量統計表—

2I智能手機平板電腦

3Q115081207Q112072

4JQ214391509Q21509

5jQ315431333Q31333

6Q414111265Q41265

7

8。C智智里能手手機機做9平平板板電電腦腦圖圖表表標標題題

9—1600

1600

1°_1400____

1400

1200------

121lo0o0o0

ir800

14600

15400

16200

170

Q1Q2Q3

18

如果還想進行格式設置，就在此時■進行。

3、使用ActiveX控件啟動宏

在Excel中創建的宏實際上是一段程序代碼，它是一個Sub過程。在工作表

中添加ActiveX控件后，用戶可以在控件的事件過程中輸入代碼，從而實現控制

宏的啟動。請使用“命令按鈕”控件來啟動一個宏。

①啟動Excel并打開包含宏的工作表，在“開發工具”選項卡的“控件”

組中單擊“插入”按鈕，在打開的下拉列表中選擇“ActiveX控件”欄中的“命

令按鈕”控件，如圖所示。

■審聞視圖開發工具幫助PC

凰

設計模式

M加載項Q源

團

表單控件

口置|畫囹El@

GH

二ActiveX控件

葭低價買入價

1.0191.095□?防ES畫H

].8520.908

3.8290.868命令按鈕（ActiveX控件）

J.4310.4520.4

].2730.2760.2插入命令按朝件。

3.2750.2790.

②拖動鼠標在工作表中繪制一個命令按鈕，此時可以從編輯欄上看出，插

入ActiveX控件與表單控件是完全不同的，如圖所示：

鼠標右擊該按鈕，選擇關聯菜單中的“查看代碼”命令。此時將打開VBA

編輯器，在按鈕的代碼窗口中自動添加了按鈕的Click事件過程。在該過程中添

加宏所對應的過程名“成交量”，如圖所示。

一H」I.J_]__:」L1*_LN

價買久價賣出價成交里

91.0951.096124448------O-&剪切

■20.9080.909103690(I)

：90.8680.86936605電復制g

：10.4520.45334185

30.2760.27726676兆粘貼(R)

’50.2790.2824586

70.7720.78321167國屬性①

130.2050.20619475

10.3880.38915793

80.3860.387150843查看代碼M

90.1910.19213920

皆MicrosoftVisualBasicforApplications-7-上機3-Activ

文件(E)編輯(￡)視圖M插入(1)格式9)調試⑼運

國卷雪皆

工程?VBAProjectX

叢7-上機3-Active控件執行宏.xlsn

國宴巨

I^CoBBandButtonl

BVBAProject(7一上機3

PrivateSubCommandBu11onl_C1ick()

臼…占MicrosoftExcel對:成交里

HiSheet3(上證B層EndSub

③切換到Excel程序窗口，鼠標右擊按鈕，選擇關聯菜單中的“屬性”命

令打開“屬性”對話框，將控件的“Caption”屬性設置為“成交量”，如圖所示:

?HIJM

價買久價賣出價成交里

￡CommandP,-**

)191.0951.096124448—剪切①

)520.9080.909103690

八ACAACL

屬性電復制(Q

晶

|CoBBandButtonlCommandButton粘貼(E)

按字母序|按分類序|

臼屬性Q)

(名稱)CommandButtonl

Accelerator星查看代碼M

AutoLoadFalse

④這樣將更改按鈕上顯示的文字，在“開發工具”選項卡的“控件”組中

單擊“設計模式”按鈕退出控件的設計模式，如圖所示：

審閱視圖開發工具幫助PowerPivot

葩映射屬性

E苴擴展包

源

囹刷新緘

XML

設計模式

UT啟用或取消設計模式。

:入價賣出價成交里成交量

.0951.096124448

單擊工作表中的命令按鈕控件即可啟動宏了。此題要注意的是使用表單控件

中的“按鈕”和使用ActiveX控件中的“按鈕”的區別：前者是直接右擊表單按

鈕，在彈出的“指定宏”對話框中為其指定宏，后者則需要通過VBA語句在該

按鈕的Click事件過程指定宏名。

課后習題7

1、使用列表框控件，依據姓名選擇自動突出顯示所在行，效果如圖所示:

ABCDEFG

學生成績表

1.

楊源NA

劉衛中

張愛國

椰婷

SB氏

2

3序號姓名語文數學英語總分平均分

41楊源言77989026588.33

52劉衛中90898826789.00

63張愛國8872672277567

7_4柳原92748625284.00

85雷民81927224581.67

96馬耀華67709423177.00

107侗明玉74727321973.00

118任正義65657920969.67

129羅遠浩62687520568.33

步驟：

點擊菜單欄中的“開發工具”下，點擊“插入”按鈕，選擇列表框控件，在

工作表適當位置畫出列表框，右擊該列表框，在隨后彈出的下拉菜單中，點擊“設

置控件格式”，在隨后彈出的“設置控件格式”對話框中，點擊“控制”選項卡，

進行如圖所示的設置：

設置控件格式？X

⑥單選⑤

Og(M)

O擴展?

0三維陰影⑶

確定取消

選取A4:G12,點擊“開始I樣式I條件格式I新建格式規則”,在彈出的“新

建格式規則”對話框中，在上面的“選擇規則類型”中選擇最后一項''使用公式

確定要設置格式的單元格”，在中間的“編輯規則說明”中輸入公式：

=$G$2=row()-3

為什么是row()-3呢？因為正式數據前面正好有3行！如果多插入1行，讓

列表框占據2行，則此處公式就該寫成row()-4!否則，點擊第一人不會有高亮

行，點擊第2人卻是第1人高亮！

然后點擊下方的“格式”按鈕設置好顏色格式，再點擊“確定”，如圖所示：

編輯格式規則?X

選擇規則類型⑤)：

A基于各自值設置所有單元格的格式

A只為包含以下內容的單元格設置格式

?僅對排名靠前或靠后的數值設置格式

A僅對高于或彳氐于平均值的數值設置格式

A僅對唯一＜或重復值設置格式

?使用公式確定要設置格式的單元格

編輯規則說明(E)：

確定取消

最后調整列表框的大小和位置使其正好將G2單元格遮住即可。

2、使用“滾動條”控件實現在Excel圖表中依次高亮顯示數據點，效果如下

圖所示:

ABCDEFG

年份2014年2015年2016年2017年2018年2019年

2銷售量230028722980284025312760

34#N/A#N/A#N/A2840#N/A#N/A

4

5

6

7圖表標題

8

9

1C

11

12

13

14

15

16

172014年2015年2016年2017年2018年2019年

1R

操作如下：

①啟動Excel并打開工作表，在工作表的B3單元格中輸入公式

“=IF($A$3=COLUMN()-1,B2,NA())”，拖動填充控制柄向右填充公式。

SUM▼：=IF($A$3=COLUMN()-1,B2,NA())

/A.B;CDEFGH

1年份_2014年―2015年2016年2017年2018年2019年

2.BlfiT2300128722980284025312760

4j#N/A1#N/A#N/A2840#N/A#N/A

3?-------

在12單元格中輸入文字“高亮顯示”，將文字設置為在單元格中右對齊。

在J2單元格中輸入公式“=OFFSET(A1,,A3)”(即移動0行)，如圖1所示。

SUM-：X?A=OFFSET(A1?A3)

ABCID，E?F?GH?—1—

11年份12014年2015年2016年2017年2018年2019年

2l銷售量.230028722980284025312760高亮顯示宙滓1

34]#N/A#N/A#N/A2840#N/A#N/A

②在“開發工具”選項卡的“控件”組中單擊“插入”按鈕，在打開的列

表中選擇“表單控件”欄中的“滾動條”控件，拖動鼠標在工作表中繪制一個

“滾動條”控件，為了美觀起見，現在就可以將該滾動條的大小設置好，并使它

正好掩蓋了12和J2兩個單元格。

③鼠標右擊工作表中的控件，選擇關聯菜單中的“設置控件格式”命令打

開“設置控件格式”對話框，在“控制”選項卡中對控件進行如圖所示設置。

G1_H_1K

2019年Q_O

2760

#N/A5

設置控ms式?X

大小屬性可選文字

取消

④在工作表中選擇B1:G3單元格區域，為其添加折線圖，注意選擇“帶數

據標記的折線圖”這一亞型，如圖所示。

BCDEFGH,l|J|K

2014年戶015年2016年2017年2018年2019年

推薦的圖表所有圖表

9最近

二?模板

Hw柱形圖

X折線圖帶數據標記的折線圖

3餅圖

置條形圖

S面積圖

-應XY散點圖

尬股價圖

H卷曲面圖

⑤在圖表中將圖例項刪除，為數據系列添加和格式化數據標簽，鼠標單擊

工作表中的滾動條控件，即可實現題目所要求的高亮顯示圖表中某個年份的數據。

如果需要對圖表標題、背景等進行編輯和格式化操作，也在這里進行。注意：選

中數據系列（那條折線）后選“設置數據系列格式"，線條的顏色和粗細在這里

設置:

設置數據系列格式▼X

系列選項▼

⑦Qill

々戰條?標記H

/建條

O無線條(N)

@實線兇、

O漸變線鬲、

O自動Q)\

蝌工),，叵▼

透明度①110%/:

m^(w)|百磅：

復合類型?|=---

"IR

標記的類型和大小在這里設置:

設置數據系列格式▼X

系列選項▼

⑦Qill

々發條人.標記F

/標記選項

'：'自動(U)

O無稅條⑻)

___________1-□1

mr...i>

在此種狀況下如果還想對高亮顯示的那個數據標志進行單獨的格式設置，只

需選中該標志(注意不要選成了標簽)后單獨設置即可。

2014年2015年2016年2017年2018年2019年

此圖本質上是兩個數據系列共用主坐標，其中一個系列是表中第二行的數據,

這些數據是齊備的，第二個系列是表中第三行的數據，該系列每次只有一個數據，

疊加在第一個系列中對應的年份上而已。

3、在EXCEL表中設置兩個按鈕控件，要求每按一次其中一個