第9章 网店商品销售数据分析

第9章网店商品销售数据分析9.1商品整体销售数据分析9.2不同商品销售数据分析9.3同类商品销售数据分析9.4商品退货与退款数据统计与分析19.1商品整体销售数据分析9.1.1制作销售报表9.1.2畅销与滞销商品分析9.1.3商品销量排名分析29.1.1制作销售报表（1）打开“销售报表.xlsx”文件，在“月销售报表”工作表中选择A1:H1单元格区域，在“视图”选项卡“窗口”组中单击“冻结窗格”→“冻结首行”命令，如下图所示。39.1.1制作销售报表（2）选择D2:E66单元格区域，在“开始”选项卡“数字”组中的“数字格式”下拉列表中选择“数字”选项，默认保留两位小数，如下图所示。49.1.1制作销售报表（3）选择F2单元格，在编辑栏中输入公式“=(D2−E2)/D2”，按Ctrl+Enter组合键确认。在“开始”选项卡“数字”组中的“数字格式”下拉列表中选择“百分比”选项，单击“减少小数位数”按钮，设置一位小数。59.1.1制作销售报表（4）将光标移至F2单元格右下角，当光标变为十字形时双击，将F2单元格中的公式快速填充到F3:F66单元格区域中，如下图所示。69.1.1制作销售报表（5）选择H2单元格，在编辑栏中输入公式“=E2*G2”，按Ctrl+Enter组合键确认。在“开始”选项卡“数字”组中的“数字格式”下拉列表中选择“数字”选项。（6）将光标移至H2单元格右下角，当光标变为十字形时双击，将H2单元格中的公式快速填充到H3:H66单元格区域中，如下图所示。此时，用户即可对销售报表进行查看和分析。79.1.2畅销与滞销商品分析（1）打开“销售报表分析（1）.xlsx”文件，将“Sheet2”工作表重命名为“滞销与畅销商品分析”。在A1:E1单元格区域中输入标题文本“商品编码”“销售总数”“总销售额”“畅滞销比率”“销售状态”。切换至“月销售报表”工作表，选择B2:B66单元格区域，按Ctrl+C组合键进行复制。再切换至“滞销与畅销商品分析”工作表，选择A2单元格，在“开始”选项卡“剪贴板”组中单击“粘贴”下拉按钮，在打开的下拉列表中选择“值”选项，如下图所示。89.1.2畅销与滞销商品分析（2）选择A2:A66单元格区域，在“数据”选项卡“数据工具”组中单击“删除重复值”按钮，弹出“删除重复项警告”提示对话框，保持默认设置，单击“删除重复项”按钮，如下图所示。99.1.2畅销与滞销商品分析（3）弹出“删除重复值”对话框，保持默认设置，单击“确定”按钮，如下图所示。109.1.2畅销与滞销商品分析重复值删除完成后会弹出提示对话框，单击“确定”按钮，如下图所示。119.1.2畅销与滞销商品分析（4）选择A2:A9单元格区域，在“数据”选项卡“排序和筛选”组中单击“排序”按钮，弹出“排序提醒”对话框，选中“以当前选定区域排序”单选按钮，单击“排序”按钮，如下图所示。129.1.2畅销与滞销商品分析（5）弹出“排序”对话框，保持默认设置，单击“确定”按钮，如下图所示。139.1.2畅销与滞销商品分析（6）选择B2单元格，在编辑栏中输入公式“=SUMIF(月销售报表!$B$2:$B$66,$A2,月销售报表!$G$2:$G$66)”，按Ctrl+Enter组合键确认，计算相应商品的“销售总数”，如下图所示。149.1.2畅销与滞销商品分析（7）选择C2单元格，在编辑栏中输入公式“=SUMIF(月销售报表!$B$2:$B$66,$A2,月销售报表!$H$2:$H$66)”，按Ctrl+Enter组合键确认，计算相应商品的“总销售额”，如下图所示。159.1.2畅销与滞销商品分析（8）选择B2:C2单元格区域，将光标移至C2单元格右下角，当光标变为十字形时按住鼠标左键拖动填充柄至C9单元格，填充其他商品的“销售总数”和“总销售额”，如下图所示。169.1.2畅销与滞销商品分析（9）在A10单元格中输入文本“总计”，选择B10:C10单元格区域，在“开始”选项卡“编辑”组中单击“自动求和”按钮，计算“总计”值，如下图所示。179.1.2畅销与滞销商品分析（10）选择C2:C10单元格区域，在“开始”选项卡“单元格”组中单击“格式”→“设置单元格格式”命令，弹出“设置单元格格式”对话框，在“分类”列表框中选择“数值”选项，在“小数位数”数值框中输入“2”，勾选“使用千位分隔符”复选框，如下图所示。189.1.2畅销与滞销商品分析单击“确定”按钮，在工作表中可以看到“总销售额”数据显示效果，如下图所示。199.1.2畅销与滞销商品分析(11)选择D2单元格，在编辑栏中输入公式“=B2/$B$10*0.8+C2/$C$10*0.2”，按Ctrl+Enter组合键确认，计算“畅滞销比率”。设置D2单元格的“数字格式”为“百分比”，保留两位小数，利用填充柄填充公式，如下图所示。209.1.2畅销与滞销商品分析（12）选择E2单元格，在编辑栏中输入公式“=IF(D2>18%,”畅销“,IF(D2>10%,”一般“,”滞销“))”，按Ctrl+Enter组合键确认，计算“销售状态”，利用填充柄填充公式，如下图所示。219.1.2畅销与滞销商品分析（13）设置字体格式、标题填充色、对齐方式、边框和表格样式等美化表格，畅销与滞销商品分析最终效果如下图所示。229.1.3商品销量排名分析（1）打开“销售报表分析（2）.xlsx”文件，在“滞销与畅销商品分析”工作表中的G1:I1单元格区域中输入标题文本“排名”“商品编码”“销售额”。选择G2单元格，在编辑栏中输入公式“{=SMALL(RANK($C$2:$C$9,$C$2:$C$9),ROW()−1)}”，按Shift+Ctrl+Enter组合键确认，生成数组公式，得到排名序号，如下图所示。239.1.3商品销量排名分析(2)选择I2单元格，在编辑栏中输入公式“=LARGE($C$2:$C$9,ROW()−1)”，按Ctrl+Enter组合键确认，得到排名第一的“销售额”，设置其小数位数为2，如下图所示。249.1.3商品销量排名分析（3）选择H2单元格，在编辑栏中输入公“{=INDEX($A:$A,SMALL(IF($C$2:$C$9=$I2,ROW($C$2:$C$9)),COUNTIF($G$2:$G2,G2)))}”，按Shift+Ctrl+Enter组合键确认，得到排名第一的“商品编码”，如下图所示。259.1.3商品销量排名分析（4）选择G2:I2单元格区域，利用填充柄填充其他商品的“排名”、“商品编码”和“销售额”，如下图所示。269.1.3商品销量排名分析（5）设置字体颜色、标题填充色、对齐方式、边框和表格样式等美化表格，商品销量排名分析最终效果如下图所示。279.2不同商品销售数据分析9.2.1按销量进行数据分析9.2.2按销售额进行数据分析9.2.3按销售额比重进行数据分析9.2.4不同商品分配方案分析289.2.1按销量进行数据分析（1）打开“近期宝贝销售记录.xlsx”文件，在“Sheet1”工作表中选择E2单元格，在“数据”选项卡“排序和筛选”组中单击“升序”按钮，如下图所示。299.2.1按销量进行数据分析（2）在“数据”选项卡“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“宝贝标题”选项，在“汇总方式”下拉列表中选择“计数”选项，在“选定汇总项”列表框中勾选“宝贝标题”复选框，如下图所示。309.2.1按销量进行数据分析（3）单击“确定”按钮，按照同类商品进行计数汇总，如下图所示。319.2.1按销量进行数据分析（4）单击工作表左上角的分级显示按钮，显示2级分类数据，可以查看不同商品的销量统计结果，如下图所示。将工作簿另存为“近期宝贝销售记录（销量分类统计）.xlsx”文件。329.2.2按销售额进行数据分析（1）打开“近期宝贝销售记录.xlsx”文件，在“Sheet1”工作表中对“宝贝标题”列进行升序排序。在“数据”选项卡“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“宝贝标题”选项，在“汇总方式”下拉列表中选择“求和”选项，在“选定汇总项”列表框中勾选“销售总金额”复选框，如下图所示。339.2.2按销售额进行数据分析（2）单击“确定”按钮，对不同商品的销售总金额进行求和汇总，如下图所示。349.2.2按销售额进行数据分析（3）单击工作表左上角的分级显示按钮，显示2级分类数据，可以查看不同商品的销售总金额统计结果，如下图所示。将工作簿另存为“近期宝贝销售记录（销售额分类统计）.xlsx”文件。359.2.3按销售额比重进行数据分析（1）打开“近期宝贝销售记录.xlsx”文件，在“Sheet1”工作表中选择E2:E16单元格区域，按Ctrl+C组合键复制数据。选择A22单元格，在“开始”选项卡“剪贴板”组中单击“粘贴”下拉按钮，在打开的下拉列表中选择“值”选项，粘贴复制的数据，如下图所示。369.2.3按销售额比重进行数据分析（2）在“数据”选项卡“数据工具”组中单击“删除重复值”按钮，弹出“删除重复值”对话框，保持默认设置，单击“确定”按钮，如下图所示。379.2.3按销售额比重进行数据分析（3）重复值删除完成后会弹出提示对话框，单击“确定”按钮，如下图所示。389.2.3按销售额比重进行数据分析（4）选择B22单元格，在“公式”选项卡“函数库”组中单击“数学和三角函数”下拉按钮，在打开的下拉列表中选择“SUMIF”函数，弹出“函数参数”对话框，设置各项函数参数，单击“确定”按钮，如下图所示。399.2.3按销售额比重进行数据分析（5）将光标移至B22单元格右下角，当光标变为十字形时按住鼠标左键拖动填充柄至B24单元格，填充其他商品的销售额数据。选择A22:B24单元格区域，在“插入”选项卡“图表”组中单击“插入饼图或圆环图”下拉按钮，在打开的下拉列表中选择“饼图”选项，插入饼图，如下图所示。409.2.3按销售额比重进行数据分析（6）将图表移动到合适的位置，将图表标题修改为“不同商品销售额比重分析”，为图表应用“样式3”图表样式，不同商品销售额比重分析最终效果如下图所示。将工作簿另存为“近期宝贝销售记录（销售额比重分析）.xlsx”文件。419.2.4不同商品分配方案分析（1）打开“商品分配方案分析.xlsx”文件，在“Sheet1”工作表中选择F3单元格，在编辑栏中输入公式“=D3*E3”，按Ctrl+Enter组合键确认，计算“毛利合计”，利用填充柄填充公式到F4单元格，如下图所示。429.2.4不同商品分配方案分析（2）选择D6单元格，在编辑栏中输入公式“=B3*E3+B4*E4”，按Ctrl+Enter组合键确认，计算“实际投入成本”。选择D7单元格，在编辑栏中输入公式“=C3*E3+C4*E4”，按Ctrl+Enter组合键确认，计算“实际销售时间”，如下图所示。439.2.4不同商品分配方案分析（3）选择B8～D8合并后的单元格，在编辑栏中输入公式“=F3+F4”，按Ctrl+Enter组合键确认，计算“总收益”，如下图所示。449.2.4不同商品分配方案分析（4）单击“文件”→“选项”命令，弹出“Excel选项”对话框，在左侧列表框中选择“加载项”选项，如下图所示。459.2.4不同商品分配方案分析（5）单击“转到”按钮，弹出“加载项”对话框，勾选“规划求解加载项”复选框，如下图所示。469.2.4不同商品分配方案分析（6）单击“确定”按钮，返回工作表。在“数据”选项卡“分析”组中单击“规划求解”按钮，弹出“规划求解参数”对话框，设置“设置目标”为B8单元格，选中“最大值”单选按钮，设置“通过更改可变单元格”为E3:E4单元格区域，如下图所示。479.2.4不同商品分配方案分析（7）单击“添加”按钮，弹出“添加约束”对话框，设置“单元格引用”为E3单元格，设置运算符号为“>=”，设置“约束”为“0”，单击“添加”按钮。使用相同的方法，添加其他约束条件，如下图所示。489.2.4不同商品分配方案分析（8）单击“确定”按钮，返回“规划求解参数”对话框，添加的约束条件显示在“遵守约束”列表框中，如下图所示。499.2.4不同商品分配方案分析（9）单击“求解”按钮，弹出“规划求解结果”对话框，保持默认设置，如下图所示。509.2.4不同商品分配方案分析（10）单击“确定”按钮，返回工作表，不同商品分配方案分析最终效果如下图所示。519.3同类商品销售数据分析9.3.1按颜色进行数据分析9.3.2按尺寸进行数据分析529.3.1按颜色进行数据分析（1）打开“不同颜色的同类商品销售统计.xlsx”文件，在“Sheet1”工作表中选择B2单元格，在“数据”选项卡“排序和筛选”组中单击“升序”按钮，对“颜色”列数据进行排序，如下图所示。539.3.1按颜色进行数据分析（2）在“数据”选项卡“分级显示”组中单击“分类汇总”按钮，弹出“分类汇总”对话框，在“分类字段”下拉列表中选择“颜色”选项，在“选定汇总项”列表框中勾选“成交数量”复选框，如下图所示。549.3.1按颜色进行数据分析（3）单击“确定”按钮，按照不同颜色对商品成交数量进行求和汇总。单击工作表左上角的分级显示按钮，显示2级分级数据，对商品成交数量进行升序排序，按颜色进行数据分析的最终效果如下图所示。559.3.2按尺寸进行数据分析（1）打开“不同尺寸的同类商品销售统计.xlsx”文件，在“Sheet1”工作表中的数据区域中选择任意非空单元格，在“插入”选项卡“表格”组中单击“数据透视表”按钮，如下图所示。569.3.2按尺寸进行数据分析（2）弹出“创建数据透视表”对话框，在“表/区域”文本框中选择工作表中的整个数据区域（即“Sheet1!$A$1:$C$29”），选中“现有工作表”单选按钮，设置“位置”为E2单元格，如下图所示。579.3.2按尺寸进行数据分析（3）单击“确定”按钮，系统会创建一个空白数据透视表，并打开“数据透视表字段”面板，将“尺寸”字段拖动至“行”列表框，将“成交数量”字段拖动至“值”列表框，如下图所示。单击“关闭”按钮，关闭“数据透视表字段”面板。589.3.2按尺寸进行数据分析（4）选择F3单元格，在“数据”选项卡“排序和筛选”组中单击“降序”按钮，对商品成交数量进行降序排序，按尺寸进行数据分析的最终效果如下图所示。599.4商品退货与退款数据统计与分析9.4.1商品退货与退款数据统计9.4.2商品退货与退款数据分析609.4.1商品退货与退款数据统计（1）打开“退货、退款原因统计.xlsx”文件，在“Sheet1”工作表中复制E2:E14单元格区域中的数据，将其粘贴到I2:I14单元格区域。在“数据”选项卡“数据工具”组中单击“删除重复值”按钮，弹出“删除重复值”对话框，保持默认设置，单击“确定”按钮，如下图所示。619.4.1商品退货与退款数据统计（2）重复值删除完成后会弹出提示对话框，单击“确定”按钮，如下图所示。629.4.1商品退货与退款数据统计（3）选择I2:I6单元格区域并复制数据，选择J2单元格，在“开始”选项卡“剪贴板”组中单击“粘贴”下拉按钮，在打开的下拉列表中选择“转置”选项，如下图所示。639.4.1商品退货与退款数据统计（4）选择I2:I6单元格区域，在“开始”选项卡“编辑”组中单击“清除”→“全部清除”命令，如下图所示。649.4.1商品退货与退款数据统计(5)选择J3单元格，在编辑栏中输入公式“=COUNTIF($E$2:$E$14,J2)”，按Ctrl+Enter组合键确认，对因“质量问题”而退货与退款数据进行统计，利用填充柄填充其他退货与退款原因数据，如下图所示。659.4.1商品退货与退款数据统计（6）选择J2:N3单元格区域，在“插入”选项卡“图表”组中单击“插入饼图或圆环图”下拉按钮，在打开的下拉列表中选择“饼图”选项，如下图所示。669.4.1商品退货与退款数据统计（7）调整图表的位置和大小，并对图表进行美化，商品退货与退款数据统计最终效果如下图所示。此时，卖家即可清晰地查看各种退货与退款原因的占比情况。679.4.2商品退货与退款数据分析（1）打开“退货、退款原因分析.xl