Excel 数据分析、Power Query 与 DAX 教程

第四编 数据分析、Power Query、Power Pivot 与 DAX

0. 数据分析模块导学

适用范围：Excel 2019、Excel 2021、Microsoft 365 Windows 版。Mac 版的 Power Query、Power Pivot 与规划求解功能可能不完整；菜单名称也可能略有差异。本文按钮路径以 Microsoft 365 Windows 中文界面为准。示例数据可粘贴到工作表；M 代码放入 Power Query 编辑器，DAX 放入数据模型的度量值编辑区，不能把它们直接当作单元格公式。先按本节说明创建表名、字段和关系。建议先把原始数据转换为“表格”（Ctrl+T），并给表格、查询、度量值使用有意义的名称。

1. 数据透视表：从明细到可交互汇总

1.1 学习目标

完成本节后，你应能：

1. 判断什么样的数据适合建立数据透视表；

2. 正确设置行、列、值和筛选区域；

3. 使用分组、排序、筛选、切片器、时间线和“值显示方式”；

4. 创建并联动数据透视图；

5. 理解并稳定使用 GETPIVOTDATA；

6. 排查无法刷新、字段缺失、数字被计数、日期不能分组等问题。

1.2 示例数据

将下表粘贴到 A1，按 Ctrl+T 转为表格，勾选“表包含标题”，在“表格设计”中把表名改为 Sales。

在 Sales 右侧新增三列：

• 销售额：=[@数量]*[@单价]
• 成本额：=[@数量]*[@成本单价]
• 毛利：=[@销售额]-[@成本额]

结果校验：第一行销售额为 2080，成本额为 1360，毛利为 720。

1.3 创建数据透视表

1. 单击 Sales 中任意单元格。

2. 选择“插入 → 数据透视表 → 从表格或区域”。

3. 确认表/区域为 Sales。

4. 选择“新工作表”，单击“确定”。

5. 将新工作表重命名为“销售透视”。

6. 在“数据透视表字段”窗格中拖动：

• 区域 → 行；
• 类别 → 列；
• 销售额 → 值；
• 渠道 → 筛选。

7. 在值区域中单击“求和项:销售额 → 值字段设置 → 数字格式”，选“数值”、千位分隔符、0 位小数。

预期结果： 行显示华东、华南、华北，列显示外设、显示设备、配件及总计；交叉单元格是相应销售额总和。完整数据总销售额应为 59095。

不要只在普通“设置单元格格式”中改透视表数字。刷新后普通格式可能丢失；应从“值字段设置 → 数字格式”进入。

1.4 四个字段区域的逻辑

• 行：主要分类维度，从上到下展开，如区域、城市、销售员。
• 列：横向比较维度。列项目过多会导致表格过宽。
• 值：被聚合的指标，如销售额、数量、订单数。
• 筛选：控制整张透视表，但一次只能直观看到当前选择。需要更直观交互时使用切片器。

字段顺序代表层级。例如行区域中先放“区域”再放“城市”，上海会归在华东下；反过来则先显示城市，再显示区域，通常不符合阅读习惯。

1.5 值字段设置与“值显示方式”

把 销售额 连续拖入“值”区域四次，分别设置：

1. 销售额：汇总方式为“求和”。

2. 区域占比：值显示方式选“父行汇总的百分比”或“列汇总的百分比”，按分析目的选择。

3. 总额占比：选“总计的百分比”。

4. 销售额排名：选“降序排列”，基本字段选“区域”。

常见显示方式：

• % 总计：结构占比；
• % 行汇总：某行内部各列构成；
• % 列汇总：某列内部各行构成；
• 差异/差异百分比：与上一期或指定基准比较；
• 按某字段汇总：累计值；
• 指数：某交叉点相对于行列总体的集中程度。

易错点： “汇总值字段方式”决定先怎样聚合，“值显示方式”决定聚合结果如何二次表达，两者不是一回事。

1.6 日期分组、数字分组和取消分组

创建另一张透视表：日期放行，销售额放值。

1. 右击任一日期 → “组合”。

2. 勾选“年”和“月”，取消“季度”（若不需要）。

3. 单击确定。

Excel 会生成“年”“月”层级。跨多年数据必须同时选“年”，否则所有年份的一月会合并。

数字字段也可分组。例如把单价放行，右击 → 组合，起始值 0、终止值 2000、步长 500，即可形成价格带。

日期不能分组排错：

1. 原字段含空白、错误值或文本日期；

2. 用 =ISNUMBER(A2) 检查真正日期，TRUE 才是日期序列值；

3. 文本日期可用“数据 → 分列 → 直接完成”，或 =DATEVALUE(A2) 转换；

4. 修复源数据后刷新；必要时删除旧透视表重新创建；

5. 数据模型透视表的日期分组行为与普通透视表不同，宜建立日期表。

1.7 排序、标签筛选和值筛选

• 普通排序：右击销售额 → 排序 → 从大到小。
• 标签筛选：区域字段下拉 → 标签筛选，例如“开头是‘华’”。
• 值筛选：区域字段下拉 → 值筛选 → 前 10 项，可改成“前 2 项，按销售额之和”。
• 多条件筛选：在“数据透视表分析 → 选项 → 汇总和筛选”中允许每字段多个筛选器，再组合标签和值筛选。

1.8 布局、重复标签、空白与总计

在“设计”选项卡中：

1. “报表布局 → 以表格形式显示”；

2. “报表布局 → 重复所有项目标签”；

3. “分类汇总 → 不显示分类汇总”；

4. “总计 → 对行和列启用”或根据报告需要关闭；

5. “空白行”通常选“删除每个项目后的空白行”。

表格形式适合后续复制、导出和查找；紧凑形式适合屏幕浏览但不利于机器处理。

如需把空单元格显示为 0：右击透视表 → 数据透视表选项 → 布局和格式 → 勾选“对于空单元格，显示”，输入 0。不要把“没有交易”误读成真实零值；业务报告可显示短横线。

1.9 切片器与时间线

1. 选中透视表 → “数据透视表分析 → 插入切片器”。

2. 勾选区域、渠道、类别。

3. 单击切片器按钮筛选；按住 Ctrl 可多选，也可点击右上角“多选”。

4. 清除筛选使用带红叉的漏斗图标。

5. 日期字段可用“插入时间线”，按年、季度、月、日拖选区间。

让一个切片器控制多张透视表：选中切片器 → “切片器 → 报表连接” → 勾选目标透视表。前提是这些透视表共享同一透视缓存或同一数据模型；否则目标表不会出现在连接列表。

1.10 数据透视图

1. 选中区域—销售额透视表。

2. “数据透视表分析 → 数据透视图”。

3. 选择“簇状柱形图”。

4. 删除多余网格线，标题改为“各区域销售额”。

5. 按销售额降序排列源透视表，图表顺序同步变化。

6. 使用切片器筛选，图表同步更新。

数据透视图适合交互分析，但有局限：字段按钮可能遮挡图面、部分组合图设置受限、它依赖透视表结构。汇报用静态图可从透视结果引用到普通区域，再制作普通图表。

1.11 GETPIVOTDATA：稳定提取透视结果

在透视表旁输入 =，再单击某个值单元格，Excel 常自动生成：

=GETPIVOTDATA("销售额",$A$3,"区域","华东","类别","外设")

语法：

GETPIVOTDATA(数据字段, 数据透视表中的任意单元格, 字段1, 项目1, ...)

其优势不是“短”，而是不依赖值所在坐标。透视表扩展、排序或布局变化后，公式仍按字段语义取数。

动态报表可让 H2 存区域、I2 存类别：

=IFERROR(GETPIVOTDATA("销售额",$A$3,"区域",H2,"类别",I2),0)

取总销售额：

=GETPIVOTDATA("销售额",$A$3)

取华东全部类别：

=GETPIVOTDATA("销售额",$A$3,"区域","华东")

常见故障：

• #REF!：字段名或项目名不存在、当前筛选隐藏了项目、引用单元格不在目标透视表中。
• 名称看似一致仍报错：检查前后空格、全角半角、日期项目的真实值。
• 复制公式后取错透视表：锁定第二参数，如 $A$3。
• 不想自动生成：数据透视表分析 → “生成 GETPIVOTDATA”关闭；但正式仪表板通常建议保留。
• 日期项目可用 DATE(2026,1,1)，不要依赖地区格式文本。

1.12 刷新、数据源与常见故障清单

刷新： 右击 → 刷新；全部对象用“数据 → 全部刷新”。在“数据透视表选项 → 数据”可设置打开文件时刷新。

新增行未进入： 如果源是固定区域 $A$1:$L$100，第 101 行不会纳入。解决：源数据转为 Excel 表格，或更改数据源。

数字显示为‘计数项’： 源列含文本、空字符串或混合类型。清洗后把值字段移出再放回，选择“求和”。仅改显示格式无效。

旧项目仍在筛选器： 数据透视表选项 → 数据 → 每个字段保留的项目数设为“无”，刷新。

刷新后列宽变化： 数据透视表选项 → 布局和格式 → 取消“更新时自动调整列宽”，勾选“更新时保留单元格格式”。

看不到字段列表： 数据透视表分析 → 字段列表；或右击 → 显示字段列表。

同一名称重复列： 源数据标题必须唯一且非空。

计算字段结果异常： 传统计算字段按汇总后的逻辑处理，复杂比率宜用数据模型度量值，例如 DIVIDE([毛利],[销售额])。

1.13 练习与答案

练习 1： 用示例数据创建按销售员显示销售额、毛利和毛利率的报表，支持区域切片。

练习 2： 显示每个月销售额及环比增长率。

练习 3： 在普通单元格中按 K2 区域和 L2 类别动态取销售额。

2. 图表选择与信息表达

2.1 先确定问题，再选择图表

信息表达原则：

• 一个图回答一个主要问题。
• 标题应写结论或比较对象。
• 避免 3D、阴影、花哨渐变。
• 减少不必要网格线。
• 颜色用于强调而不是装饰。
• 数据标签只标关键值。

2.2 坐标轴的正确设置

柱形图通常从零开始。 截断纵轴会夸大差异。例如 98 与 100 若纵轴从 97 开始，视觉差异会被放大数十倍。

设置步骤：右击纵轴 → 设置坐标轴格式：

1. 边界最小值通常设 0；

2. 最大值略高于数据最大值；

3. 主要单位取便于阅读的整数；

4. 数字格式可用 0.0,"千" 或 0.0,,"百万"；

5. 不要把单位只写在数据标签里，最好在轴标题注明。

