Excel數(shù)據(jù)透視表入門(mén)到精通-文檔資料

1、L.O.G.O1Excel 數(shù)據(jù)透視表入門(mén)到精通數(shù)據(jù)透視表入門(mén)到精通培訓(xùn)發(fā)展部培訓(xùn)發(fā)展部 花旺東花旺東2Contents初識(shí)數(shù)據(jù)透視表初識(shí)數(shù)據(jù)透視表1數(shù)據(jù)透視表入門(mén)篇數(shù)據(jù)透視表入門(mén)篇2數(shù)據(jù)透視表進(jìn)階篇數(shù)據(jù)透視表進(jìn)階篇3數(shù)據(jù)透視表活用篇數(shù)據(jù)透視表活用篇43初識(shí)數(shù)據(jù)透視表概述初識(shí)數(shù)據(jù)透視表概述 從從Excel數(shù)據(jù)列表、關(guān)系數(shù)據(jù)庫(kù)文件或數(shù)據(jù)列表、關(guān)系數(shù)據(jù)庫(kù)文件或OLAP（聯(lián)機(jī)分析處理（聯(lián)機(jī)分析處理 ）多維數(shù)據(jù)集中）多維數(shù)據(jù)集中的特殊字段中總結(jié)信息的分析工具。的特殊字段中總結(jié)信息的分析工具。 一種交互式報(bào)表，可以快速分類匯總比一種交互式報(bào)表，可以快速分類匯總比較大量的數(shù)據(jù)，并可以隨時(shí)選擇其中頁(yè)、較大量

2、的數(shù)據(jù)，并可以隨時(shí)選擇其中頁(yè)、行和列中的不同元素，以達(dá)到快速查看行和列中的不同元素，以達(dá)到快速查看源數(shù)據(jù)的不同統(tǒng)計(jì)結(jié)果。源數(shù)據(jù)的不同統(tǒng)計(jì)結(jié)果。 可以隨意顯示和打印出你所感興趣區(qū)域可以隨意顯示和打印出你所感興趣區(qū)域的明細(xì)數(shù)據(jù)。的明細(xì)數(shù)據(jù)。4初識(shí)數(shù)據(jù)透視表用處初識(shí)數(shù)據(jù)透視表用處 對(duì)大量數(shù)據(jù)快速匯總和建立交叉列表的交對(duì)大量數(shù)據(jù)快速匯總和建立交叉列表的交互式動(dòng)態(tài)表格，能幫助用戶分析、組織數(shù)互式動(dòng)態(tài)表格，能幫助用戶分析、組織數(shù)據(jù)。據(jù)。 可以對(duì)數(shù)據(jù)透視表重新安排，以便從不同可以對(duì)數(shù)據(jù)透視表重新安排，以便從不同的角度查看數(shù)據(jù)。的角度查看數(shù)據(jù)。 具有具有“透視透視”表格的能力，從大量看似無(wú)表格的能力，從大量看

3、似無(wú)關(guān)的數(shù)據(jù)中尋找背后的聯(lián)系，從而將紛繁關(guān)的數(shù)據(jù)中尋找背后的聯(lián)系，從而將紛繁的數(shù)據(jù)轉(zhuǎn)化為有價(jià)值的信息，以供研究和的數(shù)據(jù)轉(zhuǎn)化為有價(jià)值的信息，以供研究和決策所用。決策所用。5入門(mén)篇?jiǎng)?chuàng)建數(shù)據(jù)透視表入門(mén)篇?jiǎng)?chuàng)建數(shù)據(jù)透視表11.在工作表中，選擇包含數(shù)據(jù)的任意單元格。在工作表中，選擇包含數(shù)據(jù)的任意單元格。在在“數(shù)據(jù)數(shù)據(jù)”菜單上，單擊菜單上，單擊“數(shù)據(jù)透視表和數(shù)據(jù)透視表和數(shù)據(jù)透視圖數(shù)據(jù)透視圖”。將顯示向?qū)?。將顯示向?qū)А?2.在向?qū)У牟襟E在向?qū)У牟襟E 1 中，選擇中，選擇“Microsoft Excel 數(shù)據(jù)列表或數(shù)據(jù)庫(kù)數(shù)據(jù)列表或數(shù)據(jù)庫(kù)”。3.選擇選擇“數(shù)據(jù)透視表數(shù)據(jù)透視表”。4.單擊單擊“完成完成”。 6入門(mén)

