最新

Excel 怎么用模拟运算表做双变量试算

定价和销量同时变动时,用模拟运算表一次算出整张结果矩阵,不用反复改数重算。

定价、利率、成本这几个变量一起动,手工改一个算一次很费时间。Excel 的模拟运算表能把一组行值和一组列值代进同一个公式,一次算出整张结果矩阵,适合做敏感性试算。

入口在哪

  • 数据选项卡右侧的「模拟分析」下拉里,点「模拟运算表」。
  • 单变量用同一个入口,双变量要在对话框里同时填行输入单元格和列输入单元格。
  • Mac 版在数据选项卡的模拟分析组里,名称可能显示为模拟运算,找不到就在帮助里搜模拟运算表。

具体步骤

  1. 先在一张表里把公式搭好,让结果单元格只依赖两个输入单元格,比如价格和销量。
  2. 在结果单元格下方竖着填一组行变量值,右侧横着填一组列变量值,围出一块矩形空区。
  3. 选中「结果单元格加变量值区域」围成的整块矩形,左上角那个结果单元格也要框进去。
  4. 打开「数据 > 模拟分析 > 模拟运算表」,行输入单元格填横向那组值的来源格,列输入单元格填纵向那组值的来源格。
  5. 单变量试算只填一个输入单元格:把变量值竖排一列,选中结果单元格加这一列,另一个框留空。
  6. 确定后 Excel 用数组公式一次填满整块区域,之后逐个单元格手动改是改不动的。

几个坑

  • 两个输入单元格必须被结果公式直接引用,引用关系一旦断开,整片结果会是同一个数。
  • 整块是数组公式,想删只能整块选中后按 Delete,单改一格会提示不能更改数组的某一部分。
  • 输入单元格和运算表必须在同一个工作表,跨表或跨工作簿会直接报错。
  • 变量值动一格全表重算,几万格的矩阵会让文件明显变卡,做完可以复制成数值留档,原表另存备份再删。

怎么验证

随手挑矩阵里一个格子,把对应的行值和列值手工代回公式算一遍,两个数一致就说明输入单元格填对了;如果整片数字完全相同,先回去检查公式有没有真的引用那两个单元格。另外看一眼整块区域的列宽,数组公式不会自动撑开,数字显示成一串井号就说明列太窄。

常见问题

为什么确定后提示不能更改数组的某一部分

因为整块区域已经是数组公式,只能整块选中后统一修改或删除,想单独调一格要先整块删掉再重做。

能不能做三个变量的试算

模拟运算表只支持一到两个变量,三个以上要把其中一个变量拆成多张表,或者改用数据透视表配合计算字段分步看。

结果能不能直接拿去做图

可以,整块选中后插入折线图即可,但图表引用的是数组区域,原表删掉后图表会变空。

一句话总结:模拟运算表的关键是把公式和两个输入单元格理顺,一次算出整张敏感性矩阵。