05223AA15.indd

Size: px

Start display at page:

Download "05223AA15.indd"

苗高
7 years ago
Views:

1 CHAPTER 15 第 15 章試算表的應用與管理試算表的應用與管理章將介紹樞紐分析資料剖析與文件輸出, 以及活頁簿與工作表的保護等試算本表的進階功能, 讓同學對 Excel 的使用有更進一步的瞭解單元樞紐分析有樞紐, 統計資料快速搞定樞紐的中文意思是比喻重要的關鍵 Excel 的樞紐分析功能, 可從多筆資料中, 彙整及統計出關鍵的資訊, 讓使用者快速掌握資料的重點例如在圖 15-1 多筆技能檢定結果的資料中, 若要整理出近 3 年的檢定平均通過率, 雖然可利用前面教過的公式與函數, 先計算每年各職種的檢定通過率, 再計算各年度的平均通過率, 但報表製作人必須輸入多個相關的公式或函數, 最後才能統計出所需的資訊, 因此相當費時若改用樞紐分析功能, 則可快速從原始資料中, 彙整出近 3 年技能檢定的平均通過率資料, 省時又省力原資料表樞紐分析表 ( 內文範例 "ch15-1.xls") 圖 15-1 使用樞紐分析的範例 381

2 計算機概論樞紐分析表的建立為了方便使用者製作樞紐分析表,Excel 提供樞紐分析表和樞紐分析圖精靈圖 15-2 是依照此精靈的引導, 依序設定報表類型資料來源位置及版面配置, 所製作出來的加班時數統計樞紐分析表原資料表選資料 / 樞紐分析表及圖報表 1 step 設定報表類型選取資料的來源, 以及要建立的報表類型 2 step 設定資料來源設定樞紐分析表的資料範圍 3 step 選擇位置選擇樞紐分析表要顯示於原工作表中或另新建一個工作表 4 step 設定版面分頁欄位欄欄位資料欄位設定樞紐分析表各欄位的資料 ( 例如指定部門名稱作為列欄位 ) 列欄位樞紐分析表工作表中的欄位 382 圖 15-2 樞紐分析表和樞紐分析圖精靈交談窗 ( 內文範例 "ch15-2.xls")

3 第 15 章試算表的應用與管理單元 3 樞紐分析表的編修如果需要調整樞紐分析表中的統計項目, 可新增或刪除樞紐分析表中的欄位, 來更改設定圖 15-3 為增刪欄位的範例選檢視 / 工具列 / 樞紐分析表, 可開啟 ( 或關閉 ) 樞紐分析表工具列新增樞紐分析表欄位按樞紐分析表工具列上的, 拖曳欲新增的欄位至樞紐分析表新增 " 月份 " 欄位刪除 " 月份 " 欄位 ( 內文範例 "ch15-3.xls") 刪除樞紐分析表欄位拖曳欲刪除的欄位至工作表的空白處圖 15-3 增刪樞紐分析表欄位的範例密技報你知按樞紐分析表工具列的報表格式, 或選格式 / 自動格式設定, 可套用 Excel 內建的表格樣式, 來美化樞紐分析表 383

4 計算機概論樞紐分析表摘要方式的變更摘要方式就是樞紐分析表進行資料統計的方式 ( 如加總平均值等 ) 在建立樞紐分析表時, 預設是以加總方式來統計資料, 我們可視實際需要更改摘要方式, 例如計算平均值 ( 圖 15-4) 雙按資料欄位標籤或按樞紐分析表工具列上的欄位設定樞紐分析表的左上角會顯示摘要方式設定資料欄位的摘要方式 ( 如平均值 ) 圖 15-4 變更欄位摘要方式的範例 ( 內文範例 "ch15-4.xls") 樞紐分析圖的建立利用樞紐分析功能製作出來的統計表, 可轉成以圖的方式呈現, 只要按圖表精靈鈕, 即可快速產生如圖 15-5 所示的統計圖建立樞紐選取樞紐分析表中任一分析圖儲存格, 按圖表精靈樞紐分析圖的編輯, 如圖表類型位置格式設定等, 與本書第 13 章介紹的圖表編修方式相同欄位按鈕按此可調整樞紐分析圖的欄位項目 ( 內文範例 "ch15-5.xls") 圖 15-5 依樞紐分析表中的資料建立樞紐分析圖的範例 384 密技報你知選取樞紐分析圖後, 按樞紐分析表工具列的樞紐分析圖, 選隱藏樞紐分析圖欄位按鈕, 在列印樞紐分析圖時, 就不會印出圖中的欄位按鈕

