Excel數據透視表怎么做的15個操作方法,含非重復計數和百分比統計及四個區域間的用途

在 Excel 中,數據透視表用于做各項統計,例如統計每個部門的員工占總員工的百分比、每種產品的銷量占總銷量的百分比、每種產品的某個地區的銷量占所有地區銷量的百比分等。它的計算類有求和、求平均值、計數、求最大最小值、求標準差和非重復計數等。

Excel數據透視表怎么做?它的創建方法有兩種,一種為創建空數據透視表,另一種為創建 Excel 推薦的數據透視表,即按某些項統計好的數據透視表。如果是創建空數據透視表,需要自己統計要統計的項;完成統計要知道四個區域間的用途、計算類型的選擇和值顯示方式的選擇等;文章列舉了非重復計數和百分比兩個統計實例。

一、Excel數據透視表怎么創建

(一)創建空數據透視表

(1)把數據透視表創建到另一個工作表

1、選中要創建數據透視表的表格其中一個單元格(如 A2),選擇“插入”選項卡,單擊屏幕左上角的“數據透視表”,打開“創建數據透視表”窗口,“表/區域”已自動填好了對表格的引用(即 工資表!$A$1:$F$11);“選擇放置數據透視表的位置”選擇“新工作表”,勾選“將此數據添加到數據模型”,單擊“確定”,開始創建數據透視表,所用時間長短取決表格大小;數據透視表創建好后自動切換到該工作表,默認不勾選任何字段,勾選“部門”和“應發工資”,則統計出各部門的“應發工資”;操作過程步驟,如圖1所示:

圖1

2、說明:

A、如果不是把整個表格都創建數據透視表,只把部分單元格或列創建,可以只框選它們;例如只把 A、B 和 C 列創建數據透視表,只選中這三列即可。

B、“使用外部數據源”可以把另一個 Excel 文檔或數據庫作為創建數據透視表的數據源,如果要使用數據庫作為數據源,需要建立與數據庫的連接。

C、“將此數據添加到數據模型”的作用是非重復計數,也就是重復的項只統計一次,如果表格有重復數據,可以勾它;如果不勾選,在值列表中將沒有“非重復計數”選項。

提示:打開“創建數據透視表”窗口也可以按快捷鍵 Alt + N + V,按鍵方法為:按住 Alt,依次按一次 N 和 V。

(2)把數據透視表創建到現有工作表

1、假如只把 B、C、D 三列創建數據透視表。單擊列號 B,選中 B 列,按住 Shift,再單擊列號 D,選中 B、C、D 三列,按住 Alt,分別按一次 N 和 V,打開“創建數據透視表”窗口,選擇“現有工作表”,單擊“位置”右邊的輸入框把光標定位到那里,單擊 H1,則 Sheet1!$H$1 自動填充到里面,表示把數據透視表創建到 H1 的位置,單擊“確定”,成功創建數據透視表,勾選“部門”和“基本工資”,它們作一列顯示,把“計數項:基本工資”拖到“Σ 值”列表,它們顯示為一行;操作過程步驟,如圖2所示:

圖2

2、如果只把幾列創建數據透視表,這幾列必須連續,否則無法創建,不連續的列可以把它們移到一起,按住 Shift,把鼠標移到要移動列的左邊線或右邊線上,鼠標變為帶小加號圖標后,按住左鍵移動即可。

(二)創建“推薦的數據透視表”

1、選中要創建數據透視表的其中一單元格,選擇“插入”選項卡,單擊屏幕左上角的“推薦的數據透視表”,打開“推薦的數據透視表”窗口,選擇一種數據透視表樣式(如“求和項:應發工資(元),按部門”),單擊“確定”,則創建好所選樣式的數據透視表,已經統計好每個部門的“應發工資”;操作過程步驟,如圖3所示:

圖3

2、說明:“推薦的數據透視表”其實是統計好了的一項或幾項的表格,例如演示中選擇的“求和項:應發工資(元),按部門”就是統計好了每個部門的應發工資。如果“推薦的數據透視表”有現存的樣式,選擇一個創建數據透視表速度比較快。

提示:創建“推薦的數據透視表”也可以用快捷鍵 Alt + N + SP,按鍵方法為:按住 Alt,依次按一次 N、S 和 P。

二、Excel數據透視表,怎么更改數據源和源表格修改數據后怎么更新

(一)怎么更改數據源

假如要把數據源由整個表格改為 A 列到 D 列。單擊數據透視表其中一個單元格(如 A3)以便顯示“分析”選項卡,選擇“分析”,單擊“更改數據源”圖標,Excel 自動切換到數據源表格“工資表”,框選 A 到 D 列,單擊“確定”,則數據源改為 A 列至 D 列,“加班費”列和“應發工資”列已經沒有了;操作過程步驟,如圖4所示:

圖4

(二)源表格修改數據后怎么更新

1、以把源表格中兩條“李秀麗”的記錄刪除一條為例。右鍵其中一條“李秀麗”記錄所在行行號 2,在彈出的菜單中選擇“刪除”,則刪除其中一條重復記錄;單擊“數據透視表”標簽切換到該工作表,選中“李秀麗”對應的“應發工資”單元格 B5,選擇“分析”選項卡,單擊“更新”,則“李秀麗”對應的“應發工資”減半,說明數據透視表已經更新;操作過程步驟,如圖5所示:

