Excel高级应用历年真题.docx
《Excel高级应用历年真题.docx》由会员分享，可在线阅读，更多相关《Excel高级应用历年真题.docx（10页珍藏版）》请在冰点文库上搜索。
Excel高级应用历年真题
Excel高级应用-历年真题
E某cel高级应用—历年真题
考生文件夹：
d:
\\reult\\医院病人护理统计表
1．在heet4中，使用函数，根据A1单元格中的身份证号码判断性别，结果为“男”或“女”，存放在A2单元格中。
2．在heet4中，使用函数，将B1单元格中的数四舍五入到整百，存放在C1单元格中。
3．使用VLOOKUP函数，根据Sheet1中的“护理价格表”，对“医院病人护理统计表”中的“护理价格”列进行自动填充。
①单击F3单元格——单击“插入函数”按钮——选择“VLOOKUP”函数②设置参数如图所示：
③对该列下方的值进行自动填充
4．使用数组公式，根据Sheet1中“医院病人护理统计表”中的“入住时间”列和“出院时间”列中的数据计算护理天数，并把结果保存在“护理天数”列中。
计算方法为：
护理天数=出院时间－入住时间。
①单击H3单元格——输入“G3-D3”——按“Ctrl+Shift+Enter”键输入数组公式②对该列下方的值进行自动填充
5．使用数组公式，根据Sheet1中“医院病人护理统计表”的“护理价格”和“护理天数”列，对病人的护理费用进行计算，并把结果保存在该表的“护理费用”列中。
计算方法为：
护理费用=护理价格某护理天数。
①选择I3单元格——输入“=F3某H3”——按“Ctrl+Shift+Enter”键输入数组公式②选择I3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充）6．使用数据库函数，按以下要求计算。
要求：
（a）计算Sheet1“医院病人护理统计表”中，性别为女性，护理级别为中级护理，护理天数大于30天的人数，并保存N13单元格中；
（b）计算护理级别为高级护理的护理费用总和，并保存N22单元格中。
①单击N13单元格——单击“插入函数”按钮——选择“DCOUNTA”函数②设置参数如图所示：
③单击N22单元格——单击“插入函数”按钮——选择“DSUM”函数④设置参数如图所示：
7．把Sheet1中的“医院病人护理统计表”复制到Sheet2，进行自动筛选。
要求：
（a）筛选条件为：
“性别”—女、“护理级别”—高级护理；（b）将筛选结果保存在Sheet2中。
注意：
（a）复制过程中，将标题项“医院病人护理统计表”连同数据一同复制。
（b）复制数据表后，粘贴时，数据表必须顶格放置。
①选择A1至I30单元格——“复制”——单击Sheet2工作表——单击A1单元格——“粘贴”②复制Sheet工作表中F3至F30单元格——单击Sheet2工作表——单击F3单元格——“选择性粘贴”——“数值”
③将光标定位到Sheet2的A2单元格——“数据”菜单——“筛选”——“自动筛选”④单击“性别”标题项右边的筛选按钮——选择“女”
⑤单击“护理级别”标题项右边的筛选按钮——选择“高级护理”
8．根据Sheet1中的“医院病人护理统计表”，创建一个数据透视图Chart1，要求：
（a）显示每个护理级别的护理费用情况；（b）某坐标设置为“护理级别”；（c）数据区域设置为“护理费用”；（d）求和为护理费用；
（e）将对应的数据透视表保存在Sheet3中。
考生文件夹：
d:
\\reult\\图书订购信息表
1．在Sheet4中，使用函数，根据E1单元格中的身份证号码判断性别，结果为“男”或“女”，存放在F1单元格中。
倒数第二位为奇数的为“男”，为偶数的为“女”
2．在Sheet4中，使用条件格式将“性别”列中数据为“女”的单元格中字体颜色设置为红色、加粗显示。
3．使用IF和MID函数，根据Sheet1中的“图书订购信息表”中的“学号”列对“所属学院”列
2
进行填充。
要求：
根据每位学生学号的第七位填充对应的“所属学院”。
学号第七位为1——计算机学院学号第七位为0——电子信息学院
①选择C3单元格——单击“插入函数”按钮——选择“IF”函数②设置参数如图所示：
③对该列下方的值实行自动填充。
4．使用COUNTBLANK函数，对Sheet1中的“图书订购信息表”中的“订书种类数”列进行填充。
注意：
（a）其中“1”表示该同学订购该图书，空格表示没有订购；
（b）将结果保存在Sheet1中的“图书订购信息表”中的“订书种类数”列。
①选择H3单元格——在编辑框中输入（如图所示）：
②对该列下方的值实行自动填充。
5．使用公式，对Sheet1中的“图书订购信息表”中的“应缴总额（元）”列进行填充。
计算方法为：
应缴总额=C语言某单价+高等数学某单价+大学语文某单价+大学英语某单价。
①选择I3单元格——在编辑框中输入（如图所示）：
②对该列下方的值实行自动填充。
6．使用统计函数，根据Sheet1中“图书订购信息表”的数据，统计应缴总额大于100元的学生人数，将结果保存在Sheet1的M9单元格中。
①单击M9单元格——单击“插入函数”按钮——选择“COUNTIF”函数②设置参数如图所示：
③对该列下方的值实行自动填充
7．将Sheet1中“图书订购信息表”复制到Sheet2中，并对Sheet2进行自动筛选，要求：
a．筛选条件为：
“订书种类数”——>=3，“所属学院”——计算机学院;b．将筛选结果保存在Sheet2中。
注意：
（a）复制过程中，将标题项“图书订购信息表”连同数据一同复制；（b）复制数据表后，粘贴时，数据表必须顶格放置。
8．根据Sheet1中“图书订购信息表”，创建一张数据透视图Chart1，要求：
3
a．显示每个学院图书订购的订书金额汇总情况；b．某坐标设置为“所属学院”；c．数据区域为“订购金额（元）”。
d．求和项设置为订书金额（元）；e．将对应的数据透视表保存在Sheet3中。
考生文件夹：
d:
\\reult\\零件检测结果表
1．在Sheet4的A1单元格中输入分数1/3
2．在heet4中，使用函数，将B1中的时间四舍五入到最接近的15分钟的倍数，结果存放在C1单元格中。
3．使用数组公式，根据Sheet1中“零件检测结果表”的“外轮直径”和“内轮直径”列，计算内外轮差，并将结果表保存在“轮差”列中。
计算方法为：
轮差=外轮直径—内轮直径。
①选择D3单元格——输入“=B3-C3”——按“Ctrl+Shift+Enter”键输入数组公式②选择D3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充）4．使用IF函数，对Sheet1中的“零件检测结果表”的“检测结果”列进行填充。
要求：
（a）如果“轮差”<4mm，测量结果保存为“合格”，否则为“不合格”；（b）将计算结果保存在Sheet1中的“零件检测结果表”的“检测结果”列。
①选择E3单元格——单击“插入函数”按钮——选择“IF”函数②设置参数如图所示：
③对该列下方的值实行自动填充
5．使用统计函数，根据以下要求进行计算，并将结果保存在相应位置。
要求：
（a）统计：
轮差为0的零件的个数，并将结果保存在Sheet1中的K4单元格中；（b）统计：
零件的合格率，并将结果保存在Sheet1中的K5单元格中。
注意：
（a）计算合格率时，分子分母必须用函数计算。
（b）合格率的计算结果保存为数值型小数点后两位。
①选择K4单元格——单击“插入函数”按钮——选择“COUNTIF”函数②设置参数如图所示：
③对该列下方的值实行自动填充。
4
④选择K5单元格——在编辑框中输入（如图所示）：
⑤设置参数如图所示：
⑤对该列下方的值实行自动填充。
6．使用文本函数，判断Sheet1中“字符串2”在“字符串1”中的起始位置并把返回结果保存在Sheet1中的K9单元格中。
①选择K9单元格——单击“插入函数”按钮——选择“FIND”函数②设置参数如图所示：
③对该列下方的值实行自动填充。
7．将Sheet1中“零件检测结果表”复制到Sheet2中，并自动筛选，要求：
a．筛选条件为：
“制作人员”——赵俊峰，“检测结果”——合格;b．将筛选结果保存在Sheet2中。
注意：
（a）复制过程中，将标题项“零件检测结果表”连同数据一同复制；（b）复制数据表后，粘贴时，数据表必须顶格放置。
8．根据Sheet1中“零件检测结果表”，在Sheet3中新建一张数据透视表，要求：
a．显示每个制作人员制作的不同检测结果的零件个数情况；b．行区域设置为“制作人员”；c．列区域设置为“检测结果”。
d．数据区域设置为“检测结果”。
；e．计数项伟检测结果。
考生文件夹：
d:
\\reult\\学生成绩表1
1．在heet5中，使用函数，将B1中的时间四舍五入到最接近的15分钟的倍数，结果存放在C1单元格中。
2．在heet1中，使用条件格式将“铅球成绩（米）”列中单元格数据按下列要求显示。
数据大于9的单元格中字体颜色设置为红色、加粗显示；数据介于7和9之间的单元格中字体颜色设置为蓝色、加粗显示；数据小于7的单元格中字体颜色设置为绿色、加粗显示。
选中heet1中的“铅球成绩（米）”的数据，选择“格式”→“条件格式”，在“条件格式”对话框中，单击两次“添加”按钮，并做如下三个条件的输入设置，并分别选择“格式”按钮进行相应的颜色和加粗设置。
5
3．在Sheet1“学生成绩表”中，使用REPLACE函数和数组公式，将原学号转变成新学号，同时将所得的新学号填入“新学号”列中，转变方法：
将原学号的第四位后面加上“5”，例如：
“2007032001”——>20075032001
①选择B3单元格——单击“插入函数”按钮——选择“REPLACE”函数②设置参数如图所示：
③按Ctrl+Shift+Alt键，计算值，并对该列下方的值实行自动填充。
4．使用IF函数和逻辑函数，对Sheet1中“学生成绩表”中的“结果1”和“结果2”列进行填充。
要求：
填充的内容根据以下条件确定：
（将男生、女生分开写进IF函数当中）结果1：
如果是男生，成绩<14.00，填充为“合格”；成绩>=14.00，填充为“不合格”；如果是女生，成绩<16.00，填充为“合格”；成绩>=16.00，填充为“不合格”；结果2：
如果是男生，成绩>7.50，填充为“合格”；成绩<=7.5，填充为“不合格”；如果是女生，成绩>5.50，填充为“合格”；成绩<=5.50，填充为“不合格”；
①单击F3单元格——在编辑框中输入（如图所示）:
②对该列下方的值实行自动填充。
③单击H3单元格——在编辑框中输入（如图所示）:
④对该列下方的值实行自动填充。
5．对于Sheet1“学生成绩表”中的数据，根据以下条件，使用统计函数进行统计，要求：
（a）获取“100米跑的最快的学生成绩”，并将结果填入到Sheet1的K4单元格中；（b）统计“所有学生结果1为合格的总人数”，并将结果填入到Sheet1的K5单元格中；
①选择K4单元格——单击“插入函数”按钮——选择“MIN”函数
6
②设置参数如图所示：
③选择K5单元格——单击“插入函数”按钮——选择“COUNTIF”函数④设置参数如图所示：
6．根据Sheet2中的贷款情况，使用财务函数对贷款偿还金额进行计算，要求：
（a）计算“按年偿还贷款金额（年末）”，并将结果填入到Sheet2的E2单元格中；（b）计算“第9个月贷款利息金额”，并将结果填入到Sheet2的E3单元格中；
①单击Sheet2——单击E2单元格——单击“插入函数”按钮——选择“PMT”函数:
②单击E3单元格——单击“插入函数”按钮——选择“ISPMT”函数:
7．将Sheet1中的“学生成绩表”复制到Sheet3中，并对Sheet3进行高级筛选，要求：
a．筛选条件为：
“性别——男、100米成绩（秒）——<=12.00、铅球成绩（米）——>9.00”；b．将筛选结果保存在Sheet3中。
注意：
（a）无需考虑是否删除或移动筛选条件；
（b）复制过程中，将标题项“学生成绩表”连同数据一同复制。
（c）复制数据表后，粘贴时，数据表必须顶格放置。
8．根据Sheet1中“学生成绩表”的结果，在Sheet4中创建一张数据透视表，要求：
a．显示每种性别学生的合格与不合格总人数；b．行区域设置为“性别”；c．列区域设置为“结果1”；d．计数项为结果1；
e．数据区域设置为“结果1”。
7
考生文件夹：
d:
\\reult\\通讯费年度计划表
1．在Sheet4的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。
闰年定义：
年数能被4整除而不能被100整除，或者能被400整除的年份。
2．在heet1中，使用条件格式将“岗位类别”列中单元格数据按下列要求显示。
数据为“副经理”的单元格中字体颜色设置为红色、加粗显示；数据为“服务部”的单元格中字体颜色设置为蓝色、加粗显示
3．使用VLOOKUP函数，根据Sheet1中的“岗位最高限额明细表”，填充“通讯费年度计划表”中的“岗位标准”列。
①选择D4单元格——单击“插入函数”按钮——选择“VLOOKUP”函数②设置参数如图所示：
③对该列下方的值实行自动填充。
4．使用INT函数，计算Sheet1中“通讯费年度计划表”的“预计报销总时间”列。
要求：
（a）每月以30天计算；
（b）将结果填充在“预计报销总时间”列中。
①单击G4单元格——在编辑框中输入（如图所示）:
②右击单元格G4，选择“设置单元格格式”→数值→小数位0位③对该列下方的值实行自动填充。
5．使用数组公式，计算Sheet1中“通讯费年度计划表”的“年度费用”列，计算方法为：
年度费用=岗位标准某预计报销总时间。
①单击H4单元格——输入“=D4某G4”——按“Ctrl+Shift+Enter”键输入数组公式②对该列下方的单元格的值进行自动填充
6．使用函数，根据Sheet1中“通讯费年度计划表”的“年度费用”列，计算预算总金额。
要求：
（a）将结果保存在Sheet1中的C2单元格中。
（b）并根据C2单元格中的结果，转换为金额大写形式，保存在Sheet1中的F2单元格中。
①单击C2单元格——单击“插入函数”按钮——选择“SUM”函数:
②单击F2单元格，输入“=C2”——右键单击——“设置单元格格式”
8
7．将Sheet1中的“通讯费年度计划表”复制到Sheet2中，并对Sheet2进行自动筛选，要求：
a．筛选条件为：
“岗位类别——技术研发、报销地点——武汉”；b．将筛选结果保存在Sheet2中。
注意：
（a）无需考虑是否删除或移动筛选条件；
（b）复制过程中，将标题项“通讯费年度计划表”连同数据一同复制。
（c）复制数据表后，粘贴时，数据表必须顶格放置。
8．根据Sheet1中“通讯费年度计划表”的结果，在Sheet3中创建一张数据透视表，要求：
a．显示不同报销地点不同岗位的年度费用情况；b．行区域设置为“报销地点”；c．列区域设置为“岗位类别”；d．求和项为年度费用；
e．数据区域设置为“年度费用”。
考生文件夹：
d:
\\reult\\三月份销售统计表
1．在Sheet4中使用函数计算A1:
A10中奇数的个数，结果存放在A12单元格中。
①在Sheet4中选择A12单元格——单击“插入函数”按钮——选择“SUMPRODUCT”函数②设置参数如图所示：
2．在Sheet4的B1单元格中输入分数1/3
在Sheet4中选择B1单元格，输入0，再按一下空格键，接着输入1/3即可。
3．使用VLOOKUP函数，对Sheet1中的“3月份销售统计表”的“产品名称”列和“产品单价”列进行填充。
要求：
根据“企业销售产品清单”，使用VLOOKUP函数，将产品名称和产品单价填
9
充到“3月份销售统计表”的“产品名称”列和“产品单价”列中。
①选择G3单元格——单击“插入函数”按钮——选择“VLOOKUP”函数②设置参数如图所示：
③对该列下方的值实行自动填充。
④选择H3单元格——单击“插入函数”按钮——选择“VLOOKUP”函数⑤设置参数如图所示：
⑥对该列下方的值实行自动填充。
4．使用数组公式，计算Sheet1中的“3月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中，计算方法：
销售金额=产品单价某销售数量
①单击L3单元格——输入“=H3某I3”——按“Ctrl+Shift+Enter”键输入数组公式:
②对该列下方的值实行自动填充。
5．使用统计函数，根据“3月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入该表的“总销售额”列。
①选择O3单元格——单击“插入函数”按钮——选择“SUMIF”函数②设置参数如图所示：
③对该列下方的值实行自动填充。
6．在Sheet1中，使用RANK函数，在“分部销售业绩统计表”中，根据“总销售额”对各部门进行排名，并将结果填入到“销售排名”列中。
①选择P3单元格——单击“插入函数”按钮——选择“RANK”函数②设置参数如图所示：
10
升级会员 