Excel 公式终极指南：从入门到精通的“各种公式大全”
在数据处理、财务分析、项目管理乃至日常办公中，Microsoft Excel 都是的工具。不过，很多的用户只停留在简单的求和与基础计算上，未能充分发挥 Excel 的潜力。掌握一套全面且高效的 Excel 公式库，不仅能将工作效率提升数倍，还能让数据洞察更加精准深刻。
这篇文章将为您梳理 Excel 中最核心、最实用的各类公式，涵盖基础运算、逻辑判断、文本处理、查找引用及统计分析五大领域，并辅以实战案例与数据表格，助您成为 Excel 高手。
基础数学与统计：数据的基石
这是 Excel 中最常用的公式类别，适用于绝大多数日常计算场景。
核心函数解析
| 函数名称 | 语法示例 | 功能描述 | 适用场景 |
|---|---|---|---|
| SUM | `=SUM(A1:A10)` | 计算区域内所有数值的总和 | 计算总销售额、总工时等 |
| AVERAGE | `=AVERAGE(B1:B10)` | 计算区域内数值的平均值 | 计算平均分、平均成本等 |
| COUNT | `=COUNT(C1:C10)` | 统计区域内包含数字的单元格个数 | 统计有效数据条数 |
| COUNTA | `=COUNTA(D1:D10)` | 统计区域内非空单元格的个数 | 统计填写了内容的行数 |
| MAX/MIN | `=MAX(E1:E10)` `=MIN(E1:E10)` |
分别返回最大值和最小值 | 找出最高销量、最低库存 |
| SUMIF/SUMIFS | `=SUMIF(A1:A10,"北京",B1:B10)` `=SUMIFS(B1:B10,A1:A10,"北京",C1:C10,">1000")` |
条件求和（单条件/多条件） | 按地区求和、按部门和金额范围求和 |
实战案例：销售数据汇总
假设您有一份销售表，A列为地区，B列为销售额。若要计算“北京”地区的总销售额，使用 `SUMIF` 比手动筛选更高效： ```excel =SUMIF(A2:A100, "北京", B2:B100) ```逻辑判断函数：让数据“会思考”
逻辑函数允许您根据特定条件执行不同的操作，是完成自动化决策。
核心函数解析
| 函数名称 | 语法示例 | 功能描述 | 适用场景 |
|---|---|---|---|
| IF | `=IF(A1>60, "及格", "不及格")` | 如果条件成立返回一个值，否则返回另一个值 | 成绩判定、状态标记 |
| IFS | `=IFS(A1>90,"优", A1>80,"良", A1>60,"中", TRUE,"差")` | 检查多个条件并返回个为 TRUE 的值 | 多级评分、复杂状态分类 |
| AND/OR | `=AND(A1>10, B1<20)` `=OR(A1>10, B1>10)` |
所有条件都为真返回真 / 任一条件为真返回真 | 嵌套在 IF 中用于多条件判断 |
| IFERROR | `=IFERROR(VLOOKUP(...), "未找到")` | 如果公式出错则返回指定值，否则返回结果 | 避免 #N/A, #DIV/0! 等错误显示 |
实战案例：员工绩效评级
根据员工得分（假设在 C 列）自动评定等级： ```excel =IFS(C2>=90, "S", C2>=80, "A", C2>=70, "B", C2>=60, "C", TRUE, "D") ``` 注：`IFS` 函数（Excel 2019 及以上版本）比嵌套 `IF` 更清晰易读。查找与引用函数：数据关联的桥梁
在大型数据库中，根据关键字段查找对应信息是最高频的需求之一。
核心函数解析
| 函数名称 | 语法示例 | 功能描述 | 适用场景 |
|---|---|---|---|
| VLOOKUP | `=VLOOKUP(查找值, 表格区域, 列号, [匹配模式])` | 垂直查找，从左向右查找 | 根据工号查找姓名、根据产品ID查价格 |
| XLOOKUP | `=XLOOKUP(查找值, 查找数组, 返回数组, [未找到值], [匹配模式])` | 新一代查找函数，功能更强 | 替代 VLOOKUP，支持反向查找、默认精确匹配 |
| INDEX + MATCH | `=INDEX(返回列, MATCH(查找值, 查找列, 0))` | 组合使用，灵活查找 | 在旧版本 Excel 中实现双向查找或左侧查找 |
VLOOKUP vs XLOOKUP 对比
| 特性 | VLOOKUP | XLOOKUP |
|---|---|---|
| 查找方向 | 仅支持从左向右 | 支持任意方向 |
| 列号引用 | 需手动输入列号，插入列易出错 | 直接引用返回列范围，更安全 |
| 默认匹配 | 需指定精确或近似匹配（默认近似） | 默认精确匹配，更符合直觉 |
| 错误处理 | 需配合 IFERROR | 内置第四个参数可直接处理未找到情况 |
推荐用法：
```excel
=XLOOKUP(D2, A2:A100, B2:B100, "未找到该员工")
```
文本处理函数：清洗与格式化
当数据来自不同系统或必须整合时，文本函数。
核心函数解析
| 函数名称 | 语法示例 | 功能描述 | 适用场景 |
|---|---|---|---|
| LEFT/RIGHT/MID | `=LEFT(A1, 3)` `=MID(A1, 2, 4)` |
从左侧/右侧/指定位置截取字符 | 提取身份证前6位、截取邮箱前缀 |
| LEN | `=LEN(A1)` | 计算文本字符串的长度 | 检查手机号位数、验证数据完整性 |
| CONCATENATE/TEXTJOIN | `=TEXTJOIN("-", TRUE, A1, B1)` | 连接多个文本，可指定分隔符 | 组合姓名与部门，生成唯一ID |
| TRIM | `=TRIM(A1)` | 清除文本中多余的空格 | 清理从网页或系统导出的脏数据 |
实战案例：标准化邮箱格式
假设 A 列是姓名，B 列是部门，需要生成 `姓名@部门.com` 格式的邮箱： ```excel =TRIM(A2) & "@" & TRIM(B2) & ".com" ```日期与时间函数：时间序列分析
时间管理是数据分析的重要维度。
核心函数解析
| 函数名称 | 语法示例 | 功能描述 | 适用场景 |
|---|---|---|---|
| TODAY/NOW | `=TODAY()` `=NOW()` |
返回当前日期 / 当前日期和时间 | 计算剩余天数、制作动态报表头 |
| DATEDIF | `=DATEDIF(开始日期, 结束日期, "y")` | 计算两个日期之间的间隔 | 计算工龄、年龄、项目周期 |
| EOMONTH | `=EOMONTH(A1, 0)` | 返回指定日期前或后月份的一日 | 确定月末结账日、生成月度报表标题 |
| YEAR/MONTH/DAY | `=YEAR(A1)` | 提取日期中的年/月/日 | 按年份汇总数据、提取月份标签 |
实战案例：计算员工司龄
假设入职日期在 A2，当前日期用 `TODAY()`： ```excel =DATEDIF(A2, TODAY(), "y") & "年" & DATEDIF(A2, TODAY(), "ym") & "个月" ``` 注：`"y"` 返回完整年数，`"ym"` 忽略年份后的完整月数。高级进阶：数组与动态函数（Excel 365/2021+）
如果您使用的是新版 Excel，以下函数将彻底改变您的工作流。
1. FILTER 函数：根据条件动态筛选数据。
`=FILTER(A2:C100, B2:B100="销售部")` —— 自动返回销售部所有员工信息，无需复制粘贴。
2. UNIQUE 函数：提取唯一值。
`=UNIQUE(A2:A100)` —— 快速去除重复项，生成唯一客户列表。
3. SEQUENCE 函数：生成数字序列。
`=SEQUENCE(10, 1, 1, 1)` —— 生成 1 到 10 的垂直序列，无需拖动填充。
提高公式效率的 5 个黄金技巧
1. 使用绝对引用（A$1` 无论复制到何处都指向 A1。
2. 定义名称（Name Manager）：将常用范围（如 `SalesData`）命名为有意义的名称，使公式更易读：`=SUM(SalesData)`。
3. F9 键调试：选中公式中的某一部分（如 `A1:A10`），按 F9 可查看该部分的计算结果，帮助排查错误。
4. 避免整列引用：尽量使用 `A1:A1000` 而非 `A:A`，以减少计算量，提升 Excel 运行速度。
5. 使用表格（Ctrl+T）：将数据区域转换为“超级表”，公式会自动扩展，且引用更直观（如 `=[@Price][@Quantity]`）。
Excel 公式的世界浩瀚无边，但掌握上面这些核心函数已能解决 90% 以上的办公需求。建议您从VLOOKUP/XLOOKUP、IF 和 SUMIFS 开始练习，逐步深入到数组函数和动态公式。记住，最好的学习方式是在实际工作中遇到问题时，针对性地查阅和尝试这些公式。
希望这份“Excel 各种公式大全”能成为您数据之旅中的得力助手，让繁琐的数据处理变得简单而优雅。
