Zi 字媒體

點擊圖片上方「會計視野」藍色小字，可訂閱本微信。函數中最受歡迎的有三大家族，一個是以SUM函數為首的求和家族，一個是以VLOOKUP函數為首的查找引用家族，另外一個就是以IF函數為首的邏輯函數家族。根據二八定律，學好這三大家族的函數，就能完成80%的工作。現在一起來學習VLOOKUP函數，讓關於查找的煩惱一次全解決！1、根據番號精確查找俗稱。=VLOOKUP(D2,A:B,2,0)VLOOKUP函數語法：=VLOOKUP(查找值,查找區域,返回查找區域第N列,查找模式)VLOOKUP函數示意圖。2、屏蔽錯誤值錯誤值查找。VLOOKUP函數如果查找不到對應值會顯示錯誤值#N/A，這個看起來很不美觀。這時可以在外面加個容錯函數IFERROR，如果是2013版本那就更好，可以用IFNA函數，這個是專門處理#N/A這種錯誤值。=IFERROR(VLOOKUP(D2,A:B,2,0),"")=IFNA(VLOOKUP(D2,A:B,2,0),"")函數語法：=IFERROR(表達式,錯誤值要顯示的結果)說白了就是將錯誤值顯示成你想要的結果，不是錯誤值就返回原來的值。IFNA函數的作用也是一樣，只是IFERROR函數是針對所有錯誤值，而IFNA函數只針對#N/A。3、按順序返回多列對應值。通過上面的例子，我們知道可以通過更改第3參數，返回各項對應值如：=VLOOKUP($A13,$A$1:$F$10,2,0)=VLOOKUP($A13,$A$1:$F$10,3,0)如果項目少，更改幾次參數也沒什麼，但項目多時，肯定不方便。如圖 5‑103所示，可以通過ROW、COLUMN產生行列號，從而得到1，2，……，n的值。=VLOOKUP($A13,$A$1:$F$10,COLUMN(B1),0)因為這裡是同一行產生序號，所以用COLUMN函數。4、按不同順序返回對應值。這回看來只能手動更改第3參數了，COLUMN完全派不上用場。NO！每當你覺得操作繁瑣時，就要停下來思考，也許Excel本身存在這個功能，只是自己一時想不到或者不知道而已。列號不管千變萬化，在數據源的位置始終不變，利用這個特點可以去搜索一下看看有什麼函數可以解決。在「搜索函數」文本框輸入：位置，單擊「轉到」按鈕，就會出現跟位置有關的函數，查看每個函數的說明，找到我們需要的，如MATCH函數，返回符合特定值特定順序的項在數組中的相應位置，單擊「確定」按鈕。在彈出的「函數參數對話框」中嘗試填寫相應的參數，每個參數的作用下面都有相關說明，填寫後會出現計算結果3，也就是訂單數在區域中是第3列。嘗試下更改第1參數為C12（俗稱），計算結果是2，也就是區域中第2列。經過嘗試，知道這個函數是我們要找的那個函數，單擊「取消」按鈕，返回工作表。在單元格再做最後一次驗證。到這一步已經十拿九穩了，將公式設置為：=VLOOKUP($A13,$A$1:$F$10,MATCH(B$12,$A$1:$F$1,0),0)5、根據番號逆序俗稱。幫助提到VLOOKUP函數只能按首列查找，不能逆向查找，既然如此，那就得想辦法將非首列的區域轉換成首列。怎麼轉換區域呢，這時IF函數就派上用場。一步步來了解IF函數的轉換。看看好友傳遞如何趣聊IF函數，吃貨的福音。IF函數其實只有一個條件來判斷是否符合條件，返回FALSE和TRUE兩種結果。當菜只有分甜的或鹹的2種口味時，甜味是紅燒肉，鹹味是醬油肉。盲人吃飯時，看不到是什麼菜。當別人問盲人：「你現在吃的什麼菜？ 是鹹的嗎？如果是鹹的，就是醬油肉，如果不是鹹的就是紅燒肉。」（給定判斷條件：鹹味）盲人剛好在吃紅燒肉，於是就咂吧著嘴說：「恩，好吃，不是鹹的！是紅燒肉」（根據提問的要求，不符合鹹的）假如要是盲人當時是在吃醬油肉呢,一定回答；「是的，鹹的，是醬油肉」（條件為真，是！TRUE）。盲人根據口感，結合提問者說的條件，就知道自己吃的是紅燒肉還是醬油肉了。把這段話用公式來寫：=IF(A1="鹹的",A2,B2)翻譯：是鹹的嗎？要是（TRUE），就是醬油肉，要是不是鹹的（FALSE），就是甜的紅燒肉。A1="鹹的"這個條件也可以直接換成TRUE或者FALSE。=IF(TRUE,A2,B2)因為滿足條件，所以返回A2的對應值醬油肉。=IF(FALSE,A2,B2)因為不滿足條件，所以返回B2的對應值紅燒肉。其實TRUE=1，FALSE=0，所以可以直接用1跟0表示。=IF(1,A2,B2)=IF(0,A2,B2)IF函數不止可以返回1個單元格的值，也可以返回多個單元格的值。=IF({1,0},A2,B2)=IF({0,1},A2,B2)選擇兩個單元格輸入，按Ctrl+Shift+Enter三鍵結束。條件為{1,0}，返回A2:B2的對應值順序不變；條件為{0,1}，返回A2:B2的對應值，順序對換。也就是說通過改變1跟0的位置，可以調換兩單元格的前後位置。看到這裡，知道IF函數通過改變1,0可以調換單元格的順序，如果要改變區域的順序也是可以實現的。用IF函數重新構造的新區域，是多單元格數組公式，記得按Ctrl+Shift+Enter三鍵結束，否則出錯。新區域：=IF({1,0},B2:B10,A2:A10)所以公式可以變成：=VLOOKUP(A13,新區域,2,0)兩個公式合併，大功告成。=VLOOKUP(A13,IF({1,0},$B$2:$B$10,$A$2:$A$10),2,0)6、根據俗稱跟訂單號兩個條件查詢完成情況。正常情況下VLOOKUP函數是不能多條件查詢，通過IF函數的學習，我們知道IF函數可以重新構造區域，這裡就再次用IF構成一個區域。新區域：=IF({1,0},A2:A9&C2:C9,E2:E9)所以公式可以變成：=VLOOKUP(A12&B12,新區域,2,0)兩個公式合併，大功告成，記得按Ctrl+Shift+Enter三鍵結束。=VLOOKUP(A12&B12,IF({1,0},$A$2:$A$9&$C$2:$C$9,$E$2:$E$9),2,0)7、根據俗稱的第一個字元查找番號。=VLOOKUP(D2&"*",A:B,2,0)星號（*）是通配符，代表所有字元，問號（?）代表一個字元。D2&"*"就是開頭包含D2的意思。8、根據區域判斷成績的等級。藉助輔助列的話，很容易查詢等級，只需將VLOOKUP函數的第四參數設置為1或者省略即可。=VLOOKUP(E2,A:C,3)如果不用輔助列，估計很多人看到這條公式就得哭了，得結合前面所有函數知識才能完成，有興趣的朋友可以自己去研究。=VLOOKUP(E2,IF({1,0},--LEFT(B$2:B$5,FIND("-",B$2:B$5)-1),C$2:C$5),2)前陣子無意間發現了IMREAL函數，所以不用輔助列的數組公式可以稍微簡單一點。=VLOOKUP(E2,IF({1,0},IMREAL(B$2:B$5&"i"),C$2:C$5),2) IMREAL函數是計算複數的實部係數的函數，作用就是提取區間的下限。通過這8個疑難，基本上的查詢問題都能夠解決。開心嗎？一下搞定8大疑難！會計視野系列微信群，已經有面向在職人員的地域類或行業及專業類交流群、面向學生的在校大學部生和在校碩士生群。歡迎加 shixiaoye11 微信好友后加群。