《Excel-2016数据处理与分析》教学课件08常用的数据分析工具

模块8常用的数据分析工具本模块主要学习Excel的高级数据分析工具：模拟分析和分析数据库，其中包含多项数据分析功能，如单双变量的模拟分析、移动平均、回归分析等，是数据分析人员必备的技能。学习目标本模块共介绍10个数据分析的高级方法，其中需要重点掌握的是模拟运算、规范求解、移动平均和方差分析。至于数据库中的其他分析工具，使用的方法基本相同，读者可以举一反三、融会贯通。学习重点8.1

模拟分析模拟分析，顾名思义是一种对未来数据的假设性分析，属于静止变量的预测、预算，作为当下决策制定的数据支撑参考。Excel中的模拟运算分为两种：模拟运算和方案分析。下面分别结合实例进行讲解。提示：模拟运算中的变量是人为主观的制定，具有静止停顿的特性，可能会随着政策或市场的变化而变化，所以称为静止变量。8.1.1

模拟运算表模拟运算表根据提供的变量数据和运算公式对指定数据进行一系列自动计算，分为单变量模拟运算和双变量模拟运算，操作方法虽有细小差异，不过大体相同。【实例8-1】使用单变量模拟运算表计算变动利率下的还贷金额小王向银行借款50万元，以15年为固定的还款期限，他需要按照每年不同的利率来计算出每年需要还款的金额，于是他使用单变量模拟运算表计算未来还款金额，操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.1\还款方案分析.xlsx”文件，选择“浮动还贷利率下的年还款”，选中D3单元格，在编辑栏中输入函数“=PMT（$C$3，$B$3，$A$3）”，按Ctrl+Enter组合键确认，如图8-1所示。步骤2选择C3：D12单元格区域，在“数据”选项卡下，单击“分析”组中的“模拟分析”下拉按钮，在弹出的下拉菜单中选择“模拟运算表”选项，打开“模拟运算表”对话框，如图8-2所示。8.1.1模拟运算表8.1.1模拟运算表步骤3在“输入引用列的单元格”后的文本框中输入“$C$3”，单击“确定”按钮，如图8-3所示。步骤4Excel自动计算出每年按照不同利率的还款金额数据，如图8-4所示。实现步骤视频8.1.1模拟运算表支招：上面操作的是纵向表格，横向表格的操作步骤与之相同，如图8-5所示。8.1.1模拟运算表【实例8-2】使用双变量模拟运算表计算利率和年限同时变动下的还贷金额小王向银行贷了一笔款，贷款年限不同，利率也会发生相应变化，怎样计算出不同年限对应的还款金额呢？在银行人员的指点下，小王运用Excel的双变量模拟运算功能轻松解决了这个现实问题，他的操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.1\还款方案分析.xlsx”文件，选择“年限、还款利率变动下的年还款”工作表，选中B4单元格，在编辑栏中输入函数“=PMT（$B$3，$B$2，$B$1）”，按Ctrl+Enter组合键确认，如图8-6所示。步骤2选择B4：K10单元格区域，在“数据”选项卡下单击“分析”组中的“模拟分析”下拉按钮，选择“模拟运算表”选项，打开“模拟运算表”对话框，如图8-7所示。8.1.1模拟运算表8.1.1模拟运算表步骤3选择“输入引用行的单元格”为B3单元格，选择“输入引用列的单元格”为B2单元格，单击“确定”按钮，如图8-8所示。步骤4Excel自动计算出利率和年限同时变动下的还款金额，如图8-9所示。实现步骤视频8.1.2方案分析在多套方案中要进行最优选择，必须进行数据比对。如果完全靠人力，效率会很低。Excel中的方案分析功能能够自动匹配对比并及时得出计算结果或摘要方案。【实例8-3】使用方案优化分析投资项目小李打算在银行贷款一笔资金作为投资准备金，他想寻求最优的还贷方案，因此使用了方案优化在不同的项目中生成方案，选择最优方案，操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.1\还贷方案.xlsx”文件，选择B3：B5单元格区域，在“数据”选项卡下单击“预测”组中的“模拟分析”下拉按钮，在弹出的下拉列表中选择“方案管理器”选项，如图8-10所示。步骤2在打开的“方案管理器”对话框中单击“添加”按钮，如图8-11所示。8.1.2方案分析8.1.2方案分析步骤3打开“添加方案”对话框，在“方案名”文本框中输入“还贷方案1”，单击“确定”按钮，打开“方案变量值”对话框，在“1”后的文本框中输入“250000”，在“2”后的文本框中输入“360”，“3”保持不变，单击“添加”按钮，如图8-12所示。8.1.2方案分析步骤4返回到“添加方案”对话框，在“方案名”文本框中输入“还贷方案2”，单击“确定”按钮，打开“方案变量值”对话框，在“3”后的文本框中输入“0.066”，其他保持不变，单击“添加”按钮，如图8-13所示。8.1.2方案分析步骤5在“方案名”文本框中输入“还贷方案3”，单击“确定”按钮，打开“方案变量值”对话框，在“1”后的文本框中输入“280000”，在“2”后的文本框中输入“120”，“3”保持不变，单击“确定”按钮，如图8-14所示。8.1.2

