Excel數(shù)據(jù)透視表培訓PPT課件

1、EXCEL03EXCEL03數(shù)據(jù)透視表 數(shù)據(jù)透視表基礎 創(chuàng)建基本數(shù)據(jù)透視表 自定義數(shù)據(jù)透視表 控制查看視圖數(shù)據(jù)的方法 在數(shù)據(jù)透視表內(nèi)進行計算 用數(shù)據(jù)透視圖和其他可視化工具 用數(shù)據(jù)透視表對不同數(shù)據(jù)源進行分析 借助宏改善數(shù)據(jù)透視表報表第1頁/共50頁數(shù)據(jù)透視表基礎 什么是數(shù)據(jù)透視表 為什么應該使用數(shù)據(jù)透視表 何時應該使用數(shù)據(jù)透視表 數(shù)據(jù)透視表的結(jié)構(gòu) 對數(shù)據(jù)透視表報表的限制第2頁/共50頁數(shù)據(jù)透視表基礎 什么是數(shù)據(jù)透視表 可以把Excel想象成一個很大的工具箱，它包含我們要使用的各種工具。數(shù)據(jù)透視表本質(zhì)上是Excel工具箱中的一個工具。 通過數(shù)據(jù)透視表查看數(shù)據(jù)集時，有可能看到之前沒有注意到的數(shù)據(jù)細節(jié)

2、，此外還可以旋轉(zhuǎn)數(shù)據(jù)透視表，以便從不同的透視角度查看數(shù)據(jù)。 使用數(shù)據(jù)透視表能夠建立數(shù)據(jù)集的交互視圖。我們稱這個視圖為數(shù)據(jù)透視報表，從而我們可以方便地將數(shù)據(jù)分組，匯總大量的數(shù)據(jù)形成有用的信息，在很很短的時間內(nèi)進行各種計算。第3頁/共50頁數(shù)據(jù)透視表基礎 為什么應該使用數(shù)據(jù)透視表 通常，Excel的行為可分為兩大類：計算數(shù)據(jù)和整理（格式化）數(shù)據(jù)。雖然很多內(nèi)置工具和公式可以使這些任務很容易完成，但是數(shù)據(jù)透視表通常是計算、整理數(shù)據(jù)最有效的方法。它可以幫助我們以比傳統(tǒng)的函數(shù)和公式更快更好的方式完成許多任務，有助于極大地提高必須使用EXCEL完成的大量的任務的效率，并減少錯誤，數(shù)據(jù)透視表不能做所有的事情，

3、但哪怕只知道數(shù)據(jù)透視表的基本功能的用法，就可以讓自己的數(shù)據(jù)分析能力和效率提高到的新水平。第4頁/共50頁數(shù)據(jù)透視表基礎 何時應該使用數(shù)據(jù)透視表 有大量交易數(shù)據(jù)，已很難通過有意義的方式分析和匯總 需要找出數(shù)據(jù)內(nèi)部的關系并分組 需要找出數(shù)據(jù)中某一字段的一系列特定值 需要利用各種時間周期找出數(shù)據(jù)趨勢 預期要頻繁地查詢數(shù)據(jù)分析的變化 需要創(chuàng)建常常包含新增加部分數(shù)據(jù)的分類匯總 需要將數(shù)據(jù)組織成易于制成圖表的格式第5頁/共50頁數(shù)據(jù)透視表基礎 數(shù)據(jù)透視表的結(jié)構(gòu) 值區(qū)域 行區(qū)域 列區(qū)域 報表篩選區(qū)域第6頁/共50頁數(shù)據(jù)透視表基礎對數(shù)據(jù)透視表報表的限制類別20002002/20032007行字段數(shù)（受存儲空間

4、限制）受可用存儲空間限制受可用存儲空間限制1048576列字段數(shù)25625616384頁面字段256256數(shù)據(jù)字段256256單個數(shù)據(jù)透視表字段中特定項數(shù)（可能受存儲空間限制）8000325001048576計算項數(shù)受可用存儲空間限制受可用存儲空間限制受可用存儲空間限制一張工作表上的數(shù)據(jù)透視報表數(shù)受可用存儲空間限制受可用存儲空間限制受可用存儲空間限制第7頁/共50頁創(chuàng)建基本數(shù)據(jù)透視表 為數(shù)據(jù)透視表報表準備數(shù)據(jù) 有效數(shù)據(jù)源設計要點 創(chuàng)建基本數(shù)據(jù)透視表 與數(shù)據(jù)源保持一致 共享數(shù)據(jù)透視表緩存第8頁/共50頁創(chuàng)建基本數(shù)據(jù)透視表 為數(shù)據(jù)透視表報表準備數(shù)據(jù) 確保數(shù)據(jù)以表格形式布局 避免在分節(jié)標題中存儲數(shù)據(jù)

