MOS2019

認證考試總共6個題組共25個小題，時間50分鐘 (總分1000分、要考700分、只能錯3小題，每個小題配分不同。)
本版本11個題組為整合AB版的題組(#AB版會有共通題組)。
GOTOP 劉道弘 <nick_liu@gotop.com.tw>

1-4 在「折扣」工作表，使用錯誤檢查功能找出與附近不一致的公式。更正錯誤。

A: 公式>更正錯誤>從上方複製公式。

1-5 在「折扣分析」工作表上，插入一個可以讓您依照「會員等級」來篩選樞紐分析表的交叉分析篩選器。使用此交叉分析篩選器來篩選並僅顯示「會員等級」為「A」的記錄。 交叉分析篩選器的大小和位置不重要。

A: 選樞紐分析表，樞紐分析表工具/分析> 插入交叉分析篩選器。選A。

2-1 請在「禮盒銷售表」工作表上，使用資料小計功能來計算出每位銷售員的總數量與總金額。在資料下方顯示每位銷售員的總數量與總金額。 如有必要，請先排序資料，以確保每個欄位只有一個小計。

A:要先針對"銷售員名稱"排序 直接C3 A->Z； 資料>小計 >小計欄位選: 銷售員名稱, 函數:加總, 新增小計位置 勾選 數量&金額 > 確定。

2-2 在「銷售員」工作表上，修改套用於儲存格 B4:B7 的設定格式化的條件規則，將［粗體］字型套用至包含文字「週末」的儲存格，且不填滿色彩。

A: 先選B4:B7 設定格式化條件>管理規則>粗體/填滿 無色彩；也可在 規則管理員 顯示格式化規則~夏拉 選這個表格、可以一次挑出所有其他的規則。

2-3 在「銷售日期」工作表上的儲存格 B9 中，使用函數來計算水果行的開始銷售日期是星期幾。 注意：該儲存格已格式化為顯示星期幾的名稱。

A: =WEEKDAY(B8) ；直接選要判斷的日期欄位。 儲存格格式設定 自訂:[$-zh-TW]dddd

2-4 在「收入」工作表上建立一份圖表，以折線圖顯示每位銷售員的「基本薪資」，並在相同軸上以群組直條圖顯示「加給獎金」。 圖表的大小和位置不重要。

A: 收入資料表/選 整個表(包含欄位) 插入>建議圖表>所有圖表>組合圖 挑選正確的圖表類型即可。

2-5 在「分析」工作表上，修改圖表以使用［版面配置 8］和［樣式 6］。將圖表色彩變更為［單色的調色盤 4］，並將圖表標題變更為「禮盒收入」。

A: 先點選分析頁的樞紐分析圖> 快速版面配置~配置8> 圖表樣式6> 變更色彩 單色4。改圖表標題 點一下空白處以更新輸入。

3-1 請設定 Excel 僅在儲存活頁簿時自動重算公式，而不是在每次資料變更時才重算公式。

A: 檔案 選項 公式> 計算選項/手動 勾選 儲存活頁簿前自動重算。

4-1 在「總銷售額」工作表上的儲存格 D4:E7 中，利用填滿數列功能，以每季「40000」的等差級數成長來完成銷售額的預估。

A: B3:C7框選 控點拖拉 兩欄 即可產生填滿等差40000數據。

4-2 在「職員」工作表上的儲存格 C4 中，輸入公式，使其藉由 B 欄「區域」的內容，進行完全相符的比對，傳回來自「連絡電話」儲存格範圍內的電話號碼。

A: 公式(查閱與參照)HLOOKUP； 選B4、已經被表格化 所以B4顯示為[@區域]；選H2:K4(已經有範圍名稱)；第四引數入0或FALSE。已經被設為表格所以會自動填滿B4的公式。

4-3 從「職員」工作表中，建立名為「主題」的巨集。並在目前的活頁簿中儲存巨集。設定此巨集可以在使用中的工作表之頁首中間區段裡顯示文字「Andy水果行」。

A: 左下角 錄製巨集、輸入名稱 主題>> 版面配置 進入設定>頁首/頁尾> 自訂頁首 中 > 輸入 Andy水果行、確定，記得 左下角 停止錄製巨集。

4-4 在「方案分析」工作表上，修改樞紐分析表，使其依照「售價」欄位裡的值將資料進行分組。分組時以「250」為間距(從「1」開始到「500」結束)。

A: 選樞紐分析表 售價任一欄位、 右鍵 組成群組 設定 開始點(1) 結束點(500) 間距值(250) 即可。

5-1 在「2020年銷售數量」工作表上，將儲存格 I4:I31 的資料驗證錯誤訊息變更為「請輸入1到28之間的數字」。

A: 先選 I4:I31 (用快速鍵)、資料>資料驗證>錯誤提醒 訊息內容 輸入"請輸入1到28之間的數字"。

5-2 在「2020年銷售數量」工作表上的儲存格 F4:F31 中，建立使用三色色階格式樣式的設定格式化的條件規則，以橙色顯示最小值，以黃色顯示中間點，並以淺藍色顯示最大值。

A: 選F4:F31 常用>設定格式化條件>新增規則 根據其值格式化儲存格 樣式:三色色階 > 改顏色(標準色彩)即可。

5-3 在「2020年銷售數量」工作表上的 H 欄中，輸入公式使其可運用「第四季」工作表的資料，傳回產品的銷售數量。

A: 也是查表 這次是查欄 VLOOKUP (HLOOKP是搜尋列)。 =VLOOKUP([@水果名稱],表1[[水果名稱]:[銷售數量]],2,0) #資料範圍都有設為表格了!!
#教學是使用函數引數小視窗做設定。

5-4 在「數量變化」工作表上的樞紐分析表中，建立名為「差異」的計算欄位，顯示 第一季 到 第二季的銷售數量。***

A: 選樞紐分析表 工具列 分析/ 計算 欄位,項目> 計算欄位 輸入 名稱 與 公式: 第一季銷售數量 - 第二季銷售數量。

6-1 請設定 Excel 可以讓您使用［法文(比利時)］作為編輯語言來編輯內容。 請勿將法文(比利時)設為預設編輯語言。如果系統提示您重新啟動 Office，請關閉提示，不要重新啟動 Excel。

A: 檔案 選項 語言 新增語言> 法文(比利時) ，認證時不要重開EXCEL。 #練習時可以移除之前新增的語言

6-2 在「售價」工作表上，使用 Excel 功能從「產品名稱」範圍(儲存格 A4:M48 )移除重複的記錄。

A: 售價切頁 選取資料含欄位 資料>移除重複項 出現欄位名稱 先取消全選 再勾選 產品名稱欄位， 確定 即可。

6-3 在「顧客」工作表上的儲存格 H4 中計算客戶數量，其中「水果喜好」為「蘋果」，「一個月內購買次數」超過 10次。***

A: =COUNTIFS(B4:B98,"蘋果",C4:C98,">10")

6-4 在「水果行拓展」工作表的儲存格 B7 中，使用函數來計算繳清貸款所需的月份數。

A: 如 練習檔案函數 =NPER(B4/12,B5,B3)

6-5 在「行銷活動」工作表上，建立顯示「1月」行銷活動資料的群組橫條圖，並在圖表左側顯示描述。將圖表標題變更為「一月活動」。圖表的大小和位置不重要。

A: 如 練習檔案

6-6 在「顧客分析」工作表上，建立一個可以顯示樞紐分析表資料的立體群組直條圖樞紐分析圖。篩選圖表僅顯示銷售員為「Joe」的資料。圖表的大小和位置不重要。

A: 如 練習檔案 # 使用 插入 建議圖表。 #使用紐分析圖做篩選。

7-1 在 「2021年」工作表上，修改設定格式化的條件規則，使其格式化表格的資料列為「20呎」超過 10,000 貨櫃的港口。

A: 如練習檔案 設定視窗。

7-2 在「摘要」工作表上，從儲存格 A5 開始，合併彙算「2018年」和「2019年」工作表中的資料。顯示每個「地區」的「貨櫃量」合計值。在頂端列和最左欄中使用標籤名稱。 從合併後的資料中刪除空白的「港口」欄。

A: 如練習檔案 設定視窗。 選A5 、資料>合併彙算 函數:加總 、參照位址：新增兩個年度(包含欄位名稱A~C欄 、B欄用不到稍後刪除)，標籤名稱:頂端列、最左欄勾選。

7-3 在「貨櫃量分析圖」圖表上，向上縮小資料以顯示每季的貨櫃量。

A: 選該樞紐分析圖、有季跟月的資料，本題為縮減資料量、選 摺疊整個欄位 選-；也可能考展開+。

8-1 在「業務會議」工作表上，將儲存格 A4:A10 設定為僅允許 1 到 7 的整數。否則，顯示［警告］錯誤警訊，標題為「Error」，訊息內容為「請輸入1到7」。

A: 資料驗證，如練習檔案。

8-2 在「年度銷售量」工作表上的儲存格 L5:L13 中輸入公式，可計算並傳回與J欄裡的「地區」相符，並且與K欄裡的「代表編號」相符的「銷售量總計」之平均值。***

A: 資料範圍要先做成表格。Criteria Range1 選B4:B25, Criteria1: J5, Criteria Range2 A4:A25, Criteria2:K5 .... 如練習檔!。

8-3 在「業務會議」工作表上的「報名期限」欄位中，使用函數計算報名截止日期。此報名期限是在 E 欄會議日期之前的 20 個工作日。 在計算過程中應排除名為「休假日」範圍裡所指定的假日。

A: =WORKDAY.INTL([@會議日期],-20,,休假日) #休假日(一個被隱藏的工作表)。

8-4 在「銷售摘要」工作表上建立一份圖表，以直條圖圖表顯示每位代表的「銷售總計」，並在副座標軸以折線圖圖表顯示「總計百分比」。圖表的大小和位置不重要。

A: 間隔選，插入圖表 建議圖表 所有圖表> 組合圖 如練習檔!。

8-5 在「地區銷售量」工作表上，修改樞紐分析表使其在各地區底下逐列顯示每一個「縣市」。

A: 樞紐分析表，縣市勾選 並確認位置在地區之下，如練習檔!

9-1 在「激勵金」工作表上的儲存格 E4 中，輸入可從「銷售量激勵金」表格中傳回該員工獎金的公式。
請調整公式，然後將其複製到儲存格 E4:E20。***

A: =VLOOKUP(C4,$G$2:$H$6,2) #第4個引數，邏輯判斷省略。 找最接近的!!! ##此備表數據間隔必須要依序排列 > 如練習檔案!!

9-2 在「主機板」工作表上建立長條圖圖表，並以 $2000 元為間隔寬度來顯示主機板的「售價」。圖表的大小和位置不重要。***

A: 長條圖(非直條圖)!! 座標軸格式>間隔 2000。

10-1 在「縣市銷售量」工作表上的儲存格 D4:G6 中，利用填滿數列功能，以每年「300,000」的等差級數成長來完成銷售量的預估。

A: 縣市銷售量 B4:C6選取，控點拖曳 自動填滿 產生數據(自動等差間隔)，如練習檔!。

10-2 在「熱門類別」工作表上，為儲存格 B4:E15 建立設定格式化的條件規則，以粗體藍色字型顯示最高的3個值。

A: 選B4:E15 設定格式化條件，新增規則 >只格式化排在最前面... 填入 前 3，設定 格式。

10-3 在「外送員」工作表上的儲存格 C4 中，輸入公式，使其藉由 B 欄「縣市」的內容，進行完全相符的比對，傳回來自「各縣市連絡電話」儲存格範圍內的連絡電話號碼。

A: HLOOKUP 橫向 在列上面查詢。 回傳第3列的值。 =HLOOKUP([@縣市],各縣市連絡電話,3,0)

11-1 在「電影」工作表上，為儲存格 E3:E11 建立並套用自訂數字格式，並以「2020-Jan-01」的格式來顯示日期。

A: 選E4:E11 (含標題列)，自訂儲存格格式 yyyy-mmm-dd # mm (顯示數字月份)/ mmm(顯示縮寫) / mmmm(4各m顯示完整月份英文名稱)

11-2 在「版權」工作表上，「應付版權」欄中的現有公式會計算應付給每個團隊的版稅。 請修改現有「應付版權」公式，使其在金額大於 $1,000,000 的情況下，才傳回應給付給製作團隊的版權，否則傳回 0。

A: F4>1,000,000 才顯示數值、否則顯示0；=IF((D4*C4)-E4>1000000,(D4*C4)-E4,0) ； IF公式 為否的引數，直接輸入數字0不加引號!!

11-3 在「銷量分析」工作表上，插入一個可以讓使用者根據「類別」來篩選樞紐分析表的交叉分析篩選器。然後使用此交叉分析篩選器來篩選資料，僅顯示「台灣電影」。 交叉分析篩選器的大小和位置不重要。

A: 插入交叉分析篩選器，如練習檔。

11-4 在「版權分析」工作表上，將「電影編號」欄位新增為樞紐分析圖篩選。將篩選套用於圖表，並僅顯示電影編號為「TW01」的結果。

A: 點 樞紐分析表> 勾選 電影編號 選入 "篩選"，在勾選TW01，如練習檔。