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

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

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

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

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

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

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

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

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

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

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

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

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