數(shù)據(jù)管理與分析課件

1、數(shù)據(jù)管理與分析,第五章 數(shù)據(jù)管理與分析,數(shù)據(jù)管理與分析,Overview 5.0 概述 5.1 數(shù)據(jù)列表 5.2 數(shù)據(jù)排序 5.3 數(shù)據(jù)篩選 5.4 分類匯總 5.5 使用數(shù)據(jù)透視表 5.6 數(shù)據(jù)導(dǎo)入與導(dǎo)出,Chapter 5 數(shù)據(jù)管理與分析,數(shù)據(jù)管理與分析,Excel具有強(qiáng)大的數(shù)據(jù)管理與分析能力，能夠?qū)ぷ鞅碇械臄?shù)據(jù)進(jìn)行排序、篩選、分類匯總等，還能夠使用數(shù)據(jù)透視表對(duì)工作表的數(shù)據(jù)進(jìn)行重組，對(duì)特定的數(shù)據(jù)行或數(shù)據(jù)列進(jìn)行各種概要分析，并且可以生成數(shù)據(jù)透視圖，直觀的表示分析結(jié)果。,5.0 概述,數(shù)據(jù)管理與分析,在這一章中，我們將使用Excel的數(shù)據(jù)管理與分析功能，對(duì)第4章實(shí)例中的商品銷售清單、銷售統(tǒng)計(jì)

2、表和庫(kù)存清單等工作表進(jìn)行分析。 找出月度銷售冠軍和利潤(rùn)最高的商品； 跟蹤各個(gè)類別與各個(gè)品牌的商品銷售情況，監(jiān)測(cè)商品的庫(kù)存情況； 對(duì)各個(gè)類別與各個(gè)品牌的商品銷售進(jìn)行分類匯總和數(shù)據(jù)透視，找出最暢銷的類別和品牌，從而幫助店主確定今后的經(jīng)營(yíng)方向。,本章目標(biāo),數(shù)據(jù)管理與分析,我們將上面提出的目標(biāo)進(jìn)行細(xì)分，分為4個(gè)任務(wù)。 任務(wù)1：分別按銷售數(shù)量和利潤(rùn)金額對(duì)銷售統(tǒng)計(jì)表進(jìn)行排序，找出月度銷售冠軍和利潤(rùn)最高的商品；按商品的類別自定義排序，得到各個(gè)類別的商品銷售排名情況。 任務(wù)2：使用自動(dòng)篩選功能分析銷售清單，跟蹤各個(gè)類別與各個(gè)品牌的商品銷售情況；使用自動(dòng)篩選分析庫(kù)存清單，得出庫(kù)存最大的5種商品與庫(kù)存為0的商品，

3、作為店主進(jìn)貨的依據(jù)；使用高級(jí)篩選功能分析銷售統(tǒng)計(jì)表，找出銷售金額高于平均銷售金額的商品。 任務(wù)3：使用分類匯總對(duì)銷售統(tǒng)計(jì)表進(jìn)行匯總，計(jì)算各個(gè)類別與各個(gè)品牌的銷售總金額與利潤(rùn)總金額。 任務(wù)4：使用數(shù)據(jù)透視表和數(shù)據(jù)透視圖分析銷售清單，統(tǒng)計(jì)各個(gè)類別與各個(gè)品牌商品的銷售總數(shù)量，找出最暢銷的類別和品牌。,本章任務(wù),數(shù)據(jù)管理與分析,由于排序與篩選數(shù)據(jù)記錄的操作需要通過(guò)“數(shù)據(jù)列表”來(lái)進(jìn)行，因此在操作前應(yīng)先創(chuàng)建好“數(shù)據(jù)列表”。 “數(shù)據(jù)列表”是工作表中包含相關(guān)數(shù)據(jù)的一系列數(shù)據(jù)行，如前面所建立的銷售清單和銷售統(tǒng)計(jì)表，就包含有這樣的數(shù)據(jù)行，它可以像數(shù)據(jù)庫(kù)一樣接受瀏覽與編輯等操作。,5.1 數(shù)據(jù)列表,數(shù)據(jù)管理與分析,

4、數(shù)據(jù)列表的創(chuàng)建方法如下：選定要?jiǎng)?chuàng)建列表的數(shù)據(jù)區(qū)域，然后選擇Excel的“數(shù)據(jù)”“列表”“創(chuàng)建列表”菜單項(xiàng)。例如，要建立如圖5-1所示的銷售記錄數(shù)據(jù)列表，應(yīng)選中A1:O36區(qū)域，然后選擇“數(shù)據(jù)”“列表”“創(chuàng)建列表”菜單項(xiàng)建立數(shù)據(jù)列表。 實(shí)際上，如果一個(gè)工作表只有一個(gè)連續(xù)數(shù)據(jù)區(qū)域，并且這個(gè)數(shù)據(jù)區(qū)域的每個(gè)列都有列標(biāo)題，那么系統(tǒng)會(huì)自動(dòng)將這個(gè)連續(xù)數(shù)據(jù)區(qū)域識(shí)別為數(shù)據(jù)列表。如圖5-2。,5.1.1 創(chuàng)建數(shù)據(jù)列表,數(shù)據(jù)管理與分析,圖5-1 建立數(shù)據(jù)列表,圖5-2 自動(dòng)識(shí)別為數(shù)據(jù)列表,數(shù)據(jù)管理與分析,將類型相同的數(shù)據(jù)項(xiàng)置于同一列中 使數(shù)據(jù)列表獨(dú)立于其它數(shù)據(jù) 將關(guān)鍵數(shù)據(jù)置于列表的頂部或底部 注意顯示行和列 注意數(shù)

