辦公小技巧：Excel引用相對還是絕對

科技 04-24

平時在工作中，我們經常在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）。

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