折线图可在有充分理由时不从零开始，但应明确刻度并避免误导。日期横轴若间隔不规则，应选择“日期坐标轴”而非“文本坐标轴”。

2.3 双轴图的风险与替代方案

双轴可让单位不同的两组数据共图，如销售额与毛利率，但两个轴的上下限可被任意调整，容易制造“高度相关”的假象。

创建方法：

1. 选择月份、销售额、毛利率；

2. 插入 → 组合图；

3. 销售额设簇状柱形，毛利率设折线；

4. 为毛利率勾选“次坐标轴”；

5. 左轴标“销售额（元）”，右轴标“毛利率”；右轴固定为合理范围，如 0%–50%。

安全规范：

• 两侧轴必须清楚标单位；
• 不随意微调范围来制造交点；
• 颜色与轴标题对应；
• 若读者可能误判，改为上下对齐的两张图或把两指标标准化为指数（基期=100）。

2.4 添加目标线

示例数据：

1. 选中三列数据 → 插入簇状柱形图。

2. 图表设计 → 更改图表类型 → 组合图。

3. 销售额设柱形，目标设折线。

4. 目标线设橙色虚线，去掉标记。

5. 如单位相同，不要使用次坐标轴。

动态目标线可在目标列引用单一参数单元格，例如 =$H$2，修改 H2 后整条线同步变化。

2.5 帕累托图：找出关键少数

示例：

步骤：

1. 按“次数”降序排序。

2. C2 输入累计次数：=SUM($B$2:B2)，向下填充。

3. D2 输入累计占比：=C2/SUM($B$2:$B$6)，设百分比并向下填充。

4. 选择缺陷类型、次数、累计占比。

5. 插入组合图：次数为柱形；累计占比为折线并使用次坐标轴。

6. 右轴设 0%–100%，主要单位 20%。

7. 可再添加一列全为 80%，作为“80%参考线”。

结果：前三类累计为 87%，说明优先处理划伤、色差和尺寸偏差可覆盖大部分缺陷。帕累托原则不是机械地断言一定为 80/20，而是用累计贡献识别优先级。

Microsoft 365 也可“插入 → 统计图 → 帕累托图”，但手工组合图更便于控制累计线和阈值线。

2.6 甘特图：用堆积条形图表达计划

示例：

步骤：

1. 选择任务、开始日期、工期。

2. 插入 → 条形图 → 堆积条形图。

3. 图中“开始日期”系列设置为“无填充、无线条”，仅保留其占位作用。

4. 右击纵轴 → 勾选“逆序类别”，让第一项显示在顶部。

5. 横轴最小值设为项目开始日前后对应的日期序列，避免左侧巨大空白。

6. 横轴数字格式设为 m/d。

7. 调整间隙宽度到 30%–60%。

若需要进度，可新增“已完成天数”和“剩余天数”，构成三层堆积；开始日期仍无填充，已完成用深色，剩余用浅色。里程碑可用散点系列叠加，但设置复杂时可用菱形图标人工标注。

2.7 迷你图

示例：B1:G1 为 1–6 月，A2:A4 为产品，B2:G4 为月销售额。

1. 选中 H2:H4。

2. 插入 → 迷你图 → 折线。

3. 数据范围选 B2:G4，位置范围为 H2:H4。

4. 在“迷你图”选项卡勾选高点、低点、负点。

5. “坐标轴 → 所有迷你图的纵坐标轴最小值/最大值相同”，确保跨行可比。

若每行轴自动缩放，一条 100–110 的波动与 100–1000 的波动可能看起来同样剧烈。比较时必须统一轴；只看各自行内趋势时才可独立缩放。

2.8 图表常见故障

• 横轴顺序错误： 月份是文本，按字典顺序排列；改用真实日期并格式化为 m月。
• 空值被画成 0： 图表设计 → 选择数据 → 隐藏的单元格和空单元格，选择“间隙”；公式返回 "" 有时仍被当作文本，可用 NA() 形成断点。
• 新增数据不进入图表： 源区域转成 Excel 表格，图表引用表格列。
• 数据标签重叠： 只标最后一个值、最大值或异常点；放大图表不是唯一办法。
• 颜色过多： 普通系列用中性灰，重点系列用一种强调色。
• 柱形图类别倒序： 条形图常默认首项在底部，设置纵轴“逆序类别”。
• 图表筛选后仍显示隐藏行： “选择数据 → 隐藏的单元格和空单元格”检查“显示隐藏行列中的数据”。

2.9 练习与答案

练习 1： 有 12 个月销售额、目标额和毛利率，应如何选图？

练习 2： 缺陷次数为 40、25、15、10、6、4，求前三项累计占比。

练习 3： 甘特图为什么要把开始日期系列设为无填充？

3. Power Query 完整教程：可刷新数据清洗

3.1 Power Query 的定位

Power Query 是“获取与转换数据”工具，核心思想是把导入、清洗、合并等操作记录为可重复步骤。它不等同于公式：公式通常逐单元格计算，Power Query 对整张数据集执行转换；源文件更新后只需刷新，无须重复人工清洗。

入口通常在“数据 → 获取和转换数据”。编辑器主要区域：

• 左侧“查询”窗格：所有查询；
• 中间数据预览：通常只用于预览，不代表完整数据已加载；
• 右侧“查询设置”：名称和“应用的步骤”；
• 顶部功能区：主页、转换、添加列、视图、工具；
• 公式栏：显示当前步骤的 M 表达式，可在“视图 → 公式栏”开启；
• 高级编辑器：查看完整 let ... in ... 代码。

3.2 示例脏数据

保存为工作表“原始订单”，转为表格并命名 RawOrders：

目标：去空格、统一大小写和类型、删除重复、处理错误，并计算金额。

3.3 从表格进入编辑器并理解步骤

1. 单击 RawOrders 中任意单元格。

2. 数据 → 从表格/区域。

3. 编辑器打开后把查询名改为 qOrdersClean。

4. 观察自动生成的“源”“提升的标题”“更改的类型”等步骤。

5. 单击任一步骤，预览会回到该状态；后续步骤不会消失，除非你删除或修改前置结构导致后续引用失效。

步骤名称应表达业务含义，如“去除客户空格”“筛选有效日期”，不要保留十几个“添加的自定义1”。右击步骤可重命名。

3.4 数据类型：最重要的基础

列标题左侧图标代表类型：ABC 文本、123 整数、1.2 小数、日历日期、日期时间、TRUE/FALSE 逻辑值。

类型不仅影响显示，还决定可用操作、比较规则、聚合方式和加载结果。推荐顺序：先清洗字符和区域设置，再最终指定类型。

设置：选中列 → 主页/转换 → 数据类型。

• 订单号即使全是数字，也常应设为文本，以保留前导零并避免算术。
• 数量设整数；单价和金额设固定小数或小数；日期设日期。
• 身份证号、手机号、邮编必须用文本。

使用区域设置： 如果 31/12/2026 被错误解释，右击列 → 更改类型 → 使用区域设置 → 日期 → 选择对应文化，如“英语（英国）”。金额中逗号、小数点规则也受区域设置影响。

3.5 文本清洗

多选客户、状态列，使用：

1. 转换 → 格式 → 清除：去除不可打印字符；

2. 转换 → 格式 → 修剪：去除首尾空格；

3. 产品代码 → 格式 → 大写；

4. 订单号 → 格式 → 修剪。

“修剪”不会自动删除文本中间的多个空格。可用替换值把双空格换成单空格，重复执行，或用 M：

Text.Combine(List.Select(Text.SplitAny([客户], " "), each _ <> ""), " ")

替换值区分文本与类型。例如把文本 null 替换为空值，与真正的 null 不同。

3.6 拆分、合并、提取与条件列

• 拆分：选择列 → 拆分列 → 按分隔符、字符数、位置或从数字到非数字。
• 合并列：选择多列 → 转换 → 合并列，指定分隔符；原列会被替换。
• 提取：首字符、末字符、范围、分隔符之前/之后。
• 条件列：添加列 → 条件列，无需手写 M。
• 自定义列：添加列 → 自定义列，可使用 M 表达式。

为示例添加金额：添加列 → 自定义列，公式：

[数量] * [单价]

命名“金额”，再设为固定小数。如果数量为空，结果通常为 null；是否将空数量视为 0 必须依据业务定义，不应默默替换。

3.7 处理中文数字、货币符号与错误

示例的数量“一”无法直接转整数。可先“替换值”：一→1、二→2等；真正业务应使用映射表并合并，避免大量硬编码。

单价列含 ¥ 和千位逗号：

1. 类型暂设文本；

2. 替换 ¥ 为空；

3. 替换 , 为空（需确认源小数规则）；

4. 修剪；

5. 改为固定小数。

日期“错误日期”转换后产生 Error。处理方法：

• 主页 → 删除行 → 删除错误：适合确认错误行无业务价值；
• 转换 → 替换错误：例如替换为 null；
• 保留错误：建立质量检查查询；
• 用 try ... otherwise：

try Date.From([订单日期]) otherwise null

更可审计的写法：

let r = try Date.From([订单日期])
in if r[HasError] then "日期错误" else "有效"

不要一开始就删除所有错误。错误可能揭示源系统字段漂移或新的编码规则。

3.8 空值、空字符串与 null

Power Query 的 null 表示缺失，不等于 0，也不等于空文本 ""。筛选器中的 (null) 与空白文本可能是不同项。

M 中判断 null 推荐：

if [数量] = null then 0 else [数量]

文本清洗后可把空字符串转为 null，便于统一统计。注意，数据库中的 null 与 Excel 空单元格加载后的表现可能不同。

3.9 删除重复：先明确“唯一键”

示例中订单 1003 完全重复：

1. 选择订单号列；

2. 主页 → 删除行 → 删除重复项。

只按订单号去重可能删除有效明细，因为一个订单可以包含多个商品。若唯一键是“订单号+产品代码”，请同时选择这两列。Power Query 的 Table.Distinct 不保证保留哪一条重复记录，单独排序后去重也不保证保留最新记录。可按业务键分组，在每组内按更新时间降序、唯一记录 ID 升序排序后取第一条；若使用 Table.Buffer 配合去重，应评估内存成本并验证保留结果。

3.10 合并查询（Merge）：类似数据库 Join

准备产品表 Products：

从表格创建查询 qProducts，仅创建连接。然后：

1. 打开 qOrdersClean。

2. 主页 → 合并查询。

3. 上表选择产品代码，下表选 qProducts 的产品代码。

4. 联接种类选“左外部（第一个中的所有行，第二个中的匹配行）”。

5. 确定后出现嵌套表列。