5 第 15 章試算表的應用與管理單元 3 1 建立樞紐分析表變更摘要方式開啟檔案 "Ex15-p1.xls", 依照下列步驟操作, 最後將檔案另存為 "Ans15-p1.xls" 1 選取儲存格 A2 ~ G 選按資料 / 樞紐分析表及圖報表選項 3 點選樞紐分析表選項按鈕, 按下一步鈕 3 4 確認資料來源為儲存格 A2 ~ G38, 按下一步鈕 5 點選新工作表選項按鈕, 4 將樞紐分析表建立在新的工作表中 5 6 按版面配置鈕拖曳年度項目至列欄位 8 分別拖曳檢定類別項目至 7 8 欄欄位通過率項目至資料欄位, 按確定鈕 9 按完成鈕, 即可建立出樞紐分析表 385

6 計算機概論按樞紐分析表工具列上的欄位設定鈕 11 在摘要方式列示窗, 選平均值, 按確定鈕, 將摘要方式由預設的加總更改為平均值選取第 A ~ D 欄, 並雙按 A 欄欄名的右框線, 將第 A ~ D 欄欄寬調整至最適欄寬選取儲存格 B5 ~ D8, 選按格式/ 儲存格選項 14 在類別列示窗, 選百分比 15 在小數位數欄輸入 "1", 按確定鈕選取儲存格 D4, 輸入 " 平均 ", 按鍵後, 儲存格 A8 的 " 總計 " 也會跟著更改為 " 平均 "

7 第 15 章試算表的應用與管理單元 3 1. 在 Excel 中, 若要刪除樞紐分析表中的欄位, 應先選取該欄位後, 接著進行下列哪一項操作? (A) 選按編輯 / 清除 / 內容選項 (B) 按鍵 (C) 選按編輯 / 清除 / 全部選項 (D) 拖曳欲刪除的欄位至工作表的空白處 2. 在 Excel 中, 若要將樞紐分析表轉成統計圖, 應先選取樞紐分析表中的任一儲存格, 再按下列哪一個按鈕? (A) (B) (C) (D) 3. Excel 的樞紐分析表是由資料分頁等欄位組成 15-2 資料剖析與文件輸出 Excel 提供有資料剖析將工作表存成網頁格式及將活頁簿內容以寄送出去等功能, 本節將一一介紹資料剖析為方便進行資料的比較計算等處理,Excel 提供資料剖析功能, 可用來將存放於單一儲存格中的資料, 分欄存放至多個儲存格例如圖 15-6 是將 " 出生年月日 " 欄中的資料, 利用在選取要剖析的資料範圍時, 一次只能選取一欄此項功能剖析存放於 6 個欄位中資料剖析選資料 / 資料剖析利用資料剖析功能, 將出生日期, 依年月日分開存放圖 15-6 資料剖析的範例 ( 內文範例 "ch15-6.xls") 387

8 計算機概論將工作表存成網頁格式要將 Excel 工作表製作成網頁形式, 以瀏覽器 ( 如 IE) 讀取, 可以選按檔案 / 另存新檔選項, 將工作表以 htm html 等網頁格式儲存此外, 透過另存新檔交談窗, 也可將檔案儲存成範本檔或純文字檔等, 如圖 15-7 所示選檔案 / 另存新檔副檔名說明 xls xlt txt 註 Excel 預設的活頁簿格式範本檔案的格式只能儲存純文字, 不同儲存格間的資料會以 Tab 字元 ( 即 ) 分隔按此可選擇檔案的儲存格式圖 15-7 Excel 提供的儲存檔案類型利用傳送工作表如果電腦中安裝有電子郵件軟體 ( 如 Outlook), 我們可直接按 Excel 提供的電子郵件鈕 ( 圖 15-8), 寄送工作表給需要使用的相關人員用傳送工作表按電子郵件或選檔案 / 傳送到 / 郵件收件者收到的 , 會直接顯示工作表的內容按傳送這張工作表鈕, 可寄出工作表圖 15-8 利用傳送工作表 ( 內文範例 "ch15-7.xls") 388 註 Excel 2007( 含 ) 之後的版本預設副檔名為 ".xlsx", 但這些較新版的軟體仍可存取 ".xls" 的文件密技報你知選檔案 / 另存成網頁, 也可將活頁簿內容以網頁格式儲存

