1、創建數據透視表
菜單【插入】中,選擇“數據透視表”,打開“創建數據透視表”對話框,選擇數據源。這裡選擇數據源區域A1:E8
2、數據源刷新
如果修改了數據源,那麼只需要單擊菜單中的“刷新”按鈕,即可更新數據透視表。
3、更改數據源
通過菜單中的“更改數據源”可以修改數據透視表的數據源。
4、數據透視表篩選
通過數據透視表的篩選器,可以篩選數據透視表。
例如,需要按照日期進行篩選,將日期字段拖到“篩選”中即可
5、刪除數據透視表
刪除透視表也很簡單,全選之後,按“Delete”鍵,即可刪除數據透視表。
二、布局樣式
1、套用樣式
通過Excel自帶的數據透視表樣式可以對數據透視表進行美化,
2、以表格形式顯示
有時候,選擇了多個行字段,數據透視表會呈現出以下形式。
此時只需在“報表布局”中選擇“以表格形式顯示”即可。
以表格形式顯示的數據透視表。
3、隱藏/顯示匯總項
如果不想顯示中間的分類匯總項,可以通過“分類匯總”-“不顯示分類匯總”來隱藏分類匯總項。
4、重複所有項目標籤
如果需要第一列的標籤重複顯示,選擇“重複所有項目標籤”即可。
5、合并標籤所在單元格
右擊鼠標,選擇“數據透視表”選項
勾選“合并且居中排列帶標籤的單元格”
三、計算排序
1、更改計算方式
在以上數據透視表中,對於“金額”字段的計算方式默認是求和,其實我們可以自行更改計算方式。
在值區域中,單擊“求和項:金額”,打開“值字段設置”,可以選擇計算方式,如求和、計數、平均值、最大值、最小值等。
2、求佔比(百分比)
前面已經對金額進行了求和,如果想求佔比,可以做如下操作。
(1)將“金額”再次拖到值區域,得到“求和項:金額2”。
(2)在“值顯示方式”中,選擇“列匯總的百分比”
“確定”後,得到金額的佔比。
3、插入計算字段
在數據透視表中,還可以創建計算字段。例如,給每個金額乘以一個折扣係數0.8,得到折扣後的金額。
依次選擇:【計算】-【字段、項目和集】-【計算字段
計算字段名稱:折扣金額,公式:金額*0.8。
輸入完成後,單擊“添加”按鈕,得到一個新的字段“折扣金額”。
4、數據透視表排序
數據透視表的排序也很簡單,光標移至需要排序的列,菜單【數據】中,選擇升序(A-Z)或降序(Z-A)。
注意:數據透視表中,排序只在一個類別中進行,如上,分別在食品、書籍和油費中進行降序排列。
四、組合篩選
數據透視表中還有一個“組合”功能,能夠對日期、數字等進行組合。
1、按月/季度/年匯總報表
例如,對於以下數據透視表,將日期字段放到了行區域。
在1月單元格,右擊鼠標,選擇“組合”,在“組合”對話框中,把季度、年選上。
“確定”後,可以看到數據透視表可以按季度、年份進行匯總。
2、求分段佔比
對於數字類型的字段,還可以分段統計。
例如,以下數據透視表,想對年齡進行分段統計。
在“年齡”列右擊鼠標,選擇“組合”,在“組合”對話框中,可以設置起始值、終止值及步長。
設置完成後,就實現了對年齡的分段統計,統計出了各年齡段的購買人數、金額總和。
3、插入切片器
切片器其實就是篩選器。
例如,對於以上數據透視表,插入一個切片器。
勾選“類型”,然後“確定”,這樣就得到一個切片器,它能夠按照類型進行篩選。
說明:選中右上角的多選按鈕,還能進行多選。
4、插入日程表(日期控件)
日程表其實就是日期控件,通過它能夠對數據透視表按照日期篩選。
插入日程表,勾選“日期”,然後“確定”。
有了這個日期控件,就可以按照年/季度/月/日對數據進行多維度篩選了,拖動底部的滑塊即可。
五、其他操作
1、數據透視圖
數據透視圖,顧名思義,數據透視表+圖表。
插入數據透視圖,
選擇數據源後,確定,構造如下數據透視表,會自動生成對應的圖表,在此圖表中,還可以按照字段“類型”對圖表進行篩選。
2、多表合并透視
還可以對多個表格中的數據進行合并透視,例如,有以下三個月份的數據。
說明:這3個月份的數據分布在3個sheet中,這裡為了方便介紹,截圖放在一起。
如何進行多表合并透視?
(1)按一下Alt鍵,然後按一下D鍵,最後按一下P鍵,打開數據透視表向導對話框。
(2)選擇“多重合并計算數據區域”,然後“下一步”。
(3)選擇“創建單頁字段”,然後“下一步”。
選擇需要合并的數據區域,選擇一個區域後,單擊“添加”按鈕。這裡選擇1月、2月、3月這3個sheet中的數據區域。
“下一步”後,得到如下圖所示對話框,這裡不用選,默認現有工作表。
單擊“完成”按鈕後,得到多表合并的數據透視表。
這個數據透視表是3個sheet中的數據合并的結果,可以按照“頁”(表)來對數據進行篩選。
1、創建數據透視表
菜單【插入】中,選擇“數據透視表”,打開“創建數據透視表”對話框,選擇數據源。這裡選擇數據源區域A1:E8
2、數據源刷新
如果修改了數據源,那麼只需要單擊菜單中的“刷新”按鈕,即可更新數據透視表。
3、更改數據源
通過菜單中的“更改數據源”可以修改數據透視表的數據源。
4、數據透視表篩選
通過數據透視表的篩選器,可以篩選數據透視表。
例如,需要按照日期進行篩選,將日期字段拖到“篩選”中即可
5、刪除數據透視表
刪除透視表也很簡單,全選之後,按“Delete”鍵,即可刪除數據透視表。
二、布局樣式
1、套用樣式
通過Excel自帶的數據透視表樣式可以對數據透視表進行美化,
2、以表格形式顯示
有時候,選擇了多個行字段,數據透視表會呈現出以下形式。
此時只需在“報表布局”中選擇“以表格形式顯示”即可。
以表格形式顯示的數據透視表。
3、隱藏/顯示匯總項
如果不想顯示中間的分類匯總項,可以通過“分類匯總”-“不顯示分類匯總”來隱藏分類匯總項。
4、重複所有項目標籤
如果需要第一列的標籤重複顯示,選擇“重複所有項目標籤”即可。
5、合并標籤所在單元格
右擊鼠標,選擇“數據透視表”選項
勾選“合并且居中排列帶標籤的單元格”
三、計算排序
1、更改計算方式
在以上數據透視表中,對於“金額”字段的計算方式默認是求和,其實我們可以自行更改計算方式。
在值區域中,單擊“求和項:金額”,打開“值字段設置”,可以選擇計算方式,如求和、計數、平均值、最大值、最小值等。
2、求佔比(百分比)
前面已經對金額進行了求和,如果想求佔比,可以做如下操作。
(1)將“金額”再次拖到值區域,得到“求和項:金額2”。
(2)在“值顯示方式”中,選擇“列匯總的百分比”
“確定”後,得到金額的佔比。
3、插入計算字段
在數據透視表中,還可以創建計算字段。例如,給每個金額乘以一個折扣係數0.8,得到折扣後的金額。
依次選擇:【計算】-【字段、項目和集】-【計算字段
計算字段名稱:折扣金額,公式:金額*0.8。
輸入完成後,單擊“添加”按鈕,得到一個新的字段“折扣金額”。
4、數據透視表排序
數據透視表的排序也很簡單,光標移至需要排序的列,菜單【數據】中,選擇升序(A-Z)或降序(Z-A)。
注意:數據透視表中,排序只在一個類別中進行,如上,分別在食品、書籍和油費中進行降序排列。
四、組合篩選
數據透視表中還有一個“組合”功能,能夠對日期、數字等進行組合。
1、按月/季度/年匯總報表
例如,對於以下數據透視表,將日期字段放到了行區域。
在1月單元格,右擊鼠標,選擇“組合”,在“組合”對話框中,把季度、年選上。
“確定”後,可以看到數據透視表可以按季度、年份進行匯總。
2、求分段佔比
對於數字類型的字段,還可以分段統計。
例如,以下數據透視表,想對年齡進行分段統計。
在“年齡”列右擊鼠標,選擇“組合”,在“組合”對話框中,可以設置起始值、終止值及步長。
設置完成後,就實現了對年齡的分段統計,統計出了各年齡段的購買人數、金額總和。
3、插入切片器
切片器其實就是篩選器。
例如,對於以上數據透視表,插入一個切片器。
勾選“類型”,然後“確定”,這樣就得到一個切片器,它能夠按照類型進行篩選。
說明:選中右上角的多選按鈕,還能進行多選。
4、插入日程表(日期控件)
日程表其實就是日期控件,通過它能夠對數據透視表按照日期篩選。
插入日程表,勾選“日期”,然後“確定”。
有了這個日期控件,就可以按照年/季度/月/日對數據進行多維度篩選了,拖動底部的滑塊即可。
五、其他操作
1、數據透視圖
數據透視圖,顧名思義,數據透視表+圖表。
插入數據透視圖,
選擇數據源後,確定,構造如下數據透視表,會自動生成對應的圖表,在此圖表中,還可以按照字段“類型”對圖表進行篩選。
2、多表合并透視
還可以對多個表格中的數據進行合并透視,例如,有以下三個月份的數據。
說明:這3個月份的數據分布在3個sheet中,這裡為了方便介紹,截圖放在一起。
如何進行多表合并透視?
(1)按一下Alt鍵,然後按一下D鍵,最後按一下P鍵,打開數據透視表向導對話框。
(2)選擇“多重合并計算數據區域”,然後“下一步”。
(3)選擇“創建單頁字段”,然後“下一步”。
選擇需要合并的數據區域,選擇一個區域後,單擊“添加”按鈕。這裡選擇1月、2月、3月這3個sheet中的數據區域。
“下一步”後,得到如下圖所示對話框,這裡不用選,默認現有工作表。
單擊“完成”按鈕後,得到多表合并的數據透視表。
這個數據透視表是3個sheet中的數據合并的結果,可以按照“頁”(表)來對數據進行篩選。