Chapter 06 使 用 樞 紐 分 析 表 快 速 分 組 資 料 127 學 習 要 點 認 識 樞 紐 分 析 表 建 立 樞 紐 分 析 表 調 整 樞 紐 分 析 表 的 版 面 配 置 顯 示 群 組 小 計 的 合 計 折 疊 與 展 開 明 細 資 料 篩 選 與 排 序 樞 紐 分 析 表 資 料 新 增 計 算 欄 位 本 章 結 構 什 麼 是 樞 紐 分 析 表 適 用 於 樞 紐 分 析 表 的 情 況 建 立 樞 紐 分 析 表 單 一 列 標 籤 的 樞 紐 分 析 表 多 重 列 標 籤 的 樞 紐 分 析 表 設 定 欄 位 在 樞 紐 分 表 的 顯 示 順 序 行 標 籤 列 標 籤 與 Σ 值 的 概 念 調 整 樞 紐 分 析 表 區 域 的 版 面 配 置 修 飾 圖 表 中 各 項 目 的 細 節 顯 示 合 計 資 料 設 定 數 字 格 式 設 定 資 料 群 組 報 表 版 面 配 置 折 疊 與 展 開 明 細 資 料 多 元 化 小 計 資 料 更 改 標 籤 或 欄 位 名 稱 Σ 值 欄 位 的 計 算 方 式 篩 選 與 排 序 樞 紐 分 析 表 資 料 新 增 計 算 欄 位 Chapter 06 使 用 樞 紐 分 析 表 快 速 分 組 資 料 1 建 立 樞 紐 分 析 表 2 篩 選 樞 紐 分 析 表 資 料 3 排 序 樞 紐 分 析 表 資 料 4 新 增 計 算 欄 位
128 提 示 樞 紐 分 析 表 的 用 途 樞 紐 分 析 表 是 指 可 以 用 來 快 速 合 併 和 比 較 大 量 資 料 的 互 動 式 表 格, 透 過 它 可 以 詳 細 分 析 數 值 資 料, 特 別 適 用 於 下 列 情 況 : 需 要 從 含 有 大 量 資 料 的 清 單 中, 存 取 有 意 義 的 資 訊 完 成 特 定 資 料 分 析 時 需 要 分 析 相 關 的 總 計 值, 特 別 是 針 對 一 長 串 數 字 進 行 加 總 並 針 對 每 一 數 字 進 行 多 重 比 較 時 需 要 建 立 小 計 與 彙 整 數 值 資 料, 或 依 類 別 子 類 別 彙 總 資 料 並 建 立 自 訂 運 算 公 式 時 需 要 進 行 樞 紐 分 析 ( 將 列 資 料 移 到 欄 資 料 或 將 欄 資 料 移 到 列 資 料 ) 以 便 查 看 來 源 資 料 的 不 同 彙 整 結 果 時 需 要 將 有 用 的 資 料 進 行 篩 選 排 序 群 組 及 設 定 格 式 化 條 件 時 要 針 對 各 類 產 品 與 銷 售 數 據 做 出 明 智 的 決 策, 必 須 分 析 比 較 不 同 銷 若 售 人 員 的 業 績 不 同 產 品 的 訂 購 額, 因 此 需 要 不 斷 追 蹤 訂 單 記 錄, 如 果 這 些 記 錄 只 是 簡 單 且 毫 無 意 義 的 數 字, 就 沒 有 任 何 作 用 透 過 Excel 的 樞 紐 分 析 表 能 將 相 關 記 錄 組 織 與 分 析 樞 紐 分 析 表 是 一 種 可 以 快 速 彙 總 大 量 資 料 的 互 動 式 方 法, 它 能 協 助 您 在 企 業 中 製 訂 關 於 重 要 資 料 的 縝 密 決 策 6-1 什 麼 是 樞 紐 分 析 表 樞 紐 分 析 表 是 具 有 交 互 功 能 的 表 格, 不 同 於 一 般 的 表 格 只 是 單 純 的 顯 示 之 所 以 稱 為 樞 紐 分 析 表, 是 因 為 可 以 動 態 改 變 其 版 面 配 置, 以 便 按 照 不 同 方 式 分 析 資 料 ; 也 可 以 重 新 安 排 欄 列 標 籤 和 報 表 篩 選 欄 位 每 一 次 改 變 版 面 配 置 時, 樞 紐 分 析 表 會 立 即 按 照 新 的 配 置 重 新 計 算 資 料 ; 如 果 原 始 資 料 有 異 動, 也 可 以 立 即 更 新 樞 紐 分 析 表 報 表 區 域 欄 位 區 段 區 域 區 段 在 學 習 如 何 建 立 樞 紐 分 析 表 之 前, 我 們 先 來 認 識 一 下 它 的 版 面 配 置 內 容
Chapter 06 使 用 樞 紐 分 析 表 快 速 分 組 資 料 129 報 表 篩 選 Σ 值 列 標 籤 欄 標 籤 提 示 使 用 樞 紐 分 析 表 的 方 法 內 容 Σ 值 列 標 籤 欄 標 籤 報 表 篩 選 說 明 用 來 顯 示 需 要 彙 總 的 數 值 資 料, 也 可 以 設 定 不 同 的 計 算 類 型, 例 如 : 加 總 平 均 值 乘 積 最 大 值 最 小 值 項 目 個 數 等 放 置 在 樞 紐 分 析 表 中 的 列 資 料, 例 如 : 地 區 和 銷 售 人 員 都 是 列 標 籤 放 置 在 樞 紐 分 析 表 中 的 欄 資 料, 例 如 : 產 品 允 許 使 用 者 篩 選 整 張 樞 紐 分 析 表, 以 顯 示 單 一 或 所 有 資 料, 例 如 : 工 作 表 中 的 訂 單 日 期 是 報 表 篩 選 欄 位, 透 過 它 可 以 顯 示 第 一 季 和 第 二 季 的 彙 總 資 料 6-2 適 用 於 樞 紐 分 析 表 的 情 況 如 果 必 須 手 動 處 理 的 話, 龐 大 的 資 料 與 時 刻 變 化 的 機 動 性 資 料 要 求, 以 及 多 層 次 的 報 表, 十 足 是 生 產 力 上 的 殺 手 人 工 解 決 這 其 中 的 任 何 一 個 問 題 不 僅 花 費 時 間, 而 且 可 能 會 在 分 析 中 引 發 數 不 清 的 錯 誤 當 使 用 者 擁 有 下 列 任 何 一 種 的 資 料 時, 都 可 以 使 用 樞 紐 分 析 表 有 大 量 交 易 資 料, 很 難 透 過 有 意 義 的 方 式 分 析 和 彙 總 需 要 找 出 資 料 內 部 的 關 係 並 分 組 需 要 找 出 資 料 中 某 一 欄 位 的 一 系 列 特 定 值 需 要 利 用 各 種 時 間 週 期 找 出 資 料 的 趨 勢 預 期 要 頻 繁 地 查 詢 分 析 資 料 的 變 化 需 要 建 立 包 含 新 增 部 分 資 料 的 分 類 匯 總 需 要 將 資 料 組 織 成 易 於 製 成 圖 表 的 格 式 定 義 樞 紐 分 析 表 資 料 來 源 排 列 欄 位 選 擇 版 面 配 置 之 後, 可 以 執 行 下 列 操 作 : 探 索 資 料 - 展 開 及 摺 疊 資 料, 顯 示 和 值 有 關 的 基 本 詳 細 資 料 排 序 篩 選 及 群 組 欄 位 變 更 彙 總 函 數 或 新 增 自 訂 計 算 公 式 變 更 版 面 配 置 與 欄 位 排 列 方 式 - 變 更 樞 紐 分 析 表 格 式 : 壓 縮 大 綱 或 列 表 新 增 重 新 排 列 及 移 除 欄 位 變 更 欄 位 或 項 目 的 順 序 變 更 欄 列 與 小 計 的 版 面 配 置 - 開 啟 或 關 閉 欄 與 列 欄 位 標 題, 顯 示 或 隱 藏 空 白 線 於 列 上 方 或 下 方 顯 示 小 計 更 新 時 調 整 欄 寬 將 欄 欄 位 移 到 列 標 籤 或 將 列 欄 位 移 到 欄 標 籤 接 下 頁
130 接 上 頁 變 更 空 白 與 錯 誤 值 的 顯 示 方 式 - 變 更 顯 示 錯 誤 及 空 白 儲 存 格 的 方 式 變 更 未 包 含 資 料 項 目 及 標 籤 的 顯 示 方 式 顯 示 / 隱 藏 空 白 行 變 更 格 式 - 手 動 及 依 條 件 設 定 儲 存 格 範 圍 格 式 變 更 整 個 樞 紐 分 析 表 表 格 樣 式 變 更 欄 位 的 數 值 格 式 6-3 建 立 樞 紐 分 析 表 基 本 樞 紐 分 析 表 是 指 根 據 已 有 的 資 料 內 容, 直 接 建 立 的 簡 單 樞 紐 分 析 表 在 樞 紐 分 析 表 中, 必 須 選 擇 要 新 增 到 樞 紐 分 析 表 的 欄 位, 視 需 要 製 作 單 一 列 標 籤 的 樞 紐 分 析 表, 也 可 以 製 作 多 重 列 標 籤 的 樞 紐 分 析 表 6-3-1 單 一 列 標 籤 的 樞 紐 分 析 表 單 一 列 標 籤 的 樞 紐 分 析 表 是 指 在 報 表 中 只 顯 示 一 個 列 標 籤, 將 資 料 完 整 呈 現 沒 有 內 含 群 組 資 料 原 始 檔 案 Example\CH06\Start\ 銷 售 業 績 統 計.xlsx 完 成 檔 案 Example\CH06\End\ 單 一 列 標 籤 樞 紐 分 析 表.xlsx 1 開 啟 範 例 檔 案 後, 執 行 插 入 > 表 格 > 樞 紐 分 析 表 > 樞 紐 分 析 表 指 令 2 出 現 建 立 樞 紐 分 析 表 對 話 方 塊, 確 認 表 格 / 範 圍 的 資 料 來 源 為 $A$2:$F$30 ; 選 擇 要 放 置 樞 紐 分 析 表 的 位 置, 點 選 新 工 作 表 選 項, 按 確 定 鈕 提 示 樞 紐 分 析 圖 與 一 般 圖 表 之 間 的 差 異 第 五 章 已 詳 細 說 明 有 關 Excel 圖 表 的 各 項 操 作 與 用 途, 熟 悉 之 後 會 發 現 當 您 建 立 樞 紐 分 析 圖 之 後, 設 定 圖 表 項 目 的 方 法 與 一 般 圖 表 的 操 作 大 都 一 樣, 但 二 者 之 間 存 在 下 列 差 別 : 互 動 : 使 用 一 般 圖 表, 必 須 針 對 所 要 查 看 的 每 種 資 料 檢 視, 建 立 一 張 圖 表, 且 資 料 之 間 無 法 互 動 使 用 樞 接 下 頁 3 在 sheet1 新 工 作 表 中 會 顯 示 空 白 待 建 立 樞 紐 分 析 表 的 報 表 區 域 報 表 區 域
Chapter 06 使 用 樞 紐 分 析 表 快 速 分 組 資 料 131 4 在 樞 紐 分 析 表 欄 位 清 單 窗 格 中, 勾 選 產 品 名 稱 銷 售 數 量 銷 售 單 價 銷 售 金 額 ( 元 ) 核 取 方 塊, 將 這 些 欄 位 新 增 到 報 表 中 5 報 表 區 域 已 顯 示 所 建 立 的 樞 紐 分 析 表 資 料, 其 中 只 有 一 個 列 標 籤 視 需 要 可 以 將 sheet1 工 作 表 重 新 命 名 為 樞 紐 分 析 表 單 一 列 標 籤 報 表 6-3-2 多 重 列 標 籤 的 樞 紐 分 析 表 選 擇 欄 位 多 重 行 標 籤 樞 紐 分 析 表 是 指 樞 紐 分 析 表 中 有 多 個 行 標 籤 欄 位, 首 先 資 料 來 源 中 有 多 個 欄 位, 並 且 添 加 的 欄 位 在 行 標 籤 的 位 置, 再 使 每 組 資 料 以 組 合 的 方 式 進 行 顯 示 原 始 檔 案 Example\CH06\Start\ 銷 售 業 績 統 計.xlsx 完 成 檔 案 Example\CH06\End\ 多 重 列 標 籤 樞 紐 分 析 表.xlsx 1 開 啟 範 例 檔 案 後, 執 行 插 入 > 表 格 > 樞 紐 分 析 表 > 樞 紐 分 析 表 指 令 2 出 現 建 立 樞 紐 分 析 表 對 話 方 塊, 確 認 表 格 / 範 圍 的 資 料 來 源 為 $A$2:$F$30 ; 選 擇 要 放 置 樞 紐 分 析 表 的 位 置, 點 選 新 工 作 表 選 項, 按 確 定 鈕 接 上 頁 紐 分 析 圖, 可 以 變 更 報 表 版 面 配 置 或 顯 示 的 詳 細 資 料, 建 立 單 一 圖 表 然 後 以 不 同 方 式 檢 視 資 料 圖 表 類 型 : 可 以 將 樞 紐 分 析 圖 的 類 型 變 更 為 除 了 散 佈 圖 股 票 圖 和 泡 泡 圖 之 外 的 任 何 其 他 圖 表 類 型 圖 表 位 置 : 預 設 值, 一 般 圖 表 是 嵌 入 在 工 作 表 ; 樞 紐 分 析 圖 是 建 立 在 圖 表 工 作 表 中, 當 然 也 可 將 其 重 新 移 動 到 指 定 的 工 作 表 來 源 資 料 : 一 般 圖 表 會 直 接 連 接 到 工 作 表 中 對 應 的 儲 存 格 ; 樞 紐 分 析 圖 則 是 依 據 關 聯 的 樞 紐 分 析 表 中 數 個 不 同 的 資 料 類 型 來 建 立 圖 表 項 目 : 樞 紐 分 析 圖 除 了 包 含 與 一 般 圖 表 相 同 的 項 目 之 外, 還 包 含 欄 位 加 總 值, 可 以 新 增 旋 轉 或 刪 除 欄 位 加 總 值 顯 示 不 同 資 料 的 分 析 圖 ; 樞 紐 分 析 圖 還 可 包 含 報 表 篩 選 接 下 頁
132 接 上 頁 欄 位 在 樞 紐 分 析 圖 中, 可 以 設 定 圖 例 的 擺 放 位 置 並 更 改 其 標 題 的 字 型 大 小, 但 無 法 移 動 或 重 新 調 整 繪 圖 區 圖 例 圖 表 標 題 或 座 標 軸 標 題 的 大 小 在 一 般 圖 表 中, 則 可 以 移 動 和 重 新 調 整 上 述 圖 表 項 目 的 大 小 格 式 設 定 : 當 您 更 新 樞 紐 分 析 圖 時, 會 保 留 大 部 分 的 格 式 設 定, 包 含 項 目 版 面 配 置 及 樣 式, 但 不 會 保 留 趨 勢 線 資 料 標 籤 誤 差 線 及 其 他 資 料 集 的 變 更 ; 而 一 般 圖 表 在 套 用 格 式 設 定 後, 不 會 遺 失 上 述 的 設 定 3 在 sheet1 新 工 作 表 中 會 顯 4 在 樞 紐 分 析 表 欄 位 清 單 窗 示 空 白 待 建 立 樞 紐 分 析 表 的 報 表 格 中, 勾 選 所 有 欄 位 核 取 方 塊, 將 區 域 它 們 新 增 到 報 表 中 選 擇 欄 位 報 表 區 域 5 報 表 區 域 已 顯 示 所 建 立 的 樞 紐 分 析 表 資 料, 其 中 包 含 多 重 列 標 籤, 即 產 品 名 稱 銷 售 地 區 業 務 員 多 重 列 標 籤 報 表 6 在 樞 紐 分 析 表 欄 位 清 單 窗 格 中, 取 消 勾 選 業 務 員 核 取 方 塊 ; 此 時 可 以 看 到 樞 紐 分 析 表 已 自 動 更 新, 只 顯 示 二 個 列 標 籤, 即 產 品 名 稱 銷 售 地 區 取 消 顯 示 業 務 員 列 標 籤 報 表
Chapter 06 使 用 樞 紐 分 析 表 快 速 分 組 資 料 133 6-3-3 設 定 欄 位 在 樞 紐 分 析 表 的 顯 示 順 序 建 立 樞 紐 分 析 表 選 擇 報 表 欄 位 時, 選 擇 欄 位 的 順 序 與 樞 紐 分 析 表 中 欄 位 顯 示 位 置 的 先 後 是 互 相 對 應 的, 即 在 欄 位 清 單 中 先 選 擇 的 欄 位, 會 顯 示 在 其 他 欄 位 的 前 面, 依 次 類 推 原 始 檔 案 Example\CH06\Start\ 銷 售 業 績 統 計.xlsx 完 成 檔 案 Example\CH06\End\ 欄 位 的 顯 示 順 序.xlsx 1 開 啟 範 例 檔 案 後, 執 行 插 入 > 表 格 > 樞 紐 分 析 表 > 樞 紐 分 析 表 指 令 2 出 現 建 立 樞 紐 分 析 表 對 話 方 塊, 確 認 表 格 / 範 圍 的 資 料 來 源 為 $A$2:$F$30 ; 選 擇 要 放 置 樞 紐 分 析 表 的 位 置, 點 選 新 工 作 表 選 項, 按 確 定 鈕 3 在 sheet1 新 工 作 表 中 會 顯 示 空 白 待 建 立 樞 紐 分 析 表 的 報 表 區 域 4 在 樞 紐 分 析 表 欄 位 清 單 窗 格 中, 依 次 勾 選 所 有 欄 位 核 取 方 塊, 將 它 們 新 增 到 報 表 中 選 擇 欄 位 報 表 區 域
134 提 示 5 樞 紐 分 析 表 中 顯 示 的 列 標 籤 資 料, 與 樞 紐 分 析 表 欄 位 清 單 窗 格 中 的 欄 位 順 序 相 同 同 時 建 立 樞 紐 分 析 表 和 樞 紐 分 析 圖 建 立 樞 紐 分 析 表 的 同 時, 也 可 以 同 時 建 立 樞 紐 分 析 圖 1 開 啟 範 例 檔 案 後, 執 行 插 入 > 表 格 > 樞 紐 分 析 表 > 樞 紐 分 析 圖 指 令 依 次 選 擇 報 表 欄 位 的 結 果 6 在 樞 紐 分 析 表 欄 位 清 單 窗 格 中, 取 消 勾 選 所 有 欄 位 的 核 取 方 塊, 再 依 次 勾 選 產 品 名 稱 銷 售 地 區 銷 售 單 價 欄 位 的 核 取 方 塊 7 在 樞 紐 分 析 表 欄 位 清 單 窗 格 中, 勾 選 所 需 要 新 增 的 欄 位 核 取 方 塊, 例 如 : 銷 售 數 量 2 出 現 建 立 具 有 樞 紐 分 析 圖 的 樞 紐 分 析 表 對 話 方 塊, 確 認 表 格 / 範 圍 的 資 料 來 源 為 $A$2:$F$30 ; 選 擇 要 放 置 樞 紐 分 析 表 和 分 析 圖 的 位 置, 點 選 新 工 作 表 選 項, 按 確 定 鈕 8 此 時, 可 以 看 到 後 面 新 增 的 銷 售 數 量 欄 位, 雖 然 在 窗 格 中 是 顯 示 在 前 面, 但 在 報 表 中 是 顯 示 在 最 後 接 下 頁
Chapter 06 使 用 樞 紐 分 析 表 快 速 分 組 資 料 135 6-3-4 行 標 籤 列 標 籤 與 Σ 值 的 概 念 樞 紐 分 析 表 是 由 四 個 區 域 組 成 : 報 表 篩 選 列 標 籤 欄 標 籤 Σ 值 這 一 小 節 將 說 明 樞 紐 分 析 表 結 構, 讓 您 更 加 明 白 樞 紐 分 析 表 功 用 完 成 檔 案 Example\CH06\Start\ 設 定 樞 紐 分 析 表 欄 位.xlsx 報 表 篩 選 區 域 開 啟 範 例 檔 案 後, 可 以 看 到 報 表 篩 選 區 域 位 於 樞 紐 分 析 表 的 左 上 方, 並 顯 示 下 拉 式 清 單 鈕, 表 示 該 區 域 是 可 選 擇 的, 可 以 視 需 顯 示 動 態 篩 選 結 果 報 表 篩 選 區 域 接 上 頁 3 在 sheet1 新 工 作 表 中 會 顯 示 樞 紐 分 析 表 和 樞 紐 分 析 圖 區 域 4 在 樞 紐 分 析 表 欄 位 清 單 窗 格 中, 勾 選 要 新 增 到 報 表 的 欄 位 核 取 方 塊 列 標 籤 列 標 籤 是 由 樞 紐 分 析 表 左 側 展 開 的 標 題 組 成 的, 例 如 : 地 區 和 銷 售 人 員 都 是 列 欄 位, 這 個 區 域 可 以 有 欄 位, 也 可 以 沒 有 ; 若 有 顯 示 欄 位, 可 以 在 樞 紐 分 析 表 左 邊 展 開 的 各 列 中, 顯 示 特 定 的 值 列 標 籤 5 sheet1 工 作 表 已 顯 示 對 應 資 料 的 樞 紐 分 析 表 欄 標 籤 欄 標 籤 是 由 樞 紐 分 析 表 各 欄 上 方 展 開 的 標 題 組 成, 例 如 : 銷 售 單 價 和 銷 售 金 額 就 顯 示 在 欄 標 籤 區 域 6 同 時 會 顯 示 對 應 資 料 的 樞 紐 分 析 圖, 二 者 的 資 料 相 同 欄 標 籤
136 Σ 值 Σ 值 是 列 標 籤 右 側 至 欄 標 籤 下 方 的 一 大 矩 形 區 域, 在 該 區 域 顯 示 各 欄 位 的 值 這 個 區 域 至 少 要 有 一 個 欄 位 和 一 個 針 對 該 欄 位 的 計 算 結 果 Σ 值 區 域 各 區 域 中 的 顯 示 欄 位 樞 紐 分 析 表 欄 位 清 單 窗 格 下 方 包 括 : 報 表 篩 選 欄 標 籤 列 標 籤 及 Σ 值 四 個 區 域, 在 各 區 域 中 可 以 看 到 目 前 樞 紐 分 折 表 中 各 欄 位 的 配 置 樞 紐 分 析 表 的 結 構 6-3-5 調 整 樞 紐 分 析 表 區 域 的 版 面 配 置 建 立 樞 紐 分 析 表 之 後, 可 以 針 對 各 區 域 中 的 欄 位 進 行 調 整, 例 如 : 移 動 欄 位 調 整 欄 位 的 顯 示 次 序 移 除 欄 位 等, 使 樞 紐 分 析 表 呈 現 所 需 要 的 結 果 原 始 檔 案 Example\CH06\Start\ 設 定 樞 紐 分 析 表 版 面 配 置.xlsx 完 成 檔 案 Example\CH06\End\ 設 定 樞 紐 分 析 表 版 面 配 置.xlsx 移 動 欄 位 在 報 表 篩 選 欄 標 籤 列 標 籤 及 Σ 值 區 域 中, 可 以 調 整 各 欄 位 的 顯 示 位 置, 如 此 可 以 得 到 全 新 不 同 分 析 角 度 的 樞 紐 分 析 表