|
第一節(jié)：VLOOKUP按指定次數(shù)重復(fù)數(shù)據(jù) 如下圖，有這樣一道題,，要求按照B列的指定次數(shù),，重復(fù)C列的班級名稱，結(jié)果放入E列,。 
看到這道題,，好學(xué)的表親們，大概會立刻想起祝老師的某個基礎(chǔ)操作教程動畫，函數(shù)了得的親們,，會立刻想起某個多維數(shù)組套路,。 但咱們這里只想VLOOKUP。 如果用VLOOKUP,，這題怎么做,？ 很簡單。 只需要兩步,。 第一步,，A2輸入公式：=A1+B2，向下填充 第二步,，E2輸入公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&"",，向下拖動。 然后……結(jié)果…… 如下圖：
我們結(jié)合兩個公式,，解釋下其中過程,。 第一個公式：A1+B2，是計算相關(guān)次數(shù)的累計值,，比較好理解,。 第二個公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，看起來是常用的VLOOKUP套路,，但其實(shí)有兩個很有意思的地方,。 其一，VLOOKUP的查找值——ROW(A1),。在公式的下拉過程中,，通過查找1，2,，3,，4，5,，（電腦配音,，12345，上~山~打~老~虎）……來返回結(jié)果,。 其二,，屏蔽VLOOKUP錯誤值的方式。如果VLOOKUP查找不到相關(guān)數(shù)值,，比如此例中的1和2,，通常會返回錯誤值#N/A，而我們通過IFERROR,，使它返回公式所在單元格的下一個單元格的值,。 比如,，我們在E2輸入公式，VLOOKUP函數(shù)的錯誤值則返回E3,，公式向下拖動,，E3的錯誤值返回E4……如此類推，直至VLOOKUP函數(shù)返回正確值——則之前通過IFERROR函數(shù)判斷為錯誤值的單元格,，自然統(tǒng)一更新為相應(yīng)的正確值（……腦海里播放多米諾骨牌從依次跌倒到依次站起的畫面）,。 然后再進(jìn)行新一輪循環(huán)判斷、數(shù)據(jù)更正,。 最后的&””,，是函數(shù)里常用的屏蔽零值的技法，以便在VLOOKUP公式下拉過界時,，返回的零值顯示為空白,。 以上兩個公式,，除了VLOOKUP(ROW(A1)……)的技巧外,，還利用了函數(shù)的另外一個技巧，我們姑且稱之為上下其手,。 何謂上下其手,？簡而言之，便是拿公式所在單元格的上下單元格結(jié)果為己用,。 第一個公式,，=A1+B2,是上手，拿公式所在單元格的上一個單元格的值為己用,。很多人比較熟悉常用,，已經(jīng)很了解了。 第二個公式,，=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&"",，是下手，拿公式所在單元格的下一個單元格的值為己用,。大家用的可能就比較少了,。因?yàn)樯儆茫圆棚@得比較有意思,。大家有閑時,，不妨多想下，興許別有收獲哈,。 ==========我是似水溫柔的分割線========== 通過輔助列的方式,，我們實(shí)現(xiàn)了按指定次數(shù)重復(fù)數(shù)據(jù)。 下面咱們要做的,，便是丟掉輔助列,，直接用一個公式得出結(jié)果。 即，我們需要把A列累計次數(shù)求和的數(shù)據(jù),，放入VLOOKUP公式的查找范圍中,，以便直接得出所需要的結(jié)果。 我們可以使用這樣的公式： SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>") 這是一個累計求和的多維數(shù)組套路,，類似的套路還有MMULT,、INDIRECT、SUBTOTAL等,。 這個公式,，是通過OFFSET函數(shù)，制作多維求和統(tǒng)計范圍,，比如B2:B2,B2:B3,B2:B4…… 最后使用SUMIF進(jìn)行求和,。 我們把這一段放入VLOOKUP函數(shù)中： E2=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>"),$C$2:$C$5),2,0),E3)&"" 如此，這個公式便正式寫完了,。 當(dāng)然,，如果用LOOKUP，公式可以簡潔： =LOOKUP(ROW(A1),SUMIF(OFFSET($B$1,,,ROW($1:$5),),"<>")+1,$C$2:$C$6)&"" 真是暈菜了,，這一節(jié),，我發(fā)了N次，一直提示我有不良信息,，我開始以為是上下其手,，但最后發(fā)現(xiàn)…… 是上山打~老~虎。,。 好吧,，愛~老~虎油，不讓說,。 老~虎也不行,。第二節(jié)：VLOOKUP查詢符合條件的多個結(jié)果。 通過第一節(jié)的內(nèi)容,，我們初步認(rèn)識了VLOOKUP(ROW(A1),……)的技巧,。 這一節(jié)，我們需要利用這個技巧,，回答開篇所提到的第一個問題,。 VLOOKUP能否查詢符合條件的多個數(shù)值？就像經(jīng)典數(shù)組套路INDEX+SMALL+IF那樣,？ 
如上圖,，我們需要提取C列符合F1班級的姓名，放入E4:E15,。 通常我們會使用INDEX+SMALL+IF的數(shù)組套路： E4=INDEX(C:C,SMALL(IF($B$1:$B$15=F$1,ROW($1:$15),4^8),ROW(A1)))&"" 如果使用VLOOKUP,，我們應(yīng)該怎么做,？ 其實(shí)也簡單。 我們還是如第一節(jié)那般,，先采用輔助列的方式,。 A2=COUNTIF(B$2:B2,F$1) 向下填充。 E4=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"") 向下填充,。 結(jié)果……如下： 
這里,，咱們依然利用了VLOOKUP(ROW(A1)……)的技巧。 第一個公式：=COUNTIF(B$2:B2,F$1) 我們使用COUNTIF函數(shù),，配合相對引用的原理,，統(tǒng)計班級的累計重復(fù)次數(shù)。 第二個公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"") 我們通過VLOOKUP查詢ROW(a1)（1,，2,，3，4,，5,，上山打老……），來返回與之相對應(yīng)的C列姓名結(jié)果,，最后外套IFERROR函數(shù),，屏蔽VLOOKUP查詢不到結(jié)果而返回的錯誤值,，使之返回空白,。 在數(shù)據(jù)量大時，我們使用INDEX+SMALL數(shù)組查詢數(shù)據(jù),，難免卡機(jī),，此時不妨使用VLOOKUP+輔助列的方式，當(dāng)然,，輔助列我們不能再使用低效函數(shù)COUNTIF了,，我們可以使用這樣的公式： =(B2=$F$1)+A1 （感謝Bodhidharma老師指正錯誤之處哈） ==========我是往事如煙的分割線========== 理解了輔助列的意義，加深了VLOOKUP(ROW(A1),……)技巧的理解,，我們下面要做的,，依然是丟掉輔助列，把輔助列的內(nèi)容,，放到公式中,，直接使用一個公式得出結(jié)果。 我們依然可以使用OFFSET對COUNTIF的統(tǒng)計范圍進(jìn)行多維引用,，比如： =COUNTIF(OFFSET(B$2,,,ROW($1:$14)),F$1) 這個公式的意思,，是使用COUNTIF對B2:B2,B2:B3,B2:B4……直至B2:B15的范圍內(nèi)，分別統(tǒng)計F1數(shù)值的重復(fù)次數(shù),，得出來的結(jié)果,，自然是和輔助列是一致的,。 我們將這一段公式，放入VLOOKUP函數(shù)公式中： =IFERROR(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET($B$2,,,ROW($1:$14)),$F$1),$C$2:$C$15),2,0),"") 如此,，這個公式也便正式寫完了,。 ==========我是如煙往事的分割線========== 當(dāng)然，如果您確實(shí)了解透徹了VLOOKUP的心,，關(guān)于VLOOKUP查詢符合條件的多個數(shù)值,，我們其實(shí)也可以寫成這樣： =IFERROR(VLOOKUP($F$1,OFFSET($B$1:$C$1,SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)-1),ROW(1:1)),,15),2,0),"") 或者這樣： =IFERROR(VLOOKUP($F$1,INDIRECT("b"&SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))&":c15"),2,0),"") 我們結(jié)合第二個函數(shù)套路來稍微解釋下此中過程。 SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1)) IF函數(shù)判斷B1:B15的值,，是否等于F1,，并返回相對應(yīng)的行數(shù)序號ROW(1:15)，或者FALSE,。(為什么將IF的假值留白,，而不是像許多學(xué)友那樣習(xí)慣性的輸入4^8之類？因?yàn)檫@里沒有必要撒,，邏輯值天生就比數(shù)值大不是……) SMALL函數(shù),，按IF函數(shù)的結(jié)果，在公式下拉的過程中,，依次從小到大取數(shù),，即ROW(1:1),ROW(2:2)，取得最小值,，第二小值……,。 INDIRECT函數(shù)，搭配SMALL所取得的結(jié)果,，完成對VLOOKUP查找范圍從大到小的限定,。 比如此例中的INDIRECT(“B”&13&”:C15”),INDIRECT(“B”&14&”:C15”)……。 由于VLOOKUP天生只取首個匹配結(jié)果,，所以咱們通過查找范圍的精確限定,，便可以使它依次取得所有符合條件的結(jié)果…… 最后外套IFERROR函數(shù)，屏蔽錯誤值,，使之返回空白,。 ... .. . 好啦，現(xiàn)在,，咱們可以很清楚的知道,，關(guān)于VLOOKUP無法提取符合條件多個數(shù)值的說法，是不正確的,。呵呵,。（我每次發(fā)呵呵，都會想起胡劍么么噠,，唉）第三節(jié)：VLOOKUP條件求和以及T/N+IF{1}技巧建立內(nèi)存數(shù)組的一個應(yīng)用小例,。 這一節(jié),，我們來回答開篇所提到的第二個以及第三個問題： VLOOKUP能否進(jìn)行條件求和？就像SUMIF那樣,？ VLOOKUP第一個參數(shù)能否支持數(shù)組引用,？ 如下圖，有這樣一道題,，需要在E1,，求出A列存在的D3:D6班級的成績之和。 
解這道題的方法有很多種,，我們通常使用SUMIF： 數(shù)組：=SUM(SUMIF(A1:B5,D4:D6,B1)) 或者： 數(shù)組：=SUM((A2:A5=TRANSPOSE(D4:D6))*B2:B5) 如果用VLOOKUP,，又怎么做呢？ 我們可以寫成這樣： E1=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0)) 這個公式不需要按數(shù)組三鍵,。 我們來簡單了解下這個公式,。 重點(diǎn)是VLOOKUP的查找值，T(IF({1},D4:D6)),。 我們知道D4:D6,，是需要進(jìn)行查找統(tǒng)計的班級名稱，那么為什么要在其外套T和IF函數(shù),？或者,，我們反過來想，為什么不套T和IF函數(shù),，VLOOKUP就只對查詢范圍的第一個數(shù)值（金庸班）進(jìn)行查詢呢,？ 我們可以這么簡單的理解。 T/N+IF組合,，是讓VLOOKUP函數(shù)的第一參數(shù),，接受數(shù)組形式,，因此返回相應(yīng)的內(nèi)存數(shù)組,。 如此，VLOOKUP方能對每一個查找值進(jìn)行查詢統(tǒng)計,。 具體解釋參見小翟斑竹的貼子：http://club./thread-1115878-1-1.html 如果為了避免錯誤值的問題,，比如D4:D6出現(xiàn)了查詢范圍不曾出現(xiàn)的班級名稱：天仙班，公式可以修改為： 數(shù)組：=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0)) =========我是溫暖恰春的分割線========= 
再看一道題,。 如上圖,，對A列存在的D4:D6的班級進(jìn)行求和，班級重復(fù)的只計算一次,，答案是305,。 我們通常使用這樣的數(shù)組公式： =SUM(SUMIFS(B:B,A:A,D4:D6)/COUNTIF(A:A,D4:D6)) 或者： =SUM(N(INDIRECT("b"&MATCH(D4:D6,A1:A9,)))) 其實(shí)我們也可以使用VLOOKUP： =SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0)) 依然由于VLOOKUP天生就只取首個匹配的結(jié)果的緣故，所以咱們也就不需要對重復(fù)數(shù)據(jù)進(jìn)行二次處理,。 如果要屏蔽錯誤值,，依然要增加IFERROR： 數(shù)組=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0)) =========我是恰春溫暖的分割線========= 綜合以上兩個問題,，咱們不難發(fā)現(xiàn)，在條件求和方面,，VLOOKUP和SUMIF還是有所不同的,。 如果未加以處理，VLOOKUP只對第一次出現(xiàn)的數(shù)據(jù)進(jìn)行計算,，這是它的短處,，當(dāng)然，未必不是它的長處,。 如果未加以處理,，SUMIF會對所有數(shù)據(jù)進(jìn)行求和，不論重復(fù)與否,，這是它的長處,，當(dāng)然，未必不是它的短處,。 =========我只是分割線========= T/N+IF{1}技巧建立內(nèi)存數(shù)組的一個應(yīng)用小例 
如圖,，判定D列姓名的相對累計重復(fù)次數(shù)（中文名和英文名如果是同一個人的名字，則同樣視為重復(fù)） 這道題如果用輔助列,，會很簡單,。 直接把名字統(tǒng)一轉(zhuǎn)換為中文或者英文，再使用COUNTIF進(jìn)行重復(fù)次數(shù)計算,。 比如E2公式：=IFERROR(VLOOKUP(D2,A:B,2,0),D2),，下拉后將名字統(tǒng)一更換為中文；再使用公式=COUNTIF($E$2:E2,E2),下拉后便可得出正確結(jié)果,。 但如果不用輔助列呢,？ 如果我們繼續(xù)之前的解題思路，將查詢的名字,，統(tǒng)一更換為中文或者英文,，再進(jìn)行重復(fù)次數(shù)的計算，我們依然可以使用VLOOKUP函數(shù),。 比如公式：F2=SUM(N(IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)=IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2))) IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2),，是在公式下拉的過程中，將第二行到公式所在行的D列姓名,，統(tǒng)一轉(zhuǎn)換為中文,，并以可以計算的內(nèi)存數(shù)組的形式保存相關(guān)值。 IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2),是將D列需要判定重復(fù)次數(shù)的值,，統(tǒng)一轉(zhuǎn)化為中文,。 SUM(N……)是統(tǒng)計第一個公式的內(nèi)存數(shù)組值等于第二個公式返回值的次數(shù)，即相關(guān)名字的重復(fù)次數(shù),。第四節(jié)：VLOOKUP在字符串提取中的使用小例,。 我們依然用題來說事哈,。 下面這道題，我們需要提取A列單元格內(nèi)第一個數(shù)值,，結(jié)果如B列,。 
我們通常使用數(shù)組公式： B2=MID(A2,MATCH(1=1,ISNUMBER(-MID(A2,ROW($1:$99),1)),),1) 上面這個公式，通過ISNUMBER和MID組合,，來判斷單元格內(nèi)每一個字符是否是數(shù)值,，再通過MATCH函數(shù)，對首個數(shù)值的位置進(jìn)行定位,，最后通過MID函數(shù)來取值,。 如果我們用VLOOKUP來處理呢？ 我們可以寫成這樣： 數(shù)組：=VLOOKUP(,MID(A2,ROW($1:$99),1)*{0,1},2,) 這個公式,，依然利用MID函數(shù),，把單元格內(nèi)的字符拆成個體，分別乘以0和1,，如此則產(chǎn)生兩列數(shù)據(jù),，一列由MID(A2,ROW($1:$99),1)*0得來，另外一列由MID(A2,ROW($1:$99),1)*1得來,。 我們知道文本*0,，是錯誤值，數(shù)值*0,，結(jié)果為0,。 于是當(dāng)我們利用VLOOKUP，查找第一列的0值,，得出來的結(jié)果,，便是首個0值所對應(yīng)的數(shù)值——即我們所需要的結(jié)果。 這個技巧,，并不僅僅局限于提取首個數(shù)字的使用,，比如一個稍微復(fù)雜的示例： 
結(jié)果如C列,。 我們可以使用這樣的公式： =B3&IF(COUNT(--MID(B3,ROW($1:$52),11)),,VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0)) IF(COUNT(--MID(B3,ROW($1:$52),11))，是判斷單元格內(nèi)是否有電話號碼,。 VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0)) PHONETIC函數(shù),，將數(shù)據(jù)區(qū)域捏合為一個數(shù)據(jù)，MID函數(shù),，從中提取手機(jī)號碼,，最后通過VLOOKUP(,數(shù)據(jù)*{0,1},2,)的技巧，將MID的提取結(jié)果,，分別乘以0和1,，如此前所言，文本乘0,，為錯誤值,，數(shù)值乘0，結(jié)果為0,，最后通過VLOOKUP來取得首個匹配結(jié)果,，便是距離最近的手機(jī)號碼。最后有B3黏合提取的電話號碼,。 … .. . 后記： 這篇帖子,，只是分享思路和技巧，并不是建議每類問題用vlookup去解決,。術(shù)業(yè)有專攻,，每個函數(shù)，均有長處和短處,，而且,，數(shù)據(jù)應(yīng)該適應(yīng)函數(shù)，而不是函數(shù)來適應(yīng)數(shù)據(jù),，不管什么時候,，數(shù)據(jù)錄入的規(guī)范性，都是最重要的哈,。 再后記： 第一次寫這類分享文,，從早上9點(diǎn)鐘動筆時的信心滿滿，到中午11.30草草結(jié)束時的垂頭喪氣,，這中間的過程,，真他媽的苦。如果不是忌憚旁邊MM的心理承受能力,，俺真想砸桌子罵臟話,。妹的，俺果然還是適合講故事，不適合玩技術(shù)分析……嗯,，只希望這篇破爛東西,，可以開拓大家對于VLOOKUP函數(shù)的視野，拓展下思維方式,，嗯,，祝安。,。,。
|
|
|
