---
name: office-excel-formula
description: "写 Excel/WPS 公式：查引用、条件统计、文本处理、日期计算，附拆解说明。"
---

# Excel 公式助手

## 触发场景

用户要写 Excel 公式、问「这个怎么算」「怎么自动统计」时使用。
适用于 Excel、WPS 表格、Google Sheets。

## 先要三样东西

1. **想算什么**：说清楚目的（「把 A 列里大于 100 的加起来」）
2. **数据长什么样**：哪列是什么、从哪行开始、有没有表头
3. **结果放哪**：在哪一个单元格写公式

**最关键的是第 2 条**——同样的需求，数据布局不同，公式完全不同。

## 一、先给公式，再给拆解

输出格式固定：

```
【公式】
=SUMIFS(C:C, A:A, ">100", B:B, "华东")

【拆解】
 SUMIFS：多条件求和
 C:C      → 要求和的列（金额）
 A:A,">100" → 条件1：A 列大于 100
 B:B,"华东" → 条件2：B 列等于"华东"

【怎么用】
放到 XX 单元格，然后下拉填充
```

**必须解释每个参数**，用户要能改，不是只会复制。

## 二、高频公式速查

### 查找引用

| 需求 | 公式 |
|---|---|
| 精确查找 | `=VLOOKUP(查什么, 在哪找, 第几列, FALSE)` |
| 更灵活（推荐） | `=XLOOKUP(查什么, 查找列, 返回列, "找不到")` |
| 双向查找 | `=INDEX(返回区, MATCH(行条件,行区,0), MATCH(列条件,列区,0))` |

⚠️ **VLOOKUP 的两个坑**：
1. 查找列必须在区域**第一列**
2. 第 4 参数一定要写 `FALSE`（精确匹配），省略会出错

**能用 XLOOKUP 就用 XLOOKUP**（Excel 365 / 新版 WPS），没有方向限制，还能指定找不到时返回什么。

### 条件统计

| 需求 | 公式 |
|---|---|
| 单条件计数 | `=COUNTIF(A:A, "已完成")` |
| 多条件计数 | `=COUNTIFS(A:A, "华东", B:B, ">100")` |
| 单条件求和 | `=SUMIF(A:A, "华东", C:C)` |
| 多条件求和 | `=SUMIFS(C:C, A:A, "华东", B:B, ">100")` |
| 条件平均 | `=AVERAGEIFS(C:C, A:A, "华东")` |
| 条件判断 | `=IF(A1>100, "高", "低")` |
| 多条件判断 | `=IFS(A1>=90,"优", A1>=60,"及格", TRUE,"不及格")` |
| 查不到就空 | `=IFERROR(原公式, "")` |

### 文本处理

| 需求 | 公式 |
|---|---|
| 拼接 | `=A1&B1` 或 `=TEXTJOIN("-",TRUE,A1:A10)` |
| 截取 | `=LEFT(A1,3)` / `=RIGHT(A1,4)` / `=MID(A1,2,5)` |
| 查找字符位置 | `=FIND("-", A1)` |
| 替换 | `=SUBSTITUTE(A1, "旧", "新")` |
| 去空格 | `=TRIM(A1)` |
| 分列（按分隔符） | `=TEXTSPLIT(A1, "-")` |
| 数字转文本 | `=TEXT(A1, "0.00")` |

### 日期计算

| 需求 | 公式 |
|---|---|
| 今天 | `=TODAY()` |
| 相差天数 | `=B1-A1` 或 `=DATEDIF(A1,B1,"d")` |
| 相差月数 | `=DATEDIF(A1,B1,"m")` |
| 加减月份 | `=EDATE(A1, 3)` |
| 月末日期 | `=EOMONTH(A1, 0)` |
| 工作日天数 | `=NETWORKDAYS(A1, B1)` |
| 提取年月 | `=YEAR(A1)` / `=MONTH(A1)` |

### 逻辑与容错

| 需求 | 公式 |
|---|---|
| 任一条件满足 | `=OR(A1>10, B1>20)` |
| 全部条件满足 | `=AND(A1>10, B1>20)` |
| 错误处理 | `=IFERROR(公式, "出错时显示")` |
| 空值判断 | `=IF(A1="", "未填", A1)` |

## 三、常见报错怎么修

| 报错 | 原因 | 修法 |
|---|---|---|
| `#N/A` | 查不到 | 检查查找值是否一致（有无空格/格式不同） |
| `#VALUE!` | 数据类型不对 | 数字存成了文本，用 `VALUE()` 转换 |
| `#DIV/0!` | 除数为 0 | 加 `IFERROR` 或判断分母 |
| `#REF!` | 引用区域被删 | 检查公式引用的区域 |
| 公式不计算 | 单元格是文本格式 | 改格式为「常规」，重新输入 |

**最常见的坑：数字存成了文本**（左上角有绿三角）。
修法：选中列 → 数据 → 分列 → 完成，或用 `=VALUE(A1)`。

## 四、绝对引用与相对引用

| 写法 | 行为 | 用在哪 |
|---|---|---|
| `A1` | 下拉会变 | 每行取自己的值 |
| `$A$1` | 下拉不变 | 固定参照某个值（如税率） |
| `$A1` | 列不变行变 | 横向填充 |
| `A$1` | 行不变列变 | 纵向填充 |

⚠️ 下拉公式结果不对，**八成是少了 `$`**。

## 五、给出公式前的自查

1. 数据从第几行开始？（有表头就从第 2 行）
2. 区域要不要绝对引用？（下拉填充时会不会跑）
3. 数值是不是文本格式？
4. 有没有合并单元格？（合并单元格会让公式大面积出错）
5. 找不到时要显示什么？（加 `IFERROR`）

## 注意

- 用户说「算不出来」时，**先问报错是什么**，不要瞎猜。
- 涉及合并单元格，明确告诉用户：**先取消合并再写公式**，否则公式基本没法用。
- 大数据量（几万行）避免用整列引用（如 `A:A`），会卡；改用具体区域（如 `A2:A10000`）。
- 不同软件支持度不同：XLOOKUP / TEXTSPLIT 老版本 Excel 没有，要给兼容版公式。