Excel高级应用操作指导.doc

第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-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”“2011001”。(3) 使用数组公式，对Sheet1“学生成绩表”中的“总分”列进行计算。计算方法：总分语文数学英语信息技术体育(4) 使IF函数，根据以下条件，对对Sheet1“学生成绩表”中的“考评”列进行计算。条件：如果“总分”=350，填充为“合格”；否则，填充为“不合格”。(5) 在Sheet1中，利用数据库函数及已设置的条件区域，根据以下情况，并将结果填入到相应的单元格当中。条件：计算：“语文”和“数学”成绩都大于或等于85的学生人数。 计算：“体育”成绩大于或等于90的“女生”姓名。 计算：“体育”成绩中“男生”的平均分。 计算：“体育”成绩中“男生”的最高分。(6) 将Sheet1中的“学生成绩表”复制到Sheet2，并对Sheet2进行高级筛选。要求：筛选条件为：“性别”男；“英语”90；“信息技术”95。 将筛选结果保存在Sheet2中。 注意：无须考虑是否删除或移动筛选条件。 复制过程中，将标题项“学生成绩表”连同数据一同复制。 数据表必须顶格放置。(7) 根据Sheet1中“学生成绩表”，在Sheet3中新建一张数据透视表。要求：显示不同性别、不同考评结果的学生人数情况。行区域设置为“性别”。 列区域设置为“考评”。 数据区域设置为“考评”。 计数项为“考评”。2. 操作步骤如下：(1) 选中Sheet1工作表中的D3: D24单元格区域，选择“格式”“条件格式”命令，如图 31所示，在打开的“条件格式”对话框中，分别选择“单元格数值”、“等于”，并在右侧的文本框中输入“男”，如图 33所示。图 31 选择“条件格式”命令图 32 “条件格式”对话框再单击“格式”按钮，在打开的“单元格格式”对话框中，选择“字体”选项卡，字体设置为红色，加粗，如图 33所示。单击“确定”按钮即可。图 33 “单元格格式”对话框(2) 单击B3单元格，选择“插入”“函数”命令，打开插入函数对话框并选择REPLACE函数，如图 34所示。单击“确定”打开REPLACE函数参数对话框，并在相应的文本框中输入如图 35所示的参数，单击“确定”按钮。双击B3单元格的填充柄填充该列的数据。图 34 插入REPLACE函数对话框图 35 REPLACE函数参数对话框(3) 选中J3:J24单元格区域，输入公式：“=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24”（各列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键。(4) 单击K3单元格，选择“插入”“函数”命令，打开IF函数对话框，并在相应的文本框中输入如图 36所示的参数，单击“确定”按钮。双击K3单元格的填充柄填充该列的数据。图 36 IF函数参数对话框(5)单击I28单元格，选择“插入”“函数”命令，打开插入函数对话框并选择数据库函数DCOUNT，如图 37所示。单击“确定”打开DCOUNT函数参数对话框，并在相应的文本框中输入如图 38所示的参数，单击“确定”按钮。图 37 插入DCOUNT函数对话框图 38 DCOUNT函数参数对话框单击I29单元格，选择“插入”“函数”命令，打开插入函数对话框并选择数据库函数DGET，如图 39所示。单击“确定”打开DGET函数参数对话框，并在相应的文本框中输入如图 310所示的参数，单击“确定”按钮。图 39插入DGET函数对话框图 310 DGET函数参数对话框单击I30单元格，选择“插入”“函数”命令，打开插入函数对话框并选择数据库函数DAVERAGE，如图 311所示。单击“确定”打开DAVERAGE函数参数对话框，并在相应的文本框中输入如Error! Reference source not found.所示的参数，单击“确定”按钮。图 311插入AVERAGE函数对话框图 312 DAVERAGE函数参数对话框单击I31单元格，选择“插入”“函数”命令，打开插入函数对话框并选择数据库函数DMAX，如图 313所示。单击“确定”打开DMAX函数参数对话框，并在相应的文本框中输入如图 314所示的参数，单击“确定”按钮。图 313插入DMAX函数对话框图 314 DMAX函数参数对话框(6) 选中Sheet1工作表中的A1:K24单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“复制”命令。再单击Sheet2的A1单元格，单击鼠标右键，在弹出的快捷菜单中选择“粘贴”命令。在Sheet2工作表空白区域创建筛选条件，如图 315所示（建议直接复制Sheet2工作表中的相关字段）。单击Sheet2工作表中A2:K24单元格区域中的任一单元格，选择“数据”“筛选”“高级筛选”命令（如图 316所示），打开“高级筛选”对话框，“列表区域”文本框中已自动填入数据清单所在的单元格区域，把光标定位在“条件区域”文本框内，用鼠拖选前面创建的筛选条件区域（如图 317所示），单击“确定”按钮。图 315 创建筛选条件图 316 选择“高级筛选”命令图 317 设置条件区域(7) 单击Sheet1工作表中A2:K24单元格区域中的任一单元格，选择“数据”“数据透视表和透视图”命令（如图 318所示），打开“数据透视表和数据透视图向导3步骤之1”对话框（如图 319所示），选中“数据透视表”单选按钮后，单击“下一步”按钮，打开“数据透视表和数据透视图向导3步骤之2”对话框，“选定区域”文本框中已自动填入数据清单所在的单元格区域（如图 320所示），单击“下一步”按钮，打开“数据透视表和数据透视图向导3步骤之3”对话框，选中“现有工作表”单选按钮，再单击Sheet3工作表中的A1单元格，如图 321所示，单击“完成”按钮。 图 318 选择“数据透视表和数据透视图”命令图 319 “数据透视表和数据透视图向导3步骤之1”对话框图 320 “数据透视表和数据透视图向导3步骤之2”对话框图 321“数据透视表和数据透视图向导3步骤之3”对话框在弹出的如图 322所示界面中，按题目要求将对应字段拖放到位即可。单击常用工具栏的“保存”按钮。图 322 “数据透视表”布局3-2 衣物采购表1. 操作要求(1) 在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中，字体设置为红色，加粗显示。(2) 使用VLOOKUP函数，对Sheet1中的“采购表”的“单价”列进行填充。(3) 使用IF函数，对Sheet1“采购表”中的“折扣”列进行填充。(4) 使用公式，对Sheet1中“采购表”的“合计”列进行填充。计算公式：合计单价*采购数量（1折扣率）(5) 使用SUMIF函数，计算各种商品的采购数量和采购总金额，将结果保存在Sheet1 “统计表”的相应位置。(6) 将Sheet1中的“采购表”复制到Sheet2，并对Sheet2进行高级筛选。要求：筛选条件为：“采购数量”150，“折扣率”0。 将筛选结果保存在Sheet2中。 注意：无须考虑是否删除或移动筛选条件。 复制过程中，将标题项“采购表”连同数据一同复制。 数据表必须顶格放置。(7) 根据Sheet1中“采购表”，新建一个数据透视图Chart1。要求：该图形显示每个采购时间点所采购的所有项目数量汇总的情况。 x坐标设置为“采购时间”。 求和项为“采购数量”。 将对相应的数据透视表保存在Sheet3中。2.操作步骤如下：(1) 参考3-1中的步骤（1）。(2) 单击D11单元格，选择 “插入”“函数”命令，打开插入函数对话框并选择VLOOKUP函数，如图 323所示。单击“确定”打开VLOOKUP函数参数对话框，并在相应的文本框中输入如图 324所示的参数（注意Table_Arrray区域的绝对引用，可拖选选区后直接按F4功能键实现），单击“确定”按钮。双击D11单元格的填充柄填充该列的数据。图 323 插入VLOOKUP函数对话框图 324 VLOOKUP函数参数对话框(3) 分析IF嵌套函数，画出其流程图如图 325所示。 NNYYNY折扣率为B3单元格所示的（绝对引用）B11100B11200折扣率为B4单元格所示的（绝对引用）折扣率为B5单元格所示的（绝对引用）B11300折扣率为B6单元格所示的（绝对引用） 图 325 IF嵌套函数流程图据此，则在E11单元格中输入公式：“=IF(B11100,$B$3,IF(B11200,$B$4,IF(B11=500，且金额总数110”的种类数条件“=850”的种类数即在L3单元格中输入公式“=COUNTIF(G3:G52,110)-COUNTIF(G3:G52,=850)”（公式中的函数可通过中所示的方法插入）。 110850图 339 统计区域的数轴分析(4) 单击L8单元格，选择 “插入”“函数”命令，打开插入函数对话框并选择“数学与三角函数”函数中的SUMIF函数，如图 340所示。单击“确定”打开SUMIF函数参数对话框，并在相应的文本框中输入如图 341所示的参数（绝对引用可在鼠标拖选相应的单元格区域后按F4功能键实现）。双击L8单元格的填充柄。图 340 插入SUMIF函数对话框图 341 SUMIF函数参数对话框(5) 根据题意，闰年条件分析如图 342所示。两个条件满足其中之一OR(AND(MOD(A2,4)=0, MOD(A2,100)0), MOD(A2,400)=0)两个条件同时满足AND(MOD(A2,4)=0, MOD(A2,100)0)能被400整除MOD(A2,400)=0能被4整除MOD(A2,4)=0不能被100整除MOD(A2,100)0图 342 闰年条件分析单击Sheet2工作表的B2单元格，选择 “插入”“函数”命令，打开插入函数对话框并选择“逻辑”函数中的IF函数，如图 343所示。单击“确定”打开IF函数参数对话框，并在相应的文本框中输入如图 344所示的参数（Logical_test文本框中的参数为“OR(AND(MOD(A2,4)=0, MOD(A2,100)0), MOD(A2,400)=0)”）。双击B2单元格的填充柄。图 343 插入IF函数对话框图 344 IF函数参数对话框(6) 略。(7) 略。 3-4 公务员考试成绩表1. 操作要求(1) 在Sheet1中，使用条件格式将“性别”列中为“女”的单元格，字体颜色设置为红色、加粗显示。(2) 使用IF函数，对Sheet1中“学位”列进行自动填充。要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）。 博士研究生博士；硕士研究生硕士；本科学士；无。(3) 使用数组公式，在Sheet1中计算下列项目：计算笔试成绩比例分，并将结果保存在“公务员考试成绩表”中的“笔试成绩比例分”中。计算方法：笔试成绩比例分（笔试成绩/3）*60%。计算面试成绩比例分，并将结果保存在“公务员考试成绩表”中的“面试成绩比例分”中。计算方法：笔面试成绩比例分（面试成绩/3）*40%。计算总成绩，并将结果保存在“公务员考试成绩表”中的“总成绩”中。计算方法：总成绩笔试成绩比例分+面试成绩比例分。(4) 将Sheet1中的“公务员考试成绩表”复制到Sheet2，根据以下要求修改“公务员考试成绩表”中的数组公式，并将结果保存在Sheet2的相应列中。要求：修改“笔试成绩比例分”的计算，计算方法为：笔试成绩比例分（笔试成绩/2）*60%，并将结果保存在“笔试成绩比例分”列中。注意：复制过程中，将标题项“公务员考试成绩表”连同数据一同复制。数据表必须顶格放置。(5) 在Sheet2中，使用RANK函数，根据“总成绩”列对所有考生进行排名。要求：将排名结果保存在“排名”列中。(6) 将Sheet2中的“公务员考试成绩表”复制到Sheet3，并对Sheet3进行高级筛选。要求：筛选条件为：“报考单位”中院、“性别”男、“学历”硕士研究生。将筛选结果保存在Sheet3中。注意：无须考虑是否删除或移动筛选条件。复制过程中，将标题项“公务员考试成绩表”连同数据一同复制。复制数据表后，粘贴时数据表必须顶格放置。(7) 根据Sheet2中的“公务员考试成绩表”，在Sheet4中创建一张数据透视表。要求：显示每个报考单位的人的不同学历的人数汇总情况。行区域设置为“报考单位”。列区域设置为“学历”。数据区域设置为“学历”。计数项为“学历”。2. 操作步骤如下：(1) 参考3-1中的步骤（1）。(2) 分析IF嵌套函数，画出其流程图如图 345所示。 NNYYNY博士G3=”博研”生”=60）”，并将结果填入到N3单元格中。统计“全体1级考生的考试平均分”，并将结果填入到N4单元格中。（其中，计算时的分母直接使用N2单元格的数据）(5) 使用财务函数，根据以下要求对Sheet2中的数据进行计算。 要求：根据“投资情况表1”中的数据，计算10年以后得到的金额，并将结果填入到B7单元格中。根据“投资情况表2”中的数据，计算预计投资金额，并将结果填入到E7单元格中。(6) 将Sheet1中的“学生成绩表”复制到Sheet3，并对Sheet3进行高级筛选。要求： 筛选条件为：“级别”2、“总分”=70。将筛选结果保存在Sheet3中。注意：无须考虑是否删除或移动筛选条件。复制过程中，将标题项“学生成绩表”连同数据一同复制。数据表必须顶格放置。(7) 根据Sheet1中的“学生成绩表”，在Sheet4中新建一张数据透视表。要求：显示每个级别不同总分的人数汇总情况。行区域设置为“级别”。列区域设置为“总分”。数据区域设置为“总分”。计数项为“总分”。2. 操作步骤如下：(1) 参考3-1中的步骤（1）。(2) 在Sheet1工作表中选中J3:J57单元格区域，输入公式：“=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57”， （各列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键。(3) 单击Sheet1工作表的C3单元格，插入文本函数MID，打开该函数参数对话框，输入相应参数如图 348所示，单击“确定”按钮。双击C3单元格的填充柄。图 348 MID函数参数对话框(4) 单击N2单元格，选择插入统计函数COUNTIF，打开该函数对话框，并在相应的文本框中输入如图 349所示的参数，单击“确定”按钮。图 349 COUNTIF函数参数对话框单击N3单元格，打开COUNTIF函数对话框，并在相应的文本框中输入如图 350所示的参数，单击“确定”按钮。图 350 COUNTIF函数参数对话框单击N4单元格，打开SUMIF函数对话框，并在相应的文本框中输入如图 351所示的参数，单击“确定”按钮。图 351 SUMIF函数参数对话框 观察SUMIF函数的结果是全体一级考生的总分，此时公式显示“=SUMIF(C3:C57,=1,J3:J57)”，再将该公式补充完整即可，为“=SUMIF(C3:C57,=1,J3:J57)N2”。(5) 单击Sheet2工作表的B7单元格，打开财务函数FV函数对话框，并在相应的文本框中输入如图 352所示的参数，单击“确定”按钮。图 352 FV函数参数对话框 单击Sheet2工作表的E7单元格，打开财务函数PV函数对话框，并在相应的文本框中输入如图 353所示的参数，单击“确定”按钮。图 353 PV函数参数对话框(6) 略。(7) 略。3-6 图书订购信息表1. 操作要求(1) 在Sheet4中，使用条件格式将性别列中数据为“女”的单元格，字体颜色设置为红色、加粗显示。(2) 使用IF函数，根据Sheet1中的“图书订购信息表”中的“学号”列对“所属学院”列进行填充。要求：根据每位学生学号的第七位填充对应的“所属学院”。学号第七位为1“计算机学院”。 学号第七位为0“电子信息学院”。(3) 使用COUNTBLANK函数，对Sheet1中的“图书订购信息表”中的“订书种类数”列进行填充。注意：其中“1”表示该同学订购该图书，空格表示没有订购。将结果保存在Sheet1中的“图书订购信息表”中的“订书种类数”列。(4) 使用公式，对Sheet1中的“图书订购信息表”中的“订书金额（元）”列进行填充。计算方法：订书金额C语言*单价高等数学*单价大学语文*单价高等数学*单价。(5) 使用统计函数，根据Sheet1中的“图书订购信息表”中的数据，统计订书金额大于100元的学生人数，将结果保存在Sheet1中的M9单元格中。(6) 将Sheet1中的“图书订购信息表”复制到Sheet2，并对Sheet2进行自动筛选。 要求： 筛选条件为：“订书种类数”=3，“所属学院”“计算机学院”。将筛选结果保存在Sheet2中。注意：复制过程中，将标题项“图书订购信息表”连同数据一同复制。数据表必须顶格放置。(7) 根据Sheet1的“图书订购信息表”，创建一个数据透视图Chart1. 要求：显示每个学院图书订购的订书金额汇总情况。x坐标设置为“所属学院”。数据区域设置为“订购金额（元）”。求和项为“订购金额（元）”。将对应的数据透视表保存在Sheet3中。 2. 操作步骤如下：(1) 参考3-1中的步骤（1）。(2) 单击Sheet1的C3单元格，插入MID函数，打开该函数参数对话框，输入相应参数如图 354所示，双击C3单元格的填充柄，观察MID函数的结果1和0，公式显示“=MID(A3,7,1)”。图 354 MID函数参数对话框再在C3单元格中重新插入IF函数，在打开的IF函数参数对话框中输入相应参数如图 355所示（注意在英文输入法状态下输入条件Logical_test中的字符）。双击C3单元格的填充柄。图 355 IF函数参数对话框(3) 单击H3单元格，插入COUNTBLANK函数，打开该函数参数对话框，输入相应参数如图 356所示。观察该函数的结果（统计了空单元格数目），书的种类数为4，因此对H3单元格中已有公式进行运算，即为“=4 - COUNTBLANK(F3:G3)”，按回车键确认。拖动H3单元格的填充柄至H50。图 356 COUNTBLANK函数参数对话框(4) 在I3单元格中输入公式：“=D3*$L$3+E3*$L$4+F3*$L$5+G3*$L$6”（单元格名称也可通过鼠标单击该单元格输入，注意相对引用和绝对引用的区别），按回车键确认。双击I3单元格的填充柄。(5) 在M9单元格中插入COUNTIF函数，在该函数参数对话框中输入如图 357所示的参数。单击“确定”按钮。图 357 COUNTIF函数参数对话框(6) 选中Sheet1中A1:I50单元格区域，复制，单击Sheet2工作表的A1单元格，粘贴。单击A2:I50的任一单元格，选择“数据”“筛选”“自动筛选”命令，出现如图 358所示的下拉按钮。图 358 自动筛选下拉按钮单击“订书种类数”右侧的下拉按钮，选择“自定义”打开“自定义自动筛选方式”对话框，设置筛选条件（如图 359所示）。单击“确定”按钮。图 359 自定义自动筛选方式对话框单击“所属学院”右侧的下拉按钮，选择“计算机学院”。(7) 略。3-7 杭州电话用户情况表1. 操作要求(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中。注意：无须考虑是否删除或移动筛选条件。数据表必须顶格放置。(7) 根据Sheet1的结果，创建一个数据透视图Chart1。要求：显示每个区域所拥有的用户数量。 x坐标设置为“所在区域”。 计数项为“所在区域”。 将对相应的数据透视表保存在Sheet4中。2. 操作步骤如下：(1) 参考3-3中的步骤（1）。(2) 单击D2单元格，插入时间函数YEAR，在打开的YEAR函数参数对话框中输入如图 360所示的参数，得到年份2008。图 360 YEAR函数参数对话框(1) 再在该公式后面减去如图 361所示的计算所得的年份，按回车。（此时公式显示为“=YEAR(2008-11-15)-YEAR(C2)”）。双击D2单元格的填充柄填充该列数据。图 361 YEAR函数参数对话框(2) (3) 单击G2单元格，插入REPLACE函数，在打开的REPLACE函数参数对话框中输入如图 362所示的参数，单击“确定”按钮。双击G2单元格的填充柄填充该列的数据。图 362 REPLACE函数参数对话框(4) 单击H2单元格，插入AND函数，在打开的AND函数参数对话框中输入如图 363所示的参数，单击“确定”按钮。双击H2单元格的填充柄填充该列的数据。图 363 AND函数参数对话框(5) 单击Sheet2的B2单元格，插入统计函数COUNTIF，在打开的COUNTIF函数参数对话框中输入如图 364所示的参数（参数可通过鼠标拖拽或单击选中），单击“确定”按钮。图 364 COUNTIF函数参数对话框（求男性人数）单击Sheet2的B3单元格，插入统计函数COUNTIF，在打开的COUNTIF函数参数对话框中输入如图 365所示的参数（参数可通过鼠标拖拽或单击选中），单击“确定”按钮。 图 365 COUNTIF函数参数对话框（求年龄大于40人数）(6) 略。(7) 略。3-8 学生成绩表（体育成绩）1. 操作要求(1) 在Sheet1中，使用条件格式将“铅球成绩（米）”列中单元格数据按下列要求显示：数据大于9的单元格中字体的颜色设置为红色、加粗显示。数据介于7和9之间的单元格中字体的颜色设置为蓝色、加粗显示。数据小于7的单元格中字体的颜色设置为绿色、加粗显示。(2) 在Sheet1“学生成绩表”中，使用REPLACE函数和数组公式，将原学号变成新学号，同时将所得的新学号填入“新学号”列中。转变方法：在原学号的第四位后面加上“5”。例如：“2007032001”“20075032001”。(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单元格中。(5) 根据Sheet2中的贷款情况，使用财务函数对贷款偿还金额进行计算。 要求： 计算“按年偿还贷款金额（年末）”，并将结果填入到Sheet2的E2单元格中。 计算“第9个月贷款利息金额”，并将结果填入到Sheet2的E3单元格中。(6) 将Sheet1中的“学生成绩表”复制到Sheet3中，对Sheet3进行高级筛选。 要求：筛选条件为：“性别”男，“100米成绩（秒）”9.00。 将筛选结果保存在Sheet3中。注意：无须考虑是否删除或移动筛选条件。复制过程中，将标题项“学生成绩表”连同数据一同复制。数据表必须顶格放置。(7) 根据Sheet1中的“学生成绩表”，在Sheet4中创建一张数据透视表。要求：显示每种性别学生的合格与不合格总人数。行区域设置为“性别”。列区域设置为“结果1”。数据区域设置为“结果1”。计数项为“结果1”。2. 操作步骤如下：(1) 参考3-1中的步骤（1）。在打开的“条件格式”对话框中，按题目要求分别输入如图 366所示的条件。单击“格式”按钮，在打开的“单元格格式”对话框中，设置字体为红色、加粗，结果如图 367所示，再单击“添加”按钮，打开如图 368所示的条件2格式对话框，进行设置。同理单击“添加”按钮设置条件3。图 366 “条件1格式”对话框图 367 设置条件1后的对话框 图 368“条件2格式”对话框(2) 选中B3:B30单元格区域，插入REPLACE函数，打开REPLACE函数参数对话框，并在相应的文本框中输入如图 369所示的参数，然后按下Ctrl+Shift+Enter组合键。图 369 REPLACE函数参数对话框(3)分析IF嵌套函数，画出其流程图如图 370所示。 NYNY合格AND(D3=男,E314)合格不合格AND(D3=女,E316) 图 370 IF嵌套函数流程图 据此，则在F3单元格中输入公式：“=IF(AND(D3=男,E314),合格,IF(AND(D3=女,E37.5)合格不合格AND(D3=” 女”,G35.5)图 371 IF嵌套函数流程图据此，则在H3单元格中输入公式：“=IF(AND(D3=男,E35.5),合格,不合格)”。按回车确认，双击H3单元格的填充柄填充该列的数据。(4) 单击K4单元格，插入MIN函数，在MIN函数参数对话框中插入如图 372所示的参数，单击“确定”按钮。图 372 MIN函数参数对话框单击K5单元格，插入统计函数COUNTIF，在COUNTIF函数参数对话框中插入如图 373所示的参数，单击“确定”按钮。图 373 COUNTIF函数参数对话框 (5) 在Sheet2中的E2单元格中插入财务函数PMT，在打开的PMT 函数参数对话框中输入如图 374所示的参数，单击“确定”按钮。图 374 PMT 函数参数对话框 在Sheet2中的E3单元格中插入财务函数IPMT，在打开的IPMT 函数参数对话框中输入如图 375所示的参数，单击“确定”按钮。图 375 IPMT 函数参数对话框(6) 略。(7) 略。3-9 三月份销售统计表1. 操作要求：(1) 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。(2) 使用VLOOKUP函数，对Sheet1中的“三月份销售统计表”的“产品名称”列和“产品单价”列进行填充。要求：根据“企业销售产品清单”，使用VLOOKIP函数，将产品名称和产品单价填充到“三月份销售统计表”的“产品名称”列和“产品单价”列中。(3) 使用数组公式，计算Sheet1中的“三月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中。计算方法：销售金额产品单价*销售数量(4) 使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计”表中的总销售额，并将结果填入该表的“总销售额”列中。(5) 在Sheet1中，使用RANK函数，在“分部销售业绩统计”表中，根据“总销售额”对各部门进行排名，并将结果填入到“销售排名”列中。(6) 将Sheet1中的“三月份销售统计表”复制到Sheet2中，并对Sheet2进行高级筛选。要求：筛选条件为：“销售数量”=3、“所属部门”市场1、“销售金额”1000。 将筛选结果保存在Sheet2中。 注意：无须考虑是否删除或移动筛选条件。 复制过程中，将标题项“三月份销售统计表”连同数据一同复制。 数据表必须顶格放置。(7) 根据Sheet1的“三月份销售统计表”中的数据，新建一个数据透视图Chart1。要求：该图形显示每位经办人的总销售额情况。 x坐标设置为“经办人”。 数据区域设置为“销售金额”。 求和项为“销售金额”。将对相应的数据透视表保存在Sheet3中。2.操作步骤如下：(1) 参考3-3中的步骤（1）。(2) 单击G3单元格，插入VLOOKUP函数，在VLOOKUP函数参数对话框中输入如图 376所示的参数（注意Table_Arrray区域的绝对引用，可拖选选区后直接按F4功能键实现）。单击“确定”按钮。双击G3单元格的填充柄填充该列的数据。图 376 VLOOKUP函数参数对话框同理，单击H3单元格，插入VLOOKUP函数，在VLOOKUP函数参数对话框中输入如图 377所示的参数。单击“确定”按钮。双击H3单元格的填充柄填充该列的数据。图 377 VLOOKUP函数参数对话框(3) 先选中L3:L44单元格区域，再输入公式“= H3:H44*I3:I44”（各列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键，此时公式编辑栏显示“= H3:H44*I3:I44”。(4) 单击O3单元格，插入SUMIF函数，在SUMIF函数参数