5、 消除數(shù)據(jù)源中的空白和空單元格第9頁/共50頁創(chuàng)建基本數(shù)據(jù)透視表 有效的數(shù)據(jù)源設計要點 數(shù)據(jù)源的第一行由字段標簽或者各列信息的標題組成 數(shù)據(jù)源中的每一列表示惟一一類數(shù)據(jù) 數(shù)據(jù)源中的每一行表示各列中的單獨一項第10頁/共50頁創(chuàng)建基本數(shù)據(jù)透視表 創(chuàng)建基本數(shù)據(jù)透視表 添加字段到報表中 增加數(shù)據(jù)透視表的層次 重新排列數(shù)據(jù)透視表 創(chuàng)建報表篩選第11頁/共50頁創(chuàng)建基本數(shù)據(jù)透視表 與數(shù)據(jù)源保持一致 對現(xiàn)有數(shù)據(jù)源進行修改 數(shù)據(jù)源的范圍已經(jīng)隨著行或列的增加而增大第12頁/共50頁創(chuàng)建基本數(shù)據(jù)透視表 共享數(shù)據(jù)透視表緩存 通過簡單地復制數(shù)據(jù)透視表并粘貼到任何其他地方，可以創(chuàng)建另一個數(shù)據(jù)透視表，而不用復制數(shù)據(jù)透視

6、表緩存，從而減少內(nèi)存。 共享透視表的副作用 刷新數(shù)據(jù) 增加計算字段 增加計算項 組合字段或者取消字段組合第13頁/共50頁自定義數(shù)據(jù)透視表 改變常用修飾 修改匯總計算 添加和刪除分類匯總 使用累積總計選項第14頁/共50頁自定義數(shù)據(jù)透視表 改變常用修飾 修改數(shù)字格式來添加千位分隔符 用0代替空值 修改字段名稱第15頁/共50頁自定義數(shù)據(jù)透視表 修改匯總計算第16頁/共50頁自定義數(shù)據(jù)透視表 添加和刪除分類匯總 有許多行字段時，禁止分類匯總 為一個字段添加多種分類匯總第17頁/共50頁自定義數(shù)據(jù)透視表 使用累積總計選項 利用差異選項顯示年與年之間的不同 利用差異百分比選項將當年與上一年進行對比

7、利用“按某一字段匯總”跟蹤總和 占同行數(shù)據(jù)總和的百分比 創(chuàng)建季節(jié)性報表 用占總和的百分比選項度量兩個字段的百分比 使用百分比選項將一行與另一行進行對比 利用指數(shù)選項跟蹤相對重要性第18頁/共50頁控制查看視圖數(shù)據(jù)的方法 組合數(shù)據(jù)透視表字段 查看數(shù)據(jù)透視表字段列表 對數(shù)據(jù)透視表中的數(shù)據(jù)時行排序 對數(shù)據(jù)透視表中的數(shù)據(jù)時行篩選第19頁/共50頁控制查看視圖數(shù)據(jù)的方法 組合數(shù)據(jù)透視表字段 組合日期字段 組合值字段 組合文本字段 取消組合第20頁/共50頁控制查看視圖數(shù)據(jù)的方法 組合日期字段 按月組合時包含年 按周組合包含日期字段第21頁/共50頁控制查看視圖數(shù)據(jù)的方法第22頁/共50頁控制查看視圖數(shù)據(jù)

