在Excel中，學習函數(shù)始終繞不開相對引用和絕對引用，引用方法得當，才能對函數(shù)靈活運用。否則計算結果很可能出現(xiàn)錯誤。本文就以幾個常用的案例介紹一下Excel中的相對引用、絕對引用與混合引用的用法吧。

在你復制公式時，如果你需要被引用的單元格或單元格區(qū)域不會因公式拉動變化而變化，就要用到絕對引用或混合引用，在輸入時按F4鍵可快速切換。

$在誰的前面就絕對引用誰

一、相對引用、絕對引用基本情況介紹。

相對引用和絕對引用在形式上的區(qū)別就是絕對引用有$符號(英文狀態(tài)下shift+4)，相對引用沒有。內容上的區(qū)別就是填充以后引用的單元格不同。在編輯欄的公式中按F4鍵可以在相對引用、絕對引用和混合引用之間切換。

如下圖所示，右側數(shù)據(jù)是三種引用方式的計算結果：

在D3單元格輸入公式【=A3*B3】，向下拖動，隨著單元格的變化，計算公式和計算結果也會發(fā)生變化。因為此處對A列和B列的單元格均采用了相對引用。

在E3單元格輸入公式【=$A$3*B3】，向下拖動后，引用的A列單元格一直是A3單元格，不會發(fā)生變化，而引用B列單元格會發(fā)生變化。因為此處對A3單元格采用了絕對引用。

在F3單元格輸入公式【=$A$3*$B$3】，向下拖動后，公式一直保持一致，因為此處對A3和B3單元格都采用了絕對引用。

二、混合引用在九九乘法表中的運用。

在制作九九乘法表中，如下圖所示，在B3單元格中輸入的公式應該是【=$A3*B$2】，為什么這樣輸入呢?

我們可以這樣想，C3單元格的計算公式應該是【=A3*C2】，而向右拖動填充公式時列會發(fā)生變化，行不會變化。即填充公式后 C3單元格計算公式變?yōu)?【=B3*C2】。為了讓第一個乘數(shù) A3單元格固定保持不變，我們就需要用到絕對引用。A3的A列絕對引用，我們需要將第一個第一個的公式改為【=$A3*C2】。這樣的話公式在向右拖動過程中，A參數(shù)就不再變化。

如圖是拖動到最右端的時候效果。

然后將B3單元格中的【=$A3*B2】這個公式向下拖動填充公式時行會發(fā)生變化，列不會變化。即填充公式后，B4單元格公式成為【=$A4*B3】，導致錯誤。原因是B2變成了B3。

為了讓第二個乘數(shù)B2固定，不再變化。我們就需要用到絕對引用。B2的2行絕對引用，我們需要將公式改為【=$A3*B$2】。這樣的話公式在向右拖動過程中，A參數(shù)就不再變化。并且公式在向下拖動過程中，第二個參數(shù)中的2就不再變化。

如圖是公式拖動到最下端的效果。

所以由此可以看出，這種既有橫向填充又有縱向填充的，采用混合引用才可以計算出正確的結果。如果弄不清楚，就多試幾次就好了。

接下來可以利用if函數(shù)去除表中的重復項，即在B3單元格修改函數(shù)公式為【=IF(B$2>$A3,"",$A3*B$2)】，并向下、向右填充。

這里的if函數(shù)第一個參數(shù)也采用了混合引用。行數(shù)大于對應的列數(shù)時單元格顯示為空值，否則就顯示計算結果。

三、絕對引用和相對引用在累加計算中的運用。

如下圖所示，收入用正數(shù)表示，支出用負數(shù)表示，要根據(jù)收支計算每天的余額。

在C2單元格中輸入函數(shù)【=SUM($B$2:B2)】，向下拖動后第一個B2保持不變，第二個B2會隨著單元格的變化而變化。

比如在C3單元格中，公式就變成了【=SUM($B$2:B3)】，表示B2到B3單元格值的累計數(shù)。C4單元格就成了【=SUM($B$2:B4)】，表示B2到B4單元格值的累計數(shù)……

