第5章數(shù)據(jù)透視表匯總海量數(shù)據(jù)教師：Office助手Pivottablessummarizemassiveamountsofdata實例51海量數(shù)據(jù)快速分類匯總實際工作中經(jīng)常會遇到包含海量數(shù)據(jù)的工作表，面對眾多的數(shù)據(jù)使用公式或函數(shù)等方法進(jìn)行處理，不僅運(yùn)算操作復(fù)雜，而且容易出錯，此時，使用數(shù)據(jù)透視表僅需用鼠標(biāo)進(jìn)行相關(guān)操作即可滿足大部分需求，既簡化了操作，又提高了工作效率。如圖所示的表中包含了幾百條銷售明細(xì)記錄，存放在“年度統(tǒng)計”工作表中，現(xiàn)要求根據(jù)年度統(tǒng)計中的銷售明細(xì)記錄，按照分部和銷售渠道對銷售額進(jìn)行匯總。實例51海量數(shù)據(jù)快速分類匯總此例可利用數(shù)據(jù)透視表按照要求快速實現(xiàn)匯總，操作步驟如下：步驟1：單擊A2:J373區(qū)域中的任意一個單元格(例如D3單元格)。打開“插入”選項卡，單擊“數(shù)據(jù)透視表”按鈕，彈出“創(chuàng)建數(shù)據(jù)透視表”對話框。步驟2：選中“選擇單元格區(qū)域”單選按鈕，在其下方的文本框中輸入要分析的數(shù)據(jù)區(qū)域，通常默認(rèn)選擇（如果系統(tǒng)給出的區(qū)域選擇不正確，那么用戶可拖動鼠標(biāo)重新選擇區(qū)域），然后選中“新工作表”單選按鈕，單擊“確定”按鈕，進(jìn)入如圖所示的數(shù)據(jù)透視表設(shè)計環(huán)境。實例51海量數(shù)據(jù)快速分類匯總步驟3：在右側(cè)“數(shù)據(jù)透視表字段”窗格中，拖動“分部”到“行”區(qū)域；拖動“銷售渠道”到“列”區(qū)域；拖動“銷售額”到“值”區(qū)域，即可按照分部和銷售渠道得到銷售額的匯總結(jié)果，如圖所示。實例51海量數(shù)據(jù)快速分類匯總步驟4：將A4單元格中的“行標(biāo)簽”設(shè)置為具有實際含義的字段名稱，需要將數(shù)據(jù)透視表的報表布局設(shè)為“以表格形式顯示”，設(shè)置方法如左圖所示，效果如右圖所示。通過鼠標(biāo)簡單的拖動操作，即可從幾百條數(shù)據(jù)記錄中快捷的得到所需的匯總結(jié)果實例51海量數(shù)據(jù)快速分類匯總數(shù)據(jù)透視表不僅可以按照條件對海量數(shù)據(jù)進(jìn)行分類匯總，還可以靈活地改變分類匯總的方式，以多種不同方式展示數(shù)據(jù)的特征。如圖所示中，要求按照商品類別和銷售渠道對銷售金額進(jìn)行分類匯總，只需要更改數(shù)據(jù)透視表的字段布局，將數(shù)據(jù)透視表“行”區(qū)域中的“分部”更換為“商品類別”，即可將數(shù)據(jù)透視表的分類匯總結(jié)果自動更新，如圖所示。實例51海量數(shù)據(jù)快速分類匯總?cè)绻偬砑有碌囊螅蟀凑珍N售渠道、分店、商品類別對全年銷售記錄進(jìn)行分類匯總，只需要更改數(shù)據(jù)透視表的字段布局，將“銷售區(qū)域”和“分店”字段分別拖動到“行”區(qū)域中，將“商品類別”字段拖動到“列”區(qū)域中，即可將數(shù)據(jù)透視表的分類匯總結(jié)果自動更新，如圖所示。實例52將數(shù)據(jù)按照季度、月份分類匯總?cè)绻麛?shù)據(jù)源中有日期字段，利用數(shù)據(jù)透視表的分組功能將日期歸類分組匯總到月份、季度，然后按照月份、季度進(jìn)行分類匯總，下面通過實例介紹具體介紹。如圖所示，要求將其按照季度和月份對銷售額進(jìn)行分類匯總，操作步驟如下：實例52將數(shù)據(jù)按照季度、月份分類匯總步驟1：首先將數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表，在“數(shù)據(jù)透視表字段”窗格中，將“日期”字段拖動到“行”區(qū)域，打開“分析”選項卡，單擊“組字段”按鈕，如左圖所示，彈出“組合”對話框。步驟2：在對話框中選中“月”和“季度”，如中間圖所示，單擊“確定”按鈕，將日期按照“月”和“季度”分組顯示，效果如右圖所示。實例52將數(shù)據(jù)按照季度、月份分類匯總步驟3：在“數(shù)據(jù)透視表字段”窗格中，將“銷售額”字段拖到“值”區(qū)域，即可實現(xiàn)按照季度和月份對銷售額進(jìn)行分類匯總，如左圖所示。步驟4：在A列中季度和月份兩個字段被壓縮顯示在一列，若將季度和月份兩個字段分別顯示在兩列，可將數(shù)據(jù)透視表的報表布局更改為“以表格形式顯示”，更改方法如右圖所示。實例52將數(shù)據(jù)按照季度、月份分類匯總步驟5：更改報表布局后的效果如左圖所示，此時，季度字段位于A列，月份位于B列，字段名為“銷售日期”。步驟6：單擊“銷售日期”單元格，在編輯欄中將其改為“月份”，如右圖所示，更改后的效果如圖所示。這樣就完成了將上半年銷售記錄按照季度、月份對銷售額進(jìn)行分類匯總的要求。實例52將數(shù)據(jù)按照季度、月份分類匯總數(shù)據(jù)透視表使用分組功能后，根據(jù)需要可以再添加或調(diào)整數(shù)據(jù)透視表字段，數(shù)據(jù)透視表的結(jié)果會同步更新。例如，在按季度和月份匯總的基礎(chǔ)上，再添加“分部”對銷售額分類匯總，此時，只需將“數(shù)據(jù)透視表字段”窗格中的“分部”字段拖動到“列”區(qū)域即可，匯總結(jié)果同步更新，如圖所示。實例53動態(tài)更新數(shù)據(jù)透視表結(jié)果創(chuàng)建數(shù)據(jù)透視表后，如果數(shù)據(jù)源的數(shù)據(jù)發(fā)生了變化，那么數(shù)據(jù)透視表中的結(jié)果也應(yīng)該隨之發(fā)生變化。數(shù)據(jù)源的數(shù)據(jù)變化有2種情況，一是原有數(shù)據(jù)源范圍不變，數(shù)據(jù)變化，二是原有數(shù)據(jù)源范圍增大或減小，數(shù)據(jù)增多或減少。1.原有數(shù)據(jù)源范圍不變，數(shù)據(jù)變化若要更新數(shù)據(jù)透視表結(jié)果，單擊數(shù)據(jù)透視表中的任意一個單元格(如C4)，打開“分析”選項卡，單擊“刷新”按鈕，如圖所示，或按快捷鍵Alt+F5，當(dāng)前數(shù)據(jù)透視表的結(jié)果隨數(shù)據(jù)源內(nèi)容的變化而變化。若要更新工作簿中所有的數(shù)據(jù)透視表，則單擊“全部刷新”按鈕。實例53動態(tài)更新數(shù)據(jù)透視表結(jié)果2.原有數(shù)據(jù)源范圍變化，數(shù)據(jù)變化使用刷新功能，更新后的數(shù)據(jù)透視表的結(jié)果有可能不準(zhǔn)確，此時可以使用以下兩種方法更新數(shù)據(jù)透視表以確保結(jié)果正確。方法1：重新選擇數(shù)據(jù)透視表的數(shù)據(jù)源范圍如左圖所示的銷售記錄得到右圖所示的數(shù)據(jù)透視表，當(dāng)銷售記錄持續(xù)增加時，需要重新選擇數(shù)據(jù)透視表的數(shù)據(jù)源，才能確保更新后的數(shù)據(jù)透視表結(jié)果正確，操作步驟如下：實例53動態(tài)更新數(shù)據(jù)透視表結(jié)果步驟1：單擊數(shù)據(jù)透視表中的任意一個單元格，打開“分析”選項卡，單擊“更改數(shù)據(jù)源”按鈕，打開的下拉列表中選擇“更改數(shù)據(jù)源”，如左圖所示。如果數(shù)據(jù)源不在當(dāng)前工作簿中，而是來自于其他Excel文件或其他外部渠道的數(shù)據(jù)源時，單擊“連接屬性”，對外部數(shù)據(jù)源進(jìn)行設(shè)置。步驟2：單擊“更改數(shù)據(jù)源”后，彈出“更改數(shù)據(jù)透視表數(shù)據(jù)源”對話框，重新選擇更改后的數(shù)據(jù)源區(qū)域即可，如右圖所示。實例53動態(tài)更新數(shù)據(jù)透視表結(jié)果步驟3：重新選擇數(shù)據(jù)透視表的數(shù)據(jù)源后，若使數(shù)據(jù)透視表結(jié)果同步更新，打開“分析”選項卡，單擊“刷新”按鈕，即可更新當(dāng)前數(shù)據(jù)透視表。方法2：創(chuàng)建超級表，動態(tài)更新數(shù)據(jù)透視表的數(shù)據(jù)源范圍重新選擇數(shù)據(jù)透視表數(shù)據(jù)源后，需要單擊“分析”選項卡中“刷新”按鈕，才能確保數(shù)據(jù)透視表結(jié)果同步更新，這種更新方式適用于數(shù)據(jù)源范圍偶爾變動。如果數(shù)據(jù)源范圍頻繁變動，可將普通表創(chuàng)建為超級表，利用超級表的行列自動擴(kuò)展功能，為數(shù)據(jù)透視表創(chuàng)建動態(tài)數(shù)據(jù)源，從而使數(shù)據(jù)透視表結(jié)果隨著數(shù)據(jù)源的變化而自動更新。創(chuàng)建超級表的操作步驟如下：步驟1：單擊數(shù)據(jù)源中的任意一個單元格(例如D1單元格)，按快捷鍵Ctrl＋T或者打開“插入”選項卡，單擊“表格”按鈕，彈出“創(chuàng)建表”對話框，單擊“確定”按鈕，即可將普通表創(chuàng)建為超級表。步驟2：此時，超級表的名稱自動顯示為“表1”，每個列標(biāo)題名的右側(cè)出現(xiàn)篩選符號，數(shù)據(jù)區(qū)域隔行填充顏色，選項卡區(qū)域出現(xiàn)“表格工具”的“設(shè)計”選項卡，當(dāng)單擊空白單元格時，此選項卡隱藏。實例53動態(tài)更新數(shù)據(jù)透視表結(jié)果步驟3：創(chuàng)建超級表“表1”后，“表1”會隨著數(shù)據(jù)源的變化自動調(diào)整表格范圍，即為數(shù)據(jù)透視表創(chuàng)建動態(tài)數(shù)據(jù)源。如果數(shù)據(jù)透視表的數(shù)據(jù)源發(fā)生變化，更改數(shù)據(jù)源為“表1”，如左圖所示，數(shù)據(jù)透視表即可隨著數(shù)據(jù)源范圍的變化而同步更新。步驟4：如果要取消每個列標(biāo)題名的右側(cè)的篩選符號，單擊數(shù)據(jù)源中的任意一個單元格，打開“數(shù)據(jù)”選項卡，單擊“篩選”按鈕，如右圖所示。實例53動態(tài)更新數(shù)據(jù)透視表結(jié)果若要取消超級表，將其轉(zhuǎn)換為普通區(qū)域，則單擊“設(shè)計”選項卡中的“轉(zhuǎn)換為區(qū)域”按鈕，如圖所示。超級表轉(zhuǎn)為普通區(qū)域后，數(shù)據(jù)透視表失去隨數(shù)據(jù)源的變化而自動更新結(jié)果的功能。實例54匯總表快速轉(zhuǎn)換為明細(xì)表數(shù)據(jù)透視表通常展示數(shù)據(jù)匯總結(jié)果，這些匯總結(jié)果是由多項數(shù)據(jù)匯總得到的。根據(jù)用戶需求可以將匯總結(jié)果顯示為構(gòu)成這個結(jié)果的明細(xì)數(shù)據(jù)，下面通過實例介紹將匯總表轉(zhuǎn)換為明細(xì)表的方法。在圖所示的數(shù)據(jù)透視表中，要求顯示構(gòu)成這個數(shù)字的具體銷售明細(xì)記錄。在本例中要顯示的明細(xì)記錄同時滿足以下3個條件：①分部=北京總公司②銷售渠道=實體店③商品類別=計算機(jī)實例54匯總表快速轉(zhuǎn)換為明細(xì)表步驟1：雙擊要顯示明細(xì)的數(shù)據(jù)954727.05所在的單元格，此時，Excel自動創(chuàng)建一個新的工作表Sheet1，用來顯示匯總數(shù)據(jù)954727.05對應(yīng)的明細(xì)數(shù)據(jù)，如圖所示。步驟2：顯示明細(xì)后可將該工作表刪除，刪除該工作表對數(shù)據(jù)透視表不會產(chǎn)生任何影響，使用同樣的方法可以顯示數(shù)值區(qū)域任意數(shù)值的明細(xì)數(shù)據(jù)。實例54匯總表快速轉(zhuǎn)換為明細(xì)表在數(shù)據(jù)透視表中顯示明細(xì)數(shù)據(jù)功能默認(rèn)是打開的，此時雙擊數(shù)值所在的單元格即可顯示構(gòu)成該數(shù)值的明細(xì)數(shù)據(jù)。如果不希望他人隨意查看明細(xì)數(shù)據(jù)，可將該功能關(guān)閉，關(guān)閉方法如下：步驟1：在數(shù)據(jù)透視表的任意一個單元格(例如C4單元格)上右鍵單擊，彈出的快捷菜單中單擊“數(shù)據(jù)透視表選項”。步驟2：彈出“數(shù)據(jù)透視表選項”對話框，在“數(shù)據(jù)”選項卡中取消選中“啟用顯示明細(xì)數(shù)據(jù)”復(fù)選框，如圖所示，單擊“確定”按鈕，即可在數(shù)據(jù)透視表中關(guān)閉顯示明細(xì)數(shù)據(jù)功能。關(guān)閉后在數(shù)據(jù)透視表中雙擊數(shù)據(jù)則會彈出錯誤提示。實例55將總表拆分為多表并同步更新利用數(shù)據(jù)透視表的報表篩選功能可以將總表按照條件批量拆分為多張分表并放置在不同的工作表中，當(dāng)總表更新時，所有的分表同步更新。下面結(jié)合實例介紹總表拆分為多表的方法。某商貿(mào)公司按商品類別和分部分類匯總得到的數(shù)據(jù)透視表如圖所示，要求將該總表按商品類別拆分為6張分表，并與總表保持同步更新。操作步驟如下：實例55將總表拆分為多表并同步更新步驟1：將拆分條件字段(商品類別)拖動到數(shù)據(jù)透視表的“篩選器”區(qū)域，如左圖所示。步驟2：單擊數(shù)據(jù)透視表中的任意一個單元格(例如B4單元格)，打開“數(shù)據(jù)透視表工具”中的“分析”選項卡，進(jìn)行如右圖所示的設(shè)置。實例55將總表拆分為多表并同步更新步驟3：數(shù)據(jù)透視表按照選定的篩選項(商品類別)自動批量生成6張工作表，如圖所示，分別放置冰箱、電視、計算機(jī)、空調(diào)、熱水器、洗衣機(jī)的分表數(shù)據(jù)。實例55將總表拆分為多表并同步更新6張分表中的數(shù)據(jù)來源于數(shù)據(jù)透視表的總表，因此，當(dāng)總表數(shù)據(jù)變化時，所有分表中的數(shù)據(jù)跟隨總表的變化同步更新。當(dāng)拆分條件發(fā)生變化時，分表的字段結(jié)構(gòu)和報表布局隨之發(fā)生變化。例如，圖是按照銷售渠道和商品類別分類匯總的總表，現(xiàn)要求以分部為條件將總表拆分為每個分部一張工作表。實例55將總表拆分為多表并同步更新6張分表中的數(shù)據(jù)來源于數(shù)據(jù)透視表的總表，因此，當(dāng)總表數(shù)據(jù)變化時，所有分表中的數(shù)據(jù)跟隨總表的變化同步更新。當(dāng)拆分條件發(fā)生變化時，分表的字段結(jié)構(gòu)和報表布局隨之發(fā)生變化。如圖所示是按照銷售渠道和商品類別分類匯總的總表，現(xiàn)要求以分部為條件將總表拆分為每個分部一張工作表。實例55將總表拆分為多表并同步更新將拆分條件(分店)字段拖動到數(shù)據(jù)透視表“篩選器”區(qū)域，在“分析”選項卡中進(jìn)行如左圖所示的設(shè)置，即可以分部為條件將總表拆分為每個分部一張工作表，如右圖所示，每個分表的字段結(jié)構(gòu)和報表布局與總表相同。實例56添加切片器將數(shù)據(jù)動態(tài)更新數(shù)據(jù)透視表中的切片器是一個強(qiáng)大的篩選工具，利用切片器可以將數(shù)據(jù)按照查詢條件動態(tài)更新，切片器不能在普通表格中使用，只能在智能表格或數(shù)據(jù)透視表中使用。在圖所示的數(shù)據(jù)透視表中，根據(jù)指定的銷售日期快速查詢該銷售日期所在分部的報表結(jié)果，操作步驟如下：實例56添加切片器將數(shù)據(jù)動態(tài)更新步驟1：單擊數(shù)據(jù)透視表中的任意一個單元格(如A3單元格)，打開“分析”選項卡，單擊“插入切片器”按鈕，彈出的對話框中選中“銷售日期”復(fù)選框，單擊“確定”按鈕，插入“銷售日期”切片器。步驟2：若要查看銷售日期“2022年1月10日”的銷售數(shù)據(jù)，在切片器中單擊“2022年1月10日”，數(shù)據(jù)透視表中的數(shù)據(jù)同步動態(tài)更新，如圖所示。實例56添加切片器將數(shù)據(jù)動態(tài)更新步驟3：如果要同時查看多個銷售日期的銷售數(shù)據(jù)，在切片器中按住鼠標(biāo)左鍵進(jìn)行拖動選擇連續(xù)的多個銷售日期，或者按住Ctrl鍵選擇不連續(xù)的多個銷售日期。步驟4：若要