常用函数速查手册
引言
Excel函数是数据处理的利器。本章汇总最常用的函数,便于快速查阅和应用。
一、基础统计函数
1.1 求和与计数
SUM(范围)
=SUM(A1:A10) → 求和
=SUMIF(范围,条件,求和范围) → 条件求和
=SUMIFS(求和范围,条件范围1,条件1,条件范围2,条件2) → 多条件求和
AVERAGE(范围)
=AVERAGE(A1:A10) → 平均值
=AVERAGEIF(范围,条件) → 条件平均
COUNT / COUNTA / COUNTIF
=COUNT(A1:A10) → 统计数字个数
=COUNTA(A1:A10) → 统计非空单元格
=COUNTIF(范围,条件) → 条件计数
1.2 最大值最小值
MAX(范围)
=MAX(A1:A10) → 最大值
MIN(范围)
=MIN(A1:A10) → 最小值
LARGE(范围,K)
=LARGE(A1:A10,1) → 第1大的数
=LARGE(A1:A10,2) → 第2大的数
SMALL(范围,K)
=SMALL(A1:A10,1) → 最小的数
二、逻辑判断函数
2.1 IF函数
基础IF:
=IF(条件,真值,假值)
=IF(A1>100,"合格","不合格")
嵌套IF(多条件):
=IF(条件1,值1,IF(条件2,值2,IF(条件3,值3,值4)))
=IF(成绩>=90,"A",IF(成绩>=80,"B",IF(成绩>=70,"C","D")))
IFS函数(多条件,更简洁):
=IFS(条件1,值1,条件2,值2,条件3,值3,条件4,值4)
=IFS(成绩>=90,"A",成绩>=80,"B",成绩>=70,"C",成绩>=60,"D")
2.2 条件判断函数
AND(条件1,条件2,...)
=AND(A1>10,B1<20) → 同时满足两个条件
OR(条件1,条件2,...)
=OR(A1>100,B1>100) → 满足任一条件
NOT(条件)
=NOT(A1>100) → 条件不成立
IFERROR(公式,错误值)
=IFERROR(VLOOKUP(A1,表,3),0) → 查找不到时返回0
三、文本函数
3.1 提取与连接
LEFT(文本,字数)
=LEFT(A1,3) → 提取左边3个字
RIGHT(文本,字数)
=RIGHT(A1,4) → 提取右边4个字
MID(文本,起始位置,字数)
=MID(A1,2,5) → 从第2个位置开始,取5个字
CONCATENATE(文本1,文本2,...)
=CONCATENATE(A1,"-",B1) → 连接文本
或使用&符号:=A1&"-"&B1
或使用TEXTJOIN:=TEXTJOIN("-",TRUE,A1:A5)
3.2 查找与替换
FIND(查找字符,文本)
=FIND("@",A1) → 返回@的位置
SEARCH(查找字符,文本)
=SEARCH("apple",A1) → 不区分大小写搜索
SUBSTITUTE(文本,原字符,新字符)
=SUBSTITUTE(A1,"旧名字","新名字") → 替换
TRIM(文本)
=TRIM(A1) → 删除首尾空格
UPPER/LOWER(文本)
=UPPER(A1) → 转换为大写
=LOWER(A1) → 转换为小写
四、查找与引用函数
4.1 VLOOKUP
=VLOOKUP(查找值,表范围,列数,[精确匹配])
示例:
=VLOOKUP(A2,B:E,3,FALSE)
在B:E范围内查找A2的值,返回第3列的结果
常见问题:
#N/A错误 > 没找到 > 使用IFERROR包装
=IFERROR(VLOOKUP(A2,B:E,3,FALSE),"未找到")
#REF错误 > 列数超出范围 > 检查列数
FALSE vs TRUE:
FALSE = 精确匹配(推荐)
TRUE = 近似匹配(需排序)
4.2 INDEX+MATCH组合
=INDEX(返回范围,MATCH(查找值,查找范围,0))
示例:
=INDEX(C:C,MATCH(A2,B:B,0))
在B列查找A2,返回C列对应的值
优势vs VLOOKUP:
✓ 可向左查找(VLOOKUP只能向右)
✓ 更灵活,更强大
✓ 性能更好(处理大数据)
4.3 XLOOKUP(最新)
=XLOOKUP(查找值,查找数组,返回数组,[未找到时返回],[匹配模式],[搜索模式])
示例:
=XLOOKUP(A2,B:B,C:C,"未找到")
特点:
✓ 最新函数(Excel 365)
✓ 功能强大,语法简洁
✓ 自动处理错误
✓ 支持向左查找
✓ 逐步替代VLOOKUP
五、日期时间函数
5.1 日期计算
TODAY()
=TODAY() → 今天的日期
DATE(年,月,日)
=DATE(2024,1,15) → 2024年1月15日
DAY/MONTH/YEAR(日期)
=DAY(A1) → 提取日期中的日
=MONTH(A1) → 提取月份
=YEAR(A1) → 提取年份
DATEDIF(开始日期,结束日期,单位)
=DATEDIF(A1,B1,"D") → 天数差
=DATEDIF(A1,B1,"M") → 月数差
=DATEDIF(A1,B1,"Y") → 年数差
5.2 时间函数
NOW()
=NOW() → 当前日期和时间
TIME(小时,分钟,秒)
=TIME(14,30,0) → 14:30:00
HOUR/MINUTE/SECOND(时间)
=HOUR(A1) → 提取小时
=MINUTE(A1) → 提取分钟
=SECOND(A1) → 提取秒
六、高级应用
6.1 数组公式
SUM(IF(...))
=SUM(IF(条件,值,0))
输入后按Ctrl+Shift+Enter确认
示例:
=SUM(IF(A:A>100,B:B,0))
统计A列>100对应的B列值的和
SUMPRODUCT
=SUMPRODUCT(条件1*条件2*值)
更简洁的多条件求和
=SUMPRODUCT((A:A>10)*(B:B<20)*C:C)
6.2 动态公式
OFFSET(基准单元格,行偏移,列偏移,高,宽)
=OFFSET(A1,1,2,3,1)
从A1向下1行,向右2列,选取3行1列
INDIRECT(引用文本)
=INDIRECT("A"&ROW())
根据当前行号动态生成引用
CHOOSE(位置,值1,值2,...)
=CHOOSE(2,"红","绿","蓝") → 返回绿
七、函数组合模板
常见场景
场景1: 按条件统计
=COUNTIF(范围,条件)
场景2: 按条件求和
=SUMIF(范围,条件,求和范围)
场景3: 多条件求和
=SUMIFS(求和范围,条件范围1,条件1,条件范围2,条件2)
场景4: 条件平均
=AVERAGEIF(范围,条件)
场景5: 不重复计数
=SUMPRODUCT(1/COUNTIF(A:A,A:A))
场景6: 排名
=RANK(数值,范围)
场景7: 百分比排名
=PERCENTRANK(范围,值)
场景8: 数据查找
=IFERROR(VLOOKUP(...),替代值)
场景9: 条件分级
=IFS(条件1,值1,条件2,值2,...)
场景10: 错误处理
=IFERROR(公式,"错误")或=IFNA(公式,"未找到")
实用快速查询表
| 函数 | 用途 | 示例 |
|---|---|---|
| SUM | 求和 | =SUM(A1:A10) |
| AVERAGE | 平均值 | =AVERAGE(A1:A10) |
| COUNT | 计数 | =COUNT(A1:A10) |
| IF | 条件判断 | =IF(A1>100,"合格","不合格") |
| VLOOKUP | 查找 | =VLOOKUP(A2,B:E,3,FALSE) |
| INDEX/MATCH | 高级查找 | =INDEX(C:C,MATCH(A2,B:B,0)) |
| TEXT | 格式化文本 | =TEXT(A1,"0.00") |
| TODAY | 今天日期 | =TODAY() |
| DATEDIF | 日期差 | =DATEDIF(A1,B1,"D") |
| SUMIF | 条件求和 | =SUMIF(A:A,">100",B:B) |
检查清单
学习计划
- 已掌握10个基础函数
- 已掌握VLOOKUP等查找函数
- 了解数组公式原理
- 能根据需求组合使用函数
- 建立了函数速查手册
实际应用
- 在日常工作中主动使用函数
- 能快速找到合适的函数
- 遇到复杂需求能通过函数解决
- 能优化现有的函数公式
- 教会了同事常用函数
总结
函数学习的关键:
- 掌握基础: 从SUM、AVERAGE、IF开始
- 深化应用: 学习VLOOKUP、INDEX/MATCH等查找函数
- 实践应用: 在工作中反复使用
- 逐步扩展: 学习高级函数如SUMPRODUCT、OFFSET等
- 问题解决: 将需求转化为函数公式
建议:
- 将这个速查手册保存或打印
- 每周学习2-3个新函数
- 在工作中遇到需求时查阅手册
- 整理常用的函数组合
- 分享函数技巧给团队成员
掌握函数,Excel不再是表格工具,而是强大的数据分析武器!