书籍 / 用 AI 搞定 Excel：从公式到自动化
第 1 章

用 AI 生成你看不懂的公式

第1章：用 AI 生成你看不懂的公式

这一章是全书的核心。你在后面每一章里看到的 AI 辅助方法，都建立在这一章的基础上。读完这一章，你就掌握了用 AI 处理 Excel 公式的完整方法论——不管是写公式、看懂公式、找公式里的错，还是让公式变得更快更简洁。

为什么要用 AI 写公式

三个真实的痛点

在讲方法之前，先说说为什么大多数人在 Excel 公式这件事上会遇到困难。根本原因不是人笨，而是公式的设计方式对人类不友好。

痛点一：函数名记不住。 Excel 有超过 500 个函数，即便是日常用到的也有几十个。你知道自己"想要找出 A 列中某个值在 B 列中对应的数据"，但你不一定记得这叫 VLOOKUP 还是 MATCH 还是 INDEX。你知道自己"想要统计满足某个条件的单元格数量"，但你得记住这是 COUNTIF 而不是别的什么。函数名本身就是一道记忆门槛。

痛点二：参数顺序经常搞错。 就算你知道是 VLOOKUP，你还得记住它的参数顺序：第一个是查找值，第二个是查找范围，第三个是返回第几列，第四个是精确匹配还是近似匹配。搞错一个参数，结果就全错了，错误信息还不告诉你哪里错了。

痛点三：嵌套公式看不懂，写不出来。 当你需要"如果 A 列的值大于 100 就返回 B 列的值，否则从 C 列查找 D 列的对应值"时，你需要把 IF 和 VLOOKUP 嵌套在一起。这个逻辑本身不复杂，但把它翻译成嵌套公式的语法，就超过了很多人的直觉边界。

这三个痛点的共同根源是：Excel 的公式语言是给计算机看的，不是给人看的。 而 AI 恰好能做这个翻译工作——把你用人话描述的需求，翻译成 Excel 能理解的公式语言。

用 AI 写公式的核心优势

ℹ️ Note

**一个重要前提：**AI 写的公式需要你验证。AI 不知道你的具体表格结构，它生成的公式是基于你的描述的，所以你要：①确认列引用是否正确（AI 可能用 A1:A100，而你的数据在 C1:C50）；②用几个已知结果的行验证公式是否正确。验证公式的正确性是你的职责，不是 AI 的。

AI 写公式的正确方式——"需求描述法"

核心原则：描述你想要的结果，不是描述公式

很多人第一次用 AI 写公式时，会犯一个错误：他们描述的是公式，而不是需求。

比如说："帮我写一个 VLOOKUP 公式，在 A 列查找 E2 的值，返回 B 列的内容。"

这种方式的问题是：如果你已经知道要用 VLOOKUP，你大概率也知道怎么用。更多的情况是你不知道要用什么函数，或者你以为是 VLOOKUP 但其实是另一个函数更合适。

正确的方式是：描述你的业务需求，让 AI 帮你选择最合适的函数。

错误示范："帮我写一个 VLOOKUP"。正确示范："我的表格 A 列是产品编码，B 列是产品名称。我在 E2 单元格有一个产品编码，我想在 F2 单元格显示这个编码对应的产品名称。"

WHAT-FROM-WHERE 框架

为了让你每次向 AI 提问都能得到准确的公式，我设计了一个三要素框架：

WHAT-FROM-WHERE 框架

WHAT
**我想要什么结果？** 描述你希望在目标单元格里看到的内容。例如："我想显示某个员工的部门名称"、"我想计算满足两个条件的销售额总和"。

FROM
**数据从哪里来？** 描述你的数据在哪张表、哪一列或哪个范围。例如："员工信息在 Sheet1 的 A 到 C 列，A 是工号，B 是姓名，C 是部门"。


WHERE
**公式放在哪里？** 说明公式的目标单元格，以及你用什么值作为查找条件。例如："公式写在 D2，D2 对应的工号在 A2"。

用这个框架，你的问题就会从模糊的"帮我写公式"变成清晰的需求描述，AI 给出的公式也会直接可用，而不是需要你猜测和修改。

接下来，我们用 4 个典型案例来演示这个框架的实际效果。

用 AI 写公式：4 个典型案例

1案例一：VLOOKUP 基础查找 场景：你有一张产品价格表（Sheet1：A 列是产品编码，B 列是单价），现在销售表（Sheet2）的 A 列有产品编码，你需要在 Sheet2 的 B 列自动填入对应的单价。

帮我写一个VLOOKUP公式

