歐飛斯 Office 教學: 2008/9/7

TQC-EXC-310 監視器報價管理系統

注意事項︰建立或複製公式時需考慮是否需使用絕對位址。除題目要求更改之設定外，不能任意改變原有之設定。

利用檔案內已定義的範圍名稱，對『報價單』工作表作設定：

1. 項目：設定範圍B2～B21的資料驗證
依據「類別」的範圍名稱設定清單式的資料驗證

2. 產品編號：設定範圍C2～C21的資料驗證
依據「項目」欄位所選取的類別分別選擇相對應的清單，其清單的範圍名稱為「黑白基板式清單」、「黑白CCD系列清單」、「彩色CCD系列清單」、「監視器配件系列清單」、「腳架鏡頭配件清單」、「迴轉台配件清單」，以此清單項目設定清單式的資料驗證（提示：利用INDIRECT函數與範圍名稱）

選取 B2:B21 （選取 C2:C21）
資料 / 驗證

3. 輸入公式：依據「產品編號」所選取的編號搜尋相關工作表的產品名稱

(1) 產品名稱：設定範圍D2～D21的公式
(2) 單位：設定範圍E2～E21的公式
(3) 售價：設定範圍G2～G21的公式
（提示：利用IF、ISBLANK、VLOOKUP與INDIRECT函數及範圍名稱）

在 D2 中輸入 =IF(ISBLANK($C2),"",VLOOKUP($C2,INDIRECT($B2),2,0))

填滿 D3:D21

在 E 中輸入 =IF(ISBLANK($C2),"",VLOOKUP($C2,INDIRECT($B2),3,0))

填滿 E3:E21

在 G2 中輸入 =IF(ISBLANK($C2),"",VLOOKUP($C2,INDIRECT($B2),4,0))

填滿 G3:G21

說明：
利用 ISBLANK 函數來判斷 C 欄的編號是否空白，再利用 IF 函數來判斷非空白時要執行的運算。

4. 輸入二筆資料：在第7列與第8列輸入資料

(1) 項目為「腳架鏡頭配件」、產品編號為「SPP-80」、數量為6
(2) 項目為「迴轉台配件」、產品編號「BMW-125A」、數量為4

省略

5. 合計：使用SUMPRODUCT函數

(1) 在E22輸入報價總金額的公式（公式內的範圍請使用第2～21列，加入新報價資料合計金額會自動更新）
(2) 格式為「$****1,234」請利用會計專用格式修改，粗體字

在 E22 中輸入 =SUMPRODUCT(F2:F21,G2:G21)

填滿 G3:G21

6. 修改工作表標籤色彩：

(1)『報價單』為深黃褐色（第五列第二欄）
(2)『類別』為灰-50%（第二列第八欄）
(3)『黑白基板式』、『黑白CCD系列』為黑色（第一列第一欄）
(4)『彩色CCD系列』為玫瑰紅色（第五列第一欄）
(5)『監視器配件系列』為淺黃色（第五列第三欄）
(6)『腳架鏡頭配件』為淺綠色（第五列第四欄）
(7)『迴轉台配件』為淺綠藍色（第五列第五欄）

在索引標籤名稱上按右鍵，點 |索引標籤色彩|

7. 設定格式：
從『黑白基板式』至『迴轉台配件』每一張工作表，均指定
A欄的欄寬為5、B欄的欄寬為15、C欄的欄寬為40、D欄的欄寬為5、E欄的欄寬為15。

省略

8. 將處理結果儲存於磁碟機根目錄檔名為EXA05.XLS。參考解答下載頁

TQC-EXC-308 分期付款帳款管理系統

注意事項︰建立或複製公式時需考慮是否需使用絕對位址。除題目要求更改之設定外，不能任意改變原有之設定。

利用檔案內已定義的範圍名稱，在下列工作表內作設定：

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。參考解答下載頁

TQC-EXC-306 人事考評管理系統

注意︰建立或複製公式時需考慮是否需使用絕對位址。除題目要求更改之設定外，不能任意更改。

『助專』、『領組』、『辦事員』、『助理員』工作表：
「編號」欄位的編碼說明：X-XXX-X-XX
第1碼：升等的等級第3、4、5碼：單位
第7碼：組別第9、10碼：考試編號

1.「單位」、「組別」、「升等」欄位：
依據「編號」欄位的編碼說明，以VLOOKUP、MID函數搜尋與欄名相同的範圍名稱，傳回相對應的資料（提示：「升等」欄位利用VALUE函數）

以下公式皆需完成包含 \ 助專 /、\ 領組 /、\ 辦事員 /、\ 助理員 / 四張工作表中相關儲存格
在 B3 中輸入 =VLOOKUP(MID($A3,3,3),單位,2,0)
在 D3 中輸入 =VLOOKUP(MID($A3,7,1),組別,2,0)
在 E3 中輸入 =VLOOKUP(VALUE(MID($A3,1,1)),升等,2,0)
填滿儲存格

說明：
利用 MID 函數從編號的欄位中將符合單位與組別的代號字串取出，然後再用 VLOOKUP 從 \ 單位 /、\ 組別 / 中找出符合的代號而取得單位。

升等代號是數字型態，經由 MID 函數從編號中取得的代號是文字型態，所以需要 VALUE 函數將文字轉成數字。

