數據處理與分析：助你成為數據達人的指南！

各位同學好！歡迎來到香港中學文憑考試（HKDSE）資訊及通訊科技科最實用、最有力量的課題之一：數據處理與分析。有沒有想過企業如何分析趨勢，或者各機構如何可靠地管理成千上萬的學生記錄？答案都在於有效處理與組織數據。

在這一章，我們將會探討必修部分考核的兩大核心工具：試算表與關聯式資料庫。你可以將試算表視為靈活的計算與建立模型網格，而資料庫則是專為維持數據完整性而設計的結構化多資料表系統。讓我們一起開始學習吧！



第一部份：精通試算表

試算表讓你可以將數值與文字數據儲存、整理、格式化及計算在由列和欄組成的網格裡面。

1.1 試算表的基本構成要素

• 儲存格 (Cell)：網格裡面的一個方格，以儲存格地址識別（例如：A1、B2）。
• 列與欄 (Row & Column)：列是一行橫向的儲存格，以數字編號（1、2、3...）；欄是一行縱向的儲存格，以字母標籤（A、B、C...）。
• 工作表與活頁簿 (Worksheet & Workbook)：工作表是單一的網格；活頁簿則是包含一張或多張工作表的試算表檔案。
• 公式 (Formula)：輸入到儲存格中的計算或運算式。所有公式都必須以等號 (\(=\)) 開頭。

重點概念：儲存格參照

1. 相對參照 (Relative Reference)（例如：A1）
當複製到不同的列或欄時，會根據相對位置自動改變。
例子：如果儲存格 C1 包含 `=A1+B1`，當向下複製到 C2 時，它會自動調整為 `=A2+B2`。

2. 絕對參照 (Absolute Reference)（例如：\$A\$1）
同時鎖定欄和列，因此複製時地址不會改變。\$ 符號用於鎖定參照。
例子：如果儲存格 H1 儲存了 5% 的強積金（MPF）供款率，計算 A2 總薪金扣除額的公式為 `=A2 * \$H\$1`。當向下複製時，A2 會更新為 A3，但 `\$H\$1` 會保持固定不變。

3. 混合參照 (Mixed Reference)（例如：\$A1 或 A\$1）
複製時只鎖定欄（`\$A1`）或只鎖定列（`A\$1`）。

1.2 公式、函數與錯誤值

運算子

• 算術運算子：`+`、`-`、`*`、`/`、`^`（次方）
• 關係（比較）運算子：`=`、`>`、`<`、`>=`、`<=`、`<>`（不等於）。這些運算子的運算結果為 TRUE（真）或 FALSE（假）。
• 邏輯運算子：`AND()`、`OR()`、`NOT()`

核心試算表函數

• 基本聚合函數：`SUM(範圍)`、`AVERAGE(範圍)`、`COUNT(範圍)`（僅計算數值）、`COUNTA(範圍)`（計算非空白儲存格）、`MAX(範圍)`、`MIN(範圍)`。
• 條件計算：
• `IF(條件判斷, 為真時返回值, 為假時返回值)`：評估條件。例如：`=IF(A2>=50, "Pass", "Fail")`
• `COUNTIF(範圍, 準則)`：計算符合條件的儲存格數目。例如：`=COUNTIF(B2:B50, ">=50")`
• `SUMIF(範圍, 準則, [加總範圍])`：將符合條件的儲存格加總。例如：`=SUMIF(A2:A30, "Class 5A", C2:C30)`
• 尋找函數：
• `VLOOKUP(尋找值, 資料表範圍, 欄編號, [範圍尋找])`：在資料表的第一欄中向下垂直搜尋，並從指定的欄中檢索數值。將 `範圍尋找` 設定為 `FALSE`（或 `0`）以進行完全相符的搜尋。
• `HLOOKUP(尋找值, 資料表範圍, 列編號, [範圍尋找])`：在資料表的第一列中橫向水平搜尋。
• 數學與排名函數：
• `INT(數值)`：將數值向下取整至最接近的整數。
• `ROUND(數值, 小數位數)`：將數值四捨五入至指定的小數位數。
• `MOD(被除數, 除數)`：返回除法運算後的餘數。
• `RANK(數值, 參照範圍, [次序])`：返回數值在列表中的排名（次序為 `0` 代表降冪，`1` 代表升冪）。

常見試算表錯誤代碼

• #DIV/0!：嘗試除以零或空白儲存格。
• #VALUE!：函數或公式中使用了錯誤的數據類型（例如：在需要數值的地方輸入了文字）。
• #REF!：儲存格參照無效（通常是因為所參照的儲存格已被刪除）。
• #NAME?：試算表無法辨識公式中的文字（通常是函數名稱拼寫錯誤）。
• #N/A：數值無法取得（常見於 `VLOOKUP` 找不到相符項目時）。

1.3 處理與分析數據