方案分析步骤6返回到“方案管理器”对话框中，单击“摘要”按钮，打开“方案摘要”对话框，选中“方案摘要”单选按钮，设置“结果单元格”参数为“B6”，单击“确定”按钮，如图8-15所示。8.1.2方案分析步骤7Excel自动将方案分析摘要报表放置在新建的“方案摘要”工作表中，如图8-16所示。实现步骤视频8.1.3

单变量求解单变量求解是根据已知的数据求解未知的数据，类似于一元一次方程。例如，已知还款金额和期限，需要根据付款的多少计算利率。【实例8-4】制作还贷利率计算模型会计助理小胡每隔一段时间都会对公司各项还贷利率进行计算，为了一劳永逸，她使用单变量计算求解功能制作了一个简单模型，自动根据每期还款金额数同步计算出还贷利率，操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.1\还贷利率.xlsx”文件，选中B6单元格，在编辑框中输入函数“=PMT（B5/12，B4，B3）”，按Enter键确认，完成B6付款的数据的输入，如图8-17所示。步骤2在“数据”选项卡的“预测”组中单击“模拟分析”下拉按钮，选择“单变量求解”选项，如图8-18所示。8.1.3单变量求解8.1.3单变量求解步骤3打开“单变量求解”对话框，在“目标单元格”文本框中输入“$B$6”，在“目标值”文本框中输入“-1500”，在“可变单元格”文本框中输入“$B$5”，单击“确定”按钮，如图8-19所示。步骤4打开“单变量求解状态”对话框，等待计算停止后，单击“确定”按钮，如图8-20所示。8.1.3单变量求解步骤5根据相应数据计算出还款利率，如图8-21所示。提示：在数据表格中“目标单元格”必须含有公式。实现步骤视频8.2

规划分析规划分析通过更改用于计算目标单元格中的约束条件，从而算出目标单元格中的最优值。这里的最优值可以是最大值（利润），也可以是最小值（投入成本）或是中值，完全根据实际需求而定。8.2.1

创建规划模型使用规划求解对人员或资料进行自动配置前，需要先创建一个计算模型，告诉Excel按照何种方式计算求解，这是必不可少的工作。【实例8-5】创建生产人员配置模型老王是生产部的主任，专门负责人员和资金的分配。一天小林告诉他：Excel中的规划求解功能可自动分配，不用再花时间手动计算了。抱着试一试态度的他，操作了很多遍都没有得出任何数据，他带着疑惑找到小林。小林说明了问题所在，并为老王演示模型创建过程，操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.2\优化配置.xlsx”文件，选中D10单元格，在编辑栏中输入公式“=A10*B3+B10*C3+C10*D3”，如图8-22所示。步骤2选中E10单元格，在编辑框内输入公式“=A10*B2+B10*C2+C10*D2”，如图8-23所示。8.2.1创建规划模型8.2.1创建规划模型步骤3选中F10单元格，在编辑框内输入公式“=SUM（A10：C10）”，如图8-24所示。步骤4完成模型的建立，如图8-25所示。实现步骤视频8.2.2

