什麼是Excel公式固定？為什麼要學會這項技巧
在Excel進行數據分析或自動計算時，經常會遇到一個問題：當你將公式往下或往旁邊複製時，參照的儲存格位置會自動變動，導致計算結果出錯。這種現象稱為「相對引用」。而「公式固定」則是指利用絕對引用或混合引用，讓公式在複製時能鎖定特定儲存格或行列，避免錯誤發生。
常見困擾與錯誤示例
- 匯率或稅率計算：將一列金額乘以固定的匯率，若未固定匯率儲存格，複製公式後會導致引用錯誤。
- 成績加權：計算多位學生成績時，權重欄位需固定，否則結果不正確。
- 表格自動填充：大量複製公式時，若未正確固定，容易產生錯誤數據。
學會正確的公式固定技巧，不僅能避免這些常見錯誤，更能大幅提升Excel的自動化與效率。
Excel公式引用類型詳解
Excel的儲存格引用方式分為三大類：相對引用、絕對引用、混合引用。理解這三者的差異，是正確固定公式的基礎。
相對引用（A1）
- 說明：預設狀態。公式複製時，行與列都會隨著移動而改變。
- 應用情境：大多數批次計算、橫向或縱向填充時。
- 範例：在B2輸入
=A2*2，往下複製到B3會自動變成=A3*2。
絕對引用（$A$1）
- 說明：行與列都固定，無論公式複製到哪裡，參照的都是同一個儲存格。
- 應用情境：計算時需鎖定特定參數（如匯率、稅率、常數）。
- 範例：在B2輸入
=$A$1*B2，無論複製到哪一格，A1都不會改變。
混合引用（$A1、A$1）
- $A1：固定列，不固定行。複製時列不變，行會變。
- A$1：固定行，不固定列。複製時行不變，列會變。
- 應用情境：常用於表格交叉計算（如成績表、交叉乘積表）。
- 範例：在C2輸入
=$A2*B$1，往右複製時，B$1固定行，往下複製時，$A2固定列。
| 引用類型 | 範例 | 固定行 | 固定列 | 複製時變化 |
|---|---|---|---|---|
| 相對引用 | A1 | 否 | 否 | 行列皆隨複製改變 |
| 絕對引用 | $A$1 | 是 | 是 | 行列皆固定 |
| 混合引用1 | $A1 | 否 | 是 | 列固定，行隨複製改變 |
| 混合引用2 | A$1 | 是 | 否 | 行固定，列隨複製改變 |
如何快速設定固定引用
在編輯公式時，手動輸入$符號雖然可行，但效率較低。Excel提供了便捷的F4快速鍵，讓你能在編輯儲存格時快速切換引用類型。
F4快速鍵操作技巧
- 在公式編輯狀態下，點選欲固定的儲存格參照（如A1）。
- 按下F4，會自動在A1前後加上$，變成$A$1。
- 連續按F4，可依序切換：$A$1 → A$1 → $A1 → A1。
- 適用於單一儲存格、多重選取、甚至跨表引用。
編輯公式時的實務技巧
- 先輸入公式，再用方向鍵選取需固定的儲存格，按F4切換。
- 複雜公式可分段固定，避免一次性輸入錯誤。
- 若使用筆記型電腦，F4可能需搭配Fn鍵。
實用案例解析
單一儲存格固定：匯率計算
情境：A1為匯率（如30.5），B2~B10為金額，需計算台幣金額。
- 在C2輸入公式：
=B2*$A$1 - 向下複製到C10，A1始終被固定，確保計算正確。
固定列或行的表格計算
情境：計算學生各科成績加權總分。B1~E1為科目權重，A2~A10為學生，B2~E10為成績。
- 權重行固定：公式
=B2*B$1，向右複製時B$1固定行。 - 學生列固定：公式
=$A2*B2，向下複製時$A2固定列。
延伸應用：交叉乘積表、商品價格表等。
跨工作表/跨檔案引用
情境：需引用其他工作表或檔案的固定儲存格。
- 跨工作表：
=Sheet2!$A$1 - 跨檔案：
='[報表.xlsx]Sheet1'!$A$1 - 複製公式時，引用位置不會變動，適合總表彙整、年度統計等。
常見錯誤與排解
- 未固定導致錯誤：複製公式後，引用儲存格跑掉，計算結果異常。
- 混合引用誤用：只固定行或列，導致部分計算正確、部分錯誤。
- 跨表引用失效：檔案路徑或工作表名稱變動，需重新確認引用格式。
解決方法：
– 檢查公式中的$符號位置，確認是否符合需求。
– 利用F4快速檢查與修正。
– 跨檔案時，確保路徑與檔名無誤。
進階應用技巧
陣列公式與固定引用
說明：陣列公式可同時計算多個儲存格，常搭配固定引用處理批次運算。
- 範例：計算多列金額與固定稅率的總和
=SUM(B2:B10*$A$1)
按下Ctrl+Shift+Enter輸入，$A$1固定稅率。
名稱定義與固定引用搭配
說明：將常用參數（如匯率、稅率）定義名稱，公式更直觀。
- 步驟：選取A1，點選「定義名稱」，命名為Rate。
- 公式：
=B2*Rate
具備固定引用效果，且易於閱讀與維護。
與Google Sheets的異同
- 相同點：$符號用法、相對/絕對/混合引用邏輯一致。
- 差異：Google Sheets支援即時多人協作，跨表引用語法略有不同（如
'工作表名稱'!$A$1）。 - 快捷鍵：Google Sheets同樣支援F4切換引用類型。
常見問題FAQ
Q1：如何一次固定多個儲存格？
A：可在公式中分別對多個儲存格加上$符號，例如=$A$1+$B$1。若需批次固定，需手動或分別用F4處理。
Q2：可以只固定列或行嗎？有什麼限制？
A：可以。$A1固定列，A$1固定行。限制在於複製方向需與固定需求一致，否則可能出現計算錯誤。
Q3：複製公式時固定失效怎麼辦？
A：常見原因為$符號位置錯誤或未使用。請檢查公式，並用F4快速修正。跨表引用時，請確認路徑與名稱正確。
Q4：公式固定可以搭配哪些進階功能？
A：可與陣列公式、名稱定義、條件格式、資料驗證等進階功能搭配，提升自動化與彈性。
結語與效率提升建議
熟練掌握Excel公式固定，不僅能避免常見錯誤，更能大幅提升數據處理與自動化效率。無論是財務報表、業務統計、專案管理或團隊協作，正確運用絕對引用與混合引用，都是提升工作品質的關鍵。如果你需要進一步提升團隊協作效率，建議可結合如Monday.com、ClickUp等專案管理工具，搭配Excel自動化，打造高效的數據與任務管理流程。