• 排序：使用單一或多個關鍵字以升冪或降冪排列記錄（例如：主要排序依班別，次要排序依學生姓名）。
• 篩選：僅顯示符合特定準則的列，同時隱藏其餘部分。
• 假設分析與目標搜尋：測試輸入參數的變更如何影響計算結果，或尋找達成目標結果所需的輸入值。
• 樞紐分析表與樞紐分析圖：互動式摘要工具，可跨列、欄、數值（例如：SUM、COUNT）及頁面篩選器來彙總大型數據集。



第二部份：關聯式資料庫

關聯式資料庫將數據組織成結構化、相互連結的二維資料表，以盡量減少數據冗餘並維持數據完整性。

2.1 資料庫結構與概念

• 資料表 (Table / Entity)：由列和欄組織而成的一組相關數據。
• 記錄 (Record / Tuple / Row)：單一數據項目，包含一個實體實例的所有屬性。
• 欄位 (Field / Attribute / Column)：具有特定數據類型（例如：文字、數字、日期/時間、布林值）的特定數據類別。
• 主鍵 (Primary Key, PK)：唯一識別資料表中每條記錄的一個欄位（或欄位組合）。它不能包含 null（空值）。
• 複合鍵 (Composite Key)：由兩個或多個欄位組合而成的主鍵。
• 外鍵 (Foreign Key, FK)：某資料表中的欄位，參照另一資料表的主鍵，從而在它們之間建立連結。

資料表關聯

• 一對一 (1:1)：資料表 A 中的每條記錄最多只關聯資料表 B 中的一條記錄。
• 一對多 (1:N)：資料表 A 中的單一記錄可以關聯資料表 B 中的多條記錄（例如：一個班別有多名學生）。
• 多對多 (N:M)：資料表 A 中的多條記錄可以關聯資料表 B 中的多條記錄（例如：學生與學會；在實作中會透過中介的關聯資料表來解決）。

數據驗證檢查

驗證規則可防止儲存不正確的數據：

• 存在檢查 (Presence Check)：確保必填欄位沒有留空。
• 範圍檢查 (Range Check)：確保數值或日期落在允許的範圍之內（例如：分數介乎 \(0\) 至 \(100\) 之間）。
• 類型檢查 (Type Check)：確保輸入的數值符合欄位的數據類型（例如：年齡欄位只可輸入數值）。
• 格式檢查 (Format Check)：確保數據符合預定義的模式（例如：香港身份證號碼格式或電話號碼模式）。

2.2 SQL 查詢 (Structured Query Language)

SQL 用於從資料庫資料表中檢索、篩選、聚合及排序數據。

核心 SQL 語法與子句

`SELECT 欄位1, 欄位2, 聚合函數(欄位3)`
`FROM 資料表名稱`
`WHERE 條件`
`GROUP BY 欄位1`
`HAVING 聚合條件`
`ORDER BY 欄位1 ASC|DESC;`

主要 SQL 子句與運算子

• SELECT 與 FROM：指定要顯示的欄位以及來源資料表。
• WHERE：在分組前篩選記錄。支援如 `=`、`<>`、`>`、`<`、`BETWEEN 數值1 AND 數值2`、`IN (數值1, 數值2)` 以及 `LIKE` 模式匹配（`%` 匹配任意字元序列；`_` 匹配單一字元）等運算子。
• 聚合函數：`COUNT()`、`SUM()`、`AVG()`、`MAX()`、`MIN()`。
• GROUP BY：將具有相同數值的列分組，以便進行聚合計算。
• HAVING：在聚合後篩選已分組的記錄（與聚合函數一同使用）。
• ORDER BY：以 `ASC`（升冪）或 `DESC`（降冪）次序為輸出排序。

SQL 查詢範例

`SELECT Class, COUNT(StudentID) AS TotalStudents, AVG(ExamScore) AS AverageScore`
`FROM Students`
`WHERE Status = 'Active'`
`GROUP BY Class`
`HAVING AVG(ExamScore) >= 60`
`ORDER BY Class ASC;`

2.3 表單與報表

• 表單 (Forms)：為使用者提供直觀的圖形介面，以便每次輸入、檢視和修改一條記錄，同時強制執行驗證規則。
• 報表 (Reports)：為列印輸出或螢幕展示組織並格式化查詢/資料表的數據，包括群組頁首、摘要總計和頁碼。


本章摘要

在試算表中，掌握相對/絕對參照、錯誤疑難排解，以及重點函數（`SUMIF`、`COUNTIF`、`VLOOKUP`、`ROUND`、`RANK`）。

在資料庫中，理解關聯結構（資料表、記錄、欄位、主鍵/外鍵、驗證規則），並能使用 `SELECT`、`WHERE`、`GROUP BY`、`HAVING` 和 `ORDER BY` 編寫精確的 SQL 查詢。