|
作為一名數(shù)據(jù)分析師，工作中打交道最多的就是數(shù)據(jù)，大部分都是用Excel處理，很早之前覺得數(shù)據(jù)匹配查詢只用vlookup函數(shù)就夠了，但是vlookup函數(shù)有個弊端，就是無法反向查找。 如下圖所示，用姓名去查找工號，單獨用vlookup函數(shù)是無法完成的：  平時遇到這種情況，個人最常用的方法就是將數(shù)據(jù)源中的B列姓名復(fù)制到A列工號之前，在利用vlookup函數(shù)匹配，但是在遇到表格數(shù)據(jù)較多，或者表格操作空間較少，這種方法較為耗時，可操作性差。 那么有其它辦法嗎？下面分享三個可以逆向查找的小技巧，希望對大家有所幫助！ vlookup if(或者choose） 下圖的示例中，用姓名去匹配個人工號，在F2單元格輸入公式： =VLOOKUP(E2,IF({1,0},B:B,A:A),2,0)，公式下拉，核對數(shù)據(jù)，可以發(fā)現(xiàn)公式可以正確返回結(jié)果。  vlookup第二個參數(shù)表示要匹配的區(qū)域，這里用IF({1,0},B:B,A:A)代替，if函數(shù)的參數(shù)1是個數(shù)組，其中1代表true，0代表false； true返回if第二個參數(shù)，即B:B列內(nèi)容，false返回if第三個參數(shù)，即A:A列內(nèi)容，這樣，IF({1,0},B:B,A:A)相當(dāng)于重新組合出一個虛擬數(shù)組，數(shù)組形式為B（姓名）A（工號），這個虛擬數(shù)組組成vlookup函數(shù)的參數(shù)2，這樣給vlookup才能返回正確的結(jié)果。 小結(jié)： IF({1,0},B:B,A:A)構(gòu)建了一個虛擬數(shù)組，也就是將查找列與匹配列正向化，使vlookup功能能夠正常使用。 這里也可以利用vlookup choose方法，公式如下：'=CHOOSE({1,2},B:B,A:A)'，原理同vlookup if相似，這里就不在贅述。 index match 這兩個函數(shù)組合使用功能還是蠻強大的，容易理解，更方便掌握。 第一步，先找出'小天'在B列中的行位置，F(xiàn)2單元格輸入：'=match(E2,B:B,0)',函數(shù)返回5，核對數(shù)據(jù)源，小天的確處在B列中的第五行；  第二步，取出A列的第五行的數(shù)，即為'小天'的工號，邏輯上大家可以理解吧，F(xiàn)2單元格輸入：'=index(A:A,match(E2,B:B,0))',函數(shù)返回正確的結(jié)果。  注：此法index()有三個參數(shù)，這里只用到前兩個參數(shù)，第三個參數(shù)忽略。 lookup法 原則上lookup可以替代vlookup函數(shù)，因為lookup函數(shù)可以實現(xiàn)vlookup函數(shù)的所有功能，只不過vlookup足夠應(yīng)付大部分的數(shù)據(jù)匹配被大家熟知。 在F2單元格輸入：'=LOOKUP(1,0/(E2=B:B),A:A)'，公式下拉，完成匹配。  公式：LOOKUP(查找的值，查找區(qū)域，返回區(qū)域) 第二個參數(shù)'查找區(qū)域'的數(shù)據(jù)必須按升序排列，且第三個參數(shù)區(qū)域的大小必須與第二個參數(shù)區(qū)域大小一致，否則函數(shù)LOOKUP不能返回正確的結(jié)果； 為了解決升序問題，引入了LOOKUP(0,0/條件，返回區(qū)域)來解決這一問題，參數(shù)二中的'條件'成立返回true，不成立返回false； 0/true返回0，0/false返回#DIV/0!，說明滿足條件返回0，不滿足返回#DIV/0!，這時只要用一個大于等于0的數(shù)值查找就可以返回正確結(jié)果了，因為lookup默認忽略錯誤值，相當(dāng)于排序。 覺得比較饒的小伙伴，直接套用公式即可！ |
|
|
