資料透視表是Microsoft Excel最強大的功能之一。它們允許在幾次滑鼠點選中分析和總結大量資料。在本文中，我們將探索資料透視表，瞭解它們是什麼，並學習如何建立和自定義資料透視表。

注意：本文使用Excel 2010（Beta）編寫。資料透視表的概念在過去幾年中幾乎沒有什麼變化，但是在Excel的每次迭代中，建立資料透視表的方法都發生了變化。如果您使用的是不是2010的Excel版本，那麼您可以期待與本文中看到的螢幕不同。

一點歷史

在電子表格程式的早期，Lotus1-2-3統治著公雞。它的優勢是如此的完全，以至於人們認為微軟在開發自己的電子表格軟體（Excel）與Lotus競爭上是浪費時間的。Flash向前邁進2010年，Excel在電子表格市場的主導地位比Lotus以往任何時候都大，而仍在執行Lotus1-2-3的使用者數量接近零。這是怎麼發生的？是什麼導致瞭如此巨大的命運逆轉？

行業分析人士將其歸結為兩個因素：首先，Lotus認為這種被稱為“Windows”的新型GUI平臺只是一時的時尚，永遠不會流行起來。他們拒絕建立lotus1-2-3的Windows版本（至少有幾年的時間），並預測DOS版本的軟體是任何人都需要的。微軟自然是專門為Windows開發Excel的。其次，微軟為Excel開發了Lotus在1-2-3中沒有提供的特性，即資料透視表。Excel獨有的資料透視表功能被認為非常有用，以至於人們願意學習一個全新的軟體包（Excel），而不是堅持使用一個沒有它的程式（1-2-3）。這一特性，連同對Windows成功的錯誤判斷，是lotus1-2-3的喪鐘，也是microsoftexcel成功的開始。

瞭解資料透視表

那麼什麼是資料透視表呢？

簡單地說，資料透視表是一些資料的摘要，建立時可以方便地分析所述資料。但是與手動建立的摘要不同，Excel資料透視表是互動式的。一旦你建立了一個，如果它不能提供你希望的資料的確切見解，你就可以很容易地改變它。在幾次單擊後，可以“旋轉”摘要—以這樣的方式旋轉，列標題成為行標題，反之亦然。還有很多事情可以做。我們將簡單地演示資料透視表的所有特性，而不是試圖描述它們…

使用資料透視表分析的資料不能只是任何資料——它必須是原始資料，以前未處理（未經管理）-通常是某種型別的列表。這方面的一個例子可能是過去六個月公司的銷售交易清單。

檢查以下資料：

請注意，這不是原始資料。事實上，這已經是某種總結了。在B3單元我們可以看到30000美元，這顯然是詹姆斯·庫克一月份的總銷售額。那麼原始資料在哪裡呢？我們是怎麼得出三萬美元這個數字的？生成此數字的原始銷售交易記錄列表在哪裡？很明顯，在某個地方，一定有人不辭辛勞地把過去六個月的所有銷售交易整理成了我們上面看到的摘要。你認為這要花多長時間？一個小時？十個？

很可能是的。你看，上面的電子表格實際上不是資料透視表。它是從其他地方儲存的原始資料手動建立的，編譯確實需要幾個小時。然而，它正是那種可以使用資料透視表建立的摘要，在這種情況下，只需幾秒鐘。讓我們看看…

如果我們要追蹤原始的銷售交易列表，它可能看起來像這樣：

您可能會驚訝地發現，使用Excel的資料透視表功能，只需單擊幾下滑鼠，我們就可以在幾秒鐘內建立一個類似於上述內容的月度銷售摘要。我們可以做到這一點——還有更多！

如何建立資料透視表

首先，確保在Excel的工作表中有一些原始資料。財務交易的清單是典型的，但它可以是一個幾乎任何東西的清單：員工聯絡方式、你的CD收藏或你公司車隊的燃油消耗資料。

所以我們啟動Excel…載入這樣一個列表…

一旦我們在Excel中打開了列表，我們就可以開始建立資料透視表了。

單擊列表中的任意一個單元格：

然後，從“**”選項卡中，單擊“資料透視表”圖示：

