单变量分析与多变量分析是什么关系(excel单变量分析公式)(1)

在EXCEL的数据选项卡-模拟分析下,有三个功能:方案管理器、单变量求解和模拟运算表。学习EXCEL的同学几乎很少接触到这个模块。这个模块有什么功能及应用场景呢?

接下来几天我们会通过多篇文章分别一一介绍

本文为第三篇,介绍 模拟分析|单变量求解

一、什么是单变量求解

来自百度百科解释

单变量求解是解决假定一个公式要取的某一结果值,其中变量的引用单元格应取值为多少的问题。

通俗点解释

单变量求解就是解方程(一元方程)

以下通过一个简单案例展示来理解

某同学期末测试成绩如下,已经有三科成绩出来,物理还没考。

问题:如果该同学希望平均分是81分,物理应该考多少分?

单变量分析与多变量分析是什么关系(excel单变量分析公式)(2)

设该同学平均为Y, 物理成绩为X,则

平均分 Y=(X 80 85 79)/4

如果Y=81, 那么 X=81*4-80-85-79

以上如果用单变量求解的做法是

1、设置数据关系

平均分 B18=AVERAGE(B14:B17)

单变量分析与多变量分析是什么关系(excel单变量分析公式)(3)

2、调出单变量工具,路径:数据--模拟分析--单变量求解

单变量分析与多变量分析是什么关系(excel单变量分析公式)(4)

3、设置参数

可变单元格:即物理成绩X,设置B17

目标单元格:即平均分Y,设置B18

目标值:即Y的目标(目标平均分),这里是81

点击确定后,excel自动进行迭代运算

单变量分析与多变量分析是什么关系(excel单变量分析公式)(5)

运算得到结果后,点击确认,EXCEL自动填入结果X与Y的结果

单变量分析与多变量分析是什么关系(excel单变量分析公式)(6)

通过以上案例,总结单变量求解的应用场景必须包括以下3点

---1个自变量X 和1个因变量Y

---X与Y的数据关系是已知

---Y的目标值确定,求X的值

像以上的简单计算案例,还未能发挥 单变量求解 的功能,以下通过案例2来发掘它的魅力

二、实战案例

如图:某店铺过去15周的收入,按照一定的复合增长率增长

已知第一周收入150万,15周总收入6000万,请问该复合增长率是多少?

单变量分析与多变量分析是什么关系(excel单变量分析公式)(7)

解题思路

1、 找出自变量 X与因变量Y

显然 X 就是 复合增长率,Y就是总收入

2、确定X与Y的关系,总收入等于15周收入总和

Y=150 150*(1 X) 150*(1 X)^2 …… 150*(1 X)^14

3、确定目标值,Y=6000

操作步骤:

1、设置数据关系

B4=$B$3*(1 $B$2)^(A4-1),下拉公式(注意固定单元格)

B18=SUM(B3:B17)

单变量分析与多变量分析是什么关系(excel单变量分析公式)(8)

2、调出单规划求解,设置参数

单变量分析与多变量分析是什么关系(excel单变量分析公式)(9)

3、运行后,得到结果 当复合增长率=0.12872时,总收入=5999.99(无限接近6000),为最优解。

单变量分析与多变量分析是什么关系(excel单变量分析公式)(10)

虽然单变量求解使用起来很方便,但它有一些小毛病需要注意

注意1.问题无解

单变量求解的运算次数取决于设置的迭代次数,当在迭代次数内没有计算出结果时,EXCEL会自动放弃计算,不返回结果。

解决办法设置迭代次数:文件--选项--公式--启用迭代计算

单变量分析与多变量分析是什么关系(excel单变量分析公式)(11)

注意2.结果精度

单变量求解的结果精度取决于最大误差,如果误差设置过大,会导致求解结果不够准确,相反如果误差设置过小,会导致求解时间延长。

设置最大误差:文件--选项--公式--启用迭代计算(和注意1同处)

注意3.多解问题

如果问题本身有多个解,单变量求解只会返回与初始值最接近的一个解,而不会同时返回其他解。

看到这里,是不是对单变量求解理解更深入呢。