Excel VLOOKUP進階

  第一節:VLOOKUP按指定次數重復數據

如下圖,有這樣一道題,要求按照B列的指定次數,重復C列的班級名稱,結果放入E列。

Excel VLOOKUP進階

看到這道題,好學的表 大家,大概會立刻想起祝老師的某個基礎操作教程動畫,函數了得的 大家,會立刻想起某個多維數組套路。

但咱們這裡只想VLOOKUP。

如果用VLOOKUP,這題怎麼做?

很簡單。

只需要兩步。

第一步,A2輸入公式:=A1+B2,向下填充

第二步,E2輸入公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&””,向下拖動。

然後……結果……

如下圖:

Excel VLOOKUP進階

我們結合兩個公式,解釋下其中過程。

第一個公式:A1+B2,是計算相關次數的累計值,比較好理解。

第二個公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&””,看起來是常用的VLOOKUP套路,但其實有兩個很有意思的地方。

其一,VLOOKUP的查找值——ROW(A1)。在公式的下拉過程中,通過查找1,2,3,4,5,(電腦配音,12345,上~山~打~老~虎)……來返回結果。

其二,屏蔽VLOOKUP錯誤值的方式。如果VLOOKUP查找不到相關數值,比如此例中的1和2,通常會返回錯誤值#N/A,而我們通過IFERROR,使它返回公式所在單元格的下一個單元格的值。

比如,我們在E2輸入公式,VLOOKUP函數的錯誤值則返回E3,公式向下拖動,E3的錯誤值返回E4……如此類推,直至VLOOKUP函數返回正確值——則之前通過IFERROR函數判斷為錯誤值的單元格,自然統一更新為相應的正確值(……腦海裡播放多米諾骨牌從依次跌倒到依次站起的畫面)。

然後再進行新一輪循環判斷、數據更正。

最後的&」」,是函數裡常用的屏蔽零值的技法,以便在VLOOKUP公式下拉過界時,返回的零值顯示為空白。

以上兩個公式,除了VLOOKUP(ROW(A1)……)的技巧外,還利用了函數的另外一個技巧,我們姑且稱之為上下其手。

何謂上下其手?簡而言之,便是拿公式所在單元格的上下單元格結果為己用。

第一個公式,=A1+B2,是上手,拿公式所在單元格的上一個單元格的值為己用。很多人比較熟悉常用,已經很了解了。

第二個公式,=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&””,是下手,拿公式所在單元格的下一個單元格的值為己用。大家用的可能就比較少了。因為少用,所以才顯得比較有意思。大家有閒時,不妨多想下,興許別有收獲哈。

==========我是似水溫柔的分割線==========

通過輔助列的方式,我們做到了按指定次數重復數據。

下面咱們要做的,便是丟掉輔助列,直接用一個公式得出結果。

即,我們需要把A列累計次數求和的數據,放入VLOOKUP公式的查找範圍中,以便直接得出所需要的結果。

我們可以使用這樣的公式:

SUMIF(OFFSET($B$2,,,ROW($1:$4),),”<>”)

這是一個累計求和的多維數組套路,類似的套路還有MMULT、INDIRECT、SUBTOTAL等。

這個公式,是通過OFFSET函數,製作多維求和統計範圍,比如B2:B2,B2:B3,B2:B4……

最後使用SUMIF進行求和。

我們把這一段放入VLOOKUP函數中:

E2=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET($B$2,,,ROW($1:$4),),”<>”),$C$2:$C$5),2,0),E3)&””

如此,這個公式便正式寫完了。

當然,如果用LOOKUP,公式可以簡潔:

=LOOKUP(ROW(A1),SUMIF(OFFSET($B$1,,,ROW($1:$5),),”<>”)+1,$C$2:$C$6)&””

真是暈菜了,這一節,我發了N次,一直提示我有不良信息,我開始以為是上下其手,但最後發現……

是上山打~老~虎。。

好吧,愛~老~虎油,不讓說。

老~虎也不行。第二節:VLOOKUP查詢符合條件的多個結果。

通過第一節的內容,我們初步認識了VLOOKUP(ROW(A1),……)的技巧。

這一節,我們需要利用這個技巧,回答開篇所提到的第一個問題。

VLOOKUP能否查詢符合條件的多個數值?就像經典數組套路INDEX+SMALL+IF那樣?

Excel VLOOKUP進階

如上圖,我們需要提取C列符合F1班級的姓名,放入E4:E15。

通常我們會使用INDEX+SMALL+IF的數組套路:

E4=INDEX(C:C,SMALL(IF($B$1:$B$15=F$1,ROW($1:$15),4^8),ROW(A1)))&””

如果使用VLOOKUP,我們應該怎麼做?

其實也簡單。

我們還是如第一節那般,先採用輔助列的方式。

A2=COUNTIF(B$2:B2,F$1)

向下填充。

E4=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),””)

向下填充。

結果……如下:

Excel VLOOKUP進階

這裡,咱們依然利用了VLOOKUP(ROW(A1)……)的技巧。

