excel典型试题操作步骤.docx

excel典型试题操作步骤.docx

《excel典型试题操作步骤.docx》由会员分享，可在线阅读，更多相关《excel典型试题操作步骤.docx（33页珍藏版）》请在冰豆网上搜索。

excel典型试题操作步骤

6.2典型试

典型试题1

打开素材库中的“典型试题6-1.xls”文件，按下面的操作要求进行操作，并把操作结果存盘。

注意：

在做题时，不得将数据表进行更改。

一、操作要求

1.在Sieet5的A单元格中设置为只能录入5位数字或文本。

当录入位数错误时，提示

错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

2.使用数组公式，对Sheet1中“教材订购情况表”的订购金额进行计算，并将结果保存在该表的“金额”列当中。

计算方法：

金额=订数*单价

3.使用统计函数，对Shcetl中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置。

要求：

（1）统计名称为“高等教育”的书的种类数,并将结果保存在sheet1

中L2单元格中。

（2）统计订购数量大于110且小于850的书的种类数,并将结果保存在sheet1中L3单元格中。

4.使用函数，计算每个用户所订购图书所需支付的金额总数，并将结果保存在Sheet1中“用户支付情况表”的“支付总额”列中。

5.使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

说明：

闰年定义：

年数能被4整除而不能被100整除，或者能被400整除的年份。

6.将Sheei中的“教材订购情况表”复制到Sheet3中，对Sheet3进行高级筛选.

要求：

（1）筛选条件为“订数>=500，且金额总数<=30000”。

2）将结果保存在Sheet3中。

注意：

1）无需考虑是否删除或移动筛选条件。

2）复制过程中，将标题项“教材订购情况表”连同数据一同复制。

3）数据表必须顶格放置。

7.根据Sheet1中“教材订购情况表”的结果，在Sheet4中新建一数据透视

表。

要求：

（1）显示每个客户在每个所订的教材数目。

（2）行区域设置为“”。

（3）列区域设置为“客户”。

（

4（5）数据区域设置为“订数”。

二、解答

求

和步骤1选中Sheet5工作表中的A1单元格，选择菜单“数据”宀“有效性”命令（如项图6-13所示），在打开的“数据有效性”对话框中，选择“设置”选项卡，再

选择为“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”，如图6-14所示“。

。

选择“出错警告”选项卡，再选择“样式”为“警告”，在“错误信息”文本框中输入“只能录入5位数字或文本”，如图6-15所示，单击“确定”按钮。

图6-13选择“有效性”命令

图6-14“设置”选项卡

图6-15“岀错警告”选项卡

步骤2:

在Sheetl工作表中，先选中I3:

152单元格区域，再输入公式

说明：

公式中单元格或单元格区域也可用鼠标选择输入，下同。

步3

G

图6-16“插入函数”对话框

一一卷9••:

选中2l*2h第元格H,單击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉

couktif

Ran

涵M出雌丿

Criteii

r外语数学与研宛

“高等歡靑出版社"

l+直某个区域中億足给定臬件的車元格數目

R环娈要计篡耳中非空单元格数目的区域

计算结果=6

有关連函数的带助优）

图6-17“函数参数”对话框

选中L3单元格，在公式编辑栏中输入

说明：

COUN函数的功能和用法，请参见上一节的相关容。

公步骤4:

选中L8单元格，单击插入函数按扭，打开“插入函数”对话框，在“选

择

式-18所示。

再按“确定”按钮，即可计算出cl用户所订购图书需支付的金额总数

显框22输，1入SXRa

在可

计在

算9

计

可

计

算

出

按

户

所

键

购复

图令

书书

域中

购

额

年

数

金

金

数

筛

在

元

格明：

SUMIF函数的功能和用法，请参见上一节的相关容。

步骤5:

在Sheet2工作表中，选择B2单元格，在公式编辑栏中输入公输=IF（OR（ANDIOD（A2,4=0,MOD2,100）<>0）,MO（A2,400）=0）,"闰年","平年"）”入

公

格

式明：

IF、ORANDMO函数的功能和用法，请参见上一节的相关容。

a

““骤6：

选中Sieet1工作表中的A1：

I52单元格区域，单击右键在快捷菜单中选择

制”命令。

单击Sheet3工作表中的A1单元格，单击右键在快捷菜单中选择“粘贴”

入Sheet3工作表的空白区域创建筛选条件，如图6-19所示，在K2：

L3单元格区“入筛选条件。

单击Sheet3工作表中A1:

I52单元格区域中的任一单元格，选

格单击“确定”按钮。

区

域

H

I

J

K

L

K/

k

单价金额

2/

订数

23

226665

:

J

>=500

<=3^W/

25

92500

图6-19条建筛选条件

件

该

。

再

.|L:

图6-21设置“列表区域”图6-22设置“条件区域”

步骤7：

单击Sieetl工作表中A1：

