Ecel数据分析教程 第7章上机题及课后习题

上机题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表中设置两个按钮控件，要求每按一次其中一个