Excel2010二级典型试题解析.pdf

《Excel2010二级典型试题解析.pdf》由会员分享，可在线阅读，更多相关《Excel2010二级典型试题解析.pdf（32页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、，其他方式无效。4. 4.使用逻辑函数，对 Sheet1“采购表”中的“折扣”列进行填充。要求：* 根据“折扣表”中的商品折扣率，使用相应的函数，将其折扣率填充到采购表中的“折扣”列中。5. 5.使用公式，对 Sheet1 中“采购表”的“合计”列进行填充。* 根据“采购数量” ， “单价”和“折扣” ，计算采购的合计金额，将结果保存在“合计”列中。* 计算公式：单价*采购数量*（1折扣率）6. 6.使用 SUMIF 函数，计算各种商品的采购数量和采购总金额，将结果保存在Sheet1 “统计表”当中相应位置。7. 7.将 Sheet1 中的“采购表”复制到Sheet2，并对 Sheet2 进行

3、高级筛选。（1）要求：*筛选条件为： “采购数量”150， “折扣率”0；*将筛选结果保存在 Sheet2 中。（2）注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“采购表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置；*复制过程中，保持数据一致。8. 8.根据 Sheet1 中“采购表” ，新建一个数据透视图，保存在Sheet3 中。要求：*该图形显示每个采购时间点所采购的所有项目数量汇总的情况；*x 坐标设置为“采购时间” ；*求和项为“采购数量” ；*将对相应的数据透视表保存在 Sheet3 中。操作步骤如下：操作步骤如下：1. 1.步骤步骤 1 1：单击

4、选择 Sheet5 的 B1 单元格，在“开始”选项卡中，单击编辑栏中的【插入函数】按钮，打开“插入函数”函数对话框，选择“全部”中的“ ROUND”函数，如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-1所示。 （注意：在已知函数具体名的情况下，可以在“搜索函数”文本框中输入函数名，然后单击【转到】按钮，即可快速找到函数。 ）图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-1 插入 ROUND 函数步骤步骤 2 2： 单击 【确定】 按钮， 打开 “函数参数” 对话框并设置参数， 如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样

5、式的文字。 -2所示。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-2 ROUND 函数参数对话框步骤步骤 3 3：单击【确定】按钮，完成设置。2. 2.步骤步骤 1 1：选中 Sheet1 的“采购数量”列的数据区域。切换到“开始”选项卡，单击“样式”选项组的【条件格式】按钮。步骤步骤 2 2：从弹出的菜单中选择“突出显示单元格规则”下的“大于”命令（如图 错误错误! !文档中没有指定样式文档中没有指定样式的文字。的文字。-3 所示） ，打开“大于”对话框：输入条件“100” ；单击“设置为”文本框右侧的下拉箭头，从弹出的子菜单里面选择“自定义格式” （如图 错误错

6、误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-4 所示） ，打开“设置单元格格式”对话框。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-3 “条件格式”菜单图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-4 设置“大于”对话框步骤步骤 3 3：在“设置单元格格式”对话框中，切换到“字体”选项卡，选择字形和颜色，如图 错误错误! !文档中没文档中没有指定样式的文字。有指定样式的文字。-5 所示。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-5“设置单元格格式”对话框步骤步骤 4 4：单击【确定】按钮返回“

7、大于”对话框，再单击【确定】按钮完成设置。3. 3.选中 D11 单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择函数VLOOKUP。单击【确定】按钮打开 VLOOKUP 函数参数对话框，在相应的文本框中输入如图 错误错误! !文档中没有指定样式的文档中没有指定样式的文字。文字。-6 所示的参数（注意：Table_Arrray区域的绝对引用。可在拖选选区后直接按F4 功能键快速实现输入） 。单击【确定】按钮。双击D11 单元格的填充柄填充该列的数据。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-6 VLOOKUP 函数参数对话框4. 4.根据题意分析

