3.单元三 数据分析应用

Excel预测分析：时间数据数据分析应用Excel预测分析：时间数据01Excel预测分析：时间数据预测分析，简单来说是通过过去和现在的数据预测未来数据的过程，主要根据时间序列数据进行定量预测。预测分析常用在销售、市场、工业生产等领域，它能够帮助公司规划下一年的业务、预测产品的销量等。如果预测得准，就能够极大地促进公司发展；如果预测得不准，就很可能会造成公司人力、物力等资源的浪费。在Excel中进行预测分析常用的方法有4种，分别是使用预测工作表、使用趋势线、使用函数和使用分析工具。Excel预测分析：时间数据而时间序列是按发生的时间先后顺序排列而成的数据，一般数据中会有一列是日期。时间序列分析的主要目的是根据已有的历史数据对未来进行预测。在日常工作中，经常需要对时间序列数据做预测分析，预测分析就是根据现有的历史的数据来预估未知的未来的数据。例如，对下一年销量进行预测，以便做好业务规划。Excel有个非常强大的功能——预测工作表，它就是基于历史时间数据来预测未来某时间段内的数据，并且会以图表的形式展示出来，从中能直观地看到预测的趋势。Excel预测分析：时间数据需要注意的是，Excel2016及以上的版本才提供该功能，且仅支持Windows系统的Excel,Mac系统的Excel无此功能。接下来，我们依旧通过一个案例来学习如何使用Excel对时间序列数据进行预测分析。Excel预测分析：时间数据上海初禾服装实体店收集了2023年5月1日至5月24日的每日销量数据，并希望根据现有销量数据使用Excel预测工作表方法预测接下来一个星期（5月25日至5月31日）的销量。该预测将帮助店主进行库存管理、制定促销活动以及做出其他营销决策。Excel预测分析：时间数据一、调用预测工作表打开本实例的文件【Excel预测分析-时间序列预测-原始文件】，单击销量数据中的任意单元格，点击【数据】-【预测工作表】。在弹出的“创建预测工作表”窗格中，可调整“预测结束”时间。因为在本案例中，只想预测到5月31日的销量数据，所以对应地把“预测结束”时间调整为2023/5/31。Excel预测分析：时间数据一、调用预测工作表为了进一步了解Excel数据预测工作表的运行机制，把上图的“选项”展开，来看看其它参数的设置。除了上面提到的“预测结束”之外，Excel的预测工作表还有以下几个主要参数：Excel预测分析：时间数据一、调用预测工作表预测开始：即从历史数据中的哪一天的数据开始进行预测；默认是从历史数据的最后一天开始预测，如案例中，默认从5月24日开始预测。当然，预测时间也可以手动调整，让其与历史数据有所重叠，有助于提高预测的准确性。置信区间：也就是预测值的范围（预测的最大值-上限，预测的最小值-下限）默认是95%；该值越小，则上下限之间的范围越小。Excel预测分析：时间数据一、调用预测工作表季节性：周期性的规律，可以是自动检测或手动设置。设置一个周期数，比如12，表示每12个数据作为一个周期进行预测。需要注意的是，这里的“季节性”不是我们常规理解的“季节性”，而是历史数据所呈现的波动性和周期性。  例如：3小时/为一个周期；3天/为一个周期；7天/为一个周期；11天/为一个周期；30天/为一个周期…Excel预测分析：时间数据一、调用预测工作表如在本案例中，它已自动检测出“季节性”为7，也就是7天一个周期，所以，操作时并不需要手动设置为7，而直接用了“自动检测”。Excel预测分析：时间数据一、调用预测工作表日程表范围：就是历史数据里的时间数据；如本案例历史数据的日程表范围是“日期”列（A2:A25）。值范围：就是历史数据里用来计算预测的历史值；如本案例历史数据的值范围是列“销售量”列（B2:B25）。使用以下方式填充缺失点：为了处理缺少点，Excel使用插值，也就是说，只要缺少的点不到30%，都将使用相邻点的权重平均值补足缺少的点。如果要改为将缺少的点视为零，可以单击列表中的“零”。Excel预测分析：时间数据一、调用预测工作表聚合重复项使用：如果数据中包含时间戳相同的多个值，比如是同一日期的值有N个，那么Excel将默认取这些值的平均值作为这时间戳的值。若要使用其他计算方法可从列表中选择所需的计算。这么多参数不懂，怎么办？不要害怕，一般以上参数的设置，除了要调整“预测开始”“预测结束”或“季节性”的参数外，其他的默认即可。Excel预测分析：时间数据二、在对上面的参数设置完成后，点击“创建”预测结果表会在原来数据表左侧生成的新工作表中呈现，预测结果图表如下图所示，就预测到了5月25到5月31日的销量。Excel预测分析：时间数据二、在对上面的参数设置完成后，点击“创建”左侧的表格里，有趋势预测、置信下限（预测的最小值）、置信上限（预测的最大值），右边图表也对应着有趋势预测、置信下限、置信上限。Excel预测分析：时间数据三、预测结果的解读蓝色粗折线：是历史数据，对应着案例中5月1日到5月24日的销量数据；最上面的黄色细线：叫做置信上限，即未来趋势的上限不超过此线，也就是说，数据最好的时候，就是置信上限这个数值；如5月25日的置信上限为3189.47，即这天的销量最高能达到3189件；Excel预测分析：时间数据三、预测结果的解读最下面的黄色细线：叫做置信下限，即未来趋势的下限不超过此线，也就是说，数据最差的时候，就是置信下限这个数值；如5月25日的置信下限为1417.39，即这天的销量最低为1417件；上下两根黄色细线之间：叫做置信区间，即未来趋势在此区间中波动；如5月25日的销量就是在1417.39-3189.47之间波动；Excel预测分析：时间数据三、预测结果的解读中间黄色加粗线：叫做趋势线，即未来趋势最有可能沿此线的趋势发展；在案例中，5月25日的销量最有可能是2303件。Excel预测分析：时间数据三、预测结果的解读如果有对预测工作表有更多要求，可设置【置信区间】：设置预测值的上限和下限；置信区间值越小，上下限间的范围越小，反之，值越大上下限的范围越大。如右图是置信区间为95%和置信区间为80%的对比。Excel预测分析：时间数据四、注意事项预测工作表要求有两列数据：历史时间列和历史值列。其中，时间列要求：必须均匀分布，也就是说，时间列必须为间隔相等的时序列。如右图。Excel对比分析：热销产品数据分析应用Excel对比分析：热销产品01Excel对比分析：热销产品分析热销产品是销售数据分析中的一项重要工作。要分析热销产品，首先需要对各种规格产品的销售数量进行汇总，然后进行排序；最后才能分析出哪几种产品的销售数量排名靠前，是热销产品。本节将以一家家电零售商为案例公司，通过数据分析来了解不同规格产品的销售情况，并确定畅销产品。Excel对比分析：热销产品在2023年3月，一家家电零售商通过其线下门店销售了多种厨房电器产品，包括破壁机、豆浆机、空气炸锅、榨汁机和热水器。该公司希望通过数据分析来了解不同规格产品的销售情况，并确定畅销产品。为了达到这个目标，该公司收集了每日的销售清单，并记录了销售的数量和产品规格。使用数据分析技术，计算每种产品规格在3月份的总销售数量，并对其进行排序。最终，得到销售数量前8名的产品规格并要求选择柱状图来呈现前8名产品规格的销售情况。Excel对比分析：热销产品一、对不同规格的产品的销售数量进行汇总对不同规格的产品的销售数量进行汇总时，我们可以使用数据透视表。  根据要求，在“销售数据汇总”工作表中创建一个数据透视表。一次将【产品名称】和【规格】拖曳到【行】列表框中，将【数量】拖曳到【值】列表框中，即可得到不同规格产品的销售数量汇总表格。Excel对比分析：热销产品一、对不同规格的产品的销售数量进行汇总调整数据透视表的布局。切换到【数据透视表工具】栏的【设计】选项卡，在【布局】组中单击【报表布局】按钮，在弹出的下拉列表中选择【以表格形式显示】选项，即可将“产品名称”和“规格”显示为两列。Excel对比分析：热销产品一、对不同规格的产品的销售数量进行汇总以表格形式显示后，虽然“产品名称”和“规格”显示在了不同的列中，但是在“产品名称”列中同一个产品名称只显示了一次，存在空白单元格。如果不想显示空白单元格，可以再次单击【报表布局】按钮，在弹出的下拉列表中选择【重复所有项目标签】选项。Excel对比分析：热销产品一、对不同规格的产品的销售数量进行汇总数据透视表中默认是包含分类汇总数据的。在【布局】组中单击【分类汇总】按钮，在弹出的下拉列表中选择【不显示分类汇总】选项，则会隐藏分类汇总数据。Excel对比分析：热销产品二、将不同规格的产品按销售数量进行排序当前数据透视表中存在两个行字段，其中的数据已按产品名称分类汇总，因此不能直接对“求和项：数量”列中的数据进行排序。我们可以用公式将数据透视表中的数据引用到其他区域中，然后进行排序。对数据进行排序的目的是找出热销产品，一般对热销产品进行可视化时，需要同时显示产品名称和规格，因此在从数据透视表中引用数据时，可以直接将产品名称和规格合并显示。Excel对比分析：热销产品二、将不同规格的产品按销售数量进行排序通过公式将数据透视表中的数据引用到G列和H列，公式如下图所示。Excel对比分析：热销产品二、将不同规格的产品按销售数量进行排序引用完毕后，选中G列和H列中的数值，将其选择性粘贴成数值。Excel对比分析：热销产品二、将不同规格的产品按销售数量进行排序选中“销售数量”列中的任意一个单元格，切换到【数据选项卡】，在【排序和筛选】组中单击【降序】按钮。Excel对比分析：热销产品三、将不同规格的产品按销售数量进行排名排名需要用到RANK函数，RANK函数是Excel中的一个排名函数，用于返回一个数字在数字列表中的排名。其语法规则是：第三个参数“降序还是升序”，降序是0，升序是1，如果省略这个参数，则默认为按照降序来进行排名。Excel对比分析：热销产品三、将不同规格的产品按销售数量进行排名在单元格I1中输入“排名”，然后再单元格I2中输入公式“=RANK(H2,$H$2:$H$32)”，完成输入之后即可得到“破壁机PBJ04”的销售数量排名。Excel对比分析：热销产品三、将不同规格的产品按销售数量进行排名将单元格I2中的公式向下填充，即可得到其他规格产品的销售数量排名。我们看到，“豆浆机DJJ02”和“热水器RSQ01”都是排第6，两个第6，而后就是“空气炸锅KQZG03”排在了第8，而不是第7。那么如何实现，“豆浆机DJJ02”和“热水器RSQ01”都是排第6，“空气炸锅KQZG03”排在第7（而不是第8）呢？Excel对比分析：热销产品三、将不同规格的产品按销售数量进行排名这边可以借助数据透视表，来实现这种中式排名。  在数据区域任意一单元格单击，在单元格L1插入数据透视表。把“产品名称及规格”拖到行，把“销售数量”拖到值，连续拖两次。Excel对比分析：热销产品三、将不同规格的产品按销售数量进行排名然后对“销售数量2”进行“值”字段设置，在弹出的【值字段设置】窗口里，将值显示方式设置为“降序排列”。Excel对比分析：热销产品三、将不同规格的产品按销售数量进行排名修改透视表的列名称，同时对排名进行降序排列，最终如下：Excel对比分析：热销产品三、将不同规格的产品按销售数量进行排名可以看到，两个并列排名第6后之后，紧随的是第7，而不是从第8开始。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”选中L列、M列和N列中的数值，将其选择性粘贴成数值在P列、Q列和R列。在前8个排名序号前添加“TOP”，需要使用IF函数，其逻辑关系如下图所示。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”在单元格S1中输入列标题“排名榜”，然后再单元格S2中输入公式“=IF(R2<=8,"TOP"&R2,"")”,完成输入后，将公式填充到下方的单元格中，效果如右图所示。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”①创建主坐标轴对应的辅助数据为了使两个数据系列的横坐标一致，辅助数据的值应该与销量的值相差不大，销量的最大值为36，此处将“辅助系列”列中的数值统一设置为40即可。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”②创建条形图在“销售数据汇总”工作表中，选中单元格区域P1:Q10和T1:T10,切换到【插入】选项卡，在【图表】组中单击【插入柱形图和条形图】按钮。在弹出的下拉列表中选择【簇状条形图】选项，即创建了一个簇状条形图。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”③设置条形图的结构删除网格线、图例。分别选中网格线、图例，依次按【Delete】键即可删除。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”调整横坐标轴上的数值。一般默认的数值都会比实际数值大，所以数据系列绘图区右侧边界处有一定的留白，而此处不需要留白，先将横坐标轴的【边界】的【最大值】设置为【40.0】。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”删除横坐标轴。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”添加次要纵坐标轴。图表中如果有两个坐标轴，那么次坐标轴的数据系列是位于顶层的，此处应该将数据系列“销量“设置为次坐标轴，选中数据系列“销量”，打开【设置数据系列格式】任务窗格，选中【次坐标轴】单选钮。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”可以看到，将数据系列“销量”设置为次坐标轴后，会自动显示对应的横坐标轴，按照前面的方法，将其【边界】的【最大值】设置为【40.0】，然后将其删除。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”通过上图可以看到，将数据系列“销量”设置为次坐标轴后，次要纵坐标轴并没有显示出来。单击图表右侧的【图表元素】按钮，在弹出的下拉列表中勾选【次要纵坐标轴】复选框。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”次坐标轴默认显示了线条，而此处不需要显示线条，可以在【设置坐标轴格式】任务窗格中，将坐标轴的线条设置为【无线条】。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”两个纵坐标轴的轴标签是一样的，此处需要将左侧主要纵坐标轴的轴标签修改为排名。切换到【图表工具】栏的【设计】选项卡，在【数据】组中单击【选择数据】按钮，打开【选择数据源】对话框，选择“辅助系列“选项，然后在【水平（分类）轴标签】列表框中单击【编辑】按钮。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”打开【轴标签】对话框，将【轴标签区域】更改为“销售数据汇总“工作表中的S2:S10单元格区域。单击【确定】按钮，返回【选择数据源】对话框，再次单击【确定】按钮，返回图表效果图如下所示。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”现有图表的排名顺序是TOP8~TOP1的，如果想让其以TOP1~TOP8的顺序显示，可以选中左侧纵坐标轴，打开【设置坐标轴格式】任务窗格，勾选【逆序类别】复选框。两个纵坐标轴的轴标签是互相对应的，因此右侧纵坐标轴也需要设置为【逆序类别】，最终效果如下图所示。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”选中”销量”数据系列，为其添加标签，标签默认是显示在【数据标签外】的，此处将其【居中】显示。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”选中数据标签，在【开始】选项卡的【字体】组中设置数据标签文字的字号为8号，颜色白色，打开【设置数据标签格式】任务窗格，选中【纯色填充】单选钮，将其填充颜色设置为浅蓝色；然后将数据标签的左、右边距设置为【0.05厘米】，上下边距设置为【0厘米】。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”④美化条形图美化“销量“数据系列。选中“销量”数据系列，打开【设置数据系列格式】任务窗格，将其【间隙宽度】调整为【100%】，将其填充样式设置为【渐变填充】，其颜色及其他设置如下图所示。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”美化“辅助数据”数据系列。在工作表的空白处绘制一个无填充、无轮廓的矩形和一条浅蓝色虚直线，矩形和直线的长度相等，然后将矩形和直线水平、垂直居中，并将它们组合为一个整体。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”复制组合后的矩形和直线，选中“辅助数据”数据系列，然后粘贴组合图形，效果如下图所示。Excel对比分析：热销产品四、可视化销售数量排名前8的产品在前8个排名序号前添加“TOP”为图表添加合适的标题，“销售数量单品TOP8“，最终效果图如下。Excel趋势分析：全年销售数据分析应用Excel趋势分析：全年销售01Excel趋势分析：全年销售通过观察全年销售额的变动趋势，可以分析出销售额是否会受季节影响，是否存在淡季、旺季的差异等。本例子中：跨国公司ABCCorporation，专门销售多种产品线，包括电脑用品、工业用品、工艺收藏、户外运动和家居园艺产品。为了更好地了解公司的销售情况，我们收集了全年每个季度的销售数据，希望通过使用Excel的函数来对这些销售数据进行分析。我们将使用SUMIFS函数来按产品线统计每个销售员在各个季度的销量，并使用SUMIF函数按产品线统计每个季度的销量。Excel趋势分析：全年销售最后，将基于全年销售额，制作一个面积图，展示每个季度的销售情况。通过对这些数据进行分析，可以识别每个销售员在不同产品线上的表现，并了解各个季度的销售趋势。这有助于我们更好地评估产品线的表现，并制定相应的销售策略和目标。Excel趋势分析：全年销售一、多条件求和：sumifssumifs函数，多条件求和，语法规则为：Excel趋势分析：全年销售一、多条件求和：sumifs需求1：按产品线统计每个销售员的各季度的销量。例如工业用品销售员AA的第一季度销量是多少？同样是工业用品销售员BB的第一季度销量是多少？Excel趋势分析：全年销售一、多条件求和：sumifs在目标单元格J3输入公式“=SUMIFS(C$2:C$43,$A$2:$A$43,$I3,$B$2:$B$43,$H3)”，按【Enter】键完成输入，将公式填充到右方和下方的单元格中，效果如下图所示。Excel趋势分析：全年销售一、多条件求和：sumifs公式说明：这边sumsif设置两个条件匹配，将条件区域A2:A43（销售员列）中的每一个单元格与条件值I3单元格（销售员）比对；将条件区域B2:B43（产品线列）中的每一个单元格与条件值H3（产品线）单元格进行比对；如果都相等,则将C2:C43（“第一季度”列）中对应的销量进行求和。Excel趋势分析：全年销售二、单条件求和：sumifExcel趋势分析：全年销售需求2：按产品线统计每个季度的销量二、单条件求和：sumifExcel趋势分析：全年销售在目标单元格I11中输入公式“=SUMIF($B$2:$B$43,$H11,C$2:C$43)”，然后向右、向下填充公式，最后运用SUM基础求和函数求出总计行内容。二、单条件求和：sumifExcel趋势分析：全年销售公式说明：sumif将数据区域B2:B43（产品线列）中的每一个单元格都与条件值H11单元格（产品线）进行比对，如果相等，就与B2:B43（产品线列）中对应的“第一季度”列（C2:C43）的值进行求和。也就是按产品线分类来求和。二、单条件求和：sumifExcel趋势分析：全年销售通过以上两种情形，我们可以看一下sumifs函数与sumif函数的参数位置的区别：除了参数顺序不一致之外，sumifs实现原理其实与sumif函数完全相同，只不过，是在后面多添加了统计条件而已。二、单条件求和：sumifExcel趋势分析：全