Excel 公式实战指南：从入门到精通的常用公式大全
在数字化办公时代，Microsoft Excel 依然是数据处理工具。无论是财务报表、库存管理，还是项目进度追踪，掌握高效的公式不仅能将数小时的工作缩短至几分钟，更能显著降低人为错误的风险。
很多用户虽然知道 Excel 强大，却停留在简单的加减乘除上。梳理 Excel 表格常用公式大全，按功能场景分类，配合实战案例与数据表格，助你构建高效的数据处理工作流。
逻辑判断类：让数据“会思考”
逻辑函数是 Excel 智能化的基石，它们能让表格根据条件自动做出判断。
IF 函数：基础的条件分支
语法：`=IF(逻辑测试, 值如果为真, 值如果为假)`场景：根据销售额判断是否达标。
| 员工姓名 | 销售额 (元) | 考核结果公式 | 考核结果 |
|---|---|---|---|
| 张三 | 150,000 | `=IF(B2>=100000, "达标", "未达标")` | 达标 |
| 李四 | 80,000 | `=IF(B3>=100000, "达标", "未达标")` | 未达标 |
| 王五 | 120,000 | `=IF(B4>=100000, "达标", "未达标")` | 达标 |
IFS 函数：多条件嵌套（Excel 2019+）
当条件超过三个时，嵌套 IF 会变得难以维护。IFS 函数让代码更整洁。场景：根据成绩评定等级。
公式：`=IFS(C2>=90, "A", C2>=80, "B", C2>=60, "C", TRUE, "D")`
注：的 `TRUE, "D"` 作为默认值，相当于其他语言中的 `else`。
AND / OR 函数：组合逻辑
常与 IF 结合使用。 AND：所有条件都满足才返回 TRUE。 OR：只要有一个条件满足就返回 TRUE。统计汇总类：快速掌握数据概貌
SUMIF / SUMIFS：条件求和
这是财务和数据分析中最常用的函数。SUMIF：单条件求和。
公式：`=SUMIF(条件区域, 条件, 求和区域)`
SUMIFS：多条件求和（注意：求和区域在最前）。
公式：`=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2)`
实战案例：计算“销售部”在“Q1”季度的总业绩。
| 部门 | 季度 | 业绩 (万元) |
|---|---|---|
| 销售部 | Q1 | 50 |
| 技术部 | Q1 | 30 |
| 销售部 | Q2 | 60 |
| 销售部 | Q1 | 45 |
公式：
```excel
=SUMIFS(C2:C5, A2:A5, "销售部", B2:B5, "Q1")
```
结果：95 （50 + 45）
COUNTIF / COUNTIFS：条件计数
用于统计符合特定条件的单元格数量。场景：统计“达标”的员工人数。
公式：`=COUNTIF(D2:D100, "达标")`
AVERAGEIF / AVERAGEIFS：条件平均值
场景：计算技术部员工的平均薪资。 公式：`=AVERAGEIFS(薪资列, 部门列, "技术部")`查找引用类：精准定位数据
VLOOKUP：经典查找函数
尽管已有新函数，VLOOKUP 仍是职场必须技能。语法：`=VLOOKUP(查找值, 查找区域, 返回列序数, [匹配模式])`
注意：查找值必须位于查找区域的列。
XLOOKUP：新一代查找神器（Excel 365/2021+）
XLOOKUP 解决了 VLOOKUP 的诸多痛点：无需指定列序数、默认精确匹配、支持反向查找。语法：`=XLOOKUP(查找值, 查找数组, 返回数组, [未找到值], [匹配模式])`
对比示例：
假设我们要根据“员工ID”查找“姓名”。
| ID | 姓名 | 部门 |
|---|---|---|
| 1001 | 张三 | 销售 |
| 1002 | 李四 | 技术 |
| 1003 | 王五 | 市场 |
VLOOKUP 写法：`=VLOOKUP(1002, A2:C4, 2, FALSE)`
XLOOKUP 写法：`=XLOOKUP(1002, A2:A4, B2:B4)`
特长：如果插入或删除了中间列，XLOOKUP 无需修改公式，而 VLOOKUP 的列序数需要手动调整，极易出错。
INDEX + MATCH：灵活查找组合
在旧版本 Excel 中，这是 VLOOKUP 的最佳替代方案，支持左右双向查找。公式：`=INDEX(返回区域, MATCH(查找值, 查找区域, 0))`
文本处理类：清洗脏数据
数据清洗是数据处理中最繁琐的一环，文本函数能事半功倍。
LEFT / RIGHT / MID：提取子字符串
LEFT：从左侧提取。 RIGHT：从右侧提取。 MID：从指定位置提取。场景：从身份证号中提取出生年份（第7-10位）。
公式：`=MID(A2, 7, 4)`
CONCATENATE / TEXTJOIN：合并文本
& 符号：最简单，如 `=A2 & "-" & B2` TEXTJOIN：更强大，可指定分隔符并忽略空单元格。 公式：`=TEXTJOIN(", ", TRUE, A2:C2)`TRIM / CLEAN：净化文本
TRIM：去除文本首尾空格（以及中间多余的空格，视版本而定，仅去首尾）。 CLEAN：去除不可打印字符。 实战：`=TRIM(CLEAN(A2))` 常用于清洗从网页或系统导出的脏数据。日期与时间类：动态时间轴
TODAY / NOW
TODAY()：返回当前日期。 NOW()：返回当前日期和时间。 技巧：按 `Ctrl + ;` 也可快速输入当前日期。DATEDIF：计算日期间隔
虽然它在函数列表中隐藏（不显示在自动完成中），但非常实用。场景：计算员工工龄。
公式：`=DATEDIF(入职日期, TODAY(), "Y")`
`"Y"`：年
`"M"`：月
`"D"`：天
`"YM"`：忽略年份的月差
EOMONTH：月末日期
场景：计算合同到期日（假设合同为每月一天）。 公式：`=EOMONTH(起始日期, 月数)` 例：`=EOMONTH("2023-1-15", 12)` 返回 2024年1月31日。数组与动态数组：现代 Excel 的力量
随着 Excel 365 的普及，动态数组函数彻底改变了公式编写形式。
UNIQUE：提取唯一值
场景：从一列包含重复项的客户名单中提取所有不重复的客户。 公式：`=UNIQUE(A2:A100)` 效果：结果会自动“溢出”到相邻单元格，无需下拉填充。FILTER：动态筛选
场景：筛选出“销售部”且“业绩大于50万”的员工。 公式：`=FILTER(A2:C100, (B2:B100="销售部") (C2:C100>50000))` 优势：结果随源数据变化而自动更新，无需使用高级筛选或数据透视表。SORT：动态排序
公式：`=SORT(数据区域, 排序列索引, 升降序)`避坑指南与最佳实践
1. 绝对引用与相对引用：
`A1`：相对引用（下拉填充时行列会变）。
`1`：绝对引用（下拉填充时行列固定）。
`A$1`：混合引用（行固定，列可变）。
提示：选中单元格按 `F4` 键可快速切换引用模式。
2. 错误值处理：
使用 `IFERROR(公式, "自定义提示")` 或 `IFNA(公式, "提示")` 让表格更美观，避免 `#N/A` 或 `#DIV/0!` 干扰阅读。
3. 性能优化：
尽量避免在整个列引用（如 `A:A`），除非数据量极小。指定具体范围（如 `A2:A1000`）能显著提升计算速度。
减少易失性函数（如 `INDIRECT`, `OFFSET`, `TODAY`, `RAND`）的使用，因为它们每次工作表计算时都会重新运行。
掌握 Excel 常用公式大全并非一蹴而就，场景化记忆与高频练习。建议初学者从 `IF`、`SUMIFS` 和 `VLOOKUP`/`XLOOKUP` 这三大金刚入手，逐步扩展到文本处理和日期函数。
随着 Excel 版本的迭代，动态数组函数（如 `FILTER`, `UNIQUE`）正在成为新的标准。拥抱变化，善用工具，你将发现数据处理不再是一项枯燥的任务，而是一次高效创造价值的旅程。
