制作數(shù)據(jù)透視表課件

1、Excel應(yīng)用技術(shù)之提高篇-2數(shù)據(jù)透視表和數(shù)據(jù)透視圖合并計(jì)算主要內(nèi)容單表數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖多表創(chuàng)建動態(tài)數(shù)據(jù)透視表多表創(chuàng)建單頁字段數(shù)據(jù)透視表多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表利用數(shù)據(jù)透視表快速統(tǒng)計(jì)重復(fù)項(xiàng)目數(shù)據(jù)透視表和數(shù)據(jù)透視圖 Excel的數(shù)據(jù)透視表是一種可以輕松的對數(shù)據(jù)進(jìn)行交叉分析的三維表格。它的特點(diǎn)在于表格的不固定性，可以隨時根據(jù)實(shí)際需要進(jìn)行調(diào)整，從而得到不同的視圖。它可以將數(shù)據(jù)的排序、篩選和分類匯總3個過程結(jié)合在一起，可以轉(zhuǎn)換行和列查看數(shù)據(jù)的不同匯總結(jié)果，可以顯示不同頁面來篩選數(shù)據(jù)，還可以根據(jù)需要顯示指定區(qū)域的明細(xì)數(shù)據(jù)，非常便于用戶在一個清單中重新組織和統(tǒng)計(jì)數(shù)據(jù)。數(shù)據(jù)透視表和數(shù)據(jù)

2、透視圖一、 單表數(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ù)透視圖一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖改為“訂貨日期”改為“城市”關(guān)閉數(shù)據(jù)透視表字段列表一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖2.創(chuàng)建組右鍵單擊一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖2.創(chuàng)建組 一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖3.篩選數(shù)據(jù)(1)篩選數(shù)據(jù)一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖3.篩選數(shù)據(jù)(3)篩選數(shù)據(jù)一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖4.美化表格一、 單表數(shù)

3、據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖5.增加列一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖6.改變報表布局一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖7.創(chuàng)建數(shù)據(jù)篩選頁數(shù)據(jù)篩選頁的效果一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖7.創(chuàng)建數(shù)據(jù)篩選頁一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖8.創(chuàng)建數(shù)據(jù)透視圖一、 單表數(shù)據(jù)源做數(shù)據(jù)透視表和數(shù)據(jù)透視圖8.創(chuàng)建數(shù)據(jù)透視圖可以篩選地區(qū)可篩選年份二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表首先，在功能區(qū)上鼠標(biāo)右鍵，選擇【自定

4、義快速訪問工具欄】。 添加數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)В菏紫龋诠δ軈^(qū)上鼠標(biāo)右鍵，選擇【自定義快速訪問工具欄】。Alt+D+P二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表單擊“列標(biāo)簽”，選擇“金額”和“數(shù)量”，如圖：二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表二、多表創(chuàng)建單頁字段數(shù)據(jù)透視表三、多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表以上題的數(shù)據(jù)源為例講解頁字段:在數(shù)據(jù)透視表或數(shù)據(jù)透視圖報表中指定為 頁方向的字段三、多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表頁字段別忘了，要和上面配套哦三、多表創(chuàng)建自

5、定義頁字段數(shù)據(jù)透視表三、多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表三、多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表三、多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表三、多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表三、多表創(chuàng)建自定義頁字段數(shù)據(jù)透視表多表創(chuàng)建數(shù)據(jù)透視表創(chuàng)建多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表：數(shù)據(jù)源可以是同一個工作簿的多張工作表，也可以是其他工作簿的多張工作表，但待合并的數(shù)據(jù)源工作表結(jié)構(gòu)必須完全一致，當(dāng)數(shù)據(jù)內(nèi)容更改時，可以刷新數(shù)據(jù)透視表來更新數(shù)據(jù)透視表，但當(dāng)數(shù)據(jù)源的數(shù)據(jù)量更改時則不可以更新數(shù)據(jù)透視表。怎么創(chuàng)建動態(tài)的數(shù)據(jù)透視表呢：即記錄量增減時，能自動更新建數(shù)據(jù)透視表？四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透

6、視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表OFFSET() 格式： OFFSET(引用,行位移,列位移,行數(shù),列數(shù))功能：以指定的引用為參照系，通過給定偏移量得到新的引用。“行位移,列位移,行數(shù),列數(shù)”均采用整數(shù)截?cái)?。返回的引用可以為一個單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。例如：在A1中輸入公式 =SUM(OFFSET(A2,1,2,5,3) 得到的結(jié)果是區(qū)域$C$3:$E$7 的和。 四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表函數(shù)：sum(offset(b3,2,1,3,2)、sum(c5:d7)、sum(11,13,15,15,18,21)等價四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)

7、建動態(tài)數(shù)據(jù)透視表1.定義數(shù)據(jù)源名稱(公式/定義的名稱/名稱管理器)=OFFSET(河?xùn)|區(qū)!$A$1,COUNTA(河?xùn)|區(qū)!$A:$A),COUNTA(河?xùn)|區(qū)!$1:$1)四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表參照位置所有行所有列COUNTA($A:$A)統(tǒng)計(jì)A列有數(shù)據(jù)的行數(shù) COUNTA($1:$1)統(tǒng)計(jì)第1行有數(shù)據(jù)的列數(shù)。 四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表1.定義數(shù)據(jù)源名稱(公式/定義的名稱/名稱管理器)四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表2.打開數(shù)據(jù)表和數(shù)據(jù)透視圖向?qū)б晥D四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表2.打開數(shù)據(jù)表和數(shù)據(jù)透視圖向?qū)б晥D四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表 在選定區(qū)域框中輸入“河?xùn)|區(qū)”（即第一步中定義的名稱），并單擊