6. 单击列标题右侧展开按钮，选择产品名称、类别、标准成本；取消“使用原始列名作为前缀”。

常见联接：

• 左外部：保留所有订单，补充维表属性，最常用；
• 内部：只保留两边匹配；
• 完全外部：保留两边所有记录，用于对账；
• 左反：只保留左表未匹配记录，非常适合找缺失产品代码；
• 左半：只保留左表能匹配记录。

匹配不上排错： 两边类型不同、前后空格、大小写、全角半角、前导零、不可见字符。先在两边统一类型、修剪、清除和大小写。

行数突然增加： 右表键不唯一，形成一对多匹配。先对维表键做重复检查；若业务上应唯一，修复维表，不要用“删除重复”掩盖冲突。

3.11 追加查询（Append）：纵向堆叠

假设 qJan 与 qFeb 列结构相同：

1. 主页 → 追加查询 → 将查询追加为新查询。

2. 选“三个或更多表”或“两表”。

3. 加入月份查询，命名 qOrdersAll。

追加按列名对齐，不按列的位置。某表缺少的列填 null；列名存在一个空格差异会生成两列。追加前应统一列名和类型。

合并是横向加列，追加是纵向加行，这是最常混淆的概念。

3.12 文件夹批量合并

适用场景：每月收到结构相同的销售 CSV/XLSX，统一放在一个文件夹。

1. 数据 → 获取数据 → 自文件 → 从文件夹。

2. 选择文件夹，预览文件清单。

3. 先筛选 Extension，排除临时文件 ~$、隐藏文件和无关格式。

4. 单击“合并并转换数据”。

5. 在“合并文件”窗口选择正确工作表、表格或 CSV 示例对象。

6. Power Query 自动生成“示例文件”“转换示例文件”“调用自定义函数”等辅助查询。

7. 在“转换示例文件”中做清洗，步骤会应用到每个文件。

8. 保留源文件名列，便于追踪来源。

9. 检查最终行数、列名与类型，再加载。

新增文件刷新： 把新文件放入同一文件夹后“全部刷新”，无需重建查询。

常见故障：

• 某文件列名变化导致“找不到列”：先定义允许的列或用 MissingField.UseNull。
• 某文件多了标题说明行：在示例转换中删除顶部行，再提升标题。
• 工作表名称每月变化：不要硬编码固定名称，可筛选 Kind=Sheet 后按规则选首个或指定模式；但需防止读错。
• 文件正被打开生成 ~$ 临时文件：在文件名列筛除“以 ~$ 开头”。
• 文件夹包含历史输出文件，刷新时把输出再次读入：输入与输出目录必须分开。
• 性能差：尽早筛选文件、删除不需要列；避免对每个文件做昂贵的逐行函数。

3.13 参数化查询

参数可把路径、日期阈值、环境名称从代码中分离。

创建参数：主页 → 管理参数 → 新建参数：

• 名称：pStartDate
• 类型：日期
• 建议值：任意值
• 当前值：2026/1/1

筛选订单日期时选择“大于等于参数 pStartDate”。修改参数后刷新即可改变范围。

文件路径参数可在高级编辑器中使用：

Source = Folder.Files(pFolderPath)

也可从 Excel 命名单元格读取参数：创建单单元格表 tblParameter，查询后钻取值，再在其他查询中引用。这样普通用户无需打开编辑器即可修改。

参数不是安全机制，不能用来隐藏密码；凭据应由数据源设置和组织安全策略管理。

3.14 隐私级别与 Formula.Firewall

Power Query 的隐私级别用于防止一个数据源的信息意外传递到另一个数据源：

• 公共：可与其他源共享；
• 组织：可在组织源间共享；
• 专用：隔离程度最高。

当查询组合多个源时可能出现 Formula.Firewall。正确处理：

1. 数据 → 获取数据 → 数据源设置；

2. 编辑权限，为各源设置合理且一致的隐私级别；

3. 将复杂查询拆成“提取层”和“组合层”；

4. 不建议为了消除报错而盲目勾选“忽略隐私级别”，尤其涉及敏感数据。

动态构造数据源路径还可能触发凭据或刷新限制。生产环境应尽量使用稳定根路径与参数。

3.15 加载选项与数据模型

编辑器中“主页 → 关闭并加载到…”可选：

• 表：加载到工作表；
• 仅创建连接：中间查询推荐，减少工作簿膨胀；
• 添加到数据模型：供 Power Pivot、DAX 和多表透视使用；
• 同时加载表与数据模型：方便查看但占用更多空间。

分层命名建议：

• stg_：原始暂存，只连接；
• dim_：维表；
• fact_：事实表；
• out_：最终输出。

不要把每一个辅助查询都加载到工作表。右击查询 → “加载到…”可修改。

3.16 刷新与刷新策略

• 单查询：右击查询结果表 → 刷新；
• 全部：数据 → 全部刷新；
• 查询属性：可设置打开文件时刷新、每隔若干分钟刷新、启用后台刷新；
• 有依赖关系时，Power Query 会按依赖图执行，但外部连接的并发行为受版本与源限制。

刷新失败先检查：文件是否移动、网络盘是否可用、凭据是否过期、列名是否改变、数据类型是否出现新值、隐私级别是否冲突。

后台刷新虽方便，但宏或后续公式可能在刷新完成前运行。需严格顺序时关闭后台刷新，或由 VBA 等待刷新完成。

3.17 性能与查询折叠

对数据库数据源，Power Query 可把筛选、选列、分组等步骤翻译成源端查询，这称为“查询折叠”。右击步骤若“查看本机查询”可用，说明至少该处可折叠。

优化原则：

1. 尽早筛选行、删除列；

2. 可折叠操作尽量放在不可折叠操作前；

3. 避免逐行调用自定义函数；

4. 不要无理由使用 Table.Buffer，它可能占内存并阻断折叠；

5. CSV/Excel 文件本身通常没有数据库式折叠；

6. 预览慢不一定代表最终加载同样慢，但应检查步骤复杂度。

3.18 M 语言基础

典型查询：

let
    Source = Excel.CurrentWorkbook(){[Name="RawOrders"]}[Content],
    Trimmed = Table.TransformColumns(Source, {
        {"客户", Text.Trim, type text},
        {"状态", Text.Trim, type text},
        {"产品代码", Text.Upper, type text}
    }),
    Typed = Table.TransformColumnTypes(Trimmed, {
        {"订单号", type text},
        {"订单日期", type date},
        {"数量", Int64.Type},
        {"单价", Currency.Type}
    }, "zh-CN"),
    AddedAmount = Table.AddColumn(Typed, "金额", each [数量] * [单价], Currency.Type),
    Filtered = Table.SelectRows(AddedAmount, each [状态] <> "取消")
in
    Filtered

核心规则：

• let 定义一系列名称与表达式；in 指定最终返回哪个步骤；
• M 区分大小写；
• 每一步引用前一步，步骤名含空格时写 #"更改的类型"；
• each 是简写匿名函数，each [数量]*[单价] 等价于 (row)=>row[数量]*row[单价]；
• 列表用 {}，记录用 []，表是行记录的集合；
• 注释：单行 //，多行 /* ... */。

常用函数：

Table.SelectRows(t, each [金额] > 1000)
Table.SelectColumns(t, {"订单号","金额"})
Table.RenameColumns(t, {{"金额","销售额"}})
Table.Group(t, {"地区"}, {{"销售额", each List.Sum([金额]), type number}})
Table.NestedJoin(t, {"产品代码"}, p, {"产品代码"}, "产品", JoinKind.LeftOuter)
Table.Combine({Jan, Feb, Mar})
Text.Trim([客户])
Date.StartOfMonth([订单日期])
try Number.FromText([数量]) otherwise null

3.19 Power Query 完整实战结果

对示例脏数据执行：修剪文本、产品代码大写、中文一替换为 1、单价去符号与逗号、类型转换、订单号去重、错误日期替换 null、合并产品表、添加金额。

• 预期保留订单 1001–1005 共 5 行。
• 1003 重复被删除一行。1001 金额 2598。
• 1002 金额 2697。
• 1003 金额 1299。
• 1004 数量缺失导致金额 null。
• 1005 金额 4495、日期 null。未匹配产品代码数应为 0。

质量检查建议额外输出：总行数、唯一订单数、空日期数、空数量数、未匹配产品数、错误行数。清洗不是“看起来整齐”，而是可量化地满足规则。

3.20 练习与答案

练习 1： 每月文件列顺序不同，但列名相同，文件夹追加是否会错位？

练习 2： 找出订单表中没有产品主数据的记录。

练习 3： 为什么金额列出现 Error，而不是 null？

练习 4： 写 M 代码，仅保留 2026 年且金额大于 1000 的记录。

参考答案：

Table.SelectRows(Source, each Date.Year([订单日期]) = 2026 and [金额] > 1000)

4. Power Pivot 数据模型与 DAX

4.1 为什么需要数据模型

当分析涉及订单事实表、产品表、客户表、日期表等多个表时，不应使用大量 VLOOKUP 把所有属性复制到一张宽表。数据模型通过关系连接表，减少重复，支持高压缩与 DAX 度量值。

若看不到 Power Pivot：文件 → 选项 → 加载项 → 管理“COM 加载项”→ 转到 → 勾选 Microsoft Power Pivot for Excel。部分版本不提供该功能。

4.2 星型模型

建议结构：

• FactSales：订单明细，含日期键、产品键、客户键、数量、销售额、成本额；
• DimDate：每日期一行；
• DimProduct：每产品一行；
• DimCustomer：每客户一行；
• DimRegion：必要时单独维表。

维表位于“一”端，键必须唯一；事实表位于“多”端。筛选通常从维表流向事实表。避免把事实表彼此直接多对多连接，也避免无控制的双向筛选造成歧义。

4.3 建立关系

1. 把清洗后的查询加载到数据模型。

2. Power Pivot → 管理 → 图示视图。

3. 从 DimProduct[ProductKey] 拖到 FactSales[ProductKey]。

4. 从 DimDate[Date] 拖到 FactSales[OrderDate]。

5. 检查关系方向、基数和活动状态。

也可在“数据 → 关系”创建。

建立失败排错：

• 两端数据类型不同；
• “一”端存在重复值或空白键；
• 存在循环或多条活动路径；
• 日期一端不是连续唯一日期；
• 文本键含空格或不可见字符。

空白成员 (Blank) 常表示事实表有键在维表中找不到。应做反联接检查，不要只在透视表隐藏空白。

4.4 日期表

日期表至少含连续日期，无断档，从事实最小日期覆盖到最大日期。可在 Power Pivot 中创建：