2.「加權分數」欄位：

(1) 考核分數：利用Average計算91年、92年及總考評的平均成績。
91年與92年考績分數，依據「考績等級」範圍名稱搜尋相對應的分數。總考評分數，則依據「考評等級」範圍名稱搜尋相對應的分數（提示：利用VLOOKUP函數）

以下公式皆需完成包含 \ 助專 /、\ 領組 /、\ 辦事員 /、\ 助理員 / 四張工作表中相關儲存格
在 K3 中輸入 =AVERAGE(VLOOKUP(F3,考績等級,2,0),VLOOKUP(G3,考績等級,2,0),VLOOKUP(H3,考評等級,2,0))
在 L3 中輸入 =I3*0.3+J3*0.7
填滿儲存格

(2) 筆試分數：筆試分數＝筆試共同分數×30%＋筆試專業分數×70%

在 L3 中輸入 =I3*0.3+J3*0.7
填滿儲存格

3.「合計」欄位：合計＝考核分數×60%＋筆試分數×40%
並以ROUND函數取至小數位數第一位

在 M3 中輸入 =ROUND(K3*0.6+L3*0.4,1)
填滿儲存格

4.「名次」欄位：以Rank函數計算排名，並依據名次順序排列資料

在 N3 中輸入 =RANK(M3,$M$3:$M$40)
填滿儲存格
選取 A3:N40
資料 / 排序

5. 將處理結果儲存於磁碟機根目錄檔名為EXA03.XLS。參考解答下載頁

TQC-EXC-304 成功國中基本學力模擬測驗記錄表

Office 2003 新增題型

注意事項︰建立或複製公式時需考慮是否需使用絕對位址。除題目要求更改之設定外，不能任意改變原有之設定。

1. 根據『各科成績記錄表』工作表的內容，計算『各科成績次數分配表』中各科目、各級距的人次與累計人次。

(1) 國文、英文、數學、社會、自然等各科人次的計算，請使用FREQUENCY()函數，各級距的參照位置，請使用欄M的資料。

切換至 \ 各科成績次數分配表 / 工作表
在 B5 中輸入 = FREQUENCY(各科成績記錄表!$C$4: $C$53, M5:M6)
填滿 B5:B15
同理在 D5 中輸入 = FREQUENCY(各科成績記錄表!$D$4: $D$53, M5:M6)
填滿 D5:D15
依上述公式完成數學、社會、自然

(2) 計算國文、英文、數學、社會、自然等各科、各級距的累計人次。

在 C5 中輸入 =B5
在 C6 中輸入 = C5+B6

填滿 C7:C15

依上述公式完成

2. 將『各科成績記錄表』工作表中的「總分」及「名次」內容，以貼上連接方式貼至『五科總分百分等級表』工作表中的欄C及欄D相對應的位置。

選取 \ 各科成績記錄表 / 的 H4:I53
按 Ctrl + C
點 \ 五科總分百分等級表 / 的 C4 儲存格，按
依上述步驟完成「名次」的操作

3. 在『五科總分百分等級表』工作表中，根據每位學生的名次，計算其相對應的百分等級：
百分等級公式說明：

在 E4 中輸入 =100-(100/50)*(D4-0.5)
填滿 E4:E53

4. 在『五科總分百分等級表』工作表中，根據計算出的百分等級數值，由大到小進行排序。

選取 A3:E53
資料/排序

5. 將處理結果儲存於磁碟機根目錄檔名為EXA02.XLS。參考解答下載頁

TQC-EXC-302 班費收支表

注意事項︰建立或複製公式時需考慮是否需使用絕對位址。除題目要求更改之設定外，不能任意改變原有之設定。

1. 在『班費收支表』工作表之「合計」欄位（I4~I11）以SUM函數計算各項費用一星期的總和。

選取I4:I11
按一般工具列自動加總鈕

2. 為「合計」欄（I4~I11）定義顯示格式，如「$12,333」紅色格式。

選取I4:I11
格式 / 儲存格

3. 複製工作表：複製『班費收支表』工作表，並更名為「班費收支排序」。

依下圖複製工作表
依上圖所示選 [ 重新命名 ]

4. 更改『班費收支排序』工作表欄位標題：

(1) 將A3儲存格內容改為「支出排序」。
(2) 利用編輯填滿於B3~H3，填入「排名1、排名2…排名7」。

在 A3 儲存格輸入 " 支出排序 "

在 B3 輸入排名 1，向右填滿至 H3

5. 排序：

(1) 在『班費收支排序』工作表上排序。
(2) 每一項支出依高低排序，「排名1」為此週最高消費額，「排名7」為此週最低消費額。

選取列範圍 B4:H4

資料 / 排序

選取列範圍 B5:H5，按 F4 (重覆上次動作)

選取列範圍 B6:H6，按 F4

重覆上述動作至 B11:H11 完成

6. 框線：設定『班費收支排序』工作表A3~I11範圍加上雙實線外框。（線條樣式右邊倒數第1種樣式）

選取 A3:I11
格式 / 儲存格

7. 將處理結果儲存於磁碟機根目錄檔名為EXA01.XLS。參考解答下載頁

訂閱：意見 (Atom)