规划模型求解规划模型求解是根据数据和实际要求，添加约束条件，从而求解到最优值。在这个过程中需要添加多组约束条件，而这些条件主要来自规划求解模型中。【实例8-6】使用规划模型优化配置生产人员人力资源部的小张接到一个新任务：根据最新的订单投入，按照人数和产出系数临时分配生产部门人员，组建一个临时生产团队。在数据模型已经创建的情况下，小张使用规范求解功能轻松得出组件方案。1.加载规划求解工具默认情况下规划求解工具并没有直接显示在“数据”选项卡的“分析”组中，需要手动加载。操作步骤如下。步骤1打开任一工作簿，单击“文件”选项卡，进入“文件”界面，单击“选项”按钮，打开“Excel选项”对话框，如图8-26所示。8.2.2规划模型求解8.2.2规划模型求解步骤2在左侧列表中选择“加载项”选项，单击“转到”按钮，打开“加载项”对话框，如图8-27所示。步骤3选中“规划求解加载项”复选框，单击“确定”按钮，完成“规划求解”工具的加载，如图8-28所示。8.2.2规划模型求解2.使用规划求解寻求人员配置最优解步骤1打开素材文件中的“素材\模块8\8.2\优化配置1.xlsx”文件，切换到“数据”选项卡，单击“分析”组中的“规划求解”按钮，打开“规划求解参数”对话框，如图8-29所示。步骤2设置“设置目标”参数为“$D$10”，选中“最大值”单选按钮，设置“通过更改可变单元格”参数为“$A$10：$C$10”，单击“添加”按钮，打开“添加约束”对话框，如图8-30所示。8.2.2规划模型求解8.2.2规划模型求解支招：在设置“设置目标”和“通过更改可变单元格”参数时，可直接在表格中选择对应的单元格，方法为：将鼠标指针定位在文本框中，在表格中拖动选择，如图8-31所示。8.2.2规划模型求解步骤3设置“单元格引用”参数为“A10”，运算符为“<=”，在“约束”文本框中输入“10”（A部门的最大人数），单击“添加”按钮，如图8-32所示。步骤4设置“单元格引用”参数为“B10”，运算符为“<=”，在“约束”文本框中输入“11”（B部门的最大人数），单击“添加”按钮，如图8-33所示。8.2.2规划模型求解步骤5设置“单元格引用”参数为“C10”，运算符为“<=”，在“约束”文本框中输入“18”（C部门的最大人数），单击“添加”按钮，如图8-34所示。步骤6返回“规划求解参数”对话框，单击“添加”按钮，打开“添加约束”对话框，设置“单元格引用”参数为“E10”，运算符为“<=”，在“约束”文本框中输入“225980”（投入最大预算），单击“添加”按钮，如图8-35所示。8.2.2规划模型求解步骤7设置“单元格引用”参数为“F10”，运算符为“<=”，在“约束”文本框中输入“30”（投入总人数），单击“添加”按钮，如图8-36所示。步骤8设置“单元格引用”参数为“A10：C10”，运算符为“int”，单击“添加”按钮，如图8-37所示。8.2.2规划模型求解步骤9返回到“规划求解参数”对话框中，在“选择求解方法”下拉列表框中选择“单纯线性规划”选项，单击“求解”按钮，如图8-38所示。步骤10打开“规划求解结果”对话框，单击“确定”按钮，如图8-39所示。8.2.2规划模型求解提示：非线性GRG是求解约束极小化问题的较好的算法。单纯线性规划拥有唯一最优解。演化是最接近最优解的模糊算法。步骤11Excel自动对A、B、C三个部门人员进行配置，实现利润最大化和投入最小化，如图8-40所示。8.2.2规划模型求解支招：如需制作报告大纲，只需在打开的“规划求解结果”对话框内选中“制作报告大纲”复选框，选择“运算结果报告”选项，单击“确定”按钮，Excel自动在新工作表中生成一份运算结果报告，在其中可以看到人员配置是否达到极限、公式及其他重要信息，如图8-41所示。实现步骤视频8.3

方差分析为了了解两组数据间的相关性，以及比较两组数据之间的差异，推断出各个数据之间存在的差异，使用方差分析可轻松得出结果。在数据分析中常用的方差分析有两个：单因素方差和多因素方差。8.3.1