4、篇?jiǎng)?chuàng)建數(shù)據(jù)透視表入門(mén)篇?jiǎng)?chuàng)建數(shù)據(jù)透視表2 新工作表包括三個(gè)項(xiàng)目：新工作表包括三個(gè)項(xiàng)目：“數(shù)據(jù)透視表字?jǐn)?shù)據(jù)透視表字段列表段列表”、報(bào)表的布局區(qū)域（其中包括各、報(bào)表的布局區(qū)域（其中包括各個(gè)用于放置拖放字段的帶外邊框的獨(dú)立拖個(gè)用于放置拖放字段的帶外邊框的獨(dú)立拖放區(qū)域）和放區(qū)域）和“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄。工具欄。 7入門(mén)篇設(shè)置報(bào)表布局入門(mén)篇設(shè)置報(bào)表布局1 從從“數(shù)據(jù)透視表字段列表數(shù)據(jù)透視表字段列表”中，將中，將“銷售銷售人員人員”字段拖動(dòng)到標(biāo)簽為字段拖動(dòng)到標(biāo)簽為“將行字段拖至將行字段拖至此處此處”的拖放區(qū)域。的拖放區(qū)域。 8入門(mén)篇設(shè)置報(bào)表布局入門(mén)篇設(shè)置報(bào)表布局21.從從“數(shù)據(jù)透視表字段列表數(shù)據(jù)透

5、視表字段列表”中，將中，將“訂單訂單金額金額”字段拖動(dòng)到標(biāo)簽為字段拖動(dòng)到標(biāo)簽為“請(qǐng)將數(shù)據(jù)項(xiàng)拖請(qǐng)將數(shù)據(jù)項(xiàng)拖至此處至此處”的拖放區(qū)域。的拖放區(qū)域。2.將字段拖放到數(shù)據(jù)項(xiàng)的拖放區(qū)域中時(shí)，將將字段拖放到數(shù)據(jù)項(xiàng)的拖放區(qū)域中時(shí)，將顯示報(bào)表，其中顯示有每位銷售人員的銷顯示報(bào)表，其中顯示有每位銷售人員的銷售總額。售總額。 9入門(mén)篇轉(zhuǎn)動(dòng)報(bào)表入門(mén)篇轉(zhuǎn)動(dòng)報(bào)表1.單擊單擊“銷售人員銷售人員” 字段標(biāo)題字段標(biāo)題 2.拖放拖放“銷售人員銷售人員”字段標(biāo)題到字段標(biāo)題到 “匯總匯總”正正上方的單元格。拖動(dòng)時(shí)，只要獲得灰色框上方的單元格。拖動(dòng)時(shí)，只要獲得灰色框光標(biāo)并把光標(biāo)指向目標(biāo)單元格即可。光標(biāo)并把光標(biāo)指向目標(biāo)單元格即可。 1

