Excel 絕對參照（如何建立與使用）

當您在 Excel 公式中參照儲存格時，預設採用的是相對參照。當您將公式複製到其他儲存格時，這些參照會根據其與原始位置的相對欄列關係自動調整。若您希望無論公式複製到哪裡，參照的儲存格都維持不變，就必須使用絕對參照。

• 什麼是絕對參照
• 如何製作絕對參照
• 使用絕對參照的範例
• 計算占總計的百分比
• 搜尋某個值並傳回對應的相符值
• 只需 2 次點擊，即可透過 Kutools 批次將儲存格參照轉為絕對參照
• 相對參照與混合參照
• 注意事項

免費下載範例檔案

影片：絕對參照

什麼是絕對參照

絕對參照是 Excel 中用來鎖定特定儲存格位置的一種參照方式。

相較於相對參照會在公式複製到其他儲存格時，依據相對位置自動調整，絕對參照無論公式複製或移動到哪裡，都會維持不變。

絕對參照是透過在公式中的欄與列標示前加上美元符號（$）來建立。例如，若要為儲存格 A1 建立絕對參照，應寫成 $A$1.

絕對參照適用於當您將公式複製到多個儲存格時，希望參照的儲存格或範圍保持固定，且不隨位置變更的情況。

例如，範圍 A4:C7 包含產品價格，而您希望根據儲存格 B2 中的稅率，計算每項產品的應付稅額。

當您在公式中使用相對參照（例如 “=B5*B2"）並向下拖曳自動填滿控點來套用該公式時，將會得到錯誤的結果。這是因為儲存格 B2 的參照會隨著公式所在位置而相對調整：在儲存格 C6 中，公式會變成 “=B6*B3“；而在儲存格 C7 中，則會變成 “=B7*B4“。

但若您對儲存格 B2 使用絕對參照，並以公式 “=B5*$B$2" 進行計算，即可確保向下拖曳自動填滿控點時，所有儲存格的稅率維持不變，從而獲得正確結果。

使用相對參照		使用絕對參照

如何建立絕對參照

在 Excel 中建立絕對參照，只需在公式中的欄與列參照前加上美元符號（$）。建立絕對參照有兩種方法：

手動為儲存格參照加上美元符號

當您在儲存格中輸入公式時，可手動在您希望設為絕對參照的欄與列前加上美元符號（$）。

例如，若您想將儲存格 A1 與 B1 中的數字相加，並讓兩者皆為絕對參照，只需輸入公式「=$A$1+$B$1」。如此一來，當公式被複製或移動到其他儲存格時，所參照的儲存格將維持不變。

或者，若您想將現有公式中的參照改為絕對參照，只需選取該儲存格，然後在編輯欄中加入美元符號（$）即可。

使用快速鍵 F4 將相對參照轉換為絕對參照

1. 雙擊包含公式的儲存格以進入編輯模式；
2. 將游標置於欲設為絕對參照的儲存格參照上；
3. 按下鍵盤上的「F4」鍵，切換參照類型，直到欄與列參照前均出現美元符號；
4. 按下「ENTER」鍵即可退出編輯模式並套用變更。

按下 F4 鍵，即可在相對、絕對與混合儲存格參照之間快速切換。

A1 → $A$1 → A$1 → $A1 → A1

若要將公式中的所有參照設為絕對，請在編輯欄中選取整個公式，然後反覆按下「F4」鍵，直到欄與列的參照前都加上美元符號（$）為止。

A 1+B1 → $A$1++$B$1 → A$1++B$1 → $A 1+$B1 → A 1+B1

使用絕對參照的範例

本節提供兩個範例，說明在 Excel 公式中何時及如何使用絕對參照。

範例 1：計算佔總計的百分比

假設您有一個數據區域（A3:B7），其中列出各水果的銷售額，而儲存格 B8 則包含這些水果的總銷售金額。現在，您希望計算每種水果的銷售額佔總計的百分比。

計算佔總計百分比的通用公式：

Percentage = Sale/Amount

在公式中使用相對參照來計算第一種水果的百分比如下：

=B4/B8

當您向下拖曳自動填滿控點，以計算其他水果的百分比時，會出現 #DIV/0! 錯誤。

這是因為當您拖曳自動填滿控點將公式複製到下方儲存格時，相對參照 B8 會依其相對位置自動調整為其他儲存格參照（B9、B10、B11）。然而，儲存格 B9、B10 與 B11 均為空值（即零），導致公式在除數為零時傳回錯誤。

為修正此錯誤，您必須在公式中將儲存格參照 B8 設為絕對參照（$B$8），以確保無論公式移動或複製到何處，該參照都不會變更。此時，公式已更新為：

