數(shù)據(jù)透視表操作匯總

1、數(shù)據(jù)透視表的應(yīng)用例實(shí)小吳是一家公司銷售部門的員工，每天要面對很多銷售信息，對這些數(shù)據(jù)統(tǒng)計(jì)、分析是他的工作之一，對付這些數(shù)據(jù)，當(dāng)然是使用Excel作為工具是最合適的?？墒切窃诜治鲞@些數(shù)據(jù)時(shí)，只會使用Excel中的排序、自動篩選等功能，頂多再加上一些簡單的函數(shù)幫忙，面對這些每天都會生成的大量數(shù)據(jù)，案頭的工作已經(jīng)成為了很重的包袱?，F(xiàn)在，有小樣吳這困擾的人很多，在Excel中，往往會在數(shù)據(jù)表中存放或管理很多數(shù)據(jù)量較大的數(shù)據(jù)，若沒有好的工作方法，這些數(shù)據(jù)確實(shí)是很難進(jìn)行統(tǒng)計(jì)和分析的。其實(shí)，這些數(shù)據(jù)大多又是以字段表的形式填寫的。所謂“字段表”就是數(shù)據(jù)表的第一行是標(biāo)題行，下面的數(shù)據(jù)信息按照標(biāo)題的內(nèi)容進(jìn)行分類

2、填寫，每個(gè)標(biāo)題所在的一列就稱為一個(gè)字段，本文就來向大家介紹如何對付這些數(shù)據(jù)量較大的“字段表”。這種字段表有一個(gè)非常大的好處就是分類清楚，每一列的信息類型是完全一致的，所以無論是排序、篩選、還是設(shè)置“數(shù)據(jù)有效性”都非常的方便。但是，這樣的字段列表也有一個(gè)致命的弱點(diǎn)，就是無法便捷地跨列進(jìn)行數(shù)據(jù)查詢和分析，而在實(shí)際工作中，這種對多列信息進(jìn)行跨列分類匯總的數(shù)據(jù)分析，恰恰是一種非常常見的操作，為了便捷有效地對多列數(shù)據(jù)的跨列（或跨表）分析，Excel給我們提供了一個(gè)非常好的分析工具一一數(shù)據(jù)透視表。數(shù)據(jù)透視表可以通過【數(shù)據(jù)】菜單【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，借助【數(shù)據(jù)透視表向?qū)А可?。可以根?jù)需要，使“數(shù)

3、據(jù)透視表”創(chuàng)建在源數(shù)據(jù)表中或是創(chuàng)建在一個(gè)新的“數(shù)據(jù)透視表”工作表中。數(shù)據(jù)透視表創(chuàng)建出來后，會在“字段列表”中自動顯示出源數(shù)據(jù)表的各個(gè)字段名稱；同時(shí)，會出現(xiàn)一個(gè)二維數(shù)據(jù)表的空表框架，如圖1所示。圖1剛創(chuàng)建的數(shù)據(jù)透視表數(shù)據(jù)透視表的構(gòu)成在數(shù)據(jù)透視表的空框架中，一共有4個(gè)不同的區(qū)域，分別是行字段”區(qū)、列字段”區(qū)、數(shù)據(jù)項(xiàng)”區(qū)以及頁字段”區(qū)。這4個(gè)區(qū)域都可以包容一個(gè)或多個(gè)源數(shù)據(jù)表中的字段信息，但是由于它們的位置不同，所以它們的名稱和作用則完全不同。各區(qū)的作用和操作在數(shù)據(jù)透視表中，行字段”區(qū)和列字段”區(qū)的作用是分類；數(shù)據(jù)項(xiàng)”區(qū)的作用是匯總（匯總有求和“求平均“計(jì)數(shù)”等多種方式）；頁字段”區(qū)的作用則主要是分類

4、篩選。無論是哪個(gè)區(qū)域，操作都是相同的，都是將字段列表中的字段名”拖拽至相應(yīng)的位置即可。拖拽的原則在拖拽這些字段至透視表時(shí)，是沒有一個(gè)定式的，要根據(jù)數(shù)據(jù)分析的目的，選擇需要的字段拖拽。但是由于各個(gè)區(qū)域的作用是不同的，在數(shù)據(jù)分析時(shí)，只有將數(shù)據(jù)拖到了合適的位置時(shí)，才能得到正確的結(jié)果，否則不會得到正確的結(jié)果，這確實(shí)需要些經(jīng)驗(yàn)。為了讓大家很快的記住數(shù)據(jù)透視表的各區(qū)作用，可以熟記下面的順口溜：行列分類在兩旁，數(shù)據(jù)匯總?cè)ブ醒?，各區(qū)能容多字段，篩選分類在上方?！睘榱俗尨蠹腋玫乩斫馀c掌握數(shù)據(jù)透視表的功能，下面來舉一個(gè)實(shí)例說明。這是一個(gè)銷售訂單月表”表中記錄了有關(guān)訂單和銷售的4個(gè)字段，分別是訂單ID”號碼、訂單

