Excel 試算表進階操作 (2)

Excel試算表進階操作(2) 主講人：劉威良臺南大學資訊教育所

表格與圖表應用 • 合併列印範例練習 • 資料剖析 • 資料資料剖析 • 樞紐分析表及圖報表 • 表格樞紐分析表及圖報表

題目要求(1/3) • 以Excel開啟一名為Student.dbf之資料庫檔 • 檔案／開啟舊檔／檔案類型：dBase檔案／雙按Student.dbf即可，並另存成excel之檔案類型 • 全表欄寬請設定為：最適欄寬 • 全選儲存格／雙按欄邊界即可 • 將「班級座號」欄，分離為「班級」「座號」 • 選取C、D欄／右鍵／插入 • C1輸入「班級」，D1輸入「座號」 • C2輸入「＝mid(B2,1,3)」從B2欄位之第1位數字開始取3位數給C2儲存格 • D2輸入「＝mid(B2,4,2)」從B2欄位之第4位數字開始取2位數給D2儲存格

題目要求(2/3) • 新增一欄位，辨別學生之性別 • 選取H欄／右鍵／插入 • 於H1輸入「性別」 • 於H2輸入=IF(MID(G2,2,1)=“1”,“男”,“女”) • 由於身份証號碼為「英數字」混合模式，Excel會將其判斷為文字，故MID(G2,2,1)=“1”中之1要用 “ ” 引起來以文字模式處理。 • 雙按H2之「填滿控點」向下複製公式 • 將出生日期由西元年，修正為民國年 • 選取儲存格／ ctrl + 1 ／數值標籤／類別：自訂／類型：e/mm/dd ／確定

題目要求(3/3) • 將工作表中，住在台北縣市，且為102班的學生資料，套印至信件中輸出。 • 資料篩選：資料／篩選／自動篩選（各欄出現下拉式選單鈕） • 於住址欄點選「自訂」，如下圖所示： • 並於班級欄點選「102」 • 複製篩選結果，貼至新工作表，命名為「篩選資料」並存檔離開 • 啟動word • 開啟合併列印工具列 • 開啟信件檔，進行合併

資料篩選結果

合併畫面(1/2) 1. 3. 4. 2. 5. 6.

合併畫面(2/2) 稱謂的合併 1. 2. 3. 4.

資料剖析 (1/6) • 資料剖析，可將欄位資料剖析為更細的欄位資料 • 如：將生日資料，剖析成以「出生年」、「出生月」、「出生日」表示的三欄式資料。 • 操作步驟如下： • 選取欲剖析之資料點選「資料」資料剖析

資料剖析 (2/6) 1. 選取要剖析之資料欄(一次只可以轉換一個欄) 2. 資料資料剖析

資料剖析 (3/6) 3. 使用「分隔符號」區分每一個欄位下一步 4. 分隔符號處勾選「其他」並輸入「/」下一步

資料剖析 (4/6) 5. 點選第一欄下一步 6. 設定資料格式為「文字」下一步

資料剖析 (5/6) 7. 點選第二欄下一步 8. 設定資料格式為「文字」 下一步 9. 點選第三欄，設定同上

資料剖析 (6/6) 10. 設定完成後，資料畫面如下所示。 11. 選取儲存格後，設定如下

樞紐分析表及圖報表 (1/17) • 樞紐分析表：可從外部 Excel 的多種來源，總結並分析資料之交互式、交叉列表的 Excel 報告，例如資料庫記錄。 • 樞紐分析圖：提供資料互動分析的圖表，如樞紐分析表。藉由拖曳欄位，及顯示或隱藏欄位中的項目變更資料檢視、查看詳細資訊的不同級別或重新組織圖表版面配置。 • 以「銷售狀況與統計表」為立，建立樞紐分析表及圖報表 • 操作步驟如下： • 顯示資料以季為單位 • 選取欲建立樞紐分析表及圖報表之資料點選「資料」樞紐分析表及圖報表

樞紐分析表及圖報表 (2/17) • 快速調整資料欄寬為「最適欄寬」 • 全選表格，拖曳調整欄寬度至可容納所有文字之大小 • 指標指向欄與欄之間呈雙向指標時雙按滑鼠左鍵即可快速調整資料欄寬為「最適欄寬」

樞紐分析表及圖報表 (3/17) • 選取資料範圍 • 點選任意儲存格滑鼠指標指向「選取目前範圍」鈕 • 點按「選取目前範圍」鈕，即可選取有資料的儲存格

樞紐分析表及圖報表 (4/17) • 建立樞紐分析表(1) • 資料樞紐分析表及圖報表建立「樞紐分析表」

樞紐分析表及圖報表 (5/17) • 建立樞紐分析表(2) • 樞紐分析表和樞紐分析圖精靈會自動選取資料範圍

樞紐分析表及圖報表 (6/17) • 建立樞紐分析表(3) • 將樞紐分析表產生在「新工作表」 • 並設定「版面設置」與「選項」

樞紐分析表及圖報表 (7/17) • 建立樞紐分析表(3-1版面配置設定) • 拖曳產品名稱至「列」 • 拖曳交易月至「欄」 • 拖曳銷售數量至「資料」

樞紐分析表及圖報表 (8/17) • 建立樞紐分析表(3-2選項設定) • 修改名稱為「銷售量樞紐分析表 • 設定資料若為空值時，顯示為「0」

樞紐分析表及圖報表 (9/17) • 樞紐分析表完成圖 • 出現樞紐分析表欄位清單與樞紐分析表工具列 • 使用工具列，隱藏欄位清單 • 修改資料表標籤名稱

樞紐分析表及圖報表 (10/17) • 「以季為單位」顯示資料 • 選取「1、2、3」月之儲存格按右鍵群組及顯示群組資料點選「群組」 以此類推，將「4、5、6」「7、8、9」「10、11、12」月群組

樞紐分析表及圖報表 (11/17) • 修改群組名稱 • 群組完，呈現資料組1、資料組2、資料組3、資料組4 • 點選資料組1於資料編輯列修改文字為「第一季」 • 以此類推，修改資料組2為第二季、資料組3為第三季……

樞紐分析表及圖報表 (12/17) • 調整顯示畫面，只以季資料顯示，不顯示月份資料 • 雙按第一季，可將月份折疊隱藏 • 以此類推，將四季之月份資料隱藏，結果如下圖所示

樞紐分析表及圖報表 (13/17) • 修改欄位清單按鈕名稱 • 雙按「加總的銷售數量」鈕修改為「總銷售量」 • 雙按「交易月2」修改為「季交易」 • 雙按「交易月」修改為「月交易」

樞紐分析表及圖報表 (14/17) • 以欄位清單按鈕決定欲顯示之資料，如 • 產品名稱資料、季交易資料、月交易資料

樞紐分析表及圖報表 (15/17) • 建立樞紐分析圖(以銷售額為主，方法如前) • 由於方才已建立樞紐分析表，故第二次建立時，會出現詢問方塊，如下圖所示，點按「否」即可。

樞紐分析表及圖報表 (16/17) • 未以季顯示之分始樞紐分析圖如下

樞紐分析表及圖報表 (17/17) • 群組資料以季為單位 • 分析圖出現「季交易」之欄位按紐，如下圖 • 移除「月交易」之欄位按紐，向外拖曳即可

參考資料來源 • Excel 2000實力養成暨評量 • Excel 2002實力養成暨評量 • 電腦軟體應用乙級術科解題秘笈 • 乙級電腦軟體應用--術科得分常識

The End

Excel 試算表進階操作 (2)