學電腦知識

小技巧:Excel引用相對還是絕對

學電腦知識 http://www.nnyvff.tw 2019-05-13 12:31 出處:網絡 編輯:@學電腦
平時在工作中,我們經常在Excel函數中對一些元素如單元格、行、列等元素進行相對或絕對的引用。今天我們就來探討一下這兩者的區別,以及我們又該在什么時候進行相對或者絕對引用。

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

相對OR絕對,認識引用

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

小技巧:Excel引用相對還是絕對

圖1 相對引用

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

小技巧:Excel引用相對還是絕對

圖2 絕對引用

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

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

1.相對引用

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

小技巧:Excel引用相對還是絕對

圖3 相對引用區域

2.絕對引用

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

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

小技巧:Excel引用相對還是絕對

圖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)。

小技巧:Excel引用相對還是絕對

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

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

小技巧:Excel引用相對還是絕對

圖6 絕對引用單元格

3.混合引用

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

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

小技巧:Excel引用相對還是絕對

圖7 混合引用示例

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

小技巧:Excel引用相對還是絕對

圖8 混合引用


0

上一篇:

沒有了:下一篇

精彩評論

双色球26选5开奖结果