Ecel數(shù)據(jù)分析教程 第7章上機(jī)題及課后習(xí)題

上機(jī)題7

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

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

目，選擇的項目將出現(xiàn)在上方的文本框中。當(dāng)需要選擇的項目較多時，使用選項

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

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

ABCDEFG,

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

2青義店296722493043243223583144

3龍門店275221302778244830603400

4方水店340028392523214233512381

5普明店331824822561292728532859

6松垓店216232902585255530133157

以下是操作步驟：

①啟動Excel并打開工作表，將B1:G1單元格區(qū)域中的內(nèi)容復(fù)制到B8:G8

單元格區(qū)域中。在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單元格中的數(shù)字所規(guī)定的行數(shù),

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

的列號會依次變?yōu)锽l、C1等等，也就是說會從1依次變?yōu)?、3等等，而這個

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

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

字來填充到相應(yīng)的單元格中。將來就依據(jù)這些數(shù)據(jù)作圖。

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

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

在工作表中創(chuàng)建柱形圖，如圖所示。

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

9.青義店296722493043243223583144』

10

11

121

131

141

15

16

17

18

19

2QJ

I21

22J

“

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

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

框，如圖所示。

O0

o

圖表標(biāo)題e0

3500

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

④鼠標(biāo)右擊控件，選擇關(guān)聯(lián)菜單中的“設(shè)置控件格式”命令打開“設(shè)置控

件格式”對話框，在“控制”選項卡中將“數(shù)據(jù)源區(qū)域”設(shè)置為A2:A6單元格

區(qū)域，將“單元格鏈接”設(shè)置為A8單元格，將“下拉顯示項數(shù)”設(shè)置為5,如

圖所示。完成設(shè)置后單擊“確定”按鈕關(guān)閉對話框。

o

圖表標(biāo)題

o

3500

3000

2500設(shè)置對象格式

2000

1500大小屬性可選文字控制

1000

500

0

取消

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

即可在圖表中顯示對應(yīng)的數(shù)據(jù)系列，如圖所示。

7

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

9.水店3400283925232142335123811

10—Q

11圖表標(biāo)題方領(lǐng)

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節(jié)的教學(xué)案例”簡單動態(tài)圖”制作是否相像?

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

在使用Excel創(chuàng)建圖表時，經(jīng)常會遇到圖表中存在多個數(shù)據(jù)系列的情況。如

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

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

選擇Excel圖表中需要顯示的數(shù)據(jù)，原始數(shù)據(jù)如圖所示：

1ABC

數(shù)碼產(chǎn)品季度銷量統(tǒng)計表

2

智能手機(jī)平板電腦

1207

341081

139509

5431333

一

60414111265

以下是操作步驟：

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

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

其下的3個單元格中。在H3單元格中輸入數(shù)字“1"o此時，F(xiàn)3:F6單元格區(qū)域

中將顯示“智能手機(jī)”的數(shù)據(jù)，如圖所示。

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

ABCD二GH

1I

2_..智能手機(jī)平板電腦

3)|01I1508150811_11

4021413915091439

±5413331543

1141』II

1265

②以E3:F6單元格區(qū)域中的數(shù)據(jù)為數(shù)據(jù)源來創(chuàng)建圖表，如圖7B-2b所示。

AlBICIDIEIFIGIH

11數(shù)碼產(chǎn)品季度銷量統(tǒng)計表

21智能手機(jī)平板電腦

31

451QI15081207QI15081

6一Q214391509Q21439

-

7Q315431333Q31543

Q414111265.041411

8-=

9

圖表標(biāo)題

101

111600

121550

13

"1500

,1450

15師

1400

1350

1300

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

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

如圖所示。

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

加ExcelCOM加載項插入二

載項加載項

加

選項按鈕3“

ABC,D

數(shù)碼產(chǎn)品季度銷量統(tǒng)計表

2

智能手機(jī)平板電腦

3

415081207

O1喳16nq

5

6圖表標(biāo)題

7

1550

91500

101450

iann

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

如圖所示。

鼠標(biāo)右擊控件，選擇“設(shè)置控件格式”命令打開“設(shè)置控件格式”對話框,

進(jìn)行如圖所示的設(shè)置，完成設(shè)置后單擊“確定”按鈕關(guān)閉對話框。

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

選項按鈕后，按住Ctrl鍵，然后用鼠標(biāo)左鍵拖拽，如果用通常方式復(fù)制很容易復(fù)

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

鍵同時選擇這兩個控件，鼠標(biāo)右擊選擇后的對象，選擇關(guān)聯(lián)菜單中的“組合I組

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

78|@智刖―6圖表標(biāo)題

9一

一

10|

一

11

12

13

141

151

161

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

,ABCDEFG-H」

數(shù)碼產(chǎn)品季度銷量統(tǒng)計表—

2I智能手機(jī)平板電腦

3Q115081207Q112072

4JQ214391509Q21509

5jQ315431333Q31333

6Q414111265Q41265

7

8。C智智里能手手機(jī)機(jī)做9平平板板電電腦腦圖圖表表標(biāo)標(biāo)題題

9—1600

1600

1°_1400____

1400

1200------

121lo0o0o0

ir800

14600

15400

16200

170

Q1Q2Q3

18

如果還想進(jìn)行格式設(shè)置，就在此時■進(jìn)行。

3、使用ActiveX控件啟動宏

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

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

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

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

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

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

■審聞視圖開發(fā)工具幫助PC

凰

設(shè)計模式

M加載項Q源

團(tuán)

表單控件

口置|畫囹El@

GH

二ActiveX控件

葭低價買入價

