Excel2010二级典型试题解析(58页).doc

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

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”函数，如图 31所示。（注意：在已知函数具体名的情况下，

4、可以在“搜索函数”文本框中输入函数名，然后单击【转到】按钮，即可快速找到函数。）图 Error! No text of specified style in document.1插入ROUND函数步骤2：单击【确定】按钮，打开“函数参数”对话框并设置参数，如图 32所示。图 Error! No text of specified style in document.2 ROUND函数参数对话框步骤3：单击【确定】按钮，完成设置。2. 步骤1：选中Sheet1的“采购数量”列的数据区域。切换到“开始”选项卡，单击“样式”选项组的【条件格式】按钮。步骤2：从弹出的菜单中选择“突出显示单元格规则”下

5、的“大于”命令（如图 33所示），打开“大于”对话框：输入条件“100”；单击“设置为”文本框右侧的下拉箭头，从弹出的子菜单里面选择“自定义格式”（如图 34所示），打开“设置单元格格式”对话框。图 Error! No text of specified style in document.3 “条件格式”菜单图 Error! No text of specified style in document.4 设置“大于”对话框步骤3：在“设置单元格格式”对话框中，切换到“字体”选项卡，选择字形和颜色，如图 35所示。图 Error! No text of specified style in

6、document.5“设置单元格格式”对话框步骤4：单击【确定】按钮返回“大于”对话框，再单击【确定】按钮完成设置。3. 选中D11单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择函数VLOOKUP。单击【确定】按钮打开VLOOKUP函数参数对话框，在相应的文本框中输入如图 36所示的参数（注意：Table_Arrray区域的绝对引用。可在拖选选区后直接按F4功能键快速实现输入）。单击【确定】按钮。双击D11单元格的填充柄填充该列的数据。图 Error! No text of specified style in document.6 VLOOKUP函数参数对话框4. 根据题意