DimDate =
ADDCOLUMNS(
    CALENDAR(DATE(2025,1,1), DATE(2027,12,31)),
    "Year", YEAR([Date]),
    "MonthNo", MONTH([Date]),
    "Month", FORMAT([Date], "yyyy-mm"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "WeekdayNo", WEEKDAY([Date], 2),
    "Weekday", FORMAT([Date], "aaa")
)

然后“标记为日期表”，选择 Date 列。将 Month 按 MonthNo 排序时，若跨年需使用年月排序键，如 YEAR([Date])*100+MONTH([Date])。

4.5 计算列 vs 度量值

计算列： 对表中每一行计算，结果存储在模型中，可作为切片、行或列字段。

FactSales[LineAmount] = FactSales[Quantity] * FactSales[UnitPrice]

度量值： 在当前筛选上下文中计算，不逐行永久存储，主要放值区域。

[Sales Amount] = SUM(FactSales[LineAmount])

选择原则：

• 需要逐行分类、关系键、排序列 → 计算列；
• 需要聚合、比率、KPI、随筛选变化 → 度量值；
• 能用度量值就不要为每个汇总结果建计算列，以减小模型。

“毛利率”必须是 总毛利/总销售额，不能简单平均每行毛利率：

[Gross Profit] = [Sales Amount] - [Cost Amount]
[Gross Margin %] = DIVIDE([Gross Profit], [Sales Amount])

4.6 行上下文与筛选上下文

• 行上下文：当前正在处理哪一行，常见于计算列和 X 迭代函数。
• 筛选上下文：由透视表行列、切片器、筛选器及 CALCULATE 形成。

理解上下文是 DAX 的核心。同一个 [Sales Amount] 在华东行只算华东，在 2026 年列只算 2026 年，在总计处算所有当前可见数据。

4.7 SUMX

若事实表没有 LineAmount 列，可直接度量：

[Sales Amount] =
SUMX(
    FactSales,
    FactSales[Quantity] * FactSales[UnitPrice]
)

SUM 只能对一列求和；SUMX 先遍历表，逐行求表达式，再累加。其他迭代函数包括 AVERAGEX、MINX、MAXX、COUNTX。

性能上，简单列求和优先 SUM；确需逐行表达式才用 SUMX。避免在巨大事实表上迭代复杂过滤。

4.8 CALCULATE

CALCULATE 重新定义筛选上下文：

[Online Sales] =
CALCULATE(
    [Sales Amount],
    FactSales[Channel] = "线上"
)

忽略产品筛选计算总额：

[All Product Sales] =
CALCULATE([Sales Amount], REMOVEFILTERS(DimProduct))

产品份额：

[Product Share %] =
DIVIDE(
    [Sales Amount],
    CALCULATE([Sales Amount], REMOVEFILTERS(DimProduct))
)

保留外部多选并在其中叠加条件，可研究 KEEPFILTERS。需要精细表过滤时：

[Large Order Sales] =
CALCULATE(
    [Sales Amount],
    FILTER(FactSales, FactSales[Quantity] >= 10)
)

布尔筛选能表达时优先布尔筛选，通常更清晰高效。

4.9 RELATED 与 RELATEDTABLE

在事实表计算列中，从“一”端维表取属性：

FactSales[StandardCost] = RELATED(DimProduct[StandardCost])

RELATED 依赖已建立关系，常从多端取一端值。反方向如在产品表计算相关销售行数：

DimProduct[SalesRows] = COUNTROWS(RELATEDTABLE(FactSales))

若只是为了在报表中按类别切片，不必把类别 RELATED 到事实表；直接使用维表类别更符合模型设计。

4.10 常用基础度量值

[Sales Amount] = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])
[Quantity] = SUM(FactSales[Quantity])
[Order Count] = DISTINCTCOUNT(FactSales[OrderNo])
[Cost Amount] = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitCost])
[Gross Profit] = [Sales Amount] - [Cost Amount]
[Gross Margin %] = DIVIDE([Gross Profit], [Sales Amount])
[Average Order Value] = DIVIDE([Sales Amount], [Order Count])

用 DIVIDE 代替 /，因为可安全处理分母为零：DIVIDE(分子,分母,备用结果)。

4.11 基础时间智能

前提：连续日期表、已标记日期表、日期关系有效。

[Sales YTD] = TOTALYTD([Sales Amount], DimDate[Date])

[Sales Previous Year] =
CALCULATE(
    [Sales Amount],
    SAMEPERIODLASTYEAR(DimDate[Date])
)

[YoY Change] = [Sales Amount] - [Sales Previous Year]

[YoY %] = DIVIDE([YoY Change], [Sales Previous Year])

[Sales Previous Month] =
CALCULATE(
    [Sales Amount],
    DATEADD(DimDate[Date], -1, MONTH)
)

[Rolling 12M Sales] =
CALCULATE(
    [Sales Amount],
    DATESINPERIOD(
        DimDate[Date],
        MAX(DimDate[Date]),
        -12,
        MONTH
    )
)

财政年度若 6 月 30 日结束：

[Sales FYTD] = TOTALYTD([Sales Amount], DimDate[Date], "6/30")

常见故障：

• 上年同期为空：日期表范围不足、关系未激活、用了事实表日期而非日期表日期。
• 月份顺序错误：显示名未按年月键排序。
• 总计看似“不等于各行相加”：度量值在总计筛选上下文重新计算，尤其比率和去重计数，这是正确行为。
• 多日期字段：订单日期和发货日期只能有一条活动关系。用非活动关系时：

[Sales by Ship Date] =
CALCULATE(
    [Sales Amount],
    USERELATIONSHIP(FactSales[ShipDate], DimDate[Date])
)

4.12 DAX 排错方法

1. 从最基础度量值开始验证：行数、数量、销售额。

2. 在透视表中逐层加入日期、产品、地区，观察在哪层异常。

3. 检查关系是否活动、方向是否合理、维表键是否唯一。

4. 将复杂度量拆成多个中间度量值。

5. 使用 DIVIDE 处理零值。

6. 区分空值 BLANK 与 0；可用 COALESCE([Measure],0) 显示 0，但不要掩盖缺失。

7. 不要在度量值里直接引用一个有多值的裸列，否则可能出现“无法确定单一值”；使用聚合或 SELECTEDVALUE：

[Selected Region] = SELECTEDVALUE(DimRegion[Region], "多个区域")

4.13 练习与答案

练习 1： 写出只统计“外设”类别销售额的度量值。

参考答案：

[Peripheral Sales] =
CALCULATE([Sales Amount], DimProduct[Category] = "外设")

练习 2： 为什么不应使用 AVERAGE(FactSales[RowMargin]) 作为整体毛利率？

练习 3： 上年同期全部为空，列出三个检查点。

5. 假设分析与规划求解

5.1 假设分析工具概览

Excel“数据 → 模拟分析”通常包括：

• 单变量求解：已知公式目标，反求一个输入；
• 单变量/双变量数据表：批量观察一个或两个输入变化对公式的影响；
• 方案管理器：保存多组离散输入并生成摘要。

规划求解（Solver）处理多个决策变量、目标函数和约束条件，可求最大值、最小值或指定值。

5.2 单变量求解：反推销量

建立模型：

当前利润为 -10000。若目标利润为 30000：

1. 数据 → 模拟分析 → 单变量求解。

2. 设置单元格：$B$6。

3. 目标值：30000。

4. 可变单元格：$B$5。

5. 确定。

结果销量为 1600，因为 (120-70)*1600-50000=30000。

故障：

• 设置单元格必须含公式且依赖可变单元格。
• 单变量求解只能改变一个单元格。
• 非线性或无解模型可能不收敛。
• 销量需整数时单变量求解不会自动强制整数，应使用 Solver。

5.3 单变量数据表：价格敏感性

继续使用模型，把价格放 B2、利润放 B6。在 D2 输入 =$B$6；D3:D8 输入价格 100、110、120、130、140、150。

1. 选择 D2:E8（E 列留作结果）。

2. 数据 → 模拟分析 → 数据表。

3. 因候选价格纵向排列，设置“列输入单元格”为 $B$2；行输入留空。

4. 确定。

Excel 会把每个价格临时代入 B2，并返回对应 B6 利润。数据表结果是特殊数组，不能单独删除其中一个单元格。

5.4 双变量数据表：价格与销量

1. 在 D2 输入 =$B$6。

2. E2:I2 横向输入销量：800、1000、1200、1400、1600。

3. D3:D8 纵向输入价格：100、110、120、130、140、150。

4. 选择 D2:I8 → 数据表。

5. “行输入单元格”选 $B$5（横向候选是销量）。

6. “列输入单元格”选 $B$2（纵向候选是价格）。

交叉点即相应价格、销量组合下的利润。可添加条件格式色阶，但避免只凭颜色下结论。

性能： 大量数据表会频繁重算。文件 → 选项 → 公式，可把工作簿计算设置为“自动（数据表除外）”，需要时按 F9。

5.5 方案管理器

假设三个输入：单价 B2、单位成本 B3、销量 B5。

1. 数据 → 模拟分析 → 方案管理器 → 添加。

2. 方案名“基准”，可变单元格 $B$2,$B$3,$B$5，输入 120、70、800。

3. 添加“乐观”：130、65、1500。

4. 添加“悲观”：105、78、600。

5. 单击“摘要”，结果单元格选利润 $B$6。

Excel 创建方案摘要表。方案管理器适合少量、离散、可解释的情境；大量连续敏感性分析更适合数据表。

5.6 启用规划求解

文件 → 选项 → 加载项 → 管理“Excel 加载项”→ 转到 → 勾选“规划求解加载项”。启用后在“数据”选项卡右侧出现“规划求解”。

5.7 产品组合最大利润案例

某工厂生产 A、B 两种产品：

假设 B6:C6 为生产量。设置：

• 总利润 B8：=SUMPRODUCT(B2:C2,B6:C6)
• 机器使用 B9：=SUMPRODUCT(B3:C3,B6:C6)
• 人工使用 B10：=SUMPRODUCT(B4:C4,B6:C6)

Solver 步骤：

1. 数据 → 规划求解。

2. 设置目标：$B$8；选择“最大值”。

3. 可变单元格：$B$6:$C$6。

4. 添加约束：

• $B$9<=100
• $B$10<=90
• $B$6<=40
• $C$6<=50
• $B$6:$C$6>=0
• 若产品不可分割，添加 $B$6:$C$6 为整数。

5. 求解方法选“单纯形 LP”，因为目标和约束均为线性。

