|
VLOOKUP，應該是Excel里使用頻率僅次于SUM函數(shù)的吧…… 所以，這里優(yōu)先介紹一下VLOOKUP函數(shù)的基本使用方法，以及常見錯誤的簡單解析。 VLOOKUP主要的功能是什么呢，什么情況下用這個傳說中的函數(shù)？ VLOOKUP在Excel里，屬于查找/索引函數(shù)，就是根據(jù)你提供的條件，去數(shù)據(jù)列表/數(shù)據(jù)庫里把相對應的東西查找并返回來，這也說明一點，那就是使用這個函數(shù)的前提是必須有可以能用來查找的數(shù)據(jù)列表或者數(shù)據(jù)庫。 深刻了解了這一點，以后有需要查找的問題，首先就要想到VLOOKUP函數(shù)，同類的查找函數(shù)還是LOOKUP、HLOOKUP，而VLOOKUP是Vertical_LOOKUP的簡寫，HLOOKUP是Horizontal_LOOKUP的簡寫，以區(qū)分他們不同的使用環(huán)境和情況，常用還是VLOOKUP。 看圖說話，如下圖，A、B、C、D列是我們預先做好的數(shù)據(jù)列表（數(shù)據(jù)庫），現(xiàn)在我們希望在F4單元格里輸入名稱，然后右邊的G4、H4單元格就能自動返回該名稱相對應的CODE、PART。
如下，G4單元格只需要將下面的公式復制并粘貼進去，G4單元格就可以顯示“AT002”了：
------------------- 語法介紹：下面我們詳細分析一下VLOOKUP函數(shù)的語法和各個參數(shù)應該注意的內(nèi)容。
如上為VLOOKUP函數(shù)的語法規(guī)則，我們用什么函數(shù)，都必須以其語法規(guī)則為準則書寫對應的參數(shù)，而不是按自己想當然的方式把自己認為可以的東西寫到參數(shù)里去。 如上語法，以逗號為分隔，VLOOKUP函數(shù)總共有4個參數(shù)： lookup_value，這是第一參數(shù)，表示需要查找的內(nèi)容。 就像我們前面講的例子，F(xiàn)4單元格的“NAME”，就是我們需要在數(shù)據(jù)列表或數(shù)據(jù)庫中查找的內(nèi)容。 table_array，這是第二參數(shù)，表示的是你要從中匹配查找并返回結(jié)果的數(shù)據(jù)區(qū)域。 上例中我們使用的是B:D，表示B列到D列的全部區(qū)域，這是比較簡單快捷的寫法，而負責任一點的寫法，應該是$B$2:$D$13，因為這個才是我們要查找并返回結(jié)果的有效數(shù)據(jù)區(qū)域，如果數(shù)據(jù)區(qū)域下方還有別的數(shù)據(jù)，那最好還是使用后面用具體區(qū)域的寫法。 這個參數(shù)里有兩點必須注意的： 可能你看不明白，這樣來說吧，我們例子要找的是“NAME”，那么第二參數(shù)里指定的區(qū)域，必須以NAME那一列為第一列，雖然我們的數(shù)據(jù)庫是A列到D列，但我們在參數(shù)里只能寫B(tài):D，而不能寫A:D，因為A:D的第一列是“NO.”，不是我們要查找的“NAME”…… 如果我們現(xiàn)在想根據(jù)“NO.”返回后面對應的“NAME”，那就可以以A:D作為數(shù)據(jù)區(qū)域。 這里也引申出一點，就是VLOOKUP只能從左往右查找，如果想從右往左查找，則需要另外構(gòu)造數(shù)據(jù)源或者換函數(shù)，新人們使用過程中要遵循這一點，不要想當然了。。 col_index_num，這是第三參數(shù)，表示你要返回結(jié)果的值（的字段），在你第二參數(shù)里指定的數(shù)據(jù)區(qū)域中的第幾列。 這個很好理解，例子里我們寫的是“2”，因為以NAME為第一列，CODE則是B:D區(qū)域中的第2列，那么后面H4單元格的公式，我們要返回的是PART，你就應該知道這個公式應該怎么修改了吧？ 有時候可能你會看到別人用COLUMN()放在這個參數(shù)里，這又表示什么意思呢？（作為思考題吧） [range_lookup]，這是第四參數(shù)，表示的是要在數(shù)據(jù)庫中查找數(shù)據(jù)的匹配方式，有兩種參數(shù)可以選擇，TRUE和FALSE，其中True表示模糊查找，F(xiàn)alse表示精確查找。 1>. 這個參數(shù)使用了中括號，表示該參數(shù)是可省略的，而省略的時候，默認使用TRUE，也就是模糊查找方式； 2>. True和False我們簡單描述是模糊與精確的關系，但實際上使用True時，要求你要匹配的列，必須是按“升序”排列好的，比如說上例中如果我們最后的參數(shù)用的是True，那么B列的NAME就首先要按從小到大排序好，不然就匹配不到結(jié)果，可能結(jié)果正確但那也只是碰巧。 而False則沒有排序的這個要求，你的數(shù)據(jù)可以是亂序的，因此通常情況下，我們使用的都是False。 ------------------------- 加個小插曲，如下圖，先看看下面這5個公式，其區(qū)別在于第四參數(shù)，你能辨識她們各自代表的查找方式是哪一種嗎？她們又有什么區(qū)別呢？ 首先，先科普一個小知識，在Excel表格中（與VBA的定義有區(qū)別），False能轉(zhuǎn)化為數(shù)值0，而True能轉(zhuǎn)化為數(shù)值1，所以你可以用0表示False，用非0的數(shù)值表示True。以此作為背景，我們再來解釋上面這5個公式函數(shù)的含義和區(qū)別，這個也經(jīng)常應用在其他很多公式參數(shù)的書寫上。 首先，公式1、2、3這三個公式代表的含義和查找方式是一樣的，也就是FALSE的精確查找方式； 后面，公式4、5兩個公式，使用的則是TRUE的模糊查找方式。 語法介紹完了，示例公式里為什么那樣寫也基本上解讀完了，現(xiàn)在，你試一下自己書寫H4單元格的公式吧！ 有興趣可以用你自己書寫的公式回復這條博客哦…… ------------------------------ 錯誤處理：下面我們說說常見的一些錯誤，并按下面羅列的可能原因按順序排查即可： 1.
#N/A 2. #REF! - “引用無效錯誤”，表示你第三參數(shù)指定要返回的第幾列，壓根就沒包括在你第二參數(shù)指定的區(qū)域里 -------------------------- 錯誤屏蔽：某些情況下，我們寫的公式檢查沒有錯，但數(shù)據(jù)列表里確實不存在要查找的值，那就鐵定會返回#N/A錯誤，畢竟我們都喜歡整潔，不想表格里零零落落分布著#N/A錯誤，那可怎么辦呢，有沒有辦法在公式里屏蔽這樣的錯誤呢？ 答案是肯定的，一般人會直接教你用=IF(ISERROR(原公式),"",原公式)，或者=IF(ISNA(原公式),"",原公式)，這什么意思呢？就是用ISERROR函數(shù)，判斷你“原公式”返回的結(jié)果是不是錯誤值，如果是就顯示""（空值），也就是什么都不顯示，如果沒有錯誤，就顯示原公式的結(jié)果。 拿上面的例子來說，我們處理后的結(jié)果應該是： =IF(ISNA(VLOOKUP(F4,B:D,2,FALSE)),"",VLOOKUP(F4,B:D,2,FALSE)) 當然，我會覺得這樣的公式太長，所以一般會教人使用=IF(COUNTIF(),原公式,"")，這種方式樣寫出來的公式就是： =IF(COUNTIF(B:B,F4),VLOOKUP(F4,B:D,2,FALSE),"") 而自從07版新增IFERROR函數(shù)之后，我們在xlsx格式的文檔中寫容錯公式就可以更簡潔了，直接寫為： =IFERROR(原公式,"") 如：=IFERROR(VLOOKUP(F4,B:D,2,FALSE),"") ---------------------------- 另外說一下，源數(shù)據(jù)里NO.5和NO.12的NAME是一樣的，如果我們同樣使用“Michel”來查詢后面的值，返回的只會是排在前面的NO.5的記錄哦，這一對一查找也算是VLOOKUP的一個特性，因此用VLOOKUP查找時有多種查詢關鍵值時最好是使用如號碼、證件號等有唯一性的關鍵詞，而姓名、地區(qū)等可能有重名的則不能為首選，不然會徒勞無功哦。 至于一對多的查找，那需要用復合函數(shù)嵌套，如典型的INDEX+IF+SMALL+MATCH+ROW ~~~ |
|
|
(2013-11-19 21:04:51)
