Excel高级应用操作课件

《Excel高级应用操作课件》由会员分享，可在线阅读，更多相关《Excel高级应用操作课件（51页珍藏版）》请在金锄头文库上搜索。

1、第第6章章 Excel2003高级应用操作高级应用操作Excel高级应用操作第 2 页第第6章章 Excel2003高级应用操作高级应用操作本章的重点主要包括：条件格式；数据有效性设置；相对地址与绝对地址；公式与数组；单元格数字格式；数据填充；函数及函数的嵌套；高级筛选；数据透视表和数据透视图。第 3 页61基本操作基本操作（1）SUM函数函数1 1、数学与三角函数、数学与三角函数（2）SUMIF函数函数（4）INT函数函数（3）ABS函数函数（5）MOD函数函数第 4 页61基本操作基本操作（1）AVERAGE函数函数2 2、统计函数、统计函数 （2）COUNTIF函数函数（3）MAXMIN

2、函数函数（4）RANK函数函数第 5 页61基本操作基本操作（1）HLOOKUP函数函数3 3、查找与引用函数、查找与引用函数（2）VLOOKUP函数函数第 6 页61基本操作基本操作（1）AND函数函数4 4、逻辑函数、逻辑函数（2）OR函数函数（4）IF函数函数（3）NOT函数函数第 7 页61基本操作基本操作（1）YEAR函数函数5 5、日期和时间函数、日期和时间函数（2）MONTH函数函数（4）NOW函数函数（3）DAY函数函数（5）TODAY函数函数（6）HOUR函数函数（7）MINUTE函数函数第 8 页61基本操作基本操作（1）REPLACE函数函数6 6、文本函数、文本函数（2

3、）MID函数函数（4）EXACT函数函数（3）CONCATENATE函数函数（5）PROPER函数函数（6）UPPER函数函数（7）TEXT函数函数第 9 页61基本操作基本操作7 7、信息函数、信息函数8 8、数据库函数、数据库函数9 9、财务函数、财务函数1010、数组公式、数组公式第 10 页常见试题常见试题1打开第打开第6章章Excel操作素材库中的操作素材库中的“通讯费年度计划表通讯费年度计划表.xls”文件，按下面文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。的操作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不

4、能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet1中，使用条件格式将中，使用条件格式将“岗位类别岗位类别”列中单元格数据按下列列中单元格数据按下列要求显示。要求显示。数据为数据为“副经理副经理”的单元格中字体颜色设置为红色，加粗显示；的单元格中字体颜色设置为红色，加粗显示；数据为数据为“服务部服务部”的单元格中字体颜色设置为蓝色、加粗显示。的单元格中字体颜色设置为蓝色、加粗显示。（2）使用）使用VLOOKUP函数，根据函数，根据Sheet1中的中的“岗位最高限额明细表岗位最高限额明细表”，填，填充充“通讯费年度计划表通讯费年度计划表”中的中的“岗位标准岗位标准”列。列。（3）使

5、用）使用INT函数，计算函数，计算Sheet1中的中的“通讯费年度计划表通讯费年度计划表”的的“预计报预计报销总时间销总时间”列，要求：列，要求：每月以每月以30天计算；天计算；将结果填充在将结果填充在“预计报销总时间预计报销总时间”列中。列中。（4）使用数组公式，计算）使用数组公式，计算Sheet1中中“通讯费年度计划表通讯费年度计划表”的的“年度费用年度费用”列。列。计算方法为：年度费用计算方法为：年度费用=岗位标准岗位标准*预计报销总时间。预计报销总时间。62 试题讲解试题讲解第 11 页（5）根据）根据Sheet1中中“通讯费年度计划表通讯费年度计划表”的的“年度费用年度费用”列，计算

6、预算总金额。列，计算预算总金额。要求：要求：使用函数计算并将结果保存在使用函数计算并将结果保存在Sheet1中的中的C2单元格中。单元格中。并根据并根据C2单元格中的结果，转换为金额大写形式，保存在单元格中的结果，转换为金额大写形式，保存在Sheet1中的中的F2单元格中。单元格中。（6）将）将Sheet1中的中的“通讯费年度计划表通讯费年度计划表”复制到复制到Sheet2中，并对中，并对Sheet2进行自进行自动筛选。要求：动筛选。要求：筛选条件为：筛选条件为：“岗位类别岗位类别”为技术研发、为技术研发、“报销地点报销地点”为武汉；为武汉；将筛选条件保存在将筛选条件保存在Sheet2中。中。

7、注意：注意：1）无须考虑是否删除或移动筛选条件。）无须考虑是否删除或移动筛选条件。 2）在复制过程中，将标题项）在复制过程中，将标题项“通讯费年度计划表通讯费年度计划表”连同数据一同复制；连同数据一同复制； 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据Sheet1中的中的“通讯费年度计划表通讯费年度计划表”，在，在Sheet3中新建一张数据透视表。中新建一张数据透视表。要求：要求：显示不同报销地点不同岗位的年度费用情况；显示不同报销地点不同岗位的年度费用情况；行区域设置为行区域设置为“报销地点报销地点”；列区域设置为列区域设置为“岗位类别岗位类别”；数据区域设置为数据区域设置

8、为“年度费用年度费用”；求和项为年度费用。求和项为年度费用。 62 试题讲解试题讲解第 12 页常见试题常见试题2打开第打开第6章章Excel操作素材库中的操作素材库中的“电话号码统计电话号码统计.xls”文件，按下面的操文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet5的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本，当录入位位数字或文本，当录入位数错误时数错误时,提示错误原因，样式为提示错误原

9、因，样式为“警告警告”，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）使用时间函数，对）使用时间函数，对Sheet1中用户的年龄进行计算。要求：中用户的年龄进行计算。要求：假设当前时间是假设当前时间是“2008-11-15”，结合用户的出生年月，计算用户的年龄，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在并将其计算结果保存在“年龄年龄”列当中。计算方法为两个时间年份之列当中。计算方法为两个时间年份之差。差。（3）使用）使用REPLACE函数，对函数，对Sheet1中用户的电话号码进行升级。要求：中用户的电话号码进行升级。要求：对对“原电话号码原电话号码

10、”列中的电话号码进行升级。升级方法是在区号（列中的电话号码进行升级。升级方法是在区号（0571）后面加上后面加上“8”，并将其计算结果保存在，并将其计算结果保存在“升级电话号码升级电话号码”列的相应单列的相应单元格中。元格中。例如：电话号码例如：电话号码“”升级后为升级后为“8”。 62 试题讲解试题讲解第 13 页（4）在）在Sheet1中，使用中，使用AND函数，根据函数，根据“性别性别”及及“年龄年龄”列中的数据，判断所列中的数据，判断所有用户是否为大于等于有用户是否为大于等于40岁的男性，并将结果保存在岁的男性，并将结果保存在“是否是否=40男性男性”列中。列中。注意：如果是，保存结果