6. 单击求解，选择“保留规划求解解”。

若允许连续产量，该线性模型的一个最优解为 A=30、B=40，总利润 2400；机器用 100，人工用 90，两项资源均用满。整数约束下该解仍为整数，因此结果不变。

5.8 求解方法选择

• 单纯形 LP：线性目标和线性约束，最快且能提供灵敏度报告；模型应勾选“假定线性模型”。
• GRG 非线性：平滑非线性模型，如价格影响需求、投资组合含平方项。
• 进化：不连续、非光滑、含 IF/查找或组合选择问题；速度慢且结果可能因随机性不同。

不要用进化算法掩盖本可建成线性模型的问题。正确建模通常比增加迭代次数更重要。

5.9 约束、整数、二进制与 alldifferent

• 整数 int：员工人数、车辆数；
• 二进制 bin：是否选址、是否启动项目，变量只能 0/1；
• alldifferent：变量必须是不同整数，适合排程序号。

固定启动成本模型可设二进制变量 Y，并约束 生产量 <= 大M*Y，目标扣除启动成本。大 M 应尽可能紧，过大会削弱求解效率和数值稳定性。

5.10 Solver 报告与结果解释

求解成功后可生成：

• 答案报告：原值、终值、约束是否绑定；
• 灵敏度报告：影子价格、允许增加/减少，仅通常用于无整数约束的线性模型；
• 极限报告：变量在边界变化时的情况。

绑定约束表示资源完全耗尽。影子价格表示在允许范围内增加一单位资源可改善多少目标值。例如机器小时影子价格 15，意味着多 1 小时理论上使最大利润增加 15，前提是仍在有效范围内。

5.11 Solver 常见故障

• “找不到可行解”：约束互相冲突；逐条停用检查，确认单位一致。
• “目标单元格值不收敛”：缺少上界，模型可无限增大；给变量或资源合理边界。
• 结果出现小负数如 -1E-9：浮点容差；可设置非负约束和整数约束，不要仅用格式隐藏。
• 线性模型却选 GRG：可能慢或结果不稳定，应选单纯形 LP。
• 公式中含 IF、ROUND：造成不连续；尽量用约束表达逻辑，整数舍入应作为约束而非事后 ROUND。
• 改变变量却目标不变：目标公式没有真正引用变量，检查依赖链。
• 整数模型耗时长：缩小变量边界、减少对称解、使用更紧的大 M。
• 求得局部最优：GRG 非线性可能依赖初始值；尝试多个合理初值或进化方法，并结合业务验证。

5.12 模型审计规范

1. 输入单元格、公式单元格、输出单元格使用一致颜色，但不要依赖颜色代替说明。

2. 所有单位写清楚：元/件、小时/件、件。

3. 把决策变量集中在连续区域。

4. 每个约束建立“左侧值、符号、右侧值、松弛量”检查表。

5. 用手工极端情境检查公式，例如生产量全为 0 时利润是否为 0 或负固定成本。

6. 保存求解前输入，记录求解方法、选项、时间和结果。

7. 最优解不等于可执行决策，还需审查未建模风险、数据质量和业务规则。

5.13 练习与答案

练习 1： 单价 200、单位成本 120、固定成本 80000，目标利润 40000，反求销量。

练习 2： 双变量数据表中，横向是折扣率，纵向是销量，行输入和列输入分别选什么？

练习 3： 项目只能选或不选，且总预算不超过 100 万，应怎样建模？

参考答案：

• 每项目设 0/1 二进制变量。
• 目标用 SUMPRODUCT(收益,选择变量) 最大化。
• 预算约束 SUMPRODUCT(成本,选择变量)<=100万。
• 变量添加 bin 约束。

练习 4： Solver 结果显示所有变量无限增大，应检查什么？

6. 综合案例：从文件夹明细到管理仪表板

6.1 业务任务

每月把各区域订单文件放入同一文件夹，要求：自动清洗、关联产品与目标、计算销售额和毛利、建立多表模型、生成区域/月度仪表板，并用规划求解制定下月产品组合。

6.2 推荐实施顺序

1. 数据规范：统一每月文件列名；订单明细一行一商品；建立稳定产品键、客户键。

2. Power Query 获取：从文件夹读取，筛除临时文件，保留源文件名。

3. 清洗：修剪文本、指定区域设置和类型、记录错误、去除真正重复。

4. 质量检查：输出空键、重复键、错误日期、负数量、未匹配代码。

5. 维表处理：产品、客户、日期、目标分别形成唯一键表。

6. 加载模型：中间查询仅连接；事实表与维表加载到数据模型。

7. 关系：建立维表一端到事实表多端的单向关系。

8. DAX：销售额、成本、毛利、毛利率、订单数、同比、滚动 12 月。

9. 透视与图表：月份为行、区域为列或切片；图表以趋势和目标差异为主。

10. 刷新测试：新增一个月文件，执行全部刷新，核对行数与总额。

11. 规划：把单位利润和资源耗用导出为清晰输入区，用 Solver 最大化利润。

6.3 仪表板建议布局

• 顶部 KPI：销售额、毛利、毛利率、订单数、同比；
• 中部左：月度销售额与目标线；
• 中部右：区域贡献条形图；
• 底部左：产品类别帕累托；
• 底部右：异常或未匹配记录数；
• 左侧或顶部：年份、区域、渠道切片器和日期时间线。
• 保持同一指标格式一致。
• 销售额统一万元或百万元。
• 毛利率统一一位小数。
• 标题随筛选可用单元格拼接，但应说明当前筛选范围。

6.4 验收清单

• [ ] 原始文件更新后无需复制粘贴即可刷新；
• [ ] 查询步骤有业务化名称，源路径参数化；
• [ ] 订单键、产品键规则明确；
• [ ] 未匹配维表键有单独检查结果；
• [ ] 日期表连续、关系活动；
• [ ] 毛利率采用“总毛利/总销售额”；
• [ ] 透视表来源为表格或数据模型；
• [ ] 图表坐标轴、单位、目标线无误；
• [ ] 双轴图有充分理由且范围透明；
• [ ] 刷新后格式、列宽、切片器连接正常；
• [ ] Solver 变量、目标和约束均有单位与审计表；
• [ ] 所有关键结果均与独立手算样本核对。

7. 模块综合练习与参考答案

练习 A：透视分析

将 Sales 按年、月汇总，显示销售额、上月差异、总额占比；添加区域与渠道切片器；用 GETPIVOTDATA 在摘要区提取所选区域销售额。

• 日期放行并组合年/月。
• 销售额放值三次，第二次显示“与上一项差异”，第三次显示“总计百分比”。
• 插入区域、渠道切片器并连接透视表。摘要公式以透视表内部单元格为锚点。
• 若需读取切片器当前项，传统 GETPIVOTDATA 不会自动把切片器选择写入普通单元格，可让用户在下拉单元格选区域，或使用数据模型/CUBE 函数进阶实现。

练习 B：可视化选择

管理层希望同时看月销售额、目标、毛利率和订单数。设计不误导的表达。

练习 C：Power Query

每月 CSV 可能新增“备注”列、缺少“促销代码”列，且产品代码有小写和空格。要求稳定追加。

• 文件夹查询先筛选扩展名并排除临时文件。
• 在示例转换中修剪列名；产品代码先执行 Text.Trim，再执行 Text.Upper。
• 选择必需列时可用 Table.SelectColumns(table, requiredColumns, MissingField.UseNull)，缺失的可选列填入 null。
• 新增备注列是否保留，应由明确的字段清单决定，避免未经确认的结构变化进入模型。

练习 D：模型与 DAX

写出区域销售额占当前所有区域销售额的比例，但保留年份和产品筛选。

[Region Share %] =
DIVIDE(
    [Sales Amount],
    CALCULATE([Sales Amount], REMOVEFILTERS(DimRegion))
)

REMOVEFILTERS(DimRegion) 只移除区域维度，日期和产品筛选仍保留。

练习 E：规划求解

• 三种产品单位利润 50、35、25。
• 机器小时消耗 3、2、1，可用 120。
• 人工小时消耗 1、2、2，可用 100。
• 需求上限 30、40、50。说明模型。

• 三个生产量为决策变量。
• 目标 SUMPRODUCT(单位利润,生产量) 最大。
• 机器约束 SUMPRODUCT(机器耗用,生产量)<=120。
• 人工约束 <=100。
• 各变量不超过需求并且非负。
• 不可分割时加整数约束。
• 全部线性，选单纯形 LP。求解后还应检查资源松弛量与需求绑定情况，而非只抄最大利润。

8. 关键概念速查

• 数据透视表：面向交互汇总；源数据必须规范，刷新不会自动修复脏数据。
• GETPIVOTDATA：按字段语义取数，比直接坐标引用稳定。
• 图表：比较用条柱，趋势用折线，关系用散点，累计贡献用帕累托。
• 双轴：可用但高风险；优先分面或统一指数。
• Power Query：记录可刷新转换；类型、错误、隐私和加载方式同样重要。
• 合并：横向联接；追加：纵向堆叠。
• 数据模型：维表一端、事实表多端，优先星型结构。
• 计算列：逐行存储；度量值：按筛选上下文计算。
• SUMX：迭代行；CALCULATE：修改筛选上下文；RELATED：沿关系取一端值。
• 时间智能：必须有可靠日期表。
• 单变量求解：一个输入反推一个目标；数据表：批量敏感性；Solver：多变量约束优化。
• 最优结果必须经过数据质量、单位、约束完整性与业务可执行性审查。

学习建议：先用小数据手算验证，再扩大到真实数据；每次刷新都核对行数、唯一键数、金额总计和错误数。Excel 的高级分析能力并不来自“按钮更多”，而来自规范数据、明确模型、可重复步骤和可审计结果。

文件编码说明： 本文采用 UTF-8 Markdown；公式参数分隔符按英文逗号书写。部分地区 Excel 使用分号作为参数分隔符，输入时按本机设置替换。DAX 函数名与 M 函数名保持英文，这是 Excel 的标准写法。中文界面不同更新通道的按钮名称可能略有差异，但功能路径一致。

9. 数据透视表进阶实验：把“会拖字段”提升为“会设计报表”

9.1 实验一：订单数为什么不能直接计数销售额

业务问题是“每个区域有多少张订单”，正确字段应为订单号，而不是销售额。把订单号拖入值区域，默认通常显示“计数项”。如果一张订单有多条商品明细，普通计数会把明细行数当作订单数，此时必须使用“非重复计数”。

