Excel二级试题详解Word格式文档下载.docx
- 文档编号:7341698
- 上传时间:2023-05-08
- 格式:DOCX
- 页数:24
- 大小:409.57KB
Excel二级试题详解Word格式文档下载.docx
《Excel二级试题详解Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《Excel二级试题详解Word格式文档下载.docx(24页珍藏版)》请在冰点文库上搜索。
(例如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
35:
42
21:
36:
14
6:
00:
32
图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。
在Table_array处,输入$F$2:
$G$4。
在Col_index_num处输入2。
Col_index_num的意思是返回匹配值的列序号。
再双击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中的“采购表”进行高级筛选。
“采购数量”>
150,“折扣”>
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进行高级筛选,要求:
“性别”—男,“年龄”>
30,“工龄”>
=0,“职称”—助工
6、根据Sheet1中的数据,创建一张数据透视图Chart1,要求:
1)显示工厂中各个职称的人数
2)X坐标设置为“职称”
3)计数项为职称
第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:
选择C20单元格,输入:
Max(C2:
C16)
选择C21单元格,输入:
Min(C2:
MaxMin一级
4、将Sheet1复制到Sheet2中,在Sheet2中,重新编辑数组公式,将Sheet2中的“相差的温值”中的数值取其绝对值(均为正数)
将Sheet1复制到Sheet2,选择E2:
E16,将原公式改为=ABS(B2:
B16–C2:
C16),按Ctrl+Shift+Enter。
修改数组公式新的
5、将Sheet2复制到Sheet3中,并对Sheet3进行高级筛选,要求:
筛选条件:
“杭州平均气温”>
=20,“上海平均气温”<
20
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<
g2,”false”,if(d2<
g2,”false”,if(e2<
g2,”false”,”true”)))。
确定。
双击I2单元格右下角的填充柄。
IF嵌套和第1题的第3小题类同。
4、根据Sheet1中的结果,使用统计函数,统计“数学”考试成绩各个分数段的同学人数,将统计结果保存到Sheet2中的相应位置。
在Sheet2中,作如下操作。
在B2单元格中,输入公式:
=Countif(Sheet1!
:
d2:
d39,”<
=20”),确定。
在B3单元格中,输入公式:
=40”)-b2,确定。
在B4单元格中,输入公式:
=60”)-b2-b3,确定。
在B5单元格中,输入公式:
=80”)-b2-b3-b4,确定。
在B6单元格中,输入公式:
=100”)-b2-b3-b4-b5,确定。
Countif和第1题中的第4小题类同。
5、将Sheet1复制到Sheet3中,并对Sheet3进行高级筛选,要求:
1)筛选条件:
“语文”>
=75,“数学”>
=75,“英语”>
=75,“总分”>
=250;
2)将结果保存在Sheet3中。
第1题的第5小题类似。
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,输入公式:
d51,”高等教育出版社”)
选择L3,输入公式:
=countif(g2:
g51,”>
110”)–countif(g2:
=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”
和第1题的第5小题差不多。
6、根据Sheet1中的结果,在Sheet4中新建一张透视表,要求:
1)显示每个客户在每个出版社所订的教材数目
“出版社”
3)列区域设置为:
“客户”
4)计数项为订数。
和第4题的第6小题差不多。
第7题对DExcel07.xls文件按下列要求进行操作,结果存盘。
1、使用数组公式,计算Sheet1中的每种产品的价值,将结果保存到表中的“价值”列中。
计算价值的计算方法为:
“单价*每盒数量*采购盒数”。
选择H2:
H17,输入:
=E2:
E17*F2:
F17*G2:
G17,按Ctrl+Shift+Enter。
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:
Y"
)
4、使用函数,对Sheet3中的B21单元格中的内容进行判断,判断其是否为文本,如果是,结果为“TRUE”;
如果不是,结果为“FALSE”,并将结果保存在Sheet3中的B22单元格当中。
选中B22,输入:
=istext(B21)
Istext新的
5、将Sheet1复制到Sheet4中,对Sheet4进行高级筛选,要求:
“产品为白炽灯,商标为上海”,并将结果保存;
2)将结果保存在Sheet4中。
6、根据Sheet1的结果,在Sheet5中创建一张数据透视表,要求:
1)显示不同商标的不同产品的采购数量
2)行区域设置为“产品”
3)列区域设置为“商标”
4)计数项为“采购盒数”
和第4题的第6小题类同。
第8题对DExcel08.xls文件按下列要求进行操作,结果存盘。
1、使用IF函数,对Sheet1中的“学位”列进行自动填充
填充的内容根据“学历”列的内容来确定(假定学生均已获得相应学位)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 二级 试题 详解