【實用教程】數據分析利器--EXCEL 數據透視表(一)

一一數據透視表是excel中最有用的功能,沒有之一。下面是Excel數據透視表入門教程,共14集。

一一Excel表格里三足鼎立:excel技巧操作千萬變化;excel函數以不變應萬變;excel VBA無所不能。三者各有長短,互不相下。其中excel技巧陣營中的「老大」非「數據透視表」莫屬。

第1講:什麼是數據數據透視表?

一一在學習excel數據透視表之前,我們有必要了解它的概念。什麼是數據透視表呢?

一一理論上講,數據透視表是excel提供的一種互動式的強大數據分析和匯總工具。可以總結為:數據透視表是可以把一個明細表進行分類匯總,而且可以隨意改變匯總模式的一種工具。

一一明細表?分類匯總?隨意改變模式?工具?神馬東東?聽起來還是有點抽象,還是看圖比較真觀。

一一銷售明細表:

一一根據地區匯總

一一根據地區和銷售員匯總

一一根據地區、銷售員、商品名稱匯總

一一根據月份、地區、銷售員、商品名稱匯總

一一好吧!因為篇幅關係只能展示這幾種匯總方式。也許你認為我函數水平高超,我會說我一個函數和公式也沒用;也許你會說你是不是用了傳說中的宏,對不起,我沒寫一句代碼。以上。。。全是excel數據透視表實現的,而且展示的也只是數據透視表最最最。。基礎的匯總功能!!

第2講:製作數據透視表

一一第一講中我們簡單介紹了什麼是excel數據透視表,關於數據透視表的基本概念還有很多,如果現在拿來全講,估計同學們聽起來會象天書一樣。基本的概念我們可以在後面逐步介紹,我想大家現在最想做的,就是象第一講中,根據數據表自已親手創建一個數據透視表。

一一做數據透視表之前,我們需要了解以下兩個問題:

一一1 什麼樣的表格需要做數據透視表

一一數據透視表的用途如果細數會有很多,但最基本、最常用的是「分類匯總」,所以當我們需要對一個流水賬式的明細表進行分類匯總時,就需要使用數據透視表。數據透視表的源數據即是「明細表」。如第1講中的銷售明細表。

一一2 什麼樣的格式才能做出數據透視表

一一在製作數據透視表,需要注意以下幾點:

一1 空欄位名包括空列,無法製作數據透視表一2 相同的欄位名,會自動添加序號,以示區別 一3 欄位所在行有合併單元格,等同於空欄位,也無法創建數據透視表 一4 如果有空行,會當成空值處理

一一如果數據源表不規範,需要處理一下才能製作數據透視表,數據表的整理我們將在第3講介紹。

一一下面我們就要親手製作數據透視表了。

一一原材料:銷售明細表(點微信右下角閱讀原文 下載示例表格)

一一製作步驟:

一一第1步 選取銷售明細表,excel2003版 : 數據菜單 - 數據透視表(Excel2010版:插入選項卡 - 數據透視表 )

一一excel2003版

一一excel2010版

一一第2步 執行「數據透視表」命令後,會進入數據透視表創建嚮導。(excel2003和excel2010稍有差異,但大體選項是一樣的)在嚮導的第一步一般是默認選項,不需要設置,直接點確定即可(excel2003版在第一步點「完成」按鈕)。

一一第3步 進行前兩步後,會自動新建一個工作表,且在工作表中會有一個數據透視表空白區域,就象蓋樓房地基已打好,需要我們安排具體的房間位置。

一一添加數據透視表項目

一一A 把地區拖動到行標籤

一一B 把銷售量和銷售金額拖動到數值區域。

一一注意:excel2003版和excel2010添加方式不同,是選取欄位,直接拖到數據透視表中的相對位置。如下圖所示。

一一拖動完成後,一個數據透視表的雛形已展現在我們的面前,如下圖所示。

一一在欣喜自已成功創建數據透視表之餘,同樣會有很多疑問伴隨而來:什麼是行標籤?如何修改數據透視表的匯總方式?如何修改數據透視表的格式...呵呵,別急..一口吃不成胖子,以後會一一幫你揭開謎底。

第3講:源表的整理

一一製作成數據透視表之前,你需要看看你的明細表是否存在下面的問題:

一一1 存在空列或沒有行標題。

一一影響:明細表如果有標題為空,將無法製作數據透視表。

一一整理: 把標題補充完整。

一一2 存在相同的標題。

一一影響:數據透視表會自動添加序號以區分

一一整理:盡量不要存在相同的列標題。

一一3 存在合併單元格

一一影響:合併單元格除第1個格外,其他均作為空值處理。

一一整理:取消合併單元格,填充完整。

