《Excel数据分析与应用》课件-项目5 商品分析

主讲：马元元西安电子科技大学出版社项目5

商品分析目

录项目概述任务1：商品销售额分析任务2：商品销售量分析任务3：商品毛利润分析任务4：商品促销情况分析任务5：工作日和周末商品销售额对比任务6：商品毛利润详细分析拓展延伸—预测商品销售量课后技能训练西安电子科技大学出版社

项目概述西安电子科技大学出版社西安电子科技大学出版社项目背景

“和美家”连锁超市某分店收集了某周的销售数据，现在想对每种大类商品的销售情况做统计与对比分析，以便发现销售规律，提升店铺销售和盈利水平。西安电子科技大学出版社“和美家”连锁超市某分店一周各大类商品销售情况。项目演示西安电子科技大学出版社“和美家”连锁超市某分店一周各大类商品销售情况。项目演示西安电子科技大学出版社“和美家”连锁超市某分店一周各大类商品销售情况。项目演示思维导图西安电子科技大学出版社思政聚焦—大数据抗疫专家—贾晓丰/2020/0909/c4670a172758/page.htm西安电子科技大学出版社思政聚焦—大数据抗疫专家—贾晓丰学习贾晓丰同志的抗疫工作，深厚的技术功底、对行业深入的认知、对祖国、对人民强烈的责任感和热爱，以及在压力面前勇于担当、勇于探索的精神值得我们每一个大数据人学习。思考与讨论：你还知道哪些大数据工作者以及他们的事迹吗？他们的身上表现出哪些优秀的品质和精神值得我们学习呢？西安电子科技大学出版社任务5.1商品销售额分析西安电子科技大学出版社任务描述对3月22日—3月28日一周内各大类商品的销售额进行分析。任务分析（1）商品销售额排行分析（2）商品销售额占比分析任务5.1：商品销售额分析西安电子科技大学出版社1.统计每种大类商品的销售额。（1）新建数据透视表。（2）各大类销售额排序。（3）格式化表格。5.1.1商品销售额排行分析西安电子科技大学出版社2．绘制各大类商品销售额排行条形图。（1）绘制柱形图。（2）添加数据标签。5.1.1商品销售额排行分析西安电子科技大学出版社本节要绘制各大类商品销售额占比饼图，利用5.1.1节统计得到的数据即可。（1）绘制复合饼图。（2）添加数据标签。（3）套用图表样式。5.1.2商品销售额占比分析西安电子科技大学出版社（3）套用图表样式。（4）个性化美化图表，修改图表标题。在周销售额占比中，日配占比最多占23%，其次是蔬果占21%，休闲和粮油都占14%，洗化11%，酒饮9%，肉禽和熟食占比很低，分别为6%和2%。5.1.2商品销售额占比分析西安电子科技大学出版社任务5.2商品销售量分析西安电子科技大学出版社任务描述对3月22日—3月28日一周内各大类商品的销售量进行分析，要求有数据有图表。任务分析（1）商品销售量排行分析（2）商品销售量占比分析任务5.2：商品销售量分析西安电子科技大学出版社统计商品销售量排行的方法和5.1.1节统计商品销售额排行的步骤大致相同，不同的是在新建数据透视表之后，在【数据透视表字段设置】面板中，将【大类名称】拖动到【行】位置，将【销售数量】拖动到【值】位置。5.2.1商品销售量占比排行分析西安电子科技大学出版社使用图5-12所示数据，使用5.1.2节所示方法绘制复合饼图如图所示。商品销售额占比最大的是日配类，占到35%，其次是蔬果和粮油类，分别占19%和14%，这三类一共占到68%。接下来是休闲和酒饮占12%和10%，洗化类占6%，熟食和肉禽都只占2%。5.2.2商品销售量占比分析西安电子科技大学出版社任务5.3商品毛利润分析西安电子科技大学出版社任务描述对3月22日—3月28日一周内各大类商品的毛利润进行分析，要求有数据有图表。任务分析（1）商品毛利润排行分析（2）商品毛利润占比分析任务5.3：商品毛利润分析西安电子科技大学出版社1.统计商品毛利润。（1）新建数据透视表。（2）各大类销售额排序。（3）格式化表格。5.3.1商品毛利润排行分析西安电子科技大学出版社2.绘制各大类商品毛利润排行条形图。毛利润最高的商品是日配类，达到3378.33，接着是蔬果、休闲和酒饮，为2600.69、1630.63、1310.2，洗化和粮油以及肉禽分别是996.8、685.25、523.21，最低是熟食只有272.51。5.3.1商品毛利润排行分析西安电子科技大学出版社使用图5-16所示数据，使用5.1.2节所示方法绘制复合饼图如图5-18所示。毛利润占比最大的是日配类，占30%，接着是蔬果和休闲，分别占23%和14%，这三者共占67%。酒饮占11%，洗化、粮油分别占9%和6%，肉禽和熟食分别占5%和2%。5.3.2商品毛利润占比分析西安电子科技大学出版社任务5.4商品促销情况分析西安电子科技大学出版社任务描述统计各大类商品销售额中促销销售额占比，要求有数据有图表。任务分析（1）统计各大类商品促销商品和非促销商品的销售额（2）统计促销商品的销售金额在总销售额中的占比任务5.4：商品促销情况分析西安电子科技大学出版社1.统计各大类商品促销情况数据统计。使用数据透视表统计各大类商品促销和非促销商品的销售额，然后计算各大类商品中促销商品的销售额占比。（1）新建数据透视表（2）计算促销销售额占比。（3）格式化表格。任务5.4：商品促销情况分析西安电子科技大学出版社2．绘制各大类商品促销情况统计图表（1）绘制组合图。（2）修改图表类型。任务5.4：商品促销情况分析西安电子科技大学出版社2．绘制各大类商品促销情况统计图表（3）添加数据标签。（4）修改标题。促销销售额占比最高的是日配类，占41.55%，接着是洗化和粮油，分别占39.85%和31.99%，休闲占23.58%，酒饮和蔬果占8.22%和4.02%，熟食和肉禽没有促销。任务5.4：商品促销情况分析西安电子科技大学出版社任务5.5工作日和周末商品销售额对比西安电子科技大学出版社任务描述分析3月22日—3月28日一周内顾客在工作日和周末的消费偏好。任务分析计算工作日和周末各大类商品日均销售额，绘制可视化图表。任务5.5：工作日和周末商品销售额对比西安电子科技大学出版社1.统计各大类商品在工作日和周末的日均销售额。使用数据透视表统计各大类商品促销和非促销商品的销售额，然后计算各大类商品中促销商品的销售额占比。（1）新建数据透视表。（2）计算工作日和周末日均销售额。（3）设置数字格式。（4）格式化表格。任务5.5：工作日和周末商品销售额对比西安电子科技大学出版社2．各大类商品工作日和周末日均销售额柱形图（1）绘制柱形图。（2）添加数据标签并设置数据标签数据位数。任务5.5：工作日和周末商品销售额对比西安电子科技大学出版社（3）修改标题。将图表标题修改为“工作日和周末商品销售额”，完成后图表如图所示。本周日配、肉禽、蔬果、洗化、休闲这几类商品工作日的日均销售额大于周末的日均销售额，而酒饮、粮油、熟食在周末的日均销售额大于工作日日均销售额。任务5.5：工作日和周末商品销售额对比西安电子科技大学出版社任务5.6商品毛利润详细分析西安电子科技大学出版社任务描述找到毛利润最大的大类产品。对于这类产品，将商品分析的粒度减小到中类，对比分析这类产品中毛利润最大的两种中类产品的销售情况，分析两种中类产品在销售过程中促销的作用。任务分析（1）毛利润分析--包括大类精确到中类（2）毛利润最大的大类产品中，对比分析毛利润最大的两种中类产品的销售额（3）两种中类产品的促销与非促销销售额分析任务5.6：商品毛利润详细分析西安电子科技大学出版社1.统计各中类商品毛利润（1）新建数据透视表。（2）修改数据表现形式。（3）修改表格格式。5.6.1各中类毛利润分析西安电子科技大学出版社2.绘制各中类毛利润树状图。（1）绘制树状图。（2）添加数据标签。（3）修改图表标题。大类毛利润排行为日配、蔬果、休闲、洗化、酒饮、粮油、肉禽、熟食。日配类毛利润最大的分别是常温乳品、冷藏乳品、蛋类等等。5.6.1各中类毛利润分析西安电子科技大学出版社常温乳制品和冷藏乳制品是日配类销量毛利润最大的中类商品，绘制两类乳制品的销售额曲线，观察两类乳制品销售规律。1.统计常温乳品和冷藏乳品每日销售数据（1）筛选常温乳品和冷藏乳品数据。5.6.2常温乳品和冷藏乳品的销售额对比西安电子科技大学出版社（2）新建数据透视表。（3）格式化表格。5.6.2常温乳品和冷藏乳品的销售额对比西安电子科技大学出版社2.绘制常温乳品和冷藏乳品销售额对比图表。（1）绘制折线图。（2）添加数据标签并设置位置。5.6.2常温乳品和冷藏乳品的销售额对比西安电子科技大学出版社（3）将折线变为平滑曲线。（4）修改图表标题。5.6.2常温乳品和冷藏乳品的销售额对比西安电子科技大学出版社作为日配商品中毛利润最高的两个中类，销售曲线完全不同，推测因为促销原因导致产品销售曲线差别较大，绘制图形比较分析两类乳制品销售额和促销的关系。（1）新建数据透视表。（2）组织销售额数据。（3）格式化表格。5.6.3常温乳品和冷藏乳品销售额与促销的关系西安电子科技大学出版社2.绘制常温乳品和冷藏乳品促销与非促销销售额对比图表。（1）绘制簇状柱形图。（2）将折线变为平滑曲线。（3）更改数据系列颜色。5.6.3常温乳品和冷藏乳品销售额与促销的关系西安电子科技大学出版社（4）添加数据标签。（5）修改图表标题。冷藏乳品的促销和非促销销售额曲线都比较平缓，变化不大；常温乳品的两条销售额曲线变化都比较明显。3月24日周三常温乳品的促销销售额最高，达到653.3,3月25日的促销销售额仅为124.9。非促销销售额3月22日周一最高359.9,3月25日周四最低，仅18.4。5.6.3常温乳品和冷藏乳品销售额与促销的关系西安电子科技大学出版社拓展延伸预测商品销售量西安电子科技大学出版社任务描述“和美家”超市某分店想通过2021年1月1日到3月28日的水果的销售量预测未来四周水果销售量，以便安排进货。任务分析可以使用函数、绘制预测曲线图、预测工作表等几种方式通过以往多个周期的已有数据预测未来周期的数据，本节采用预测工作表方式。拓展延伸：预测商品销售量西安电子科技大学出版社1.制作水果销量预测数据表。（1）得到水果销售数据。打开【0322-28消费数据】工作表，利用【筛选】，先在大类中选择【蔬果】，然后在种类中选择【水果】类，得到水果的销售数据。（2）新建【拓展-水果销售数据】工作表。创建新的工作表并重命名为【拓展-水果销售数据】，只粘贴值，得到所有水果的销售数据工作表，包括标题行共有206行数据。拓展延伸：预测商品销售量西安电子科技大学出版社（3）新建【拓展-水果销量预测】工作表。创建新的工作表并重命名为【拓展-水果销量预测】，打开【2021每周水果销量统计】，将里面的所有数据复制到【拓展-水果销量预测】数据工作表中，则数据如图所示。拓展延伸：预测商品销售量西安电子科技大学出版社（4）计算本周水果销售量。【水果数据工作表】中，，选中N207，输入公式“=SUM(N2:N206)”得到本周水果的总销售量为210.891千克。（5）将本周销售量加入工作表。在【拓展-水果销量预测】增加新行，输入“13”及日期区间，将刚才计算得到的本周水果销售量210.891到工作表。（6）输入预测周数和日期数据。在【拓展-水果销量预测】增加新行，输入14、15、16、17以及日期区间。（7）格式化表格。完成后效果如图所示。拓展延伸：预测商品销售量西安电子科技大学出版社2.绘制折线图预计水果销售量（1）创建预测工作表。单击【拓展-水果销量预测】工作表中有数据的任意单元格，在【数据】选项卡的【预测】命令组中，单击【预测工作表】图标，弹出【创建预测工作表】对话框如图所示。拓展延伸：预测商品销售量西安电子科技大学出版社（2）计算预测值并绘制折线图。单击【选项】，将【置信区间】设置为50%，如图5-63所示。单击【创建】按钮，创建新工作表并自动计算出序号为14、15、16、17的预测值，同时自动绘制出销售额的折线图，如图5-64所示，将新工作表重命名为“拓展-自动预测工作表”。拓展延伸：预测商品销售量西安电子科技大学出版社（3）添加数据标签、图表标题。选中图表区，单击图表区右上角的绿色按钮，在弹出的列表框中勾选【图表标题】，【数据标签】，【数据表】复选框，如图所示。拓展延伸：预测商品销售量