Excel2010二级典型试题解析

3.1 Excel2010二级典型试题解析（*）

3.2.1 衣物采购表

1.在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。

2.在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体设置为红色，加粗显示。

3.使用VLOOKUP函数，对Sheet1中的“采购表”的“单价”列进行填充。

* 根据“价格表”中的商品单价，使用VLOOKUP函数，将其单价填充到采购表中的“单价”列中。

* 函数中参数如果需要用到绝对地址的，请使用绝对地址进行答题，其他方式无效。

4.使用逻辑函数，对Sheet1“采购表”中的“折扣”列进行填充。要求：

* 根据“折扣表”中的商品折扣率，使用相应的函数，将其折扣率填充到采购表中的“折扣”列中。

5.使用公式，对Sheet1中“采购表”的“合计”列进行填充。

* 根据“采购数量”，“单价”和“折扣”，计算采购的合计金额，将结果保存在“合计”列中。

* 计算公式：单价*采购数量*（1－折扣率）

6.使用SUMIF函数，计算各种商品的采购数量和采购总金额，将结果保存在Sheet1“统计表”当中相应位置。

7.将Sheet1中的“采购表”复制到Sheet2，并对Sheet2进行高级筛选。

（1）要求：

*筛选条件为：“采购数量”>150，“折扣率”>0；

*将筛选结果保存在Sheet2中。

（2）注意：

*无须考虑是否删除或移动筛选条件；

*复制过程中，将标题项“采购表”连同数据一同复制；

*复制数据表后，粘贴时，数据表必须顶格放置；

*复制过程中，保持数据一致。

8.根据Sheet1中“采购表”，新建一个数据透视图，保存在Sheet3中。要求：

*该图形显示每个采购时间点所采购的所有项目数量汇总的情况；

*x坐标设置为“采购时间”；

*求和项为“采购数量”；

*将对相应的数据透视表保存在Sheet3中。

操作步骤如下：

1.步骤1：单击选择Sheet5的B1单元格，在“开始”选项卡中，单击编辑栏中的【插入函数】按钮，

打开“插入函数”函数对话框，选择“全部”中的“ROUND”函数，如图错误！文档中没有指定样式的文字。-1所示。（注意：在已知函数具体名的情况下，可以在“搜索函数”文本框中输入函数名，然后单击【转到】按钮，即可快速找到函数。）

图错误！文档中没有指定样式的文字。-1插入ROUND函数

步骤2：单击【确定】按钮，打开“函数参数”对话框并设置参数，如图错误！文档中没有指定样式的文字。-2所示。

图错误！文档中没有指定样式的文字。-2ROUND函数参数对话框

步骤3：单击【确定】按钮，完成设置。

2.步骤1：选中Sheet1的“采购数量”列的数据区域。切换到“开始”选项卡，单击“样式”选项组的【条

件格式】按钮。

步骤2：从弹出的菜单中选择“突出显示单元格规则”下的“大于”命令（如图错误！文档中没有指定样式的文字。-3所示），打开“大于”对话框：输入条件“100”；单击“设置为”文本框右侧的下拉箭头，从弹出的子菜单里面选择“自定义格式”（如图错误！文档中没有指定样式的文字。-4所示），打开“设置单元格格式”对话框。

图错误！文档中没有指定样式的文字。-3“条件格式”菜单

图错误！文档中没有指定样式的文字。-4设置“大于”对话框

步骤3：在“设置单元格格式”对话框中，切换到“字体”选项卡，选择字形和颜色，如图错误！文档中没有指定样式的文字。-5所示。

图错误！文档中没有指定样式的文字。-5“设置单元格格式”对话框

步骤4：单击【确定】按钮返回“大于”对话框，再单击【确定】按钮完成设置。

3.选中D11单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择函数VLOOKUP。

单击【确定】按钮打开VLOOKUP函数参数对话框，在相应的文本框中输入如图错误！文档中没有指定样式的文字。-6所示的参数（注意：Table_Arrray区域的绝对引用。可在拖选选区后直接按F4功能键快速实现输入）。单击【确定】按钮。双击D11单元格的填充柄填充该列的数据。

