第 2 章：公式与函数基础¶
从"计算器"到"计算引擎"——掌握 Excel 最核心的能力
公式和函数是 Excel 的灵魂。本章系统讲解单元格引用、核心函数分类和公式调试技巧，帮你建立扎实的函数基础。
2.1 单元格引用的三种模式¶
相对引用、绝对引用、混合引用¶
把单元格引用想象成"指路"的方式：
| 引用类型 | 写法 | 比喻 | 拖拽填充时 |
|---|---|---|---|
| 相对引用 | A1 |
"往前走两步" | 引用位置相对变化 |
| 绝对引用 | $A$1 |
"北京市朝阳区 XX 路 1 号" | 引用位置固定不变 |
| 混合引用 | $A1 或 A$1 |
"A 路往前走两步" | 部分固定，部分变化 |
实战：理解三种引用
| A | B | C | D |
|---|---|---|---|
| 单价 | 数量 | 公式 | 结果 |
| 100 | 5 | =A2*B2 |
500 |
| 100 | 5 | =$A$2*B2 |
500 |
| 100 | 5 | =A$2*B2 |
500 |
将 C 列公式向下拖拽：
- =A2*B2 → =A3*B3（都变了）
- =$A$2*B2 → =$A$2*B3（单价不变，数量变）
- =A$2*B2 → =A$2*B3（行不变，列可变）
F4 键快速切换引用模式
在编辑公式时，选中单元格引用，按 F4 循环切换：A1 → $A$1 → A$1 → $A1 → A1
2.2 核心函数分类¶
数学与统计函数¶
| 函数 | 用途 | 示例 |
|---|---|---|
SUM |
求和 | =SUM(A1:A10) |
SUMIF |
条件求和 | =SUMIF(B:B,"销售部",C:C) |
SUMIFS |
多条件求和 | =SUMIFS(C:C,A:A,">100",B:B,"华东") |
AVERAGE |
平均值 | =AVERAGE(A1:A10) |
AVERAGEIF |
条件平均 | =AVERAGEIF(B:B,">60") |
COUNT |
计数（数字） | =COUNT(A1:A10) |
COUNTA |
计数（非空） | =COUNTA(A1:A10) |
COUNTIF |
条件计数 | =COUNTIF(B:B,"通过") |
COUNTIFS |
多条件计数 | =COUNTIFS(A:A,">80",B:B,"男") |
MAX / MIN |
最大/最小值 | =MAX(A1:A10) |
RANK |
排名 | =RANK(A1,$A$1:$A$10) |
SUBTOTAL |
可见单元格统计 | =SUBTOTAL(9,A1:A10) |
SUBTOTAL vs SUM
SUBTOTAL 只统计 可见单元格 （筛选后隐藏的行不计入）。第一个参数：9=SUM、1=AVERAGE、2=COUNT、4=MAX、5=MIN。
逻辑函数¶
| 函数 | 用途 | 示例 |
|---|---|---|
IF |
条件判断 | =IF(A1>60,"及格","不及格") |
IFS |
多条件判断 | =IFS(A1>=90,"A",A1>=80,"B",A1>=60,"C") |
AND |
全部为真 | =AND(A1>0,B1>0) |
OR |
任一为真 | =OR(A1="是",B1="是") |
NOT |
取反 | =NOT(A1="") |
IFERROR |
错误处理 | =IFERROR(VLOOKUP(...),"未找到") |
SWITCH |
精确匹配 | =SWITCH(A1,1,"优",2,"良",3,"中","差") |
实战：绩效评级
注意最后的 TRUE 作为"兜底"条件，捕获所有不满足前面条件的情况。
文本函数¶
| 函数 | 用途 | 示例 |
|---|---|---|
LEFT / RIGHT / MID |
提取子串 | =MID(A1,7,8) 提取身份证出生日期 |
LEN |
字符长度 | =LEN(A1) |
CONCAT / TEXTJOIN |
文本合并 | =TEXTJOIN(",",TRUE,A1:A5) |
TEXT |
数字格式化 | =TEXT(A1,"yyyy年m月d日") |
FIND / SEARCH |
查找位置 | =FIND("@",A1) |
SUBSTITUTE |
替换文本 | =SUBSTITUTE(A1," ","") 去除空格 |
TRIM |
去除多余空格 | =TRIM(A1) |
UPPER / LOWER / PROPER |
大小写转换 | =PROPER(A1) |
2.3 公式调试技巧¶
常见错误值¶
| 错误值 | 含义 | 常见原因 |
|---|---|---|
#DIV/0! |
除以零 | 分母为空或为零 |
#N/A |
值不可用 | VLOOKUP 找不到匹配值 |
#NAME? |
无法识别的名称 | 函数名拼写错误 |
#VALUE! |
值类型错误 | 对文本执行数学运算 |
#REF! |
引用无效 | 引用的单元格被删除 |
#NUM! |
数值无效 | 计算结果超出范围 |
#NULL! |
空交集 | 引用运算符错误 |
公式审核工具¶
| 工具 | 快捷键 | 用途 |
|---|---|---|
| 追踪引用单元格 | Ctrl+[ |
显示当前公式引用了哪些单元格 |
| 追踪从属单元格 | Ctrl+] |
显示哪些公式引用了当前单元格 |
| 显示公式 | Ctrl+~ |
切换显示公式/显示结果 |
| 公式求值 | 公式 → 公式求值 | 逐步查看公式计算过程 |
| 错误检查 | 公式 → 错误检查 | 自动定位公式错误 |
F9 键局部求值
在编辑栏中选中公式的一部分，按 F9 可以查看该部分的计算结果。按 Esc 恢复原公式（ 不要按 Enter，否则公式会被替换为计算结果 ）。
要点总结¶
- 理解相对引用、绝对引用、混合引用的区别，能用 F4 快速切换
- 掌握 SUMIF/SUMIFS、COUNTIF/COUNTIFS 等条件统计函数
- 能使用 IF/IFS 进行多条件判断，配合 AND/OR 构建复杂逻辑
- 掌握 LEFT/RIGHT/MID/TEXTJOIN 等文本处理函数
- 能识别常见错误值并使用公式审核工具调试
课后练习¶
- 创建一个成绩表，使用 IFS 函数自动评定等级（A/B/C/D）
- 使用 SUMIFS 统计各部门、各季度的销售额
- 使用文本函数从身份证号中提取出生日期和性别
下一章预告： 第 3 章：数据分析工具 —— 排序、筛选、分类汇总、模拟分析，让数据回答业务问题。