4.2 · 单元格引用与函数
目标： 使用相对、绝对、混合引用；为每个任务挑对内置函数。
单元格引用 —— 相对 vs 绝对 vs 混合
复制公式时，引用按类型自动调整。
| 类型 | 语法 | 向下复制时 |
|---|---|---|
| 相对 | A1 | 行变（如 → A2、A3） |
| 绝对 | $A$1 | 永远是 $A$1 |
| 混合（列锁定） | $A1 | 行变，列锁 |
| 混合（行锁定） | A$1 | 列变，行锁 |
实例 · 为什么绝对引用重要
B2:B13 包含 12 个月的销售。在 C2 我们想标「Above」或「Below」年均值，然后向下复制到 C13。
text
✗ =IF(B2 > AVERAGE(B2:B13), "Above", "Below")1
复制到 C3 时，公式变成：
text
=IF(B3 > AVERAGE(B3:B14), "Above", "Below")1
区域往下滑到 B3:B14，错了 —— B14 是空、B2 被排除。
text
✓ =IF(B2 > AVERAGE($B$2:$B$13), "Above", "Below")1
$ 锁住区域，复制时维持 $B$2:$B$13。
常用运算符与函数
数学函数
| 函数 | 说明 | 例子 |
|---|---|---|
SUM(range) | 总和 | =SUM(B2:B13) |
AVERAGE(range) | 平均 | =AVERAGE(B2:B13) |
MIN(range) MAX(range) | 最小 / 最大 | =MIN(B2:B13) |
COUNT(range) | 仅数字 | =COUNT(B2:B13) |
COUNTA(range) | 非空单元格 | =COUNTA(A2:A100) |
COUNTIF(range, criteria) | 按条件计数 | =COUNTIF(B2:B100, ">=80") |
SUMIF(range, criteria, sum_range) | 按条件求和 | =SUMIF(B2:B100, "F.4A", C2:C100) |
ROUND(num, digits) | 四舍五入 | =ROUND(3.14159, 2) → 3.14 |
INT(num) | 截尾取整 | =INT(3.9) → 3 |
MOD(a, b) | 取余 | =MOD(10, 3) → 1 |
ABS(num) | 绝对值 | =ABS(-5) → 5 |
逻辑函数
| 函数 | 说明 | 例子 |
|---|---|---|
IF(cond, then, else) | 条件 | =IF(B2>=50, "Pass", "Fail") |
IFS(cond1, val1, cond2, val2, ...) | 多路 | =IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F") |
AND(c1, c2, ...) | 布尔与 | =AND(B2>=50, B3>=50) |
OR(c1, c2, ...) | 布尔或 | =OR(B2="F.4A", B2="F.4B") |
NOT(c) | 布尔非 | =NOT(B2=0) |
查找函数
| 函数 | 说明 | 例子 |
|---|---|---|
VLOOKUP(value, table, col_index, FALSE) | 纵向查找 | =VLOOKUP(A2, $E$2:$F$10, 2, FALSE) |
HLOOKUP(value, table, row_index, FALSE) | 横向查找 | （少用） |
INDEX(range, row, col) | 取某格 | =INDEX($E$2:$F$10, MATCH(A2, $E$2:$E$10, 0), 2) |
MATCH(value, range, 0) | 找位置 | =MATCH("Alice", $A$2:$A$100, 0) |
文本函数
| 函数 | 说明 | 例子 |
|---|---|---|
LEN(text) | 长度 | =LEN("HKDSE") → 5 |
LEFT(text, n) | 前 n 字 | =LEFT("HKDSE", 2) → "HK" |
MID(text, start, len) | 子串 | =MID("HKDSE", 2, 2) → "KD" |
UPPER(text) / LOWER(text) | 大小写 | =UPPER("dse") → "DSE" |
TRIM(text) | 去多余空格 | =TRIM(" HK ") → "HK" |
日期函数
| 函数 | 说明 |
|---|---|
TODAY() | 今天日期 |
NOW() | 当前日期 + 时间 |
YEAR(date) MONTH(date) DAY(date) | 取年 / 月 / 日 |
DATEDIF(start, end, "Y") | 间隔年数 |
实例
例 A · 自动评级一个班
text
=IF(C2>=80,"A",IF(C2>=70,"B",IF(C2>=60,"C","F")))1
更整洁（用 IFS）：
text
=IFS(C2>=80,"A", C2>=70,"B", C2>=60,"C", TRUE,"F")1
例 B · 按班级数及格人数
text
=COUNTIFS(B2:B100, "F.4A", C2:C100, ">=50")1
例 C · 查表得等级
| E | F |
|---|---|
| 80 | A |
| 70 | B |
| 60 | C |
| 0 | F |
text
=VLOOKUP(C2, $E$2:$F$5, 2, TRUE) ← TRUE = 近似匹配（用于已排序区域）1
学生常见错误
- 忘了
$，复制后总和算错。 - 这里用
=而非==没问题（电子表格用单一=）。 - 在未排序区域用
VLOOKUP加TRUE→ 匹配错。 - 把
IF(cond, false_value, true_value)的参数顺序写反（then 在前）。
练习活动
给一个 B2:B31 是分数的班级名单：
- 在
D1写一个公式显示班级平均。 - 在
D2写一个公式显示得分 ≥ 80 的人数。 - 在
E2写一个（可下拉）公式显示每位学生分数占最高分的百分比。 - 在
F2写一个用VLOOKUP把分数翻成等级的公式，等级表在H2:I5。
参考答案
=AVERAGE(B2:B31)=COUNTIF(B2:B31, ">=80")=B2/MAX($B$2:$B$31)*100=VLOOKUP(B2, $H$2:$I$5, 2, TRUE)
考试式题目
题（5 分）： 单元格
B2:B25储存一家冰淇淋店一个月的每日销售。写 Excel 公式计算：(a) 当月总销售。 (b) 平均日销售。 (c) 销售超过 HK$2,000 的天数。 (d) 当月销售最高那天的日数（假设日数在
A2:A25）。 (e) 总销售超过 HK$60,000 时标「Bonus」，否则「Standard」。
参考答案：
(a) =SUM(B2:B25) (b) =AVERAGE(B2:B25) (c) =COUNTIF(B2:B25, ">2000") (d) =INDEX(A2:A25, MATCH(MAX(B2:B25), B2:B25, 0)) (e) =IF(SUM(B2:B25)>60000, "Bonus", "Standard")
关键要点
$锁定引用。复制时用绝对 / 混合引用。- 熟悉核心几大家：数学、逻辑、查找、文本、日期。
- 选能用的最简单公式。
➡️ 下一节：4.3 数据透视表与 What-if