|
一說起查找函數(shù),，大部分人都會想到VLOOKUP函數(shù)。 作為一個有34年歷史的老牌函數(shù),，VLOOKUP也算聲名遠揚,，無愧 “查找之王”的美稱。 今天主要從VLOOKUP的4個參數(shù)來深度解讀,，同時介紹一下最新用法,，這里先賣個關子。 溫馨提示：本文巨巨巨巨長,，建議大家先收藏,、轉發(fā)，慢慢看,，能靜下心看完的同學,，我保證你100%學會這個函數(shù)。 四個參數(shù)分別是：查找值,，查找區(qū)域,，返回列，匹配類型,。 第一參數(shù)查找值：表里如一的堅守者,；靈活變通和與時俱進者！ 1.表里如一 表里必須如一,，來不得半點虛假,。
如下圖，我們要查找編號為714848的銷售金額,，Excel卻給我們返回了一個錯誤值,，這是咋回事呢？
取消A列和G列居中對齊,，G2單元格的數(shù)字靠左,，而A9單元格的數(shù)字靠右，原來G2和A9屬性不一樣,！
Vlookup函數(shù)有自己的規(guī)則,，查找的時候嚴格遵守【表里如一】的第一定律：數(shù)據(jù)屬性一致。
再往下查,，懊惱繼續(xù),，怎么又被Vlookup發(fā)了一個好人“NA”卡？
通過取消對齊,，排除了數(shù)據(jù)屬性不一致的原因,。 考慮是數(shù)據(jù)違反了第一參數(shù)【表里如一】的第二定律：字符數(shù)相等！ 表面看到的字符是一樣的,，但因為有空格或者不可見字符的存在,，兩者實際字符數(shù)可能不等，是不一樣的,。 用LEN函數(shù)檢查字符數(shù)： A2單元格的字符數(shù)=LEN(A2)=8,，G2單元格的字符數(shù)=LEN(G2)=7,。字符數(shù)不相等,，兩者肯定不相等啦，也就查不到了,。
TIPS： 幾個常見的Excel不可見字符,，水平制表符char(9)、換行符char(10),、空格符char(32),。 在單元格輸入公式=char(9)即可得到一個不可見的水平制表符，其雖然不可見,，但字符數(shù)為1,。 彩蛋：如何糾正Vlookup看著有卻查找錯誤的問題？ 對于數(shù)據(jù)屬性不一樣的：比如文本與數(shù)字的切換,，這里不再贅述,。 如果是不可見字符造成字符數(shù)不一樣的： 這里給大家一個萬金油公式=CLEAN(TRIM(SUBSTITUTE(要清洗的某單元格,CHAR(32),))) 可以快速清除空格和常見的不可見字符，清洗后再復制并選擇性粘貼覆蓋原來數(shù)據(jù)即可,。 2.靈活變通——支持通配符查找和支持多條件連接查找 表里如一的堅守并不意味著死板,、不知變通！ 相反,，第一參數(shù)非常靈活,。 如果查找值本身不完整，如是簡稱,，我們可以通過添加通配符“*”或者“?”來進行包含查找,。 如下圖，查找鞋,，即為查找包含鞋字的：
VLOOKUP的第一參數(shù)還支持用&連接多個單元格的內(nèi)容,，用于多條件查詢：
3.與時俱進——引用數(shù)據(jù)區(qū)域或數(shù)組進行查找 這是Vlookup最新用法！ 不需要下拉填充公式即可完成所有查找值的查詢,。 如下，直接在I2中輸入公式即可完成所有訂單查找,。
第二參數(shù)查找區(qū)域：圈地愛好者與固執(zhí)的向右查找者,！ 1.圈地愛好者 要查找先圈地，只在領地里進行查找,，其他地方恕不接待,。 比如下圖中的B到E列就是此次查找的領地（查找區(qū)域）。
這點與它的兄長Lookup迥然不同,，Lookup可以分別指定查找區(qū)域和返回區(qū)域,。 2. 固執(zhí)的向右查找者！ 這塊地從哪里開始圈起,？往哪個方向圈,？往哪個方向查？ VLOOKUP要求領地中左起的第一列必須是查找值所在列,，然后向右圈地,。 譬如：查找值為訂單編號，那么我們就要以訂單編號這一列為第一列,，向右進行圈地,，直到圈到要查詢的結果值——銷售金額這一列為止。然后在B列中查找訂單“762145”,，找到后水平往右走,，直到返回列。
一個小問題,，能向左進行圈地嗎,？ 根據(jù)訂單編號來查找銷售部門，我們可以選中B列再向左邊A列開始圈嗎,？ 注意：選擇的區(qū)域如B1:A15在公式輸入完畢后自動會更正為A1:B15,。 那能否向左查找呢？ 答案是不可以,！
TIPS： 如果必須從右往左查找,，可以請來IF這個熱心腸的函數(shù)將這兩列內(nèi)容顛倒一下順序，即用IF({1,0}把B列和A列組合在一起,，并把 B列放在A列前面,。
TIPS： 如果有向右或者向下復制公式的需求，我們還需要給這塊查找區(qū)域上個保險,。 通俗一點就是說,，不想你千辛萬苦圈起來的地到處亂跑,，最好給它修個柵欄。修柵欄,，就得花錢對不對,？ 所以我們用這個$符號，將它放在你需要固定的數(shù)據(jù)前面,，這樣,，拖拽公式的時候，查找區(qū)域就不會再變化了,。
TIPS： VLOOKUP的查找區(qū)域是只能圈一塊地,，還是可以圈多塊地呢？ 比如現(xiàn)在我們要找西紅柿的產(chǎn)量,、銷量,、利潤，而這些數(shù)據(jù)分別放在三個Excel表里,，又該如何寫公式,？ 借助INDIRECT函數(shù)可以實現(xiàn)多表查詢數(shù)據(jù),，如下圖所示：
第三參數(shù)返回列：笨拙的數(shù)數(shù)者 1.默認數(shù)數(shù) 返回第幾列呢,？VLOOKUP是邊走邊數(shù)數(shù)來確定返回列數(shù)的。從領地的第一列開始,，一步一列,，走到返回列有幾步，就寫幾列,。
尤其是同樣的條件需要查找多列返回值的時候,，每次都去數(shù)一次然后手動修改，特麻煩,。 2.找朋友相助自動給出返回數(shù) 在查找多列返回值時,，為避免數(shù)來數(shù)去以及改來改去出錯，那就只有求人相助了,。 VLOOKUP：各位大哥大姐,，小弟能力有限，實在是活不下去了,。俗話說在家靠父母,，出門靠朋友…… 眾函數(shù)覺得VLOOKUP不擺明星架子，還算真誠,，于是紛紛效犬馬之勞,。 （1）COLUMN，搞定有序變化的返回列 如圖所示,，要找出A,、B,、D產(chǎn)品在1、2,、3月的銷量,，如果單單是靠VLOOKUP的話，只能頻繁地去修改第三參數(shù),，于是COLUMN雪中來送碳,。 =VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE)
（2）MATCH——自動識別返回列 如果不是1月、2月,、3月這樣的有序排列,，而是1月、3月,、5月的序列,，還有MATCH函數(shù)絕渡逢舟。 =VLOOKUP($A14,$A$2:$G$10,MATCH(B$13,$A$1:$G$1,0),FALSE)
3.支持數(shù)組一次返回多個值 這也是VLOOKUP的最新用法,！ 將Vlookup的第三參數(shù)用花括號括起來,，2,、 3 、 4,，這三個數(shù)據(jù)分別對應著 “產(chǎn)品A” 在1 ,、2、3月份的銷售數(shù)量,。然后在外面套一個SUM函數(shù)就得到了A產(chǎn)品第一季度的銷量,。
數(shù)組公式，低版本（office365,、2019,、2021等以下）的，需按Ctrl+Shift+Enter三鍵結束,。 第四參數(shù)匹配類型：失誤的反人類設計 1.反人類設計 第4參數(shù)是個可選參數(shù),，用于設置匹配類型。匹配類型有近似匹配（TRUE或1）和精確匹配（FALSE或0）兩種。 默認為近似匹配,，可以省略不寫,，而精確匹配則須給出參數(shù)，這和我們?nèi)粘Ｖ饕樵冃枨蟆非缶_——截然不同,。 如下圖所示,，我們要查找編號為“786029”的銷售數(shù)量，保持默認不填寫第4參數(shù),，那對不起,，只能得到一個錯誤的答案。
這就意味著,，每次輸完前三個參數(shù)時,，你都需要謹慎地寫出第4參數(shù)（FALSE或0），或者你至少要在第三參數(shù)后加一個逗號,，才能精確查找,。 2.也并非一無是處——做區(qū)間查找很合適 默認近似匹配也并非一無是處，當我們做區(qū)間查找,，如根據(jù)數(shù)據(jù)查等級時,，就可以少寫一個參數(shù)，很省事,。 譬如查A列的銷售等級,，只用三個參數(shù)即可。
注：利用近似匹配做區(qū)間查找時,，查找區(qū)域首列必須是升序排列,。Vlookup近似匹配時,，其查找方法與Lookup函數(shù)一樣,，都采用二分法進行。需要了解的可以看《一文講透LOOKUP二分法原理》,。 VLOOKUP作為查找明星，很多Excel人都認他,、用他,。 今天我們從他的四個參數(shù)入手，去掉光環(huán),，看到他的本真,，有堅守，有笨拙,，有固執(zhí),，有失誤。 同時，Excel高版本中Vlookup有最新用法,，可以直接用數(shù)組做參數(shù),，不用下拉填充公式即可完成所有查找。 有了這些理解,，相信大家再使用VLOOKUP可以提前規(guī)避很多錯誤并提高效率,。 大家有什么疑問，歡迎評論區(qū)留言或者進群交流~
|
|
|