11、为注意：如果是，保存结果为TRUE；否则，保存结果为；否则，保存结果为FALSE。（5）根据）根据Sheet1中的数据，对以下条件，使用统计函数进行统计。要求：中的数据，对以下条件，使用统计函数进行统计。要求：统计性别为统计性别为“男男”的用户人数，将结果填入的用户人数，将结果填入Sheet2的的B2单元格中；单元格中；统计年龄为统计年龄为“=40”岁的用户人数，并将结果填入岁的用户人数，并将结果填入Sheet2到到B3单元格中。单元格中。（6）将）将Sheet1复制到复制到Sheet3，并对，并对Sheet3进行高级筛选。要求：进行高级筛选。要求：筛选条件为：筛选条件为：“性别性别”-女、女

12、、“所在区域所在区域”-西湖区；西湖区；将筛选结果保存在将筛选结果保存在Sheet3中。中。 注意：注意：1）无需考虑是否删除或移动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制数据表后，粘帖时，数据表必须顶格放置；）复制数据表后，粘帖时，数据表必须顶格放置；（7）根据）根据Sheet1的结果，创建一个数据透视图的结果，创建一个数据透视图Chart1。要求：。要求：显示每个区域所拥有的用户数量；显示每个区域所拥有的用户数量；X坐标设置为坐标设置为“所在区域所在区域”；计数项为计数项为“所在区域所在区域”；将对应的数据透视表保存在将对应的数据透视表保存在Sheet4中。中。62 试题讲解

13、试题讲解第 14 页常见试题常见试题3打开第打开第6章章Excel操作素材库中的操作素材库中的“学生成绩统计表学生成绩统计表.xls”文件，按文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。下面的操作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet1中，使用条件格式为中，使用条件格式为“语文语文”列中的数据大于列中的数据大于80的的单元格中字体颜色设置为红色、加粗显示。单元格中字体颜色设置为红色、加粗显示。（2）使用数组公式，根据）使用数组公式，根据Sheet1中

14、的数据，计算总分和平均分，中的数据，计算总分和平均分，将其计算结果保存到表中的将其计算结果保存到表中的“总分总分”列和列和“平均分平均分”列当中。列当中。（3）使用）使用RANK函数，根据函数，根据Sheet1中的中的“总分总分”列对每个同学排列对每个同学排名情况进行统计，并将排名结果保存到表中的名情况进行统计，并将排名结果保存到表中的“排名排名”列当中。列当中。 62 试题讲解试题讲解第 15 页（4）使用逻辑函数，判断）使用逻辑函数，判断Sheet1中每个同学的每门功课是否均高于全班中每个同学的每门功课是否均高于全班单科平均分。要求：单科平均分。要求：如果是，保存结果为如果是，保存结果为T

15、RUE，否则，保存结果为，否则，保存结果为FALSE；将结果保存在表中的将结果保存在表中的“优等生优等生”列当中。列当中。注意：优等生条件：每门功课均高于全班单科平均分注意：优等生条件：每门功课均高于全班单科平均分（5）根据）根据Sheet1中的结果，使用统计函数，统计中的结果，使用统计函数，统计“数学数学”考试成绩各个考试成绩各个分数段的同学人数，将统计结果保存到分数段的同学人数，将统计结果保存到Sheet2中的相应位置。中的相应位置。（6）将）将Sheet1复制到复制到Sheet3中，并对中，并对Sheet3进行高级筛选。要求：进行高级筛选。要求：筛选条件：筛选条件：“语文语文”=75，“

16、数学数学”=75，“英语英语“=75，“总分总分”=250；将结果保存在将结果保存在Sheet3中。中。注意：无需考虑是否删除筛选条件；复制数据表后，粘贴时，数据表必须注意：无需考虑是否删除筛选条件；复制数据表后，粘贴时，数据表必须顶格放置。顶格放置。（7）根据）根据Sheet1中的结果，在中的结果，在Sheet4中创建一张数据透视表。要求：中创建一张数据透视表。要求：显示是否为优等生的学生人数汇总情况；显示是否为优等生的学生人数汇总情况；行区域设置为：行区域设置为：“优等生优等生”；数据区域设置为数据区域设置为“优等生优等生”；计数项为优等生计数项为优等生62 试题讲解试题讲解第 16 页常

17、见试题常见试题4打开第打开第6章章Excel操作素材库中的操作素材库中的“停车收费表停车收费表.xls”文件，按下面的操作文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet4的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本，当录入位位数字或文本，当录入位数错误时数错误时,提示错误原因，样式为提示错误原因，样式为“警告警告”，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）根

18、据）根据Sheet1中的中的“停车价目表停车价目表”价格，利用价格，利用HLOOKUP函数对函数对“停停车情况记录表车情况记录表”中的中的“单价单价”列根据不同的车型进行自动填充。列根据不同的车型进行自动填充。（3）在）在Sheet1中，利用时间函数计算汽车在停车库中的停放时间，要求：中，利用时间函数计算汽车在停车库中的停放时间，要求：计算方法为：停放时间计算方法为：停放时间=出库时间出库时间入库时间。入库时间。格式为：格式为：“小时：分钟：秒小时：分钟：秒”将结果保存在将结果保存在“停车情况记录表停车情况记录表”中的中的“停放时间停放时间”列中（例如：一小时列中（例如：一小时十五分十二秒在停

19、放时间中的表示为：十五分十二秒在停放时间中的表示为：“1：15：12”）。）。 62 试题讲解试题讲解第 17 页（4）使用函数公式，根据停放时间的长短对）使用函数公式，根据停放时间的长短对“停车情况记录表停车情况记录表”的停车费用进行计的停车费用进行计算，将计算结果填入到算，将计算结果填入到“停车情况记录表停车情况记录表”的的“应付金额应付金额”列中。要求：列中。要求：停车按小时收费，对于不满一个小时的按照一个小时收费；停车按小时收费，对于不满一个小时的按照一个小时收费；对于超过整点小时数十五分钟的多积累一个小时。（例如对于超过整点小时数十五分钟的多积累一个小时。（例如1小时小时23分，将以

20、分，将以2小时计小时计费）费）使用统计函数，对使用统计函数，对Sheet1中的中的“停车情况记录表停车情况记录表”根据下列条件进行统计，要求：根据下列条件进行统计，要求：统计停车费用大于等于统计停车费用大于等于40元的停车记录条数，并将结果保存在元的停车记录条数，并将结果保存在J8单元格中。单元格中。统计最高的停车费用，并将结果保存在统计最高的停车费用，并将结果保存在J9单元格中。单元格中。（6）将）将Sheet1中的中的“停车情况记录表停车情况记录表”复制到复制到Sheet2中，对中，对Sheet2进行高级筛进行高级筛选，要求：选，要求：筛选条件为：筛选条件为：“车型车型”小汽车，小汽车，“

21、应付金额应付金额”=30；将结果保存在将结果保存在Sheet2中。中。注意：注意：1）无需考虑是否删除或移动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制过程中，将标题项）复制过程中，将标题项“停车情况记录表停车情况记录表”连同数据一同复制。连同数据一同复制。3）粘帖时，数据表必须顶格放置；）粘帖时，数据表必须顶格放置；（7）根据）根据Sheet1中的中的“停车情况记录表停车情况记录表”，创建一个数据透视图，创建一个数据透视图Chart1，要求：，要求：显示各种车型所收费用的汇总；显示各种车型所收费用的汇总；行区域设置为行区域设置为“车型车型”；求和项为求和项为“应付金额应付金额”；将

