商务数据的分析与应用模块7商品销售情况统计与分析

项目七实践篇商务数据的分析与应用商品销售情况统计与分析目录商品销售数据统计与分析不同商品销售情况统计与分析任务一任务二同类商品销售情况统计与分析商品退货、退款情况统计与分析任务三任务四商品销售数据统计与分析任务一132制作销售报表畅销与滞销商品分析商品销量排名分析通过对商品销售数据进行统计与分析，有助于卖家发现店铺销售中存在的问题，并能找到新的销售增长点，在不增加成本的前提下提高店铺的商品销量。本任务将介绍如何通过Excel制作销售报表、对畅销与滞销商品进行分析，并对商品销量进行排名。制作销售报表任务概述步骤01打开“素材与实例\项目七\销售报表.xlsx”，在“月销售报表”工作表中选择A1:H1单元格区域，然后单击“视图”选项卡“窗口”组中的“冻结窗格”按钮，在展开的下拉列表中选择“冻结首行”选项。制作销售报表任务重点与实施一、制作销售报表步骤02选择D2:E66单元格区域，在“开始”选项卡“数字”组中的“数字格式”下拉列表中选择“数字”选项，默认保留两位小数。步骤03选择F2单元格，在编辑栏中输入公式“=(D2−E2)/D2”，并按“Ctrl+Enter”组合键确认。然后在“开始”选项卡“数字”组中的“数字格式”下拉列表中选择“百分比”选项，并单击“减少小数位数”按钮，设置其小数位数为1。步骤04将鼠标指针置于F2单元格右下角，当鼠标指针变为十字形状时双击鼠标左键，将F2单元格中的公式快速填充到F3:F66单元格区域中。制作销售报表任务重点与实施一、制作销售报表步骤05选择H2单元格，在编辑栏中输入公式“=E2*G2”，并按“Ctrl+Enter”组合键确认。然后利用“开始”选项卡中的“数字”组设置H2单元格的数字格式为“数值”。步骤06将鼠标指针置于H2单元格右下角，当鼠标指针变为十字形状时双击鼠标左键，将H2单元格中的公式快速填充到H3:H66单元格区域中。此时，用户即可对销售报表进行查看和分析。制作销售报表任务重点与实施一、制作销售报表卖家通过对商品销售数据进行分析，可以直观地判定哪些商品畅销，哪些商品滞销，然后针对不同销售状态的商品，制定不同的采购计划和销售策略。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤02在A1:E1单元格区域中输入标题文本“商品编码”“销售总数”“总销售额”“畅滞销比率”“销售状态”。步骤01打开“素材与实例\项目七\销售报表分析（1）.xlsx”，将“Sheet2”工作表重命名为“滞销与畅销商品分析”。步骤03切换到“月销售报表”工作表，选择B2:B66单元格区域，然后按“Ctrl+C”组合键进行复制。再切换到“滞销与畅销商品分析”工作表，选择A2单元格，单击“剪贴板”组中“粘贴”按钮下方的三角按钮，在展开的下拉列表中选择“值”选项。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤04选择A2:A66单元格区域，单击“数据”选项卡“数据工具”组中的“删除重复值”按钮，弹出“删除重复项警告”对话框，保持默认设置，单击“删除重复项”按钮。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤05弹出“删除重复值”对话框，保持默认设置，单击“确定”按钮，如图7-7所示。重复值删除完成后会弹出提示对话框，单击“确定”按钮。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤06选择A2:A9单元格区域，单击“数据”选项卡“排序和筛选”组中的“排序”按钮，弹出“排序提醒”对话框，选中“以当前选定区域排序”单选钮，然后单击“排序”按钮。步骤07弹出“排序”对话框，保持默认设置，单击“确定”按钮。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤08选择B2单元格，在编辑栏中输入公式“=SUMIF(月销售报表!$B$2:$B$66,$A2,月销售报表!$G$2:$G$66)”，并按“Ctrl+Enter”组合键确认，计算相应商品的销售总数。制作销售报表任务重点与实施二、畅销与滞销商品分析SUMIF函数作用：对满足条件的单元格求和。语法：SUMIF(range,criteria,[sum_range])（1）range：条件区域，用于条件判断的单元格区域。（2）criteria：求和条件，由数字、逻辑表达式等组成的判定条件。criteria参数中可以使用通配符“？”和“*”。其中，“？”匹配任意单个字符；“*”匹配任意一串字符。如果要查找实际的问号或星号，应在该字符前输入波形符（~）。（3）sum_range：实际求和区域，是需要求和的单元格、单元格区域或引用。当省略该参数时，则条件区域即为实际求和区域。制作销售报表任务重点与实施二、畅销与滞销商品分析课堂解疑步骤09选择C2单元格，在编辑栏中输入公式“=SUMIF(月销售报表!$B$2:$B$66,$A2,月销售报表!$H$2:$H$66)”，并按“Ctrl+Enter”组合键确认，计算相应商品的总销售额。步骤10选择B2:C2单元格区域，向下拖动单元格区域右下角的填充柄至C9单元格，填充其他销售总数和总销售额数据。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤11在A10单元格中输入文本“总计”，选择B10:C10单元格区域，在“开始”选项卡“编辑”组中单击“自动求和”按钮，计算总计值。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤12选择C2:C10单元格区域，在“开始”选项卡“单元格”组中单击“格式”按钮，在展开的下拉列表中选择“设置单元格格式”选项，弹出“设置单元格格式”对话框。在“分类”列表框中选择“数值”选项，在“小数位数”编辑框中输入2，选中“使用千位分隔符”复选框，然后单击“确定”按钮。此时，总销售额数据显示效果。制作销售报表任务重点与实施二、畅销与滞销商品分析步骤13选择D2单元格，在编辑栏中输入公式“=B2/$B$10*0.8+C2/$C$10*0.2”，并按“Ctrl+Enter”组合键确认，计算畅滞销比率。然后设置D2单元格的数字格式为“百分比”格式，保留两位小数，再利用填充柄填充其他畅滞销比率数据。步骤14选择E2单元格，在编辑栏中输入公式“=IF(D2>18%,"畅销",IF(D2>10%,"一般","滞销"))”，并按“Ctrl+Enter”组合键确认，计算销售状态。然后利用填充柄填充其他销售状态数据。制作销售报表任务重点与实施二、畅销与滞销商品分析IF函数作用：判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。语法：IF(logical_test,value_if_true,value_if_false)（1）logical_test：要测试的条件，可以是任意值或表达式，测试结果为TRUE或FALSE。（2）value_if_true：结果为TRUE时返回的值。（3）value_if_false：结果为FALSE时返回的值。制作销售报表任务重点与实施二、畅销与滞销商品分析课堂解疑步骤15根据需要为表格设置字体格式、标题填充色、对齐方式、边框、表格样式等，以美化表格。制作销售报表任务重点与实施二、畅销与滞销商品分析表样式：天蓝，表样式浅色20对齐方式：“商品编码”和“销售状态”列居中对齐标题字体格式和填充：微软雅黑，白色，16磅，加粗，水绿色填充表格边框：外边框为1磅黑色实线，内边框为0.5磅黑色实线步骤01打开“素材与实例\项目七\销售报表分析（2）.xlsx”，在“滞销与畅销商品分析”工作表的G1:I1单元格区域中输入标题文本“排名”“商品编码”“销售额”。步骤02选择G2单元格，在编辑栏中输入公式“=SMALL(RANK($C$2:$C$9,$C$2:$C$9),ROW()−1)”，并按“Ctrl+Shift+Enter”组合键确认，生成数组公式，得出排名序号。制作销售报表任务重点与实施三、商品销量排名分析步骤03选择I2单元格，在编辑栏中输入公式“=LARGE($C$2:$C$9,ROW()−1)”，并按“Ctrl+Enter”组合键确认，得出排名第一的销售额，然后设置其小数位数为2。制作销售报表任务重点与实施三、商品销量排名分析课堂解疑内容详见课本P123-124LARGE函数作用：返回数组或数字型数据区域中的第k个最大值。语法：LARGE(array,k)（1）array：需要找到第k个最大值的数组或数字型数据区域。（2）k：返回的数据在数组或数字型数据区域中的位置（按从大到小排序）。制作销售报表任务重点与实施三、商品销量排名分析课堂解疑步骤04选择H2单元格，在编辑栏中输入公式“=INDEX($A:$A,SMALL(IF($C$2:$C$9=$I2,ROW($C$2:$C$9)),COUNTIF($G$2:$G2,G2)))”，并按“Ctrl+Shift+Enter”组合键确认，得出相应的商品编码。制作销售报表任务重点与实施三、商品销量排名分析步骤05选择G2:I2单元格区域，利用填充柄填充其他排名、商品编码和销售额数据。制作销售报表任务重点与实施三、商品销量排名分析步骤06美化表格，最终效果如图所示。课堂解疑内容详见课本P1251234不同商品销量分类统计不同商品销售额分类统计不同商品销售额比重统计与分析不同商品分配方案分析任务二不同商品销售情况统计与分析卖家通过对不同商品的销售情况进行统计与分析，可以直观地判定哪些商品卖得好，哪些商品的销量不容乐观，从而相应地调整采购计划、经营策略和促销方式等，以提高店铺的销量。不同商品销售情况统计与分析任务概述不同商品销售情况统计与分析任务重点与实施一、不同商品销量分类统计步骤01打开“素材与实例\项目七\近期宝贝销售记录.xlsx”，在“Sheet1”工作表中选择E2单元格，然后单击“数据”选项卡“排序和筛选”组中的“升序”按钮。不同商品销售情况统计与分析任务重点与实施一、不同商品销量分类统计步骤02在“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“宝贝标题”选项，在“汇总方式”下拉列表中选择“计数”选项，在“选定汇总项”列表框中选中“宝贝标题”复选框。步骤03单击“确定”按钮，Excel按照同类商品进行计数汇总。不同商品销售情况统计与分析任务重点与实施一、不同商品销量分类统计步骤04单击左上方的分级显示按钮，显示2级分类数据，可查看不同商品的销量统计结果。不同商品销售情况统计与分析任务重点与实施一、不同商品销量分类统计步骤05将工作簿以“近期宝贝销售记录（销量分类统计）”为名另存。步骤01打开“素材与实例\项目七\近期宝贝销售记录.xlsx”，在“Sheet1”工作表中对“宝贝标题”列进行升序排序。不同商品销售情况统计与分析任务重点与实施二、不同商品销售额分类统计

