第 2 章 銀行招考成績計算 著作權所有 © 旗標出版股份有限公司.

Presentation on theme: "第 2 章 銀行招考成績計算 著作權所有 © 旗標出版股份有限公司."— Presentation transcript:

1 第 2 章 銀行招考成績計算 著作權所有 © 旗標出版股份有限公司

2 應用實例說明 銀行每次舉辦新人招考總是門庭若市, 甚至還得向學校租借教室來舉行考試才敷使用。因此考試成績的計算工作自然十分繁重, 為了減少計算錯誤及提高作業效率, 最好使用電腦來代替人工計算。

3 應用實例說明 大力銀行準備招考本年度的行員, 考試科目有國文、英文、綜合科目 (貨幣銀行學、票據法、會計), 考試成績分為二個階段, 第一階段為筆試, 通過筆試測驗者得以進行第二階段的口試。 此次預計的錄取名額為 8 名, 應考人數有 50 人, 以下為成績計算方式及錄取標準：

4 應用實例說明 筆試總分的計算方式是依據各科的佔比來計算, 國文佔 25%, 英文佔 15%, 綜合科目 (貨幣銀行學、票據法、會計) 佔 60%。 筆試測驗科目任何一科為零分, 或是總成績未達六十分者為不得參加口試。

5 應用實例說明 此次招募考試的總成績為筆試的總分加上口試成績, 其中筆試成績佔 80%, 口試佔 20%。
排名次時,若總成績相同, 則依序以國文、英文、綜合科目分數之高低來決定錄取標準。

6 本章提要 求算各科平均及計算筆試總分 計算筆試合格的人 筆試成績是否合格 條件式格式化 計算筆試合格與不合格人數 計算總成績 排名次
查詢個人成績

7 求算各科平均及計算筆試總分 求算各科平均分數 計算筆試分數

8 求算各科平均分數 為了解今年度應試者的人員素質, 我們先來替大力銀行計算此次應試者的各科平均分數, 請開啟範例檔案 Ch02-01, 並拉曳垂直捲軸捲動到工作表最底端, 然後如下操作：

9 求算各科平均分數

10 求算各科平均分數

11 求算各科平均分數

12 求算各科平均分數 接著, 請拉曳儲存格 C52 的填滿控點到 G52儲存格, 即可算出各科的平均分數了：

13 求算各科平均分數

14 『自動計算』功能 除了以自動加總鈕來計算平均外, Excel 還提供一種自動計算功能, 讓您在不需撰寫公式或函數的情況下, 就能快速得到運算的結果, 其操作如下：

15 『自動計算』功能

16 『自動計算』功能

17 『自動計算』功能 若想計算某一科的最高分或最低分是多少, 就可利用自動計算功能, 選取快顯功能表中的『最大值』或『最小值』即可求得。

18 計算筆試分數 求算出各科的平均分數後, 接著我們來計算每人的筆試總分, 筆試總分的計算公式如下：
請選定儲存格 H2, 在資料編輯列中輸入公式" =(C2*0.25)+(D2*0.15)+((E2+F2+G2)/3) *0.6", 完成後, 請按下 [Enter] 鍵：

19 計算筆試分數

20 計算筆試分數 要算出其他人的筆試成績, 只要拉曳儲存格 H2 的填滿控點到儲存格 H51 即可。

21 計算筆試分數 求算出來的總成績, 因為小數位數不同, 所以看起來有些零亂, 若要讓資料看起來整齊美觀, 您可以選取 H2：H51 儲存格, 然後執行『格式/儲存格』命令, 開啟儲存格格式交談窗, 設定小數位數：

22 計算筆試分數

23 計算筆試分數

24 計算筆試合格的人 由於此次的招募考試需筆試合格, 才能進行口試, 且筆試成績有一科為零分, 或者是筆試的總成績未達六十分, 都是不能參加口試的, 因此接下來我們要選出有資格參加口試的人。 筆試成績是否合格 條件式格式化 計算筆試合格與不合格人數

