Vlookup函數最難的6個查找公式+12種常見錯誤
一、Vlookup函數最難的6個查找公式
1、區間查找
【例4】如下圖所示,要求根據左表的提成比率表,在右表中根據銷售額在G列查找適用的比率。
=VLOOKUP(F3,$B$3:$C$11,2)
2、模糊查找
【例5】查找包含AAA名稱的產品價格
=VLOOKUP("*"&A10&"*",A2:B6,2,0)
3. 多條件查找
【例8】如下圖所示要求,同時根據姓名和工號查找職位。
{=VLOOKUP(B9&C9,IF(,B2:B6&A2:A6,E2:E6),2,0)}
4、多工作表查找
【例10】從各部門中查找員工的基本工資,在哪一個表中不一定。
方法1
=IFERROR(VLOOKUP(A2,服務!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),"無此人信息")))))
方法2:
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務";"人事";"綜合";"財務"}&"!a:a"),A2),{"銷售";"服務";"人事";"綜合";"財務"})&"!a:g"),7,0)
5、一對多查找1
【例】根據產品查找相對應的所有供應商
A2 =B2&COUNTIF(B$1:B2,B2)
B11=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")
6、一對多查找2
【例12】查找張三的所有消費記錄
數組公式,大括弧是按ctrl+shift+enter後自動產生的,非手工輸入
{=VLOOKUP(B$9&ROW(A1),IF(,$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}
二、Vlookup函數12種最常見錯誤
如果評選Excel中最常用的函數,Vlookup函數肯定是第1名,但如果評出錯率最高的函數,也會是Vlookup函數。經常會有同學提問,什麼我的Vlookup又出現#N/A了,明明公式是正確的哦。
一、函數參數使用錯誤。
第1種:第2個參數區域設置錯誤之1。
【例1】:如下圖所示,根據姓名查找齡時產生錯誤。
錯誤原因: vlookup函數第二個參數是查找區域,該區域的第1列有一個必備條件,就是查找的對象(A9),必須對應於區域的第1列。本例中是根據姓名查找的,那麼,姓名列必須是在區域的第1列位置,而上述公式中姓名列是在區域A1:E6的第2列。所以公式應改為:
=VLOOKUP(A9,B1:E6,3,0)
----------------------------------
第2種:第2個參數區域設置錯誤之2。
【例2】 如下圖所示根據姓名查找職務時產生查找錯誤。
錯誤原因:本例是根據姓名查找職務,可大家注意一下,第2個參數B1:D6根本就沒有包括E列的職務,當然會產生錯誤了。所以公式應改為:
=VLOOKUP(A9,B1:E6,4,0)
----------------------------------
第3種:第4個參數少了或設置錯誤。
【例3】,如下圖所示根據工號查找姓名
錯誤原因:vlookup第四個參數為0時表示精確查找,為1或省略時表示模糊查找。如果忘了設置第4個參數則會被公式誤以為是故意省略,按模糊查找進行。當區域也不符合模糊查找規則時,公式就會返回錯誤值。所以公式應改為。
=VLOOKUP(A9,A1:D6,2,0)
二、數字格式不同,造成查找錯誤。
第4種查找為數字,被查找區域為文本型數字。
【例4】:如下圖所示根據工號查找姓名,查找出現錯誤。
錯誤原因:在vlookup函數查找過程中,文本型數字和數值型數字會被認為不同的字元。所以造成無法成功查找。
解決方案:把查找的數字在公式中轉換成文本型,然後再查找。即:
=VLOOKUP(A9&"",A1:D6,2,0)
-----------------------------------------------
第5種查找格式為文本型數字,被查找區域為數值型數字。
【例5】:如下圖所示根據工號查找姓名,查找出現錯誤
錯誤原因:同4
解決方法:把文本型數字轉換成數值型。即:
=VLOOKUP(A9*1,A1:D6,2,0)
註:如果你手工把文本轉換成數值類型,就不必在公式中轉換格式了。
-------------------------------------------
三、引用方式使公式複製後產生錯誤。
第6種沒有正確的使用引用方式,造成在複製公式後區域發生變動引起錯誤。
【例6】,如下圖所示,當C9的公式複製到C10和C11後,C10公式返回錯誤值。
錯誤原因:由於第二個參數A2:D6是相對引用,所以向下複製公式後會自動更改為A3:D7,而A10中的工號A01所在的行,不在A3:D7區域中,從而造成查找失敗。
解決方案:把第二個參數的引用方式由相對引用改為絕對引用即可。
四、多餘的空格或不可見字元
第7種數據表中含有多餘的空格。
【例7】 如下圖所示,由於A列工號含有多餘的空格,造成查找錯誤。
錯誤原因:多一個空格,用不帶空格的字元查找當然會出錯了。
解決方案: 1 手工替換掉空格。建議用這個方法
2 在公式中用trim函數替換空格而必須要用數據公式形式輸入。
按ctrl+shift+enter輸入後數組形式為
{=VLOOKUP(A9,TRIM(A1:D6),2,0)}
--------------------------------------
第8種:類空格但非空格的字元。
在表格存在大量的「空格」,但又用空格無法替換掉時,這些就是類空格的不可見字元,這時可以「以其人之道還之其人之身」,直接在單元格中複製不可見字元粘貼到替換窗口,替換掉即可。
-----------------------------------
第9種:不可見字元的影響
【例】: 如下圖所示的A列中,A列看上去不存在空格和類空格字元,但查找結果還是出錯。
出錯原因:這是從網頁或資料庫中導入數據時帶來的不可見字元,造成了查找的錯誤。
解決方案:在A列後插入幾列空列,然後對A列進行分列操作(數據 - 分列),即可把不可見字元分離出去。
蘭色說:在日常vlookup函數查找錯誤示例中,第7種是最常見的一種錯誤之一。同學遇到vlookup查找錯誤時,如果參數設置沒有問題,就看一下數據表中有沒有多餘的空格。
第10種:反向查找vlookup不支持產生的錯誤。
【例10】 如下圖所示的表中,根據姓名查找工號,結果返回了錯誤
。
錯誤原因:vlookup不支持反向查找。
解決方法:1 用if函數重組區域,讓兩列顛倒位置。
=VLOOKUP(D8,IF(,D2:D4,E2:E4),2,0)
2 用index+match組合實現。
=INDEX(D2:D4,MATCH(D8,E2:E4,0))
第11種:通配符引起的查找錯誤
【例11】,如下圖所示,根據區間查找提成返回錯誤值。
錯誤原因:~用於查找通配符,如果在vlookup公式中出現,會被認為特定用途,非真正的~。如在表格中查找3*6 ,356,376也被查找到。
如果精確查找3*6,需要使用~,如下圖所示。
解決方法:用~~就可以表示查找~了。所以公式可以修改為
=VLOOKUP(SUBSTITUTE(A8,"~","~~"),A2:B4,2,0)
第12種:vlookup函數第1個參數不直接支持數組形式產生的錯誤
【例12】:如下圖所示,同時查找A和C產品的和,然後用SUM求和。
錯誤原因: VLOOKUP第一個參數不能直接用於數組。
解決方法:利用N/T+IF結構轉化一下數組,如果不了解N/T+IF結構用法,可以參考http://www.excelpx.com/thread-174362-1-1.html。公式修改為:
=SUM(VLOOKUP(T(IF(,A8:B8)),A2:B5,2,))
蘭色說:vlookup函數是最有用的函數,但同時也應了那句老話:做的越多,錯的也越多。所以掌握提高避錯的技術非常有必要。
※6小時,寫了一篇適合Excel小白學的VBA入門教程
※Sumif 函數的4個怪異用法,第3個太利害了!
※Excel公式這樣寫,慢的要死!
※你只會雙擊打開excel?太out了!
※老外做的Excel動態圖表,為什麼這麼漂亮?
TAG:EXCEL精英 |
※Excel函數公式:萬能查找函數Lookup的神應用和技巧
※Google 開源 ClusterFuzz:使得查找錯誤並修復錯誤變得異常簡單
※SQL Server中LIKE %search_string% 索引查找(Index Seek)淺析
※快速查找本地和在線資料庫的exploits-Findsploit
※快速查找3D asset,Unity推出3D內容搜索引擎Visual Search
※使用BurpSuite的Collaborator查找.Onion隱藏服務的真實IP地址
※linux下常用的五種查找命令
※linux中用locate查找文件
※Google 要為 Android 的查找設備功能提供室內定位
※在 Linux 中如何從命令行查找 VirtualBox 版本
※文件查找命令之locate,find
※Facebook可通過手機號查找賬號,而且沒有關閉選項
※民警用「天眼」查找罌粟種植 誤差不超過5米一查一個準
※Linux下的文件查找
※Firefox 62將使用DNS over HTTPS技術終結DNS查找明碼風險
※SQL Servere 通過LIKE在另一個字元串中查找字元串
※F5做負載均衡時,如何通過修改cookie查找敏感信息
※蘋果好貼心!開發新App,取代「查找我的iPhone」
※蘋果將用新App取代「查找我的iPhone」功能
※三星官微回應華為P20 Pro吊打S9+:對不起 無法查找到F1.8光圈