簡單的辦公室系列高級教室-OfficeExcel透視和數(shù)據(jù)透視

(a)作業(yè)基礎(chǔ)

一、透視表和透視概覽

您可以使用透視表來匯總、分析、導航和提供摘要數(shù)據(jù)。通過數(shù)據(jù)透視報表，您可以在透視表中顯示摘要數(shù)據(jù)，并輕松查看比較、模式和趨勢。透視表和數(shù)據(jù)透視報表使您能夠就企業(yè)的關(guān)鍵數(shù)據(jù)作出可靠的決策。

1、概述。

透視表是一種快速匯總大量數(shù)據(jù)的交互式方法。您可以使用透視表深入分析數(shù)值數(shù)據(jù)，并回答意外的數(shù)據(jù)問題。透視表是專門為以下目的設(shè)計的：

以各種用戶友好的方式查詢大量數(shù)據(jù)。分類和匯總數(shù)值數(shù)據(jù)，按分類和子分類匯總數(shù)據(jù)，并生成自定義計算和公式。展開或折疊要集中于結(jié)果的數(shù)據(jù)層，以查看感興趣區(qū)域匯總數(shù)據(jù)的詳細信息。將行移動到列或?qū)⒘幸苿拥叫?或透視)中，以查看源數(shù)據(jù)的各種摘要。通過過濾、排序、分組和有條件地格式化最有用和最感興趣的數(shù)據(jù)子集，可以專注于所需的信息。提供簡潔、吸引人、有注釋的在線報告或打印的報告。2、透視表的處理方法。

定義數(shù)據(jù)源、對“透視表字段列表”中的字段進行排序并選擇初始布局以創(chuàng)建初始透視表后，可以執(zhí)行以下任務(wù)來處理透視表：

通過展開和折疊數(shù)據(jù)以及顯示與值相關(guān)的基本詳細信息來導航數(shù)據(jù)。對字段和項目進行排序、過濾和分組。更改摘要函數(shù)并添加自定義計算和公式。執(zhí)行透視表格式(壓縮、大綱或表)更改，以更改表單布局和字段對齊方式。添加、重新排列和刪除字段。更改字段或項目的順序。打開或關(guān)閉列和行字段標題，或顯示或隱藏空行，以更改列、行和分類摘要的布局。在相應行的上方或下方顯示分類摘要。刷新時調(diào)整列寬。將列字段移動到行區(qū)域，或?qū)⑿凶侄我苿拥搅袇^(qū)域。合并外部行和列項目的單元格，或取消合并單元格。通過更改錯誤和空單元格的顯示方式，更改空單元格和錯誤的顯示方式。變更沒有資料的項目和標示的顯示方式。顯示或隱藏空行。對單元格和區(qū)域執(zhí)行手動和條件格式設(shè)置，以更改格式設(shè)置。更改整個透視表的格式樣式。更改字段的數(shù)字格式。包含OLAP服務(wù)器格式。3、透視表和數(shù)據(jù)透視報表比較

數(shù)據(jù)透視表報表提供透視表中數(shù)據(jù)的圖形表示。具有這種圖形表示的透視表稱為連接的透視表。要更改數(shù)據(jù)透視表報表中顯示的布局和數(shù)據(jù)，請更改鏈接透視表的布局。

關(guān)鍵字說明：

透視表：交互式交叉表Excel報表，用于匯總和分析來自各種源的數(shù)據(jù)(如數(shù)據(jù)庫記錄)，包括Excel中的外部數(shù)據(jù)。

連接的透視表：提供數(shù)據(jù)透視的源數(shù)據(jù)的透視表。透視表是在創(chuàng)建新數(shù)據(jù)透視時自動創(chuàng)建的。如果更改一個報告的布局，其他報告也會隨之更改。

與標準圖表一樣，數(shù)據(jù)透視報表有數(shù)據(jù)系列、類別、數(shù)據(jù)標記和軸。還可以更改圖表類型和其他選項，如圖表標題、圖例位置、數(shù)據(jù)標簽和圖表位置。

