Excel二级试题详解.docx
《Excel二级试题详解.docx》由会员分享，可在线阅读，更多相关《Excel二级试题详解.docx（24页珍藏版）》请在冰豆网上搜索。
Excel二级试题详解
第1题对DExcel01.xls文件按下列要求进行操作，结果存盘。
1、使用HLOOKUP函数，对Sheet1中的停车单价进行自动填充。
要求：
根据Sheet1中的“停车价目表”价格，利用HLOOKUP函数，
对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。
操作步骤：
单击C9单元格，选择“插入”菜单的“fx函数（F）…”命令或单击编辑栏的fx，此时会弹出如图2-1所示的“插入函数”对话框。
在“或选择类别”处，选择“全部”，在“选择函数”处，选择HLOOKUP，单击“确定”按钮。
这时，弹出如图2-2所示的“函数参数”对话框。
在Lookup_value处，输入b9。
Lookup_value的意思是需要在数据表第一行中进行查找的数据。
其值可以是数值、引用或文本。
在Table_array处，输入$A$2:
$C$3。
Table_array的意思是需要查找数据的数据表的区域。
在Row_index_num处输入2。
Row_index_num的意思是返回匹配值的行序号。
在Range_lookup处输入False。
Range_lookup为一逻辑值，为Truea或省略，返回近似匹配值，为False，返回精确匹配值。
输入以上四个参数值后，单击“确定”按钮。
再双击C9单元格右下角的填充柄。
图2-1
图2-2
2、在Sheet1中，利用时间函数计算汽车在停车库的停放时间，要求：
a公式计算方法为“出库时间-入库时间”
b格式为：
“小时：
分钟：
秒”
（例如：
一小时十五分十二秒在停放时间中的表示为：
“1：
15：
12”）
操作步骤：
单击F9单元格，输入公式“=e9-d9”，按回车或单击编辑栏上勾号（此操作与称确认）。
双击F9单元格右下角的填充柄。
说明：
时间类的数据可以作加减运算。
3、使用函数公式：
计算停车费用，要求：
根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。
注意：
1）停车按小时收费，对于不满一个小时的按照一个小时计费；
2）对于超过整点小时数十五分钟的多累积一个小时。
（例如1小时23分，将以2小时计费）
操作步骤：
单击G9单元格，输入公式“=（if（hour（f9）=0,1,if（minute（f9）>15,hour（f9）+1,hour（f9）））*c9”，确认。
双击G9单元格右下角的填充柄。
4、使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计，要求：
1）统计停车费用大于等于40元的停车记录条数
2）统计最高的停车费用
操作步骤：
1）在H8单元格输入“记录条数”，在I8单元格输入“最高停车费”。
单击H9单元格，选择“插入”菜单的“fx函数（F）”命令或单击编辑栏的勾号，在弹出的“插入函数”对话框中选择条件记数函数COUNTIF，按“确定”按钮。
这时，弹出的“函数参数”对话框，如图2-3所示。
在Range处输入G9:
G39。
在Range处需要输入的是计数的范围即单元格区域。
在Criteria处输入>=40。
在Criteria处需要输入的是计数的条件。
在输入以上两个参数后，按“确定”按钮。
图2-3
2）单击I9单元格，输入公式“=MAX（G9:
G39）”，确认。
5、对Sheet2，进行高级筛选，要求：
1）筛选条件为：
“车型”为小汽车，“应付金额”>=30
2）将结果保存在Sheet2中。
操作结果：
单击Sheet2工作表标签，进入Sheet2工作表。
在I1：
J2单元格区域设置条件，如表2-1所示。
在数据清单中任意单击一个单元格，选择“数据”菜单中的“筛选”命令下的“高级筛选”，弹出如图2-4所示的对话框。
单击“条件区域”右侧的文本，选择I1：
J2区域，单击“确定”按钮。
筛选结果如表2-2所示。
表2-1
车型
应付金额
小汽车
>=30
表2-2
车牌号
车型
单价
入库时间
出库时间
停放时间
应付金额
浙A66871
小汽车
5
9:
30:
49
15:
13:
48
5:
42:
59
30
浙A56587
小汽车
5
15:
35:
42
21:
36:
14
6:
00:
32
30
图2-4
6、根据Sheet1，创建一个数据透视图Chart1，要求：
1）显示各种车型所收费用的汇总
2）行区域设置为“车型”
3）计数项为“应付金额”
4）将对应的数据透视表保存在Sheet3中。
操作步骤：
单击Sheet1工作表标签，进入Sheet1工作表。
在停车情况记录表中任意单击一个单元格。
选择“数据”菜单中的“数据透视表和数据透视图”命令，弹出如图2-5所示的对话框。
单击“数据透视图（及数据透视表）”前面的单选钮，单南击“下一步”按钮。
弹出如图2-6所示的对话框，单击“下一步”按钮。
弹出如图2-7所示的对话框，单击“现有工作表”前的单选钮，单击Sheet3工作表标签，再单击Sheet3工作表中的A1单元格，单击“布局”按钮，弹出如图2-8所示的对话框。
将“车型”拖到“行”处，将“应付金额”拖到“数据”处，单击“确定”，再单击“完成”。
这时，出现Chart1透视图。
图2-5
图2-6
图2-7
图2-8
第2题对DExcel02.xls文件按下列要求进行操作，结果存盘。
1、使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。
要求：
根据“价格表”中的商品单价，利用VLOOKUP函数，
将其单价自动填充到采购表中的“单价”列中。
操作步骤：
单击D11单元格，选择“插入”菜单的“fx函数（F）…”命令或单击编辑栏的fx，此时会弹出如图2-9所示的“插入函数”对话框。
在“或选择类别”处，选择“全部”，在“选择函数”处，选择VLOOKUP，单击“确定”按钮。
这时，弹出如图2-10所示的“函数参数”对话框。
在Lookup_value处，输入工a11。
Lookup_value的意思是需要在数据表第一行中进行查找的数据。
其值可以是数值、引用或文本。
在Table_array处，输入$F$2:
$G$4。
Table_array的意思是需要查找数据的数据表的区域。
在Col_index_num处输入2。
Col_index_num的意思是返回匹配值的列序号。
在Range_lookup处输入False。
Range_lookup为一逻辑值，为Truea或省略，返回近似匹配值，为False，返回精确匹配值。
输入以上四个参数值后，单击“确定”按钮。
再双击D11单元格右下角的填充柄。
图2-9
图2-10
D11=VLOOKUP（A11,$F$2:
$g$4,2,False）新的
2、使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。
要求：
根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表的“折扣”列中。
操作步骤：
单击E11单元格，输入：
=if（b11<100,$b$3,if（b11<200,$b$4,if（b11<300,$b$5,$b$6）））类同第1题的第3小题。
3、利用公式，计算Sheet1中的“合计金额”。
要求：
根据“采购数量”，“单价”和“折扣”，计算采购的“合计金额”。
计算公式：
单价*采购数量*（1-折扣率）
操作步骤：
单击F11单元格，输入：
=b11*d11*（1-e11）这是一级的考试内容。
4、使用SUMIF函数，统计各种商品采购总量和采购总金额，将结果保存在Sheet1中的“统计表”当中。
操作步骤：
单击J12单元格，选择“插入”菜单的“fx函数（F）…”命令或单击编辑栏的fx，此时会弹出如图2-11所示的“插入函数”对话框。
在“或选择类别”处，选择“全部”，在“选择函数”处，选择SUMIF，单击“确定”按钮。
这时，弹出如图2-12所示的“函数参数”对话框。
在Range处，输入工$A$11:
$A$43。
Range为用于判断条件的单元格区域。
在Criteria处，输入I12。
Criteria为确定哪些单元格将被相加求和的条件。
在Sum_range处输入$b$11:
$b$43。
Sum_range是需要求和的实际单元格。
输入以上三个参数值后，单击“确定”按钮。
再双击J12单元格右下角的填充柄。
J12=sumif（$a$11:
$a$43,I12,$b$11:
$b$43）新的。
图2-11
图2-12
5、对Sheet2中的“采购表”进行高级筛选。
1）筛选条件为：
“采购数量”>150，“折扣”>0
2）将筛选结果保存在Sheet2中。
操作步骤：
和第1题的第5小题类同。
6、根据Sheet1中的采购表，新建一个数据透视图Chart1，要求：
1）该图形显示每个采购时间点所采购的所有项目数量汇总情况
2）X坐标设置为“采购时间”
3）将对应的数据透视表保存在Sheet3中。
操作步骤：
和第1题的第6小题类同。
第3题对DExcel03.xls文件按下列要求进行操作，结果存盘。
1、使用REPLACE函数，对Sheet1中的员工代码进行升级，要求：
1）升级方法：
在PA后面加上
2）将升级后的员工代码结果填入表中的“升级员工代码”列中。
操作步骤：
单击C2单元格，选择“插入”菜单的“fx函数（F）…”命令或单击编辑栏的fx，此时会弹出“插入函数”对话框。
在“或选择类别”处，选择“全部”，在“选择函数”处，选择REPLACE，单击“确定”按钮。
这时，弹出如图2-13所示的“函数参数”对话框。
在Old_text处，输入B2。
旧的文本，其中的部分或全部文本要被替换。
在Start_num处，输入3。
用新文本替换旧文本的起始位置。
在Num_chars处，输入0。
被替换的旧文本的字符个数。
在New_text处，输入0。
用来替换旧文本的新文本。
输入以上四个参数值后，单击“确定”按钮。
再双击C2单元格右下角的填充柄。
图2-13
C2=replace（b2,3,0,0）新的
2、使用时间函数，对Sheet1员工的“年龄”和“工龄”进行计算，并将结果填入到表中的“年龄”列和“工龄”列中。
操作步骤：
单击F2单元格，输入
=year（today（））-year（e2）一级
或=year（now（））-year（e2）
3、使用统计函数，对Sheet1中的数据，根据以下统计条件进行如下统计。
1）统计男性员工的人数，结果填入N3单元格中
2）统计高级工程师人数，结果填入N4单元格中
3）统计工龄大于等于10的人数，结果填入N5单元格。
操作步骤：
1）单击N3单元格，输入：
=countif（d2:
d65,”男”）和第1题的第4小题类同。
2）、3）类同1）。
4、使用逻辑函数，判断员工是否有资格评“高级工程师”
评选条件为：
工龄大于20，且为工程师的员工。
操作步骤：
单击K2单元格，输入：
=if（and（h2>20,I2=”工程师”,”是”,”否”）和第1题的第3小题类同。
And的用法和其它高级语言有所不同。
5、将Sheet1复制到Sheet2k，并对Sheet2进行高级筛选，要求：
1）筛选条件为：
“性别”—男，“年龄”>30，“工龄”>=0，“职称”—助工
2）将结果保存在Sheet2中。
操作步骤：
和第1题的第5小题类同。
6、根据Sheet1中的数据，创建一张数据透视图Chart1，要求：
1）显示工厂中各个职称的人数
2）X坐标设置为“职称”
3）计数项为职称
4）将对应的数据透视表保存在Sheet3中。
操作步骤：
和第1题的第6小题类同。
第4题对DExcel04.xls文件按下列要求进行操作，结果存盘。
1、使用IF函数，对Sheet1中的“温度较高城市”列进行填充。
操作步骤：
单击D2单元格，输入：
=if（b2>c2,”杭州”,”上海”）和第1题的第3小题类同。
2、使用数组公式，对Sheet1中的相差温度值（杭州相对于上海的温差）进行填充
操作步骤：
选择E2：
E16，输入=，选择B2：
B16，输入-，选择C2：
C16，按Ctrl+Shift+Enter。
数组公式新的
3、利用函数，根据Sheet1中的结果，符合以下条件的进行统计。
1）杭州这半个月以来的最高气温和最低气温
2）上海这半个月以来的最高气温和最低气温
操作步骤：
选择C18单元格，输入：
Max（B2:
B16）
选择C19单元格，输入：
Min（B2:
B16）
选择C20单元格，输入：
Max（C2:
C16）
选择C21单元格，输入：
Min（C2:
C16）
MaxMin一级
4、将Sheet1复制到Sheet2中，在Sheet2中，重新编辑数组公式，将Sheet2中的“相差的温值”中的数值取其绝对值（均为正数）
操作步骤：
将Sheet1复制到Sheet2，选择E2：
E16，将原公式改为=ABS（B2：
B16–C2：
C16），按Ctrl+Shift+Enter。
修改数组公式新的
5、将Sheet2复制到Sheet3中，并对Sheet3进行高级筛选，要求：
筛选条件：
“杭州平均气温”>=20，“上海平均气温”<20
操作步骤：
和第1题的第5小题类同。
6、根据Sheet1中的结果，在Sheet4中创建一张数据透视表，要求：
1）显示杭州气温高于上海气温的天数和上海气温高于杭州气温的天数
2）行区域设置为“温度较高的城市”
3）计数项设置为“温度较高的城市”
操作步骤：
选择数据清单中的任一单元格，在“数据”菜单中选择“数据透视表和数据透视图”命令，弹出“数据透视表和数据透视图向导---3步骤之1”对话框，如图2-14所示。
单击“下一步”，弹出“数据透视表和数据透视图向导---3步骤之2”对话框，如图2-15所示。
单击“下一步”，弹出“数据透视表和数据透视图向导---3步骤之3”对话框，如图2-16所示。
单击“现有工作表”前的单选钮，单击Sheet4工作表标签，再单击Sheet4工作表中的A1单元格。
单击“布局”按钮，弹出“数据透视表和数据透视图向导---布局”对话框，如图2-17所示。
把“”拖到“行”，再把“温度较高的城市”拖到“数据”区，然后单击“确定”，再单击“完成”。
在Sheet4中产生的结果如表2-3所示。
图2-14
图2-15
图2-16
图2-17
表2-3
新的
第5题对DExcel05.xls文件按下列要求进行操作，结果存盘。
1、使用数组公式，对Sheet1计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。
操作步骤：
选择F2：
F39单元格区域，输入：
=C2:
C39+D2:
D39+E2:
E39，按Ctrl+Shift+Enter。
选择G2：
G39单元格区域，输入：
=F2：
F39/3，按Ctrl+Shift+Enter。
数组公式和第4题的第2小题类同。
2、使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。
操作步骤：
单击H2单元格，选择“插入”菜单的“fx函数（F）…”命令或单击编辑栏的fx，此时会弹出“插入函数”对话框。
在“或选择类别”处，选择“全部”，在“选择函数”处，选择RANK，单击“确定”按钮。
这时，弹出如图2-18所示的“函数参数”对话框。
在Number处，输入F2。
需要被排序的数字。
在Ref处，输入$F$2:
$F$39。
需要被排序的数字的范围。
在Order处，输入0。
0为降序，1为升序。
输入以上三个参数值后，单击“确定”按钮。
再双击H2单元格右下角的填充柄。
图2-18
RANK新的
3、使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。
操作步骤：
在I2单元格中输入：
=if（c2确定。
双击I2单元格右下角的填充柄。
IF嵌套和第1题的第3小题类同。
4、根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。
操作步骤：
在Sheet2中，作如下操作。
在B2单元格中，输入公式：
=Countif（Sheet1!
:
d2:
d39,”<=20”），确定。
在B3单元格中，输入公式：
=Countif（Sheet1!
:
d2:
d39,”<=40”）-b2，确定。
在B4单元格中，输入公式：
=Countif（Sheet1!
:
d2:
d39,”<=60”）-b2-b3，确定。
在B5单元格中，输入公式：
=Countif（Sheet1!
:
d2:
d39,”<=80”）-b2-b3-b4，确定。
在B6单元格中，输入公式：
=Countif（Sheet1!
:
d2:
d39,”<=100”）-b2-b3-b4-b5，确定。
Countif和第1题中的第4小题类同。
5、将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求：
1）筛选条件：
“语文”>=75，“数学”>=75，“英语”>=75，“总分”>=250；
2）将结果保存在Sheet3中。
操作步骤：
第1题的第5小题类似。
6、根据Sheet1中的结果，在Sheet4中创建一张数据透视表，要求：
1）显示是否三科均超过平均分的学生人数
2）行区域设置为：
“三科成绩是否超过平均”
3）计数项为三科成绩是否均超过平均。
操作步骤：
和第4题的第6小题类似。
第6题对DExcel06.xls文件按下列要求进行操作，结果存盘。
1、使用数组公式，计算Sheet1中的订购金额，将结果保存到表中的“金额”列当中
操作步骤：
选择I2：
I51，输入：
=G2:
G51*H2*H51，按Ctrl+Shift+Enter。
数组公式和第4题第2小题类同。
2、使用统计函数，对Sheet1中结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置，要求：
1）统计出版社名称为“高等教育出版社”的书的种类数
2）统计订购数量大于110且小于850的书的种类数。
操作步骤：
选择L2，输入公式：
=countif（d2:
d51,”高等教育出版社”）
选择L3，输入公式：
=countif（g2:
g51,”>110”）–countif（g2:
g51,”>=850”）
Countif和第1题的第4小题类同。
3、使用函数计算，每个用户所订购图书所需支付的金额总数，将结果保存在sheet1中的相应位置。
操作步骤：
单击L8单元格，输入：
=sumif（$a$2:
$a$51,k8,$i$2:
$i$51）
Sumif和第2题的第4小题类同。
4、使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存为“闰年”，如果不是，则结果保存为“平年”，并将结果保存在“是否为闰年”列中。
说明：
闰年定义：
年数能被整除而不能被子00整除，或者能被整除的年份。
操作步骤：
单击B2单元格，输入：
=IF（OR（AND（MOD（A2,4）=0,MOD（A2,100）<>0,MOD（A2,400）=0）,”闰年”,”平年”）
IForandmodIf和第1题的第3小题类同，其余是新的。
5、将Sheet1复制到Sheet3中，对Sheet3进行高级筛选，要求：
1）筛选条件为“订数>=500，且金额总数<=30000”
2）将结果保存在Sheet2中。
操作步骤：
和第1题的第5小题差不多。
6、根据Sheet1中的结果，在Sheet4中新建一张透视表，要求：
1）显示每个客户在每个出版社所订的教材数目
2）行区域设置为：
“出版社”
3）列区域设置为：
“客户”
4）计数项为订数。
操作步骤：
和第4题的第6小题差不多。
第7题对DExcel07.xls文件按下列要求进行操作，结果存盘。
1、使用数组公式，计算Sheet1中的每种产品的价值，将结果保存到表中的“价值”列中。
计算价值的计算方法为：
“单价*每盒数量*采购盒数”。
操作步骤：
选择H2：
H17，输入：
=E2：
E17*F2：
F17*G2：
G17，按Ctrl+Shift+Enter。
数组公式和第4题的第2小题类同。
2、在Sheet2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存到相应的单元格中。
1）计算：
商标为上海，瓦数小于100的白炽灯的平均单价
2）计算：
产品为白炽灯，其瓦数大于等于80且小于等于100的数量
操作步骤：
1）单击G23单元格，选择“插入”菜单的“fx函数（F）…”命令或单击编辑栏的fx，此时会弹出“插入函数”对话框。
在“或选择类别”处，选择“全部”，在“选择函数”处，选择Daverage，单击“确定”按钮。
这时，弹出如图2-19所示的“函数参数”对话框。
在Database处，输入A1:
H17（也可输入A1：
E17）。
和题目相关的数据库，可以是部分，也可以是全部。
在Field处，输入“单价”（也可输入5）。
指定函数所使用的数据列。
在Criteria处，输入J2:
L3。
条件区域。
输入以上三个参数值后，单击“确定”按钮。
图2-19
2）单击I1，输入“数量”，选择I2：
I17，输入：
=f2:
f17*g2:
g17，按Ctrl+Shift+Enter。
再选中G24单元格，输入：
=DSUM（A1：
I17，”数量”，J7：
L8）
数据库函数DavergeDsum新的
******
3、某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。
对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。
根据调查情况，制做出Sheet3。
请使用函数，统计符合以下条件的数值。
1）统计问登记的部门个数
2）统计在登记的部门中，吸烟的部门个数。
操作步骤：
1）选中B14,输入：
=COUNTIF（B1:
B11,""），注意双引号不含空格。
2）选中B15,输入：
=COUNTIF（B1:
B11,"Y"）
Countif和第1题的第4小题类同。
4、使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否为文本，如果是，结果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格当中。
操作步骤：
选中B22，输入：
=istext（B21）
Istext新的
5、将Sheet1复制到Sheet4中，对Sheet4进行高级筛选，要求：
1）筛选条件：
“产品为白炽灯，商标为上海”，并将结果保存；
2）将结果保存在Sheet4中。
操作步骤：
和第1题的第5小题类同。
6、根据Sheet1的结果，在Sheet5中创建一张数据透视表，要求：
1）显示不同商标的不同产品的采购数量
2）行区域设置为“产品”
3）列区域设置为“商标”
4）计数项为“采购盒数”
操作步骤：
和第4题的第6小题类同。
第8题对DExcel08.xls文件按下列要求进行操作，结果存盘。
1、使用IF函数，对Sheet1中的“学位”列进行自动填充
要求：
填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）
升级会员 