excel典型试题操作步骤

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

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

一、操作要求
1. 在Sh e et5的A1单元格中设置为只能录入5位数字或文本。

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

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

计算方法：金额= 订数* 单价
3. 使用统计函数，对S he e t1中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在S heet1 中的相应位置。

要求：
（1）统计出版社名称为“高等教育出版社”的书的种类数,并将结果保存在s heet1
中L2 单元格中。

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

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

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

说明：
闰年定义：年数能被4整除而不能被100 整除，或者能被400 整除的年份。

6. 将S hee t1中的“教材订购情况表”复制到S hee t3中，对S heet3 进行高级筛选.
要求：
（1）筛选条件为“订数>=500，且金额总数<=30000”。

（2）将结果保存在S heet3 中。

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

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

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

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

要求：
（1）显示每个客户在每个出版社所订的教材数目。

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

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

（4）
求和项为“。

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

二、解答
步骤 1：选中 S heet5 工作表中的 A1 单元格，选择菜单“数据”→“有效性”命 令（如图 6-13 所示），在打开的“数据有效性”对话框中，选择“设置”选项卡， 再选择“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”， 如图 6-14 所示。

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

图 6-13 选择“有效性”命令
图6-14 “设置”选项卡
图6-15 “出错警告”选项卡
步骤2：在Sheet1 工作表中，先选中I3:I52单元格区域，再输入公式
“=G 3:G 52*H 3:H
，然后同时按组合键 Sh i ft+Ctr l +Enter ，此时，公式编辑栏显示
“{=G 3:G 52*H 3:H 5。

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

步
骤 3：选中 L 2 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉
图 6-16 “插入函数”对话框
图 6-17 “函数参数”对话框
选 中 L3 单 元 格 ， 在 公 式 编 辑 栏 中 输
入
公 式 “=C O U N T I F (G 3:G 52,"<8说明：COUN T IF 函数的功能和用法，请参见上一节的相关内容。

步骤 4：选中 L8 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“数学与三角，在“选择函数”列表框中选择 “SUMIF ”函数。

单击“确定”按钮，弹出“函数参数”对话框，输入“R a 6-18 所示。

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

此时，公式编辑栏显示“=S U M
图 6-18 “函数参数”对话框
在
L 9 单元格中输入公式“=S
U M I F (
A 3:A 52,"=c 可计算出 c 2 用户所订购图书需支付的金额总数（53在
L
10 单元格中输入公式“=S U M
I F (A 3:可计算出 c 3 用户所订购图书需支付的金额总数（在 L 11
单
元格中输入公式“
=
S U M I F (A
可计算出 c 4 用户所订购图书需支付的金额总说明：SUMIF 函数的功能和用法，请参见上一节的相关内容。

步骤 5：在 Sheet2 工作表中，选择 B2 单元格，在公式编辑栏中输入公式 “=IF(OR(AND(M OD(A2,4)=0,MOD(A 2,100)<>0),MOD (A 2,400)=0),"闰年","平年")”， 按回车键确认，即可判断出该年份是“闰年”还双击 B 2 单元格的填充柄，即可
判断说明：IF 、OR 、AND 、MOD 函数的功能和用法，请参见上一节的相关内容。

步骤 6：选中 S heet1 工作表中的 A1：I52 单元格区域，单击右键在快捷菜单中选 择“复制”命令。

单击 S h eet3 工作表中的 A1 单元格，单击右键在快捷菜单中选择“粘 贴”命令。

在
Sheet3 工作表的空白区域创建筛选条件，如图 6-19 所示，在 K2：L3 单元格 区域中输入筛选条件。

单击 Sheet3 工作表中 A1：I 52 单元格区域中的任一单元格， 选择菜单“数据”→“筛
格区域 K 2：“
条
件区域”文本框内会自动
填入该区域地址（如图 6-22 。

再
单击“确定”按钮。

图 6-19 创建筛选条件
图 6-20 选择“高级筛选”命令
图6-21 设置“列表区域”图6-22 设置“条件区域”
步骤7：单击S heet1 工作表中A1：I52 单元格区域中的任一单元格，选择菜单“数据”→“
数
据
透
视表和数据透视图”命令（如图
6 -2 3在弹出的如图6-2
7 所示界面中，将字段“出版社”拖至行字段区域，将字段“客
户”拖至列字段区域，将字段“订数”拖至数据项区域，结果如图6-28 所示。

保存文件后退出。

图6-23 选择“数据透视表和数据透视图”命令
图6-24 “数据透视表和数据透视图向导——3 步骤之1”对话框图6-25 “数据透视表和数据透视图向导——3 步骤之2”对话框
图6-26 “数据透视表和数据透视图向导——3 步骤之3”对话框
图6-27 “数据透视表”布局
图6-28 数据透视表
典型试题2
打开素材库中的“典型试题”文件，按下面的操作要求进行操作，并把操作
结果存盘。

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

一、操作要求
1. 在Sh e et5的A1单元格中设置为只能录入5位数字或文本。

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

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

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

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

3. 使用R EPLACE 函数，对S heet1 中用户的电话号码进行升级。

要求：对“原电话号码”
列中的电话号码进行升级。

升级方法是在区号（0571）
后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。

例如：电话号码“008”升级后为“0808”
4. 在S h eet1中，使用A ND 函数，根据“性别”及“年龄”列中的数据，判断所有用
户是否为大于等于40 岁的男性，并将结果保存在“是否>=40 男性”列中。

注意：如果是，保存结果为T RUE；否则，保存结果为F ALSE。

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

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

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

6. 将S hee t1复制到S heet3，并对S he e t3进行高级筛选。

要
求：
（1）筛选条件为：“性别”－女、“所在区域”－西湖区。

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

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

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

7. 根据S he e t1的结果，创建一个数据透视图C h art1。

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

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

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

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

二、解答
步骤 1：操作方法参见“典型试题 1”中的步骤 1。

步骤 2：在 Sheet1 工作表中，选中 D2 单元格，在公式编辑栏中输入公式
“=Y E A R ("2008-11-15")-Y E A R (C ，再按回车键说明：YEAR 函数的功能和用法，请参见上一节的相关内容。

步骤 3：选中 G
2
单元格
，
单击插入函
数
按扭，打开“插入函数”对话框，在“选 双击 G 2 单元格的填充柄，即可对其余电话号码进行升级。

说明：REPLACE 函数的功能和用法，请参见上一节的相关内容。

图 6-29 “函数参数”对话框 步骤 4：在 H 2 单元格中可判断该用户是否为大于等于 40 岁的男性。

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

步骤 5：在 Sheet2 工作表的 B 2 单元格中，输入公式“=COUNTIF (Sheet1!B 2:B37,"男 ，再按回车键确认在 S h e e t
按
回车键确认，即可统计
出
大于 40 岁的用
户
人
数
（。

步骤 6：选中 S heet1 工作表中的 A 1：H37 单元格区域，单击右键在快捷菜单中选 择“复制”命令。

单击 S h eet3 工作表中的 A 1 单元格，单击右键在快捷菜单中选择“粘 贴”命令。

在 Sheet3 工作表的空白区域创建筛选条件，如图 6-30 所示，在 J1：K2 单元格区域 中输入筛选条件。

单击 S heet3 工作表中 A1：H37 单元格区域中的任一单元格，选择 菜单“数据”→“
筛选”→“高级筛选”命
令，
打
开
“
高级筛选
”
对
“列表区 域”文本框中已自动填入数据清单所在的单元格区域。

将光标定位在“条件区
域
注意：J1 单元格中“性”和“别”之间有空格，原因是 B1 单元格中“性”和“别” 之间有空格。

图 6-30 创建筛选条件 图 6-31 设置“条件区域”
步骤 7：单击 S heet1 工作表中 A 1：H37 单元格区域中的任一单元格，选择菜单“数
据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——3
步
骤
之
1”对话框，选中“数据透视图（及数据透”单选钮（如图
6 -3保存文件后退出。

图6-32 “数据透视表和数据透视图向导——3 步骤之1”对话框
图6-33 “数据透视表和数据透视图向导——3 步骤之2”对话框图6-34 “数据透视表和数据透视图向导——3 步骤之3”对话框
图6-35 “数据透视表”布局
图6-36 数据透视图图6-37 数据透视表
典型试题 3
打开素材库中的“典型试题 ”文件，按下面的操作要求进行操作，并把操作 结果存盘。

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

一、操作要求
1. 在Sh e et4的A1单元格中设置为只能录入5位数字或文本。

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

2
.
使用 V L O O K U P 对 S heet1 中的“三月份销售统计表”的“产品名称”列和“产 品单价”列进行填充。

要求：根据“企业销售产品清单”，使用 V LOOKUP 函数，将产品名称和产品单价填充 到“三月份销售统计表”的“产品名称”列和“产品单价”列中。

3. 使用数组公式，计算
S h eet1 中的“三月份销售统计表”中的销售金额，并将结果 填入到该表的“销售金额”列中。

计算方法：销售金额 = 产品单价 * 销售数量 4. 使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计 表”中的总销售额，并将结果填入该表的“总销售额”列。

5. 在 S heet1 中，使用 R ANK 函数，在“分部销售业绩统计”表中，根据“总销售额” 对各部门进行排名，并将结果填入到“销售排名”列中。

6. 将 s heet1 中的“三月份销售统计表”复制到 S he e t2 中，对 S heet2 进行高级筛选。

要求： （1）筛选条件为：“销售数量”－ >3、“所属部门”－ 市场 1 部、“销售金额”- >1000。

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

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

（2）复制过程中，将标题项“三月份销售统计表”连同数据一同复制。

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

7. 根据 S he e t1 的“三月份销售统计表”中的数据，新建一个数据透视图 C hart1。

要
求：
（1）该图形显示每位经办人的总销售额情况。

（2）x 坐标设置为“经办人”。

（3）数据区域设置为“销售金额”。

（
4）求和项为
“
销售。

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

二、解答 步骤 1：操作方法参见“典型试题 1”中的步骤 1。

步骤 2：选中 S heet1 工作表中的 G 3 单元格，单击插入函数按扭，打开“插入函数” 对话框，在“选择类别”下
拉列表框中
选
择“查找与
，在“选择函数”列表框 中选择“VLO O KUP ”函数，单击“确定”按钮后，弹出“函数参数”对话框。

输入 “ Lookup_value ” 参 数为 “ F 3 ”，“ T able_array ” 参 数为 “ $A
$3:$B $10 ”， “双击 G 3 单元格的填充柄，即可查找到其余产品型号的产品名称。

在 H 3
单元格中
，输入公式
“=V L O O K U P (F 3,$说明：VLOOKUP 函数的功能和用法，请参见上一节的相关内容。

图 6-38 “函数参数”对话框 步骤 3：先选中
L 3按组合键 S h i f
步骤 4：选中 O3 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “
选择类别”下拉
列
表框中选择“
数
学
与三角，在“选择函数”列表框中选择 “
S U M I F ”函
数。

单击“确定
”
按钮
，
弹
出“函数参数”对话框，输入
“R a n g e 在
O 4 单元格中，输入公式“=SUMIF(K 3:K44,"=市场 2 部",L3:L44)”后，按回车键确 认。

在 O 5 单元格中，输入公式“=SUMIF(K 3:K44,"=市场
3 部",L3:L44)”后，按回车键确 认。

图 6-39 “函数参数”对话框
步
骤 5：选中 P
3
单元格
，
单击插入函
数
按扭双击 P 3 单元格的填充柄，可统计出其他部门的总销售额排名。

说明：RANK 函数的功能和用法，请参见上一节的相关内容。

图6-40 “函数参数”对话框
步骤6：选中S heet1 工作表中的E1：L44 单元格区域，单击右键在快捷菜单中选择“复制”命令。

单击S h eet2工作表中的A1 单元格，单击右键在快捷菜单中选择“选择性粘贴”命令，打开如图6-41 所示的“选择性粘贴”对话框，选中“值和数字格式”单选钮，再单击“确定”按钮。

在 Sheet2 工作表的空白区域创建筛选条件，如图6-42所示，在 J2：L3 单元格区域中输入筛选条件。

单击 Sheet2 工作表中A1：H44单元格区域中的任一单元格，选择
“列表区域”菜单“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框，
文本框中已自动填入数据清单所在的单元格区域。

将光标定位在“条件区域”文本框内，用
鼠
标
拖
选
前
面
创
建
的
筛
选
条
件
单
元
格
区
图6-41 “选择性粘贴”对话框图6-42 创建筛选条件
图 6-43 设置“条件区域”
步骤 7：单击 S heet1 工作表中 E 1：L44 单元格区域中的任一单元格，选择菜单“数
据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导—— 3
步
骤
之
1
”
对话
框，选
中
“
数
据
透
视
图
（
及
6-45 ，打开 S heet3 工作表有如图 6-46 所示的相应的数据透视表。

保存文件后退出。

图6-44 “数据透视表”布局
图6-45 数据透视图图6-46 数据透视表
典型试题4
打开素材库中的“典型试题”文件，按下面的操作要求进行操作，并把操作
结果存盘。

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

一、操作要求
1. 在Sh e et4的A1单元格中设置为只能录入5位数字或文本。

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

2. 使用H LOOKUP 函数，对S h e et1“停车情况记录表”中的“单价”列进行填充。

要求：
根据S he e t1中的“停车价目表”价格，使用H LOOKUP 函数对“停车情况记
录表”中的“单价”列根据不同的车型进行填充。

注意：函数中如果需要用到绝对地址的将使用绝对地址进行计算，其他方式无效。

3. 在S hee t1中，使用时间函数计算汽车在停车库中的停放时间。

要求：
（1）计算方法为：“停放时间= 出库时间-入库时间”。

（2）格式为：“小时：分钟：秒”。

（3）将结果保存在“停车情况记录表”中的“停放时间”列中。

（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”）
4. 使用函数公式，对“停车情况记录表”的停车费用进行计算。

要求：根据S he e t1停放时间的长短计算停车费用，将计算结果填入到“停车情况记录表”的“应付金额”列中。

注意：
（1）停车按小时收费，对于不满一个小时的按照一个小时计费。

（2）对于超过整点小时数十五分钟（包含十五分钟）的多累积一个小时。

（例如1小时23 分，将以2小时计费）
5. 使用统计函数，对S heet1 中的“停车情况记录表”根据下列条件进行统计。

要
求：
（1）统计停车费用大于等于40 元的停车记录条数，并将结果保存在J8 单元格中。

（2）统计最高的停车费用，并将结果保存在J9 单元格中。

6. 将S hee t1中的“停车情况记录表”复制到S hee t2中，对S heet2 进行高级筛选。

要
求：
（1）筛选条件为：“车型”－小汽车，“应付金额”>＝30。

（2）将结果保存在S heet2 中。

注意：
（1）无需考虑是否删除筛选条件。

（2）复制过程中，将标题项“停车情况记录表”连同数据一同复制。

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

7. 根据 S he e t1 中的“停车情况记录表”，创建一个数据透视图 C hart1。

要
求：
（1）显示各种车型所收费用的汇总。

（2）x 坐标设置为“车型”。

（3）求和项为“应付金额”。

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

二、解答
步骤 1：操作方法参见“典型试题 1”中的步骤 1。

步骤 2：选中 S heet1 工作表中的 C 9 单元格，单击插入函数按扭，打开“插入函数” 对话框，在
“
选
择
类别”
下
拉
列
表
框
中
选
择
“
查
找
与
，在“选择函数”列表框 中选择“HLO O KUP ”函数，单击“确定”按钮后，弹出“函数参数”对话框。

输入 “ Lookup_value ” 参 数为 “ B9 ”，“ T a ble_ar r ay ” 参 数为
“
$
A
$
2
:
$
C
$
双击 C 9 单元格的填充柄，即可查找到其余车辆的停车单价。

说明：HLOOKUP 函数的功能和用法，请参见上一节的相关内容。

图 6-47 “函数参数”对话框
步
骤
3：在 F
9 单元格中，输
入
公式“=E 9-，再按回车键确认，即计算出第一 辆车的停放时间（3:03:。

双击 F 9 单元格的填充柄，即可计算出其余车辆的停放时间。

步 骤 4 ： 选 中 G9 单 元 格 ， 在 公 式 编 辑 栏 中 输 入 公 式 “=I F (H O U R (F 9)=0,
1
,I F 双击 G 9 单元格的填充柄，即可计算出其余车辆的停车费用。

说明
：H O U R
、M I N U T E
的
功
能
步骤 5：选中 J 8 单元格，单击插入单击“确定”按钮后，弹出“函数参数”
图6-48 “函数参数”对话框选
中
J 9单元格，单击插入函数按扭，打开“插入函数”对话框，在“说明：MAX函数的功能和用法，请参见上一节的相关内容。

图6-49 “函数参数”对话框
步骤6：选中S heet1 工作表中的A7：G39 单元格区域，单击右键在快捷菜单中选
择“复制”命令。

单击 S h eet2 工作表中的 A 1 单元格，单击右键在快捷菜单中选择“选 择性粘贴”命令，在弹出的“选择性粘贴”对话框中，选中“值和数字格式”单选钮， 再按“确定”按钮。

在 Sheet2 工作表的空白区域创建筛选条件，如图 6-50 所示，在 I2：J3 单元格区域 中输入筛选条件。

单击 S heet2 工作表中 A1：G33 单元格区域中的任一单元格，选择 菜单
“
数
据
”
→
“筛选”
→
“
高
级
筛
选
”
命
令
，
打
开
“
高级
筛选”
对
“列表区 域”文本框中已自动填入数据清单所在的单元格区域。

将光标定位在“条件区
域
图 6-50 创建筛选条件
图 6-51 设置“条件区域” 步骤 7：单击 S heet1 工作表中 A 7：G39 单元格区域中的任一单元格，选择菜单“数
据”→“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——
3
步骤之 1”对话框，
选中“数据透视图（
及
数
据
透”单选钮，再单击“下一步”
按
钮
，打开“数据透
视表
和
数
据透视图向导——3
步骤之
2
”
对
“选定区域”文 本框中已自动填入数据清单所在的单元格区域，单击“下一步”按钮，打开“数据透 视表和数据透视图向导——3 步骤之 3”对话框，选中“现有工作表”单选钮，再单 击 S heet3 工作表中的 A 1 单元格，该单元格地址会自动填入“现有工作表”下方的地 址文本框中。

单击“布局”按钮，在弹出的如图 6-52 所示界面中，将字段“车型” 拖至“行”区域内，将字段“应付金额”拖至“数据”区域内，然后按“确定”按钮 返回，再按“完成”按钮。

这时在 S h eet3 工作表前会插入一张名为 C hart1 的工作表。

保存文件后退出。

图 6-52 “数据透视表”布局
图6-53 数据透视图图6-54 数据透视表。