數(shù)據(jù)透視表的使用方法

對數(shù)據(jù)透視表有點(diǎn)迷糊，看到網(wǎng)上一篇講解文字很詳細(xì)，拿來分享給大家。

一、什么是數(shù)據(jù)數(shù)據(jù)透視表？

數(shù)據(jù)透視表是excel提供的一種交互式的強(qiáng)大數(shù)據(jù)分析和匯總工具。

是可以把一個明細(xì)表進(jìn)行分類匯總，而且可以隨意改變匯總模式的一種工具。

比如：

二、制作數(shù)據(jù)透視表

做數(shù)據(jù)透視表之前，我們需要了解以下兩個問題：

1 .什么樣的表格需要做數(shù)據(jù)透視表？

數(shù)據(jù)透視表的用途如果細(xì)數(shù)會有很多，但最基本、最常用的是“分類匯總”，所以當(dāng)我們需要對一個流水賬式的明細(xì)表進(jìn)行分類匯總時，就需要使用數(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)成空值處理

如果數(shù)據(jù)源表不規(guī)范，需要處理一下才能制作數(shù)據(jù)透視表，數(shù)據(jù)表的整理我們將在后面介紹。

第1步?點(diǎn)擊區(qū)域中任意一個單元格

第2步?插入- 數(shù)據(jù)透視表

第3步 會自動新建一個工作表，且在工作表中會有一個數(shù)據(jù)透視表空白區(qū)域，就像蓋樓房地基已打好，需要我們安排具體的房間位置。

右鍵調(diào)出數(shù)據(jù)透視表選項(xiàng)-顯示-經(jīng)典數(shù)據(jù)透視表布局

三：源表的整理。

1. 存在空列或沒有行標(biāo)題。

影響：明細(xì)表如果有標(biāo)題為空，將無法制作數(shù)據(jù)透視表。

整理: 把標(biāo)題補(bǔ)充完整。

2. 存在相同的標(biāo)題。

影響：數(shù)據(jù)透視表會自動添加序號以區(qū)分

整理：盡量不要存在相同的列標(biāo)題。

3. 存在合并單元格

影響：合并單元格除第1個格外，其他均作為空值處理。

整理：取消合并單元格，填充完整。

4. 存在非法日期。

影響：生成的數(shù)據(jù)透視表中，無法按日期格式進(jìn)行年、月、日格式的篩選和組合。

整理：轉(zhuǎn)換成excel認(rèn)可的日期格式。方法：選取列 - 分列 - 第3步選日期

5. 存在文本型數(shù)字

影響：文本型數(shù)字將無法在數(shù)據(jù)透視表中正確求和。

整理：轉(zhuǎn)換成數(shù)值型數(shù)字。方法:選取列 - 分列 - 完成

綜上所述，一個無空標(biāo)題行、無重復(fù)行標(biāo)題、無合并單元格、無非法日期和數(shù)字格式的數(shù)據(jù)表，才是一個標(biāo)準(zhǔn)的數(shù)據(jù)透視表的數(shù)據(jù)源。

四、數(shù)據(jù)透視表的結(jié)構(gòu)

1. 字段列表。明細(xì)表的所有第一行列標(biāo)題都會顯示在“字段列表”中。相當(dāng)于數(shù)據(jù)透視表的原材料基地。

2. 字段設(shè)置區(qū)域。把要顯示的字段拖放在相應(yīng)區(qū)域內(nèi)。相當(dāng)于數(shù)據(jù)透視表的加工廠。加工的方式把相應(yīng)的字段拖入。

3 .數(shù)據(jù)透視表的顯示區(qū)域。組合成的數(shù)據(jù)透視表會在這兒顯示。

字段拖放在不同的區(qū)域，就會以不同的顯示方式顯示匯總的結(jié)果。而且同一個區(qū)域內(nèi)的順序不同，在數(shù)據(jù)透視表內(nèi)匯總的先后層次也會不同(可以拖動轉(zhuǎn)換一下各個區(qū)域的字段內(nèi)容，同時觀察數(shù)據(jù)透視表的變化)。

