Excel資料透視表與資料透檢視的超詳細教程!
一、什麼是資料透視表?
資料透視表是一種可以快速彙總、分析大量資料表格的互動式分析工具。使用資料透視表可以按照資料表格的不同欄位從多個角度進行透視,並簡歷交叉表格,用以檢視資料表格不同層面的彙總資訊、分析結果以及摘要資料。
使用資料透視表可以深入分析數值資料,以幫助使用者發現關鍵資料,並作出有關企業中關鍵資料的決策。
二、資料來源
1、什麼是資料來源?
資料來源是指:用於建立資料透視表的資料來源。可以是Excel的資料列表、其他資料的透視表,也可以是外部的資料來源。
2、資料來源的原則:
每列資料的第一行包含該列的標題
資料來源中不能包含空行和空列
資料來源中不能包含空單元格
資料來源中不能包含合併單元格
資料來源中不能包含同類欄位(即既能當標題也能當資料資料內容)
三、建立資料透視表
1、建立資料來源
2、點選選單欄上的“插入”,選擇建立資料透視表
點選確定後,建立資料透視表成功
建立資料透視表後,想要檢視資料則點選右側欄位列表,先勾選哪個欄位,哪個欄位排在前面。
想要欄位出現在列,則點選欄位名,把欄位名拖進“資料透視表區域”裡的列區域裡,欄位就在列中展示。
四、資料透視表的基本術語和4大區域
1、基本術語
資料來源:用於建立資料透視表的資料來源,可以是單元區域、定義的名稱、另一個數據透視表資料或其他外部資料來源。
欄位:資料來源中各列的列標題,每個欄位代表一類資料。欄位可分為:報表篩選欄位、行欄位、列欄位、值欄位。
項:項是每個欄位中包含的資料,表示資料來源中欄位的唯一條目。
2、4大區域
行區域、列區域、值區域、報表篩選區域
五、資料透視表的基本操作
1、選擇資料透視表及表中單元格、欄位、項
2、重新命名資料透視表
3、複製、移動和刪除資料透視表
以上操作很簡單,略
六、資料透視表字段列表窗格
1、開啟/關閉透視表字段列表窗格
2、重新命名欄位
七、資料來源更新
資料來源修改了,資料透視表沒有修改,這時候就需要重新整理。
1、手動重新整理
資料來源區域未改變,如只是修改了欄位的某個值。
當前sku-01的金額為59
將sku-01的金額修改為50
沒重新整理之前的資料透視表的sku-01還是59
重新整理後的資料透視表的sku-01為50
資料來源區域改變,如刪除了資料來源的某些行或列、添加了某些行或列,資料來源的區域大小發生改變。
只需要將資料的前10條做資料透視表:
修改後的透視表
2、自動重新整理
開啟工作簿時,自動重新整理
重新整理後如何保持調整好的列寬?
選中資料透視表 -> 右鍵 -> 選中資料透視表選項 -> 佈局和格式 -> 取消勾選更新時自動調整列寬
3、如何清理垃圾條目?
當前資料來源的類目為乾果蜜餞、酒水飲料、母嬰用品
資料透視表的類目也為乾果蜜餞、酒水飲料、母嬰用品
現將資料來源中母嬰用品類目刪除
在資料透視表中重新整理,母嬰用品類目不見了。
但在類目篩選裡面還有母嬰用品類目,稱之為垃圾條目。
清理垃圾條目,選中資料透視表 -> 右鍵 -> 選中資料透視表選項 -> 資料 -> 在每個欄位保留的項數里選擇“無”即可。
八、資料透視表的三種佈局形式
壓縮佈局形式
大綱佈局形式
表格佈局形式
九、改變資料透視表的外觀
1、修改資料透視表的樣式
選中資料透視表 -> 設計 ,紅色方框裡就是資料透視表的的樣式
還可以自定義樣式
2、修改資料透視表的主題
3、修改資料透視表樣式三
在每組資料中插入空白行
合併資料透視表中的欄位項(當資料報表佈局為表格形式時)
十、設定分類彙總的顯示
1、關閉分類彙總顯示
2、自定義分類彙總
選中資料透視表 -> 右鍵 -> 欄位設定
十一、設定總計的顯示
十二、設定數字格式顯示
1、設定數字格式顯示
右鍵 -> 數字格式
2、設定空值和錯誤值的顯示方式
選擇資料透視表 -> 右鍵 -> 資料透視表選項 -> 佈局與格式 -> 格式
十三、條件格式的使用
1、突出顯示單元格規則
2、專案選取規則
3、資料條
結果:
4、色階
5、圖示集
6、新建規則
7、清除規則
8、管理規則
十四、排序
1、排序規則
型別:排序規則(升序)
數字:數字按從小到大排序
日期:越早的日期越小
文字:僅包含文字(中文或英文)可按字母或筆畫排序;若包含數字、字母、和各種符號,將按以下順序排列:
空格 0~9 ! “ # $ % & ( ) * , 。 / : ; ? @ [ \ ] ^ _ ‘ { | } ~ + < = > A~Z;
邏輯值:false < true
錯誤值:優先順序相同
空單元格:無論是升序還是降序排列,空格單元格總在最後
2、手動升序:
直接選擇要移動的單元格進行拖拽,或移動
3、自動升序:
4、其他方式排序:
(1)按筆畫排序
按第一個字的筆畫多少來排列。
若筆畫相同則按起筆的順序排序:橫、豎、撇、捺、折。
若前兩項都相同,則按字形結構排序:左右、上下、整體。
(2)使用排序對話方塊進行排序
(3)、自定義排序
(4)對篩選欄位進行排序
十五、篩選
1、透過篩選欄位進行篩選
2、對標籤進行篩選
3、值篩選
4、日期篩選
5、根據所選內容進行篩選
十六、切片器
切片器:用於選擇的選取器。
1、建立切片器
2、刪除切片器
在切片器上右鍵 ->
3、使用切片器
(1)在多個數據透視表中共享切片器
條件:同一個資料來源,同一快取
在任意一個數據透視表中建立切片器
在切片器上,右鍵 -> 報表連線
選擇要連線的報表
(2)更改切片器的顯示順序
當兩個切片器重疊的時候,會遮住另一個切片器
(3)對切片器中的欄位進行排序
在切片器上,右鍵 ->
十七、分組
資料必須在“行區” 或 “列區”裡
1、對數值資料分組
或
分組後的結果:
2、對文字資料分組
以第一個漢字相同的為一組
可以修改名稱
修改組名
3、對日期資料分組
注意事項:日期
必須
是日期資料型別,否則會出現“選定區域不能分組”!
十八、設定資料計算方式
1、在不該變資料來源的情況下,修改資料透視表的資料計算方式
在值區域的任意單元格上,右鍵,選擇“值顯示方式”
2、資料計算:指數
公式:((單元格的值)*(總體彙總之和))/((行彙總)*(列彙總))
十九、計算欄位
計算欄位也稱為虛擬欄位,在資料透視表中建立,不會出現在資料來源中。
計算欄位只能出現在值區域。
1、計算欄位的建立
2、修改和刪除計算欄位
二十、計算項
計算項是臨時新增的資料,不會改變資料來源。
計算項只能出現在列區域 或 行區域。
計算項不會出現在列表窗格中。
1、建立計算項
2、計算求解次序
3、列出公式
二十一、資料透檢視的建立與使用
1、資料透檢視與普通圖表的區別
(1)普通圖示的資料來源就是單元格,資料透檢視的資料來源即可以是資料工作表的資料、也可以是外部的資料庫。
(2)普通圖表可建立“簇狀柱形圖”(預設),按照分類來比較值;資料透檢視預設建立型別為“堆積柱形圖”,主要是用來比較各個值在整個分類總計當中,所佔的比例。對於資料透檢視而言,它不可以使用X、Y散點圖、氣泡圖、以及股價圖。
(3)普通圖示不具備互動性,屬於靜態圖表;資料透檢視具備良好的互動性,是一種動態圖表。
(4)普通圖示的格式,除非你手動刪除掉,否則永久的存在,不會丟失;資料透檢視可能重新整理之後就會丟失資料標籤、趨勢線、誤差線、以及對資料系列的一些更改。
2、資料透檢視與資料透視表的關係
如果資料透檢視是建立在資料透視表的基礎上,那麼資料透檢視的預設樣式由資料透視表決定。
資料透視表上的四大區域(篩選區、列區、行區、值區域)與資料透檢視一一對應,行區域對應資料透檢視的X軸,列區域對應資料透視表的Y軸,值區域對應資料透檢視當中的資料系列。
3、資料透檢視的限制
(1)不建立散點圖、氣泡圖、以及股價圖。
(2)資料透檢視當中無法切換資料來源的位置。
(3)無法調整資料標籤的大小。
(4)如果在資料透檢視當中,添加了趨勢線 ,而這個趨勢線所基於的資料透視表當中,新增或刪除這個欄位的時候,這些趨勢線就會消失。
4、建立資料透檢視
(1)根據資料透視表建立資料透檢視
(2)根據資料來源建立資料透檢視
(3)使用嚮導建立資料透檢視
5、移動資料透檢視
(1)透過複製、貼上或剪下的方式移動
(2)透過滑鼠移動
(3)透過選單移動
6、使用資料透檢視
(1)從不同佈局來檢視資料透檢視(像資料透視表一樣操作)
(2)顯示和關閉資料透檢視的欄位列表
或
(3)欄位按鈕的顯示與關閉
(4)新增趨勢線
7、重新整理資料透檢視
8、刪除資料透檢視
二十二、編輯透檢視
1、更改資料透檢視的型別
2、更改資料透檢視的樣式
3、設定資料透檢視的標題
4、設定資料透檢視的圖例
5、設定資料透檢視的標籤
6、設定資料透檢視的資料系列
更改資料系列圖表型別:
7、設定座標軸及其標題
將座標軸的單位修改方向:
8、設定資料透檢視的資料表
資料表是資料透檢視自帶的。有時候資料透視表與資料透檢視分開,這個時候可以自己新增一個數據表。
9、設定資料透檢視的誤差線
10、設定資料透檢視的網格線
二十三、美化資料透檢視
1、設定資料透檢視的繪圖區域和圖表
2、設定資料透檢視的背景牆和基底
注:背景牆和基底只針對三維圖形。
設定基底樣式:
設定背景牆樣式:和基底的操作一樣。
結果:
3、設定資料透檢視的文字樣式
4、使用資料透檢視模板
使用資料透檢視模板:
5、使用主題美化資料透檢視
二十四、將資料透檢視轉換為靜態資料圖
1、將資料透視錶轉換為靜態資料
透過靜態資料建立資料圖:
2、將資料透檢視轉換為圖片
3、直接刪除資料透視表
刪除資料透視表後,資料透檢視就是一個普通的圖。
4、斷開資料透檢視與資料透視表的連線
複製一份資料透視表到另一個工作表中,再把原來的資料透視表刪除。
二十五、切片器和迷你圖的使用
1、切片器
2、迷你圖
二十六、資料透視表的列印