在Excel中，學(xué)習(xí)函數(shù)始終繞不開(kāi)相對(duì)引用和絕對(duì)引用，引用方法得當(dāng)，才能對(duì)函數(shù)靈活運(yùn)用。否則計(jì)算結(jié)果很可能出現(xiàn)錯(cuò)誤。本文就以幾個(gè)常用的案例介紹一下Excel中的相對(duì)引用、絕對(duì)引用與混合引用的用法吧。

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

$在誰(shuí)的前面就絕對(duì)引用誰(shuí)

一、相對(duì)引用、絕對(duì)引用基本情況介紹。

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

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

在D3單元格輸入公式【=A3*B3】，向下拖動(dòng)，隨著單元格的變化，計(jì)算公式和計(jì)算結(jié)果也會(huì)發(fā)生變化。因?yàn)榇颂帉?duì)A列和B列的單元格均采用了相對(duì)引用。

在E3單元格輸入公式【=$A$3*B3】，向下拖動(dòng)后，引用的A列單元格一直是A3單元格，不會(huì)發(fā)生變化，而引用B列單元格會(huì)發(fā)生變化。因?yàn)榇颂帉?duì)A3單元格采用了絕對(duì)引用。

在F3單元格輸入公式【=$A$3*$B$3】，向下拖動(dòng)后，公式一直保持一致，因?yàn)榇颂帉?duì)A3和B3單元格都采用了絕對(duì)引用。

二、混合引用在九九乘法表中的運(yùn)用。

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

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

如圖是拖動(dòng)到最右端的時(shí)候效果。

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

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

如圖是公式拖動(dòng)到最下端的效果。

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

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

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

三、絕對(duì)引用和相對(duì)引用在累加計(jì)算中的運(yùn)用。

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

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

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

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

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

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

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

這就是相對(duì)引用，絕對(duì)引用和混合引用在Excel表中的幾種應(yīng)用，趕快試一下吧。

總結(jié)

簡(jiǎn)介

• A1 是相對(duì)引用
• $A1 絕對(duì)引用 列 是 混合引用
• A$1 絕對(duì)引用 行 是 混合引用
• $A$1 絕對(duì)引用 行和列 是 絕對(duì)引用
• $ 在 誰(shuí)的前面 就 絕對(duì)引用誰(shuí)

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

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

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

具體情況舉例說(shuō)明：

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

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

注意：工作薄和工作表都是絕對(duì)引用，沒(méi)有相對(duì)引用。

技巧：在輸入單元格地址后可以按F4鍵切換“絕對(duì)引用”、“混合引用”和“相對(duì)引用”狀態(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