毫不夸張地說，99%天天和Excel打交道的人，他們所掌握的Excel知識(shí)量不到總體的5%，也就是說還有95%的知識(shí)點(diǎn)并沒有掌握。這不是危言聳聽，這是我這幾年數(shù)據(jù)分析培訓(xùn)中觀察的結(jié)果。大部分的Excel使用者，每天在用最低級(jí)的知識(shí)處理著各種復(fù)雜的數(shù)據(jù)分析問題，分析要有效率只是一種傳說。
不信我們就來測試一下，用一個(gè)最大眾化的函數(shù)Vlookup來做測試，別瞧不起這個(gè)初階函數(shù)，國外有個(gè)小哥還專門給這個(gè)函數(shù)寫了一本書，可見這個(gè)函數(shù)簡約而不簡單。
于是我就琢磨了個(gè)題考考大家函數(shù)水平，看你在幾段：
一段：會(huì)簡單的vlookup函數(shù)的使用
二段：會(huì)vlookup+column函數(shù)的嵌套使用
三段：會(huì)vlookup+match函數(shù)的嵌套使用
四段：會(huì)vlookup的模糊匹配使用
五段：會(huì)vlookup+offset+match的高階嵌套使用
相信大部分人在一段或者段外徘徊，vlookup函數(shù)基本上是使用頻率最高的一個(gè)函數(shù)，這個(gè)函數(shù)不會(huì)使用的話，基本上就算是不會(huì)函數(shù)了。只會(huì)sum或count這種函數(shù)的朋友自動(dòng)面壁去，下面的描述你基本看不懂哈。
很多表哥表妹常說這些函數(shù)都會(huì)，但是組合在一起就不會(huì)了。確實(shí)，函數(shù)的嵌套是最難的，不光難在技術(shù)，最關(guān)鍵是邏輯，很多時(shí)候是我們自己想不到這樣取巧的使用而自己打敗了自己。
別慌，今天我給大家上堂干貨課程，分享給你辦公室的每個(gè)表哥表姐表弟表妹們，讓他們都學(xué)會(huì)。謙虛的說，這樣你們的辦公效率至少會(huì)提高一倍吧。
一段：vlookup的基本用法
vlookup是一個(gè)縱向查找函數(shù)（從左往右查），官方的語法規(guī)則是這樣的：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。翻譯成中文就是：查找（一個(gè)值，這個(gè)值所在的區(qū)間，它位于第幾列，精準(zhǔn)匹配還是模糊匹配）
圖1
lookup_value：可以是一個(gè)值、日期或文本等。如你查詢上圖中的“城市”
table_array：查詢值所處的區(qū)域，對(duì)于上圖就是A1:H11這個(gè)范圍，強(qiáng)烈推薦區(qū)域改成A:H這種寫法，好處是當(dāng)添加新數(shù)據(jù)源時(shí)不用更改公式。
col_index_num：查詢的數(shù)據(jù)處于第幾列，比如要查“完成率”這個(gè)值就是4，查“銷售數(shù)量”就是6。
range_lookup：0為精準(zhǔn)匹配，就是查詢對(duì)象必須長得一模一樣，少根汗毛都不行。一般情況都要求精準(zhǔn)匹配，如果這個(gè)值省略這是模糊匹配（見vlookup四段的用法）
舉例說明：
公式=VLOOKUP('上海',A:H,5,0)
查找“上?！彼诘牡谖辶袛?shù)據(jù)，要求精準(zhǔn)匹配。這個(gè)公式生成的結(jié)果是718。
注：“上?！笨梢允遣樵冎邓幍膯卧?，如果“上?！痹贙2單元格，則公式可以改成：
公式=VLOOKUP(K2,A:H,5,0)
K2中如果是“成都”，結(jié)果則是659，如果是“雄安”，結(jié)果則是668。
Vlookup是非常好的數(shù)據(jù)查找函數(shù)，很方便的把處于不同地方的數(shù)據(jù)匹配到指定的地方，其中關(guān)鍵點(diǎn)就是數(shù)據(jù)查詢的區(qū)域，這個(gè)區(qū)域可以是不同的區(qū)域，不同的工作簿，不同的工作表。
拓展知識(shí)點(diǎn)：
Vlookup家族還有Hlookup，Lookup。
二段：Vlookup+Column
當(dāng)我們需要用Vlookup匹配多列數(shù)據(jù)的時(shí)候，往往需要手動(dòng)去更改公式中的第3個(gè)值（就是col_index_num），但是匹配對(duì)象太多的情況下，手動(dòng)修改其實(shí)是非常沒有效率并且非?？啾频囊患拢@個(gè)時(shí)候column函數(shù)可以解放你們。
相信大部分會(huì)vlookup的人，現(xiàn)在還是傻傻的手動(dòng)在改這個(gè)參數(shù)，說的就是你。
COLUMN(reference)
返回reference所在單元格所處的列號(hào)，如果A1就是1（第1列），B25就是2（第2列），H2就是8（第8列），這三個(gè)公式分別為COLUMN(A1)，COLUMN(B25)，COLUMN(H2)。如果reference為空則返回當(dāng)前單元格的列號(hào)。
圖2
上圖就是在L2單元格寫好公式后直接往后拉這個(gè)公式就可以直接匹配出其它6個(gè)值，不用手動(dòng)將第3個(gè)參數(shù)分別改成3,4,5......，因?yàn)榈谌齻€(gè)值自動(dòng)復(fù)制成COLUMN(C1)，COLUMN(D1)，COLUMN(E1)......
高效不？就是這么簡單，小函數(shù)有大用途。
拓展知識(shí)點(diǎn)：
與column（reference）函數(shù)對(duì)應(yīng)的是row（reference），試試看。
三段：Vlookup+match
Vlookup和match函數(shù)組合是V函數(shù)的標(biāo)準(zhǔn)用法，與column函數(shù)一樣的功效，match函數(shù)的作用也是用來改變第三個(gè)參數(shù)值。
MATCH(lookup_value, lookup_array, match_type)
M函數(shù)是返回指定數(shù)值在指定數(shù)組區(qū)域中的位置，生成的是位置而不是V函數(shù)中位置所處的值，這是二者的區(qū)別。match_type如果是0則為精準(zhǔn)匹配，省略則為模糊匹配，一般都是用0進(jìn)行精準(zhǔn)匹配。
例如我們使用上面圖1中的數(shù)據(jù)源，公司如下：
公司=MATCH('完成率',B1:H1,0)
返回值為3，因?yàn)椤巴瓿陕省边@個(gè)指標(biāo)是在B1:H1這個(gè)區(qū)域的第3個(gè)值，如果查詢“進(jìn)店顧客數(shù)”則返回7。所以M函數(shù)可以用來查詢指定對(duì)象所處的位置，和V函數(shù)組合威力巨大，基本上可以兩個(gè)查詢值的無死角匹配。
圖3
圖3中嵌套公式寫在了V2單元格，U2和V1單元格是可以修改“城市”和“查詢指標(biāo)”的地方，V2單元格將生成對(duì)應(yīng)的查詢值，修改U2和V1的值即可以查到對(duì)應(yīng)的數(shù)據(jù)。
V+M函數(shù)組合是非常靈活的查詢函數(shù)，是E界必備之效率嵌套用法。
四段：Vlookup的模糊匹配
從技術(shù)層面來講，這個(gè)V函數(shù)的用法大概處于二段水平，但是從數(shù)據(jù)分析業(yè)務(wù)場景來說，我更愿意把它放在四段，因?yàn)檫@種應(yīng)用解決了好幾個(gè)業(yè)務(wù)場景的實(shí)際使用。
比如將商品價(jià)格分成低中高三段，將員工年齡分成青年、中年、老年等，將員工工齡分成4段等等場景。
如下圖，通過每個(gè)商品的價(jià)格，自動(dòng)匹配出來它處于的'價(jià)格段'和'價(jià)格描述'兩個(gè)字段，有了這兩個(gè)字段后，再用數(shù)據(jù)透視表做分析就so easy了。
圖4
要實(shí)現(xiàn)這樣的功能，首先需要建立一個(gè)自定義的分段標(biāo)準(zhǔn)，沒有標(biāo)準(zhǔn)鬼才知道你應(yīng)該歸位到哪兒。知識(shí)點(diǎn)來了：
圖5
這里的價(jià)格節(jié)點(diǎn)可以自定義修改，修改后在圖4的對(duì)應(yīng)位置就可以自動(dòng)生成對(duì)應(yīng)的價(jià)格段。自定義的知識(shí)點(diǎn)其實(shí)比較簡單，真正的知識(shí)點(diǎn)是圖4、圖5的數(shù)據(jù)該如何關(guān)聯(lián)在一起？
圖6
單元格C2和D2中的公式就是答案，它利用了vlookup函數(shù)的模糊匹配功能，你可以看到公式中第四個(gè)參數(shù)是缺失的。
拓展知識(shí)點(diǎn)：
透視表的分組功能也可以實(shí)現(xiàn)數(shù)據(jù)的分組，但是是有局限的，透視表只能實(shí)現(xiàn)步長一樣的分段，而V函數(shù)的這種用法則不受這種局限。
五段：Vlookup+offset+match
V+O+M函數(shù)嵌套這種用法一般是大內(nèi)高手才會(huì)的，offset函數(shù)相信很多人聽都沒聽說過，這是一個(gè)相對(duì)高階的函數(shù)。一般的函數(shù)是返回一個(gè)值，而O函數(shù)可以返回一個(gè)區(qū)域，厲害了吧。
反應(yīng)快的朋友應(yīng)該已經(jīng)猜到了O函數(shù)是為了V函數(shù)中的查詢區(qū)域而來的，沒錯(cuò)。前面2-3段是改變第三個(gè)位置參數(shù)，4段是改變了第4個(gè)參數(shù)，這次我們要改變第2個(gè)參數(shù)了。
OFFSET(reference,rows,cols,height,width)
首先O函數(shù)需要和其它組合使用，比如sum，count，vlookup等。這個(gè)函數(shù)相對(duì)比較難，我錄制了一個(gè)sum+offset函數(shù)組合的視頻來演示它的使用。視頻數(shù)據(jù)范例文件來自方驥老師。
（因?yàn)槲⑿拍壳安婚_放高清視頻的認(rèn)證，所以會(huì)有點(diǎn)模糊……）
今天重點(diǎn)不是講O函數(shù)的具體使用（想學(xué)這個(gè)函數(shù)的詳細(xì)使用的同學(xué)可以到微博搜賬號(hào)“數(shù)據(jù)化管理”，然后成為V+會(huì)員后，未來我會(huì)講這個(gè)函數(shù)的詳細(xì)使用），重點(diǎn)是講這V+O+M函數(shù)組合嵌套的強(qiáng)大功能。
簡單說這三個(gè)函數(shù)組合在一起，可以實(shí)現(xiàn)指哪兒就到哪個(gè)區(qū)域去提數(shù)，想查什么指標(biāo)就查什么指標(biāo)，自由度是相當(dāng)?shù)母?，一般?dòng)態(tài)圖表就會(huì)用到這個(gè)。我再給你看看我的一個(gè)月分析模板，使用這個(gè)功能后強(qiáng)大的動(dòng)態(tài)圖表效果視頻：
（因?yàn)槲⑿拍壳安婚_放高清視頻的認(rèn)證，所以有點(diǎn)模糊……本視頻你可以復(fù)制網(wǎng)址到瀏覽器中打開看高清版本：https://v.qq.com/x/page/x0700of87h1.html）
看到這兒，你還覺得自己是熟練使用vlookup函數(shù)了嗎？
Excel學(xué)習(xí)建議：
想學(xué)習(xí)動(dòng)態(tài)圖表的朋友，建議可以跟Excel圖表大神-劉萬祥老師學(xué)習(xí)，他在這方面已沉淀多年，總結(jié)了很多實(shí)用的經(jīng)驗(yàn)，教過的學(xué)員10000+，廣受好評(píng)！（學(xué)完讓你有種脫胎換骨的感覺~）
聯(lián)系客服
