為什麼在Excel公式中使用$：相對和絕對單元格參考

Excel 公式中的單元格引用中的美元符號($) 常常令用戶困惑，但其原理很簡單。美元符號在Excel 單元格引用中只有一個作用：它告訴Excel 在將公式複製到其他單元格時，是否更改引用。本教程將詳細講解此功能。

Excel 單元格引用的重要性怎麼強調都不為過。了解絕對引用、相對引用和混合引用的區別，您就掌握了Excel 公式和函數強大功能的一半。

大家可能都見過Excel 公式中的美元符號($) 並想知道它是什麼。事實上，您可以通過四種不同的方式引用同一個單元格，例如A1、$A$1、$A1 和A$1。

Excel 單元格引用中的美元符號只影響一件事——它指示Excel 在將公式移動或複製到其他單元格時如何處理引用。簡而言之，在行和列坐標前使用$ 符號會創建一個不會更改的絕對單元格引用。不使用$ 符號，引用就是相對的，它會更改。

如果您正在為單個單元格編寫公式，您可以使用任何引用類型，無論如何公式都是正確的。但是，如果您打算將公式複製到其他單元格，則選擇合適的單元格引用類型至關重要。如果您感覺幸運，可以拋硬幣試試:) 如果您想認真對待，那麼請花幾分鐘時間學習Excel 中絕對和相對單元格引用的來龍去脈以及何時使用哪一個。

- 什麼是Excel 單元格引用

• 相對單元格引用
• 絕對單元格引用
• 在一個公式中使用相對和絕對單元格引用
• Excel 中的混合單元格引用
• 整列和整行引用
• 在不同引用類型之間切換(F4 鍵)

什麼是Excel 單元格引用？

簡單來說，Excel 中的單元格引用就是單元格地址。它告訴Microsoft Excel 在哪裡查找要在公式中使用的值。

例如，如果您在單元格C1 中輸入簡單的公式=A1，Excel 將從單元格A1 中提取值到C1：

如前所述，只要您為單個單元格編寫公式，就可以自由使用任何引用類型，無論是否使用美元符號($) ，結果都相同：

但是，如果您想在工作表中移動或複製公式，選擇正確的引用類型對於公式正確複製到其他單元格至關重要。以下部分將對每種單元格引用類型進行詳細解釋並提供公式示例。

注意。除了A1 引用樣式（其中列由字母定義，行由數字定義）之外，還存在R1C1 引用樣式，其中行和列都由數字標識（R1C1 表示第1 行，第1 列）。

由於A1 是Excel 中的默認引用樣式，並且大多數時候都使用它，因此本教程只討論A1 類型引用。如果當前有人使用R1C1 樣式，您可以通過單擊文件>選項>公式，然後取消選中R1C1 引用樣式複選框來關閉它。

Excel 相對單元格引用（無$ 符號）

Excel 中的相對引用是沒有行和列坐標中$ 符號的單元格地址，例如A1 。

當包含相對單元格引用的公式複製到另一個單元格時，引用會根據行和列的相對位置而變化。默認情況下，Excel 中的所有引用都是相對的。以下示例顯示了相對引用是如何工作的。

假設您在單元格B1 中有以下公式：

=A1*10

如果您將此公式複製到同一列中的另一行，例如單元格B2，則公式將調整為第2 行(A2*10)，因為Excel 假設您希望將A 列的每一行的值乘以10。

如果您將包含相對單元格引用的公式複製到同一行中的另一列，Excel 將相應地更改列引用：

如果您將包含相對單元格引用的Excel 公式複製或移動到另一行和另一列，則列和行引用都會更改：

如您所見，在Excel 公式中使用相對單元格引用是在整個工作表中執行相同計算的一種非常方便的方法。為了更好地說明這一點，讓我們討論一個真實的例子。

在Excel 中使用相對引用- 公式示例

假設您的工作表中有一列美元價格（B 列），您想將其轉換為歐元。已知美元- 歐元兌換率（撰寫本文時為0.93），第2 行的公式很簡單，即=B2*0.93 。請注意，我們正在使用Excel 相對單元格引用，沒有美元符號。

按Enter 鍵將計算公式，結果將立即顯示在單元格中。

