平時在工作中，我們經常在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）。
圖6 絕對引用單元格
3.混合引用
上面主要介紹相對引用和絕對引用，在實際操作中還會遇到混合引用，也就是在一個公式中同時需要上述兩種引用。比如下表數據中，一款產品針對不同等級會員有多個售價，如果要快速計算出每種會員的價格，此時就需要使用混合引用。
因為這裡折扣參數是在第2行（E2和F2），C、D列數據是將B列數據*折扣率獲取的。因此在這個案例中，我們需要固定B列數據和固定第二行數據，使得公式在下拉和右拉的時候，基準價始終引用的是B列數據，而右拉的時候則是始終引用第二行的折扣數據。這就是一個典型的混合引用（圖7）。
圖7 混合引用示例
定位到C2輸入公式「=$B2*E$2」（固定B列，固定第2行），這樣將公式下拉右拉可以看到引用的就都是固定行列的數據，這樣可以快速求出不同會員的實際折扣售價（圖8）。
圖8 混合引用
上海艾磊科技有限公司專門為企業提供IT諮詢，IT外包，系統集成，以及各類IT增值服務。其中增值服務包括OFFICE 365雲服務，鼎捷企業ERP管理軟體，雲備份，企業郵箱，無線覆蓋，上網行為管理，VPN架設，網絡安全服務，INTERNET接入，設備租賃， IP電話服務。