我有两张工作表：
Sheet1（价格表）：A列是产品编码，B列是单价，数据从A2开始往下共100行
Sheet2（销售表）：A列是产品编码，B列我想填入对应的单价

我想在Sheet2的B2单元格写一个公式，根据Sheet2的A2的产品编码，从Sheet1的A列查找，找到后返回Sheet1对应的B列单价。

请给我一个可以直接复制到B2的公式，并向下填充的那种。

=VLOOKUP(A2,Sheet1!$A:$B,2,0)

说明：
- A2：当前行的产品编码（查找值）
- Sheet1!$A:$B：在 Sheet1 的 A 到 B 列范围内查找（$符号锁定列，向下填充时不会移位）
- 2：返回查找范围的第 2 列，即 B 列（单价）
- 0：精确匹配，找不到时返回 #N/A

向下填充时，A2 会自动变成 A3、A4……，Sheet1!$A:$B 保持不变。

2案例二：多条件 SUMIF 场景：销售数据表，A 列是销售员姓名，B 列是产品类别，C 列是销售额。你需要计算"张三"在"电子产品"类别下的总销售额。

我想用SUMIF计算某个人在某个类别的销售额，怎么写？

我的表格结构：
A列：销售员姓名（A2:A500）
B列：产品类别（B2:B500）
C列：销售额（C2:C500）

我想在另一个单元格里写一个公式，计算满足以下两个条件的销售额总和：
条件1：A列的值等于"张三"
条件2：B列的值等于"电子产品"

请给我公式，并解释每个参数的意思。

=SUMIFS(C2:C500, A2:A500, "张三", B2:B500, "电子产品")

参数说明：
- C2:C500：要求和的数值范围（销售额）
- A2:A500, "张三"：第一个条件——A列等于"张三"
- B2:B500, "电子产品"：第二个条件——B列等于"电子产品"

如果"张三"和"电子产品"是从某个单元格引用的（比如在E2填姓名、F2填类别），可以改成：
=SUMIFS(C2:C500, A2:A500, E2, B2:B500, F2)

3案例三：复杂嵌套 IF 场景：绩效评级表，A 列是员工姓名，B 列是绩效得分。规则：90分以上评"优秀"，75-89 分评"良好"，60-74 分评"合格"，60 分以下评"待改进"。需要在 C 列自动填入评级。

帮我写一个IF嵌套公式，判断分数等级

我需要一个公式，根据B2单元格的分数，返回对应的绩效评级：
- 90分及以上：返回"优秀"
- 75到89分（含75和89）：返回"良好"
- 60到74分（含60和74）：返回"合格"
- 59分及以下：返回"待改进"

公式写在C2，B2是分数。请给出可以向下填充的公式，并解释嵌套逻辑。

=IF(B2>=90,"优秀",IF(B2>=75,"良好",IF(B2>=60,"合格","待改进")))

嵌套逻辑说明：
1. 先判断 B2 是否 ≥ 90，是则返回"优秀"，进入下一层
2. 判断 B2 是否 ≥ 75（已排除≥90的情况），是则返回"良好"，进入下一层
3. 判断 B2 是否 ≥ 60（已排除≥75的情况），是则返回"合格"
4. 以上条件都不满足（即 B2 4案例四：文本提取
  场景：A 列有一批格式为"城市-区域-门店编号"的数据，例如"上海-浦东-SH001"。你需要分别提取城市名、区域名和门店编号到 B、C、D 列。

帮我用公式提取文本里的内容

我的A列有数据，格式是"城市-区域-门店编号"，用短横线"-"分隔，例如： A2 = "上海-浦东-SH001" A3 = "北京-朝阳-BJ002"

我需要：

请分别给出B2、C2、D2的公式，可向下填充。Excel版本是Office 365。

由于您使用 Office 365，可以用更简洁的新函数：

B2（城市）：=TEXTBEFORE(A2,"-") C2（区域）：=TEXTBEFORE(TEXTAFTER(A2,"-"),"-") D2（门店编号）：=TEXTAFTER(A2,"-",2)

如果您的同事用的是较旧版本的 Excel（2016/2019），建议改用兼容性更好的写法：

B2（城市）：=LEFT(A2,FIND("-",A2)-1) C2（区域）：=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1) D2（门店编号）：=MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,LEN(A2))



## 让 AI 解释你看不懂的公式


接手别人的表格，或者发现了一个网上搜来的公式，但看不懂它在做什么——这是职场里极其常见的场景。这一节教你怎么用 AI 解决这个问题。


