《Excel-2016数据处理与分析》教学课件10综合案例：进销存数据的处理与分析

模块10综合案例：进销存数据的处理与分析

本模块通过对进销存数据的处理与分析，在巩固前面所学知识的同时，帮助读者将Excel处理数据、计算和分析数据的操作灵活应用于实战中，提高实际应用的综合能力和思维模式，做到学以致用、学以为用。学习目标（1）IF函数。（2）ROW函数。（3）数据序列选项。（4）数字大小限定。（5）删除重复值。（6）突显大于规则。学习重点（7）数据条。（8）COUNTIF函数。（9）同比分析。（10）环比分析。（11）移动平均。（12）文本包含。10.1案例制作说明进销存又称购销链，是指企业管理过程中采购（进）—入库（存）—销售（销）的动态管理过程，能够有效地解决企业账目混乱、库存不准、信息反馈不及时等问题。本案例通过使用Excel计算、管理和分析功能，分别对进货采购数据、销售明细数据和库存积压情况进行处理与分析，帮助决策者根据时态的变化制定出精准的方针政策。10.1.1案例效果进销存数据的处理与分析效果如图10-1所示。10.1.1案例效果10.1.2制作流程在分析处理进销存数据时，由于没有数据上的相互调用，操作相对简单一些，只需在对应的表格中进行。本案例的操作重心在“采购分析表”和“销售分析表”上，库存分析只是作为补充完善。当然，读者可以将处理分析采购表和销售表的操作用于库存分析中，方法基本相同。图10-2所示为本案例的操作架构。10.1.2制作流程10.2采购数据的处理与分析进销存中“进”就是进货、采购的意思。企业通过对采购数据进行处理分析，可让采购数据更加规范，也可以分析出采购货物的基本情况，如单一货物的采购金额、频次、采购金额的环比增长或减少情况等。可以为后期的采购指标调整提供数据支撑。10.2.1

采购数据管理通常情况下，企业单次采购的货物都会有批次和金额的限定，不是随意采购的，因此，为了让采购表更加规范，我们在表格中编辑连续的采购编号、固定货物的种类，对采购金额进行限定，具体操作步骤如下。步骤1打开素材文件中的“素材文件\模块10\进销存.xlsx”文件，选中A2单元格，在编辑栏中输入“=IF（B2=""，""，"FTCGPC-0"&ROW（A2）-1）”，并向下填充，如图10-3所示。10.2.1采购数据管理10.2.1采购数据管理步骤2选择C2：C43单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框，如图10-4所示。步骤3在“允许”下拉列表框中选择“序列”选项，在“来源”文本框中输入“杯茶，卷纸，A4纸，笔记本，瓶装水，零食”，单击“确定”按钮，如图10-5所示。10.2.1采购数据管理步骤4选中C2单元格，单击右侧激活的下拉按钮，选择“卷纸”选项，然后用相同的方法录入C3：C43单元格区域中的数据，如图10-6所示。10.2.1采购数据管理步骤5选择E2：E43单元格区域，在“数据”选项卡的“数据工具”组中单击“数据验证”按钮，打开“数据验证”对话框，在“允许”下拉列表框中选择“小数”选项，在“最小值”文本框中输入“0”，在“最大值”文本框中输入“5000”，单击“确定”按钮，如图10-7所示。10.2.1采购数据管理实现步骤视频10.2.2