5、金額”銷售人員”和銷售人部門”如圖2所示。UTBcDE借售訂單月表Jarrv420040402“iDOOJarry和宜':部S20040403¥WaTJ二620010101.000Hike7口FKSTifn,00082004040$01000Jarryg20040407¥150.000len1020040408Kike1120040409Y100.cooHelen鎧整亂1220040410,500.00013：200Y2SS.000IlCCD加部1420040412Y沁6qqTon1&20040413YTroooJarry組當(dāng)：路16：200Y670,COO

6、TonT7200404150.000Helen1820040416Y,：70J7!Tod-v1920040417Y；Tx.x3Jarry2020040418f1E2,H4Toed八/r21,¥2s6Helen和科2噩圖2銷售訂單源數(shù)據(jù)表這就是一個(gè)典型的字段表”數(shù)據(jù)表的第一行是標(biāo)題行”下面各行是一條一條的記錄。這樣的數(shù)據(jù)表不便于根據(jù)銷售人”或部門”進(jìn)行分類數(shù)據(jù)查詢，可將它利用【數(shù)據(jù)】菜單【數(shù)據(jù)透視表和數(shù)據(jù)透視圖】命令，借助【數(shù)據(jù)透視表向?qū)А吭谛碌摹癝hee表”生成一個(gè)數(shù)據(jù)透視表（圖1所示）。下面我們從不同的角度來分析這個(gè)銷售訂單月表”親身體會一下數(shù)據(jù)透視表的透視”功能：1以銷售人”分類

7、查詢訂單總額（單字段分類單字段匯總）既然是以銷售人分類，所以就要將數(shù)據(jù)透視表中的銷售人”字段拖拽至行字段”區(qū)或列字段”區(qū)以進(jìn)行自動分類；然后再將訂單金額”字段拖拽至數(shù)據(jù)項(xiàng)”區(qū)，以進(jìn)行求和匯總。這種在透視表的不同區(qū)域拖放一個(gè)字段的方式是最基本的分類匯總”操作，如圖3所示。4 -£皿火爐ra金r：5 fflf6 Helen一£¥1,559.285¥2,878.570Ytmcoo¥1455.142T 9.712 第7STS圖3單字段分類單字段匯總的結(jié)果2以部門”和銷售人”分類查詢訂單總額和總訂單數(shù)（多字段分類多字段匯總）在數(shù)據(jù)透視表的各個(gè)區(qū)域中，可以

8、拖拽放置多個(gè)字段，這樣就可以起到同時(shí)查詢多個(gè)字段的分類匯總效果。由于本例要同時(shí)查詢部門”和銷售人”兩個(gè)字段的分類，所以可以分別將部門”和銷售人”字段都拖拽至行字段”區(qū)域，以實(shí)現(xiàn)分類；然后再分別將訂單號”和訂單金額”字段都拖拽至數(shù)據(jù)項(xiàng)”區(qū)域以實(shí)現(xiàn)匯總。這樣就可以在一個(gè)數(shù)據(jù)透視表中查詢多字段分類和多字段匯總的效果了，如圖4所示。9圖4多字段分類多字段匯總的結(jié)果TIPS本例還可以將部門”字段拖拽至列字段”區(qū)，將銷售人”字段拖拽至行字段”區(qū)，生成出一個(gè)二維透視表效果。3按照指定的部門”查詢銷售情況（利用頁字段”進(jìn)行篩選）數(shù)據(jù)透視表的每個(gè)區(qū)域都有篩選”的功能，尤其是頁字段”，它的主要功能就是篩選。若將部

9、門”字段拖拽至數(shù)據(jù)透視表最上方的頁字段”區(qū)域，那么就可以利用它篩選出需要查詢的某部門的銷售情況了，如圖5所示。wuHtiBianw訂他14訂 恒門圖5利用頁字段”的篩選來查詢銷售1部”的銷售情況在此，我們只是舉了一個(gè)簡單的銷售訂單月表”案例，來向大家介紹如何通過Excel中的數(shù)據(jù)透視表來分析數(shù)據(jù)。限于篇幅，并沒有將數(shù)據(jù)透視表的各種應(yīng)用都進(jìn)行介紹，除了本文向大家介紹的這些應(yīng)用外，使用數(shù)據(jù)透視表還可以創(chuàng)建計(jì)算”字段或?qū)Χ啾磉M(jìn)行透視分析等。相信掌握了數(shù)據(jù)透視表的應(yīng)用，小吳在工作中的困擾會少了很多，數(shù)據(jù)透視表的應(yīng)用是Excel軟件的一大精華，它匯集了Excel的“COUNT'、"SU