25 筆試成績是否合格 了解參加口試的資格後, 請開啟範例檔案 Ch02-03, 然後選定儲存格 I2, 我們可以將公式寫成：

26 IF 與O R 函數的用法 IF 函數主要是依條件的成立與否來判斷要傳回的值。 其語法為：

27 IF 與O R 函數的用法 而 OR 函數則是, 只要有任何一個引數為TRUE (真), 便傳回 TRUE；若是所有引數都為 FALSE (假) 時, 才會傳回 FALSE 值。其語法如下： OR 函數最多可接受 30 個引數, Logical1,Logical2,…便是您想要測試其為TRUE 或 FALSE 的條件。

28 筆試成績是否合格 請在儲存格 I2 中輸入以上公式： 接著, 拉曳儲存格 I2的填滿控點到儲存格 I51, 即可算出所有人是否符合資格。

29 條件式格式化 算出筆試成績是否合格後, 我們可以利用格式化的條件功能, 將不合格的資料特別標示出來以做辨識。
請選定儲存格 C2：G51, 然後執行『格式/設定格式化的條件』命令,我們要來設定當選取的儲存格範圍等於0, 則以紅色、粗體標示：

30 條件式格式化

31 條件式格式化

32 條件式格式化

33 條件式格式化 請仿照上述步驟, 將筆試總分小於60分的也以紅色、粗體標示。 您可以開啟範例檔案 Ch02-04來觀看結果。

34 計算筆試合格與不合格人數 計算出所有人的筆試成績後, 若要查看筆試成績合格與不合格的人數各有多少。
我們可以利用 COUNTIF 函數來計算, 底下先舉一個簡單的例子來為您說明函數的用法：

35 計算筆試合格與不合格人數

36 計算筆試合格與不合格人數 另外, COUNTIF 函數也可以這樣用：

37 計算筆試合格與不合格人數 了解 COUNTIF 函數的用法後, 請開啟範例檔案 Ch02-04, 然後如下操作：

38 計算筆試合格與不合格人數

39 計算筆試合格與不合格人數

40 計算筆試合格與不合格人數

41 計算筆試合格與不合格人數

42 計算筆試合格與不合格人數 算出合格的人數之後, 不合格的人數則可以用總人數減去合格人數來求得, 也可以再次使用 COUNTIF 函數來幫我們求出結果。為了加深您的印象, 我們仍然使用 COUNTIF 函數來求算不合格人數。 請選定 C54 儲存格, 然後輸入公式 "=COUNTIF(I2:I51,"不合格")",即可求算出不合格的人數：

43 計算筆試合格與不合格人數

44 計算總成績 篩選筆試合格的人 移除篩選 取消自動篩選箭頭 複製篩選後的記錄 將口試成績複製過來 計算總成績

45 篩選筆試合格的人 篩選是呈現記錄的一種方式, 透過篩選功能, 可以將不符合尋找準則的記錄暫時隱藏起來, 只留下您要的記錄。在此我們就要利用篩選功能, 將筆試合格的人篩選出來, 隱藏不合格的人, 以進行後續的計算工作。 請開啟範例檔案 Ch02-05 並切換至成績計算工作表, 然後選取成績資料中的任一儲存格, 執行『資料/篩選/自動篩選』命令：

46 篩選筆試合格的人

47 篩選筆試合格的人 按下自動篩選鈕會出現一個列示窗, 顯示該欄所有資料經過歸類整理的結果, 我們便是從這些列示窗來設定自動篩選條件。
例如我們要找出「合格」的記錄, 則按下合格與否欄的自動篩選鈕選擇 "合格", 清單中就只會剩下符合條件的記錄, 其它的記錄則被隱藏起來了：

48 篩選筆試合格的人

49 篩選筆試合格的人

50 篩選筆試合格的人 符合條件的記錄其列標題會改用藍色顯示, 而用來設定篩選條件的欄位 (如合格與否欄), 其自動篩選鈕中的箭頭亦會變成藍色。
之後, 我們還可以在目前的篩選結果上繼續設定其他欄位的篩選條件。

