歡迎來到試算表：建立數據模型！

你好，未來的 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，內建捷徑)

    函數是試算表軟體內建的預定義指令或程式，能快速執行複雜的計算。

    範例： 要使用函數計算銷售總額，你輸入 =SUM(B5:D5)

算術運算子

你必須學會如何在公式中使用標準的數學符號：

  • 加法：+
  • 減法：-
  • 乘法：*
  • 除法：/
  • 次方/指數：^ (例如：=A1^2 代表 A1 的平方)
運算順序 (BODMAS/PEMDAS)

試算表在計算公式時遵循嚴格的數學運算順序。

BODMAS（或 PEMDAS）規則為：

  1. Brackets (括號)
  2. Orders (指數/次方)
  3. Division 和 Multiplication (除法與乘法，由左至右)
  4. Addition 和 Subtraction (加法與減法，由左至右)

重要技巧： 使用括號 () 來強制試算表優先計算公式中的特定部分。
範例： 如果你想先將 A1 和 A2 相加，再除以 2，你必須寫成 =(A1+A2)/2。如果你寫成 =A1+A2/2，系統會先執行 A2 除以 2，這通常不是你想要的結果。

快速複習：公式 vs 函數
公式 = 你手動輸入數學運算 (+, -, *)。
函數 = 試算表執行複雜指令 (SUM, AVG)。

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，鎖定)


絕對參照是指當你複製公式時，儲存格參照不會改變。你使用美元符號 ($) 來「鎖定」列、欄或兩者。\n

\n
    \n
  • 類比： 你告訴朋友：「去地圖上標示為『噴水池』的確切位置」。無論你的朋友站在哪裡，他們都必須前往該固定地點。
    • \n
  • 目的： 當公式需要參照單一固定值（如稅率、折扣百分比或轉換因子）時，絕對參照就顯得至關重要。
    • \n
\n\n

共有三種絕對鎖定方式：

\n
    \n
  1. 全鎖定 (Absolute)： $A$1
    （複製時，欄位與列號皆不改變。）
    2. \n
  2. 混合鎖定 (欄絕對)： $A1
    （欄 A 被鎖定，但列 1 可以改變。）
  3. 混合鎖定 (列絕對)： A$1
    （列 1 被鎖定，但欄 A 可以改變。）
    4. \n
\n\n

覺得困難嗎？試試這個小技巧！ 當你在建立模型時，問自己：「無論我複製到哪裡，這個參照是否永遠指向同一個儲存格？」 如果答案是「是」，你就需要使用 $ 符號！

重點提示： 對於對應的數據（同一行的數據），使用相對參照。對於所有計算都必須使用的固定值（常數），請使用絕對 ($) 參照。

4. 進階模型工具：命名範圍與函數

為了讓複雜的公式更易於閱讀和管理，我們可以使用命名儲存格 (named cells) 和命名範圍 (named ranges)。

4.1 命名儲存格與範圍
  • 它們是什麼？ 不再以 C4 稱呼儲存格，你可以給它一個描述性的名稱，例如 Tax_Rate (稅率)。
  • 優點： 這讓你的公式更加清晰。你不用寫 =Sales*C4，而是寫 =Sales*Tax_Rate。這在稽核或排查複雜模型錯誤時特別有用。
4.2 基礎函數 (工具箱)

你必須學會並使用多種函數來應對實作任務，並理解其用途以回答理論問題。

A. 統計與數學函數
  • SUM： 加總一個數字範圍。 (例如：=SUM(A1:A10))
  • AVERAGE： 計算範圍內的平均值。 (例如：=AVERAGE(B1:B10))
  • MAXIMUM (MAX)： 找出範圍內最大的數字。
  • MINIMUM (MIN)： 找出範圍內最小的數字。
  • COUNT： 計算範圍內包含數字數據的儲存格個數。
B. 四捨五入與整數函數

這些函數對於確保處理金錢或整數數量（如人數或物品）時的計算準確性非常重要。

  • ROUNDING (四捨五入)： 將數字調整為指定的位數。 (例如：=ROUND(A1, 2) 將 A1 四捨五入至小數點後兩位。)
  • INTEGER (INT，取整)： 截斷數字，丟棄小數部分並只保留整數部分。（它總是向下取整，趨近於零。）
C. 邏輯函數 (IF)
  • IF： 執行邏輯測試，如果條件為真 (TRUE) 則返回一個值，如果條件為假 (FALSE) 則返回另一個值。
    結構為：=IF(Logical_Test, Value_if_True, Value_if_False)
  • 範例： =IF(B2>50, "合格", "不合格")
D. 查找函數 (VLOOKUP, HLOOKUP, XLOOKUP)

查找函數會在一個區域（通常是表格）中搜尋特定值，並返回來自不同欄或列的對應值。這對於處理稅率、價格表或代碼至關重要。

  • VLOOKUP： 垂直搜尋（沿著欄向下）查找值。
  • HLOOKUP： 水平搜尋（沿著列向右）查找值。
  • XLOOKUP： 一種現代且更靈活的查找函數，搜尋一個範圍並返回對應範圍的結果。

你知道嗎？ 查找表是管理複雜定價結構的高效方式，無需編寫數十個 IF 語句！

4.3 巢狀函數 (Nested Functions)

巢狀函數是指將一個函數完全置於另一個函數之內。內層函數會先被計算，其結果將作為外層函數的引數 (argument)。

範例： 如果你想計算銷售平均值，並將該平均值四捨五入為整數：

=ROUND(AVERAGE(B1:B10), 0)

這裡，AVERAGE 函數先計算平均值，其結果隨即傳遞給 ROUND 函數。

常見錯誤： 使用巢狀函數時，請務必確保右括號 )) 的數量正確。缺少括號會導致錯誤。

5. 使用外部數據來源

試算表模型本身往往不會包含所有需要的數據。你必須知道如何連結存放在他處的數據。

函數內的外部數據

你通常會使用函數（特別是查找函數）來檢索其他地方的資訊：

  • 其他工作表： 數據可能位於同一個活頁簿的不同工作表中。
  • 其他檔案： 數據可以連結自完全獨立的試算表檔案。

此流程能讓你的模型引入定價資訊、員工編號或統計常數，而無需手動複製貼上數據，使你的主模型更加整潔且易於更新。

重點提示： 熟練運用公式、函數和參照，是將基礎網格轉化為功能強大的數據模型的關鍵。