一一4 存在非法日期。

一一影響:生成的數據透視表中,無法按日期格式進行年、月、日格式的篩選和組合。

一一整理:轉換成excel認可的日期格式。方法:選取列 - 分列 - 第3步選日期

一一5 存在文本型數字

一一影響:文本型數字將無法在數據透視表中正確求和。

一一整理:轉換成數值型數字。方法:選取列 - 分列 - 完成

一一綜上所述,一個無空標題行、無重複行標題、無合併單元格、無非法日期和數字格式的數據表,才是一個標準的數據透視表的數據源。

第4講:數據透視表的結構

一一今天我們要了解數據透視表的基本結構。

一一1 欄位列表。明細表的所有第一行列標題都會顯示在「欄位列表」中。相當於數據透視表的原材料基地。

一一2 欄位設置區域。把要顯示的欄位拖放在相應區域內。相當於數據透視表的加工廠。加工的方式把相應的欄位拖入。

一一3 數據透視表的顯示區域。組合成的數據透視表會在這兒顯示。

一一A 報表篩選區域(excel2003版稱為頁欄位)。如上圖所示,可以按「月」篩選數據透視表要顯示的內容。

一一B 行標籤(2003版稱為行欄位)。該區域的欄位會上下排列顯示,如上圖中「銷售地區」和「銷售員」欄位。

一一C 列標籤(2003版稱為列欄位)。該區域的欄位會左右排列顯示。如下圖中的「地區」。

一一D 數值。要統計的數據一定要放在該欄內才會進行求和等統計。如上圖中的「銷量」。

一一欄位拖放在不同的區域,就會以不同的顯示方式顯示匯總的結果。而且同一個區域內的順序不同,在數據透視表內匯總的先後層次也會不同。同學們可以拖動轉換一下各個區域的欄位內容,同時觀察數據透視表的變化。

第5講:值的計算方式

一一上講學習了數據透視表的基本結構,其中一個重要的組成部分是「數值」區域。我們需要把要求和的數值拖放到「數值」框內進行求和,如下圖所示。可有時結果卻顯示「計數」。

一一原因:如果把含文本格式內容的列拖放到值區域,統計方式默認「計數」,顯示為「計數項:欄位名」。本例中,一是誤把銷售員放在了值區域,二是在銷售量列可能含有文本型內容。

一一處理方法:把銷售員從值框中拖出去(刪除),然後雙擊「數量」列標題,在彈出的「值欄位設置」窗口中,把「計數」修改為「求和」,如下圖所示。

一一結果如下圖

一一從上圖也可以看到,數據透視表值的統計方式,不僅有求和、計數,還可以進行最大值、最小值、標準偏差等計算。

一一本講最後介紹一個小技巧,上圖中,如果你想把「求和項:數量」改成「數量」會提示無法更改,解決方法是可以在欄位名後添加一個空格。即「數量 」。

第6講:數據透視表的布局方式

一一成數據透視表後,如果你是excel2003以上版本,你會看到多列的內容會顯在一列中,如下圖所示。

一一如果你想讓列分開,就需要調整數據透視表的報表布局。選取數據透視表 - 在頂部找到「設計」選項卡 - 再打開報表布局下拉菜單,會看到5種不同的布局方式:

一一1 壓縮式布局。如本文開始所示。

一一2 大綱式視圖。特點標題分列顯示。匯總顯示在每項的上方

一一3 以表格顯示形式。特點:標題分列顯示。匯總顯示在每項的下方

一一4 重複所有項目標籤。可以填充相同的內容。大家注意下圖和上圖的區別。

一一5 不重複所有項目標答。取消重複。是4的逆操作。

TOP10 精彩導航(回復文章前數字閱讀)

19 EXCEL在寬頻薪酬調整中的應用

17 Excel自動到期提醒,狠實用!

14 那些顛覆你三觀的Word隱藏技能

15 Excel技巧大全,接住!

11 圖解麥肯錫方法---十種高效思考方式

18 酷炫excel2013體驗

12 Excel自動篩選的9個高級用法

01 瀑布圖,你會用嗎?

03 做彙報PPT時,有哪些好用到爆的快捷鍵?

04 EXCEL數據有效性——行政人事必備

推薦閱讀:

練好接發球之步法移動的十二個利器
盾堅矛銳、警界利器
手機成'哄娃利器'? 爺爺奶奶:為了讓孩子乖一點
伊朗依靠一件利器,將其千年死敵——伊拉克變為密友
試盤買入法---贏家利器(5)【偉臣看盤】

TAG:數據分析 | 數據 | 透視 | 實用 | 教程 | 數據透視表 | 利器 | 分析 |