|
涉世未深：函數(shù)的功能和基本參數(shù)介紹，適合初學(xué)者。 中流砥柱：注意事項(xiàng)及要點(diǎn)，大量貼合實(shí)際應(yīng)用的案例，適合大部分用戶。 立地成佛：多個(gè)函數(shù)組合，或結(jié)合數(shù)組的復(fù)雜案例。 涉世未深 簡(jiǎn)介：VLOOKUP在EXCEL誕生之初的1985年就已經(jīng)存在，它是第一個(gè)查詢類(lèi)的函數(shù)。VLOOKUP不但入選了官方網(wǎng)站上公示的十大最受歡迎函數(shù)，更是應(yīng)用最廣泛的三個(gè)函數(shù)之一，另外兩個(gè)是SUM和AVERAGE. 功能：在指定區(qū)域中查找指定值，返回與其對(duì)應(yīng)的另外一個(gè)值。 示例：在學(xué)生成績(jī)中查詢小王的語(yǔ)文成績(jī)： =VLOOKUP('小王',A:D,3,FALSE)  VLOOKUP示例 語(yǔ)法結(jié)構(gòu)：VLOOKUP(要查找的值，查找區(qū)域，返回值所在的列數(shù)，模糊查詢或精確查詢) 四個(gè)參數(shù)： 參數(shù)一，要查找的值，必選參數(shù)?？梢灾苯虞斎?，也可以引用單元格。如示例中要查找“小王”。 參數(shù)二，查找區(qū)域，必選參數(shù)。被查找的值和要返回的值都應(yīng)該被包含在該區(qū)域內(nèi)。如示例中的A:D列。 Excel筆記：被查找的值必須位于查詢區(qū)域的第一列。 如示例中“小王”在查詢區(qū)域的第一列（A列）中，而下圖的錯(cuò)誤示范中“小王“所在的B列被置于查詢區(qū)域A:E的第二列，VLOOKUP返回了錯(cuò)誤值”#N/A”.  VLOOKUP錯(cuò)誤示例 參數(shù)三，返回值所在的列數(shù)，必選參數(shù)。如示例中要返回的語(yǔ)文成績(jī)位于A:D中的第3列。 參數(shù)四，TRUE或FALSE，可選參數(shù)，未輸入則默認(rèn)為T(mén)RUE.TURE表示模糊查詢，F(xiàn)ALSE表示精確查詢。TRUE和FALSE也可以分別用1和0代替。 大多數(shù)使用到VLOOKUP的場(chǎng)景中需要向下或向右填充公式，習(xí)慣性鎖定查詢區(qū)域可確保查詢區(qū)域準(zhǔn)確。 Excel筆記：建議全鎖定（絕對(duì)引用）查詢區(qū)域（即第二參數(shù)）。  絕對(duì)引用 中流砥柱
通配符* 代表任意一串字符 通配符？代表任意單個(gè)字符 Excel筆記：VLOOKUP支持通配符。 案例1，查詢客戶“王牌汽車(chē)“的聯(lián)系人。 客戶信息表中完整的名稱(chēng)是”北京王牌汽車(chē)制造廠“，此時(shí)需要在”王牌汽車(chē)“的前后分別加上一個(gè)通配符”*“，用于查詢包含關(guān)鍵字”王牌汽車(chē)“的項(xiàng)。 =VLOOKUP('*'&D3&'*',A:B,2,0)  VLOOKUP通配符查找
第二參數(shù)查詢區(qū)域鎖定的情況下，第三參數(shù)設(shè)置為不同的值將帶回不同的查找值，這一特性極大擴(kuò)展了VLOOKUP的應(yīng)用場(chǎng)景和靈活性。 Excel筆記：VLOOKUP第三參數(shù)常用動(dòng)態(tài)參數(shù)：COLUMN函數(shù)，MATCH函數(shù)，數(shù)組。 案例2，查詢學(xué)生的考試成績(jī)。 G3=VLOOKUP(F3,A:D,2,0) H3 =VLOOKUP(F3,A:D,3,0) I3 =VLOOKUP(F3,A:D,4,0)  VLOOKUP第三參數(shù) 如圖所示，根據(jù)姓名查詢?nèi)齻€(gè)科目的成績(jī)，可以分三次輸入公式，但顯然效率是低下的。仔細(xì)觀察三個(gè)公式的參數(shù)會(huì)發(fā)現(xiàn)：除了第三參數(shù)外，其他參數(shù)都完全一樣，而且第三參數(shù)是有規(guī)律的遞增數(shù)列{2,3,4}. 此時(shí)對(duì)第一個(gè)公式做兩點(diǎn)修改： 1，第一，二參數(shù)全鎖定； 2，第三參數(shù)修改為COLUMN(B:B) 得到一個(gè)新的公式： G3=VLOOKUP($F$3,$A:$D,COLUMN(B:B),0) 向右拖動(dòng)將公式直接填充到后面兩個(gè)單元格， COLUMN函數(shù)產(chǎn)生{2,3,4}作為第三參數(shù)。  COLUMN動(dòng)態(tài)參數(shù) 上述案例中查詢科目的順序與原數(shù)據(jù)的順序一致，COLUMN函數(shù)產(chǎn)生的{2,3,4}作為第三函數(shù)剛好匹配，如果查詢科目的順序與原數(shù)據(jù)不一致呢？ 案例3，錯(cuò)位查詢學(xué)生成績(jī)。 3個(gè)VLOOKUP的第三參數(shù)分別是{4,2,3},COLUMN無(wú)法產(chǎn)生不規(guī)則的數(shù)組，需要用到函數(shù)Match.  VLOOKUP數(shù)組 G3=VLOOKUP($F$3,$A:$D,MATCH(G2,$A$1:$D$1,0),0) G3單元格輸入以上公式后向右拖動(dòng)填充，Match函數(shù)將產(chǎn)生{4,2,3}作為VLOOKUP的第3參數(shù)。 Match返回G2:I2各個(gè)科目在A1:D1中的相對(duì)位置，此時(shí)即便調(diào)整G2:I2各個(gè)科目的順序，VLOOKUP也能返回正確的數(shù)據(jù)性。  動(dòng)態(tài)參數(shù)MATCH 并不是所有場(chǎng)景都需要用函數(shù)來(lái)實(shí)現(xiàn)動(dòng)態(tài)參數(shù)，數(shù)據(jù)結(jié)構(gòu)固定的情況下用數(shù)組作為第三參數(shù)也是不錯(cuò)的選擇。 案例4，隔列求和。 VLOOKUP需要返回黃色四列的數(shù)據(jù)，第三參數(shù)使用數(shù)組{3,5,7,9}一次性完成查詢后SUM求和即可。 =SUM(VLOOKUP(A12,A3:I9,{3,5,7,9},0))  VLOOKUP隔列求和
第四參數(shù)省略，或設(shè)置為T(mén)RUE，或數(shù)字1時(shí)表示模糊匹配。 Excel筆記：VLOOKUP模糊匹配狀態(tài)下，找不到查找值時(shí)，返回小于查找值的最大值 案例5，根據(jù)學(xué)生成績(jī)?cè)u(píng)定等級(jí)。 模糊匹配模式下，在F列找不到小李的成績(jī)（100），則返回小于100的最大值80對(duì)應(yīng)的“良好”。 =VLOOKUP(B2,$E$2:$F$4,2,1)  VLOOKUP模糊匹配模式 VLOOKUP模糊匹配經(jīng)常應(yīng)用于按銷(xiāo)售額區(qū)間計(jì)算提成，按重量區(qū)間計(jì)算快遞費(fèi)，按成績(jī)區(qū)間評(píng)定等級(jí)等場(chǎng)景。無(wú)一例外，這些情況都需要整理好區(qū)間下限與等級(jí)的對(duì)應(yīng)關(guān)系，且按升序排列。 Excel筆記：VLOOKUP模糊匹配時(shí)查詢區(qū)域按升序排列，否則將得到錯(cuò)誤結(jié)果。 案例6，錯(cuò)誤示范，根據(jù)學(xué)生成績(jī)?cè)u(píng)定等級(jí)。 =VLOOKUP(B2,$E$2:$F$4,2,1) 公式正確，但E列查詢區(qū)域未按升序排列，黃色部分全部是錯(cuò)誤結(jié)果。  模糊匹配錯(cuò)誤示范
VLOOKUP中的LOOKUP是查找的意思，V則是vertical，垂直的意思。直觀的解釋了它的功能：從上而下的垂直查找。找到第一個(gè)查找值時(shí)就會(huì)停止，不管后續(xù)還有沒(méi)有同樣的查找值。 Excel筆記：當(dāng)查詢區(qū)域存在多個(gè)查找值時(shí)，VLOOKUP返回第一個(gè)（最上面一個(gè)）值。 這一特性在某些場(chǎng)景下也會(huì)派上用場(chǎng)。 案例7，查詢“桃子”第一次批發(fā)價(jià)格。 =VLOOKUP('桃子',B:C,2,0) VLOOKUP返回第一個(gè)值
Excel筆記：VLOOKUP要求查詢數(shù)據(jù)和被查找值完全一致，包括格式。 案例8，根據(jù)代號(hào)查詢姓名。 =VLOOKUP(D3,A:B,2,0) 公式結(jié)果為錯(cuò)誤值”#N/A”. 原因：查找值”9529”是數(shù)字格式，查詢區(qū)域是文本格式。 VLOOKUP錯(cuò)誤示范 三個(gè)解決方案：1. 將查找值設(shè)置為文本格式；2. 將查詢區(qū)域設(shè)置為數(shù)字格式；3. 公式中連接一個(gè)空值。 大部分場(chǎng)景下用戶更希望保留原始的數(shù)據(jù)格式，第3種方案： =VLOOKUP(D3&'',A:B,2,0) D3&””的核心邏輯也是將D3由數(shù)字格式轉(zhuǎn)換為文本格式，只是在公式中完成。 VLOOKUP數(shù)據(jù)格式案例 立地成佛 VLOOKUP第2參數(shù)要求查詢數(shù)據(jù)與返回?cái)?shù)據(jù)處于連續(xù)的數(shù)據(jù)區(qū)域，且只能從左往右排列，在數(shù)據(jù)格式不滿足的情況下，常規(guī)做法是通過(guò)IF函數(shù)或CHOOSE函數(shù)構(gòu)建一個(gè)數(shù)據(jù)區(qū)域，在高版本的Excel中可以通過(guò)溢出功能直觀地看出人為構(gòu)建的數(shù)據(jù)區(qū)域，沒(méi)有溢出功能的版本比較考驗(yàn)用戶的想象力。 Excel筆記：數(shù)據(jù)結(jié)構(gòu)不支持VLOOKUP時(shí)，用IF或CHOOSE構(gòu)建一個(gè)滿足要求的數(shù)據(jù)區(qū)域作為第二參數(shù)。
案例9，要根據(jù)學(xué)號(hào)查詢姓名。 姓名在學(xué)號(hào)的左邊，不符合VLOOKUP第二參數(shù)從左往右的要求，此時(shí)用IF函數(shù)構(gòu)建一個(gè)滿足要求的數(shù)據(jù)區(qū)域。 G4= VLOOKUP(F4,IF({1,0},B2:B10,A2:A10),2,0) VLOOKUP反向查詢 將第2參數(shù)的IF函數(shù)置于F2單元格,通過(guò)溢出功能可以直觀地看出其構(gòu)建的數(shù)據(jù)區(qū)域中姓名處于學(xué)號(hào)的右側(cè)，滿足VLOOKUP第二參數(shù)的要求。 IF構(gòu)建數(shù)據(jù)區(qū)域
案例10，同名同姓查詢。 同姓同名示例 同名同姓的情況，如果不加上性別用于區(qū)分是無(wú)法對(duì)應(yīng)“小張”的成績(jī)的。其次，科目的順序也發(fā)生了變化。 此時(shí)仍然可以采用構(gòu)建數(shù)據(jù)區(qū)域的方式來(lái)處理，IF函數(shù)只能構(gòu)建2列數(shù)據(jù)的區(qū)域，已經(jīng)無(wú)法滿足需求，需要用到CHOOSE函數(shù)。 F2=CHOOSE({1,2,3},A2:A10&B2:B10,D2:D10,C2:C10) 構(gòu)建的數(shù)據(jù)區(qū)域中將”姓名”連接”性別”作為查詢區(qū)域置于首列，后續(xù)依次是”語(yǔ)文”成績(jī)和”數(shù)學(xué)”成績(jī)。 CHOOSE構(gòu)建數(shù)據(jù)區(qū)域 將CHOOSE函數(shù)整體作為VLOOKUP的第2參數(shù)，即表示在黃色區(qū)域內(nèi)查詢。如允許保留輔助列，也可以直接框選黃色區(qū)域作為第2參數(shù)。 同時(shí)需要注意第1參數(shù)需要用“姓名“連接”性別“作為查詢條件。 L4=VLOOKUP($J$4&$K$4,CHOOSE({1,2,3},A2:$A$10&$B$2:$B$10,$D$2:$D$10,$C$2:$C$10),COLUMN(B:B),0) VLOOKUP多條件查詢
無(wú)法構(gòu)建數(shù)據(jù)區(qū)域的場(chǎng)景下，輔助列成為了最后的救命稻草。 案例11，查詢市場(chǎng)部的員工姓名。 數(shù)據(jù)的前端插入輔助列，A2輸入以下公式并向下填充。 =C2&COUNTIF($C$2:C2,C2) F2輸入以下公式并下拉填充。 =IFERROR(VLOOKUP($C$2&ROW(1:1),$A$2:$B$10,2,0),'') VLOOKUP一對(duì)多查詢
地獄級(jí)難度，實(shí)用性暫且不論，先感受一下來(lái)自地獄的氣息吧！ 案例12，提取電話號(hào)碼。 =VLOOKUP(TRUE,IF({1,0},ISNUMBER(--MID(A2,ROW($1:$100),11)),MID(A2,ROW($1:$100),11)),2,0) VLOOKUP提取電話號(hào)碼 該案例中的MID將數(shù)據(jù)拆分為100組數(shù)據(jù)，ISNUMBER判斷其中的數(shù)字項(xiàng)，IF強(qiáng)行構(gòu)建數(shù)據(jù)區(qū)域。要進(jìn)一步理解，同樣可以將IF函數(shù)單獨(dú)提取出來(lái)直觀顯示構(gòu)建的區(qū)域。 案例13，按指定數(shù)量產(chǎn)生隊(duì)列。 輸入公式： =VLOOKUP(ROW(A1),IF({1,0},IFERROR(SUMIF(OFFSET($B$2,,,ROW($1:$5)-1),'<>')+1,0),$A$2:$A$6&''),2,1)&'' 版本較低的Excel中，輸入公式后需按三鍵Ctrl+Shift+Enter. VLOOKUP產(chǎn)生隊(duì)列 該案例涉及了兩個(gè)核心要點(diǎn)：IF構(gòu)建數(shù)據(jù)區(qū)域作為第二參數(shù)；第四參數(shù)1表示模糊匹配。 難點(diǎn)在于如何用IF構(gòu)建一個(gè)合理的數(shù)據(jù)區(qū)域。將IF函數(shù)單獨(dú)提列出來(lái)（如綠色區(qū)域）可以直觀地看到數(shù)據(jù)結(jié)構(gòu)，在此區(qū)域運(yùn)用VLOOKUP模糊匹配模式查找即可。 VLOOKUP及輔助列 開(kāi)發(fā)者恐怕也沒(méi)有想到在后來(lái)的30多年里VLOOKUP會(huì)成為制霸一方的查詢函數(shù)，種種別出心裁的用法并非筆者首創(chuàng)，而是大量用戶不斷總結(jié)積累而來(lái)，算是打工人只會(huì)的結(jié)晶吧。 |
|
|
