《AIGC高效办公：Excel数据处理与分析》课件 第7章 竞争对手数据分析

第7章竞争对手数据分析7.1案例要求内容导航7.2案例准备7.3案例操作7.4综合实训7.5课后练习案例要求某经营童鞋的企业为了提高自身的竞争力，并在与竞争对手的竞争中占得先机，需要了解当地主要竞争对手的运营情况，包括竞争商品的交易情况、竞争企业的整体销售情况、竞争企业的商品销售贡献情况、主要竞争商品的销售情况等。现需要该企业的数据分析部门利用收集到的相关数据对这些情况进行分析并得出结论，为企业制订新的运营策略提供有利依据。左图所示为本案例的部分数据分析效果展示。7.1案例要求内容导航7.2.1竞争对手的类型

7.2.2动销率与售罄率7.2.3商品销售贡献7.2案例准备7.3案例操作7.4综合实训7.5课后练习7.2.1竞争对手的类型竞争对手之间的竞争性主要体现在对市场资源的争夺上。企业的竞争对手一般可以分为3类，即与企业竞争共同市场的长期固定竞争对手、在经营活动中与企业有局部竞争的局部竞争对手，以及就某一特定资源与企业有竞争的暂时性竞争对手，如下图所示。7.2.2动销率与售罄率企业在分析竞争企业的销售情况时，一方面，可以从基础的数据指标（如销售额、销售品种数、销量等）查看其整体的销售水平；另一方面，可以借助动销率和售罄率指标来更深入地分析其销售质量。动销率是指在一定周期内有销量的商品品种数与全部上架销售的商品总品种数的比率，可以用来反映商品品种的有效性。动销率越高，有效的商品品种越多；动销率越低，无效的商品品种越多。售罄率是指企业的商品的销售数量占生产数量或进货数量的比例，可以用来衡量商品的销售速度，查看商品在市场上受欢迎的程度。相同生产数量或进货数量的情况下，售罄率越高，商品的销售情况越好；售罄率越低，商品的销售情况越差。7.2.3商品销售贡献商品销售贡献是指在特定时期内，某个商品对企业销售额和利润的贡献程度。商品销售贡献的计算公式：销售贡献=销售收入-可变成本。该公式中，销售收入指的是某个商品在特定时期内实际的销售额；可变成本指的是该商品在销售过程中相关的支出，如采购成本、运输成本、促销费用等。例如，某商品在一个月内的实际销售额为10000元，其可变成本为3000元，那么该商品的销售贡献就是7000元。通常来说，企业会按照每个商品的销售贡献来制订商品组合、促销策略等方面的决策。对于销售贡献较高的商品，企业可以加大宣传力度、加强库存管理等，以便最大化其贡献能力；对于销售贡献较低的商品，企业则可以采取降价、清仓等方式，或调整其定位和目标客户群体，以便提高其销售贡献能力。7.1案例要求内容导航7.3.1分析竞争品牌交易数据

7.3.2分析竞争企业的整体销售数据

7.3.3借助文心一言计算并分析竞争企业的商品销售贡献

