真正的Excel高手,能把「引用」玩得風生水起~

  上一回,我們學習了 Excel 「絕對引用」和「相對引用」,表格界的各路小白紛紛表示:

真正的Excel高手,能把「引用」玩得風生水起~

為了滿足大家按捺不住的學(zhuang)習(bi)之心,今天就繼續學習!

如果學習過程中覺得困難,發現自己完全分不清「絕對引用」和「相對引用」,不知道如何切換,請立即回到這裡,點擊「Excel 高手必備絕技」回顧基本知識(前三式)!

真正的Excel高手,能把「引用」玩得風生水起~

累計值計算是 Excel 界經常遇到的小 boss,功力不夠的少俠,往往是這樣解決的:

真正的Excel高手,能把「引用」玩得風生水起~

效率低得令人髮指真正的Excel高手,能把「引用」玩得風生水起~

但是,用 SUM 函數,立馬秒殺累計值

真正的Excel高手,能把「引用」玩得風生水起~

這裡需要注意,很多少俠剛用這招時,經常忽略=SUM($B$2:B2) 中 $B$2 是絕對引用,以至於出現怎麼都算不對,最終導致走火入魔。

真正的Excel高手,能把「引用」玩得風生水起~

VLOOKUP函數雖然是個神器,但使用時極容易出現引用錯誤。多少人在出現 #N/A 時,百思不解無法處理,又是走火入魔。

比如下圖中,公式在第一個單元格明明好好的,「周伯通」都顯示出來了,往下拖拉填充,卻出現了 #N/A。

真正的Excel高手,能把「引用」玩得風生水起~

問題出在哪?先看輸出正確結果單元格的公示,注意紅框中的區域:

真正的Excel高手,能把「引用」玩得風生水起~

而出現 #N/A 錯誤的單元格,它的公式卻變了,

真正的Excel高手,能把「引用」玩得風生水起~

上圖很容易說明原因,紅色區域是 VLOOKUP函數引用的區域,由於引用區域沒有鎖定,導致向下填充時,引用區域跟著發生相對位移,結果查詢失敗,出現了 #N/A。

真正的Excel高手,能把「引用」玩得風生水起~

一句講嗮,你沒給錢(沒鎖定),人家搗亂不給用,所以給錢就好。

真正的Excel高手,能把「引用」玩得風生水起~

是不是覺得功力又大漲一成?

嘿嘿,你知道 VLOOKUP 函數還有進階的高級玩法嘛!

真正的Excel高手,能把「引用」玩得風生水起~

根據一個參數,查找對應的其他信息,我們這裡把它稱為「VLOOKUP一對多」

先看看效果:

真正的Excel高手,能把「引用」玩得風生水起~

只要添加一行輔助行,我們就可以做到寫一個公式,通過拖動填充,快速完成其他參數的查找~

想知道這個招怎麼做到?三步快速搞定!

第 1 步,添加輔助行

在表格上方插入一行輔助行,在輔助行中填入這一列對應的數字編號。

真正的Excel高手,能把「引用」玩得風生水起~

第 2 步,輸入 VLOOKUP 函數

點擊編號單元格,並切換成$A3混合引用形式,引用源數據區域,鎖定引用區域單元格

真正的Excel高手,能把「引用」玩得風生水起~

點擊輔助行的單元格,並切換成B$1混合引用形式,選擇近似匹配

真正的Excel高手,能把「引用」玩得風生水起~

第 3 步,愉快地拖動快速填充小手柄

真正的Excel高手,能把「引用」玩得風生水起~

學習這一招,必須注意以下 3 點,避免走火入魔

查找值單元格,是$A3混合引用形式,而不是其他引用形式

第 2 步中,引用區域一定記得都給錢,全部絕對引用

第 2 步中,最後輸入 0 或 1 ,還是輸入 FALSE 或 TRUE 都可以,在 Excel 裡 0 可以代指 FALSE,1 代指 TRUE。

處女座少俠表示很憋屈,看著輔助行,想去掉又去不掉真是難受。

真正的Excel高手,能把「引用」玩得風生水起~

好好好,我們就把輔助行去掉,把第三個參數換成了函數 COLUMN就行啦~

COLUMN 函數的作用是直接顯示指定單元格在第幾列,看下面的動圖你就知道:

真正的Excel高手,能把「引用」玩得風生水起~

A1 – A4 在 A 列,也就是第 1 列,所以都顯示數字 1。

A1 – F1,分別是 A/B/C/D/E/F 列,等於第 1/2/3/4/5/6 列,顯示數字1/2/3/4/5/6。

輸入 COLUMN 函數直接顯示它們的列數。

利用這一點,COLUMN 函數可以起到替代輔助行的作用:

真正的Excel高手,能把「引用」玩得風生水起~

怎麼樣,這麼厲害的 Excel,爽不爽!

不過要注意,學習 Excel 需謹慎,要一步步看仔細,切記不可囫圇吞棗,當心走火入魔!

本文作者:TT