步骤02在“数据”选项卡“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“宝贝标题”选项，在“汇总方式”下拉列表中选择“求和”选项，在“选定汇总项”列表框中选中“销售总金额”复选框。步骤03单击“确定”按钮，Excel对不同商品的销售总金额进行求和汇总。不同商品销售情况统计与分析任务重点与实施二、不同商品销售额分类统计步骤04单击左上方的分级显示按钮，显示2级分类数据，可查看不同商品的销售总金额统计结果。不同商品销售情况统计与分析任务重点与实施二、不同商品销售额分类统计步骤05将工作簿以“近期宝贝销售记录（销售额分类统计）”为名另存。步骤01打开“素材与实例\项目七\近期宝贝销售记录.xlsx”，在“Sheet1”工作表中选择E2:E16单元格区域，按“Ctrl+C”组合键复制数据。步骤02选择A22单元格，单击“剪贴板”组中“粘贴”按钮下方的三角按钮，在展开的下拉列表中选择“值”选项，粘贴复制的数据。不同商品销售情况统计与分析任务重点与实施三、不同商品销售额比重统计与分析步骤04重复值删除完成后会弹出提示对话框，单击“确定”按钮。不同商品销售情况统计与分析任务重点与实施三、不同商品销售额比重统计与分析步骤03单击“数据”选项卡“数据工具”组中的“删除重复值”按钮，弹出“删除重复值”对话框，单击“全选”按钮，取消选中“数据包含标题”复选框，然后单击“确定”按钮。不同商品销售情况统计与分析任务重点与实施三、不同商品销售额比重统计与分析步骤05选择B22单元格，在“公式”选项卡“函数库”组中单击“数学和三角函数”按钮，在展开的下拉列表中选择“SUMIF”选项，弹出“函数参数”对话框，在其中设置各项函数参数，然后单击“确定”按钮。不同商品销售情况统计与分析任务重点与实施三、不同商品销售额比重统计与分析步骤06向下拖动B22单元格右下角的填充柄至B24单元格，即可填充其他销售额数据。选择A22:B24单元格区域，然后单击“插入”选项卡“图表”组中的“插入饼图或圆环图”按钮，在展开的下拉列表中选择“饼图”选项，插入饼图。不同商品销售情况统计与分析任务重点与实施三、不同商品销售额比重统计与分析步骤07移动图表到合适位置，修改图表标题为“不同商品销售额比重分析”，为图表应用“样式3”图表样式。此时，卖家即可对不同商品的销售额比重进行分析。步骤08将工作簿以“近期宝贝销售记录（销售额比重分析）”为名另存。对于上架的各类商品，卖家可以根据获利目标进行科学的分配，以获得期望的利润。步骤01打开“素材与实例\项目七\商品分配方案分析.xlsx”，在“Sheet1”工作表中选择F3单元格，在编辑栏中输入公式“=D3*E3”，按“Ctrl+Enter”组合键确认，计算毛利合计，并利用填充柄将公式填充到F4单元格中。不同商品销售情况统计与分析任务重点与实施四、不同商品分配方案分析步骤02选择D6单元格，在编辑栏中输入公式“=B3*E3+B4*E4”，并按“Ctrl+Enter”组合键确认，计算实际投入成本。选择D7单元格，在编辑栏中输入公式“=C3*E3+C4*E4”，并按“Ctrl+Enter”组合键确认，计算实际销售时间。步骤03选择B8至D8合并后的单元格，在编辑栏中输入公式“=F3+F4”，并按“Ctrl+Enter”组合键确认，计算总收益。不同商品销售情况统计与分析任务重点与实施四、不同商品分配方案分析步骤04选择“文件”→“选项”选项，弹出“Excel选项”对话框，在左侧列表框中选择“加载项”选项，然后单击“转到”按钮。不同商品销售情况统计与分析任务重点与实施四、不同商品分配方案分析不同商品销售情况统计与分析任务重点与实施四、不同商品分配方案分析步骤05弹出“加载项”对话框，选中“规划求解加载项”复选框，然后单击“确定”按钮。步骤06单击“数据”选项卡“分析”组中的“规划求解”按钮，弹出“规划求解参数”对话框，设置“设置目标”为B8单元格，选中“最大值”单选钮，然后设置“通过更改可变单元格”为E3:E4单元格区域，最后单击“添加”按钮。步骤07弹出“添加约束”对话框，设置“单元格引用”为E3单元格，设置“运算符号”为“>=”，设置“约束”为0，然后单击“添加”按钮。步骤08参考步骤07，采用同样的方法添加其他约束条件。添加完最后一个约束条件后在对话框中单击“确定”按钮。不同商品销售情况统计与分析任务重点与实施四、不同商品分配方案分析步骤09返回“规划求解参数”对话框，添加的约束条件显示在“遵守约束”列表框中。单击“求解”按钮。步骤10弹出“规划求解结果”对话框，保持默认设置，单击“确定”按钮。不同商品销售情况统计与分析任务重点与实施四、不同商品分配方案分析步骤11返回工作表，卖家即可查看按照设定的规划求解参数得出的“商品分配数量”“毛利合计”“实际投入成本”“实际销售时间”“总收益”。不同商品销售情况统计与分析任务重点与实施四、不同商品分配方案分析12不同颜色的同类商品销售情况统计与分析不同尺寸的同类商品销售情况统计与分析任务三同类商品销售情况统计与分析对于同类商品而言，不同颜色和尺寸的商品的销售情况会有所不同，甚至差距很大。此时，就需要卖家对不同属性的同类商品的销售情况进行统计和分析，从而做出正确的采购计划和销售策略。同类商品销售情况统计与分析任务概述步骤02在“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“颜色”选项，在“选定汇总项”列表框中选中“成交数量”复选框，然后单击“确定”按钮。同类商品销售情况统计与分析任务重点与实施一、不同颜色的同类商品销售情况统计与分析步骤01打开“素材与实例\项目七\不同颜色的同类商品销售统计.xlsx”，在“Sheet1”工作表中选择B2单元格，然后单击“数据”选项卡“排序和筛选”组中的“升序”按钮，对“颜色”列数据进行排序。同类商品销售情况统计与分析任务重点与实施一、不同颜色的同类商品销售情况统计与分析步骤03此时，系统自动按照不同的颜色对商品成交数量进行求和汇总。单击左上方的分级按钮，显示2级分级数据，然后对“成交数量”列中的数据进行升序排序。此时，卖家即可对不同颜色的同类商品的销售情况进行分析。同类商品销售情况统计与分析任务重点与实施一、不同颜色的同类商品销售情况统计与分析同类商品销售情况统计与分析任务重点与实施二、不同尺寸的同类商品销售情况统计与分析