9 第 15 章試算表的應用與管理單元 3 1. 利用下列哪一項功能, 可以將某一儲存格中的資料分割存放在相鄰的儲存格中? (A) 設定自動篩選 (B) 匯入外部資料 (C) 資料排序 (D) 資料剖析 2. 試算表必須先儲存成下列哪一種格式, 才能發布到網路上供瀏覽者瀏覽? (A) 網頁 (*.htm; *.html) (B) 工作環境 (*.xlw) (C) 活頁簿 (*.xls) (D) 範本 (*.xlt) 3. Microsoft Excel 2003 預設的活頁簿檔案格式為 15-3 活頁簿與工作表的保護 Excel 提供有活頁簿及工作表保護的功能, 我們可善用這兩項功能, 對重要資料, 提供更嚴密的保護活頁簿的保護使用者可以設定活頁簿的保護密碼, 以保護重要的檔案不被任意開啟 ; 或設定防寫密碼, 以防止不相干的人任意更改活頁簿中的資料內容 ( 圖 15-9) 在儲存選項交談窗中, 刪除保護密碼及防寫密碼欄中已輸入的密碼, 即可取消密碼設定選檔案 / 另存新檔, 按工具, 選一般選項設定保護密碼設定防寫密碼輸入正確保護密碼, 才可開啟檔案圖 15-9 活頁簿的保護輸入正確防寫密碼, 才可編修檔案 ; 若不知道密碼, 只能以唯讀的方式開啟檔案 389

10 計算機概論工作表的保護在 Excel 中, 透過保護工作表的設定 ( 圖 15-10), 可賦予使用者對工作表的操作權限 ( 例如只能設定儲存格格式插入欄等 ), 以避免使用者任意修改工作表的內容選工具 / 保護 / 保護工作表若要取消工作表的保護, 可選工具 / 保護 / 取消保護工作表, 再輸入保護密碼即可設定工作表可以操作的項目 ( 勾選代表允許 ) 圖保護工作表交談窗如果被保護的工作表, 需要開放部分儲存格供使用者編修, 可透過圖之交談窗來設定允許編輯的儲存格範圍選工具 / 保護 / 允許使用者編輯範圍允許使用者編輯的儲存格範圍新增允許使用者編輯的儲存格範圍按保護工作表, 可設定使用者對工作表的操作權限圖允許使用者編輯範圍交談窗 2 剖析資料保護工作表開啟檔案 "Ex15-p2.xls", 依照下列步驟操作, 最後將檔案另存為 "Ans15-p2.xls" 1 選取儲存格 F2 ~ F15, 再選按資料/ 資料剖析選項 2 點選固定寬度選項按鈕, 按下一步鈕

11 第 15 章試算表的應用與管理單元將指標分別移至要分欄的位置單按一下, 並按下一步鈕 4 在目標儲存格欄, 輸入 "G2", 並按完成鈕 5 5 按確定鈕, 將剖析後的資料取代原 G ~ L 欄儲存格 6 選按檔案 / 另存新檔選項 7 在檔案名稱欄輸入 "Ans15-p2" 6 8 按工具鈕, 選一般選項將儲存格 F2 ~ F15 的資料, 分欄存至多個儲存格了! 在保護密碼欄輸入密碼 ( 如 15069AB), 並按確定鈕 10 在此欄, 重新輸入步驟 9 所設定的密碼, 並按確定鈕按儲存鈕 1. 小英利用 Excel 來記錄零用錢的收支明細, 她不希望檔案被他人開啟, 請問她可以利用下列哪一項功能來達成? (A) 設定活頁簿的保護密碼 (B) 設定活頁簿的防寫密碼 (C) 設定只允許使用者編輯特定儲存格範圍 (D) 設定保護工作表的密碼 2. 在儲存 Excel 檔案時, 設定防寫密碼的作用為 (A) 避免檔案被刪除 (B) 避免他人開啟檔案 (C) 防止他人編修檔案內容 (D) 隱藏檔案 3. 在 Excel 中, 若要限制使用者對工作表的操作權限, 且設定僅能編輯部分儲存格的資料時, 應透過下列哪些功能來設定? 保護工作表允許使用者編輯範圍活頁簿的保護密碼活頁簿的防寫密碼 391

12 計算機概論 3 善用樞紐統計快手 - 加班時數摘要本範例以製作加班時數摘要為例, 讓同學練習以下 5 項 Excel 常用的功能 1. 建立樞紐分析表 2. 建立樞紐分析圖 3. 保護工作表 4. 保護活頁簿 5. 寄送建立樞紐分析表詳細步驟參考 1.1 開啟檔案 "Ex15-1.xls" 1.2 選取儲存格 A2 ~ H17, 選按資料/ 樞紐分析表及圖報表選項, 以開啟樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 1 交談窗點選樞紐分析圖 ( 及樞紐分析表 ) 選項按鈕, 按下一步鈕, 以開啟樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 2 交談窗 392