五、值的計(jì)算方式

數(shù)據(jù)透視表的基本結(jié)構(gòu)，其中一個重要的組成部分是“數(shù)值”區(qū)域。我們需要把要求和的數(shù)值拖放到“數(shù)值”框內(nèi)進(jìn)行求和，可有時結(jié)果卻顯示“計(jì)數(shù)”。

原因：如果把含文本格式內(nèi)容的列拖放到值區(qū)域，統(tǒng)計(jì)方式默認(rèn)“計(jì)數(shù)”，顯示為“計(jì)數(shù)項(xiàng)：字段名”。本例中，一是誤把銷售員放在了值區(qū)域，二是在銷售量列可能含有文本型內(nèi)容。

處理方法：把銷售員從值框中拖出去（刪除），然后雙擊“數(shù)量”列標(biāo)題，在彈出的“值字段設(shè)置”窗口中，把“計(jì)數(shù)”修改為“求和”，如下圖所示。

介紹一個小技巧，上圖中，如果你想把“求和項(xiàng)：數(shù)量”改成“數(shù)量”會提示無法更改，解決方法是可以在字段名后添加一個空格。即“數(shù)量 ”。

六、數(shù)據(jù)透視表的布局方式

選取數(shù)據(jù)透視表 - 在頂部找到“設(shè)計(jì)”選項(xiàng)卡 - 再打開報表布局下拉菜單，會看到5種不同的布局方式。

練習(xí)的時候把5種布局方式多練習(xí)幾次，就會發(fā)現(xiàn)各自的特點(diǎn)及區(qū)別，在使用時選擇最適合自己的。

七、添加和刪除匯總項(xiàng)

默認(rèn)情況下，在數(shù)據(jù)透視表中添加行或列都會自動添加匯總項(xiàng)，如何調(diào)整隱藏或顯示匯總行/列呢？

1 .總計(jì)行------總計(jì)行分為行總計(jì)和列總計(jì)?？刂扑[藏和顯示的命令在 數(shù)據(jù)透視表右鍵 - 選項(xiàng)-匯總和篩選。

2. 分類匯總行------分類匯總主要是指行或列標(biāo)簽的分類匯總。想隱藏匯總，在數(shù)據(jù)透視表相應(yīng)的列上右鍵，取消“分匯匯總..”勾選，如果要恢復(fù)操作相同。

八、 數(shù)據(jù)透視表的美化。

新創(chuàng)建的數(shù)據(jù)透視表，就是一個無任何美化效果的表格。今天學(xué)習(xí)快速給數(shù)據(jù)透視表穿上美麗的外衣。

操作步驟：

1. 打開 選取數(shù)據(jù)透視表 - 頂端數(shù)據(jù)透視表選項(xiàng) - 設(shè)計(jì)

2 .點(diǎn)設(shè)計(jì) - 其他 - 打開更多數(shù)據(jù)透視表模塊，點(diǎn)擊后即套用該模板。

九、修改引用數(shù)據(jù)源。

如果明細(xì)表中行或列數(shù)發(fā)生了變化，數(shù)據(jù)透視表一般是不會自動調(diào)整數(shù)據(jù)源的，而需要手工去調(diào)整數(shù)據(jù)源的數(shù)據(jù)范圍。方法是：

選取數(shù)據(jù)透視表 - 最頂端數(shù)據(jù)透視表選項(xiàng)?-?更改數(shù)據(jù)源?- 重新選取范圍。

自動調(diào)整方法：把明細(xì)表設(shè)置為“數(shù)據(jù)表”，設(shè)置步驟是：選取明細(xì)表區(qū)域，插入?-?表格。設(shè)置完成后，再次增加新數(shù)據(jù)或新列，數(shù)據(jù)透視表就會自動更新數(shù)據(jù)源了。

十、數(shù)據(jù)透視表的刷新。

當(dāng)明細(xì)表更新數(shù)據(jù)后，以該明細(xì)表制的數(shù)據(jù)透視表也需要刷新才可以顯示新的匯總數(shù)據(jù)。

在數(shù)據(jù)透視表上的左鍵菜單上，點(diǎn)“刷新”即可完成數(shù)據(jù)刷新。

如果excel文件中有很多數(shù)據(jù)透視表，可以添加“全部刷新”按鈕。方法是

文件 - Excel選項(xiàng) - 快捷訪問工具欄 - 左側(cè)欄中找到“全部刷新” - 點(diǎn)擊“添加”按鈕即可添加到“快捷訪問工具欄”。

點(diǎn)擊“全部刷新”命令可以刷新本工作薄所有數(shù)據(jù)透視表。

十一、數(shù)據(jù)透視表的移動和刪除。

1.移動和復(fù)制

選取包含數(shù)據(jù)透視表的區(qū)域，把光標(biāo)放在選取區(qū)域邊緣后，直接拖動到指定的區(qū)域放下即可。如果在拖動時按ctrl鍵。移動變?yōu)閺?fù)制數(shù)據(jù)透視表。