5、據(jù)列表格式 使用單元格邊框突出顯示數(shù)據(jù)列表 避免空行和空列,數(shù)據(jù)列表-數(shù)據(jù)輸入準(zhǔn)則,數(shù)據(jù)管理與分析,當(dāng)數(shù)據(jù)表或列表中的數(shù)據(jù)記錄太多時(shí)，要查看、修改或編輯其中的某條記錄很困難，為了解決這個(gè)問(wèn)題，Excel提供了記錄單功能。 只有每列數(shù)據(jù)都有標(biāo)題的工作表才能夠使用記錄單功能。單擊銷售記錄數(shù)據(jù)列表中的任一單元格，從“數(shù)據(jù)”下拉菜單中選擇“記錄單”命令，進(jìn)入圖5-3所示的數(shù)據(jù)記錄單對(duì)話框就能完成這些操作。,5.1.2 使用記錄單,數(shù)據(jù)管理與分析,記錄單具有條件查詢功能，允許使用通配符查找，即用“*”代替不可知的任意長(zhǎng)度的任何符號(hào)，,圖5-3 記錄單,數(shù)據(jù)管理與分析,數(shù)據(jù)排序的功能是按一定的規(guī)則對(duì)數(shù)據(jù)進(jìn)

6、行整理和排列，為進(jìn)一步處理數(shù)據(jù)做好準(zhǔn)備。Excel 提供了多種對(duì)數(shù)據(jù)列表進(jìn)行排序的方法，既可以按升序或降序進(jìn)行排序，也可以按用戶自定義的方式進(jìn)行排序。 例如，每月商品的銷量排名情況就需要對(duì)商品銷售數(shù)量進(jìn)行排序，從中可以得出賣得最好的商品或賣得最差的商品。,5.2 數(shù)據(jù)排序,數(shù)據(jù)管理與分析,使用“數(shù)據(jù)”下拉菜單的“排序”命令，打開“排序”對(duì)話框。,排序?qū)υ捒?主要關(guān)鍵字 選擇排序字段，按升序或降序排序。 次要關(guān)鍵字 “主要關(guān)鍵字”列中出現(xiàn)了重復(fù)項(xiàng)，就按次要關(guān)鍵字來(lái)排序重復(fù)的部分。 第三關(guān)鍵字 “主要關(guān)鍵字”與“次要關(guān)鍵字”列中都出現(xiàn)了重復(fù)項(xiàng)，就按第三關(guān)鍵字來(lái)排序重復(fù)的部分。 有標(biāo)題行：排序時(shí)包含

7、列表第一行。 無(wú)標(biāo)題行：排序時(shí)不包含列表第一行。,圖5-4 排序?qū)υ捒?數(shù)據(jù)管理與分析,現(xiàn)在我們使用普通排序來(lái)完成任務(wù)1，對(duì)圖5-2中的銷售數(shù)量進(jìn)行降序排序，得到商品銷量排名，排在第一的即是月度銷售冠軍；對(duì)利潤(rùn)金額進(jìn)行降序排序，則得到商品利潤(rùn)排名，排在第一的即是利潤(rùn)最高的商品。,5.2.1 普通排序,數(shù)據(jù)管理與分析,步驟1：?jiǎn)螕粼垂ぷ鞅碇械娜我粏卧?，或選中要排序的整個(gè)單元格區(qū)域。本例中，可單擊A3:G30中的任一單元格，也可以選擇整個(gè)A3:G30區(qū)域。 步驟2：選擇“數(shù)據(jù)”“排序”菜單項(xiàng)，系統(tǒng)會(huì)顯示圖5-4所示的對(duì)話框。 步驟3：從對(duì)話框中“主要關(guān)鍵字”下拉列表中選擇排序關(guān)鍵字，選擇“銷售數(shù)

8、量”。 步驟4：指定排序方式，由于需要找出月度銷售冠軍，因此選擇“降序”作為排序的方式。 步驟5：將“有標(biāo)題行”單選鈕選中，然后單擊“確定”按鈕，Excel就會(huì)對(duì)源工作表中的數(shù)據(jù)按銷售數(shù)量從高到低進(jìn)行重新排列，其結(jié)果如圖5-5所示。,操作步驟,數(shù)據(jù)管理與分析,如何找出利潤(rùn)最高的商品？,圖5-5 對(duì)銷售數(shù)量進(jìn)行降序排序后的工作表,數(shù)據(jù)管理與分析,有時(shí)，我們需要按照一種指定的次序進(jìn)行排序，而不是按照數(shù)值或者文本的順序排序。例如在任務(wù)1中，店主需要知道本月各個(gè)類別的商品銷售排名情況，商品類別要按照“手機(jī)、相機(jī)、MP4、MP3、儲(chǔ)存卡”這個(gè)自定義順序排列，而不是字母或筆畫順序。 要完成這樣的排序，需要