=B4/$B$8

接著向下拖曳自動填滿控點，即可輕鬆計算其他水果的百分比。

範例 2：尋找值並傳回對應的相符值

假設您希望在 D4:D5 中搜尋員工姓名，並根據範圍 A4:B8 所提供的姓名與對應年薪，回傳他們的薪資。

查詢的通用公式：

=VLOOKUP(lookup_value, table_range, column_index, logical)

如果您在公式中使用相對參照來查詢值並傳回對應的相符值，如下所示：

=VLOOKUP(D4,A4:B8,2,FALSE)

接著向下拖曳自動填滿控點以查詢下方的值，將會傳回錯誤。

當您向下拖曳填滿控點，將公式複製到下方儲存格時，公式中的參照會自動向下調整一列，使表格範圍從 A4:B8 變為 A5:B9. 由於範圍 A5:B9 中找不到 Lisa，公式便會傳回錯誤。

為避免錯誤，請在公式中使用絕對參照 $A$4：$B$8，而非相對參照 A4:B8：

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

接著向下拖曳自動填滿控點，即可取得 Lisa 的薪資。

僅需 2 次點擊，即可透過 Kutools 批次將儲存格參照設為絕對

無論您選擇手動輸入或使用 F4 快速鍵，在 Excel 中一次只能修改一個公式。若您希望將數百個公式中的儲存格參照轉為絕對參照，「Kutools for Excel」的「轉換儲存格參照」功能只需 2 次點擊，即可輕鬆完成！

若要將多個公式中的儲存格參照設為絕對，請先選取包含公式的儲存格，然後點選「Kutools」>「更多」>「轉換儲存格參照」。接著選擇「轉為絕對」選項，再按一下「確定」或「套用」。此時，所選公式中的所有儲存格參照都已成功轉換為絕對參照。

相對參照與混合參照

除了絕對參照之外，還有另外兩種參照類型：相對參照與混合參照。

相對參照是 Excel 中的預設參照類型，其欄與列參照前不帶美元符號（$）。當含有相對參照的公式被複製或移動至其他儲存格時，參照會根據相對位置自動調整。

例如，當您在儲存格中輸入公式「=A 1+1」，然後向下拖曳自動填滿控點以將此公式套用至下一個儲存格時，公式會自動更新為「=A 2+1」。

混合參照由絕對參照與相對參照組合而成，也就是在複製或填入公式時，透過美元符號（$） 固定其中的列或欄其中之一。

以乘法表為例，各列數據分別包含從 1 到 9 的數字，並彼此相乘。

首先，您可以在儲存格 C3 中輸入公式「=B3*C2」，將儲存格 B3 中的 1 與第一欄中的數字（1）相乘。然而，當您向右拖曳自動填滿控點以填入其他儲存格時，會發現除了第一個結果外，其餘結果皆不正確。

這是因為當您將公式向右複製時，列位置保持不變，但欄位會從 B3 依序變為 C3、D3 等。因此，右側儲存格（如 D3、E3 等）中的公式會變成 “=C3*D2“、“=D3*E2" 等，但您真正需要的其實是 “=B3*D2“、“=B3*E2" 等。

在此情況下，您需在「B3」的欄位參照前加上美元符號（$），以鎖定該欄。請使用下列公式：

=$B3*C2

現在當您向右拖曳公式時，即可獲得正確結果。

接下來，您需要將儲存格 C2 中的數字 1 與其下方列中的數字相乘。

當您向下複製公式時，儲存格 C2 的欄位置不會改變，但列位置會依序變為 C3、C4 等。因此，下方儲存格中的公式將變成「=$B4*C3」、「=$B5*C4」等，導致計算結果錯誤。

要解決此問題，請將「C2」改為「C$2」，這樣在向下拖曳自動填滿控制點以複製公式時，列的參照就會保持不變。

=$B3*C$2

現在，只要向右或向下拖曳自動填滿控制點，就能立即取得所有結果。

注意事項

• 儲存格參照摘要

  類型	範例	摘要
  絕對參照	$A$1	當公式複製到其他儲存格時，絕不改變
  相對參照	A1	當公式複製到其他儲存格時，列與欄的參照皆會根據相對位置而改變
  混合參照	$A1/A$1	當公式複製到其他儲存格時，列參照會改變但欄參照固定；或欄參照會改變但列參照固定；

• 一般而言，公式移動時，絕對參照不會改變。然而，當您在工作表頂端或左側插入或刪除列與欄時，絕對參照會自動調整。例如，公式「=$A$1+1」在您於工作表頂端插入一列後，會自動更新為「=$A$2+1」。

  

• 按下「F4」鍵，即可在相對、絕對與混合參照之間快速切換。