等了64個(gè)夜晚,VLOOKUP坐字法合并單元格查找的秘密終于破了!
?
作者:老菜鳥來(lái)源:部落窩教育發(fā)布時(shí)間:2019-03-27 09:25:31點(diǎn)擊:5868
編按:
如何在合并的單元格中使用VLOOKUP進(jìn)行數(shù)據(jù)查找?在1月23日的教程《大膽合并吧!VLOOKUP坐字法專做單元格合并查找》中作者推薦使用VLOOKUP“坐”字法。很多伙伴對(duì)這個(gè)“坐”字法非常感興趣,想了解其中的原理??嗟?4個(gè)夜晚,今天“坐”字法背后的秘密終于浮出了水面。
1月23日發(fā)布的教程中VLOOKUP里出現(xiàn)了一個(gè)“坐”字,大家紛紛表示想了解這個(gè)“坐”字到底是何意,今天就為大家解釋這個(gè)公式的原理。
要說(shuō)這個(gè)“坐”的含義,得從VLOOKUP的第四參數(shù)說(shuō)起。
熟悉VLOOKUP的朋友都知道,這個(gè)函數(shù)一共有四個(gè)參數(shù),第四參數(shù)的作用就是控制匹配方式,填寫1或者TRUE表示近似匹配,通常也叫做模糊匹配:
填寫0或者FALSE表示精確匹配:
對(duì)于精確匹配,大家都明白,但對(duì)于近似匹配,大家就難理解了。我們通過(guò)一個(gè)例子來(lái)看看這兩種匹配的區(qū)別:
從上圖可以看到,在我們的數(shù)據(jù)區(qū)域A列中并沒(méi)有“人事部”。查找“人事部”,精確查找得到了錯(cuò)誤值,而近似查找找到了類似的部門——“人力資源中心”。
難道近似查找這么智能?其實(shí)這里找到“人力資源中心”只是一個(gè)巧合。在這個(gè)數(shù)據(jù)源中,A列是按照升序排列的,現(xiàn)在我們把排序打亂,再看看查找結(jié)果如何:
結(jié)果令人大跌眼鏡,全亂了,這是什么鬼?
請(qǐng)注意,這并不是公式的錯(cuò)誤,之所以得到這樣的結(jié)果,根本原因是精確查找和近似查找的查找原理不同。精確查找使用的是遍歷法,而近似查找使用的是二分法。
以下簡(jiǎn)單說(shuō)明這兩種查找的原理。
1.遍歷法
這種方法是將需要查找的內(nèi)容,在指定的查找區(qū)域中,逐一進(jìn)行比較,當(dāng)找到完全一致的內(nèi)容后,即可得到對(duì)應(yīng)的結(jié)果。
例如查找“采購(gòu)部”,查找區(qū)域是A:A,遍歷法會(huì)從A1單元格開始找,當(dāng)找到A2時(shí)發(fā)現(xiàn)目標(biāo),返回結(jié)果,就完成了第一個(gè)數(shù)據(jù)的查找;接著找“人事部”,還是從A1開始找,找遍A列所有的單元格,也沒(méi)有發(fā)現(xiàn)目標(biāo),只能得到#N/A,說(shuō)明沒(méi)有找到所需的結(jié)果。
由此也可以體會(huì)到遍歷法的特點(diǎn):查找準(zhǔn)確性高,但需要一個(gè)一個(gè)對(duì)比數(shù)據(jù),當(dāng)數(shù)據(jù)多的時(shí)候,查找速度慢是個(gè)很大的問(wèn)題。
下面再來(lái)看看二分法又是如何進(jìn)行查找的。
2.二分法
與遍歷法不同,二分法不逐一比較,并且對(duì)數(shù)據(jù)源做了一種假設(shè),這個(gè)假設(shè)就是默認(rèn)數(shù)據(jù)升序排列。
我們知道,升序排列的數(shù)據(jù)中,小的在上,大的在下。二分法默認(rèn)數(shù)據(jù)升序排列,本質(zhì)就是不管數(shù)據(jù)實(shí)際是如何排序的,都認(rèn)為上邊小,下邊大。
二分法對(duì)數(shù)據(jù)進(jìn)行比較時(shí),不像遍歷法那樣從上往下逐個(gè)做比較,而是從數(shù)據(jù)源最中間的位置開始進(jìn)行比較。如果要找的值比中間位置的值大,則向下找,反之則向上找,以此類推。二分法查找的結(jié)果要么是小于等于查找內(nèi)容的值,要么就是錯(cuò)誤。
舉個(gè)例子來(lái)說(shuō):
在這個(gè)例子中,數(shù)據(jù)源有五個(gè)數(shù),中間位置的數(shù)據(jù)是9。當(dāng)要找的值為2時(shí),首先用2和最中間的9比較,2<9,所以繼續(xù)向上找。以此類推,找不到小于或等于2的數(shù)據(jù)時(shí)就會(huì)得到#N/A;
接著再查找第二個(gè)數(shù)字9,9和中間的數(shù)據(jù)比較,正好相同,所以直接得到正確的結(jié)果;
最后查找數(shù)字10和99,結(jié)果都是6。由于二分法默認(rèn)數(shù)據(jù)是升序排列,當(dāng)要找的數(shù)據(jù)比數(shù)據(jù)源中所有的值都大時(shí),二分法就會(huì)一直向下找,找不到的時(shí)候就會(huì)返回最后一個(gè)數(shù)據(jù),所以這里只要查找值大于9,得到的結(jié)果都是數(shù)據(jù)源中的最后一個(gè)數(shù)據(jù)6。
關(guān)于二分法的查找原理,官方的資料并沒(méi)有詳細(xì)介紹,只是提到幾個(gè)要點(diǎn):
(1)默認(rèn)數(shù)據(jù)源是升序排列;
(2)當(dāng)查找值比查找區(qū)域內(nèi)的任何值都大時(shí),則返回?cái)?shù)據(jù)區(qū)域內(nèi)最下面的數(shù)據(jù);
(3)如果查找值小于查找區(qū)域內(nèi)的最小值時(shí),則會(huì)得到錯(cuò)誤值#N/A。
通過(guò)這個(gè)例子我們大致可以理解二分法的特點(diǎn),那就是查找速度快,一次就能排除一半數(shù)據(jù);但如果數(shù)據(jù)源不是升序排列的,結(jié)果可能并不是我們所需要的。
漢字之間的大小關(guān)系通常是按照拼音的順序排列的,所以用zuo這個(gè)拼音的字,基本都是比較靠后的。在實(shí)際使用中,最小的字是“吖”(ā,也讀y?。?,最大字是“咗”,“々”不是漢字但比所有漢字都大。
最后再來(lái)解釋一下VLOOKUP第四參數(shù)省略時(shí)的兩個(gè)小細(xì)節(jié):
(1)只省略第四參數(shù)值,不省略逗號(hào)時(shí),表示精確查找;
(2)如果將逗號(hào)和第四參數(shù)值全部省略,表示模糊查找。
在這個(gè)公式中,外層的VLOOKUP省略第四參數(shù)而保留了逗號(hào),表示精確查找;內(nèi)層的VLOOKUP同時(shí)省略逗號(hào)和參數(shù),表示模糊查找。
明白了以上原理,用VLOOKUP “坐”字法解決合并單元格的查詢就不神秘了。譬如,A2:A5合并后只保留了A2單元格值“財(cái)務(wù)中心”。查上圖劉丙奇的獎(jiǎng)金基數(shù),因?yàn)?span>A1:A3只有兩個(gè)值“部門”“財(cái)務(wù)中心”并都比“坐”字小,所以公式VLOOKUP("坐",$A$1:A3,1)的結(jié)果就是A1:A3 中最下方值“財(cái)務(wù)中心”,公式VLOOKUP(VLOOKUP("坐",$A$1:A3,1),$G$1:$H$7,2,)就等于VLOOKUP("財(cái)務(wù)中心",$G$1:$H$7,2,)。
今天的教程就到這了,還有哪些知識(shí)是你想了解的可以留言,咱們下期再見。
本文配套的練習(xí)課件請(qǐng)加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
如何在合并單元格中使用VLOOKUP查找《大膽合并吧!VLOOKUP坐字法專做單元格合并查找》
VLOOKUP常見問(wèn)題《破除日期迷惑,多條件查找就用Vlookup!》
VLOOKUP找不到數(shù)據(jù)的原因《你一定要了解:公式?jīng)]錯(cuò)Vlookup仍找不到數(shù)據(jù)的3大原因》
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)