Excel高级应用操作.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第,#,页,第6章 Excel2003高级应用操作,本章的重点主要包括：条件格式；数据有效性设置；相对地址与绝对地址；公式与数组；单元格数字格式；数据填充；函数及函数的嵌套；高级筛选；数据透视表和数据透视图。,6,1,基本操作,（,1,）,SUM,函数,1,、数学与三角函数,（,2,）,SUMIF,函数,（,4,）,INT,函数,（,3,）,ABS,函数,（,5,）,MOD,函数,6,1,基本操作,（,1,）,AVERAGE,函数,2,、统计函数,（,2,）,COUNTIF,函数,（,3,）,MAX,MIN,函数,（,4,）,RANK,函数,6,1,基本操作,（,1,）,HLOOKUP,函数,3,、查找与引用函数,（,2,）,VLOOKUP,函数,6,1,基本操作,（,1,）,AND,函数,4,、逻辑函数,（,2,）,OR,函数,（,4,）,IF,函数,（,3,）,NOT,函数,6,1,基本操作,（,1,）,YEAR,函数,5,、日期和时间函数,（,2,）,MONTH,函数,（,4,）,NOW,函数,（,3,）,DAY,函数,（,5,）,TODAY,函数,（,6,）,HOUR,函数,（,7,）,MINUTE,函数,6,1,基本操作,（,1,）,REPLACE,函数,6,、文本函数,（,2,）,MID,函数,（,4,）,EXACT,函数,（,3,）,CONCATENATE,函数,（,5,）,PROPER,函数,（,6,）,UPPER,函数,（,7,）,TEXT,函数,6,1,基本操作,7,、信息函数,8,、数据库函数,9,、财务函数,10,、数组公式,常见试题,1,打开第,6,章,Excel,操作素材库中的,“,通讯费年度计划表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet1,中，使用条件格式将,“,岗位类别,”,列中单元格数据按下列要求显示。,数据为,“,副经理,”,的单元格中字体颜色设置为红色，加粗显示；,数据为,“,服务部,”,的单元格中字体颜色设置为蓝色、加粗显示。,（,2,）使用,VLOOKUP,函数，根据,Sheet1,中的,“,岗位最高限额明细表,”,，填充,“,通讯费年度计划表,”,中的,“,岗位标准,”,列。,（,3,）使用,INT,函数，计算,Sheet1,中的,“,通讯费年度计划表,”,的,“,预计报销总时间,”,列，要求：,每月以,30,天计算；,将结果填充在,“,预计报销总时间,”,列中。,（,4,）使用数组公式，计算,Sheet1,中,“,通讯费年度计划表,”,的,“,年度费用,”,列。,计算方法为：年度费用,=,岗位标准*预计报销总时间。,6,2,试题讲解,（,5,）根据,Sheet1,中,“,通讯费年度计划表,”,的,“,年度费用,”,列，计算预算总金额。要求：,使用函数计算并将结果保存在,Sheet1,中的,C2,单元格中。,并根据,C2,单元格中的结果，转换为金额大写形式，保存在,Sheet1,中的,F2,单元格中。,（,6,）将,Sheet1,中的,“,通讯费年度计划表,”,复制到,Sheet2,中，并对,Sheet2,进行自动筛选。要求：,筛选条件为：,“,岗位类别,”,为技术研发、,“,报销地点,”,为武汉；,将筛选条件保存在,Sheet2,中。,注意：,1,）无须考虑是否删除或移动筛选条件。,2,）在复制过程中，将标题项,“,通讯费年度计划表,”,连同数据一同复制；,3,）数据表必须顶格放置；,（,7,）根据,Sheet1,中的,“,通讯费年度计划表,”,，在,Sheet3,中新建一张数据透视表。要求：,显示不同报销地点不同岗位的年度费用情况；,行区域设置为,“,报销地点,”,；,列区域设置为,“,岗位类别,”,；,数据区域设置为,“,年度费用,”,；,求和项为年度费用。,6,2,试题讲解,常见试题,2,打开第,6,章,Excel,操作素材库中的,“,电话号码统计,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet5,的,A1,单元格中设置为只能录入,5,位数字或文本，当录入位数错误时,提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）使用时间函数，对,Sheet1,中用户的年龄进行计算。要求：,假设当前时间是,“,2008-11-15,”,，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在,“,年龄,”,列当中。计算方法为两个时间年份之差。,（,3,）使用,REPLACE,函数，对,Sheet1,中用户的电话号码进行升级。要求：,对,“,原电话号码,”,列中的电话号码进行升级。升级方法是在区号（,0571,）后面加上,“,8,”,，并将其计算结果保存在,“,升级电话号码,”,列的相应单元格中。,例如：电话号码,“,05716742808,”,升级后为,“,057186742808,”,。,6,2,试题讲解,（,4,）在,Sheet1,中，使用,AND,函数，根据,“,性别,”,及,“,年龄,”,列中的数据，判断所有用户是否为大于等于,40,岁的男性，并将结果保存在,“,是否,=40,男性,”,列中。,注意：如果是，保存结果为,TRUE,；否则，保存结果为,FALSE,。,（,5,）根据,Sheet1,中的数据，对以下条件，使用统计函数进行统计。要求：,统计性别为,“,男,”,的用户人数，将结果填入,Sheet2,的,B2,单元格中；,统计年龄为,“,=40,”,岁的用户人数，并将结果填入,Sheet2,到,B3,单元格中。,（,6,）将,Sheet1,复制到,Sheet3,，并对,Sheet3,进行高级筛选。要求：,筛选条件为：,“,性别,”,-,女、,“,所在区域,”,-,西湖区；,将筛选结果保存在,Sheet3,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制数据表后，粘帖时，数据表必须顶格放置；,（,7,）根据,Sheet1,的结果，创建一个数据透视图,Chart1,。要求：,显示每个区域所拥有的用户数量；,X,坐标设置为,“,所在区域,”,；,计数项为,“,所在区域,”,；,将对应的数据透视表保存在,Sheet4,中。,6,2,试题讲解,常见试题,3,打开第,6,章,Excel,操作素材库中的,“,学生成绩统计表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet1,中，使用条件格式为,“,语文,”,列中的数据大于,80,的单元格中字体颜色设置为红色、加粗显示。,（,2,）使用数组公式，根据,Sheet1,中的数据，计算总分和平均分，将其计算结果保存到表中的,“,总分,”,列和,“,平均分,”,列当中。,（,3,）使用,RANK,函数，根据,Sheet1,中的,“,总分,”,列对每个同学排名情况进行统计，并将排名结果保存到表中的,“,排名,”,列当中。,6,2,试题讲解,（,4,）使用逻辑函数，判断,Sheet1,中每个同学的每门功课是否均高于全班单科平均分。要求：,如果是，保存结果为,TRUE,，否则，保存结果为,FALSE,；,将结果保存在表中的,“,优等生,”,列当中。,注意：优等生条件：每门功课均高于全班单科平均分,（,5,）根据,Sheet1,中的结果，使用统计函数，统计,“,数学,”,考试成绩各个分数段的同学人数，将统计结果保存到,Sheet2,中的相应位置。,（,6,）将,Sheet1,复制到,Sheet3,中，并对,Sheet3,进行高级筛选。要求：,筛选条件：,“,语文,”,=75,，,“,数学,”,=75,，,“,英语,“,=75,，,“,总分,”,=250,；,将结果保存在,Sheet3,中。,注意：无需考虑是否删除筛选条件；复制数据表后，粘贴时，数据表必须顶格放置。,（,7,）根据,Sheet1,中的结果，在,Sheet4,中创建一张数据透视表。要求：,显示是否为优等生的学生人数汇总情况；,行区域设置为：,“,优等生,”,；,数据区域设置为,“,优等生,”,；,计数项为优等生,6,2,试题讲解,常见试题,4,打开第,6,章,Excel,操作素材库中的,“,停车收费表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet4,的,A1,单元格中设置为只能录入,5,位数字或文本，当录入位数错误时,提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）根据,Sheet1,中的,“,停车价目表,”,价格，利用,HLOOKUP,函数对,“,停车情况记录表,”,中的,“,单价,”,列根据不同的车型进行自动填充。,（,3,）在,Sheet1,中，利用时间函数计算汽车在停车库中的停放时间，要求：,计算方法为：停放时间,=,出库时间,入库时间。,格式为：,“,小时：分钟：秒,”,将结果保存在,“,停车情况记录表,”,中的,“,停放时间,”,列中（例如：一小时十五分十二秒在停放时间中的表示为：,“,1,：,15,：,12,”,）。,6,2,试题讲解,（,4,）使用函数公式，根据停放时间的长短对,“,停车情况记录表,”,的停车费用进行计算，将计算结果填入到,“,停车情况记录表,”,的,“,应付金额,”,列中。要求：,停车按小时收费，对于不满一个小时的按照一个小时收费；,对于超过整点小时数十五分钟的多积累一个小时。（例如,1,小时,23,分，将以,2,小时计费）,使用统计函数，对,Sheet1,中的,“,停车情况记录表,”,根据下列条件进行统计，要求：,统计停车费用大于等于,40,元的停车记录条数，并将结果保存在,J8,单元格中。,统计最高的停车费用，并将结果保存在,J9,单元格中。,（,6,）将,Sheet1,中的,“,停车情况记录表,”,复制到,Sheet2,中，对,Sheet2,进行高级筛选，要求：,筛选条件为：,“,车型,”,小汽车，,“,应付金额,”,=30,；,将结果保存在,Sheet2,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制过程中，将标题项,“,停车情况记录表,”,连同数据一同复制。,3,）粘帖时，数据表必须顶格放置；,（,7,）根据,Sheet1,中的,“,停车情况记录表,”,，创建一个数据透视图,Chart1,，要求：,显示各种车型所收费用的汇总；,行区域设置为,“,车型,”,；,求和项为,“,应付金额,”,；,将对应的数据透视表保存在,Sheet3,中。,6,2,试题讲解,常见试题,5,打开第,6,章,Excel,操作素材库中的,“,员工资料表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet5,的,A1,单元格中设置为只能录入,5,位数字或文本，当录入位数错误时,提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）仅使用,MID,函数和,CONCATENATE,函数，对,Sheet1,中的,“,员工资料表,”,的,“,出生日期,”,列进行填充，要求：,填充的内容根据,“,身份证号码,”,列的内容来确定：,身份证号码中的第,7,位,第,10,位：表示出生年份；,身份证号码中的第,11,位,第,12,位：表示出生月份；,身份证号码中的第,13,位,第,14,位：表示出生日；,填充结果的格式为：,XXXX,年,XX,月,XX,日,（,3,）根据,Sheet1,中,“,职务补贴率表,”,的数据，使用,VLOOKUP,函数，对,“,员工资料表,”,中的,“,职务补贴率,”,列进行自动填充。,6,2,试题讲解,（,4,）使用数组公式，在,sheet1,中对,“,员工资料表,”,的,“,工资总额,”,列进行计算，并将结果保存在,“,工资总额,”,列中。,计算方法：工资总额,=,基本工资*（,1+,职务补贴）,（,5,）在,Sheet2,中，根据,“,固定资产情况表,”,，使用财务函数，对以下条件进行计算。,计算,“,每天折旧值,”,，并将结果填入到,E2,单元格中；,计算,“,每月折旧值,”,，并将结果填入到,E3,单元格中；,计算,“,每年折旧值,”,，并将结果填入到,E4,单元格中；,（,6,）将,Sheet1,的,“,员工资料表,”,复制到,Sheet3,，并对,Sheet3,进行高级筛选。要求：,筛选条件为：,“,性别,”,-,女、,“,职务,”,=,高级工程师；,将筛选结果保存在,Sheet3,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制过程中，将标题项,“,房产销售表,”,连同数据一同复制；,3,）数据表必须顶格放置；,（,7,）根据,Sheet1,中的,“,员工资料表,”,，在,Sheet4,中新建一张数据透视表。要求：,显示每种性别的不同职务的人数汇总情况；,行区域设置为,“,性别,”,；,列区域设置为,“,职务,”,；,数据区域设置为,“,职务,”,；,计数项为职务。,6,2,试题讲解,常见试题,6,打开第,6,章,Excel,操作素材库中的,“,学生成绩表,-A.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet1,中，使用条件格式将,“,铅球成绩（米）,”,列中单元格数据按下列要求显示。,数据大于,9,的单元格中字体颜色设置为红色、加粗显示；,数据介于,7,和,9,之间的单元格中字体颜色设置为蓝色、加粗显示；,数据小于,7,的单元格中字体颜色设置为绿色、加粗显示,（,2,）在,Sheet1,“,学生成绩表,”,中，使用,REPLACE,函数和数组公式，将原学号转变成新学号，同时将所得的新学号填入,“,新学号,”,列中。,转变方法：将原学号的第四位后面加上,“,5,”,；例：,“,2007032001,”,-,“,20075032001,”,。,6,2,试题讲解,（,3,）使用,IF,函数和逻辑函数，对,Sheet1,“,学生成绩表,”,中的,“,结果,1,”,和,“,结果,2,”,列进行填充。填充的内容根据以下条件确定。（要求：将男生、女生分开写入,IF,函数中）,结果,1,如果是男生：成绩,=14.00,，填充为,“,不合格,”,；,如果是女生：成绩,=16.00,，填充为,“,不合格,”,；,结果,2,如果是男生：成绩,7.50,，填充为,“,合格,”,；成绩,5.50,，填充为,“,合格,”,；成绩,=5.50,，填充为,“,不合格,”,；,（,4,）在,Sheet1,“,学生成绩表,”,中的数据，根据以下条件，使用统计函数进行统计。要求：,获取,“,100,米跑的最快的学生成绩,”,，并将结果填入到在,Sheet1,的,K4,单元格中；,统计,“,所有学生结果,1,为合格的总人数,”,，并将结果填入在,Sheet1,的,K5,单元格中。,6,2,试题讲解,（,5,）根据在,Sheet2,中的贷款情况，使用财务函数对贷款偿还金额进行计算。要求：,计算,“,按年偿还贷款金额（年末）,”,，并将结果填入到在,Sheet2,中的,E2,单元格中,计算,“,第,9,个月贷款利息金额,”,，并将结果填入到在,Sheet2,中的,E3,单元格中。,（,6,）将在,Sheet1,中的,“,学生成绩表,”,复制到在,Sheet3,，对在,Sheet3,进行高级筛选。要求：,筛选条件为：,“,性别,”,-,“,男,”,，,“,100,米成绩（秒）,”,-,“,9.00,”,将筛选结果保存在,Sheet3,中。,注意：无需考虑是否删除或移动筛选条件；复制过程中，将标题项,“,学生成绩表,”,连同数据一同复制；数据表必须顶格放置。,（,7,）根据,Sheet1,中的,“,学生成绩表,”,，在,Sheet4,中创建一张数据透视表。要求：,显示每种性别学生的合格与不合格总人数；,行区域设置为,“,性别,”,列区域设置为,“,结果,1,”,；,数据区域设置为,“,结果,1,”,；,计数项为,“,结果,1,”,6,2,试题讲解,常见试题,7,打开第,6,章,Excel,操作素材库中的,“,学生总分统计表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet5,中，使用条件格式将,“,性别,”,列中的数据为,“,男,”,的单元格中字体颜色设置为红色、加粗显示。,（,2,）使用数组公式，根据,Sheet1,中的,“,学生成绩表,”,的,“,数据，计算考试总分，并将结果填入到,“,总分,”,列中。,计算方法：总分,=,单选题,+,判断题,+windows,操作题,+Excel,操作题,+PowerPoint,操作题,+IE,操作题。,（,3,）使用文本函数中的一个函数，在,Sheet1,中，利用,“,学号,”,列的数据，根据以下要求获得考生所考级别，并将结果填入,“,级别,”,列中。要求：,学号中的第八列指示的考生所考级别，例如：,“,085200821023080,”,中的,“,2,”,标识了该考生所考级别为二级；,在,“,级别,”,列中，填入的数据是函数的返回值。,6,2,试题讲解,（,4,）使用统计函数，根据以下要求对,sheet1,中的,“,学生成绩表,”,的数据进行统计。要求：,统计,“,考,1,级的考生人数,”,，并将计算结果填入到,N2,单元格中；,统计,“,考试通过人数（,=60,）,”,，并将计算结果填入到,N3,单元格中；,统计,“,全体,1,级考生的考试平均分,”,，并将计算结果填入到,N4,单元格中；,注意：计算时，分母直接使用,“,N2,”,单元格的数据。,（,5,）使用财务函数，根据以下要求对,Sheet2,中的数据进行计算。要求：,根据,“,投资情况表,1,”,中的数据，计算,10,年以后得到的金额，并将结果填入到,B7,单元格中；,根据,“,投资情况表,2,”,中的数据，计算预计投资金额，并将结果填入到,E7,单元格中。,（,6,）将,Sheet1,的,“,学生成绩表,”,复制到,Sheet3,，并对,Sheet3,进行高级筛选。要求：,筛选条件为：,“,级别,”,-2,、,“,总分,”,-=70,；,将筛选结果保存在,Sheet3,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制过程中，将标题项,“,学生成绩表,”,连同数据一同复制；,3,）数据表必须顶格放置；,（,7,）根据,Sheet1,的,“,学生成绩表,”,，在,Sheet4,中创建一个数据透视表要求：,显示每个级别不同总分的人数汇总情况；,行区域设置为,“,级别,”,；,列区域设置为,“,总分,”,；,数据区域设置为,“,总分,”,；,计数项为总分。,6,2,试题讲解,常见试题,8,打开第,6,章,Excel,操作素材库中的,“,公司员工人事信息表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet4,的,A1,单元格中设置为只能录入,5,位数字或文本。当录入位数错误时，提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）使用大小写转换函数，根据,Sheet1,中的,“,公司员工人事信息表,”,的,“,编号,”,列，对,“,新编号,”,列进行填充。,要求：把编号中的小写字母改为大写字母，并将结果保存在,“,新编号,”,列中。,例如：,“,a001,”,更改后为,“,A001,”,。,（,3,）使用文本函数和时间函数，根据,Sheet1,中的,“,公司员工人事信息表,”,的,“,身份证号码,”,列，计算用户的年龄，并保存在,“,年龄,”,列中。注意：,身份证的第,7,位,第,10,位表示出生年份；,计算方法：年龄,=,当前年份,-,出生年份。其中,“,当前年份,”,使用时间函数计算。,6,2,试题讲解,（,4,）在,Sheet1,中，利用数据库函数及已设置的条件区域，根据以下情况计算，并将结果填入到相应的单元格当中。,计算：获取具有硕士学历，职务为经理助理的员工姓名，并将结果保存在,Sheet1,中的,E31,单元格中。,（,5,）使用函数，判断,Sheet1,中,L12,和,M12,单元格中的文本字符串是否完全相同。注意：,如果完全相同，结果保存为,TRUE,，否则保存为,FALSE,。,将结果保存在,Sheet1,中的,N11,单元格中。,（,6,）将,Sheet1,中的,“,公司员工人事信息表,”,复制到,Sheet2,中，并对,Sheet2,进行自动筛选。要求：,筛选条件为：,“,籍贯,”,-,广东、,“,学历,”,-,硕士、,“,职务,”,-,职员；,将筛选条件保存在,Sheet2,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制过程中，将标题项,“,公司员工人事信息表,”,连同数据一同复制；,3,）数据表必须顶格放置；,（,7,）根据,Sheet1,中的,“,公司员工人事信息表,”,，在,Sheet3,中新建一张数据透视表。要求：,显示每个职位的不同学历的人数情况；,行区域设置为,“,职务,”,；,列区域设置为,“,学历,”,；,数据区域设置为,“,学历,”,；,计数项为学历。,6,2,试题讲解,常见试题,9,打开第,6,章,Excel,操作素材库中的,“,采购情况表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet1,中，使用条件格式将,“,瓦数,”,列中数据小于,100,的单元格中，字体颜色设置为红色、加粗显示。,（,2,）使用数组公式，计算,Sheet1,中,“,采购情况表,”,的每种产品的采购总额，将结果保存到表中的,“,采购总额,”,列中。计算方法：,“,单价*每盒数量*采购盒数,”,。,（,3,）根据,Sheet1,中的,“,采购情况表,”,，使用数据库函数及已设置的条件区域，计算以下情况的结果。,计算：商标为上海，瓦数小于,100,的白炽灯的平均单价，并将结果填入,sheet1,的,G25,单元格中，保留,2,位小数；,计算：产品为白炽灯，其瓦数大于等于,80,且小于等于,100,的品种数，并将结果填入,sheet1,的,g26,单元格中。,6,2,试题讲解,（,4,）某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答,y,（吸烟）或者,n,（不吸烟）。根据调查情况，制作出,sheet2,中的,“,吸烟情况调查表,”,。使用函数，统计符合以下条件的数值。,统计未登记的部门个数，将结果保存在,b14,单元格中；,统计在登记的部门中吸烟的部门个数，将结果保存在,b15,单元格中。,（,5,）使用函数，对,sheet2,中的,b21,单元格中的内容进行判断，判断其是否为文本，如果是，单元格填充为,“,True,”,；如果不是，单元格填充为,“,False,”,，并将结果保存在,sheet2,中的,b22,单元格当中。,（,6,）将,sheet1,中的,“,采购情况表,”,复制到,sheet3,中，对,sheet3,进行高级筛选，要求：,筛选条件：,“,产品为白炽灯，商标为上海,”,；,将结果保存在,sheet3,中。,注意：,1,）无须考虑是否删除或移动筛选条件。,2,）在复制过程中，将标题项,“,采购情况表,”,连同数据一同复制；,2,）数据表必须顶格放置；,（,7,）根据,sheet1,中的,“,采购情况表,”,，在,sheet4,中创建一张数据透视表，要求：,显示不同商标的不同产品的采购数量；,行区域设置为,“,产品,”,；,列区域设置为,“,商标,”,；,数据区域为,“,采购盒数,”,；,求和项为,“,采购盒数,”,。,6,2,试题讲解,常见试题,10,打开第,6,章,Excel,操作素材库中的,“,教材订购,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet5,的,A1,单元格中设置为只能录入,5,位数字或文本。当录入位数错误时，提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）使用数组公式，计算,sheet1,中,“,教材订购情况表,”,的订购金额，将结果保存到该表的,“,金额,”,列当中。计算方法：金额订数*单价。,（,3,）使用统计函数，对,sheeet1,中,“,教材订购情况表,”,的结果按以下条件进行统计，并将结果保存在,sheet1,中的相应位置，要求：,统计出版社名称为,“,高等教育出版社,”,的书的种类数，并将结果保存在,sheet1,中,l2,单元格中；,统计订购数量大于,110,且小于,850,的书的种类数，并将结果保存在,sheet1,中,l3,单元格中。,6,2,试题讲解,（,4,）使用函数，计算每个用户所订购图书所需支付的金额总数，将结果保存在,sheet1,中,“,用户支付情况,”,的,“,支付总额,”,列中。,（,5,）使用函数，判断,sheet2,中的年份是否为闰年，如果是，结果保存,“,闰年,”,，如果不是，则结果保存为,“,平年,”,，并将结果保存在,“,是否为闰年,”,列中。,说明：闰年定义：年数能被,4,整除而不能被,100,整除，或者能被,400,整除的年份。,（,6,）将,sheet1,中的,“,教材订购情况表,”,复制到,sheet3,中，对,sheet3,进行高级筛选，要求：,筛选条件为,“,订数,=500,，且金额总数,=20,，,“,上海平均气温,”,1000000,；,将筛选结果保存在,Sheet3,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制过程中，将标题项,“,房产销售表,”,连同数据一同复制；,3,）数据表必须顶格放置；,（,7,）根据,Sheet1,的,“,房产销售表,”,的结果，创建一个数据透视图,Chart1,。要求：,显示每个销售人员所销售房屋应缴纳契税总额汇总情况；,X,坐标设置为,“,销售人员,”,；,数据区域设置为,“,契税总额,”,；,求和项设置为契税总额；,将对应的数据透视表保存在,sheet4,中。,6,2,试题讲解,常见试题,13,打开第,6,章,Excel,操作素材库中的,“,公务员考试成绩表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet1,中，使用条件格式将,“,性别,”,列中为,“,女,”,的单元格中，字体颜色设置为红色、加粗显示。,（,2,）使用,IF,函数，对,Sheet1,中的,“,学位,”,列进行自动填充。要求：填充的内容根据,“,学历,”,列的内容来确定（假定学生均已获得相应学位）：,博士研究生,博士,硕士研究生,硕士,本科,学士,其他,无,（,3,）使用数组公式，在,Sheet1,中计算下列项目：,计算笔试成绩比例分，并将结果保存在,“,公务员考试成绩表,”,中的,“,笔试成绩比例分,”,中。计算方法：笔试成绩比例分,=,（笔试成绩,/3,）*,60%,。,计算面试成绩比例分，并将结果保存在,“,公务员考试成绩表,”,中的,“,面试成绩比例分,”,中。计算方法：面试成绩比例分,=,面试成绩*,40%,计算总成绩，并将结果保存在,“,公务员考试成绩表,”,中的,“,总成绩,”,中。计算方法：总成绩,=,笔试成绩比例分,+,面试成绩比例分。,6,2,试题讲解,（,4,）将,Sheet1,中的,“,公务员考试成绩表,”,复制到,Sheet2,中，根据以下要求修改,“,公务员考试成绩表,”,中的数组公式，并将结果保存在,Sheet2,中相应列中。要求：,修改,“,笔试成绩比例分,”,的计算，计算方法为：笔试成绩比例分,=,（笔试成绩,/2,）*,60%,，并将结果保存在,“,笔试成绩比例分,”,列中。,注意：,1,）复制过程中，将标题项,“,公务员考试成绩表,”,连同数据一同复制。,2,）数据表必须顶格放置。,（,5,）在,Sheet2,中，使用,RANK,函数，根据,“,总成绩,”,列对所有考生排名。要求：将排名结果保存在,“,排名,”,列中。,（,6,）将,Sheet2,中的,“,公务员考试成绩表,”,复制到,Sheet3,，并对,Sheet3,进行高级筛选。要求：,筛选条件为：,“,报考单位,”,中院、,“,性别,”,男、,“,学历,”,硕士研究生,将筛选结果保存在,Sheet3,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制过程中，将标题项,“,公务员考试成绩表,”,连同数据一同复制；,3,）复制数据表后，粘贴时数据表必须顶格放置；,（,7,）根据,Sheet2,中的,“,公务员考试成绩表,”,，在,Sheet4,中创建一张数据透视表。要求：,显示每个报考单位的人的不同学历的人数汇总情况。,行区域设置为,“,报考单位,”,列区域设置为,“,学历,”,数据区域设置为,“,学历,”,计数项为学历。,6,2,试题讲解,常见试题,14,打开第,6,章,Excel,操作素材库中的,“,3,月份销售统计表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet4,的,A1,单元格中设置为只能录入,5,位数字或文本，当录入位数错误时,提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）使用,VLOOKUP,函数，对,SHEET1,中的,“,3,月份销售统计表,”,的,“,产品名称,”,列和,“,产品单价,”,列进行填充。要求：,根据,“,企业销售产品清单,”,，使用,VLOOKUP,函数，将产品名称和产品单价填充到,“,3,月份销售统计表,”,的,“,产品名称,”,列和,“,产品单价,”,列中。,（,3,）使用数组公式，计算,SHEET1,中的,“,3,月份销售统计表,”,中的销售金额，并将结果填入到该表的,“,销售金额,”,列中。,计算方法：销售金额,=,产品单价*销售数量。,6,2,试题讲解,（,4,）使用统计函数，根据,“,3,月份销售统计表,”,中的数据，计算,“,分部销售业绩统计表,”,中的总销售额，并将结果填入该表的,“,总销售额,”,列。,（,5,）在,SHEET1,中，使用,RANK,函数，在,“,分部销售业绩统计表,”,表中，根据,“,总销售额,”,对各部门进行排名，并将结果填入到,“,销售排名,”,列中。,（,6,）将,SHEET1,中的,“,3,月份销售统计表,”,复制到,SHEET2,中，对,SHEET2,进行高级筛选。要求：,筛选条件为：,“,销售数量,”,-3,、,“,所属部门,”,-,市场,1,部、,“,销售金额,”,-1000.,将筛选结果保存在,SHEET2,中。,注意：,1,）无需考虑是否删除或移动筛选条件；,2,）复制过程中，将标题项,“,3,月份销售统计表,”,连同数据一同复制；,3,）复制数据表后，粘贴时数据表必须顶格放置；,（,7,）根据,SHEET1,的,“,3,月份销售统计表,”,中的数据，新建一个数据透视图,Chart1,。要求：,该图形显示每位经办人的总销售额情况。,X,坐标设置为,“,经办人,”,。,数据区域设置为,“,销售金额,”,。,求和项为,“,销售金额,”,。,将对应的数据透视表保存在,SHEET3,中。,6,2,试题讲解,常见试题,15,打开第,6,章,Excel,操作素材库中的,“,员工信息表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet4,的,A1,单元格中设置为只能录入,5,位数字或文本，当录入位数错误时,提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）使用,REPLACE,函数，对,SHEET1,中,“,员工信息表,”,的员工代码进行升级。要求：,升级方法：在,PA,后面加上,0,。,将升级后的员工代码结果填入表中的,“,升级员工代码,”,列中。例如：,PA125,，修改后为,PA0125,。,（,3,）使用时间函数，计算,SHEET1,中,“,员工信息表,”,的,“,年龄,”,列和,“,工龄,”,列。要求：,假设当前时间是,“,2009-11-15,”,，结合表中的,“,出生年月,”,、,“,参加工作时间,”,列，对员工,“,年龄,”,和,“,工龄,”,进行计算。,计算方法为两年份之差，并将结果保存到表中的,“,年龄,”,列和,“,工龄,”,列中。,6,2,试题讲解,（,4,）使用统计函数，根据,SHEET1,中,“,员工信息表,”,的数据，对以下条件进行统计。要求：,统计男性员工的人数，结果填入,N3,单元格中。,统计高级工程师人数，结果填入,N4,单元格中。,统计工龄大于等于,10,的人数，结果填入,N5,单元格中。,（,5,）使用逻辑函数，判断员工是否有资格评,“,高级工程师,”,。要求：,评选条件为：工龄大于,20,，且为工程师的员工。,将结果保存在,“,是否有资格评选高级工程师,”,列中。,如果有资格，保存结果为,TRUE,；否则为,FALSE,。,（,6,）将,SHEET1,的,“,员工信息表,”,复制到,SHEET2,中，并对,SHEET2,进行高级筛选。要求：,筛选条件为：,“,性别,”,=,男，,“,年龄,”,30,，,“,工龄,”,=10,，,“,职称,”,=,助工。,将结果保存在,SHEET2,中。,注意：,1,）无须考虑是否删除或移动筛选条件。,2,）在复制过程中，将标题项,“,员工信息表,”,连同数据一同复制；,3,）数据表必须顶格放置；,（,7,）根据,SHEET1,中的数据，创建一个数据透视图,Chart1,。要求：,显示工厂中各种职称人数的汇总情况。,X,坐标设置为,“,职称,”,。,数据区域为,“,职称,”,。,将对应的数据透视表保存在,SHEET3,中。,6,2,试题讲解,常见试题,16,打开第,6,章,Excel,操作素材库中的,“,零件检测结果表,.xls,”,文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。,注意：在做题时，不能修改原有的数据。,一、操作要求,（,1,）在,Sheet4,的,A1,单元格中设置为只能录入,5,位数字或文本。当录入位数错误时，提示错误原因，样式为,“,警告,”,，错误信息为,“,只能录入,5,位数字或文本,”,。,（,2,）使用数组公式，根据,Sheet1,中,“,零件检测结果表,”,的,“,外轮直径,”,和,“,内轮直径,”,列，计算内外轮差，并将结果表保存在,“,轮差,”,列中。,计算方法为：轮差,=,外轮直径,内轮直径,（,3,）使用,IF,函数，对,Sheet1,中,“,零件检测结果表,”,的,“,检测结果,”,进行填充。要求：,如果,“,轮差,”,“,2009001,”,。,（,3,）使用数组公式，对,Sheet1,中,“,学生成绩表