圖5

提示:如果在表格后面添加了一條記錄,需更新數據源,因為所添加的記錄不在已選擇表格的范圍內。

2、如果打開文檔時要自動刷新數據,需要設置,方法為:右鍵 A3 單元格(即“行標簽”),在彈出的菜單中選擇“數據透視表選項”,打開“數據透視表選項”窗口,選擇“數據”選項卡,勾選“打開文件時刷新數據”,單擊“確定”,則每次打開文檔會自動刷新數據;操作過程步驟,如圖6所示:

圖6

提示:打開“數據透視表選項”窗口也可以用快捷鍵 Shift + F10 + O,按鍵方法為:按住 Shift,依次按一次 F10 和 O。

三、Excel數據透視表的四個區域間

(一)區域間“行”

區域間“行”用于把字段顯示到行,如果是“文本”字段,只要勾選,它會自動顯示到區域間“行”;如果是數值字段,需要把它拖到區域間“行”才會顯示到表格的行。例如勾選“姓名”,它自動顯示到區域間“行”,同時顯示到表格的行;在區域間“行”,“部門”顯示在“姓名”的上面,在表格中也是一樣的次序顯示,把“姓名”拖到“部門”的上面,表格中的次序也交換了;演示如圖7所示:

圖7

(二)區域間“列”

區域間“列”用于把字段顯示到列,只需把要顯示到列的字段拖到區域間“列”,該字段的每一項顯示到一列。例如把“姓名”拖到區域間“列”,每個員工的姓名顯示到了一列;操作過程步驟,如圖8所示:

圖8

(三)區域間“篩選器”

1、篩選器用于篩選記錄,只要把字段拖到區域間“篩選器”,則以該字段篩選記錄。例如把“部門”拖到“篩選器”,則 A2 自動出現“部門”,B2 自動出現 All,單擊 All,在彈出的菜單中單擊加號(+)展開,單擊 All 前的小方框取消選擇所有,再勾選“財務部”,則篩選出“財務部”的所有員工;操作過程步驟,如圖9所示:

圖9

提示:如果把字段(如“部門”)從篩選器中刪除了,可以從 A4 中的“行標簽”篩選或重新把字段拖到篩選器。

2、“快速瀏覽”也可以把字段添加到篩選器。右鍵要快速瀏覽的單元格,如 A5(或單擊在 A5 右邊的放大鏡圖標),展開“瀏覽”窗口,選擇“姓名”,再單擊“鉆取到 姓名”,則 A5 中的“財務部”被選為篩選,“部門”字段同時被添加到“篩選器”;操作過程步驟,如圖10所示:

圖10

提示:沒有勾選的字段才可以被鉆取到。

“快速瀏覽”后的恢復。把“部門”從“篩選器”拖到“行”,單擊 A4 中的篩選圖標,在彈出的菜單中單擊“全選”勾選所有部門,則顯示出所有部門及屬于它們的員工;操作過程步驟,如圖11所示:

圖11

3、雙擊“值”列完整顯示一條記錄。假如要完全顯示員工“李秀麗”的每個字段的信息;雙擊“李秀麗”對應的“B5 中的應發工資”,則Excel 開始創建一個新的工作表(即 Sheet1),在里面顯示了“李秀麗”的所有信息,由于有兩條“李秀麗”的記錄,因此顯示了兩條記錄;操作過程步驟,如圖12所示:

圖12

四、Excel數據透視表怎么做各項統計

(一)求和與求平均值

1、假如要把求和改為求平均值。雙擊要更改計算類型的標題(如 B3 單元格),打開“值字段設置”窗口,選擇“計算類型”下的“平均值”,把“自定義名稱”右邊的“以下項目的”去掉,單擊“確定”,則統計出各部門的平均工資;操作過程步驟,如圖13所示:

圖13

2、在“值字段設置”窗口中,“計算類型”有多種,需要修改為什么類型,選擇即可。

(二)非重復計數統計

假如要對員工“姓名”作非重復計數統計。把“姓名”字段拖到“Σ 值”列表中,自動統計出了每個員工的個數,因為“計算類型”默認選擇了“計數”,員工“李秀麗”統計結果為 2;雙擊“計數”標題所在的單元格 B3,打開“值字段設置”窗口,“計算類型”選擇“非重復計數”,單擊“確定”,則重復的項只統計一次,員工“李秀麗”統計的結果由 2 變為 1;操作過程步驟,如圖14所示:

圖14

(三)百分比統計

假如要統計各部門與每個員工的工資占總工資的百分比。把“應發工資”字段拖到“Σ 值”列表中,雙擊它的標題所在的 C3 單元格,打開“值字段設置”窗口,把名稱改為“百分比”,單擊“確定”,右鍵有數值的其中一個單元格,如 C4,在彈出的菜單中依次選擇“值顯示方式”→ 總計的百分比,則統計出滿足條件的百分比;操作過程步驟,如圖15所示:

圖15