平時(shí)在工作中，我們經(jīng)常在Excel函數(shù)中對(duì)一些元素如單元格、行、列等元素進(jìn)行相對(duì)或絕對(duì)的引用。今天我們就來探討一下這兩者的區(qū)別，以及我們又該在什么時(shí)候進(jìn)行相對(duì)或者絕對(duì)引用。
相對(duì)OR絕對(duì)，認(rèn)識(shí)引用
在Excel函數(shù)中少不了需要對(duì)行列的引用，相對(duì)引用是指在引用時(shí)，引用的行列會(huì)隨著位置不同而發(fā)生變化。比如在下表中，假設(shè)我們?cè)贑1處輸入公式“=A1”，當(dāng)我們下拉公式的時(shí)候，公式會(huì)依次變?yōu)?ldquo;=A2”、“=A3”，橫拉則會(huì)依次變?yōu)?ldquo;=B1”、“=C1”。也就是說無論公式是下拉或是橫拉，引用的行列數(shù)值都會(huì)同步發(fā)生變化，這就是相對(duì)引用（圖1）。
圖1 相對(duì)引用
如果在D1輸入公式“=$A1”，那么在下拉公式的時(shí)候，公式會(huì)依次變?yōu)?ldquo;=$A2”、“=$A3”，橫拉則保持不變一直為“=$A1”，也就是說在表示行標(biāo)記的字母前加上“$”符號(hào)后，表示是對(duì)行的鎖定，這樣公式在橫拉后保持對(duì)原來行數(shù)的引用，這就是絕對(duì)引用（示例是針對(duì)行）。如果公式改為“=A$1”，則表示對(duì)列的絕對(duì)引用，如果公式變成=$A$1則行列均鎖定，就是所謂的“行列均絕對(duì)引用”（圖2）。
圖2 絕對(duì)引用
因地制宜，相對(duì)/絕對(duì)引用有講究
上面我們了解了什么是相對(duì)引用和絕對(duì)引用，那么在實(shí)際工作中，相對(duì)引用和絕對(duì)引用各自應(yīng)該應(yīng)用在什么場合？
1.相對(duì)引用
由于相對(duì)引用是對(duì)行列進(jìn)行同步的變化，因此對(duì)于沒有特殊要求的公式，常規(guī)情況下都應(yīng)該使用相對(duì)引用。這樣在公式進(jìn)行下拉或者橫拉的時(shí)候，公式的引用值才不會(huì)出錯(cuò)。比如我們常用的SUM求和函數(shù)，在類似“=SUM(A1:A13)”公式中，這里對(duì)(A1:A13)區(qū)域進(jìn)行相對(duì)引用，如果區(qū)域數(shù)值發(fā)生變化，比如刪減或者增加了行數(shù)，那么公式會(huì)自動(dòng)進(jìn)行引用，從而始終保證引用的正確（圖3）。
圖3 相對(duì)引用區(qū)域
2.絕對(duì)引用
除了相對(duì)引用外，在實(shí)際工作中還有很多時(shí)候是需要對(duì)行列進(jìn)行絕對(duì)引用。這樣引用的數(shù)值才不會(huì)出現(xiàn)錯(cuò)誤。比如我們經(jīng)常使用Vlookup函數(shù)制作查詢語句，因?yàn)椴樵兊姆秶枪潭ǎ跃托枰褂媒^對(duì)引用固定查詢區(qū)域。
比如下表是使用Vlookup進(jìn)行查詢A1:E5的數(shù)據(jù)，如果使用相對(duì)引用的語句，在B11輸入公式“=VLOOKUP(A11,A1:E5,2,0)”，將函數(shù)下拉的時(shí)候B13會(huì)出現(xiàn)“#N/A”的提示（圖4）。
圖4 相對(duì)引用導(dǎo)致引用錯(cuò)誤
這個(gè)錯(cuò)誤就是由于相對(duì)引用造成的，定位到B13，可以看到這里的公式是“=VLOOKUP(A13,A3:E7,2,0)”，也就是在A3:E7的范圍去查找數(shù)據(jù)，因?yàn)楫a(chǎn)品A實(shí)際是在A2單元格（不在A3:E7區(qū)域），所以導(dǎo)致引用錯(cuò)誤。因此要實(shí)現(xiàn)引用正確性，我們就需要對(duì)A1:E5區(qū)域進(jìn)行絕對(duì)引用。同上定位到B11，將公式更改為“=VLOOKUP(A11,$A$1:$E$5,2,0)”（在Excel中輸入公式時(shí),只要按下F4鍵,就能簡單地對(duì)單元格的相對(duì)引用和絕對(duì)引用進(jìn)行切換），這里使用$固定引用區(qū)域，這樣公式下拉后就不會(huì)再出現(xiàn)引用錯(cuò)誤了（圖5）。
圖5 絕對(duì)引用可以避免錯(cuò)誤
上述例子是固定區(qū)域進(jìn)行絕對(duì)引用，在實(shí)際使用中我們還可以根據(jù)實(shí)際需要對(duì)行、列、或者單元格進(jìn)行絕對(duì)引用。比如在產(chǎn)品系列的利潤率是一定的情況下，當(dāng)我們對(duì)下表的產(chǎn)品利潤總和進(jìn)行計(jì)算（銷售額*利潤率）。由于利潤率是固定引用B6的數(shù)據(jù)，此時(shí)此時(shí)就需要在E2輸入公式“=D2*$B$6”（即固定B6進(jìn)行絕對(duì)引用，如果使用相對(duì)引用，則會(huì)導(dǎo)致引用的利潤率數(shù)值不準(zhǔn)確），這樣下拉公式就不會(huì)出現(xiàn)數(shù)據(jù)錯(cuò)誤了（圖6）。
圖6 絕對(duì)引用單元格
3.混合引用
上面主要介紹相對(duì)引用和絕對(duì)引用，在實(shí)際操作中還會(huì)遇到混合引用，也就是在一個(gè)公式中同時(shí)需要上述兩種引用。比如下表數(shù)據(jù)中，一款產(chǎn)品針對(duì)不同等級(jí)會(huì)員有多個(gè)售價(jià)，如果要快速計(jì)算出每種會(huì)員的價(jià)格，此時(shí)就需要使用混合引用。
因?yàn)檫@里折扣參數(shù)是在第2行（E2和F2），C、D列數(shù)據(jù)是將B列數(shù)據(jù)*折扣率獲取的。因此在這個(gè)案例中，我們需要固定B列數(shù)據(jù)和固定第二行數(shù)據(jù)，使得公式在下拉和右拉的時(shí)候，基準(zhǔn)價(jià)始終引用的是B列數(shù)據(jù)，而右拉的時(shí)候則是始終引用第二行的折扣數(shù)據(jù)。這就是一個(gè)典型的混合引用（圖7）。
圖7 混合引用示例
定位到C2輸入公式“=$B2*E$2”（固定B列，固定第2行），這樣將公式下拉右拉可以看到引用的就都是固定行列的數(shù)據(jù)，這樣可以快速求出不同會(huì)員的實(shí)際折扣售價(jià)（圖8）。
圖8 混合引用
| 
