-
1 # 精進Excel
-
2 # 罌粟姐姐
作為Excel中的大眾情人，VLOOKUP函式可謂是人見人愛，花見花開，俗稱“職場必殺技”。
可是人無完人，函式也沒有完美的函式，VLOOKUP函式有兩大弱點：
一是當存在多條滿足條件的記錄時，VLOOKUP函式只能返回第1個滿足條件的記錄。
二是第3個引數必須為正，不能為負，即只能從左往右查，不能從右往左查。
今天，我們來看看如果破解VLOOKUP函式的第一個弱點。
案例：
有這樣一組資料。
希望得到這樣的結果。
下面我們來一步一步實現想要的效果。
第一步：建立基礎表格，插入控制元件。
第二步：編輯通知單編號。
公式=2015000+F2&""（其中，""是為了將數字格式轉換為文字格式）
第三步：在原始資料中設定輔助列，對重複的查詢值進行編碼。
公式=IF(B2=通知單!$D$2,COUNT($A$1:A1)+1,"")
公式解讀：當源資料中的通知單編號與通知單SHEET表中通知單編號一致時，則返回該編號是第幾次出現，如果不一致則為空格。
第四步：在通知單sheet表中輸入公式，進行查詢。
公式=IFERROR(VLOOKUP(ROW(1:1),源資料!$A:$E,COLUMN(B:B),0),"")
當通知單編號發生變化時，源資料中的輔助列也在發生變化，編號為哪一個，輔助列中對應的編碼都發生變化。
然後用IFERROR函式將沒有編碼的通知單遮蔽，變為空格。
公式原理如圖所示。
好啦，案例分析就到這裡了，原始檔下載地址：
連結：http://pan.baidu.com/s/1i4RNyNr 密碼：vyza
-
3 # 時說新語
VLOOKUP函式是EXCEL最常用的查詢函式，VLOOKUP函式欄目有很多該函式的各種使用方法的教程，但還是有很多同學提問與該函式有關的問題。本文將常見的VLOOKUP函式相關問題總結一下，主要為VLOOKUP函式怎麼用？如何使用VLOOKUP函式進行反向逆向查詢、模糊查詢、區間查詢、多條件查詢、多項查詢。
一、VLOOKUP函式怎麼用?
VLOOKUP是一個查詢函式，給定一個查詢的目標，它就能從指定的查詢區域中查詢返回想要查詢到的值。它的基本語法為：
VLOOKUP(查詢目標，查詢範圍，返回值的列數，精確OR模糊查詢)
下面以一個例項來介紹一下這四個引數的使用
例：如下圖所示，要求根據表二中的姓名，查詢姓名所對應的年齡。
公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)
引數說明：
1、查詢目標：就是你指定的查詢的內容或單元格引用。本例中表二A列的姓名就是查詢目標。我們要根據表二的“姓名”在表一中A列進行查詢。
公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0)
2、查詢範圍(VLOOKUP(A13,$B$2:$D$8,3,0) )：指定了查詢目標，如果沒有說從哪裡查詢，EXCEL肯定會很為難。所以下一步我們就要指定從哪個範圍中進行查詢。VLOOKUP的這第二個引數可以從一個單元格區域中查詢，也可以從一個常量陣列或記憶體陣列中查詢。本例中要從表一中進行查詢，那麼範圍我們要怎麼指定呢?這裡也是極易出錯的地方。大家一定要注意，給定的第二個引數查詢範圍要符合以下條件才不會出錯：
A 查詢目標一定要在該區域的第一列。本例中查詢表二的姓名，那麼姓名所對應的表一的姓名列，那麼表一的姓名列(列)一定要是查詢區域的第一列。象本例中，給定的區域要從第二列開始，即$B$2:$D$8，而不能是$A$2:$D$8。因為查詢的“姓名”不在$A$2:$D$8區域的第一列。
B 該區域中一定要包含要返回值所在的列，本例中要返回的值是年齡。年齡列(表一的D列)一定要包括在這個範圍內，即：$B$2:$D$8，如果寫成$B$2:$C$8就是錯的。
3、返回值的列數(B13 =VLOOKUP(A13,$B$2:$D$8,3,0))。這是VLOOKUP第3個引數。它是一個整數值。它怎麼得來的呢。它是“返回值”在第二個引數給定的區域中的列數。本例中我們要返回的是“年齡”，它是第二個引數查詢範圍$B$2:$D$8的第3列。這裡一定要注意，列數不是在工作表中的列數(不是第4列)，而是在查詢範圍區域的第幾列。如果本例中要是查詢姓名所對應的性別，第3個引數的值應該設定為多少呢。答案是2。因為性別在$B$2:$D$8的第2列中。
4、精確OR模糊查詢(VLOOKUP(A13,$B$2:$D$8,3,0) )，最後一個引數是決定函式精確和模糊查詢的關鍵。精確即完全一樣，模糊即包含的意思。第4個引數如果指定值是0或FALSE就表示精確查詢，而值為1 或TRUE時則表示模糊。這裡蘭色提醒大家切記切記，在使用VLOOKUP時千萬不要把這個引數給漏掉了，如果缺少這個引數默為值為模糊查詢，我們就無法精確查詢到結果了。
二、VLOOKUP的反向查詢
VLOOKUP的反向查詢，需要用IF函式把資料來源倒置一下。
一般情況下，VLOOKUP函式只能從左向右查詢。但如果需要從右向右查詢，則需要把區域進行“乾坤大挪移”，把列的位置用陣列互換一下。
例：要求在如下圖所示表中的姓名反查工號。
公式：=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)
公式剖析：
1、這裡其實不是VLOOKUP可以實現從右至右的查詢，而是利用IF函式的陣列效應把兩列換位重新組合後，再按正常的從左至右查詢。
2、IF({1,0},B2:B5,A2:A5)這是本公式中最重要的組成部分。在EXCEL函式中使用陣列時(前提時該函式的引數支援陣列)，返回的結果也會是一個數組。這裡1和0不是實際意義上的數字，而是1相關於TRUE，0相當於FALSE，當為1時，它會返回IF的第二個引數(B列)，為0時返回第二個引數(A列)。根據陣列運算返回陣列，所以使用IF後的結果返回一個數組(非單元格區域)：{"張一","A001";"趙三","A002";"楊五","A003";"孫二","A004"}
三、VLOOKUP的模糊查詢
模糊查詢就是匹配查詢。
在A列我們知道如何查詢型號為“AAA”的產品所對應的B列價格，即：
=VLOOKUP(C1,A:B,2,0)
如果需要查詢包含“AAA”的產品名稱怎麼表示呢?如下圖表中所示。
公式=VLOOKUP("*"&A10&"*",A2:B6,2,0)
公式說明：VLOOKUP的第一個引數允許使用萬用字元“*”來表示包含的意思，把*放在字元的兩邊，即"*" & 字元 & "*"。
四、VLOOKUP的區間查詢
數字的區間查詢即給定多個區間，指定一個數就可以查找出它在哪個區間並返回這個區間所對應的值。
VLOOKUP的第4個引數，如果為0或FALSE是精確查詢，如果是1或TRUE或省略則為模糊查詢，那麼實現區間查詢正是第4個引數的模糊查詢應用。
首先需要了解一下VLOOKUP函式模糊查詢的兩個重要規則：
1、引用的數字區域一定要從小到大排序。雜亂的數字是無法準確查詢到的。如下面A列符合模糊查詢的前題，B列則不符合。
2、模糊查詢的原理是：給一定個數，它會找到和它最接近，但比它小的那個數。詳見下圖說明。
最後看一個例項：
例：如下圖所示，要求根據上面的提成比率表，在提成表計算表中計算每個銷售額的提成比率和提成額。
公式：=VLOOKUP(A11,$A$3:$B$7,2)
公式說明：
1、上述公式省略了VLOOKUP最後一個引數，相當於把第四個引數設定成1或TRUE。這表示VLOOKUP要進行數字的區間查詢。
2、圖中公式中在查詢5000時返回比率表0所對應的比率1%，原因是0和10000與5000最接近，但VLOOKUP只選比查詢值小的那一個，所以公式會返回0所對應的比率1%。
五、VLOOKUP的多條件查詢
VLOOKUP函式需要借用陣列才能實現多條件查詢。
例：要求根據部門和姓名查詢C列的加班時間。
分析：不是讓VLOOKUP本身實現多條件查詢，而是想辦法重構一個數組。多個條件可以用&連線在一起，同樣兩列也可以連線成一列資料，然後用IF函式進行組合。
公式：{=VLOOKUP(A9&B9,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)}
公式剖析：
1、A9&B9 把兩個條件連線在一起。把他們做為一個整體進行查詢。
2、A2:A5&B2:B5，和條件連線相對應，把部分和姓名列也連線在一起，作為一個待查詢的整體。
3、IF({1,0},A2:A5&B2:B5,C2:C5) 用IF({1，0}把連線後的兩列與C列資料合併成一個兩列的記憶體陣列。按F9後可以檢視的結果為：
{"銷售張一",1;"銷售趙三",5;"人事楊五",3;"銷售趙三",6}
4、完成了陣列的重構後，接下來就是VLOOKUP的基本查詢功能了，另外公式中含有多個數據與多個數據運算(A2:A5&B2:B5),，所以必須以陣列形式輸入，即按ctrl+shift後按ENTER結束輸入。
六、VLOOKUP的多項查詢
VLOOKUP一般情況下只能查詢一個，那麼多項該怎麼查詢呢?
例3 要求把如圖表中所有張一的消費金額全列出來
分析：經過前面的學習，我們也有這樣一個思路，我們在實現複雜的查詢時，努力的方向是怎麼重構一個查詢內容和查詢的區域。要想實現多項查詢，我們可以對查詢的內容進行編號，第一個出現的是後面連線1，第二個出現的連線2。。。
公式：{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}
公式剖析：
1、B$9&ROW(A1) 連線序號，公式向下複製時會變成B$9連線1，2，3
2、給所有的張一進行編號。要想生成編號，就需要生成一個不斷擴充的區域(INDIRECT("b2:b"&ROW($2:$6))，然後在這個逐行擴充的區域內統計“張一”的個數，在連線上$B$2:$B$6後就可以對所有的張一進行編號了。
3、IF({1，0}把編號後的B列和C組重構成一個兩列陣列
透過以上的講解，需要知道，VLOOKUP函式的基本用法是固定的，要實現高階查詢，就需要藉助其他函式來重構查詢內容和查詢陣列。
-
4 # 午後電商
這個問題應該說的是一對多的匹配，其實不難主要是一個思路問題：
一對多匹配，比如一個產品的供應商有多個，要根據產品把所有的供應商匹配出來：
如上圖根據左邊的資料來源表在右邊的供應商匹配表中匹配出每個產品對應的供應商：
第一步：最佳化資料來源表，給表格新增匹配列。
選中第一列右鍵 > 查入一列 > 在第一個單元格利用countif函式製作匹配值：=B2&COUNTIF(B$1:B2,B2) > 向下複製填充
注：COUNTIF(B$1:B2,B2) 是用來確定重複數值是第幾次出現；
第二步：用VLOOKUP函式在目標表格匹配對應的值。
選中第一個單元格輸入公式：=IFERROR(VLOOKUP($E2&COLUMN(A1),$A:$C,3,0),"") > 向左，向下複製填充：
注：
COLUMN(A1)為了實現向右複製過程中返回數值1、2、3序號，用&和產品連結，實現生成A1、A2、A3···
$E2，實現向右複製不改變引用單元格，向下複製改變單元格；
IFERROR函式實現無值的時候現實為空白：
如果夥伴們想要利用碎片時間學習excel、ppt、ps、axure等職場軟體，或者在關鍵的時候需要一個可以提供幫助的小夥伴，關注【菜鳥辦公】小超隨叫隨到
-
5 # 陶澤昱
Match+Index函式比Vlookup函式功能很多，不僅可以返回多個結果，還可以完成多個條件查詢！
看下圖，姓名重複了，VLOOKUP無法判斷！Match+Index多條件查詢可以輕鬆完成！
Match+Index功能>Vlookup功能
以上圖為例，姓名重複時，查詢引用函式Match+Index實現多條件查詢。
原理：將姓名和學號列合併產生一個唯一值！然後查詢這個唯一值返回對應資料即可！
步驟一：在C12單元格輸入=INDEX(D2:D8,MATCH(A12:A18&B12:B18,$A$2:$A$8&$B$2:$B$8,0))
步驟二：輸入完函式後以CTRL+SHIFT+ENTER三鍵結束！！！
我們來解析一下=INDEX(D2:D8,MATCH(A12:A18&B12:B18,$A$2:$A$8&$B$2:$B$8,0))
A12:A18B12:B18,是將姓名+學號合併，即“曾令煊”+“C120101”，曾令煊C120101
$A$2:$A$8$B$2:$B$8是將姓名列+學號列合併
MATCH會在合併列裡查詢合併的資料“曾令煊C120101”
即MATCH(A12:A18&B12:B18,$A$2:$A$&8$B$2:$B$8,0))返回“曾令煊C120101”所在的行數1
D2:D8是總分列
INDEX(D2:D8,MATCH(A12:A18&B12:B18,$A$2:$A$8&$B$2:$B$8,0)))返回D2：D8列中的第“1”行，即703.50分。
這裡大家需要注意一個技巧，輸入函式之前一定要選定C12：C18區域，然後到fx插入函式，最後以CTRL+SHIFT+ENTER三鍵結束！
所有的對應分數就會被查找出來了！
更多免費教程及表格模板，私聊裡回覆相應的關鍵字獲取！
行業財務報表：回覆“財務報表”
專案進度表：回覆“專案進度表”
考勤表：回覆“考勤表”
HR管理系統：回覆“HR管理”
倉管表：回覆“倉管表”
進銷存管理系統：回覆“進銷存”
員工薪資管理系統：回覆“個稅工資條”
自動生成全年排班表：回覆“排班表”
精美圖表模板：回覆“分享”
Office Excel2016最新版：回覆“2016”
回覆列表
兩步即可實現Vlookup函式的一對多查詢！！
案例如下表中，我們要查詢所有屬於二組的成員，但是從左邊的表格中可以看出，二組對應兩個成員：高玉良、李達康，也就是說一次要查詢並返回兩個值。
Vlookup函式有一個特點：總會以第一個被找到資料作為最終的匹配資料。
意思就是說，無論一個查詢值對應多少個應返回的值，查詢之後，總是返回第一個找到的值。
所以，當有兩個”二組“同時存在時，很自然的，它只能預設為匹配首個，也就是說只能返回”高育良“。
那麼，是不是傳說中的Vlookup遇到這種情況也只能望洋興嘆，無能為力啊？
當然不是！
作為專業從事匹配工作的函式，Vlookup只是需要一些額外的輔助。
Step1：構造輔助列從資料來源中可以看出來一定的規律：比如，組別中第一個“一組”對應“侯亮平”，第二個“一組”對應“陳海”……，也即需要返回的成員是按照次序排列的。
所以，為了實現一對一匹配，需要構建出組別的次序。
在C2單元格中輸入公式=COUNTIF(A$2:A2,A2)，並向下複製填充，可以求出每一個組別對應成員的次序。
求出成員在組別中的次序
這一步是非常關鍵的一點，可以看出，求出成員的次序之後，從次序上來看，每個組別返回成員時，就是唯一值了。
然後插入一個新列如圖中的B列，並將A列資料和D列資料組合，公式為：=A2&D2，形成新的資料列，如圖所示。
奇蹟出現了，使用新組別這一列資料去查詢成員，相當於為每一個組別創造了唯一的識別碼，再用Vlookup時，就能精確地一對一匹配到了。
至此，可得出解決此問題的關鍵點為：
①透過COUNTIF函式，製造出序列（難點是A$2:A2動態引用，這是創造正確編號的核心）；
②透過&組合組別和次序，製造唯一性。
Step2：實現VLOOKUP一對多查詢萬事俱備只欠東風，最後一步——Vlookup多條件匹配。
在G2單元格中寫入公式=VLOOKUP(F$2&ROW(A1),B:C,2,0)，然後向下複製填充，直到出現#N/A錯誤，則會返回“二組”對應的所有成員。
這裡使用ROW(A1)函式生成序列，然後再與F2單元格組合，於是就依次生成“二組1”，“二組2”，相當於在資料表中建立了一個輔助列，只不過這樣的做法更加簡便。
The End