贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

國中教育會考早已結束,會考成績也已成為進入高中的重要評比項目。現在,高中已經放榜,各個學校(國中和高中)也已陸續拿到學生的會考成績。如何藉由 Excel 來處理這些成績呢?

(以下圖為範例的相關操作說明,重點是在練習 Excel 的各種功能,而不強調會考成績要如何運用。)

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

 

【範例一:計算總點數】

會考成績是以「A++、A+、A、B++、B+、B、C」來標示,若要換算成點數,可參考下圖左。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

根據儲存格J1:K8的表格中,有各個等級標示對應的點數,要將五個科目所對應的點數予以加總。以下提供有三種做法可以求得總點數:

1. 使用VLOOKUP函數

儲存格H2:
=VLOOKUP(C2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(D2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(E2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(F2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)
+VLOOKUP(G2,{"A++",7;"A+",6;"A",5;"B++",4;"B+",3;"B",2;"C",1},2,FALSE)

2. 使用VLOOKUP函數+資料表

儲存格H2:
=VLOOKUP(C2,$J$2:$K$8,2,FALSE)+VLOOKUP(D2,$J$2:$K$8,2,FALSE)
+VLOOKUP(E2,$J$2:$K$8,2,FALSE)+VLOOKUP(F2,$J$2:$K$8,2,FALSE)
+VLOOKUP(G2,$J$2:$K$8,2,FALSE)

3. 使用陣列公式

選取儲存格J1:K8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:等級標示、點數。

儲存格H2:{=SUM((等級標示=C2:G2)*點數)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格H2,往下各列貼上,即可求得每位學生換算得的總點數。

若以 SUMPRODUCT 函數取代陣列公式,則公式:

儲存格H2:=SUMPRODUCT((等級標示=C2:G2)*點數)

結語:

第 1 種方式直接用定數的觀念,在 VLOOKUP 函數中求得等級標示所對應的點數,所以公式較為冗長。而第 2 種方式將定數改為儲存格中的變數,所以縮短了公式長度。第 3 種方式透過定義名稱和陣列觀念,公式顯得較為簡短,但是思考上較有難度。

 

【範例二:依成績排序位】

假設:要將全體學生依「總點數→國文→數學→英文→社會→自然」的成績高低來排序。

首先,必須自訂排序的順序:A++>A+>A>B++>B+>B>C。參考以下步驟:

1. 選取[檔案/選項]選項,並點選[進階]標籤,按下[編輯自訂清單]按鈕。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

2. 在[選項]對話框的「自訂清單」標籤下:

(1) 在[匯入清單來源]中選取儲存格J2:J8

(2) 按一下[匯入]按鈕,在[清單項目]方塊中會列出A++、A+、...、C。

(3) 按一下[新增]按鈕,在[自訂清單]方塊中會列出A++、A+、...、C。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

3. 進行排序。

1. 選取[常用/編輯]功能表中的「自訂排序」,開啟[排序]對話框,勾選「我的資料有標題」項目。

2. 設定第1個排序層級:欄→總點數/排序對象→值/順序→最大到最小。

3. 按一下[新增層級]按鈕,設定:欄→總點數/排序對象→值/順序→自訂清單。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

4. 在[自訂清單]對話框中選取已定義好的「A++、A+、...、C」清單。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

結果如下:

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

5.仿步驟3和4,依序完成 「數學→英文→社會→自然」等排序設定。設定結果如下:

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

完成的排序結果:

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

 

【範例三:成績分佈】

以國文科為例,想要知道A++、A+、A、B++、B+、B、C的人數分佈。(如下圖)

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

本例要使用樞紐分析表,並且資料中需要一個不會重覆的值(例如:准考證號碼)

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

1. 執行插入這個資料表的樞紐分析表。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

2. 在樞紐分析表欄位設定中,設定:列→班級、性別;欄→國文;值:計數-准考證。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

初步的結果如下:

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

3. 點選一個班級的儲存格,再選取「作用中欄位/欄位設定」。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

4. 在[欄位設定]對話框中的「小計與篩選」標籤下,將小計設定為:無。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

5. 在[版面配置與列印]標籤下,選取「以列表方式顯示項目標籤」選項,再勾選「重複項目標籤」項目。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

結果如下:

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

例如,你想知道305班A++的二個男生是誰,只要點選二下數字2即可。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

如果你想列出所有國文A++者,只要在數字16上點選二下即可。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

結果如下:

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

 

【範例四:計算各班成績A的個數】

以下來練計算各班各科成績A的個數和5A的個數,其中欄I是輔助欄位。

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

本例建立一個輔助欄位:I欄,用以計算每個學生成績有幾個A。

儲存格I2:=SUMPRODUCT(1*(LEFT(C2:G2,1)="A"))

複製儲存格I2,往下各列貼上。

接著,定義「班級、性別、A個數」三個名稱。

1. 計算A的個數

儲存格M2:=SUMPRODUCT((班級=$K2)*(性別=$L2)*A個數)

如果你將所有學生成績的儲存格範圍定義為:成績,也可以使用以下公式,不需藉用輔助欄位即可計算出結果。

儲存格M2:=SUMPRODUCT((班級=$K2)*(性別=$L2)*(LEFT(成績,1)="A"))

 

2. 計算5A的個數

儲存格N2:=SUMPRODUCT((班級=$K2)*(性別=$L2)*(A個數=5))

文章標籤
創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()


留言列表 (2)

發表留言
  • 悄悄話
  • junxi
  • 比序:會考總積分→會考標示數量 先比ΣA的加號數量->ΣB的加號數量→會考單科積分(國→數→英→社→自)→寫作級分→會考單科成績標示(國→數→英→社→自)
    本區的比序如上,不曉得第二個及第三序位要如何計算呢??謝謝您
  • 請參考:http://isvincent.pixnet.net/blog/post/47526510

    vincent 於 2017/09/15 13:17 回覆

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