Excel二级典型试题解析.docx
- 文档编号:7629398
- 上传时间:2023-01-25
- 格式:DOCX
- 页数:42
- 大小:1.47MB
Excel二级典型试题解析.docx
《Excel二级典型试题解析.docx》由会员分享,可在线阅读,更多相关《Excel二级典型试题解析.docx(42页珍藏版)》请在冰豆网上搜索。
Excel二级典型试题解析
3.1Excel2021二级典型试题解析〔*〕
3.2.1衣物采购表
1.在Sheet5中,使用函数,将A1单元格中的数四舍五入到整百,存放在B1单元格中。
2.在Sheet1中,使用条件格式将“采购数量〞列中数量大于100的单元格中字体设置为红色,加粗显示。
3.使用VLOOKUP函数,对Sheet1中的“采购表〞的“单价〞列进行填充。
*根据“价格表〞中的商品单价,使用VLOOKUP函数,将其单价填充到采购表中的“单价〞列中。
*函数中参数如果需要用到绝对地址的,请使用绝对地址进行答题,其他方式无效。
4.使用逻辑函数,对Sheet1“采购表〞中的“折扣〞列进行填充。
要求:
*根据“折扣表〞中的商品折扣率,使用相应的函数,将其折扣率填充到采购表中的“折扣〞列中。
5.使用公式,对Sheet1中“采购表〞的“合计〞列进行填充。
*根据“采购数量〞,“单价〞和“折扣〞,计算采购的合计金额,将结果保存在“合计〞列中。
*计算公式:
单价*采购数量*〔1-折扣率〕
6.使用SUMIF函数,计算各种商品的采购数量和采购总金额,将结果保存在Sheet1“统计表〞当中相应位置。
7.将Sheet1中的“采购表〞复制到Sheet2,并对Sheet2进行高级筛选。
〔1〕要求:
*筛选条件为:
“采购数量〞>150,“折扣率〞>0;
*将筛选结果保存在Sheet2中。
〔2〕注意:
*无须考虑是否删除或移动筛选条件;
*复制过程中,将标题项“采购表〞连同数据一同复制;
*复制数据表后,粘贴时,数据表必须顶格放置;
*复制过程中,保持数据一致。
8.根据Sheet1中“采购表〞,新建一个数据透视图,保存在Sheet3中。
要求:
*该图形显示每个采购时间点所采购的所有工程数量汇总的情况;
*x坐标设置为“采购时间〞;
*求和项为“采购数量〞;
*将对相应的数据透视表保存在Sheet3中。
●操作步骤如下:
1.步骤1:
单击选择Sheet5的B1单元格,在“开始〞选项卡中,单击编辑栏中的【插入函数】按钮
,翻开“插入函数〞函数对话框,选择“全部〞中的“ROUND〞函数,如图3102所示。
〔注意:
在函数具体名的情况下,可以在“搜索函数〞文本框中输入函数名,然后单击【转到】按钮,即可快速找到函数。
〕
图3102插入ROUND函数
步骤2:
单击【确定】按钮,翻开“函数参数〞对话框并设置参数,如图3103所示。
图3103ROUND函数参数对话框
步骤3:
单击【确定】按钮,完成设置。
2.步骤1:
选中Sheet1的“采购数量〞列的数据区域。
切换到“开始〞选项卡,单击“样式〞选项组的【条件格式】按钮
。
步骤2:
从弹出的菜单中选择“突出显示单元格规那么〞下的“大于〞命令〔如图3104所示〕,翻开“大于〞对话框:
输入条件“100〞;单击“设置为〞文本框右侧的下拉箭头,从弹出的子菜单里面选择“自定义格式〞〔如图3105所示〕,翻开“设置单元格格式〞对话框。
图3104“条件格式〞菜单
图3105设置“大于〞对话框
步骤3:
在“设置单元格格式〞对话框中,切换到“字体〞选项卡,选择字形和颜色,如图3106所示。
图3106“设置单元格格式〞对话框
步骤4:
单击【确定】按钮返回“大于〞对话框,再单击【确定】按钮完成设置。
3.选中D11单元格,单击编辑栏上的【插入函数】按钮
,翻开插入函数对话框并选择函数VLOOKUP。
单击【确定】按钮翻开VLOOKUP函数参数对话框,在相应的文本框中输入如图3107所示的参数〔注意:
Table_Arrray区域的绝对引用。
可在拖选选区后直接按F4功能键快速实现输入〕。
单击【确定】按钮。
双击D11单元格的填充柄填充该列的数据。
图3107VLOOKUP函数参数对话框
4.根据题意分析IF嵌套函数,画出其流程图如图3108所示。
图3108IF嵌套函数流程图
据此,那么在E11单元格中输入公式:
“=IF(B11<100,$B$3,IF(B11<200,$B$4,IF(B11<300,$B$5,$B$6)))〞。
按回车确认。
双击E11单元格的填充柄填充该列的数据。
〔注意:
如果数据格式不对,单击“开始〞选项卡的“数字选项组〞中的【百分比】按钮
即可。
〕
5.在F11单元格中输入公式“=B11*D11*(1-E11)“,按回车确认,双击F11单元格填充柄。
〔注意:
如果单元格中出现“######〞,表示该列宽度缺乏,无法显示数据。
只需加宽该列即可〕
6.计算采购数量:
单击J12单元格,单击编辑栏上的【插入函数】按钮
,翻开插入函数对话框并选择SUMIF函数,单击“确定〞翻开SUMIF函数参数对话框,并在相应的文本框中输入如图3109所示的参数(注意绝对引用和相对引用的使用)。
双击J12单元格的填充柄。
图3109SUMIF函数参数对话框
计算采购总金额:
同理,在K12单元格中插入函数,设置SUMIF函数参数如图3110所示,单击【确定】按钮。
双击K12单元格的填充柄。
图3110SUMIF函数参数对话框
7.步骤1:
复制表格。
选择Sheet1的采购表,按下“Ctrl+C〞;将光标定位在Sheet2的A1单元格,按下“Ctrl+v〞〔注意:
此时复制的数据会出现一个出错信息,如图3111所示〕。
单击【确定】按钮。
〔注意:
由于包含了公式的循环引用,此时复制的数据有错误。
〕
图3111“循环引用警告〞对话框
选择Sheet1采购表中的计算过的数据区域〔即“单价〞、“折扣〞、“合计〞列下方的数据区域〕,按下“Ctrl+C〞;将光标定位在Sheet2的错误的数据区域的起始单元格〔即“单价〞下方的单元格〕,单击鼠标右键,在弹出的快捷菜单中选择“选择性粘贴——值和数字格式〞〔如图3112所示〕。
图3112选择性粘贴
〔注意:
当某单元格出现“
〞时,表示单元格宽度不够,此时可调整单元格的宽度以看到完整的单元格数据〕
步骤2:
建立高级筛选的条件区域。
在数据区域的下方,根据题目要求建立条件区域,如图3113所示。
〔建议直接复制表格中的相关字段〕。
图3113高级筛选的条件区域
步骤3:
高级筛选。
单击数据区域中的任一单元格,然后切换功能区的“数据〞选项卡,在“排序和筛选〞选项组中单击【高级】按钮
,翻开“高级筛选〞对话框。
此时,“列表区域〞的文本框中已自动填入所有数据区域。
再把光标定位在“条件区域〞文本框内,拖动鼠标选中条件区域〔如图3114所示〕,单击【确定】按钮完成设置。
图3114选择条件区域
8.步骤1:
将光标定位在Sheet1工作表数据区域中的任一单元格,切换到功能区中的“插入〞选项卡,在“表格〞选项组中单击【数据透视表】下拉箭头,在弹出的菜单中选择“数据透视图〞命令〔如图3115所示〕,翻开“创立数据透视表及数据透视图〞对话框。
图3115选择“数据透视表〞菜单
图3116“创立数据透视表及数据透视图〞对话框
此时,在“选择一个表或区域〞单项选择按钮下方的“表/区域〞文本框中自动填入了表格的数据区域,如图3116上方所示。
步骤2:
选择“现有工作表〞单项选择按钮,将光标定位在“位置〞右侧的文本框中,单击Sheet3工作表标签切换到Sheet3工作表,并单击A1单元格,如图3116下方所示。
步骤3:
单击【确定】按钮,进入数据透视表及数据透视图设计环境:
从“选择要添加到报表的字段〞列表框中,将“采购时间〞拖到“轴字段〞框中;将“采购数量〞拖到“∑数值〞框中,如图3117所示。
最后的效果图见图3118。
图3117设置数据透视图〔表〕字段列表
图3118数据透视图〔表〕效果图
3.2.2教材订购情况表
1.在Sheet5的A1单元格中设置只能录入5位数字或文本。
当录入位数错误时,提示错误原因,样式为“警告〞,错误信息为“只能录入5位数字或文本〞。
2.在Sheet5的B1单元格中输入分数1/3。
3.使用数组公式,对Sheet1中“教材订购情况表〞的订购金额进行计算。
*将结果保存在该表的“金额〞列当中。
*计算方法:
金额=订数*单价。
4.使用统计函数,对Sheet1中“教材订购情况表〞的结果按以下条件进行统计,并将结果保存在Sheet1中的相应位置。
要求:
*统计出版社名称为“高等教育出版社〞的书的种类数,并将结果保存在Sheet1中的L2单元格中。
*统计订购数量大于110且小于850的书的种类数,并将结果保存在Sheet1中的L3单元格中。
5.使用函数,计算每个用户所订购图书所需支付的金额总数,并将结果保存在Sheet1中的“用户支付情况表〞的“支付金额〞列中。
6.使用函数,判断Sheet2中的年份是否为闰年,如果是,结果保存“闰年〞;如果不是,那么结果保存“平年〞,并将结果保存在“是否为闰年〞列中。
*闰年定义,年数能被4整除而不能被100整除,或者能被400整除的年份。
7.将Sheet1中的“教材订购情况表〞复制到Sheet3,并对Sheet3进行高级筛选。
〔1〕要求:
*筛选条件为“订数>=500,且金额总数<=30000”;
*将结果保存在Sheet3中。
〔2〕注意:
*无须考虑是否删除或移动筛选条件;
*复制过程中,将标题项“教材订购情况表〞连同数据一同复制;
*数据表必须顶格放置;
*复制过程中,数据保持一致。
8.根据Sheet1中“教材订购情况表〞的结果,在Sheet4中新建一张数据透视表。
要求:
*显示每个客户在每个出版社所订的教材数目;
*行区域设置为“出版社〞;
*列区域设置为“客户〞;
*求和项为订数;
*数据区域设置为“订数〞。
●操作步骤如下:
1.步骤1:
选中Sheet5工作表中的A1单元格,切换到功能区的“数据〞选项卡,单击“数据工具〞选项组中的“数据有效性〞的上半部按钮
,翻开“数据有效性〞对话框。
步骤2:
切换到“设置〞选项卡:
选择“允许〞下拉菜单为“文本长度〞;选择“数据〞下拉菜单为“等于〞;并在“长度〞文本框中输入“5〞,如图3119所示。
图3119设置数据有效性
步骤3:
再切换到“出错警告〞选项卡:
选择“样式〞下拉菜单为“警告〞;在“错误信息〞文本框中输入“只能录入5位数字或文本〞,如图3120所示,单击【确定】按钮完成设置。
图3120设置出错信息
2.在Sheet5的B1单元格中,输入“01/3〞〔注意:
0和1中间以空格间隔〕。
3.在Sheet1工作表中,先选中“金额〞列的数据区域,再输入公式“=G3:
G52*H3:
H52”〔注意:
各列数据区域建议用鼠标拖拽选取〕,然后按下“Ctrl+Shift+Enter〞组合键即可。
4.步骤1:
选中L2单元格,单击编辑栏上的【插入函数】按钮
,翻开插入函数对话框并选择“统计〞函数中的COUNTIF函数,单击【确定】按钮翻开COUNTIF函数参数对话框,并在相应的文本框中输入如图3121所示的参数〔注意:
Range文本框中的单元格区域可拖拽鼠标进行输入;Criteria文本框中的文本可直接单击“高等教育出版社〞所在的单元格进行输入〕,单击【确定】按钮。
图3121COUNTIF函数参数对话框
步骤2:
选中L2单元格,单击编辑栏上的【插入函数】按钮
,翻开插入函数对话框并选择“统计〞函数中的COUNTIFS函数,单击“确定〞翻开COUNTISF函数参数对话框,并在相应的文本框中输入如图3122所示的参数〔注意:
Criteria_Range各文本框中的单元格区域可拖拽鼠标进行输入〕,单击【确定】按钮。
图3122COUNTIFS函数参数对话框
5.选中L8单元格,单击编辑栏上的【插入函数】按钮
,翻开插入函数对话框并选择“数学与三角函数〞函数中的SUMIF函数,单击【确定】按钮翻开SUMIF函数参数对话框,并在相应的文本框中输入如图3123所示的参数〔注意:
绝对引用可在鼠标拖选相应的单元格区域后,按F4功能键快速实现输入〕。
双击L8单元格的填充柄。
图3123SUMIF函数参数对话框
6.步骤1:
根据题意,闰年条件分析如错误!
未找到引用源。
23所示。
两个条件满足其中之一
OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0)
图3124闰年条件分析
步骤2:
选中Sheet2工作表的B2单元格,单击编辑栏上的【插入函数】按钮
,翻开插入函数对话框并选择“逻辑〞函数中的IF函数,单击【确定】按钮翻开IF函数参数对话框,并在相应的文本框中输入如图3124所示的参数〔Logical_test文本框中的参数为“OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0)〞〕。
双击B2单元格的填充柄。
图3124IF函数参数对话框
7.参考3.2.1中的题7。
8.可参考3.2.1中的题8。
步骤1:
将光标定位在Sheet1数据区域的任一单元格,切换到功能区中的“插入〞选项卡,在“表格〞选项组中单击【数据透视表】下拉箭头,在弹出的菜单中选择“数据透视表〞命令〔如图3125所示〕,翻开翻开“创立数据透视表〞对话框。
图3125“数据透视表〞菜单
此时,在“选择一个表或区域〞单项选择按钮下方的“表/区域〞文本框中自动填入了表格的数据区域。
步骤2:
选择“现有工作表〞单项选择按钮,将光标定位在“位置〞右侧的文本框中,单击Shee4工作表标签切换到Sheet4工作表,并单击A1单元格。
步骤3:
单击【确定】按钮,进入数据透视表设计环境:
从“选择要添加到报表的字段〞列表框中,将“出版社〞拖到“行标签〞框中;将“客户〞拖到“列标签〞框中;将“定数〞拖到“∑数值〞框中,如图3126所示。
最后的效果图见图3127。
图3126设置数据透视表字段列表
图3127数据透视表效果图
3.2.3公务员考试成绩表
1.在Sheet5的A1单元格中输入分数1/3。
2.在Sheet1中,使用条件格式将“性别〞列中为“女〞的单元格中字体颜色设置为红色、加粗显示。
3.使用IF函数,对Sheet1中“学位〞列进行自动填充。
要求:
填充的内容根据“学历〞列的内容来确定〔假定学生均已获得相应学位〕
*博士研究生—博士
*硕士研究生—硕士
*本科—学士
*其他—无。
4.使用数组公式,在Sheet1中计算:
计算笔试比例分,并将结果保存在“公务员考试成绩表〞中的“笔试比例分〞中。
*计算方法为:
笔试比例分=〔笔试成绩/3〕*60%
计算面试比例分,并将结果保存在“公务员考试成绩表〞中的“面试比例分〞中。
*计算方法为:
面试比例分=面试成绩*40%
计算总成绩,并将结果保存在“公务员考试成绩表〞中的“总成绩〞中。
*计算方法:
总成绩=笔试比例分+面试比例分
5.将Sheet1中的“公务员考试成绩表〞复制到Sheet2,根据以下要求修改“公务员考试成绩表〞中的数组公式,并将结果保存在Sheet2的相应列中。
要求:
*修改“笔试比例分〞的计算,计算方法为:
笔试比例分=〔笔试成绩/2〕*60%,并将结果保存在“笔试成绩比例分〞列中。
注意:
*复制过程中,将标题项“公务员考试成绩表〞连同数据一同复制;
*复制数据表后,粘贴时,数据表必须顶格放置。
6.在Sheet2中,使用函数,根据“总成绩〞列对所有考生进行排名。
〔如果多个数值排名相同,那么返回该组数值的最正确排名〕
*要求:
将排名结果保存在“排名〞列中。
7.将Sheet2中的“公务员考试成绩表〞复制到Sheet3,并对Sheet3进行高级筛选。
〔1〕要求:
*筛选条件为:
“报考单位〞—一中院、“性别〞—男、“学历〞—硕士研究生;
*将筛选结果保存在Sheet3中。
〔2〕注意:
*无须考虑是否删除或移动筛选条件;
*复制过程中,将标题项“公务员考试成绩表〞连同数据一同复制;
*复制数据表后,粘贴时,数据表必须顶格放置。
8.根据Sheet2中的“公务员考试成绩表〞,在Sheet4中创立一张数据透视表。
要求:
*显示每个报考单位的人的不同学历的人数汇总情况;
*行区域设置为“报考单位〞;
*列区域设置为“学历〞;
*数据区域设置为“学历〞;
*计数项为学历。
●操作步骤如下:
1.参考3.2.2中的题2。
2.参考3.2.1中的题2。
3.步骤1:
根据题意,分析IF嵌套函数,画出其流程图如图3128所示。
图3128IF嵌套函数流程图
步骤2:
根据以上流程图,在H3单元格中输入公式:
“=IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无")))〞〔注意:
务必在英文输入法状态下输入字符,仅中文字符例外;且括号要配对〕。
按回车确认。
双击H3单元格的填充柄填充该列的数据。
4.选中“笔试比例分〞列的数据区域,输入公式“=I3:
I18/3*0.6”,〔各列数据区域可用鼠标拖拽选取〕,然后按下Ctrl+Shift+Enter组合键。
同理,选中“面试比例分〞列的数据区域,输入公式“=I3:
I18*0.4”,同上。
同理,选中“总成绩〞列的数据区域,输入公式“=J3:
J18+L3:
L18”,同上。
5.步骤1:
选中Sheet1工作表中的数据区域,复制;单击Sheet2工作表的A1单元格,右键单击选择“粘贴选项〞的第一项。
步骤2:
在“笔试成绩比例分〞中修改公式“=I3:
I18/2*0.6”,按下Ctrl+Shift+Enter组合键。
6.选中Sheet2工作表的N3单元格,单击编辑栏上的【插入函数】按钮
,翻开插入函数对话框并选择RANK.EQ函数,单击【确定】按钮翻开RANK.EQ函数参数对话框:
在相应的文本框中输入如图3129所示的参数〔注意使用绝对引用,用鼠标拖选选区后,直接按下F4功能键快速实现输入〕。
按回车确认。
双击N3单元格的填充柄填充该列的数据。
图3129RANK.EQ函数参数对话框
7.参考3.2.1中的题7。
8.参考3.2.2中的题8。
3.2.4杭州用户情况表
1.在Sheet5的A1单元格中设置只能录入5位数字或文本。
当录入位数错误时,提示错误原因,样式为“警告〞,错误信息为“只能录入5位数字或文本〞。
2.在Sheet5的B1单元格中输入公式,判断当年是否为闰年,结果为TRUE或FALSE。
*闰年定义:
年数能被4整除而不能被100整除,或者能被400整除的年份。
3.使用时间函数,对Sheet1中用户的年龄进行计算。
要求:
*假设当前时间是“2021-5-1〞,结合用户的出生年月,计算用户的年龄,并将其计算结果保存在“年龄〞列当中。
计算方法为两个时间年份之差。
4.使用REPLACE函数,对Sheet1中用户的号码进行升级。
要求:
*对“原号码〞列中的号码进行升级。
升级方式是在区号〔0571〕后面加上“8〞,并将其计算结果保存在“升级号码〞列的相应单元格中。
*例如:
号码“0571*******”升级后为“0571********”。
5.在Sheet1中,使用AND函数,根据“性别〞及“年龄〞列中的数据,判断所有用户是否为大于等于40岁的男性,并将结果保存在“是否>=40男性〞列中。
*注意:
如果是,保存结果为TRUE;否那么,保存结果为FALSE。
6.根据Sheet1中的数据,对以下条件,使用统计函数进行统计。
要求:
*统计性别为“男〞的用户人数,将结果填入到Sheet2的B2单元格中。
*统计年龄为“>40”岁的用户人数,将结果填入到Sheet2的B3单元格中。
7.将Sheet1复制到Sheet3中,并对Sheet3进行高级筛选。
〔1〕要求:
*筛选条件为:
“性别〞—女,“所在区域〞—西湖区;
*将筛选结果保存在Sheet3中。
〔2〕注意:
*无须考虑是否删除或移动筛选条件;
*复制数据表后,粘贴时,数据表必须顶格放置。
8.根据Sheet1的结果,创立一个数据透视图,保存在Sheet4中。
要求:
*显示每个区域所拥有的用户数量;
*x坐标设置为“所在区域〞;
*计数项为“所在区域〞;
*将对应的数据透视表保存在Sheet4中。
●操作步骤如下:
1.参考3.2.2中的题1。
2.参考3.2.2中的题6。
此处输入公式“=OR(AND(MOD(YEAR(NOW()),4)=0,MOD(YEAR(NOW()),100)<>0),MOD(YEAR(NOW()),400)=0)〞。
〔注意:
NOW函数无参数,只有括号〕
3.步骤1:
单击Sheet的D2单元格,插入时间函数YEAR,在翻开的YEAR函数参数对话框中,输入如图3130所示的参数,得到年份2021。
图3130YEAR函数参数对话框
(1)
步骤2:
再在该公式后面减去如所图3131示的计算所得的年份,按回车。
〔此时公式显示为“=YEAR("2021-5-1")-YEAR(C2)〞〕。
按回车,再填充公式。
图3131YEAR函数参数对话框
(2)
4.选择Sheet的G2单元格,插入REPLACE函数,在翻开的REPLACE函数参数对话框中输入如图3132所示的参数,单击【确定】按钮。
双击G2单元格的填充柄填充该列的数据。
图3132REPLACE函数参数对话框
5.选择Sheet1的H2单元格,插入AND函数,在翻开的AND函数参数对话框中输入如图3133所示的参数,单击【确定】按钮。
双击H2单元格的填充柄填充该列的数据。
图3133AND函数参数对话框
6.参考3.2.2中的题4。
7.参考3.2.1中的题7。
8.参考3.2.1中的题8。
3.2.5停车情况记录表
1.在Sheet4的A1单元格中设置为只能录入5位数字或文本。
当录入位数错误时,提示错误原因,样式为“警告〞,错误信息为“只能录入5位数字或文本〞。
2.在Sheet4的B1单元格中输入公式,判断当前年份是否为闰年,结果为TURE或FALSE.
*闰年定义:
年数能被4整除而不能被100整除,或者能被400整除的年份。
3.使用HLOOKUP函数,对Sheet1“停车情况记录表〞中的“单价〞列进行填充。
要求:
*根据Sheet1中的“停车价目表〞价格,使用HLOOKUP函数对“停车情况记录表〞中的“单价〞列根据不同的车型进行填充。
注意
*函数中如果需要用到绝对地址的请使用绝对地址进行计算,其他方式无效。
4.在Sheet1中,使用时间函数计算汽车在停车库中的停放时间。
要求:
*计算方法为:
“停放时间=出库时间-入库时间〞
*格式为:
“小时:
分钟:
秒〞
*将结果保存在“停车情况记录表〞中的“停放时间〞列中
*例如:
一小时十五分十二秒在停放时间中的表示为:
“1:
15:
12”。
5.使用函数公式,对“停车情况记录表〞中的停车费用进行计算。
要求:
*根据Sheet1停放时间的长短计算停车费用,将计算结果填入到“停车情况记录表〞中的“应付金额〞列中。
注意:
*停车按小时收费,对于不满一个小时的按照一个小时计费;
*对于超过整点小时数十五分钟〔包含十五分钟〕的,多累积一个小时;
*例如:
1小时23分,将以2小时计费。
6.使用统计函数,对Sheet1中的“停车情况记录表〞根据以下条件进行统计。
*统计停车费用大于等于40元的停车记
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 二级 典型 试题 解析