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

上機(jī)題7

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

組合框是一個(gè)Excel表格中的下拉列表框，用戶可以在獲得的列表中選擇項(xiàng)

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

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

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

ABCDEFG,

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

2青義店296722493043243223583144

3龍門(mén)店275221302778244830603400

4方水店340028392523214233512381

5普明店331824822561292728532859

6松垓店216232902585255530133157

以下是操作步驟：

①啟動(dòng)Excel并打開(kāi)工作表，將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門(mén)

275221302778244830603400

店

方

4水

340028392523214233512381

明

店

5普

怔

店

松331824822561292728532859

6

216232902585255530133157

7

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

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

該公式的含義是，以$人$1為參照，移動(dòng)$A$8單元格中的數(shù)字所規(guī)定的行數(shù),

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

的列號(hào)會(huì)依次變?yōu)锽l、C1等等，也就是說(shuō)會(huì)從1依次變?yōu)?、3等等，而這個(gè)

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

的意思是第9行各單元格會(huì)依據(jù)A8中的數(shù)據(jù)從A1開(kāi)始移動(dòng)到哪個(gè)單元格的數(shù)

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

②在A8單元格中輸入一個(gè)小于7的數(shù)字(這只是針對(duì)本例的，因?yàn)楸纠?/p>

中要依據(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

“

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

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

框，如圖所示。

O0

o

圖表標(biāo)題e0

3500

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

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

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

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

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

o

圖表標(biāo)題

o

3500

3000

2500設(shè)置對(duì)象格式

2000

1500大小屬性可選文字控制

1000

500

0

取消

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

即可在圖表中顯示對(duì)應(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é)案例”簡(jiǎn)單動(dòng)態(tài)圖”制作是否相像?

2、用“選項(xiàng)按鈕”控件控制圖表顯示

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

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

能來(lái)實(shí)現(xiàn)之外，還可以通過(guò)“選項(xiàng)按鈕”控件來(lái)實(shí)現(xiàn)。請(qǐng)使用“選項(xiàng)按鈕”控件

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

1ABC

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

2

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

1207

341081

139509

5431333

一

60414111265

以下是操作步驟：

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

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

其下的3個(gè)單元格中。在H3單元格中輸入數(shù)字“1"o此時(shí)，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ù)源來(lái)創(chuàng)建圖表，如圖7B-2b所示。

AlBICIDIEIFIGIH

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

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

31

451QI15081207QI15081

6一Q214391509Q21439

-

7Q315431333Q31543

Q414111265.041411

8-=

9

圖表標(biāo)題

101

111600

121550

13

"1500

,1450

15師

1400

1350

1300

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

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

如圖所示。

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

加ExcelCOM加載項(xiàng)插入二

載項(xiàng)加載項(xiàng)

加

選項(xiàng)按鈕3“

ABC,D

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

2

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

3

415081207

O1喳16nq

5

6圖表標(biāo)題

7

1550

91500

101450

iann

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

如圖所示。

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

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

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

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

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

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

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

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

9一

一

10|

一

11

12

13

141

151

161

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

,ABCDEFG-H」

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

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è)置，就在此時(shí)■進(jìn)行。

3、使用ActiveX控件啟動(dòng)宏

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

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

宏的啟動(dòng)。請(qǐng)使用“命令按鈕”控件來(lái)啟動(dòng)一個(gè)宏。

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

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

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

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

凰

設(shè)計(jì)模式

M加載項(xiàng)Q源

團(tuán)

表單控件

口置|畫(huà)囹El@

GH

二ActiveX控件

葭低價(jià)買(mǎi)入價(jià)

1.0191.095□?防ES畫(huà)H

].8520.908

3.8290.868命令按鈕（ActiveX控件）

J.4310.4520.4

].2730.2760.2插入命令按朝件。

3.2750.2790.

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

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

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

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

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

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

價(jià)買(mǎi)久價(jià)賣(mài)出價(jià)成交里

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國(guó)屬性①

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)

國(guó)卷雪皆

工程?VBAProjectX

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

國(guó)宴巨

I^CoBBandButtonl

BVBAProject(7一上機(jī)3

PrivateSubCommandBu11onl_C1ick()

臼…占MicrosoftExcel對(duì):成交里

HiSheet3(上證B層EndSub

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

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

?HIJM

價(jià)買(mǎi)久價(jià)賣(mài)出價(jià)成交里

￡CommandP,-**

)191.0951.096124448—剪切①

)520.9080.909103690

八ACAACL

屬性電復(fù)制(Q

晶

|CoBBandButtonlCommandButton粘貼(E)

按字母序|按分類(lèi)序|

臼屬性Q)

(名稱(chēng))CommandButtonl

Accelerator星查看代碼M

AutoLoadFalse

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

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

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

葩映射屬性

E苴擴(kuò)展包

源

囹刷新緘

XML

設(shè)計(jì)模式

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

:入價(jià)賣(mài)出價(jià)成交里成交量

.0951.096124448

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

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

鈕，在彈出的“指定宏”對(duì)話框中為其指定宏，后者則需要通過(guò)VBA語(yǔ)句在該

按鈕的Click事件過(guò)程指定宏名。

課后習(xí)題7

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

ABCDEFG

學(xué)生成績(jī)表

1.

楊源NA

劉衛(wèi)中

張愛(ài)國(guó)

椰婷

SB氏

2

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

41楊源言77989026588.33

52劉衛(wèi)中90898826789.00

63張愛(ài)國(guó)8872672277567

7_4柳原92748625284.00

85雷民81927224581.67

96馬耀華67709423177.00

107侗明玉74727321973.00

118任正義65657920969.67

129羅遠(yuǎn)浩62687520568.33

步驟：

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

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

置控件格式”，在隨后彈出的“設(shè)置控件格式”對(duì)話框中，點(diǎn)擊“控制”選項(xiàng)卡，

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

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

⑥單選⑤

Og(M)

O擴(kuò)展?

0三維陰影⑶

確定取消

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

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

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

=$G$2=row()-3

為什么是row()-3呢？因?yàn)檎綌?shù)據(jù)前面正好有3行！如果多插入1行，讓

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

行，點(diǎn)擊第2人卻是第1人高亮！

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

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

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

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

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

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

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

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

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

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

確定取消

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

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

圖所示:

ABCDEFG

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

2銷(xiāo)售量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

操作如下：

①啟動(dòng)Excel并打開(kāi)工作表，在工作表的B3單元格中輸入公式

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

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è)置為在單元格中右對(duì)齊。

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

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

ABCID，E?F?GH?—1—

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

2l銷(xiāo)售量.230028722980284025312760高亮顯示宙滓1

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

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

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

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

正好掩蓋了12和J2兩個(gè)單元格。

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

開(kāi)“設(shè)置控件格式”對(duì)話框，在“控制”選項(xiàng)卡中對(duì)控件進(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散點(diǎn)圖

尬股價(jià)圖

H卷曲面圖

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

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

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

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

設(shè)置:

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

系列選項(xiàng)▼

⑦Qill

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

/建條

O無(wú)線條(N)

@實(shí)線兇、

O漸變線鬲、

O自動(dòng)Q)\

蝌工),，叵▼

透明度①110%/:

m^(w)|百磅：

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

"IR

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

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

系列選項(xiàng)▼

⑦Qill

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

/標(biāo)記選項(xiàng)

'：'自動(dòng)(U)

O無(wú)稅條⑻)

___________1-□1

mr...i>

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

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

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

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

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

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

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