22、对应的数据透视表保存在将对应的数据透视表保存在Sheet3中。中。 62 试题讲解试题讲解第 18 页常见试题常见试题5打开第打开第6章章Excel操作素材库中的操作素材库中的“员工资料表员工资料表.xls”文件，按下面的操作文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet5的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本，当录入位位数字或文本，当录入位数错误时数错误时,提示错误原因，样式为提示错误

23、原因，样式为“警告警告”，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）仅使用）仅使用MID函数和函数和CONCATENATE函数，对函数，对Sheet1中的中的“员工资员工资料表料表”的的“出生日期出生日期”列进行填充，要求：列进行填充，要求：填充的内容根据填充的内容根据“身份证号码身份证号码”列的内容来确定：列的内容来确定：身份证号码中的第身份证号码中的第7位位第第10位：表示出生年份；位：表示出生年份；身份证号码中的第身份证号码中的第11位位第第12位：表示出生月份；位：表示出生月份；身份证号码中的第身份证号码中的第13位位第第14位：表示出生日；位：表示出

24、生日；填充结果的格式为：填充结果的格式为：XXXX年年XX月月XX日日（3）根据）根据Sheet1中中“职务补贴率表职务补贴率表”的数据，使用的数据，使用VLOOKUP函数，对函数，对“员工资料表员工资料表”中的中的“职务补贴率职务补贴率”列进行自动填充。列进行自动填充。62 试题讲解试题讲解第 19 页（4）使用数组公式，在）使用数组公式，在sheet1 中对中对“员工资料表员工资料表”的的“工资总额工资总额”列进行计算，列进行计算，并将结果保存在并将结果保存在“工资总额工资总额”列中。列中。计算方法：工资总额计算方法：工资总额=基本工资基本工资*（1+职务补贴）职务补贴）（5）在）在She

25、et2中，根据中，根据“固定资产情况表固定资产情况表”，使用财务函数，对以下条件进行计，使用财务函数，对以下条件进行计算。算。计算计算“每天折旧值每天折旧值”，并将结果填入到，并将结果填入到E2单元格中；单元格中；计算计算“每月折旧值每月折旧值”，并将结果填入到，并将结果填入到E3单元格中；单元格中；计算计算“每年折旧值每年折旧值”，并将结果填入到，并将结果填入到E4单元格中；单元格中；（6）将）将Sheet1的的“员工资料表员工资料表”复制到复制到Sheet3，并对，并对Sheet3进行高级筛选。要进行高级筛选。要求：求：筛选条件为：筛选条件为：“性别性别”-女、女、“职务职务”=高级工程师

26、；高级工程师；将筛选结果保存在将筛选结果保存在Sheet3中。中。 注意：注意：1）无需考虑是否删除或移动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制过程中，将标题项）复制过程中，将标题项“房产销售表房产销售表”连同数据一同复制；连同数据一同复制； 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据Sheet1中的中的“员工资料表员工资料表”，在，在Sheet4中新建一张数据透视表。要求：中新建一张数据透视表。要求：显示每种性别的不同职务的人数汇总情况；显示每种性别的不同职务的人数汇总情况；行区域设置为行区域设置为“性别性别”；列区域设置为列区域设置为“职务职务”；数据区

27、域设置为数据区域设置为“职务职务”；计数项为职务。计数项为职务。62 试题讲解试题讲解第 20 页常见试题常见试题6打开第打开第6章章Excel操作素材库中的操作素材库中的“学生成绩表学生成绩表-A.xls”文件，按下面的操作文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet1中，使用条件格式将中，使用条件格式将“铅球成绩（米）铅球成绩（米）”列中单元格数据列中单元格数据按下列要求显示。按下列要求显示。数据大于数据大于

28、9的单元格中字体颜色设置为红色、加粗显示；的单元格中字体颜色设置为红色、加粗显示；数据介于数据介于7和和9之间的单元格中字体颜色设置为蓝色、加粗显示；之间的单元格中字体颜色设置为蓝色、加粗显示；数据小于数据小于7的单元格中字体颜色设置为绿色、加粗显示的单元格中字体颜色设置为绿色、加粗显示（2）在）在Sheet1“学生成绩表学生成绩表”中，使用中，使用REPLACE函数和数组公式，将原学函数和数组公式，将原学号转变成新学号，同时将所得的新学号填入号转变成新学号，同时将所得的新学号填入“新学号新学号”列中。列中。转变方法：将原学号的第四位后面加上转变方法：将原学号的第四位后面加上“5”；例：；例：

29、“2007032001”-“20075032001”。62 试题讲解试题讲解第 21 页（3）使用）使用IF函数和逻辑函数，对函数和逻辑函数，对Sheet1“学生成绩表学生成绩表”中的中的“结果结果1”和和“结果结果2”列进行填充。填充的内容根据以下条件确定。（要求：将男生、列进行填充。填充的内容根据以下条件确定。（要求：将男生、女生分开写入女生分开写入IF函数中）函数中）结果结果1如果是男生：成绩如果是男生：成绩=14.00，填充为填充为“不合格不合格”；如果是女生：成绩如果是女生：成绩=16.00，填充为填充为“不合格不合格”； 结果结果2 如果是男生：成绩如果是男生：成绩7.50，填充为

30、，填充为“合格合格”；成绩；成绩5.50，填充为，填充为“合格合格”；成绩；成绩=5.50，填充为，填充为“不合格不合格”；（4）在）在Sheet1“学生成绩表学生成绩表”中的数据，根据以下条件，使用统计函数进中的数据，根据以下条件，使用统计函数进行统计。要求：行统计。要求：获取获取“100米跑的最快的学生成绩米跑的最快的学生成绩”，并将结果填入到在，并将结果填入到在Sheet1的的K4单元格中；单元格中；统计统计“所有学生结果所有学生结果1为合格的总人数为合格的总人数”，并将结果填入在，并将结果填入在Sheet1的的K5单单元格中。元格中。62 试题讲解试题讲解第 22 页（5）根据在）根据

31、在Sheet2中的贷款情况，使用财务函数对贷款偿还金额进行计算。要求：中的贷款情况，使用财务函数对贷款偿还金额进行计算。要求：计算计算“按年偿还贷款金额（年末）按年偿还贷款金额（年末）”，并将结果填入到在，并将结果填入到在Sheet2中的中的E2单元格单元格中中计算计算“第第9个月贷款利息金额个月贷款利息金额”，并将结果填入到在，并将结果填入到在Sheet2中的中的E3单元格中。单元格中。（6）将在）将在Sheet1中的中的“学生成绩表学生成绩表”复制到在复制到在Sheet3，对在，对在Sheet3进行高级筛进行高级筛选。要求：选。要求：筛选条件为：筛选条件为：“性别性别”-“男男”，“100