图错误！文档中没有指定样式的文字。-6VLOOKUP函数参数对话框

4.根据题意分析IF嵌套函数，画出其流程图如图错误！文档中没有指定样式的文字。-7所示。

图错误！文档中没有指定样式的文字。-7 IF嵌套函数流程图

据此，则在E11单元格中输入公式：“=IF(B11<100,$B$3,IF(B11<200,$B$4,IF(B11<300,$B$5,$B$6)))”。按回车确认。双击E11单元格的填充柄填充该列的数据。（注意：如果数据格式不对，单击“开始”选项卡的“数字

选项组”中的【百分比】按钮即可。）

5.在F11单元格中输入公式“=B11*D11*(1-E11)“，按回车确认，双击F11单元格填充柄。（注意：如果单元格中出现“######”，表示该列宽度不足，无法显示数据。只需加宽该列即可）

6.计算采购数量：单击J12单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择

SUMIF函数，单击“确定”打开SUMIF函数参数对话框，并在相应的文本框中输入如图错误！文档中没有指定样式的文字。-8所示的参数(注意绝对引用和相对引用的使用)。双击J12单元格的填充柄。

图错误！文档中没有指定样式的文字。-8 SUMIF函数参数对话框

计算采购总金额：同理，在K12单元格中插入函数，设置SUMIF函数参数如图错误！文档中没有指定样式的文字。-9所示，单击【确定】按钮。双击K12单元格的填充柄。

图错误！文档中没有指定样式的文字。-9 SUMIF函数参数对话框

7.步骤1：复制表格。

选择Sheet1的采购表，按下“Ctrl+C”；将光标定位在Sheet2的A1单元格，按下“Ctrl+v”（注意：此时复制的数据会出现一个出错信息，如图错误！文档中没有指定样式的文字。-10所示）。单击【确定】按钮。（注意：由于包含了公式的循环引用，此时复制的数据有错误。）

图错误！文档中没有指定样式的文字。-10“循环引用警告”对话框选择Sheet1采购表中的计算过的数据区域（即“单价”、“折扣”、“合计”列下方的数据区域），按下“Ctrl+C”；将光标定位在Sheet2的错误的数据区域的起始单元格（即“单价”下方的单元格），单击鼠标右键，在弹出的快捷菜单中选择“选择性粘贴——值和数字格式”（如图错误！文档中没有指定样式的文字。-11所示）。

图错误！文档中没有指定样式的文字。-11选择性粘贴

（注意：当某单元格出现“”时，表示单元格宽度不够，此时可调整单元格的宽度以看到完整

的单元格数据）

步骤2：建立高级筛选的条件区域。在数据区域的下方，根据题目要求建立条件区域，如图错误！文档中没有指定样式的文字。-12所示。（建议直接复制表格中的相关字段）。

图错误！文档中没有指定样式的文字。-12高级筛选的条件区域

步骤3：高级筛选。单击数据区域中的任一单元格，然后切换功能区的“数据”选项卡，在“排序和筛选”选项组中单击【高级】按钮，打开“高级筛选”对话框。此时，“列表区域”的文本框中已自动填入所

有数据区域。

再把光标定位在“条件区域”文本框内，拖动鼠标选中条件区域（如图错误！文档中没有指定样式的文字。-13所示），单击【确定】按钮完成设置。

图错误！文档中没有指定样式的文字。-13选择条件区域

8.步骤1：将光标定位在Sheet1工作表数据区域中的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视图”命令（如图错误！文档中没有指定样式的文字。-14所示），打开“创建数据透视表及数据透视图”对话框。

图错误！文档中没有指定样式的文字。-14选择“数据透视表”菜单

图错误！文档中没有指定样式的文字。-15“创建数据透视表及数据透视图”对话框此时，在“选择一个表或区域”单选按钮下方的“表/区域”文本框中自动填入了表格的数据区域，如图错误！文档中没有指定样式的文字。-15上方所示。

步骤2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文本框中，单击Sheet3工作表标签切换到Sheet3工作表，并单击A1单元格，如图错误！文档中没有指定样式的文字。-15下方所示。

