Excel 公式大全：常用函数速查
Excel 是职场必备工具，但常用公式记不全、VLOOKUP 一用就错、SUMIFS 条件写不对。本文整理日常最常用的 30+ 函数，按场景分类速查。
52tool 在线计算器
工具地址：在线计算器
简单计算用在线计算器，复杂公式用 Excel。两者各有适用场景。
基础数学函数
SUM 求和
=SUM(A1:A10) 求和 A1 到 A10
=SUM(A1, A5, A10) 求和多个单元格
=SUM(A1:A10, 100) 求和范围 + 数值
AVERAGE 平均值
=AVERAGE(A1:A10)
MAX/MIN 最大/最小
=MAX(A1:A10)
=MIN(A1:A10)
COUNT 计数
=COUNT(A1:A10) 统计数字单元格
=COUNTA(A1:A10) 统计非空单元格
=COUNTBLANK(A1:A10) 统计空单元格
=COUNTIF(A1:A10, ">5") 统计大于 5 的
ROUND 四舍五入
=ROUND(3.14159, 2) → 3.14（保留 2 位）
=ROUNDUP(3.14, 1) → 3.2（向上舍入）
=ROUNDDOWN(3.19, 1) → 3.1（向下舍入）
=INT(3.9) → 3（取整）
=TRUNC(3.9) → 3（截断）
MOD 取余
=MOD(10, 3) → 1
判断奇偶：=IF(MOD(A1, 2)=0, "偶数", "奇数")
文本函数
CONCAT / & 连接
=CONCAT(A1, " ", B1) 连接
=A1 & " " & B1 用 & 连接
=CONCATENATE(A1, B1) 旧函数
LEFT/RIGHT/MID 截取
=LEFT("Hello", 2) → "He"
=RIGHT("Hello", 2) → "lo"
=MID("Hello", 2, 3) → "ell"（从第 2 位取 3 字符）
LEN 长度
=LEN("Hello") → 5
=LEN(A1) A1 单元格字符数
UPPER/LOWER/PROPER 大小写
=UPPER("hello") → "HELLO"
=LOWER("HELLO") → "hello"
=PROPER("hello world") → "Hello World"
TRIM 去空格
=TRIM(" hello ") → "hello"（去首尾空格，中间空格保留一个）
SUBSTITUTE 替换
=SUBSTITUTE("Hello", "l", "L") → "HeLLo"（替换所有）
=SUBSTITUTE("Hello", "l", "L", 1) → "HeLlo"（只替换第 1 个）
TEXT 格式化
=TEXT(1234.5, "#,##0.00") → "1,234.50"
=TEXT(0.85, "0%") → "85%"
=TEXT(DATE(2024,1,1), "yyyy-mm-dd") → "2024-01-01"
逻辑函数
IF 判断
=IF(A1>60, "及格", "不及格")
=IF(A1>=90, "优秀", IF(A1>=80, "良好", IF(A1>=60, "及格", "不及格")))
嵌套 IF 最多 64 层，但建议用 IFS 替代。
IFS 多条件
Excel 2016+：
=IFS(A1>=90, "优秀", A1>=80, "良好", A1>=60, "及格", TRUE, "不及格")
AND/OR/NOT
=AND(A1>0, A1<100) 两个条件都满足
=OR(A1=0, A1=100) 任一满足
=NOT(A1=0) 非
IFERROR 错误处理
=IFERROR(A1/B1, "除零错误")
=IFERROR(VLOOKUP(...), "未找到")
IFNA 处理 #N/A
=IFNA(VLOOKUP(...), "未找到")
查找函数
VLOOKUP 垂直查找
=VLOOKUP(查找值, 表区域, 列号, FALSE)
=VLOOKUP(A1, B:D, 3, FALSE) 在 B:D 区域找 A1，返回第 3 列，精确匹配
注意：
- 查找值必须在第 1 列
- FALSE 表示精确匹配
- 找不到返回 #N/A
HLOOKUP 水平查找
=HLOOKUP(A1, B1:F10, 3, FALSE)
INDEX + MATCH（更强大）
替代 VLOOKUP，更灵活：
=INDEX(返回列, MATCH(查找值, 查找列, 0))
=INDEX(C:C, MATCH(A1, B:B, 0)) 在 B 列找 A1，返回 C 列对应值
优势：
- 查找列可在任意位置
- 可向左查找
- 性能略好
XLOOKUP（Excel 365）
最强大的查找函数：
=XLOOKUP(查找值, 查找列, 返回列, "未找到", 0)
支持：
- 向左查找
- 默认精确匹配
- 找不到时返回默认值
- 返回多列
CHOOSE 选择
=CHOOSE(2, "A", "B", "C") → "B"
条件求和/计数
SUMIF 单条件求和
=SUMIF(A1:A10, ">5") 条件范围和求和范围相同时
=SUMIF(A1:A10, ">5", B1:B10) A 列大于 5 时，对 B 列求和
=SUMIF(A1:A10, "苹果", B1:B10) A 列是"苹果"时，对 B 列求和
SUMIFS 多条件求和
=SUMIFS(求和范围, 条件范围1, 条件1, 条件范围2, 条件2)
=SUMIFS(C:C, A:A, "苹果", B:B, ">5")
注意参数顺序与 SUMIF 不同！
COUNTIF 单条件计数
=COUNTIF(A1:A10, ">5")
=COUNTIF(A1:A10, "苹果")
=COUNTIF(A1:A10, "*果") 通配符：以"果"结尾
COUNTIFS 多条件计数
=COUNTIFS(A:A, "苹果", B:B, ">5")
AVERAGEIF / AVERAGEIFS
=AVERAGEIF(A1:A10, ">5", B1:B10)
=AVERAGEIFS(C:C, A:A, "苹果", B:B, ">5")
日期函数
TODAY / NOW
=TODAY() 今天日期
=NOW() 当前日期时间
DATE 构造日期
=DATE(2024, 1, 1) → 2024-01-01
YEAR / MONTH / DAY 提取
=YEAR(A1) 年份
=MONTH(A1) 月份
=DAY(A1) 日
WEEKDAY 星期
=WEEKDAY(A1) 1-7（默认周日=1）
=WEEKDAY(A1, 2) 1-7（周一=1）
EDATE 月份加减
=EDATE(A1, 3) 3 个月后
=EDATE(A1, -1) 1 个月前
EOMONTH 月末
=EOMONTH(A1, 0) 本月最后一天
=EOMONTH(A1, 1) 下月最后一天
DATEDIF 日期差（隐藏函数）
=DATEDIF(A1, B1, "Y") 相差多少年
=DATEDIF(A1, B1, "M") 相差多少月
=DATEDIF(A1, B1, "D") 相差多少天
=DATEDIF(A1, B1, "YM") 相差月份（忽略年）
=DATEDIF(A1, B1, "MD") 相差天数（忽略月年）
NETWORKDAYS 工作日
=NETWORKDAYS(A1, B1) 工作日数
=NETWORKDAYS(A1, B1, 节假日范围) 排除节假日
WORKDAY 工作日加
=WORKDAY(A1, 10) A1 后 10 个工作日
=WORKDAY(A1, 10, 节假日范围)
财务函数
PMT 等额本息
=PMT(利率/12, 期数, 本金)
=PMT(4.1%/12, 360, 1000000) 房贷月供
参考 52tool 房贷计算器。
FV 未来值
=FV(利率, 期数, 每期投入, 现值)
=FV(5%, 10, -1000) 每年投 1000，5% 利率，10 年后
PV 现值
=PV(利率, 期数, 每期)
=PV(5%, 10, -1000)
RATE 利率
=RATE(期数, 每期, 本金)
NPER 期数
=NPER(利率, 每期, 本金)
统计函数
STDEV 标准差
=STDEV.P(A1:A10) 总体标准差
=STDEV.S(A1:A10) 样本标准差
VAR 方差
=VAR.P(A1:A10)
=VAR.S(A1:A10)
PERCENTILE 百分位
=PERCENTILE(A1:A10, 0.9) 90 百分位
RANK 排名
=RANK(A1, A1:A10) 降序排名
=RANK(A1, A1:A10, 1) 升序排名
高级函数
SUMPRODUCT 数组乘积求和
=SUMPRODUCT(A1:A10, B1:B10) A*B 后求和
=SUMPRODUCT((A1:A10>5)*(B1:B10)) A 大于 5 时，对应 B 求和
ARRAYFORMULA 数组公式（旧版）
Ctrl+Shift+Enter：
{=SUM(IF(A1:A10>5, B1:B10, 0))}
新版 Excel 365 直接回车即可。
UNIQUE 去重（365）
=UNIQUE(A1:A10)
FILTER 过滤（365）
=FILTER(A1:B10, A1:A10 > 5)
SORT 排序（365）
=SORT(A1:B10, 2, -1) 按第 2 列降序
XLOOKUP 进阶
=XLOOKUP(A1, B:B, C:E) 返回 C 到 E 列
实战场景
场景 1：算工资
姓名 基本工资 绩效 应发
张三 5000 2000 =B2+C2
李四 6000 3000 =B3+C3
个税 =IF(D2-5000>0, (D2-5000)*0.1, 0)
实发 =D2-E2
场景 2：销售汇总
日期 产品 金额
2024-01-01 苹果 100
2024-01-02 香蕉 200
2024-01-03 苹果 150
=SUMIFS(C:C, B:B, "苹果") 苹果总销售
=SUMIFS(C:C, B:B, "苹果", A:A, ">2024-01-01") 1 月 1 日后苹果销售
=COUNTIF(B:B, "苹果") 苹果订单数
场景 3：考勤统计
=COUNTIF(考勤范围, "迟到") 迟到次数
=NETWORKDAYS(月初, 月末) 应出勤天数
=NETWORKDAYS(月初, 月末) - COUNTIF(考勤范围, "请假") 实际出勤
场景 4：年龄计算
=DATEDIF(A1, TODAY(), "Y") 周岁
=DATEDIF(A1, TODAY(), "M") 总月数
=DATEDIF(A1, TODAY(), "D") 总天数
参考 52tool 年龄计算器。
场景 5：生日提醒
=IF(DATE(YEAR(TODAY()), MONTH(A1), DAY(A1))=TODAY(), "今天生日",
IF(DATE(YEAR(TODAY()), MONTH(A1), DAY(A1))<TODAY(),
DATE(YEAR(TODAY())+1, MONTH(A1), DAY(A1))-TODAY(),
DATE(YEAR(TODAY()), MONTH(A1), DAY(A1))-TODAY()) & "天后生日")
参考 52tool 生日提醒。
场景 6：房贷计算
贷款 100 万，30 年，4.1% 利率
月供 =PMT(4.1%/12, 360, -1000000) → 4831
总利息 = 月供 × 360 - 1000000 → 739160
参考 52tool 房贷计算器。
常见错误
#DIV/0! 除零
=A1/B1 B1 为 0 时报错
解决：=IFERROR(A1/B1, 0)
#N/A 找不到
=VLOOKUP(...) 找不到目标
解决：=IFERROR(VLOOKUP(...), "未找到")
#VALUE! 类型错误
=A1+B1 A1 是文本时报错
解决：检查数据类型，用 =VALUE(A1)+B1
#REF! 引用无效
删除了被引用的单元格。
#NAME? 函数名错误
函数名拼错或文本未加引号。
#NUM! 数值无效
如 =SQRT(-1)。
###### 列宽不够
列太窄显示不下数字，加宽列即可。
实用技巧
技巧 1：F4 切换引用
=A1 相对引用
=$A$1 绝对引用
=$A1 列固定
=A$1 行固定
按 F4 循环切换。
技巧 2：CTRL+` 显示公式
CTRL + `（反引号）切换显示公式和结果。
技巧 3：名称管理器
选中区域 → 公式选项卡 → 定义名称：
定义"销售" = Sheet1!$A$2:$A$100
=SUM(销售) 替代 =SUM(A2:A100)
技巧 4：数据验证
数据 → 数据验证 → 允许"列表"：
来源：苹果,香蕉,橙子
单元格变成下拉选择。
技巧 5：条件格式
开始 → 条件格式 → 突出显示规则：
大于 50 → 绿色
小于 0 → 红色
常见问题解答
Q: VLOOKUP 总是返回 #N/A？
A: 检查：1) 查找值在第 1 列；2) 第 4 参数用 FALSE（精确匹配）；3) 数据无空格（用 TRIM 清理）；4) 数据类型一致（数字和文本不能匹配）。
Q: SUMIFS 和 SUMIF 参数顺序怎么记？
A: SUMIF（单条件）= (范围, 条件, 求和范围)；SUMIFS（多条件）= (求和范围, 范围1, 条件1, 范围2, 条件2)。SUMIFS 把求和范围放最前面。
Q: 怎么反向 VLOOKUP（向左查找）？
A: VLOOKUP 只能向右。用 INDEX+MATCH：=INDEX(A:A, MATCH(查找值, B:B, 0))。或用 Excel 365 的 XLOOKUP：=XLOOKUP(查找值, B:B, A:A)。
Q: 日期显示成数字怎么办？
A: 单元格格式设置为日期（右键 → 设置单元格格式 → 日期）。Excel 内部日期是数字（从 1900-01-01 算天数）。
Q: 怎么跨表引用？
A: =Sheet2!A1 或 ='Sheet Name'!A1（含空格的表名加单引号）。
Q: 公式太长怎么调试？
A: 1) 用"公式求值"逐步查看计算过程；2) 拆分成多个辅助列；3) 用名称管理器命名范围；4) 用 IFERROR 包装定位错误。
总结
Excel 公式高频但易忘，常用 30 个足够应付 90% 工作场景：
- 简单计算 → 52tool 在线计算器
- 房贷 → 房贷计算器
- 复利 → 复利计算器
- 年龄 → 年龄计算器
记住：复杂公式拆开写，多用 IFERROR 容错，VLOOKUP 学不会就学 XLOOKUP。
