Excel 商务数据分析 课件 项目四 商品销售情况分析

项目四商品销售情况分析项目描述对商品销售情况进行分析，一方面是对销售情况进行整体把控，了解店铺销售现状；另一方面是通过对对过往数据的对比分析，认识销售规律，发现问题和不足，通过不断改进，解决问题，使店铺运营朝着良性轨道发展。同时通过商品销售数据分析进行销售预测，战略规划，为上层决策提供量化依据。项目三中，小A已经学会对客户情况进行分析，对EXCEL函数和图表应用有了更深入的了解。这个项目中我们就跟着小A一起进行商品销售情况分析，研究哪些商品比较畅销，哪些地域和时段商品销售情况比较好，哪些促销手段比较受欢迎，哪些问题导致了退货和退款。通过数据分析为店铺营销、铺货、物流走向、仓储布局等提供量化依据。学习目标学会对商品情况进行分析学会对销售情况进行分析学会对促销活动进行分析学会对退换货情况进行分析任务1商品情况分析项目四商品销售情况分析项目背景商品数据是商务数据分析的基础，包括商品的品牌、规格、单价、客户订单支付情况、收货地址、购买时间、促销方式等重要信息。商品数据的内容越全面，在进行数据分析时越能从各个角度获取有效信息，帮助企业判定不同品牌、不同地域、不同营销手段下客户对商品的关注度。通过前期的客户情况分析，小A对数据分析有了一定的认识，信心满满的进入商品情况分析阶段，通过分析提取爆款商品、热销商品、转化率高的商品、复购率高的商品和滞销商品，将分析结果提供给销售和仓储管理人员，为更好地促进商品销售尽一份力。冻结标题行和首列1、冻结标题行和首列2、潜在爆款商品选择3、热销商品筛选4、滞销商品筛选商品情况分析冻结标题行和首列操作：单击“视图”选项卡“窗口”组中的“冻结窗格”按钮，选择“冻结首行”命令。注意：如需要恢复到正常状态，则单击“视图”选项卡“窗口”组中的“冻结窗格”按钮，选择“取消冻结窗格”命令。冻结标题行和首列1、冻结标题行和首列2、潜在爆款商品选择3、热销商品筛选4、滞销商品筛选商品情况分析潜在爆款商品选择爆款是指在商品销售中，供不应求，销售量很高的商品，通俗点说就是流量大、销量高、人气高、排名高，最重要是转化率高。一般情况下看一个月的销售数据，销量最好、复购率高的商品就是潜在的爆款商品，可以作为店铺主推商品。1、计算商品销量2、计算商品复购率计算商品销量操作：单击“数据”选项卡“排序和筛选”组中的“排序”按钮，以商品编号为主要关键字，客户网名为次要关键字，按降序进行排序两重排序设置示意图排序结果示意图1、将数据排序计算商品销量操作：单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，在“分类字段”框中选择“商品编码”，在“汇总方式”框中选择“求和”，在“选定汇总项”框中勾选“订单数”，单击“确定”按钮。2、将数据分类汇总单击左上角的“2”，得到商品销量计算结果可见商品编号为GLT125YP的商品销量最好，具有打造爆款的潜质！计算商品复购率商品复购率=复购客户数÷总购买客户数例如：有10个客户购买了某种商品，6个客户产生了重复购买行为，则复购率为60%。1、使用COUNTIF函数计算购买次数2、删除无复购的数据3、计算复购率使用COUNTIF函数计算购买次数编号为GLT50Y2的商品客户购买次数计算参数设置编号为GLT125YP的客户购买次数计算参数设置删除无复购的数据操作：1、单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，单击“购买次数”右下角的下拉箭头，筛选无复购记录的客户。2、单击“开始”选项卡“编辑”组中的“查找和选择”按钮，单击“定位条件…”命令，在弹出的“定位条件”对话框中点击“可见单元格”单选按钮，则将无复购记录的客户全部选中。3、在筛选出的数据区域中单击鼠标右键，选择“删除行”命令，则将所有的无复购记录客户数据删除。计算复购客户数使用“删除重复项”功能删除重复客户记录操作：1、单击“数据”选项卡“数据工具”组中的“删除重复项”按钮，弹出“删除重复项”对话框，单击“取消全选”按钮，勾选“客户网名”列，将重复的客户删除。2、按照“商品编号”升序进行排序，之后进行分类汇总，通过分类汇总计算每种商品的复购客户数。得到复购客户数计算商品复购率因为一个商品对应一个购买客户，所以商品所占行数=总客户数计算操作;光标放置在复购率下面第一个单元格，输入公式“=复购客户数所在单元格/总客户数所在单元格”，将单元格格式设置为“百分比”，则计算出GLT125YP商品的月复购率为15.52%。使用图表展现操作：选中“商品编号”“商品销量”“复购率”三列，单击“插入”选项卡“图表”组中的“插入柱形图或条形图”按钮，选择推荐的组合图，单击“确定”按钮。美化图表：在图表区域单击鼠标右键，选择“设置图表区格式…”，设置图表区边框为实线、蓝色（个性色1）、1.5磅、圆角，所示图表。商品编号为GLT125YP的商品销量和客户复购率都大幅度领先于其他商品，可以作为店铺爆款商品来打造。冻结标题行和首列1、冻结标题行和首列2、潜在爆款商品选择3、热销商品筛选4、滞销商品筛选商品情况分析热销商品筛选一般情况下，人们把适合市场需求的、销量较大的商品称为热销商品。一般对于单价较低的商品按销量排名来筛选，对于单价较高的商品按照销售额占比来筛选。热销商品筛选操作：1、在“销量计算”表中按住“Ctrl”键选择“商品编号、订单数量”两列数据2、按住【CTRL+G】快捷键，系统弹出“定位”对话框，单击左下角的“定位条件…”按钮，在“定位条件”对话框中点击“可见单元格”单选按钮，则将各类商品销量汇总结果全部选中，按住【CTRL+C】组合键复制选中内容。3、光标放置在“热销商品”表A1单元格位置，按住【CTRL+V】组合键将商品销量汇总结果粘贴到新表中。4、选中“商品编号”“订单数”两列，单击“插入”选项卡“图表”组“插入柱形图或条形图”按钮，选择“三维柱形图”右边的彩色柱形图，得到所示图表。排名第2的商品可以认定为是该店铺的热销商品。商品情况分析1、冻结标题行和首列2、潜在爆款商品选择3、热销商品筛选4、滞销商品筛选商品情况分析动销率是否为店铺的滞销商品，一般用动销率来衡量，不同类别的商品、不同店铺和不同的销售渠道计算动销率的时间段是不同的，可以是周动销率、月动销率、年度动销率或者某个时间段的动销率。例如，对于奶片之类的小零食，京东一般规定45天以内，动销率小于50%可认定为是滞销商品。动销率计算公式为：商品动销率=（某时段内商品累计销售量÷某时段商品库存量×100%。商品动销率计算表商品编号8月份商品销量8月份商品库存月动销率GLT50Y2961002