13 第 15 章試算表的應用與管理單元確認資料來源為儲存格 A2 ~ H17, 按下一步鈕, 以開啟樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 3 交談窗點選新工作表選項按鈕, 將樞紐分析表及樞紐分析圖分別放置在新的工作表中 1.6 按版面配置鈕, 以開啟樞紐分析表和樞紐分析圖精靈 - 版面配置交談窗拖曳部門名稱項目至列欄位分別拖曳職稱項目至欄欄位 ; 加班時數項目至資料欄位按確定鈕, 返回樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 3 交談窗按完成鈕, 完成樞紐分析圖表的建立 2. 建立樞紐分析圖詳細步驟參考 Excel 會自動產生兩個工作表, 一個用來存放樞紐分析圖 ; 一個用來存放樞紐分析表在圖表區按右鍵, 選按圖表類型選項, 以開啟圖表類型交談窗 393

14 計算機概論 2.2 在圖表類型列示窗中, 點選直條圖選項在副圖表類型區, 選群組直條圖, 並按確定鈕 2.4 在圖表區按右鍵, 選圖表選項, 以開啟圖表選項交談窗在標題標籤中的圖表標題欄輸入 " 加班時數摘要 ", 設定圖表的標題

15 第 15 章試算表的應用與管理單元切換至資料標籤標籤, 勾選內容核取方塊, 設定要在直條圖顯示所代表的內容 ( 即加班時數 ), 按確定鈕設定圖表樣式 : 圖表標題華康中圓體 30 粗體深藍數值座標軸 Arial 20 背景牆白色填滿色彩圖表區白色至淺綠色漸層的填滿色彩保護工作表詳細步驟參考 3.1 切換至 Sheet1 工作表, 並選按工具 / 保護 / 保護工作表選項, 以開啟保護工作表交談窗 395

16 計算機概論 3.2 在此欄輸入密碼 ( 如 05223AA), 設定要取消保護工作表的密碼取消勾選所有核取方塊, 並按確定鈕, 設定使用者無法編輯工作表 3.4 在此欄, 重新輸入步驟 3.2 所設定的密碼, 並按確定鈕, 完成工作表的保護保護活頁簿詳細步驟參考 4.1 選按檔案 / 另存新檔選項, 以開啟另存新檔交談窗 4.2 按工具鈕, 選一般選項, 開啟儲存選項交談窗在防寫密碼欄輸入防寫密碼 ( 如 AA32250), 並按確定鈕 396

17 第 15 章試算表的應用與管理單元在此欄, 重新輸入步驟 4.3 所設定的密碼, 並按確定鈕在檔案名稱欄輸入 "Ans15-1", 按儲存鈕 5. 寄送詳細步驟參考 5.1 切換至 Chart1 工作表, 按職稱欄旁的下拉式按鈕, 取消勾選助理工程師副工程師資深工程師核取方塊, 並按確定鈕, 設定樞紐分析圖僅顯示研發科的加班時數加總 5.1 樞紐分析圖變更後, 樞紐分析表也會跟著變更喔! 按電子郵件鈕在收件者欄輸入收件者的電子郵件, 在主旨欄輸入郵件主旨, 並按傳送這份圖表鈕, 將樞紐分析圖寄送給收件者 397

18 MENU 選擇題作答處紅色底線表示該題為技能檢定考題 1. 在 Excel 中, 下列何者非樞紐分析表中可設定的欄位? (A) 列 (B) 資料 (C) 欄 (D) 函數 2. 在 Excel 中, 下列哪一個精靈, 可以逐步引導使用者針對工作表中的資料, 進行分類統計的工作? (A) 圖表精靈 (B) 樞紐分析表和樞紐分析圖精靈 (C) 匯入字串精靈 (D) 資料剖析精靈 3. 關於 Excel 的樞紐分析表敘述, 下列何者有誤? (A) 樞紐分析表的資料來源可以來自外部資料庫 (B) 可將產生的樞紐分析表存放在已存在的工作表中 (C) 使用者不能自行設定資料欄位的摘要方式 (D) 可將建立的樞紐分析表另存於新的工作表中 4. 在 Excel 中, 下列哪一個樞紐分析表工具列中的按鈕, 可以用來變更資料欄位的摘要方式? (A) (B) (C) (D) 5. 在 Excel 中, 建立樞紐分析表時, 預設的摘要方式為何? (A) 加總 (B) 平均值 (C) 最大值 (D) 最小值 6. 若要將 Excel 儲存成範本格式, 其副檔名為 (A)xls (B)doc (C)htm (D)xlt 7. 在試算表軟體 Microsoft Excel 中, 如果想要將儲存格中一個日期的內容 57/6/19 拆開成年月日分別置於三個儲存格中, 並抽離 / 符號, 可以使用以下哪一個功能? (A) 規劃求解 (B) 資料剖析 (C) 資料驗證 (D) 段落重排 8. 曉玲想將某一 Excel 工作表中的地址欄位, 依區縣 / 市路 / 街樓分開存放在四個不同的儲存格中, 她該怎麼做最有效率? (A) 利用填滿控點功能 (B) 利用資料剖析功能 (C) 利用函數 (D) 手動重新輸入 9. 若要將 Excel 的工作表儲存成網頁檔, 應選擇下列哪一種檔案格式? (A).xls 格式 (B).doc 格式 (C).htm 格式 (D).exe 格式 10. 下列有關 Excel 的敘述, 何者不正確? (A)Excel 2007 預設的副檔名為.xlsx (C)Excel 2003 預設的副檔名為.xlt (B) 可將工作表儲存成網頁檔 (D) 可將工作表另存成.html 檔案格式 11. 利用 Excel 的另存新檔功能, 無法將檔案儲存成下列哪一種格式? (A)Microsoft Word 檔案格式 (.doc) (B) 網頁檔 (.html) (C) 純文字檔 (.txt) (D) 範本檔案 (.xlt) 12. 在 Excel 中, 若想使用電子郵件功能, 將工作表或活頁簿檔案傳送給相關的人, 應先按下列哪一個按鈕? (A) (B) (C) (D) 13. 為 Excel 活頁簿設定保護密碼, 作用為何? (A) 隱藏檔案 (B) 避免他人刪除檔案 (C) 防止他人複製檔案 (D) 防止他人任意開啟檔案 398

