第2章 Excel高级应用
2.5二级考试实战示例与练习
实战示例1 如图2-14
图2-14
题目：在考生文件夹的Paper子文件夹中，已有DExcel.xls文件。 按下列要求操作，并将结果存盘。
1. 使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的 “金额”列当中。
2. 使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在 Sheet1中的相应位置，要求：
a. 统计出版社名称为“高等教育出版社”的书的种类数； b. 统计订购数量大于110且小于850的书的种类数。
3. 使用函数计算，每个用户所订购图书所需支付的金额总数，将结果 保存在Sheet1中的相应位置。
4. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”， 如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。 说明：闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。 5. 将Sheet1复制到Sheet3中，对Sheet3进行高级筛选，要求： a. 筛选条件为“订数>=500，且金额总数<=30000”；
- 39 -
办公软件高级应用（计算机二级辅导资料）
b. 将结果保存在Sheet2中。（注：无需考虑是否删除筛选条件） 6. 根据Sheet1中的结果，在Sheet4中新建一张数据透视表，要求： a. 显示每个客户在每个出版社所订的教材数目； b. 行区域设置为：“出版社”； c. 列区域设置为：“客户”； d. 计数项为订数。 问题1：操作步骤
（1） 在Sheet1中选中填写金额的单元格i2：i51； （2） 在编辑栏中书写公式“=G2:G51*H2:H51”；
（3） 按ctrl+shift+enter键，所编辑的公式出现数组标志符号“{}”，同时i2：i51
列各个单元中生成相应结果。
问题2：操作步骤
（1） 在Sheet1L2单元格中编辑公式“=COUNTIF(D2:D51,\高等教育出版社\”； （2） 确定公式，生成统计结果;
（3） 在Sheet1L3单元格中编辑公式“=COUNTIF(G2:G51,\
COUNTIF(G2:G51,\”；
（4） 确定公式，生成统计结果。 说明：
（1） COUNTIF函数中如果使用条件表达式需要用引号引起。
（2） 一个COUNTIF函数不能同时使用两个表达式，如Ｌ3中的两个条件关系需
转化为两个COUNTIF函数计算。
问题3：操作步骤
（1） 在Sheet1L8单元格中编辑公式“=SUMIF(A2:A51,\”，确定公式 （2） 在Sheet1L9单元格中编辑公式“=SUMIF(A2:A51,\”，确定公式 （3） 在Sheet1L10单元格中编辑公式“=SUMIF(A2:A51,\”，确定公式 （4） 在Sheet1L11单元格中编辑公式“=SUMIF(A2:A51,\”，确定公式 问题4：操作步骤
（1）选中Sheet2表格，出现图2-15；
（2）在B2单元格中编辑公式“=if（or（and（mod（A2，4）=0，mod（A2，100< >0））,mod(A2,400)=0）,”闰年”,”平年”）”,确定公式；
（3）拖动引用B2公式至B21，生成全部结果。
问题5：操作步骤 图 2 –15 （1） 选择Sheet1中表格内容，复制到Sheet3中
粘贴（A1：I51部分）；
（2） 在Sheet3K4：L5单元格编辑高级筛选条件，其中K4中为“订数”，K5中
为“金额总数”，L4中为“>=500”，L5中为“<=30000”；
- 40 -
第2章 Excel高级应用
（3） 点击菜单“数据”?”筛选”?”高级筛选”,出现数据区和条件区定义对话框，
按Sheet3中的实际位置定义；
（4） 生成结果如图2-16，复制数据内容，至Sheet2中粘贴。
图2-16
问题6：操作步骤
（1）点击数据区中的任意位置，选择“数据”?“数据透视表和数据透视图向导”，按数据透视表的一般定义过程定义相应内容；
（2）在如图2-9所对应的环境中，将“出版社”字段拖动至行字段区，将“客户”字段拖动至列字段区，将“订数”字段拖动至数据区，生成结果如图2-17；
图2-17
（3）根据题目要求，将工作簿中的Sheet4删除，将当前透视表所在工作表Sheet5更名为Sheet4。
实战示例2
- 41 -
办公软件高级应用（计算机二级辅导资料）
如图2-18和图2-19
图2-18
- 42 -
第2章 Excel高级应用
图2-19
题目：在考生文件夹的Paper子文件夹中，已有DExcel.xls文件。 按下列要求操作，并将结果存盘。
1. 使用HLOOKUP函数，对Sheet1中的停车单价进行自动填充。
要求：根据Sheet1中的“停车价目表”价格，利用HLOOKUP函数 对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。 2. 在Sheet1中，利用时间函数计算汽车在停车库中的停放时间，要求： a. 公式计算方法为“出库时间-入库时间” b. 格式为：“小时：分钟：秒”
（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”） 3. 使用函数公式，计算停车费用，要求：
根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。 注意：
a. 停车按小时收费，对于不满一个小时的按照一个小时计费； b. 对于超过整点小时数十五分钟的多累积一个小时。 （例如1小时23分，将以2小时计费） 4. 使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计，要求： a. 在J8，统计停车费用大于等于40元的停车记录条数； b. 在J9，统计最高的停车费用。 5. 对Sheet2，进行高级筛选，要求：
- 43 -
计算机二级高级办公室软件复习资料