提示。默認情況下，Excel 中的所有單元格引用都是相對引用。因此，在編寫公式時，您可以通過單擊工作表上的相應單元格來添加相對引用，而不是手動鍵入單元格引用。

要向下複製公式，請將鼠標懸停在填充柄（所選單元格右下角的小方塊）上。當您這樣做時，光標將變為細黑色十字，您可以按住並將其拖動到要自動填充的單元格上。

就是這樣！公式已復製到其他單元格，其相對引用已針對每個單元格正確調整。為了確保每個單元格中的值計算正確，請選擇任何單元格並在公式欄中查看公式。在此示例中，我選擇了單元格C4，並看到公式中的單元格引用相對於第4 行，這正是它應該的樣子：

Excel 絕對單元格引用（帶$ 符號）

Excel 中的絕對引用是在行或列坐標中帶有美元符號($) 的單元格地址，例如$A$1 。

美元符號將引用固定到給定的單元格，以便無論公式移動到哪裡，它都保持不變。換句話說，在單元格引用中使用$ 允許您複製Excel 公式而無需更改引用。

例如，如果單元格A1 中有10，並且您使用絕對單元格引用( $A$1 )，則公式=$A$1 5將始終返回15，無論將該公式複製到哪些其他單元格。另一方面，如果您使用相對單元格引用( A1 )編寫相同的公式，然後將其向下複製到列中的其他單元格，則將為每一行計算不同的值。下圖演示了差異：

注意。儘管我們一直在說Excel 中的絕對引用永遠不會改變，但實際上，當您在工作表中添加或刪除行和/或列時，它確實會改變，這會改變引用的單元格的位置。在上例中，如果我們在工作表頂部插入新行，Excel 足夠聰明，可以調整公式以反映該更改：

在實際工作表中，在Excel 公式中僅使用絕對引用的情況非常罕見。但是，有很多任務需要同時使用絕對引用和相對引用，如下面的示例所示。

注意。絕對單元格引用不應與絕對值混淆，絕對值是不考慮其符號的數字的大小。

在一個公式中使用相對和絕對單元格引用

您可能經常需要一個公式，其中一些單元格引用會根據公式複製到的列和行進行調整，而其他單元格引用則保持固定在特定單元格上。換句話說，您必須在一個公式中使用相對和絕對單元格引用。

示例1. 用於計算數字的相對和絕對單元格引用

在我們之前的美元和歐元價格示例中，您可能不想在公式中硬編碼匯率。相反，您可以將該數字輸入到某個單元格中，例如C1，並使用美元符號($) 在公式中固定該單元格引用，如下面的屏幕截圖所示：

在此公式(B4*$C$1) 中，有兩種單元格引用類型：

• B4 -相對單元格引用，會針對每一行進行調整，以及
• $C$1 -絕對單元格引用，無論公式複製到哪裡都不會更改。

這種方法的優點是您的用戶可以根據可變匯率計算歐元價格，而無需更改公式。一旦轉換率發生變化，您只需更新單元格C1 中的值即可。

示例2. 用於計算日期的相對和絕對單元格引用

在一個公式中使用絕對和相對單元格引用的另一個常見用途是根據今天的日期計算Excel 中的日期。

假設您在B 列中有一列交貨日期，並且您使用TODAY() 函數在C1 中輸入當前日期。您想知道每件商品的運輸時間，您可以使用以下公式計算： =B4-$C$1

同樣，我們在公式中使用了兩種引用類型：

• 相對引用用於包含第一個交貨日期的單元格(B4)，因為您希望此單元格引用根據公式所在的行的不同而變化。
• 絕對引用用於包含今天日期的單元格($C$1)，因為您希望此單元格引用保持不變。

總而言之，每當您想要創建一個始終引用同一單元格的Excel 靜態單元格引用時，請確保在公式中包含美元符號($) 以在Excel 中創建絕對引用。

Excel 混合單元格引用

Excel 中的混合單元格引用是指列字母或行號固定的一種引用。例如，$A1 和A$1 是混合引用。但是每個是什麼意思呢？這很簡單。

您還記得，Excel 絕對引用包含2 個美元符號($) ，它們會鎖定列和行。在混合單元格引用中，只有一個坐標是固定的（絕對的），另一個（相對的）將根據行或列的相對位置而變化：

