前言

136

图 6-35 “数据透视表”布局

前言

137

图 6-36 数据透视图

图 6-37 数据透视表

前言

138

典型试题 3

打开素材库中的“典型试题 6-3.xls”文件，按下面的操作要求进行操作，并把操作 结果存盘。

注意：在做题时，不得将数据表进行更改。

一、操作要求

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

2. 使用 VLOOKUP 函数，对 Sheet1 中的“三月份销售统计表”的“产品名称”列和“产 品单价”列进行填充。

要求：根据“企业销售产品清单”，使用 VLOOKUP 函数，将产品名称和产品单价填充 到“三月份销售统计表”的“产品名称”列和“产品单价”列中。 3. 使用数组公式，计算 Sheet1 中的“三月份销售统计表”中的销售金额，并将结果 填入到该表的“销售金额”列中。

计算方法：销售金额 = 产品单价 * 销售数量 4. 使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计 表”中的总销售额，并将结果填入该表的“总销售额”列。 5. 在 Sheet1 中，使用 RANK 函数，在“分部销售业绩统计”表中，根据“总销售额” 对各部门进行排名，并将结果填入到“销售排名”列中。

6. 将 sheet1 中的“三月份销售统计表”复制到 Sheet2 中，对 Sheet2 进行高级筛选。 要

求： （1）筛选条件为：“销售数量”－ >3、“所属部门”－ 市场 1 部、“销售金额”- >1000。

（2）将筛选结果保存在 Sheet2 中。 注意：

（1）无需考虑是否删除或移动筛选条件。

（2）复制过程中，将标题项“三月份销售统计表”连同数据一同复制。 （3）数据表必须顶格放置。 7. 根据 Sheet1 的“三月份销售统计表”中的数据，新建一个数据透视图 Chart1。 要

求：

（1）该图形显示每位经办人的总销售额情况。 （2）x 坐标设置为“经办人”。

（3）数据区域设置为“销售金额”。

前言

139

（4）求和项为“销售金额”。

（5）将对应的数据透视表保存在 Sheet3 中。

二、解答

步骤 1：操作方法参见“典型试题 1”中的步骤 1。 步骤 2：选中 Sheet1 工作表中的 G3 单元格，单击插入函数按扭，打开“插入函数” 对话框，在“选择类别”下拉列表框中选择“查找与引用”，在“选择函数”列表框 中选择“VLOOKUP”函数，单击“确定”按钮后，弹出“函数参数”对话框。输入 “ Lookup_value ” 参 数为 “ F3 ”，“ Table_array ” 参 数为 “ $A$3:$B$10 ”， “Col_index_num”参数为“2“，Range_lookup”参数为“FALSE””，如图 6-38 所示。 再按“确定”按钮，即可查找到产品型号为“A01”的产品名称（卡特扫描枪）。此时， 公式编辑栏显示“=VLOOKUP(F3,$A$3:$B$10,2,FALSE)”。 双击 G3 单元格的填充柄，即可查找到其余产品型号的产品名称。 在 H3 单元格中，输入公式“=VLOOKUP(F3,$A$3:$C$10,3,FALSE)”，再按回车键确认， 即可查找到产品型号为“A01”的产品单价（368）。双击 H3 单元格的填充柄，即可查 找到其余产品型号的产品单价。

说明：VLOOKUP 函数的功能和用法，请参见上一节的相关内容。

图 6-38 “函数参数”对话框

步骤 3：先选中 L3:L44 单元格区域，再输入公式“=H3:H44*I3:I44”，然后同时

按组合键 Shift+Ctrl+Enter，此时，公式编辑栏显示“{=H3:H44*I3:I44}”。