6、0入門(mén)篇重命名字段標(biāo)題入門(mén)篇重命名字段標(biāo)題 在工作表的底部單擊數(shù)據(jù)透視表選項(xiàng)卡，在工作表的底部單擊數(shù)據(jù)透視表選項(xiàng)卡，以便看到數(shù)據(jù)透視表。然后單擊單元格以便看到數(shù)據(jù)透視表。然后單擊單元格 （在（在“銷售人員銷售人員”字段標(biāo)題之上的灰色框字段標(biāo)題之上的灰色框中的字段標(biāo)題）中的中的字段標(biāo)題）中的“求和項(xiàng)求和項(xiàng):訂單金額訂單金額”，鍵入鍵入“全部訂單全部訂單”，按，按 Enter 鍵。鍵。 11入門(mén)篇?jiǎng)?chuàng)建頁(yè)視圖入門(mén)篇?jiǎng)?chuàng)建頁(yè)視圖 頁(yè)字段：在數(shù)據(jù)透視表或數(shù)據(jù)透視圖報(bào)表頁(yè)字段：在數(shù)據(jù)透視表或數(shù)據(jù)透視圖報(bào)表中指定為頁(yè)方向的字段。中指定為頁(yè)方向的字段。 在頁(yè)字段中，既在頁(yè)字段中，既可以顯示所有項(xiàng)的匯總，也可以一

7、次顯示可以顯示所有項(xiàng)的匯總，也可以一次顯示一個(gè)項(xiàng)，而篩選掉所有其他項(xiàng)的數(shù)據(jù)。一個(gè)項(xiàng)，而篩選掉所有其他項(xiàng)的數(shù)據(jù)。 將將“國(guó)家國(guó)家/地區(qū)地區(qū)”字段從字段從“數(shù)據(jù)透視表字段數(shù)據(jù)透視表字段列表列表”拖放到拖放到“將頁(yè)字段拖至此處將頁(yè)字段拖至此處”區(qū)域。區(qū)域。 12入門(mén)篇從報(bào)表中拖出字段入門(mén)篇從報(bào)表中拖出字段 單擊工作表上的單擊工作表上的“國(guó)家國(guó)家/地區(qū)地區(qū)”字段標(biāo)題，字段標(biāo)題，然后拖放到外邊框外面的任何位置。然后拖放到外邊框外面的任何位置。 單擊工作表上的單擊工作表上的“訂單金額總額訂單金額總額” 字段標(biāo)字段標(biāo)題，然后拖放到外邊框外面的任何位置。題，然后拖放到外邊框外面的任何位置。 單擊工作表上的單擊

8、工作表上的“銷售人員銷售人員”字段標(biāo)題，字段標(biāo)題，然后拖放到外邊框外面的任何位置然后拖放到外邊框外面的任何位置 。 13入門(mén)篇小節(jié)入門(mén)篇小節(jié) 創(chuàng)建數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表 設(shè)置報(bào)表布局設(shè)置報(bào)表布局 轉(zhuǎn)動(dòng)報(bào)表轉(zhuǎn)動(dòng)報(bào)表 重命名字段標(biāo)題重命名字段標(biāo)題 創(chuàng)建頁(yè)視圖創(chuàng)建頁(yè)視圖 從報(bào)表中拖出字段從報(bào)表中拖出字段14進(jìn)階篇更改排序次序進(jìn)階篇更改排序次序 1.單擊單擊“銷售人員銷售人員”2.在在“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄上，單擊工具欄上，單擊“數(shù)據(jù)數(shù)據(jù)透視表透視表”，然后單擊，然后單擊“排序并顯示前排序并顯示前 10 項(xiàng)項(xiàng)”。 3.在在“自動(dòng)排序選項(xiàng)自動(dòng)排序選項(xiàng)”下，單擊下，單擊“降序降序”。 4.在在“

9、使用字段使用字段”列表中，單擊向下箭頭，列表中，單擊向下箭頭，選擇在練習(xí)選擇在練習(xí) 1 中為中為“訂單金額總和訂單金額總和”字段字段指定的新標(biāo)題進(jìn)行排序。例如，如果將該指定的新標(biāo)題進(jìn)行排序。例如，如果將該字段重命名為字段重命名為“全部訂單全部訂單”，則單擊該名，則單擊該名稱。單擊稱。單擊“確定確定”。 15進(jìn)階篇設(shè)置數(shù)字的格式進(jìn)階篇設(shè)置數(shù)字的格式 1.單擊數(shù)據(jù)拖放區(qū)域中包含數(shù)值的任一單元單擊數(shù)據(jù)拖放區(qū)域中包含數(shù)值的任一單元格。格。 2.單擊單擊“數(shù)據(jù)透視表數(shù)據(jù)透視表”上的上的“字段設(shè)置字段設(shè)置”按按鈕鈕 ，然后單擊，然后單擊“數(shù)字?jǐn)?shù)字”。 3.在在“單元格格式單元格格式”對(duì)話框中，單擊對(duì)話框中