32、米成绩（秒）米成绩（秒）”-“9.00”将筛选结果保存在将筛选结果保存在Sheet3中。中。注意：无需考虑是否删除或移动筛选条件；复制过程中，将标题项注意：无需考虑是否删除或移动筛选条件；复制过程中，将标题项“学生成绩表学生成绩表”连同数据一同复制；数据表必须顶格放置。连同数据一同复制；数据表必须顶格放置。（7）根据）根据Sheet1中的中的“学生成绩表学生成绩表”，在，在Sheet4中创建一张数据透视表。要求：中创建一张数据透视表。要求：显示每种性别学生的合格与不合格总人数；显示每种性别学生的合格与不合格总人数；行区域设置为行区域设置为“性别性别”列区域设置为列区域设置为“结果结果1”；数据

33、区域设置为数据区域设置为“结果结果1”；计数项为计数项为“结果结果1”62 试题讲解试题讲解第 23 页常见试题常见试题7打开第打开第6章章Excel操作素材库中的操作素材库中的“学生总分统计表学生总分统计表.xls”文件，按下面的文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。操作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet5中，使用条件格式将中，使用条件格式将“性别性别”列中的数据为列中的数据为“男男”的单元的单元格中字体颜色设置为红色、加粗显示。格中字体

34、颜色设置为红色、加粗显示。（2）使用数组公式，根据）使用数组公式，根据Sheet1中的中的“学生成绩表学生成绩表”的的“数据，计算考数据，计算考试总分，并将结果填入到试总分，并将结果填入到“总分总分”列中。列中。计算方法：总分计算方法：总分=单选题单选题+判断题判断题+windows操作题操作题+Excel操作题操作题+PowerPoint操作题操作题+IE操作题。操作题。（3）使用文本函数中的一个函数，在）使用文本函数中的一个函数，在Sheet1中，利用中，利用“学号学号”列的数据，列的数据，根据以下要求获得考生所考级别，并将结果填入根据以下要求获得考生所考级别，并将结果填入“级别级别”列中

35、。要求：列中。要求：学号中的第八列指示的考生所考级别，例如：学号中的第八列指示的考生所考级别，例如：“3080”中的中的“2”标识了该标识了该考生所考级别为二级；考生所考级别为二级；在在“级别级别”列中，填入的数据是函数的返回值。列中，填入的数据是函数的返回值。62 试题讲解试题讲解第 24 页（4）使用统计函数，根据以下要求对）使用统计函数，根据以下要求对sheet1 中的中的“学生成绩表学生成绩表”的数据进行统计。的数据进行统计。要求：要求：统计统计“考考1级的考生人数级的考生人数”，并将计算结果填入到，并将计算结果填入到N2单元格中；单元格中；统计统计“考试通过人数（考试通过人数（=60

36、）”，并将计算结果填入到，并将计算结果填入到N3单元格中；单元格中；统计统计“全体全体1级考生的考试平均分级考生的考试平均分”，并将计算结果填入到，并将计算结果填入到N4单元格中；单元格中；注意：计算时，分母直接使用注意：计算时，分母直接使用“N2”单元格的数据。单元格的数据。（5）使用财务函数，根据以下要求对）使用财务函数，根据以下要求对Sheet2中的数据进行计算。要求：中的数据进行计算。要求：根据根据“投资情况表投资情况表1”中的数据，计算中的数据，计算10年以后得到的金额，并将结果填入到年以后得到的金额，并将结果填入到B7单元单元格中；格中；根据根据“投资情况表投资情况表2”中的数据，

37、计算预计投资金额，并将结果填入到中的数据，计算预计投资金额，并将结果填入到E7单元格中。单元格中。（6）将）将Sheet1的的“学生成绩表学生成绩表”复制到复制到Sheet3，并对，并对Sheet3进行高级筛选。要进行高级筛选。要求：求：筛选条件为：筛选条件为：“级别级别”-2、“总分总分”-=70；将筛选结果保存在将筛选结果保存在Sheet3中。中。 注意：注意：1）无需考虑是否删除或移动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制过程中，将标题项）复制过程中，将标题项“学生成绩表学生成绩表”连同数据一同复制；连同数据一同复制； 3）数据表必须顶格放置；）数据表必须顶格放置；（7）

38、根据）根据Sheet1的的“学生成绩表学生成绩表”，在，在Sheet4中创建一个数据透视表要求：中创建一个数据透视表要求：显示每个级别不同总分的人数汇总情况；显示每个级别不同总分的人数汇总情况；行区域设置为行区域设置为“级别级别”；列区域设置为列区域设置为“总分总分”；数据区域设置为数据区域设置为“总分总分”；计数项为总分。计数项为总分。62 试题讲解试题讲解第 25 页常见试题常见试题8打开第打开第6章章Excel操作素材库中的操作素材库中的“公司员工人事信息表公司员工人事信息表.xls”文件，按下文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。面的操作要求进行操作，并把操作

39、结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet4的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本。当录入位位数字或文本。当录入位数错误时，提示错误原因，样式为数错误时，提示错误原因，样式为“警告警告”，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）使用大小写转换函数，根据）使用大小写转换函数，根据Sheet1中的中的“公司员工人事信息表公司员工人事信息表”的的“编号编号”列，对列，对“新编号新编号”列进行填充。列进行填充。要求：把编号中的小写字母改为大

40、写字母，并将结果保存在要求：把编号中的小写字母改为大写字母，并将结果保存在“新编号新编号”列列中。中。例如：例如：“a001”更改后为更改后为“A001”。（3）使用文本函数和时间函数，根据）使用文本函数和时间函数，根据Sheet1中的中的“公司员工人事信息表公司员工人事信息表”的的“身份证号码身份证号码”列，计算用户的年龄，并保存在列，计算用户的年龄，并保存在“年龄年龄”列中。列中。注意：注意：身份证的第身份证的第7位位第第10位表示出生年份；位表示出生年份；计算方法：年龄计算方法：年龄=当前年份当前年份-出生年份。其中出生年份。其中“当前年份当前年份”使用时间函数计使用时间函数计算。算。

41、62 试题讲解试题讲解第 26 页（4）在）在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况计算，并中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入到相应的单元格当中。将结果填入到相应的单元格当中。计算：获取具有硕士学历，职务为经理助理的员工姓名，并将结果保存在计算：获取具有硕士学历，职务为经理助理的员工姓名，并将结果保存在Sheet1中中的的E31单元格中。单元格中。（5）使用函数，判断）使用函数，判断Sheet1中中L12和和M12单元格中的文本字符串是否完全相同。注单元格中的文本字符串是否完全相同。注意：意：如果完全相同，结果保存为如果完全相同，结果保

42、存为TRUE，否则保存为，否则保存为FALSE。将结果保存在将结果保存在Sheet1中的中的N11单元格中。单元格中。（6）将）将Sheet1中的中的“公司员工人事信息表公司员工人事信息表”复制到复制到Sheet2中，并对中，并对Sheet2进行进行自动筛选。要求：自动筛选。要求：筛选条件为：筛选条件为：“籍贯籍贯”-广东、广东、“学历学历”-硕士、硕士、“职务职务”-职员；职员；将筛选条件保存在将筛选条件保存在Sheet2中。中。注意：注意：1）无需考虑是否删除或移动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制过程中，将标题项）复制过程中，将标题项“公司员工人事信息表公司员工人事信

