Excel高级应用操作.ppt

第6章 Excel2003高级应用操作 本章的重点主要包括：条件格式；数据有效性设置；相对地址与绝对地址；公式与数组；单元格数字格式；数据填充；函数及函数的嵌套；高级筛选；数据透视表和数据透视图。 常见试题1 打开第6章Excel操作素材库中的“通讯费年度计划表.xls”文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。 注意：在做题时，不能修改原有的数据。 一、操作要求 （1）在Sheet1中，使用条件格式将“岗位类别”列中单元格数据按下列要求显示。 数据为“副经理”的单元格中字体颜色设置为红色，加粗显示； 数据为“服务部”的单元格中字体颜色设置为蓝色、加粗显示。 （2）使用VLOOKUP函数，根据Sheet1中的“岗位最高限额明细表”，填充“通讯费年度计划表”中的“岗位标准”列。 （3）使用INT函数，计算Sheet1中的“通讯费年度计划表”的“预计报销总时间”列，要求： 每月以30天计算； 将结果填充在“预计报销总时间”列中。 （4）使用数组公式，计算Sheet1中“通讯费年度计划表”的“年度费用”列。 计算方法为：年度费用=岗位标准*预计报销总时间。 （5）根据Sheet1中“通讯费年度计划表”的“年度费用”列，计算预算总金额。要求： 使用函数计算并将结果保存在Sheet1中的C2单元格中。 并根据C2单元格中的结果，转换为金额大写形式，保存在Sheet1中的F2单元格中。 （6）将Sheet1中的“通讯费年度计划表”复制到Sheet2中，并对Sheet2进行自动筛选。要求： 筛选条件为：“岗位类别”为技术研发、“报销地点”为武汉； 将筛选条件保存在Sheet2中。 注意：1）无须考虑是否删除或移动筛选条件。 2）在复制过程中，将标题项“通讯费年度计划表”连同数据一同复制； 3）数据表必须顶格放置； （7）根据Sheet1中的“通讯费年度计划表”，在Sheet3中新建一张数据透视表。要求： 显示不同报销地点不同岗位的年度费用情况； 行区域设置为“报销地点”； 列区域设置为“岗位类别”； 数据区域设置为“年度费用”； 求和项为年度费用。 常见试题2 打开第6章Excel操作素材库中的“电话号码统计.xls”文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。 注意：在做题时，不能修改原有的数据。 一、操作要求 （1）在Sheet5的A1单元格中设置为只能录入5位数字或文本，当录入位数错误时,提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。 （2）使用时间函数，对Sheet1中用户的年龄进行计算。要求： 假设当前时间是“2008-11-15”，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”列当中。计算方法为两个时间年份之差。 （3）使用REPLACE函数，对Sheet1中用户的电话号码进行升级。要求： 对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。 例如：电话号码“05716742808”升级后为“057186742808”。 （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中。 常见试题3 打开第6章Excel操作素材库中的“学生成绩统计表.xls”文件，按下面的操作要求进行操作，并把操作结果保存在相应文件夹中。 注意：在做题时，不能修改原有的数据。 一、操作要求 （1）在Sheet1中，使用条件格式为“语文”列中的数据大于80的单元格中字体颜色设置为红色、加粗显示。 （2）使用数组公式，根据Sheet1中的数据，计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。