操作如下：创建数据透视表时勾选“将此数据添加到数据模型”；把区域放入行，把订单号放入值；打开值字段设置，选择“非重复计数”。若创建时没有加入数据模型，传统透视表通常看不到该汇总方式，需要重新创建。验证时分别计算明细行数和唯一订单号数，两者不一定相同。

排错重点：订单号中前后空格会让视觉上相同的编号成为两个项目；数字订单号和文本订单号也可能被视为不同值。进入 Power Query，统一为文本并修剪，再做非重复计数。空订单号通常不会被当成有效订单，必须在清洗阶段标记并回到源系统修复。

9.2 实验二：同一指标同时展示本期、占比和同比

准备至少两年的月度数据。字段配置为：

• 日期表中的年、月放入行区域。
• 销售额度量值放入值区域三次。第一个保持普通求和，命名“本期销售额”。
• 第二个设置为“总计的百分比”，命名“结构占比”。
• 第三个若是普通透视表，可设置“差异百分比”，基本字段为年或月份并选择正确基准。
• 若使用数据模型，建议直接使用 [YoY %] 度量值。

设计报表时不要让三个指标都使用同一数字格式：本期销售额显示千位分隔符，结构占比和同比显示百分比。负同比可用自定义格式 0.0%;[红色]-0.0%，但颜色不应成为唯一编码，还可保留负号或向下箭头。

若总计处同比看起来异常，应理解总计不是可见行同比的算术平均，而是在总计筛选上下文中重新计算。真正的整体同比应是“所有本期销售额减所有上年同期，再除以上年同期”，而不是月份同比的简单平均。

9.3 实验三：显示没有发生交易的类别

默认透视表只显示事实表中存在的组合。若希望即使某月没有销售也显示该月，需要完整日期维表，并在字段设置中启用“显示无数据的项目”。普通透视表中可右击字段 → 字段设置 → 布局和打印 → 显示无数据的项目；数据模型中还需保证维表关系和度量值逻辑正确。

显示空项目后，不能立刻把空白解释为 0。空白可能表示没有事实记录，0 可能表示有记录但金额相抵。若管理报表的计算规则认定“无交易就是 0”，可用 DAX：

[Sales Display] = COALESCE([Sales Amount], 0)

不过质量检查报表应保留 BLANK，以便区分缺记录和真实零。

9.4 实验四：钻取明细与防止误泄露

双击透视表中的数值，Excel 会在新工作表中生成构成该数值的明细。这是核对异常的快捷方法。钻取结果是当时的静态副本，不会随源数据刷新；完成检查后应删除或明确标注时间。

如果数据包含身份证号、手机号、成本、薪资等敏感字段，即使它们没有出现在透视布局中，钻取仍可能导出源表列。发布工作簿前应从模型源头移除无关敏感列，不能仅靠隐藏工作表或隐藏字段。共享前实际双击测试一次，确认明细范围符合权限。

9.5 数据透视表十项诊断流程

当结果“不对”时，按固定顺序检查：

• 第一，源数据总行数与金额总计。
• 第二，字段类型是否统一。
• 第三，源范围是否包含新增行。
• 第四，是否已刷新。
• 第五，筛选器、切片器、时间线是否仍有限制。
• 第六，值字段是求和、计数还是非重复计数。
• 第七，值显示方式是否改变了原值。
• 第八，日期是否跨年却只按月份分组。
• 第九，是否存在隐藏项目、空白键或重复键。
• 第十，用双击钻取的小明细重新手算。

不要一看到异常就删除重建透视表。先定位是数据问题、汇总规则问题、筛选上下文问题还是显示问题。建立“控制总计”单元格非常有效，例如在源表旁用 SUM(Sales[销售额])，每次刷新后与透视总计比较。

10. 图表表达进阶实验与审稿清单

10.1 条形图与柱形图的选择细节

类别名称较长、类别超过六个或需要明确排名时，横向条形图通常比柱形图易读。先按指标降序排序，让读者从上到下看到排名。若采用条形图，常需把类别轴设为逆序，使最大项在顶部。时间序列则不建议为了排名改序，时间必须按先后排列，优先折线图或柱形图。

柱宽并非越宽越好。间隙宽度过大显得数据稀疏，过小又像连续面积。常用范围为 40%—100%，同一报告保持一致。多系列柱形图超过三组后会迅速拥挤，可改成小倍数图或加入筛选器。

10.2 饼图的限制与替代

饼图只适合少量、互斥、总和有意义的构成项。类别超过五个、差异细微、存在负值或需要比较两期时，应改用排序条形图或 100% 堆积条形图。不要使用三维饼图，因为透视角度会夸大前方扇区。若必须用饼图，应直接标类别与百分比，避免让读者在图例和扇区间来回匹配。

“各地区销售额”虽然可以画饼图，但条形图更容易比较 21% 与 24%。构成分析还要确认分母：筛选后的地区占比是当前筛选总额占比，并非全公司占比，标题中应说明范围。

10.3 散点图、相关性与因果误区

若要分析广告投入与销售额关系，第一列放数值型 X（广告投入），第二列放数值型 Y（销售额），插入散点图。添加趋势线并显示决定系数可以描述线性拟合程度，但高相关不证明广告导致销售增长；季节、促销或区域规模可能同时影响两者。

散点图横轴必须是数值轴。若误用折线图，Excel 会把每行当成等间隔类别，即使投入从 1 万跳到 20 万，视觉间距仍相同。异常点不要未经调查就删除；先标注订单、区域或月份，判断是数据错误还是重要业务事件。

10.4 瀑布图表达增减因素

虽然核心要求未限定瀑布图，但它常用于解释“从预算到实际”或“从收入到利润”的桥接。准备项目和金额：期初、销量影响、价格影响、成本影响、费用影响、期末。插入瀑布图后，将期初和期末数据点设置为“汇总”。正值和负值使用语义一致的颜色，不要把红绿作为唯一差异，最好同时保留正负号。

瀑布图每个增减项必须可以加总到期末。若桥接项互相重叠或统计规则不同，图形虽能画出却没有可解释性。发布前用单元格公式验证“期初+所有变动=期末”。

10.5 目标线与容差带

单一目标线只表示一个阈值。若业务有可接受范围，例如交付率目标 95%，预警下限 92%，可用两条参考线，或用堆积面积图形成浅色容差带再叠加实际折线。参考线应弱于实际数据，使用细线或浅灰，不能抢夺视觉注意力。

目标随月份变化时，目标列必须逐月保存，不要用一个固定值复制后忘记更新。若目标来自独立表，应通过查找或数据模型关系取得，并在缺目标月份显示空白而非自动当作 0。

10.6 图表无障碍与打印检查

颜色应有足够对比度，并配合线型、标记或直接标签。红绿组合对色觉异常读者不友好，可使用蓝橙，并为负值保留负号。字号在实际投影或打印尺寸下至少可读；不要只在放大编辑状态判断。

灰度打印测试也很重要：如果转为灰度后所有系列无法区分，就需要改变线型或标记。图表标题应能独立说明“对象、指标、期间和单位”，例如“2026年1—6月各区域销售额（万元）”，而不是泛泛写“销售分析”。

10.7 图表审稿十二问

1. 图表回答的业务问题是什么？

2. 图形类型是否匹配比较、趋势、构成、分布或关系？

3. 数据范围是否包含全部需要的行？

4. 时间和类别排序是否正确？

5. 纵轴是否从合理起点开始？

6. 单位是否明确且一致？

7. 双轴是否真的必要？

8. 颜色是否只强调关键内容？

9. 标签是否过密或遮挡？

10. 筛选范围是否在标题中可见？

11. 图形结论能否由源数字复核？

12. 灰度打印和较小屏幕上是否仍可读？

11. Power Query 深度排错与可维护设计

11.1 列结构漂移的防护

真实业务文件会发生列新增、删除、改名和类型变化。若步骤硬编码删除某列，而源中该列消失，刷新会报错。可根据风险选择严格或宽松策略。严格策略让刷新失败并通知维护者，适合关键财务字段；宽松策略允许非关键列缺失并填 null。

示例：

Required = {"订单号", "订单日期", "产品代码", "数量", "单价"},
Selected = Table.SelectColumns(Source, Required, MissingField.UseNull)

随后增加质量列，检查必需字段是否为空。宽松加载不代表忽略问题，而是让流程继续并把问题显式输出。

列重命名也可使用缺失忽略：

Table.RenameColumns(Source, {{"客户名称","客户"}}, MissingField.Ignore)

但关键列若可能出现多个别名，最好先建立列名字典并记录实际采用的映射。

11.2 动态展开列的风险

合并后展开嵌套表时，界面常把当时列名写死。右表以后新增列不会自动展开，删除列则可能报错。若确实需要动态展开，可取得列名列表：

Cols = Table.ColumnNames(DimProduct),
Expanded = Table.ExpandTableColumn(Merged, "产品", Cols, Cols)

然而动态展开会让输出结构不可预测，可能破坏后续模型。生产报表通常更适合明确白名单，只展开真正需要的列。

11.3 分组统计与“所有行”

选择“转换 → 分组依据”，高级模式可按地区和月份分组，并同时计算销售额合计、订单数、最大金额。若操作选择“所有行”，每个分组会得到一张嵌套子表，可继续添加自定义统计，但也容易造成性能问题。

典型 M：

Table.Group(
    Orders,
    {"地区", "月份"},
    {
        {"销售额", each List.Sum([金额]), Currency.Type},
        {"订单数", each List.Count(List.Distinct([订单号])), Int64.Type}
    }
)

若最终要在数据模型中按任意维度切片，不要过早分组丢失明细。只有当报表粒度固定、数据量极大且不再需要明细时才考虑预聚合。

11.4 取消透视与透视

“取消透视列”可把一月份、二月份、三月份等横向列变成长表的“属性、值”两列。规范分析通常偏好长表，因为新增月份只增加行，不需要新增字段。

操作：选中不应变化的标识列，如产品和区域；右击 → 取消透视其他列。把“属性”改为月份，“值”改为销售额，再转换月份类型。

反向的“透视列”会把某字段的项目展开成列，需要指定聚合函数。若同一行列组合有多条记录却选择“不聚合”，会报错；应先确认唯一性或选择求和。为了后续数据模型，通常不必把长表重新透视成宽表。

11.5 自定义函数处理多种文件

当每个文件都需要相同逻辑，可把转换写成函数：

(file as binary) as table =>
let
    wb = Excel.Workbook(file, null, true),
    sheet = wb{[Item="订单", Kind="Sheet"]}[Data],
    promoted = Table.PromoteHeaders(sheet, [PromoteAllScalars=true]),
    selected = Table.SelectColumns(promoted,
        {"订单号","订单日期","产品代码","数量","单价"},
        MissingField.UseNull)