43、息表”连同数据一同复制；连同数据一同复制； 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据Sheet1中的中的“公司员工人事信息表公司员工人事信息表”，在，在Sheet3中新建一张数据透视表。中新建一张数据透视表。要求：要求：显示每个职位的不同学历的人数情况；显示每个职位的不同学历的人数情况；行区域设置为行区域设置为“职务职务”；列区域设置为列区域设置为“学历学历”；数据区域设置为数据区域设置为“学历学历”；计数项为学历。计数项为学历。62 试题讲解试题讲解第 27 页常见试题常见试题9打开第打开第6章章Excel操作素材库中的操作素材库中的“采购情况表采购情况表.xls”文件

44、，按下面文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。的操作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet1中，使用条件格式将中，使用条件格式将“瓦数瓦数”列中数据小于列中数据小于100的的单元格中，字体颜色设置为红色、加粗显示。单元格中，字体颜色设置为红色、加粗显示。（2）使用数组公式，计算）使用数组公式，计算Sheet1中中“采购情况表采购情况表”的每种产品的的每种产品的采购总额，将结果保存到表中的采购总额，将结果保存到表中的“采购总额采购总额”列中。计算

45、方法：列中。计算方法：“单价单价*每盒数量每盒数量*采购盒数采购盒数”。（3）根据）根据Sheet1中的中的“采购情况表采购情况表”，使用数据库函数及已设置，使用数据库函数及已设置的条件区域，计算以下情况的结果。的条件区域，计算以下情况的结果。计算：商标为上海，瓦数小于计算：商标为上海，瓦数小于100的白炽灯的平均单价，并将结果的白炽灯的平均单价，并将结果填入填入sheet1的的G25单元格中，保留单元格中，保留2位小数；位小数；计算：产品为白炽灯，其瓦数大于等于计算：产品为白炽灯，其瓦数大于等于80且小于等于且小于等于100的品种数，的品种数，并将结果填入并将结果填入sheet1的的g26单

46、元格中。单元格中。 62 试题讲解试题讲解第 28 页（4）某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。）某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答对于调查对象，只能回答y（吸烟）或者（吸烟）或者n（不吸烟）。根据调查情况，制作出（不吸烟）。根据调查情况，制作出sheet2中的中的“吸烟情况调查表吸烟情况调查表”。使用函数，统计符合以下条件的数值。使用函数，统计符合以下条件的数值。统计未登记的部门个数，将结果保存在统计未登记的部门个数，将结果保存在b14单元格中；单元格中；统计在登记的部门中吸烟的部门个数，将结果保存在

47、统计在登记的部门中吸烟的部门个数，将结果保存在b15单元格中。单元格中。（5）使用函数，对）使用函数，对sheet2中的中的b21单元格中的内容进行判断，判断其是否为文本，单元格中的内容进行判断，判断其是否为文本，如果是，单元格填充为如果是，单元格填充为“True”；如果不是，单元格填充为；如果不是，单元格填充为“False”，并将结，并将结果保存在果保存在sheet2中的中的b22单元格当中。单元格当中。（6）将）将sheet1中的中的“采购情况表采购情况表”复制到复制到sheet3中，对中，对sheet3进行高级筛选，要进行高级筛选，要求：求：筛选条件：筛选条件：“产品为白炽灯，商标为上海

48、产品为白炽灯，商标为上海”；将结果保存在将结果保存在sheet3中。中。注意：注意：1）无须考虑是否删除或移动筛选条件。）无须考虑是否删除或移动筛选条件。2）在复制过程中，将标题项）在复制过程中，将标题项“采购情况表采购情况表”连同数据一同复制；连同数据一同复制； 2）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据sheet1中的中的“采购情况表采购情况表”，在，在sheet4中创建一张数据透视表，要求：中创建一张数据透视表，要求：显示不同商标的不同产品的采购数量；显示不同商标的不同产品的采购数量；行区域设置为行区域设置为“产品产品”；列区域设置为列区域设置为“商标商标”；数据区域

49、为数据区域为“采购盒数采购盒数”；求和项为求和项为“采购盒数采购盒数”。62 试题讲解试题讲解第 29 页常见试题常见试题10打开第打开第6章章Excel操作素材库中的操作素材库中的“教材订购教材订购.xls”文件，按下面的操作要文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet5的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本。当录入位位数字或文本。当录入位数错误时，提示错误原因，样式为数错误时，提示

50、错误原因，样式为“警告警告”，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）使用数组公式，计算）使用数组公式，计算sheet1中中“教材订购情况表教材订购情况表”的订购金额，将的订购金额，将结果保存到该表的结果保存到该表的“金额金额”列当中。计算方法：金额订数列当中。计算方法：金额订数*单价。单价。（3）使用统计函数，对）使用统计函数，对sheeet1中中“教材订购情况表教材订购情况表”的结果按以下条件的结果按以下条件进行统计，并将结果保存在进行统计，并将结果保存在sheet1中的相应位置，要求：中的相应位置，要求：统计出版社名称为统计出版社名称为“高等教育出版社

51、高等教育出版社”的书的种类数，并将结果保存在的书的种类数，并将结果保存在sheet1中中l2单元格中；单元格中；统计订购数量大于统计订购数量大于110且小于且小于850的书的种类数，并将结果保存在的书的种类数，并将结果保存在sheet1中中l3单元格中。单元格中。 62 试题讲解试题讲解第 30 页（4）使用函数，计算每个用户所订购图书所需支付的金额总数，将结果保）使用函数，计算每个用户所订购图书所需支付的金额总数，将结果保存在存在sheet1中中“用户支付情况用户支付情况”的的“支付总额支付总额”列中。列中。（5）使用函数，判断）使用函数，判断sheet2中的年份是否为闰年，如果是，结果保存

52、中的年份是否为闰年，如果是，结果保存“闰年闰年”，如果不是，则结果保存为，如果不是，则结果保存为“平年平年”，并将结果保存在，并将结果保存在“是否是否为闰年为闰年”列中。列中。说明：闰年定义：年数能被说明：闰年定义：年数能被4整除而不能被整除而不能被100整除，或者能被整除，或者能被400整除的整除的年份。年份。（6）将）将sheet1中的中的“教材订购情况表教材订购情况表”复制到复制到sheet3中，对中，对sheet3进行进行高级筛选，要求：高级筛选，要求：筛选条件为筛选条件为“订数订数=500，且金额总数，且金额总数=20，“上海平均气温上海平均气温”1000000；将筛选结果保存在将筛

53、选结果保存在Sheet3中。中。 注意：注意：1）无需考虑是否删除或移动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制过程中，将标题项）复制过程中，将标题项“房产销售表房产销售表”连同数据一同复制；连同数据一同复制； 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据Sheet1的的“房产销售表房产销售表”的结果，创建一个数据透视图的结果，创建一个数据透视图Chart1。要求：要求：显示每个销售人员所销售房屋应缴纳契税总额汇总情况；显示每个销售人员所销售房屋应缴纳契税总额汇总情况；X坐标设置为坐标设置为“销售人员销售人员”；数据区域设置为数据区域设置为“契税总额契税总额”；