步骤02弹出“创建数据透视表”对话框，“表/区域”编辑框中自动选择工作表的整个数据区域（即Sheet1!$A$1:$C$29），然后选中“现有工作表”单选钮，设置“位置”为E2单元格，然后单击“确定”按钮。

步骤01打开“素材与实例\项目七\不同尺寸的同类商品销售统计.xlsx”，在“Sheet1”工作表中选择数据区域的任一单元格，然后单击“插入”选项卡“表格”组中的“数据透视表”按钮。步骤03Excel自动打开“数据透视表字段”窗格，将“尺寸”字段拖至“行”区域，将“成交数量”字段拖至“值”区域。单击“关闭”按钮，关闭“数据透视表字段”窗格。同类商品销售情况统计与分析任务重点与实施二、不同尺寸的同类商品销售情况统计与分析步骤04选择F3单元格，单击“数据”选项卡“排序和筛选”组中的“降序”按钮，对商品成交数量进行排序。此时，卖家即可对不同尺寸的同类商品的销售情况进行分析。同类商品销售情况统计与分析任务重点与实施二、不同尺寸的同类商品销售情况统计与分析12任务四商品退货、退款原因统计商品退货、退款原因分析商品退货、退款情况统计与分析商品退货、退款情况统计与分析任务概述对于卖家来说，商品退货和退款是最不希望看到的情况，因为退货、退款不仅会增加时间成本，还会直接造成利益损失。卖家通过对退货、退款情况进行统计与分析，能够有效地减少退货、退款数量，提高经营水平与店铺口碑。对于卖家来说，商品退货、退款既是对消费者的郑重承诺，也是发现店铺自身问题的有效参考。步骤01打开“素材与实例\项目七\退货、退款原因统计.xlsx”，在“Sheet1”工作表中复制E2:E14单元格区域中的数据，并将其粘贴到I2:I14单元格区域中。商品退货、退款情况统计与分析任务重点与实施一、商品退货、退款原因统计步骤02单击“数据”选项卡“数据工具”组中的“删除重复值”按钮，弹出“删除重复值”对话框，保持默认设置，单击“确定”按钮。步骤03此时，系统自动删除重复值，删除完成后弹出提示对话框，单击“确定”按钮。步骤04选择I2:I6单元格区域并复制数据，然后选择J2单元格，单击“开始”选项卡“剪贴板”组中“粘贴”按钮下方的三角按钮，在展开的下拉列表中选择“转置”选项。商品退货、退款情况统计与分析任务重点与实施一、商品退货、退款原因统计步骤05选择I2:I6单元格区域，单击“编辑”组中的“清除”按钮，在展开的下拉列表中选择“全部清除”选项。步骤06选择J3单元格，在编辑栏中输入公式“=COUN