Excel 絕對引用（如何製作和使用）

在 Excel 的公式中引用單元格時，預設的引用類型是相對引用。當公式被複製到其他單元格時，這些引用會根據其相對的列和行位置改變。如果您希望無論公式複製到何處都保持引用不變，則需要使用絕對引用。

• 什麼是絕對引用
• 如何製作絕對引用
• 使用絕對引用的範例
• 計算總數的百分比
• 查找值並返回對應的匹配值
• 只需兩次點擊即可使用 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」鍵切換引用類型，直到在列和行引用前都添加了美元符號。

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+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。由於 Lisa 無法在範圍 A5:B9 中找到，公式返回錯誤。

為避免錯誤，請在公式中使用絕對引用 $A$4:$B$8 代替相對引用 A4:B8：

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

然後拖動自動填充手柄向下獲取 Lisa 的薪水。

只需兩次點擊即可使用 Kutools 批量將單元格引用設為絕對引用

無論您選擇手動輸入還是使用 F4 快捷鍵，每次只能更改一個公式。如果您想在 Excel 中將數百個公式中的單元格引用設為絕對引用，Kutools for Excel 的「轉換儲存格參照」工具可以幫助您用兩次點擊完成任務。

要在多個公式中將單元格引用設為絕對引用，請選擇公式單元格並點擊「Kutools」>「更多」>「轉換儲存格參照」。然後選擇「至絕對」選項並點擊「確定」或「應用」。現在所選公式的所有單元格引用都已轉換為絕對引用。

相對引用與混合引用

除了絕對引用外，還有另外兩種引用類型：相對引用和混合引用。

相對引用 是 Excel 中的默認引用類型，其特徵是在行和列引用前沒有美元符號（$）。當包含相對引用的公式被複製或移動到其他單元格時，引用會根據其相對位置自動改變。

例如，當您在單元格中輸入公式如「=A1+1」，然後拖動自動填充手柄向下填充此公式到下一個單元格時，公式會自動改變為「=A2+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 的列位置不會改變，但行位置從 C2 變為 C3、C4 等。結果，下方單元格中的公式變為「=$B4C3」、「=$B5C4」等，這將產生錯誤的結果。

要解決這個問題，將「C2」改為「C$2」，以便在向下拖動自動填充手柄填充公式時保持行引用不變。

=$B3*C$2

現在您可以向右或向下拖動自動填充手柄以獲得所有結果。

需要注意的事項

• 單元格引用總結

  類型	範例	摘要
  絕對引用	$A$1	當公式複製到其他單元格時永遠不會改變
  相對引用	A1	當公式複製到其他單元格時，基於相對位置改變行和列引用
  混合引用	$A1/A$1	當公式複製到其他單元格時，行引用改變但列引用固定/列引用改變但行引用固定；

• 一般來說，當公式移動時，絕對引用永遠不會改變。然而，當工作表頂部或左側添加或刪除行或列時，絕對引用會自動調整。例如，在公式「=$A$1+1」中，當您插入一行到工作表頂部時，公式會自動變為「=$A$2+1」。

  

• F4 鍵可以在相對、絕對和混合引用之間切換。