GLT125YP1231423799

GLT115QP30904473

GLT115MP43494580

GLT100SP1021067

注意：“商品库存”为商品8月份平均库存，计算方法为（8月初库存+8月末库存）÷2计算商品动销率选中D10单元格（月的效率下面第一个单元格），输入公式“=B9/C9”,计算出编号为GLT50Y2的商品月动销率，在D10单元格单击鼠标右键，选择“设置单元格格式”，将数字格式设置为“百分比”组合图表展示选中“商品编号”“8月份商品销量”“月动销率”三列，单击“插入”选项卡“图表”组右下角的箭头，选择“所有图表”中的“组合”，选择“月动销率”的图表类型为“带数据标记的折线图”商品编号为GLT50Y2和GLT100SP的商品8月份月销量最低，动销率也最低，可认定为是滞销商品THANKS谢谢观看任务2销售情况分析项目四商品销售情况分析项目背景通过对商品转化率、店铺客单价以及不同时段、不同区域的销售数据进行对比分析，了解销售变化情况，预测未来的销量和客户需求。网店经营者可根据销售数据分析结果做出判断，来指导后续的销售、库存、制定营销计划等。了解到销售情况数据分析的重要性，小A在师傅的指导下开始了销售情况分析，期望能通过自己的努力为后续的经营决策提供翔实有效的数据。根据销售额=访客数╳转化率╳客单价这个公式可以知道，决定销售额的三个大的要素就是访客数、转化率和客单价，本任务就从这三大要素开始。销售情况分析1、商品转化率2、店铺客单价计算3、不同商品最佳销售时段筛选4、各类商品最佳销售规格筛选5、各类商品最佳销售区域筛选销售情况分析商品转化率商品转化率是指在某一周期内，访问店铺并购买商品的人数与所有访问店铺的人数的比率。商品转化率=（产生购买行为的客户人数÷所有到达店铺的访客人数）×100%例如进店人数为100人，有60人购买了某一种商品，则该商品的转化率为60%。计算商品转化率按照“商品编号”进行分类汇总，计算每种商品的购买人数计算商品转化率根据汇总结果，在“商品转化率”下方空白单元格中制作表格，从汇总结果处将商品编号、月购买客户数填到表中。商品编号月购买客户数月访客人数商品转化率GLT50Y29386160.02%GLT125YP59913861615.51%GLT115QP2334386166.04%GLT115MP3136386168.12%GLT100SP84386160.22%商品编号为GLT125YP的商品转化率最高，而编号为GLT50Y2的商品转化率最低，在进货和库存设置时应根据转换率情况提前做好计划销售情况分析1、商品转化率2、店铺客单价计算3、不同商品最佳销售时段筛选4、各类商品最佳销售规格筛选5、各类商品最佳销售区域筛选销售情况分析客单价客单价是指在一定时期内，每个客户平均购买商品的金额它的计算公式是：客单价=店铺销售总额÷客户数日客单价月客单价每日支付金额、不重复客户数汇总结果1、删除支付金额为0的数据2、在“客户网名”右边添加一列“不重复客户数”。在“不重复客户数”列下第一个单元格中输入嵌套函数“=IF(COUNTIF($H$1:$H2,H2)=1,1,"")”，其中H列为“客户网名”列。3、将销售时间分列4、按照“销售日期”对“支付金额”“不重复客户数”进行分类汇总得到按日期分类的“支付金额”“不重复客户数”汇总结果日客单价计算1、将汇总字段去除，整理表格2、在日客单价列（D列）下面第一个单元格输入公式“=B2/C2”，回车，将单元格格式设置为“数值，两位小数”，得到8月1日的客单价。3、选中“销售日期”“日客单价”两列，单击“插入”选项卡“图表”组“插入柱形图和条形图”右边的下拉箭头，选择“簇状条形图”。月客单价计算1、在“日客单价”右边输入“月客单价”，光标定位在“月客单价”下第一个单元格，输入公式“=B33/C33”,其中B33为8月份“支付金额”汇总结果，C33为“不重复客户数”汇总结果2、按下回车键，得到8月份的月客单价为53.82元销售情况分析1、商品转化率2、店铺客单价计算3、不同商品最佳销售时段筛选4、各类商品最佳销售规格筛选5、各类商品最佳销售区域筛选销售情况分析不同商品最佳销售时段筛选使用mid函数进行销售时段（精确到小时）提取数据透视表和数据透视图操作：按住【CTRL+A】组合键选中“销售时段”表所有数据，单击“插入”选项卡“图表”组“数据透视图”按钮下方的小三角，单击“数据透视图和数据透视表”，在弹出的“创建数据透视表”对话框中选择数据区域、放置数据透视图的区域数据透视表和数据透视图用拖拽的方式选取字段在“值”编辑框中，单击右侧下拉箭头，选择“值字段设置…”，可选择所选字段数据计算类型数据透视表和数据透视图每种商品的最佳销售时段均出现在上午10点—11点之间，上午11点—12点的销量居于第二，之后一直到第二天凌晨呈现出递减趋势。销售情况分析1、商品转化率2、店铺客单价计算3、不同商品最佳销售时段筛选4、各类商品最佳销售规格筛选5、各类商品最佳销售区域筛选销售情况分析规格码分离选中“规格码”列，单击“数据”选项卡“数据工具”组“分列”按钮，在第二步“分隔符号”中勾选“其他”，在后面的文本框中按住【CTRL+V】组合键，将刚刚复制的“：”贴到文本框中，如图4-2-27所示。单击“完成”按钮，在之后的警告框中单击“确定”按钮，完成规格码分离。创建规格码的数据透视图和数据透视表将“商品编号、规格码”字段拖曳到“轴（类别）”编辑框中，将“订单数量”字段拖曳到“值”编辑框中，以计算不同商品不同规格码的销量。设置文字竖排显示选中类别轴，单击鼠标右键，选择“设置坐标轴格式…”，在“设置坐标轴格式”对话框中单击“大小与属性”图标，在文字方向中选择“竖排”销售情况分析1、商品转化率2、店铺客单价计算3、不同商品最佳销售时段筛选4、各类商品最佳销售规格筛选5、各类商品最佳销售区域筛选销售情况分析各类商品最佳销售区域筛选通过商品最佳销售区域筛选，掌握各省市的商品销售情况，以此为依据对仓储设置、物流走向、店铺铺货情况等进行调整，最大限度地节约物流和时间成本。1、将“销售区域”列分成三列，并将三列命名为“直辖市/省/自治区”“市/地区/自治州”“区/县”。2、将“商品编号”“直辖市/省/自治区”字段拖曳到“轴（类别）”编辑框中，将“订单数量”字段拖曳到“值”编辑框中，以计算不同商品在不同区域的订单数量各类商品最佳销售区域筛选由于省市多，各种商品挤在一起几乎看不出来哪种商品在哪个省市销量好，所以下面我们就分不同商品来看各省市销量情况。THANKS谢谢观看任务3商品促销活动分析项目四商品销售情况分析项目背景商品促销是运用各种积极的促销方式，吸引既有和潜在的客户，刺激其购买需求，以提高店铺的销售额。它是提高商品销售最直接、最简单、最有效的方式之一。品促销可以提高店铺销售额、提高客流量，进而提高转化率和店铺知名度等。万变不离其宗，店铺搞商品促销就是通过让利来吸引更多的客户来消费，以获得更多的利润。通过前期的商品情况分析、销售情况分析，小A已经能够比较熟练地使用EXCEL函数、数据透视图表等进行简单的数据分析，本任务我们就跟随小A一起进行商品促销活动的分析吧。商品促销活动分析1、店铺每日销量和销售额2、店铺促销活动效果分析商品促销活动分析店铺每日销量情况将“销售日期”字段拖曳到“轴（类别）”编辑框中，将“订单数”字段拖曳到“值”编辑框中，计算不同日期的销量店铺每日销量额情况在“数据透视图字段”“值”编辑框中，单击“求和项：订单数”，选择“删除字段”将“订单数”字段删除。将“支付金额”字段拖曳到“值”编辑框中，计算不同日期的销售金额满赠满赠是指购买商品满一定金额会赠送一些小包装或其他口味的商品，是扩大客户体验，提高客户黏度的一种促销手段。在透视图“促销方式”中选择“满赠”，将图表类型改为“带数据标记点的折线图”，将图表标题改为“满赠销售额统计图”，将四个高点转折处的数据标记点大小改为“9”，填充颜色改为红色，得到促销方式为“满赠”的商品销售额统计图。满减满减是指购买商品满一定金额会在付款时再减去部分金额，电商的满减活动可以分为满减优惠券和系统自动满减。是吸引新客户，增加老客户购买力的一种促销手段。在透视图“促销方式”中选择“满减”，将图表标题改为“满减销售额统计图”，得到促销方式为“满减”的商品销售额统计图。满折满折是指购买商品满一定金额会在原价基础上给予一定的折扣，比如满50打9.5折，满80打9折，满100打8折等，跟满减一样，是吸引新客户，增加老客户购买力的一种促销手段。在透视图“促销方式”中选择“满折”，将图表标题改为“满折销售额统计图”，得到促销方式为“满折”的商品销售额统计图。THANKS谢谢观看任务4退换货分析项目四商品销售情况分析项目背景大的电商平台，如京东、天猫、苏宁易购、1号店等都支持7天无理由退货，这是平台为保护消费者利益做出的承诺。这样的做法使得这些平台赢得了客户的信任，也使得各平台入驻商家更加重视自家店铺商品的质量。因此，通过对客户退货、退款情况进行分析，找到客户退货或退款的原因，从自身着手解决问题，为客户提供良好的消费体验，从而赢得客户认可和良好口碑，促进店铺运营水平的提升。本任务小A根据原始销售数据的退换货情况，对客户退货、退款情况进行分析，找出客户退换货的原因，为后续改善商品销售、提高服务质量提供方向和依据。退换货分析1、退换货原因分析2、退换货方式分析退换货分析退换货原因分析当客户申请退货或退款时，作为店铺首先应该与客户沟通，查明客户退货、退款的原因，做出合理的判断和处理，以免产生误会，对店铺造成不良影响。如果符合退货、退款条件，应向客户提供完整的退货地址，快速及时的处理退换货问题。处理退换货列数据利用“分列”功能将退换货列分为退换货原因和退换货方式两列。操作：1、选中“退换货”列，单击“数据”选项卡“数据工具”组的“分列”按钮，以“空格”为分隔符将该列分成两列。2、将“退换货”改为为“退换货原因”，其右边列的列标题命名为“退换货方式”。计算不同退换货原因的销售额单击“数据”选项卡“分级显示”组“分类汇总”按钮，在弹出的对话框中选择分类字段为“退换货原因”，汇总方式为“求和”，选定汇总项为“支付金额”，单击“确定”按钮，单击左上角的“2”，可得到不同退换货原因的销售额。计算不同退换货原因的订单笔数1、筛选可见单元格数据2、复制数据到新表“退换货原因分析”。3、将“退换货原因”列以空格为分隔符分成两列，将右边内容为“汇总”的列删除，将“支付金额”列移到“退换货原因”列左边，将列标题改为“退换货金额”。4、单击“数据”选项卡“分级显示”组“分类汇总”按钮，在弹出的对话框中选择分类字段为“退换货原因”，汇总方式为“计数”，选定汇总项为“订单数”。处理退换货列数据1、删除分类汇总数据。2、选中“退换货原因分析”“退换货金额”两列数据，在“插入”选项卡“图表”组选择“三维簇状条形图”，对表格区域和边框进行美化，退换货原因分析因“包装破损”造成的退换货1“临近保质期”造成的退换货，2“赠品临近保质期”造成的退换货3“不想要了”“与商品描述不符”4做好检视工作，及时发现包装破损产品，做好商品的防破损包装，如加上气泡袋、保护膜等。要把好商品出库关，不要把临近保质期的商品发出去。如果想把临近保质期的商品作为赠品搞活动，就一定要在活动说明里明确提出，让客户自愿选择，以免造成不必要的误会和不良后果。通过与客户的充分沟通，了解清楚客户的诉求，为我们更好地做好店铺运营指明方向。退换货方式分析本任务退换货方式有换货、退款两种，通过对退换货方式进行分析，了解客户在什么情况下会退款，什么情况下会换货，为更好地做好客户服务提供量化依据。退换货总金额图表将“退换货方式