Excel引用相對還是絕對

俞木發(fā)

相對OR絕對 認(rèn)識引用

在Excel函數(shù)中少不了需要對行列的引用，相對引用是指在引用時，引用的行列會隨著位置的不同而發(fā)生變化。比如在下表中，假設(shè)我們在C1處輸入公式“=A1”，當(dāng)我們下拉公式的時候，公式會依次變?yōu)椤?A2”、“=A3”，橫拉則會依次變?yōu)椤?B1”、“=C1”。也就是說無論公式是下拉或是橫拉，引用的行列數(shù)值都會同步發(fā)生變化，這就是相對引用（圖1）。

如果在D1輸入公式“=$A1”，那么在下拉公式的時候，公式會依次變?yōu)椤?$A2”、“=$A3”，橫拉則保持不變，一直為“=$A1”。也就是說在表示行標(biāo)記的字母前加上“$”符號后，表示是對行的鎖定，這樣公式在橫拉后保持對原來行數(shù)的引用，這就是絕對引用（示例是針對行）。如果公式改為“=A$1”，則表示對列的絕對引用，如果公式變成=$A$1則行列均鎖定，就是所謂的“行列均絕對引用”（圖2）。

因地制宜 相對/絕對引用有講究

上面我們了解了什么是相對引用和絕對引用，那么在實(shí)際工作中，相對引用和絕對引用各自應(yīng)該應(yīng)用在什么場合？

相對引用

由于相對引用是對行列進(jìn)行同步的變化，因此對于沒有特殊要求的公式，常規(guī)情況下都應(yīng)該使用相對引用。這樣在公式進(jìn)行下拉或者橫拉的時候，公式的引用值才不會出錯。比如我們常用的SUM求和函數(shù)，在類似“=SUM（A1：A13）”公式中，這里對（A1：A13）區(qū)域進(jìn)行相對引用，如果區(qū)域數(shù)值發(fā)生變化，比如刪減或者增加了行數(shù)，那么公式會自動進(jìn)行引用，從而始終保證引用的正確（圖3）。

絕對引用

除了相對引用外，在實(shí)際工作中還有很多時候是需要對行列進(jìn)行絕對引用，這樣引用的數(shù)值才不會出現(xiàn)錯誤。比如我們經(jīng)常使用Vlookup函數(shù)制作查詢語句，因?yàn)椴樵兊姆秶枪潭ǖ?，所以就需要使用絕對引用固定查詢區(qū)域。

比如下表是使用V l o o k u p查詢A1：E5的數(shù)據(jù)，如果使用相對引用的語句，在B11輸入公式“=VLOOKUP （A11，A1：E5， 2，0）”，將函數(shù)下拉的時候B13會出現(xiàn)“#N/A”的提示（圖4）。

這個錯誤就是由于相對引用造成的。定位到B13，可以看到這里的公式是“=VLOOKUP （A13， A3：E7，2，0）”，也就是在A3：E7的范圍去查找數(shù)據(jù)，因?yàn)楫a(chǎn)品A實(shí)際是在A2單元格（不在A3：E7區(qū)域），所以導(dǎo)致引用錯誤。因此要實(shí)現(xiàn)引用的正確性，我們就需要對A1：E5區(qū)域進(jìn)行絕對引用。同上定位到B11，將公式更改為“=VLOOKUP（A11，$A$1：$E$5，2，0）”（在Excel中輸入公式時，只要按下F4鍵，就能簡單地對單元格的相對引用和絕對引用進(jìn)行切換），這里使用$固定引用區(qū)域，這樣公式下拉后就不會再出現(xiàn)引用錯誤了（圖5）。

上述例子是固定區(qū)域進(jìn)行絕對引用，在實(shí)際使用中我們還可以根據(jù)實(shí)際需要對行、列或者單元格進(jìn)行絕對引用。比如在產(chǎn)品系列的利潤率是一定的情況下，當(dāng)我們對下表的產(chǎn)品利潤總和進(jìn)行計(jì)算（銷售額*利潤率）。由于利潤率是固定引用B6的數(shù)據(jù)，此時就需要在E2輸入公式“=D2*$B$6”（即固定B6進(jìn)行絕對引用，如果使用相對引用，則會導(dǎo)致引用的利潤率數(shù)值不準(zhǔn)確），這樣下拉公式就不會出現(xiàn)數(shù)據(jù)錯誤了（圖6）。

混合引用

上面主要介紹相對引用和絕對引用，在實(shí)際操作中還會遇到混合引用，也就是在一個公式中同時需要上述兩種引用。比如下表數(shù)據(jù)中，一款產(chǎn)品針對不同等級會員有多個售價，如果要快速計(jì)算出每種會員的價格，此時就需要使用混合引用。

因?yàn)檫@里折扣參數(shù)是在第2行（E2和F2），C、D列數(shù)據(jù)是將B列數(shù)據(jù)*折扣率獲取的。因此在這個案例中，我們需要固定B列數(shù)據(jù)和固定第二行數(shù)據(jù)，使得公式在下拉和右拉的時候，基準(zhǔn)價始終引用的是B列數(shù)據(jù)，而右拉的時候則是始終引用第二行的折扣數(shù)據(jù)。這就是一個典型的混合引用（圖7）。

定位到C2，輸入公式“=$B2*E$2”（固定B列，固定第2行），這樣將公式下拉、右拉可以看到引用的就都是固定行列的數(shù)據(jù)，這樣可以快速求出不同會員的實(shí)際折扣售價（圖8）。