Excel2010二级典型试题解析参考模板

1、3.1 Excel2010二级典型试题解析（*）3.2.1 衣物采购表1. 在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。2. 在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体设置为红色，加粗显示。3. 使用VLOOKUP函数，对Sheet1中的“采购表”的“单价”列进行填充。* 根据“价格表”中的商品单价，使用VLOOKUP函数，将其单价填充到采购表中的“单价”列中。* 函数中参数如果需要用到绝对地址的，请使用绝对地址进行答题，其他方式无效。4. 使用逻辑函数，对Sheet1“采购表”中的“折扣”列进行填充。要求：* 根据“折扣

2、表”中的商品折扣率，使用相应的函数，将其折扣率填充到采购表中的“折扣”列中。5. 使用公式，对Sheet1中“采购表”的“合计”列进行填充。* 根据“采购数量”，“单价”和“折扣”，计算采购的合计金额，将结果保存在“合计”列中。* 计算公式：单价*采购数量*（1折扣率）6. 使用SUMIF函数，计算各种商品的采购数量和采购总金额，将结果保存在Sheet1 “统计表”当中相应位置。7. 将Sheet1中的“采购表”复制到Sheet2，并对Sheet2进行高级筛选。（1）要求：*筛选条件为：“采购数量”>150，“折扣率”>0；*将筛选结果保存在Sheet2中。（2）注意：*无须考虑是

3、否删除或移动筛选条件；*复制过程中，将标题项“采购表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置；*复制过程中，保持数据一致。8. 根据Sheet1中“采购表”，新建一个数据透视图，保存在Sheet3中。要求：*该图形显示每个采购时间点所采购的所有项目数量汇总的情况；*x坐标设置为“采购时间”；*求和项为“采购数量”；*将对相应的数据透视表保存在Sheet3中。l 操作步骤如下：1. 步骤1：单击选择Sheet5的B1单元格，在“开始”选项卡中，单击编辑栏中的【插入函数】按钮，打开“插入函数”函数对话框，选择“全部”中的“ROUND”函数，如图 3102所示。（注意：在已知函

4、数具体名的情况下，可以在“搜索函数”文本框中输入函数名，然后单击【转到】按钮，即可快速找到函数。）1 / 34图 3102插入ROUND函数步骤2：单击【确定】按钮，打开“函数参数”对话框并设置参数，如图 3103所示。图 3103 ROUND函数参数对话框步骤3：单击【确定】按钮，完成设置。2. 步骤1：选中Sheet1的“采购数量”列的数据区域。切换到“开始”选项卡，单击“样式”选项组的【条件格式】按钮。步骤2：从弹出的菜单中选择“突出显示单元格规则”下的“大于”命令（如图 3104所示），打开“大于”对话框：输入条件“100”；单击“设置为”文本框右侧的下拉箭头，从弹出的子菜单里面选择“

5、自定义格式”（如图 3105所示），打开“设置单元格格式”对话框。图 3104 “条件格式”菜单图 3105 设置“大于”对话框步骤3：在“设置单元格格式”对话框中，切换到“字体”选项卡，选择字形和颜色，如图 3106所示。图 3106“设置单元格格式”对话框步骤4：单击【确定】按钮返回“大于”对话框，再单击【确定】按钮完成设置。3. 选中D11单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择函数VLOOKUP。单击【确定】按钮打开VLOOKUP函数参数对话框，在相应的文本框中输入如图 3107所示的参数（注意：Table_Arrray区域的绝对引用。可在拖选选区后直接按F4功

