十年磨一劍

又到了要執行繁星計畫的階段，繁星計畫採計學生高一、高二的在校學業成績百分比，如何推估學生的在校成績百分比？一般都是將學生成績匯入繁星甄選委員會提供之成績 上傳作業 軟體去計算，我們也可以利用Excel 與『資料處理過濾增益集』來計算，以下示範如何利用它們來計算校排百分比，示範的版本為Excel 2013 與 『資料處理過濾增益集3.3』。

 

步驟一：首先說明大學入學繁星甄選高中校排百分比的計算方式:

 

舉例某校高三人數335人，校排百分之1的人數應該有4人(320*1%=3.35 無條件進位)，(校排百分之1  + 校排百分之2)的人數應該有7人 (320*2%=6.7 無條件進位) , 所以校排百分之2的人數為3人(7-4=3)，知道此規則我們可以建立Excel的計算公式。

 

步驟二：建立百分比人數計算公式

 

1.     開啟Excel 2013 新增一個空白的工作簿，在工作表1建立4個欄位標題分別為 高三總人數       百分比  人數百分比率    百分比人數

 

 

2.     在工作表A2資料格輸入高三總人數，在B2~B101輸入1~100(可以輸入前2格再利用自動填滿)，在C2輸入公式 =$A$2*B2/100 ，在利用自動填滿至C101，在D2輸入公式 =ROUNDUP(C2,0)，在D3輸入公式 = =ROUNDUP(C3,0)-ROUNDUP(C2,0) ，再利用自動填滿D3~D101，在C102輸入總人數，在D102輸入公式 =SUM(D2:D101) 。

範例檔案下載:人數百分比計算公式.zip

 

 

3.     將計算出來的『百分比』與『百分比人數』兩欄資料複製，以『選擇性貼上』(只貼值)在工作表2。

 

4.     接下來要使用『資料處理過濾增益集3.3』，先將『百分比人數』此欄位資料選起來，再執行Excel功能區[增益集]\[資料輸入統計]\[資料計數還原]。

 

5.     [資料計數還原]這個功能可以看成COUNT這個函數的反函數，執行此功能可以將資料的計數結果反向複製還原成資料，上述資料執行的結果如下，A欄是百分比，B欄是百分比人數，舉例原資料百分之1的有4人，所以百分之1的數值產生4個資料，原資料百分之2的有3人，所以百分之2的數值產生3個資料，所以我們只要把A欄的資料複製，貼至已排序學生成績的對應欄位即可。

 

步驟三：將學生依四學期的平均成績進行排序。

 

1.     此需求利用Excel的排序功能即可完成，若有平均同分再依照高二下、高二上、高一下、高一上新增排序層級依序排序。(如下圖)

 

2.      再新增一個『百分比』空白欄位，將步驟二產生的A欄的數值貼至此欄，即可完成學生在校成績百分比資料。如下圖

 

 

 

      歡迎引用，請注明來源出處!

作者:楊煥謀