19 14. 如果希望被保護的 Excel 工作表, 能夠開放部分儲存格供使用者編輯, 必須透過下列哪一個交談窗來進行設定? (A) 保護工作表 (B) 另存新檔 (C) 允許使用者編輯範圍 (D) 小計 15. 在 Excel 中, 若要保護儲存格的內容, 除了必須在儲存格格式 / 保護交談窗中勾選鎖定核取方塊外, 還需選按哪一個選項進行相關設定, 才能避免資料被任意修改? (A) 工具 / 共用活頁簿 (B) 工具/ 錯誤檢查 (C) 工具/ 保護 / 保護工作表 (D) 資料/ 表單實作題 1. 開啟檔案 "Ex15-a.xls", 利用保護工作表功能設定只能編輯個人成績查詢工作表中的特定儲存格後, 將結果另存為 "Ans15-a.xls" 提示 : (1) 透過允許使用者編輯範圍交談窗, 設定允許使用者編輯的範圍為儲存格 F3 (2) 利用保護工作表功能, 分別為成績總表個人成績查詢工作表, 設定保護密碼為 "05223AA"( 需注意大小寫 ) (3) 在儲存格 F3 輸入要查詢的學號 ( 如 13101), 確認可編輯該儲存格內容 (4) 編輯儲存格 F3 以外的任一儲存格 ( 如儲存格 B5), 確認無法編輯儲存格內容 (5) 利用保護活頁簿功能, 設定保護密碼為 "05233BB" 399

21 MENU 2. 開啟檔案 "Ex15-b.xls", 依照提示內容, 利用樞紐分析表和樞紐分析圖精靈, 建立樞紐分析表, 並在 Excel 工作表中輸入公式與函數, 完成如下圖的結果後, 將結果另存為 "Ans15-b.xls" 類別數值數值百分比小數位數千分位符號 - 提示 : (1) 利用樞紐分析表和樞紐分析圖精靈, 製作樞紐分析表 ( 資料範圍為儲存格 A1 ~ E75) (2) 將工作表中的 " 產品名稱 " " 單價 " " 交易月 " " 數量之總計 " 等欄位分別指定作為樞紐分析表的列列欄資料等欄位的資料來源 (3) 在樞紐分析表工具列上, 選按樞紐分析表 / 表格選項選項, 取消勾選欄總計列總計核取方塊, 並在若為空值, 顯示欄輸入 "0" (4) 在 " 產品名稱 " 欄按右鍵, 選按欄位設定選項, 點選無選項按鈕, 設定單價欄不進行小計運算 (5) 在 " 交易月 " 欄按右鍵, 選按群組及顯示詳細資料 / 群組選項, 在間距值欄輸入 "3", 設定以季為單位, 來呈現交易數量 (6) 分別將儲存格 C4 D4 E4 F4 的文字更改為第一季第二季第三季第四季, 並在儲存格 G4 H4 I4, 輸入文字 " 平均數量 " " 銷售額 " " 銷售百分比 ", 再適當調整各欄欄寬 (7) 使用公式計算銷售額 ( 銷售額 = ( 第一季 + 第二季 + 第三季 + 第四季 ) 單價 ) (8) 使用 SUM 函數在儲存格 H21 計算銷售總額 (9) 使用 ROUND 函數與公式, 計算平均數量 ( 平均數量 = ( 第一季 + 第二季 + 第三季 + 第四季 ) 4), 計算結果必須四捨五入至小數第 2 位 ; 及計算銷售百分比 ( 銷售百分比 = 銷售額總銷售額 ), 計算結果必須四捨五入至小數第 4 位 400 (10) 依上圖設定各欄位的資料類別

