李东阳是某家用电器企业的战略规划人员,正在参与制订本年度的生产与营销计划。为此,他需要对上一年度不同产品的销售情况进行汇总和分析,从中提炼出有价值的信息。根据下列要求,帮助李东阳运用已有的原始数据完成上述分析工作。
( 1 )在考生文件夹下, 将文档“ Excel 素材 .xlsx ”另存为“ Excel.xlsx ”(“ .xlsx ”为扩展名),之后所有的操作均基于此文档,否则不得分。
( 2 )在工作表“ Sheet1 ”中,从 B3 单元格开始,导入“数据源 .txt ”中的数据,并将工作表名称修改为“销售记录”。
( 3 )在“销售记录”工作表的 A3 单元格中输入文字“序号”,从 A4 单元格开始,为每笔销售记录插入“ 001 、 002 、 003 ……”格式的序号;将 B 列(日期)中数据的数字格式修改为只包含月和日的格式( 3/14 );在 E3 和 F3 单元格中,分别输入文字“价格”和“金额”;对标题行区域 A3:F3 应用单元格的上框线和下框线,对数据区域的最后一行 A891:F891 应用单元格的下框线;其他单元格无边框线;不显示工作表的网格线。
( 4 )在“销售记录”工作表的 A1 单元格中输入文字“ 2012 年销售数据”,并使其显示在 A1:F1 单元格区域的正中间(注意:不要合并上述单元格区域);将“标题”单元格样式的字体修改为“微软雅黑”,并应用于 A1 单元格中的文字内容;隐藏第 2 行。
( 5 )在“销售记录”工作表的 E4:E891 中,应用函数输入 C 列(类型)所对应的产品价格,价格信息可以在“价格表”工作表中进行查询;然后将填入的产品价格设为货币格式,并保留零位小数。
( 6 )在“销售记录”工作表的 F4:F891 中,计算每笔订单记录的金额,并应用货币格式,保留零位小数,计算规则为:金额 = 价格×数量×( 1- 折扣百分比),折扣百分比由订单中的订货数量和产品类型决定,可以在“折扣表”工作表中进行查询,例如某个订单中产品 A 的订货量为 1510 ,则折扣百分比为 2% (提示:为便于计算,可对“折扣表”工作表中表格的结构进行调整)。
( 7 )将“销售记录”工作表的单元格区域 A3:F891 中所有记录居中对齐,并将发生在周六或周日的销售记录的单元格的填充颜色设为黄色。
( 8 )在名为“销售量汇总”的新工作表中自 A3 单元格开始创建数据透视表,按照月份和季度对“销售记录”工作表中的三种产品的销售数量进行汇总;在数据透视表右侧创建数据透视图,图表类型为“带数据标记的折线图”,并为“产品 B ”系列添加线性趋势线,显示“公式”和“ R2 值”(数据透视表和数据透视图的样式可参考考生文件夹中的“数据透视表和数据透视图 .jpg ”示例文件);将“销售量汇总”工作表移动到“销售记录”工作表的右侧。
( 9 )在“销售量汇总”工作表右侧创建一个新的工作表,名称为“大额订单”;在这个工作表中使用高级筛选功能,筛选出“销售记录”工作表中产品 A 数量在 1550 以上、产品 B 数量在 1900 以上以及产品 C 数量在 1500 以上的记录(请将条件区域放置在 1-4 行,筛选结果放置在从 A6 单元格开始的区域)。