Excel表格常用公式大全及用法详细介绍

Wiktok

10924人浏览 · 2025-09-09 16:32:20

Wiktok · 2025-09-09 16:32:20 发布

Excel 公式是提升数据处理效率的核心工具，涵盖数据计算、逻辑判断、文本处理、日期分析等多个场景。以下按 功能分类，详细介绍常用公式的语法、用法及示例，帮助新手快速上手、老手查漏补缺。

一、基础运算公式：数据计算的核心

基础运算公式是 Excel 最常用的功能，用于实现加减乘除、求和、平均值等简单计算，语法简洁，适合处理纯数值数据。

公式功能	公式语法	说明	示例（基于数据：A1=10, A2=20, A3=30）
加法	`=数值1+数值2+...`	直接用 `+` 连接多个数值 / 单元格，计算总和	`=A1+A2+A3` → 结果 60
减法	`=被减数-减数`	用 `-` 计算两个数值的差值，支持单元格引用	`=A3-A1` → 结果 20
乘法	`=数值1数值2...`	用 `*` 连接多个数值 / 单元格，计算乘积（注意：不能用 `×` 符号）	`=A1*A2` → 结果 200
除法	`=被除数/除数`	用 `/` 计算商（不能用 `÷` 符号），除数不能为 0，否则显示 `#DIV/0!`	`=A2/A1` → 结果 2
求和（单区域）	`=SUM(区域)`	计算指定单元格区域内所有数值的总和，支持连续区域（用 `:` 连接）	`=SUM(A1:A3)` → 结果 60
求和（多区域）	`=SUM(区域1, 区域2,...)`	计算多个不连续区域的总和，区域间用逗号分隔	`=SUM(A1:A2, C1:C2)`（若 C1=5, C2=15，结果 50）
平均值	`=AVERAGE(区域)`	计算指定区域内数值的算术平均值，自动忽略文本和空单元格	`=AVERAGE(A1:A3)` → 结果 20
最大值	`=MAX(区域)`	提取指定区域内的最大数值	`=MAX(A1:A3)` → 结果 30
最小值	`=MIN(区域)`	提取指定区域内的最小数值	`=MIN(A1:A3)` → 结果 10
计数（数值）	`=COUNT(区域)`	统计区域内数值型单元格的数量，忽略文本、空值、逻辑值	`=COUNT(A1:A3, B1)`（若 B1="文本"，结果 3）
计数（非空）	`=COUNTA(区域)`	统计区域内非空单元格的数量，包括文本、数值、逻辑值，忽略空值	`=COUNTA(A1:A3, B1)`（若 B1="文本"，结果 4）

二、逻辑判断公式：条件筛选与决策

逻辑公式用于根据 “条件是否成立” 返回不同结果，核心是 IF 函数，常结合 AND（同时满足）、OR（任意满足）实现多条件判断。

1. 核心函数：IF（单条件 / 嵌套多条件）

语法

=IF(条件, 条件成立时返回的值, 条件不成立时返回的值)

条件：需返回 TRUE（真）或 FALSE（假）的表达式（如 A1>10、B1="合格"）；
可嵌套 IF 实现 “多分支判断”（最多嵌套 64 层，建议不超过 3 层，避免复杂）。

示例

假设 A1 为 “学生分数”，按分数评级：

单条件：=IF(A1>=60, "及格", "不及格")（分数≥60 返回 “及格”，否则 “不及格”）；
多条件嵌套：=IF(A1>=90, "优秀", IF(A1>=80, "良好", IF(A1>=60, "及格", "不及格")))（90+→优秀，80-89→良好，60-79→及格，<60→不及格）。

2. 辅助函数：AND / OR（多条件组合）

语法

AND(条件1, 条件2,...)：所有条件都成立时返回 TRUE，否则 FALSE；
OR(条件1, 条件2,...)：任意一个条件成立时返回 TRUE，否则 FALSE。

示例

假设 A1 = 分数，B1 = 出勤天数（满勤 22 天）：

多条件同时满足：=IF(AND(A1>=60, B1>=22), "合格", "不合格")（分数及格且满勤，才 “合格”）；
多条件任意满足：=IF(OR(A1>=90, B1=22), "奖励", "无奖励")（分数优秀或满勤，都给 “奖励”）。

三、文本处理公式：字符串提取与修改

文本公式用于处理文本型数据（如姓名、地址、编号），实现提取、拼接、替换等操作，常用函数包括 CONCATENATE（拼接）、LEFT/RIGHT/MID（提取）、LEN（计数）等。