此時會出現“建立資料透視表”框，詢問您兩個問題：新資料透視表應基於哪些資料，以及應在何處建立資料透視表？因為我們已經單擊了列表中的一個單元格（在上面的步驟中），所以已經為我們選擇了圍繞該單元格的整個列表（在本例中，付款表上的a$1:$G$88）。請注意，我們可以在任何其他工作表的任何其他區域中選擇列表，甚至可以選擇某些外部資料來源，例如Access資料庫表，甚至MS-SQL Server資料庫表。我們還需要選擇是在新工作表上建立新的資料透視表，還是在現有工作表上建立新的資料透視表。在本例中，我們將選擇一個新的：

將為我們建立新工作表，並在該工作表上建立一個空白資料透視表：

另一個框也會出現：資料透視表字段列表。每當單擊資料透視表中的任何單元格時，都會顯示此欄位列表（如上所示）：

框頂部的欄位列表實際上是原始資料工作表中列標題的集合。螢幕下方的四個空白框允許我們選擇資料透視表彙總原始資料的方式。到目前為止，這些框中沒有任何內容，因此資料透視表是空的。我們所要做的就是從上面的列表中將欄位向下拖動，然後將它們放到下面的框中。然後自動建立一個數據透視表來匹配我們的指令。如果我們弄錯了，我們只需要將欄位拖回原來的位置和/或拖下新欄位來替換它們。

值框可以說是這四個框中最重要的一個。拖到該框中的欄位表示需要以某種方式彙總的資料（透過求和、求平均值、求最大值、求最小值等）。幾乎都是數字資料。在我們的示例資料中，這個框的一個完美候選是“Amount”欄位/列。讓我們將該欄位拖到“值”框中：

注意，（a）欄位列表中的“Amount”欄位現在被勾選，並且“Sum of Amount”被新增到值框中，表示Amount列已經求和。

如果我們檢查資料透視表本身，就會發現原始資料工作表中所有“金額”值的總和：

我們已經建立了第一個資料透視表！手到擒來，但不是特別令人印象深刻。很可能我們需要對我們的資料有更多的瞭解。

參考我們的樣本資料，我們需要確定一個或多個列標題，我們可以想象使用這些標題來劃分這個總數。例如，我們可能決定要檢視資料摘要，其中我們有公司中每個不同銷售人員的行標題，以及每個銷售人員的總數。為此，我們只需將“Salesperson”欄位拖到行標籤框中：

現在，事情終於開始變得有趣了！我們的資料透視表開始成形…。

通過幾次單擊，我們已經建立了一個需要很長時間才能手動完成的表。

那我們還能做什麼呢？從某種意義上說，我們的資料透視表是完整的。我們已經建立了一個有用的源資料摘要。重要的東西已經學會了！在本文的其餘部分，我們將研究建立更復雜資料透視表的一些方法，以及定製這些資料透視表的方法。

首先，我們可以建立一個二維表。讓我們使用“付款方法”作為列標題來實現這一點。只需將“付款方法”標題拖動到列標籤框中：

看起來是這樣的：

開始變得很酷了！

讓我們把它做成一張三維的桌子。這樣一張桌子可能是什麼樣子？好吧，讓我看看…

將“包”列/標題拖到報表篩選器框：

注意它的結局…。

這使我們能夠根據購買的“假日套餐”篩選報表。例如，我們可以看到所有套餐的銷售人員與付款方式的細分，或者單擊幾下，將其更改為顯示“Sunseekers”套餐的相同細分：

所以，如果你想對了，我們的資料透視表現在是三維的。讓我們繼續定製…

如果我們只想看到支票和信用卡交易（即沒有現金交易），那麼我們可以從列標題中取消選擇“現金”項。單擊列標籤旁邊的下拉箭頭，然後取消選中“現金”：

讓我們看看那是什麼樣子…正如你所看到的，“現金”不見了。

格式化

這顯然是一個非常強大的系統，但到目前為止，結果看起來非常簡單和無聊。首先，我們正在求和的數字看起來不像是美元金額——只是普通的舊數字。讓我們糾正一下。