10、MIF函數(shù)、分類匯總”和自動篩選”等多種功能，是高效辦公中分析數(shù)據(jù)必不可少的利本節(jié)以在“清風(fēng)褲業(yè)銷售情況表”工作簿中創(chuàng)建和編輯數(shù)據(jù)透視表為例，幫助用戶掌握在Excel工作表中使用數(shù)據(jù)透視表和數(shù)據(jù)透視圖的方法，鞏固本章所學(xué)內(nèi)谷。（1）打開“清風(fēng)褲業(yè)銷售情況表”工作簿，如圖所示味見褲腔林售植況豢34567991011121314151617思思思思真真直一真 n n n n 二、二、二、二、M < > >1 SheetlSheet2 Sheet3 cj產(chǎn)品絡(luò)男士休閑褲男士牛仔褲女士休閑褲女士牛仔褲男士休閑褲男士牛仔褲女士休閑褲女士牛仔褲（2）選擇“插入”選項(xiàng)卡，單擊“表”組中的“

11、數(shù)據(jù)透視表”按鈕，在彈出的菜單中選擇“數(shù)據(jù)透視表”命令，如圖10-3-2所不。直祥公司員:銷售業(yè)境境計(jì)表-Microsoft艾面托局公式卻甫圉祖妾表逵視表T匡片.芙站畫形狀SmartArt蒼三拄形朗折朗餅圖條形圈面積圖散點(diǎn)圖其他醉(3)打開“創(chuàng)建數(shù)據(jù)透視表”對話框，在“請選擇要分析的數(shù)據(jù)”組中選擇“選擇一個(gè)表或區(qū)域”，單擊“表/區(qū)域”文本框右側(cè)的按鈕，如圖10-3-3所示(4)在工作表中選擇A3:D11單元格區(qū)域，單擊按鈕，如圖10-3-4所示ABCD請庵褲比觥售情況表0123567LO-001±1x1x1±1x1x1X;產(chǎn)品名稱7男士休閑褲:男士牛仔褲:女士休閑褲女士牛仔

12、褲;男士休閑褲:男士牛仔褲:女士休閑褲吐牛田星創(chuàng)建數(shù)據(jù)睡視表何岡ISheetli$AS3：$E$L1也真真真真八處樂樂樂<I1!3060210819723944.卜出LShLt11/SheetsjSheeH"tXN返回“創(chuàng)建數(shù)據(jù)透視表”對話框，在“選擇放置數(shù)據(jù)透視表的位置”選項(xiàng)區(qū)域中選擇“現(xiàn)有工作表”單選按鈕，單擊“位置”文本框右側(cè)的按鈕，選擇Shee10-3 -5 所 75。t2工作表中的B3單元格，然后單擊“確定”按鈕，如圖創(chuàng)屋數(shù)據(jù)透觀表諸選擇要分析的數(shù)據(jù)選擇一個(gè)表或區(qū)域)表/區(qū)域遼)：I弘的山$邸3：$13$門。使用外部數(shù)據(jù)源宦選擇獨(dú)置數(shù)據(jù)透觀表的位置o新工作表®

13、;®現(xiàn)有工作表的位送E)：Shfeet2_!m3驗(yàn)憐I取消(6)此時(shí)在Sheet2工作表中插入數(shù)據(jù)透視表，如圖10-3-6所示123456789數(shù)據(jù)透視表3若要生成報(bào)表，請從“數(shù)據(jù)透 視表字段列表”中選擇字段。1110i< < > 川 Sheetl Sheet2 / Sheet3 / 利 在打開的“數(shù)據(jù)透視表字段列表”任務(wù)窗格中,推遲布局更新將列表中的字段全部選中,【據(jù)透禊表字段列表選擇要添加到報(bào)表的字段:口產(chǎn)品名稱 口品牌數(shù)量 口銷售額在以下區(qū)域間拖動字段：/報(bào)表篩選二j列標(biāo)簽U行標(biāo)簽E數(shù)值更;完成數(shù)據(jù)透視表的布局設(shè)計(jì)，可以看到工作表中的數(shù)據(jù)透視表會相應(yīng)變化，如圖

