今天，財務(wù)部新人小王發(fā)現(xiàn)：用VLOOKUP函數(shù)公式查詢數(shù)據(jù)的時候，隨著公式的下拉填充，明明在數(shù)據(jù)源中已經(jīng)存在的唯一編碼，竟然查找不到對應(yīng)的物料名稱，返回錯誤值！這場數(shù)據(jù)查詢的烏龍事件，正是單元格地址中的神秘符號惹的禍。今天的文章，小編同志將帶你用"鎖與鑰匙"的思維，掌握Excel引用的終極控制術(shù)。

$是Excel單元格中“鎖定”符號，$是單元格地址絕對引用符號，一般放在單元格地址的行標(biāo)或列標(biāo)之前。當(dāng)單元格地址的行標(biāo)或列標(biāo)之前加有$符號后，該地址不會隨著單元格的變化而變化。

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大小：2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

在Excel或WPS表格中存在三種“神秘”的引用類型。三種引用類型有著明顯的基因差異。小編將其歸納為三派：分別是自由派、保守派，中立派。這三派猶如一把鎖，將引用區(qū)域牢牢束縛。

相對引用：隨遇而安的自由派

比如我們要在D列統(tǒng)計金額（數(shù)量*單價的結(jié)果）

我們輸入公式：

=B2*C2

當(dāng)下拉填充公式的時候，自動變成：

=B3*C3

=B4*C4

=B5*C5

特性歸納：

相對引用如同不系繩的熱氣球，坐標(biāo)隨位置自由漂移。

絕對引用：倔強的保守派

比如我們有兩列數(shù)量，D2單元格是一個固定的不變的單價3，我們想要讓兩列中所有的數(shù)量都與這個固定單價3相乘，分別得到所有的兩列金額，顯示在E:F列對應(yīng)的位置。

我們輸入公式：

=B2*$D$2

隨著公式的向右并向下填充，D2的單價參數(shù)永不移動：

=B2*$D$2 =C2*$D$2

=B3*$D$2 =C3*$D$2

=B4*$D$2 =C4*$D$2

=B5*$D$2 =C5*$D$2

特性歸納：

用雙$（$D$2）給行號與列號同時上鎖，化身數(shù)據(jù)堡壘。

混合引用：講究策略的中立派。

比如我們有數(shù)量1列與數(shù)量2列，C2單元格是數(shù)量1列的單價，D2單元格是數(shù)量2列的單價，我們想要將兩個數(shù)量列與之對應(yīng)的兩個固定單價分別相乘，得到新的兩列金額顯示在E:F列。

我們輸入公式：

=A2*C$2

隨著公式的向右填充，固定單價C2到D2是自由的：

=A2*C$2 =B2*D$2

也就是說橫向填充公式時鎖定了首行單價。

隨著公式的向下填充，C2到D2是束縛的，永不移動。

=A2*C$2 =B2*D$2

=A3*C$2 =B3*D$2

=A4*C$2 =B4*D$2

=A5*C$2 =B5*D$2

特性歸納：

單$符號實現(xiàn)選擇性禁錮：列自由+行鎖定（如本例），或反之列鎖定+行自由（同理未舉例）。

上面我們講解了鎖（三種引用）特性，那么鎖與鑰匙的操作密碼是什么呢？哈哈，其實是鍵盤上的F4鍵，選中需要上鎖的參數(shù)區(qū)域，按下F4鍵即可切換不同的鎖。

鎖1（相對引用）

無$：B2完全自由

鎖2（絕對引用）

按F4鍵加雙&（行號列號前均有&）：$B$2完全禁錮

鎖3（混合引用-鎖行不鎖列）

按F4鍵行號前加&：B&2鎖定行號，無論往哪填充，始終鎖定在第2行。

鎖3（混合引用-鎖列不鎖行）

按F4鍵列號前加&：&B2鎖定列號，無論往哪填充，始終鎖定在B列。

F4鍵的形態(tài)切換術(shù)：選中公式中的單元格引用，按F4鍵可在四種狀態(tài)間循環(huán)，我們繼續(xù)用一個四宮格來演示一下變化過程，可以加深我們的理解：

自由流動用相對引用（無$）

永恒坐標(biāo)用絕對引用（雙$）

縱橫捭闔用混合引用（行號前單$）鎖行不鎖列

縱橫捭闔用混合引用（列號前單$）鎖列不鎖行

我們回到文章開頭的場景，財務(wù)部新人小王遇到的問題：為什么查詢到E6單元格的編碼“a101”以及下方的編碼時出現(xiàn)了錯誤值：

F2單元格起始位置的公式：

=VLOOKUP(E2,B2:C10,2,0)

查詢到F6單元格出錯時的公式：

=VLOOKUP(E6,B6:C14,2,0)

很明顯VLOOKUP的第2參數(shù)查詢區(qū)域已經(jīng)由最初的“B2:C10”嚴(yán)重偏移到了B6:C14區(qū)域，而需要查找的“a101”已經(jīng)不存在于B6:C14區(qū)域了，所以查找不到出現(xiàn)了錯誤值。也就是說最初的B2:C10是相對引用，已經(jīng)“漂移”到了B6:C14區(qū)域。

我們需要做的就是對最初的：

=VLOOKUP(E2,B2:C10,2,0)

第2參數(shù)B2:C10加上絕對引用，目的就是讓他固定不變。

=VLOOKUP(E2,$B$2:$C$10,2,0)

那為什么財務(wù)部新人小王之前會認(rèn)為：我以前就是這么做的，咋沒出現(xiàn)錯誤呢？原因可能有兩點：

第一點就是他之前可能選擇的VLOOKUP第2參數(shù)查詢區(qū)域為整個的B與C兩列B:C：

=VLOOKUP(E2,B:C,2,0)

那么無論怎樣下拉填充，范圍都會始終是全部的B:C兩列，所以涉及不到查詢范圍的“漂移”現(xiàn)象。

還有一點就是之前可能存在一種僥幸，就是即使第二參數(shù)范圍變化了，但是碰巧所有的查詢值都存在于這個變化的區(qū)域內(nèi)，所以正好全部都查詢到了，但是在實際的工作中，我們一定要有嚴(yán)謹(jǐn)?shù)墓ぷ鲬B(tài)度，不能存在僥幸心理。

總結(jié)：

下次當(dāng)你的公式開始"暴走"時，請優(yōu)雅地按下F4鍵，用$符號為數(shù)據(jù)戴上量身定制的鐐銬。畢竟在Excel的世界里，真正的自由，源于對禁錮的精準(zhǔn)掌控。

推薦閱讀：掌握這些公式就夠了! excel 6個常用排名公式大全

最新評論