函數(shù)中最受歡迎的有三大家族,一個(gè)是以SUM函數(shù)為首的求和家族,一個(gè)是以VLOOKUP函數(shù)為首的查找引用家族,另外一個(gè)就是以IF函數(shù)為首的邏輯函數(shù)家族。根據(jù)二八定律,學(xué)好這三大家族的函數(shù),就能完成80%的工作。 現(xiàn)在一起來學(xué)習(xí)VLOOKUP函數(shù),讓關(guān)于查找的煩惱一次全解決! 1、根據(jù)番號精確查找俗稱。
VLOOKUP函數(shù)語法:
VLOOKUP函數(shù)示意圖。 2、屏蔽錯(cuò)誤值錯(cuò)誤值查找。
VLOOKUP函數(shù)如果查找不到對應(yīng)值會顯示錯(cuò)誤值#N/A,這個(gè)看起來很不美觀。這時(shí)可以在外面加個(gè)容錯(cuò)函數(shù)IFERROR,如果是2013版本那就更好,可以用IFNA函數(shù),這個(gè)是專門處理#N/A這種錯(cuò)誤值。
函數(shù)語法:
說白了就是將錯(cuò)誤值顯示成你想要的結(jié)果,不是錯(cuò)誤值就返回原來的值。IFNA函數(shù)的作用也是一樣,只是IFERROR函數(shù)是針對所有錯(cuò)誤值,而IFNA函數(shù)只針對#N/A。 3、按順序返回多列對應(yīng)值。 通過上面的例子,我們知道可以通過更改第3參數(shù),返回各項(xiàng)對應(yīng)值如:
如果項(xiàng)目少,更改幾次參數(shù)也沒什么,但項(xiàng)目多時(shí),肯定不方便。如圖 5?103所示,可以通過ROW、COLUMN產(chǎn)生行列號,從而得到1,2,……,n的值。
因?yàn)檫@里是同一行產(chǎn)生序號,所以用COLUMN函數(shù)。 4、按不同順序返回對應(yīng)值。 這回看來只能手動更改第3參數(shù)了,COLUMN完全派不上用場。 NO!每當(dāng)你覺得操作繁瑣時(shí),就要停下來思考,也許Excel本身存在這個(gè)功能,只是自己一時(shí)想不到或者不知道而已。列號不管千變?nèi)f化,在數(shù)據(jù)源的位置始終不變,利用這個(gè)特點(diǎn)可以去搜索一下看看有什么函數(shù)可以解決。 在“搜索函數(shù)”文本框輸入:位置,單擊“轉(zhuǎn)到”按鈕,就會出現(xiàn)跟位置有關(guān)的函數(shù),查看每個(gè)函數(shù)的說明,找到我們需要的,如MATCH函數(shù),返回符合特定值特定順序的項(xiàng)在數(shù)組中的相應(yīng)位置,單擊“確定”按鈕。 在彈出的“函數(shù)參數(shù)對話框”中嘗試填寫相應(yīng)的參數(shù),每個(gè)參數(shù)的作用下面都有相關(guān)說明,填寫后會出現(xiàn)計(jì)算結(jié)果3,也就是訂單數(shù)在區(qū)域中是第3列。嘗試下更改第1參數(shù)為C12(俗稱),計(jì)算結(jié)果是2,也就是區(qū)域中第2列。經(jīng)過嘗試,知道這個(gè)函數(shù)是我們要找的那個(gè)函數(shù),單擊“取消”按鈕,返回工作表。 在單元格再做最后一次驗(yàn)證。 到這一步已經(jīng)十拿九穩(wěn)了,將公式設(shè)置為:
5、根據(jù)番號逆序俗稱。 幫助提到VLOOKUP函數(shù)只能按首列查找,不能逆向查找,既然如此,那就得想辦法將非首列的區(qū)域轉(zhuǎn)換成首列。怎么轉(zhuǎn)換區(qū)域呢,這時(shí)IF函數(shù)就派上用場。一步步來了解IF函數(shù)的轉(zhuǎn)換。 看看好友傳遞如何趣聊IF函數(shù),吃貨的福音。 IF函數(shù)其實(shí)只有一個(gè)條件來判斷是否符合條件,返回FALSE和TRUE兩種結(jié)果。 當(dāng)菜只有分甜的或咸的2種口味時(shí),甜味是紅燒肉,咸味是醬油肉。
把這段話用公式來寫:
翻譯:是咸的嗎?要是(TRUE),就是醬油肉,要是不是咸的(FALSE),就是甜的紅燒肉。 A1='咸的'這個(gè)條件也可以直接換成TRUE或者FALSE。
因?yàn)闈M足條件,所以返回A2的對應(yīng)值醬油肉。
因?yàn)椴粷M足條件,所以返回B2的對應(yīng)值紅燒肉。 其實(shí)TRUE=1,F(xiàn)ALSE=0,所以可以直接用1跟0表示。
IF函數(shù)不止可以返回1個(gè)單元格的值,也可以返回多個(gè)單元格的值。
選擇兩個(gè)單元格輸入,按Ctrl Shift Enter三鍵結(jié)束。條件為{1,0},返回A2:B2的對應(yīng)值順序不變;條件為{0,1},返回A2:B2的對應(yīng)值,順序?qū)Q。也就是說通過改變1跟0的位置,可以調(diào)換兩單元格的前后位置。 看到這里,知道IF函數(shù)通過改變1,0可以調(diào)換單元格的順序,如果要改變區(qū)域的順序也是可以實(shí)現(xiàn)的。 用IF函數(shù)重新構(gòu)造的新區(qū)域,是多單元格數(shù)組公式,記得按Ctrl Shift Enter三鍵結(jié)束,否則出錯(cuò)。 新區(qū)域:
所以公式可以變成:
兩個(gè)公式合并,大功告成。
6、根據(jù)俗稱跟訂單號兩個(gè)條件查詢完成情況。 正常情況下VLOOKUP函數(shù)是不能多條件查詢,通過IF函數(shù)的學(xué)習(xí),我們知道IF函數(shù)可以重新構(gòu)造區(qū)域,這里就再次用IF構(gòu)成一個(gè)區(qū)域。 新區(qū)域:
所以公式可以變成:
兩個(gè)公式合并,大功告成,記得按Ctrl Shift Enter三鍵結(jié)束。
7、根據(jù)俗稱的第一個(gè)字符查找番號。
星號(*)是通配符,代表所有字符,問號(?)代表一個(gè)字符。D2&'*'就是開頭包含D2的意思。 8、根據(jù)區(qū)域判斷成績的等級。 借助輔助列的話,很容易查詢等級,只需將VLOOKUP函數(shù)的第四參數(shù)設(shè)置為1或者省略即可。
如果不用輔助列,估計(jì)很多人看到這條公式就得哭了,得結(jié)合前面所有函數(shù)知識才能完成,有興趣的朋友可以自己去研究。
前陣子無意間發(fā)現(xiàn)了IMREAL函數(shù),所以不用輔助列的數(shù)組公式可以稍微簡單一點(diǎn)。
IMREAL函數(shù)是計(jì)算復(fù)數(shù)的實(shí)部系數(shù)的函數(shù),作用就是提取區(qū)間的下限。 通過這8個(gè)疑難,基本上的查詢問題都能夠解決。 開心嗎?一下搞定8大疑難! 盧子簡介:20萬讀者支持的《Excel效率手冊 早做完,不加班》系列叢書作者,十年的Excel職場經(jīng)驗(yàn),精通Excel函數(shù)和數(shù)據(jù)透視表。 |
|