數(shù)據(jù)透視表教程

1、教程:數(shù)據(jù)透視表我縣有三個職業(yè)學(xué)校，每個學(xué)校有四個專業(yè)，每個專業(yè)都有五門課，學(xué)生人數(shù)千人左右。這次期末統(tǒng)考結(jié)束后，教育局要求將所有學(xué)生的考試成績匯總到一個工作表中，并要求對三所學(xué)校的各科成績、各專業(yè)成績進行分析比較?？梢姽ぷ髁恐蟀?！不僅要運用到分類、還要實現(xiàn)匯總計算。永中Office的數(shù)據(jù)透視表就具有很強大的數(shù)據(jù)處理能力，不必寫入復(fù)雜的公式，使用向?qū)?chuàng)建一個交互式表格就可以自動提取、組織和匯總數(shù)據(jù)。下面就來和大家分享：統(tǒng)計出的全縣三所學(xué)校的學(xué)生成績：一、創(chuàng)建數(shù)據(jù)透視表在創(chuàng)建數(shù)據(jù)透視表前，首先對源數(shù)據(jù)進行幾項重要檢查，否則無法創(chuàng)建數(shù)據(jù)透視表。*l 必須包含列標題。如“準考證號”、“語文”、“數(shù)

2、學(xué)”等。*l 用于創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域內(nèi)不能有空行或空列，否則僅為空行前或空列左的數(shù)據(jù)進行創(chuàng)建。* 每列只能包含一種類型的數(shù)據(jù)l1.選擇“數(shù)據(jù)”“數(shù)據(jù)透視表”，彈出“數(shù)據(jù)透視表向?qū)Р襟E1”對話框。2.單擊“下一步”，到步驟2，輸入要建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域。（注意：“全縣三所職中統(tǒng)考成績”行不能包含在內(nèi)）3.按照步驟，“完成”后，會同時顯示“數(shù)據(jù)透視表”工具欄和“數(shù)據(jù)透視表字段列表”框。二、構(gòu)建數(shù)據(jù)透視表字段1.雙擊“數(shù)據(jù)透視表字段列表”中的“學(xué)?！焙汀皩I(yè)”，項目會自動被填充到“將行字段拖至此處”區(qū)域。2.選中“語文”，將其拖拽到“請將數(shù)據(jù)字段拖至此處”區(qū)域?！罢Z文”拖至后效果見下圖（

3、這時可清晰得觀察出各個學(xué)校及各個專業(yè)的“語文”成績總分）3.參照上面方法，分別將“數(shù)學(xué)”、“英語”等科目拖至上圖中的“匯總”列區(qū)域。所有科目拖至后效果：三、改變數(shù)據(jù)透視表的匯總方式由于各個學(xué)校、各個專業(yè)的學(xué)生人數(shù)不等，所以通過總分還無法判斷哪所學(xué)校此次考試的成績更勝一籌。最有力的方法就是通過求平均值比較。1.選中數(shù)據(jù)透視表中的“求和項：語文”，單擊“數(shù)據(jù)透視表”工具欄上“字段設(shè)置”圖標，彈出“數(shù)據(jù)透視表字段”對話框。2.在“匯總方式”框中選擇“平均值”。三所學(xué)校的各專業(yè)的語文科目就由“求和”改成“求平均值”了。與此同時，各學(xué)校的語文平均分也可以輕松得到。3.參照上面方法，得出其他學(xué)科的平均分值

4、。四、隱藏項目的明細數(shù)據(jù)1.雙擊“財會學(xué)?！?、“第一職專”、“三山職中”所在的單元格，即可隱藏各專業(yè)各學(xué)科的明細數(shù)據(jù)。效果見下圖所示。2.這時顯示“每個學(xué)校每門科目的平均分”及“縣平均分”。通過觀察可一目了然得分析出哪個學(xué)校在此次統(tǒng)考中的成績更優(yōu)異及較“縣平均分”的差距。貼心提示：如果檢查發(fā)現(xiàn)數(shù)據(jù)透視表中的數(shù)據(jù)有誤時，不能直接在其上進行修改，需先修改數(shù)據(jù)源，然后單擊“數(shù)據(jù)透視表”工具欄的“數(shù)據(jù)透視表”按鈕，從下拉菜單中選擇“刷新數(shù)據(jù)”。標準數(shù)據(jù)庫數(shù)據(jù)組織格式_數(shù)據(jù)透視表1假如有一學(xué)生成績表，數(shù)據(jù)是按標準數(shù)據(jù)庫組織格式組織的即第一行為字段名，下面的每一行為一個記錄，如圖所示圖1數(shù)據(jù)透視表源數(shù)據(jù)2