8、的方法 組合值字段 通過值字段“組合”對話框可以將數(shù)據(jù)透視表中的項組合成相同的范圍。第23頁/共50頁控制查看視圖數(shù)據(jù)的方法 組合文本字段 在數(shù)據(jù)透視表中，也可以對文字字段項進行組合，從而在透視表生成新的數(shù)據(jù)透視字段。第24頁/共50頁控制查看視圖數(shù)據(jù)的方法 查看數(shù)據(jù)透視表字段列表 ?？亢透訑?shù)據(jù)透視表字段列表 重新排列數(shù)據(jù)透視表字段列表第25頁/共50頁控制查看視圖數(shù)據(jù)的方法 對數(shù)據(jù)透視表中的數(shù)據(jù)進行排序 使用字段菜單排序 使用手動排序 使用自定義序列排序第26頁/共50頁控制查看視圖數(shù)據(jù)的方法 對數(shù)據(jù)透視表中的數(shù)據(jù)進行篩選 添加字段到報表篩選區(qū)域 從報表篩選區(qū)中選擇一項 從報表篩選區(qū)中選擇

9、多項 快速選擇或清除所有選項 使用字段列表篩選 使用日期篩選 使用值篩選第27頁/共50頁在數(shù)據(jù)透視表內(nèi)進行計算 計算字段與計算項簡介 Excel提供了在數(shù)據(jù)透視表內(nèi)通過計算字段和計算項進行計算的方法，計算字段是通過對數(shù)據(jù)透視表內(nèi)現(xiàn)有字段進行計算創(chuàng)建的一個字段，可以將計算字段看作是添加一個虛擬列到數(shù)據(jù)集中。計算項是通過對數(shù)據(jù)字段內(nèi)現(xiàn)有的數(shù)據(jù)項進行計算所創(chuàng)建的一個數(shù)據(jù)項，可以將計算項看作是添加一個虛擬行到數(shù)據(jù)集中。 有了計算字段和計算項，可以向數(shù)據(jù)透視表中插入公式，創(chuàng)建自己的自定義字段或者數(shù)據(jù)項。第28頁/共50頁在數(shù)據(jù)透視表內(nèi)進行計算 創(chuàng)建第1個計算字段 創(chuàng)建第1個計算項第29頁/共50頁在數(shù)

10、據(jù)透視表內(nèi)進行計算 使用工作表函數(shù) 可以使用不需要單元格引用或者定義名稱的所有工作表函數(shù)。許多函數(shù)都屬于這一類，其中的一些包括COUNT、AVERAGE、IF、AND、NOT、OR等。第30頁/共50頁在數(shù)據(jù)透視表內(nèi)進行計算 使用常量 在數(shù)據(jù)透視表計算中可以使用任意常量。常量是不變的靜態(tài)值。第31頁/共50頁在數(shù)據(jù)透視表內(nèi)進行計算 管理和維護數(shù)據(jù)透視表計算 編輯和刪除數(shù)據(jù)透視表計算 改變計算項的求解次序 提供公式的說明文檔第32頁/共50頁用數(shù)據(jù)透視圖和其他可視化工具 什么是真正的數(shù)據(jù)透視圖 創(chuàng)建第一個數(shù)據(jù)透視圖 數(shù)據(jù)透視圖規(guī)則 能夠替代使用數(shù)據(jù)透視圖的其他方法第33頁/共50頁用數(shù)據(jù)透視圖和

11、其他可視化工具 什么是真正的數(shù)據(jù)透視圖 和其他人分享自己的分析結(jié)果時，可能很快就會發(fā)現(xiàn)效果不好的原因是人們需要圖。利用圖可以快速確定自己的數(shù)據(jù)要揭示什么，圖可以直接讓用戶滿意，讓用戶可以立即看出其相互關系，指出不同之處并觀察趨勢。根據(jù)數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖時，不用賦予它獨立單元格中獨立的數(shù)據(jù)塊，而只需把整個數(shù)據(jù)透視布局提供給它。使用數(shù)據(jù)透視圖布局對象，允許數(shù)據(jù)透視圖交互式地添加、移動、篩選和更新數(shù)據(jù)透視圖中的數(shù)據(jù)，這一點和在數(shù)據(jù)透視表中一樣。所有這些操作的結(jié)果是對在數(shù)據(jù)透視表中所看到的數(shù)據(jù)進行圖示表示。第34頁/共50頁用數(shù)據(jù)透視圖和其他可視化工具 創(chuàng)建第一個透視圖表 根據(jù)數(shù)據(jù)透視表創(chuàng)建透視

