Excel引用相對還是絕對

俞木發

相對OR絕對 認識引用

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

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

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

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

相對引用

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

絕對引用

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

比如下表是使用V l o o k u p查詢A1：E5的數據，如果使用相對引用的語句，在B11輸入公式“=VLOOKUP （A11，A1：E5， 2，0）”，將函數下拉的時候B13會出現“#N/A”的提示（圖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）。

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

混合引用

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

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

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