从一格数据，到一套可信的决策系统

先把数据区域变成表格

用 Excel 表格承载持续增长的数据，公式、格式、筛选和引用会自动向下扩展。

适用场景

原始数据会继续追加，或者下游还有图表、透视表与公式。

1. 选中数据后按 Ctrl + T
2. 勾选“表包含标题”
3. 在“表设计”中命名为 tblSales 一类有含义的名称

注意：一列只放一种含义；不要在数据区插空行、合并单元格或手工小计。

建立输入契约与数据验证

通过下拉列表、日期范围、数值边界和输入提示，在源头阻止脏数据进入工作簿。

适用场景

表格由多人填写，或者状态、部门、日期等字段必须保持统一。

1. 先建立合法值清单
2. 数据 → 数据验证 → 序列/日期/自定义
3. 配合条件格式标出仍需检查的异常

注意：数据验证不是安全边界；粘贴操作仍可能绕过限制，因此还要保留异常检查列。

清除隐藏空格与不可见字符

肉眼相同的文本可能因为不间断空格或控制字符而无法匹配。

适用场景

VLOOKUP/XLOOKUP 明明存在却返回未找到，或去重后仍有“重复值”。

注意：清洗列确认无误后再粘贴为值；不要直接覆盖唯一的原始数据。

识别“看起来像数字”的文本

日期与数字只有真正存储为数值，排序、计算、分组和时间智能才可靠。

适用场景

求和结果为 0、日期无法按年月分组、数字排序出现 1、10、2。

注意：小数点与千分位受区域设置影响；导入时应明确区域，而不是反复改显示格式。

相对、绝对与混合引用

复制公式时，决定行列是否移动：A1 全移动，$A$1 全锁定，$A1/A$1 各锁一维。

适用场景

同一公式需要横向和纵向填充，或税率、汇率等参数位于固定单元格。

注意：编辑公式时按 F4 可循环切换引用方式；结构化引用通常比整列引用更易读。

SUMIFS / COUNTIFS 条件汇总

在多个条件同时成立时求和或计数，是日常报表最稳定的基础模式。

适用场景

按区域、产品、状态和日期区间汇总明细。

注意：SUMIFS 的第一个参数是求和列；所有条件列必须与它尺寸一致。

IF / IFS：先写清业务规则

把条件按优先级排列，并为未知或不合法状态保留明确出口。

适用场景

分档、状态转换、逾期判断或异常标记。

注意：嵌套条件过多时应改用规则表 + XLOOKUP，避免把业务逻辑藏进巨型公式。

LET：为中间结果命名

让长公式像程序一样分段，减少重复计算，也更容易调试。

适用场景

同一个表达式在公式中多次出现，或公式已经难以一眼读懂。

注意：命名应表达业务含义；用“公式求值”逐步确认每个变量，而不是用 IFERROR 掩盖问题。

文本与日期函数组合

用 TEXTBEFORE/TEXTAFTER/TEXTSPLIT 拆字段，用 EOMONTH/WORKDAY 处理周期。

适用场景

编码需要拆分，或计算月末、账期、工作日截止时间。

注意：日期运算保留数值结果，TEXT 只用于最终展示，否则会失去后续计算能力。

XLOOKUP 精确查找与缺省值

默认精确匹配、可以向左查找，并把“未找到”的处理写在同一个函数里。

适用场景

按唯一编号取回名称、价格、负责人或状态。

注意：先确认查找键是否唯一；如果重复，XLOOKUP 只返回首个匹配项。

多条件 XLOOKUP

将多个布尔条件相乘为 1，定位同时满足多项条件的记录。

适用场景

编号并非全局唯一，需要同时按编号、地区或日期版本查找。

注意：数据量很大时优先建立复合键或在 Power Query 中合并，避免大量重复数组计算。

INDEX + MATCH 的可组合查找

在需要兼容旧版本、二维定位或精细控制查找方向时仍然可靠。

适用场景

横纵二维交叉查询，或文件必须兼容没有 XLOOKUP 的环境。

注意：MATCH 的 0 表示精确匹配；近似匹配前必须确认数据排序和边界。

区间与近似匹配

把税率、佣金或等级阈值维护在独立规则表中，根据“下一个较小值”返回规则。

适用场景

成绩分档、阶梯价格、税率与运费区间。

注意：阈值列必须覆盖所有边界并按升序维护；用边界值测试每一档。

FILTER：用条件生成实时清单

一个公式返回可自动伸缩的多行多列结果，无须复制到每一行。

适用场景

生成某地区订单、逾期任务或符合条件的动态子表。

注意：溢出区域必须保持空白；不要把动态数组公式放进 Excel 表格的数据列中。