關(guān)鍵字說明：

數(shù)據(jù)系列：從數(shù)據(jù)表的行或列派生的圖表中繪制的相關(guān)數(shù)據(jù)點。圖表中的每個數(shù)據(jù)系列都有自己的顏色或模式?？梢栽趫D表中繪制一個或多個數(shù)據(jù)系列。餅圖只有一個數(shù)據(jù)系列。

數(shù)據(jù)標記：圖表中的條形、面積、點、扇面或其他符號，表示從數(shù)據(jù)表單元格派生的單個數(shù)據(jù)點或值。圖表中的相關(guān)數(shù)據(jù)標記構(gòu)成了數(shù)據(jù)系列。

軸：定義用作測量參考框架的圖表繪圖區(qū)域的線。y軸通常是垂直軸，包含數(shù)據(jù)。x軸通常是水平軸，包含分類。

圖表標題：圖表標題是說明性文本，可以自動與軸對齊，也可以放置在圖表頂部的中心。

圖例：圖例是標識分配給圖表中數(shù)據(jù)系列或分類的模式或顏色的框。

數(shù)據(jù)標簽：為數(shù)據(jù)標記提供附加信息的標簽，表示從數(shù)據(jù)表單元格派生的單個數(shù)據(jù)點或值。

第一次創(chuàng)建透視表時，可以自動生成數(shù)據(jù)透視報表，也可以基于現(xiàn)有透視表生成數(shù)據(jù)透視報表。

4、透視和數(shù)據(jù)透視比較

基于透視表生成數(shù)據(jù)透視表報表時，數(shù)據(jù)透視表報表的布局(即數(shù)據(jù)透視表報表字段的位置)最初由透視表的布局決定。如果首先創(chuàng)建了數(shù)據(jù)透視表報表，則可以通過將字段從“透視表字段列表”拖動到圖表工作表的特定區(qū)域來確定圖表布局。

關(guān)鍵字說明：

圖表工作表：工作簿僅包含圖表的工作表。如果要獨立于工作表數(shù)據(jù)或透視表查看圖表或數(shù)據(jù)透視，圖表工作表非常有用。

5、數(shù)據(jù)透視報告和標準圖表之間的差異。

如果熟悉標準圖表，可以找到數(shù)字

據(jù)透視圖中的大多數(shù)操作和標準圖表中的一樣。但是二者之間也存在以下差別：

• 交互 在標準圖表中，應為要查看的每個數(shù)據(jù)視圖創(chuàng)建一個圖表，但不能與它們進行交互。而在數(shù)據(jù)透視圖報表中，只要創(chuàng)建一個圖表即可通過更改報表布局或顯示的明細數(shù)據(jù)來以不同的方式查看數(shù)據(jù)。即您可以與數(shù)據(jù)進行交互。
• 圖表類型 您可以將數(shù)據(jù)透視圖報表更改為除 XY 散點圖、股價圖或氣泡圖之外的任何其他圖表類型。
• 源數(shù)據(jù) 標準圖表可直接鏈接到工作表單元格中。數(shù)據(jù)透視圖可以基于相關(guān)聯(lián)的數(shù)據(jù)透視表 中的幾種不同數(shù)據(jù)類型。

關(guān)鍵詞解釋：

相關(guān)聯(lián)的數(shù)據(jù)透視表：為數(shù)據(jù)透視圖提供源數(shù)據(jù)的數(shù)據(jù)透視表。在新建數(shù)據(jù)透視圖時，將自動創(chuàng)建數(shù)據(jù)透視表。如果更改其中一個報表的布局，另外一個報表也隨之更改。

• 格式 刷新 數(shù)據(jù)透視圖報表時，會保留大多數(shù)格式（包括元素、布局和樣式）。但是，不保留趨勢線 、數(shù)據(jù)標簽 、誤差線以及對數(shù)據(jù)集進行的其他更改。標準圖表只要應用了這些格式，就不會丟失它們。