10、，單擊“分類分類”列表中的列表中的“貨幣貨幣”。 4.在在“小數(shù)位數(shù)小數(shù)位數(shù)”列表中，單擊向下箭頭使列表中，單擊向下箭頭使列表中的值為列表中的值為 0。這將去掉小數(shù)位。這將去掉小數(shù)位。 5.單擊單擊“確定確定”兩次。兩次。 16進(jìn)階篇刷新數(shù)據(jù)進(jìn)階篇刷新數(shù)據(jù) 1. 在工作表的底部，單擊在工作表的底部，單擊“練習(xí)練習(xí)”選項(xiàng)卡切選項(xiàng)卡切換到源數(shù)據(jù)。換到源數(shù)據(jù)。 2. 在單元格在單元格 C2 中，將中，將“￥440.00”修改成修改成“￥5,000.00”，按，按 Enter 鍵。鍵。 3. 單擊工作表底部的單擊工作表底部的“sheet1”選項(xiàng)卡，切選項(xiàng)卡，切換回?cái)?shù)據(jù)透視表。換回?cái)?shù)據(jù)透視表。 注意，注

11、意，Buchanan的總的總額是額是 ￥68,792。 4. 單擊單擊“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄上的工具欄上的“刷新數(shù)刷新數(shù)據(jù)據(jù)”按鈕按鈕 。17進(jìn)階篇更改數(shù)據(jù)匯總的方式進(jìn)階篇更改數(shù)據(jù)匯總的方式 單擊數(shù)據(jù)區(qū)域中的任何單元格。單擊數(shù)據(jù)區(qū)域中的任何單元格。 單擊單擊“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄上的工具欄上的“字段設(shè)字段設(shè)置置”按鈕按鈕 。 在在“匯總方式匯總方式”列表中，單擊列表中，單擊“計(jì)數(shù)計(jì)數(shù)”，然后單擊然后單擊“確定確定”。 18進(jìn)階篇?jiǎng)h除數(shù)據(jù)透視表進(jìn)階篇?jiǎng)h除數(shù)據(jù)透視表 單擊數(shù)據(jù)透視表。單擊數(shù)據(jù)透視表。 在在“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄上，單擊工具欄上，單擊“數(shù)據(jù)數(shù)據(jù)透視表透視表”，

12、指向，指向“選定選定”，然后單擊，然后單擊“整整張表格張表格”。 在在“編輯編輯”菜單上，指向菜單上，指向“刪除工作表刪除工作表”，然后單擊然后單擊“確定確定” 19進(jìn)階篇同一拖放區(qū)使用兩字段進(jìn)階篇同一拖放區(qū)使用兩字段 將將“訂單日期訂單日期”字段添加到報(bào)告的字段添加到報(bào)告的“行行”拖放區(qū)，這會(huì)使得拖放區(qū)，這會(huì)使得“訂單日期訂單日期”成為報(bào)告成為報(bào)告的第二行字段。的第二行字段。 從從“數(shù)據(jù)透視表字段列表數(shù)據(jù)透視表字段列表”將將“訂單日期訂單日期”字段拖動(dòng)到字段拖動(dòng)到“銷售人員銷售人員”字段的左邊。字段的左邊。 20進(jìn)階篇進(jìn)階篇按季度組合訂單日期按季度組合訂單日期 在在“訂單日期訂單日期”字段的