23 3 第 1 招選格格快速搞定 - 神奇的快速鍵! ( 可配合 11-2 節介紹 ) 想要快速選取同欄同列或矩形範圍中, 含有資料的相鄰儲存格嗎? 只要按 + + 方向鍵, 或按 + + 鍵即可一次搞定 ( 如下圖 ) 選取儲存格 A2, 選取儲存格 A2, 選取儲存格 A2, 按 + + 按 + + 按 + + 同列同欄矩形範圍選取同列含有資料的相鄰儲存格快速選取儲存格的範例選取同欄含有資料的相鄰儲存格選取含有資料的相鄰矩形範圍儲存格第 2 招線上博士 - 函數用法大全 ( 可配合 12-2 節介紹 ) Excel 提供的函數相當多, 除了第 12 章介紹的函數之外, 如果我們想要瞭解或使用更進階的相關函數, 可使用 Excel 提供的線上說明功能, 查詢各個函數的語法範例與用途說明選取欲輸入函數的儲存格, 按插入函數選取函數類別函數語法選取要使用的函數函數的語法與範例說明 Excel 的線上說明功能

24 第 3 招簡化操作有撇步 - 巨集的使用 ( 可配合第 15 章介紹 ) 利用巨集功能 ( 如左下圖 ), 可將連續的操作步驟錄製起來, 之後只要執行巨集 ( 如右下圖 ), 即可完成所錄製的一連串動作, 省去重複操作的時間例如在每次修改檔案後, 通常都要執行存檔列印資料關閉檔案等 3 個動作, 這種重複性的動作就適合錄製成巨集輸入巨集名稱選工具 / 巨集 / 巨集選工具 / 巨集 / 錄製新巨集指定快速鍵, 以透過按快速鍵來執行巨集巨集的錄製與執行開始錄製巨集選取要執行的巨集指令除了可利用上述方法來錄製巨集之外, 也可使用 VBA(Visual Basic for Application) 語言註來編寫巨集程式, 以設計出可符合各種不同需求的巨集按此鈕, 會執行用來計算各地區交易金額的小計與圖表製作的巨集小計圖表具有小計與圖表製作功能的巨集註若要使用 VBA 語言來編寫巨集程式, 需參考較進階的書籍, 以瞭解 VBA 的語法與編寫方式

25 電腦軟體乙級檢定搶先做活動目標 1. 提昇同學 Excel Word 操作技巧的熟練度 2. 讓同學在練習過程中, 熟悉電腦軟體應用乙檢的題型 3. 讓學生以分組的方式參與製作, 培養團隊精神活動進行 1. 全班分組, 每組 2~3 人, 各組組員可互相討論解題方式 2. 請各組複製本書教資光碟中的範例檔案 " 題組二.xls" 至電腦中 3. 利用範例檔案 " 題組二.xls", 並參照下頁試題要求, 以 Excel 及 Word 軟體, 來製作如下圖所示之報表 4. 報表完成後, 可由教師自己評分或由各組相互評分, 得分最高組為優勝 (1) 滿分 100 分,60 分 ( 含 ) 以上為及格 (2) 若答案不符合各項題目要求, 依題目前的符號扣分 ( -50 分, -20 分, -20 分, -10 分 ) 403

