数据分析与可视化实践- 课件 第4章 商品销售情况分析

第4章商品销售情况分析序言PREFACE本章系统介绍怎样利用Excel对商品销售情况进行分析与可视化，通过对商品销售额、商品销售量、商品利润、商品价格等的分析及可视化的展示，读者可以较好的掌握商品销售额环比、区域商品销售额对比、商品销售额目标达成率分析、按类别分析销售量、销售量与销售人员相关性、商品利润率、商品价格变化趋势等分析的方法，其结果帮助企业及时洞察市场动向，发现企业销售过程中的问题，从而快速了解市场需求、针对性的配送货品、主动调货、预测市场需求、计算安全库存、提前追单补货、提前进行促销(调价)、调整营销战略等，这样就可以有效占领市场，从而利益最大化。PART01商品销售额分析与可视化PART02商品销售量分析与可视化PART03商品利润分析与可视化PART04商品价格分析与可视化目录CONTENTS销售数据分析随着互联网大数据时代的发展，数据作为一种新型生产要素，已经和土地、劳动力、资本、技术等并列成为了企业重要的生产力之一。企业经营其实简单来说就是做买卖，有了买卖自然就产生了销售数据，那怎么能让这些销售数据产生价值呢？答案就是数据分析。通过对销售数据的分析，可以帮助企业及时洞察市场动向，发现企业销售过程中的问题，调整营销战略。但在实际分析过程中，很多企业在解读销售数据上存在很大的问题。很多企业以为的销售数据分析就是将月度、年度销售数据统计汇总，然后简单对比，得出结论，这样数据分析只能描述表层的现象，无法深入发现问题。销售数据分析主要就是从整体销售额/利润分析、产品线、价格体系这三个角度出发进行分析。在动手分析前，我们要先了解销售数据包含哪些数据，以零售企业为例，销售数据一般包括：销售日期、销售区域、销售地点、经销商、渠道分类、产品系列、产品名称、产品价格、销售额、销售数量等数据。商品销售额分析与可视化1PART01销售额分析指标与历史同时期比较，例如2020年7月份与2019年7月份相比，叫同比。与上一统计段比较，例如2022年7月份与2022年6月份相比较，叫环比。环比增长率＝（本期数－上期数）/上期数*100％反映本期比上期增长了多少同比增长率＝（本期数－同期数）/同期数*100％指和去年同期相比较的增长率。注意这里对比的时期长度一定要保持一致。销售额的分析指标包括销售增长率（环比、同比），销售达成率等。商品销售额环比分析例4-1下表为某企业2021-2022年度各月销售额数据，请利用可视化工具展示2022年各月份销售数据对比。表4-12021-2022年各月份销售额

1月2月3月4月5月6月7月8月9月10月11月12月2021年9176908708127109848718927699089009852022年675768992804955892810971702856849863分析：要对各月份数据进行对比，就要增长率数据，但表中并没有直接给出数据，需要我们分别进行计算才能使用，要对比数据最常用的就是柱状图和折线图，这里有数据，有比率，我们选择组合图来全面展示数据。销售数据的区域分析包括那些部分？分析企业的销售区域及各区域的表现，检索重点区域、发现潜在市场,提出客户下阶段区域布局策略。区域分布对重点区域的营销状况予以重点分析，解析该区域的发展走势和结构特点,为未来在重点区域的发展提供借鉴。重点区域分析对增长或者下跌明显的区域予以重点分析，总结经验教训,以期避免潜在的威胁或者抓住机会。区域销售异动分析将重点区域中的产品结构进行时间上的横向对比，进行多要素复合分析。区域—产品分析Excel数据透视表与切片器①数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关。之所以称为数据透视表，是因为可以动态地改变它们的版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。②切片器是一个筛选器，跟在图表里选择筛选的效果一样。不同的是它可以链接多个透视表或透视图。销售数据分析例4-2表4-展示了某企业2022年部分销售数据明细表，请以此数据创建动态可视化图表，展示指定地区的各月份销售额数据。表4-2某企业2022年部分销售额数据明细地区日期类别数量销售额成都2022/1/1长裤5861重庆2022/1/1长T51805杭州2022/1/1长T31203杭州2022/1/1长裤2854重庆2022/1/1T恤5549重庆2022/1/1长裤4382重庆2022/1/1长T4998重庆2022/1/1长T51851成都2022/1/1T恤51541分析：动态图表一般利用函数或者数据透视图来创建，这里的数据特点是明细单，所以比较适合先用数据透视表对数据进行清洗整理，然后再使用切片器进行动态分类。商品销售额目标达成率分析例4-3某企业1-6月销售额数据如表所示，用图表展示1-6月实际销售额与目标销售额的对比情况，要求直观反映实际销售额与目标销售额的差距，清晰展示目标达成率的具体数值。表4-3某企业1-6月销售额数据

