Excel 趋势异常预测：让 AI 分析数据找规律、标异常值、做同比环比，零函数基础

一、为什么"零函数基础"反而可能是优势

先讲一个反直觉的观察。在笔者接触过的数据分析培训场景里，函数熟练度与结论质量之间并不存在稳定的正相关。会写 =IF(AND(...)) 嵌套的人，往往倾向于"把能算的都算出来"，然后在几十列中间结果里迷失；而完全不会函数的人，被迫先想清楚"我到底要回答什么问题"，反而更容易抓住主线。

这个现象在 AI 辅助分析时代被进一步放大。当模型可以替你完成公式编写、透视表搭建、图表绘制时，稀缺资源从"操作技能"转移到了"问题定义能力"。换句话说，过去卡住你的是"怎么做"，现在卡住你的是"做什么、为什么做、做完信不信"。

1.1 分析代理范式的三次跃迁

要理解今天发生了什么，需要把时间轴拉长一点。数据分析工具的演进大致经历了三个阶段，本文评述认为，这三个阶段的划分标准不是"功能多少"，而是"谁承担了认知负荷"。

阶段	代表工具	认知负荷承担者	典型瓶颈
公式时代	Excel 函数、VBA	人（需掌握语法）	语法门槛、维护困难
可视化时代	Tableau、Power BI	人（需建模思维）	数据建模学习曲线陡
代理时代	LLM + 代码解释器	模型（人负责定义与质疑）	幻觉、口径漂移、可复现性

2023 年以来，OpenAI 的 Advanced Data Analysis（原 Code Interpreter）、Anthropic 的 Claude 分析工具、以及国内的通义千问、Kimi、智谱清言等产品陆续支持"上传表格—自然语言提问—模型写代码—返回结果"的闭环。这类系统的本质是把 Python 数据分析栈（pandas、statsmodels、scikit-learn）封装在对话界面之后，用户看到的是回答，背后跑的是代码。

本文评述：把这类系统称为"AI 分析"容易产生误解，更准确的定位是"代码生成 + 沙箱执行"。它不会凭空产生洞察，只是把"写代码"这一步自动化了。因此，凡是能用清晰语言描述的分析任务，它都能做；凡是需要业务语境才能判断的任务，它都做不好。这条边界，是全文所有操作建议的出发点。

1.2 本文的分析主线

为避免文章变成工具说明书的大杂烩，这里先明确贯穿全文的一条主线：

主线：把"趋势异常预测"视为一个"假设—检验—复现"的闭环，AI 负责闭环中所有可自动化的执行环节，人负责假设的提出与检验的裁决。

这条主线会反复出现在后续每一节：讲数据清洗时，它体现为"清洗规则必须可复现"；讲异常检测时，它体现为"异常判定阈值必须由业务假设驱动"；讲同比环比时，它体现为"口径必须显式声明"；讲预测时，它体现为"预测区间比点估计更重要"。

二、趋势、异常、同比环比：先把概念钉死

概念不清是 AI 分析翻车的头号原因。模型不会主动纠正你的模糊表述，它会按最"常见"的理解去执行，而"常见"未必是你要的。这一节把三个核心概念拆开讲透。

2.1 趋势不等于"上升"或"下降"

在时间序列分析中，一个观测序列通常被分解为四个成分：趋势项（Trend）、季节项（Seasonal）、循环项（Cyclic）、随机项（Residual/Irregular）。这个经典分解框架可追溯到 1920 年代，后来由 Box、Jenkins 等人在《Time Series Analysis: Forecasting and Control》（1970 年初版，后续多次修订）中系统化。

日常语境里的"趋势"，往往把季节波动也算进去了。比如某电商月度销售额 1 月低、11 月高，这是季节性，不是趋势。如果 AI 把季节性误判为趋势，就会给出"业务持续向好"的错误结论。