### 基本方法：直接粘贴公式，让 AI 逐行解释


你只需要把公式粘贴给 AI，然后说："请逐步解释这个公式的每一部分是做什么的，用大白话说明。" 接下来看三个案例：


  1解释案例一：数组公式

这是一个数组公式，用来计算特定条件下的求和。

逻辑分解：

  1. IF(A2:A100="张三", C2:C100, 0) 这部分对 A2 到 A100 的每一行做判断：如果 A 列等于"张三"，就取该行的 C 列值（销售额）；如果不是"张三"，就用 0 代替。 结果是一个数组，例如：{0, 450, 0, 0, 800, 0, ...}（只有张三的行有实际数值，其余为0）

  2. SUM(...) 把上面这个数组里所有的数加起来，就得到了张三的销售额总和。

简单说：这个公式等于"找出所有张三的销售额行，然后把这些数加起来"。 现代替代写法（不需要 Ctrl+Shift+Enter）：=SUMIF(A2:A100,"张三",C2:C100)



---

---

  2解释案例二：复杂嵌套公式







---

---

  3解释案例三：他人遗留公式







## 让 AI 帮你调试出错的公式


公式出错了，Excel 会显示错误代码，但它不告诉你为什么错了，更不告诉你怎么改。AI 可以帮你做这件事。


### 调试的基本姿势：错误信息 + 公式 + 数据结构


向 AI 汇报公式错误，需要提供三件事：


1. 公式是什么
2. 错误信息是什么（#REF!、#VALUE!、#N/A 等）
3. 数据结构是什么（各列放的是什么）


  1调试案例一：#REF! 错误







---

---

  2调试案例二：#N/A 错误







---

---

  3调试案例三：#VALUE! 错误







## 让 AI 优化你的公式


公式能用，但写得又长又复杂——这种情况很普遍。AI 可以帮你做三类优化：


### 优化类型一：简化嵌套，提升可读性

我有一个嵌套IF公式，能用但是太长了，请帮我简化： =IF(B2="华东",C20.1,IF(B2="华南",C20.12,IF(B2="华北",C20.08,IF(B2="西南",C20.09,0))))

B列是区域，C列是销售额，这个公式计算每个区域的提成金额（提成率不同）。Excel版本是365。



### 优化类型二：跨版本兼容性改造


## 本章速查卡：所有 Prompt 模板汇总


| 场景 | Prompt 模板 | 关键要素 |
| --- | --- | --- |
| **写新公式** | 我想要【WHAT】，数据在【FROM：列描述】，公式放在【WHERE：目标单元格，查找值来源】。Excel版本是【版本】。 | 三要素缺一不可 |
| **解释公式** | 请逐步解释这个公式每一部分在做什么，用大白话说明：【粘贴公式】。背景：【列说明】。 | 要给列说明才能准确解释 |
| **调试错误** | 公式：【公式】，报错：【#XX!错误】，数据结构：【列说明】，问题描述：【具体情况】。 | 错误代码 + 数据结构 |
| **简化公式** | 请帮我简化这个公式，保持结果不变：【公式】。背景：【列说明】。Excel版本：【版本】。 | 版本影响可用函数 |
| **兼容旧版** | 我用了【新函数】，需要兼容Excel【旧版本】的同事，请给等效写法：【公式】。 | 说明目标版本 |
| **修改公式** | 我有这个公式：【公式】，现在需要【改动说明，例如：把查找范围从A:B改成A:D，返回第3列】。 | 描述具体改动而不是重新描述需求 |


**✅ Tip**

> **本章核心总结：**
>
>   用 AI 写公式的核心是"需求描述法"——描述你想要什么结果，不是描述你以为要用什么函数。
>
>   记住 WHAT-FROM-WHERE 三要素：**我要什么结果 / 数据从哪里来 / 公式放在哪里**。
>
>   这个方法适用于后面每一章的所有函数和功能。下一章，我们建立 Excel 操作的基础认知，确保你能准确地向 AI 描述你的表格结构。


### 下一章预告


掌握了写公式的核心方法，但还有一个前提：你要能准确描述你的表格结构。A 列、B 列、单元格引用、数据范围——这些是 AI 理解你需求的语言。第 2 章，我们快速补齐这些基础概念，确保你和 AI 说的是同一种语言。


  [
    上一章
    ← 引言：为什么 Excel 高手都在用 AI
  ](/books/ai-excel/introduction)
  [
    下一章
    第2章：Excel 基础 →
  ](/books/ai-excel/ch02-basics)

---
本章评分
4.7  / 5  (100 评分)

💬 留言讨论