EXCEL培訓(xùn)之七.ppt

1、數(shù) 據(jù) 清 單 的 使用,3 數(shù)據(jù)透視表,數(shù)據(jù)透視表是Excel中最具技術(shù)性的復(fù)雜組件之一，本質(zhì)上是從數(shù)據(jù)庫中產(chǎn)生的一個動態(tài)匯總報告。數(shù)據(jù)透視表可以快速合并和比較大量數(shù)據(jù)，對分類變量進行匯總并顯示明細數(shù)據(jù)。 數(shù)據(jù)透視表由字段(頁字段、行字段、列字段和數(shù)據(jù)字段)、項(頁字段項和數(shù)據(jù)項)以及數(shù)據(jù)區(qū)域組成。,數(shù) 據(jù) 清 單 的 使用,3.1 數(shù)據(jù)透視表的創(chuàng)建,步驟一： 在數(shù)據(jù)清單中，任選一單元格，然后單擊“數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟 之1”對話框；在對話框中選擇數(shù)據(jù)源的類型和報表類型，一般采用默認選項，數(shù)據(jù)源類型為Microsoft Excel數(shù)據(jù)清單，報表

2、類型為數(shù)據(jù)透視表；,數(shù) 據(jù) 清 單 的 使用,步驟二：單擊“下一步”按鈕，彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之2”對話框，Excel一般會顯示選定區(qū)域所在的單元格區(qū)域； 步驟三：單擊“下一步”，彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之3”對話框；在該對話框中選擇數(shù)據(jù)透視表顯示的位置，默認方式為新建工作表；,數(shù) 據(jù) 清 單 的 使用,步驟四： 點擊“3步驟之3”對話框中“布局”按鈕，彈出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)Р季帧睂υ捒?，在對話框右上角有提示：“將右邊的字段按鈕拖到左邊的圖上即可構(gòu)造數(shù)據(jù)透視表”。選擇分類變量移至“行字段” 和“列字段”，如有必要還可以移到“頁字段”，將數(shù)值變量或品質(zhì)變

3、量移至“數(shù)據(jù)字段”，點擊“確定”按鈕，回到“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之3”對話框；點擊“完成”按鈕，就可以建立數(shù)據(jù)透視表。,數(shù) 據(jù) 清 單 的 使用,在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之3”中沒有按“完成”按鈕之前，如果要增添或修改項目，可以點擊“選項”，將彈出“數(shù)據(jù)透視表選項”對話框，可以在該對話框中更改數(shù)據(jù)透視表的名稱和其他格式選項和數(shù)據(jù)選項。,數(shù) 據(jù) 清 單 的 使用,在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之1”中直接單擊“完成“按鈕，屏幕就彈出數(shù)據(jù)透視表草圖，同時出現(xiàn)數(shù)據(jù)透視表工具欄和數(shù)據(jù)清單中的各字段名，可以仿照上面的步驟建立數(shù)據(jù)透視表。,創(chuàng)建數(shù)據(jù)透視表的簡便方法,數(shù) 據(jù) 清

4、 單 的 使用,3.2 數(shù)據(jù)透視表的組成,對于建立的數(shù)據(jù)透視表，我們應(yīng)該進一步了解它的組成元素。 行字段：數(shù)據(jù)透視表中指定為行方向的源數(shù)據(jù)清單或表單中的字段，通常為分類變量；有多個行字段的數(shù)據(jù)透視表具有一個內(nèi)部行字段和多個外部行字段，其中內(nèi)部行字段距離數(shù)據(jù)區(qū)最近；最外部的行字段中的項僅顯示一次，而其他行字段中的項可以重復(fù)顯示； 列字段：數(shù)據(jù)透視表中指定為列方向的源數(shù)據(jù)清單或表單中的字段，和行字段類似，通常將分類變量放到列字段中；,數(shù) 據(jù) 清 單 的 使用,頁字段：數(shù)據(jù)透視表中指定為頁方向的源數(shù)據(jù)清單或表單中的字段，通常也將分類變量作為頁字段； 數(shù)據(jù)字段：含有數(shù)據(jù)的源數(shù)據(jù)清單或表單中的字段，通常