9、先建立一個(gè)自定義序列。,5.2.2 自定義排序,數(shù)據(jù)管理與分析,步驟1：選擇“工具”“選項(xiàng)”菜單項(xiàng)，系統(tǒng)彈出如圖5-6所示的對(duì)話框。 步驟2：選擇“選項(xiàng)”對(duì)話框中的“自定義序列”標(biāo)簽，然后在該對(duì)話框中的“輸入序列”編輯框中輸入自定義序列，每輸入一個(gè)類別后按一下回車鍵。 步驟3：輸入完成后，單擊“確定”按鈕，就將這個(gè)用戶自定義序列添加到了系統(tǒng)中。,自定義序列,數(shù)據(jù)管理與分析,圖5-6 自定義序列,數(shù)據(jù)管理與分析,步驟1：?jiǎn)螕魣D5-2中A3:G30中的任一單元格，也可以選擇整個(gè)A3:G30區(qū)域。然后選擇“數(shù)據(jù)”“排序”菜單項(xiàng)。 步驟2：在彈出的“排序”對(duì)話框中，單擊“選項(xiàng)”按鈕，系統(tǒng)將彈出“排序選

10、項(xiàng)”對(duì)話框，如圖5-7所示。 步驟3：在 “自定義排序次序”下拉列表中選擇前面建立的自定義序列，按“確定”按鈕回到“排序”對(duì)話框。 步驟4：在“排序”對(duì)話框中選擇“類別”作為主關(guān)鍵字，排序方式為“升序”，選擇“銷售數(shù)量”作為次要關(guān)鍵字，排序方式為“降序”，按“確定”按鈕皆可得到如圖5-8所示的結(jié)果。,對(duì)銷售統(tǒng)計(jì)表自定義排序,數(shù)據(jù)管理與分析,圖5-8 自定義排序后的結(jié)果,圖5-7 排序選項(xiàng),數(shù)據(jù)管理與分析,數(shù)字：根據(jù)其值的大小從小到大排序。 文本和包含數(shù)字的文本：按字母順序?qū)ξ谋卷?xiàng)進(jìn)行排序。 邏輯值：False排在True之前。 錯(cuò)誤值：所有的錯(cuò)誤值都是相等的。 空白（不是空格）：空白單元格總是

11、排在最后。 漢字：漢字有兩種排序方式：漢語(yǔ)拼音和筆畫,5.2.3 排序規(guī)則,數(shù)據(jù)管理與分析,數(shù)據(jù)篩選是一種用于查找數(shù)據(jù)的快速方法，篩選將數(shù)據(jù)列表中所有不滿足條件的記錄暫時(shí)隱藏起來(lái)，只顯示滿足條件的數(shù)據(jù)行，以供用戶瀏覽和分析。Excel提供了自動(dòng)和高級(jí)兩種篩選數(shù)據(jù)的方式。,5.2 數(shù)據(jù)篩選,數(shù)據(jù)管理與分析,自動(dòng)篩選為用戶提供了在具有大量記錄的數(shù)據(jù)列表中快速查找符合某些條件的記錄的功能。篩選后只顯示出包含符合條件的數(shù)據(jù)行，而隱藏其它行。 在任務(wù)2中，為了及時(shí)跟蹤各個(gè)類別與各個(gè)品牌的商品銷售情況，需要從圖5-1中的銷售清單中查詢相關(guān)信息，可以通過(guò)自動(dòng)篩選獲取上述信息。,5.3.1 自動(dòng)篩選,數(shù)據(jù)管理

12、與分析,以“類別”字段的篩選作為例子，具體操作步驟如下。 步驟1：?jiǎn)螕魣D5-1中銷售清單中的任一單元格。 步驟2：選擇“數(shù)據(jù)”“篩選”“自動(dòng)篩選”菜單項(xiàng)。數(shù)據(jù)列表中第一行的各列中將分別顯示出一個(gè)下拉按鈕，如圖5-1所示，自動(dòng)篩選就將通過(guò)它們進(jìn)行。 步驟3：?jiǎn)螕粜枰M(jìn)行篩選的列標(biāo)的下拉列表，Excel會(huì)顯示出該列中所有不同的數(shù)據(jù)值，這些值可用于篩選條件，如單擊“類別”旁邊的下拉列表，會(huì)顯示出“類別”列中所有的值，如圖5-9所示。 步驟4：如要查看“手機(jī)”的銷售情況，只需在下拉列表中選擇“手機(jī)”，系統(tǒng)就會(huì)顯示如圖5-10所示的結(jié)果。,對(duì)銷售清單自動(dòng)篩選,數(shù)據(jù)管理與分析,圖5-9自動(dòng)篩選,圖5-10

13、自動(dòng)篩選后的結(jié)果,數(shù)據(jù)管理與分析,對(duì)庫(kù)存清單自動(dòng)篩選,在任務(wù)2中，我們還需對(duì)庫(kù)存清單進(jìn)行篩選，找出庫(kù)存最大的前5種商品，給店主張某提供進(jìn)貨的參考，庫(kù)存清單如圖5-11所示。,圖5-11 庫(kù)存清單,數(shù)據(jù)管理與分析,點(diǎn)擊“自動(dòng)篩選”命令后，系統(tǒng)添加下拉列表標(biāo)志，我們要篩選出5種庫(kù)存最大的商品，應(yīng)單擊“期末庫(kù)存”列標(biāo)的下拉列表，然后選擇列表中的“(前10個(gè))”，Excel會(huì)彈出顯示個(gè)數(shù)設(shè)置的對(duì)話框，如圖5-12所示。在 “顯示”的下拉列表中選擇“最大”，然后在編輯框中輸入5。篩選結(jié)果如圖5-13所示。,圖5-12 自動(dòng)篩選前10個(gè),圖5-13 自動(dòng)篩選前10個(gè)后的結(jié)果,數(shù)據(jù)管理與分析,自定義自動(dòng)篩選

