活用Vlookup和數(shù)據(jù)透視表

1、活用Vlookup和數(shù)據(jù)透視表 Excel是大家都非常熟悉的軟件，本文與大家分享一下如何活用Vlookup和數(shù)據(jù)透視表這兩個(gè)易學(xué)易用的功能，可以將原本平面的分散的多個(gè)數(shù)據(jù)表集中起來(lái)，使之變成“立體”的數(shù)據(jù)庫(kù)并且可以隨意“旋轉(zhuǎn)”，以達(dá)到及時(shí)準(zhǔn)確的多角度分析的需要。一、現(xiàn)實(shí)需求許多朋友可能會(huì)有這樣的經(jīng)歷，剛給老板做完按銷售部門的全年按月銷售趨勢(shì)報(bào)表，老板突然就想再看看，按客戶類型的銷售趨勢(shì)是怎樣的？剛把按客戶類型的銷售趨勢(shì)報(bào)給老板之后，老板可能馬上就會(huì)打電話說(shuō)還想看看按產(chǎn)品類型的銷售趨勢(shì)是怎么樣的？之后，可能又會(huì)需要按員工性別的銷售趨勢(shì)、按員工年齡段的銷售趨勢(shì)等等，換句話說(shuō)從涉及的員工、

2、客戶、產(chǎn)品的每一個(gè)特點(diǎn)都有可能成為老板關(guān)注的對(duì)象，但這幾個(gè)方面的排列組合就可以產(chǎn)生出無(wú)數(shù)的報(bào)表。 是老板的主意變得太快還是我們做報(bào)表的速度太慢呢？大家都清楚這樣數(shù)據(jù)分析會(huì)對(duì)決策起著非常重要的支持作用，而且數(shù)據(jù)提供的速度越快越及時(shí)，所起的效用就越大。如果數(shù)據(jù)能夠像我們看展覽品一樣，老板需要看哪個(gè)角度，分析人員馬上就將那個(gè)角度轉(zhuǎn)給老板看，那將是多么愜意的事情啊，老板不僅及時(shí)得到了所要的報(bào)告，分析人員的工作效率也得到較大的提高。以前與同事提出過(guò)這個(gè)想法，有的同事說(shuō)，這是在講一個(gè)神話吧？通過(guò)多年的實(shí)踐總結(jié)發(fā)現(xiàn)，只要活用了Excel的兩個(gè)功能，這個(gè)“神話”就很快變成了人人都能夠很快學(xué)會(huì)的現(xiàn)實(shí)。以下通過(guò)一

3、個(gè)實(shí)例來(lái)介紹這個(gè)“神話”實(shí)現(xiàn)過(guò)程和所用的功能。二、實(shí)例介紹本實(shí)例以常見三個(gè)表為例子來(lái)介紹如何讓三個(gè)圖的數(shù)據(jù)旋轉(zhuǎn)起來(lái)？簡(jiǎn)單的報(bào)表會(huì)用之后，大家只要按同一思路，翻閱相關(guān)專業(yè)書籍，那么不管多么復(fù)雜的報(bào)表還是多個(gè)不同的報(bào)表都會(huì)達(dá)到讓我們?nèi)我庑D(zhuǎn)的要求。對(duì)于這三張圖，老板可能會(huì)從員工的部門、性別，客戶的城市、行政區(qū)等各個(gè)角度來(lái)對(duì)銷售情況進(jìn)行分析，換一句話來(lái)說(shuō)，員工表和客戶表中的任何一列都可能會(huì)成為對(duì)銷售進(jìn)行分析的一種情況，那么作為分析人員，我們?nèi)绾蝸?lái)及時(shí)準(zhǔn)確的來(lái)滿足這一需求呢？在利用數(shù)據(jù)透視表功能將數(shù)據(jù)旋轉(zhuǎn)之前我們先來(lái)看一下如何利用Vlookup的功能將分散的數(shù)據(jù)集中到一個(gè)表中。三、組織數(shù)據(jù)：活用Vlo

4、okup使相關(guān)數(shù)據(jù)向核心數(shù)據(jù)集中大家知道，有一個(gè)事實(shí)是無(wú)法改變的，那就是Excel是一個(gè)兩維的空間表，做出的結(jié)果也必然體現(xiàn)在這個(gè)兩維的空間表上。為了更好的利用Excel 的數(shù)據(jù)透視表功能將數(shù)據(jù)旋轉(zhuǎn)起來(lái)，我們首先需要將這些數(shù)據(jù)內(nèi)容集中到一個(gè)工作表中，然后再針對(duì)集中后的數(shù)據(jù)利用數(shù)據(jù)透視表的功能將數(shù)據(jù)旋轉(zhuǎn)起來(lái)，圖4是我們首先想要達(dá)到的結(jié)果，也就是將銷售流水表的業(yè)務(wù)員和客戶的相關(guān)信息全部集中到銷售流水表中。即使有成千上萬(wàn)條銷售記錄，我們?nèi)绾螌?shí)現(xiàn)快速的將這些信息集中到一起呢？Excel提供的Vlookup函數(shù)可以幫助我們?cè)趲追昼娭畠?nèi)就完成數(shù)據(jù)的集合功能。許多朋友特別是非理工科的朋友一聽到函數(shù)，就感到有點(diǎn)

