Excel 枢纽分析.ppt

Excel製作樞紐分析表 樞鈕分析表精靈 產生樞紐分析表的資料 使用樞紐分析表 調整樞紐分析表 製作樞紐分析圖 2 樞鈕分析表精靈 Excel雖然可以進行資料的分析 但對象限於具有一定規則的資料 面對繁雜 毫無順序可言的流水帳資料 Excel再怎麼厲害也很難分析出其中的意義 這時候就要改用 樞紐分析表 像這種流水帳般的資料 很難看出哪個時期哪一款手機賣得最好 3 樞鈕分析表精靈 樞紐分析表 讓您只拖曳幾個欄位 就能將流水帳資料自動分類 同時顯示分類後的小計資訊 如果想用不同方式觀察資料 只要再次移動欄位的位置 立刻又會顯示不同的結果 在全配的手機銷售組合下 四月份銷售較好的是Panasonic牌的手機 4 樞鈕分析表精靈 選取要製作樞紐分析表的任何一個儲存格 點選功能表上的 資料 樞紐分析表及圖報表 功能 叫出 樞紐分析表和樞紐分析圖精靈 在第一個步驟裡 於 分析資料的來源 中選擇 MicrosoftOfficeExcel清單或資料庫 於報表的型式中選擇 樞紐分析表 選擇好後按 下一步 按鈕 5 樞鈕分析表精靈 接著要選取建立資料表的來源範圍 這裡Excel會自動選取儲存格所在的表格範圍 如果沒什麼問題 直接按 下一步 按鈕 6 樞鈕分析表精靈 範圍選擇好後 接著選擇將樞紐分析表放在 新工作表 按下 完成 按鈕 7 樞鈕分析表精靈 按下 完成 按鈕後 就會產生一個新的 Sheet4 工作表 於工作表中會有一個空白的樞紐分析表 並顯示 樞紐分析表 工具列及 樞鈕分析表欄位清單 產生樞紐分析表的資料 版面配置 加入欄位 9 版面配置 一開始所產生的樞紐分析表都是空白的 因此必須手動加入欄位 在加入欄位之前 來看樞紐分析表的版面配置 分頁欄位 列欄位 欄欄位 資料欄位 10 版面配置 欄欄位與列欄位 這兩個欄位都是用來將資料分類的項目 基本上沒有什麼差別 只不過一個位於直欄 一個位於橫列 資料欄位 放置要被分析的資料 也就是直欄與橫列項目相交所對應的資料 通常是數值資料 分頁欄位 限制下方的欄位只能顯示指定的資料 11 加入欄位 Excel會從樞紐分析表的來源範圍 自動分析出欄位 通常是將一整欄的資料當作一個欄位 這些欄位可以在 樞紐分析表欄位清單 中看到 如果沒有看到請按下樞鈕分析工具列上的 工具鈕 開啟 樞紐分析表欄位清單 12 加入欄位 選取 樞鈕分析表欄位清單 中的 廠牌 欄位 將它拖曳到欄欄位 拖曳到欄欄位上方時游標會出現符號 13 加入欄位 拖曳過去後 欄欄位就使用 廠牌 欄位當作直欄的分類 14 加入欄位 繼續將 交易日期 欄位拖曳到列欄位 將 數量 欄位拖曳到資料欄位 將 配備 欄位拖曳到分頁欄位 則最基本的樞紐分析表就完成了 從樞紐分析表中 可以看出在這段時間裡 各廠牌的手機分別賣出多少隻 以及每天又是賣出幾隻手機 15 加入欄位 16 加入欄位 除了用拖曳的方法將欄位加入到樞鈕分析表外 也可以直接在 樞鈕分析表欄位清單 中選擇要加入的欄位 再按下 新增至 選單鈕 選擇要加入的位置即可 17 加入欄位 其實使用 樞紐分析表精靈時 也可以設定版面的安排 只要在它的第3個步驟畫面中 按下 版面配置 按鈕 就可以拖曳欄位調整版面了 18 加入欄位 既然安排版面是用拖曳的方式 那麼要刪除欄位的話 也是用拖曳的方式 只要在樞紐分析表中 將不需要的欄位拖曳回 樞鈕分析表欄位清單 中 此時游標旁會出現符號 放開滑鼠 這個欄位就從版面中消失了 使用樞紐分析表 標籤與資料 顯示與隱藏資料 篩選資料 群組 分頁顯示 20 標籤與資料 使用樞紐分析表之前 必須先知道 標籤 與 資料 的差別 欄欄位與列欄位上面的項目 都是 標籤 資料欄位上的數值 則是 資料 標籤 是 資料 分類的依據 如果一個欄位同時擁有好幾個分類 例如 欄欄位同時包含 廠牌 和 機型 分類 大分類的標籤會包含多個次分類的標籤 也就是在 廠牌 標籤下 會包含好幾個 機型 的標籤 21 標籤與資料 大類別的標籤 次類別的標籤 標籤 資料 22 顯示與隱藏資料 資料欄位會顯示很多資料 有的對應到大分類的標籤 有的對應到次分類的標籤 隸屬各種標籤的資料混雜在一起 會無法產生樞紐分析的效果 因此必須適時地 隱藏明細資料 什麼是 明細資料 對大分類而言 次分類的資料就是 明細資料 例如 機型 是 廠牌 下的一個次分類 因此對應到 機型 標籤的資料 就是 廠牌 標籤的明細資料 如果把 機型 標籤的資料隱藏起來 就可以只檢視 廠牌 標籤的資料 23 顯示與隱藏資料 選取 Alcatel 標籤 按下 隱藏詳細資料 工具鈕 Alcatel 標籤下的明細資料就會被隱藏起來 24 顯示與隱藏資料 如果要再次顯示明細資料 同樣選取大分類的標籤 按樞紐分析表工具列的 按鈕 其下分類標籤的詳細資料就會顯示出來 如果直接選取欄位 例如 廠牌 欄位 按 工具鈕 則 廠牌 標籤下的每一個明細資料都會被隱藏起來 也就是 機型 標籤的資料都會暫時隱藏 25 篩選資料 樞紐分析表的每個欄位旁邊都有 按鈕 它是用來作篩選的 按任何一個欄位的 按鈕 取消勾選不想顯示的標籤 26 篩選資料 按下 確定 按鈕 則沒有勾選的標籤 都不會顯示出來 用這種方法可以篩選資料 只留下想要的部分 27 篩選資料 在分頁欄位按下 按鈕 從中選擇一個項目 則下方所有的資料都會被限定在這個項目的範圍裡面 28 群組 選取 交易日期 欄位 在上面按下滑鼠右鍵 選擇 群組及顯示詳細資料 群組 功能 29 群組 開啟 數列群組 對話方塊 在 間距值 欄位分別點選 月 和 季 點選好後按下 確定 按鈕 30 群組 回到工作表後 原本的 交易日期 欄位 會改成以 季 和 月份 呈現 就可以看出某一季的手機銷售量是多少 31 分頁顯示 選取分頁欄位 按下樞鈕分析表工具列上的 樞紐分析表 在選單中選擇 分頁顯示 32 分頁顯示 開啟 分頁顯示 對話方塊 選擇要使用的欄位 分頁欄位有可能同時擁有多個欄位 選擇好後按下 確定 按鈕 33 分頁顯示 回到工作表後 則每一個分頁欄位的項目 都會成為一個新工作表的標籤名稱 同時這個分頁項目的資料 會呈現在新的工作表上 調整樞紐分析表 欄位設定 設定表格選項 改變報表格式 35 欄位設定 修改名稱選取一個欄位 按下 樞紐分析表 工具列上的 工具鈕 36 欄位設定 開啟 樞鈕分析表欄 對話方塊 在 名稱 欄位輸入要修改的名稱 輸入好後 按下 確定 按鈕 欄位所顯示的名稱就會改變 37 欄位設定 資料摘要方式選取資料欄位的 加總的業績 項目 按下樞紐分析表 工具列上的 工具鈕 開啟 樞紐分析表欄位 對話方塊 38 欄位設定 在 摘要方式 方塊中選擇 最大值 選擇好後按下 確定 按鈕 39 欄位設定 則原本資料欄位的業績資訊 會改為顯示最大值 40 欄位設定 資料欄位的摘要資訊 不僅可以呈現數值 還可以表現各個標籤所佔的比例 選取資料欄位的 加總的數量 項目 按下樞紐分析表工具列上的 工具鈕 顯示 樞紐分析表欄位 對話方塊 按下 選項 按鈕 在 資料顯示方式 欄位選擇 總列數的百分比 都設定好後按下 確定 按鈕 41 欄位設定 回到工作表後 則Excel就會將標籤的數值佔同一列總計的比例 以百分比顯示 42 欄位設定 儲存格格式樞紐分析表其實跟普通的儲存格一樣 它的背景色彩和字型都可以任意改變 只要選取樞紐分析表中的儲存格 在上面按下滑鼠右鍵 選擇 儲存格格式 就可以改變儲存格的格式 對齊或填色 其中關於資料欄位的儲存格格式 像是要不要顯示貨幣符號 要顯示幾位小數位 除了按右下滑鼠鍵 選擇 儲存格格式 再點選 數字 標籤去修改之外 也可以按下 工具鈕 進入 樞紐分析表欄位 對話方塊 按下 數值 按鈕 同樣可以修改 43 欄位設定 小計選取列欄位 第一季 分類標籤 按下樞紐分析表工具列上的 工具鈕 開啟 樞紐分析表欄位 對話方塊 44 欄位設定 在 小計 欄位點選 自訂 選項 再選擇 加總 的方式 選擇好後按下 確定 按鈕 則在每一個 季 分類標籤下 都會顯示整季資料的加總小計 45 欄位設定 46 欄位設定 排序在 自動排序選項 欄位 選擇 遞減 選項 而 使用欄位 就是 廠牌 設定好後按下 確定 按鈕 47 欄位設定 回到 樞鈕分析表欄位 對話方塊後 再按下 確定 按鈕 回到工作表後 廠牌 分類的標籤 會按字母順序由大排到小 48 設定表格選項 在樞紐分析表的最右側和最下方 總有個 總計 欄位 這是Excel自動產生的 用來顯示每一欄和每一列加總的結果 如果不需要這兩個部分 要如何修改 另外 樞紐分析表中空白的部分表示沒有資料 不妨加上一個破折號 表示該欄位沒有資料 以上這些需求 都可以在樞紐分析表的 表格選項 裡修改 49 10 4 2設定表格選項 在 樞紐分析表 工具列上 按下 樞紐分析表 按鈕 於選擇中選擇 表格選項 開啟 樞鈕分析表選項 對話方塊 取消勾選 欄總計 和 列總計 選項 則最右邊和最下面就不會出現總計資訊 50 設定表格選項 另外勾選 若為空值 顯示 選項 在欄位裡輸入 則沒有資料的欄位 會顯示一個 破折號 51 改變報表格式 除了一一改變儲存格的格式外 針對樞紐分析表 Excel也提供了像自動格式那樣的格式組合 叫做 報表格式 它提供了一些事先搭配好的格式組合 除了儲存格底色 字型的不同外 它還會改變資料欄位 欄欄位 列欄位的位置 52 改變報表格式 在 樞紐分析表 工具列按下 工具鈕 從