采购货物的频次统计分析采购数据统计完成后，使用SUMIF和COUNTIF函数轻松将各个采购货物的金额和频次进行快速统计，具体操作方法如下。步骤1复制C2：C43单元格数据，选择C46单元格，按Ctrl+V组合键粘贴，如图10-8所示。10.2.2采购货物的频次统计分析步骤2选择刚粘贴的数据区域单元格，在“数据”选项卡的“数据工具”组中单击“删除重复值”按钮。在打开的“删除重复值”对话框中单击“确定”按钮删除重复的产品数据，如图10-9所示。10.2.2采购货物的频次统计分析步骤3选择删除重复值的单元格区域，按Ctrl+X组合键剪切数据到G2：G7单元格区域，如图10-10所示。10.2.2采购货物的频次统计分析步骤4选中H2单元格，在编辑栏中输入“=SUMIF（$C$2：$C$43，G2，$E$2：$E$43）”，并向下填充到H7单元格，统计出各项物品的采购金额，如图10-11所示。10.2.2采购货物的频次统计分析步骤5选中I2单元格，在编辑栏中输入“=COUNTIF（$C$2：$C$43，G2）”，并向下填充到I7单元格，统计出各项物品的采购频次，如图10-12所示。10.2.2采购货物的频次统计分析步骤6选择I2：I7单元格区域，单击“条件格式”下拉按钮，选择“突出显示单元格规则”→“其他规则”命令，打开“新建格式规则”对话框，如图10-13所示。步骤7选择“仅对排名靠前或靠后的数值设置格式”选项，在“对以下排列的数值设置格式”下拉列表框中选择“最高”，输入“2”，单击“格式”按钮，如图10-14所示。10.2.2采购货物的频次统计分析步骤8在打开的“设置单元格格式”对话框中单击“填充”选项卡，在“背景色”区域中选择“红色，个性色2，淡色60%”选项，单击“确定”按钮，如图10-15所示。步骤9返回到“新建格式规则”对话框中，单击“确定”按钮，如图10-16所示。10.2.2采购货物的频次统计分析步骤10选择H2：H7单元格区域，单击“条件格式”下拉按钮，选择“数据条”→“橙色数据条”选项，添加数据条直观展示各类物品的采购金额大小，如图10-17所示。实现步骤视频10.2.3

采购成本的同年环比分析在采购活动中，通常会关心当前月的采购投入相对于上月是增加了还是减少了，即环比增减情况。在本例中要计算出2~4月的采购环比增减情况，可借用SUMIF函数和简单的减法、除法公式，然后用图表集直接展示，具体操作方法如下。提示：在采购分析表中只有3~4月的采购数据明细，为了让环比分析数据更加准确，我们事先在表格中准备了2月的采购金额。步骤1选中H13单元格，在编辑栏中输入“=SUMIF（B2：B43，"<2019/3/31"，E2：E43）”，按Ctrl+Enter组合键计算出3月的采购金额，如图10-18所示。10.2.3采购成本的同年环比分析10.2.3采购成本的同年环比分析步骤2选中H14单元格，在编辑栏中输入“=SUMIF（B2：B43，">2019/3/31"，E2：E43）”，按Ctrl+Enter组合键计算出4月的采购金额，如图10-19所示。10.2.3采购成本的同年环比分析步骤3选中I13单元格，在编辑栏中输入“=（H13-H12）/H13”，并向下填充公式到I14单元格，自动计算出3月和4月的环比增长数字，如图10-20所示。10.2.3采购成本的同年环比分析步骤4选择I13：I14单元格区域，在“开始”选项卡的“样式”组中单击“条件格式”下拉按钮，选择“图标集”选项，在“方向”组中选择“3个三角形”选项，添加标识环比上涨和下跌的三角指示图标，如图10-21所示。实现步骤视频10.3销售分析对一家销售类型的企业而言，销售是基础，直接决定着企业的生死存亡。因此，销售数据实时分析相当必要。不仅会对去年同期进行同比分析、对周期的环比分析，还会对未来销售情况进行预测分析。10.3.1

同比分析同比分析是与历史同时期比较，简单理解为与不同年份的同一时期做比较，是在数据分析中经常用到的一种对照分析。在本例中我们将2019年各个月份的数据与2018年对应月份进行同比分析，查看2019年同期销售是在增长还是在减少，具体操作如下。步骤1选择任一数据单元格，单击“插入”选项卡，单击“表格”组中的“数据透视表”按钮，打开“创建数据透视表”对话框，如图10-22所示。步骤2选中“现有工作表”单选按钮，设置“位置”参数为“销售分析!$A$29”，单击“确定”按钮，如图10-23所示。步骤3在“数据透视表字段”窗格中拖动“日期”字段到“列”区域框中，拖动“年”字段到“行”区域框中，拖动“销售额”字段到“值”区域框中，如图10-24所示。步骤4在任意数据单元格上右击，在弹出的快捷菜单中选择“值显示方式”命令，在其子菜单中选择“差异百分比”命令，打开“值显示方式”对话框，如图10-25所示。10.3.1同比分析10.3.1同比分析10.3.1同比分析步骤5选择“基本字段”选项为“年”，“基本项”为“2018年”，单击“确定”按钮。在透视表中自动计算出2019年相对于2018年各个月份的同比百分数，如图10-26所示。实现步骤视频10.3.2