四、絕對引用在vlookup函數(shù)中的運用。

vlookup函數(shù)的基本參數(shù)【=vlookup(查找的內容，查找的范圍，查找內容在查找范圍第幾列，邏輯值)】。對于第二個參數(shù)，一般要采用絕對引用，不然很容易出現(xiàn)查找錯誤。

如下圖所示，我在E3單元格中輸入函數(shù)【=VLOOKUP(D3,A1:B9,2,0)】后，向下拖動發(fā)現(xiàn)李明、王小紅的年齡查找錯誤，是因為此處對于vlookup函數(shù)第二個參數(shù)采用了相對引用，向下拖動會改變查找范圍。

所以第二個參數(shù)切換到絕對引用函數(shù)為【=VLOOKUP(D3,$A$1:$B$9,2,0)】，再進行查找就沒有問題了。

這就是相對引用，絕對引用和混合引用在Excel表中的幾種應用，趕快試一下吧。

總結

簡介

• A1 是相對引用
• $A1 絕對引用 列 是 混合引用
• A$1 絕對引用 行 是 混合引用
• $A$1 絕對引用 行和列 是 絕對引用
• $ 在 誰的前面 就 絕對引用誰

F4是在四種引用間相互轉換的快捷鍵(在編輯欄輸入公式時按下F4功能鍵可進行切換)

• A1 (相對引用) 在下拉拖動引用時,會變成引用A2,A3,A4...,右拉拖動時引用變成B1,C1,D1....
• A$1 (混合引用) 當你下拉復制時想保證引用的只是A1單元格時,A1就要加$符號,成 A$1 ,這樣在下拉時能保證對A列第一行的相對引用(即保持行號在引用時不產(chǎn)生變動)
• $A1 (混合引用) 當你右拉復制時想保證引用的只是A1單元格時,A1就要加$符號,成 $A1 ,這樣在右拉時能保證對A列第一行的相對引用(即保持列標在引用時不產(chǎn)生變動)
• $A$1 (絕對引用) 當你在下拉和右拉復制時想保證引用的只是A1單元格時,A1就要加$符號,成$A$1,這樣在下拉和右拉時能保證對A列第一行的絕對引用(即保持行號列標在引用時不產(chǎn)生變動)

相對引用、絕對引用和混合引用是指在公式中使用單元格或單元格區(qū)域的地址時，當將公式向旁邊復制時，地址是如何變化的。

具體情況舉例說明：

• 1、相對引用，復制公式時地址跟著發(fā)生變化，如C1單元格有公式：=A1+B1
• 當將公式復制到C2單元格時變?yōu)椋?A2+B2
• 當將公式復制到D1單元格時變?yōu)椋?B1+C1
• 2、絕對引用，復制公式時地址不會跟著發(fā)生變化，如C1單元格有公式：=$A$1+$B$1
• 當將公式復制到C2單元格時仍為：=$A$1+$B$1
• 當將公式復制到D1單元格時仍為：=$A$1+$B$1
• 3、混合引用，復制公式時地址的部分內容跟著發(fā)生變化，如C1單元格有公式：=$A1+B$1
• 當將公式復制到C2單元格時變?yōu)椋?$A2+B$1
• 當將公式復制到D1單元格時變?yōu)椋?$A1+C$1

規(guī)律：加上了絕對地址符“$”的列標和行號為絕對地址，在公式向旁邊復制時不會發(fā)生變化，沒有加上絕對地址符號的列標和行號為相對地址，在公式向旁邊復制時會跟著發(fā)生變化。混合引用時部分地址發(fā)生變化。

注意：工作薄和工作表都是絕對引用，沒有相對引用。

技巧：在輸入單元格地址后可以按F4鍵切換“絕對引用”、“混合引用”和“相對引用”狀態(tài)。

拓展鏈接：

http://office.microsoft.com/zh-cn/excel-help/HP010342940.aspx?CTT=1

http://office.microsoft.com/zh-cn/mac-excel-help/HA102928059.aspx?CTT=1