步骤3：单击【确定】按钮，进入数据透视表及数据透视图设计环境：从“选择要添加到报表的字段”列表

框中，将“采购时间”拖到“轴字段”框中；将“采购数量”拖到“∑数值”框中，如图错误！文档中没有指定样式的文字。-16所示。最后的效果图见图错误！文档中没有指定样式的文字。-17。

图错误！文档中没有指定样式的文字。-16设置数据透视图（表）字段列表

图错误！文档中没有指定样式的文字。-17数据透视图（表）效果图

3.2.2教材订购情况表

1.在Sheet5的A1单元格中设置只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

2.在Sheet5的B1单元格中输入分数1/3。

3.使用数组公式，对Sheet1中“教材订购情况表”的订购金额进行计算。

*将结果保存在该表的“金额”列当中。

*计算方法：金额＝订数*单价。

4.使用统计函数，对Sheet1中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置。要求：

*统计出版社名称为“高等教育出版社”的书的种类数，并将结果保存在Sheet1中的L2单元格中。

*统计订购数量大于110且小于850的书的种类数，并将结果保存在Sheet1中的L3单元格中。

5.使用函数，计算每个用户所订购图书所需支付的金额总数，并将结果保存在Sheet1中的“用户支付情况表”的“支付金额”列中。

6.使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”；如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

*闰年定义，年数能被4整除而不能被100整除，或者能被400整除的年份。

7.将Sheet1中的“教材订购情况表”复制到Sheet3，并对Sheet3进行高级筛选。

（1）要求：

*筛选条件为“订数>=500，且金额总数<=30000”；

*将结果保存在Sheet3中。

（2）注意：

*无须考虑是否删除或移动筛选条件；

*复制过程中，将标题项“教材订购情况表”连同数据一同复制；

*数据表必须顶格放置；

*复制过程中，数据保持一致。

8.根据Sheet1中“教材订购情况表”的结果，在Sheet4中新建一张数据透视表。要求：

*显示每个客户在每个出版社所订的教材数目；

*行区域设置为“出版社”；

*列区域设置为“客户”；

*求和项为订数；

*数据区域设置为“订数”。

操作步骤如下：

1.步骤1：选中Sheet5工作表中的A1单元格，切换到功能区的“数据”选项卡，单击“数据工具”选项

组中的“数据有效性”的上半部按钮，打开“数据有效性”对话框。

步骤2：切换到“设置”选项卡：选择“允许”下拉菜单为“文本长度”；选择“数据”下拉菜单为“等于”；并在“长度”文本框中输入“5”，如图错误！文档中没有指定样式的文字。-18所示。

图错误！文档中没有指定样式的文字。-18设置数据有效性

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

图错误！文档中没有指定样式的文字。-19设置出错信息

2.在Sheet5的B1单元格中，输入“0 1/3”（注意：0和1中间以空格间隔）。

3.在Sheet1工作表中，先选中“金额”列的数据区域，再输入公式“=G3:G52*H3:H52”（注意：各列数据区域建议用鼠标拖拽选取），然后按下“Ctrl+Shift+Enter”组合键即可。

4.步骤1：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”

函数中的COUNTIF函数，单击【确定】按钮打开COUNTIF函数参数对话框，并在相应的文本框中输入如图错误！文档中没有指定样式的文字。-20所示的参数（注意：Range文本框中的单元格区域可拖拽鼠标进行输入；Criteria文本框中的文本可直接单击“高等教育出版社”所在的单元格进行输入），单击【确定】按钮。

图错误！文档中没有指定样式的文字。-20COUNTIF函数参数对话框

步骤2：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数

中的COUNTIFS函数，单击“确定”打开COUNTISF函数参数对话框，并在相应的文本框中输入如图错误！文档中没有指定样式的文字。-21所示的参数（注意：Criteria_Range各文本框中的单元格区域可拖拽鼠标进行输入），单击【确定】按钮。

图错误！文档中没有指定样式的文字。-21COUNTIFS函数参数对话框

5.选中L8单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“数学与三角函数”

