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”函数，如图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所示。（建议直接复制表格中的相关字段）。