13、單元格中單擊鼠標(biāo)字段的單元格中單擊鼠標(biāo)右鍵。右鍵。 在快捷鍵菜單上指向在快捷鍵菜單上指向“組及顯示明細(xì)數(shù)組及顯示明細(xì)數(shù)據(jù)據(jù)”，單擊，單擊“組合組合”。 在在“組合組合”對(duì)話框中，單擊對(duì)話框中，單擊“季度季度”。如。如果同時(shí)選擇了果同時(shí)選擇了“月月”，請(qǐng)單擊取消選擇它，請(qǐng)單擊取消選擇它，然后單擊然后單擊“確定確定”。 21進(jìn)階篇進(jìn)階篇交換位置以獲得不同的交換位置以獲得不同的視圖視圖 將將“季度季度”字段拖到字段拖到“銷售人員銷售人員”字段的字段的右邊。記住，該字段在報(bào)告中是灰色框，右邊。記住，該字段在報(bào)告中是灰色框，包含包含“季度季度”。在列。在列 B 與列與列 C 的中間看到的中間看到灰色框時(shí)

14、釋放鼠標(biāo)鍵灰色框時(shí)釋放鼠標(biāo)鍵 。22進(jìn)階篇進(jìn)階篇嘗試新的技術(shù)嘗試新的技術(shù) Buchanan 在第一季度取得成功的背后是在第一季度取得成功的背后是什么呢？在本次練習(xí)中，您將發(fā)現(xiàn)這個(gè)秘什么呢？在本次練習(xí)中，您將發(fā)現(xiàn)這個(gè)秘密。密。 雙擊單元格雙擊單元格 C5，它包含了，它包含了 Buchanan 第第一季度的銷售數(shù)據(jù)。一季度的銷售數(shù)據(jù)。 新工作表新工作表 Sheet1 會(huì)添加到工作簿中。它包會(huì)添加到工作簿中。它包含所有用來(lái)匯總以得到含所有用來(lái)匯總以得到 Buchanan 第一季第一季度結(jié)果的銷售數(shù)據(jù)。度結(jié)果的銷售數(shù)據(jù)。 23進(jìn)階篇小節(jié)進(jìn)階篇小節(jié) 更改排序次序更改排序次序 設(shè)置數(shù)字的格式設(shè)置數(shù)字的格式

15、 刷新數(shù)據(jù)刷新數(shù)據(jù) 更改數(shù)據(jù)匯總的方式更改數(shù)據(jù)匯總的方式 刪除數(shù)據(jù)透視表刪除數(shù)據(jù)透視表 同一拖放區(qū)使用兩字段同一拖放區(qū)使用兩字段 按季度組合訂單日期按季度組合訂單日期 交換位置以獲得不同的視圖交換位置以獲得不同的視圖 嘗試新的技術(shù)嘗試新的技術(shù) 24活用篇活用篇使用同一數(shù)據(jù)字段并更使用同一數(shù)據(jù)字段并更改字段匯總方式改字段匯總方式 在在“數(shù)據(jù)透視表字段列表數(shù)據(jù)透視表字段列表”上單擊上單擊“訂單訂單金額金額”，并將它拖動(dòng)到包含訂單金額字段，并將它拖動(dòng)到包含訂單金額字段的任何地方。的任何地方。 單擊任何標(biāo)為單擊任何標(biāo)為“訂單金額總和訂單金額總和2”的單元格，的單元格，然后單擊然后單擊“數(shù)據(jù)透視表數(shù)據(jù)透