14、,如果要找出庫(kù)存大于0并且小于等于3的手機(jī)的庫(kù)存情況，需要分別對(duì)“期末庫(kù)存”和“類別”進(jìn)行兩步篩選。第一步自定義自動(dòng)篩選方式如圖5-14所示； 第二步類別篩選同前面例子。,圖5-14 自定義自動(dòng)篩選,數(shù)據(jù)管理與分析,自定義篩選只能完成條件簡(jiǎn)單的數(shù)據(jù)篩選，如果篩選的條件比較復(fù)雜，自定義篩選就會(huì)顯得比較麻煩。對(duì)于篩選條件較多的情況，可以使用高級(jí)篩選功能來(lái)處理。 使用高級(jí)篩選功能，必須先建立一個(gè)條件區(qū)域，用來(lái)指定篩選條件。條件區(qū)域的第一行是所有作為篩選條件的字段名，這些字段名與數(shù)據(jù)列表中的字段名必須一致，條件區(qū)域的其它行則輸入篩選條件。需要注意的是，條件區(qū)域和數(shù)據(jù)列表不能連接，必須用以空行或空列將其

15、隔開。 條件區(qū)域的構(gòu)造規(guī)則是：同一列中的條件是“或”，同一行中的條件是“與”。,5.3.2 高級(jí)篩選,數(shù)據(jù)管理與分析,步驟1：在庫(kù)存清單中創(chuàng)建一個(gè)條件區(qū)域，輸入篩選條件，這里在I1、J1、K1單元格中分別輸入“類別”、“期末庫(kù)存”、“期末庫(kù)存”，在I2、J2、K2中分別輸入“手機(jī)”、“0”、“=3”。 步驟2：選定庫(kù)存清單數(shù)據(jù)列表中的任一單元格，然后選擇“數(shù)據(jù)”“篩選”“高級(jí)篩選”菜單項(xiàng)，打開如圖5-15所示的“高級(jí)篩選”對(duì)話框。 步驟3：指定數(shù)據(jù)列表區(qū)域和條件區(qū)域。 步驟4：指定保存結(jié)果的區(qū)域。 步驟5：最后單擊“確定”按鈕。,對(duì)庫(kù)存清單高級(jí)篩選,數(shù)據(jù)管理與分析,圖5-15 高級(jí)篩選,數(shù)據(jù)管

16、理與分析,現(xiàn)在讓我們來(lái)完成任務(wù)2中的最后一個(gè)要求，分析銷售統(tǒng)計(jì)表，找出銷售金額高于平均銷售金額的商品。 那么是否可以在高級(jí)篩選條件中包含一個(gè)平均值計(jì)算公式呢？答案是肯定的，Excel的高級(jí)篩選允許建立計(jì)算條件。建立計(jì)算條件須滿足下列3條原則。 計(jì)算條件中的標(biāo)題可以是任何文本或空白，不能與數(shù)據(jù)列表中的任一列標(biāo)相同，這一點(diǎn)與前面指定的條件區(qū)域剛好相反。 必須以絕對(duì)引用的方式引用數(shù)據(jù)列表外的單元格。 必須以相對(duì)引用的方式引用數(shù)據(jù)列表內(nèi)的單元格。,包含計(jì)算條件的高級(jí)篩選,數(shù)據(jù)管理與分析,步驟1：在單元格I9（或任一空白單元格）中輸入平均值計(jì)算公式“=median(E4:E30)”，該公式的計(jì)算結(jié)果為1

17、440。 步驟2：在I1中輸入計(jì)算條件的列標(biāo)，其值須滿足上述的第1條原則，如輸入“高于平均銷售金額”。 步驟3：在I2中輸入計(jì)算條件公式“=E4$I$9”，輸入該公式須滿足上述的第2、3條規(guī)則，E4是數(shù)據(jù)列表中的單元格，因此只能使用相對(duì)引用的方式。I9包含平均值公式，是數(shù)據(jù)列表之外的單元格，只能采用絕對(duì)引用的方式。 計(jì)算條件建立好之后，如圖5-16所示，按照前面介紹的步驟進(jìn)行高級(jí)篩選，篩選的結(jié)果如圖5-17所示。,建立計(jì)算條件,數(shù)據(jù)管理與分析,圖5-16 建立計(jì)算條件,數(shù)據(jù)管理與分析,圖5-17 使用計(jì)算條件篩選后的結(jié)果,數(shù)據(jù)管理與分析,分類匯總是對(duì)數(shù)據(jù)列表指定的行或列中的數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)，統(tǒng)

18、計(jì)的內(nèi)容可以由用戶指定，通過(guò)折疊或展開行、列數(shù)據(jù)和匯總結(jié)果，從匯總和明細(xì)兩種角度顯示數(shù)據(jù)，可以快捷的創(chuàng)建各種匯總報(bào)告。在這一節(jié)中，我們將使用分類匯總來(lái)完成任務(wù)3。,5.4 分類匯總,數(shù)據(jù)管理與分析,Excel可自動(dòng)計(jì)算數(shù)據(jù)列表中的分類匯總和總計(jì)值。當(dāng)插入自動(dòng)分類匯總時(shí)，Excel將分級(jí)顯示數(shù)據(jù)列表，以便為每個(gè)分類匯總顯示或隱藏明細(xì)數(shù)據(jù)行。Excel分類匯總的數(shù)據(jù)折疊層次最多可達(dá)8層。分類匯總可以完成以下工作。 顯示一組數(shù)據(jù)的分類匯總及總和。 顯示多組數(shù)據(jù)的分類匯總及總和。 在分組數(shù)據(jù)上完成不同的計(jì)算，如求和、統(tǒng)計(jì)個(gè)數(shù)、求平均值（或最大值、最小值）、求總體方差等。,5.4.1 分類匯總概述,數(shù)據(jù)

