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

1、數(shù) 據(jù) 清 單 的 使用,3 數(shù)據(jù)透視表,數(shù)據(jù)透視表是Excel中最具技術(shù)性的復(fù)雜組件之一，本質(zhì)上是從數(shù)據(jù)庫中產(chǎn)生的一個(gè)動(dòng)態(tài)匯總報(bào)告。數(shù)據(jù)透視表可以快速合并和比較大量數(shù)據(jù)，對分類變量進(jìn)行匯總并顯示明細(xì)數(shù)據(jù)。 數(shù)據(jù)透視表由字段(頁字段、行字段、列字段和數(shù)據(jù)字段)、項(xiàng)(頁字段項(xiàng)和數(shù)據(jù)項(xiàng))以及數(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ù)源的類型和報(bào)表類型，一般采用默認(rèn)選項(xiàng)，數(shù)據(jù)源類型為Microsoft Excel數(shù)據(jù)清單，報(bào)表

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

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

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

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

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

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

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

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

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

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

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

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

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

15、清 單 的 使用,3.5 數(shù)據(jù)透視圖的操作,1 創(chuàng)建數(shù)據(jù)透視圖的兩種方式： 方法一：在數(shù)據(jù)透視表工具欄中單擊“圖表向?qū)А?；彈出“圖表向?qū)А?，選擇“簇狀柱型圖”，再按照繪制柱狀圖的步驟來創(chuàng)建數(shù)據(jù)透視圖；,數(shù) 據(jù) 清 單 的 使用,方法二： 在存放原始數(shù)據(jù)的數(shù)據(jù)清單中，選取任一單元格，單擊“數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?步驟之1”對話框中，將“所需創(chuàng)建的報(bào)表類型”選項(xià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ù) 清 單 的 使用,點(diǎn)擊“下一步”，進(jìn)入“圖表向?qū)?步驟3圖表類型”，在“圖表標(biāo)題”框中輸入“本市和外地三大類產(chǎn)品銷售比較圖”，在“分類軸”框中輸入“產(chǎn)品大類”，在數(shù)值軸中輸入“銷售量”，點(diǎn)擊“下一步”； 在“圖表向?qū)?步驟4圖表位置”，選擇默認(rèn)方式，點(diǎn)擊完成，就生成數(shù)據(jù)透視圖：,數(shù) 據(jù) 清 單 的 使用,2 將數(shù)據(jù)透視圖更改為靜態(tài)圖,在創(chuàng)建