26 題目要求 404 檔案及報表要求紙張設定為 A4 格式, 頁面內文之上下邊界皆為 3 公分, 左右邊界亦為 3 公分因印表機紙張定位有所不同時, 左右邊界可允許有少許誤差, 惟左右邊界之總和仍為 6 公分中文設定為新細明體或細明體字型, 英文及數字設定為 Times New Roman 字型, 但圖表的標題皆設為新細明體或細明體頁首之下與頁尾之上, 各以一條 1 點之橫線與本文間相隔, 頁首之下的橫線與頁緣距離為 3 公分, 頁尾之上的橫線與頁緣距離為 3 公分並於頁首左邊以 10 點字型加印題組及附件編號, 例如題組二附件二, 且加框線及灰色網底報表內容, 應依試題要求作答, 不得自行加入無關的資料製作一份民國八十九年研發部門員工的加班及加班費支領統計報表, 內容必須包含 : 紙張設定為直式以部門名稱遞增排序方式分別製表, 並於每個部門開始列加入部門名稱每一部門內列出該部門所有應發加班費之員工相關資料, 依月薪遞減排序方式列出, 其中月薪相同者以姓名筆劃遞增排序加班費 =( 月薪 224) 1.5 全年總加班時數, 金額以整數計算, 小數四捨五入無加班費者, 不須列印資料佔月薪比例 =( 加班費薪水 ) 之百分比, 以 % 表示, 計算到小數點第二位, 並將第三位四捨五入部門加總為該部門加班費之總和加班費總計金額為整個研發部門加班費之總和附表為加班時數摘要, 各部門依職稱分別計算其加班時數, 並加上小計附表以部門名稱遞增排序, 職稱由左至右, 分別依筆劃遞增順序排列報表標題 : 頂新資訊公司民國八十九年研發部門員工加班費支領統計清冊報表含員工姓名職稱月薪加班時數加班費佔月薪比例等欄位附表置於加班費總計金額下方, 附表名稱為附表 : 加班時數摘要報表標題為 15 點字型, 置中對齊, 並加框及陰影每頁重複顯示報表標題在標題的下一行靠右加入測驗當天的日期, 其格式為民國 X 年 X 月 X 日 12 點字型 (X 以中文數字表示 ) 且每頁重複顯示日期欄位的名稱以 12 點字型表現, 每個欄位以一個 ( 含 ) 以上的空白予以間隔, 且上下均標以一條 2 1/4 點之橫線每頁重複顯示欄位名稱及橫線部門名稱與員工姓名欄對齊報表中的所有數值均應標示千分位符號, 且靠右對齊於員工資料列印完畢後, 列印部門加總部門加總字樣與員工姓名欄對齊部門加總列之上方標以一條 1 1/2 點之橫線, 下方標以一條 1 1/2 點之雙橫線部門加總列需加上網底所有部門列印完畢後, 列印加班費總計金額加班費總計金額字樣靠左對齊, 加班費總金額應靠右與加班費欄位對齊加班費總計金額列與所列印最後一個部門不需間隔附表中所有欄位均置中對齊欄位名稱上下均標以一條 2 1/4 點之橫線, 最後一列之小計上方標以一條 1 1/2 點之橫線, 下方標以一條 1 1/2 點之雙橫線在每頁頁面左下方以 10 點字型加入您的學號, 右下方以 10 點字型加入您的姓名, 頁面下方中間以 10 點字型加上頁碼 ( 學號姓名以組長為準 )

27 單元 3 第 11 章認識 Excel 軟體 Excel 檔案稱為活頁簿, 每個活頁簿預設有 3 張空白的工作表儲存格位址是由欄與列的編號組合而成, 如 A1 B2 要表示某一範圍的多個儲存格, 需以 : 連結範圍左上角與右下角的儲存格位址, 如 A1:C3 第 12 章公式與函數的使用在輸入公式或函數時, 必須以等號 "=" 為首常用的運算符號 ( 假設 A1 = "10" B1 = "5"): 運算類型運算符號意義優先順序範例運算結果 () 括號 1 = (A1 + 2) * B 負號 2 = -A1-10 % 百分比 3 = A1% 0.1 算術運算 ^ 次方 4 = A1 ^ B * 乘法 5 = A1 * B1 50 / 除法 5 = A1 / B1 2 + 加法 6 = A1 + B 減法 6 = A1 - B1 5 文字運算 & 字串連接 7 = A1 & B1 105 <(<=) 小於 ( 小於或等於 ) 8 = A1 < B1 FALSE 比較運算 >(>=) 大於 ( 大於或等於 ) 8 = A1 > B1 TRUE = 等於 8 = A1 = B1 FALSE <> 不等於 8 = A1 <> B1 TRUE 3 種儲存格位址的參照方式 : 參照方式說明表示方式相對絕對混合複製公式時依據儲存格的欄列移動變化量, 自動調整公式中的儲存格位址複製公式時, 不會因儲存格位址的改變而改變公式內容包含相對參照位址與絕對參照位址欄名 + 列號 "$" + 欄名 + "$" + 列號 "$" + 欄名 + 列號欄名 + "$" + 列號按鍵可用來切換相對參照位址絕對參照位址及混合參照位址 405