關(guān)鍵詞解釋：

刷新：更新數(shù)據(jù)透視表或數(shù)據(jù)透視圖中的內(nèi)容以反映基本源數(shù)據(jù)的變化。如果報表基于外部數(shù)據(jù)，則刷新將運行基本查詢以檢索新的或更改過的數(shù)據(jù)。

趨勢線：趨勢線以圖形的方式表示數(shù)據(jù)系列的趨勢，例如，向上傾斜的線表示幾個月中增加的銷售額。趨勢線用于問題預測研究，又稱為回歸分析。

數(shù)據(jù)標簽：為數(shù)據(jù)標記提供附加信息的標簽，數(shù)據(jù)標簽代表源于數(shù)據(jù)表單元格的單個數(shù)據(jù)點或值。

誤差線：通常用在統(tǒng)計或科學記數(shù)法數(shù)據(jù)中，誤差線顯示相對序列中的每個數(shù)據(jù)標記的潛在誤差或不確定度。

6、處理源數(shù)據(jù)

在創(chuàng)建數(shù)據(jù)透視表 或數(shù)據(jù)透視圖報表 時，可使用多種不同的源數(shù)據(jù) 類型。

關(guān)鍵詞解釋：

數(shù)據(jù)透視表：一種交互的、交叉制表的 Excel 報表，用于對多種來源（包括 Excel 的外部數(shù)據(jù)）的數(shù)據(jù)（如數(shù)據(jù)庫記錄）進行匯總和分析。

數(shù)據(jù)透視圖：提供交互式數(shù)據(jù)分析的圖表，與數(shù)據(jù)透視表類似?？梢愿臄?shù)據(jù)的視圖，查看不同級別的明細數(shù)據(jù)，或通過拖動字段和顯示或隱藏字段中的項來重新組織圖表的布局。

源數(shù)據(jù)：用于創(chuàng)建數(shù)據(jù)透視表或數(shù)據(jù)透視圖的數(shù)據(jù)清單或表。源數(shù)據(jù)可以來自 Excel 數(shù)據(jù)清單或區(qū)域、外部數(shù)據(jù)庫或多維數(shù)據(jù)集，或者另一張數(shù)據(jù)透視表。

6-1、使用工作表數(shù)據(jù)

您可以將 Microsoft Office Excel 工作表中的數(shù)據(jù)作為報表的數(shù)據(jù)來源。該數(shù)據(jù)應采用列表 格式，其列標簽應位于第一行。后續(xù)行中的每個單元格都應包含與其列標題相對應的數(shù)據(jù)。目標數(shù)據(jù)中不得出現(xiàn)任何空行或空列。Excel 會將列標簽用作報表中的字段名稱。

關(guān)鍵詞解釋：

列表：包含相關(guān)數(shù)據(jù)的一系列行，或使用“創(chuàng)建列表”命令作為數(shù)據(jù)表指定給函數(shù)的一系列行。

字段：在數(shù)據(jù)透視表或數(shù)據(jù)透視圖中，來源于源數(shù)據(jù)中字段的一類數(shù)據(jù)。數(shù)據(jù)透視表具有行字段、列字段、頁字段和數(shù)據(jù)字段。數(shù)據(jù)透視圖具有系列字段、分類字段、頁字段和數(shù)據(jù)字段。

• 使用命名區(qū)域 若要使報表的更新更易于進行，請為源區(qū)域命名一個名稱 ，并在創(chuàng)建報表時使用該名稱。如果命名區(qū)域在擴展后包含了更多數(shù)據(jù)，則可以刷新 報表來包含新的數(shù)據(jù)。

關(guān)鍵詞解釋：

名稱：代表單元格、單元格區(qū)域、公式或常量值的單詞或字符串。名稱更易于理解，例如，“產(chǎn)品”可以引用難于理解的區(qū)域“Sales!C20:C30”。

