學習筆記：單元 8 – 試算表 (9626 AS Level IT)
你好，未來的 IT 專業人員！試算表（Spreadsheets）或許是你這門課程中最實用的核心技能。它們不僅僅是用來進行加總計算，更是全球通用、功能強大的工具，廣泛應用於財務分析、數據建模、報告製作及解難過程。
本章將教你如何建構穩健、安全、經過測試且版面美觀的試算表。精通這些技巧對 Paper 2（實作考試）至關重要，並能顯著提升你對相關單元（如「數據分析」單元 17 及「建模」單元 9）的理解。讓我們開始吧！
8.1 建立試算表：結構、公式與函數
頁面結構與呈現
設計良好的試算表必須符合受眾與任務需求。這通常涉及在輸入數據前，先進行周詳的版面規劃。
- 頁面方向： 根據列印數據的需求，選擇「直向」（Portrait）或「橫向」（Landscape）。例如，包含多個欄位的月度預算表通常需要「橫向」。
- 頁面大小： 通常為 A4 或 Letter，視乎地區標準而定。
- 頁面縮放 (Fit to Page)： 調整縮放比例，確保整張工作表能整齊地列印在特定數量的頁面上（例如，將所有欄位調整至單頁寬度內）。
- 頁邊距 (Margins)： 調整列印範圍周圍的留白空間。
- 頁首與頁尾 (Header and Footer)： 用於包含標題、日期、頁碼或檔案路徑等標準資訊，以便作文件說明。
活頁簿與工作表管理
要處理複雜數據，你需要能夠有效地操控格線結構。
- 插入/刪除/隱藏列與欄： 對於整理新數據或暫時隱藏敏感資訊非常重要。
- 調整列與欄的大小： 調整尺寸以確保所有數據可見（例如使用「自動調整欄寬」功能）。
- 合併儲存格： 合併多個儲存格（常用於跨多個欄位置中標題）。（合併時要小心，因為它有時會使計算變得複雜！）
- 保護內容： 保障數據完整性。你必須能夠保護個別儲存格、列、欄、整個工作表或整個活頁簿結構。這通常涉及設置密碼，防止未經授權的變更影響公式或結構。
- 凍結窗格： 鎖定特定的列（通常是標題列）或欄，以便在瀏覽大型數據集時，這些資訊仍能保持可見。
小貼士： 在商業環境中，保護功能是關鍵。如果你製作一個供員工輸入銷售數據的試算表，應鎖定包含計算公式的儲存格，以防止出現人為錯誤。
控制數據輸入（資料驗證）
資料驗證（Validation）確保輸入的數據合理且正確。
- 驗證技巧： 利用試算表功能限制輸入內容。例如，確保日期輸入格式正確，或數字在特定範圍內。
- 下拉式選單（清單驗證）： 最常見的技巧，強制使用者從預設清單中選擇（例如，選擇「男」或「女」而非讓使用者輸入「M」或「F」）。
- 輸入訊息與錯誤訊息： 當使用者選取儲存格時提供有用的提示（輸入訊息），並在輸入無效數據時提供清晰、具描述性的警告（錯誤訊息）。
8.1 公式、函數與參照
理解參照（核心概念）
參照（Referencing）決定了公式複製到其他儲存格時會如何變化。
類比： 想像一下給予方向指引：
- 相對參照（例如 A1）： 參照會根據其新位置而改變。如果你將公式從 B1 複製到 C1，A1 會變成 B1（「去隔壁那棟房子。」）。
- 絕對參照（例如 $A$1）： 欄與列都被鎖定。當複製公式時，參照永遠不變（「始終去『主街 1 號』這個確切地址。」）。
- 混合參照（例如 $A1 或 A$1）： 其中一部分（欄或列）被鎖定，另一部分則為相對的（「$A1」表示始終查看 A 欄，但列數會隨位置移動。）。
你也必須知道如何使用已命名儲存格及已命名範圍（例如，使用『TaxRate』代替『$B$5』）。這能提高可讀性，並讓跨工作表或活頁簿的參照變得更容易。
基礎計算與統計函數
這些函數執行數學及統計運算：
- 基本算術： 加 (\(+\))、減 (\(-\))、乘 (\(*\))、除 (\(/\)) 及指數（次方，例如 \(A1^2\)）。
- SUM： 加總一個範圍內的數值。
- AVERAGE： 計算算術平均數。
- MIN, MAX： 分別找出最小值與最大值。
- MAXA / MINA： 找出清單中的最大或最小值，包含文字與邏輯值（但通常 MIN/MAX 僅用於數字數據）。
- INT： 將數字向下捨入至最接近的整數。
- ROUND： 將數字四捨五入至指定的位數。
- SUBTOTAL： 為已篩選或分組的數據執行統計函數（如 SUM 或 AVERAGE）。
計數與條件式彙總函數
這些函數用於根據特定準則計數或進行加總/平均。
- COUNT： 計算包含數字的儲存格個數。
- COUNTA： 計算非空儲存格的個數（包含文字或數字）。
- COUNTBLANK： 計算範圍內空儲存格的個數。
- COUNTIF / COUNTIFS： 計算符合單一條件（COUNTIF）或多個條件（COUNTIFS）的儲存格數量。
- SUMIF / SUMIFS： 加總符合單一條件（SUMIF）或多個條件（SUMIFS）的儲存格數值。
- AVERAGEIF / AVERAGEIFS： 計算符合單一或多個條件的儲存格平均值。
- MAXIF / MAXIFS / MINIF / MINIFS： 根據指定條件找出範圍內的最大或最小值。
查找函數（提取數據）
查找函數（Lookup functions）根據符合的值從表格中提取數據。
- VLOOKUP： 在範圍的第一欄搜尋值，並傳回同一列中指定欄位的值（垂直查找）。
- HLOOKUP： 在範圍的第一列搜尋值，並傳回同一欄中指定列位的值（水平查找）。
- XLOOKUP： 一種現代且強大的查找函數，在某些軟體中正取代 VLOOKUP/HLOOKUP，允許在任何欄位進行搜尋。
- LOOKUP： 一種基礎且較缺乏彈性的查找函數。
- INDEX / MATCH： 兩者結合使用，通常比 VLOOKUP 更具彈性與效率，讓你根據範圍內的位置搜尋並傳回值。
決策、邏輯、日期與文字函數
決策/邏輯函數： 用於進行決策或結合條件。
- IF / 巢狀 IF： 檢查條件是否為真（True）或假（False），若為真傳回一個值，若為假傳回另一個。巢狀 IF 指將一個 IF 函數放入另一個中，以處理多種結果。
- IFS： 一種現代函數，無需複雜的巢狀結構即可處理多個條件。
- AND / OR： 用於 IF 語句中，檢查所有條件是否為真 (AND) 或至少有一個條件為真 (OR)。
日期與時間函數： 用於操控時間相關的數據。
- DATE / TIME： 用於建構有效的日期或時間值。
- DAY / MONTH / YEAR： 從日期中提取對應的成分。
- WEEKDAY： 傳回指定日期的星期幾。
字串/文字函數： 用於操控文字數據。
- 連接字串 (Concatenate)： 將兩個或多個文字串合併（例如合併名字與姓氏）。
- 提取數值： 從文字欄位中提取數字。
- LEFT, RIGHT, MID： 從字串的左側、右側或中間提取指定數量的字元。
- FIND： 定位一個文字串在另一個文字串中的位置。
- 測試儲存格內容： 檢查儲存格是否包含文字、數字或為空（例如結合 IF 語句與 ISNUMBER 或 ISTEXT 函數）。
錯誤捕捉與資料旋轉
- ISERROR / IFERROR： 用於檢查公式是否產生錯誤（例如 #DIV/0!）。IFERROR 常被用於顯示空白儲存格或自訂訊息，代替難看的錯誤代碼。
- TRANSPOSE： 旋轉數據，將欄位轉為列，列轉為欄位。
關鍵要點（函數）： 將函數視為捷徑。與其手動加總 100 個儲存格，不如使用 SUM。在解決實際問題時，請先識別所需的函數類型：計算、計數、邏輯或查找。
8.2 測試試算表
充滿錯誤的試算表是毫無用處的！你必須針對計算（公式）與結構（版面、保護、驗證）進行測試。
設計測試計劃
測試計劃必須系統化地檢查試算表結構的所有關鍵組件：
- 測試函數：計算（SUM, AVERAGE, IF 語句）是否傳回預期的結果？
- 測試驗證規則：系統是否正確拒絕異常數據，並接受正常/極端數據？
- 測試設定格式化的條件：當值符合定義的準則時，儲存格是否正確變更顏色或樣式？
必要的測試數據類型
測試時，你必須選擇能確保穩健性的數據。
- 正常數據 (Normal Data)： 屬於有效且預期會被輸入的數據（例如，範圍為 1 至 100，正常數據為 50）。
- 極端數據 (Extreme Data)： 屬於有效但位於可接受範圍邊界或極限的數據（例如範圍的邊界值，即 1 和 100）。
- 異常數據 (Abnormal Data)： 屬於無效且應被驗證規則拒絕的數據（例如，若預期輸入數字，異常數據可能是文字，或是範圍外的數字，如 101 或 0）。
常見錯誤： 學生常忘記測試*異常*數據。如果驗證規則無法拒絕錯誤數據，那麼它就毫無意義！
8.3 使用與分析數據
提取與搜尋數據
為了專注於特定資訊，你需要使用「篩選」（Filter）工具進行提取與搜尋。
- 根據資料類型搜尋：文字、數字或日期與時間值。
- 使用布林運算子 (AND, OR, NOT)：例如篩選「銷售部」且薪水大於 $50,000 的員工。
- 使用比較運算子：>（大於）、<（小於）、=（等於）、>=、<=。
- 使用文字篩選：搜尋包含、以特定文字串開頭或結尾的儲存格。
排序數據
排序將數據按有意義的順序排列。
- 遞增： A 到 Z，1 到 10，或日期由遠至近。
- 遞減： Z 到 A，10 到 1，或日期由近至遠。
- 數據可以根據單一欄位或多個欄位進行排序（例如先按「部門」排序，再按「姓氏」排序）。
彙總數據
處理龐大的數據集時，彙總對於管理與分析至關重要。
- 小計 (Subtotals)： 每當指定欄位的數據發生變化時，自動計算並顯示彙總數據（如 SUM 或 COUNT）。它還會建立大綱群組，以便摺疊或展開數據。
- 樞紐分析表 (Pivot Tables)： 一種功能強大的工具，用於快速交叉列表、分組並彙總大型數據集。它允許使用者動態重新排列與分析數據，而無需更改原始數據。
- 樞紐分析圖 (Pivot Charts)： 直接從樞紐分析表建立的圖表，確保當底層的樞紐分析表調整時，視覺顯示會即時更新。
匯入與匯出數據
- 匯入數據： 將外部數據引入試算表，通常來自 CSV（逗號分隔值）或 TXT（純文字）檔案。
- 匯出數據： 以不同格式儲存試算表或報告，例如 CSV、TXT、PDF（用於靜態報告），甚至將特定範圍匯出為圖表。
你知道嗎？ CSV 檔案對於數據交換非常重要，因為它們簡單且具通用性，這意味著幾乎所有的軟體（資料庫、試算表、分析工具）都能理解它們。
8.4 圖表
數據視覺化對於清晰的溝通至關重要，特別是針對非專業受眾。你必須適當地選擇並格式化圖表類型。
建立圖表（選擇數據）
第一步是選擇正確的數據序列：
- 使用連續數據（數據序列相鄰，例如 A 欄與 B 欄）。
- 使用不連續數據（數據序列不相鄰，例如 A 欄與 D 欄）。這通常需要選取第一個範圍，按住 Ctrl/Cmd 鍵，再選取第二個範圍。
- 使用指定範圍（例如 A1:A10）。
選擇正確的圖表類型
你的選擇完全取決於圖表的目的：
- 長條圖 / 比較長條圖： 極適合比較離散的數據類別，或並排顯示多個序列（例如比較 5 個不同區域的銷售額）。
- 圓形圖： 顯示整體中的比例或百分比（例如市場佔有率）。你必須學會分離扇區（切片）以作強調。
- 折線圖 / 比較折線圖： 用於顯示趨勢或連續時間段內的變化（例如一年內的股價波動）。
- 組合圖： 顯示兩種類型的數據（例如在同一個圖表上顯示氣溫（折線）與降雨量（長條））。
必要的圖表格式化
格式化能將基礎圖表轉化為專業報告。
- 標題： 清晰、具描述性的標題。
- 圖例： 解釋每種顏色/圖案/線條代表的意義。
- 座標軸標籤（類別與數值）： 清晰標示 X（類別）與 Y（數值）軸。
- 刻度（最大值與最小值）： 為數值軸設定適當限制，以避免視覺扭曲。
- 數據間隔： 定義座標軸上刻度的步長或頻率。
- 區段標籤、數值與百分比： 專門用於圓形圖，確保切片能清晰標示其數值或百分比貢獻。
- 序列標籤： 標示個別數據線條或長條。
- 新增次座標軸： 用於組合圖中，當兩個數據序列的刻度差異極大時（例如將數千個銷售單位對應利潤百分比）。
關鍵要點（圖表）： 當被要求建立圖表時，請務必解釋你選擇該類型的原因（長條圖用於比較、折線圖用於趨勢、圓形圖用於比例），並確保所有必要的標籤與格式元素齊全，以保持清晰度。