平時在工作中，我們經常在Excel函數中對一些元素如單元格、行、列等元素進行相對或絕對的引用。今天我們就來探討一下這兩者的區別，以及我們又該在什么時候進行相對或者絕對引用。

相對OR絕對，認識引用

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

圖1 相對引用

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

圖2 絕對引用

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

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

1.相對引用

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

圖3 相對引用區域

2.絕對引用

除了相對引用外，在實際工作中還有很多時候是需要對行列進行絕對引用。這樣引用的數值才不會出現錯誤。比如我們經常使用Vlookup函數制作查詢語句，因為查詢的范圍是固定，所以就需要使用絕對引用固定查詢區域。

比如下表是使用Vlookup進行查詢A1:E5的數據，如果使用相對引用的語句，在B11輸入公式“=VLOOKUP(A11,A1:E5,2,0)”，將函數下拉的時候B13會出現“#N/A”的提示（圖4）。

圖4 相對引用導致引用錯誤

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

圖5 絕對引用可以避免錯誤

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

圖6 絕對引用單元格

3.混合引用

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

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

圖7 混合引用示例

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

圖8 混合引用