2016年11月2日 星期三

Excel-表單控制項在學生成績處理的練習

本篇是針對校內教師 Excel 研習課程使用的一個範例做說明。
在下圖中,有個學生多次小考的成績表,每次小考設有「加權」,本次要練習表單控制項在學生成績方面的處理,並且練習計算學生的「加權平均」成績。

1. 使用微調按鈕選取不同次別所有學生考試成績和統計圖
如下圖,你可使用微調按鈕來選取不同次別的考試成績,並且做成統計圖表。
Excel-表單控制項在學生成績處理的練習
參考以下的做法:
要使用「微調按鈕」的表單控制項,你必須先開啟「開發人員」功能表。在工作表上先新增一個微調按鈕。
Excel-表單控制項在學生成績處理的練習
在控制項上按右鍵,選取:控制項格式。
Excel-表單控制項在學生成績處理的練習
設定如下:(本例共有10次成績)
目前值:5;最小值:1;最大值:10;遞增值:1;儲存格連結:L3。
Excel-表單控制項在學生成績處理的練習
輸入公式:
儲存格N2:=OFFSET($A$2,,$L$3)
儲存格N3:=OFFSET($A$3,0,L3)
儲存格N4:=OFFSET($A$3,ROW(1:1),$L$3)
複製儲存格N4,貼至儲存格N4:N19。
接著:選取圖表中的標題文字方塊,然後在公式編輯列中,輸入「=」,然後再點選儲存格N3。(結果如下)
如此,便可以使用微調按鈕來顯示單次考試和成績統計圖了。
Excel-表單控制項在學生成績處理的練習

2. 使用微調按鈕選取不同學生每次考試成績和統計圖
如果使用微調按鈕選取不同學生,則改成如下的格式。(微調按鈕直接連結到儲存格M4)
Excel-表單控制項在學生成績處理的練習
輸入公式:
儲存格N4:=OFFSET($A$3,$M$4,COLUMN(A:A))
複製儲存格N4,貼至儲存格N4:W4。
計算平均成績,輸入公式:
儲存格X4:=SUMPRODUCT(N4:W4*N2:W2)/SUMPRODUCT(N2:W2)

3. 根據加權值計算「加權平均」
image
輸入公式:
儲存格L4:=SUMPRODUCT(B4:K4*$B$2:$K$2)/SUMPRODUCT($B$2:$K$2)
複製儲存格L4,貼至儲存格L4:L19。

4. 根據各次「V」記號計算要納入採記的加權平均
image
輸入公式:
儲存格M4:=SUMPRODUCT(B4:K4*$B$2:$K$2*($B$1:$K$1="V"))/
SUMPRODUCT($B$2:$K$2*($B$1:$K$1="V"))
複製儲存格M4,貼至儲存格M4:M19。
5. 利用「核取方塊」表單控制項決定是否採計加權
Excel-表單控制項在學生成績處理的練習
新增一個「核取方塊」表單控制項後,「核取方塊」的輸出結果連結至儲存格B1,當勾選核取方塊時,會在儲存格B1顯示『TRUE』,若是不勾選核取方塊時,會在儲存格B1顯示『FALSE』。
然後,將儲存格B1的文字前景色彩設定成和儲存格B1的背景色彩相同,即可隱藏文字。
image
你也可以設定儲存格格式為『;;;』,同樣可以隱藏儲存格內容。
image
輸入公式:
對照4.的公式,將『V』置換成『TRUE』
儲存格M4:=SUMPRODUCT(B4:K4*$B$2:$K$2*($B$1:$K$1=TRUE))/
SUMPRODUCT($B$2:$K$2*($B$1:$K$1=TRUE))
因為儲存格B1:K1的內容不是 TRUE 就是 FALSE,所以可以修改公式如下:
儲存格M4:=SUMPRODUCT(B4:K4*$B$2:$K$2*($B$1:$K$1))/
SUMPRODUCT($B$2:$K$2*($B$1:$K$1))
複製儲存格M4,貼至儲存格M4:M19。

沒有留言:

張貼留言

檢視其他文章

好康東東