本文共计约2000字，建议阅读时间3分钟
目录
- 模拟分析
- 规划求解
- 建模分析
- 三表建模
- 结果表
- 参数表
- 计算表
- 综合建议
- 避免出错
- 调整工具
- 演示顺序
本篇开始讲Excel的建模功能 。
模拟分析
Excel中最为人称赞的功能之一是可以创建动态模型 。由我们前面已经掌握的知识，可以很简单创建一个随引用单元格变化而变化的公式，但如果参数较多、公式较为复杂时，管理起来比较混乱 。
Excel为解决这种场景，有一个非常实用的工具，叫“模拟分析” 。
举个例子，假设我希望通过投资理财实现10年后资产超过500万，那么我需要多少本金和年化收益率，目标有可能能达成 。影响10年后资产的关键因素有2个，一是初始本金，二是年化收益率 。
第一步，我们需要建立10年后资产与初始本金及年化收益率的公式关系 。
第二步，建立模拟运算表的初始表格，左上角是链接到目标单元格的公式 。
【一 Excel数据分析——建模分析】第三步，选中初始表格，运用“数据”|“预测”|“模拟分析”|“模拟运算表”来输出模拟运算结果 。
用条件格式将结果超过500万的标识出来，可以看到，有三种可能的组合 。
- 初始资产50万，年化收益率在25%-30%之间 。
- 初始资产100万 ， 年化收益率在15-20%之间 。
- 初始资产200万 ， 年化收益率在10%偏下一点 。
模拟分析一般是用来解决what-if问题 ， 还可以通过多种组合的模拟数据 ， 判断结果受哪个因素影响更大 ， 即敏感度分析 。
在上面这个例子中，10年后的资产受年化收益率的影响更大一些，因为同样目标是500万，但初始资产50万是200万的1/4 ， 但年化收益率却不需要是10%的4倍，25-30%即可 。
这就是复利的力量 。——爱因斯坦所说的第8大奇迹 。
这只是双因素模拟分析 。如果因素再加一个呢，比如再加上每年的工资结余 。
这就得用上方案管理器了 。
第一步，仍然是先公式建模 。
第二步，添加方案 ， 设置每个方案的参数值 。操作路径是“数据”|“预测”|“模拟分析”|“方案管理器”|“添加” 。
第三步，将输入参数和输出结果，定义名称 。“公式”|“定义的名称”|“工具所选内容创建” ， 选择最左列 。
在名称管理器，可以看到刚刚定义的名称 。
第四步，输出方案 。操作路径是“数据”|“预测”|“模拟分析”|“方案管理器”|“摘要” 。
但参数数量不多、公式不复杂，且方案数量也不多的时候，使用方案管理器的必要性不大 。
手动模拟结果如下 。除了公式冗余外，没有其他缺点 。相比方案管理器，调整参数更加方便 ， 公式可以追踪，这更有掌控感 。
规划求解
规划求解要解决的问题跟上述问题相反 。上述问题是，我们已经有了方案（参数），能达到什么目标 。只不过方案可以有多个进行比较 。
而规划求解是 ， 假设我已经知道需要达到什么样的目标，在既定的限制条件下，我能做什么 。
我们将上述案例变换一下，假设我有50万初始资产，需要多少年化收益率，可以10年后总资产达到500万 。
第一步，建立10年后资产与初始本金及年化收益率的公式关系，明确固定条件和可变条件 。
第二步 ， 点击“数据”|“分析”|“规划求解”，选择“设置目标”为十年后资产 ， 可变单元格为年化收益率，点击求解，保留规划结果 。
可知，50万初始资产在10年后变成500万，需要年化收益率达到26% 。
这其实是一个非常简单的规划求解案例 ， 用代数求解法在Excel通过公式也可以得出 。
规划求解的难点从来都不在解法上，而是在模型的构建上 。
再讲一个稍微复杂的案例，是《数据、模型与决策：基于电子表格的建模和案例研究方法》这本书的一个案例习题 。
一家手机制造商的营销小组计划进行一次电话调研 ， 以确定消费者对一款正在开发的手机的态度 。为了使这一分析具有充足的样本量，他们需要接触至少100名年轻男性( 40岁以下）、150名年龄较大的男性（40岁以上）、120名年轻女性（40岁以下）和200名年龄较大的女性(40岁以上） 。白天打一个电话的成本是1美元，晚上打一个电话的成本是1.5美元（因为晚上人工成本较高） 。不管有没有人接听电话 ， 这一成本都会发生 。下表给出了特定类型的消费者接听电话的概率 。假设不管是谁先接听电话,调研都可以开始进行 。另外，由于晚上员工数量有限，所以最多只能有1/3的电话是在晚上打出的 。营销小组应该如何做才能以最低的成本满足样本量的要求?
先构建模型 。
假设条件是白天和晚上各打多少个电话（绿色底纹），初始数值随便先写一个，分别是1和0 。目标总成本=C34*C35 D34*D35 。
设置约束条件 ， 个人群样本量=白天电话数*白天接电话概率 晚天电话数*晚天接电话概率计算，晚上打电话比例=晚上打电话数/(白天打电话数 晚上打电话数) 。
规划求解的设置如下图所示 。点击求解可以得到结果 。
找到一个最优解，如下图所示 ， 需要白天打500个电话，晚上打250个电话 ， 所有约束条件均满足，并且总成本最低 。
但其实最优解跟设置的初始参数是有关的 。并不一定有解，也并不一定单次运行下的最优解是全局最优解 。
比如，我们将初始参数设置是0和1时（白天打电话数量为0，晚上打电话数量为1），结果如下 ， 找不到有用的解，电话数量已经迭代到几千万次了 。
这就要求，设置初始参数时不能过于随意 。比如这个案例下，单个电话成本，晚上比白天打电话贵50%，但接听电话的概率整体没有高那么多（95/80-1=19%），明显白天打电话更为划算，所以设置初始参数时，要把白天电话数设置成比晚上电话数更大 。
- END -
本篇是Excel建模已经组件化的内置功能 。下一篇将会从更加通用化的角度来讲解Excel分析建模 。
当然如果大家感兴趣的话可以继续深入学习，有任何问题或者还有啥想了解的，欢迎私信我 。
往期推荐
- Excel数据分析——数据输入
- Excel数据分析——数据处理
- Excel数据分析——数据透视表
- Excel数据分析——函数与公式（上）
- Excel数据分析——函数与公式（中）
- Excel数据分析——快捷键
- 数据分析必备五大思维（一）——结构化思维
- 数据分析必备五大思维（二）——分类思维
- 数据分析必备五大思维（三）——对比思维
- 数据分析必备五大思维（四）——统计思维
- 数据分析必备五大思维（五）——归因思维