李东阳是某家用电器企业的战略规划人员，正在参与制订本年度的生产与营销计划。为此，他需要对上一年度不同产品的销售情况进行汇总和分析，从中提炼出有价值的信息。根据下列要求，帮助李东阳运用已有的原始数据完成上述分析工作。

(1) 将文档“Excel素材. xlsx”另存为“Excel.xlsx”(“.xlsx”为扩展名），之后所有的操作均基于此文档，否则不得分。

打开excel表格。

(2) 在工作表“Sheet1”中，从B3单元格开始，导入“数据源. txt”中的数据，并将工作表名称修改为“销售记录”。

点击“加载到“

将数据放置在工作表sheet1的B3单元格处

将工作表命名为销售记录

在2019版本里面导入数据总是会自动应用表格样式，此处先删除表格样式，并取消勾选筛选按钮

(3) 在“销售记录”工作表的A3单元格中输入文字“序号”，从A4单元格开始，为每笔销售记录插入“001、002、003……”格式的序号；将B列(日期）中数据的数字格式修改为只包含月和日的格式(3/14)；在E3和F3单元格中，分别输入文字“价格”和“金额”；对标题行区域A3:F3应用单元格的上框线和下框线，对数据区域的最后一行A891:F891应用单元格的下框线；其他单元格无边框线；不显示工作表的网格线。

①在A3单元格中输入“序号“

②从A4单元格开始，为每笔销售记录插入“001、002、003……”格式的序号

③将B列(日期）中数据的数字格式修改为只包含月和日的格式(3/14)

④在E3和F3单元格中，分别输入文字“价格”和“金额”

⑤对标题行区域A3:F3应用单元格的上框线和下框线，对数据区域的最后一行A891:F891应用单元格的下框线；其他单元格无边框线；不显示工作表的网格线。

Ⅰ、选中标题行，应用上框线和下框线

Ⅱ、对数据区域的最后一行A891:F891应用单元格的下框线

Ⅲ、其他单元格无边框线；不显示工作表的网格线

在视图选项卡下，取消勾选网格线

(4)在“销售记录”工作表的A1单元格中输入文字“2012年销售数据”，并使其显示在A1:F1单元格区域的正中间（注意:不要合并上述单元格区域)；将“标题”单元格样式的字体修改为“微软雅黑”，并应用于A1单元格中的文字内容；隐藏第2行。

①在“销售记录”工作表的A1单元格中输入文字“2012年销售数据”，并使其显示在A1:F1单元格区域的正中间（注意:不要合并上述单元格区域)

②将“标题”单元格样式的字体修改为“微软雅黑”，并应用于A1单元格中的文字内容

Ⅰ、修改标题单元格的样式

Ⅱ、应用于A1单元格中的文字内容

③隐藏第2行

(5）在“销售记录”工作表的E4:E891中，应用函数输入C列(类型）所对应的产品价格，价格信息可以在“价格表”工作表中进行查询;然后将填入的产品价格设为货币格式，并保留零位小数。

①输入C列(类型）所对应的产品价格

②将填入的产品价格设为货币格式，并保留零位小数

(6）在“销售记录”工作表的F4:F891中，计算每笔订单记录的金额，并应用货币格式，保留零位小数，计算规则为:金额=价格×数量×（1-折扣百分比)，折扣百分比由订单中的订货数量和产品类型决定，可以在“折扣表”工作表中进行查询，例如某个订单中产品A的订货量为1510，则折扣百分比为2%(提示:为便于计算，可对“折扣表”工作表中表格的结构进行调整)。

在“折扣表”工作表第一列依次插入1、1000、1500、2000。本题的思路是采用VLOOKUP函数的近似匹配，1-999的数值可以近似匹配到1、1000-1499的数值可以近似匹配到1000……因此查找值为“销售记录“工作表中的”数量“，查找范围则是”折扣表“的A列到E列，第三个参数是查找第几列，此时要嵌套一个IF函数，假如是A产品就查找第三列，B产品就查找第四列，C产品就查找第五列。

①先用IF函数确定VLOOKUP函数的第三个参数（即查找第几列）

②用VLOOKUP函数计算折扣

③计算每笔订单的金额，其中金额=价格×数量×（1-折扣百分比)

④注意到刚刚计算完成的两列没有下框线，记住要把它补齐

(7）将“销售记录”工作表的单元格区域A3:F891中所有记录居中对齐，并将发生在周六或周日的销售记录的单元格的填充颜色设为黄色。

①居中对齐

②将发生在周六或周日的销售记录的单元格的填充颜色设为黄色

本题的思路是，先用WEEKDAY函数判断该日是星期一到星期日的那一天，已知星期一返回1，星期二返回2……星期天返回7，因此返回的数值大于5则是周末。在用条件格式将返回值为6、7的日子整行填充为黄色。

第二个参数应用2的规则，这样周六周日会返回6、7

注意WEEKDAY函数的第一个参数，要锁定列（B列），但是不锁定行

(8）在名为“销售量汇总”的新工作表中自A3单元格开始创建数据透视表，按照月份和季度对“销售记录”工作表中的三种产品的销售数量进行汇总；在数据透视表右侧创建数据透视图，图表类型为“带数据标记的折线图”，并为“产品B”系列添加线性趋势线，显示“公式”和“R2值”(数据透视表和数据透视图的样式可参考考生文件夹中的“数据透视表和数据透视图. jpg”示例文件)；将“销售量汇总”工作表移动到“销售记录”工作表的右侧。

示例文件

①创建数据透视表

②按照月份和季度对“销售记录”工作表中的三种产品的销售数量进行汇总

观察样例图，将日期拖拽到“行”，类型拖拽到“列”，数量拖拽到“值”

右键→组合→选择月、季度→将季度调出来

选择带数据标记的折线图

为“产品B”系列添加线性趋势线，显示“公式”和“R2值”

“趋势线”是图表元素，因此点击图表右侧的加号，勾选趋势线，单击产品B即可添加

双击趋势线，出现了“设置趋势线格式”对话框，勾选显示公式、显示R2

最后，参照样例图，删除图中的网格线，设置图例靠下

(9) 在“销售量汇总”工作表右侧创建一个新的工作表，名称为“大额订单”；在这个工作表中使用高级筛选功能，筛选出“销售记录”工作表中产品A数量在1550以上、产品B数量在1900以上以及产品C数量在1500以上的记录（请将条件区域放置在1-4行，筛选结果放置在从A6单元格开始的区域)。

①在“销售量汇总”工作表右侧创建一个新的工作表，名称为“大额订单”

②创建筛选条件

③点击数据选项卡下的高级筛选