in
    selected

函数调用失败时，保留文件名与错误记录：

Added = Table.AddColumn(Files, "Result", each try fxTransform([Content]))

再展开 HasError、Value 或 Error。这比让整个批次因一个坏文件终止更易审计，但是否允许部分成功必须由业务规则决定。财务关账通常应在存在坏文件时阻止最终发布。

11.6 查询依赖与分层

开启“视图 → 查询依赖关系”，观察源查询、引用查询与最终输出。推荐同一原始读取只做一次，再使用“引用”创建下游查询；“复制”会生成一套独立步骤，可能重复访问源。

分层示例：

• stg_FolderFiles 只读取文件清单。
• stg_OrdersRaw 调用文件函数。
• fact_Sales 完成类型、键和业务列。
• dq_SalesErrors 引用事实前的检查步骤输出问题。
• out_MonthlySummary 仅在确有固定输出需要时分组。

中间层只创建连接。名称、描述和步骤注释应说明输入、输出、唯一键、刷新依赖及错误处理策略。

11.7 凭据与路径迁移

刷新报“访问被禁止”时，不要反复修改查询代码。先进入“数据源设置”，选择对应源并编辑或清除权限，再重新登录。文件从本地盘迁移到 SharePoint 或网络盘时，连接器和路径语义可能改变，不宜只做字符串替换。

个人绝对路径如 C:\Users\姓名\Desktop\数据 不利于团队协作。可使用组织共享路径、SharePoint 文件夹连接器或参数化根路径。路径参数只解决位置差异，不会自动解决其他用户没有权限的问题。

11.8 刷新后的控制总数

每个生产查询至少建立以下核对值：读取文件数、读取行数、唯一业务键数、金额合计、错误数、空关键字段数、未匹配维表键数。与上次刷新比较，若波动超过阈值应人工确认。

Power Query 可以建立单行质量摘要查询并加载到“刷新日志”工作表。还可保留文件名和源行号，便于定位错误。只看最终图表是否“像正常”不是数据质量控制。

11.9 Power Query 追加练习

题目： 数据中状态存在“完成”“已完成”“ 完成 ”，应怎样统一且保留审计性？

题目： 合并产品表后总行数从一万变为一万二，应如何判断？

题目： 查询刷新后日期全部变成错误，最可能的原因是什么？

12. Power Pivot 与 DAX 上下文专项训练

12.1 为什么计算列总计与度量值不同

在事实表建立计算列 行毛利率=(销售额-成本额)/销售额，每行都有值。把该列拖入透视表并选择平均值，得到的是行毛利率平均；不同金额行被赋予同样权重。度量值 [Gross Margin %]=DIVIDE([Gross Profit],[Sales Amount]) 则在每个筛选上下文先汇总分子分母，再计算比率，通常才是管理报表的计算规则。

再如订单平均金额，若事实表一订单多行，AVERAGE(FactSales[LineAmount]) 是平均明细行金额，不是平均订单金额。正确做法：DIVIDE([Sales Amount],[Order Count])，其中订单数为非重复计数。

12.2 CALCULATE 的上下文转换

在计算列或迭代器中，CALCULATE 可把当前行上下文转换为筛选上下文。此概念对初学者较难，先记住：度量值被调用时常隐式完成必要转换，而直接聚合表达式未必如此。不建议为了“让结果出来”到处添加 CALCULATE，应先明确当前行和当前筛选是什么。

一个可观察实验是在产品表创建计算列：

DimProduct[Product Sales] = CALCULATE([Sales Amount])

当前产品行通过上下文转换成为对产品键的筛选，再沿关系过滤事实表。若缺少 CALCULATE，直接表达式的行为会不同。生产模型仍应优先使用度量值，而不是把每个产品销售额永久存成计算列。

12.3 ALL、REMOVEFILTERS 与 ALLSELECTED

REMOVEFILTERS(DimProduct) 明确表示删除产品筛选，适合计算全产品分母。ALL(DimProduct) 在许多度量中也能达到类似目的，同时作为表函数返回所有行。为了表达清晰，单纯清除筛选优先 REMOVEFILTERS。

ALLSELECTED 常用于“当前用户选定范围内的占比或累计”，它保留外部选择但忽略视觉对象内部的行列筛选，语义较复杂。例如切片器只选三个区域，希望每个区域占这三个区域合计的比例，可研究：

[Visible Region Share %] =
DIVIDE(
    [Sales Amount],
    CALCULATE([Sales Amount], ALLSELECTED(DimRegion[Region]))
)

必须用不同切片组合测试总计和单项，避免把 ALLSELECTED 当成万能占比函数。

12.4 安全除法、空值和显示

DIVIDE([A],[B]) 在分母为零或空时返回 BLANK；第三参数可指定备用结果，如 DIVIDE([A],[B],0)。是否显示 0 取决于业务。转化率没有曝光时通常应为空而非 0%，因为“没有机会发生”不同于“发生了但无转化”。

格式字符串只改变显示，不改变值。把度量值格式设百分比后，底层 0.25 显示为 25%；若源本身存 25，再设置百分比会显示 2500%，应先确认数据尺度。

12.5 排名与 Top N

产品排名：

[Product Rank] =
RANKX(
    ALLSELECTED(DimProduct[ProductName]),
    [Sales Amount],
    ,
    DESC,
    DENSE
)

只显示前五可在视觉筛选中按度量值做 Top N，或建立：

[Top5 Sales] = IF([Product Rank] <= 5, [Sales Amount])

并列排名时前五可能显示超过五个产品，这是排名规则而非错误。若必须恰好五项，需要定义稳定的第二排序键，但应向读者说明处理并列的规则。

12.6 累计销售额

[Running Sales] =
CALCULATE(
    [Sales Amount],
    FILTER(
        ALLSELECTED(DimDate[Date]),
        DimDate[Date] <= MAX(DimDate[Date])
    )
)

累计结果依赖当前可见日期范围。若切片器选择 3—6 月，使用 ALLSELECTED 往往从 3 月开始累计；使用 ALL 可能从模型最早日期累计。两者都可能合理，标题或指标定义必须说明。

12.7 多对多关系的警告

如果客户表的客户键重复，直接创建关系可能得到多对多。多对多不是简单修复重复的办法。先判断重复是否由于客户历史版本、多个标签或数据错误。常见处理是建立唯一客户桥接表，再让桥接表分别连接事实表与属性表。

双向筛选能让某些图表“工作”，但也会产生多条筛选路径和难以解释的总计。默认优先单向，从维度到事实。只有明确业务需求并验证所有交互后才启用双向。

12.8 DAX 专项练习答案

题目： 计算当前筛选下线上销售额占全部渠道销售额比例，同时保留日期、区域和产品筛选。

参考答案：

[Online Share %] =
DIVIDE(
    CALCULATE([Sales Amount], DimChannel[Channel] = "线上"),
    CALCULATE([Sales Amount], REMOVEFILTERS(DimChannel))
)

若当前视觉行本身是渠道，应仔细测试分子筛选是否覆盖当前行；可使用独立渠道维表并明确筛选逻辑。

题目： 发货日期同比应使用哪条关系？

题目： 为什么产品透视表出现空白产品且有销售额？

13. 假设分析与 Solver 深度案例

13.1 盈亏平衡二维分析

除价格和销量外，单位成本也可能变化。一次双变量数据表只能直接替换两个输入。可以先固定成本情景，分别生成“低成本、基准成本、高成本”三张价格—销量二维表；也可使用方案管理器切换成本，再观察同一敏感性表。

阅读敏感性表时关注：盈亏平衡边界、利润对哪个变量更敏感、可执行组合是否落在可接受区域。条件格式可突出负利润，但必须保留数值与单位。不要把数据表当作预测模型；它只是在给定公式关系下重新计算。

13.2 运输分配模型

两个工厂向三个仓库供货。决策变量是六条路线的运输量，目标最小化总运费。每个工厂发出量不超过产能，每个仓库收到量必须满足需求，所有运输量非负；若货物不可拆分再加整数约束。

表格设计：

• 行是工厂，列是仓库。
• 六个变量放在矩阵中。
• 同形状矩阵保存单位运费。
• 总成本用 SUMPRODUCT(运费矩阵,运输量矩阵)。每行右侧用 SUM 计算发出量，每列底部计算收到量。Solver 设目标最小，变量为运输矩阵，添加行产能 <= 与列需求 =。模型线性，选单纯形 LP。

排错时先确认总产能不小于总需求，否则必然不可行。如果允许缺货，应新增“虚拟供应”或缺货变量并赋予高惩罚成本，而不是删除需求约束。

13.3 人员排班模型

设每天需要人数，变量为每种班次启动人数；一个班次覆盖连续若干天。目标可以最小化总人数或总人工成本。覆盖矩阵中，若某班次覆盖某天记 1，否则 0；每日实际人数由 SUMPRODUCT(该日覆盖行,班次人数) 计算，并约束大于等于需求。班次人数为非负整数。

若员工必须休息、技能不同或个人偏好复杂，模型会快速扩展。应区分“每种班次人数”的聚合排班与“每位员工每天是否上班”的二进制排班。后者变量更多，但能表达个人约束。进化方法并非首选；只要目标和约束仍为线性整数模型，继续使用单纯形 LP 加整数分支求解更合适。

13.4 投资组合的基本模型与风险

变量为各资产投资比例，约束比例和等于 1、每项非负、单项上限；目标可最大化预期收益。若同时限制风险，风险通常涉及协方差矩阵和二次项，成为非线性模型，可使用 GRG 非线性。

历史平均收益和协方差并不保证未来表现。Solver 给出的精确权重可能对输入微小变化非常敏感，需设置合理上下限、做情景分析并考虑交易成本。教程模型只用于理解工具，不构成投资建议。

13.5 可行性与最优性的区别

“可行解”满足全部约束，但不一定最好；“最优解”是在当前模型、数据和求解精度下目标最好。若约束遗漏，数学最优可能业务不可行。例如生产模型未加入最低批量、换线次数或原料保质期，Solver 会利用这些缺口。

每次求解后应问：哪些约束绑定？哪些资源剩余？变量是否在边界？结果对利润、需求和产能变化是否稳定？是否存在未建模的整数、逻辑或风险约束？

13.6 Solver 选项的谨慎使用

“使无约束变量为非负”可减少逐个非负约束，但模型文档仍应说明。容差、收敛和最大时间会影响结果；不建议为了更快而把容差放得过宽，尤其是整数和财务模型。自动缩放可帮助数量级差异很大的模型，但更根本的办法是统一单位，例如把金额统一为万元，避免同时出现十亿和 0.0001。

