在 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」。
透過微調按鈕的變動，也可以在一個圖表中，顯示每一個人的成績圖表。
留言列表