51 移除篩選 移除一欄的篩選：若要移除某欄所設定的篩選條件, 只要在該欄的自動篩選鈕列示窗中選擇全部項目, 則被隱藏的記錄就會重新顯示出來。
移除所有欄位的篩選：如果清單中有多個欄位都設有篩選條件, 則執行『資料/篩選/全部顯示』命令, 可一次就移除掉所有欄位的篩選條件。

52 取消自動篩選箭頭 再次執行『資料/篩選/自動篩選』命令, 取消該命令前的符號, 則 Excel 會移除所有欄位的篩選條件, 恢復原來的樣子, 並將欄位名稱旁的自動篩選鈕取消。

53 複製篩選後的記錄 篩選出「合格」的記錄後, 我們要將成績計算工作表中顯示的資料全部複製到總成績工作表中：
請將滑鼠指標移到准考證號欄的欄標題上, 當指標出現 狀時, 按一下滑鼠左鈕, 選取整欄：

54 複製篩選後的記錄

55 複製篩選後的記錄 若想選取不連續的多欄, 只要在選取一欄後, 按住 [Ctrl] 鍵不放, 繼續點選其它欄即可。
選好要複製的範圍後, 請執行『編輯/複製』命令, 然後切換到總成績工作表, 選定A1儲存格, 執行『編輯/貼上』命令, 即可將資料複製過來：

56 將口試成績複製過來 經過一番激烈的競爭, 口試成績已經出爐了, 為方便待會兒進行總成績的計算, 請將口試成績工作表中的口試成績欄複製到總成績工作表的 J 欄, 以利於計算總成績。

57 計算總成績 進行到此, 我們就可以計算每個人的總成績了, 總成績的計算方式如下：
請在總成績工作表中的 K1儲存格輸入 "總成績", 然後選定 K2 儲存格輸入"=H2*0.8+J2*0.2"：

58 計算總成績 接著, 拉曳儲存格 K2的填滿控點到儲存格K30, 即可算出所有人的總成績了。

59 計算總成績

60 排名次 算出每個人的總成績之後, 接著我們要依照成績的高低來排名次, 以決定錄取的人選。 排序資料 填入名次

61 排序資料 在排序之前, 我們必須先瞭解基本的「清單」概念。
Excel 的清單必須是由連續的欄和列所組成, 其中每一欄的第一列為標題欄, 說明該欄資料的性質, 例如：准考證號、姓名、筆試成績、…等。 清單常用來處理大量的資料, 您可將它視為資料庫來使用。

62 排序資料 以資料庫常用的術語來說明清單的結構, 則每一欄稱為一個欄位, 每一列稱為一筆記錄, 第一列的名稱就稱為欄位名稱。
不過, 要注意的是, 一組清單範圍內不可以有空白列或空白欄。

63 排序資料 我們可以將每位考生的成績資料視為一筆「記錄」, 而每當我們排列資料時, 每個學生的資料必須隨時保持在同一列中, 也就是說所有搬移的動作都是整個「記錄」資料的移動。 排序時一定要有所依據 (例如本例中的「總成績」), 這個依據就稱為「鍵值」, 一般我們都會從每個「記錄」中選取一種資料來當作「鍵值」,或稱為「主要鍵」。

64 排序資料 若資料中「主要鍵」的值都相等, 有時還需要有「次要鍵」或是「第三鍵」才能分出高下。
請開啟範例檔案 Ch02-06, 切換到總成績工作表中, 選取清單中的任一儲存格, 然後執行『資料/排序』命令：

65 排序資料

66 排序資料

67 填入名次 將成績排序好之後, 請在 L1中輸入 "名次", 我們只需要在名次欄中由上至下填入 1、2、3、…等數字, 就可以完成排名了。
請在儲存格 L2 中填入 "1", 然後拉曳填滿控點到 L30：

68 填入名次

69 填入名次

70 填入名次 由於此次招考僅錄取 8 名, 故填好名次之後, 排名在前 8 名的人即為錄取。