在這種情況下，一種誘惑可能是做我們習慣做的事情，只需選擇整個表（或整個工作表），然後使用工具欄上的標準數字格式按鈕來完成格式設定。這種方法的問題是，如果將來更改資料透視表的結構（99%的可能性），那麼這些數字格式將丟失。我們需要一種使它們（半）永久的方法。

首先，我們在Values框中找到“Sum of Amount”條目，然後單擊它。出現一個選單。我們從選單中選擇“值欄位設定…”：

此時會出現“值欄位設定”框。

單擊“數字格式”按鈕，將顯示“標準格式單元格”框：

從“類別”列表中，選擇（比方說）Accounting，並將小數位數降至0。單擊“確定”幾次以返回資料透視表…

如您所見，數字已正確格式化為美元金額。

當我們討論格式化問題時，讓我們格式化整個資料透視表。有幾種方法可以做到這一點。讓我們用一個簡單的…

單擊“資料透視表工具/設計”選項卡：

然後下拉“資料透視表樣式”列表右下角的箭頭以檢視大量內建樣式：

選擇任何一個有吸引力的，然後檢視資料透視表中的結果：

其他選擇

我們也可以和日期一起工作。現在通常，事務列表中有很多很多日期，比如我們開始的那個。但是Excel提供了按天、周、月、年等將資料項分組的選項。讓我們看看是如何做到的。

首先，讓我們從“列標籤”框中刪除“付款方式”列（只需將其拖回欄位列表），並將其替換為“預訂日期”列：

如您所見，這使得資料透視表立即變得毫無用處，為事務發生的每個日期提供一列–一個非常寬的表！

要解決此問題，請右鍵單擊任意日期，然後從關聯選單中選擇“組…”：

將顯示分組框。我們選擇月份並單擊確定：

瞧！更有用的表格：

（順便說一句，此表實際上與本文開頭所示的表相同，即手動建立的原始銷售摘要。）

另一件很酷的事情是，可以有多個行標題（或列標題）：

……看起來像這樣…。

您可以對列標題（甚至報表過濾器）執行類似的操作。

再簡單一點，讓我們看看如何繪製平均值，而不是求和值。

首先，單擊“金額總和”，然後從出現的上下文選單中選擇“值欄位設定…”：

在“值欄位設定”框中的“按列表彙總值”欄位中，選擇“平均值”：

在這裡，讓我們將自定義名稱從“平均數量”更改為更簡潔的名稱。鍵入類似“Avg”的內容：

單擊“確定”，然後檢視它的外觀。請注意，所有值都從總計更改為平均值，並且表格標題（左上單元格）已更改為“Avg”：

如果我們願意，我們甚至可以在同一個資料透視表上有總和、平均數和計數（計數=有多少銷售額）！

以下是將類似內容放置到位的步驟（從空白資料透視表開始）：

1. 將“Salesperson”拖到列標籤中
2. 將“金額”欄位向下拖動到值框中三次
3. 對於第一個“Amount”欄位，將其自定義名稱更改為“Total”，將其數字格式更改為Accounting（0位小數位）
4. 對於第二個“Amount”欄位，將其自定義名稱更改為“Average”，將其函式更改為Average，將其數字格式更改為Accounting（0位小數位）
5. 對於第三個“Amount”欄位，將其名稱更改為“Count”，將其函式更改為Count
6. 將自動建立的欄位從列標籤拖動到行標籤

我們的結局是：

同一資料透視表上的總計、平均值和計數！

結論

由microsoftexcel建立的資料透視表有許許多多的特性和選項，這些特性和選項太多了，無法在這樣的文章中列出。為了充分發揮資料透視表的潛力，需要一本小書（或一個大型網站）。勇敢和/或極客的讀者可以很容易地進一步探索資料透視表：只需右鍵單擊幾乎所有內容，然後檢視您可以使用哪些選項。還有兩個功能區選項卡：“資料透視表工具/選項”和“設計”。不管你是否犯了錯誤——刪除資料透視表並重新開始很容易——Lotus1-2-3的老DOS使用者從來沒有過這種可能性。

如果您在Office 2007中工作，您可能希望檢視我們關於如何在Excel 2007中建立資料透視表的文章。

我們提供了一個Excel工作簿，您可以下載它來練習資料透視表技能。它應該適用於97以後的所有版本的Excel。

下載我們的練習Excel工作簿