19、管理與分析,在創(chuàng)建分類匯總之前，首先要保證要進(jìn)行分類匯總的數(shù)據(jù)區(qū)域必須是一個(gè)連續(xù)的數(shù)據(jù)區(qū)域，而且每個(gè)數(shù)據(jù)列都有列標(biāo)題；然后必須對(duì)要進(jìn)行分類匯總的列進(jìn)行排序。這個(gè)排序的列標(biāo)題稱為分類匯總關(guān)鍵字，分類匯總時(shí)只能指定排序后的列標(biāo)題為匯總關(guān)鍵字。 例如，對(duì)于圖5-1所示的銷售清單，如果要統(tǒng)計(jì)各個(gè)類別的商品銷售數(shù)量，應(yīng)該先以“類別”字段為主要關(guān)鍵字進(jìn)行自定義排序，并以“品牌”字段為次要關(guān)鍵字按升序排序，排序后的結(jié)果如圖5-18所示。,5.4.2 創(chuàng)建分類匯總,數(shù)據(jù)管理與分析,圖5-18 按類別自定義排序后的銷售清單,數(shù)據(jù)管理與分析,步驟1：?jiǎn)螕魯?shù)據(jù)區(qū)域中的任一單元格，然后選擇“數(shù)據(jù)”“分類匯總”菜單項(xiàng)，

20、打開如圖5-19所示的“分類匯總”對(duì)話框。 步驟2：從“分類字段”下拉列表中選擇要進(jìn)行分類的字段，分類字段必須是已經(jīng)排好序，在本例中，我們選擇“類別”作為分類字段。 步驟3：“匯總方式”下拉列表中列出了所有匯總方式（統(tǒng)計(jì)個(gè)數(shù)、計(jì)算平均值、求最大值或最小值及計(jì)算總和等）。在本例中，我們選擇“求和”作為匯總方式。 步驟4：“選定匯總項(xiàng)”的列表中列出了所有列標(biāo)題，從中選擇需要匯總的列，列的數(shù)據(jù)類型必須和匯總方式相符合。在本例中我們選擇“數(shù)量”作為匯總項(xiàng)。 步驟5：選擇匯總數(shù)據(jù)的保存方式，有3種方式可以選擇，可同時(shí)選中，默認(rèn)選擇是第1和第3項(xiàng)。點(diǎn)擊“確定”按鈕，結(jié)果如圖5-20所示。,統(tǒng)計(jì)各個(gè)類別的商

21、品銷售數(shù)量,數(shù)據(jù)管理與分析,圖5-19 分類匯總對(duì)話框,圖5-20分類匯總后的結(jié)果,數(shù)據(jù)管理與分析,在Excel中我們也可以對(duì)多項(xiàng)指標(biāo)進(jìn)行匯總，并且可以進(jìn)行嵌套分類匯總 。 現(xiàn)在讓我們來(lái)完成任務(wù)3，我們需要對(duì)銷售統(tǒng)計(jì)表中的銷售金額和利潤(rùn)金額兩項(xiàng)指標(biāo)進(jìn)行匯總，并且需要對(duì)各個(gè)類別與各個(gè)品牌的商品進(jìn)行分類匯總，由于每個(gè)類別都有多個(gè)品牌，因此我們可以先對(duì)類別進(jìn)行分類匯總，然后在此基礎(chǔ)上再對(duì)品牌進(jìn)行分類匯總。,嵌套分類匯總,數(shù)據(jù)管理與分析,步驟1：?jiǎn)螕翡N售統(tǒng)計(jì)表中的任一單元格，然后選擇“數(shù)據(jù)”“分類匯總”菜單項(xiàng)，打開 “分類匯總”對(duì)話框。 步驟2：分類字段選擇“類別”，匯總方式選擇“求和”，在“選定匯總

22、項(xiàng)”下拉列表框中選擇“銷售金額”和“利潤(rùn)金額”兩個(gè)字段，按“確定”按鈕即可得到如圖5-21所示的結(jié)果。 步驟3：再次選擇“數(shù)據(jù)”“分類匯總”菜單項(xiàng)。分類字段選擇“品牌”，匯總方式和匯總項(xiàng)與第2步相同，清除“替換當(dāng)前分類匯總”復(fù)選框。按下“確定”按鈕，我們就可以得到如圖5-22所示的結(jié)果。 分類匯總前先將銷售統(tǒng)計(jì)表按類別和品牌關(guān)鍵字排序。,嵌套分類匯總（續(xù)）,數(shù)據(jù)管理與分析,圖5-21 一級(jí)分類匯總結(jié)果,圖5-22 兩級(jí)分類匯總結(jié)果,下圖中“金士頓 匯總”錯(cuò)誤的顯示在“儲(chǔ)存卡 匯總”的下面，這是Excel 2003的一個(gè)Bug，出現(xiàn)以下情況時(shí)，可能會(huì)導(dǎo)致結(jié)果的顯示順序不正確。 匯總顯示在明細(xì)數(shù)據(jù)