5、將數(shù)值變量移入數(shù)據(jù)字段中，使用Sun等匯總函數(shù)對數(shù)值變量進行計算，但是品質(zhì)變量也可以移入數(shù)據(jù)字段，使用Count計數(shù)函數(shù)來計算頻數(shù)； 項：數(shù)據(jù)透視表中字段的子類型或子成員，它表示源數(shù)據(jù)中字段的唯一條目。,數(shù) 據(jù) 清 單 的 使用,3.3 數(shù)據(jù)透視表的基本操作,對數(shù)據(jù)透視表的操作，主要依靠“數(shù)據(jù)透視表”工具來實現(xiàn)，前面已經(jīng)看到，在建立數(shù)據(jù)透視表的同時，就出現(xiàn)了一個數(shù)據(jù)透視表工具欄。點擊“數(shù)據(jù)透視表(P)”,就顯示一個下列式菜單，包含“設(shè)置報告格式、數(shù)據(jù)透視圖、向?qū)?、更改?shù)據(jù)、脫機、隱藏、選定、組及顯示明細數(shù)據(jù)、公式、順序、字段設(shè)置”等選項。右邊還有并列的9個功能按鈕，包括“設(shè)置報告格式、數(shù)據(jù)透視

6、圖、隱藏明細數(shù)據(jù)、顯示明細數(shù)據(jù)、更新數(shù)據(jù)、在匯總中包含隱藏項、始終顯示項目、字段設(shè)置和隱藏字段列表”。,數(shù) 據(jù) 清 單 的 使用,單擊數(shù)據(jù)透視表中任一單元格，再單擊“數(shù)據(jù)透視表”工具欄中的“數(shù)據(jù)透視表(P)向?qū)А?，返回到“?shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之3”； 點擊“布局”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)Р季帧睂υ捒?，將一個新的字段拖到“數(shù)據(jù)字段”下面；點擊“確定”按鈕，回到“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之3”對話框，在“數(shù)據(jù)透視表顯示位置”下的復(fù)選框中選擇“新建工作表”，再點擊“完成”按鈕，會在原來求和項的字段下新增了一個新的字段,1 添加字段,數(shù) 據(jù) 清 單 的 使用,添加字段

7、的另外一種方法,在創(chuàng)建好的數(shù)據(jù)透視表中，從“數(shù)據(jù)透視表字段列表”對話框中，選擇需要添加的字段，如“訂購日期”，將它拖動到原數(shù)據(jù)透視表行字段所在的列上，可以增加一個新的行字段；,數(shù) 據(jù) 清 單 的 使用,2 移動字段,點擊數(shù)據(jù)透視表工具欄中的“布局”按鈕，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)Р季帧睂υ捒颍梢詫⑿凶侄?、列字段和頁字段進行相互轉(zhuǎn)換，達到移動字段的目的。,數(shù) 據(jù) 清 單 的 使用,3 改變字段的匯總方式,單擊數(shù)據(jù)透視表中任一單元格，再單擊“數(shù)據(jù)透視表”工具欄中的“字段設(shè)置”，彈出“數(shù)據(jù)透視表字段”對話框，在“匯總方式”列表框中列出了“求和、計數(shù)、平均值、最大值、最小值、乘積、計數(shù)值、標準

8、偏差、總體標準偏差、方差、總體方差”共11個函數(shù)； 在“匯總方式”中選擇“平均值”，點擊“確定”，就改變了字段的匯總方式。,數(shù) 據(jù) 清 單 的 使用,4 改變字段的顯示方式,如果要改變數(shù)據(jù)的顯示方式，只要在“數(shù)據(jù)透視表字段”對話框中點擊右下角的“選項”按鈕，對話框中會延伸出“數(shù)據(jù)顯示方式”下拉式列表框，其中有9種顯示方式可以選擇，假定選擇“占同列數(shù)據(jù)總和的百分比”，點擊“確定”按鈕，字段的顯示方式也就發(fā)生了變化。,數(shù) 據(jù) 清 單 的 使用,5 改變字段標題的名稱,如果想修改字段標題名稱，可以選擇字段，點擊工具欄中的“字段”按鈕，或者雙擊該字段名稱，彈出“數(shù)據(jù)透視表字段”對話框，在名稱文本框中修