54、求和项设置为契税总额；求和项设置为契税总额；将对应的数据透视表保存在将对应的数据透视表保存在sheet4中。中。 62 试题讲解试题讲解第 35 页常见试题常见试题13打开第打开第6章章Excel操作素材库中的操作素材库中的“公务员考试成绩表公务员考试成绩表.xls”文件，按下面的操作要文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet1中，使用条件格式将中，使用条件格式将“性别性别”列中为列中为“女女”的单元格中，字体

55、颜色的单元格中，字体颜色设置为红色、加粗显示。设置为红色、加粗显示。（2）使用）使用IF函数，对函数，对Sheet1中的中的“学位学位”列进行自动填充。要求：填充的内容根列进行自动填充。要求：填充的内容根据据“学历学历”列的内容来确定（假定学生均已获得相应学位）：列的内容来确定（假定学生均已获得相应学位）：博士研究生博士研究生博士博士硕士研究生硕士研究生硕士硕士本科本科学士学士其他其他无无（3）使用数组公式，在）使用数组公式，在Sheet1中计算下列项目：中计算下列项目：计算笔试成绩比例分，并将结果保存在计算笔试成绩比例分，并将结果保存在“公务员考试成绩表公务员考试成绩表”中的中的“笔试成绩比

56、例笔试成绩比例分分”中。计算方法：笔试成绩比例分中。计算方法：笔试成绩比例分=（笔试成绩（笔试成绩/3）*60%。计算面试成绩比例分，并将结果保存在计算面试成绩比例分，并将结果保存在“公务员考试成绩表公务员考试成绩表”中的中的“面试成绩比例面试成绩比例分分”中。计算方法：面试成绩比例分中。计算方法：面试成绩比例分=面试成绩面试成绩*40%计算总成绩，并将结果保存在计算总成绩，并将结果保存在“公务员考试成绩表公务员考试成绩表”中的中的“总成绩总成绩”中。计算方法：中。计算方法：总成绩总成绩=笔试成绩比例分笔试成绩比例分+面试成绩比例分。面试成绩比例分。 62 试题讲解试题讲解第 36 页（4）将

57、）将Sheet1中的中的“公务员考试成绩表公务员考试成绩表”复制到复制到Sheet2中，根据以下要求修改中，根据以下要求修改“公务员考试成绩表公务员考试成绩表”中的数组公式，并将结果保存在中的数组公式，并将结果保存在Sheet2中相应列中。要求：中相应列中。要求：修改修改“笔试成绩比例分笔试成绩比例分”的计算，计算方法为：笔试成绩比例分的计算，计算方法为：笔试成绩比例分=（笔试成绩（笔试成绩/2）*60%，并将结果保存在，并将结果保存在“笔试成绩比例分笔试成绩比例分”列中。列中。注意：注意：1）复制过程中，将标题项）复制过程中，将标题项“公务员考试成绩表公务员考试成绩表”连同数据一同复制。连同

58、数据一同复制。2）数据表必须顶格放置。）数据表必须顶格放置。（5）在）在Sheet2中，使用中，使用RANK函数，根据函数，根据“总成绩总成绩”列对所有考生排名。要求：将列对所有考生排名。要求：将排名结果保存在排名结果保存在“排名排名”列中。列中。（6）将）将Sheet2中的中的“公务员考试成绩表公务员考试成绩表”复制到复制到Sheet3，并对，并对Sheet3进行高级进行高级筛选。要求：筛选。要求：筛选条件为：筛选条件为：“报考单位报考单位”中院、中院、“性别性别”男、男、“学历学历”硕士研究生硕士研究生将筛选结果保存在将筛选结果保存在Sheet3中。中。注意：注意：1）无需考虑是否删除或移

59、动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制过程中，将标题项）复制过程中，将标题项“公务员考试成绩表公务员考试成绩表”连同数据一同复制；连同数据一同复制； 3）复制数据表后，粘贴时数据表必须顶格放置；）复制数据表后，粘贴时数据表必须顶格放置；（7）根据）根据Sheet2中的中的“公务员考试成绩表公务员考试成绩表”，在，在Sheet4中创建一张数据透视表。中创建一张数据透视表。要求：要求：显示每个报考单位的人的不同学历的人数汇总情况。显示每个报考单位的人的不同学历的人数汇总情况。行区域设置为行区域设置为“报考单位报考单位”列区域设置为列区域设置为“学历学历”数据区域设置为数据区域设置

60、为“学历学历”计数项为学历。计数项为学历。62 试题讲解试题讲解第 37 页常见试题常见试题14打开第打开第6章章Excel操作素材库中的操作素材库中的“3月份销售统计表月份销售统计表.xls”文件，按下面的文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。操作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet4的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本，当录入位位数字或文本，当录入位数错误时数错误时,提示错误原因，样式为提示错误原因，样式为“

61、警告警告”，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）使用）使用VLOOKUP函数，对函数，对SHEET1中的中的“3月份销售统计表月份销售统计表”的的“产品产品名称名称”列和列和“产品单价产品单价”列进行填充。要求：列进行填充。要求：根据根据“企业销售产品清单企业销售产品清单”，使用，使用VLOOKUP函数，将产品名称和产品单价函数，将产品名称和产品单价填充到填充到“3月份销售统计表月份销售统计表”的的“产品名称产品名称”列和列和“产品单价产品单价”列中。列中。（3）使用数组公式，计算）使用数组公式，计算SHEET1中的中的“3月份销售统计表月份销售统计表”

62、中的销售金中的销售金额，并将结果填入到该表的额，并将结果填入到该表的“销售金额销售金额”列中。列中。计算方法：销售金额计算方法：销售金额=产品单价产品单价*销售数量。销售数量。 62 试题讲解试题讲解第 38 页（4）使用统计函数，根据）使用统计函数，根据“3月份销售统计表月份销售统计表”中的数据，计算中的数据，计算“分部销分部销售业绩统计表售业绩统计表”中的总销售额，并将结果填入该表的中的总销售额，并将结果填入该表的“总销售额总销售额”列。列。（5）在）在SHEET1中，使用中，使用RANK函数，在函数，在“分部销售业绩统计表分部销售业绩统计表”表中，表中，根据根据“总销售额总销售额”对各部

63、门进行排名，并将结果填入到对各部门进行排名，并将结果填入到“销售排名销售排名”列中。列中。（6）将）将SHEET1中的中的“3月份销售统计表月份销售统计表”复制到复制到SHEET2中，对中，对SHEET2进行高级筛选。要求：进行高级筛选。要求：筛选条件为：筛选条件为：“销售数量销售数量”-3、“所属部门所属部门”-市场市场1部、部、“销售金额销售金额”- 1000.将筛选结果保存在将筛选结果保存在SHEET2中。中。注意：注意：1）无需考虑是否删除或移动筛选条件；）无需考虑是否删除或移动筛选条件； 2）复制过程中，将标题项）复制过程中，将标题项“3月份销售统计表月份销售统计表”连同数据一同复制