刷新：更新數(shù)據(jù)透視表或數(shù)據(jù)透視圖中的內(nèi)容以反映基本源數(shù)據(jù)的變化。如果報表基于外部數(shù)據(jù)，則刷新將運行基本查詢以檢索新的或更改過的數(shù)據(jù)。

• Excel 表格 Excel 表格已經(jīng)采用列表格式，因而是數(shù)據(jù)透視表不錯的候選源數(shù)據(jù)。當刷新數(shù)據(jù)透視表時，Excel 表格中新增和更新的數(shù)據(jù)會自動包含在刷新操作中。
• 包括匯總 Excel會自動在數(shù)據(jù)透視表中創(chuàng)建分類匯總和總計。如果源數(shù)據(jù)包含用“分類匯總”命令（位于“數(shù)據(jù)”選項卡上的“大綱”組中）創(chuàng)建的自動分類匯總和總計，則應在創(chuàng)建報表前再用該命令將分類匯總和總計刪除。

6-2、使用外部數(shù)據(jù)源

您可以從數(shù)據(jù)庫 、OLAP 多維數(shù)據(jù)集或文本文件等位于 Excel 外部的源中檢索數(shù)據(jù)。例如，對于要匯總和分析的銷售記錄，可以為它們維護一個數(shù)據(jù)庫。

關(guān)鍵詞解釋：

數(shù)據(jù)庫：與特定主題或用途相關(guān)的數(shù)據(jù)的集合。在數(shù)據(jù)庫內(nèi)，關(guān)于特定實體的信息（如雇員或訂單）分類歸納到表、記錄和字段中。

• Office 數(shù)據(jù)連接文件 如果使用 Office 數(shù)據(jù)連接 (ODC) 文件 (.odc) 為報表檢索外部數(shù)據(jù) ，則可以直接將該數(shù)據(jù)輸入到數(shù)據(jù)透視表中。我們建議使用 ODC 文件為報表檢索外部數(shù)據(jù)。

關(guān)鍵詞解釋：

外部數(shù)據(jù)：存儲在 Excel 之外的數(shù)據(jù)。例如，在 Access、dBASE、SQL Server 或 Web 服務(wù)器上創(chuàng)建的數(shù)據(jù)庫。

• OLAP 源數(shù)據(jù) 在從OLAP數(shù)據(jù)庫或多維數(shù)據(jù)集文件中檢索源數(shù)據(jù)時，數(shù)據(jù)只能以轉(zhuǎn)換為工作表功能的數(shù)據(jù)透視表或數(shù)據(jù)透視圖報表的形式返回 Excel。

關(guān)鍵詞解釋：

OLAP：為查詢和報表（而不是處理事務(wù)）而進行了優(yōu)化的數(shù)據(jù)庫技術(shù)。OLAP 數(shù)據(jù)是按分級結(jié)構(gòu)組織的，它存儲在多維數(shù)據(jù)集而不是表中。

多維數(shù)據(jù)集：一種 OLAP 數(shù)據(jù)結(jié)構(gòu)。多維數(shù)據(jù)集包含維度，如“國家/地區(qū)/?。ɑ蚴?自治區(qū)）/市（或縣）”，還包括數(shù)據(jù)字段，如“銷售額”。維度將各種類型的數(shù)據(jù)組織到帶有明細數(shù)據(jù)級別的分層結(jié)構(gòu)中，而數(shù)據(jù)字段度量數(shù)量。

• 非 OLAP 源數(shù)據(jù) 這是數(shù)據(jù)透視表或數(shù)據(jù)透視圖報表使用的基礎(chǔ)數(shù)據(jù)，該數(shù)據(jù)來自 OLAP 數(shù)據(jù)庫之外的源。例如，來自關(guān)系數(shù)據(jù)庫或文本文件中的數(shù)據(jù)。

6-3、使用其他數(shù)據(jù)透視表