• 絕對列和相對行，例如$A1。當包含此引用類型的公式複製到其他單元格時，列字母前的$ 符號會將引用鎖定到指定的列，使其永遠不會更改。相對行引用（無美元符號）會根據公式複製到的行而變化。
• 相對列和絕對行，例如A$1。在此引用類型中，行引用不會更改，列引用會更改。

下面您將找到使用這兩種混合單元格引用類型的示例，希望這能使事情更容易理解。

在Excel 中使用混合引用- 公式示例

在此示例中，我們將再次使用我們的貨幣轉換錶。但是這次，我們不會僅限於美元- 歐元轉換。我們將要做的是將美元價格轉換為多種其他貨幣，所有這些都使用單個公式！

首先，讓我們在某一行中輸入轉換率，例如第2 行，如下面的屏幕截圖所示。然後，您可以為左上角單元格（此示例中的C5）編寫一個公式來計算歐元價格：

=$B5*C$2

其中$B5 是同一行中的美元價格，C$2 是美元- 歐元轉換率。

現在，將公式向下複製到C 列中的其他單元格，然後通過拖動填充柄用相同的公式自動填充其他列。結果，您將擁有3 個不同的價格列，這些列根據同一列中第2 行中的相應匯率正確計算。要驗證這一點，請選擇表中的任何單元格並在公式欄中查看公式。

例如，讓我們選擇單元格D7（在英鎊列中）。我們在這裡看到的是公式=$B7*D$2 ，它採用B7 中的美元價格並將其乘以D2 中的值，即美元- 英鎊轉換率，這正是我們想要的:)

現在，讓我們了解Excel 如何準確知道要採用哪個價格以及要將其乘以哪個匯率。您可能已經猜到，這是混合單元格引用起作用的地方($B5*C$2)。

• $B5 -絕對列和相對行。在這裡，您只在列字母前添加美元符號($) 以將引用固定到A 列，因此Excel 始終使用所有轉換的原始美元價格。行引用（無$ 符號）未鎖定，因為您希望分別計算每一行的價格。
• C$2 -相對列和絕對行。因為所有匯率都位於第2 行，所以您通過在行號前添加美元符號($) 來鎖定行引用。現在，無論您將公式複製到哪一行，Excel 都會始終在第2 行中查找匯率。並且由於列引用是相對的（無$ 符號），它將針對公式複製到的列進行調整。

如何在Excel 中引用整列或整行

當您使用具有可變行數的Excel 工作表時，您可能希望引用特定列中的所有單元格。要引用整列，只需鍵入兩次列字母並在兩者之間添加冒號，例如A:A 。

整列引用

與單元格引用一樣，整列引用可以是絕對的和相對的，例如：

• 絕對列引用，例如$A:$A
• 相對列引用，例如A:A

同樣，您在絕對列引用中使用美元符號($) 將其鎖定到特定列，以便在將公式複製到其他單元格時，整列引用不會更改。

當將公式複製或移動到其他列時，相對列引用將發生更改，當您將公式複製到同一列中的其他單元格時，它將保持不變。

整行引用

要引用整行，您可以使用相同的方法，只是您鍵入行號而不是列字母：

• 絕對行引用，例如$1:$1
• 相對行引用，例如1:1

理論上，您還可以創建混合整列引用或混合整行引用，例如$A:A 或$1:1。我說“理論上”，因為我想不出這種引用的任何實際應用，儘管示例4 證明了具有這種引用的公式的工作方式完全符合預期。

示例1. Excel 整列引用（絕對和相對）

假設您在B 列中有一些數字，並且您想找出它們的總和和平均值。問題是每週都會向表中添加新行，因此為固定範圍的單元格編寫通常的SUM() 或AVERAGE() 公式並不是可行的方法。相反，您可以引用整個B 列：

=SUM($B:$B) - 使用美元符號($) 創建絕對整列引用，將公式鎖定到B 列。

=SUM(B:B) - 編寫沒有$ 符號的公式以創建相對整列引用，當您將公式複製到其他列時，它將發生更改。

提示。在編寫公式時，單擊列字母以將整列引用添加到公式中。與單元格引用一樣，Excel 默認情況下會插入相對引用（無$ 符號）：

同樣，我們編寫一個公式來計算整個B 列的平均價格：

