使用 AI 编写 Excel 公式

AI 如何理解 Excel 公式请求

当今的 AI 工具（如 ChatGPT、Claude 和 GitHub Copilot）可以在你清晰描述数据布局和期望结果后，生成准确的 Excel 公式。关键是要提供充分的上下文：列字母、数据范围以及你期望的输出。例如，不要说"给我一个查找公式"，而应该说"我在 Sheet1 的 A 列有员工 ID，Sheet2 的 A 列有姓名，B 列有薪资。我需要将每个员工的薪资提取到 Sheet1 的 C 列。"你的提示越具体，第一次就得到正确公式的几率就越高。

AI 模型在数百万个 Excel 公式示例上训练过，这些示例来自文档、论坛和教程。它们理解数百个函数的语法，并能将它们组合成嵌套公式——这些公式如果由人工构建和调试，可能需要数分钟。然而，AI 依赖你提供结构上下文——行号、工作表名称和数据类型——因为它无法看到你的实际电子表格。

AI 擅长的核心公式类型

AI 工具在以下公式类别中表现尤为出色：

• 查找函数：VLOOKUP、XLOOKUP、INDEX-MATCH 组合
• 条件聚合：SUMIFS、COUNTIFS、AVERAGEIFS、MAXIFS
• 文本处理：TEXTJOIN、LEFT/RIGHT/MID、SUBSTITUTE、正则表达式模式
• 日期和时间运算：NETWORKDAYS、EOMONTH、DATEDIF、WORKDAY
• 逻辑嵌套：嵌套 IF、IFS、SWITCH、AND/OR 组合
• 动态数组：FILTER、SORT、UNIQUE、SEQUENCE、LAMBDA
• 财务计算：XNPV、XIRR、PMT、FV、NPV

真实公式示例及 AI 提示词

示例 1：使用 INDEX-MATCH-MATCH 进行双向查找

提示词："我有一个销售表，行（A3:A12）是产品名称，列（B2:E2）是季度（Q1-Q4）。我需要一个公式，根据在 G1 中输入的产品和在 H1 中输入的季度来查找销售额。请给我 INDEX-MATCH-MATCH。"

AI 生成的公式：

=INDEX(B3:E12, MATCH(G1, A3:A12, 0), MATCH(H1, B2:E2, 0))

此公式使用第一个 MATCH 查找 G1 中产品在 A3:A12 中的行位置，第二个 MATCH 查找 H1 中季度在 B2:E2 中的列位置，然后 INDEX 返回该交叉点的值。

示例 2：带日期范围的多条件 SUMIFS

提示词："A 列是日期，B 列是产品类别，C 列是销售金额。我需要汇总 2026 年 1 月 1 日至 2026 年 3 月 31 日期间'Electronics'类别的销售额。数据从第 2 行到第 500 行。"

AI 生成的公式：

=SUMIFS(C2:C500, B2:B500, "Electronics", A2:A500, ">=1/1/2026", A2:A500, "<=3/31/2026")

SUMIFS 函数同时评估三个条件：类别必须匹配"Electronics"，且日期必须在 2026 年 Q1 范围内。每个条件对遵循（条件范围, 条件）模式。

示例 3：按百分比档位的嵌套 IF

提示词："D 列是销售数据。我需要分配佣金档位：0-5000 得 5%，5001-10000 得 7%，10001-20000 得 10%，20000 以上得 12%。为第 2 行写公式。"

AI 生成的公式：

=IF(D2<=5000, D2*0.05, IF(D2<=10000, D2*0.07, IF(D2<=20000, D2*0.10, D2*0.12)))

对于 Excel 2019 及更新版本，AI 可能会建议更简洁的 IFS 替代方案：

=IFS(D2<=5000, D2*0.05, D2<=10000, D2*0.07, D2<=20000, D2*0.10, TRUE, D2*0.12)

示例 4：多条件 FILTER（动态数组）

提示词："我的数据在 A2:D200 中，表头为：Name、Department、Salary、Location。我需要筛选并显示所有 Department 为 'Engineering' 且 Salary 大于 80000 的行。使用 FILTER 函数。"

AI 生成的公式：

=FILTER(A2:D200, (B2:B200="Engineering")*(C2:C200>80000), "没有匹配记录")