UNIQUE + SORT：自动维护维度清单

从不断增长的明细中提取不重复值并排序，适合作为下拉源或汇总轴。

适用场景

产品、地区、负责人等清单不想手工更新。

注意：跨工作簿动态数组通常要求源文件可访问；关键清单可考虑 Power Query。

TAKE / DROP / CHOOSECOLS 重塑结果

先筛选排序，再按行列裁切，直接构建 Top N 或轻量报表输出。

适用场景

只取最近记录、前十名或指定字段。

注意：复杂输出要写清列位置含义；源结构频繁变化时优先改用字段名或 Power Query。

数据透视表从整洁明细开始

每行一条事实、每列一个字段、标题唯一，才能稳定刷新和组合维度。

适用场景

快速按时间、区域、产品或负责人交叉汇总。

1. 源数据先转换为 Excel 表格
2. 插入 → 数据透视表
3. 字段放入行、列、值与筛选区域
4. 刷新后检查总计与原始明细

注意：不要直接以透视表作为新的原始数据源；保留可追溯的明细层。

值显示方式与时间分组

同一指标可显示占比、累计、同比差异或排名，不必在源数据中堆辅助列。

适用场景

需要月度趋势、构成占比、环比和累计贡献。

1. 右键日期 → 组合 → 年/月
2. 值字段设置 → 值显示方式
3. 需要同时看金额与占比时重复添加同一值字段

注意：分组失败通常意味着日期列含空白、错误或文本日期。

切片器、时间线与刷新控制

用可视化筛选器驱动多个透视表，并明确“数据 → 刷新全部”的更新路径。

适用场景

制作可交互仪表板，或让非公式用户自行切换观察维度。

1. 插入切片器或时间线
2. 报表连接中勾选共享同一缓存的透视表
3. 在交付说明中写明刷新步骤和数据截止时间

注意：切片器不是权限控制；敏感明细仍应在源头限制访问。

先按问题选择图表

趋势用折线，分类比较用条形，组成用堆积，关系用散点；不要让装饰抢走数据。

适用场景

将分析结论交付给他人，而不仅是展示一张“好看”的图。

1. 先写一句图表要回答的问题
2. 突出一个主序列，其余降噪
3. 标题直接写结论或观察范围
4. 补充单位、时间范围与数据来源

注意：饼图只适合少量互斥类别；三维图会扭曲视觉比例，通常应避免。

让图表跟随表格与筛选更新

以 Excel 表格、透视图或动态数组结果作为来源，减少手工调整系列范围。

适用场景

每周或每月追加数据，仪表板需要稳定复用。

1. 优先使用表格列作为系列
2. 复杂交互使用透视图 + 切片器
3. 动态数组来源可通过名称管理器暴露溢出范围

注意：图表刷新后检查空白、零值和日期轴；自动更新不等于自动正确。

图表交付前的可读性检查

确保颜色不是唯一编码，标签不重叠，打印与深浅主题下仍能看懂。

适用场景

面向汇报、论文、公开文章或需要无障碍阅读的场景。

1. 用直接标签替代远距离图例
2. 保证文本与背景对比
3. 为关键点添加数据标签而非全量标注
4. 给图表添加替代文字或旁边的文字结论

注意：先删掉不帮助理解的网格线、边框和渐变，再考虑增加视觉元素。

用 Power Query 固化导入与清洗

把“连接 → 变换 → 合并 → 加载”记录成可重复刷新的一条流程。

适用场景

每次都收到相同结构的新文件，手工清洗步骤重复且容易漏。

1. 数据 → 获取数据，连接原始文件/文件夹
2. 设置列类型并删除无关行列
3. 将步骤重命名为业务动作
4. 加载到表格、数据模型或仅创建连接

注意：查询会按步骤重放；不要只追求刷新成功，还要检查行数、唯一键和异常值。

追加与合并不要混淆

追加是纵向堆叠同结构记录；合并是按照键值横向补充字段。

适用场景

合并多月文件，或把订单表与产品主数据连接。

1. 多期同结构文件使用“追加查询”
2. 事实表关联维度表使用“合并查询”
3. 合并前检查键的类型、空格和唯一性
4. 展开后核对行数是否异常膨胀

注意：一对多或多对多合并会增加行数；这不是软件错误，而是关系基数的结果。

从文件夹批量合并周期文件

让新增文件自动进入同一清洗逻辑，避免每月复制粘贴。

适用场景

月报、日志、设备数据以多个 CSV 或工作簿持续到达。

1. 确保样本文件列结构稳定
2. 从文件夹连接并筛选扩展名/临时文件
3. 在“转换示例文件”中维护公共步骤
4. 刷新前记录文件数与预期行数