6、能键快速实现输入）。单击【确定】按钮。双击D11单元格的填充柄填充该列的数据。图 3107 VLOOKUP函数参数对话框4. 根据题意分析IF嵌套函数，画出其流程图如图 3108所示。 NNYYNY折扣率为B3单元格所示的（绝对引用）B11<100B11<200折扣率为B4单元格所示的（绝对引用）折扣率为B5单元格所示的（绝对引用）B11<300折扣率为B6单元格所示的（绝对引用）图 3108 IF嵌套函数流程图据此，则在E11单元格中输入公式：“=IF(B11<100,$B$3,IF(B11<200,$B$4,IF(B11<300,$B$5,$B$6)”。

7、按回车确认。双击E11单元格的填充柄填充该列的数据。（注意：如果数据格式不对，单击“开始”选项卡的“数字选项组”中的【百分比】按钮即可。）5. 在F11单元格中输入公式“=B11*D11*(1-E11)“，按回车确认，双击F11单元格填充柄。（注意：如果单元格中出现“#”，表示该列宽度不足，无法显示数据。只需加宽该列即可）6. 计算采购数量：单击J12单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择SUMIF函数，单击“确定”打开SUMIF函数参数对话框，并在相应的文本框中输入如图 3109所示的参数(注意绝对引用和相对引用的使用)。双击J12单元格的填充柄。图 3109 SU

8、MIF函数参数对话框计算采购总金额：同理，在K12单元格中插入函数，设置SUMIF函数参数如图 3110所示，单击【确定】按钮。双击K12单元格的填充柄。图 3110 SUMIF函数参数对话框7. 步骤1：复制表格。选择Sheet1的采购表，按下“Ctrl+C”；将光标定位在Sheet2的A1单元格，按下“Ctrl+v”（注意：此时复制的数据会出现一个出错信息，如图 3111所示）。单击【确定】按钮。（注意：由于包含了公式的循环引用，此时复制的数据有错误。）图 3111“循环引用警告”对话框选择Sheet1采购表中的计算过的数据区域（即“单价”、“折扣”、“合计”列下方的数据区域），按下“Ct

9、rl+C”；将光标定位在Sheet2的错误的数据区域的起始单元格（即“单价”下方的单元格），单击鼠标右键，在弹出的快捷菜单中选择“选择性粘贴值和数字格式”（如图 3112所示）。图 3112选择性粘贴（注意：当某单元格出现“”时，表示单元格宽度不够，此时可调整单元格的宽度以看到完整的单元格数据）步骤2：建立高级筛选的条件区域。在数据区域的下方，根据题目要求建立条件区域，如图 3113所示。（建议直接复制表格中的相关字段）。图 3113高级筛选的条件区域步骤3：高级筛选。单击数据区域中的任一单元格，然后切换功能区的“数据”选项卡，在“排序和筛选”选项组中单击【高级】按钮，打开“高级筛选”对话框。

10、此时，“列表区域”的文本框中已自动填入所有数据区域。再把光标定位在“条件区域”文本框内，拖动鼠标选中条件区域（如图 3114所示），单击【确定】按钮完成设置。图 3114选择条件区域8. 步骤1：将光标定位在Sheet1工作表数据区域中的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视图”命令（如图 3115所示），打开“创建数据透视表及数据透视图”对话框。图 3115选择“数据透视表”菜单图 3116 “创建数据透视表及数据透视图”对话框此时，在“选择一个表或区域”单选按钮下方的“表/区域”文本框中自动填入了表格的数据区域

11、，如图 3116上方所示。步骤2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文本框中，单击Sheet3工作表标签切换到Sheet3工作表，并单击A1单元格，如图 3116下方所示。步骤3：单击【确定】按钮，进入数据透视表及数据透视图设计环境：从“选择要添加到报表的字段”列表框中，将“采购时间”拖到“轴字段”框中；将“采购数量”拖到“数值”框中，如图 3117所示。最后的效果图见图 3118。图 3117设置数据透视图（表）字段列表图 3118数据透视图（表）效果图3.2.2 教材订购情况表1. 在Sheet5的A1单元格中设置只能录入5位数字或文本。当录入位数错误时，提示错误原因，

12、样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 在Sheet5的B1单元格中输入分数1/3。3. 使用数组公式，对Sheet1中“教材订购情况表”的订购金额进行计算。*将结果保存在该表的“金额”列当中。*计算方法：金额订数*单价。4. 使用统计函数，对Sheet1中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置。要求：*统计出版社名称为“高等教育出版社”的书的种类数，并将结果保存在Sheet1中的L2单元格中。*统计订购数量大于110且小于850的书的种类数，并将结果保存在Sheet1中的L3单元格中。5. 使用函数，计算每个用户所订购图书所需支

13、付的金额总数，并将结果保存在Sheet1中的“用户支付情况表”的“支付金额”列中。6. 使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”；如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。*闰年定义，年数能被4整除而不能被100整除，或者能被400整除的年份。7. 将Sheet1中的“教材订购情况表”复制到Sheet3，并对Sheet3进行高级筛选。（1）要求：*筛选条件为“订数>=500，且金额总数<=30000”；*将结果保存在Sheet3中。（2）注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“教材订购情况表”连同数据一同复

14、制；*数据表必须顶格放置；*复制过程中，数据保持一致。8. 根据Sheet1中“教材订购情况表”的结果，在Sheet4中新建一张数据透视表。要求：*显示每个客户在每个出版社所订的教材数目；*行区域设置为“出版社”；*列区域设置为“客户”；*求和项为订数；*数据区域设置为“订数”。l 操作步骤如下：1. 步骤1：选中Sheet5工作表中的A1单元格，切换到功能区的“数据”选项卡，单击“数据工具”选项组中的“数据有效性”的上半部按钮，打开“数据有效性”对话框。步骤2：切换到“设置”选项卡：选择“允许”下拉菜单为“文本长度”；选择“数据”下拉菜单为“等于”；并在“长度”文本框中输入“5”，如图 31

15、19所示。图 3119设置数据有效性步骤3：再切换到“出错警告”选项卡：选择“样式”下拉菜单为“警告”；在“错误信息”文本框中输入“只能录入5位数字或文本”，如图 3120所示，单击【确定】按钮完成设置。图 3120设置出错信息2. 在Sheet5的B1单元格中，输入“0 1/3”（注意：0和1中间以空格间隔）。3. 在Sheet1工作表中，先选中“金额”列的数据区域，再输入公式“=G3:G52*H3:H52”（注意：各列数据区域建议用鼠标拖拽选取），然后按下“Ctrl+Shift+Enter”组合键即可。4. 步骤1：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“

16、统计”函数中的COUNTIF函数，单击【确定】按钮打开COUNTIF函数参数对话框，并在相应的文本框中输入如图 3121所示的参数（注意：Range文本框中的单元格区域可拖拽鼠标进行输入；Criteria文本框中的文本可直接单击“高等教育出版社”所在的单元格进行输入），单击【确定】按钮。图 3121 COUNTIF函数参数对话框步骤2：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数中的COUNTIFS函数，单击“确定”打开COUNTISF函数参数对话框，并在相应的文本框中输入如图 3122所示的参数（注意：Criteria_Range各文本框中的单元格区域

17、可拖拽鼠标进行输入），单击【确定】按钮。图 3122 COUNTIFS函数参数对话框5. 选中L8单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“数学与三角函数”函数中的SUMIF函数，单击【确定】按钮打开SUMIF函数参数对话框，并在相应的文本框中输入如图 3123所示的参数（注意：绝对引用可在鼠标拖选相应的单元格区域后，按F4功能键快速实现输入）。双击L8单元格的填充柄。图 3123SUMIF函数参数对话框6. 步骤1：根据题意，闰年条件分析如错误!未找到引用源。23所示。两个条件满足其中之一OR(AND(MOD(A2,4)=0, MOD(A2,100)<>0

18、), 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图 3124闰年条件分析步骤2：选中Sheet2工作表的B2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“逻辑”函数中的IF函数，单击【确定】按钮打开IF函数参数对话框，并在相应的文本框中输入如图 3124所示的参数（Logical_test文本框中的参数为“OR(AND(MOD(A2,4)=0, MOD(A2,100)&l

19、t;>0), MOD(A2,400)=0)”）。双击B2单元格的填充柄。图 3124 IF函数参数对话框7. 参考3.2.1中的题7。8. 可参考3.2.1中的题8。步骤1：将光标定位在Sheet1数据区域的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视表”命令（如图 3125所示），打开打开“创建数据透视表”对话框。图 3125 “数据透视表”菜单此时，在“选择一个表或区域”单选按钮下方的“表/区域”文本框中自动填入了表格的数据区域。步骤2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文本框中，单击She

20、e4工作表标签切换到Sheet4工作表，并单击A1单元格。步骤3：单击【确定】按钮，进入数据透视表设计环境：从“选择要添加到报表的字段”列表框中，将“出版社”拖到“行标签”框中；将“客户”拖到“列标签”框中；将“定数”拖到“数值”框中，如图 3126所示。最后的效果图见图 3127。图 3126设置数据透视表字段列表图 3127数据透视表效果图3.2.3 公务员考试成绩表1. 在Sheet5的A1单元格中输入分数1/3。2. 在Sheet1中，使用条件格式将“性别”列中为“女”的单元格中字体颜色设置为红色、加粗显示。3. 使用IF函数，对Sheet1中“学位”列进行自动填充。要求：填充的内容根

21、据“学历”列的内容来确定（假定学生均已获得相应学位）*博士研究生博士*硕士研究生硕士*本科学士*其他无。4. 使用数组公式，在Sheet1中计算：计算笔试比例分，并将结果保存在“公务员考试成绩表”中的“笔试比例分”中。*计算方法为：笔试比例分（笔试成绩/3）*60%计算面试比例分，并将结果保存在“公务员考试成绩表”中的“面试比例分”中。*计算方法为：面试比例分面试成绩*40%计算总成绩，并将结果保存在“公务员考试成绩表”中的“总成绩”中。* 计算方法：总成绩笔试比例分+面试比例分5. 将Sheet1中的“公务员考试成绩表”复制到Sheet2，根据以下要求修改“公务员考试成绩表”中的数组公式，并

22、将结果保存在Sheet2的相应列中。要求：*修改“笔试比例分”的计算，计算方法为：笔试比例分（笔试成绩/2）*60%，并将结果保存在“笔试成绩比例分”列中。注意：*复制过程中，将标题项“公务员考试成绩表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。6. 在Sheet2中，使用函数，根据“总成绩”列对所有考生进行排名。（如果多个数值排名相同，则返回该组数值的最佳排名）*要求：将排名结果保存在“排名”列中。7. 将Sheet2中的“公务员考试成绩表”复制到Sheet3，并对Sheet3进行高级筛选。（1）要求：*筛选条件为：“报考单位” 一中院、“性别”男、“学历”硕士研究生；*

23、将筛选结果保存在Sheet3中。（2）注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“公务员考试成绩表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。8. 根据Sheet2中的“公务员考试成绩表”，在Sheet4中创建一张数据透视表。要求：*显示每个报考单位的人的不同学历的人数汇总情况；*行区域设置为“报考单位”；*列区域设置为“学历”；*数据区域设置为“学历”；*计数项为学历。l 操作步骤如下：1. 参考3.2.2中的题2。2. 参考3.2.1中的题2。3. 步骤1：根据题意，分析IF嵌套函数，画出其流程图如图 3128所示。 NNYYNY博士G3=”博研”生

24、”<100G3=”硕研”硕士学士G3=”本科”无图 3128 IF嵌套函数流程图步骤2：根据以上流程图，在H3单元格中输入公式：“=IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无")”（注意：务必在英文输入法状态下输入字符，仅中文字符例外；且括号要配对）。按回车确认。双击H3单元格的填充柄填充该列的数据。4. 选中“笔试比例分”列的数据区域，输入公式“=I3:I18/3*0.6”，（各

25、列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键。同理，选中“面试比例分”列的数据区域，输入公式“=I3:I18*0.4”，同上。 同理，选中“总成绩”列的数据区域，输入公式“=J3:J18+L3:L18”，同上。5. 步骤1：选中Sheet1工作表中的数据区域，复制；单击Sheet2工作表的A1单元格，右键单击选择“粘贴选项”的第一项。步骤2：在“笔试成绩比例分”中修改公式“=I3:I18/2*0.6”，按下Ctrl+Shift+Enter组合键。6. 选中Sheet2工作表的N3单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择RANK.EQ函数，

26、单击【确定】按钮打开RANK.EQ函数参数对话框：在相应的文本框中输入如图 3129所示的参数（注意使用绝对引用，用鼠标拖选选区后，直接按下F4功能键快速实现输入）。按回车确认。双击N3单元格的填充柄填充该列的数据。图 3129 RANK.EQ函数参数对话框7. 参考3.2.1中的题7。8. 参考3.2.2中的题8。3.2.4 杭州电话用户情况表1. 在Sheet5的A1单元格中设置只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 在Sheet5的B1单元格中输入公式，判断当年是否为闰年，结果为TRUE或FALSE。*闰年定义：

27、年数能被4整除而不能被100整除，或者能被400整除的年份。3. 使用时间函数，对Sheet1中用户的年龄进行计算。要求：*假设当前时间是“2013-5-1”，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”列当中。计算方法为两个时间年份之差。4. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。要求：*对“原电话号码”列中的电话号码进行升级。升级方式是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。*例如：电话号码“05716742808”升级后为“057186742808”。5. 在Sheet1中，使用AND函数，根据

28、“性别”及“年龄”列中的数据，判断所有用户是否为大于等于40岁的男性，并将结果保存在“是否>=40男性”列中。*注意：如果是，保存结果为TRUE；否则，保存结果为FALSE。6. 根据Sheet1中的数据，对以下条件，使用统计函数进行统计。要求： *统计性别为“男”的用户人数，将结果填入到Sheet2的B2单元格中。*统计年龄为“>40”岁的用户人数，将结果填入到Sheet2的B3单元格中。7. 将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选。（1）要求：*筛选条件为：“性别”女，“所在区域”西湖区；*将筛选结果保存在Sheet3中。（2）注意：*无须考虑是否删除

29、或移动筛选条件；*复制数据表后，粘贴时，数据表必须顶格放置。8. 根据Sheet1的结果，创建一个数据透视图，保存在Sheet4中。要求：*显示每个区域所拥有的用户数量；*x坐标设置为“所在区域”；*计数项为“所在区域”；*将对应的数据透视表保存在Sheet4中。l 操作步骤如下：1. 参考3.2.2中的题1。2. 参考3.2.2中的题6。此处输入公式“=OR(AND(MOD(YEAR(NOW(),4)=0, MOD(YEAR(NOW(),100)<>0), MOD(YEAR(NOW(),400)=0)”。（注意：NOW函数无参数，只有括号）3. 步骤1：单击Sheet的D2单元格

30、，插入时间函数YEAR，在打开的YEAR函数参数对话框中，输入如图 3130所示的参数，得到年份2013。图 3130 YEAR函数参数对话框(1)步骤2：再在该公式后面减去如所图 3131示的计算所得的年份，按回车。（此时公式显示为“=YEAR("2013-5-1")-YEAR(C2)”）。按回车，再填充公式。图 3131 YEAR函数参数对话框(2)4. 选择Sheet的G2单元格，插入REPLACE函数，在打开的REPLACE函数参数对话框中输入如图 3132所示的参数，单击【确定】按钮。双击G2单元格的填充柄填充该列的数据。图 3132 REPLACE函数参数对话框

31、5. 选择Sheet1的H2单元格，插入AND函数，在打开的AND函数参数对话框中输入如图 3133所示的参数，单击【确定】按钮。双击H2单元格的填充柄填充该列的数据。图 3133 AND函数参数对话框6. 参考3.2.2中的题4。7. 参考3.2.1中的题7。8. 参考3.2.1中的题8。3.2.5 停车情况记录表1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 在Sheet4的B1单元格中输入公式，判断当前年份是否为闰年，结果为TURE或FALSE.* 闰年定义：年数能被4整除而不能被

32、100整除，或者能被400整除的年份。3. 使用HLOOKUP函数，对Sheet1“停车情况记录表”中的“单价”列进行填充。要求：*根据Sheet1中的“停车价目表”价格，使用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行填充。注意*函数中如果需要用到绝对地址的请使用绝对地址进行计算，其他方式无效。4. 在Sheet1中，使用时间函数计算汽车在停车库中的停放时间。要求：*计算方法为：“停放时间出库时间入库时间”*格式为：“小时：分钟：秒”*将结果保存在“停车情况记录表”中的“停放时间”列中*例如：一小时十五分十二秒在停放时间中的表示为：“1:15:12”。5. 使用函数

33、公式，对“停车情况记录表”中的停车费用进行计算。要求：*根据Sheet1停放时间的长短计算停车费用，将计算结果填入到“停车情况记录表”中的“应付金额”列中。注意：*停车按小时收费，对于不满一个小时的按照一个小时计费；*对于超过整点小时数十五分钟（包含十五分钟）的，多累积一个小时；*例如：1小时23分，将以2小时计费。6. 使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计。*统计停车费用大于等于40元的停车记录条数，并将结果保存在J8单元格中；*统计最高的停车费用，并将结果保存在J9单元格中。7. 将Sheet1中的“停车情况记录表”复制到Sheet2中，对Sheet2进行

34、高级筛选。要求：*筛选条件为：“车型”小汽车，“应付金额”>=30；*将结果保存在Sheet2中。注意：*无须考虑是否删除筛选条件；*复制过程中，将标题项“停车情况记录表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。8. 根据Sheet1中的“停车情况记录表”，创建一个数据透视图，保存在Sheet3中。要求：*显示各种车型所收费用的汇总；*x坐标设置为“车型”；*求和项为“应付金额”；*将对相应的数据透视表保存在Sheet3中。l 操作步骤如下：1. 参考3.2.2中的题1。2. 参考3.2.4中的题2。3. 单击Sheet1工作表的C9单元格，插入HLOOKUP函数，

35、在HLOOKUP函数对话框中输入如图 3134所示的参数（注意Table_Arrray区域的绝对引用，可拖选选区后直接按F4功能键实现快速输入）。单击【确定】按钮。双击C9单元格的填充柄填充该列的数据。图 3134 HLOOKUP函数对话框4. 单击F9单元格，输入公式“=E9-D9”，按回车确认。双击F9单元格的填充柄。5. 步骤1：根据题意，分析IF嵌套函数，画出其流程图如图 3135所示。 NYNY1HOUR(F9)=0HOUR(F9)+1HOUR(F9)MINUTE(F9)>=155图 3135 IF嵌套函数流程图步骤2：据根据以上分析，在G9单元格输入公式：“=IF(HOUR(

36、F9)=0,1,IF(MINUTE(F9)>=15,HOUR(F9)+1,HOUR(F9)”，再乘以C9。按回车确认，双击G9单元格的填充柄填充该列的数据。6. 步骤1：参考3.2.2中的题4。步骤2：单击J9单元格，插入MAX函数，在MAX函数参数对话框中输入如图 3136所示的参数，单击【确定】按钮。图 3136MAX函数参数对话框7. 参考3.2.1中的题7。（注意：复制“三月份销售统计表”的时候注意选择使用“粘贴选项”中的“值”。）8. 参考3.2.1中的题8。3.2.6 语数英成绩表1. 在Sheet5中使用多个函数组合，计算A1:A10中奇数的个数，结果存放在B1单元格中。2

37、. 在Sheet1中，使用条件格式将“语文”列中数据大于80的单元格中，字体颜色设置为红色、加粗显示。3. 使用数组公式，根据Sheet1中的数据，计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。4. 使用函数，根据Sheet1中的“总分”列，对每个同学排名情况进行统计，并将结果保存到表中的“排名”列当中。（若有相同排名，返回最佳排名）5. 使用逻辑函数，判断Sheet1中每个同学的每门功课是否均高于全班单科平均分。要求*如果是，保存结果为TRUE；否则，保存结果为FALSE；*将结果保存在表中的“优等生”列当中；注意*优等生条件：每门功课均高于全班单科平均分。6. 根

38、据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2的相应位置。7. 将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选。 要求：*筛选条件为：“语文”>=75，“数学”>=75，“英语”>=75，“总分”>=250；*将结果保存在Sheet3中。注意：*无须考虑是否删除筛选条件；*复制数据表后，粘贴时，数据表必须顶格放置。8. 根据Sheet1中的结果，在Sheet4中创建一张数据透视表。要求：*显示是否为优等生的学生人数汇总情况；*行区域设置为“优等生”；*数据区域设置为“优等生”；*计数项为优等

39、生。l 操作步骤如下：1. 单击选择Sheet5中的B1单元格，输入公式：“=SUMPRODUCT(MOD(A1:A10,2)”。2. 参考3.2.1中的题2。3. 步骤1：选中“总分”列的数据区域，输入公式“=C2:C39+D2:D39+E2:E39”（各列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键。步骤2：选中“平均分”列的数据区域，输入公式“=F2:F39/3”（各列数据区域可用鼠标拖拽选取），然后按下Ctrl+Shift+Enter组合键。4. 参考3.2.3中的题6。5. 步骤1：根据题意，条件分析如图 3137所示（注意求各科平均分时相应单元格区域的

40、绝对引用，注意括号的配对）。三个条件同时满足AND(C2>AVERAGE($C$2:$C$39), D2>AVERAGE($D$2:$D$39), E2>AVERAGE($E$2:$E$39)语文大于语文平均分C2>AVERAGE($C$2:$C$39)数学大于数学平均分D2>AVERAGE($D$2:$D$39)英语大于英语平均分E2>AVERAGE($E$2:$E$39)图 3137条件分析步骤2：根据以上分析，在I2单元格中插入逻辑函数AND，在AND函数参数对话框中输入图 3138中所示的3个条件。单击【确定】按钮。双击I2单元格的填充柄。图 313

41、8 AND函数参数对话框6. 单击Sheet2的B2单元格，插入COUNTIFS函数，在打开的COUNTIFS函数参数对话框中输入如图 3139所示的参数（注意使用绝对引用）。图 3139 COUNTIFS函数参数对话框其余同理，只需按题意更改Criteiral中的条件即可。（为简单起见，可复制公式后直接修改条件）。7. 参考3.2.1中的题7。8. 参考3.2.2中的题8。3.2.7房产销售表1. 在Sheet5的A1单元格中设置只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 在Sheet1中，使用条件格式将“预订时期”列中

42、日期为2008-4-1后的单元格中字体颜色设置为红色、加粗显示。对C列，设置“自动调整列宽”。3. 使用公式，计算Sheet1中“房产销售表”的房价总额，并保存在“房产总额”列中。* 计算公式为：房产总额面积*单价。4. 使用数组公式，计算Sheet1中“房产销售表”的契税总额，并保存在“契税总额”列中。* 计算公式为：契税总额契税*房产总额。5. 使用函数，根据Sheet1中“房产销售表”的结果，在Sheet2中统计每个销售人员的销售总额，将结果保存在Sheet2的“销售总额”列中。6. 使用函数，根据Sheet2中“销售总额”列的结果，对每个销售人员的销售情况进行排序，并将结果保存在“排名

43、”列当中。（若有相同排名，返回最佳排名）7. 将Sheet1的“房产销售表”复制到Sheet3中，并对Sheet3进行高级筛选。要求：*筛选条件为：“户型”为两室一厅，“房价总额”>1000000；*将筛选结果保存在Sheet3中。注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“房产销售表”连同数据一同复制；*数据表必须顶格放置。8. 根据Sheet1中“房产销售表”的结果，创建一个数据透视图，保存在Sheet4中。要求：*显示每个销售人员所销售房屋应缴纳契税总额汇总情况；*x坐标设置为“销售人员”；*数据区域为“契税总额”；*求和项设置为契税总额；*将对应的数据透视表也

44、保存在Sheet4中。l 操作步骤如下：1. 参考3.2.2中的题1。2. 步骤1：参考3.2.1中的题2。步骤2：选中C列，切换到“开始选项卡”，单击“单元格”选项组中的【格式】按钮，在弹出的下拉菜单中选择“自动调整列宽”，如图 3140所示。图 3140自动调整列宽3. 在“房产总额”列的I3单元格中，输入公式“=F3*G3”，按回车确认。双击I3单元格的填充柄。4. 选中“契税总额”列的数据区域，输入公式“=H3:H26*I3:I26”， 按下Ctrl+Shift+Enter组合键。5. 单击Sheet2的B2单元格，插入SUMIF函数，在其参数对话框中输入如图 3141所示的参数，（注

45、意绝对引用和相对引用的使用），单击【确定】按钮。双击B2单元格的填充柄。图 3141 SUMIF函数参数对话框6. 参考3.2.3中的题6。7. 参考3.2.1中的题7。8. 参考3.2.1中的题8。3.2.8 温度情况表1. 在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 在Sheet5中，使用函数，根据A2单元格中的身份证号码判断性别，结果为“男”或“女”，存放在B2单元格中。*倒数第二位为奇数的为“男”，为偶数的为“女”。3. 使用IF函数，对Sheet1“温度情况表”中的“温度较高的城

46、市”列进行填充，填充结果为城市名称。4. 使用数组公式，对Sheet1“温度情况表”中的相差温度值（杭州相对于上海的温度）进行计算，并把结果保存在“相差温度值”列中。*计算方法：相差温度值杭州平均气温上海平均气温。5. 使用函数，根据Sheet1“温度情况表”中的结果，对符合以下条件的进行统计。要求：*杭州这半个月以来的最高气温和最低气温，保存在相应单元格中。*上海这半个月以来的最高气温和最低气温，保存在相应单元格中。6. 将Sheet1中的“温度情况表”复制到Sheet2中，在Sheet2中，重新编辑数组公式，将Sheet2中的“相差的温度值”中的数值取其绝对值（均为正数）。注意：*复制过程

47、中，将标题项“温度情况表”连同数据一同复制；*数据表必须顶格放置。7. 将Sheet2中的“温度情况表”复制到Sheet3中，并对Sheet3进行高级筛选。要求：*筛选条件：“杭州平均温度”>=20，“上海平均温度”<20*将筛选结果保存在Sheet3中。注意：*无须考虑是否删除筛选条件；*复制过程中，将标题项“温度情况表”连同数据一同复制；*数据表必须顶格放置。8. 根据Sheet1中“温度情况表”的结果，在Sheet4中创建一张数据透视表。要求：*显示温度较高天数的汇总情况；*行区域设置为“温度较高的城市”；*数据区域设置为“温度较高的城市”；*计数项为“温度较高的城市”。l

48、操作步骤如下：1. 参考3.2.2中的题1。2. 单击Sheet5的B2单元格，输入公式“=IF(MOD(MID(A2,17,1),2)=1,"男","女")”。3. 单击Sheet1的D3单元格，插入IF函数，在其参数对话框中输入如图 3142所示的参数，单击【确定】按钮。双击C2单元格的填充柄。图 3142 IF函数参数对话框4. 选择“相差温度值”列的数据区域，输入公式：“=B3:B17-C3:C17”，按下Ctrl+Shift+Enter组合键。5. 单击C19单元格，插入MAX函数，在其参数对话框中输入如图 3143所示的参数，单击【确定】按钮

49、。图 3143 MAX函数参数对话框同理，依次插入MIN(B3:B17)，MAX(C3:C17)，MIN(C3:C17)。6. 复制粘贴完毕，把Sheet2的E3单元格中的公式修改为：“=ABS(B3:B17-C3:C17)”，按下Ctrl+Shift+Enter组合键。7. 参考3.2.1中的题7。8. 参考3.2.1中的题8。3.2.9员工信息表1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 在Sheet4中，使用函数，将B1中的时间四舍五入到最接近的15分钟的倍数，结果存放在C1单

50、元格中。3. 使用REPLACE函数，对Sheet1中“员工信息表”的员工代码进行升级。要求：*升级方法：在PA后面加上0；*将升级后的员工代码结果填入表中的“升级员工代码”列中；*例如：PA125，修改后PA0125。4. 使用时间函数，计算Sheet1中“员工信息表”的“年龄”列和“工龄”列。要求：*假设当前时间是“2013-5-1”，结合表中的“出生年月”、“参加工作时间”列，对员工“年龄”和“工龄”进行计算；*计算方法为两年份之差。并将结果保存到表中的“年龄”列和“工龄”列中。5. 使用统计函数，根据Sheet1中“员工信息表”的数据，对以下条件进行统计。*统计男性员工的人数，结果填入

51、N3单元格中；*统计高级工程师人数，结果填入N4单元格中；*统计工龄大于等于10的人数，结果填入N5单元格中。6. 使用逻辑函数，判断员工是否有资格评“高级工程师”。要求：*评选条件为：工龄大于20，且为工程师的员工；*并将结果保存在“是否有资格评选高级工程师”列中；*如果有资格，保存结果为TRUE；否则为FALSE。7. 将Sheet1中“员工信息表”复制到Sheet2中，并对Sheet2进行高级筛选。要求：*筛选条件为：“性别”男，“年龄”>30，“工龄”>=10，“职称”助工；*将结果保存在Sheet2中。注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“员工信

52、息表”连同数据一同复制；*数据表必须顶格放置。8. 根据Sheet1中的数据，创建一个数据透视图，保存在Sheet3中。要求：*显示工厂中各种职称人数的汇总情况；*x坐标设置为“职称”；*计数取为职称；*数据区域为“职称”；*将对应的数据透视表也保存在Sheet3中。l 操作步骤如下：1. 参考3.2.2中的题1。2. 在Sheet4的B1单元格中，输入函数“=TIME(HOUR(B1),15*ROUND(MINUTE(B1)/15,0),SECOND(0)”。3. 参考3.2.4中的题4。4. 参考3.2.4中的题3。此时默认的数字格式为“日期”。在“开始”选项卡的“数字”选项组中，单击“日期”右侧的下拉箭头，在弹出的菜单中选择“常规”即可。5. 参考3.2.2中的题4。6. 参考3.2.4中的题5。7. 参考3