Excel 公式技术手册
1. Excel 公式是什么
Excel 公式是写在单元格里的计算表达式，用来实现：
- 数值计算
- 逻辑判断
- 文本处理
- 日期时间运算
- 查找匹配
- 条件汇总
- 动态数组分析
公式的基本规则
Excel 公式必须以=开头，例如：
=A1+B1表示把 A1 和 B1 相加。
2. 公式的基本组成
一个公式通常由以下部分构成：
2.1 常量
直接写死的值：
=100 ="张三" =TRUE2.2 单元格引用
引用某个单元格的值：
=A1 =B2+C22.3 运算符
算术运算符
+加-减*乘/除^乘方%百分比
例如：
=A1*B1 =A1/B1 =A1^2比较运算符
=等于>大于<小于>=大于等于<=小于等于<>不等于
例如：
=A1>60文本连接运算符
&
例如：
=A1&"_"&B12.4 函数
函数是 Excel 内置的计算工具，例如：
=SUM(A1:A10) =IF(B1>60,"及格","不及格")2.5 括号
括号用于控制优先级：
=(A1+B1)/23. 单元格引用体系
这是 Excel 公式最核心的内容之一。
3.1 相对引用
例如：
=A1如果往下复制一行，会变成：
=A23.2 绝对引用
例如：
=$A$1不管复制到哪里，都固定引用 A1。
3.3 混合引用
$A1：列固定，行变化A$1：行固定，列变化
3.4 区域引用
例如：
=A1:A10 =B2:D83.5 整列/整行引用
=A:A =1:13.6 跨表引用
=Sheet2!A1 ='销售数据'!B23.7 跨工作簿引用
='[预算表.xlsx]Sheet1'!A14. 运算优先级
默认优先级大致如下：
- 括号
() - 乘方
^ - 乘除
* / - 加减
+ - - 比较运算
例如：
=2+3*4结果是 14，不是 20。
如果要先加后乘：
=(2+3)*45. 公式输入与复制规则
5.1 输入公式
在单元格输入：
=SUM(A1:A10)按回车即可。
5.2 复制公式
拖动填充柄时，相对引用会自动变化，绝对引用不会变。
5.3 常见快捷键
F4：切换引用方式
例如 A1 →$A$1→A$1→$A1Ctrl + ~：显示/隐藏公式
6. 常用函数分类总览
Excel 函数可以按用途分为：
- 数学与统计函数
- 逻辑函数
- 文本函数
- 日期时间函数
- 查找与引用函数
- 条件汇总函数
- 错误处理函数
- 动态数组函数
- 信息函数
- 财务函数
7. 数学与统计函数
7.1 SUM
求和：
=SUM(A1:A10)7.2 AVERAGE
平均值：
=AVERAGE(A1:A10)7.3 MAX / MIN
最大值、最小值：
=MAX(A1:A10) =MIN(A1:A10)7.4 COUNT / COUNTA / COUNTBLANK
COUNT：统计数字单元格COUNTA：统计非空单元格COUNTBLANK：统计空白单元格
=COUNT(A1:A10) =COUNTA(A1:A10) =COUNTBLANK(A1:A10)7.5 ROUND / ROUNDUP / ROUNDDOWN
四舍五入、向上取整、向下取整：
=ROUND(A1,2) =ROUNDUP(A1,2) =ROUNDDOWN(A1,2)7.6 INT / MOD / ABS
INT：向下取整MOD：取余数ABS：绝对值
=INT(A1) =MOD(A1,7) =ABS(A1)7.7 SUMPRODUCT
数组乘积求和，非常实用：
=SUMPRODUCT(A1:A10,B1:B10)也常用于条件统计：
=SUMPRODUCT((A1:A10="华东")*(B1:B10))8. 逻辑函数
8.1 IF
条件判断：
=IF(A1>=60,"及格","不及格")8.2 IFS
多条件判断：
=IFS(A1>=90,"优",A1>=80,"良",A1>=60,"中",TRUE,"差")8.3 AND / OR / NOT
逻辑与、或、非：
=AND(A1>=60,B1>=60) =OR(A1>=60,B1>=60) =NOT(A1>0)8.4 IFERROR / IFNA
错误处理：
=AND(A1>=60,B1>=60) =OR(A1>=60,B1>=60) =NOT(A1>0)9. 文本函数
9.1 LEFT / RIGHT / MID
提取文本：
=LEFT(A1,3) =RIGHT(A1,4) =MID(A1,2,5)9.2 LEN
文本长度：
=LEN(A1)9.3 FIND / SEARCH
查找字符位置：
=FIND("-",A1) =SEARCH("abc",A1)FIND区分大小写，SEARCH不区分。
9.4 CONCAT / TEXTJOIN / &
拼接文本：
=A1&B1 =CONCAT(A1:C1) =TEXTJOIN("-",TRUE,A1:C1)9.5 TRIM / CLEAN
清理文本：
=TRIM(A1) =CLEAN(A1)9.6 UPPER / LOWER / PROPER
大小写转换：
=UPPER(A1) =LOWER(A1) =PROPER(A1)9.7 SUBSTITUTE / REPLACE
替换文本：
=SUBSTITUTE(A1,"旧","新") =REPLACE(A1,2,3,"XYZ")10. 日期与时间函数
10.1 TODAY / NOW
当前日期、当前日期时间：
=TODAY() =NOW()10.2 DATE / TIME
构造日期和时间：
=DATE(2026,3,26) =TIME(14,30,0)10.3 YEAR / MONTH / DAY
提取年月日：
=YEAR(A1) =MONTH(A1) =DAY(A1)10.4 HOUR / MINUTE / SECOND
提取时分秒：
=HOUR(A1) =MINUTE(A1) =SECOND(A1)10.5 DATEDIF
计算日期差：
=DATEDIF(A1,B1,"Y") =DATEDIF(A1,B1,"M") =DATEDIF(A1,B1,"D")10.6 EDATE / EOMONTH
偏移月份、返回月末：
=EDATE(A1,1) =EOMONTH(A1,0)10.7 WEEKDAY / WEEKNUM
星期、周数：
=WEEKDAY(A1,2) =WEEKNUM(A1,2)11. 查找与引用函数
11.1 VLOOKUP
纵向查找：
=VLOOKUP(A1,D:F,2,FALSE)意思：
- 用 A1 去 D 列查找
- 返回第 2 列结果
- 精确匹配
11.2 HLOOKUP
横向查找：
=HLOOKUP(A1,A1:F3,2,FALSE)11.3 INDEX + MATCH
更灵活的查找组合：
=INDEX(B:B,MATCH(E1,A:A,0))11.4 XLOOKUP
新版推荐函数：
=XLOOKUP(E1,A:A,B:B,"未找到")可向左查找，写法更清晰。
11.5 XMATCH
增强版 MATCH：
=XMATCH(E1,A:A,0)
11.6 OFFSET
偏移引用：
=OFFSET(A1,2,1)
表示从 A1 开始，下移 2 行，右移 1 列。
11.7 INDIRECT
把文本转成引用：
=INDIRECT("A1")
12. 条件汇总函数
12.1 SUMIF / SUMIFS
单条件、多条件求和：
=SUMIF(A:A,"华东",B:B)
=SUMIFS(C:C,A:A,"华东",B:B,">100")
12.2 COUNTIF / COUNTIFS
单条件、多条件计数：
=COUNTIF(A:A,"华东")
=COUNTIFS(A:A,"华东",B:B,">100")
12.3 AVERAGEIF / AVERAGEIFS
条件平均：
=AVERAGEIF(A:A,"华东",B:B)
=AVERAGEIFS(C:C,A:A,"华东",B:B,">100")
13. 动态数组函数（新版 Excel 很重要）
13.1 FILTER
筛选数据：
=FILTER(A2:C100,B2:B100="华东","无结果")
13.2 SORT / SORTBY
排序：
=SORT(A2:C100,2,-1)
=SORTBY(A2:C100,B2:B100,-1)
13.3 UNIQUE
去重：
=UNIQUE(A2:A100)
13.4 SEQUENCE
生成序列：
=SEQUENCE(10)
=SEQUENCE(5,3)
13.5 RANDARRAY
生成随机数组：
=RANDARRAY(5,2)
13.6 TAKE / DROP
截取数组：
=TAKE(A2:C100,10)
=DROP(A2:C100,5)
13.7 CHOOSECOLS / CHOOSEROWS
选择列/行：
=CHOOSECOLS(A2:F100,1,3,5)
=CHOOSEROWS(A2:F100,1,3,5)
14. 错误值
Excel 常见错误：
#DIV/0!：除数为 0#N/A：未找到结果#NAME?：函数名或名称错误#REF!：引用无效#VALUE!：参数类型不对#NUM!：数字参数错误#SPILL!：动态数组溢出受阻
15. 数组思维与公式结构
很多复杂公式其实都遵循这个模式：
15.1 判断型
=IF(条件,结果1,结果2)15.2 计算型
=数值1*数值2/数值315.3 查找型
=XLOOKUP(查找值,查找列,返回列)15.4 汇总型
=SUMIFS(求和列,条件列1,条件1,条件列2,条件2)16. 公式设计规范
16.1 先分步骤，再合并
复杂公式不要一上来写成一整条。
先拆成：
- 辅助列
- 中间结果
- 最终结果
16.2 固定基准值用绝对引用
例如：
=A2*$B$116.3 展示精度和计算精度分开
如果要求总数准确，不要在明细公式中随便加ROUND，尽量用：
- 公式保留完整值
- 单元格格式控制显示位数
16.4 命名区域
把复杂引用变得更易读：
=SUM(销售额)比
=SUM(B2:B100)更清晰。
17. 常用公式清单
=SUM(A:A) =AVERAGE(A:A) =MAX(A:A) =MIN(A:A) =COUNT(A:A) =COUNTA(A:A) =ROUND(A1,2) =IF(A1>0,"是","否") =IFERROR(A1/B1,0) =LEFT(A1,3) =RIGHT(A1,4) =MID(A1,2,5) =LEN(A1) =TRIM(A1) =TODAY() =NOW() =YEAR(A1) =MONTH(A1) =DAY(A1) =SUMIF(A:A,"华东",B:B) =SUMIFS(C:C,A:A,"华东",B:B,">100") =COUNTIF(A:A,"华东") =COUNTIFS(A:A,"华东",B:B,">100") =XLOOKUP(E1,A:A,B:B,"未找到") =INDEX(B:B,MATCH(E1,A:A,0)) =FILTER(A2:C100,B2:B100="华东") =UNIQUE(A2:A100) =SORT(A2:C100,2,-1) =TEXTJOIN("-",TRUE,A1:C1)