EXCEL數(shù)據(jù)透視表第一課.ppt

1、EXCEL數(shù)據(jù)透視表-從入門到精通,第一講：EXCEL數(shù)據(jù)透視表了解,你的EXCEL是什么版本？,查看excel版本信息,點(diǎn)擊excel左上角的office標(biāo)志 點(diǎn)excel選項(xiàng) 點(diǎn)“資源” 現(xiàn)在就可以看到詳細(xì)版本信息了，如下圖。 或者點(diǎn)“關(guān)于”按鈕，能看到更加詳細(xì)的信息,EXCEL表格里的三足鼎立,excel技巧操作千萬變化 excel函數(shù)以不變應(yīng)萬變 excel VBA無所不能（宏） 三者各有長短，互不相下 其中excel技巧陣營中的“老大”非“數(shù)據(jù)透視表”莫屬,EXCEL數(shù)據(jù)透視表了解課程目錄,1，什么是數(shù)據(jù)透視表 2，制作數(shù)據(jù)透視表 3，源表的整理 4，透視表的結(jié)構(gòu) 5，值的計(jì)算方式

2、6，報(bào)表布局 7，添加和刪除匯總項(xiàng),1，什么是數(shù)據(jù)透視表,理論上講，數(shù)據(jù)透視表是excel提供的一種交互式的強(qiáng)大數(shù)據(jù)分析和匯總工具。 使用數(shù)據(jù)透視表可以按照數(shù)據(jù)表格的不同字段從多個(gè)角度進(jìn)行透視，并建立交叉表格，用以查看數(shù)據(jù)表格不同層面的匯總信息、分析結(jié)果以及摘要數(shù)據(jù)。 總結(jié)：數(shù)據(jù)透視表是可以把一個(gè)明細(xì)表進(jìn)行分類匯總，而且可以隨意改變匯總模式的一種工具。,1，什么是數(shù)據(jù)透視表,交互式的理解,1，什么是數(shù)據(jù)透視表,明細(xì)表?分類匯總？隨意改變模式？工具？神馬東東？聽起來還是有點(diǎn)抽象，還是看圖比較真觀 銷售明細(xì)表：,根據(jù)地區(qū)匯總,根據(jù)地區(qū)和銷售員匯總,根據(jù)地區(qū)、銷售員、商品名稱匯總,根據(jù)月份、地區(qū)、銷

3、售員、商品名稱匯總,2，制作數(shù)據(jù)透視表,1 什么樣的表格需要做數(shù)據(jù)透視表 數(shù)據(jù)透視表的用途如果細(xì)數(shù)會有很多，但最基本、最常用的是“分類匯總”，所以當(dāng)我們需要對一個(gè)流水賬式的明細(xì)表進(jìn)行分類匯總時(shí)，就需要使用數(shù)據(jù)透視表。數(shù)據(jù)透視表的源數(shù)據(jù)即是“明細(xì)表”。如前面所講的銷售明細(xì)表。 2 什么樣的格式才能做出數(shù)據(jù)透視表 在制作數(shù)據(jù)透視表，需要注意以下幾點(diǎn)： 1 空字段名包括空列，無法制作數(shù)據(jù)透視表 2 相同的字段名，會自動添加序號，以示區(qū)別 3 字段所在行有合并單元格，等同于空字段，也無法創(chuàng)建數(shù)據(jù)透視表 4 如果有空行，會當(dāng)成空值處理,2，制作數(shù)據(jù)透視表,制作步驟： 第1步 選取銷售明細(xì)表， Excel

4、2003版：數(shù)據(jù)菜單 - 數(shù)據(jù)透視表 Excel2007版：插入選項(xiàng)卡 - 數(shù)據(jù)透視表,excel2003版,excel2007/2010版,2，制作數(shù)據(jù)透視表,第2步 執(zhí)行“數(shù)據(jù)透視表”命令后，會進(jìn)入數(shù)據(jù)透視表創(chuàng)建向?qū)?。（excel2003和excel2010稍有差異，但大體選項(xiàng)是一樣的）在向?qū)У牡谝徊揭话闶悄J(rèn)選項(xiàng)，不需要設(shè)置，直接點(diǎn)確定即可(excel2003版在第一步點(diǎn)“完成”按鈕)。,2，制作數(shù)據(jù)透視表,第3步 進(jìn)行前兩步后，會自動新建一個(gè)工作表，且在工作表中會有一個(gè)數(shù)據(jù)透視表空白區(qū)域，就象蓋樓房地基已打好，需要我們安排具體的房間位置。,2，制作數(shù)據(jù)透視表,添加數(shù)據(jù)透視表項(xiàng)目 A 把