2.刪除

選取包括所有數(shù)據(jù)透視表元素的區(qū)域，包括透視表篩選區(qū)域和行列及數(shù)值標(biāo)簽區(qū)域，按del鍵即可刪除。

十二、篩選數(shù)值項(xiàng)

要篩選值，可以借助行標(biāo)簽的篩選值功能。比如篩選數(shù)量大于30的行。

步驟：

打開最右一個行標(biāo)簽的按鈕，值篩選?-?大于?- 輸入30，點(diǎn)確定后即可篩選出我們需要的結(jié)果。

設(shè)置篩選條件。

另：選取“求和項(xiàng)：單價”右邊單元格 ，執(zhí)行 數(shù)據(jù) - 篩選命令，即可使用excel的自帶篩選功能。

十三、固定數(shù)字和表格格式

在數(shù)據(jù)透視表中，好不容易設(shè)置的數(shù)字格式，以及列寬，在刷新后全部恢復(fù)了原狀，設(shè)置的格式全部消失。怎么才能固定格式呢？

解決方法：

在數(shù)據(jù)透視表上右鍵 - 數(shù)據(jù)透視表選項(xiàng) - 去掉“更新時自動調(diào)整列寬”，勾選“更新時保留數(shù)字格式”

可能大家發(fā)現(xiàn)，即使進(jìn)行了上面的設(shè)置，更新時 設(shè)置的數(shù)字格式還是會變。比如下圖中，設(shè)置了廣州的數(shù)字是“千位分隔符”樣式，但地區(qū)換成“蘇州”時，還會有的數(shù)字非千位分隔符樣式。

解決上述問題的方法：把地區(qū)放在行標(biāo)簽，然后統(tǒng)一設(shè)置數(shù)字的格式為千位分隔符樣式。然后再篩選所有地區(qū)，數(shù)字格式均為千位分隔符樣式。

十四、選取

數(shù)據(jù)透視表有值、行標(biāo)簽等元素構(gòu)成，怎么能快速選取它們呢？

1 .全選數(shù)據(jù)透視表

光標(biāo)放在數(shù)據(jù)透視表上，最上面數(shù)據(jù)透視表工具 - 布局 - 選擇 - 整個數(shù)據(jù)透視表。

2. 選取值或行列標(biāo)簽部門。

在第1步中我們發(fā)現(xiàn)除了全選外，其他的都是灰色。這是因?yàn)楫?dāng)你選取的范圍內(nèi)包括值等元素時，這些才能用，所以要選取這些內(nèi)容，先全選，然后再選值或行列標(biāo)簽。

3 選取某行、某列。

當(dāng)你把光標(biāo)放在數(shù)據(jù)透視表的某行或某列前面時，你會發(fā)現(xiàn)光標(biāo)變成了黑箭頭。這時你只需要單擊就可以選取所有和本行列有關(guān)的行或列了。