Excel 技巧小剧场(215)《双变量敏感性分析,一键生成所有可能性》

小雨：“主管！我在做投资回报模型，想看不同利率和不同年限下，我的月供会怎么变化。难道要做一个几十行几十列的大表格，一个个填公式吗？这也太笨了吧！📊🤯”

主管：“用‘模拟运算表’呀！你只要建好一个公式，它就能自动帮你算出所有组合的结果，瞬间生成一张完整的分析表！🚀”

小雨：“模拟运算表？是那个‘假设分析’里的吗？它怎么能一下子算那么多？”

🎯 双变量敏感性分析，一键生成所有可能性！

• 告别手动复制公式，自动计算多种输入组合下的结果！

• 使用“模拟运算表”功能，基于一个公式快速生成二维变量分析矩阵

• 就像给你的模型装上“批量计算引擎”⚙️📈

• 设置两个变量，瞬间铺满整个结果表！

🚀 三步创建分析矩阵

1️⃣ 搭建基础模型框架​ 🏗️：

在一个单元格（比如B5）写好你的核心计算公式（如=PMT(B2/12, B3 * 12, B1)，计算月供）。其中B2（年利率）、B3（年限）是你要分析的变量。

2️⃣ 准备变量值列表​ 📋：

• 在某一列（如A6:A15）列出第一个变量（如“年限”）的所有可能值（5,10,15…30年）。

• 在某一行（如B5:F5）列出第二个变量（如“年利率”）的所有可能值（3%, 3.5%, 4%…5%）。

• 关键：把公式单元格（B5）放在这两个列表的左上角交汇处。

3️⃣ 生成运算表​ ⚡：

• 选中包含变量列表和公式单元格的整个区域（A5:F15）→ 点击【数据】选项卡 →【预测】组 →【假设分析】→【数据表】。

• “输入引用行的单元格”：选择公式中代表行变量（利率）的原始单元格（B2）。

• “输入引用列的单元格”：选择公式中代表列变量（年限）的原始单元格（B3）。

• 点击【确定】，区域会自动填充所有组合的计算结果！

⚠️ 使用时要注意这些

注意点一：变量列表和公式位置摆错了​ 🚫

现象：运算表生成后全是同一个值，或者全是错误值。

正确做法：

公式单元格必须位于变量列表区域的左上角。行变量值放在公式的右侧同一行，列变量值放在公式的下方同一列。区域选择必须包含公式和所有变量值。

注意点二：单元格引用选错了​ 🔄

现象：结果不对，感觉变量没有被正确代入。

正确做法：

在数据表对话框中，“输入引用行的单元格”应选择公式中代表行标题变量的原始单元格；“输入引用列的单元格”应选择公式中代表列标题变量的原始单元格。务必分清行、列变量在公式中的对应关系。

📜 这个“批量计算器”的好处

• 效率爆炸​ ⚡：只需一个公式，即可瞬间计算多个变量、多种组合下的所有结果，无需手动复制或修改公式。

• 布局清晰​ 📊：结果以清晰的二维表格形式呈现，行和列分别是两个变量，交叉处是计算结果，一目了然。

• 动态更新​ 🔄：更改基础公式或变量列表中的任意值，整个运算表结果会自动重算。

• 专业分析​ 📈：是进行敏感性分析、情景模拟的利器，能直观展示不同变量组合对结果的影响程度。

🛍️ 学习资料推荐

与其漫无目的地刷短视频，不如翻开这本《知识宝典》📖——每天随手翻几页，今天学个快捷键，明天练个函数公式，不知不觉就能把Excel玩得溜溜的！

📖 往期内容精选