23、的下方。 一個(gè)或多個(gè)分類匯總片段僅包含一個(gè)項(xiàng)目。,由于銷售統(tǒng)計(jì)表中儲(chǔ)存卡僅包含金士頓一個(gè)品牌，而匯總顯示在明細(xì)數(shù)據(jù)的下方，因此導(dǎo)致了圖5-22中的錯(cuò)誤顯示。,數(shù)據(jù)管理與分析,如果由于某種原因，需要取消分類匯總的顯示結(jié)果，恢復(fù)到數(shù)據(jù)列表的初始狀態(tài)。其操作步驟如下。 步驟1：?jiǎn)螕舴诸悈R總數(shù)據(jù)列表中任一單元格。 步驟2：選擇“數(shù)據(jù)”“分類匯總”命令，打開“分類匯總”對(duì)話框。 步驟3：?jiǎn)螕魧?duì)話框中的“全部刪除”按鈕即可。 經(jīng)過(guò)以上步驟之后，數(shù)據(jù)列表中的分類匯總就被刪除了，恢復(fù)成匯總前的數(shù)據(jù)。第3步中的“全部刪除”只會(huì)刪除分類匯總，不會(huì)刪除原始數(shù)據(jù)。,5.4.3 刪除分類匯總,數(shù)據(jù)管理與分析,數(shù)據(jù)透視表

24、是一種對(duì)大量數(shù)據(jù)快速匯總和建立交叉列表的交互式表格，不僅能夠改變行和列以查看源數(shù)據(jù)的不同匯總結(jié)果，也可以顯示不同頁(yè)面以篩選數(shù)據(jù)，還可以根據(jù)需要顯示區(qū)域中的明細(xì)數(shù)據(jù)。數(shù)據(jù)透視圖則是一個(gè)動(dòng)態(tài)的圖表，它可以將創(chuàng)建的數(shù)據(jù)透視表以圖表的形式顯示出來(lái)。在這一節(jié)中我們將使用數(shù)據(jù)透視表和數(shù)據(jù)透視圖來(lái)完成任務(wù)4。,5.5 使用數(shù)據(jù)透視表,數(shù)據(jù)管理與分析,5.5.1 數(shù)據(jù)透視表概述,數(shù)據(jù)管理與分析,數(shù)據(jù)透視表示例,圖5-23 數(shù)據(jù)透視表示例,數(shù)據(jù)管理與分析,數(shù)據(jù)透視表的創(chuàng)建可以通過(guò)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А边M(jìn)行。 利用向?qū)?chuàng)建數(shù)據(jù)透視表需要3個(gè)步驟。 第1步，選擇所創(chuàng)建的數(shù)據(jù)透視表的數(shù)據(jù)源類型； 第2步，選擇

25、數(shù)據(jù)源的區(qū)域； 第3步，設(shè)計(jì)將要生成的數(shù)據(jù)透視表的版式和選項(xiàng)。 下面讓我們來(lái)完成任務(wù)4，從銷售清單中創(chuàng)建數(shù)據(jù)透視表，統(tǒng)計(jì)各個(gè)類別與各個(gè)品牌的銷售數(shù)量。,5.5.2 創(chuàng)建數(shù)據(jù)透視表,數(shù)據(jù)管理與分析,步驟1 選擇數(shù)據(jù)源與報(bào)表類型,圖5-24 向?qū)Р襟E1,數(shù)據(jù)管理與分析,步驟2 選定區(qū)域 步驟3 選定顯示位置,圖5-25 向?qū)Р襟E2,圖5-26 向?qū)Р襟E3,數(shù)據(jù)管理與分析,步驟4 設(shè)置布局,圖5-27 向?qū)Р襟E4,數(shù)據(jù)管理與分析,生成的數(shù)據(jù)透視表,圖5-28 生成的數(shù)據(jù)透視表,數(shù)據(jù)管理與分析,創(chuàng)建好數(shù)據(jù)透視表之后，根據(jù)需要有可能要對(duì)它的布局、數(shù)據(jù)項(xiàng)、數(shù)據(jù)匯總方式與顯示方式、格式等進(jìn)行修改。 修改數(shù)據(jù)

26、透視表的布局 修改數(shù)據(jù)透視表的數(shù)據(jù)項(xiàng) 隱藏或顯示行、列中的數(shù)據(jù)項(xiàng) 調(diào)整數(shù)據(jù)項(xiàng)顯示的位置 修改數(shù)據(jù)透視表的數(shù)據(jù)匯總方式和顯示方式 修改數(shù)據(jù)透視表的格式,5.5.3 修改數(shù)據(jù)透視表,數(shù)據(jù)管理與分析,修改數(shù)據(jù)透視表的布局,圖5-29 修改數(shù)據(jù)透視表的布局,數(shù)據(jù)管理與分析,隱藏或顯示行、列中的數(shù)據(jù)項(xiàng) 例如在圖5-28中所示的數(shù)據(jù)透視表中，不想顯示第5行，即儲(chǔ)存卡的銷售數(shù)量，可以單擊 “類別”字段旁邊的下拉列表?xiàng)l，系統(tǒng)會(huì)顯示如圖5-30所示的對(duì)話框，將對(duì)話框中“儲(chǔ)存卡”前面的復(fù)選標(biāo)志清除，然后單擊“確定”按鈕，數(shù)據(jù)透視表中就沒(méi)有“儲(chǔ)存卡”這一行的數(shù)據(jù)了。 調(diào)整數(shù)據(jù)項(xiàng)顯示的位置 拖動(dòng)數(shù)據(jù)項(xiàng)的名稱到合適的位

