注意事項︰建立或複製公式時需考慮是否需使用絕對位址。除題目要求更改之設定外，不能任意改變原有之設定。
利用檔案內已定義的範圍名稱，在下列工作表內作設定：
1.『商品』工作表
(1) 移除原有「商品名稱」的超連結法設定
- 選取 A2:A69
- 按 Ctrl + C
- 點 D2
- 按 Ctrl + C
- 點 A2
- 按 Ctrl + V
(2) 找出「金額」欄中的三處錯誤，修正錯誤後，並將此三個儲存格格式設定成與其他金額同。
- B3、B31、B43
2. 『分期付款』工作表：
(1)「購買金額」：依據「商品」搜尋「商品目錄」範圍名稱內的「金額」
- 切換到 \ 分期付款 / 工作表
- 在 C2 中輸入 =VLOOKUP(B2,商品目錄,2,0)
(2)「繳別」欄位：設定資料驗証，清單來源名「繳別」的範圍名稱
(3)「年期」欄位：設定資料驗証，清單來源名「年期」的範圍名稱
(4)「期別」欄位（本次繳費的期別）：設定資料驗証，依據「繳別」與「年期」組合指定為清單來源。例如：繳別為「月繳」、年期為「2」，則該筆資料列清單來源為「月繳2」的範圍名稱（提示：利用INDIRECT函數）
- 選取 D2:D41
- 資料 / 驗証
說明：
「繳別」、「年期」為事先定義的範圍名稱；(4)小題中是利用 INDIRECT 函數來取得 D2&E2 的字串結合「年繳2」的範圍名稱
(5)「每期金額」：
依據分期付款函數PMT計算不同繳別、不同年期、其每期應繳金額，並取四捨五入至整數位（利率在「繳別倍數」的I2儲存格內）
（提示：利用ROUND、PMT、VLOOKUP函數）
- 在 G2 中輸入 = -ROUND(PMT(年利率/VLOOKUP(D2,繳費期數,2,0) , E2*VLOOKUP(D2,繳費期數,2,0) , C2) ,0)
說明：
PMT 的3個引數分別是「利率」「總繳期數」「繳費總和」，根據函數規則，「利率」和「總繳期數」的單位 (年、半年、季、月) 要一致。
題目中的「利率」4.5% 是「年利率」（範圍名稱），所以各個客戶的「利率」應該隨 繳別 的不同而轉換成不同期數的相對利率。所以利用 VLOOKUP 來找出期數 (1,2,4,12) ，再將「年利率 / 期數」就得到各期相對的利率。當然「總繳期數」亦隨 年期 的不同來轉換，原理相同。(6) 設定格式化條件：A2～H41
「購買金額」在5000以下（不含），「年期」只能選擇一年，「購買金額」在5000以上則無限制
當選擇項目錯誤時，整列資料顯示紅色網底、白色粗體字
- 選取 A2:H41
- 格式 / 設定格式化條件
3. 將處理結果儲存於磁碟機根目錄檔名為EXA04.XLS。參考解答下載頁
沒有留言:
張貼留言