公式功能	公式语法	说明	示例（A1="Excel 公式大全"）
文本拼接	`=CONCATENATE(文本1, 文本2,...)`	将多个文本 / 单元格内容拼接成一个字符串，等价于 `&` 符号	`=CONCATENATE(A1, "2024")` 或 `=A1&"2024"` → "Excel 公式大全 2024"
提取左侧字符	`=LEFT(文本, 提取长度)`	从文本的左侧第一个字符开始，提取指定长度的字符	`=LEFT(A1, 5)` → "Excel"
提取右侧字符	`=RIGHT(文本, 提取长度)`	从文本的右侧第一个字符开始，提取指定长度的字符	`=RIGHT(A1, 4)` → "公式大全"
提取中间字符	`=MID(文本, 起始位置, 提取长度)`	从文本的指定起始位置（第 N 个字符）开始，提取指定长度的字符	`=MID(A1, 7, 2)` → "公式"（A1 第 7 个字符是 “公”，提取 2 个）
文本长度	`=LEN(文本)`	统计文本的字符数（含空格，一个汉字 / 字母 / 空格都算 1 个字符）	`=LEN(A1)` → 8（“Excel 公式大全” 共 8 个字符）
替换文本	`=SUBSTITUTE(文本, 旧字符, 新字符, [替换次数])`	将文本中的 “旧字符” 替换为 “新字符”，可选 “替换第几次”（默认全部替换）	`=SUBSTITUTE(A1, "公式", "函数")` → "Excel 函数大全"

四、日期与时间公式：时间差与周期计算

Excel 中日期本质是 “数值”（1900-01-01 对应 1，往后每天 + 1），时间是 “小数”（12:00 对应 0.5），因此可通过数值运算实现时间差计算。

公式功能	公式语法	说明	示例（A1=2024-05-01，A2=2024-05-10）
今天日期	`=TODAY()`	返回当前系统日期（仅日期，无时间），更新后会自动刷新	`=TODAY()` → 2024-05-20（假设当前日期）
现在时间	`=NOW()`	返回当前系统日期 + 时间，精确到秒，更新后自动刷新	`=NOW()` → 2024-05-20 14:30:25
日期差（天数）	`=结束日期-开始日期`	直接用日期单元格相减，返回两个日期之间的天数差	`=A2-A1` → 9（5 月 10 日 - 5 月 1 日共 9 天）
计算年 / 月 / 日	`=YEAR(日期)` / `=MONTH(日期)` / `=DAY(日期)`	从指定日期中提取 “年份”“月份”“日” 的数值	`=YEAR(A1)` → 2024；`=MONTH(A1)` → 5；`=DAY(A1)` → 1
时间差（小时）	`=(结束时间-开始时间)*24`	时间差默认是 “天数”，乘以 24 转换为 “小时”（同理：×1440 转分钟，×86400 转秒）	若 A1=9:00，A2=11:30，`=(A2-A1)*24` → 2.5（2.5 小时）
日期加减	`=DATE(年, 月±N, 日)`	对指定日期的 “月” 或 “日” 进行加减（避免跨月 / 跨年错误，比直接 ± 更准确）	`=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))` → 2024-06-01（A1 加 1 个月）

五、查找与引用公式：精准定位数据

当需要从大量数据中快速找到指定值（如查员工工资、查商品价格）时，查找公式是核心工具，常用 VLOOKUP（纵向查找）、HLOOKUP（横向查找）、INDEX+MATCH（灵活查找）。

1. 纵向查找：VLOOKUP（最常用）

语法

=VLOOKUP(查找值, 查找区域, 返回列数, [匹配类型])

查找值：要搜索的目标（如 “员工姓名”“商品编号”）；
查找区域：必须包含 “查找值所在列” 和 “要返回值所在列”，且 “查找值所在列” 要放在区域的第一列；
返回列数：“要返回值” 在 “查找区域” 中的列序号（从左数第 N 列）；
匹配类型：0 或 FALSE 表示 “精确匹配”（推荐，避免错误），1 或 TRUE 表示 “近似匹配”（需先排序）。

示例

假设有员工表（A 列：姓名，B 列：部门，C 列：工资）：

A（姓名）	B（部门）	C（工资）
张三	技术部	8000
李四	销售部	6000

要查 “张三” 的工资：=VLOOKUP("张三", A1:C2, 3, 0) → 8000
（查找值 “张三”，查找区域 A1:C2，返回第 3 列（工资），精确匹配）。

2. 横向查找：HLOOKUP

语法

=HLOOKUP(查找值, 查找区域, 返回行数, [匹配类型])