28 計算機概論常用的函數 : 數學與三角函數說明頁碼 SUM(X, Y) 計算 X Y 的總和 308 ROUND(X, n) 將 X 四捨五入至第 n 個小數位數 309 ROUNDUP(X, n) 將 X 無條件進位至第 n 個小數位數 309 ROUNDDOWN(X, n) 將 X 無條件捨去至第 n 個小數位數 309 SUMIF( 範圍 A, 條件式, 範圍 B) 加總範圍 A 中, 符合條件式所對應之範圍 B 的值 313 SUMPRODUCT( 範圍 A, 範圍 B) 分別計算範圍 A 與範圍 B 相對應的儲存格之乘積, 再加總 313 INT(X) 計算小於等於 X 的最大整數 314 統計函數說明頁碼 AVERAGE(X, Y) 計算 X Y 的平均值 308 MAX(X, Y) 找出 X Y 的最大值 308 MIN(X, Y) 找出 X Y 的最小值 308 COUNT( 範圍 A) 在範圍 A 中, 計算含有數值資料的儲存格個數 309 COUNTA( 範圍 A) 在範圍 A 中, 計算非空白的儲存格個數 309 RANK(X, 範圍 A, Y) 排列出 X 在範圍 A 中的排名 (Y 省略或 0, 代表遞減 ;Y 為其他數值, 代表遞增 ) 309 COUNTIF( 範圍 A, 條件式 ) 計算範圍 A 中, 符合條件式的儲存格個數 312 檢視與參照函數說明頁碼 VLOOKUP(X, 範圍 A, Y, Z) HLOOKUP(X, 範圍 A, Y, Z) 在範圍 A 最左欄中尋找與 X 相同的值, 當找到時, 則傳回該列第 Y 欄中儲存格的值 (Z 省略, 代表尋找最相近的值 ) 314 在範圍 A 第一列中尋找與 X 相同的值, 當找到時, 則傳回該欄第 Y 列中儲存格的值 (Z 省略, 代表尋找最相近的值 ) 314 邏輯函數說明頁碼 NOT( 條件式 ) 條件式成立, 傳回 FALSE; 條件式不成立, 傳回 TRUE 310 AND( 條件式 ) 所有條件式成立, 傳回 TRUE; 有任一條件式不成立, 傳回 FALSE OR( 條件式 ) 有任一條件式成立, 傳回 TRUE; 所有條件式皆不成立, 傳回 FALSE 310 IF( 條件式, n, m) 判斷條件式是否成立, 當成立時傳回 n, 不成立時傳回 m 311 文字函數說明頁碼 MID(X, n, m) 從 X 字串中, 第 n 個位置起擷取 m 個字元 315 日期及時間函數說明頁碼 TODAY() 傳回目前電腦系統的日期 ( 包含年月日 ) 315 DATEDIF(X, Y, n) PMT(X, Y, Z) IPMT(X, n, Y, Z, m) 計算 X 到 Y 兩日期相差多久 ( 單位為 n,n 為 D 代表天數 M 代表月數 Y 代表年數 ) 財務函數說明頁碼計算貸款每月應付的本息 ( 本金 + 利息 ),X 為月利率 Y 為總期數 ( 單位 : 月 ) Z 為貸款金額計算第 n 期應付的利息,X 為月利率 n 的範圍為 1 ~ Y Y 為總期數 ( 單位 : 月 ) Z 為貸款金額 m 為年金終值

29 單元 3 第 13 章統計圖表的製作與編修按圖表精靈鈕, 再依循精靈的引導, 即可快速製作出圖表常用的圖表類型 : 圖表類型直條圖橫條圖圓形圖折線圖股票圖 XY 散佈圖區域圖雷達圖說明顯示不同時間點或不同類別的資料數值和直條圖相同, 但圖表以橫向呈現表示某項資料占整個資料數列的比例顯示不同時間點的資料數值走勢用來分析股票的走勢, 俗稱為 K 線圖用來比較兩類資料數值顯示不同類別資料在不同時間區間的變動程度顯示某一主題的多項評比結果第 14 章資料的整理與分析排序 : 將工作表的資料按照某種特定的順序排列 ( 如遞增遞減 ) 用來排序的欄位稱為排序鍵, 最多可設定 3 個資料驗證 : 用來設定使用者所能輸入的資料範圍, 以提升資料輸入的正確性資料篩選 : 從工作表中挑選出符合條件的資料, 分為自動篩選與進階篩選兩種小計 : 資料經過排序後, 利用小計功能可將資料分組進行各種運算 ( 如加總平均等 ) 條件式加總 : 用來將工作表中符合條件的資料進行加總第 15 章試算表的應用與管理樞紐分析 : 可快速將多筆資料加以彙整統計, 並以表或圖來呈現資料剖析 : 將存於單一儲存格中的資料, 分欄存放至多個儲存格為活頁簿設定保護密碼, 可保護重要的檔案不被任意開啟 ; 設定防寫密碼, 可防止不相干的人任意更改活頁簿中的資料內容 407

840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00

Excel - - Excel - -4-5 840 提示 Excel - Excel -- Excel (=) Excel ch0.xlsx H5 =D5+E5+F5+G5 (=) = - Excel 00 ( 0 ) 智慧標籤相關說明提示 -5 -- Excel 4 5 6 7 8 + - * / % ^ = < >= & 9 0 (:) (,) ( ) Chapter - :,