注意：不要把输出文件放回同一个输入文件夹，否则可能形成自我导入。

参数化路径、日期与环境

将文件路径、截止日期或环境地址变成参数，使查询可迁移、可审计。

适用场景

同一模板在不同电脑、年份或测试/生产数据源间复用。

1. 创建有明确类型的参数
2. 用参数替换硬编码路径或日期
3. 增加参数说明与默认值
4. 交付前在另一环境执行一次完整刷新

注意：凭据和隐私级别应在数据源设置中管理，不要写进查询文本或单元格。

先判断是否真的需要宏

公式适合实时计算，Power Query 适合可刷新清洗，宏/Office Scripts 适合操作流程自动化。

适用场景

你准备录制宏之前，先确认问题属于计算、数据处理还是界面操作。

1. 结果应随输入实时变化：公式
2. 重复导入与变换：Power Query
3. 跨对象点击、导出、格式化：VBA 或 Office Scripts

注意：选择最简单且可维护的工具；宏并不天然比公式“高级”。

编写可重复执行的 VBA

明确工作簿和工作表对象，避免 Select/Activate，并保证重复运行不会破坏结果。

适用场景

自动生成报表、批量导出、清理格式或处理桌面版 Excel 对象。

注意：这段代码应粘贴到 VBA 编辑器；运行未知宏前先检查源码并备份，不要默认启用来自网络的宏文件。

宏性能与失败恢复

批量读写数组、暂时关闭屏幕刷新，并在异常出口恢复应用状态。

适用场景

宏逐单元格运行缓慢，或报错后 Excel 保持手动计算/无刷新状态。

1. 一次性读入 Range.Value2 到数组
2. 在内存中循环计算
3. 一次性写回结果
4. 错误处理块中恢复 Calculation、EnableEvents、ScreenUpdating

注意：优化前先测量瓶颈；关闭应用状态后必须保证成功与失败路径都能恢复。

把工作簿拆成四层

RAW 保存原始输入，CLEAN 负责转换，MODEL 负责关系与指标，REPORT 只负责呈现。

适用场景

工作簿开始承担长期报表、分析模型或多人协作。

1. RAW：只导入不手改
2. CLEAN：清洗并记录规则
3. MODEL：维度、事实和指标
4. REPORT：透视表、图表和决策摘要

注意：层与层之间单向流动；避免报表单元格反过来充当源数据。

事实表与维度表的星型模型

交易明细放事实表，产品、客户、日期等描述放维度表，通过唯一键建立一对多关系。

适用场景

Power Pivot/数据模型要跨多表分析，或者传统查找公式已大量重复。

1. 确定事实表粒度：一行究竟代表什么
2. 为每个维度建立唯一键
3. 检查事实表外键是否存在孤儿值
4. 从维度表筛选事实表，避免双向关系滥用

注意：关系建立前先验证唯一性；多对多通常意味着模型粒度还没有想清楚。

用度量值表达业务指标

在数据模型中把收入、毛利率、同比等定义为统一度量值，随筛选上下文计算。

适用场景

多个透视表需要复用同一个指标，且结果应随切片器正确变化。

注意：这段代码应在数据模型中创建度量值，不是普通单元格公式；先定义基础度量值再组合。

从错误类型反推原因

#N/A 多为未匹配，#VALUE! 多为类型不兼容，#REF! 是引用失效，#DIV/0! 是分母为空或零。

适用场景

公式出错时先定位根因，而不是立即套 IFERROR。

1. 检查输入值与数据类型
2. 选中公式使用“公式求值”
3. 追踪引用单元格
4. 确认错误是预期缺省还是数据质量问题

注意：IFERROR 只应处理可预期的缺省状态；掩盖所有错误会让真正的问题静默扩散。

#SPILL! 动态数组无法溢出

公式需要返回多格结果，但目标区域被数据、合并单元格、表格边界或不确定尺寸阻挡。

适用场景

FILTER、UNIQUE、SORT 等动态数组函数显示 #SPILL!。

1. 点击错误标记查看预期溢出范围
2. 清除阻挡内容或取消合并
3. 把公式移到 Excel 表格外
4. 避免引用整列造成超大结果

注意：只清除真正的阻挡单元格，先确认其中没有需要保留的数据。

循环引用与迭代计算

公式直接或间接引用自身；大多数情况是模型结构错误，而非应该打开迭代计算。

适用场景

状态栏提示循环引用，结果为 0 或反复变化。

1. 公式 → 错误检查 → 循环引用
2. 沿前导/从属单元格找到回路
3. 拆分输入、计算与输出
4. 只有明确的数值迭代模型才设置收敛条件

注意：开启迭代计算会影响整个工作簿；必须记录最大迭代次数、误差阈值与验证方法。