乘法在 FILTER 函数中充当逻辑 AND 运算符——每个 TRUE 求值为 1，只有两个条件都为 TRUE 的行（1*1=1）才能通过筛选。

Excel 公式的提示工程

从 AI 获取可靠的公式需要结构化的提示。以下是一个经过验证的模板：

提示词模板：
"我使用的是 [Excel 版本，如 Excel 365]。我的数据结构如下：
- [A 列表头]：[描述、数据类型、示例值]
- [B 列表头]：[描述、数据类型、示例值]
我需要一个公式来 [具体结果]。公式应放在 [目标单元格/列]。附加约束：[处理空白、区分大小写等]"

提高 AI 公式准确率的关键技巧：

1. 指定 Excel 版本：Excel 365 支持动态数组（FILTER、SORT、UNIQUE）和 LAMBDA；旧版本需要 Ctrl+Shift+Enter 的传统数组公式。
2. 提供精确的单元格范围：将"我的销售数据"替换为"A2:A500，命名为 SalesData"。
3. 提及边界情况：告诉 AI 如何处理空白单元格、错误、重复值或零值。
4. 要求备选方案：要求"给我两种方法"来比较 VLOOKUP 与 INDEX-MATCH 或 SUMIFS 与 SUMPRODUCT。
5. 要求解释：加上"请逐步解释这个公式是如何工作的"，既能帮助学习，也能验证正确性。

常见陷阱及验证 AI 生成公式的方法

AI 生成的公式并非万无一失。请注意以下常见问题：

• VLOOKUP 列索引错误：当 table_array 从 A 以外的列开始时，AI 可能会算错查找列号。务必验证 col_index_num。
• 绝对引用与相对引用：AI 有时会错误地使用相对引用（$A1 vs A$1 vs A1）来适配你的拖动场景。在复制公式前检查美元符号。
• 日期格式歧义：AI 可能假设美国日期格式（MM/DD/YYYY）。如果你的区域设置不同，公式中的日期可能出错。
• 数组公式兼容性：AI 可能生成在你的 Excel 版本中无法使用的动态数组公式。
• 差一范围错误：表头行被包含在数据范围中会导致匹配错误。

验证清单：

1. 将公式复制到电子表格中，用 3-5 个已知值进行测试。
2. 检查边界情况：空白单元格、最大/最小值、数值列中的文本。
3. 使用公式审核（公式选项卡 > 公式求值）逐步演算。
4. 至少对一行数据进行手动计算比对。
5. 如果公式返回错误，向 AI 提问："此公式返回了 #N/A。我的数据范围是 A2:B50。可能是什么问题？"

构建个人 AI 公式库

当你积累了适合自己数据集的 AI 生成公式后，将它们组织成一个可复用的库。创建一个 Excel 工作簿，为每个公式类别建立单独的工作表：查找、文本、日期、条件、财务。在每个工作表中包含以下列：原始提示词、生成的公式、功能的简明描述以及测试后的修改说明。

在团队环境中，使用 Excel 的 LAMBDA 函数（Excel 365）将复杂的 AI 生成公式打包为命名的可复用自定义函数。例如，将上述 INDEX-MATCH-MATCH 逻辑封装为 LAMBDA，只需定义一次即可在任何工作簿的任何单元格中调用：

=LAMBDA(lookup_val, row_header, col_header, data_range, row_range, col_range, INDEX(data_range, MATCH(lookup_val, row_range, 0), MATCH(col_header, col_range, 0)))

在名称管理器中将其命名为"TwoWayLookup"，你的整个团队都可以使用 =TwoWayLookup(G1, H1, B3:E12, A3:A12, B2:E2)，而无需理解底层的 INDEX-MATCH 机制。

AI 的局限及应对策略

AI 在处理依赖于其无法感知的视觉布局线索的公式时会遇到困难——合并单元格、隐藏行、条件格式规则或数据验证约束。它也无法引用外部工作簿或处理易变的实时数据（股票价格、API 数据源）。在这些情况下：

• 对于合并单元格的布局，在请求 AI 提供公式之前先取消合并并重新组织。
• 对于外部数据连接，在提示词中描述连接结构。
• 对于极其复杂的多步骤逻辑（10 个以上的嵌套条件），先让 AI 将问题分解为辅助列，然后再合并。
• 如果 AI 反复失败，分享错误信息并让它调试自己的输出——这种迭代方法通常能在 2-3 轮对话内解决问题。