CHAPTER 15 第 15 章 試算表的應用與管理 試算表的應用與管理 章將介紹樞紐分析 資料剖析與文件輸出, 以及活頁簿與工作表的保護等試算本表的進階功能, 讓同學對 Excel 的使用有更進一步的瞭解 單元 3 15-1 樞紐分析 有 樞紐, 統計資料快速搞定 樞紐的中文意思是 比喻重要的關鍵 Excel 的樞紐分析功能, 可從多筆資料中, 彙整及統計出關鍵的資訊, 讓使用者快速掌握資料的重點 例如在圖 15-1 多筆技能檢定結果的資料中, 若要整理出近 3 年的檢定平均通過率, 雖然可利用前面教過的公式與函數, 先計算每年各職種的檢定通過率, 再計算各年度的平均通過率, 但報表製作人必須輸入多個相關的公式或函數, 最後才能統計出所需的資訊, 因此相當費時 若改用樞紐分析功能, 則可快速從原始資料中, 彙整出近 3 年技能檢定的平均通過率資料, 省時又省力 原資料表 樞紐分析表 ( 內文範例 "ch15-1.xls") 圖 15-1 使用樞紐分析的範例 381
計算機概論 樞紐分析表的建立為了方便使用者製作樞紐分析表,Excel 提供樞紐分析表和樞紐分析圖精靈 圖 15-2 是依照此精靈的引導, 依序設定報表類型 資料來源 位置及版面配置, 所製作出來的加班時數統計樞紐分析表 原資料表 選 資料 / 樞紐分析表及圖報表 1 step 設定報表類型 選取資料的來源, 以及要建立 的報表類型 2 step 設定資料來源 設定樞紐分析表的資料範圍 3 step 選擇位置 選擇樞紐分析表要顯示於原工 作表中或另新建一個工作表 4 step 設定版面 分頁欄位 欄欄位 資料欄位 設定樞紐分析表各欄位的資料 ( 例如指定部門名稱作為列欄位 ) 列欄位 樞紐分析表 工作表中的欄位 382 圖 15-2 樞紐分析表和樞紐分析圖精靈交談窗 ( 內文範例 "ch15-2.xls")
第 15 章 試算表的應用與管理 單元 3 樞紐分析表的編修 如果需要調整樞紐分析表中的統計項目, 可新增或刪除樞紐分析表中的欄位, 來更改設定 圖 15-3 為增刪欄位的範例 選 檢視 / 工具列 / 樞紐分析表, 可開啟 ( 或關閉 ) 樞紐分析表工具列 新增樞紐分析表欄位 按樞紐分析表工具列上的, 拖曳欲新增的欄位至樞紐分析表 新增 " 月份 " 欄位 刪除 " 月份 " 欄位 ( 內文範例 "ch15-3.xls") 刪除樞紐分析表欄位 拖曳欲刪除的欄位至工作表的空白處 圖 15-3 增刪樞紐分析表欄位的範例 密技報你知 按樞紐分析表工具列的報表格式, 或選 格式 / 自動格式設定, 可套用 Excel 內建的表格樣式, 來美化樞紐分析表 383
計算機概論 樞紐分析表摘要方式的變更摘要方式就是樞紐分析表進行資料統計的方式 ( 如加總 平均值等 ) 在建立樞紐分析表時, 預設是以加總方式來統計資料, 我們可視實際需要更改摘要方式, 例如計算平均值 ( 圖 15-4) 雙按資料欄位標籤或按樞紐分析表工具列上的欄位設定 樞紐分析表的左上角會顯示摘要方式 設定資料欄位的摘要方式 ( 如平均值 ) 圖 15-4 變更欄位摘要方式的範例 ( 內文範例 "ch15-4.xls") 樞紐分析圖的建立 利用樞紐分析功能製作出來的統計表, 可轉成以圖的方式呈現, 只要按圖表精靈鈕, 即可快速產生如圖 15-5 所示的統計圖 建立樞紐選取樞紐分析表中任一分析圖儲存格, 按圖表精靈 樞紐分析圖的編輯, 如圖表類型 位置 格式設定等, 與本書第 13 章介紹的圖表編修方式相同 欄位按鈕按此可調整樞紐分析圖的欄位項目 ( 內文範例 "ch15-5.xls") 圖 15-5 依樞紐分析表中的資料建立樞紐分析圖的範例 384 密技報你知 選取樞紐分析圖後, 按樞紐分析表工具列的樞紐分析圖, 選隱藏樞紐分析圖欄位按鈕, 在列印樞紐分析圖時, 就不會印出圖中的欄位按鈕
第 15 章 試算表的應用與管理 單元 3 1 建立樞紐分析表 變更摘要方式 開啟檔案 "Ex15-p1.xls", 依照下列步驟操作, 最後將檔案另存為 "Ans15-p1.xls" 1 選取儲存格 A2 ~ G38 1 2 2 選按 資料 / 樞紐分析表及圖報表 選項 3 點選樞紐分析表選項按鈕, 按下一步鈕 3 4 確認資料來源為儲存格 A2 ~ G38, 按下一步鈕 5 點選新工作表選項按鈕, 4 將樞紐分析表建立在新的工作表中 5 6 按版面配置鈕 6 9 7 拖曳年度項目至列欄位 8 分別拖曳檢定類別項目至 7 8 欄欄位 通過率項目至資料欄位, 按確定鈕 9 按完成鈕, 即可建立出樞紐分析表 385
計算機概論 10 10 按樞紐分析表工具列上的欄位設定鈕 11 在摘要方式列示窗, 選平均值, 按確定鈕, 將摘要方式由預設的加總更改為平均值 11 13 12 選取第 A ~ D 欄, 並雙按 A 欄欄名的右框線, 將第 A ~ D 欄欄寬調整至最適欄寬 12 13 選取儲存格 B5 ~ D8, 選按 格式/ 儲存格 選項 14 在類別列示窗, 選百分比 15 在小數位數欄輸入 "1", 按確定鈕 15 16 選取儲存格 D4, 輸入 " 平均 ", 按鍵後, 儲存格 A8 的 " 總計 " 也會跟著更改為 " 平均 " 14 16 386
第 15 章 試算表的應用與管理 單元 3 1. 在 Excel 中, 若要刪除樞紐分析表中的欄位, 應先選取該欄位後, 接著進行下列哪一項操作? (A) 選按 編輯 / 清除 / 內容 選項 (B) 按鍵 (C) 選按 編輯 / 清除 / 全部 選項 (D) 拖曳欲刪除的欄位至工作表的空白處 2. 在 Excel 中, 若要將樞紐分析表轉成統計圖, 應先選取樞紐分析表中的任一儲存格, 再按下列哪一個按鈕? (A) (B) (C) (D) 3. Excel 的樞紐分析表是由 資料 分頁等欄位組成 15-2 資料剖析與文件輸出 Excel 提供有資料剖析 將工作表存成網頁格式 及將活頁簿內容以 E-mail 寄送出去等功能, 本節將一一介紹 資料剖析 為方便進行資料的比較 計算等處理,Excel 提供資料剖析功能, 可用來將存放於單一儲存格中的資料, 分欄存放至多個儲存格 例如圖 15-6 是將 " 出生年月日 " 欄中的資料, 利用 在選取要剖析的資料範圍時, 一次只能選取一欄 此項功能剖析存放於 6 個欄位中 資料剖析 選 資料 / 資料剖析 利用資料剖析功能, 將出生日期, 依年 月 日分開存放 圖 15-6 資料剖析的範例 ( 內文範例 "ch15-6.xls") 387
計算機概論 將工作表存成網頁格式要將 Excel 工作表製作成網頁形式, 以瀏覽器 ( 如 IE) 讀取, 可以選按 檔案 / 另存新檔 選項, 將工作表以 htm html 等網頁格式儲存 此外, 透過另存新檔交談窗, 也可將檔案儲存成範本檔或純文字檔等, 如圖 15-7 所示 選 檔案 / 另存新檔 副檔名說明 xls xlt txt 註 Excel 預設的活頁簿格式 範本檔案的格式 只能儲存純文字, 不同儲存格間的資料會以 Tab 字元 ( 即 ) 分隔 按此可選擇檔案的儲存格式 圖 15-7 Excel 提供的儲存檔案類型 利用 E-mail 傳送工作表如果電腦中安裝有電子郵件軟體 ( 如 Outlook), 我們可直接按 Excel 提供的電子郵件鈕 ( 圖 15-8), 寄送工作表給需要使用的相關人員 用 E-mail 傳送工作表 按電子郵件 或選 檔案 / 傳送到 / 郵件收件者 收到的 E-mail, 會直接顯 示工作表的內容 按傳送這張工作表鈕, 可寄出工作表 圖 15-8 利用 E-mail 傳送工作表 ( 內文範例 "ch15-7.xls") 388 註 Excel 2007( 含 ) 之後的版本預設副檔名為 ".xlsx", 但這些較新版的軟體仍可存取 ".xls" 的文件 密技報你知 選 檔案 / 另存成網頁, 也可將活頁簿內容以網頁格式儲存
第 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
計算機概論 工作表的保護在 Excel 中, 透過保護工作表的設定 ( 圖 15-10), 可賦予使用者對工作表的操作權限 ( 例如只能設定儲存格格式 插入欄等 ), 以避免使用者任意修改工作表的內容 選 工具 / 保護 / 保護工作表 若要取消工作表的保護, 可選 工具 / 保護 / 取消保護工作表, 再輸入保護密碼即可 設定工作表可以操作的項目 ( 勾選代表允許 ) 圖 15-10 保護工作表交談窗 如果被保護的工作表, 需要開放部分儲存格供使用者編修, 可透過圖 15-11 之交談窗來設定允許編輯的儲存格範圍 選 工具 / 保護 / 允許使用者編輯範圍 允許使用者編輯的儲存格範圍 新增允許使用者編輯的儲存格範圍 按保護工作表, 可設定使用者對工作表的操作權限 圖 15-11 允許使用者編輯範圍交談窗 2 剖析資料 保護工作表 開啟檔案 "Ex15-p2.xls", 依照下列步驟操作, 最後將檔案另存為 "Ans15-p2.xls" 1 選取儲存格 F2 ~ F15, 再選按 資料/ 資料剖析 選項 2 點選固定寬度選項按鈕, 按下一步鈕 1 2 390
第 15 章 試算表的應用與管理 單元 3 3 4 3 將指標分別移至要分欄的位置單按一下, 並按下一步鈕 4 在目標儲存格欄, 輸入 "G2", 並按完成鈕 5 5 按確定鈕, 將剖析後的資料取代原 G ~ L 欄儲存格 6 選按 檔案 / 另存新檔 選項 7 在檔案名稱欄輸入 "Ans15-p2" 6 8 按工具鈕, 選一般選項 將儲存格 F2 ~ F15 的資料, 分欄存至多個儲存格了! 8 9 7 11 9 在保護密碼欄輸入密碼 ( 如 15069AB), 並按確定鈕 10 在此欄, 重新輸入步驟 9 所設定的密碼, 並按確定鈕 10 11 按儲存鈕 1. 小英利用 Excel 來記錄零用錢的收支明細, 她不希望檔案被他人開啟, 請問她可以利用下列哪一項功能來達成? (A) 設定活頁簿的保護密碼 (B) 設定活頁簿的防寫密碼 (C) 設定只允許使用者編輯特定儲存格範圍 (D) 設定保護工作表的密碼 2. 在儲存 Excel 檔案時, 設定防寫密碼的作用為 (A) 避免檔案被刪除 (B) 避免他人開啟檔案 (C) 防止他人編修檔案內容 (D) 隱藏檔案 3. 在 Excel 中, 若要限制使用者對工作表的操作權限, 且設定僅能編輯部分儲存格的資料時, 應透過下列哪些功能來設定? 保護工作表 允許使用者編輯範圍 活頁簿的保護密碼 活頁簿的防寫密碼 391
計算機概論 3 善用 樞紐 統計快手 - 加班時數摘要 本範例以製作 加班時數摘要 為例, 讓同學練習以下 5 項 Excel 常用的功能 1. 建立樞紐分析表 2. 建立樞紐分析圖 3. 保護工作表 4. 保護活頁簿 5. 寄送 E-mail 1.1 1.2 1. 建立樞紐分析表 詳細步驟參考 1.1 開啟檔案 "Ex15-1.xls" 1.2 選取儲存格 A2 ~ H17, 選按 資料/ 樞紐分析表及圖報表 選項, 以開啟樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 1 交談窗 1.3 1.3 點選樞紐分析圖 ( 及樞紐分析表 ) 選項按鈕, 按下一步鈕, 以開啟樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 2 交談窗 392
第 15 章 試算表的應用與管理 單元 3 1.4 1.4 確認資料來源為儲存格 A2 ~ H17, 按下一步鈕, 以開啟樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 3 交談窗 1.6 1.5 1.5 點選新工作表選項按鈕, 將樞紐分析表及樞紐分析圖分別放置在新的工作表中 1.6 按版面配置鈕, 以開啟樞紐分析表和樞紐分析圖精靈 - 版面配置交談窗 1.7 1.7 拖曳部門名稱項目至列欄位 1.8 1.9 1.8 分別拖曳職稱項目至欄欄位 ; 加班時數項目至資料欄位 1.1010 1.9 按確定鈕, 返回樞紐分析表和樞紐分析圖精靈 - 步驟 3 之 3 交談窗 1.1010 按完成鈕, 完成樞紐分析圖表的建立 2. 建立樞紐分析圖 詳細步驟參考 Excel 會自動產生兩個工作表, 一個用來存放樞紐分析圖 ; 一個用來存放樞紐分析表 2.1 2.1 在圖表區按右鍵, 選按 圖表類型 選項, 以開啟圖表類型交談窗 393
計算機概論 2.2 在圖表類型列示窗中, 點選直條圖選項 2.2 2.3 2.3 在副圖表類型區, 選群組直條圖, 並按確定鈕 2.4 在圖表區按右鍵, 選圖表選項, 以開啟圖表選項交談窗 2.4 2.5 在標題標籤中的圖表標題欄輸入 " 加班時數摘要 ", 設定圖表的標題 2.5 394
第 15 章 試算表的應用與管理 單元 3 2.6 切換至資料標籤標籤, 勾選內容核取方塊, 設定要在直條圖顯示所代表的內容 ( 即加班時數 ), 按確定鈕 2.6 2.7 2.7 設定圖表樣式 : 圖表標題 華康中圓體 30 粗體 深藍數值座標軸 Arial 20 背景牆 白色填滿色彩圖表區 白色至淺綠色漸層的填滿色彩 3.1 3. 保護工作表 詳細步驟參考 3.1 切換至 Sheet1 工作表, 並選按 工具 / 保護 / 保護工作表 選項, 以開啟保護工作表交談窗 395
計算機概論 3.2 在此欄輸入密碼 ( 如 05223AA), 設定要取消保護工作表的密碼 3.2 3.3 3.3 取消勾選所有核取方塊, 並按確定鈕, 設定使用者無法編輯工作表 3.4 在此欄, 重新輸入步驟 3.2 所設定的密碼, 並按確定鈕, 完成工作表的保護 3.4 4.1 4. 保護活頁簿 詳細步驟參考 4.1 選按 檔案 / 另存新檔 選項, 以開啟另存新檔交談窗 4.2 按工具鈕, 選一般選項, 開啟儲存選項交談窗 4.2 4.3 4.3 在防寫密碼欄輸入防寫密碼 ( 如 AA32250), 並按確定鈕 396
第 15 章 試算表的應用與管理 單元 3 4.4 4.4 在此欄, 重新輸入步驟 4.3 所設定的密碼, 並按確定鈕 4.5 4.5 在檔案名稱欄輸入 "Ans15-1", 按儲存鈕 5. 寄送 E-mail 詳細步驟參考 5.1 切換至 Chart1 工作表, 按職稱欄旁的下拉式按鈕, 取消勾選助理工程師 副工程師 資深工程師核取方塊, 並按確定鈕, 設定樞紐分析圖僅顯示研發科的加班時數加總 5.1 樞紐分析圖變更後, 樞紐分析表也會跟著變更喔! 5.2 5.2 按電子郵件鈕 5.3 5.3 在收件者欄輸入收件者的電子郵件, 在主旨欄輸入郵件主旨, 並按傳送這份圖表鈕, 將樞紐分析圖寄送給收件者 397
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
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
MENU 2. 開啟檔案 "Ex15-b.xls", 依照提示內容, 利用樞紐分析表和樞紐分析圖精靈, 建立樞紐分析表, 並在 Excel 工作表中輸入公式與函數, 完成如下圖的結果後, 將結果另存為 "Ans15-b.xls" 類別數值數值百分比 小數位數 0 2 2 千分位符號 - 提示 : (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) 依上圖設定各欄位的資料類別
3 第 1 招選 格格 快速搞定 - 神奇的快速鍵! ( 可配合 11-2 節介紹 ) 想要快速選取同欄 同列或矩形範圍中, 含有資料的相鄰儲存格嗎? 只要按 + + 方向鍵, 或按 + + 鍵即可一次搞定 ( 如下圖 ) 選取儲存格 A2, 選取儲存格 A2, 選取儲存格 A2, 按 + + 按 + + 按 + + 同列同欄 矩形範圍 選取同列含有資料的相鄰儲存格 快速選取儲存格的範例 選取同欄含有資料的相鄰儲存格 選取含有資料的相鄰矩形範圍儲存格 第 2 招 線上博士 - 函數用法大全 ( 可配合 12-2 節介紹 ) Excel 提供的函數相當多, 除了第 12 章介紹的函數之外, 如果我們想要瞭解或使用更進階的相關函數, 可使用 Excel 提供的 線上說明 功能, 查詢各個函數的語法 範例與用途說明 選取欲輸入函數的儲存格, 按插入函數 選取函數類別 函數語法 選取要使用的函數 函數的語法與範例說明 Excel 的 線上說明 功能
第 3 招 簡化操作有 撇 步 - 巨集的使用 ( 可配合第 15 章介紹 ) 利用巨集功能 ( 如左下圖 ), 可將連續的操作步驟錄製起來, 之後只要執行巨集 ( 如右下圖 ), 即可完成所錄製的一連串動作, 省去重複操作的時間 例如在每次修改檔案後, 通常都要執行 存檔 列印資料 關閉檔案 等 3 個動作, 這種重複性的動作就適合錄製成巨集 輸入巨集名稱 選 工具 / 巨集 / 巨集 選 工具 / 巨集 / 錄製新巨集 指定快速鍵, 以透過按快速鍵來執行巨集 巨集的錄製與執行 開始錄製巨集 選取要執行的巨集指令 除了可利用上述方法來錄製巨集之外, 也可使用 VBA(Visual Basic for Application) 語言註來編寫巨集程式, 以設計出可符合各種不同需求的巨集 按此鈕, 會執行用來計算 各地區交易金額的小計與圖表製作 的巨集 小計 圖表 具有小計與圖表製作功能的巨集 註若要使用 VBA 語言來編寫巨集程式, 需參考較進階的書籍, 以瞭解 VBA 的語法與編寫方式
電腦軟體乙級檢定搶先做 活動目標 1. 提昇同學 Excel Word 操作技巧的熟練度 2. 讓同學在練習過程中, 熟悉電腦軟體應用乙檢的題型 3. 讓學生以分組的方式參與製作, 培養團隊精神 活動進行 1. 全班分組, 每組 2~3 人, 各組組員可互相討論解題方式 2. 請各組複製本書教資光碟中的範例檔案 " 題組二.xls" 至電腦中 3. 利用範例檔案 " 題組二.xls", 並參照下頁試題要求, 以 Excel 及 Word 軟體, 來製作如下圖所示之報表 4. 報表完成後, 可由教師自己評分或由各組相互評分, 得分最高組為優勝 (1) 滿分 100 分,60 分 ( 含 ) 以上為及格 (2) 若答案不符合各項題目要求, 依題目前的符號扣分 ( -50 分, -20 分, -20 分, -10 分 ) 403
題目要求 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 點字型加上頁碼 ( 學號 姓名以組長為準 )
單元 3 第 11 章 認識 Excel 軟體 Excel 檔案稱為活頁簿, 每個活頁簿預設有 3 張空白的工作表 儲存格位址是由欄與列的編號組合而成, 如 A1 B2 要表示某一範圍的多個儲存格, 需以 : 連結範圍左上角與右下角的儲存格位址, 如 A1:C3 第 12 章 公式與函數的使用 在輸入公式或函數時, 必須以等號 "=" 為首 常用的運算符號 ( 假設 A1 = "10" B1 = "5"): 運算類型 運算符號 意義 優先順序 範例 運算結果 () 括號 1 = (A1 + 2) * B1 60 - 負號 2 = -A1-10 % 百分比 3 = A1% 0.1 算術運算 ^ 次方 4 = A1 ^ B1 100000 * 乘法 5 = A1 * B1 50 / 除法 5 = A1 / B1 2 + 加法 6 = A1 + B1 15 - 減法 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
計算機概論 常用的函數 : 數學與三角函數 說明 頁碼 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 為年金終值 310 316 315 315 406
單元 3 第 13 章 統計圖表的製作與編修 按圖表精靈鈕, 再依循精靈的引導, 即可快速製作出圖表 常用的圖表類型 : 圖表類型直條圖橫條圖圓形圖折線圖股票圖 XY 散佈圖區域圖雷達圖 說明顯示不同時間點或不同類別的資料數值和直條圖相同, 但圖表以橫向呈現表示某項資料占整個資料數列的比例顯示不同時間點的資料數值走勢用來分析股票的走勢, 俗稱為 K 線圖用來比較兩類資料數值顯示不同類別資料在不同時間區間的變動程度顯示某一主題的多項評比結果 第 14 章 資料的整理與分析 排序 : 將工作表的資料按照某種特定的順序排列 ( 如遞增 遞減 ) 用來排序的欄位稱為排序鍵, 最多可設定 3 個 資料驗證 : 用來設定使用者所能輸入的資料範圍, 以提升資料輸入的正確性 資料篩選 : 從工作表中挑選出符合條件的資料, 分為自動篩選與進階篩選兩種 小計 : 資料經過排序後, 利用小計功能可將資料分組進行各種運算 ( 如加總 平均等 ) 條件式加總 : 用來將工作表中符合條件的資料進行加總 第 15 章 試算表的應用與管理 樞紐分析 : 可快速將多筆資料加以彙整統計, 並以 表 或 圖 來呈現 資料剖析 : 將存於單一儲存格中的資料, 分欄存放至多個儲存格 為活頁簿設定保護密碼, 可保護重要的檔案不被任意開啟 ; 設定防寫密碼, 可防止不相干的人任意更改活頁簿中的資料內容 407