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”函数，如图3-102所示。（注意：在已知函数具体名的情况下，可以在“搜索函数”文本框中输入函数名，然后单击【转到】按钮，即可快速找到函数。）
图3-102插入ROUND函数
步骤2：单击【确定】按钮，打开“函数参数”对话框并设置参数，如图3-103所示。
图3-103 ROUND函数参数对话框
步骤3：单击【确定】按钮，完成设置。
2.步骤1：选中Sheet1的“采购数量”列的数据区域。切换到“开始”选项卡，单击“样式”选项组的【条
件格式】按钮。
步骤2：从弹出的菜单中选择“突出显示单元格规则”下的“大于”命令（如图3-104所示），打开“大于”对话框：输入条件“100”；单击“设置为”文本框右侧的下拉箭头，从弹出的子菜单里面选择“自定义格式”（如图3-105所示），打开“设置单元格格式”对话框。
图3-104 “条件格式”菜单
图3-105 设置“大于”对话框
步骤3：在“设置单元格格式”对话框中，切换到“字体”选项卡，选择字形和颜色，如图3-106所示。
图3-106“设置单元格格式”对话框
步骤4：单击【确定】按钮返回“大于”对话框，再单击【确定】按钮完成设置。
3.选中D11单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择函数VLOOKUP。单击【确定】按钮打开VLOOKUP函数参数对话框，在相应的文本框中输入如图3-107所示的参数（注意：Table_Arrray区域的绝对引用。可在拖选选区后直接按F4功能键快速实现输入）。单击【确定】按钮。双击D11单元格的填充柄填充该列的数据。
图3-107 VLOOKUP函数参数对话框
4.根据题意分析IF嵌套函数，画出其流程图如图3-108所示。
图3-108 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函数参数对话框，并在相应的文本框中输入如图3-109所示的参数(注意绝对引用和相对引用的使用)。双击J12单元格的填充柄。
图3-109 SUMIF函数参数对话框
计算采购总金额：同理，在K12单元格中插入函数，设置SUMIF函数参数如图3-110所示，单击【确定】按钮。双击K12单元格的填充柄。
图3-110 SUMIF函数参数对话框
7.步骤1：复制表格。
选择Sheet1的采购表，按下“Ctrl+C”；将光标定位在Sheet2的A1单元格，按下“Ctrl+v”（注意：此时复制的数据会出现一个出错信息，如图3-111所示）。单击【确定】按钮。（注意：由于包含了公式的循环引用，此时复制的数据有错误。）
图3-111“循环引用警告”对话框
选择Sheet1采购表中的计算过的数据区域（即“单价”、“折扣”、“合计”列下方的数据区域），按下“Ctrl+C”；将光标定位在Sheet2的错误的数据区域的起始单元格（即“单价”下方的单元格），单击鼠标右键，在弹出的快捷菜单中选择“选择性粘贴——值和数字格式”（如图3-112所示）。
图3-112选择性粘贴
（注意：当某单元格出现“”时，表示单元格宽度不够，此时可调整单元格的宽度以看到完整的单元格数据）
步骤2：建立高级筛选的条件区域。在数据区域的下方，根据题目要求建立条件区域，如图3-113所示。（建议直接复制表格中的相关字段）。
图3-113高级筛选的条件区域
步骤3：高级筛选。单击数据区域中的任一单元格，然后切换功能区的“数据”选项卡，在“排序和筛选”
选项组中单击【高级】按钮，打开“高级筛选”对话框。此时，“列表区域”的文本框中已自动填入所有数据区域。
再把光标定位在“条件区域”文本框，拖动鼠标选中条件区域（如图3-114所示），单击【确定】按钮完成设置。
图3-114选择条件区域
8.步骤1：将光标定位在Sheet1工作表数据区域中的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视图”命令（如图3-115所示），打开“创建数据透视表及数据透视图”对话框。
图3-115选择“数据透视表”菜单
图3-116 “创建数据透视表及数据透视图”对话框
此时，在“选择一个表或区域”单选按钮下方的“表/区域”文本框中自动填入了表格的数据区域，如图3-116上方所示。
步骤2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文本框中，单击Sheet3工作表标签切换到Sheet3工作表，并单击A1单元格，如图3-116下方所示。
步骤3：单击【确定】按钮，进入数据透视表及数据透视图设计环境：从“选择要添加到报表的字段”列表框中，将“采购时间”拖到“轴字段”框中；将“采购数量”拖到“∑数值”框中，如图3-117所示。最后的效果图见图3-118。
图3-117设置数据透视图（表）字段列表
图3-118数据透视图（表）效果图
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”，如图3-119所示。
图3-119设置数据有效性
步骤3：再切换到“出错警告”选项卡：选择“样式”下拉菜单为“警告”；在“错误信息”文本框中输入“只能录入5位数字或文本”，如图3-120所示，单击【确定】按钮完成设置。
图3-120设置出错信息
2.在Sheet5的B1单元格中，输入“0 1/3”（注意：0和1中间以空格间隔）。
3.在Sheet1工作表中，先选中“金额”列的数据区域，再输入公式“=G3:G52*H3:H52”（注意：各列数据区域建议用鼠标拖拽选取），然后按下“Ctrl+Shift+Enter”组合键即可。
4.步骤1：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数中的COUNTIF函数，单击【确定】按钮打开COUNTIF函数参数对话框，并在相应的文本框中输入如图3-121所示的参数（注意：Range文本框中的单元格区域可拖拽鼠标进行输入；Criteria文本框中的文本可直接单击“高等教育”所在的单元格进行输入），单击【确定】按钮。
图3-121 COUNTIF函数参数对话框
步骤2：选中L2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“统计”函数中的COUNTIFS函数，单击“确定”打开COUNTISF函数参数对话框，并在相应的文本框中输入如图3-122所示的参数（注意：Criteria_Range各文本框中的单元格区域可拖拽鼠标进行输入），单击【确定】按钮。
图3-122 COUNTIFS函数参数对话框
5.选中L8单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选择“数学与三角函数”函数中的SUMIF函数，单击【确定】按钮打开SUMIF函数参数对话框，并在相应的文本框中输入如图3-123所示的参数（注意：绝对引用可在鼠标拖选相应的单元格区域后，按F4功能键快速实现输入）。双击L8单元格的填充柄。
图3-123SUMIF函数参数对话框
6.
步骤1：根据题意，闰年条件分析如错误!未找到引用源。23所示。
图 3-124闰年条件分析
步骤2：选中Sheet2工作表的B2单元格，单击编辑栏上的【插入函数】按钮，打开插入函数对话框并选
择“逻辑”函数中的IF 函数，单击【确定】按钮打开IF 函数参数对话框，并在相应的文本框中输入如图 3-124所示的参数（Logical_test 文本框中的参数为“OR(AND(MOD(A2,4)=0, MOD(A2,100)<>0), MOD(A2,400)=0)”）
。双击B2单元格的填充柄。
图 3-124 IF 函数参数对话框
7. 参考3.2.1中的题7。 8. 可参考3.2.1中的题8。
步骤1：将光标定位在Sheet1数据区域的任一单元格，切换到功能区中的“插入”选项卡，在“表格”选项组中单击【数据透视表】下拉箭头，在弹出的菜单中选择“数据透视表”命令（如图 3-125所示），打开打开“创建数据透视表”对话框。
图 3-125 “数据透视表”菜单
此时，在“选择一个表或区域”单选按钮下方的“表/区域”文本框中自动填入了表格的数据区域。
两个条件满足其中之一 OR(AND(MOD(A2,4)=0, MOD(A2,100)<>0), MOD(A2,400)=0)
步骤2：选择“现有工作表”单选按钮，将光标定位在“位置”右侧的文本框中，单击Shee4工作表标签切换到Sheet4工作表，并单击A1单元格。
步骤3：单击【确定】按钮，进入数据透视表设计环境：从“选择要添加到报表的字段”列表框中，将“”拖到“行标签”框中；将“客户”拖到“列标签”框中；将“定数”拖到“∑数值”框中，如图3-126所示。最后的效果图见图3-127。
图3-126设置数据透视表字段列表
图3-127数据透视表效果图
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嵌套函数，画出其流程图如图3-128所示。
图3-128 IF嵌套函数流程图
步骤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函数参数对话框：在相应的文本框中输入如图3-129所示的参数（注意使用绝对引用，用鼠标拖选选区后，直接按下F4功能键快速实现输入）。
按回车确认。双击N3单元格的填充柄填充该列的数据。