9、改字段的名稱； 最快捷的方式是單擊字段名稱，在Excel編輯欄中為字段改名。,數(shù) 據(jù) 清 單 的 使用,6 顯示明細數(shù)據(jù)：,點擊需要顯示明細數(shù)據(jù)的字段單元格，再單擊“數(shù)據(jù)透視表”工具欄中的“顯示明細數(shù)據(jù)”按鈕，彈出“顯示明細數(shù)據(jù)對話框”；或者點擊需要顯示明細數(shù)據(jù)的字段單元格，從右鍵快捷菜單中選擇“組及顯示明細數(shù)據(jù)顯示明細數(shù)據(jù)”，彈出“顯示明細數(shù)據(jù)”對話框，從中選擇明細數(shù)據(jù)中包含的字段； 如果在數(shù)據(jù)區(qū)域所在的列點擊“顯示明細數(shù)據(jù)”按鈕，明細數(shù)據(jù)清單中會包含所有的字段。,數(shù) 據(jù) 清 單 的 使用,在第一個數(shù)據(jù)透視表的基礎(chǔ)上，單擊“數(shù)據(jù)透視表”工具欄種的“向?qū)А卑粹o，返回“數(shù)據(jù)透視表和數(shù)據(jù)透視圖3步

10、驟3”對話框，將“品種”字段拖回原位置，將“銷售地區(qū)”字段移至“行字段”，“產(chǎn)品大類”字段不動，建立一個新的數(shù)據(jù)透視表； 選定“本市”的“塑料薄膜”單元格，再單擊“數(shù)據(jù)透視表”工具欄種的“顯示明細數(shù)據(jù)”，按鈕，就列出4種薄膜各個季度的銷售量：,數(shù) 據(jù) 清 單 的 使用,7 設(shè)置報告格式,點擊“數(shù)據(jù)透視表”工具欄中“設(shè)置報告格式”，按鈕，彈出“自動套用格式”對話框，共顯示了22種自動套用格式，從中可以選擇合適的格式修飾數(shù)據(jù)透視表。假設(shè)選擇“表2”，即產(chǎn)生如下報表：,數(shù) 據(jù) 清 單 的 使用,3.4 數(shù)據(jù)透視表的高級功能,1 數(shù)據(jù)組合 組合項目是數(shù)據(jù)透視表的一個有用的功能。它可以對行字段或列字段進

11、行組合。比如下面建立的一個數(shù)據(jù)透視表，如果想計算出1019、2029和3139歲的人數(shù)。點擊行字段標題“年齡”，單擊右鍵，選擇“組及顯示明細數(shù)據(jù)組合”，顯示“組合”對話框；,數(shù) 據(jù) 清 單 的 使用,在“起始于”后文本框中鍵入10，“步長”后文本框中鍵入10，點擊“確定”按鈕，數(shù)據(jù)透視表中的年齡字段就按照這三個年齡段重新進行組合計算。,數(shù) 據(jù) 清 單 的 使用,2 插入計算字段,從數(shù)據(jù)透視表的字段中創(chuàng)建新字段，計算字段必須位于數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域，不能在行、列或頁面區(qū)域內(nèi)使用計算字段； 數(shù)據(jù)透視表是一種特殊的數(shù)據(jù)區(qū)域，不能再數(shù)據(jù)透視表中插入新行或者新列，用戶不能使用插入公式計算數(shù)據(jù)透視表中的數(shù)

12、據(jù)，但是為數(shù)據(jù)透視表創(chuàng)建計算字段可以解決這個問題； 計算字段的公式不是標準的Excel函數(shù)，不能再單元格內(nèi)輸入公式，而是在對話框中輸入計算公式，它們和數(shù)據(jù)透視表中的數(shù)據(jù)一起存儲起來。,數(shù) 據(jù) 清 單 的 使用,在前面建立的數(shù)據(jù)透視表中，如果要根據(jù)訂單金額給銷售人員給與一定的提成或獎金，就可以利用計算字段功能來生產(chǎn)這個新的獎金或提成字段。,數(shù) 據(jù) 清 單 的 使用,點擊數(shù)據(jù)字段所在區(qū)域，從數(shù)據(jù)透視表工具欄中選擇“公式計算字段”，彈出“插入計算字段對話框； 在名稱后的文本框中鍵入插入字段的名字，比如獎金提成；從字段下的變量列表框中選擇訂購金額，點擊插入字段按鈕，將它移入公式文本框中，在后面鍵入公式