函数中的SUMIF函数，单击【确定】按钮打开SUMIF函数参数对话框，并在相应的文本框中输入如图错误！文档中没有指定样式的文字。-22所示的参数（注意：绝对引用可在鼠标拖选相应的单元格区域后，按F4功能键快速实现输入）。双击L8单元格的填充柄。

图错误！文档中没有指定样式的文字。-22SUMIF函数参数对话框

6.步骤1：根据题意，闰年条件分析如错误！未找到引用源。23所示。

图错误！文档中没有指定样式的文字。-23闰年条件分析

步骤2：选中Sheet2工作表的B2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选

择“逻辑”函数中的IF函数，单击【确定】按钮打开IF函数参数对话框，并在相应的文本框中输入如图错误！文档中没有指定样式的文字。-23所示的参数（Logical_test文本框中的参数为“OR(AND(MOD(A2,4)=0, MOD(A2,100)<>0), MOD(A2,400)=0)”）。双击B2单元格的填充柄。

图错误！文档中没有指定样式的文字。-23 IF函数参数对话框

7.参考3.2.1中的题7。

8.可参考3.2.1中的题8。

步骤1：将光标定位在Sheet1数据区域的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视表”命令（如图错误！文档中没有指定样式的文字。-24所示），打开打开“创建数据透视表”对话框。

图错误！文档中没有指定样式的文字。-24“数据透视表”菜单

此时，在“选择一个表或区域”单选按钮下方的“表/区域”文本框中自动填入了表格的数据区域。

步骤2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文本框中，单击Shee4工作表标签切换到Sheet4工作表，并单击A1单元格。

步骤3：单击【确定】按钮，进入数据透视表设计环境：从“选择要添加到报表的字段”列表框中，将“出

版社”拖到“行标签”框中；将“客户”拖到“列标签”框中；将“定数”拖到“∑数值”框中，如图错误！文档中没有指定样式的文字。-25所示。最后的效果图见图错误！文档中没有指定样式的文字。-26。

图错误！文档中没有指定样式的文字。-25设置数据透视表字段列表

图错误！文档中没有指定样式的文字。-26数据透视表效果图

3.2.3 公务员考试成绩表

1.在Sheet5的A1单元格中输入分数1/3。

2.在Sheet1中，使用条件格式将“性别”列中为“女”的单元格中字体颜色设置为红色、加粗显示。

3.使用IF函数，对Sheet1中“学位”列进行自动填充。要求：

填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）

*博士研究生—博士

*硕士研究生—硕士

*本科—学士

*其他—无。

4.使用数组公式，在Sheet1中计算：

①计算笔试比例分，并将结果保存在“公务员考试成绩表”中的“笔试比例分”中。

*计算方法为：笔试比例分＝（笔试成绩/3）*60%

②计算面试比例分，并将结果保存在“公务员考试成绩表”中的“面试比例分”中。

*计算方法为：面试比例分＝面试成绩*40%

③计算总成绩，并将结果保存在“公务员考试成绩表”中的“总成绩”中。

* 计算方法：总成绩＝笔试比例分+面试比例分

5.将Sheet1中的“公务员考试成绩表”复制到Sheet2，根据以下要求修改“公务员考试成绩表”中的数组公式，并将结果保存在Sheet2的相应列中。

①要求：

*修改“笔试比例分”的计算，计算方法为：笔试比例分＝（笔试成绩/2）*60%，并将结果保存在“笔试成绩比例分”列中。

②注意：

*复制过程中，将标题项“公务员考试成绩表”连同数据一同复制；

*复制数据表后，粘贴时，数据表必须顶格放置。

6.在Sheet2中，使用函数，根据“总成绩”列对所有考生进行排名。（如果多个数值排名相同，则返回该

组数值的最佳排名）

*要求：将排名结果保存在“排名”列中。

7.将Sheet2中的“公务员考试成绩表”复制到Sheet3，并对Sheet3进行高级筛选。

（1）要求：

*筛选条件为：“报考单位”—一中院、“性别”—男、“学历”—硕士研究生；

*将筛选结果保存在Sheet3中。

（2）注意：

*无须考虑是否删除或移动筛选条件；

*复制过程中，将标题项“公务员考试成绩表”连同数据一同复制；

*复制数据表后，粘贴时，数据表必须顶格放置。

