這個(gè)問題是針對(duì)Excel普通用戶來說的。如果你是Excel函數(shù)的超級(jí)FANS，那么這個(gè)問題的答案是顯然的。對(duì)于那些解決任何問題都想使用函數(shù)的同學(xué)，顯然也并不想拋棄VLOOKUP函數(shù)。但是，對(duì)于那些只想更方便的處理和分析數(shù)據(jù)的同學(xué)來說，可能VLOOKUP并不是那么必要了。
其實(shí)，我本人和我們公司，都是VLOOKUP函數(shù)的忠實(shí)擁躉。在所有的Excel課上，我都會(huì)大家強(qiáng)調(diào)，VLOOKUP和IF是Excel的兩個(gè)最重要和最有用的函數(shù)。如果只能選擇兩個(gè)函數(shù)學(xué)習(xí)的話，毫無疑問就是這兩個(gè)。
在我們公司提出的“Excel工作的標(biāo)準(zhǔn)模式”中，VLOOKUP函數(shù)占據(jù)了一個(gè)很重要的位置。它是用來創(chuàng)建標(biāo)準(zhǔn)的源數(shù)據(jù)表的基礎(chǔ)。
例如，我們有一份銷售數(shù)據(jù)：
對(duì)這個(gè)銷售數(shù)據(jù)進(jìn)行分析，大部分工作都可以通過數(shù)據(jù)透視表來完成（這也是Excel工作的標(biāo)準(zhǔn)模式的建議和要求），工作效率極高。
但是，如果我們希望來分析每個(gè)類別的產(chǎn)品的銷售情況時(shí)，這個(gè)表的數(shù)據(jù)缺少大類信息，而大類信息存在另外一個(gè)表上：
這個(gè)針對(duì)大類的分析基于兩個(gè)表，在以前，很多同學(xué)就會(huì)使用函數(shù)來完成這個(gè)工作，這是個(gè)效率較低的半手工方法。在Excel工作的標(biāo)準(zhǔn)模式中，我們建議同學(xué)維護(hù)一個(gè)標(biāo)準(zhǔn)的完整的源數(shù)據(jù)表，通過Vlookup函數(shù)在銷售數(shù)據(jù)表中添加一個(gè)大類列：
這樣，我們就可以使用透視表來完成各種針對(duì)大類的分析工作了。
這個(gè)方法確實(shí)可以極大的提高數(shù)據(jù)處理，分析以及報(bào)表制作的工作效率，減少人為失誤的發(fā)生。但是，不得不說，這個(gè)方法有一個(gè)薄弱環(huán)節(jié)，那就是VLOOKUP公式了。
在每天學(xué)員咨詢的問題中，關(guān)于VLOOKUP函數(shù)來的問題占了很大的比例。對(duì)于普通的Excel使用者來說，這個(gè)函數(shù)是一個(gè)不小的挑戰(zhàn)。
另外，如果我們還有其他的數(shù)據(jù)表需要關(guān)聯(lián)銷售數(shù)據(jù)做分析的話（比如，客戶類別，銷售代表部門，等等），就需要做多個(gè)輔助列，這樣的話，這個(gè)大而全的“源數(shù)據(jù)”表的公式就會(huì)有點(diǎn)多，很可能造成了計(jì)算的困難。
而且，在“Excel工作的標(biāo)準(zhǔn)模式”中，后面的所有環(huán)節(jié)都是通過數(shù)據(jù)透視表自動(dòng)完成的，一旦建立，以后的工作只需要刷新就行了。但是在這些公式的步驟中，原始數(shù)據(jù)變化時(shí)，我們始終需要去維護(hù)這些公式，總是不太完美。
我們需要一個(gè)比較好的方式來完成這個(gè)工作。
選中銷售數(shù)據(jù)表的任意單元格，在“數(shù)據(jù)”選項(xiàng)卡中，點(diǎn)擊“從表格”，
在Power Query編輯器中，點(diǎn)擊“主頁(yè)”選項(xiàng)卡的“關(guān)閉并上載至”：
在對(duì)話框中選擇“僅創(chuàng)建連接”：
點(diǎn)擊加載，完成查詢的創(chuàng)建。
選中大類表數(shù)據(jù)區(qū)域的任意單元格，同樣在“數(shù)據(jù)”選項(xiàng)卡中點(diǎn)擊“從表格”：
在Power Query編輯器中的“主頁(yè)”選項(xiàng)卡中，點(diǎn)擊“合并查詢”，選擇 “將查詢合并為新查詢”：
在對(duì)話框中，將上面的表格選定為表1（銷售數(shù)據(jù)表），下面的表格選定為表2（大類表），將聯(lián)接種類選擇為“左外部”：
在上下兩個(gè)表中分別點(diǎn)擊“產(chǎn)品名稱”列（關(guān)聯(lián)列）：
點(diǎn)擊確定，得到表格：
點(diǎn)擊“表2”列的右側(cè)按鈕，去掉產(chǎn)品名稱前面的勾選，去掉“使用原始列名作為前綴”：
點(diǎn)擊確定，得到合并結(jié)果：
點(diǎn)擊“主頁(yè)”選項(xiàng)卡中的“關(guān)閉并上載”，在Excel中插入新的工作表，并得到了結(jié)果表：
于是，我們不用VLOOKUP函數(shù)就得到了一個(gè)結(jié)果源數(shù)據(jù)表，你可以基于這個(gè)表格使用數(shù)據(jù)透視表進(jìn)行各種分析工作，制作各種分析報(bào)表。整個(gè)過程只要幾下鼠標(biāo)的點(diǎn)擊即可。而且，以后源數(shù)據(jù)發(fā)生了變化（銷售數(shù)據(jù)或者大類數(shù)據(jù)），你需要做的所有工作就是點(diǎn)擊一下鼠標(biāo)：刷新。
但是，我們其實(shí)還有更好的選擇。
在“Excel工作的標(biāo)準(zhǔn)模式”中，我們之所以需要一個(gè)完整的源數(shù)據(jù)表的存在，是由于在傳統(tǒng)的Excel框架下，數(shù)據(jù)透視表沒有辦法分析多個(gè)表格。因此，我們需要使用函數(shù)（VLOOKUP是主力函數(shù)之一），將多個(gè)表關(guān)聯(lián)成一個(gè)大而全的源數(shù)據(jù)表。在上面的方法中，我們使用Power Query取代了VLOOKUP函數(shù)。但是，實(shí)際上，現(xiàn)在我們有了Power Pivot，只要所有的表格符合源數(shù)據(jù)表規(guī)范，我們就可以直接用來分析，沒有必要采取這個(gè)多余的步驟。
首選，選中銷售數(shù)據(jù)表區(qū)域的任意單元格，在“Power Pivot”選項(xiàng)卡中點(diǎn)擊“添加到數(shù)據(jù)模型”：
會(huì)打開“Power Pivot for Excel”，不用管這個(gè)窗口，回到Excel窗口，選擇大類數(shù)據(jù)區(qū)域任意單元格，將大類表添加到數(shù)據(jù)模型：
在Power Pivot for Excel中的“設(shè)計(jì)”選項(xiàng)卡中，點(diǎn)擊“創(chuàng)建關(guān)系”：
在創(chuàng)建關(guān)系對(duì)話框中，將左表選擇為“表1”（銷售數(shù)據(jù)表），右表選擇為“表2”（大類表），分別點(diǎn)擊兩個(gè)表的“產(chǎn)品名稱”列（關(guān)聯(lián)列）：
點(diǎn)擊確定，完成關(guān)系的創(chuàng)建。
在“主頁(yè)”選項(xiàng)卡中，點(diǎn)擊“數(shù)據(jù)透視表”：
在對(duì)話框中選擇“新工作表”：
點(diǎn)擊確定后，得到透視表：
在右側(cè)透視表面板中，展開表2，將產(chǎn)品大類拖拽到行字段：
展開表1，將數(shù)量添加到值字段：
于是我們就得到了基于大類的銷量分析：
Power Pivot的出現(xiàn)，使得我們維護(hù)一個(gè)大而全的源數(shù)據(jù)表不再是個(gè)必要的事情了。相反，通過在表之間創(chuàng)建關(guān)系，就可以利用Power Pivot分析多個(gè)表中的數(shù)據(jù)。這就極大的減輕了工作量。
當(dāng)然，與之對(duì)應(yīng)的，我們的“Excel工作的標(biāo)準(zhǔn)模式”也需要升級(jí)了。在原來的框架中，“Excel工作的標(biāo)準(zhǔn)模式”中需要一個(gè)大而全的源數(shù)據(jù)表是個(gè)不得已的事情。但是除此之外，其他的要求都是必要和合理的。比如，我們?nèi)匀恍枰@多個(gè)源數(shù)據(jù)表符合規(guī)范，而要做出符合規(guī)范的源數(shù)據(jù)表，就需要借助Power Query了。
這樣，在升級(jí)版的“Excel工作的標(biāo)準(zhǔn)模式”中，我們完全有可能建立一個(gè)從原始數(shù)據(jù)到最終結(jié)果報(bào)表的自動(dòng)化生產(chǎn)線。將各位一直跟Excel和數(shù)據(jù)做艱苦斗爭(zhēng)的同學(xué)從那些繁重的工作中解脫出來。這條生產(chǎn)線甚至還可以連通過郵件或微信發(fā)布報(bào)告這樣的工作也包括進(jìn)來。
聯(lián)系客服
