【表格】AI 写复杂函数与数组公式
摘要：用 AI 帮你写出 XLOOKUP、动态数组等复杂公式，照抄就能用。
一、痛点引入
你有没有过这种经历：老板甩来一张表，让你"把每个客户对应的区域价格拉出来"，你脑子里立刻冒出 VLOOKUP，还要再套一层 IFERROR 防报错，结果公式写到第三层就把自己绕晕了，一按回车满屏 #N/A。
说实话，复杂 Excel 公式最大的门槛不是"难"，而是"记不住参数"——函数名一长串，括号一嵌套，光标都不知道往哪放。很多人最后干脆去论坛复制一段公式，黏上去才发现引用的是别人的表，改都没法改。
现在有个省心的办法：把这件事交给 AI 写公式。主推用网页版 AI——智谱清言（GLM，chatglm.cn）、Kimi（kimi.com）、通义千问（tongyi.aliyun.com） 打开网页就能用，不挑你本机 Excel 版本，你把表结构和需求粘进去，它直接吐出能黏进单元格的公式。Excel 自带的 Copilot 也能干同样的事，但前提是你们单位已开通 Microsoft 365 Copilot 许可——它不是默认就有的，没开通在 Excel 里连按钮都找不到；若你已开通，把同样的提示词丢进它的对话框也通用。本文以网页版 AI 为主演示，每个公式都配了真实示例数据，你黏过去一算就知道对不对。
二、目标产出
读完后，你能用 AI 一口气搞定下面四类"硬核"公式，并且每个都附了示例数据和预期结果，你黏过去一算就能验证：
- 多层查找：用 XLOOKUP（Excel 365 / 2021 之后用来"按关键字查值"的新函数，比老 VLOOKUP 更聪明）做"产品 × 区域"这种二维查表。
- 容错：用 IFERROR（"算错就显示别的"的兜底函数）把刺眼的 #N/A 换成友好提示。
- 文本拼接：用 TEXTJOIN（"把一堆文字粘成一串"的函数）把筛选结果拼到同一个格子里。
- 动态数组：用 FILTER / SORT / UNIQUE / SEQUENCE / LET 这五个"结果会自动溢出填满一片格子"的新函数，做清洗和看板。
前置条件（按版本分三类）：① IFERROR、TEXTJOIN 这类在 Excel 2019 及以上就能用；② XLOOKUP 不在 Excel 2019（永久版），它需要 Microsoft 365 或 Excel 2021 及以上（与动态数组同级）；③ 动态数组（FILTER / SORT / UNIQUE / SEQUENCE / LET）同样需要 Excel 365 或 2021 及以上。老版本（2016 及更早）里没有这些函数，后面「避坑指南」会讲怎么办。AI 工具主推网页版（通义千问 / ChatGPT），不挑 Excel 版本、即开即用；若单位已开通 M365 Copilot 许可，也可用 Excel 里的 Copilot，提示词同理通用。
三、案例实战
先准备两张表当"靶子"。
表 1：价格矩阵（放在 Sheet「价格」的 A1:D4）
| 单元格 | A | B | C | D |
|---|---|---|---|---|
| 第 1 行 | 产品 | 华东 | 华北 | 华南 |
| 第 2 行 | 苹果 | 5 | 6 | 7 |
| 第 3 行 | 香蕉 | 4 | 5 | 6 |
| 第 4 行 | 橙子 | 8 | 9 | 10 |
表 2：订单明细（放在 Sheet「订单」的 A1:C6）
| 单元格 | A 订单号 | B 客户 | C 金额 |
|---|---|---|---|
| 第 1 行 | 订单号 | 客户 | 金额 |
| 第 2 行 | O001 | 张三 | 100 |
| 第 3 行 | O002 | 李四 | 200 |
| 第 4 行 | O003 | 张三 | 150 |
| 第 5 行 | O004 | 王五 | 80 |
| 第 6 行 | O005 | 张三 | 300 |
场景一：多层查找 XLOOKUP
第一步：把需求说给 AI 听（提示词 / prompt）
在网页版通义 / ChatGPT（或已开通的 Excel Copilot）对话框里输入下面这段话（照抄即可，把表名换成你自己的）：
我在 Excel 里有价格表，A 列是产品（A2:A4），第一行 B1:D1 是区域，B2:D4 是价格。请用 XLOOKUP 写一个公式，查出"香蕉"在"华北"区域的价格。
第二步：拿到公式并验证
AI 通常会给你：
excel=XLOOKUP("香蕉", A2:A4, XLOOKUP("华北", B1:D1, B2:D4))
- 预期结果：
5（香蕉在"华北"那列、那行交叉处的值）。 - 人话解释：这是"两层 XLOOKUP"（也叫嵌套 XLOOKUP）。里层
XLOOKUP("华北", B1:D1, B2:D4)先在区域名里找到"华北"，把那一整列价格拎出来；外层再拿"香蕉"去产品列里定位行，最后返回交叉点的 5。
关键点：多层 XLOOKUP 不用像老 VLOOKUP 那样必须把"查找列"放在最左边，行、列随便摆，这也是它比 VLOOKUP 好用的核心原因。
场景二：IFERROR 容错
第一步：让 AI 给公式"加壳"
提示词：
在上面那个 XLOOKUP 公式外面套一层 IFERROR，如果查不到（比如产品不存在）就显示"无价格"，不要出现 #N/A。
第二步：验证查不到的情况
AI 给你：
excel=IFERROR(XLOOKUP("芒果", A2:A4, XLOOKUP("华北", B1:D1, B2:D4)), "无价格")
- 预期结果：
无价格（"芒果"不在产品列里，原本会报 #N/A，被 IFERROR 接住换成文字）。 - 人话解释：IFERROR 的规则是"先算括号里的，一旦出错就显示第二个参数"。这样表格给老板看时干干净净，不会蹦红字。
场景三：TEXTJOIN 拼接（配合 FILTER 筛选）
版本标注：本例的
TEXTJOIN本身在 Excel 2019+ 就能用，但它组合了FILTER（动态数组函数），所以整套公式仍需 Excel 365 或 2021 及以上才能跑；在 2019 永久版里会直接报#NAME?。
第一步：让 AI 写"筛选 + 拼接"
提示词：
订单表里 A 列是订单号、B 列是客户。请用公式把"张三"名下的所有订单号拼成一个单元格，用逗号加空格隔开。
第二步：验证拼接结果
AI 给你：
excel=TEXTJOIN(", ", TRUE, FILTER(A2:A6, B2:B6="张三"))
- 预期结果：
O001, O003, O005（张三在订单表里有三笔：O001、O003、O005）。 - 人话解释：
FILTER(A2:A6, B2:B6="张三")先把张三的订单号筛出来，得到一组值{O001; O003; O005}；TEXTJOIN 再把它们用", "粘成一长串。第二个参数TRUE意思是"空单元格跳过别管"。
关键点：TEXTJOIN 本身不会筛选，它只负责"粘"。要按条件拼，得先让 FILTER 把符合条件的值挑出来，再交给 TEXTJOIN 粘——这是动态数组函数最常见的"组合拳"。
场景四：动态数组五件套
动态数组（dynamic array）＝一种新公式，算出来的一组结果会自动"溢出"到相邻的空白格，不用你手动下拉。 这是 Excel 365 的强项。咱们五个逐个过，提示词都给你写好，黏过去就能用。
（1）FILTER——按条件筛选
提示词："把订单表里客户等于张三的所有行筛出来。"
excel=FILTER(A2:C6, B2:B6="张三")
- 预期结果：溢出成 3 行 × 3 列：
O001 张三 100/O003 张三 150/O005 张三 300。
（2）SORT——排序
提示词："把 A2:C6 按第 3 列（金额）从大到小排。"
excel=SORT(A2:C6, 3, -1)
- 预期结果：按金额降序：
O005 张三 300/O002 李四 200/O003 张三 150/O001 张三 100/O004 王五 80。第三个参数-1就是"降序"，写成1则是升序。
（3）UNIQUE——去重
提示词："把 B2:B6 里的客户名去重，列出不重复的。"
excel=UNIQUE(B2:B6)
- 预期结果：
张三/李四/王五（原本 5 个里有 3 个张三，去重后只留一个）。
（4）SEQUENCE——生成序号
提示词："帮我生成 1 到 5 的连续序号填在一列里。"
excel=SEQUENCE(5)
- 预期结果：
1/2/3/4/5。常用来做辅助序号列，或当其他公式的"行号"用（比如配合 INDEX 取第 N 行）。
（5）LET——给中间结果起名字，公式更好读
提示词："用 LET 写一个公式：先定义阈值 100，再筛出金额大于 100 的订单号。"
excel=LET(阈值, 100, 订单, A2:C6, 金额, INDEX(订单, 0, 3), FILTER(INDEX(订单, 0, 1), 金额 > 阈值))
- 预期结果：
O002/O003/O005（金额 200、150、300 都大于 100）。 - 人话解释：LET 让你把"阈值""订单""金额"这些中间量先起个名字，后面直接复用，公式一长串也不会乱。
INDEX(订单, 0, 3)里的0表示"取整列"（第 3 列即金额列）。
小提示：LET 里用中文当变量名（如"阈值"）在 Excel 365 里是支持的，但极个别旧补丁版本或某些区域设置下可能报错；真遇到，把变量名换成英文（如
th、amt）即可，逻辑完全不变。
四、原理小结
老 Excel 的数组公式要按 Ctrl+Shift+Enter 才能跑（俗称"CSE 数组公式"），还容易因为选区不对整片报错；而 Excel 365 的"动态数组"彻底改了玩法——一个公式算出一组结果，会自动"溢出"到相邻的空白格，旁边不用再手动拖动。XLOOKUP 之所以能替换 VLOOKUP，是因为它不用死守"查找列必须在最左"的铁律，还能直接指定"查不到时返回什么"，天然就和 IFERROR 解耦；TEXTJOIN 则是把"拼接 + 忽略空值"两件麻烦事合成一个函数；LET 则是给公式里的重复计算起名字、提性能、增可读性。一句话：这些新函数让"复杂"变得"可拆解"，再加上 AI 把语法门槛也抹平了，普通人也能写出以前要翻书才敢碰的公式。
五、避坑指南
- XLOOKUP 的"查找列"和"返回列"行数要对齐。第一参数（找什么）和第二参数（在哪找）必须一样长；多层 XLOOKUP 里，里层返回的那一列也要和外层查找列行数一致，否则会蹦出 #VALUE!。
- 动态数组会"溢出"，别在它脚下塞数据。FILTER / SORT 等公式的结果会自动往下 / 往右铺，如果你在它正下方手动填了字，就会报 #SPILL!（"溢出被挡住"）。把下方清空即好。
- TEXTJOIN 第二个参数别漏。必须是
TRUE才会跳过空单元格；写成FALSE或漏掉，空单元格也会占一个分隔符，出现 ", , " 这种怪样子。 - IFERROR 别包太大。它很"霸道"，会把里面所有错误（包括你公式写错的真错误）一起吃掉，调试时先拆掉 IFERROR 看原始报错，定位好再加回去。
- AI 给的公式一定要先拿"示例数据 + 预期结果"试一遍。别直接黏到老板的生产表上。AI 偶尔会把表头行算进去或引用错区域，用上面的示例验证一下最稳。
六、进阶延展
- 回指 B2（数据清洗）：如果拿到的原始表乱七八糟（合并单元格、多余空格、日期是文本），先去看 B2 用「通义」一键清洗，再回来套本文的公式，省得公式被脏数据带歪。
- 指向下一层 B4（财务模型）：当你要把这些查表、筛选的结果拿去做利润表、现金流预测，就进 B4 学怎么搭可联动的财务模型。
- 更深的玩法：多维汇总可以上 PivotTable（数据透视表） 拖拽分析；要写跨表度量指标就学 DAX（Power BI / Excel 数据模型里的专用计算语言）；而把几十张表自动合并清洗，靠的是 Power Query（Excel 里"获取数据 / 查询"那个 ETL 工具）。这三样和本文的公式配合，基本能覆盖职场 90% 的数据活。