=AVERAGE(B:B)

在此示例中，我們使用的是相對整列引用，因此當我們將它複製到其他列時，我們的公式會得到正確的調整：

注意。在Excel 公式中使用整列引用時，切勿在同一列中的任何位置輸入公式。例如，似乎在一個空單元格中輸入公式=SUM(B:B) 在B 列最底部的單元格中輸入公式=SUM(B:B) 以在同一列的末尾顯示總和可能是一個好主意。不要這樣做！這將創建一個所謂的循環引用，公式將返回0。

示例2. Excel 整行引用（絕對和相對）

如果Excel 表格中的數據按行而不是按列組織，則可以在公式中引用整行。例如，這就是我們如何計算第2 行的平均價格：

=AVERAGE($2:$2) -絕對整行引用通過使用美元符號($) 鎖定到特定行。

=AVERAGE(2:2) - 當將公式複製到其他行時，相對整行引用將發生更改。

在此示例中，我們需要相對整行引用，因為我們有3 行數據，我們希望通過複製相同的公式來計算每一行的平均值：

示例3. 如何引用整列（不包括前幾行）

這是一個非常熱門的問題，因為工作表中的前幾行通常包含一些介紹性條款或解釋性信息，您不希望將它們包含在計算中。遺憾的是，Excel 不允許像B5:B 這樣的引用，它將包含從第5 行開始的B 列中的所有行。如果您嘗試添加此類引用，您的公式很可能會返回#NAME 錯誤。

相反，您可以指定最大行，以便您的引用包含給定列中的所有可能行。在Excel 2016、2013、2010 和2007 中，最大值為1,048,576 行和16,384 列。較早版本的Excel 的行最大值為65,536，列最大值為256。

因此，要查找下表（B 列到D 列）中每個價格列的平均值，請在單元格F2 中輸入以下公式，然後將其複製到單元格G2 和H2：

=AVERAGE(B5:B1048576)

如果您使用SUM 函數，您也可以減去要排除的行：

=SUM(B:B)-SUM(B1:B4)

示例4. 在Excel 中使用混合整列引用

正如我在前面幾段中提到的那樣，您還可以在Excel 中創建混合整列或整行引用：

• 混合列引用，例如$A:A
• 混合行引用，例如$1:1

現在，讓我們看看當您將包含此類引用的公式複製到其他單元格時會發生什麼。假設您在某個單元格中輸入公式=SUM($B:B) ，在此示例中為F2。當您將公式複製到相鄰的右側單元格(G2) 時，它會更改為=SUM($B:C) ，因為第一個B 用$ 符號固定，而第二個沒有。結果，公式將對B 列和C 列中的所有數字求和。不確定這是否有任何實際價值，但您可能想知道它是如何工作的：

警告！不要在一個工作表中使用過多的整列/整行引用，因為它們可能會降低Excel 的速度。

如何切換絕對、相對和混合引用(F4 鍵)

在編寫Excel 公式時，當然可以手動鍵入$ 符號以將相對單元格引用更改為絕對或混合引用。或者，您可以按F4 鍵來加快速度。要使用F4 快捷鍵，您必須處於公式編輯模式：

1. 選擇包含公式的單元格。
2. 按F2 鍵或雙擊單元格進入編輯模式。
3. 選擇要更改的單元格引用。
4. 按F4 在四種單元格引用類型之間切換。

如果您選擇了沒有$ 符號的相對單元格引用，例如A1，則重複按F4 鍵會在包含兩個美元符號的絕對引用（例如$A$1）、絕對行A$1、絕對列$A1 之間切換，然後返回到相對引用A1。

注意。如果您在沒有選擇任何單元格引用的情況下按F4，則光標左側的引用將自動被選中並更改為另一種引用類型。

我希望您現在完全理解相對和絕對單元格引用是什麼，並且帶有$ 符號的Excel 公式不再是一個謎。在接下來的幾篇文章中，我們將繼續學習Excel 單元格引用的各個方面，例如引用另一個工作表、3D 引用、結構化引用、循環引用等等。在此期間，感謝您的閱讀，希望下週能在我們的博客上見到您！

以上是為什麼在Excel公式中使用$：相對和絕對單元格參考的詳細內容。更多資訊請關注PHP中文網其他相關文章！