7.3.4分析竞争商品的销售高峰7.2案例准备7.3案例操作7.4综合实训7.5课后练习7.3.1分析竞争品牌交易数据本案例收集了本企业运营的品牌和某竞争品牌在5月份的交易数据。下面在Excel2019中利用这些数据并结合数据透视图，分析竞争品牌的交易变化趋势，并对比本品牌和竞争品牌的交易表现。其具体操作如下。STEP01打开“竞争品牌数据.xlsx”素材文件，以表中所有数据为数据源，在新工作表中创建数据透视表，将“日期”字段添加到“行”列表中，将“竞争品牌交易额/元”字段添加到“值”列表中，如右图所示。7.3.1分析竞争品牌交易数据STEP02按前文所述方法，在数据透视表的基础上创建数据透视图，类型为折线图。按前文所述方法，删除图表标题和图例，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，并调整图表尺寸至合适，效果如下图所示。STEP03按前文所述方法，为图表添加横坐标轴和纵坐标轴标题，将内容分别修改为“日期”和“交易额/元”，并调整坐标轴标题的位置至合适。将数据系列的线条设置为“绿色”，“0.25磅”，效果如上图所示。7.3.1分析竞争品牌交易数据STEP04在“数据透视图字段”任务窗格中将“本品牌交易额/元”字段添加到“值”列表框中，在数据透视图中选择本品牌交易额对应的数据系列，将其线条设置为“橙色”“0.25磅”“虚线-长划线”。由图可知，本品牌5月份的交易额为100000～300000元，与竞争品牌相比交易体量更小。但是，本品牌的交易额数据在5月是呈稳步上升的，5月底已经接近了竞争品牌的交易额，说明本品牌越来越得到市场的认可，竞争力日益提升。STEP05按前文所述方法，在图表顶部添加图例对象，并适当调整图例的位置和折线图的大小，如上图所示。7.3.2分析竞争企业的整体销售数据分析竞争企业的销售数据，可以了解该企业的整体销售情况。本案例收集了竞争企业1-7月的销售数据，包括销售额、销售品种数、品种总数、销量、进货量等。下面在Excel2019中利用这些数据分析该企业的销售情况。其具体操作如下。STEP01打开“竞争企业销售数据.xlsx”素材文件，在G1单元格中输入“动销率”，选择G2:G8单元格区域，在编辑框中输入“=C2/D2”，按【Ctrl+Enter】组合键计算1—7月的动销率，如左图所示。7.3.2分析竞争企业的整体销售数据STEP02在H1单元格中输入“售罄率”，选择H2:H8单元格区域，在编辑框中输入“=E2/F2”，按【Ctrl+Enter】组合键计算1—7月的售罄率，如下图所示。STEP03按前文所述方法，以月份和销售额数据为数据源创建柱形图，将图表标题修改为“竞争企业的销售额”，添加横坐标轴和纵坐标轴标题，将内容分别修改为“月份”和“销售额/元”，在数据系列外侧添加数据标签，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，并调整图表尺寸至合适，如左图所示。7.3.2分析竞争企业的整体销售数据STEP04按上述方法，以月份和销量数据为数据源创建柱形图，将图表标题修改为“竞争企业的销量”，添加横坐标轴和纵坐标轴标题，将内容分别修改为“月份”和“销量/件”，在数据系列外侧添加数据标签，将数据系列的边框颜色设置为“绿色”，填充颜色设置为“无颜色”，图表中文字的字体格式设置为“方正兰亭纤黑简体”，调整图表尺寸至合适，如下图所示。STEP05按上述方法，以月份和动销率数据为数据源创建折线图，将图表标题修改为“竞争企业的动销率”，添加横坐标轴和纵坐标轴标题，将内容分别修改为“月份”和“动销率”，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，并调整图表尺寸至合适，如上图所示。7.3.2分析竞争企业的整体销售数据STEP06按上述方法，以月份和售罄率数据为数据源创建折线图，将图表标题修改为“竞争企业的售罄率”，添加横坐标轴和纵坐标轴标题，将内容分别修改为“月份”和“售罄率”，在数据系列上方添加数据标签，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，并调整图表尺寸至合适，如下图所示。STEP07将创建的4张图表放在一起，如上图所示。由图可知，该竞争企业1—3月的销售额和销量相对较低，4—6月的则上升明显，7月的又有大幅增长，近7个月的销售额和销量呈逐渐上升的趋势。7.3.3借助文心一言计算并分析竞争企业的商品销售贡献本案例收集了近7日竞争企业销量较高的25种商品的销售数据。下面利用文心一言了解销售贡献的含义和计算公式，然后在Excel2019中分析这些商品的销售贡献情况。具体操作如下。STEP01登录文心一言官方网站，在文本框中输入需求内容并提交，这里询问销售贡献的含义，并询问已知销售额和成本，能否计算出销售贡献。文心一言反馈的信息如上图所示。7.3.3借助文心一言计算并分析竞争企业的商品销售贡献STEP02打开“竞争企业商品贡献数据.xlsx”素材文件（配套资源:\素材文件\第7章\竞争企业商品贡献数据.xlsx），选择F2:F26单元格区域，在编辑框中输入“=D2*E2”，按【Ctrl+Enter】组合键计算各商品近7日的销售额，如下图所示。STEP03选择H2:H26单元格区域，在编辑框中输入“=F2-G2”，按【Ctrl+Enter】组合键计算各商品近7日的销售贡献，如上图所示。7.3.3借助文心一言计算并分析竞争企业的商品销售贡献STEP04按前文所述方法，表格中所有的数据为数据源，新工作表中创建数据透视表，将“类目”字段添加到“行”列表中，将“销售贡献/元”字段添加到“值”列表中，如下图所示。STEP05按前文所述方法，在数据透视表的基础上创建数据透视图，类型为饼图，删除图表标题和图例，降序排列饼图的扇区。在【数据透视图工具设计】/【图表样式】组中单击“更改颜色”按

