網頁

2016年12月25日 星期日

Excel 實作 (4) - 樞紐分析表、樞紐分析圖製作

教學目標:
1. 建立樞紐分析表
2. 建立樞紐分析圖
3. 保護活頁簿
4. 保護工作表

資料下載:
請先下載資料檔:
01王小明_加班時數統計.xlsx
對下載的檔案,選 「各部門加班時數統計」此工作表, 按右鍵/移動或複製。
把此工作表複製到「 01王小明_個人成績與班級平均比較_統計圖.xlsx」 的最後,步驟如下:



(對下載的檔案,按右鍵/重新命名,改成自己的座號和姓名。使用 Excel,檔案/開啟舊檔,打開此檔案。或者直接對此檔連續按二下,呼叫 Excel 程式來打開。)

建立樞紐分析表
1.插入
2.樞紐分析表
3.選取表格或範圍
    確認表格範圍為A1:H46, 按確定鈕, 以建立樞紐分析表。
    可按右邊的摺疊鈕,將「建立樞紐分析表」交談視窗縮小,以便 選取範圍。

樞紐分析表欄位清單工作窗格, 拖曳月份欄位至欄標籤區, 設定月份為欄位。
參照上述步驟,分別拖曳部門名稱標籤區、加班時數區。
研發一課在1月份的加班時數總計為 36小時。

值此欄位預設為加總,但也可以改成計數,方法如下:
1.按「加總- 加班時數」
2.選「值欄位設定」
3.改成項目個數
結果為:研發一課在1月分加班的次數有5筆。

建立樞紐分析圖

 Excel 2010:
樞紐分析表工具 / 選項   工具鈕,再按樞紐分析圖鈕,以開啟插入圖表交談窗。
 Excel 2013:
樞紐分析表工具選項的工具鈕,再按樞紐分析圖鈕,以開啟插入圖表交談窗。

點選直條圖,群組直條圖,按確定鈕,以建立樞紐分析圖。
適當調整圖表的大小與位置。

 Excel 2010:
樞紐分析圖工具 版面配置的標籤區,按圖表標題鈕,選圖表上方,設定圖表上方顯示標題。

 Excel 2013:
樞紐分析圖工具/ 設計,新增圖表項目鈕,按圖表標題鈕,選圖表上方,設定圖表上方顯示標題。

在標題欄輸入文字 "加班時數統計",原來的"圖表標題",請刪除。

Excel 2010:
樞紐分析圖工具 的版面配置標籤區, 按資料標籤鈕, 選終點外側, 設定直條圖上方顯示加班時數的數值。

 Excel 2013:
按圖表右邊的 + ,選資料標籤,選終點外側

設定圖表樣式:

如何更改 圖表區的填滿效果,如用  白色至淺綠色漸層的填滿色彩?
選整個圖表,樞紐分析圖工具
樞紐分析圖工具/格式/ 圖案填滿/漸層 / 其他漸層



將中間二個漸層停駐點刪除( 按住漸層停駐點,往外移),祇剩下左右二個漸層停駐點
按最右邊的漸層停駐點,顏色選 「淺綠」。


白色到淺綠 漸層 的結果:

樞紐分析表、圖的重點整理
跟小計的比較
小計要先排序,樞紐分析表不用
小計祇能作欄的小計,樞紐分析表可以作欄、列的小計
小計來源資料有異動,小計結果會自動更新。樞紐分析表來源資料有異動,結果不會自動更新。要按右鍵,重新整理。

跟圖表的比較
圖表來源資料有異動,統計圖會自動更新
樞紐分析表、圖來源資料有異動,會自動更新嗎?不會。對樞紐分析圖按右鍵,更新資料。

(資二1沒有要求作這部分,老師有示範。資二3此部分都沒講。資二2 有要求做此部分的功能)
選 「各部門加班時數統計」此工作表。
選第5列 ,按右鍵,選「複製」。

移到第18列,按右鍵,選「插入複製的儲存格」,在第18列的上方插入複製的儲存格。
新的資料在第18列,你會發現,資料沒有排序 ,研發二課下面 有研發一課。


選工作表1,就是有樞紐分析表的那個工作表,樞紐分析表和樞紐分析圖,沒有自動更新。‧
選 樞紐分析表,按右鍵,重新整理。

或者 選 樞紐分析圖,按右鍵,更新資料。

上述這二種方法,都可以同時更新 樞紐分析表和樞紐分析圖。最後結果如下:
因為研發一課在1月份多增加一筆加班資料,結果  研發一課在1月份的 「計數 - 加班時數」由 5 筆變成 6筆。


如何保護活頁簿?
選按『檔案/另存新檔』選項, 以開啟另存新檔交談窗。
工具鈕, 選一般選項,開啟一般選項交談窗。

防寫密碼欄輸入防寫密碼(如 12 ),並按確定鈕。(如果在保護密碼 輸入密碼,當老師要打開你的檔案,要輸入密碼,會造成無法評分!)
重新輸入上述步驟所設定的密碼(如 12 ), 並按確定鈕。
按儲存位置下拉式方塊,切換至存放檔案的資料夾, 直接選 「01王小明_加班時數統計.xlsx 」,按儲存鈕。會出現是否要取代的訊息,按

如何保護工作表?
1.先選擇「各部門加班時數統計」此工作表。
2.選 校閱
3.在變更區, 按保護工作表鈕, 以開啟保護工作表交談窗。
4.在此欄輸入密碼( 如 12 ),設定要取消保護工作表的密碼。 
5.取消勾選所有核取方塊,並按確定鈕, 設定使用者無法編輯工作表。

重新輸入上述步驟所設定的密碼,如 12 , 並按確定鈕,完成工作表的保護。


按儲存檔案鈕, 將檔案儲存。
檔名為:  01王小明_加班時數統計.xlsx,請務必改成自己的座號和姓名。上傳到老師的classroom。

3 則留言 :

  1. 您好,請問您是否可以授權此教學檔案,目前工作上遇到瓶頸,要分析各單位每月加班差異比較,此篇教學符合我目前的需求,不知道您是否願意?非常感謝您~

    回覆刪除