Excel VLOOKUP進階

看到這道題，好學的表親們，大概會立刻想起祝老師的某個基礎操作教程動畫，函數了得的親們，會立刻想起某個多維數組套路。但咱們這裡只想VLOOKUP。如果用VLOOKUP，這題怎麼做？很簡單。只需要兩步。第一步，A2輸入公式：=A1+B2，向下填充第二步，E2輸入公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，向下拖動。然後……結果……如下圖：

我們結合兩個公式，解釋下其中過程。第一個公式：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那樣？

如上圖，我們需要提取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),"")向下填充。結果……如下：

這裡，咱們依然利用了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班級的成績之和。

解這道題的方法有很多種，我們通常使用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))=========我是溫暖恰春的分割線=========

再看一道題。如上圖，對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}技巧建立內存數組的一個應用小例

如圖，判定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列。

我們通常使用數組公式：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值所對應的數值——即我們所需要的結果。這個技巧，並不僅僅局限於提取首個數字的使用，比如一個稍微複雜的示例：

如上圖。數據區域是一些數據，有的人名後有電話號碼，有的人名後沒有電話號碼，現在要求把沒有電話號碼的人名增補電話號碼，增補的電話號碼從哪來呢？向下數，從距離最近的擁有電話號碼的人名那兒來，（關係再遠，比如表大爺，畢竟也是一家人不是？）結果如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函數的視野，拓展下思維方式，嗯，祝安。。。
推薦閱讀：