1.0191.095□?防ES畫H

].8520.908

3.8290.868命令按鈕（ActiveX控件）

J.4310.4520.4

].2730.2760.2插入命令按朝件。

3.2750.2790.

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

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

鼠標(biāo)右擊該按鈕，選擇關(guān)聯(lián)菜單中的“查看代碼”命令。此時將打開VBA

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

加宏所對應(yīng)的過程名“成交量”，如圖所示。

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

價買久價賣出價成交里

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

■20.9080.909103690(I)

：90.8680.86936605電復(fù)制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-上機(jī)3-Activ

文件(E)編輯(￡)視圖M插入(1)格式9)調(diào)試⑼運(yùn)

國卷雪皆

工程?VBAProjectX

叢7-上機(jī)3-Active控件執(zhí)行宏.xlsn

國宴巨

I^CoBBandButtonl

BVBAProject(7一上機(jī)3

PrivateSubCommandBu11onl_C1ick()

臼…占MicrosoftExcel對:成交里

HiSheet3(上證B層EndSub

③切換到Excel程序窗口，鼠標(biāo)右擊按鈕，選擇關(guān)聯(lián)菜單中的“屬性”命

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

?HIJM

價買久價賣出價成交里

￡CommandP,-**

)191.0951.096124448—剪切①

)520.9080.909103690

八ACAACL

屬性電復(fù)制(Q

晶

|CoBBandButtonlCommandButton粘貼(E)

按字母序|按分類序|

臼屬性Q)

(名稱)CommandButtonl

Accelerator星查看代碼M

AutoLoadFalse

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

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

審閱視圖開發(fā)工具幫助PowerPivot

葩映射屬性

E苴擴(kuò)展包

源

囹刷新緘

XML

設(shè)計模式

UT啟用或取消設(shè)計模式。

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

.0951.096124448

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

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

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

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

課后習(xí)題7

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

ABCDEFG

學(xué)生成績表

1.

楊源NA

劉衛(wèi)中

張愛國

椰婷

SB氏

2

3序號姓名語文數(shù)學(xué)英語總分平均分

41楊源言77989026588.33

52劉衛(wèi)中90898826789.00

63張愛國8872672277567

7_4柳原92748625284.00

85雷民81927224581.67

96馬耀華67709423177.00

107侗明玉74727321973.00

118任正義65657920969.67

129羅遠(yuǎn)浩62687520568.33

步驟：

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

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

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

進(jìn)行如圖所示的設(shè)置：

設(shè)置控件格式？X

⑥單選⑤

Og(M)

O擴(kuò)展?

0三維陰影⑶

確定取消

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

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

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

=$G$2=row()-3

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

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

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

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

編輯格式規(guī)則?X

選擇規(guī)則類型⑤)：

A基于各自值設(shè)置所有單元格的格式

A只為包含以下內(nèi)容的單元格設(shè)置格式

?僅對排名靠前或靠后的數(shù)值設(shè)置格式

A僅對高于或彳氐于平均值的數(shù)值設(shè)置格式

A僅對唯一＜或重復(fù)值設(shè)置格式

?使用公式確定要設(shè)置格式的單元格

編輯規(guī)則說明(E)：

確定取消

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

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

圖所示:

ABCDEFG

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

2銷售量230028722980284025312760

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

4

5

6

7圖表標(biāo)題

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單元格中輸入文字“高亮顯示”，將文字設(shè)置為在單元格中右對齊。

在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

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

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

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

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

③鼠標(biāo)右擊工作表中的控件，選擇關(guān)聯(lián)菜單中的“設(shè)置控件格式”命令打

開“設(shè)置控件格式”對話框，在“控制”選項卡中對控件進(jìn)行如圖所示設(shè)置。

G1_H_1K

2019年Q_O

2760

#N/A5

設(shè)置控ms式?X

大小屬性可選文字

取消

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

據(jù)標(biāo)記的折線圖”這一亞型，如圖所示。

BCDEFGH,l|J|K

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

推薦的圖表所有圖表

9最近

二?模板

Hw柱形圖

X折線圖帶數(shù)據(jù)標(biāo)記的折線圖

3餅圖

置條形圖

S面積圖

-應(yīng)XY散點圖

尬股價圖

H卷曲面圖

⑤在圖表中將圖例項刪除，為數(shù)據(jù)系列添加和格式化數(shù)據(jù)標(biāo)簽，鼠標(biāo)單擊

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

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

中數(shù)據(jù)系列（那條折線）后選“設(shè)置數(shù)據(jù)系列格式"，線條的顏色和粗細(xì)在這里

設(shè)置:

設(shè)置數(shù)據(jù)系列格式▼X

系列選項▼

⑦Qill

々戰(zhàn)條?標(biāo)記H

/建條

O無線條(N)

@實線兇、

O漸變線鬲、

O自動Q)\

蝌工),，叵▼

透明度①110%/:

m^(w)|百磅：

復(fù)合類型?|=---

"IR

標(biāo)記的類型和大小在這里設(shè)置:

設(shè)置數(shù)據(jù)系列格式▼X

系列選項▼

⑦Qill

々發(fā)條人.標(biāo)記F

/標(biāo)記選項

'：'自動(U)

O無稅條⑻)

___________1-□1

mr...i>

在此種狀況下如果還想對高亮顯示的那個數(shù)據(jù)標(biāo)志進(jìn)行單獨的格式設(shè)置，只

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

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

此圖本質(zhì)上是兩個數(shù)據(jù)系列共用主坐標(biāo)，其中一個系列是表中第二行的數(shù)據(jù),

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

疊加在第一個系列中對應(yīng)的年份上而已。

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