13、，比如乘以0.10，銷售人員按照銷售額的10%來提成，點擊確定按鈕，就生成一個新的計算字段。,數(shù) 據(jù) 清 單 的 使用,3 插入計算項目,計算項目使用數(shù)據(jù)透視表的一個字段中其它項目的內(nèi)容，計算項目必須位于數(shù)據(jù)透視表的行、列或頁面字段內(nèi)，不能在數(shù)據(jù)區(qū)域中使用計算字段。 計算字段是在數(shù)據(jù)源中增加新字段的一種可選的方法。,數(shù) 據(jù) 清 單 的 使用,下面的數(shù)據(jù)透視表中顯示了按照月份和銷售人員分類的銷售額，在這個透視表中使用“月份”作為行字段，可以為它創(chuàng)建一個計算項目，比如計算每一季度的銷售額，當然可以使用組合功能來進行組合，但是組合會隱藏單獨的月份，只顯示某組的合計，因此為每個季度的總和創(chuàng)建一個項目更

14、加靈活?？梢燥@示單獨月份的銷售額和每季度的總銷售額。,數(shù) 據(jù) 清 單 的 使用,將鼠標移動到要進行計算項目的數(shù)據(jù)透視表的行列或頁字段區(qū)域，本例中將光標移到月份字段； 從數(shù)據(jù)透視表中選擇“公式計算項”，彈出“在月份中插入計算字段對話框”，在名稱后的文本框中鍵入“一季度”，從“項”列表框中選擇“一月”，點擊“插入項”，將它移入公式文本框中，采用同樣方式，輸入一季度合計公式，點擊“添加”按鈕；采用同樣方式，為其它三個季度輸入公式； 單擊“確定”按鈕，完成計算項目的設(shè)定；,數(shù) 據(jù) 清 單 的 使用,創(chuàng)建好“季度”項目后，這些新項目顯示在數(shù)據(jù)透視表的下方，可以通過選擇和拖動來重新排列這些項目。,數(shù) 據(jù)

15、清 單 的 使用,3.5 數(shù)據(jù)透視圖的操作,1 創(chuàng)建數(shù)據(jù)透視圖的兩種方式： 方法一：在數(shù)據(jù)透視表工具欄中單擊“圖表向?qū)А?；彈出“圖表向?qū)А保x擇“簇狀柱型圖”，再按照繪制柱狀圖的步驟來創(chuàng)建數(shù)據(jù)透視圖；,數(shù) 據(jù) 清 單 的 使用,方法二： 在存放原始數(shù)據(jù)的數(shù)據(jù)清單中，選取任一單元格，單擊“數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之1”對話框中，將“所需創(chuàng)建的報表類型”選項改為“數(shù)據(jù)透視圖(及數(shù)據(jù)透視表)”； 單擊“下一步”按鈕，再單擊“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之2”的“下一步”按鈕，單擊“完成”按鈕，顯示數(shù)據(jù)透視圖雛形；,數(shù) 據(jù) 清 單 的 使用,在“數(shù)據(jù)透視圖”工具欄中，將“產(chǎn)品大類”字段拖到底部框中，將“銷售地區(qū)”字段拖到右側(cè)框中，將“全年”字段拖到中間放置數(shù)據(jù)的部位，然后單擊工具欄中的“圖表向?qū)А卑粹o，在“圖表向?qū)?步驟1圖表類型”中，選擇“簇狀柱形圖”；,數(shù) 據(jù) 清 單 的 使用,點擊“下一步”，進入“圖表向?qū)?步驟3圖表類型”，在“圖表標題”框中輸入“本市和外地三大類產(chǎn)品銷售比較圖”，在“分類軸”框中輸入“產(chǎn)品大類”，在數(shù)值軸中輸入“銷售量”，點擊“下一步”； 在“圖表向?qū)?步驟4圖表位置”，選擇默認方式，點擊完成，就生成數(shù)據(jù)透視圖：,數(shù) 據(jù) 清 單 的 使用,2 將數(shù)據(jù)透視圖更改為靜態(tài)圖,在創(chuàng)建