笔者认为：对业务读者而言，最实用的区分方法是问一句"这个变化明年还会重复吗"。会重复的是季节，不会重复但方向稳定的是趋势，两者都不属于的是噪声。这个问题不需要任何统计学背景，却能过滤掉大部分误判。

2.2 异常值的三种类型

异常检测文献通常区分三类异常，这个分类在 Chandola、Banerjee、Kumar 发表于《ACM Computing Surveys》2009 年的综述《Anomaly Detection: A Survey》中有系统梳理，至今仍是该领域引用量最高的文献之一。

类型	特征	业务例子	检测难度
点异常	单个数据点偏离	某日订单量骤降	低
上下文异常	在特定语境下才异常	冬季空调销量高属正常	中
集体异常	一组点整体偏离	连续一周转化率偏低	高

绝大多数"AI 标异常"的失败案例，都发生在上下文异常与集体异常上。因为这两类异常无法仅凭数值分布判断，必须引入时间上下文或业务规则。模型默认只会做点异常检测，需要你在提示词里显式要求。

2.3 同比与环比：差一个字，差一个季节

这两个概念在国内业务语境中高频出现，但口径混乱程度惊人。

• 同比（YoY）：与去年同期比，如 2024 年 3 月 vs 2023 年 3 月。作用是剔除季节性。
• 环比（MoM/QoQ）：与上一个相邻周期比，如 2024 年 3 月 vs 2024 年 2 月。作用是捕捉近期变化。
• 定基比：与某个固定基期比，如全部月份 vs 2020 年 1 月。作用是看长期累积。

这里有一个极易被忽略的坑：同比的计算前提是数据具备完整的年度周期。如果数据只有 14 个月，那么只有后 2 个月能算同比，模型如果"贴心"地给所有月份都算了同比，结果一定是错的。这个问题在下一节会给出具体的校验方法。

三、数据体检：AI 分析前的"洗菜"环节

把原始表格直接丢给 AI，然后抱怨结果不准，是新手最常见的操作。数据体检这一步，决定了后续所有分析的可信度上限。笔者把它总结为"四查"：查结构、查缺失、查重复、查口径。

3.1 四查清单与 AI 提示词

以下提示词可直接复制使用（假设文件名为 sales.csv）：

请对上传的 sales.csv 做数据体检，输出以下四部分：
1. 结构：每列的名称、数据类型、非空数量、唯一值数量；
2. 缺失：每列缺失比例，若某列缺失超过 30%，单独标出；
3. 重复：完全重复的行数，以及按"订单号"列判断的重复行数；
4. 口径：日期列的取值范围（最小/最大），是否存在跨年；
   数值列是否存在负数、零值、极端大值（超过均值 10 倍）。
请用表格输出，不要做任何清洗，只做诊断。

注意最后一句"不要做任何清洗，只做诊断"。这是关键控制点。模型有强烈的"帮忙"倾向，如果你不说，它会顺手把缺失值填了、把异常值删了，而你可能永远不知道它动过什么。

本文评述："只诊断、不清洗"应当成为与 AI 协作的默认约定。清洗是不可逆操作，一旦模型自作主张，后续所有结论都建立在未知的修改之上。把清洗拆成独立步骤，每一步都要求模型说明"做了什么、为什么、影响多少行"，这是保证可复现性的最低成本手段。

3.2 日期列：最容易被忽视的重灾区

Excel 中的日期存储格式极其混乱。同一个文件里可能同时存在 2024-03-01、2024/3/1、44927（Excel 序列号）、3月1日 五种写法。如果模型把日期当字符串处理，排序和周期计算会全盘崩溃。

建议在提示词中明确要求：

请检查日期列能否被解析为标准日期类型。
如果不能，请列出所有无法解析的原始值及其行号，
并给出建议的解析规则（不要直接修改数据）。

3.3 缺失值处理：三种策略的适用场景

策略	方法	适用场景	风险
删除	整行删除	缺失比例极低（<5%）	可能引入选择偏差
填充	均值/中位数/前值	缺失随机、比例中等	低估方差、扭曲分布
标记	新增缺失指示列	缺失本身有信息量	增加维度，需模型支持