1月2月3月4月5月6月计划销售额9000010000095000880009100096000实际销售额800009200090125900008930086369分析：根据数据特点和分析目标，可以选用温度计型的柱状图或者条形图，另外目标达成率需要商品销售量分析与可视化2PART02按类别分析销售量例4-4某超市生鲜组12月销售数据如表所示，请分析各产品销售量贡献率。表4-4某超市生鲜组12月销售数据产品类别水果蔬菜海鲜冷冻食品猪肉牛肉羊肉销售量（吨）883.1530.04443.06267.2563.0936.3626.93分析：通过观察表4-4中的数据，发现表中后3种产品都属于肉类，这时的贡献率比较我们可以采用复合条饼图来实现可视化展示。VLOOKUP函数VLOOKUP函数是Excel中的一个纵向查找函数，可以用来核对数据，多个表格之间快速导入数据等函数功能。功能是按列查找，最终返回该列所需查询列序所对应的值。该函数的语法规则如下：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)表4-5参数说明参数简单说明输入数据类型lookup_value要查找的值数值、引用或文本字符串table_array要查找的区域数据表区域col_index_num返回数据在查找区域的第几列数正整数range_lookup模糊匹配/精确匹配TRUE（或不填）/FALSE销售量与销售人员等数据相关性分析例4-5表是9名员工在1-6月的销售量数据，请使用动态可视化图表展示不同员工1-6月的销售情况，能够显示不同月份的销售量差距，明确显示每人1-6月的销量的平均值。表4-6某部门上半年员工销售量数据员工姓名1月2月3月4月5月6月张春玲14286103133166200胡雪欣175758915813484马晓丹681456490116170李龙福17914013493198130王东升191551781018070赵少平10718315215315995郭海峰182150171152138133杨倩倩19710213910869145周建峰20080195167138172分析：如果分别为9名员工制作9张表，太麻烦了，这里我们将员工姓名由列表控件来控制动态图表，这样就只需要制作一张带平均线的柱状图即可。商品利润分析与可视化3PART03商品利润分析利润分析是以一定时期的利润计划为基础，计算利润增减幅度，查明利润变动原因和提出增加利润的措施等工作。通过利润分析可促进企业在遵守国家方针政策和财经纪律的情况下，改善经营管理，挖掘内部潜力，厉行增产节约，降低产品成本，提高利润水平。利润分析包括利润总额分析和利润率分析。利润总额分析是指以本期实际利润总额与计划相比，考察利润总额计划的完成情况。与上年同期相比，考察利润总额的增长速度，分析利润总额中各构成部分的变动情况。它包括：产品销售利润分析;其它销售利润分析;营业外收支分析。利润率分析指对产品的成本，价格，税率和结构的变动分析。通过利润率指标变动的分析，找出利润率指标变动的原因，作出正确评价，并采取措施，提高利润水平。商品利润分析（1）利润率（毛利）=（销售收入-销售成本）/销售收入

（2）利润率分析的目的：

①寻找提升利润的空间：在销售额恒定的情况下，调整销售结构，多做高毛利品种来替代低毛利品种。

②及时发现各经营环节的问题:及时发现具体品种（厂商）的异常毛利率，进一步寻求解决办法。商品利润率分析例4-6表4-展示了某企业的部分销售数据明细，在不改动原表的情况下，请根据表中数据统计1-9月总销量和总销售额以及每月的利润率，分析利润和销量以及销售额变化的相关性。表4-7某企业部分销售数据明细销售月份产品规格销售数量销售额成本01月2201A81207,96457,01701月2202A26231,713108,09201月2201A18316,24577,79501月2202A50300,884108,09202月2203A60207,964195,83802月2203A69212,389199,16502月2203A1141,592185,46302月2203A90215,712199,19102月2204A84111,504185,827..............................分析：明细表必须经过数据清洗、加工才能使用，所以需要先使用数据透视表统计出每月的汇总数据，本题要求不改动原表，所以利润率的计算只能放到数据透视表里去计算了，这里需要用到数据透视表的“添加计算项”功能。商品利润与其他数据相关性分析前面的例子中，商品利润仅仅和销售量以及产品成本相关，实际销售中，影响利润的因素有很多，例如商品的销售成本，比如损耗、促销费用等，还有税金等因素，都需要综合考量例4-7表4-展示了某企业上半年月度销售表，内容包括了销售收入以及各种成本，请分析商品利润率随表中字段变化的相关性。表4-8上半年销售表

销售收入物料成本销售费用税金1月1024376223183752月1251065656003613月1330865014143054月1578569065644935月1689379484193436月192308421301584分析：相关性分析使用组合图表来进行分析，表中给出的参数较多需要公式计算利润及利润率。利润=销售收入-物料成本-销售费用-税金，利润率=利润/销售收入。分析结果：从图中可以看出，①利润随着销售收入的增长而增长，正相关。②利润和物料成本以及销售费用等成本却并不明显相关，这说明企业的成本管理有一定问题。③利润率随着利润的增长而增长。商品价格分析与可视化4PART04价格的影响因素价值决定价格。第三，竞争也在一定程度上决定价格。第二，供求关系决定价格企业利润最重要的决定因素就是价格，价格每上升一个点，利润都会有数倍的增长。这里指的价格是销售价格，很多人的理解误区就是价格卖得越高，利润就越高。那么价格是由什么决定的呢？。商品价格变化趋势例4-8某产品2022年1-9月价格变动情况如表4-所示，请使用可视化图表展示价格变动区间，将平均值上下的价格区分显示，自动标识最低价格和最高价格。表4-9某产品2022年1-9月价格变动情况

1月2月3月4月5月6月7月8月9月价格86126639812011713193120分析：数据很简单，要求很多，需要添加辅助列来实现。以平均线区分显示数据，可以使用渐变线来实现，我们在本例中还要实现随源数据增加而自动更新的动态图表。在数据表添加最高线、最低线、平均线、高低点四行，为了方便后期添加数据，数据区域选择为整个第二行即MAX(2:2)、MIN(2:2)、AVERAGE(2:2)。高低点的公式为“=IF(O