8.根据Sheet2中的“公务员考试成绩表”，在Sheet4中创建一张数据透视表。

要求：

*显示每个报考单位的人的不同学历的人数汇总情况；

*行区域设置为“报考单位”；

*列区域设置为“学历”；

*数据区域设置为“学历”；

*计数项为学历。

操作步骤如下：

1.参考3.

2.2中的题2。

2.参考

3.2.1中的题2。

3.步骤1：根据题意，分析IF嵌套函数，画出其流程图如图错误！文档中没有指定样式的文字。-27所示。

图错误！文档中没有指定样式的文字。-27IF嵌套函数流程图

步骤2：根据以上流程图，在H3单元格中输入公式：“=IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="本科","学士","无")))”（注意：务必在英文输入法状态下输入字符，仅中文字符例外；且括号要配对）。

按回车确认。双击H3单元格的填充柄填充该列的数据。

4.选中“笔试比例分”列的数据区域，输入公式“=I3:I18/3*0.6”，（各列数据区域可用鼠标拖拽选取），然后按下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函数，单击【确定】按钮打开RANK.EQ函数参数对话框：在相应的文本框中输入如图错误！文档中没有指定样式的文字。-28所示的参数（注意使用绝对引用，用鼠标拖选选区后，直接按下F4功能键快速实现输入）。

按回车确认。双击N3单元格的填充柄填充该列的数据。

图错误！文档中没有指定样式的文字。-28 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。

*闰年定义：年数能被4整除而不能被100整除，或者能被400整除的年份。

3.使用时间函数，对Sheet1中用户的年龄进行计算。要求：

*假设当前时间是“2013-5-1”，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”列当中。计算方法为两个时间年份之差。

4.使用REPLACE函数，对Sheet1中用户的电话号码进行升级。要求：

*对“原电话号码”列中的电话号码进行升级。升级方式是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。

*例如：电话号码“0571*******”升级后为“0571********”。

5.在Sheet1中，使用AND函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于40岁的男性，并将结果保存在“是否>=40男性”列中。

*注意：如果是，保存结果为TRUE；否则，保存结果为FALSE。

6.根据Sheet1中的数据，对以下条件，使用统计函数进行统计。要求：

*统计性别为“男”的用户人数，将结果填入到Sheet2的B2单元格中。

*统计年龄为“>40”岁的用户人数，将结果填入到Sheet2的B3单元格中。

7.将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选。

（1）要求：

*筛选条件为：“性别”—女，“所在区域”—西湖区；

*将筛选结果保存在Sheet3中。

（2）注意：

*无须考虑是否删除或移动筛选条件；

*复制数据表后，粘贴时，数据表必须顶格放置。

8.根据Sheet1的结果，创建一个数据透视图，保存在Sheet4中。要求：

*显示每个区域所拥有的用户数量；

*x坐标设置为“所在区域”；

*计数项为“所在区域”；

*将对应的数据透视表保存在Sheet4中。

操作步骤如下：

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单元格，插入时间函数YEAR，在打开的YEAR函数参数对话框中，输入如图错误！文档中没有指定样式的文字。-29所示的参数，得到年份2013。

图错误！文档中没有指定样式的文字。-29 YEAR函数参数对话框(1)

步骤2：再在该公式后面减去如所图错误！文档中没有指定样式的文字。-30示的计算所得的年份，按回车。（此时公式显示为“=YEAR("2013-5-1")-YEAR(C2)”）。按回车，再填充公式。

图错误！文档中没有指定样式的文字。-30 YEAR函数参数对话框(2)

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

图错误！文档中没有指定样式的文字。-31 REPLACE函数参数对话框

5.选择Sheet1的H2单元格，插入AND函数，在打开的AND函数参数对话框中输入如图错误！文档中没有指定样式的文字。-32所示的参数，单击【确定】按钮。双击H2单元格的填充柄填充该列的数据。

图错误！文档中没有指定样式的文字。-32 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整除而不能被100整除，或者能被400整除的年份。

3.使用HLOOKUP函数，对Sheet1“停车情况记录表”中的“单价”列进行填充。

①要求：