|
今天和大家分享的是三個(gè)常用的套路化函數(shù)公式,，都是用于模糊查詢(xún)的。 什么是模糊查詢(xún),？我們今天說(shuō)的模糊查詢(xún)是指根據(jù)指定字符串,，在另一個(gè)數(shù)據(jù)表里查找與之相似的數(shù)據(jù)信息。 針對(duì)常見(jiàn)的情況,，我們今天舉三個(gè)小例子,。 iiiiiiiiii 先來(lái)看第一個(gè)例子，根據(jù)簡(jiǎn)稱(chēng)查找全稱(chēng),。 如上圖所示,，A:B列是某班人員某科的高考成績(jī)（照例嚴(yán)肅聲明,，人名和成績(jī)都是虛擬的，如有雷同,，理所當(dāng)然都是巧合哈）,。D列是部分人員的簡(jiǎn)稱(chēng)，現(xiàn)在,，需要根據(jù)A：B列的信息,，查詢(xún)D列人員的考試成績(jī)。 E2公式： =VLOOKUP('*'&D2&'*',A:B,2,0) 公式解析： VLOOKUP函數(shù)支持使用通配符查詢(xún),，'*'&D2&'*',，星號(hào)是通配符，可以代替零到多個(gè)字符,。比如查找”*星光*”,，A2的”看見(jiàn)星光”符合條件，于是取得其成績(jī)92分,。（咱也有一科成績(jī)及格了,，先對(duì)隨機(jī)函數(shù)#￥%&，然后感嘆一聲真TM不容易啊,，嚇的我都不敢買(mǎi)彩票了） 由于此處是查詢(xún)成績(jī),，且人名不存在重復(fù)的問(wèn)題，所以也可以使用支持通配符查詢(xún)的SUMIF函數(shù)： =SUMIF(A:A,'*'&D2&'*',B:B) iiiiiiiiii 既然有根據(jù)簡(jiǎn)稱(chēng)查全稱(chēng)的事情,，自然也就有根據(jù)全稱(chēng)找簡(jiǎn)稱(chēng)的情況,，這就是我們要說(shuō)的第二個(gè)例子。 如上圖所示,，A:B列依然是某班人員某科的高考成績(jī)（照例嚴(yán)肅……你懂得）,。D列是部分人員的全稱(chēng)，現(xiàn)在,，需要根據(jù)A：B列的信息,，查詢(xún)D列人員的考試成績(jī)。 E2公式： =LOOKUP(,-FIND(A$2:A$10,D2),B$2:B$10) 公式解析： ?、貺OOKUP的第一參數(shù),，即查找值為0，做了省略,。 ?、?FIND(A$2:A$10,D2)，F(xiàn)IND函數(shù)依次查詢(xún)A2:A10的值是否在D2單元格中存在,，如果存在,，返回位置序號(hào)，不存在則返回錯(cuò)誤值#VALUE!,，（FIND函數(shù)的結(jié)果只有兩種,，正數(shù)和錯(cuò)誤值）,，之后做減法運(yùn)算，得到一個(gè)由負(fù)數(shù)和錯(cuò)誤值構(gòu)成的內(nèi)存數(shù)組： {#VALUE!;#VALUE!;-1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} ?、跮OOKUP忽略錯(cuò)誤值,，它的查找值為0，比查找范圍內(nèi)的任意值均大,，因而返回最后的數(shù)值所對(duì)應(yīng)的數(shù)據(jù),。 以D2單元格的空空女神為例，返回-1所對(duì)應(yīng)的B4單元格的值125,。 iiiiiiiiii 說(shuō)到這里,，細(xì)心的同學(xué)也許會(huì)發(fā)現(xiàn)，我們舉的兩個(gè)例子,，不管是簡(jiǎn)稱(chēng)查全稱(chēng),，還是全稱(chēng)找簡(jiǎn)稱(chēng)，都有一個(gè)最重要的規(guī)律,，也就是每個(gè)簡(jiǎn)稱(chēng)都是全稱(chēng)完整的一部分,，或者頭部，或者尾部,，或者中間,。比如，大花是【大花美女】的頭部,，星光是【看見(jiàn)星光】的尾部,。這么說(shuō)，似乎有點(diǎn)奇怪,，反正就是這么個(gè)意思,。 但假設(shè)有這樣一種情況，比如看見(jiàn)星光的簡(jiǎn)稱(chēng)是看星光,，又如何通過(guò)簡(jiǎn)稱(chēng)找到全稱(chēng)呢？ 之前的公式肯定是不成的,。 打個(gè)響指,，這就是我們要說(shuō)的第三種情況。 如上圖所示,，A:B列依然是某班人員某科的高考成績(jī)（照例……懂得）,。D列是部分人員的簡(jiǎn)稱(chēng)，現(xiàn)在,，需要根據(jù)A：B列的信息,，查詢(xún)D列人員的考試成績(jī)。 E2公式： =INDEX(B:B,MATCH(,MMULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26)),)) 公式解析: ?、?ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)) MID函數(shù)從D2單元格的第1個(gè)位置至第26個(gè)位置分別截取1個(gè)字符,；FIND函數(shù)判斷MID函數(shù)的結(jié)果在A1:A10單元格中是否存在,，如果存在，返回位置序號(hào),，否則返回錯(cuò)誤值,，最后通過(guò)ISERR函數(shù)搭配減法運(yùn)算，將FIND函數(shù)的結(jié)果轉(zhuǎn)化為-1和0,。 ?、贛MULT(-ISERR(FIND(MID(D2,COLUMN(A:Z),1),A$1:A$10)),ROW(1:26)) MMULT函數(shù)對(duì)矩陣數(shù)據(jù)進(jìn)行計(jì)算，當(dāng)D2單元格字符串的每一個(gè)字符都在A$1:A$10單元格中存在時(shí),，MMULT函數(shù)的結(jié)果為0,。 ③最后通過(guò)MATCH函數(shù),，取得MMULT函數(shù)結(jié)果首次為0的位置,，進(jìn)而通過(guò)INDEX函數(shù)進(jìn)行取值。 這個(gè)套路化公式的思路是判斷所查詢(xún)的字符串中每一個(gè)字符是否都在查找范圍內(nèi)存在,，換句話說(shuō),，就是玩一個(gè)文字歸屬計(jì)數(shù)游戲。那么,，思考題來(lái)了,，既然有這種情況的簡(jiǎn)稱(chēng)找全稱(chēng)，自然也有這種情況的全稱(chēng)找簡(jiǎn)稱(chēng),，如果碰到下面這種情況,，如何書(shū)寫(xiě)公式呢？ iiiiiiiiii 這就是今天給大家分享的有關(guān)模糊查詢(xún)的三個(gè)例子,，最后說(shuō)一句,，函數(shù)公式從來(lái)不是萬(wàn)能的，VBA代碼亦如是,，那些把函數(shù)公式講的天花亂墜,，好像可以上天入地拯救世界似的，純屬胡扯,，矮油,，這么能，雜不去飛天呢,？關(guān)于簡(jiǎn)稱(chēng)和全稱(chēng)的查詢(xún)和統(tǒng)計(jì),，最理想的情況自然還是制作一張匹配表，規(guī)范數(shù)據(jù)源,，從源頭上解決問(wèn)題,，而不是依靠技巧去湊數(shù)哈。 比如碰到下面這樣的情況,，若是沒(méi)有個(gè)匹配表,，撞破南墻那都沒(méi)轍啊,。 瀟瀟 簡(jiǎn)稱(chēng)【才人】。 你說(shuō),，如果沒(méi)有個(gè)匹配表,，誰(shuí)知道才人是瀟瀟的簡(jiǎn)稱(chēng)啊,？是不是,？ 拱手作別，下期再見(jiàn),。
|
