李东阳是某家用电器企业的战略规划人员,正在参与制订本年度的生产与营销计划。为此,他需要对上一年度不同产品的销售情况进行汇总和分析,从中提炼出有价值的信息。根据下列要求,帮助李东阳运用已有的原始数据完成上述分析工作。
(1) 将文档“Excel素材. xlsx”另存为“Excel.xlsx”(“.xlsx”为扩展名),之后所有的操作均基于此文档,否则不得分。
打开excel表格。
(2) 在工作表“Sheet1”中,从B3单元格开始,导入“数据源. txt”中的数据,并将工作表名称修改为“销售记录”。
- 考点1:导入数据
点击“加载到“
将数据放置在工作表sheet1的B3单元格处
将工作表命名为销售记录
- 考点2:取消表格样式
在2019版本里面导入数据总是会自动应用表格样式,此处先删除表格样式,并取消勾选筛选按钮
(3) 在“销售记录”工作表的A3单元格中输入文字“序号”,从A4单元格开始,为每笔销售记录插入“001、002、003……”格式的序号;将B列(日期)中数据的数字格式修改为只包含月和日的格式(3/14);在E3和F3单元格中,分别输入文字“价格”和“金额”;对标题行区域A3:F3应用单元格的上框线和下框线,对数据区域的最后一行A891:F891应用单元格的下框线;其他单元格无边框线;不显示工作表的网格线。
①在A3单元格中输入“序号“
- 考点3:设置单元格格式
②从A4单元格开始,为每笔销售记录插入“001、002、003……”格式的序号
③将B列(日期)中数据的数字格式修改为只包含月和日的格式(3/14)
④在E3和F3单元格中,分别输入文字“价格”和“金额”
⑤对标题行区域A3:F3应用单元格的上框线和下框线,对数据区域的最后一行A891:F891应用单元格的下框线;其他单元格无边框线;不显示工作表的网格线。
- 考点4:设置表格边框
Ⅰ、选中标题行,应用上框线和下框线
Ⅱ、对数据区域的最后一行A891:F891应用单元格的下框线
Ⅲ、其他单元格无边框线;不显示工作表的网格线
在视图选项卡下,取消勾选网格线
(4)在“销售记录”工作表的A1单元格中输入文字“2012年销售数据”,并使其显示在A1:F1单元格区域的正中间(注意:不要合并上述单元格区域);将“标题”单元格样式的字体修改为“微软雅黑”,并应用于A1单元格中的文字内容;隐藏第2行。
①在“销售记录”工作表的A1单元格中输入文字“2012年销售数据”,并使其显示在A1:F1单元格区域的正中间(注意:不要合并上述单元格区域)
②将“标题”单元格样式的字体修改为“微软雅黑”,并应用于A1单元格中的文字内容
- 考点5:修改标题单元格的样式
Ⅰ、修改标题单元格的样式
Ⅱ、应用于A1单元格中的文字内容
③隐藏第2行
(5)在“销售记录”工作表的E4:E891中,应用函数输入C列(类型)所对应的产品价格,价格信息可以在“价格表”工作表中进行查询;然后将填入的产品价格设为货币格式,并保留零位小数。
①输入C列(类型)所对应的产品价格
- 考点6:VLOOKUP函数
②将填入的产品价格设为货币格式,并保留零位小数
(6)在“销售记录”工作表的F4:F891中,计算每笔订单记录的金额,并应用货币格式,保留零位小数,计算规则为:金额=价格×数量×(1-折扣百分比),折扣百分比由订单中的订货数量和产品类型决定,可以在“折扣表”工作表中进行查询,例如某个订单中产品A的订货量为1510,则折扣百分比为2%(提示:为便于计算,可对“折扣表”工作表中表格的结构进行调整)。
在“折扣表”工作表第一列依次插入1、1000、1500、2000。本题的思路是采用VLOOKUP函数的近似匹配,1-999的数值可以近似匹配到1、1000-1499的数值可以近似匹配到1000……因此查找值为“销售记录“工作表中的”数量“,查找范围则是”折扣表“的A列到E列,第三个参数是查找第几列,此时要嵌套一个IF函数,假如是A产品就查找第三列,B产品就查找第四列,C产品就查找第五列。
- 考点7:VLOOKUP函数和IF函数的嵌套,以及VLOOKUP函数的近似匹配
①先用IF函数确定VLOOKUP函数的第三个参数(即查找第几列)
②用VLOOKUP函数计算折扣
③计算每笔订单的金额,其中金额=价格×数量×(1-折扣百分比)
④注意到刚刚计算完成的两列没有下框线,记住要把它补齐
(7)将“销售记录”工作表的单元格区域A3:F891中所有记录居中对齐,并将发生在周六或周日的销售记录的单元格的填充颜色设为黄色。
①居中对齐
②将发生在周六或周日的销售记录的单元格的填充颜色设为黄色
本题的思路是,先用WEEKDAY函数判断该日是星期一到星期日的那一天,已知星期一返回1,星期二返回2……星期天返回7,因此返回的数值大于5则是周末。在用条件格式将返回值为6、7的日子整行填充为黄色。
- 考点8:WEEKDAY函数
第二个参数应用2的规则,这样周六周日会返回6、7
- 考点9:条件格式
注意WEEKDAY函数的第一个参数,要锁定列(B列),但是不锁定行
(8)在名为“销售量汇总”的新工作表中自A3单元格开始创建数据透视表,按照月份和季度对“销售记录”工作表中的三种产品的销售数量进行汇总;在数据透视表右侧创建数据透视图,图表类型为“带数据标记的折线图”,并为“产品B”系列添加线性趋势线,显示“公式”和“R2值”(数据透视表和数据透视图的样式可参考考生文件夹中的“数据透视表和数据透视图. jpg”示例文件);将“销售量汇总”工作表移动到“销售记录”工作表的右侧。
示例文件
- 考点10:数据透视表
①创建数据透视表
②按照月份和季度对“销售记录”工作表中的三种产品的销售数量进行汇总
观察样例图,将日期拖拽到“行”,类型拖拽到“列”,数量拖拽到“值”
- 考点11:创建组
右键→组合→选择月、季度→将季度调出来
- 考点12:创建数据透视图
选择带数据标记的折线图
为“产品B”系列添加线性趋势线,显示“公式”和“R2值”
“趋势线”是图表元素,因此点击图表右侧的加号,勾选趋势线,单击产品B即可添加
双击趋势线,出现了“设置趋势线格式”对话框,勾选显示公式、显示R2
最后,参照样例图,删除图中的网格线,设置图例靠下
(9) 在“销售量汇总”工作表右侧创建一个新的工作表,名称为“大额订单”;在这个工作表中使用高级筛选功能,筛选出“销售记录”工作表中产品A数量在1550以上、产品B数量在1900以上以及产品C数量在1500以上的记录(请将条件区域放置在1-4行,筛选结果放置在从A6单元格开始的区域)。
- 考点13:高级筛选
①在“销售量汇总”工作表右侧创建一个新的工作表,名称为“大额订单”
②创建筛选条件
③点击数据选项卡下的高级筛选