5、地區(qū)拖動到行標(biāo)簽 B 把銷售量和銷售金額拖動到數(shù)值區(qū)域。,注意：excel2003版和excel2010添加方式不同，是選取字段，直接拖到數(shù)據(jù)透視表中的相對位置。如下圖所示。,2，制作數(shù)據(jù)透視表,拖動完成后，一個(gè)數(shù)據(jù)透視表的雛形已展現(xiàn)在我們的面前，如下圖所示。,3，源表的整理,制作成數(shù)據(jù)透視表之前，需要看看你的明細(xì)表是否存在下面的問題： 1 存在空列或沒有行標(biāo)題。,影響：明細(xì)表如果有標(biāo)題為空，將無法制作數(shù)據(jù)透視表。 整理：把標(biāo)題補(bǔ)充完整。,3，源表的整理,2 存在相同的標(biāo)題。 影響：數(shù)據(jù)透視表會自動添加序號以區(qū)分 整理：盡量不要存在相同的列標(biāo)題。,3，源表的整理,3 存在合并單元格 影響：合并

6、單元格除第1個(gè)格外，其他均作為空值處理。 整理：取消合并單元格，填充完整。,3，源表的整理,4 存在非法日期 影響：生成的數(shù)據(jù)透視表中，無法按日期格式進(jìn)行年、月、日格式的篩選和組合。 整理：轉(zhuǎn)換成excel認(rèn)可的日期格式。方法：選取列 - 分列 - 第3步選日期,3，源表的整理,5 存在文本型數(shù)字 影響：文本型數(shù)字將無法在數(shù)據(jù)透視表中正確求和。 整理：轉(zhuǎn)換成數(shù)值型數(shù)字。方法:選取列 - 分列 - 完成,3，源表的整理,綜上所述，一個(gè)無空標(biāo)題行、無重復(fù)行標(biāo)題、無合并單元格、無非法日期和數(shù)字格式的數(shù)據(jù)表，才是一個(gè)標(biāo)準(zhǔn)的數(shù)據(jù)透視表的數(shù)據(jù)源。,4，透視表的結(jié)構(gòu),操作數(shù)據(jù)透視表，需要先了解一下數(shù)據(jù)透視表

7、的結(jié)構(gòu)。 1 字段列表。明細(xì)表的所有第一行列標(biāo)題都會顯示在“字段列表”中。相當(dāng)于數(shù)據(jù)透視表的原材料基地。,4，透視表的結(jié)構(gòu),2 字段設(shè)置區(qū)域。把要顯示的字段拖放在相應(yīng)區(qū)域內(nèi)相當(dāng)于數(shù)據(jù)透視表的加工廠。加工的方式把相應(yīng)的字段拖入。,4，透視表的結(jié)構(gòu),3 數(shù)據(jù)透視表的顯示區(qū)域。組合成的數(shù)據(jù)透視表會在這兒顯示。,4，透視表的結(jié)構(gòu),A 報(bào)表篩選區(qū)域（excel2003版稱為頁字段）。如上圖所示，可以按“月”篩選數(shù)據(jù)透視表要顯示的內(nèi)容。 B 行標(biāo)簽（2003版稱為行字段）。該區(qū)域的字段會上下排列顯示，如上圖中“銷售地區(qū)”和“銷售員”字段。 C 列標(biāo)簽（2003版稱為列字段）。該區(qū)域的字段會左右排列顯示。如