I52单元格区域中的任一单元格，选择菜单“数据”宀数

据

透

视在弹出的如图6-27所示界面中，将字段“”拖至行字段区域，将字段“客表”拖至列字段区域，将字段“订数”拖至数据项区域，结果如图6-28所示。

和保存文件后退出。

数

据

透

视

图命令

（如图

图6-24“数据透视表和数据透视图向导一一3步骤之1”对话框

数攜透视春和数撩伊视图向异~3歩廉疋J

②凶

布局◎…][谨项翅…]I城消]K上一步⑻][完成_迥

图6-26“数据透视表和数据透视图向导一一3步骤之3”对话框

2

3

■

5

工

7

is

藪基速視生字耳列击

將玻忖3£兰宠朋透如表

9

10

11

L2

TT

K

1E

秫

冬社

户B材版武者敷价张

客Is数出版件17羊全

□已已H冃

图6-27

“数据透视表”布局

A

B

C

D

E

F

1

2

求和项:

订載熔尸-

出版社一一卜

cl

c2

<3

u4

总计

3

北京航大

63

e3

4

北京理工

421

421

5

电子工业出版社

555

71

626

6

东北财经大学出版社

75

75

7J

复旦大学

106

106

81

高等教育

1061

1061

9

高等教育出版社

10719

10719

10

高教

509

5C9

11

华东师大

78

76

171

科学

203

203

科学出版社

2940

2940

14

立信会计

637

637

15

立信会计出版社

30

80

16

辽宁美术出版社

53

58

17

南京大学

240

240

18

清半大学

120

120

19

人艮大学

721

721

20

人民卫生

366

366

21

上潜外语教育岀版社

500

500

221

天津人民黄术出版社

58

se

23

外语教学与研究出版社

9655

99S5

24

浙江科技出版社

1504

1504

25

浙江科学技术

106

106

26

淅江科学技术出版社

500

500

27

中園金融

160

160

2B

中国金融出版社

645

29

中圉人大

224

224

30

中園人艮大学出版社

585

167

752

31

中園物资

109

109

32

总计

27158

1965

19&8

2343

S3434

图6-28数据透视

表

典型试题2

打开素材库中的“典型试题6-2.xIs”文件，按下面的操作要求进行操作，并把操作结果存盘。

注意：

在做题时，不得将数据表进行更改。

一、操作要求

1.在Sieet5的A单元格中设置为只能录入5位数字或文本。

当录入位数错误时，提示

错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

2.使用时间函数，对Sheet1中用户的年龄进行计算。

要求：

假设当前时间是“2008-11-15”，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”列当中。

计算方法为两个时间年份之差。

3.使用FEPLAC函数，对heetl中用户的进行升级。

要求：

对“原”列中的进行升级。

升级方法是在区号（0571）

后面加上“8”，并将其计算结果保存在“升级”列的相应单元格中。

例如：

“”升级后为“8”

4.在Sieetl中，使用AND函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于40岁的男性，并将结果保存在“是否>=40男性”列中。

注意：

如果是，保存结果为TRU；否则，保存结果为FALSE

5.根据Sheet1中的数据，对以下条件，使用统计函数进行统计。

要

求：

（1）统计性别为“男”的用户人数，将结果填入Sieet2的B2单元格中。

（2）统计年龄为“>40”岁的用户人数，将结果填入Sheet2的B3单元格中。

6.将Sheet1复制到Sheet3，并对Sheet3进行高级筛选。

要求：

（1）筛选条件为：

“性别”－女、“所在区域”－西湖区。

（2）将筛选结果保存在Sheet3中。

注意：

（1）无需考虑是否删除或移动筛选条件。

（2）数据表必须顶格放置。

7.根据Sheet1的结果，创建一个数据透视图Chart1。

要求：

（1）显示每个区域所拥有的用户数量。

（2）x坐标设置为“所在区域”。

（3）计数项为“所在区域”。

（4）将对应的数据透视表保存在Sheet4中。

二、解答

步骤1:

操作方法参见“典型试题1”中的步骤1。

步骤2:

在Sheetl工作表中，选中D2单元格，在公式编辑栏中输入公式

说明：

YEAR函数的功能和用法，请参见上一节的相关容。

击G2单元格的填充柄，即可对其余进行升级。

说明：

REPLACE｝数的功能和用法，请参见上一节的相关容。

打图6-29“函数参数”对话框

开

可判断该用户是否为大于等于40岁的男性。

双击坨单元格的填充柄，即可判断其余用户是否为大于等的男性。

步骤5:

在Sheet2工作表的B2单元格中，输入公式=C0UN「Sheet1!

E2:

B37,"男

工

按

回步骤6:

选中Sheetl工作表中的A1:

H37单元格区域，单击右键在快捷菜单中选择“复制”命令。

单击Sieet3工作表中的A1单元格，单击右键在快捷菜