，在弹出的下拉列表中选择“单色”栏下的第4种颜色，如上图所示。7.3.3借助文心一言计算并分析竞争企业的商品销售贡献STEP06按前文所述方法，添加数据标签，将标签的显示内容设置为“类别名称”“百分比”“显示引导线”，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，并调整图表尺寸至合适，效果如右图所示。由图可知，该竞争企业近7日内，做出主要销售贡献的商品类目是运动鞋和靴子，销售贡献占比分别达到了36%和25%。如果竞争企业的商品在质量、价格方面与本企业的商品相差无几，则说明这两种类目的商品在这段时间能够更好地被市场认可，因此本企业一方面可以考虑增加这两类商品的销售力度，另一方面可以适度减小拖鞋、棉鞋等销售贡献占比小的商品的销售力度。7.3.4分析竞争商品的销售高峰本案例采集了某主要竞争商品近一个月的销量和销售额数据。下面将一周7天作为7个时间段，在Excel2019中以周为周期汇总近一个月内该竞争商品在各时间段内的销售额和销量，找到该商品的销售高峰时间段。其具体操作如下。STEP01打开“竞争商品销售数据.xlsx”素材文件，在B列列标上单击鼠标右键，在弹出的快捷菜单中选择“插入”命令，然后在插入的B1单元格中输入“星期”。STEP02选择B2:B31单元格区域，在编辑框中输入“=TEXT(A2,"AAAA")”，表示根据日期返回对应的星期数据，按【Ctrl+Enter】组合键返回结果，效果如左图所示。7.3.4分析竞争商品的销售高峰STEP03按前文所述方法，以表格中所有的数据为数据源，在新工作表中创建数据透视表，将“星期”字段添加到“行”列表中，将“销量/件”字段和“销售额/元”字段添加到“值”列表中。STEP04选择数据透视表中星期日数据记录所在行，拖曳所选单元格区域的下边框至“总计”行上方，调整各行数据的显示顺序，如左图所示。STEP05按前文所述方法，在数据透视表的基础上创建数据透视图，类型为组合图。其中，销量数据系列对应的图表类型设置为“折线图”；销售额数据系列对应的图表类型设置为“簇状柱形图”，对应的轴设置为“次坐标轴”。7.3.4分析竞争商品的销售高峰STEP06按前文所述方法，将图例对象调整至图表顶部，添加横坐标轴、主要纵坐标轴和次要纵坐标轴的标题，将内容分别修改为“星期”“销量/件”和“销售额/元”。STEP07按前文所述方法，将销量数据系列的线条设置为“-1磅”“虚线-长划线”，将图表中文字的字体格式设置为“方正兰亭纤黑简体”，并调整图表尺寸至合适，效果如右图所示。由图可知，近一个月内该竞争商品在星期四和星期日两个时间段内的销售数据相对较差，而星期一和星期五是销售高峰时间段，其他时间段的销售表现也较为不错。如果本企业的商品需要开展推广和销售，可以考虑在星期四或星期日。7.1案例要求内容导航7.4.1分析男装竞争企业销售数据

7.4.2借助ExcelAI分析男装竞争商品数据7.2案例准备7.3案例操作7.4综合实训7.5课后练习7.4.1分析男装竞争企业销售数据分析竞争企业的销售数据可以了解该企业的销售情况，如整体销售业绩的优劣、各类商品的销售表现等。通过分析，企业可以了解市场需求、竞争企业的销售策略，从而能够及时调整销售策略。【实训背景】某男装企业收集了其主要竞争企业近一年的商品进货、销量和单价数据，现需要利用这些数据分析该竞争企业的商品销售额结构，并分析占比最大的商品的售罄率，以便深入了解该竞争企业的销售情况。【操作要求】（1）计算竞争企业各商品的销售额和售罄率数据；

（2）按商品类目分析各类商品的销售额占比；

（3）通过销售额占比分析占比最大的商品的售罄率数据。7.4.1分析男装竞争企业销售数据（1）利用销量和单价数据计算销售额；利用进货量和销量数据计算售罄率；

（2）建立数据透视表，汇总不同商品类目的销售额，然后创建数据透视图，以饼图的形式分析各类目商品的销售额占比；

（3）以休闲裤商品的名称和售罄率为数据源建立条形图，分析各休闲裤商品的售罄率。【操作思路】【参考效果】7.4.2借助ExcelAI分析男装竞争商品数据分析竞争商品数据能够了解该商品的销售数据和市场表现，对企业调整商品布局和销售策略有积极影响。【实训背景】某企业连续跟踪了主要竞争商品一个月的销售数据，收集了该商品近一个月每日的销量和销售额数据，现需要利用这些数据分析该商品近一个月的销量走