8、下圖中的“地區(qū)”。 D 數(shù)值。要統(tǒng)計(jì)的數(shù)據(jù)一定要放在該欄內(nèi)才會進(jìn)行求和等統(tǒng)計(jì)。如上圖中的“銷量”。 字段拖放在不同的區(qū)域，就會以不同的顯示方式顯示匯總的結(jié)果。而且同一個(gè)區(qū)域內(nèi)的順序不同，在數(shù)據(jù)透視表內(nèi)匯總的先后層次也會不同。同學(xué)們可以拖動轉(zhuǎn)換一下各個(gè)區(qū)域的字段內(nèi)容，同時(shí)觀察數(shù)據(jù)透視表的變化。,5，值的計(jì)算方式,數(shù)據(jù)透視表的基本結(jié)構(gòu)一個(gè)重要的組成部分是“數(shù)值”區(qū)域。 我們需要把要求和的數(shù)值拖放到“數(shù)值”框內(nèi)進(jìn)行求和，如下圖所示。可有時(shí)結(jié)果卻顯示“計(jì)數(shù)”。,5，值的計(jì)算方式,原因：如果把含文本格式內(nèi)容的列拖放到值區(qū)域，統(tǒng)計(jì)方式默認(rèn)“計(jì)數(shù)”，顯示為“計(jì)數(shù)項(xiàng)：字段名”。本例中，一是誤把銷售員放在了值區(qū)

9、域，二是在銷售量列可能含有文本型內(nèi)容。 處理方法：把銷售員從值框中拖出去（刪除），然后雙擊“數(shù)量”列標(biāo)題，在彈出的“值字段設(shè)置”窗口中，把“計(jì)數(shù)”修改為“求和”，如下圖所示。,5，值的計(jì)算方式,從上圖也可以看到，數(shù)據(jù)透視表值的統(tǒng)計(jì)方式，不僅有求和、計(jì)數(shù)，還可以進(jìn)行最大值、最小值、標(biāo)準(zhǔn)偏差等計(jì)算。 最后介紹一個(gè)小技巧，上圖中，如果你想把“求和項(xiàng)：數(shù)量”改成“數(shù)量”會提示無法更改，解決方法是可以在字段名后添加一個(gè)空格。即“數(shù)量 ”。,5，值的計(jì)算方式,問題 數(shù)據(jù)透視表怎樣設(shè)置值的默認(rèn)顯示為求和項(xiàng)而不是計(jì)數(shù)項(xiàng)？ 這是根據(jù)你的數(shù)據(jù)列特等來的。一般如果你求和列有文本字符，那通常會默認(rèn)按計(jì)算項(xiàng)走，如果全是

10、數(shù)據(jù)型數(shù)字，通過會默認(rèn)按求和項(xiàng)走。,6，報(bào)表布局,生成數(shù)據(jù)透視表后，如果你是excel2003以上版本，你會看到多列的內(nèi)容會顯在一列中，如下圖所示。,6，報(bào)表布局,如果你想讓列分開，就需要調(diào)整數(shù)據(jù)透視表的報(bào)表布局。選取數(shù)據(jù)透視表 - 在頂部找到“設(shè)計(jì)”選項(xiàng)卡 - 再打開報(bào)表布局下拉菜單，會看到5種不同的布局方式： （圖為2010版本，2007版本只有3種）,6，報(bào)表布局,1 壓縮式布局。如本文開始所示。,6，報(bào)表布局,2 大綱式視圖。特點(diǎn)標(biāo)題分列顯示。匯總顯示在每項(xiàng)的上方,6，報(bào)表布局,3 以表格顯示形式。特點(diǎn)：標(biāo)題分列顯示。匯總顯示在每項(xiàng)的下方,6，報(bào)表布局,2010版本： 4 重復(fù)所有項(xiàng)目標(biāo)簽。可以填充相同的內(nèi)容。大家注意下圖和上圖的區(qū)別。 5 不重復(fù)所有項(xiàng)目標(biāo)答。取消重復(fù)。是4的逆操作。,7，添加和刪除匯總項(xiàng),默認(rèn)情況下，在數(shù)據(jù)透視表中添加行或列都會自動添加匯總項(xiàng)，如何調(diào)整隱藏或顯示匯總行/列呢？ 1 總計(jì)行：總計(jì)行分為行總計(jì)和列總計(jì)，如下圖所示。,7，添加和刪除匯總項(xiàng),控制它隱藏和顯示的命令在 數(shù)據(jù)透視表右鍵 - 選項(xiàng) - 如下圖所示。（2003版在數(shù)據(jù)透視表上右鍵 - 表格選項(xiàng) - 行和列總計(jì)選中）,7，添加和刪除匯總項(xiàng),2 分類匯總行