Excel 函数与公式大全：构建数据思维的核心武器库
从职场小白到数据分析专家，Excel 函数与公式是您通往高效办公的必经之路。本页面不仅收录了数千个常用函数，更深度解析了数据背后的逻辑，助您掌握真正的数据处理能力。
深入理解 IF, IFS, AND, OR 等逻辑函数，让数据自动分类，告别繁琐的人工筛选。
彻底掌握 VLOOKUP, XLOOKUP, INDEX, MATCH，轻松应对跨表关联、模糊查询等复杂场景。
利用 SUMIFS, COUNTIFS, AVERAGEIF 等多条件统计函数，瞬间完成百万级数据的汇总分析。
基础函数实战：从入门到精通
对于初学者而言，理解 Excel 函数的语法结构是第一步。我们将常用的基础函数分为三大类：文本处理、日期时间以及数学运算。请通过下方的选项卡切换查看详细内容。
1. 文本处理：清洗杂乱数据的关键
在处理从系统导出的原始数据时，文本格式往往是最大的痛点。乱码、多余空格、格式不统一等问题层出不穷。以下函数是解决这些问题的利器：
| 函数名称 | 功能描述 | 典型应用场景 | 示例公式 |
|---|---|---|---|
| TRIM | 删除文本首尾及中间多余的空格 | 清洗导入系统的客户名单，去除隐形空格导致的匹配失败 | =TRIM(A2) |
| LEFT / RIGHT / MID | 分别提取字符串左侧、右侧或中间的指定长度字符 | 从身份证号中提取出生年月，或从订单号中提取地区代码 | =MID(A2, 7, 8) |
| FIND / SEARCH | 查找特定字符在文本中的位置 | 定位邮箱地址中的@符号，或提取文件名后缀 | =FIND("@", A2) |
| TEXTJOIN | 用指定分隔符连接多个文本区域 | 将多行员工姓名合并为一行，或用逗号分隔 | =TEXTJOIN(", ", TRUE, A2:A10) |
实战案例：提取手机号
假设 A2 单元格内容为“张三 13800138000"，我们需要提取出手机号。可以使用 RIGHT 函数结合 LEN 函数，或者直接利用 TEXTSPLIT（Office 365 新函数）。
=RIGHT(A2, LEN(A2)-FIND(" ", A2))这个公式的逻辑是：先找到空格的位置，然后用总长度减去空格位置，得到剩余字符数，最后向右截取这么多字符。
2. 日期时间：精准掌控时间维度
时间管理是职场必备技能，而 Excel 日期函数则是量化时间的关键。无论是计算工龄、项目工期，还是自动生成报表日期，都离不开它们。
- TODAY(): 返回当前系统日期，常用于制作动态报表，确保每天打开都是最新数据。
- NOW(): 返回当前的日期和时间，适合记录操作日志。
- DATEDIF(): 计算两个日期之间的差值（年、月、日），虽然微软官方文档将其列为隐藏函数，但其兼容性极佳，广泛用于计算工龄和年龄。
- EOMONTH(): 返回某日期前或后指定月份的最后一天的日期，非常适合计算月末截止日。
3. 数学统计：数据聚合的基础
这是 Excel 函数中使用频率最高的类别之一。从简单的求和到复杂的条件统计，构成了数据分析的骨架。
SUMIFS 与 COUNTIFS 的奥义
传统的 SUM 只能对所有数据进行求和，而 SUMIFS 则允许设置多个条件。例如：“统计华东地区、且销售额大于 10000 的所有订单总额”。
=SUMIFS(金额列，地区列，"华东", 金额列，">10000")
参数顺序至关重要：先写求和区域，再写条件区域和条件值。这种多条件筛选机制是构建复杂报表的基石。
进阶技巧：超越 VLOOKUP 的思维跃迁
当基础函数无法满足需求时，许多用户会陷入困境。事实上，Excel 函数与公式大全中最高级的技巧往往在于组合拳的使用。本节重点讲解如何通过逻辑嵌套和数组运算解决复杂问题。
1. INDEX + MATCH：万能的查找组合
虽然 VLOOKUP 广为人知，但它存在致命弱点：只能从左向右查找，且插入列会导致公式失效。相比之下，INDEX 和 MATCH 的组合是业界公认的最稳健方案。
MATCH 负责“定位”，它返回查找值在指定区域中的相对位置（行号或列号）。
INDEX 负责“取值”，它根据给定的行号和列号，从区域中返回对应的值。
支持双向查找（向左、向右、向上、向下均可）。即使你在数据中间插入了一列，只要 MATCH 找到的列号正确，INDEX 依然能准确返回数据，不会像 VLOOKUP 那样报错。
2. 动态数组公式：Excel 365 的革命
如果您使用的是 Office 365 或 Excel 2021+，那么 UNIQUE, FILTER, SORT, SEQUENCE 等新函数将彻底改变您的工作方式。
一键筛选！不再需要数据透视表或复杂的辅助列。只需输入 =FILTER(数据区域，条件区域)，即可动态返回符合条件的列表。
快速去重。直接从一列数据中提取不重复的值，生成一个动态的列表，无需使用“删除重复项”功能。
VLOOKUP 的终极进化版。支持反向查找、默认返回值、精确匹配/近似匹配灵活切换，语法更直观。
示例：使用 FILTER 动态提取高绩效员工
=FILTER(A2:D100, (C2:C100="销售部") (D2:D100>=10000), "无符合条件人员")
此公式逻辑：在 A2:D100 范围内，筛选出 C 列为“销售部”且 D 列数值大于等于 10000 的所有行。若没有结果，则显示提示文字。
数据处理：Power Query 与数据透视表深度应用
面对几十万行甚至上百万行的数据，普通的 Excel 函数可能会让电脑卡顿。此时，您需要引入更强大的工具——Power Query 和数据透视表。
Power Query：ETL 自动化流程
Power Query 是 Excel 中隐藏的“超级英雄”。它允许您录制一系列数据清洗步骤（如：删除空行、拆分列、合并表、逆透视等）。当新的数据源更新时，只需点击“刷新”，所有步骤自动重演，无需人工干预。
| 操作步骤 | Power Query 实现方式 | 传统 Excel 方法对比 |
|---|---|---|
| 合并多个文件夹文件 | 使用“从文件夹”功能，自动遍历并追加 | 需手动复制粘贴，易遗漏 |
| 逆透视列转行 | 一键“逆透视其他列”，将宽表转为长表 | 需编写复杂公式或手动调整 |
| 数据清洗 | 内置替换、修剪、类型转换向导 | 需逐个单元格操作或依赖函数 |
数据透视表：多维分析利器
数据透视表不仅仅是汇总，更是探索数据的显微镜。通过拖拽字段，您可以瞬间生成不同维度的交叉分析报表。
- 切片器 (Slicer): 添加可视化按钮，让报表互动性更强，适合制作大屏展示。
- 计算字段: 在透视表中直接使用 Excel 函数创建自定义指标，如利润率、同比增速等。
- 分组功能: 自动将日期按年、季、月分组，或将数字按区间分组。
自动化办公：VBA 宏与宏录制初探
当 Excel 内置功能达到极限，VBA (Visual Basic for Applications)便是您的终极武器。虽然编程听起来复杂，但现代 Excel 提供的“宏录制”功能让零基础用户也能快速上手。
宏录制：录屏式的编程教学
想象一下，您想每天重复执行一套复杂的操作：打开文件、格式化表头、运行几个公式、导出 PDF。以前这需要 30 分钟，现在只需录制一次，以后点击一个按钮即可完成。
如何开始录制宏？
- 点击“开发工具”选项卡 -> “录制宏”。
- 命名宏（避免中文，建议英文），指定快捷键（可选）。
- 执行您的操作（如：加粗字体、填充颜色、输入公式）。
- 停止录制。
VBA 代码示例：批量打印工作表
以下是一段简单的 VBA 代码，用于将所有工作表一次性打印出来。您可以将此代码放入模块中运行：
Sub PrintAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.PrintOut
Next ws
End Sub这段代码利用了 For Each...Next 循环结构，遍历工作簿中的每一个工作表并执行打印命令。这就是 Excel 自动化的魅力所在。
常见问题解答 (FAQ)
Q: 为什么我的公式显示 #VALUE!?
A: 这通常表示数据类型不匹配。例如，试图用数字参与文本运算，或者区域中包含非数值字符。检查单元格格式，确保参与计算的列都是正确的数字格式。
Q: 如何保护公式不被他人修改？
A: 全选工作表 -> 右键“设置单元格格式” -> “保护” -> 勾选“锁定”。然后进入“审阅”选项卡 -> “保护工作表”，设置密码。这样用户只能编辑未锁定的单元格。
Q: Excel 文件太大怎么办？
A: 尝试以下步骤：1. 删除不必要的行和列；2. 将历史数据导出到 CSV 或其他数据库；3. 移除多余的格式；4. 将文件另存为 .xlsb (二进制格式) 以减小体积。