8、“添加”按鈕，按同樣的方法將四個區(qū)域添加到所有區(qū)域下拉列表中。 并選中頁字段數(shù)目“1”單選按鈕。3.添加數(shù)據(jù)源四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表選中“北辰區(qū)”，在“字段1”文本框中輸入“北辰區(qū)”，再選中“河西區(qū)”，在“字段1”文本框中輸入“河西區(qū)”，按同樣的方法把別的區(qū)域做完4.給頁字段命名類似上題的篩選項(xiàng)哦四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表右鍵單擊5.生成數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表選擇任意一個第一列上數(shù)據(jù)。同樣方法修改“行標(biāo)簽”為“品名”修改“頁1”為“地區(qū)”四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表6.組合數(shù)據(jù)選中電器類產(chǎn)品四、多表創(chuàng)

9、建動態(tài)數(shù)據(jù)透視表6.組合數(shù)據(jù)選中，右鍵單擊四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表組合數(shù)據(jù)四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表6.組合數(shù)據(jù)按同樣方法分組四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表單擊【數(shù)據(jù)透視表工具】|【設(shè)計(jì)】|【報表布局】|【以表格形式顯示】。7. 更改報表布局四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表將“品名2”改名為“類別”，在分類匯總下拉列表框中選擇“求和”，然后更改單元格“數(shù)據(jù)組1”的名字為“電器”，同樣更改“”數(shù)據(jù)組2為“其它”等等8.更改部分名稱，符合常態(tài)理解選中電器組合項(xiàng)下的任一單元格，如A6，單擊鼠標(biāo)右鍵，彈出快捷菜單，選單擊“數(shù)據(jù)透視表選項(xiàng)”，打開“數(shù)據(jù)透視表選項(xiàng)”對話框，“布局和格式”選項(xiàng)卡中的“布局”，勾選“

10、合并且居中排列帶標(biāo)簽的單元格”。四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表單擊右鍵9.選擇顯示列字段四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表10.美 化 表 格四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表 使用定義名稱的方法創(chuàng)建的數(shù)據(jù)透視表是動態(tài)的，即在數(shù)據(jù)源表中可增減記錄。但是要求數(shù)據(jù)源區(qū)域中的首行和首列不能有空白單元格，否則將無法用定義名稱取得正確的區(qū)域。 數(shù)據(jù)有變化時，選擇透視表區(qū)域，單擊右鍵，選擇“刷新”數(shù)據(jù)透視表的刷新方法1：在數(shù)據(jù)透視表的任意單元格上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊【刷新】。如下圖。方法2：在【數(shù)據(jù)】選項(xiàng)卡中單擊【全部刷新】。四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表數(shù)據(jù)透視表的刷新方法3，打開工作

11、簿時刷新，右鍵單擊數(shù)據(jù)透視表的任意單元格，彈出如下快捷菜單，選擇“數(shù)據(jù)透視表選項(xiàng)”。選擇此項(xiàng)數(shù)據(jù)透視表的排序在需要排序的列的任意非字段單元格上單擊右鍵，在彈出的快捷菜單中選擇【排序/升序】,如下圖。四、多表創(chuàng)建動態(tài)數(shù)據(jù)透視表五、使用數(shù)據(jù)透視表快速統(tǒng)計(jì)重復(fù)項(xiàng)目使用Excel函數(shù)公式可以統(tǒng)計(jì)數(shù)據(jù)列表中某個字段的重復(fù)次數(shù)，但更為快捷的方式是使用數(shù)據(jù)透視表。統(tǒng)計(jì)各部門的人數(shù)五、使用數(shù)據(jù)透視表快速統(tǒng)計(jì)重復(fù)項(xiàng)目五、利用數(shù)據(jù)透視表快速統(tǒng)計(jì)重復(fù)項(xiàng)目 插入切片器 選擇數(shù)據(jù)透視表工具下的選項(xiàng)，點(diǎn)擊插入切片器 在彈出的“插入切片器”對話框中勾選“姓名”和“組別” 選項(xiàng)。單擊“確定”按鈕。五、利用數(shù)據(jù)透視表快速統(tǒng)計(jì)重

