歡迎來到試算表：建立數據模型！
你好，未來的 ICT 專家！本章的主題是建立強大且靈活的工具，我們稱之為試算表 (spreadsheets) 或數據模型 (data models)。
為什麼這很重要？數據模型能讓企業預測結果、管理預算並自動計算結果。如果你能掌握這些技巧，就能將原始數據轉化為有意義的決策！
快速複習：什麼是試算表模型？
試算表模型是對現實世界系統的數位化呈現，用於模擬流程或根據輸入值計算結果。你可以把它想像成一個結合了結構化歸檔系統的超智能計算機。
1. 建立結構（建立與編輯）
在進行計算之前，你需要一個整潔的結構！設計良好的試算表易於理解且不易出錯。
核心結構技巧 (20.1 實作)
- 插入/刪除： 你必須能夠插入或刪除個別的儲存格 (cells)、整行 (rows) 和整列 (columns)，以便調整版面配置。
- 合併儲存格 (Merging Cells)： 這能將多個相鄰儲存格（例如 A1 和 B1）合併為一個大儲存格，通常用於在數據表格上方建立清晰的標題 (headings) 或標籤 (titles)。
重點提示： 保持版面邏輯清晰！清晰的標籤和正確的欄位運用能讓模型更易於管理。
2. 公式 (Formulae) 與函數 (Functions)：釐清兩者差異
這兩個術語常被混淆，但它們是用於計算的兩種不同工具。理解其中的區別對於考試理論題至關重要！
公式與函數的區別
-
1. 公式 (Formulae，手動方法)
公式是你手動輸入儲存格以執行計算的指令，開頭必須加上等號 (
=)。它使用算術運算子 (arithmetic operators)。範例： 要手動計算銷售總額，你輸入
=B5 + C5 + D5 -
2. 函數 (Functions，內建捷徑)
函數是預定義的內建指令，使用指定的引數 (arguments) 來執行計算。
範例： 要使用函數計算銷售總額，你輸入
=SUM(B5:D5)
算術運算子
你必須學會如何在公式中使用標準的算術運算子：
- 加法：+
- 減法：-
- 乘法：*
- 除法：/
- 指數（次方/乘冪）：^ (例如：
=A1^2代表 A1 的平方)
運算順序 (BODMAS/PEMDAS)
試算表在計算公式時遵循標準的數學運算順序：
- Brackets (括號 / Parentheses)
- Orders (指數/次方 / Exponents)
- Division 和 Multiplication (除法與乘法，由左至右)
- Addition 和 Subtraction (加法與減法，由左至右)
重要技巧： 使用括號 () 來強制試算表優先計算公式中的特定部分。
範例： 如果你想先將 A1 和 A2 相加，再除以 2，請寫成 =(A1+A2)/2。如果你寫成 =A1+A2/2，系統會先執行 A2 除以 2。
公式 = 你手動輸入算術運算子 (+, -, *, /)。
函數 = 試算表使用具名的內建常式 (例如 SUM, AVERAGE)。
3. 儲存格參照 (Cell Referencing)：複製的威力
試算表最重要的技能之一是複製 (replicating) 公式至整欄或整行。為了正確做到這一點，你必須掌握相對參照與絕對參照的區別。
3.1 相對參照 (Relative Cell Referencing，預設值)
當你複製使用相對參照（例如 A1）的公式時，儲存格參照會相對於新位置自動改變。
- 類比： 告訴某人：「向右走 2 步，向前走 3 步。」當他們移動到新的座位時，他們仍然是從該新起點向右走 2 步、向前走 3 步。
- 實務操作： 如果你將儲存格 D2 中的公式
=B2*C2向下複製到 D3，公式會自動變更為=B3*C3。
3.2 絕對參照 (Absolute Cell Referencing，鎖定)
絕對參照在複製時不會改變。你在前面加上美元符號 ($) 來鎖定列、欄或兩者。
- 類比： 告訴某人：「前往座標 (5, 10)。」無論他們目前站在哪裡，目的地座標都保持固定。
- 目的： 當公式參照單一固定儲存格（例如稅率、折扣率或單價）時，絕對參照至關重要。
共有三種絕對鎖定方式：
- 全鎖定 (Full Absolute Lock)：
$A$1
（複製時，欄 A 與列 1 皆不改變。） - 混合鎖定 (欄絕對)：
$A1
（欄 A 被鎖定，但向下複製時列 1 會改變。） - 混合鎖定 (列絕對)：
A$1
（列 1 被鎖定，但向右複製時欄 A 會改變。）
覺得困難嗎？試試這個小技巧！ 問自己：「無論我複製到哪裡，這個參照是否永遠指向該特定儲存格？」 如果答案是「是」，請用 $ 符號將其鎖定！
重點提示： 對於逐行對應的數據使用相對參照，對於固定常數請使用絕對 ($) 參照。
4. 函數工具箱與進階模型工具
為了讓複雜的計算更易於管理和閱讀，你將使用命名範圍和必備的內建函數。
4.1 命名儲存格與範圍
- 它們是什麼？ 給予儲存格或儲存格範圍一個描述性的名稱（例如將儲存格
C4命名為 Tax_Rate，或將A2:A50命名為 Scores）。 - 優點： 公式變得更加清晰且易於理解（例如寫成
=Price*Tax_Rate而不是=Price*$C$4）。命名範圍自動具備絕對參照特性。
4.2 必備函數工具箱
A. 統計與數學函數
- SUM： 將範圍內的所有數字相加。(例如：
=SUM(A1:A10)) - AVERAGE： 計算範圍內的算術平均值。(例如：
=AVERAGE(B1:B10)) - MAX： 找出範圍內的最大值。(例如：
=MAX(C1:C20)) - MIN： 找出範圍內的最小值。(例如：
=MIN(C1:C20)) - COUNT： 計算範圍內包含數字的儲存格個數。(例如：
=COUNT(D1:D30)) - COUNTA： 計算範圍內所有非空白儲存格（包含文字、數字或錯誤值）的個數。(例如：
=COUNTA(A1:A30)) - COUNTIF： 計算符合單一特定條件的儲存格個數。(例如：
=COUNTIF(E1:E20, ">50")) - COUNTIFS： 計算跨不同範圍且符合多個條件的儲存格個數。(例如：
=COUNTIFS(A1:A20, "Male", B1:B20, ">=18")) - SUMIF： 將範圍內符合指定單一條件的數值相加。(例如：
=SUMIF(CategoryRange, "Books", PriceRange)) - SUMIFS： 將範圍內符合多個條件的數值相加。(例如：
=SUMIFS(SumRange, CriteriaRange1, "North", CriteriaRange2, ">100"))
B. 四捨五入與整數函數
- ROUND： 將數字四捨五入至指定的小數位數或位數。(例如：
=ROUND(A1, 2)) - ROUNDUP： 總是遠離零向上取整至指定的小數位數。(例如：
=ROUNDUP(A1, 0)) - ROUNDDOWN： 總是朝向零向下取整至指定的小數位數。(例如：
=ROUNDDOWN(A1, 0)) - INT： 將數字向下取整至最接近的整數。(例如：
=INT(A1))
C. 邏輯函數 (IF)
- IF： 評估邏輯測試，如果條件為真 (TRUE) 則返回一個值，如果條件為假 (FALSE) 則返回另一個值。
結構為：=IF(logical_test, value_if_true, value_if_false)
範例：=IF(B2>=50, "PASS", "FAIL")
D. 查找函數 (Lookup Functions)
查找函數會在陣列或表格中搜尋特定值，並返回對應的值：
- VLOOKUP： 沿著垂直表格的第一欄向下搜尋，並從指定的欄索引 (column index) 返回值。(例如：
=VLOOKUP(lookup_value, table_array, col_index, FALSE)) - HLOOKUP： 沿著水平表格的第一列橫向搜尋，並從指定的列索引 (row index) 返回值。
- LOOKUP： 在單列或單欄範圍（向量）中搜尋值，並從另一個範圍的相同位置返回值。
- XLOOKUP： 一種靈活的現代查找函數，搜尋查找陣列並沿任何方向從返回陣列中返回匹配項。
4.3 巢狀函數 (Nested Functions)
當一個函數作為引數置於另一個函數內部時，即構成巢狀函數。內層函數會先執行，其輸出將傳遞給外層函數。
範例： 要計算範圍的平均值並立即將其四捨五入至 0 位小數：
=ROUND(AVERAGE(B1:B10), 0)
常見錯誤： 檢查成對的括號 ()。每個左括號都必須有對應的右括號。
5. 參照外部數據來源
試算表模型經常需要參照儲存在使用中工作表之外的數據。
- 跨工作表： 連結位於同一個活頁簿內其他工作表的數據（例如：
=Sheet2!A1）。 - 跨外部檔案/活頁簿： 連結來自完全獨立的試算表檔案中的數據。
使用外部參照能讓你將原始數據表（如員工名單或價格目錄）與計算表分開，保持數據模型的模組化、井然有序且易於維護。
重點提示： 熟練掌握結構、參照、公式以及完整的函數組合，是將基礎網格轉化為健全、自動化數據模型的關鍵。