Excel高级应用的操作指导.docVIP

Excel高级应用操作指导 重点：条件格式，数据有效性设置，相对地址和绝对地址，一般公式和数组公式，单元格数字格式，数据填充，各种Excel函数及函数的嵌套，自动筛选，高级筛选，数据透视表和透视图等。 本章目录 3-1 学生成绩表 2 3-2 衣物采购表 11 3-3 教材订购情况表 16 3-4 公务员考试成绩表 21 3-5 学生成绩表（计算机一级成绩） 24 3-6 图书订购信息表 27 3-7 杭州电话用户情况表 30 3-8 学生成绩表（体育成绩） 34 3-9 三月份销售统计表 38 3-10 停车情况记录表 41 3-11 通讯费年度计划表 44 3-12 医院病人护理统计表 46 3-13 员工资料表 48 3-14 学生成绩表（数学、语文、英语） 52 3-15 公司员工人事信息表 54 3-16 房产销售表 56 3-17 温度情况表 57 3-18 员工信息表 59 3-19 采购情况表 62 3-20 零件检测结果表 65 3-1 学生成绩表 1. 操作要求： 在Sheet1中，使用条件格式将“性别”列中数据为“男”的单元格中，字体设置为红色，加粗显示。 使用REPLACE函数，对Sheet1中的“学生成绩表”的学生学号进行更改，并将更改的学号填入到“新学号”列中，学号更改的方法为：在原学号的前面加上“2011”.例如：“001”——“2011001”。 使用数组公式，对Sheet1“学生成绩表”中的“总分”列进行计算。 计算方法：总分＝语文＋数学＋英语＋信息技术＋体育 使IF函数，根据以下条件，对对Sheet1“学生成绩表”中的“考评”列进行计算。 条件：如果“总分”=350，填充为“合格”；否则，填充为“不合格”。 在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况，并将结果填入到相应的单元格当中。 条件：①计算：“语文”和“数学”成绩都大于或等于85的学生人数。 ②计算：“体育”成绩大于或等于90的“女生”姓名。 ③计算：“体育”成绩中“男生”的平均分。 ④计算：“体育”成绩中“男生”的最高分。 将Sheet1中的“学生成绩表”复制到Sheet2，并对Sheet2进行高级筛选。 要求： ①筛选条件为：“性别”——男；“英语”——90；“信息技术”——95。 ②将筛选结果保存在Sheet2中。 注意： ①无须考虑是否删除或移动筛选条件。 ②复制过程中，将标题项“学生成绩表”连同数据一同复制。 ③数据表必须顶格放置。 根据Sheet1中“学生成绩表”，在Sheet3中新建一张数据透视表。 要求： ①显示不同性别、不同考评结果的学生人数情况。 ②行区域设置为“性别”。 ③列区域设置为“考评”。 ④数据区域设置为“考评”。 ⑤计数项为“考评”。 2. 操作步骤如下： 选中Sheet1工作表中的D3: D24单元格区域，选择“格式”→“条件格式”命令，如图 31所示，在打开的“条件格式”对话框中，分别选择“单元格数值”、“等于”，并在右侧的文本框中输入“男”，如图 33所示。 图 31 选择“条件格式”命令 图 32 “条件格式”对话框 再单击“格式”按钮，在打开的“单元格格式”对话框中，选择“字体”选项卡，字体设置为红色，加粗，如图 33所示。单击“确定”按钮即可。 图 33 “单元格格式”对话框 单击B3单元格，选择“插入”→“函数”命令，打开插入函数对话框并选择REPLACE函数，如图 34所示。单击“确定”打开REPLACE函数参数对话框，并在相应的文本框中输入如图 35所示的参数，单击“确定”按钮。双击B3单元格的填充柄填充该列的数据。 图 34 插入REPLACE函数对话框 图 35 REPLACE函数参数对话框 选中J3:J24单元格区域，输入公式：“=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24”（各列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键。 单击K3单元格，选择“插入”→“函数”命令，打开IF函数对话框，并在相应的文本框中输入如图 36所示的参数，单击“确定”按钮。双击K3单元格的填充柄填充该列的数据。 图 36 IF函数参数对话框 ①单击I28单元格，选择“插入”→“函数”命令，打开插入函数对话框并选择数据库函数DCOUNT，如图 37所示。单击“确定”打开DCOUNT函数参数对话框，并在相应的文本框中输入如图 38所示的参数，单击“确定”按钮。 图 37 插入DCOUNT函数对话框 图 38 DCOUNT函数参数对话框 ②单击I29单元