14、10-3-7所不。2二工987654321.ABCDEI1值行標(biāo)簽/求和項(xiàng):數(shù)量求和項(xiàng):銷售額-男土牛仔褲1248432樂真312108亦思936324男士布閑褲1107480樂真453060亦思654420T女士牛仔褲79537212131415161718樂真583944蘇思21142S女士休閑褲634-84樂真291972蘇思342312總計(jì)37625568w4!SheetlSheetESheet3.麗(8)單擊“數(shù)據(jù)透視表字段列表”任務(wù)窗格中的“關(guān)閉”按鈕，或者在“選項(xiàng)”選項(xiàng)卡“顯示/隱藏”組中單擊按鈕，關(guān)閉”數(shù)據(jù)透視表字段列表”任務(wù)窗格。(9)選擇B3單元格，單擊鼠標(biāo)右鍵，在彈出的快

15、捷菜單中選擇“數(shù)據(jù)透視表選項(xiàng)”命令,如圖10-3-8所不。1123456789101112131415161718心復(fù)制g雪設(shè)置單元格格式（E）J用新兇數(shù)據(jù)透視表選項(xiàng)g）.口顯示字讀列表（£）843221086324748030604420女士牛仔褲79灣座樂真583944亦思211428里女士休閑褲634284樂真291972亦思342312總計(jì)37625568SheetlSheet2Sheet3Uh（10）打開“數(shù)據(jù)透視表選項(xiàng)”對話框，選擇“顯示”選項(xiàng)卡，勾選“顯示”區(qū)域中的“經(jīng)典數(shù)據(jù)透視表布局（啟用網(wǎng)格中的字段拖放）”復(fù)選框，單擊“確定”按鈕，如圖10-3-9所示。數(shù)據(jù)睡挪表旌

16、項(xiàng)名稱©裂邏逵諷疊1布周和格武匯總和篩選顯示打印數(shù)據(jù)y顯亦展開/折西按鈕g)0顯示上不文工具提示C)公匚具提示中顯示犀性Cr0顯示宇段標(biāo)題和踴選下才宜列表回婪典數(shù)據(jù)透視希舄怕用網(wǎng)格中的字段拖預(yù)必并F|l*-1*axatu«i-IlBvtv*tot*111rtta«-ri，«*1mkU»U顯亍行中的空數(shù)據(jù)頊冽顯示列中的空數(shù)據(jù)項(xiàng)CCr值區(qū)域中授有芋段時(shí)顯示項(xiàng)目標(biāo)第汀)字段列羲0升序%)嚴(yán)按數(shù)據(jù)源順序排序迅)取消(11)此時(shí)顯示數(shù)據(jù)透視表的經(jīng)典布局，如圖10-3_10所示ABC3值T產(chǎn)品名稱品牌求和項(xiàng):數(shù)量求和項(xiàng):銷售頷5男士牛仔褲§心31

17、210B69363247男士牛仔褲匯總1248432一男士休閑褲樂真453060亦思65442010男士休閑褲匯總110748011女士牛仔褲樂真58394412亦思21142813女士牛仔掩匯總79537214三i女士休閑褲樂真29197215亦思34231216女士休閑褲匯總63428417總計(jì)3762556818HISheetl.：Sheet2zSheet3V(12)選擇整個(gè)數(shù)據(jù)透視表，單擊“選項(xiàng)”選項(xiàng)卡“操作”組中的“移動數(shù)據(jù)透視表”按鈕,如圖10-3-n所示。開始 插入 頁面布局 公式 逛黑閱 視圖言寸町產(chǎn)*58風(fēng)揮業(yè)銷售情況表-MicrosoftEx.活商字段:數(shù)讖視表1活動字段z

18、l51a-J)啟新更改*數(shù)據(jù)尉排序345S9 S910遼12131415161710JJI1(13)在打開的“移動數(shù)據(jù)透視表”對話框中選擇“現(xiàn)有工作表”單選按鈕，單擊“位置”文本框后的按鈕，如圖10-3-12所示。女士牛仔褲樂真583944亦思21142日女士牛仔褲匯總795372-女士休閑褲樂真291972亦思342312女士休閑褲匯總634284總計(jì)37625568w電卜*Sheet1Sheets,Sheet3戀(14)選擇E2單元格，單擊按鈕,如圖10-3-13所示。移動數(shù)據(jù)透視表請廊褲生卷金口食貴理麥廣,品名稱品牌男士休閑褲男士牛仔弭亙789女士休閑褲 女士牛仔褲 男士休閑褲 男士牛仔褲 女士休閑褲342123120248876 2010女士牛仔褲11121314151617*SheetlIP(15)此時(shí)返回到“移動數(shù)據(jù)透視表”對話框，單擊“確定”按鈕，如圖10-3-1,所zjO(16)此時(shí)將數(shù)據(jù)透視表