ShowDocs
搜索文章Ctrl K

外观

← 返回Office列表
使用指南

Excel 进阶分析：数据模型、目标求解与方案比较 ​

本篇分成两个场景：多张表如何共同分析，以及在明确计算模型后如何比较方案。先确保基础公式和透视表可靠，再引入模型；功能是否可用要按版本、平台和许可确认。

图示跟练反推达到利润目标所需的数量

点击步骤切换图示，再到 Office 中跟做；每一步都可跳到下方详细说明。

Excel练习文件 · 界面示意
③ 单元格 B2:B6操作 / 设置示意

B2=100

B3=60

B4=1000

B5=1

详细输入与检查见本步正文 ↓
简化示意，非软件截图。以 Windows 中文界面为主，实际按钮位置与功能取决于版本；可在应用搜索框查找命令。

第 1 / 3 步：工作表区域 → 单元格 B2:B6

查看本步详细操作 →

什么时候使用数据模型 ​

只有一张小明细表时，普通透视表通常足够。订单、产品、部门分别维护且需要一起分析时，可用数据模型建立关系，避免把每张表的属性都重复复制到订单中。

例如订单表每行是一条订单明细，产品表每个产品编码一行。产品表的编码必须唯一，订单表的编码可以重复，建立产品到订单的一对多关系。连接列的数据类型应一致；缺失产品编码、重复维度键和错误粒度都会影响分析结果。

在支持的 Windows 版本中，可通过加载到数据模型、数据关系或 Power Pivot 管理界面完成设置。Power Pivot 在不同产品中的可用范围不同，尤其不要把 Windows 的完整功能流程直接等同于 Mac。微软功能与平台说明

计算列与度量值 ​

计算列为每行保存结果，例如数量乘单价；度量值在当前透视表的筛选上下文中计算，例如“某部门、某月、某状态”的合计。DAX 与单元格公式不是同一套使用环境，不应把度量值直接粘到普通单元格。

下面是度量值的概念写法；如果对话框把名称和公式分开，名称填冒号前的内容，公式从等号开始：

text
订单金额 := SUM(Sales[金额])
完成金额 := CALCULATE(SUM(Sales[金额]), Sales[状态] = "已完成")

第一个随当前筛选变化；第二个明确应用已完成状态条件，对同一状态字段已有筛选的行为要理解并验证。日期分析宜有连续的日期表并建立正确关系，不要只凭月份文字跨年度汇总。先用已知八条数据校验 5120、4060 和 3160，再处理真实大数据。

单变量目标求解 ​

假设单价为 100、单位成本为 60、固定成本为 1000，希望利润达到 2000。新表 B2 输入单价，B3 输入单位成本，B4 输入固定成本，B5 输入数量，B6 输入：

text
=(B2-B3)*B5-B4

在支持此功能的桌面版中，使用“数据 → 模拟分析 → 单变量求解”：目标单元格 B6，目标值 2000，可变单元格 B5。预期数量是 75，可手算 (2000+1000)/(100-60) 核对。

可变单元格必须影响目标公式。求解得到的数还要符合业务约束，例如数量必须整数、价格不可负；目标求解不负责完整约束优化，也不能保证所有模型都有唯一解。

比较多个方案 ​

先建立一张明确的方案表，列出价格、数量、收入、成本和利润。每个方案用同样公式，比较时只改变已声明的输入。可使用模拟分析中的数据表分析一个或两个变量，注意它与 Ctrl+T 建立的 Excel 表格不是同一个功能。

涉及多变量和约束优化时可考虑规划求解加载项：定义目标、可变区域、上下界和整数条件，再选择适合模型的方法。不能只看“找到解”的提示，还需核对约束、单位、可执行性和不同初始值是否产生不同结果。

练习与验收 ​

把单位成本从 60 改为 70，同样利润目标下数量应为 100；当单价等于单位成本时，增加数量无法覆盖固定成本，不能用旧答案继续解释。记录模型假设“单价和单位成本固定、不考虑容量限制”，说明这些假设变化后结果为什么失效。

图示跟做：反推达到利润目标所需的数量 ​

按上方图示切换三个关键步骤；下面保留完整操作和检查结果，便于跟做与回查。请先使用练习副本。

第 1 步：建立一眼能核对的模型 ​

定位：工作表区域 → 单元格 B2:B6。

在独立工作表 A2:A6 写单价、单位成本、固定成本、数量、利润，B2:B5 分别输入 100、60、1000、1。B6 输入 =(B2-B3)*B5-B4，初始利润应为 -960。先手动把数量改成 75，确认利润为 2000，然后改回 1。

第 2 步：让 Excel 调整数量 ​

定位：数据 → 单变量求解。

在支持的桌面版选择“数据 → 模拟分析 → 单变量求解”。目标单元格选择利润公式 B6，目标值填 2000，可变单元格选 B5。完成后检查结果接近 75，并代回公式复算；目标格不能是一个写死的 2000，必须依赖可变单元格。

第 3 步：改变假设再做敏感性检查 ​

定位：数据 → 单变量求解。

把单位成本 B3 改为 70，重新运行同一目标求解，数量应为 100。记录模型假设：单价、单位成本固定且未考虑产能限制。如果业务数量必须整数，应检查结果是否可执行；存在多变量、上下界或整数约束时，不能把一次目标求解当成完整优化方案。

没有得到预期结果时 ​

当单价等于单位成本时，每件毛利为零，增加数量不能覆盖固定成本。此时求解失败是模型约束导致的结果，不能强行沿用旧数量。

继续阅读