4-2... Excel 2016 實力養成暨評量 4-1 術科題庫分類及涵蓋技能內容 類別技能內容 第一類基本函數與格式設定能力 1. 開啟與儲存不同檔案格式的文件 2. 工作表編輯 : 插入 刪除 重新命名 移動或複製 3. 儲存格文字編修 4. 儲存格樣式 5. 列高與欄寬之調整 6. 儲存格格式設定 : 數字類別 對齊 外框 圖樣 鎖定與隱藏儲存格公式 自訂格式 保護工作表 7. 字型設定 : 字型 字體 大小 底線 色彩 特殊效果 8. 插入符號 9. 格式化條件之設定 : 醒目提示儲存格規則 頂端與底端項目規則 資料橫條 色階 圖示集 10. 儲存格的值之格式化條件 11. 基本函數之應用 : 日期及時間函數 數學函數 統計函數 邏輯函數 檢視與參照函數 12. 使用範圍名稱 13. 插入圖片 圖案 美工圖案 SmartArt 物件 14. 使用繪圖工具列
... 第四章技能測驗 - 術科題庫 4-3 類別技能內容 15. 插入走勢圖 : 折線圖 直條圖 輸贏分析 技能內容說明 : 評核受測者是否了解 儲存格 之相關樣式設定及設定格式化的條件, 並使用基本函數處理資料, 最後學習如何利用處理過後的資料輔以美工圖案 走勢圖, 或是 Smart Art 物件呈現 基本函數包含 :SUM AVERAGE AVERAGEA AVERAGEIF ROUND MAX MIN DATEDIF INT LEFT RIGHT TODAY YEAR MONTH DAY HOUR MINUTE SECOND COUNT COUNTA COUNTIF IF SUMIF
4-4... Excel 2016 實力養成暨評量 類別技能內容 第二類資料編修及列印能力 1. 插入註解 簽名欄 2. 儲存格編輯 : 插入 刪除 搬移 複製 3. 選擇性貼上 4. 自動填滿 5. 編輯填滿 ( 向上 下 左 右 ) 6. 設定保護文件 7. 常用函數應用 : 日期與時間函數, 文字 數學 統計與財務函數, 邏輯與資訊函數, 檢視與參照函數 8. 表格設計與樣式設定 9. 交叉分析篩選器 ( 篩選表格 ) 10. 儲存格範圍與表格轉換 11. 資料轉置 12. 合併彙算 13. 凍結資料欄列 隱藏視窗 分割視窗 14. 超連結 15. OLE( 插入物件 ) 16. 設定列印格式 : 頁面 ( 列印方向 縮放比例 紙張大小 ) 邊界與居中方式 標題設計 列印範圍選定 循欄與循列列印 壓縮列印 17. 跨頁標題重複 18. 頁首頁尾的設定
... 第四章技能測驗 - 術科題庫 4-5 類別技能內容 19. 自訂分頁線 技能內容說明 : 評核受測者是否熟悉 Excel 儲存格資料的進階處理, 運用常用函數處理資料, 並加入表格的應用, 加深資料與表格之間的關聯性, 並因應不同輸出需求, 設定列印格式將資料輸出 常用函數包含 :SUM AVERAGE AVERAGEA AVERAGEIF AVERAGEIFS ROUND ROUNDUP ROUNDDOWN SQRT MAX MIN INT SUMPRODUCT MOD LEFT RIGHT LEN REPT TODAY YEAR MONTH DAY HOUR MINUTE SECOND COUNT COUNTA COUNTIF COUNTIFS DATEDIF SUMIF SUMIFS LARGE SMALL STDEV.S RANK.AVG RANK.EQ IF AND OR VLOOKUP HLOOKUP WEEKDAY WORKDAY FIND SEARCH MID TEXT REPLACE BIG5 VALUE ISBLANK IFERROR HYPERLINK
4-6... Excel 2016 實力養成暨評量 類別技能內容 第三類統計圖表之建立與應用能力 1. 使用圖表精靈 2. 變更圖表的類型或格式 3. 統計圖表組成元件之調整 4. 圖表上的資料數列之增刪與編輯 5. 3D 地圖 6. 圖表的合併 7. 增添圖表元素 : 圖表標題 格線 圖例 座標軸 格線 資料標記 8. 資料數列的格式變化 9. 樞紐分析表圖表 技能內容說明 : 評核受測者是否熟悉 Excel 資料與圖表之間的關聯性, 其中包括不同圖表的組合與交互應用, 使處理過後的資料更具說服力
... 第四章技能測驗 - 術科題庫 4-7 類別技能內容 第四類進階資料處理與分析能力 1. 資料處理 : 排序 篩選 表單 小計 2. 自動填滿 : 自訂清單 - 新增 修改 刪除 3. 進階函數的使用 4. 填滿工作群組 5. 填滿數列 : 等差 等比 日期級數 6. 段落重排 7. 資料剖析 8. 資料驗證 9. 以公式設定格式化條件 10. 單項欄位的樞紐分析表 11. 分頁欄位的樞紐分析表 12. 群組與切入式樞紐分析表 13. 建立樞紐分析表以分析外部資料 14. 建立樞紐分析表以分析多個表格中的資料 15. 樞紐分析表中使用時間表顯示不同時期的資料 16. 交叉分析篩選器 ( 篩選樞紐分析 ) 17. 移除重複 18. Power Pivot 和資料模型 技能內容說明 : 評核受測者是否可將資料進一步轉換為有系統的數據以供分析之用, 包括資料篩選與小計 資料驗證與剖析等應用, 運用進階函數處理資料, 並進行樞紐分析表的製作與統計分析 進階函數包含 : 所有 Excel 函數
4-8... Excel 2016 實力養成暨評量 類別技能內容 第五類進階函數及陣列設定與進階列印能力 1. 公式陣列 2. 進階函數之應用 : 日期及時間函數 數學與三角函數 統計函數 財務函數 邏輯函數 檢視與參照函數 資訊函數 3. 陣列函數的建置與應用 4. 運算列表 5. 頁首頁尾的進階設定 6. 列印範圍選定 : 選定工作表 整個活頁簿 7. 多張工作表的列印 8. 輸出至檔案 9. 列印資料篩選設定 10. 與其他軟體間資料轉換 11. 下載網頁資料 12. 匯入與匯出 13. XML 技能內容說明 : 評核受測者是否了解資料數據從頭到尾的編修 處理與輸出, 此類將大量運用統計 數學 檢視參照函數與財務函數, 並針對各種不同的輸出方式 ( 諸如工作表列印 與其他軟體間的資料轉換或是 XML 格式等 ) 做設定 進階函數包含 : 所有 Excel 函數
... 第四章技能測驗 - 術科題庫 4-9 4-2 第一類 : 基本函數與格式設定能力 101 完工金額估算表易中難 一 題目說明 : 1. 思遠工程顧問公司的財務主管欲執行工程進度的資金預估與控制, 並進行資金調度工作 2. 依據未完工的工程合約金額除以完工月數, 估算每月工程收入金額, 並將四捨五入的餘絀金額放在第一個月裡, 若已經完工則不顯示任何數字 工程收入金額以萬為單位, 故金額須以自訂數值格式規範顯示 " 萬 " 單位 二 作答須知 : 1. 請至 C:\ANS.CSF\EX01 資料夾開啟 EXD01.ods 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA01.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址, 並請使用範圍名稱 除題目要求更改之設定外, 不能任意改變原有之設定 三 設計項目 : 1. 設定範圍名稱如下 : 設定儲存格 E2~X2 範圍名稱為 月份 將儲存格範圍 B3~D19, 以頂端列 ( 儲存格 B3 C3 與 D3) 建立範圍名稱 2. 在 E4 儲存格以範圍名稱製作公式, 計算每月金額, 再將公式複製給儲存格 E4~K19 與儲存格 M4~X19, 設定如下 : 如果完工月數 (C4~C19) 與月份 (E2~X2) 相同時, 每月金額 = 每月 _ 完工金額 + 未完成 _ 合約 - 完工月數 * 每月 _ 完工金額 如果完工月數 (C4~C19) 大於月份 (E2~X2) 時, 則每月金額 = 每月 _ 完工金額, 否則為 0
4-10... Excel 2016 實力養成暨評量 3. 進行格式編輯, 資料均無小數位數 : A. 未完成合約 (B4~B20) 與每月金額 (E4~Y20) 欄位 : 使用自訂格式 "#,##0" 進行修改, 顯示結果為 ", 萬 ", 字串與數值間需有一半形空格, 若該儲存格有負數與零的數值, 則需使用儲存格格式進行隱藏 B. 完工月數 (C4~C20) 欄位 : 使用自訂格式 "0" 進行修改, 顯示結果為 " 個月 ", 字串與數值間需有一半形空格 C. 每月完工金額 (D4~D20) 欄位 : 使用自訂格式 "#,##0" 進行修改, 顯示結果為 ", 萬 / 月 ", 字串與數值間需有一半形空格 4. 鎖定視窗 : 讓視窗畫面自第 3 列開始顯示, 列號 1 與 2 不須在視窗中呈現, 不可使用隱藏列的方式, 將第 3 列 A~D 欄鎖定在視窗中 四 參考結果如下所示 :
... 第四章技能測驗 - 術科題庫 4-35 4-3 第二類 : 資料編修及列印能力 201 學期成績易中難 一 題目說明 : 1. 遠明大學的資訊安全政策中的個資保護法, 想利用 Excel 的函數針對學生的身分證統一編號 姓名及電話執行資料隱蔽 2. 資訊中心針對學生資料, 身分證統一編號 ( 個資 ) 欄位, 隱藏中間三碼數字, 以 * 符號顯示 ; 姓名 ( 個資 ) 欄位, 隱藏中間名字並以 " " 符號取代 ; 電話 ( 個資 ) 欄位, 隱藏中間三碼數字, 以英文字母 "X" 顯示 二 作答須知 : 1. 請至 C:\ANS.CSF\EX02 資料夾開啟 EXD02.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA02.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 三 設計項目 : 1. 設定範圍名稱如下 : 於 期末考 工作表的儲存格 A2~G99, 設定範圍名稱為 期末考 於 期中考 工作表的儲存格 A3~Z100, 設定範圍名稱為 期中考 於 平時成績 工作表的儲存格 A2~K99, 設定範圍名稱為 平時成績 於 點名 工作表的儲存格 A2~P99, 設定範圍名稱為 點名
4-36... Excel 2016 實力養成暨評量 2. 於 學期成績 工作表各欄位, 進行如下設定 : A. 使用 VLOOKUP 函數, 完成以下事項 : 平時成績 欄位(C3~C100): 依據 學號 欄位資料, 搜尋 平時成績 範圍名稱內,K 欄的成績 期中考 欄位(D3~D100): 依據 學號 欄位資料, 搜尋 期中考 範圍名稱內,Z 欄的成績 期末考 欄位(E3~E100): 依據 學號 欄位資料, 搜尋 期末考 範圍名稱內,G 欄的成績 B. 原始成績 欄位(F3~F100): 使用 SUMPRODUCT 函數, 計算各成績與其成績比例 ( 儲存格 C1~E1) 的乘積 C. 點名 欄位(G3~G100): 使用 IF VLOOKUP 函數與 點名 範圍名稱 使用 點名 範圍名稱的最後一欄, 判斷該生是否有缺席或請假紀錄 若無缺席 請假, 則點名分數為 5 分, 否則為 0 分 D. 學期成績 欄位(H3~H100): 使用 ROUND 函數, 計算原始成績與點名的合計, 並四捨五入取至個位數 3. 於 學生基本資料 工作表, 進行如下設定 : A. 身分證統一編號( 個資 ) 欄位(H2~H99): 使用 REPLACE 函數, 隱藏 身分證統一編號 欄位 ( E2~E99) 的中間三碼數字, 以符號 "*" 顯示 例如 :F288199531 則以 F288***531 形式顯示 B. 姓名( 個資 ) 欄位(I2~I99): 使用 REPLACE LEN 與 REPT 函數 隱藏 姓名 欄位 (F2~F99) 的中間名字並以符號 " " 取代, 利用插入符號功能中選取 ( 一般文字 ) 插入符號" ", 符號 " " 的 Unicode( 十六進位 ) 字元代碼為 25CB
... 第四章技能測驗 - 術科題庫 4-37 例如 : 朱 宇 則以 朱 宇 顯示 喬倚立 則以 喬 立 顯示 歐陽在霖 則以 歐 霖 形式顯示 C. 電話( 個資 ) 欄位(J2~J99): 使用 REPLACE 函數 隱藏 電話 欄位 (G2~G99) 中間三碼數字, 以英文字母 "X" 顯示 例如 :0917-119-782 則以 0917-XXX-782 形式顯示 D. 隱藏 身分證統一編號 姓名 與 電話 三個欄位 四 參考結果如下所示 :
4-62... Excel 2016 實力養成暨評量 4-4 第三類 : 統計圖表之建立與應用能力 301 電子產品市場佔有率統計易中難 一 題目說明 : 1. 希妮卡電子公司的老闆發現, 公司投入許多廣告費, 卻沒有得到相對應的銷貨收入及市場佔有率 所以, 想分析同業之間廣告費的投入 銷售金額及市場佔有率的關聯 2. 老闆交付業務人員, 以圖表方式分析同業之間, 廣告費 銷售金額及市場佔有率之間的關聯 3. 由於這是三組數據的分析, 所以業務人員閱讀 Excel 書籍後, 發現需以泡泡圖才能標示三組數值資料 二 作答須知 : 1. 請至 C:\ANS.CSF\EX03 資料夾開啟 EXD03.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA03.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 三 設計項目 : 1. 於 市場佔有率 工作表繪製圖表 : 圖表資料來源 :B1~D10 儲存格 圖表型態為 立體泡泡圖, 數列依資料點分色自動填滿 圖表需置於 F1~M10 儲存格內 2. 圖表內顯示資訊設定 ( 標題名稱需依指定儲存格內容異動 ): A. 在圖表上方顯示圖表標題 : 標題指定為 D1 儲存格內容, 顯示名稱為 市場佔有率 字體大小為 14pt, 字型設定為粗體字
... 第四章技能測驗 - 術科題庫 4-63 B. 顯示主垂直 ( 數值 ) 座標軸標題 : 指定為 C1 儲存格內容 垂直式標題 ( 垂直文字方向 ) 標題顯示名稱為 銷售金額 ( 萬 ) 字體大小為 10pt, 字型設定為粗體字 C. 顯示主水平 ( 數值 ) 座標軸標題 : 指定為 B1 儲存格內容 標題顯示名稱為 廣告費 ( 百萬 ) 字體大小 10pt, 字型設定為粗體字 3. 圖表縱座標軸數值設定 : 限定作法為使用自訂數值格式為 0," 千萬 ", 使刻度顯示為每隔 50 千萬 數值格式顯示為 千萬 4. 圖表區進行以下設定 : 框線設定 : 開啟圓角 設定中央位移外陰影 圖表不顯示圖例, 但需顯示主水平格線與主垂直格線 四 參考結果如下所示 :
... 第四章技能測驗 - 術科題庫 4-87 4-5 第四類 : 進階資料處理與分析能力 401 商品資訊易中難 一 題目說明 : 1. 華維科技公司是專門銷售筆記型電腦的經銷商, 銷售人員發現從總公司伺服器下載的資料, 所有欄位全都擠在一起, 讓他們不知所措, 難道要逐筆搬移嗎? 2. 請教高手後, 分析出下載的資料事實上是具有規則的, 只須使用取代與資料剖析的方式, 即可使資料進行分割 3. 將分割好的資料, 再進一步依據廠牌與顯示晶片統計其數量 二 作答須知 : 1. 請至 C:\ANS.CSF\EX04 資料夾開啟 EXD04.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA04.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 三 設計項目 : 1. 商品明細資料 工作表: A. 針對儲存格 B1~B189, 進行以下編修 : 將副標題 ( 處理器種類 處理器速度 螢幕尺寸 重量 內建記憶體 硬碟容量 無線網路 螢幕技術 顯示晶片 光碟機 ) 刪除 將字串 "-" 及其前後的空白刪除 刪除後如下圖所示, 其中的字串 ":" 必須保留
4-88... Excel 2016 實力養成暨評量 將儲存格 B1~B189 的資料, 依據字串 ":" 進行資料剖析至 B~K 欄, 原本存在於 C D 欄的資料需變為 L 與 M 欄 在第一列插入新列, 並將 標題列 工作表中第一列標題複製到儲存格 A1~M1 排序 : 將儲存格 A1~M190 依產品名稱 (A 欄 ) 遞增排序 於 A 欄新增一個欄位, 使資料從 B 欄開始 B. A~N 欄設定為自動換列格式 C. 將 A1 儲存格新增名稱 廠牌, 並在儲存格 A2~A190, 設定內容如下 : 使用 LEFT FIND 函數, 取 產品名稱 欄位中, 第一位空白之前的字串作為廠牌資料 將 A 欄欄寬設定為 10 D. 將儲存格 A1~N190 格式化為表格, 套用 表格樣式中等深淺 15 2. 建立樞紐分析表, 資料來源為 商品明細資料 工作表 : 分析表置於 顯示晶片分析 工作表的 A3 儲存格 依據產品名稱統計數量, 分析顯示晶片 ( 欄資料 ) 與廠牌 ( 列資料 ) 之間的數量統計
... 第四章技能測驗 - 術科題庫 4-89 將儲存格中 欄標籤 名稱變更為 顯示晶片 ; 列標籤 名稱變更為 廠牌 ; 數值欄位名稱變更為 數量 將同類型資料製作成群組, 未群組前資料由左至右需照順序製作為 Nvidia 分享式 整合 3D 及 獨立式 四個群組 製作完成後, 需將群組進行折疊, 折疊完之後資料範圍為 A3~F19 儲存格 樞紐分析表應如下圖所示 :
4-90... Excel 2016 實力養成暨評量 四 參考結果如下所示 :
4-116... Excel 2016 實力養成暨評量 4-6 第五類 : 進階函數及陣列設定與進階列印能力 501 昱遠工程員工退休年金資料易中難 一 題目說明 : 1. 昱遠工程公司的會計人員, 以勞退基金對照表的資料, 計算每位員工的月投保薪資 勞工 單位 2. 以彩色箭頭標示出其薪資的等級, 列印所有資料並加上封面 二 作答須知 : 1. 請至 C:\ANS.CSF\EX05 資料夾開啟 EXD05.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA05.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 三 設計項目 : 1. 封面 工作表: A. 插入 SmartArt 物件的 遞增圖片輔色流程圖, 將物件置於 A1~I23 儲存格內 B. 參考結果圖, 輸入文字 勞退基金對照表 與 基本資料, 字型為 微軟正黑體, 字體為 20pt 粗體字 C. SmartArt 物件填滿圖片, 並輸入替代文字 : 替代文字必需輸入在 描述 方框內 上方圓形物件填滿 People1.jpg 圖片, 並設定圖片的替代文字名稱為 People1 下方圓形物件填滿 People2.jpg 圖片, 並設定圖片的替代文字名稱為 People2
... 第四章技能測驗 - 術科題庫 4-117 部分結果應如下圖所示 : 2. 基本資料 工作表: A. 將儲存格範圍 A1~K41 轉換成表格, 套用 表格樣式中等深淺 13 B. 薪資 欄位(H2~H41): 設定 圖示集 格式化條件, 套用 三箭號 ( 彩色 ) 樣式 C. 月投保薪資 欄位(I2~I41): 使用 VLOOKUP TEXT 函數 以 薪資 欄位資料, 查閱 勞退基金對照表 範圍名稱, 傳回所對應的 月投保薪資 資料 D. 勞工 欄位(J2~J41): 使用 VLOOKUP TEXT 函數 以 薪資 欄位資料, 查閱 勞退基金對照表 範圍名稱, 傳回所對應的 勞工 資料 E. 單位 欄位(K2~K41): 使用 VLOOKUP TEXT 函數 以 薪資 欄位資料, 查閱 勞退基金對照表 範圍名稱, 傳回所對應的 單位 資料 3. 基本資料 工作表列印設定: 列印第 1 列為標題列,A~B 欄為標題欄
4-118... Excel 2016 實力養成暨評量 4. 設定三張工作表的列印格式 : A. 指定 第一頁不同 隨文件縮放 對齊頁面邊界 B. 頁首 / 頁尾內容設定 : 頁首中央格式設定 ( 非第一頁頁首位置 ): 工作表名稱標籤, 字型為 標楷體, 字體為 20pt 粗體字 頁尾中央格式設定 ( 非第一頁頁尾位置 ): 第 頁 / 共 頁 ( "/" 符號前後各空一半形空格 ) C. 自訂邊界上下為 1.9 左右為 1.8 頁首與頁尾皆為 0.8, 水平置中 D. 列印方式為循列方向列印 四 參考結果如下所示 :
... 第四章技能測驗 - 術科題庫 4-119