萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 網路安全 >  >> 網路基礎知識

Excel 數據透視表從入門到精通:實戰步驟詳解

數據透視表是 Excel 最強大的數據分析工具,能將龐大原始數據快速多維度匯總、分類、篩選、計算,生成動態報表。本文以「南方公司 1998 年 10 月商品銷售表」為例,演示完整建立流程。

資料準備:規範化原始表

  • 第一行為**標題列**(欄位名),無空行、無合併儲存格。
  • 每列資料類型一致(數值、日期、文本)。
  • 範例欄位:`日期`、`商店`、`商品`、`銷售員`、`數量`、`單價`、`金額`。

建立透視表 4 步驟 (Excel 2010/2013/2016/2019/365 通用)

  1. 選擇資料來源: 點選資料區域任一儲存格 → 插入 選項卡 → 數據透視表 → 對話框自動識別範圍 (如 `$A$1:$G$28`) → 選擇「新工作表」放置 → 確定。
  2. 拖放欄位佈局 (右側「數據透視表欄位」窗格):
    • 篩選區域:拖入 `日期` (可按月/季篩選)。
    • 列標籤:拖入 `商品` (按商品分類)。
    • 行標籤:拖入 `商店` (按商店分組)。
    • 值:拖入 `金額` → 自動變為「求和項: 金額」 (若為文本自動變「計數」,需手動改「值欄位設定」→「求和」)。
  3. 調整報表版面:
    • 「設計」選項卡 → 「報表版面」→ 選擇「表格形式」/「大綱形式」/「緊湊形式」。
    • 「小計」→「不顯示小計」或「顯示在頂部/底部」。
    • 「空白行」→ 插入/移除空白行分隔群組。
  4. 進階分析:
    • 值顯示方式: 右鍵值區 → 「值顯示方式」→ 「行總計的百分比」/「列總計的百分比」/「與上一項差異」/「排名」。
    • 分組: 日期欄位右鍵 → 「群組」→ 依月/季/年分組;數值欄位 → 依區間分組 (如金額 0-1000, 1000-2000)。
    • 計算欄位/項目: 「分析」→ 「欄位、項目與集合」→ 「計算欄位」→ 如 `利潤率 = 金額 / 成本` (需成本欄位)。
    • 切片器/時間軸 (2013+): 「分析」→ 「插入切片器」選 `商店`、`商品` → 視覺化按鈕篩選;「插入時間軸」選 `日期` → 直觀時間篩選。

進階技巧與最佳實踐

技巧操作效果
動態資料源將原始資料建立為「超級表」(Ctrl+T) → 透視表資料源引用表名新增資料自動擴展範圍,重新整理即可更新
多張表關聯 (資料模型)建立透視表勾選「將此資料加入資料模型」 → 「管理關係」建立主鍵關聯跨表分析 (如銷售表關聯產品表/客戶表),Power Pivot 基礎
保留格式刷新右鍵透視表 → 「數據透視表選項」→ 「版面與格式」→ 勾選「重新整理時保留儲存格格式」自訂條件格式、欄寬不被重置
顯示明細資料雙擊任意匯總值儲存格新工作表自動鑽取該單元格對應的原始明細行
防止欄寬自動調整選項 → 取消勾選「重新整理時自動調整欄寬」保持報表版面穩定

常見問題排查

  • 數值顯示為計數而非求和: 原始欄位含文本/空值/錯誤值 → 清洗資料 (文字分欄轉數值、IFERROR 處理錯誤) 或值欄位設定改「求和」。
  • 日期無法分組: 儲存格格式為文本/真實日期混雜 → 全選欄位「文字分欄」→ 完成 (轉真實日期) 或 `DATEVALUE` 函數轉換。
  • 重新整理報錯: 檢查資料源範圍是否存在、工作表是否被刪除/重命名、外部連結是否斷開。

原始銷售數據表範例

建立透視表向導步驟1

選擇資料源範圍步驟2

選擇放置位置步驟3

copyright © 萬盛學電腦網 all rights reserved