64、；连同数据一同复制； 3）复制数据表后，粘贴时数据表必须顶格放置；）复制数据表后，粘贴时数据表必须顶格放置；（7）根据）根据SHEET1的的“3月份销售统计表月份销售统计表”中的数据，新建一个数据透视中的数据，新建一个数据透视图图Chart1。要求：。要求：该图形显示每位经办人的总销售额情况。该图形显示每位经办人的总销售额情况。X坐标设置为坐标设置为“经办人经办人”。数据区域设置为数据区域设置为“销售金额销售金额”。求和项为求和项为“销售金额销售金额”。将对应的数据透视表保存在将对应的数据透视表保存在SHEET3中。中。 62 试题讲解试题讲解第 39 页常见试题常见试题15打开第打开第6章章

65、Excel操作素材库中的操作素材库中的“员工信息表员工信息表.xls”文件，按下面的操作文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet4的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本，当录入位位数字或文本，当录入位数错误时数错误时,提示错误原因，样式为提示错误原因，样式为“警告警告”，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）使用）使用REPLACE函数，对函数

66、，对SHEET1中中“员工信息表员工信息表”的员工代码进行的员工代码进行升级。要求：升级。要求：升级方法：在升级方法：在PA后面加上后面加上0。将升级后的员工代码结果填入表中的将升级后的员工代码结果填入表中的“升级员工代码升级员工代码”列中。例如：列中。例如：PA125，修改后为，修改后为PA0125。（3）使用时间函数，计算）使用时间函数，计算SHEET1中中“员工信息表员工信息表”的的“年龄年龄”列和列和“工工龄龄”列。要求：列。要求：假设当前时间是假设当前时间是“2009-11-15”，结合表中的，结合表中的“出生年月出生年月”、“参加工作参加工作时间时间”列，对员工列，对员工“年龄年龄

67、”和和“工龄工龄”进行计算。进行计算。计算方法为两年份之差，并将结果保存到表中的计算方法为两年份之差，并将结果保存到表中的“年龄年龄”列和列和“工龄工龄”列列中。中。 62 试题讲解试题讲解第 40 页（4）使用统计函数，根据）使用统计函数，根据SHEET1中中“员工信息表员工信息表”的数据，对以下条件进行统计。的数据，对以下条件进行统计。要求：要求：统计男性员工的人数，结果填入统计男性员工的人数，结果填入N3单元格中。单元格中。统计高级工程师人数，结果填入统计高级工程师人数，结果填入N4单元格中。单元格中。统计工龄大于等于统计工龄大于等于10的人数，结果填入的人数，结果填入N5单元格中。单元

68、格中。（5）使用逻辑函数，判断员工是否有资格评）使用逻辑函数，判断员工是否有资格评“高级工程师高级工程师”。要求：。要求：评选条件为：工龄大于评选条件为：工龄大于20，且为工程师的员工。，且为工程师的员工。将结果保存在将结果保存在“是否有资格评选高级工程师是否有资格评选高级工程师”列中。列中。如果有资格，保存结果为如果有资格，保存结果为TRUE；否则为；否则为FALSE。（6）将）将SHEET1的的“员工信息表员工信息表”复制到复制到SHEET2中，并对中，并对SHEET2进行高级筛选。进行高级筛选。要求：要求：筛选条件为：筛选条件为：“性别性别”=男，男，“年龄年龄”30，“工龄工龄”=10

69、，“职称职称”=助工。助工。将结果保存在将结果保存在SHEET2中。中。注意：注意：1）无须考虑是否删除或移动筛选条件。）无须考虑是否删除或移动筛选条件。 2）在复制过程中，将标题项）在复制过程中，将标题项“员工信息表员工信息表”连同数据一同复制；连同数据一同复制； 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据SHEET1中的数据，创建一个数据透视图中的数据，创建一个数据透视图Chart1。要求：。要求：显示工厂中各种职称人数的汇总情况。显示工厂中各种职称人数的汇总情况。X坐标设置为坐标设置为“职称职称”。数据区域为数据区域为“职称职称”。将对应的数据透视表保存在将对应的数据

70、透视表保存在SHEET3中。中。62 试题讲解试题讲解第 41 页常见试题常见试题16打开第打开第6章章Excel操作素材库中的操作素材库中的“零件检测结果表零件检测结果表.xls”文件，按下面的文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。操作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet4的的A1单元格中设置为只能录入单元格中设置为只能录入5位数字或文本。当录入位位数字或文本。当录入位数错误时，提示错误原因，样式为数错误时，提示错误原因，样式为“警告警告”

71、，错误信息为，错误信息为“只能录入只能录入5位数字或文本位数字或文本”。（2）使用数组公式，根据）使用数组公式，根据Sheet1中中“零件检测结果表零件检测结果表”的的“外轮直径外轮直径”和和“内轮直径内轮直径”列，计算内外轮差，并将结果表保存在列，计算内外轮差，并将结果表保存在“轮差轮差”列中。列中。计算方法为：轮差计算方法为：轮差=外轮直径外轮直径内轮直径内轮直径（3）使用）使用IF函数，对函数，对Sheet1中中“零件检测结果表零件检测结果表”的的“检测结果检测结果”进行进行填充。要求：填充。要求：如果如果“轮差轮差”“2009001”。（3）使用数组公式，对）使用数组公式，对Sheet

72、1中中“学生成绩表学生成绩表”的的“总分总分”列进行计算。列进行计算。计算方法：总分计算方法：总分=语文语文+数学数学+英语英语+信息技术信息技术+体育。体育。 62 试题讲解试题讲解第 46 页（4）使用）使用IF函数，根据以下条件，对函数，根据以下条件，对SHEET1中中“学生成绩表学生成绩表”的的“考评考评”列进行列进行计算。要求：计算。要求：如果总分如果总分=350，填充为，填充为“合格合格”；否则，填充为；否则，填充为“不合格不合格”。（5）在）在SHEET1中，利用数据库函数及已设置的条件区域，根据以下情况计算，并中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入

73、到相应的单元格当中。要求将结果填入到相应的单元格当中。要求“计算：计算：“语文语文”和和“数学数学”成绩都大于或等于成绩都大于或等于85的学生人数。的学生人数。计算：计算：“体育体育”成绩大于或等于成绩大于或等于90的的“女生女生”姓名。姓名。计算：计算：“体育体育”成绩中成绩中“男生男生”的平均分。的平均分。计算：计算：“体育体育”成绩中成绩中“男生男生”的最高分。的最高分。（6）把）把Sheet1中的中的“学生成绩表学生成绩表”复制到复制到heet2中，并对中，并对SHEET2进行高级筛选。进行高级筛选。要求：要求：筛选条件为：筛选条件为：“性别性别”-男、男、“英语英语”为为90；“信息