12、圖表 根據(jù)數(shù)據(jù)源從頭創(chuàng)建透視圖表注意：創(chuàng)建數(shù)據(jù)透視圖時，Excel忽略分類匯總和總計第35頁/共50頁用數(shù)據(jù)透視圖和其他可視化工具 數(shù)據(jù)透視圖規(guī)則 修改數(shù)據(jù)透視表影響數(shù)據(jù)透視圖 數(shù)據(jù)透視表中數(shù)據(jù)字段的放置不一定最適合數(shù)據(jù)透視圖 Excel2003中數(shù)據(jù)視圖的格式限制 創(chuàng)建數(shù)據(jù)視圖時，不能使用XY(散點)圖、氣泡圖或者股價圖 在基礎數(shù)據(jù)透視表中，添加或者刪除字段時，已應用的趨勢線常常丟失。 不能像標準圖表那樣，將數(shù)據(jù)源由行切換到列（或者反過來郵列切換到行）第36頁/共50頁用數(shù)據(jù)透視圖和其他可視化工具 數(shù)據(jù)透視圖規(guī)則 在數(shù)據(jù)透視圖中，X軸和Y軸分別對應數(shù)據(jù)透視表中的特定區(qū)域。 X軸對應數(shù)據(jù)透視表

13、中的列區(qū)域，組成數(shù)據(jù)透視圖的Y軸 Y軸對應數(shù)據(jù)透視表中的行區(qū)域，組成數(shù)據(jù)透視圖的X軸第37頁/共50頁用數(shù)據(jù)透視圖和其他可視化工具 能夠替代使用數(shù)據(jù)透視圖的其他方法 研究能夠替代使用數(shù)據(jù)透視圖的其他方法一般有兩個原因。第一，不想負擔生成數(shù)據(jù)透視圖的開銷。其次，想避免數(shù)據(jù)透視圖的一些格式限制。事實上，有時創(chuàng)建數(shù)據(jù)透視表可能僅僅是匯總和整理數(shù)據(jù)，為制圖做準備。在這種情況下，沒有必要保存數(shù)據(jù)源，也沒有必要讓數(shù)據(jù)透視表占據(jù)內(nèi)存和文件空間。第38頁/共50頁用數(shù)據(jù)透視圖和其他可視化工具 能夠替代使用數(shù)據(jù)透視圖的其他方法 把數(shù)據(jù)透視表轉(zhuǎn)化成實際的數(shù)值 使用單元格鏈接回數(shù)據(jù)透視表，作為圖表的數(shù)據(jù)源第39頁/

14、共50頁用數(shù)據(jù)透視表對不同數(shù)據(jù)源進行分析 使用多重合并計算數(shù)據(jù)區(qū)域第40頁/共50頁用數(shù)據(jù)透視表對不同數(shù)據(jù)源進行分析第41頁/共50頁用數(shù)據(jù)透視表對不同數(shù)據(jù)源進行分析第42頁/共50頁借助宏改善數(shù)據(jù)透視表報表 為什么對數(shù)據(jù)透視表報表使用宏 錄制第一個宏 創(chuàng)建帶有表單控件的用戶界面第43頁/共50頁借助宏改善數(shù)據(jù)透視表報表 為什么對數(shù)據(jù)透視表報表使用宏 宏就是已經(jīng)錄制和保存的一系列鍵操作。一旦保存之后，就可以隨時播放宏。換句話說，就是可以在宏中錄制操作，保存宏，然后允許客戶通過單擊按鈕來播放操作，就像在使用這些操作一樣！發(fā)布數(shù)據(jù)透視表報表時這個功能非常有用。對數(shù)據(jù)透視表使用宏的主要好處是可以使用戶輕松執(zhí)行他一般不能自己執(zhí)行的數(shù)據(jù)透視表操作，從而使他們能夠更高效地分析所提供的數(shù)據(jù)。第44頁/共50頁借助宏改善數(shù)據(jù)透視表報表錄制第一個宏宏名：輸入宏的名稱快捷鍵：可以在這個輸入框中輸入任何字母保存在：指定存儲宏的位置說明：輸入描述第45頁/共50頁借助宏改善數(shù)據(jù)透視表報表管理錄制的宏執(zhí)行單步執(zhí)行編輯刪除選項第