• 數(shù)據(jù)透視表緩存 每次在新建數(shù)據(jù)透視表或數(shù)據(jù)透視圖時，Excel 均將報表數(shù)據(jù)的副本存儲在內(nèi)存中，并將其保存為工作簿文件的一部分。這樣每張新的報表均需要額外的內(nèi)存和磁盤空間。但是，如果將現(xiàn)有數(shù)據(jù)透視表作為同一個工作簿中的新報表的源數(shù)據(jù)，則兩張報表就可以共享同一個數(shù)據(jù)副本。因為可以重新使用存儲區(qū)，所以就會縮小工作簿文件，減少內(nèi)存中的數(shù)據(jù)。
• 位置要求 如果要將某個數(shù)據(jù)透視表用作其他報表的源數(shù)據(jù)，則兩個報表必須位于同一工作簿中。如果源數(shù)據(jù)透視表位于另一工作簿中，則需要將源報表復制到要新建報表的工作簿位置。不同工作簿中的數(shù)據(jù)透視表和數(shù)據(jù)透視圖是獨立的，它們在內(nèi)存和工作簿文件中都有各自的數(shù)據(jù)副本。
• 更改會同時影響兩個報表 在刷新新報表中的數(shù)據(jù)時，Excel 也會更新源報表中的數(shù)據(jù)，反之亦然。如果對某個報表中的項進行分組或取消分組，那么也將同時影響兩個報表。如果在某個報表中創(chuàng)建了計算字段 或計算項 ，則也將同時影響兩個報表。

關(guān)鍵詞解釋：

刷新：更新數(shù)據(jù)透視表或數(shù)據(jù)透視圖中的內(nèi)容以反映基本源數(shù)據(jù)的變化。如果報表基于外部數(shù)據(jù)，則刷新將運行基本查詢以檢索新的或更改過的數(shù)據(jù)。

計算字段：數(shù)據(jù)透視表或數(shù)據(jù)透視圖中的字段，該字段使用用戶創(chuàng)建的公式。計算字段可使用數(shù)據(jù)透視表或數(shù)據(jù)透視圖中其他字段中的內(nèi)容執(zhí)行計算。

計算項：數(shù)據(jù)透視表字段或數(shù)據(jù)透視圖字段中的項，該項使用用戶創(chuàng)建的公式。計算項使用數(shù)據(jù)透視表或數(shù)據(jù)透視圖中相同字段的其他項的內(nèi)容進行計算。

• 數(shù)據(jù)透視圖報表 您可以基于其他數(shù)據(jù)透視表創(chuàng)建新的數(shù)據(jù)透視表或數(shù)據(jù)透視圖報表，但是不能直接基于其他數(shù)據(jù)透視圖報表創(chuàng)建報表。不過，每當創(chuàng)建數(shù)據(jù)透視圖報表時，Excel 都會基于相同的數(shù)據(jù)創(chuàng)建一個相關(guān)聯(lián)的數(shù)據(jù)透視表 ；因此，您可以基于相關(guān)聯(lián)的報表創(chuàng)建一個新報表。對數(shù)據(jù)透視圖報表所做的更改將影響相關(guān)聯(lián)的數(shù)據(jù)透視表，反之亦然。

關(guān)鍵詞解釋：

相關(guān)聯(lián)的數(shù)據(jù)透視表：為數(shù)據(jù)透視圖提供源數(shù)據(jù)的數(shù)據(jù)透視表。在新建數(shù)據(jù)透視圖時，將自動創(chuàng)建數(shù)據(jù)透視表。如果更改其中一個報表的布局，另外一個報表也隨之更改。

6-4、更改現(xiàn)有報表的源數(shù)據(jù)

• 更改源數(shù)據(jù) 將導致用于分析的數(shù)據(jù)也發(fā)生變化。例如，您可能希望方便地從測試數(shù)據(jù)庫切換到生產(chǎn)數(shù)據(jù)庫?？梢酝ㄟ^刷新報表，使用與原始數(shù)據(jù)連接信息類似的新數(shù)據(jù)來更新數(shù)據(jù)透視表或數(shù)據(jù)透視圖。