74、技术信息技术”为为95。将筛选结果保存在将筛选结果保存在Sheet2中。中。注意：注意：1）无须考虑是否删除或移动筛选条件。）无须考虑是否删除或移动筛选条件。 2）在复制过程中，将标题项）在复制过程中，将标题项“学生成绩表学生成绩表”连同数据一同复制。连同数据一同复制。 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据Sheet1中的中的“学生成绩表学生成绩表”，在，在SHEET3中新建一张数据透视表。要求：中新建一张数据透视表。要求：显示不同性别、不同考评结果的学生人数情况。显示不同性别、不同考评结果的学生人数情况。行区域设置为行区域设置为“性别性别”。列区域设置为列区域设置为

75、“考评考评”。数据区域设置为数据区域设置为“考评考评” 计数项为计数项为“考评考评”。 62 试题讲解试题讲解第 47 页常见试题常见试题19打开第打开第6章章Excel操作素材库中的操作素材库中的“图书订购信息表图书订购信息表.xls”文件，按下面的文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。操作要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet4中，使用条件格式将中，使用条件格式将“性别性别”列中的数据为列中的数据为“女女”的单元的单元格中字体颜色设置为红色

76、、加粗显示。格中字体颜色设置为红色、加粗显示。（2）使用）使用IF函数，根据函数，根据Sheet1中的中的“图书订购信息表图书订购信息表”中的中的“学号学号”列列对对“所属学院所属学院”列进行填充。要求：根据每位学生学号的第七位填充列进行填充。要求：根据每位学生学号的第七位填充对应的对应的“所属学院所属学院”。学号第七位为学号第七位为1计算机学院计算机学院学号第七位为学号第七位为0-电子信息学院电子信息学院（3）使用）使用COUNTBLANK函数，对函数，对Sheet1中的中的“图书订购信息表图书订购信息表”中的中的“订书种类数订书种类数”列进行填充。注意：列进行填充。注意：其中其中“1”表示

77、该同学订购该图书，空格表示没有订购；表示该同学订购该图书，空格表示没有订购；将结果保存在将结果保存在Sheet1中的中的“图书订购信息表图书订购信息表”中的中的“订书种类数订书种类数”列。列。 62 试题讲解试题讲解第 48 页（4）使用公式，对）使用公式，对Sheet1中的中的“图书订购信息表图书订购信息表”中的中的“订书金额（元）订书金额（元）”列进列进行填充。行填充。计算方法为：应缴总额计算方法为：应缴总额=C语言语言*单价单价+高等数学高等数学*单价单价+大学语文大学语文*单价单价+大学英语大学英语*单单价价（5）使用统计函数，根据）使用统计函数，根据Sheet1中的中的“图书订购信息

78、表图书订购信息表”的数据，统计订书金额的数据，统计订书金额大于大于100元的学生人数，将结果保存在元的学生人数，将结果保存在Sheet1的的M9单元格中。单元格中。（6）将）将Sheet1的的“图书订购信息表图书订购信息表”复制到复制到Sheet2，并对，并对Sheet2进行高级筛选。进行高级筛选。要求：要求：筛选条件为：筛选条件为：“订书种类数：订书种类数：-=3、“所属学院所属学院”-计算机学院；计算机学院；将筛选结果保存在将筛选结果保存在Sheet2中。中。 注意：注意：1）复制过程中，将标题项）复制过程中，将标题项“图书订购信息表图书订购信息表”连同数据一同复制；连同数据一同复制； 2

79、）复制过程中，保持数据一致；）复制过程中，保持数据一致； 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据Sheet1的的“图书订购信息表图书订购信息表”，创建一个数据透视图，创建一个数据透视图Chart1。要求：。要求：显示每个学院图书订购的订书金额汇总情况；显示每个学院图书订购的订书金额汇总情况；X坐标设置为坐标设置为“所属学院所属学院”；数据区域设置为数据区域设置为“订书金额（元）订书金额（元）”；求和项为订书金额（元）；求和项为订书金额（元）；将对应的数据透视表保存在将对应的数据透视表保存在Sheet3中。中。 62 试题讲解试题讲解第 49 页常见试题常见试题20打开第

80、打开第6章章Excel操作素材库中的操作素材库中的“服装采购表服装采购表.xls”文件，按下面的操作文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。要求进行操作，并把操作结果保存在相应文件夹中。注意：在做题时，不能修改原有的数据。注意：在做题时，不能修改原有的数据。一、操作要求一、操作要求（1）在）在Sheet1中，使用条件格式将中，使用条件格式将“采购数量采购数量”列中的数量大于列中的数量大于100的的单元格中，字体颜色设置为红色、加粗显示。单元格中，字体颜色设置为红色、加粗显示。（2）使用）使用VLOOKUP函数，对函数，对Sheet1中中“采购表采购表”的的“单价单价”列

81、进行填充。列进行填充。要求：要求：根据根据“价格表价格表”中的商品单价，利用中的商品单价，利用VLOOKUP函数，将其单价填充到采购函数，将其单价填充到采购表中的表中的“单价单价”列中。列中。注意：函数中参数如果需要用到绝对地址的，使用绝对地址进行答题，其注意：函数中参数如果需要用到绝对地址的，使用绝对地址进行答题，其他方式无效。他方式无效。（3）使用）使用IF函数，对函数，对Sheet1“采购表采购表”中的中的“折扣折扣”列进行填充。要求：列进行填充。要求：根据根据“折扣表折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的

82、到采购表中的“折扣折扣”列中。列中。 62 试题讲解试题讲解第 50 页（4）利用公式，对）利用公式，对Sheet1中中“采购表采购表”的的“合计合计”列进行计算。要求：列进行计算。要求：根据根据“采购数量采购数量”、“单价单价”和和“折扣折扣”，计算采购的合计金额，将结果保存在，计算采购的合计金额，将结果保存在“合计合计”列中。列中。计算公式：单价计算公式：单价*采购数量采购数量*（1-折扣率）折扣率）（5）使用）使用SUMIF函数，计算各种商品的采购总量和采购总金额，将结果保存在函数，计算各种商品的采购总量和采购总金额，将结果保存在Sheet1 “统计表统计表”的相应位置。的相应位置。（6

83、）将）将SHEET1中的中的“采购表采购表”复制到复制到SHEET2中，并对中，并对Sheet2进行高级筛选。进行高级筛选。要求：要求：筛选条件为：筛选条件为：“采购数量采购数量”150，“折扣率折扣率”0；将筛选结果保存在将筛选结果保存在Sheet2中。中。注意：注意：1）无须考虑是否删除或移动筛选条件。）无须考虑是否删除或移动筛选条件。 2）在复制过程中，将标题项）在复制过程中，将标题项“采购表采购表”连同数据一同复制。连同数据一同复制。 3）数据表必须顶格放置；）数据表必须顶格放置；（7）根据）根据Sheet1中的中的“采购表采购表”，新建一个数据透视图，新建一个数据透视图Chart1，要求：，要求：该图形显示每个采购时间点所采购的所有项目数量汇总情况；该图形显示每个采购时间点所采购的所有项目数量汇总情况；X坐标设置为坐标设置为“采购时间采购时间”；求和项为求和项为“采购数量采购数量”；将对应的数据透视表保存在将对应的数据透视表保存在Sheet3中。中。 62 试题讲解试题讲解第 51 页