Excel公式与函数专家

技能文档

---
name: spreadsheet-formula-pro
title: Excel公式与函数专家
category: 办公效率
description: Excel公式与函数专家，精通Excel/WPS表格全部函数与高级用法。覆盖查找引用(VLOOKUP/XLOOKUP/INDEX-MATCH)、条件计算(SUMIFS/COUNTIFS)、文本处理、日期函数、数组公式、数据透视表、条件格式、图表制作。提供公式编写指导、错误排查、性能优化。当用户需要编写Excel公式、解决表格问题、学习函数用法、优化数据处理时触发。触发词：Excel公式、VLOOKUP、函数、表格公式、SUMIFS、数据透视表、条件格式、Excel技巧、表格处理。
---

# Excel公式与函数专家

## 函数分类速查

### 查找引用类

| 函数 | 用途 | 语法 |
|------|------|------|
| VLOOKUP | 纵向查找 | `=VLOOKUP(查找值, 区域, 列号, 0)` |
| HLOOKUP | 横向查找 | `=HLOOKUP(查找值, 区域, 行号, 0)` |
| XLOOKUP | 万能查找 | `=XLOOKUP(查找值, 查找范围, 返回范围)` |
| INDEX+MATCH | 灵活查找 | `=INDEX(返回范围, MATCH(查找值, 查找范围, 0))` |
| INDIRECT | 间接引用 | `=INDIRECT("Sheet1!A1")` |
| OFFSET | 偏移引用 | `=OFFSET(基准, 行偏移, 列偏移, 高度, 宽度)` |

### 条件计算类

| 函数 | 用途 | 示例 |
|------|------|------|
| SUMIFS | 多条件求和 | `=SUMIFS(C:C, A:A, "北京", B:B, ">100")` |
| COUNTIFS | 多条件计数 | `=COUNTIFS(A:A, "完成", B:B, ">=2026-01-01")` |
| AVERAGEIFS | 多条件平均 | `=AVERAGEIFS(C:C, A:A, "销售", B:B, "Q1")` |
| MAXIFS | 条件最大值 | `=MAXIFS(B:B, A:A, "产品A")` |
| IF | 条件判断 | `=IF(A1>60, "及格", "不及格")` |
| IFS | 多条件判断 | `=IFS(A1>=90,"优",A1>=80,"良",A1>=60,"中")` |

### 文本处理类

| 函数 | 用途 | 示例 |
|------|------|------|
| LEFT/RIGHT/MID | 截取 | `=MID(A1, 3, 5)` |
| LEN | 长度 | `=LEN(A1)` |
| FIND/SEARCH | 查找位置 | `=FIND("@", A1)` |
| SUBSTITUTE | 替换 | `=SUBSTITUTE(A1, "旧", "新")` |
| TEXT | 格式化 | `=TEXT(A1, "yyyy-mm-dd")` |
| TEXTJOIN | 合并 | `=TEXTJOIN(",", TRUE, A1:A10)` |
| TRIM | 去空格 | `=TRIM(A1)` |

### 日期时间类

| 函数 | 用途 | 示例 |
|------|------|------|
| TODAY/NOW | 当前日期/时间 | `=TODAY()` |
| YEAR/MONTH/DAY | 提取 | `=YEAR(A1)` |
| DATEDIF | 日期差 | `=DATEDIF(A1, B1, "D")` |
| EOMONTH | 月末日期 | `=EOMONTH(A1, 0)` |
| NETWORKDAYS | 工作日数 | `=NETWORKDAYS(A1, B1)` |
| WEEKDAY | 星期几 | `=WEEKDAY(A1, 2)` |

## 常见场景公式

### 数据查询

```
多条件查找：
=XLOOKUP(1, (A:A="北京")*(B:B="产品A"), C:C)

模糊匹配：
=XLOOKUP("*"&A1&"*", D:D, E:E, "未找到", 2)

返回多个结果：
=TEXTJOIN(";", TRUE, FILTER(B:B, A:A="北京"))
```

### 统计汇总

```
去重计数：
=SUMPRODUCT(1/COUNTIF(A:A, A:A))

条件排名：
=RANK(B2, FILTER(B:B, A:A=A2))

动态Top N求和：
=SUM(LARGE(B:B, ROW(1:10)))

移动平均（最近7天）：
=AVERAGE(OFFSET(C1, COUNT(C:C)-7, 0, 7))
```

### 文本提取

```
提取括号内内容：
=MID(A1, FIND("(", A1)+1, FIND(")", A1)-FIND("(", A1)-1)

提取最后一个空格后的内容：
=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

提取数字：
=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A1, ROW($1:$100), 1)), MID(A1, ROW($1:$100), 1), ""))
```

## 常见错误排查

| 错误 | 原因 | 解决方案 |
|------|------|----------|
| #N/A | 查找值不存在 | 检查数据/用IFERROR包裹 |
| #VALUE! | 类型不匹配 | 检查参数类型，用VALUE转换 |
| #REF! | 引用无效 | 检查是否删除了被引用的行列 |
| #NAME? | 函数名拼错 | 检查拼写/确认函数可用 |
| #DIV/0! | 除以零 | 加IF判断分母 |
| #NUM! | 数值无效 | 检查数值范围 |

## 性能优化

- 避免整列引用（A:A → A1:A10000）
- 减少易失函数（INDIRECT/OFFSET/TODAY）
- 用SUMPRODUCT替代数组公式
- 大数据量用数据透视表替代公式
- 将计算结果粘贴为值减少重算

如何安装此技能？

访问技能市场，点击「安装」按钮，按提示将技能包放入 AI 编程助手的 skills 目录即可。