12、復(fù)項(xiàng)目關(guān)閉切片器 單擊清除篩選器按鈕或使用Alt+C鍵即可關(guān)閉切片器。 如果要刪除切片器，選擇某個切片器，按Delete鍵即可。五、利用數(shù)據(jù)透視表快速統(tǒng)計(jì)重復(fù)項(xiàng)目五、利用數(shù)據(jù)透視表快速統(tǒng)計(jì)重復(fù)項(xiàng)目六、在數(shù)據(jù)透視表中添加計(jì)算項(xiàng)數(shù)據(jù)透視表提供了強(qiáng)大的自動匯總功能，除了“求和”、“計(jì)數(shù)”、“求平均”、“方差”等多種匯總方式。如不能滿足需求還可以在數(shù)據(jù)透視表中添加計(jì)算項(xiàng)的方法達(dá)到目的。六、在數(shù)據(jù)透視表中添加計(jì)算項(xiàng)六、在數(shù)據(jù)透視表中添加計(jì)算項(xiàng)先選中數(shù)據(jù)透視表的“列標(biāo)簽”單元格，在【數(shù)據(jù)透視表工具】的【選項(xiàng)/域、項(xiàng)目和集】中選擇“計(jì)算項(xiàng)”，如圖：七、更改數(shù)據(jù)透視表的數(shù)據(jù)源 當(dāng)數(shù)據(jù)源中添加新的記錄時，刷新數(shù)

13、據(jù)透視表，新增的記錄不會自動添加到數(shù)據(jù)透視表中，（當(dāng)記錄內(nèi)容更改時，刷新紀(jì)錄，能實(shí)現(xiàn)數(shù)據(jù)透視表的更新），此時可以更改數(shù)據(jù)透視表的數(shù)據(jù)源，使數(shù)據(jù)透視表的數(shù)據(jù)源包括新增記錄。七、更改數(shù)據(jù)透視表的數(shù)據(jù)源原數(shù)據(jù)七、更改數(shù)據(jù)透視表的數(shù)據(jù)源原數(shù)據(jù)透視表七、更改數(shù)據(jù)透視表的數(shù)據(jù)源更改數(shù)據(jù)源七、更改數(shù)據(jù)透視表的數(shù)據(jù)源七、更改數(shù)據(jù)透視表的數(shù)據(jù)源重新選擇數(shù)據(jù)源七、更改數(shù)據(jù)透視表的數(shù)據(jù)源合并計(jì)算利用合并計(jì)算創(chuàng)建分戶報表有選擇的合并計(jì)算多工作表篩選不重復(fù)的值利用合并計(jì)算對數(shù)值型數(shù)據(jù)進(jìn)行校對利用合并計(jì)算對文本型數(shù)據(jù)進(jìn)行校對一、認(rèn)識合并計(jì)算Excel中的“合并計(jì)算”功能可以匯總或合并多個數(shù)據(jù)源區(qū)域中的數(shù)據(jù)。合并計(jì)算的數(shù)據(jù)

14、源區(qū)域可以是同一個工作表中的不同表格，也可以是同一工作簿中的不同工作表，還可以是不同工作簿中的表格。求各城市的銷售數(shù)量和金額選中某一單元格，作為合并計(jì)算后結(jié)果的存放起始位置，再單擊【數(shù)據(jù)/合并計(jì)算】命令按鈕，打開【合并計(jì)算】對話框。一、認(rèn)識合并計(jì)算結(jié)果演示修飾后注意：在使用按類別合并的功能時，數(shù)據(jù)源列表必須包含行或列標(biāo)題，并且在【合并計(jì)算】對話框的【標(biāo)簽位置】組合框中勾選相應(yīng)的復(fù)選框。合并的結(jié)果表中包含行列標(biāo)題，但在同時選中的【首行】和【最左列】復(fù)選項(xiàng)時，所生成的合并結(jié)果表會缺失第一列的標(biāo)題。合并后，結(jié)果表的數(shù)據(jù)項(xiàng)排列順序是按第一個數(shù)據(jù)表的數(shù)據(jù)項(xiàng)順序排列。合并計(jì)算過程不能復(fù)制數(shù)據(jù)表的格式，如果

15、設(shè)置結(jié)果表的格式，可以使用【格式刷】。一、認(rèn)識合并計(jì)算二、利用合并計(jì)算創(chuàng)建分戶報表合并計(jì)算可以按類別進(jìn)行合并，如果引用區(qū)域的行、列方向均包含了多個類別時，則可以利用合并計(jì)算功能將引用區(qū)域中的全部類別匯總到同一表格上并顯示所有明細(xì)。二、利用合并計(jì)算創(chuàng)建分戶報表三、有選擇的合并計(jì)算五月份銷售情況六月份銷售情況七月份銷售情況三、有選擇的合并計(jì)算在需要進(jìn)行“匯總”的工作表A1:C1單元格區(qū)域，分別輸入所需匯總的列字段名稱：“日期”，“銷售數(shù)量”和“銷售金額”，然后選中A1:C1單元格區(qū)域，這是最關(guān)鍵的步驟。單擊【數(shù)據(jù)/合并計(jì)算】命令，打開【合并計(jì)算】對話框。三、有選擇的合并計(jì)算三、有選擇的合并計(jì)算修改為日期格式三、有選擇的合并計(jì)算做數(shù)據(jù)透視表后