16、視表”工具欄上的工具欄上的“字字段設(shè)置段設(shè)置”按鈕按鈕 。 在在“匯總方式匯總方式”列表中，單擊列表中，單擊“計(jì)數(shù)計(jì)數(shù)”，然后單擊然后單擊“確定確定”。 25活用篇活用篇格式化報(bào)告格式化報(bào)告 單擊報(bào)告，然后單擊單擊報(bào)告，然后單擊“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具工具欄上的欄上的“設(shè)置報(bào)告格式設(shè)置報(bào)告格式”按鈕按鈕 。 在在“自動(dòng)套用格式自動(dòng)套用格式”對(duì)話框中，單擊對(duì)話框中，單擊“報(bào)報(bào)告告 5”，然后單擊，然后單擊“確定確定”。 現(xiàn)在更改現(xiàn)在更改“訂單金額計(jì)數(shù)訂單金額計(jì)數(shù)2”字段的名稱。字段的名稱。26活用篇活用篇以總和的百分比顯示數(shù)以總和的百分比顯示數(shù)值數(shù)據(jù)值數(shù)據(jù) 單擊單擊“訂單訂單 #”字段中的任

17、一單元格。字段中的任一單元格。 在在“數(shù)據(jù)透視表數(shù)據(jù)透視表”工具欄上，單擊工具欄上，單擊“字段字段設(shè)置設(shè)置”按鈕按鈕 。 在在“匯總方式匯總方式”列表中，單擊列表中，單擊“求和求和”將將匯總方式由匯總方式由“計(jì)數(shù)計(jì)數(shù)”更改為更改為“求和求和”。我。我們希望顯示總計(jì)的百分比，而不是計(jì)數(shù)。們希望顯示總計(jì)的百分比，而不是計(jì)數(shù)。 單擊單擊“選項(xiàng)選項(xiàng)”按鈕。按鈕。 在在“數(shù)據(jù)顯示方式數(shù)據(jù)顯示方式”列表中，單擊箭頭，列表中，單擊箭頭，向下滾動(dòng)列表，然后單擊向下滾動(dòng)列表，然后單擊“占總和的百分占總和的百分比比”。單擊。單擊“確定確定”。 27活用篇活用篇確定獎(jiǎng)金百分比確定獎(jiǎng)金百分比1 銷售人員每季度會(huì)得到銷

18、售人員每季度會(huì)得到 7% 的獎(jiǎng)金，但今的獎(jiǎng)金，但今年每個(gè)銷售額超過(guò)年每個(gè)銷售額超過(guò) ￥25,000 的銷售人員會(huì)的銷售人員會(huì)在任何季度獲得在任何季度獲得 10% 的獎(jiǎng)金。在本次練習(xí)的獎(jiǎng)金。在本次練習(xí)中您將使用計(jì)算字段輸入公式。這里是公中您將使用計(jì)算字段輸入公式。這里是公式：式： =IF(訂單金額訂單金額25000,10%,7%) 。28活用篇活用篇確定獎(jiǎng)金百分比確定獎(jiǎng)金百分比2 單擊報(bào)告內(nèi)部。然后在單擊報(bào)告內(nèi)部。然后在“數(shù)據(jù)透視表數(shù)據(jù)透視表”工工具欄上單擊具欄上單擊“數(shù)據(jù)透視表數(shù)據(jù)透視表”，指向，指向“公公式式”，然后單擊，然后單擊“計(jì)算字段計(jì)算字段”。 在在“名稱名稱”框中輸入公式名。例如，輸入框中輸入公式名。例如，輸入“獎(jiǎng)金獎(jiǎng)金 %”。 刪除刪除“公式公式”框中的文字?？蛑械奈淖?。 在框中，輸入在框中，輸入=IF(。 29活用篇活用篇確定獎(jiǎng)金百分比確定獎(jiǎng)金百分比3在在“字段字段”列表中，單擊列表中，單擊“訂單金額訂單金額”，然后單擊然后單擊“插入字段插入字段”。 注意注意 盡管盡管“訂單金額訂單金額”字段重新命名為字段重新命名為“訂單訂單 ￥”，但該字段名在，但該字段名在“插入計(jì)算插入計(jì)算字段字段”對(duì)話框中仍為對(duì)話框中仍為“訂單金額訂單金額”。接著輸入公式的其他部分：接著輸