【實用教程】數據分析利器--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)【偉臣看盤】