与 VLOOKUP 类似，区别是：HLOOKUP 按 “行” 查找，“查找值所在行” 需放在区域的第一行，“返回行数” 是目标值在区域中的行序号。

示例

假设有月度销售表（A 列：月份，B 列：1 月，C 列：2 月）：

A（项目）	B（1 月）	C（2 月）
销售额	50000	60000
成本	30000	35000

要查 “2 月销售额”：=HLOOKUP("销售额", A1:C2, 2, 0) → 60000
（查找值 “销售额”，查找区域 A1:C2，返回第 2 行（2 月数据），精确匹配）。

3. 灵活查找：INDEX + MATCH（替代 VLOOKUP）

VLOOKUP 仅能按 “区域第一列” 查找，而 INDEX+MATCH 可按 “任意列 / 行” 查找，更灵活。

语法组合

=INDEX(返回区域, MATCH(查找值, 查找列/行, 0))

INDEX(返回区域, 行号)：返回 “返回区域” 中指定 “行号” 的数值；
MATCH(查找值, 查找列/行, 0)：返回 “查找值” 在 “查找列 / 行” 中的行号 / 列号（精确匹配）。

示例

用员工表查 “张三” 的工资（查找列是 A 列姓名，返回区域是 C 列工资）：
=INDEX(C1:C2, MATCH("张三", A1:A2, 0)) → 8000
（MATCH 先找到 “张三” 在 A 列的行号 1，INDEX 再返回 C 列第 1 行的工资 8000）。

六、进阶公式：数据统计与分析

适用于复杂数据场景（如按条件求和、统计唯一值、排名），常用 SUMIF（条件求和）、COUNTIF（条件计数）、RANK（排名）等。

1. 条件求和：SUMIF / SUMIFS

SUMIF：单条件求和；
SUMIFS：多条件求和（条件需同时满足）。

语法

单条件：=SUMIF(条件区域, 条件, [求和区域])（若 “条件区域”=“求和区域”，可省略 “求和区域”）；
多条件：=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2,...)（求和区域必须在最前面）。

示例

假设有销售表（A 列：产品，B 列：地区，C 列：销量）：

A（产品）	B（地区）	C（销量）
手机	北京	100
电脑	北京	50
手机	上海	80

单条件：求 “北京” 的总销量 → =SUMIF(B1:B3, "北京", C1:C3) → 150；
多条件：求 “北京” 的 “手机” 销量 → =SUMIFS(C1:C3, A1:A3, "手机", B1:B3, "北京") → 100。

2. 条件计数：COUNTIF / COUNTIFS

COUNTIF：单条件计数；
COUNTIFS：多条件计数。

语法

单条件：=COUNTIF(条件区域, 条件)；
多条件：=COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2,...)。

示例

用上述销售表：

单条件：统计 “手机” 的销售记录数 → =COUNTIF(A1:A3, "手机") → 2；
多条件：统计 “北京” 且 “销量 > 80” 的记录数 → =COUNTIFS(B1:B3, "北京", C1:C3, ">80") → 1。

3. 数据排名：RANK.EQ

语法

=RANK.EQ(要排名的数值, 排名区域, [排序方式])

排序方式：0 或省略表示 “降序”（数值越大排名越前），1 表示 “升序”（数值越小排名越前）；
若有重复值，会返回相同排名，后续排名会跳过（如两个第 2 名，下一个是第 4 名）。

示例

假设 A 列是分数（A1=90, A2=85, A3=90）：

降序排名：=RANK.EQ(A1, A1:A3, 0) → 1（A1=90 是第 1 名）；=RANK.EQ(A3, A1:A3, 0) → 1（A3=90 与 A1 并列第 1）；=RANK.EQ(A2, A1:A3, 0) → 3（A2=85 是第 3 名）。

七、公式使用注意事项

公式开头必须加 =：Excel 识别公式的标志，否则会视为文本（如输入 “1+1” 会显示 “1+1”，输入 “=1+1” 才显示 2）；
单元格引用要准确：避免引用错误（如引用空单元格会返回 0，引用文本单元格会导致计算错误）；
文本条件需加引号：公式中的文本型条件（如 “合格”“北京”）必须用英文半角引号包裹，数值条件（如 > 80、=100）无需引号；
处理错误值：若公式返回 #DIV/0!（除数为 0）、#N/A（查找不到值），可加 IFERROR 优化：
示例：=IFERROR(VLOOKUP("王五", A1:C2, 3, 0), "无此数据") → 查找不到 “王五” 时返回 “无此数据”，而非 #N/A。