5、高深莫測(cè)，其實(shí)函數(shù)并沒(méi)有那么神秘，我們只要能夠很快學(xué)會(huì)家用電器是怎么使用的，那么學(xué)會(huì)函數(shù)的使用也會(huì)同樣的容易。舉個(gè)使用“自動(dòng)洗衣機(jī)”的例子，大家只要將衣服放到規(guī)定的洗衣桶中，選擇好洗衣模式，確認(rèn)啟動(dòng)之后，就等著將衣服拿出來(lái)就可以了，至于洗衣機(jī)是怎么洗的，我們完全可以不用過(guò)問(wèn)。使用函數(shù)也一樣。圖5是Vlookup的使用實(shí)例，我們一步一步來(lái)揭開它的神秘面紗，看它是不是與使用洗衣機(jī)一樣簡(jiǎn)單？以下是這個(gè)函數(shù)的公式：標(biāo)準(zhǔn)函數(shù)公式：Vlookup（lookup_value，table_array，col_index_num，range_lookup）實(shí)例函數(shù)公式：Vlookup（F2，員工！$B$2：$C

6、$13，2，F(xiàn)ALSE）許多朋友可能一看這么一堆，就感覺很復(fù)雜，開始望而卻步，其實(shí)所謂的參數(shù)也與洗衣機(jī)的按鈕功能是一樣的、我們分別來(lái)看一下： lookup_value：這個(gè)參數(shù)的目的是告訴Excel我們要查什么；實(shí)例中的“2”的位置，是告訴Excel我們要找 “劉天王”；table_array：這個(gè)參數(shù)的目的是告訴Excel我們要在哪里找；實(shí)例中的“員工！$B$2P：$C$13”是告訴Excel我們要在員工表的這些區(qū)域內(nèi)去找“劉天王”。?。盒枰貏e提醒注意的是這個(gè)區(qū)域的第一列必須是我們要找的位置列，也就是姓名列； col_index_num：這個(gè)參數(shù)的目的是告訴Excel在指定區(qū)域內(nèi)找到loo

7、kup_value之后，取該區(qū)域的第幾列的值回來(lái)；實(shí)例中的“”是告訴Excel在這個(gè)區(qū)域找到“劉天王”后，將“劉天王”所屬的部門給取回來(lái)，也就是取第二列的值回來(lái)，即員工表中部門那一列。需要特別注意的是：這個(gè)序號(hào)是從開始數(shù)，在指定區(qū)域內(nèi)按從左到右順序。range_lookup：這個(gè)參數(shù)的目的是告訴Excel如果找不到lookup_value，Excel怎么辦？實(shí)例中“FALSE”是告訴計(jì)算機(jī)精確匹配，也就是當(dāng)Excel在員工表中沒(méi)有找到“劉天王”時(shí)返回錯(cuò)誤值。強(qiáng)調(diào)建議使用這個(gè)函數(shù)的時(shí)候全部使用“FALSE”這個(gè)參數(shù)值，以便使你及時(shí)發(fā)現(xiàn)問(wèn)題，使分析更有效。當(dāng)我們將這幾個(gè)設(shè)置指定給Vlookup之后

8、，回車之后，Excel就會(huì)自動(dòng)將“劉天王”所屬的部門“業(yè)務(wù)二部”從員工表中找出來(lái)放到相應(yīng)的單元格內(nèi)，利用同樣的方法，我們就可以很快的將需要做分析的相關(guān)員工客戶信息從各自的表中取出來(lái)集中到我們想要分析的報(bào)表中來(lái)。其實(shí)總結(jié)起來(lái)，我們的目的是想將銷售流水表中每一行中員工對(duì)應(yīng)的所屬部門集中到這張表中，Vlookup所起到的作用就是我們只要告訴要找哪一列的值（流水表的員工列），然后在哪里找（員工表這個(gè)數(shù)據(jù)表），找到后返回什么值（員工表的部門列），找不到怎么辦（alse）這幾個(gè)條件之后，剩下的工作就全部由Excel來(lái)完成就可以了，Excel會(huì)精確的將對(duì)應(yīng)員工的部門給選擇出來(lái)。！快速填充公式提示：當(dāng)鼠標(biāo)移動(dòng)

