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

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

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

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

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

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

什麼是Excel 單元格引用？

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

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

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

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

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

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

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

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

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

假設(shè)您在單元格B1 中有以下公式：

=A1*10

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

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

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

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

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

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

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

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

要向下複製公式，請將鼠標懸停在填充柄（所選單元格右下角的小方塊）上。當您這樣做時，光標將變?yōu)榧毢谏?，您可以按住並將其拖動到要自動填充的單元格上。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這種方法的優(yōu)點是您的用戶可以根據(jù)可變匯率計算歐元價格，而無需更改公式。一旦轉(zhuǎn)換率發(fā)生變化，您只需更新單元格C1 中的值即可。

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

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

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

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

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

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

Excel 混合單元格引用

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

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

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

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

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

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

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

=$B5*C$2

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

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

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

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

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

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

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

整列引用

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

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

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

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

整行引用

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

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

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

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

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

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

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

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

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

=AVERAGE(B:B)

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

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

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

如果Excel 表格中的數(shù)據(jù)按行而不是按列組織，則可以在公式中引用整行。例如，這就是我們?nèi)绾斡嬎愕? 行的平均價格：

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

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

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

示例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 函數(shù)，您也可以減去要排除的行：

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

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

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

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

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

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

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

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

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

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

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

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

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