时间序列场景下，笔者更推荐"标记 + 插值"组合：先用前向填充（forward fill）保持时间连续性，同时新增一列标记哪些点是插值出来的。这样后续做异常检测时，可以主动排除插值点，避免把人工构造的数据当成真实观测。

四、让 AI 找规律：趋势分解的对话式实现

数据干净之后，进入核心环节：找规律。这一节的目标不是让 AI"告诉你趋势是什么"，而是让它把趋势分解的中间结果摊开给你看，由你来判断。

4.1 移动平均：最朴素也最稳的起点

移动平均（Moving Average）是趋势提取的入门方法。它的逻辑是：用最近 N 期的平均值代表当前水平，从而抹平短期波动。窗口 N 的选择直接决定平滑程度——N 越大越平滑，但对转折点的响应越迟钝。

请对 sales.csv 的"销售额"列分别计算 3 期、7 期、30 期移动平均，
在同一张折线图上叠加原始数据与三条均线。
图例标注清楚，x 轴为日期，y 轴为销售额。
最后用一段话说明：三条均线在哪些时间点给出了不同的方向信号。

最后那句"在哪些时间点给出了不同的方向信号"是刻意设计的。它强迫模型做对比分析，而不是简单画图。不同窗口的均线出现分歧的位置，往往就是趋势可能发生变化的候选点。

4.2 STL 分解：把季节和趋势分开

当数据存在明显季节性时，移动平均会把季节波动混进趋势里。此时需要 STL 分解（Seasonal-Trend decomposition using Loess），该方法由 Cleveland 等人于 1990 年发表在《Journal of Official Statistics》，是当前应用最广的季节-趋势分解方法之一。

STL 的核心优势是鲁棒性：它允许季节成分随时间缓慢变化，并且对异常值不敏感。对于零售、电商、能源等强季节性行业，STL 几乎是标配。

请对销售额序列做 STL 分解（周期设为 12，若为月度数据），
输出四张子图：原始序列、趋势项、季节项、残差项。
然后回答三个问题：
1. 趋势项整体方向是上升、下降还是平稳？
2. 季节项的峰值出现在哪几个月？振幅大约是多少？
3. 残差项中绝对值最大的 5 个点分别在哪？

本文评述：STL 分解最大的价值不是"得到趋势"，而是"得到残差"。残差项是剔除了趋势和季节之后剩下的部分，它才是异常检测真正应该盯住的对象。很多团队直接在原始序列上做异常检测，结果每年双十一都被标成异常——这不是数据的问题，是方法的问题。

4.3 趋势强度的量化

Hyndman 与 Athanasopoulos 在《Forecasting: Principles and Practice》（第 3 版，2021 年在线发布）中给出了趋势强度与季节强度的量化公式，思路是用方差比来衡量：

趋势强度 = max(0, 1 − Var(残差) / Var(残差 + 趋势))
季节强度 = max(0, 1 − Var(残差) / Var(残差 + 季节))

取值在 0 到 1 之间，越接近 1 说明该成分越强。这个指标的好处是把"趋势明显吗"这种主观判断变成了可比较的数字。建议在提示词中直接要求模型输出这两个值。

五、异常值标注：从 3σ 到 STL 残差的实战选择

异常检测方法众多，从简单的标准差法到复杂的孤立森林、自编码器。对零函数基础的读者，笔者建议按"数据特征"而非"方法先进程度"来选择。

5.1 四种方法的适用边界

方法	原理	适用	不适用
3σ 法则	偏离均值 3 倍标准差	近似正态、无季节	强季节、偏态分布
IQR 法	超出四分位距 1.5 倍	偏态分布	小样本
STL 残差法	残差偏离阈值	强季节时间序列	数据点过少
孤立森林	随机切分隔离异常点	多维、大样本	可解释性要求高