關(guān)鍵詞解釋：

源數(shù)據(jù)：用于創(chuàng)建數(shù)據(jù)透視表或數(shù)據(jù)透視圖的數(shù)據(jù)清單或表。源數(shù)據(jù)可以來自 Excel 數(shù)據(jù)清單或區(qū)域、外部數(shù)據(jù)庫或多維數(shù)據(jù)集，或者另一張數(shù)據(jù)透視表。

刷新：更新數(shù)據(jù)透視表或數(shù)據(jù)透視圖中的內(nèi)容以反映基本源數(shù)據(jù)的變化。如果報表基于外部數(shù)據(jù)，則刷新將運行基本查詢以檢索新的或更改過的數(shù)據(jù)。

要包括附加數(shù)據(jù)或其他數(shù)據(jù)，可以重新定義報表的源數(shù)據(jù)。如果這些數(shù)據(jù)與多數(shù)新字段或附加字段有很大差異，那么創(chuàng)建一個新的報表可能會更好。

• 通過刷新顯示新數(shù)據(jù) 刷新報表也會更改可顯示的數(shù)據(jù)。對于基于工作表列表的報表，Excel 會在源區(qū)域或指定的命名 區(qū)域中檢索新的字段。對于基于外部數(shù)據(jù)的報表，Excel 會檢索符合基礎(chǔ)查詢 條件的新數(shù)據(jù)或 OLAP 多維數(shù)據(jù)集 中可用的數(shù)據(jù)。您可以在“字段列表”中查看所有新字段，也可以向報表中添加字段。

關(guān)鍵詞解釋：

名稱：代表單元格、單元格區(qū)域、公式或常量值的單詞或字符串。名稱更易于理解，例如，“產(chǎn)品”可以引用難于理解的區(qū)域“Sales!C20:C30”。

查詢：在 Query 或 Access 中，查詢是一種查找記錄的方法，而這些記錄回答了用戶對數(shù)據(jù)庫中存儲的數(shù)據(jù)提出的特定問題。

多維數(shù)據(jù)集：一種 OLAP 數(shù)據(jù)結(jié)構(gòu)。多維數(shù)據(jù)集包含維度，如“國家/地區(qū)/?。ɑ蚴?自治區(qū)）/市（或縣）”，還包括數(shù)據(jù)字段，如“銷售額”。維度將各種類型的數(shù)據(jù)組織到帶有明細數(shù)據(jù)級別的分層結(jié)構(gòu)中，而數(shù)據(jù)字段度量數(shù)量。

• 更改創(chuàng)建的 OLAP 多維數(shù)據(jù)集 基于 OLAP 數(shù)據(jù)的報表始終可以訪問多維數(shù)據(jù)集中的所有數(shù)據(jù)。如果您創(chuàng)建了包含服務(wù)器多維數(shù)據(jù)集中的數(shù)據(jù)子集的脫機多維數(shù)據(jù)集文件 ，則可用“脫機 OLAP”命令來修改該多維數(shù)據(jù)集文件，以使其包含服務(wù)器中的其他數(shù)據(jù)。

關(guān)鍵詞解釋：

脫機多維數(shù)據(jù)集文件：創(chuàng)建于硬盤或網(wǎng)絡(luò)共享位置上的文件，用于存儲數(shù)據(jù)透視表或數(shù)據(jù)透視圖的 OLAP 源數(shù)據(jù)。脫機多維數(shù)據(jù)集文件允許用戶在斷開與 OLAP 服務(wù)器的連接后繼續(xù)進行操作。

下一節(jié)：《輕松辦公系列進階課堂-OfficeExcel數(shù)據(jù)透視表和數(shù)據(jù)透視圖（二）創(chuàng)建或刪除數(shù)據(jù)透視表或數(shù)據(jù)透視圖》

更多精彩內(nèi)容將在以后的章節(jié)分享給朋友們，請?zhí)砑邮詹兀瑲g迎關(guān)注！