5、數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖如果只生成數(shù)據(jù)透視表，則只選擇數(shù)據(jù)透視表，如果同時要生成數(shù)據(jù)透視圖，則最好選擇下一項，即數(shù)據(jù)透視圖（及數(shù)據(jù)透視表），這里選擇第一項 ，下一步圖2數(shù)據(jù)透視表向?qū)б?指定數(shù)據(jù)區(qū)域在這里可以指定數(shù)據(jù)區(qū)域，假如以后，還要添加新的記錄，最好這里指定的數(shù)據(jù)區(qū)域可以大一些，如A1:F1000（你甚至可以設(shè)為A:F，這樣做的缺點是，會產(chǎn)生空格項，但如果增加數(shù)據(jù)時，不必再重頭制作），下一步圖3數(shù)據(jù)透視表向?qū)Ф?指定數(shù)據(jù)透視表產(chǎn)生的位置可以指定新建工作表，還可以指定現(xiàn)有工作表，只需指定產(chǎn)生數(shù)據(jù)透視表的第一個單元格就可以了，如H3，完成。圖4數(shù)據(jù)透視表向?qū)a(chǎn)生的空數(shù)據(jù)透視表如下圖所示圖5

6、數(shù)據(jù)透視表初表看了這個圖，你應(yīng)該明白，我為什么要在4步，指定在H3顯示數(shù)據(jù)透視表了吧。最上面產(chǎn)生的哪一行的妙處，將在下面介紹。5行字段的拖放將學(xué)號拖至“將行字段拖至此處”框中，姓名和性別分別拖至學(xué)號相鄰的右側(cè)，將科目拖至“將列字段拖至此處”框中。將成績拖至“請將數(shù)據(jù)項拖至此處”框中。經(jīng)過以上設(shè)置，可以產(chǎn)生如下圖所示的效果。圖6初步完成表一6字段的進一步設(shè)置在左側(cè)的行字段中，不需要對姓名和性別進行匯總，可以在任意的姓名匯總上（如劉備 匯總單元格）右擊，隱藏（圖12），或者字段設(shè)置，無，確定。同樣設(shè)置隱藏性別的匯總。注：這里的匯總，實際就相當于數(shù)據(jù)分類匯總產(chǎn)生的結(jié)果，也就是說，數(shù)據(jù)透視表對于需要經(jīng)

7、常分類匯總的人來說，要方便了許多，不需要手工排序，排序工作自動完成。本例只是選擇了最簡單一種，就不需要分類匯總了。圖7字段設(shè)置對話框（注意上面這個對話框，下面要反復(fù)用到）此時，數(shù)據(jù)透視表變?yōu)槿缦滦问綀D8初步完成表二7隱藏空白行列在行(空白)或列(空白)字段名上右擊，隱藏（圖12）。則透視表變?yōu)閳D9初步完成表三如果你不想顯示總計行和總計列，同樣可以在總計行標題和列標題上右擊，隱藏。8調(diào)整數(shù)據(jù)匯總方式此時的顯示，并不理想，顯示的僅為數(shù)據(jù)的個數(shù)，而我們需要顯示各科成績，與幾門課的平均值在圖中顯示為1和區(qū)域任一單元格上右擊，字段設(shè)置，改為求和或平均值（圖7）。因為每人每項目的數(shù)據(jù)僅有一個，求和與平均值

8、在數(shù)值上是相等的，可以任選一項。其實，在圖7字段設(shè)置對話框中，你還可以設(shè)置其他的計算方式，包括求和、計數(shù)、平均值、最大值、最小值、乘積、數(shù)值計數(shù)、標準偏差、總體標準偏差、方差、總體方差，總計11種計算方式，你可以根據(jù)你的需要進行選擇喲。圖10初步完成表四9數(shù)值格式的設(shè)置對于最右側(cè)的總計項，由于顯示的小數(shù)點位數(shù)過多，能不能通過設(shè)置小數(shù)點位數(shù)，使列寬變得窄一些呢？這個沒有問題，右擊，字段設(shè)置，數(shù)字（圖7中與高級相同位置的按鈕，在這里已經(jīng)變?yōu)閿?shù)字），數(shù)值，保留小數(shù)點1位。令人可惜的是，這里設(shè)置的數(shù)值格式，會影響到整個表的單元格設(shè)置，在一處設(shè)置了保留小數(shù)點位數(shù)，在其他單元格，也自動繼承這種數(shù)值格式，如

9、果要單獨設(shè)置，可以按常規(guī)的單元格格式設(shè)置方法，選中要設(shè)置的單元格區(qū)域，再設(shè)置單元格格式。圖11完成表10數(shù)據(jù)刷新數(shù)據(jù)透視表還有一個致使的弱點，就是不能自動刷新數(shù)據(jù)，需要通過右擊刷新數(shù)據(jù)，進行手工刷新。圖12右擊快捷菜單數(shù)據(jù)刷新后，你可以看出，姓名中多了周瑜，科目中添加了政治。而劉備的政治缺考。如果你在第3步，沒有多指定的數(shù)據(jù)區(qū)域，新增添的數(shù)據(jù)，將不能顯示出來。圖13添加數(shù)據(jù)并刷新后的效果11數(shù)據(jù)順序的調(diào)整圖13中的數(shù)據(jù)，怎么按性別排序呢？比如把貂蟬和小喬放到周瑜的后面。這個操作對于Excel 2003及以前的版本，稍微差了一些，只能一個一個地調(diào)整，在貂蟬對應(yīng)的學(xué)號上右擊，順序，移至底端。再在小