孤立森林由 Liu、Ting、Zhou 于 2008 年提出（发表于 ICDM 2008），其核心思想是异常点更容易被随机切分隔离。它在多维数据上表现优异，但单变量时间序列场景下，STL 残差法往往更直观、更易解释。

5.2 阈值不是魔法数字

3σ 中的"3"、IQR 中的"1.5"，都是经验值，不是定理。它们来自特定分布假设下的概率计算（正态分布下 3σ 之外约 0.27%），但真实业务数据很少严格正态。

笔者认为：阈值的选择应该由"你能承受多少误报"决定，而不是由教科书决定。如果异常需要人工逐一核查，阈值就应设高一些（减少误报）；如果异常只是用于触发自动告警，阈值可以设低一些（减少漏报）。这个权衡是业务问题，模型无法替你做。

实操建议：让 AI 同时输出多个阈值下的异常点数量，形成一张"阈值-数量"对照表，再由你决定用哪个。

请基于 STL 分解的残差项，分别用 2σ、2.5σ、3σ、3.5σ 四个阈值
标注异常点，输出一张对照表：阈值、异常点数量、占比、具体日期列表。
不要替我选择阈值，只需呈现差异。

5.3 异常标注的输出规范

一个合格的异常标注结果，至少应包含五列：日期、原始值、预期值（趋势+季节）、残差、是否异常。这样业务方才能理解"为什么这个点被标为异常"。

如果模型只返回一个"异常"标签，务必追问："请补充预期值和残差两列，并说明预期值是如何计算的。"这是把黑箱变成白箱的关键一步。

六、同比与环比：AI 最容易算错的地方

这一节单独成章，因为它是笔者见过出错率最高的环节，而且错误往往很隐蔽。

6.1 三个高频错误

• 错误一：数据不足却强行算同比。只有 14 个月数据，模型可能用"第 13 个月 vs 第 1 个月"充当同比，逻辑上完全错误。
• 错误二：跨年边界处理错误。2024 年 1 月的同比对象应是 2023 年 1 月，但若数据从 2023 年 3 月开始，则 2024 年 1 月、2 月没有同比对象，应显示为空而非 0。
• 错误三：增长率与增长量混淆。"同比增长 20%"和"同比增长 20 万"是两回事，模型有时会在同一张表里混用。

6.2 一个可靠的提示词模板

请为 sales.csv 计算同比和环比，要求：
1. 先输出日期列的完整范围，确认跨越几个自然年；
2. 同比 = 本期 / 去年同期 - 1，仅当去年同期存在时才计算，
   否则该单元格留空并注明"无同期数据"；
3. 环比 = 本期 / 上期 - 1，仅当上期存在时计算；
4. 同时输出增长量（绝对值）和增长率（百分比），分两列；
5. 最后单独列出所有"无法计算同比"的月份及原因。

第 5 条是精髓。它强迫模型显式暴露数据缺口，而不是用某种"聪明"的方式填补。很多时候，业务方看到同比表里某几个月是空的，才会意识到"哦，我们的数据只到去年 3 月"。

6.3 累计同比与单月同比

国家统计局等机构发布的月度数据，通常同时提供"当月同比"和"累计同比"。两者含义不同：当月同比反映单月表现，累计同比反映年初至今的整体表现。当某月数据波动剧烈时，累计同比会更平稳。

建议在分析报告中同时呈现两者，并说明差异。如果只给一个，务必标注清楚是哪一个。

七、趋势外推与预测：别让模型替你许愿

预测是 AI 最容易被过度信任的环节。模型可以轻松画出一条平滑的未来曲线，但那条曲线的可信度，取决于方法假设与数据质量，而非曲线的美观程度。

7.1 从简单到复杂的方法阶梯

方法	假设	数据要求	适用期长
朴素法	下期等于本期	无	1 期
季节朴素法	下期等于去年同期	≥2 年	1 年
指数平滑	近期权重更高	≥2 年	1-2 年
ARIMA	自相关结构稳定	≥3 年	1-3 年

