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