Download
1 / 16
170 likes | 316 Views
班級成績單. Excel 公式與函數的應用. 07. 「班級成績單」具有計算與統計的動作，而為了加速工作的流程並提高正確率，此時會應用到 Excel 裡的公式與函數。這不僅可以在成績輸入完畢後即時產生結果，還可以利用其他的功能顯示出相關的資訊，即使有修改或是更動，都能馬上重新產生應有的數據。. 關於「班級成績單」. 7.1.
E N D
班級成績單 Excel 公式與函數的應用 • 07 • 「班級成績單」具有計算與統計的動作，而為了加速工作的流程並提高正確率，此時會應用到 Excel 裡的公式與函數。這不僅可以在成績輸入完畢後即時產生結果，還可以利用其他的功能顯示出相關的資訊，即使有修改或是更動，都能馬上重新產生應有的數據。
關於「班級成績單」 • 7.1 • 公式與函數是 Excel 中十分重要功能，它能在儲存格中顯示儲存格之間或是指定範圍、區域中即時運算的結果。在成績單裡常要計算的內容有各科的總分、平均分數、分數排名，並依所呈現的分數來顯示等級，在本章範例中將應用不同的公式或是函數進行計算。
函數為 Excel 中十分重要的功能，它能在最短時間內將報表中的數據轉化成有用的統計表，使工作更有效率。
加入SUM函數計算加總 • 7.2 • 請開啟範例原始檔 <班級成績單.xlsx>，目前工作表中學生的基本資料與成績已經輸入完畢，接下來就是要利用公式與函數來完成整個工作表內容。 • 加總：SUM是 Excel 工作表中最常使用的函數，以下將利用 插入函數 鈕來進行函數的輸入。
請將作用儲存格移動到「F3」，接著按資料編輯列的 插入函數 鈕進入對話方塊。 • 選取 SUM函數並設定使用的引數，最後按 確定 鈕。 • 回到工作表後，請先檢視 SUM 函數設定後的結果。 • 利用填滿控點，將「F3」儲存格中的函數複製到其他同學的總分儲存格中，即可完成總分的計算。
加入AVERAGE函數計算平均 • 7.3 • 選取AVERAGE 函數後利用選取儲存格範圍鈕來設定引數。 • 設定完AVERAGE 函數的引數後，請按 確定 鈕完成設定。 • 利用填滿控點，將「G3」儲存格中的函數複製到其他同學的平均分數的儲存格中，即可完成平均分數的計算。 • 計算平均值：AVERAGE 也是 Excel 工作表常使用的函數，以下將利用 插入函數 鈕來進行函數的輸入。 • 請將作用儲存格移動到「G3」，接著按資料編輯列的 插入函數 鈕進入對話方塊。
加入ROUND函數設定四捨五入 • 7.4 • 利用填滿控點，將「G3」儲存格中的函數複製到其他同學的平均分數的儲存格中，即可完成平均分數的計算。 • 以四捨五入的方式來顯數值，是工作表中經常要使用的功能。此時 ROUND函數就十分重要，它不僅能自動四捨五入，還能指定顯示到小數第幾位。 • 請選取「G3」儲存格，請在原來的 AVERAGE函數外，再加入 ROUND函數，並指定顯示在小數第 2 位。
加入RANK函數設定名次 • 7.5 • 要如何知道每個學生的總分在班上是排第幾名呢？這時候可以利用RANK 函數。 • 以目前的範例來說，學生的總分在「F」欄中，名次是以每個學生「F」欄中分數與所有總分儲存格範圍「F3：F12」進行比較。這個範圍必須設定為絕對位址，因為它不能因儲存格複製函數而相對改變。 • 在 Excel 絕對位址的表示方式必須在欄名或列號前加上「$」符號，例如位址「F3：F12」要設定為絕對位址就必須寫為「$F$3：$F$12」。
請將作用儲存格移動到「H3」，接著按資料編輯列的 插入函數 鈕進入對話方塊。 • 選取 RANK函數後利用選取儲存格範圍鈕來設定引數。 • 回到工作表後，請先檢視RANK 函數設定後的結果。
加入VLOOKUP函數設定等級 • 7.6 • 一般學生能由他的平均分數得到成績的等級，例如在本範例設定 90 分以上為 A，75 分以上到 89 分為 B，60 分以上到 74 分為 C，45 分以上到 59 分為 D，不到 45 分的為 E。使用 IF 判斷式可以達到這樣的需求，不過撰寫起來相當複雜。這裡將使用 VLOOKUP函數，不僅設定方式彈性，設定過程也很簡單。
請將作用儲存格移動到「I3」，接著按資料編輯列的 插入函數 鈕進入對話方塊。 • 選取 VLOOKUP函數後利用選取儲存格範圍鈕來設定引數。 • 回到工作表後，請先檢視 VLOOKUP函數設定後的結果。 • 利用填滿控點，將「I3」儲存格中的函數複製到其他同學的等級儲存格中，即可完成等級的顯示。
設定格式化條件 • 7.7 • 若希望在成績單上用紅字標示出不及格的成績，可以使用格式化條件的功能。所謂格式化條件，就是為選取的範圍加上條件，符合的以設定的格式來顯示，如此即能將有差異性的資料標示出來。 • 選取填寫成績的儲存格範圍後開始設定格式化的條件。 • 回到工作表後，果然不及格的儲存格都以設定的格式顯示。
資料排序與篩選 • 7.8 • 資料排序 • 請選取表格範圍，於 資料 索引標籤選按 篩選 表頭欄位會顯示自動篩選鈕。 • 如果想以國文成績由最低分排到最高分，或篩選出前 3 名的學生名單，可以利用資料排序與篩選的功能。
這裡要以國文成績由小排到大進行排序，請選按國文欄的自動篩選鈕後進行設定這裡要以國文成績由小排到大進行排序，請選按國文欄的自動篩選鈕後進行設定 • 選取第一個同學的學號，於 資料 索引標籤選按 由小排到大進行排序 鈕，所有的資料即會恢復原來的排序狀態。
資料篩選 • 接著要篩選出前 3 名的同學名單，請選按名次欄位的自動篩選鈕後選取 數字篩選 \ 小於或等於，設定名次小於或等於 3 的條件後按 確定 鈕。
接著選取名次儲存格，於 資料 索引標籤選按 由小排到大進行排序 鈕，除了成功篩選出前 3 名的同學名單，也按照 1、2、3 的順序來排列。