单因素方差分析数据分析中有一个重要的参数：实验指标，而影响实验指标的条件称为因素，因素所处的状态称为水平，若数据分析中只有一个因素变化则称为单因素变化，在Excel中称为单因素方差分析。【实例8-7】单因素方差分析成本变化情况前一段时间，公司领导层对经营模式进行创新改变，最直接的影响是产品成本发生了变化。为了验证改革创新是否有效益，领导让小王拿出分析样本。小王作为Excel技术控，很快就拿出了样本，他的操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.3\成本.xlsx”文件，打开“Excel选项”对话框，选择“加载项”选项，单击“转到”按钮，打开“加载项”对话框，如图8-42所示。步骤2选中“分析工具率”复选框，单击“确定”按钮，添加“数据分析”按钮到“数据”选项卡“分析”组中，如图8-43所示。8.3.1单因素方差分析8.3.1单因素方差分析步骤3单击“数据”选项卡，单击“分析”组中的“数据分析”按钮，打开“数据分析”对话框，如图8-44所示。步骤4在“分析工具”列表框中选择“方差分析：单因素方差分析”选项，单击“确定”按钮，如图8-45所示。8.3.1单因素方差分析8.3.1单因素方差分析步骤5打开“方差分析：单因素方差分析”对话框，在“输入区域”中输入B1：C8单元格区域，选中“输出选项”的“输出区域”单选按钮，在“输出区域”后输入“A10”，单击“确定”按钮，如图8-46所示。步骤6单因素方差分析成本情况生成，从单因素方差分析表中可知，改制后的成本下降，如图8-47所示。实现步骤视频8.3.2

双因素方差分析在数据分析中，若有两个变量影响数据分析结果，也就是两组数据影响数据走势情况，则单因素方差无能为力，需要启用双因素方差功能。【实例8-8】双因素方差分析区域销售变化情况产品销量的多少，有两个重要的影响因素：日期和地区。同一地区不同日期的销量会发生变化，即淡旺季。不同月份、不同的地区也有销量的多少差异，即地域性。公司领导为了弄清这两个因素同时作用对产品销量的影响情况，让数据分析员小张快速拿出分析样本，小张的具体操作如下。步骤1打开素材文件中的“素材\模块8\8.3\区域销售.xlsx”文件，切换到“数据”选项卡，单击“分析”组中的“数据分析”按钮，如图8-48所示。步骤2在打开的“数据分析”对话框的“分析工具”列表框中选择“方差分析：无重复双因素分析”选项，如图8-49所示，单击“确定”按钮，打开“方差分析：无重复双因素分析”对话框。8.3.2双因素方差分析8.3.2双因素方差分析提示：双因素方差分析分为重复的双因素方差分析和无重复的双因素方差分析。两者最直接的区别是重复的双因素方差用于分析带有重复项的数据，无重复的双因素方差分析适用于没有重复的数据（不能分解出双因素的交互作用）。步骤3在“输入区域”文本框中输入A1：E11单元格区域，选中“标志”复选框，选中“输出区域”单选按钮，在其后的文本框中输入“$A$13”，单击“确定”按钮，如图8-50所示。8.3.2双因素方差分析步骤4在表格中生成无重复双因素方差分析表格，如图8-51所示。提示：在方案分析结果中有SS、df、MS、F、P-value和F-crit列。其代表的意思分别是：SS离均差平方和，也就是变量中每个数据点与变量均值差的平方和。df是自由度。MS是均方，其值等于对应的SS除以df。F是F统计量，是方差分析中用于假设检验的统计量，其值等于处理的MS除以误差的MS。P-value是在相应F值下的概率值。F-crit是在相应显著水平下的F临界值。实现步骤视频8.4预测分析预测分析对未来数据走势或情况进行预测分析，依靠的基础是已有的一组或是多组数据。常用的方法有移动平均分析（过滤波动分析）和回归分析。下面分别进行实例讲解。8.4.1