10、喬的學(xué)號上右擊，順序，移至底端。請諒解，調(diào)整后的效果圖，就不要再上傳了吧，這個文章已經(jīng)夠長了。在Excel 2007中，排序功能大大增強，你用了就知道了，這里也不再多敘。圖14順序調(diào)整子菜單如果你并不在意表格各字段的排列，只是要統(tǒng)計男生與女生的平均值，只需將性別拖放到學(xué)號的左側(cè)圖15按性別匯總效果12數(shù)據(jù)的篩選數(shù)據(jù)透視表還自動地設(shè)置成自動篩選狀態(tài)，你可以方便地進行篩選，如只顯示男同學(xué)的成績，只需點擊性別右側(cè)的三角形篩選標志，僅勾選“男”。圖16透視表的自動篩選功能同樣，對科目進行篩選，可以有選擇地顯示某一科目的成績。但這樣篩選后，可能會導(dǎo)致數(shù)據(jù)格式的清除。既然不想顯示所有的科目，就不如將科目拖

11、放到最上面的“將頁字段拖至此處”框中，并通過篩選，只顯示某一科目，如數(shù)學(xué)。圖17將科目拖至頁字段的效果如果將科目拖放到行字段區(qū)域，則會產(chǎn)生按學(xué)號（姓名）匯總的效果（這個就不上傳效果圖了吧）。數(shù)據(jù)透視表是不是很自由？13數(shù)據(jù)透視表的復(fù)制辛辛苦苦制作的數(shù)據(jù)透視表，按上所述，只顯示為數(shù)學(xué)成績，要同時再制作一個只顯示英語成績的透視表，是不是需要重新制作呢？答案是：如果你不怕麻煩，想再練習(xí)一遍，就重新制作好了。不過，我可不愿意一切從頭來，選中透視表區(qū)域，復(fù)制，在N1單元格粘貼。其它的設(shè)置，就不用再多說了吧。你不妨試試下面的效果，復(fù)制一份透視表，讓原來的透視表顯示平均成績，新粘貼的透視表顯示計數(shù)項。14數(shù)

12、據(jù)透視表數(shù)據(jù)的獲取數(shù)據(jù)表是動態(tài)變化的，一旦對透視表結(jié)構(gòu)進行調(diào)整，所引用的單元格的位置有可能會發(fā)生改變，如何才能獲得一個動態(tài)跟隨的效果呢？方法很簡單，比如要獲得周瑜的各科目的平均成績，可以將透視表調(diào)整回圖13所示效果，在引用數(shù)據(jù)單元格輸入“=”，再點擊周瑜最右邊的總計項。在編輯欄中，就會顯示周瑜各科目平均值的引用公式。=GETPIVOTDATA(成績,$H$3,學(xué)號,20070616,姓名,周瑜,性別,男)當然你完全可以將公式中的學(xué)號、20070616等項，改為某個單元格引用。但這種引用，如果脫離開透視表，就會出現(xiàn)錯誤。15數(shù)據(jù)透視圖的生成如果在第2步，選擇“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”，則會同

13、時生成數(shù)據(jù)透視表和數(shù)據(jù)透視圖，不過現(xiàn)在生成數(shù)據(jù)透視圖，也為時不晚。只需將光標放到透視表區(qū)域，插入圖表。即可生成相應(yīng)的數(shù)據(jù)透視圖。其他方面的應(yīng)用在許多數(shù)據(jù)中篩選出不重復(fù)的數(shù)據(jù)，以本例說明，源數(shù)據(jù)中的B、C、D列都可以看成是有重復(fù)的數(shù)據(jù)，通過數(shù)據(jù)透視表，就可以生成不重復(fù)的姓名列表，性別列表和科目列表。 動態(tài)圖表的建立在企業(yè)的經(jīng)營活動中，往往需要為每個部門建立大量相似的圖表，如果在一張工作表上建立太多的圖表，既費時也使得圖表顯得凌亂不堪。我們可以建立動態(tài)圖表來解決這個問題，當需要了解某個部門的銷售情況時，只需將鼠標移到工作表中該部門的單元格上，即可立即顯示出該部門的銷售圖表?！纠?-10】某企業(yè)的8個銷售部門一年內(nèi)各月的銷售量數(shù)據(jù)如圖2-34所示，建立各部門的動態(tài)圖表的步驟如下：圖2-34 動態(tài)銷售圖表（1）設(shè)計動態(tài)圖表數(shù)據(jù)區(qū)域，如圖2-34所示。（2）在單元格A13中輸入公式“=INDIRECT(ADDRESS(CELL(row),COLUMN (A3)”，并把該公式向右填充復(fù)制到M13中，這里COLUMN的意思是返回參數(shù)所在的列標，CELL(row)的意思是返回當前光標所在的行號，ADDRES