Excel高级应用操作指导

《Excel高级应用操作指导》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、第3章Excel高级应用操作指导重点：条件格式，数据有效性设置，相对地址和绝对地址，一般公式和数组公式，单元格数字格式，数据填充，各种Excel函数及函数的嵌套，自动筛选，高级筛选，数据透视表和透视图等。本章目录3-1学生成绩表23-2衣物采购表113-3教材订购情况表163-4公务员考试成绩表213-5学生成绩表（计算机一级成绩）243-6图书订购信息表273-7杭州电话用户情况表303-8学生成绩表（体育成绩）343-9三月份销售统计表383-10停车情况记录表413-11通讯费年度计划表443-12医院病人护理统计表463-

2、13员工资料表483-14学生成绩表（数学、语文、英语）523-15公司员工人事信息表543-16房产销售表563-17温度情况表573-18员工信息表593-19采购情况表623-20零件检测结果表653-1学生成绩表1.操作要求：(1)在Sheet1中，使用条件格式将“性别”列中数据为“男”的单元格中，字体设置为红色，加粗显示。(2)使用REPLACE函数，对Sheet1中的“学生成绩表”的学生学号进行更改，并将更改的学号填入到“新学号”列中，学号更改的方法为：在原学号的前面加上“2011”.例如：“001”——“201100

3、1”。(3)使用数组公式，对Sheet1“学生成绩表”中的“总分”列进行计算。计算方法：总分＝语文＋数学＋英语＋信息技术＋体育(4)使IF函数，根据以下条件，对对Sheet1“学生成绩表”中的“考评”列进行计算。条件：如果“总分”>=350，填充为“合格”；否则，填充为“不合格”。(5)在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况，并将结果填入到相应的单元格当中。条件：①计算：“语文”和“数学”成绩都大于或等于85的学生人数。②计算：“体育”成绩大于或等于90的“女生”姓名。③计算：“体育”成绩中“男生”的平均

4、分。④计算：“体育”成绩中“男生”的最高分。(6)将Sheet1中的“学生成绩表”复制到Sheet2，并对Sheet2进行高级筛选。要求：①筛选条件为：“性别”——男；“英语”——>90；“信息技术”——>95。②将筛选结果保存在Sheet2中。注意：①无须考虑是否删除或移动筛选条件。②复制过程中，将标题项“学生成绩表”连同数据一同复制。③数据表必须顶格放置。(7)根据Sheet1中“学生成绩表”，在Sheet3中新建一张数据透视表。要求：①显示不同性别、不同考评结果的学生人数情况。②行区域设置为“性别”。③列区域设置为“考评”。

5、④数据区域设置为“考评”。⑤计数项为“考评”。2.操作步骤如下：(1)选中Sheet1工作表中的D3:D24单元格区域，选择“格式”→“条件格式”命令，如图31所示，在打开的“条件格式”对话框中，分别选择“单元格数值”、“等于”，并在右侧的文本框中输入“男”，如图33所示。图31选择“条件格式”命令图32“条件格式”对话框再单击“格式”按钮，在打开的“单元格格式”对话框中，选择“字体”选项卡，字体设置为红色，加粗，如图33所示。单击“确定”按钮即可。图33“单元格格式”对话框(1)单击B3单元格，选择“插入”→“函数”

6、命令，打开插入函数对话框并选择REPLACE函数，如图34所示。单击“确定”打开REPLACE函数参数对话框，并在相应的文本框中输入如图35所示的参数，单击“确定”按钮。双击B3单元格的填充柄填充该列的数据。图34插入REPLACE函数对话框图35REPLACE函数参数对话框(1)选中J3:J24单元格区域，输入公式：“=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24”（各列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键。(2)单击K3单元格，选择“插入”→“函数”命令，打

7、开IF函数对话框，并在相应的文本框中输入如图36所示的参数，单击“确定”按钮。双击K3单元格的填充柄填充该列的数据。图36IF函数参数对话框(1)①单击I28单元格，选择“插入”→“函数”命令，打开插入函数对话框并选择数据库函数DCOUNT，如图37所示。单击“确定”打开DCOUNT函数参数对话框，并在相应的文本框中输入如图38所示的参数，单击“确定”按钮。图37插入DCOUNT函数对话框图38DCOUNT函数参数对话框②单击I29单元格，选择“插入”→“函数”命令，打开插入函数对话框并选择数据库函数DGET，如图3

8、9所示。单击“确定”打开DGET函数参数对话框，并在相应的文本框中输入如图310所示的参数，单击“确定”按钮。图39插入DGET函数对话框图310DGET函数参数对话框③单击I30单元格，选择“插入”→“函数”命令，打开插入函数对话框并选择数