移动平均分析依次得到一组测定值时，按顺序取一定数量的数据并算得其全部算术平均值，得到的数据就称为移动平均值，简单理解为用一组最近的数据来预测未来一期或几期的数据，这个过程中将会自动过滤掉一些波动因素，让结果更加接近于客观。【实例8-9】移动平均分析未来收益率公司老板想要了解公司未来的收益情况，于是想叫助理小朱制作一份关于公司未来收益率的分析样本，小朱想起之前老员工教给他的方法，即移动平均分析，于是几分钟就完成了，他的操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.4\收益表.xlsx”文件，在“数据”选项卡的“分析”组中单击“数据分析”按钮，如图8-52所示。步骤2在打开的“数据分析”对话框中选择“移动平均”选项，单击“确定”按钮，打开“移动平均”对话框，如图8-53所示。8.4.1移动平均分析8.4.1移动平均分析步骤3设置“输入区域”参数为D2：D5单元格区域，在“间隔”文本框中输入“2”，设置“输出区域”参数为E2：E7单元格区域，选中“图表输出”复选框，单击“确定”按钮，如图8-54所示。步骤4完成使用移动平均分析未来收益率，结果表明未来收益率将持续在11%左右，如图8-55所示。实现步骤视频8.4.2回归分析回归分析的全称是回归分析预测法，是一种重要的市场预测方法，它是在自变量和因变量之间相关关系的基础上，建立变量之间的回归方程，并将回归方程作为预测模型，然后根据自变量在预测期的数量变化来预测因变量关系，大多表现为相关关系。【实例8-10】回归分析预测投入与收益相关性分析公司负责人老李近期在对公司的未来收益情况进行预测分析，用了多种方法后总觉得靠不住，心里没底，但又找不到更多的分析方法。在彷徨焦虑之际，财务小张用回归分析帮助了老李，他的操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.4\投资与收益.xlsx”文件，在“数据”选项卡的“分析”组中单击“数据分析”按钮，打开“数据分析”对话框，如图8-56所示。步骤2选择“回归”选项，单击“确定”按钮，如图8-57所示。8.4.2回归分析8.4.2回归分析步骤3在打开的“回归”对话框中选择“Y值输入区域”为D2：D12单元格区域，选择“X值输入区域”为E2：E12单元格区域，选择“输出区域”为B14：E27单元格区域，单击“确定”按钮，如图8-58所示。步骤4Excel自动生成了一份回归分析预测样本，可以直接看出投资与收益成正比，投资越高收益越高，如图8-59所示。实现步骤视频8.4.3

一键式预测分析在Excel2016中有一个一键式预测分析功能。它能快速根据已有的数据对数据未来的发展趋势进行预测展示，操作也很简单，只需几步即可完成，属于“傻瓜式”智能分析。【实例8-11】预测未来一周的收入走势情况小王作为创业者，刚开了一家快餐销售门店，为了降低创业的风险和管控成本利润，他使用一键式预测分析功能分析下一周的利润情况，操作步骤如下。步骤1打开素材文件中的“素材\模块8\8.4\盈亏分析.xlsx”文件，选择A2：A9单元格区域，按住Ctrl键选择C2：C9单元格区域，在“数据”选项卡的“预测”组中单击“预测工作表”按钮，在打开的对话框中单击“创建”按钮，如图8-60所示。步骤2完成一键式预测分析未来一周的收入走势情况，如图8-61所示。提示：非预测工作表中的“置信下限”与“置信上限”可简单对应理解为最大值（最高点）与最小值（最低点）。8.4.3一键式预测分析实现步骤视频8.5

综合实例在前面我们掌握了一些常用的数据分析工具，虽然都是在不同的实例不同的情景中使用的，但仍然可以根据时间工作需要进行联合使用。例如对产品销售数据进行分析，操作方法如下。8.5综合实例步骤1打开素材文件中的“素材\模块8\综合实例\产品销售情况与投资方案分析.xlsx”文件，选择“产品销售分析”数据表，在“数据”选项卡的“分析”组中单击“数据分析”按钮，打开“数据分析”对话框，如图8-62所示。8.5综合实例步骤2在打开的“数据分析”对话框的“分析工具”列表框中选择“相关系数”选项，单击“确定”按钮，如图8-63所示。8.5综合实例步骤3在打开的“相关系数”对话框中设置“输入区域”为B1：C9单元格区域，选中“标志位于第一行”复选框，选中“输出区域”单选按钮，如图8-64所示。步骤4将鼠标指针定位于“输出区域”文本框中，在表格中选中A14单元格，如图8-65所示。8.5综合实例8.5综合实例步骤5选中E2单元格，在“数据”选项卡的“分析”组中单击“数据分析”按钮，打开“数据分析”对话框，在“分析工具”列表框中选择“移动平均”选项，单击“确定”按钮，打开“移动平均”对话框，如图8-66所示。8.5综合实例步骤6设置“输入区域”参数为D2：D9单元格区域，在“间隔”文本框中输入“2”，设置“输出区域”参数为E2：E9单元格区域，选中“图表输出”复选框，单击“确定”按钮，如图8-67所示。8.5综合实例步骤7单击“产品投资方案分析”工作表标签，单击“数据”选项卡“预测”组中的“模拟分析”下拉按钮，在弹出的下拉列表中选择“方案管理器”选项，打开“方案管理器