試卷編號 :X10-1205 認證說明與注意事項 Excel 2010 測驗試卷實用級 一 本項考試為術科, 所需總時間為 40 分鐘, 時間結束前需完成所有考試動作 成 績計算滿分為 100 分, 合格分數為 70 分 二 術科為三大題, 第一大題至第二大題每題 30 分 第三大題 40 分, 總計 100 分 三 術科所需的檔案皆於 C:\ANS.CSF 資料夾內讀取 題目存檔方式, 請依題目指 示儲存於 C:\ANS.CSF 資料夾, 測驗結束前必須自行存檔, 並關閉 Excel, 檔案 名稱錯誤或未自行存檔者, 均不予計分 四 術科每大題之各評分點彼此均有相互關聯, 作答不完整, 將影響各評分點之得 分, 請特別注意 題意內未要求修改之設定值, 以原始設定為準, 不需另設 五 試卷內 0 為阿拉伯數字,O 為英文字母, 作答時請先確認 所有滑鼠左右鍵位 之訂定, 以右手操作方式為準, 操作者請自行對應鍵位 六 有問題請舉手發問, 切勿私下交談 壹 術科 100%( 第一題至第二題每題 30 分 第三題 40 分 )
一 學期成績計算表 1. 請至 C:\ANS.CSF\EX01 資料夾開啟 EXD01.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA01.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址, 並請使用範圍名稱 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目 : 成績總表 工作表: 1. 使用 AVERAGE 函數計算 第一次定期考查 第二次定期考查 第三次定期考查 日常考查 的 學期成績 ( 四次平均 ), 且以 ROUND 函數取至整數位 2. 趨勢圖 (I2~I31) 欄位 : 依據 第一次定期考查 第二次定期考查 第三次定期考查 日常考查 學期成績 欄位資料顯示直條圖類型的走勢圖群組 套用 走勢圖樣式彩色 #6 標示高點與最終點 3. 設定 成績總表 工作表之 學期成績 欄的格式化條件 : 條件 1: 三符號無框圖示集, 分數 <50 顯示, >=50 顯示, >=60 顯示 條件 2: 儲存格值的條件設定 : 成績大於等於 60 分, 字體顏色為藍色 成績小於 60 分, 字體顏色為紅色 成績計算流程 工作表 : 4. 插入 SmartArt 發散星形圖, 變更色彩為 彩色 - 輔色 SmartArt 樣式為 光澤, 位置為 A1~G17 Excel 2010 [ 第 1 頁 / 共 6 頁 ] X10-1205
5.SmartArt 發散星形圖內容設定 : 依序輸入 學期成績 第一次定期考查 第二次定期考查 第三次定期考查 日常考查, 字型大小為 11 微軟正黑體 ( 提示 : 文字呈現的方式, 請參閱參考結果圖 ) 改變 日常考查 及其右側箭頭物件的圖案樣式為 鮮明效果 - 橙色, 輔色 6 學期成績 文字樣式為 填滿- 紅色, 輔色 2, 霧面質感浮凸, 並改變文字填滿色彩為深藍色, 改變字型大小為 16 ( 三 ) 參考結果如下所示: Excel 2010 [ 第 2 頁 / 共 6 頁 ] X10-1205
一 在職訓練班學生選課資料內容 1. 請至 C:\ANS.CSF\EX01 資料夾開啟 EXD01.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA01.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址, 並請使用範圍名稱 除題目要求更改之設定外, 不能任意改變原有之設定 3. 本題各評分點彼此均有相互關聯, 作答不完整, 將影響各評分點之得分, 請特別注意 ( 二 ) 設計項目: 1. 學員基本資料 工作表: 將學號搬移至 A 欄 年齡 欄位: 利用 DATEDIF TODAY 函數計算年齡, 格式為 歲 2. 插入新工作表, 名稱為 修課資料 A. 將 學員基本資料 工作表中 上課期別 及 班別 二欄搬移至 C D 欄 B. 將 學員基本資料 工作表中 學號 姓名 複製到 A B 欄 3. 修課資料 工作表繪製框線: A.A1~D120 外框線為次粗實線 B. 設定 B2~B120 範圍的儲存格與 學員基本資料 工作表的年齡欄位色彩相同 Excel 2010 [ 第 1 頁 / 共 6 頁 ] X10-1206
4. 開課期別 工作表的 結束日期 欄位 : 依據 開課班別 工作表內的課程時數, 每次上課時間為 3 小時, 每週上課一次, 以此計算課程結束的日期 ( 三 ) 參考結果如下所示 : Excel 2010 [ 第 2 頁 / 共 6 頁 ] X10-1206
一 樂樂樂樂透彩中獎機率率率統計 1. 請至 C:\ANS.CSF\EX01 資料料夾開啟 EXD01.xlsx 檔設計 完成結果儲存於同一資料料夾之下, 檔案名稱為 EXA01.xlsx 2. 建立立或複製公式時需考慮是否需使用絕對位址, 並請使用範圍名稱 除題目要求更更改之設定外, 不不能任意改變原有之設定 ( 二 ) 設計項目: 1. 設定 91 年年 及 92 年年 工作表 : 將 開獎日期 及 兌獎截止 二欄欄的西元日期轉換為民國日期, 如 2002/3/1, 轉換成 91 年年 03 月 01 日 2. 統計分析 工作表: A. 利利用 COUNTIF COUNT 函數數設定 91 年年機率率率 92 年年機率率率 欄欄位, 將機率率率值以分數數型態 / 表示( / 符號前後各空一半形空白 ), 以 10000 為分母 B. 合計 欄欄位:91 年年與 92 年年出現機率率率的合計 C. 排名 欄欄位: 利利用 RANK.EQ 函數數, 由大至小計算名次 ( 提示 :RANK.EQ(number, ref, order) ) 3. 在 E2~E43 範圍設定格式化條件 : 排名前 3 名以藍藍色 ( 即字型色彩表中 (R:0,G:112,B:192)) 粗體字顯示, 並顯示 第 名 字樣 ( 提示 : 僅限使用小於等於 <= 判斷, 不不可使用小於 < 判斷 ) Excel 2010 [ 第 1 頁 / 共 5 頁 ] X10-1207
( 三 ) 參參考結果如下所示 : Excel 2010 [ 第 2 頁 / 共 5 頁 ] X10-1207
一 Golf 1. 請至 C:\ANS.CSF\EX01 資料夾開啟 EXD01.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA01.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 在第 1 列與第 2 列之間插入一列, 並分別在 C2~E2 F2~H2 I2~K2 L2~N2 依序輸入 Score Position Leader 2. 合併儲存格 : 分別合併 A1~A2 B1~B2 C1~E1 F1~H1 I1~K1 L1~N1 3. 設定 72 Holes 的 Leader 欄位公式: 72 Holes 的 Score-72*4 4. 所有列的列高為 25 5. 框線 : 內框線為最細實線, 外框線為次粗實線 第 2 列與第 3 列之間為雙線 6. 格式化條件 : 設定 18 Holes 36 Holes 54 Holes 的 Leader 欄位指定為五箭號 ( 彩色 ) 的圖示集格式化條件 ( 注意 : 比較對象為 E3~E75 H3~H75 K3~K75 整個範圍, 非單欄比較 ) 設定 72 Holes 的 Leader 欄位指定為三旗幟的圖示集格式化條件 最高 10%( 含 ) 為綠色旗幟 (>=90), 桿數最低 10% ( 含 ) 為紅色旗幟 (<=10) 7. 將 A1~N2 的填滿色彩為淺綠色 Excel 2010 [ 第 1 頁 / 共 6 頁 ] X10-1209
( 三 ) 參考結果如下所示 : Excel 2010 [ 第 2 頁 / 共 6 頁 ] X10-1209
一 電子股 1. 請至 C:\ANS.CSF\EX01 資料夾開啟 EXD01.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA01.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 調整欄位順序由左而右為 股票代號 時間 昨收 開盤 成交 最高 最低 買進 賣出 漲跌 張數 2. 在 L 欄新增欄位 : 欄位名稱為 昨收開盤與成交比較 欄寬為 15 自動換列 依據 昨收 開盤 成交 顯示 折線圖 類型的走勢圖, 並顯示 標記, 套用 走勢圖樣式彩色 #1 3. 在 M 欄新增欄位 : 欄位名稱為 成交與最高最低比較 欄寬為 15 自動換列 依據 成交 最高 最低 顯示 直條圖 類型的走勢圖, 並顯示 第一點, 套用 走勢圖樣式彩色 #1 4. 張數 欄位(K2~K65): 設定 資料橫條 / 漸層填滿 / 橘色資料橫條 的格式化條件 5. 設定第 1 列的列高為 35, 並設定 A1~M1 範圍的字型色彩為 白色, 背景 1 填滿色彩為藍色 6. 設定 A1~M65 範圍的內框線為最細實線, 外框線為次粗實線 Excel 2010 [ 第 1 頁 / 共 5 頁 ] X10-1208
( 三 ) 參考結果如下所示 : Excel 2010 [ 第 2 頁 / 共 5 頁 ] X10-1208
二 快樂小學學生名冊 1. 請至 C:\ANS.CSF\EX02 資料夾開啟 EXD02.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA02.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 依據性別, 在 H7 至 H61 計算標準體重 : 公式為 ( 身高 - 標準體重指標 )* 0.7, 並將結果無條件進位, 取至整數位 ( 注意 : 請使用 ROUNDUP IF 函數 ) 2. 計算增減體重 (I7~I61): 增減體重 = 標準體重 - 體重 3. 儲存格自訂數值格式 (I7~I62): 增減體重為正數, 顯示 公斤 ; 負數則顯示紅色 減 公斤 ; 增減體重零, 顯示藍色 完美身材 4. 計算平均值 : 在 F62 至 I62 分別使用 AVERAGE 函數計算 身高 體重 標準體重 增減體重 之平均值 ( 請勿更改原格式設定 ) 將此區域字體改成 Arial 字體 此區域 (F62~I62) 背景設為橙色 ( R:255 G:192 B:0) 色彩 5. 設定 A6~I62 範圍名稱為 學生名冊 6. 列印設定 : 指定列印 學生名冊 的範圍名稱 列印標題的範圍為第 6 列 Excel 2010 [ 第 3 頁 / 共 6 頁 ] X10-1206
( 三 ) 參考結果如下所示 : Excel 2010 [ 第 4 頁 / 共 6 頁 ] X10-1206
二 人事考評管理系統 1. 請至 C:\ANS.CSF\EX02 資料夾開啟 EXD02.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA02.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 3. 助專 領組 辦事員 助理員 工作表之 編號 欄位的編碼說明 :X-XXX-X-XX 第 1 碼 : 升等的等級, 第 3 4 5 碼 : 單位, 第 7 碼 : 組別, 第 9 10 碼 : 考試編號 ( 二 ) 設計項目: 1. 單位 組別 升等 欄位: 依據 編號 欄位的編碼說明, 以 VLOOKUP MID 函數查閱與欄名相同的範圍名稱, 傳回相對應的資料 ( 提示 : 升等 欄位利用 VALUE 函數 ) 2. 加權分數 欄位: A. 考核分數 : 利用 AVERAGE 計算 91 年 92 年及總考評的平均成績 91 年與 92 年考績分數, 依據 考績等級 範圍名稱查閱相對應的分數 總考評分數, 則依據 考評等級 範圍名稱查閱相對應的分數 ( 提示 : 利用 VLOOKUP 函數 ) B. 筆試分數 : 筆試分數 = 筆試共同分數 *0.3+ 筆試專業分數 *0.7 ( 注意 : 公式內不可使用百分比符號 ) 3. 合計 欄位: 合計 = 考核分數 *0.6+ 筆試分數 *0.4, 並以 ROUND 函數取至小數位數第一位 ( 注意 : 公式內不可使用百分比符號 ) 4. 名次 欄位: 以 RANK.EQ 函數計算排名 Excel 2010 [ 第 3 頁 / 共 5 頁 ] X10-1208
( 三 ) 參考結果如下所示 : Excel 2010 [ 第 4 頁 / 共 5 頁 ] X10-1208
二 合併第一季至第四季報表 1. 請至 C:\ANS.CSF\EX02 資料夾開啟 EXD02.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA02.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 第一季 至 第四季 工作表: A. 取至千位數值 : 四張工作表內所有數值, 其實際值均取千位, 例如 :815750 變成 815000 ( 提示 : 利用 ROUNDDOWN 函數 ) B. 計算每月總計項目 : 以 SUM 函數計算四張工作表內每月成本總和 2. 計算每個成本項目的平均 合計 標準差 : 以 AVERAGE 函數計算四張工作表內每月每個成本項目的平均值 以 SUM 函數計算四張工作表內每月每個成本項目的合計 以 STDEV.S 與 ROUND 函數計算四張工作表內每月每個成本項目的標準差, 四捨五入至整數位數 3. 計算總計項目的平均 合計 標準差 : E11 使用 AVERAGE 函數計算 B2~D10 範圍的平均值 F11 使用 SUM 函數計算 B2~D10 範圍的合計金額 G11 使用 STDEV.S 與 ROUND 函數計算 B2~D10 範圍的標準差 Excel 2010 [ 第 3 頁 / 共 6 頁 ] X10-1209
4. 年度報表 工作表: 應用合併彙算功能, 彙總 第一季 至 第四季 工作表內 一月 至 十二月 所有成本項目, 結果如下頁所示, 並需建立自動更新功能 ( 注意 : 需刪除 B 欄, 所有欄寬為 11.25) ( 三 ) 參考結果如下所示 : Excel 2010 [ 第 4 頁 / 共 6 頁 ] X10-1209
二 Booking 1. 請至 C:\ANS.CSF\EX02 資料夾開啟 EXD02.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA02.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 在 Booking 工作表, 利用 VLOOKUP TEXT 函數及 Hotel HotelDetail Check_in Check_out First_name 範圍名稱, 設定 Booking id 欄位中的資料顯示為:Hotel id-check in( mmdd) -Days-First Name 例如 :Hotel Izukyu 的 Hotel id 為 I1 Check in 為 2009/1/15 Check out 為 2009/1/16 Days 為 Check out-check in+1 First name 為 ESPOSITO, 則 Booking id 顯示為 I1-0115-2-ESPOSITO Bill List 工作表: 利用 VLOOKUP 函數與 Booking 範圍名稱 ( 提示 : 除 Booking 範圍名稱外, 不可使用其它範圍名稱 ) 2. Name 欄位: 依據 Booking id 搜尋 Booking 範圍名稱, 傳回 First name 與 Sur name 中間間隔一位空白 3. Room no. 欄位: 依據 Booking id 搜尋 Booking 範圍名稱, 傳回 Room no. 4. Nights 欄位: 依據 Booking id 搜尋 Booking 範圍名稱, 傳回 Check_in 及 Check_out, Nights=Check_out-Check_in 5. Price 欄位: 依據 Booking id 的前三碼, 串接 Room no. 後, 比對 LookupPrice 範圍內的 Price. ( 提示 : 利用 LEFT 函數 ) 6. Amount 欄位:Amount= Nights * Price 7. 套用 表格樣式中等深淺 9, 套用後再轉換為範圍 Excel 2010 [ 第 3 頁 / 共 6 頁 ] X10-1205
8. 列印設定 : 縮放比例調整為 80% 頁首 : 中央為工作表名稱 頁尾 : 中央選擇 第 頁, 共 頁 格式 列印標題的範圍為第 1 列 列印格線 ( 三 ) 參考結果如下所示: Excel 2010 [ 第 4 頁 / 共 6 頁 ] X10-1205
三 應屆畢業生升學人數數統計 1. 請至 C:\ANS.CSF\EX03 資料料夾開啟 EXD03.xlsx 檔設計 完成結果儲存於同一資料料夾之下, 檔案名稱為 EXA03.xlsx 2. 建立立或複製公式時需考慮是否需使用絕對位址 除題目要求更更改之設定外, 不不能任意改變原有之設定 ( 二 ) 設計項目: 1.C3~C6 儲存格 : 計算 A3~A6 各項所佔的百分比, 以 ROUND 函數數計算至小數數位數數第 3 位, 顯示百分比格式至小數數位數數第 2 位 2. 建立立圖表 : A. 圖表類類型 : 以資料料範圍 A2~ B6 儲存格製作 立立體分裂裂式圓形圖 B. 在圖表上方顯示圖表標題 : 98 學年年度度應屆畢業生 升學人數數統計, 字型大小為 12pt ( 提示 : 標題需分段顯示, 請參參閱參參考結果圖 ) 3. 圖表存放位置 :A8~E21 儲存格 ( 注意 : 圖表大小不不要超出此範圍 ) 4. 資料料標籤 : 顯示標籤及百分比 ( 置於終點外側並且換行行顯示 ), 文字大小為 9 5. 立立體旋轉透視圖 40 Y 軸 30, 刪除圖例例 ( 三 ) 參參考結果如下所示 : Excel 2010 [ 第 5 頁 / 共 5 頁 ] X10-1207
三 A 與 B 品牌進行二種儀器的檢測結果 1. 請至 C:\ANS.CSF\EX03 資料夾開啟 EXD03.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA03.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 根據 A 品牌 工作表的 A1~C402 B 品牌 工作表的 A1~C102 資料來源, 繪製圖表於新工作表 2. 將新工作表更改名稱為 比較圖表 3. 圖表類型 : 帶有直線的 XY 散佈圖 4. 標題 :( 標題樣式請參閱參考結果圖 ) A. 在圖表上方顯示圖表標題 :A 與 B 品牌執行二種儀器檢測結果比較圖 B.X 軸標題 ( 主水平軸標題 ): 測試間距 C.Y 軸標題 ( 主垂直軸標題 ): 變形弧度 5. 顯示水平主要與次要格線 垂直主要與次要格線 6. 將圖例改至下方顯示, 圖例說明文字為 12pt 大小 7. 水平 ( 數值 ) 軸 : 最小值為 1 最大值為 6 主要刻度為交叉型態 線條色彩為實心線條, 深黃褐 背景 2 較深 75% ( R:74 G:69 B:42) 色彩 線條樣式 :4.5pt 粗細 Excel 2010 [ 第 5 頁 / 共 6 頁 ] X10-1205
( 三 ) 參考結果如下所示 : Excel 2010 [ 第 6 頁 / 共 6 頁 ] X10-1205
三 萬事通銀行逾期放款分析表 1. 請至 C:\ANS.CSF\EX03 資料夾開啟 EXD03.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA03.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 根據 筆數與金額統計 工作表之樞紐分析表繪製圖表於新工作表 : A. 將新工作表更改名稱為 圖表分析 B. 圖表類型為組合直條圖與折線圖 ( 注意 : 放款筆數為含有資料標記的折線圖, 加總/ 總金額 為群組直條圖 ) 2. 設定 : 分組統計的加總 / 總金額對應副座標軸 分組統計的放款筆數對應主座標軸 3. 直條圖資料標籤 : 顯示在終點外側 4. 圖例 : 位置在圖表下方 5. 欄位按鈕必須設定全部隱藏 ( 請參閱參考結果圖 ) 6. 在圖表上方顯示圖表標題 逾期放款統計圖 大小 18 點 藍色 標楷體 粗體 Excel 2010 [ 第 5 頁 / 共 6 頁 ] X10-1206
( 三 ) 參考結果如下所示 : Excel 2010 [ 第 6 頁 / 共 6 頁 ] X10-1206
三 銷售訂單統計表 1. 請至 C:\ANS.CSF\EX03 資料夾開啟 EXD03.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA03.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 訂單 工作表:( 公式中一律使用範圍名稱 ) A. 建立範圍名稱 : 以 A1~I51 選取範圍中的頂端列建立範圍名稱 B. 訂單編號 : 訂單編號 (A2: A51) 為流水號前加上交易日期的 年 - 月 - ( 為交易日期的年份與月份 ), 例如 : 交易日期為 96-01-12 流水號為 034 則訂單編號為 96-01-034 ( 提示 : 請用 TEXT 函數 ) C. 成本 : 依據 品名 欄位搜尋 產品價格表 工作表內 產品 範圍名稱的成本 D. 成本總值 = 數量 * 成本 2. 銷售統計圖 工作表: A. 總銷售數量 : 依據 訂單 工作表內容, 利用 SUMIF 函數計算各產品總銷售數量 ( 公式中一律使用欄位範圍名稱 ) B. 繪製子母圓形圖 : 依據 A3~B9 儲存格資料繪製, 放在 A12~G25 範圍內, 第二個區域為 照片專用紙 與 彩色影印紙 C. 套用 圖表樣式 26 D. 關閉圖例 E. 圖表區 : 霧漸層填滿色彩, 框線為圓角 無陰影 框線色彩為紫色 (R:112 G:48 B:160) 3pt 實線 F. 在圖表上方顯示圖表標題內容為 總銷售數量 G. 資料數列 : 顯示類別標籤及百分比於資料點的終點內側 Excel 2010 [ 第 5 頁 / 共 6 頁 ] X10-1209
( 三 ) 參考結果如下所示 : Excel 2010 [ 第 6 頁 / 共 6 頁 ] X10-1209
三 各廠牌印表機性能比較表 1. 請至 C:\ANS.CSF\EX03 資料夾開啟 EXD03.xlsx 檔設計 完成結果儲存於同一資料夾之下, 檔案名稱為 EXA03.xlsx 2. 建立或複製公式時需考慮是否需使用絕對位址 除題目要求更改之設定外, 不能任意改變原有之設定 ( 二 ) 設計項目: 1. 在統計問卷工作表的 B30~L33 建立公式, 統計每種印表機廠牌的數量 ( 提示 : 必須使用 COUNTIF 函數 ) 2. 依據 A1~L1 及 A30~L33( 即第 1 題統計結果 ), 繪製圖表 : A. 新圖表工作表 : 將新工作表名稱更名為 雷達圖 B. 圖表類型為 帶有資料標記的雷達圖, 樣式 32 C. 圖表區文字 : 大小 18 點 D. 圖表圖例 : 在右方顯示, 大小 12 點 E. 取消格線 F. 座標軸線條色彩為 白色, 背景 1, 較深 50% G. 類別標籤文字顏色為深藍色 ( 三 ) 參考結果如下所示 : Excel 2010 [ 第 5 頁 / 共 5 頁 ] X10-1208