Hyndman 与 Athanasopoulos 反复强调一个观点：在多数业务场景下，简单方法的预测精度不输复杂模型。他们组织的 M 系列预测竞赛（M1 至 M4，以及后续的 M5）结果也支持这一判断——复杂模型的边际收益往往被其实现成本和维护难度抵消。

7.2 预测区间比点估计更重要

"下个月销售额预计 120 万"这句话的信息量，远小于"下个月销售额预计 120 万，80% 置信区间为 105 万至 138 万"。前者给你一个数字，后者给你一个决策空间。

请用 Holt-Winters 季节模型预测未来 6 个月销售额，
输出点预测值、80% 预测区间、95% 预测区间。
同时用朴素法和季节朴素法做基准对比，
计算三种方法在历史数据上的 MAPE（平均绝对百分比误差）。
不要只给结论，把误差指标列出来。

要求模型输出 MAPE 等误差指标，是判断预测可信度的直接手段。如果 MAPE 超过 20%，说明预测精度有限，报告中应明确提示。

7.3 回测：预测的照妖镜

回测（Backtesting）是评估预测方法的标准做法：用历史数据训练，预测已知的后续数据，比较预测值与真实值。这一步在 AI 辅助流程中极易被跳过，因为模型默认只做"向前预测"，不做"向后验证"。

务必在提示词中显式要求回测，并指定回测窗口。例如："请用 2022 年 1 月至 2023 年 12 月的数据训练，预测 2024 年 1 月至 6 月，与实际值对比，输出误差表。"

八、六步操作路径：一份可照抄的工作流

把前面各节的方法串起来，形成一条完整的工作流。这条路径的设计原则是：每一步都有明确的输入、输出和验收标准，任何一步出问题都能定位。

步骤	动作	输出物	验收标准
1	数据体检	诊断报告	日期范围、缺失率、重复数清晰
2	清洗确认	清洗日志	每步改动行数可追溯
3	趋势分解	分解图 + 强度值	趋势/季节强度可量化
4	异常标注	异常清单	含预期值与残差
5	同比环比	对比表	缺口显式标注
6	预测回测	预测区间 + 误差	MAPE 与基准对比

8.1 每一步的对话示例

以步骤 3 为例，完整的对话可能是这样的：

【用户】请对清洗后的销售额做 STL 分解，周期 12，
输出分解图，并计算趋势强度和季节强度。

【AI】（返回图表和数值）

【用户】趋势强度 0.72，季节强度 0.85，
请解释这两个数值的含义，以及它们对后续异常检测的影响。

【AI】（解释）

【用户】基于这个分解结果，残差项用什么阈值标异常比较合适？
请给出 2σ 到 3.5σ 的对照表。

注意这种"追问式"的交互模式。不要期待一次提问就得到完整答案，而是把分析过程拆成多轮对话，每轮聚焦一个问题。这样既能控制模型的输出质量，也便于你逐步建立对数据的理解。

九、提示词工程：把需求写成模型能执行的任务

与 AI 协作分析数据，提示词的质量直接决定结果质量。这一节给出几条经过验证的原则。

9.1 四要素结构

一条好的分析提示词，通常包含四个要素：

• 数据说明：文件是什么、每列什么意思、时间范围。
• 任务定义：要算什么、用什么方法、输出什么格式。
• 约束条件：不要做什么、遇到缺失怎么办、阈值怎么定。
• 验收要求：如何证明结果可靠、需要哪些中间量。

很多人的提示词只有第二项，结果就是模型自由发挥，输出看似合理但无法验证的结果。

9.2 三个反直觉技巧

技巧一：要求模型先复述任务。在提示词末尾加一句"在开始分析前，请先用一句话复述你理解的任务"。如果复述有偏差，立刻纠正，避免它跑完整个流程才发现方向错了。

技巧二：要求模型暴露中间步骤。"请输出你使用的代码"或"请说明每一步的计算逻辑"。这不仅能验证正确性，还能让你在下次遇到类似问题时复用。

