Excel 資料分析 - 資料透視表

---

---

對大型資料集進行資料分析通常是必要且重要的。它涉及對資料進行彙總，獲取所需的值並呈現結果。

Excel 提供資料透視表，使您可以輕鬆快速地彙總數千個數據值，從而獲得所需的結果。

考慮以下銷售資料表。根據此資料，您可能需要按區域、按月份或按銷售人員彙總總銷售額。處理這些任務的簡便方法是建立一個數據透視表，您可以動態修改該表以按您想要的方式彙總結果。

建立資料透視表

要建立資料透視表，請確保第一行包含標題。

• 單擊表格。
• 單擊功能區上的“插入”選項卡。
• 單擊“表格”組中的“資料透視表”。將出現“資料透視表”對話方塊。

如您在對話方塊中所見，您可以使用當前工作簿中的表格或區域，也可以使用外部資料來源。

• 在“表格/區域”框中，鍵入表名。
• 單擊“新建工作表”以告訴 Excel 將資料透視表儲存在何處。
• 單擊“確定”。

將出現一個空白資料透視表和一個數據透視表字段列表。

推薦的資料透視表

如果您不熟悉資料透視表或不知道要從資料中選擇哪些欄位，則可以使用 Excel 提供的**推薦的資料透視表**。

• 單擊資料表。

• 單擊“插入”選項卡。

• 單擊“表格”組中的“推薦的資料透視表”。將出現“推薦的資料透視表”對話方塊。

在“推薦的資料透視表”對話方塊中，將顯示適合您資料的可能的自定義資料透視表。

• 單擊每個資料透視表選項以檢視右側的預覽。
• 單擊“按銷售人員和月份彙總訂單金額”的資料透視表。

單擊“確定”。選定的資料透視表將出現在新的工作表上。您可以觀察到資料透視表字段列表中選擇的欄位。

資料透視表字段

資料表中的標題將顯示為資料透視表中的欄位。

您可以選擇/取消選擇它們以立即更改資料透視表，使其僅顯示您想要的資訊以及您想要的方式。例如，如果要顯示帳戶資訊而不是訂單金額資訊，請取消選擇“訂單金額”並選擇“帳戶”。

資料透視表區域

您甚至可以立即更改資料透視表的佈局。您可以使用資料透視表區域來實現此目的。

在資料透視表區域中，您可以選擇：

• 哪些欄位顯示為行
• 哪些欄位顯示為列
• 如何彙總資料
• 任何欄位的篩選器
• 何時更新資料透視表佈局
  • 您可以立即在您跨區域拖動欄位時更新它，或者
  • 您可以延遲更新，並且只有在單擊“更新”時才更新它。

即時更新可以幫助您嘗試不同的佈局並選擇適合您的報表要求的佈局。

您只需將欄位拖動到這些區域，並在執行此操作時觀察資料透視表佈局。

資料透視表中的巢狀

如果您在任何區域中有多個欄位，則巢狀將按您在該區域中放置欄位的順序發生。您可以透過拖動欄位來更改順序並觀察巢狀如何變化。在上述佈局選項中，您可以觀察到：

• 月份位於列中。
• 區域和銷售人員按該順序排列在行中，即銷售人員值巢狀在區域值下。
• 彙總按“訂單金額總和”進行。
• 未選擇任何篩選器。

生成的透視表如下：

在“資料透視表區域”中的“行”中，單擊“區域”並將其拖到“銷售人員”下方，使其如下所示：

巢狀順序發生更改，生成的透視表如下：

**注意** - 您可以清楚地看到，巢狀順序為“區域”，然後是“銷售人員”的佈局比巢狀順序為“銷售人員”，然後是“區域”的佈局產生更好、更緊湊的報表。如果“銷售人員”代表多個區域，並且您需要按銷售人員彙總銷售額，則第二個佈局將是更好的選擇。

篩選器

您可以為某個欄位分配篩選器，以便您可以根據該欄位的值動態更改資料透視表。

將“區域”從“行”拖動到“資料透視表區域”中的“篩選器”。

帶有標籤“區域”的篩選器將出現在資料透視表上方（如果您在資料透視表上方沒有空行，則資料透視表將向下推以騰出空間用於篩選器）。

您可以看到：

• 銷售人員值出現在行中。
• 月份值出現在列中。
• 區域篩選器顯示在頂部，預設選擇為“全部”。
• 彙總值為“訂單金額總和”。
  • 按銷售人員彙總的訂單金額總和顯示在“總計”列中。
  • 按月份彙總的訂單金額總和顯示在“總計”行中。

單擊篩選器區域右側框中的箭頭。將出現一個包含欄位區域值的下拉列表。

• 選中**“選擇多個專案”**選項。將為所有值顯示覆選框。
• 選擇“南方”和“西方”，然後取消選擇其他值，然後單擊“確定”。

將僅彙總與南方和西方地區相關的資料，如下面的螢幕截圖所示：

您可以看到，在“區域篩選器”旁邊顯示了**“多個專案”**，這表示您已選擇了多個專案。但是，從顯示的報表中無法知道選擇了多少個專案和/或選擇了哪些專案。在這種情況下，使用切片器是更好的篩選選項。

切片器

您可以使用切片器更清楚地瞭解過濾了哪些專案。

• 單擊功能區上“資料透視表工具”下的“分析”。