保存 Solver 模型时，可使用“加载/保存”把参数写入工作表区域，方便版本控制。结果报告应同时保存输入快照，否则日后无法重现。

13.7 深度练习与答案

题目： 某资源约束的左侧为 95，可用量 100，松弛量是多少？是否绑定？

题目： 为什么在线性整数模型中通常拿不到普通灵敏度报告？

题目： 模型要求至少选两个项目，二进制变量为 Y1:Y5，约束如何写？

14. 期末综合任务与评分参考

14.1 任务说明

• 给定十二个月订单文件、产品主数据、区域目标和资源约束，完成以下交付：用 Power Query 文件夹批量导入并清洗。
• 输出数据质量摘要。
• 建立星型数据模型。
• 创建销售额、毛利、毛利率、订单数、同比、滚动十二月度量值。
• 用透视表和切片器制作管理报表。
• 绘制月度目标图、产品帕累托图和项目甘特图。
• 在摘要区使用 GETPIVOTDATA 或模型度量稳定提取关键数字。
• 最后用 Solver 给出下月利润最大化产品组合。

14.2 必须提交的验证证据

1. 原始文件清单与读取文件数；

2. 清洗前后行数、重复数、错误数和未匹配键数；

3. 查询依赖关系截图或文字说明；

4. 模型关系说明，标出一端、多端和活动日期关系；

5. 至少六个 DAX 度量值及格式；

6. 透视表字段布局和刷新设置；

7. 图表选择理由、轴范围和单位；

8. Solver 目标、变量、全部约束、求解方法和结果；

9. 独立手算样本与控制总计；

10. 新增一个月文件后的刷新测试结果。

14.3 评分参考

• 数据结构与清洗 25 分：类型正确、键规则明确、错误可追踪、刷新可重复；
• 数据模型与 DAX 25 分：星型关系合理、度量值计算规则正确、时间智能有效；
• 透视和图表 20 分：布局清楚、交互有效、图形不误导；
• 假设分析与优化 20 分：变量目标约束完整、方法正确、结果可解释；
• 审计与表达 10 分：控制总计、单位、说明、练习结论和文件组织规范。

常见扣分包括：

• 手工复制月份数据而非刷新。
• 把文本数字直接求和。
• 维表键重复。
• 用平均行毛利率冒充整体毛利率。
• 截断柱形图纵轴。
• 无解释使用双轴。
• 删除错误而无日志。
• Solver 缺少非负或需求上限。
• 只报告最优利润而不报告决策变量和资源使用。

14.4 综合任务参考路线

先用文件夹连接器读取所有月文件，在示例转换中统一标题、类型和必需列；把错误记录分流到质量查询，不让问题静默消失。产品和区域目标分别清洗为唯一键维表。事实表保留最低必要粒度，不提前按月汇总。加载事实与维表到数据模型，中间查询仅连接。

建立日期表并标记，连接订单日期。先写销售额、成本和订单数，再写毛利、毛利率与时间智能。用简单透视逐项验证总计，再设计切片器和图表。产品帕累托必须按销售额降序并计算累计占比；目标图的目标线与销售额同单位；甘特图的开始日期系列无填充。

最后把预测需求、单位利润、机器与人工耗用整理为 Solver 输入区。目标最大化利润，变量为各产品计划量，添加资源、需求、非负和整数约束，选择单纯形 LP。生成答案报告，说明绑定资源和剩余能力。修改关键输入做至少三种情景，判断方案是否稳定。

15. 最终自测题答案速查

1. 透视表新增行不刷新最常见原因？ 源为固定区域；改成 Excel 表格并刷新。

2. 销售额为何显示计数？ 源列含文本或混合类型；清洗类型后重新放入值区域。

3. GETPIVOTDATA 的主要优势？ 按字段与项目取数，不依赖透视值的物理坐标。

4. 柱形图轴是否应从零开始？ 通常应从零，避免夸大类别差异。

5. 双轴最大风险？ 两套独立刻度可人为制造相似趋势或交点。

6. 帕累托图前置步骤？ 按贡献值降序，再计算累计占比。

7. 甘特图隐藏哪个系列？ 开始日期系列无填充，它只负责位置偏移。

8. 迷你图跨行比较要做什么？ 统一纵轴最小值和最大值。

9. Power Query 合并与追加区别？ 合并横向加列，追加纵向加行。

10. 文件夹刷新读到输出文件怎么办？ 输入输出目录分离，并筛文件名与扩展名。

11. null 是否等于零？ 不等；null 表示缺失，零是有效数值。

12. Formula.Firewall 反映什么？ 多数据源组合与隐私隔离冲突。

13. 为何中间查询仅连接？ 减少工作表膨胀和重复输出，保持分层。

14. 数据模型一端必须满足什么？ 关系键唯一，类型与多端匹配。

15. 度量值与计算列核心区别？ 度量值按筛选上下文计算；计算列逐行计算并存储。

16. SUMX 做什么？ 遍历表，对每行表达式求值后聚合。

17. CALCULATE 做什么？ 在修改后的筛选上下文中计算表达式。

18. RELATED 的前提？ 模型中存在可用关系，通常从多端取一端属性。

19. 时间智能为何需要日期表？ 提供连续、唯一、可正确移动期间的日期上下文。

20. 比率总计为何不等于行比率平均？ 总计在整体筛选上下文重新计算分子分母。

21. 单变量求解能改几个输入？ 一个。

22. 双变量数据表最多直接替换几个输入？ 两个。

23. 线性产品组合用哪种方法？ 单纯形 LP。

24. 人数变量为何加整数约束？ 人数不可取小数，事后四舍五入可能破坏约束和最优性。

25. Solver 无界的典型原因？ 正贡献变量缺少资源、需求或变量上限。

26. 绑定约束是什么？ 最优解处松弛量为零的约束。

27. 最优解是否必然可执行？ 否；它只对已建模数据和约束最优。

28. 刷新后首先核对什么？ 文件数、行数、唯一键、错误数和控制总计。

29. 隐藏敏感列是否足够安全？ 不足；应从查询或模型源头移除无关敏感字段。

30. 完整分析流程的核心？ 规范数据、可刷新转换、明确模型、正确的计算规则、诚实图形和可审计决策。

完成本模块后，请删除练习结果并从原始数据重新建立一次。随后分别加入文本数字、重复键、缺列、错误日期和无界约束，按本章的排错顺序修复。验收标准是：能够说明错误原因，并用控制总计证明修复结果正确。

16. 教师演示与学习者回顾检查指南

16.1 一次完整课堂演示的节奏

教师不应直接展示已经完成的仪表板，。从一份故意含有问题的明细开始。第一阶段先让学习者指出空标题、合并单元格、文本数字、重复订单、错误日期和缺失产品代码，并讨论这些问题会怎样影响求和、分组和关系。第二阶段用 Power Query 逐步清洗，每完成一步就观察数据类型图标、行数和错误数的变化。第三阶段把事实表与维表加载到模型，先建立最简单的销售额度量，再逐步加入毛利率和同比。第四阶段才创建透视表与图表，强调图形只是模型结果的表达，而不是修复数据的工具。最后使用 Solver 将分析结果转化为资源分配决策，并检查最优解是否符合现实。

演示中每个关键步骤都应暂停提问：“如果源文件下月多一列会怎样”“如果产品表键重复会怎样”“如果纵轴从九十开始会怎样”“如果忘记销量整数约束会怎样”。学习者能预测后果，比记住按钮位置更重要。

16.2 每次作业必须附带的回顾检查记录

学习者应在文件末尾写明：

• 数据来源和刷新日期。
• 事实表粒度。
• 主键或组合键。
• 删除了哪些记录及理由。
• 错误值如何处理。
• 查询加载位置。
• 模型关系。
• 主要度量值计算规则。
• 图表选择理由。
• Solver 的目标、变量和约束。
• 至少一个手算验证样本。

回顾检查还应回答三个问题。第一，哪一步最可能在下月刷新时失败？第二，当前结果对哪项假设最敏感？第三，若交给同事维护，哪项规则最需要文档说明？这些问题能迫使制作者从“一次性完成”转向“可重复、可解释、可交接”。

16.3 分层排错原则

• 遇到错误时从底层向上检查。数据层检查文件、行、列、类型、键和缺失。
• 转换层检查步骤顺序、合并基数、追加列名和错误处理。
• 模型层检查关系、方向、日期表和筛选上下文。
• 报表层检查透视布局、筛选器、值显示方式和图表轴。
• 决策层检查假设、目标、变量、单位和约束。不要在上层用格式或公式掩盖下层问题。

例如图表销售额少了一个月，先检查事实表是否读取该月文件，再检查日期是否成功转换、日期表是否覆盖该月、关系是否活动、透视是否筛掉该月，最后才检查图表数据范围。沿依赖链排查可以避免无目的地反复刷新和重建。

16.4 发布前的最终演练

• 关闭工作簿前保存一个控制总计。
• 复制一份测试文件。
• 向输入文件夹加入结构正确的新月份文件并刷新。
• 再加入一个缺列文件，确认质量查询或错误提示按设计出现。
• 恢复正常文件后刷新，检查透视表、切片器、时间线、GETPIVOTDATA、DAX 和图表是否同步。随后改变一个参数，确认参数化查询有效。
• 修改一个目标值，确认目标线更新。
• 改变资源上限，重新运行 Solver 并解释新绑定约束。

最后在另一台计算机或另一个用户账户测试路径和凭据。如果文件只能在制作者电脑上刷新，就还不算可交付。清除不必要的缓存明细、临时钻取表和敏感字段，保护需要保护的公式区域，但同时保留维护说明。保护工作表只能防止误改，不能替代数据权限管理。

16.5 能力达成标准

入门达成标准是能从规范表格创建正确透视表、选择基本图表并完成单变量求解。进阶标准是能用 Power Query 建立可刷新清洗流程，处理合并、追加、文件夹和错误，能建立星型模型并写基础度量值。精通标准是能解释筛选上下文、设计可靠时间智能、审查双轴与累计图、诊断结构漂移和关系异常，并把业务问题转化为有单位、有边界、可验证的优化模型。

本模块按以下标准验收：

• 结果计算正确。
• 刷新过程稳定。
• 指标计算规则写清楚。
• 故障可以定位。
• 分析结论能够复核。
• 能够从一份陌生数据独立完成获取、清洗、建模、分析和表达，并说明关键选择及其风险。