在 Excel 中取得一個學生考試成績表(如下圖),其中每個人的平均分數是一個加權平均。

試著建立一個可以藉由改變微調按鈕,可以顯示每個學生的成績和平均成績的對照表,和動態的圖表。

【處理成績表】

1. 計算加權平均

儲存格H3:=SUMPRODUCT($C$1:$G$1,C3:G3)/SUM($C$1:$G$1)

2. 計算名次

儲存格I3:=RANK(H3,$H$3:$H$22)

複製儲存格H3:I3,往下各列貼上。

 

【處理個人成績】

1. 在[開發人員/控制項]下,選取[表單控制項]的「微調按鈕」。

2. 在微調按鈕上按一下右鍵,選取「控制項格式」。

3. 設定最小值:1、最大值:20、遞增值1,儲存格連結:$A$26。

4. 由成績表查詢得到個人成績。

儲存格B26:=INDEX($A$3:$I$22,MATCH($A$26,$A$3:$A$22,0),COLUMN(B:B))

複製儲存格B26,貼至儲存格B26:I26。

MATCH($A$26,$A$3:$A$22,0)

以儲存格A26的內容找到資料在資料表中的第幾列。

INDEX($A$3:$I$22,MATCH($A$26,$A$3:$A$22,0),COLUMN(B:B))

利用查表方式求得指定列的每一欄資料,其中COLUMN(B:B)在往右複製時會變成COLUMN(C:C)、COLUMN(D:D)、…,可以求得第2欄、第3欄、第4欄、…的資料。

 

【處理個人圖表】

1. 選取儲存格C25:G27。

2. 選取[插入/圖表]區中的[直條圖/群組直條圖]。

3. 在[平均]數列上按一下右鍵,選取「變更數列圖表類型」選項。

4. 選取「折線圖」。

 

【進階處理個人圖表】

如果你不想透過個人成績表來動態查詢成績再製作圖表,則可以使用以下的方法:

1. 先定義一個名稱:DATA

參照到:=OFFSET(工作表1!$C$3,MATCH(工作表1!$A$26,工作表1!$A$3:$A$22,0)-1,,,5)

注意到範圍要選「工作表1」(指定給參照範圍在工作表1)

相關位址請使用絶對參照,例如:工作表1!$A$26、工作表1!$A$3:$A$22等。OFFSET函數會產生一個動態的位址,例如儲存格A26為1時,位址為C3:G3,而儲存格A26為2時,位址為C4:G4,…。

2. 選取儲存格C2:G3,按照 Ctrl 鍵,再選取儲存格C23:G23。

(為了便於說明,下圖中將部分列予以隱藏。)

3. 仿【進階處理個人圖表】說明之方式建立圖表。

4. 在圖表中點選個人成績數列(藍色)。

資料編輯列顯示:=SERIES(,工作表1!$C$2:$G$2,工作表1!$C$3:$G$3,1)

5. 在資料編輯列中,將「工作表1!$C$3:$G$3」改成「DATA」。

透過微調按鈕的變動,也可以在一個圖表中,顯示每一個人的成績圖表。

arrow
arrow
    全站熱搜

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