• 單擊“篩選器”組中的“插入切片器”。將出現“插入切片器”框。它包含來自您的資料的所有欄位。

• 選擇“區域”和“月份”欄位。單擊“確定”。

每個選定欄位的切片器將出現，預設情況下所有值都已選中。“切片器工具”將出現在功能區上，用於處理切片器的設定、外觀和感覺。

• 在“區域”切片器中選擇“南方”和“西方”。
• 在“月份”切片器中選擇“二月”和“三月”。
• 在切片器中選擇多個值時，按住 Ctrl 鍵。

切片器中選定的專案將突出顯示。將顯示包含所選專案彙總值的資料透視表。

透過其他計算彙總值

在之前的示例中，您已經看到透過“總和”彙總值。但是，如有必要，您也可以使用其他計算。

在“資料透視表字段列表”中

• 選擇“帳戶”欄位。
• 取消選擇“訂單金額”欄位。

• 將“帳戶”欄位拖動到“彙總值”區域。預設情況下，將顯示“帳戶總和”。
• 單擊框右側的箭頭。
• 在出現的下拉選單中，單擊“值欄位設定”。

將出現“值欄位設定”框。多個型別的計算將顯示為列表，位於“按以下方式彙總值欄位”下：

• 在列表中選擇“計數”。
• 自定義名稱將自動更改為“帳戶計數”。單擊“確定”。

資料透視表將按計數彙總帳戶值。

資料透視表工具

按照以下步驟學習如何使用資料透視表工具。

• 選擇資料透視表。

以下資料透視表工具將出現在功能區上：

• 分析
• 設計

分析

一些**分析**功能區命令是：

• 設定資料透視表選項
• 所選欄位的值欄位設定
• 展開欄位
• 摺疊欄位
• 插入切片器
• 插入時間軸
• 重新整理資料
• 更改資料來源
• 移動資料透視表
• 求解順序（如果有多個計算）
• 資料透檢視

設計

一些**設計**功能區命令是：

• 資料透視表佈局
  • 小計選項
  • 總計選項
  • 報表佈局表單
  • 空行選項
• 資料透視表樣式選項
• 資料透視表樣式

展開和摺疊欄位

您可以透過兩種方式展開或摺疊所選欄位的所有專案：

• 透過選擇所選欄位左側的符號或。
• 透過單擊“分析”功能區上的“展開欄位”或“摺疊欄位”。

透過選擇所選欄位左側的展開符號或摺疊符號

• 選擇資料透視表中包含“東部”的單元格。
• 單擊“東部”左側的摺疊符號。

“東部”下的所有專案都將被摺疊。“東部”左側的摺疊符號將更改為展開符號。

您可以觀察到只有“東部”下面的專案被摺疊了。其餘的資料透視表專案保持不變。

單擊“東部”左側的展開符號。“東部”下面的所有專案都將顯示。

使用功能區上的“分析”

您可以使用功能區上的“展開欄位”和“摺疊欄位”命令立即展開或摺疊資料透視表中的所有專案。

• 單擊資料透視表中包含“東部”的單元格。
• 單擊功能區上的“分析”選項卡。
• 單擊“活動欄位”組中的“摺疊欄位”。

資料透視表中“東部”欄位的所有專案都將摺疊。

單擊“活動欄位”組中的“展開欄位”。

所有專案都將顯示。

報表演示樣式

您可以像包含報表一樣選擇資料透視表演示樣式。選擇適合您其他簡報或報表的樣式。但是，不要過於沉迷於樣式，因為能有效顯示結果的報表總是比色彩豔麗但無法突出重要資料點的報表更好。

• 單擊資料透視表中的“東部”。
• 單擊“分析”。
• 單擊“活動欄位”組中的“欄位設定”。將出現“欄位設定”對話方塊。
• 單擊“佈局和列印”選項卡。
• 選中“在每個專案標籤後插入空行”。

每個“區域”欄位的值之後都將顯示空行。

您也可以從“設計”選項卡插入空行。

• 單擊“設計”選項卡。
• 單擊“佈局”組中的“報表佈局”。
• 在下拉列表中選擇“顯示在大綱形式”。

• 將滑鼠懸停在資料透視表樣式上。將顯示滑鼠所指向樣式的預覽。
• 選擇適合您報表的樣式。

將顯示採用所選樣式的大綱形式資料透視表。

資料透視表中的時間軸

要了解如何使用時間軸，請考慮以下示例，其中給出了按銷售人員和地點分類的各種商品的銷售資料。共有 1891 行資料。

從此區域建立資料透視表，其中：

• 按此順序將“地點”和“銷售人員”放在“行”中
• 將“產品”放在“列”中
• 將“金額之和”放在“彙總值”中

• 單擊資料透視表。
• 單擊“插入”選項卡。
• 單擊“篩選器”組中的“時間軸”。將出現“插入時間軸”對話方塊。

單擊“日期”，然後單擊“確定”。將出現“時間軸”對話方塊，並且“時間軸工具”將出現在功能區上。

• 在“時間軸”對話方塊中，選擇“月份”。
• 從下拉列表中選擇“季度”。
• 單擊“2014 年第 2 季度”。
• 按住 Shift 鍵並拖動到 2014 年第 4 季度。

時間軸選擇為 2014 年第 2 季度 - 第 4 季度。

資料透視表已根據此時間軸進行篩選。