技巧三：要求模型给出不确定性。"请说明这个结论有哪些前提假设"或"哪些情况下这个结论会不成立"。这会显著降低被误导的概率。

9.3 常见提示词陷阱

陷阱	问题	改进写法
"分析一下这个数据"	任务过于宽泛	明确要回答的具体问题
"找出所有异常"	未定义异常标准	指定方法与阈值
"预测未来"	未指定期数与区间	明确期数、置信水平、基准

十、结果校验：AI 说"异常"就真的异常吗

模型输出的结论，必须经过校验才能进入决策流程。这一节给出三类校验方法。

10.1 数值校验：抽样手算

随机抽取 3-5 个数据点，用计算器或 Excel 手工验证。重点验证：同比环比的计算、异常点的残差、预测值的区间边界。这一步看似笨拙，却是发现系统性错误最有效的方法。

10.2 逻辑校验：异常是否有业务解释

把 AI 标出的异常点交给业务方，问一句"这几天发生了什么"。如果每个异常都能对应到具体事件（促销、故障、政策变化），说明检测有效；如果大部分异常无法解释，可能是阈值过松或方法不当。

本文评述：业务解释不是"事后找理由"，而是异常检测的必要环节。统计学上的异常与业务上的异常是两个概念：前者是数值偏离，后者是值得行动的信号。只有两者重合，异常检测才产生价值。

10.3 复现校验：换一种方法是否得到相似结论

用不同方法做同一件事，看结论是否一致。比如异常检测同时用 STL 残差法和 IQR 法，如果两者标出的异常点高度重合，结论可信度就高；如果差异巨大，说明数据特征复杂，需要谨慎解读。

十一、工具选型与合规边界

11.1 主流工具对比

工具	优势	局限	适用场景
ChatGPT 数据分析	代码执行稳定、图表丰富	数据需上传、有隐私顾虑	公开数据、脱敏数据
Claude 分析工具	长上下文、解释细致	执行环境受限	复杂逻辑推理
国产大模型	中文语境好、合规性强	代码能力参差	国内企业内网部署
本地部署方案	数据不出内网	硬件成本、维护成本	敏感数据、金融医疗

11.2 数据合规的三条红线

• 个人信息：包含姓名、身份证、手机号的数据，上传前必须脱敏或使用本地部署方案。
• 商业秘密：客户名单、成本结构、未公开财务数据，需评估泄露风险。
• 行业监管：金融、医疗、政务数据受专门法规约束，使用前应咨询合规部门。

一个实用的做法是：先在脱敏样本上跑通流程，确认方法有效后，再在合规环境中处理完整数据。

十二、前沿方向与开放问题

这一节讨论几个正在演进的方向，供有兴趣的读者深入。

12.1 时序基础模型

2023 年以来，时间序列领域出现了"基础模型"（Foundation Model）趋势。代表性工作包括 Google 的 TimesFM、Amazon 的 Chronos、Salesforce 的 Moirai 等。这些模型在大规模时序语料上预训练，目标是实现"零样本预测"——给定一段历史序列，直接输出未来值，无需针对具体任务训练。

笔者认为：时序基础模型的价值目前主要体现在"冷启动"场景——新业务没有历史数据积累时，可以用预训练模型给出初步预测。但在有充足历史数据的成熟业务中，传统统计方法（如 ETS、ARIMA）配合业务规则，往往更可控、更易解释。基础模型不会取代统计方法，而是补充了一个新的选项。

12.2 LLM 作为分析代理的可靠性

把 LLM 用作数据分析代理，核心挑战是可靠性。模型可能生成语法正确但逻辑错误的代码，也可能在解释结果时产生幻觉。当前的研究方向包括：代码执行的沙箱隔离、结果的自洽性检查、多模型交叉验证等。

对实践者而言，可行的做法是建立"检查清单"：每次分析后，逐项核对数据范围、计算逻辑、异常判定、结论表述。清单化能显著降低遗漏风险。

12