excel你一定要知道的神奇有趣的相對引用

excel你一定要知道的神奇有趣的相對引用

下圖所示的工作表中，我們要求水果的銷售額，很顯然，使用列B中的單價乘以列C中的銷量即得到銷售額。因此，要得到蘋果的銷售額，就在單元格D2中輸入公式：

=B2*C2

同理，要得到其他水果的銷售額，就在列D中相應的單元格輸入列B乘以列C單元格的公式。如果數據很多，這樣一個一個輸入將費時費力。實際上，在單元格D2中輸入公式后，我們可以拖動復制該單元格公式至下方所有要計算的單元格，快速計算出結果，如下圖所示。

Excel會自動調整，讓公式使用正確的單元格，從而得到我們想要的結果。

上面的操作，是我們在Excel中經常會進行的操作，通過在一個單元格中輸入公式，然后拖動公式到指定的單元格，自動獲得想要的結果，非常方便快捷。

為什么能夠這樣呢？

其實，這些看似輕松平常的操作背后，蘊藏著Excel的相對引用原理。

回到上面的例子，再看看單元格D2中的公式，表面上看，表示單元格B2的值乘以單元格C3的值，但實際上應該是“本單元格左側第2個單元格的值乘以左側第1個單元格的值”。公式向下復制后，雖然單元格變了，但表示的意思仍然是本單元格左側第2個單元格的值與左側第1個單元格的值相乘。

也就是說，我們看到的只是表象。在使用相對引用時，Excel是以相對于輸入單元格的位置來處理單元格地址的。這就是Excel的相對引用原理。

理解相對引用原理非常重要，它是理解Excel公式運算以及在名稱、數據有效性和條件格式等應用的基礎。下面再舉一個例子。

如下圖所示的工作表，在單元格C5中輸入公式：

=A1+200

表面上看，是求單元格A1中的值加上200后的結果，而實際上Excel認為是將基于當前單元格向上4行向左2列的單元格的值加上200。

拖動單元格C5復制公式至單元格C6，結果如下圖所示，基于當前單元格C6，相關單元格相應地調整為單元格A2。

將單元格C5復制到單元格E7，此時公式變為：

=C3+200

即，公式中相關的單元格調整為基于當前單元格E7向上移4行向左移2列的單元格C3，如下圖所示。

上述操作也表明，相對引用是Excel默認使用的引用方式。因此，要結合工作表實際要求來正確使用。如果不正確的使用相對引用，有時會得到錯誤的結果。

如下圖所示的工作表，交稅金額為工資總額乘以稅率。我們先在單元格B1中輸入公式：=A2*D2，然后向下拖至單元格B5，顯然除B2中的值正確外，其它單元格中的值都是錯誤的。

這是因為按照Excel默認的相對引用方式，會自動調整相關單元格，特別是列D中的單元格，如下圖所示。列D中的單元格除D2外，都為空，因此除單元格B2外，其它單元格的值為0。

然而，我們需要的是列A中的單元格隨當前單元格的位置改變而自動調整，但列D中的單元格永遠固定在單元格D2。此時，就需要運用單元格的絕對引用，如下圖所示。

在單元格B2中輸入公式：

=A2*$D$2

向下拖動復制公式至單元格B5，得到正確的結果。

在上面的公式中，$D$2是絕對引用，不會隨著單元格自動調整。

當我們需要一直使用某單元格或者單元格區域時，就應該使用絕對引用。

當然，還可以使用混合引用，例如D$2表明行不變而列可以變化，$D2表明列不變而行可以變化。

例如下圖所示的工作表，我們要計算3種不同稅率下的交稅金額。在單元格B5中輸入公式：

=$A5*A$2

向下拖動至單元格B8，向右拖動至列D。絕對引用的行或者列不變，而使用相對應用的行或者列將自動調整獲取正確的單元格數據。

示例1：在條件格式中的相對引用

如下圖所示的工作表，要求對分數小于60的單元格背景色設置為紅色。使用公式：

=$B2<60

列固定而行隨著單元格調整。

注：條件格式中使用的公式中的單元格引用，是基于公式中所選單元格區域左上角的引對引用。

示例2：在數據有效性中的相對引用

與條件格式一樣，在設置數據有效性的公式中包含的單元格引用，也是其于所選區域左上角單元格的相對引用。

如下圖所示的工作表，我們在列A中設置了數據有效性，使得用戶在列A中不能輸入重復數據。

其中，使用了公式：

=COUNTIF(A:A,A1)=1

當我們選擇列A中任一單元格時，公式會自動調整到當前單元格，如下圖所示。

注意到，公式中使用的A:A也是相對引用，因此可以很容易地將列A中的不允許輸入重復值的功能復制應用到其他列。

示例3：在名稱中的相對引用

選擇工作表單元格A1，在“新建名稱”對話框中定義名稱GetRightCellValue：

=Sheet7!B1

即獲取當前單元格右側單元格中的值。

定義名稱后，在工作表中輸入該名稱時，會獲取所在單元格右側單元格中的值，如下圖所示。

如果想要該名稱在工作簿其他工作表中也可使用，那么可以將該名稱修改如下：

=!B1

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。