9、到單元格右下角的時(shí)候，即鼠標(biāo)變成一個(gè)小十字的時(shí)候，雙擊鼠標(biāo)左鍵就自動(dòng)填充到列末的位置在完成了如何將相關(guān)數(shù)據(jù)集中起來(lái)之后，那么我們?nèi)绾螌?shí)現(xiàn)將這些數(shù)據(jù)“旋轉(zhuǎn)起來(lái)”呢？以下我們將詳細(xì)介紹一下數(shù)據(jù)透視表的功能。 四、數(shù)據(jù)旋轉(zhuǎn)活用數(shù)據(jù)透視表有許多朋友一看到數(shù)據(jù)透視表的那么多功能，就開始望而卻步，繼續(xù)使用Excel的初級(jí)功能來(lái)完成工作，其實(shí)數(shù)據(jù)透視表類似于“堆積木“，也就是將我們需要的內(nèi)容堆積到相應(yīng)的位置基本上就可以完成分析的目的。在使用數(shù)據(jù)透視表之前，我們先想一下我們最終想要的報(bào)表格式，Excel本身特點(diǎn)決定了報(bào)表的格式是由三個(gè)要素組成的，行、列、數(shù)據(jù)。以部門銷售趨勢(shì)分析為例，表1是我們需要的最終格式。

10、如果行列的顯示，我們可以任意拖動(dòng)數(shù)據(jù)表的項(xiàng)目進(jìn)去，里面的數(shù)據(jù)隨著行列項(xiàng)目的不同而進(jìn)行調(diào)整，那么我們的數(shù)據(jù)不就旋轉(zhuǎn)起來(lái)了嗎？以下我們介紹一下數(shù)據(jù)透視表是如何幫助我們實(shí)現(xiàn)這一功能的：第一步：首先打開剛才做好的數(shù)據(jù)表，用鼠標(biāo)單擊數(shù)據(jù)清單上的任意單元格。第二步：選擇數(shù)據(jù)（），執(zhí)行子菜單數(shù)據(jù)透視表和圖表報(bào)告（）菜單項(xiàng)命令，進(jìn)入“數(shù)據(jù)透視表和數(shù)據(jù)透視圖表向?qū)А钡牟襟E一界面，如圖6：第三步：我們按默認(rèn)設(shè)置，在“所需創(chuàng)建的報(bào)表類型”中選中“數(shù)據(jù)透視表”單選按鈕，然后按“下一步”按鈕。第四步：“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А钡牟襟E二要求指定要建立的數(shù)據(jù)透視表報(bào)表所基于的數(shù)據(jù)區(qū)域。如果在進(jìn)入該向?qū)е埃銢](méi)有打開一

11、覽表，可按“瀏覽”按鈕選擇它所在的工作簿；如果進(jìn)入該向?qū)е耙呀?jīng)打開該工作簿并且單擊了其上的任意單元格，則Excel將自動(dòng)選中整個(gè)數(shù)據(jù)清單，如圖7：第五步：進(jìn)入向?qū)У牟襟E三，如果你希望把數(shù)據(jù)透視表報(bào)表顯示在和源數(shù)據(jù)不同的工作表上，可選中“新建工作表”單選按鈕；如果你希望把透視表建立在源數(shù)據(jù)所在的工作表上，則選中“現(xiàn)有工作表”單選按鈕，按該單選按鈕下輸入域后的“壓縮對(duì)話框”圖標(biāo)進(jìn)入工作表，在工作表上選定顯示數(shù)據(jù)透視表的單元格區(qū)域。如圖8：第六步：你可以在向?qū)У牟襟E三中按“布局”按鈕進(jìn)入如圖9所示界面，設(shè)置數(shù)據(jù)透視表報(bào)表的布局。大家看這就是Excel給我們提供的拖拉積木的平臺(tái)：第七步：把右邊我們集

12、中后數(shù)據(jù)表的列名稱拖放到我們想要的行、列、數(shù)據(jù)位置后，你還可以用鼠標(biāo)雙擊字段的名稱進(jìn)行更高級(jí)的設(shè)置。我們以部門銷售趨勢(shì)分析為例，如圖10：我們將“部門”拖到“行”的位置，將“銷售日期”拖到“列”的位置，將“銷售金額”拖到“數(shù)據(jù)”的位置。我們看一下按“確定”、“完成”之后的結(jié)果是怎么樣的？第八步：圖11是“確定”之后的結(jié)果：我們發(fā)現(xiàn)數(shù)據(jù)已經(jīng)按照部門進(jìn)行匯總了，但是銷售日期卻是按天進(jìn)行匯總，并不是我們想要的按月進(jìn)行匯總的結(jié)果。這個(gè)問(wèn)題Excel 早已經(jīng)幫我們想好了，我們點(diǎn)擊“銷售日期”按右鍵，選擇“組及顯示明細(xì)數(shù)據(jù)”及下拉菜單“組合”，就會(huì)出現(xiàn)“分組”的小窗口。第九步：日期分組示例（見圖12）：大家可以看到Excel提供了按月、按季度以