數據透視表 是 Excel 中最強大的工具之一。 它們使您只需單擊幾下鼠標即可分析和總結大量數據的各種摘要。 在本文中，我們將熟悉數據透視表，了解它們是什麼，學習如何創建和自定義它們。
本文是使用 Excel 2010 編寫的。這些年來數據透視表的概念沒有太大變化，但在每個新版本的 Excel 中創建它們的方式略有不同。 如果您的 Excel 版本不是 2010，請準備好本文中的屏幕截圖與您在屏幕上看到的不同。
一點點歷史回顧
在電子表格軟件的早期，Lotus 1-2-3 規則球。 它的統治地位如此完整，以至於微軟開發自己的軟件 (Excel) 作為 Lotus 的替代品的努力似乎是在浪費時間。 現在快進到 2010 年！ Excel 在電子表格中的主導地位超過了 Lotus 代碼在其歷史上的表現，而且仍然使用 Lotus 的人數接近於零。 這怎麼可能發生？ 發生如此戲劇性的轉折的原因是什麼？
分析師確定了兩個主要因素：
- 首先，Lotus 認為這個名為 Windows 的新奇 GUI 平台只是曇花一現，不會持續太久。 他們拒絕構建 Lotus 1-2-3 的 Windows 版本（但只持續了幾年），預測他們的軟件的 DOS 版本將是所有消費者所需要的。 微軟自然而然地專門為 Windows 開發了 Excel。
- 其次，微軟在 Excel 中引入了一個名為 PivotTables 的工具，而 Lotus 1-2-3 中沒有該工具。 Excel 獨有的數據透視表被證明非常有用，以至於人們傾向於堅持使用新的 Excel 軟件套件，而不是繼續使用沒有它們的 Lotus 1-2-3。
數據透視表與普遍低估 Windows 的成功一起，為 Lotus 1-2-3 演奏了死亡行軍，並迎來了 Microsoft Excel 的成功。
什麼是數據透視表？
那麼，表徵什麼是數據透視表的最佳方式是什麼？
簡單來說，數據透視表是一些數據的摘要，創建它是為了便於分析這些數據。 與手動創建的總計不同，Excel 數據透視表是交互式的。 創建後，如果它們沒有提供您希望的圖片，您可以輕鬆修改它們。 只需單擊幾下鼠標，即可翻轉總計，使列標題變為行標題，反之亦然。 您可以使用數據透視表做很多事情。 與其試圖用語言描述數據透視表的所有功能，不如在實踐中更容易地展示它……
您使用數據透視表分析的數據不能是隨機的。 它應該是原始的原始數據，例如某種列表。 例如，它可以是公司在過去六個月中的銷售清單。
看下圖所示的數據：
請注意，這不是原始數據，因為它已經進行了總結。 在單元格 B3 中，我們看到 30000 美元，這可能是 James Cook 在 30000 月份所做的總結果。 那麼原始數據在哪裡呢？ XNUMX美元的數字從何而來？ 得出此月度總額的原始銷售清單在哪裡？ 很明顯，有人在組織和整理過去六個月的所有銷售數據並將其轉化為我們看到的總計表方面做得非常出色。 你覺得花了多長時間？ 小時？ 十點鐘？
事實是上面的表格不是數據透視表。 它是根據存儲在其他地方的原始數據手工製作的，至少需要幾個小時來處理。 只需幾秒鐘即可使用數據透視表創建這樣一個匯總表。 讓我們弄清楚如何……
如果我們回到原來的銷售清單，它看起來像這樣：
您可能會感到驚訝，在數據透視表的幫助下，我們可以在幾秒鐘內從這個交易列表中創建 Excel 中的月度銷售報告，我們在上面進行了分析。 是的，我們可以做到這一點，甚至更多！
如何創建數據透視表？
首先，確保您在 Excel 工作表中有一些源數據。 金融交易清單是最典型的發生。 事實上，它可以是任何內容的列表：員工聯繫方式、CD 收藏或貴公司的油耗數據。
因此，我們啟動 Excel……並加載這樣的列表……
在 Excel 中打開此列表後，我們可以開始創建數據透視表。
從此列表中選擇任何單元格：
然後在選項卡上 插入 （插入）選擇命令 數據透視表 （數據透視表）：
將出現一個對話框 創建數據透視表 （創建數據透視表）給您兩個問題：
- 使用什麼數據來創建新的數據透視表？
- 在哪裡放置數據透視表？
由於在上一步中我們已經選擇了一個列表單元格，因此將自動選擇整個列表來創建數據透視表。 請注意，我們可以選擇不同的範圍、不同的表，甚至可以選擇一些外部數據源，例如 Access 或 MS-SQL 數據庫表。 此外，我們需要選擇新數據透視表的放置位置：新工作表或現有工作表之一。 在此示例中，我們將選擇選項 - 新工作表 （到新表）：
Excel 將創建一個新工作表並在其上放置一個空數據透視表：
一旦我們單擊數據透視表中的任何單元格，就會出現另一個對話框： 數據透視表字段列表 （數據透視表字段）。
對話框頂部的字段列表是原始列表中所有標題的列表。 屏幕底部的四個空白區域允許您告訴數據透視表您希望如何匯總數據。 只要這些區域是空的，表格中也沒有任何內容。 我們所要做的就是將標題從頂部區域拖到下面的空白區域。 同時，根據我們的說明自動生成數據透視表。 如果我們犯了錯誤，我們可以從底部區域刪除標題或拖動其他標題來替換它們。
面積 價值觀 （含義）可能是四個中最重要的。 該區域中放置的標題決定了將匯總哪些數據（總和、平均值、最大值、最小值等）。這些幾乎總是數值。 標題下的數據是該區域的一個很好的候選者 金額 我們原始表的（成本）。 將此標題拖到該區域 價值觀 （價值觀）：
請注意標題 金額 現在標有復選標記，並且在該區域 價值觀 （值）出現了一個條目 金額總和 （金額字段Amount），表示該列 金額 總結。
如果我們查看數據透視表本身，我們將看到列中所有值的總和 金額 原表。
所以，我們的第一個數據透視表就創建好了！ 方便，但不是特別令人印象深刻。 我們可能希望獲得比我們目前擁有的更多的數據信息。
讓我們轉向原始數據並嘗試確定可用於拆分此總和的一列或多列。 例如，我們可以形成我們的數據透視表，以便單獨計算每個賣家的總銷售額。 那些。 行將添加到我們的數據透視表中，其中包含公司中每個銷售人員的姓名及其總銷售額。 要達到這個結果，只需拖動標題 銷售人員 （銷售代表）到該地區 行標籤 （字符串）：
變得更有趣了！ 我們的數據透視表開始成形……
看到好處了嗎？ 只需單擊幾下，我們就創建了一個手動創建需要很長時間的表。
我們還能做什麼？ 好吧，從某種意義上說，我們的數據透視表已經準備好了。 我們已經創建了原始數據的有用摘要。 重要信息已收到！ 在本文的其餘部分，我們將介紹一些創建更複雜數據透視表的方法，並學習如何自定義它們。
數據透視表設置
首先，我們可以創建一個二維數據透視表。 讓我們使用列標題來執行此操作 付款方式 （付款方式）。 只需拖動標題 付款方式 到該地區 列標籤 （列）：
我們得到結果：
看起來很酷！
現在讓我們製作一個三維表格。 這樣的桌子會是什麼樣子？ 讓我們來看看…
拖動標題 禮包套裝 （複雜）到該地區 報告過濾器 （過濾器）：
注意他在哪裡……
這使我們有機會根據“支付了哪個假日綜合體”來過濾報告。 例如，我們可以查看所有綜合大樓的賣家和付款方式的細分，或者只需單擊幾下鼠標，更改數據透視表的視圖並僅針對訂購綜合大樓的人顯示相同的細分 追日者.
所以，如果你理解正確的話，那麼我們的數據透視表就可以稱為三維。 讓我們繼續設置……
如果突然發現數據透視表中應該只顯示支票和信用卡支付（即無現金支付），那麼我們可以關閉標題的顯示 現金 （現金）。 為此，旁邊 列標籤 單擊向下箭頭並取消選中下拉菜單中的框 現金:
讓我們看看我們的數據透視表現在是什麼樣子。 如您所見，該列 現金 從她身邊消失了。
在 Excel 中格式化數據透視表
數據透視表顯然是一個非常強大的工具，但到目前為止，結果看起來有點平淡乏味。 例如，我們加起來的數字看起來不像美元金額——它們只是數字。 讓我們解決這個問題。
在這種情況下，很容易做你習慣的事情，只需選擇整個表格（或整個工作表）並使用工具欄上的標準數字格式按鈕來設置所需的格式。 這種方法的問題在於，如果您將來更改數據透視表的結構（發生這種情況的可能性為 99%），格式將會丟失。 我們需要的是一種使其（幾乎）永久化的方法。
首先，讓我們找到入口 金額總和 in 價值觀 （值）並單擊它。 在出現的菜單中，選擇項目 值字段設置 （值字段選項）：
將出現一個對話框 值字段設置 （值字段選項）。
按 數字格式 （數字格式），將打開一個對話框。 單元格格式 （單元格格式）：
從列表中 類別 （數字格式）選擇 會計 （財務）並將小數位數設置為零。 現在按幾次 OK回到我們的數據透視表。
如您所見，這些數字被格式化為美元金額。
當我們處理格式時，讓我們為整個數據透視表設置格式。 有幾種方法可以做到這一點。 我們使用更簡單的一種……
點擊 數據透視表工具：設計 （使用數據透視表：構造函數）：
接下來，通過單擊該部分右下角的箭頭展開菜單 數據透視表樣式 （數據透視表樣式）查看廣泛的內聯樣式集合：
選擇任何合適的樣式並查看數據透視表中的結果：
Excel 中的其他數據透視表設置
有時您需要按日期過濾數據。 例如，在我們的交易列表中有很多很多的日期。 Excel 提供了一種按日、月、年等對數據進行分組的工具。 讓我們看看它是如何完成的。
首先刪除條目。 付款方式 來自該地區 列標籤 （列）。 為此，將其拖回標題列表，並在其位置移動標題 預訂日期 （預訂日期）：
如您所見，這暫時使我們的數據透視表無用。 Excel 為每個進行交易的日期創建了一個單獨的列。 結果，我們得到了一張非常寬的桌子！
要解決此問題，請右鍵單擊任何日期並從上下文菜單中選擇 本集團 （團體）：
將出現分組對話框。 我們選擇 個月 （月）並單擊 OK:
瞧！ 這張表更有用：
順便說一句，該表與文章開頭顯示的表幾乎相同，其中銷售總額是手動編制的。
還有一個非常重要的點你需要知道！ 您可以創建的不是一個，而是多個級別的行（或列）標題：
......它看起來像這樣......
列標題（甚至過濾器）也可以這樣做。
讓我們回到表格的原始形式，看看如何顯示平均值而不是總和。
要開始，請單擊 金額總和 並從出現的菜單中選擇 值字段設置 （值字段選項）：
列表 匯總值字段 （操作）在對話框中 值字段設置 （值字段選項）選擇 中等 （平均）：
同時，當我們在這裡的時候，讓我們改變 自定義名稱 （自定義名稱）與 平均金額 （金額字段金額）到更短的內容。 在此字段中輸入類似 平均:
媒體推薦 OK 看看會發生什麼。 請注意，所有值都已從總計變為平均值，並且表格標題（在左上角單元格中）已更改為 平均:
如果需要，您可以立即將金額、平均值和數量（銷售額）放在一個數據透視表中。
以下是有關如何執行此操作的分步指南，從一個空的數據透視表開始：
- 拖動標題 銷售人員 （銷售代表）到該地區 列標籤 （列）。
- 拖動標題三下 金額 （成本）到地區 價值觀 （值）。
- 對於第一個字段 金額 將標題更改為 Total （金額），該字段的數字格式為 會計 （金融）。 小數位數為零。
- 第二場 金額 名稱 阿韋拉格e、為其設置操作 中等 (Average) 並且該字段中的數字格式也更改為 會計 （財務）小數點後零位。
- 對於第三場 金額 設置標題 計數 和他的手術—— 計數 （數量）
- 在 列標籤 （列）字段自動創建 Σ 值 (Σ Values) – 將其拖到該區域 行標籤 （線）
這是我們最終的結果：
總金額、平均值和銷售數量 - 都在一個數據透視表中！
結論
Microsoft Excel 中的數據透視表包含許多功能和設置。 在這麼小的一篇文章中，他們甚至沒有接近涵蓋所有內容。 完全描述數據透視表的所有可能性需要一本小書或一個大型網站。 大膽而好奇的讀者可以繼續探索數據透視表。 為此，只需右鍵單擊數據透視表的幾乎任何元素，然後查看打開了哪些功能和設置。 在功能區上，您會找到兩個選項卡： 數據透視表工具：選項 （分析）和 設計 （構造函數）。 不要害怕犯錯，您可以隨時刪除數據透視表並重新開始。 您有一個長期使用 DOS 和 Lotus 1-2-3 的用戶從未有過的機會。