第一個公式:=COUNTIF(B$2:B2,F$1)

我們使用COUNTIF函數,配合相對引用的原理,統計班級的累計重復次數。

第二個公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),””)

我們通過VLOOKUP查詢ROW(a1)(1,2,3,4,5,上山打老……),來返回與之相對應的C列姓名結果,最後外套IFERROR函數,屏蔽VLOOKUP查詢不到結果而返回的錯誤值,使之返回空白。

在數據量大時,我們使用INDEX+SMALL數組查詢數據,難免卡機,此時不妨使用VLOOKUP+輔助列的方式,當然,輔助列我們不能再使用低效函數COUNTIF了,我們可以使用這樣的公式:

=(B2=$F$1)+A1

(感謝Bodhidharma老師指正錯誤之處哈)

==========我是往事如煙的分割線==========

理解了輔助列的意義,加深了VLOOKUP(ROW(A1),……)技巧的理解,我們下面要做的,依然是丟掉輔助列,把輔助列的內容,放到公式中,直接使用一個公式得出結果。

我們依然可以使用OFFSET對COUNTIF的統計範圍進行多維引用,比如:

=COUNTIF(OFFSET(B$2,,,ROW($1:$14)),F$1)

這個公式的意思,是使用COUNTIF對B2:B2,B2:B3,B2:B4……直至B2:B15的範圍內,分別統計F1數值的重復次數,得出來的結果,自然是和輔助列是一致的。

我們將這一段公式,放入VLOOKUP函數公式中:

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET($B$2,,,ROW($1:$14)),$F$1),$C$2:$C$15),2,0),””)

如此,這個公式也便正式寫完了。

==========我是如煙往事的分割線==========

當然,如果您確實了解透徹了VLOOKUP的心,關於VLOOKUP查詢符合條件的多個數值,我們其實也可以寫成這樣:

=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),””)

我們結合第二個函數套路來稍微解釋下此中過程。

SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))

IF函數判斷B1:B15的值,是否等於F1,並返回相對應的行數序號ROW(1:15),或者FALSE。(為什麼將IF的假值留白,而不是像許多學友那樣習慣性的輸入4^8之類?因為這裡沒有必要撒,邏輯值天生就比數值大不是……)

SMALL函數,按IF函數的結果,在公式下拉的過程中,依次從小到大取數,即ROW(1:1),ROW(2:2),取得最小值,第二小值……。

INDIRECT函數,搭配SMALL所取得的結果,完成對VLOOKUP查找範圍從大到小的限定。

比如此例中的INDIRECT(「B」&13&」:C15」),INDIRECT(「B」&14&」:C15」)……。

由於VLOOKUP天生只取首個匹配結果,所以咱們通過查找範圍的精確限定,便可以使它依次取得所有符合條件的結果……

最後外套IFERROR函數,屏蔽錯誤值,使之返回空白。

..

.

好啦,現在,咱們可以很清楚的知道,關於VLOOKUP無法提取符合條件多個數值的說法,是不正確的。呵呵。(我每次發呵呵,都會想起胡劍麼麼噠,唉)第三節:VLOOKUP條件求和以及T/N+IF{1}技巧建立記憶體數組的一個應用小例。

這一節,我們來回答開篇所提到的第二個以及第三個問題:

VLOOKUP能否進行條件求和?就像SUMIF那樣?

VLOOKUP第一個參數能否支持數組引用?

如下圖,有這樣一道題,需要在E1,求出A列存在的D3:D6班級的成績之和。

Excel VLOOKUP進階

解這道題的方法有很多種,我們通常使用SUMIF:

數組:=SUM(SUMIF(A1:B5,D4:D6,B1))

或者:

數組:=SUM((A2:A5=TRANSPOSE(D4:D6))*B2:B5)

如果用VLOOKUP,又怎麼做呢?

我們可以寫成這樣:

E1=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))

這個公式不需要按數組三鍵。

我們來簡單了解下這個公式。

重點是VLOOKUP的查找值,T(IF({1},D4:D6))。

我們知道D4:D6,是需要進行查找統計的班級名稱,那麼為什麼要在其外套T和IF函數?或者,我們反過來想,為什麼不套T和IF函數,VLOOKUP就只對查詢範圍的第一個數值(金庸班)進行查詢呢?

我們可以這麼簡單的理解。

T/N+IF組合,是讓VLOOKUP函數的第一參數,接受數組形式,因此返回相應的記憶體數組。

如此,VLOOKUP方能對每一個查找值進行查詢統計。

具體解釋參見小翟斑竹的貼子:http://club.excelhome.net/thread-1115878-1-1.html

如果為了避免錯誤值的問題,比如D4:D6出現了查詢範圍不曾出現的班級名稱:天仙班,公式可以修改為:

數組:=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是溫暖恰春的分割線=========

Excel VLOOKUP進階

再看一道題。

如上圖,對A列存在的D4:D6的班級進行求和,班級重復的只計算一次,答案是305。

我們通常使用這樣的數組公式:

=SUM(SUMIFS(B:B,A:A,D4:D6)/COUNTIF(A:A,D4:D6))

或者:

=SUM(N(INDIRECT(“b”&MATCH(D4:D6,A1:A9,))))

其實我們也可以使用VLOOKUP:

=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))

依然由於VLOOKUP天生就只取首個匹配的結果的緣故,所以咱們也就不需要對重復數據進行二次處理。

如果要屏蔽錯誤值,依然要增加IFERROR:

數組=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是恰春溫暖的分割線=========

綜合以上兩個問題,咱們不難發現,在條件求和方面,VLOOKUP和SUMIF還是有所不同的。

如果未加以處理,VLOOKUP只對第一次出現的數據進行計算,這是它的短處,當然,未必不是它的長處。

如果未加以處理,SUMIF會對所有數據進行求和,不論重復與否,這是它的長處,當然,未必不是它的短處。

=========我只是分割線=========

T/N+IF{1}技巧建立記憶體數組的一個應用小例

Excel VLOOKUP進階

如圖,判定D列姓名的相對累計重復次數(中文名和英文名如果是同一個人的名字,則同樣視為重復)

這道題如果用輔助列,會很簡單。

直接把名字統一轉換為中文或者英文,再使用COUNTIF進行重復次數計算。

比如E2公式:=IFERROR(VLOOKUP(D2,A:B,2,0),D2),下拉後將名字統一更換為中文;再使用公式=COUNTIF($E$2:E2,E2),下拉後便可得出正確結果。

但如果不用輔助列呢?

如果我們繼續之前的解題思路,將查詢的名字,統一更換為中文或者英文,再進行重復次數的計算,我們依然可以使用VLOOKUP函數。

比如公式: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列姓名,統一轉換為中文,並以可以計算的記憶體數組的形式保存相關值。

IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2),是將D列需要判定重復次數的值,統一轉化為中文。

SUM(N……)是統計第一個公式的記憶體數組值等於第二個公式返回值的次數,即相關名字的重復次數。第四節:VLOOKUP在字符串提取中的使用小例。

我們依然用題來說事哈。

下面這道題,我們需要提取A列單元格內第一個數值,結果如B列。

Excel VLOOKUP進階

我們通常使用數組公式:

B2=MID(A2,MATCH(1=1,ISNUMBER(-MID(A2,ROW($1:$99),1)),),1)

上面這個公式,通過ISNUMBER和MID組合,來判斷單元格內每一個字符是否是數值,再通過MATCH函數,對首個數值的位置進行定位,最後通過MID函數來取值。

如果我們用VLOOKUP來處理呢?

我們可以寫成這樣:

數組:=VLOOKUP(,MID(A2,ROW($1:$99),1)*{0,1},2,)

這個公式,依然利用MID函數,把單元格內的字符拆成個體,分別乘以0和1,如此則產生兩列數據,一列由MID(A2,ROW($1:$99),1)*0得來,另外一列由MID(A2,ROW($1:$99),1)*1得來。

我們知道文本*0,是錯誤值,數值*0,結果為0。

於是當我們利用VLOOKUP,查找第一列的0值,得出來的結果,便是首個0值所對應的數值——即我們所需要的結果。

這個技巧,並不僅僅局限於提取首個數字的使用,比如一個稍微複雜的示例:

Excel VLOOKUP進階

如上圖。數據區域是一些數據,有的人名後有電話號碼,有的人名後沒有電話號碼,現在要求把沒有電話號碼的人名增補電話號碼,增補的電話號碼從哪來呢?向下數,從距離最近的擁有電話號碼的人名那兒來,(關係再遠,比如表大爺,畢竟也是一家人不是?)

結果如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)),是判斷單元格內是否有電話號碼。

VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))

PHONETIC函數,將數據區域捏合為一個數據,MID函數,從中提取手機號碼,最後通過VLOOKUP(,數據*{0,1},2,)的技巧,將MID的提取結果,分別乘以0和1,如此前所言,文本乘0,為錯誤值,數值乘0,結果為0,最後通過VLOOKUP來取得首個匹配結果,便是距離最近的手機號碼。最後有B3黏合提取的電話號碼。

http://club.excelhome.net/thread-1149155-1-1.html

..

.

後記:

這篇帖子,只是分享思路和技巧,並不是建議每類問題用vlookup去解決。術業有專攻,每個函數,均有長處和短處,而且,數據應該適應函數,而不是函數來適應數據,不管什麼時候,數據錄入的規範性,都是最重要的哈。

再後記:

第一次寫這類分享文,從早上9點鐘動筆時的信心滿滿,到中午11.30草草結束時的垂頭喪氣,這中間的過程,真他媽的苦。如果不是忌憚旁邊MM的心理承受能力,俺真想砸桌子罵髒話。妹的,俺果然還是適合講故事,不適合玩技術分析……嗯,只希望這篇破爛東西,可以開拓大家對於VLOOKUP函數的視野,拓展下思維方式,嗯,祝安。。。