71 查詢個人成績 成績、名次都計算出來之後, 為了方便考生查詢, 我們來建立一個小小的成績查詢系統, 只要輸入考生的准考證號碼, 就可以馬上查出該考生的各科成績、總分及名次。

72 查詢個人成績

73 認識 VLOOKUP函數 要建立一個小型的自動成績查詢, 我們會用到 VLOOKUP 這個函數來進行查詢, 以下就先說明這個函數的用法。
假設有一清單如右：

74 認識 VLOOKUP函數 VLOOKUP 函數的功用,就是在清單中的第一欄尋找特定值, 找到時,就會傳回所找到的那一列中某個欄位的值。例如：

75 認識 VLOOKUP函數

76 建立查詢系統 了解 VLOOKUP 函數的用法後, 我們就可以開始來建立查詢系統了。
請開啟範例檔案 Ch02-07 的成績查詢工作表, 我們已經建立好如下的查詢表格：

77 建立查詢系統

78 建立查詢系統 首先, 請選定 B4 儲存格, 然後按下資料編輯列上的插入函數鈕 ,我們要利用 VLOOKUP 函數來查詢准考證號：

79 建立查詢系統

80 建立查詢系統 Lookup_value：請輸入成績查詢工作表中的 "C1" 儲存格, 表示要尋找我們所輸入的准考證號。
Table_array：請按照下列步驟輸入：

81 建立查詢系統

82 建立查詢系統

83 建立查詢系統 Col_index_num：由於准考證號是在清單的第1欄, 因此, 請輸入 "1"。
Range_lookup：請輸入 "0", 表示要尋找完全符合的資料。

84 建立查詢系統

85 建立查詢系統

86 『錯誤檢查選項』鈕 當儲存格發生 " #N/A " 、 " #NAME " 這類錯誤時, 只要選取錯誤的儲存格, 就可以在儲存格旁邊發現錯誤檢查選項鈕的蹤影, 當您將滑鼠指標移至按鈕上時, 便可拉出一下拉選單, 裡頭提供了幾種不同的除錯方法供您選擇：

87 『錯誤檢查選項』鈕

88 『錯誤檢查選項』鈕 如果您想要關掉錯誤檢查功能, 以避免錯誤檢查選項鈕來打擾我們進行工作表的編輯工作, 可以選擇下拉選單中的『錯誤檢查選項』命令, 然後取消勾選選項/ 錯誤檢查交談窗中的啟用背景錯誤檢查項目即可。

89 建立查詢系統 由於 C1 儲存格尚未輸入任何資料, 因此 B4 儲存格會出現錯誤值,我們只要在 C1 儲存格中輸入某位考生的准考證號就可以了。

90 建立查詢系統 接下來的各個欄位, 我們只要依照同樣的方法輸入公式即可；不同的是要改變 Col_index_num 欄的值 (例如：姓名為第 2 欄、國文為第 3 欄、…)。

91 建立查詢系統 請依此類推, 分別在成績計算工作表中查出各科成績、筆試成績及是否合格。

92 建立查詢系統

93 建立查詢系統 由於此次的招考測驗, 得先通過筆試成績才能進行口試, 所以我們要查詢口試的成績時, 必須判斷筆試是否合格欄中的值, 才能分別查出口試成績、總成績及名次欄的值。 請選定 B10 儲存格, 然後輸入如下公式：

94 建立查詢系統

95 建立查詢系統 接著, 請分別在 B12及 B13 儲存格中, 輸入以下公式,查詢出總成績及名次：

96 建立查詢系統

97 建立查詢系統 進行到此, 成績查詢系統已經完成了, 不過, 為了在查詢時能夠方便得知考生是否錄取, 我們可以在備註欄中加以說明。
例如：名次在前 8 名者, 會顯示 "恭禧您錄取了!!!"；名次為第 9 名及第 10 名, 則會顯示 "後補人選!!!"；第 11 名以後, 則顯示 "未錄取!!!"。

98 建立查詢系統 請選定儲存格 B15, 然後輸入以下的公式：

99 建立查詢系統

100 建立查詢系統