27、置釋放即可。,修改數(shù)據(jù)透視表的數(shù)據(jù)項(xiàng),圖5-30 行字段列表,數(shù)據(jù)管理與分析,在默認(rèn)情況下，數(shù)據(jù)透視表采用SUM函數(shù)對(duì)其中的數(shù)值項(xiàng)進(jìn)行匯總，用COUNT函數(shù)對(duì)文本類型字段項(xiàng)進(jìn)行計(jì)數(shù)。 但有時(shí)SUM和COUNT函數(shù)并不能滿足透視需要，如平均值、百分比、最大值之類的計(jì)算。實(shí)際上，Excel提供了很多匯總方式，在數(shù)據(jù)透視表中可以使用這些函數(shù)。 操作方法是：雙擊數(shù)據(jù)透視表中的數(shù)據(jù)字段，系統(tǒng)會(huì)彈出“數(shù)據(jù)透視表字段”對(duì)話框，如圖5-31所示，在對(duì)話框中從“匯總方式”中選擇需要的函數(shù)。,修改數(shù)據(jù)透視表的數(shù)據(jù)匯總方式和顯示方式,數(shù)據(jù)管理與分析,默認(rèn)情況下，數(shù)據(jù)透視表中的數(shù)據(jù)是以數(shù)值方式顯示，不過(guò)我們可以根據(jù)需

28、要將它修改為其他形式的數(shù)據(jù)顯示形式，如顯示為小數(shù)、百分?jǐn)?shù)或其他需要的形式。例如把數(shù)據(jù)顯示方式設(shè)為“占總和的百分比”。,圖5-31 數(shù)據(jù)透視表字段,數(shù)據(jù)管理與分析,數(shù)據(jù)透視表的格式也可以修改，可以象格式化工作表一樣，將數(shù)據(jù)透視表的格式設(shè)置成各種需要的樣式。用戶可以使用“自動(dòng)套用格式”命令將Excel內(nèi)置的數(shù)據(jù)透視表格式應(yīng)用于選中的數(shù)據(jù)透視表。自動(dòng)套用數(shù)據(jù)透視表格式時(shí)，首先選定數(shù)據(jù)透視表，然后單擊“數(shù)據(jù)透視表”工具欄中的“設(shè)置報(bào)告格式”按鈕，打開如圖5-32所示的“自動(dòng)套用格式”對(duì)話框。在對(duì)話框中選擇要應(yīng)用的格式即可。,修改數(shù)據(jù)透視表的格式,數(shù)據(jù)管理與分析,圖5-32 自動(dòng)套用格式,數(shù)據(jù)管理與分析

29、,數(shù)據(jù)透視圖表是利用數(shù)據(jù)透視的結(jié)果制作的圖表，數(shù)據(jù)透視圖總是與數(shù)據(jù)透視表相關(guān)聯(lián)的。如果更改了數(shù)據(jù)透視表中某個(gè)字段的位置，則透視圖中與之相對(duì)應(yīng)的字段位置也會(huì)改變。 數(shù)據(jù)透視表中的行字段對(duì)應(yīng)于數(shù)據(jù)透視圖中的分類字段，而列字段則對(duì)應(yīng)于數(shù)據(jù)透視圖中的系列字段。數(shù)據(jù)透視表中的頁(yè)字段和數(shù)據(jù)字段分別對(duì)應(yīng)于數(shù)據(jù)透視圖中的頁(yè)字段和數(shù)據(jù)字段。,5.5.4 制作數(shù)據(jù)透視圖,數(shù)據(jù)管理與分析,在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А钡牡?步中將所需創(chuàng)建的報(bào)表類型選為“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”，這樣就會(huì)同時(shí)創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖。 如果已經(jīng)單獨(dú)創(chuàng)建了數(shù)據(jù)透視表，那么只要單擊常用工具欄中的“圖表向?qū)А被颉皵?shù)據(jù)透視表”工具欄

30、中的“圖表向?qū)А卑粹o，系統(tǒng)會(huì)自動(dòng)插入一個(gè)新的數(shù)據(jù)透視圖。,數(shù)據(jù)透視圖的創(chuàng)建方法,數(shù)據(jù)管理與分析,圖5-33 與圖28 中數(shù)據(jù)透視表對(duì)應(yīng)的數(shù)據(jù)透視圖,數(shù)據(jù)管理與分析,修改數(shù)據(jù)透視圖的圖表類型和選項(xiàng),上一節(jié)中我們使用“自動(dòng)套用格式”去修改數(shù)據(jù)透視表的格式，而數(shù)據(jù)透視圖則可以修改它的圖表類型和圖表選項(xiàng)。單擊“圖表”菜單的“圖表類型” 或“圖表選項(xiàng)”菜單項(xiàng)即可打開相應(yīng)對(duì)話框,圖5-34 修改圖表類型,圖5-35 修改圖表選項(xiàng),數(shù)據(jù)管理與分析,在進(jìn)行數(shù)據(jù)管理與分析的過(guò)程中，有時(shí)需要將數(shù)據(jù)導(dǎo)入到Excel工作表中，有時(shí)則要將Excel工作表導(dǎo)出為其他類型的數(shù)據(jù)。 本節(jié)介紹Excel常用的導(dǎo)入導(dǎo)出功能。,5