8、IF 嵌套函数，画出其流程图如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-7 所示。B11100NB11200NB11300N折扣率为 B6 单元格所示的（绝对引用）Y折扣率为 B3 单元格所示的（绝对引用）Y折扣率为 B4 单元格所示的（绝对引用）Y折扣率为 B5 单元格所示的（绝对引用）图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-7IF 嵌套函数流程图据此，则在 E11 单元格中输入公式： “=IF(B11100,$B$3,IF(B11200,$B$4,IF(B11=500，且金额总数=30000” ；*将结果保存在 Sheet3 中

9、。（2）注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“教材订购情况表”连同数据一同复制；*数据表必须顶格放置；*复制过程中，数据保持一致。8. 8.根据 Sheet1 中“教材订购情况表”的结果，在Sheet4 中新建一数据透视表。要求：*显示每个客户在每个所订的教材数目；*行区域设置为“” ；*列区域设置为“客户” ；*求和项为订数；*数据区域设置为“订数” 。操作步骤如下：操作步骤如下：1. 1.步骤步骤 1 1：选中 Sheet5 工作表中的 A1 单元格，切换到功能区的“数据”选项卡，单击“数据工具”选项组中的“数据有效性”的上半部按钮，打开“数据有效性”对话框。步骤

10、步骤 2 2：切换到“设置”选项卡：选择“允许”下拉菜单为“文本长度”；选择“数据”下拉菜单为“等于”；并在“长度”文本框中输入“5” ，如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-18 所示。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-18 设置数据有效性步骤步骤 3 3：再切换到“出错警告”选项卡：选择“样式”下拉菜单为“警告” ；在“错误信息”文本框中输入“只能录入 5 位数字或文本” ，如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-19 所示，单击【确定】按钮完成设置。图 错误错误! !文档中没有指定

11、样式的文字。文档中没有指定样式的文字。-19 设置出错信息2. 2.在 Sheet5 的 B1 单元格中，输入“01/3” （注意：0 和 1 中间以空格间隔） 。3. 3.在 Sheet1 工作表中，先选中“金额”列的数据区域，再输入公式“=G3:G52*H3:H52” （注意：各列数据区域建议用鼠标拖拽选取） ，然后按下“Ctrl+Shift+Enter”组合键即可。4. 4.步骤步骤 1 1：选中 L2 单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数中的 COUNTIF 函数，单击【确定】按钮打开 COUNTIF 函数参数对话框，并在相应的文本框中输入如图

12、错错误误! !文档中没有指定样式的文字。文档中没有指定样式的文字。 -20所示的参数 （注意： Range文本框中的单元格区域可拖拽鼠标进行输入； Criteria文本框中的文本可直接单击“高等教育”所在的单元格进行输入） ，单击【确定】按钮。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-20 COUNTIF 函数参数对话框步骤步骤 2 2：选中 L2 单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数中的 COUNTIFS 函数，单击“确定”打开 COUNTISF 函数参数对话框，并在相应的文本框中输入如图 错误错误! !文文档中没有指定样

13、式的文字。档中没有指定样式的文字。-21所示的参数 （注意： Criteria_Range 各文本框中的单元格区域可拖拽鼠标进行输入） ，单击【确定】按钮。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-21 COUNTIFS 函数参数对话框5. 5.选中 L8 单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择 “数学与三角函数”函数中的 SUMIF 函数，单击【确定】按钮打开 SUMIF 函数参数对话框，并在相应的文本框中输入如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-22 所示的参数（注意：绝对引用可在鼠标拖选相应的单元

14、格区域后，按 F4 功能键快速实现输入） 。双击 L8 单元格的填充柄。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-22SUMIF 函数参数对话框6. 6.步骤步骤 1 1：根据题意，闰年条件分析如错误错误! !未找到引用源。未找到引用源。23 所示。能被 4 整除MOD(A2,4)=0不能被 100 整除MOD(A2,100)0两个条件同时满足AND(MOD(A2,4)=0, MOD(A2,100)0)能被 400 整除MOD(A2,400)=0两个条件满足其中之一OR(AND(MOD(A2,4)=0, MOD(A2,100)0), MOD(A2,400)=0)图

15、 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-23 闰年条件分析步骤步骤 2 2：选中 Sheet2 工作表的 B2 单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“逻辑”函数中的 IF 函数，单击【确定】按钮打开IF 函数参数对话框，并在相应的文本框中输入如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-23 所示的参数（ Logical_test 文本框中的参数为“OR(AND(MOD(A2,4)=0,MOD(A2,100)0), MOD(A2,400)=0)” ） 。双击 B2 单元格的填充柄。图 错误错误! !文档中没有

16、指定样式的文字。文档中没有指定样式的文字。-23 IF 函数参数对话框7. 7.参考 3.2.1 中的题 7。8. 8.可参考 3.2.1 中的题 8。步骤步骤 1 1：将光标定位在 Sheet1 数据区域的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视表”命令（如图错误错误! !文档中没有指定样式文档中没有指定样式的文字。的文字。-24 所示） ，打开打开“创建数据透视表”对话框。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-24 “数据透视表”菜单此时，在“选择一个表或区域”单选按钮下方的“表

17、/区域”文本框中自动填入了表格的数据区域。步骤步骤 2 2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文本框中，单击Shee4 工作表标签切换到 Sheet4 工作表，并单击 A1 单元格。步骤步骤 3 3：单击【确定】按钮，进入数据透视表设计环境：从“选择要添加到报表的字段”列表框中，将“”拖到“行标签”框中；将“客户”拖到“列标签”框中；将“定数”拖到“数值”框中，如图错误错误! !文档中没文档中没有指定样式的文字。有指定样式的文字。-25 所示。最后的效果图见图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-26。图 错误错误! !文档中没有指定样式的文

18、字。文档中没有指定样式的文字。-25 设置数据透视表字段列表图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-26 数据透视表效果图3.2.33.2.3 公务员考试成绩表公务员考试成绩表1. 1.在 Sheet5 的 A1 单元格中输入分数 1/3。2. 2.在 Sheet1 中，使用条件格式将“性别”列中为“女”的单元格中字体颜色设置为红色、加粗显示。3. 3.使用 IF 函数，对 Sheet1 中“学位”列进行自动填充。要求：填充的容根据“学历”列的容来确定（假定学生均已获得相应学位）*博士研究生博士*硕士研究生硕士*本科学士*其他无。4. 4.使用数组公式，在 Sh

19、eet1 中计算：计算笔试比例分，并将结果保存在“公务员考试成绩表”中的“笔试比例分”中。*计算方法为：笔试比例分（笔试成绩/3）*60%计算面试比例分，并将结果保存在“公务员考试成绩表”中的“面试比例分”中。*计算方法为：面试比例分面试成绩*40%计算总成绩，并将结果保存在“公务员考试成绩表”中的“总成绩”中。* 计算方法：总成绩笔试比例分+面试比例分5. 5.将 Sheet1 中的“公务员考试成绩表”复制到 Sheet2，根据以下要求修改“公务员考试成绩表”中的数组公式，并将结果保存在Sheet2 的相应列中。要求：*修改“笔试比例分”的计算，计算方法为：笔试比例分（笔试成绩/2）*60%

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

21、过程中，将标题项“公务员考试成绩表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。8. 8.根据 Sheet2 中的“公务员考试成绩表” ，在 Sheet4 中创建一数据透视表。要求：*显示每个报考单位的人的不同学历的人数汇总情况；*行区域设置为“报考单位” ；*列区域设置为“学历” ；*数据区域设置为“学历” ；*计数项为学历。操作步骤如下：操作步骤如下：1. 1.参考 3.2.2 中的题 2。2. 2.参考 3.2.1 中的题 2。3. 3.步骤步骤 1 1：根据题意，分析 IF 嵌套函数，画出其流程图如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。

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

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

24、1 1：单击 Sheet 的 D2 单元格，插入时间函数 YEAR，在打开的 YEAR 函数参数对话框中，输入如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-29 所示的参数，得到年份 2013。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-29 YEAR 函数参数对话框(1)步骤步骤 2 2：再在该公式后面减去如所图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-30 示的计算所得的年份，按回车。（此时公式显示为“=YEAR(2013-5-1)-YEAR(C2)” ） 。按回车，再填充公式。图 错误错误! !文档中没有

25、指定样式的文字。文档中没有指定样式的文字。-30YEAR 函数参数对话框(2)4. 4.选择 Sheet 的 G2 单元格， 插入 REPLACE 函数， 在打开的 REPLACE 函数参数对话框中输入如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-31 所示的参数，单击【确定】按钮。双击G2 单元格的填充柄填充该列的数据。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-31 REPLACE 函数参数对话框5. 5.选择 Sheet1 的 H2 单元格，插入 AND 函数，在打开的 AND 函数参数对话框中输入如图 错误错误! !文档中文档中没

26、有指定样式的文字。没有指定样式的文字。-32 所示的参数，单击【确定】按钮。双击H2 单元格的填充柄填充该列的数据。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-32 AND函数参数对话框6. 6.参考 3.2.2 中的题 4。7. 7.参考 3.2.1 中的题 7。8. 8.参考 3.2.1 中的题 8。3.2.53.2.5 停车情况记录表停车情况记录表1. 1.在 Sheet4 的 A1 单元格中设置为只能录入5 位数字或文本。当录入位数错误时，提示错误原因，样式为“警告” ，错误信息为“只能录入5 位数字或文本” 。2. 2.在 Sheet4 的 B1 单元格中

27、输入公式，判断当前年份是否为闰年，结果为TURE 或 FALSE.* 闰年定义：年数能被 4 整除而不能被 100 整除，或者能被 400 整除的年份。3. 3.使用 HLOOKUP 函数，对 Sheet1“停车情况记录表”中的“单价”列进行填充。要求：*根据 Sheet1 中的“停车价目表”价格，使用HLOOKUP 函数对“停车情况记录表”中的“单价”列根据不同的车型进行填充。注意*函数中如果需要用到绝对地址的请使用绝对地址进行计算，其他方式无效。4. 4. 在 Sheet1 中，使用时间函数计算汽车在停车库中的停放时间。要求：*计算方法为： “停放时间出库时间入库时间”*格式为： “小时：

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

29、40 元的停车记录条数，并将结果保存在J8 单元格中；*统计最高的停车费用，并将结果保存在J9 单元格中。7. 7. 将 Sheet1 中的“停车情况记录表”复制到Sheet2 中，对 Sheet2 进行高级筛选。要求：*筛选条件为： “车型”小汽车， “应付金额”=30；*将结果保存在 Sheet2 中。注意：*无须考虑是否删除筛选条件；*复制过程中，将标题项“停车情况记录表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。8. 8. 根据 Sheet1 中的“停车情况记录表” ，创建一个数据透视图，保存在Sheet3 中。要求：*显示各种车型所收费用的汇总；*x 坐标设置为“

30、车型” ；*求和项为“应付金额” ；*将对相应的数据透视表保存在 Sheet3 中。操作步骤如下：操作步骤如下：1. 1.参考 3.2.2 中的题 1。2. 2.参考 3.2.4 中的题 2。3. 3.单击 Sheet1 工作表的 C9 单元格，插入 HLOOKUP 函数，在 HLOOKUP 函数对话框中输入如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-33 所示的参数（注意 Table_Arrray区域的绝对引用，可拖选选区后直接按 F4 功能键实现快速输入） 。单击【确定】按钮。双击C9 单元格的填充柄填充该列的数据。图 错误错误! !文档中没有指定样式的文字。

31、文档中没有指定样式的文字。-33 HLOOKUP 函数对话框4. 4.单击 F9 单元格，输入公式“=E9-D9” ，按回车确认。双击 F9 单元格的填充柄。5. 5.步骤步骤 1 1：根据题意，分析 IF 嵌套函数，画出其流程图如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-34 所示。YHOUR(F9)=0NMINUTE(F9)=15NHOUR(F9)1YHOUR(F9)+1图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-34 IF 嵌套函数流程图步骤步骤 2 2：据根据以上分析，在G9 单元格输入公式：“=IF(HOUR(F9)=0,1,

32、IF(MINUTE(F9)=15,HOUR(F9)+1,HOUR(F9)” ，再乘以 C9。按回车确认，双击 G9 单元格的填充柄填充该列的数据。6. 6.步骤步骤 1 1：参考 3.2.2 中的题 4。步骤步骤 2 2：单击 J9 单元格，插入MAX 函数，在MAX 函数参数对话框中输入如图 错误错误! !文档中没有指定样式文档中没有指定样式的文字。的文字。-35 所示的参数，单击【确定】按钮。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-35MAX 函数参数对话框7. 7.参考 3.2.1 中的题 7。 （注意：复制“三月份销售统计表”的时候注意选择使用“粘贴选项

33、”中的“值”。 ）8. 8.参考 3.2.1 中的题 8。3.2.63.2.6 语数英成绩表语数英成绩表1. 1.在 Sheet5 中使用多个函数组合，计算A1:A10 中奇数的个数，结果存放在B1 单元格中。2. 2.在 Sheet1 中，使用条件格式将“语文”列中数据大于 80 的单元格中，字体颜色设置为红色、加粗显示。3. 3.使用数组公式，根据 Sheet1 中的数据，计算总分和平均分，将其计算结果保存到表中的“总分”列和“平均分”列当中。4. 4.使用函数，根据 Sheet1 中的“总分”列，对每个同学排名情况进行统计，并将结果保存到表中的“排名”列当中。 （若有相同排名，返回最佳排

34、名）5. 5.使用逻辑函数，判断 Sheet1 中每个同学的每门功课是否均高于全班单科平均分。要求*如果是，保存结果为 TRUE；否则，保存结果为 FALSE；*将结果保存在表中的“优等生”列当中；注意*优等生条件：每门功课均高于全班单科平均分。6. 6.根据 Sheet1 中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到 Sheet2 的相应位置。7. 7.将 Sheet1 复制到 Sheet3 中，并对 Sheet3 进行高级筛选。 要求：*筛选条件为： “语文”=75， “数学”=75， “英语”=75， “总分”=250；*将结果保存在 Sheet3

35、中。注意：*无须考虑是否删除筛选条件；*复制数据表后，粘贴时，数据表必须顶格放置。8. 8.根据 Sheet1 中的结果，在 Sheet4 中创建一数据透视表。要求：*显示是否为优等生的学生人数汇总情况；*行区域设置为“优等生” ；*数据区域设置为“优等生” ；*计数项为优等生。操作步骤如下：操作步骤如下：1. 1.单击选择 Sheet5 中的 B1 单元格，输入公式： “=SUMPRODUCT(MOD(A1:A10,2)” 。2. 2.参考 3.2.1 中的题 2。3. 3.步骤步骤 1 1：选中“总分”列的数据区域，输入公式“=C2:C39+D2:D39+E2:E39” （各列数据区域可用

36、鼠标拖拽选取） ，然后按下 Ctrl+Shift+Enter 组合键。步骤步骤 2 2：选中“平均分”列的数据区域，输入公式“ =F2:F39/3” （各列数据区域可用鼠标拖拽选取） ，然后按下 Ctrl+Shift+Enter 组合键。4. 4.参考 3.2.3 中的题 6。5. 5.步骤步骤 1 1：根据题意，条件分析如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-36 所示（注意求各科平均分时相应单元格区域的绝对引用，注意括号的配对） 。语文大于语文平均分C2AVERAGE($C$2:$C$39)数学大于数学平均分D2AVERAGE($D$2:$D$39)英语大

37、于英语平均分E2AVERAGE($E$2:$E$39)三个条件同时满足AND(C2AVERAGE($C$2:$C$39), D2AVERAGE($D$2:$D$39),E2AVERAGE($E$2:$E$39)图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-36 条件分析步骤步骤 2 2：根据以上分析， 在 I2 单元格中插入逻辑函数 AND，在 AND 函数参数对话框中输入图 错误错误! !文档中文档中没有指定样式的文字。没有指定样式的文字。-37 中所示的 3 个条件。单击【确定】按钮。双击I2 单元格的填充柄。图 错误错误! !文档中没有指定样式的文字。文档中没有

38、指定样式的文字。-37AND 函数参数对话框6. 6.单击 Sheet2 的 B2 单元格， 插入 COUNTIFS 函数， 在打开的 COUNTIFS 函数参数对话框中输入如图 错错误误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-38 所示的参数（注意使用绝对引用） 。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-38 COUNTIFS 函数参数对话框其余同理，只需按题意更改Criteiral 中的条件即可。 （为简单起见，可复制公式后直接修改条件） 。7. 7. 参考 3.2.1 中的题 7。8. 8. 参考 3.2.2 中的题 8。3.2.73.

39、2.7 房产销售表房产销售表1. 1.在 Sheet5 的 A1 单元格中设置只能录入 5 位数字或文本。 当录入位数错误时， 提示错误原因， 样式为 “警告” ，错误信息为“只能录入5 位数字或文本” 。2. 2.在 Sheet1 中，使用条件格式将“预订时期”列中日期为2008-4-1 后的单元格中字体颜色设置为红色、加粗显示。对 C 列，设置“自动调整列宽” 。3. 3.使用公式，计算 Sheet1 中“房产销售表”的房价总额，并保存在“房产总额”列中。* 计算公式为：房产总额面积*单价。4. 4.使用数组公式，计算 Sheet1 中“房产销售表”的契税总额，并保存在“契税总额”列中。*

40、 计算公式为：契税总额契税*房产总额。5. 5.使用函数，根据 Sheet1 中“房产销售表”的结果，在 Sheet2 中统计每个销售人员的销售总额，将结果保存在 Sheet2 的“销售总额”列中。6. 6.使用函数，根据 Sheet2 中“销售总额”列的结果，对每个销售人员的销售情况进行排序，并将结果保存在“排名”列当中。 （若有相同排名，返回最佳排名）7. 7.将 Sheet1 的“房产销售表”复制到Sheet3 中，并对 Sheet3 进行高级筛选。要求：*筛选条件为： “户型”为两室一厅， “房价总额”1000000；*将筛选结果保存在 Sheet3 中。注意：*无须考虑是否删除或移动

41、筛选条件；*复制过程中，将标题项“房产销售表”连同数据一同复制；*数据表必须顶格放置。8. 8.根据 Sheet1 中“房产销售表”的结果，创建一个数据透视图，保存在Sheet4 中。要求：*显示每个销售人员所销售房屋应缴纳契税总额汇总情况；*x 坐标设置为“销售人员” ；*数据区域为“契税总额” ；*求和项设置为契税总额；*将对应的数据透视表也保存在 Sheet4 中。操作步骤如下：操作步骤如下：1. 1.参考 3.2.2 中的题 1。2. 2.步骤步骤 1 1：参考 3.2.1 中的题 2。步骤步骤 2 2：选中 C 列，切换到“开始选项卡” ，单击“单元格”选项组中的【格式】按钮，在弹出

42、的下拉菜单中选择“自动调整列宽” ，如图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-39 所示。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-39 自动调整列宽3. 3.在“房产总额”列的 I3 单元格中，输入公式“=F3*G3” ，按回车确认。双击 I3 单元格的填充柄。4. 4.选中“契税总额”列的数据区域，输入公式“=H3:H26*I3:I26” ， 按下 Ctrl+Shift+Enter 组合键。5. 5.单击 Sheet2 的 B2 单元格，插入 SUMIF 函数，在其参数对话框中输入如图 错误错误! !文档中没有指定样式文档中没有

43、指定样式的文字。的文字。-40 所示的参数， （注意绝对引用和相对引用的使用） ，单击【确定】按钮。双击B2 单元格的填充柄。图 错误错误! !文档中没有指定样式的文字。文档中没有指定样式的文字。-40 SUMIF 函数参数对话框6. 6.参考 3.2.3 中的题 6。7. 7.参考 3.2.1 中的题 7。8. 8.参考 3.2.1 中的题 8。3.2.83.2.8 温度情况表温度情况表1. 1.在 Sheet5 的 A1 单元格中设置为只能录入5 位数字或文本。当录入位数错误时，提示错误原因，样式为“警告” ，错误信息为“只能录入5 位数字或文本” 。2. 2.在 Sheet5 中，使用函

44、数，根据A2 单元格中的判断性别，结果为“男”或“女” ，存放在 B2 单元格中。*倒数第二位为奇数的为“男” ，为偶数的为“女” 。3. 3.使用 IF 函数，对 Sheet1“温度情况表”中的“温度较高的城市”列进行填充，填充结果为城市名称。4. 4.使用数组公式，对Sheet1“温度情况表”中的相差温度值（相对于的温度）进行计算，并把结果保存在“相差温度值”列中。*计算方法：相差温度值平均气温平均气温。5. 5.使用函数，根据 Sheet1“温度情况表”中的结果，对符合以下条件的进行统计。要求：*这半个月以来的最高气温和最低气温，保存在相应单元格中。*这半个月以来的最高气温和最低气温，保

45、存在相应单元格中。6. 6.将 Sheet1 中的“温度情况表”复制到Sheet2 中，在Sheet2 中，重新编辑数组公式，将Sheet2 中的“相差的温度值”中的数值取其绝对值（均为正数） 。注意：*复制过程中，将标题项“温度情况表”连同数据一同复制；*数据表必须顶格放置。7. 7.将 Sheet2 中的“温度情况表”复制到Sheet3 中，并对 Sheet3 进行高级筛选。要求：*筛选条件： “平均温度”=20， “平均温度”30， “工龄”=10， “职称”助工；*将结果保存在 Sheet2 中。注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“员工信息表”连同数据一同复

46、制；*数据表必须顶格放置。8. 8.根据 Sheet1 中的数据，创建一个数据透视图，保存在Sheet3 中。要求：*显示工厂中各种职称人数的汇总情况；*x 坐标设置为“职称” ；*计数取为职称；*数据区域为“职称” ；*将对应的数据透视表也保存在 Sheet3 中。操作步骤如下：操作步骤如下：1. 1.参考 3.2.2 中的题 1。2. 2.在 Sheet4 的 B1 单元格中， 输入函数 “=TIME(HOUR(B1),15*ROUND(MINUTE(B1)/15,0),SECOND(0)” 。3. 3.参考 3.2.4 中的题 4。4. 4.参考 3.2.4 中的题 3。此时默认的数字格

47、式为“日期” 。在“开始”选项卡的“数字”选项组中，单击“日期”右侧的下拉箭头5. 5.参考 3.2.2 中的题 4。6. 6.参考 3.2.4 中的题 5。7. 7.参考 3.2.1 中的题 7。，在弹出的菜单中选择“常规”即可。8. 8.参考 3.2.1 中的题 8。3.2.103.2.10 灯泡采购情况表灯泡采购情况表1. 1.在 Sheet5 中设定 A 列不能输入重复的数值。2. 2.在 Sheet1 中，使用条件格式将“瓦数”列中数据小于 100 的单元格中，字体颜色设置为红色、加粗显示。3. 3.使用数组公式， 计算 Sheet1“采购情况表” 中的每种产品的采购总额， 将结果保

48、存到表中的 “采购总额”列中。*计算方法：采购总额单价*每盒数量*采购盒数4. 4.根据 Sheet1 中的“采购情况表” ，使用数据库函数及已设置的条件区域，计算以下情况的结果。*计算：商标为，瓦数小于100 的白炽灯的平均单价，并将结果填入Sheet1 中的 G25 单元格中，保留小数2位。*计算：产品为白炽灯，瓦数大于等于 80 且小于等于 100 的品种数，并将结果填入 Sheet1 中的 G26 单元格中。5. 5.某公司对各个部门员工吸烟情况进行统计， 作为人力资源搭配的一个数据依据。 对于调查情况，只能回答 Y（吸烟）或者 N（不吸烟） 。根据调查情况，制作出 Sheet2 中的

49、“吸烟情况调查表” 。使用函数，统计符合以下条件的数值。*统计未登记的部门个数，将结果保存在B14 单元格中；*统计在登记的部门中，吸烟的部门个数，将结果保存在B15 单元格中。6. 6.使用函数，对 Sheet2 中的 B21 单元格中的容进行判断，判断其是否为文本，如果是，单元格填充为“TRUE” ；如果不是，单元格填充为“FALSE” ，并将结果保存在 Sheet2 中的 B22 单元格中。7. 7.将 Sheet1 中的“采购情况表”复制到Sheet3 中，对 Sheet3 进行高级筛选。要求：*筛选条件为： “产品为白炽灯，商标为” 。*将结果保存在 Sheet3 中。注意：*无须考

50、虑是否删除或移动筛选条件；*复制过程中，将标题项“采购情况表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。8. 8.根据 Sheet1 中的“采购情况表” ，在 Sheet4 中创建一数据透视表。要求：*显示不同商标的不同产品的采购数量；*行区域设置为“产品” ；*列区域设置为“商标” ；*数据区域设置为“采购盒数” ；*求和项为“采购盒数” 。操作步骤如下：操作步骤如下：1. 1.步骤步骤 1 1：选中 Sheet5 的 A 列，切换到功能区的“数据”选项卡，单击“数据工具”选项组中的“数据有效性”的上半部按钮，打开“数据有效性”对话框。步骤步骤 2 2：切换到“设置”选项