环比分析由于采购分析表中的数据较少，我们使用简单公式进行环比分析，由于这里数据较多，为了更加便利，我们使用透视表自动计算，具体操作方法如下。步骤1选择透视表所在的区域，按Ctrl+C组合键复制，选中A36单元格并粘贴，如图10-27所示。10.3.2环比分析步骤2在任一数据单元格上右击，在弹出的快捷菜单中选择“显示字段列表”命令，打开“数据透视表字段”窗格，如图10-28所示。步骤3拖动“年”“日期”字段到“行”区域框，拖动“销售额”字段到“值”区域框，如图10-29所示。10.3.2环比分析步骤4在B39单元格上右击，在弹出的快捷菜单中选择“值显示方式”命令，在子菜单中选择“差异”命令，打开“值显式方式”对话框，如图10-30所示。步骤5选择“基本项”选项为“（上一个）”，单击“确定”按钮，如图10-31所示。10.3.2环比分析步骤6数据透视表中自动对2018年、2019年数据进行环比计算分析（1月作为最开始对照的数据，所以1月对应的单元格都是空白），效果如图10-32所示。实现步骤视频10.3.3

未来增长的过滤波动分析企业对未来销售数据进行预测分析，通常都会相当谨慎，会人为地将一些波动因素进行过滤。在Excel中图表不具备直接过滤波动功能，但多次移动平均可以轻松实现，具体操作方法如下。步骤1在功能区空白处右击，在弹出的快捷菜单中选择“自定义功能区”命令，打开“Excel选项”对话框，如图10-33所示。步骤2选择“加载项”选项，在“管理”区域单击“转到”按钮，打开“加载项”对话框，如图10-34所示。10.3.3未来增长的过滤波动分析10.3.3未来增长的过滤波动分析步骤3选中“分析工具库”复选框，单击“确定”按钮，添加“数据分析”按钮，如图10-35所示。步骤4在“数据”选项卡的“分析”组中单击“数据分析”按钮，打开“数据分析”对话框，如图10-36所示。10.3.3未来增长的过滤波动分析10.3.3未来增长的过滤波动分析步骤5选择“移动平均”选项，单击“确定”按钮，打开“移动平均”对话框，如图10-37所示。步骤6设置“输入区域”参数为B2：B25单元格区域，设置“间隔”参数为“2”，设置“输出区域”参数为C2：C25单元格区域，单击“确定”按钮，计算出第一次移动平均数据（过滤波动的未来增长数据），如图10-38所示。10.3.3未来增长的过滤波动分析步骤7再次单击“数据”选项卡“分析”组中的“数据分析”按钮，打开“数据分析”对话框，如图10-39所示。10.3.3未来增长的过滤波动分析步骤8选择“移动平均”选项，单击“确定”按钮，打开“移动平均”对话框，如图10-40所示。10.3.3未来增长的过滤波动分析步骤9设置“输入区域”参数为B2：B25单元格区域，设置“间隔”参数为“3”，设置“输出区域”参数为D2：D25单元格区域，单击“确定”按钮，计算出第一次移动平均数据（过滤波动的未来增长数据），如图10-41所示。实现步骤视频10.4库存分析库存可简单理解为库房，专门用于物品的囤放和中转，以保证购入与售出的平衡。鉴于此，我们需要对库存数据进行实时监控和分析。10.4.1

库存积压数据的计算和判定库存中有一个专有的名词称为溢短，它是库存数量与标准数量的差值，即时反应当前库存数量与当前物品要求的库存量差距。在库存管理监控中，可使用减法公式计算出该值，