31、.6 數(shù)據(jù)的導(dǎo)入與導(dǎo)出,數(shù)據(jù)管理與分析,在進(jìn)行數(shù)據(jù)管理與分析的過(guò)程中，有時(shí)需要將網(wǎng)頁(yè)中的數(shù)據(jù)導(dǎo)入到Excel工作表中。 例如店主張某可能需要獲取最新的手機(jī)報(bào)價(jià)，然后據(jù)此調(diào)整店鋪的手機(jī)賣價(jià)。Excel提供了導(dǎo)入Web頁(yè)數(shù)據(jù)的功能。,5.6.1 導(dǎo)入Web數(shù)據(jù),數(shù)據(jù)管理與分析,步驟1：首先要連上Internet網(wǎng)絡(luò)，保證可以訪問(wèn)Internet上的Web網(wǎng)站。 步驟2：選擇“數(shù)據(jù)”“導(dǎo)入外部數(shù)據(jù)”“新建Web查詢”菜單項(xiàng)，打開“新建Web查詢”對(duì)話框，如圖5-36所示。 步驟3：在圖5-36中“地址”文本框中輸入要導(dǎo)入數(shù)據(jù)的Web頁(yè)的網(wǎng)絡(luò)地址，然后單擊“轉(zhuǎn)到”按鈕，系統(tǒng)會(huì)顯示完整的Web頁(yè)。本例中

32、顯示的是太平洋電腦網(wǎng)的手機(jī)頻道。 步驟4：?jiǎn)螕鬢eb頁(yè)數(shù)據(jù)區(qū)域中要導(dǎo)入數(shù)據(jù)前的按鈕 ，它會(huì)變成 。 步驟5：?jiǎn)螕簟皩?dǎo)入”按鈕，系統(tǒng)會(huì)彈出設(shè)置數(shù)據(jù)放置位置的對(duì)話框，如圖5-37所示。 步驟6：設(shè)置了數(shù)據(jù)的放置位置后，單擊“確定”按鈕，系統(tǒng)就會(huì)將選定的數(shù)據(jù)導(dǎo)入到工作表中，結(jié)果如圖5-38所示。,操作步驟,數(shù)據(jù)管理與分析,圖5-36 新建web查詢,圖5-38 導(dǎo)入數(shù)據(jù)后的工作表,圖5-37導(dǎo)入數(shù)據(jù)對(duì)話框,數(shù)據(jù)管理與分析,與Excel交換數(shù)據(jù)的文本文件通常是帶分隔符的文本文件 (.txt)，一般用制表符分隔文本的每個(gè)字段，也可以使用逗號(hào)分隔文本的每個(gè)字段。 導(dǎo)入文本文件有兩種方法，可以通過(guò)打開文本文

33、件的方式來(lái)導(dǎo)入，也可以通過(guò)導(dǎo)入外部數(shù)據(jù)的方式來(lái)導(dǎo)入。我們以導(dǎo)入一個(gè)存放有客戶資料的文本文件為例，如圖5-39所示，介紹導(dǎo)入文本文件的操作過(guò)程。 Excel工作表中的數(shù)據(jù)可以直接保存為文本文件。方法是：使用“文件”“另存為”菜單項(xiàng)將 Excel 工作表轉(zhuǎn)換為文本文件。,5.6.2 文本文件的導(dǎo)入與導(dǎo)出,數(shù)據(jù)管理與分析,圖5-39 客戶資料.txt,圖5-40 打開文件對(duì)話框,數(shù)據(jù)管理與分析,步驟1：?jiǎn)?dòng)Excel，選擇“文件”“打開”菜單項(xiàng)，系統(tǒng)彈出“打開”對(duì)話框，如圖5-40所示。 步驟2：在“打開”對(duì)話框中，從“文件類型”下拉列表中選擇“文本文件”，在文件名中輸入“客戶資料.txt”，然后單

34、擊“打開”按鈕。Excel 將啟動(dòng)“文本導(dǎo)入向?qū)А?，打開向?qū)У牡?步對(duì)話框。 步驟3：向?qū)У牡?步主要是設(shè)置文本文件中各列數(shù)據(jù)之間的間隔符，這與導(dǎo)入的文件相關(guān)。在本例中，我們選擇“分隔符號(hào)”，并在導(dǎo)入起始行中輸入1，即要把標(biāo)題行導(dǎo)入到Excel工作表中，如不要標(biāo)題行，可將導(dǎo)入起始行改為2。然后單擊“下一步”按鈕，進(jìn)入向?qū)У牡?步對(duì)話框。 步驟4：向?qū)У牡?步主要是設(shè)置用作分隔文本數(shù)據(jù)列的具體符號(hào)。本例中我們選擇“Tab”鍵，然后單擊“下一步”按鈕，打開向?qū)У牡?步對(duì)話框。 步驟5：向?qū)У牡?步主要是設(shè)置每列的數(shù)據(jù)類型。本例中，我們將“固定電話”、“手機(jī)”、“郵編”這3列全部設(shè)置成文本。最后單擊“完成”按鈕，系統(tǒng)就會(huì)將該文本文件導(dǎo)入到Excel中。,通過(guò)打開文本文件來(lái)導(dǎo)入,數(shù)據(jù)管理與分析,步驟1：?jiǎn)螕粢脕?lái)放置文本文件數(shù)據(jù)的單元格。 步驟2：選擇“數(shù)據(jù)”“導(dǎo)入外部數(shù)據(jù)” “導(dǎo)入數(shù)據(jù)”菜單項(xiàng)，系統(tǒng)會(huì)彈出“選取數(shù)據(jù)源”對(duì)話框。 步驟3：在“選取數(shù)據(jù)源”對(duì)話框中，從“文件類型”下拉列表中選擇“文本文件”，在文件名中輸入“客戶資料.txt”，