7、分析IF嵌套函数，画出其流程图如图 37所示。 NNYYNY折扣率为B3单元格所示的（绝对引用）B11100B11200折扣率为B4单元格所示的（绝对引用）折扣率为B5单元格所示的（绝对引用）B11300折扣率为B6单元格所示的（绝对引用）图 Error! No text of specified style in document.7 IF嵌套函数流程图据此，则在E11单元格中输入公式：“=IF(B11100,$B$3,IF(B11200,$B$4,IF(B11=500，且金额总数=30000”；*将结果保存在Sheet3中。（2）注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题

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

9、度”文本框中输入“5”，如图 318所示。图 Error! No text of specified style in document.18设置数据有效性步骤3：再切换到“出错警告”选项卡：选择“样式”下拉菜单为“警告”；在“错误信息”文本框中输入“只能录入5位数字或文本”，如图 319所示，单击【确定】按钮完成设置。图 Error! No text of specified style in document.19设置出错信息2. 在Sheet5的B1单元格中，输入“0 1/3”（注意：0和1中间以空格间隔）。3. 在Sheet1工作表中，先选中“金额”列的数据区域，再输入公式“=G3:G

10、52*H3:H52”（注意：各列数据区域建议用鼠标拖拽选取），然后按下“Ctrl+Shift+Enter”组合键即可。4. 步骤1：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数中的COUNTIF函数，单击【确定】按钮打开COUNTIF函数参数对话框，并在相应的文本框中输入如图 320所示的参数（注意：Range文本框中的单元格区域可拖拽鼠标进行输入；Criteria文本框中的文本可直接单击“高等教育出版社”所在的单元格进行输入），单击【确定】按钮。图 Error! No text of specified style in document.20 COU

11、NTIF函数参数对话框步骤2：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数中的COUNTIFS函数，单击“确定”打开COUNTISF函数参数对话框，并在相应的文本框中输入如图 321所示的参数（注意：Criteria_Range各文本框中的单元格区域可拖拽鼠标进行输入），单击【确定】按钮。图 Error! No text of specified style in document.21 COUNTIFS函数参数对话框5. 选中L8单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“数学与三角函数”函数中的SUMIF函数，单击【确定】按钮打

12、开SUMIF函数参数对话框，并在相应的文本框中输入如图 322所示的参数（注意：绝对引用可在鼠标拖选相应的单元格区域后，按F4功能键快速实现输入）。双击L8单元格的填充柄。图 Error! No text of specified style in document.22SUMIF函数参数对话框6. 步骤1：根据题意，闰年条件分析如Error! Reference source not found.23所示。两个条件满足其中之一OR(AND(MOD(A2,4)=0, MOD(A2,100)0), MOD(A2,400)=0)两个条件同时满足AND(MOD(A2,4)=0, MOD(A2,100

13、)0)能被400整除MOD(A2,400)=0能被4整除MOD(A2,4)=0不能被100整除MOD(A2,100)0图 323闰年条件分析步骤2：选中Sheet2工作表的B2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“逻辑”函数中的IF函数，单击【确定】按钮打开IF函数参数对话框，并在相应的文本框中输入如图 323所示的参数（Logical_test文本框中的参数为“OR(AND(MOD(A2,4)=0, MOD(A2,100)0), MOD(A2,400)=0)”）。双击B2单元格的填充柄。图 Error! No text of specified style in

14、document.23 IF函数参数对话框7. 参考3.2.1中的题7。8. 可参考3.2.1中的题8。步骤1：将光标定位在Sheet1数据区域的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视表”命令（如图 324所示），打开打开“创建数据透视表”对话框。图 Error! No text of specified style in document.24 “数据透视表”菜单此时，在“选择一个表或区域”单选按钮下方的“表/区域”文本框中自动填入了表格的数据区域。步骤2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文

15、本框中，单击Shee4工作表标签切换到Sheet4工作表，并单击A1单元格。步骤3：单击【确定】按钮，进入数据透视表设计环境：从“选择要添加到报表的字段”列表框中，将“出版社”拖到“行标签”框中；将“客户”拖到“列标签”框中；将“定数”拖到“数值”框中，如图 325所示。最后的效果图见图 326。图 Error! No text of specified style in document.25设置数据透视表字段列表图 Error! No text of specified style in document.26数据透视表效果图3.2.3 公务员考试成绩表1. 在Sheet5的A1单元格中输

16、入分数1/3。2. 在Sheet1中，使用条件格式将“性别”列中为“女”的单元格中字体颜色设置为红色、加粗显示。3. 使用IF函数，对Sheet1中“学位”列进行自动填充。要求：填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）*博士研究生博士*硕士研究生硕士*本科学士*其他无。4. 使用数组公式，在Sheet1中计算：计算笔试比例分，并将结果保存在“公务员考试成绩表”中的“笔试比例分”中。*计算方法为：笔试比例分（笔试成绩/3）*60%计算面试比例分，并将结果保存在“公务员考试成绩表”中的“面试比例分”中。*计算方法为：面试比例分面试成绩*40%计算总成绩，并将结果保存在“公务

17、员考试成绩表”中的“总成绩”中。* 计算方法：总成绩笔试比例分+面试比例分5. 将Sheet1中的“公务员考试成绩表”复制到Sheet2，根据以下要求修改“公务员考试成绩表”中的数组公式，并将结果保存在Sheet2的相应列中。要求：*修改“笔试比例分”的计算，计算方法为：笔试比例分（笔试成绩/2）*60%，并将结果保存在“笔试成绩比例分”列中。注意：*复制过程中，将标题项“公务员考试成绩表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。6. 在Sheet2中，使用函数，根据“总成绩”列对所有考生进行排名。（如果多个数值排名相同，则返回该组数值的最佳排名）*要求：将排名结果保存在

18、“排名”列中。7. 将Sheet2中的“公务员考试成绩表”复制到Sheet3，并对Sheet3进行高级筛选。（1）要求：*筛选条件为：“报考单位” 一中院、“性别”男、“学历”硕士研究生；*将筛选结果保存在Sheet3中。（2）注意：*无须考虑是否删除或移动筛选条件；*复制过程中，将标题项“公务员考试成绩表”连同数据一同复制；*复制数据表后，粘贴时，数据表必须顶格放置。8. 根据Sheet2中的“公务员考试成绩表”，在Sheet4中创建一张数据透视表。要求：*显示每个报考单位的人的不同学历的人数汇总情况；*行区域设置为“报考单位”；*列区域设置为“学历”；*数据区域设置为“学历”；*计数项为学

19、历。l 操作步骤如下：1. 参考3.2.2中的题2。2. 参考3.2.1中的题2。3. 步骤1：根据题意，分析IF嵌套函数，画出其流程图如图 327所示。 NNYYNY博士G3=”博研”生”=40男性”列中。*注意：如果是，保存结果为TRUE；否则，保存结果为FALSE。6. 根据Sheet1中的数据，对以下条件，使用统计函数进行统计。要求： *统计性别为“男”的用户人数，将结果填入到Sheet2的B2单元格中。*统计年龄为“40”岁的用户人数，将结果填入到Sheet2的B3单元格中。7. 将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选。（1）要求：*筛选条件为：“性别”女，

20、“所在区域”西湖区；*将筛选结果保存在Sheet3中。（2）注意：*无须考虑是否删除或移动筛选条件；*复制数据表后，粘贴时，数据表必须顶格放置。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)”。（注意：

21、NOW函数无参数，只有括号）3. 步骤1：单击Sheet的D2单元格，插入时间函数YEAR，在打开的YEAR函数参数对话框中，输入如图 329所示的参数，得到年份2013。图 Error! No text of specified style in document.29 YEAR函数参数对话框(1)步骤2：再在该公式后面减去如所图 330示的计算所得的年份，按回车。（此时公式显示为“=YEAR(2013-5-1)-YEAR(C2)”）。按回车，再填充公式。图 Error! No text of specified style in document.30 YEAR函数参数对话框(2)4. 选

22、择Sheet的G2单元格，插入REPLACE函数，在打开的REPLACE函数参数对话框中输入如图 331所示的参数，单击【确定】按钮。双击G2单元格的填充柄填充该列的数据。图 Error! No text of specified style in document.31 REPLACE函数参数对话框5. 选择Sheet1的H2单元格，插入AND函数，在打开的AND函数参数对话框中输入如图 332所示的参数，单击【确定】按钮。双击H2单元格的填充柄填充该列的数据。图 Error! No text of specified style in document.32 AND函数参数对话框6. 参考

23、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整除而不能被100整除，或者能被400整除的年份。3. 使用HLOOKUP函数，对Sheet1“停车情况记录表”中的“单价”列进行填充。要求：*根据Sheet1中的“停车价目表”价格，使用HLOOKUP函数对“停车情况记录表

24、”中的“单价”列根据不同的车型进行填充。注意*函数中如果需要用到绝对地址的请使用绝对地址进行计算，其他方式无效。4. 在Sheet1中，使用时间函数计算汽车在停车库中的停放时间。要求：*计算方法为：“停放时间出库时间入库时间”*格式为：“小时：分钟：秒”*将结果保存在“停车情况记录表”中的“停放时间”列中*例如：一小时十五分十二秒在停放时间中的表示为：“1:15:12”。5. 使用函数公式，对“停车情况记录表”中的停车费用进行计算。要求：*根据Sheet1停放时间的长短计算停车费用，将计算结果填入到“停车情况记录表”中的“应付金额”列中。注意：*停车按小时收费，对于不满一个小时的按照一个小时计

25、费；*对于超过整点小时数十五分钟（包含十五分钟）的，多累积一个小时；*例如：1小时23分，将以2小时计费。6. 使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计。*统计停车费用大于等于40元的停车记录条数，并将结果保存在J8单元格中；*统计最高的停车费用，并将结果保存在J9单元格中。7. 将Sheet1中的“停车情况记录表”复制到Sheet2中，对Sheet2进行高级筛选。要求：*筛选条件为：“车型”小汽车，“应付金额”=30；*将结果保存在Sheet2中。注意：*无须考虑是否删除筛选条件；*复制过程中，将标题项“停车情况记录表”连同数据一同复制；*复制数据表后，粘贴时，

26、数据表必须顶格放置。8. 根据Sheet1中的“停车情况记录表”，创建一个数据透视图，保存在Sheet3中。要求：*显示各种车型所收费用的汇总；*x坐标设置为“车型”；*求和项为“应付金额”；*将对相应的数据透视表保存在Sheet3中。l 操作步骤如下：1. 参考3.2.2中的题1。2. 参考3.2.4中的题2。3. 单击Sheet1工作表的C9单元格，插入HLOOKUP函数，在HLOOKUP函数对话框中输入如图 333所示的参数（注意Table_Arrray区域的绝对引用，可拖选选区后直接按F4功能键实现快速输入）。单击【确定】按钮。双击C9单元格的填充柄填充该列的数据。图 Error! N

27、o text of specified style in document.33 HLOOKUP函数对话框4. 单击F9单元格，输入公式“=E9-D9”，按回车确认。双击F9单元格的填充柄。5. 步骤1：根据题意，分析IF嵌套函数，画出其流程图如图 334所示。 NYNY1HOUR(F9)=0HOUR(F9)+1HOUR(F9)MINUTE(F9)=155图 Error! No text of specified style in document.34 IF嵌套函数流程图步骤2：据根据以上分析，在G9单元格输入公式：“=IF(HOUR(F9)=0,1,IF(MINUTE(F9)=15,HOU

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

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

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

31、作步骤如下：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：根据题意，条件分析如图 336所示（注意求各科平均分时相应单元格区域的绝对引用，注

32、意括号的配对）。三个条件同时满足AND(C2AVERAGE($C$2:$C$39), D2AVERAGE($D$2:$D$39), E2AVERAGE($E$2:$E$39)语文大于语文平均分C2AVERAGE($C$2:$C$39)数学大于数学平均分D2AVERAGE($D$2:$D$39)英语大于英语平均分E2AVERAGE($E$2:$E$39)图 Error! No text of specified style in document.36条件分析步骤2：根据以上分析，在I2单元格中插入逻辑函数AND，在AND函数参数对话框中输入图 337中所示的3个条件。单击【确定】按钮。双击I2

33、单元格的填充柄。图 Error! No text of specified style in document.37 AND函数参数对话框6. 单击Sheet2的B2单元格，插入COUNTIFS函数，在打开的COUNTIFS函数参数对话框中输入如图 338所示的参数（注意使用绝对引用）。图 Error! No text of specified style in document.38 COUNTIFS函数参数对话框其余同理，只需按题意更改Criteiral中的条件即可。（为简单起见，可复制公式后直接修改条件）。7. 参考3.2.1中的题7。8. 参考3.2.2中的题8。3.2.7房产销售表1. 在Sheet5的A1单元格中设置只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 在Sheet1中，使用条件格式将“预订时期”列中日期为2008-4-1后的单元格中字体颜色设置为红色、加粗显示。对C列，设置“自动调整列宽”。3. 使用公式，计算Sheet1中“房产销售表”的房价总额，并保存在“房产总额”列中。* 计算公式为：房产总额面积*单价。4. 使用数组公式，计算Sheet