2012年11月19日 星期一

Excel-成績進步統計表製作

每個學校常需要對二次的成績做進步狀況的統計與分析,這次來練習這樣的報表。

例如:取得以下的二次成績表(參考下圖):

要製作成以下的統計分析表(參考下圖),功能要求:

一、進步分數大於等於 0 者以綠色顯示,小於 0 者以紅色顯示。(進步分數=第2次總分-第1 次總分。)

二、計算每個學生的各班排名。

三、將各班前 3 名的記錄,整列以淺色網底標示。

四、各班之間以黑色分隔線標示。

【實作練習】

一、 進步分數大於等於 0 者以綠色顯示,小於 0 者以紅色顯示。

1. 選取「進步分數」欄位的所有資料。

2. 設定格式化的條件為:

(1) 使用公式來決定要格式化哪些儲存格,公式:「=F2>=0」,格式:字型色彩為「綠色」。

執行進步分數大於等於 0 者以綠色顯示。

image

(2) 使用公式來決定要格式化哪些儲存格,公式:「=F2<0」,格式:字型色彩為「紅色」。

執行進步分數小於 0 者以紅色顯示。

image

 

二、計算每個學生的各班排名。

1. 選取「班級」欄位的所有資料,按一下 Ctrl+Shift+F3 鍵,勾選頂端列,定義名稱:班級。

2. 選取「進步分數」欄位的所有資料,按一下 Ctrl+Shift+F3 鍵,勾選頂端列,定義名稱:進步分數。

3. 在儲存格G2輸入公式:

{=RANK(F2,OFFSET($F$2,COUNT(IF(班級<A2,進步分數)),,COUNT(IF(班級=A2,進步分數)),))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

因為資料已按班級順序排列完成,所以要試著找出每一個班級的資料範圍,才能自動計算學生在各班的排名。

COUNT(IF(班級<A2,進步分數)):找出某班資料的前一列的列數。

COUNT(IF(班級=A2,進步分數)):找出某班資料的最後一列的列數。

OFFSET($F$2,COUNT(IF(班級<A2,進步分數)),,COUNT(IF(班級=A2,進步分數)),):找出每一個班級的資料範圍。

使用 RANK 函數來找出每位同學在各班的排名。

4. 複製儲存格G2,往下各列貼上。

 

三、將各班前 3 名的記錄,整列以淺色網底標示。

1. 選取所有學生成績的資料範圍。

2. 設定格式化的條件為:

使用公式來決定要格式化哪些儲存格,公式:「=$G2<=3」,格式:儲存格網底為「淺綠色」。

意義:找出排名儲存格內容小於等於3者,設定網底為「淺綠色」。

 

四、各班之間以黑色分隔線標示。

1. 選取所有學生成績的資料範圍。

2. 設定格式化的條件為:

使用公式來決定要格式化哪些儲存格,公式:「=$A3>$A2」,格式:儲存格框線為「底線為黑線」。

意義:找出後一個班級儲存格內容大於前一個班級儲存格內容者,表示換到下一個班級了,設定儲存格框線為「底線為黑線」。

數個格式化條件的設定結果如下:

沒有留言:

張貼留言

好康東東