如何利用Excel2010進行學生擇優多次小考成績的統計處理？(LARGE函數應用)

如何擇優處理學生的成績？這個問題是很久之前同事提出的，一般老師處理學生的平時成績會採取擇優幾次平均的方式，比較公平的方式應該是分別選取每位學生最佳的幾次成績去平均，而不是統一指定那幾次成績，這個在我們用尺一排一排計算的年代是很麻煩，老師大概也不會自找麻煩這樣處理，尤其當評量次數很多的時候。

現在有了Excel2010方便多了，舉例下表是一個虛擬的小考成績表，總共有8次小考成績，我們要幫每位學生挑出最佳的5次成績去平均，作為其平時成績。

如果單純只是把學生的最佳幾次小考成績挑出來平均計算，這個問題在如何快速計算學生擇優多次小考成績的平均值？(利用Excel與『資料處理過濾增益集』)？這篇文章已經介紹過，利用[資料處理過濾增益集]\[輸入統計]\[條件處理] 很快可以完成。

 

如果使用者沒有安裝[資料處理過濾增益集]則無法使用這個方法，現在我要介紹的另一個方式是純粹使用Excel2010的內訂函式進行同樣的處理，在Excel2010 新增一個 LARGE 的函數，這個函數可以幫助我們完成這個工作。

LARGE函數在Excel2010的說明如下:

傳回資料組中第 k 個最大的數值。您可以用這個函數來指定選取排在第幾位的值。例如，您可以使用 LARGE 傳回最高、第二高或第三高的分數。

語法

LARGE(array, k)

LARGE 函數語法具有下列引數(引數:將資訊提供給動作、事件、方法、屬性、函數或程序的值。)：

• Array    必要。這是您要找出第 k 個最大值之數值資料的陣列或範圍。
• K    必要。這是要傳回之資料陣列或儲存格範圍中的位置 (由最大值算起)。

註解

• 如果 array 是空值，則 LARGE 傳回錯誤值 #NUM!。
• 如果 k ≦ 0 或 k 大於資料點的個數，則 LARGE 傳回錯誤值 #NUM!。

如果 n 是範圍中資料點的個數，則 LARGE(array,1) 傳回最大值，而 LARGE(array,n) 傳回最小值。

依照函數的說明，舉例要在工作表K2資料格，呈現1號同學8次小考中第一高的成績，我們可以選取K2資料格，然後點選[公式]\[其他函數]\[統計(S)]\[LARGE]

在函數引數表單依序選取參數

點選 Array 參數輸入右邊鈕，選取1號同學的8次小考成績的資料 C2:J2

在參數K輸入1後，按下確定鈕

則在K2插入的函數為 =LARGE(C2:J2,1)，計算的結果應該為100

依樣畫葫蘆在L2、M2、N2、O2重複上述輸入函數的動作，依序把參數K改為2、3、4、5就可以分別得到前五個最佳的成績。

如果覺得要重複這樣多次覺得麻煩，可以使用這動填滿的技巧，不過要將公式改為=LARGE($C2:$J2,1)，然後使用自動填滿，但是注意因為參數K不會自動累加，所以出來都是最高的分數，會出現下面的結果

所以還是要手動改參數K的值，如果不改公式的欄位參照為絕對參照，直接用複製貼上或自動填滿可能會產生Array參數範圍會改變，這樣的結果會產生錯誤。

再來只要再將K2：O2 平均即可達到我們的目的，我們可以在P2資料格輸入平均值函數 =AVERAGE(K2:O2)

計算出來的結果如下

當然再來我們可利用自動填滿的方式完成全班的計算，只要選取K2：P2範圍讓滑鼠游標移至P2資料格右下角，游標變成黑色十字，按住滑鼠左鍵不放往下拉至最後一列即可完成。

計算出來結果如下，我們可以點選O30這個資料格看公式是否正確，從資料編輯列可以看到公式是正確。

反之如果我們要計算最差的幾次我們可以改用另一個函數SMALL，用法雷同只是一個從高至低，一個從低到高而已。

有了這樣的數據我們可以再進行不同的統計分析，例如我們可以進行標準差計算，看看每位學生的成績穩定度，重複上述輸入平均值函數的步驟，改利用STDEV.S 函數，我們在Q2輸入=STDEV.S(K2:O2) 計算的結果如下，一樣我們只要再利用自動填滿的功能，即可完成全班的標準差計算。

計算結果如下，從標準差與平均值之間的關係，可以進行學生評量表現不同的解讀，有些學生平均值不差，可是標準差很大，表示學生是屬於表現較不穩定的狀態，也許會是黑馬，也可能馬失前蹄。

 

範例檔:擇優範例.XLSX

座位表製作增益集2.0.2發佈與示範

好久沒有更新這個增益集，前一陣子有網友來信提到其中有些Bug，並且希望能提供2.0.1的32位元的版本，一直沒有空可以去處理，直到前幾天利用假期把bug修正，順便增加32bit的版本，程式下載的網址在增益集工坊，另外也錄了一個教學範例影片放在youtube。

64位元版『座位表製作增益集2.0.1發佈』

64位元版『座位表製作增益集2.0.1發佈』，有需要的網友可以至http://www.addinfactory.com註冊下載，開學了每位導師必做的工作就是幫學生安排座位表，座位表這個東西看是小事，但是在班級管理來說，其實蠻重要的。每次到不同班級上課，如果老師仔細安排班級座位，大概可以了解那些學生是必須多關心的。

64位元版原本沒有打算加入篩選的功能，所以2.0版中此功能只是一個空殼子，這個增益集主要是座位表的製作，其實這功能並沒有很多人用，當時加入篩選的功能只是因為剛好在寫另一個增益集(資料處理過濾增益集)的篩選比對功能，順便一魚多吃，原本想趁著升級至64位元，把這個功能拿掉讓程式更精簡，後來有網友反應此功能無法使用，來信希望能夠像32位元版本可以使用篩選功能，只好再花幾天的時間把程式修改，把功能完整加入。

說實在的64位元的環境跑64位元的程式，明顯效能提升，什麼時候VB6可以在64位元視窗下跑，一些舊的作品就不用再改了!

座位表製作增益集2.0正式發佈(我的第一個純64位元版本的增益集)

把8年前的主機升級至64位元的系統環境，一堆從前VB的作品通通不能用，所以最近沒什麼時間在部落格上寫文章，經過一個月的奮戰，終於產出第一個在64位元環境(WIN7 + OFFICE2010)可以使用的增益集，這個版本只能在64位元的OFFICE使用!有興趣可以至增益集工坊註冊下載，免費的。另外歡迎各地(包含大陸)網友引用，但是不要忘記標示作者是誰!

『座位表製作增益集1.6』正式發佈

『座位表製作增益集1.6』正式發佈，本版本除了修正部分bug，我把主要的程式表單進行一個調整，我把與座位表相關的設定集中在一個下拉選單，另外新增『插入相片』、『座位表加上超連結』及『刪除座位表區域超連結』等功能，主程式畫面如下：

 

有興趣的讀者可以至增益集工坊免費註冊下載。

ps:部分網友反應做好的快取圖案座位表的Excel檔案，在不同的版本的Excel無法正常讀取快取圖案組成的座位表選取區，經過測試的確會發生此種現象，這個問題尚未找到確切原因，我的猜測也許是檔案格式的問題，因為我放的範例為了相容的緣故，我存成Excel97-2003的.xls檔案，在Excel2007與Excel2010讀取都正常，並無網友反應之問題，但是如果在Excel2010的以預設存檔格式存檔為.xlsx，在Excel2007讀取的確會發生這種問題，也許這兩個版本在讀取寫入.xlsx時有所差異，所以為避免產生相容的問題，建議存為Excel97-2003的.xls檔案格式。

利用『座位表製作增益集1.6』製作相片座位表

這學期教的班級數比上學期又多了一班(八個班)，學生人數多到看到知道是自己教的學生，但是名字跟人對不起來，突然想到座位表應該上面放張學生的個人照，否則像電腦老師這種要教近二十個班級的科目，如何記得住學生？

剛好很久沒有更新『座位表製作增益集』這一支程式，就把加入相片的功能思索了一下，原有的『座位表製作增益集』除了可以利用Excel資料格繪製座位表，還可利用Excel的快取圖案功能製做座位表，而快取圖案本身可以加入圖片當背景，所以我的思考方向就是利用快取圖案來製作個人相片的座位表。

我把程式的主表單做了修正，版本改為『座位表製作增益集1.6』，原本的『範例』按鈕我放到功能表上的『關於』之下，把『建立座位表』按鈕移到最底部，以符合使用者的習慣，另外新增一個『插入相片』的選擇項目，主程式的擷取圖如下(圖一)

 

(圖一)

如何利用『座位表製作增益集1.6』製作相片座位表？

讀者如果對『座位表製作增益集』基本操作不熟悉，可以參考以下文章利用『座位表製作增益集』製作特別座位表，先熟悉其基本的操作原理。

1.首先一樣要先建立學生的『座號資料』的選取區(請參考利用『座位表製作增益集』製作特別座位表)，在有學生座號、姓名資料的工作表上選取，我們以程式附的範例資料來說明，資料欄位如下表格，另外因為我們要加入相片資料，所以姓名旁邊要增加一欄放置相片目錄資料，我們暫時保留空白，先進行其他步驟。

2.要製作具有相片功能的座位表，不能直接用Excel工作表的資料格來做，因為Excel工作表的資料格不支援圖像資料，所以我們必須使用快取圖案來取代工作表資料格，插入快取圖案功能不只在Excel中有，在Word與Powerpoint都有，所以在Word或Powerpoint所畫的快取圖案，也可以用複製、貼上的方式在Excel中使用，反之在Excel所完成的快取圖案也可貼到Word與Powerpoint。

只要點選功能區的『插入』/『圖案』就可以在工作表上加入圖案，而快取圖案可以加入文字，所以可以作為座位表的呈現圖案。而建立座位表選區域的方法，和選取資料格的方式一樣，把快取圖案當成是工作表資料格來選取(可參考利用『座位表製作增益集』製作特別座位表)。

我在程式所附的範例檔(seat.xls)中已經做了兩個利用快取圖案座位表的範例，要參考範例請點選功能表『關於』/『範例』，即可打開範例，我們就以範例來說明。

下圖二為範例中實驗分組座位表，其中代表學生座位的圓形與代表實驗桌的四方圓角形，這都是利用Excel提供的快取圖案功能所製作出來，只要畫一組，其他用複製即可，我們要在圓形的快取圖案中加入座號與姓名資料，表示學生的座位。

(圖二)

3.如何建立『座位表區域』只要把圓形快取圖案當成資料格來處理即可，要連續選取圓形記得：請按住Crtl鍵不放，然後再點選『座位表排列工具』主表單中『座位表的區域』的『選取』鈕，然後在『輸入座位表區域的名稱』對話框輸入區域名稱，差別在『座位表的區域』的文字方塊不會出現類似工作表資料格的參考位置值，在座位表區域的下拉選單可以看到新增的選取區名稱。

如果只是要建立一般座位表，只要先選『座號姓名範圍』，再選取『座位表區域』，然後指定座號姓名資料填入座位表的方式，按下『建立座位表』即可將座號與姓名資料填入座位表區域，完成一份座位表。

 

但是我們希望建立一份帶有學生照片的座位表，所以我們還要進行下一個步驟。

5.建立學生相片資料，舉例我們已經把學生的照片蒐集在D磁碟機名為”學生相片”的目錄中，以學生座號或學號為相片的檔名，輸入學生相片檔案的目錄位置檔名資料於『姓名』的下一欄位，如下表。

 

6.有了相片目錄資料，再將『插入相片』勾選，會出現程式訊息提示加入相片目錄資料，按下確定。

再按照前述步驟，先選『座號姓名範圍』，再選取『座位表區域』，然後指定座號姓名資料填入座位表的方式，按下『建立座位表』，即可產生帶有相片資料的座位表如圖三，在圓形的座位區有相片、姓名、與座號。

(圖三)

如何利用Excel 2007 與『座位表製作增益集』 來製作畢旅遊覽車座位表

座位表不是只有教室教室才用到，舉例每次校外教學學生的搭遊覽車，也需要安排座位表，以下我示範如何利用Excel 2007 與『座位表製作增益集』 來製作畢旅遊覽車座位表。因為畢業旅行不會全部人參加，以下會示範如何快速篩選出參加畢業旅行的學生，幫他們安排座車座位。

如何將製作好的座位表轉成XPS檔案格式

微軟在OFFICE 2007提供一個增益集，2007 Microsoft Office 增益集：Microsoft 另存 PDF 或 XPS 檔 可以轉換八種OFFICE檔案格式，在利用Excel2007與『座位表製作增益集』製作的座位表，除了列印為紙張之外，如果要放置於網路提供閱覽，建議可以存成XPS的檔案格式，在使用的經驗上若存成PDF，容易產生圖與表格不對齊之狀況，而存成XPS則無此問題，以下為如何除發佈為XPS格式的操作步驟，使用者可利用IE將檔案開啟閱讀。

如何利用Excel 2007 與『座位表製作增益集』 來快速製作座位表

在秀山國小研習中所示範之製作班級座位表的方法

利用『座位表製作增益集』製作特別座位表

在班級秩序管理中，學生座位的安排相當的重要，奉勸各位辛苦的導師千萬不要讓學生自己安排座位。尤其是手氣欠佳，班上剛好分到幾個麻煩製造者，導師如果不適度的將這些人隔離，而讓他們坐在一起，早晚會群聚感染一發不可收拾。所以在安排座位時，導師可以花點心思，例如擺個梅花陣，讓這些調皮搗蛋的學生分散，中間再擺幾個能穩定軍心的愛將，相信應該能提昇班級上課品質。

這個道理有上過班級經營的相關教育學程都知道，但是現在的中小學導師實在太忙了，要注意的細節太多了，有時候任課班級學生明明已經換座位一段時間，卻仍不見班級有新的座位表，經常變成要考驗任課老師的記憶力，像我們這種記憶力不好的，學生就容易趁機摸魚。

很多年前在當導師的時候我設計了『座位表製作增益集』，開始只是純粹方便自己的班級需求，後來因為當行政，這個程式就沒有再努力修改，程式有些不理想或bug。直到今年因為太座的要求，才又把這個程式做了一些改進，讓程式功能符合太座班級管理的要求。

太座的要求如下

1.能固定某些特別學生的座位。

2.其他未固定座位的同學能亂數排列座位。

3.可以使用非Excel工作表資料格作為座位表標記，因為要有些課程或活動的座位表不是行列式的排法，用Excel工作表資料格標示座位表，只能行列方式呈現。

4.能一次產生多張亂數的座位表。

針對上述的要求，我把程式做了一些修改，主程式外觀的差異不大，只在在主要程式表單新增設定『組合建立』的功能，所謂的組合建立就是可以將多次座位表製作的流程，組合在一次執行完成，其他的功能修改呈現在程式操作過程中。

我以一個例子來示範如何建立一張有特定座位的座位表。

步驟如下：

1.首先關閉正在執行的所有Excel2007程式，下載『座位表製作增益集』解壓縮後執行setup.exe進行安裝，依指示安裝必要之元件如.NET Framework 2.0與VSTO 2005 SE runtimeMicrosoft Visual Studio 2005 Tools for the Microsoft Office System (VSTO2005)語言套件，安裝完成後打開Excel2007程式。

2.在功能區選擇『增益集』/『座位表製作工具』叫出『座位排列工具』表單

3.我用『座位表製作增益集』所附的範例資料作為資料來源，點選『座位表排列工具』表單下方『範例』按鈕，叫出範例的Excel檔。

4.點選Excel2007程式下方工作表切換的按鈕，切換至『座號姓名』工作表，先要確定要建立的座位表的學生資料，資料的原則為兩欄，第一欄為座號(或要標記在姓名之上的資料，例如班級幹部名稱)，第二欄為學生姓名

 

5.首先我們要先建立放入座位表的學生資料，例如我們可以從校務行政系統下載學生座號與姓名資料來利用，再來建立『座號姓名範圍』選取區，例如我們把要固定座位的學生資料選取之後，儲存成一個學生座號資料範圍。請用滑鼠選取座號與姓名欄位資料，也可以只選座號欄位資料，程式會自動以座號右邊的欄位當成姓名資料，選取學生座號姓名資料之後，在『座位表排列工具』表單中按下『學生座號資料』右方的『選取』按鈕。會出現一個輸入『座號姓名的範圍的名稱』的對話表單。

 

在輸入『座號姓名的範圍的名稱』表單中輸入範圍名稱，例如我們把選取的座號1,4,7,9的學生，儲存為『特別座位』這個範圍名稱。

則在『座號姓名範圍』的下拉選單中會新增一個『特別座位』選項，只要選取『特別座位』則會把座號1,4,7,9的學生資料選起反白，另外要在工作表中不連續選取資料格，請按住Crtl鍵不放，再用滑鼠選取所要的資料。再依同樣步驟選取其他不固定座位學生的資料，將該選取區指定一個名稱，例如為『非特別座位』，則則在『座號姓名範圍』的下拉選單中會新增一個『非特別座位』選項。

6.學生座號與姓名資料區選取完畢，再來進行座位表區域的建立，一般教室座位表不外乎是以下這種行列的排列，剛好Excel的工作表基本上就是行列的表格所構成，所以當時會想用Excel來建立座位表而不使用Word或其他的軟體來建立原因在此。

(範例圖一)

7.首先我們先構思好我們要建立的座位排數與每排的人數，排與排之間留空，以(範例圖一)為例，共6排每排7個人，因為此範例是為高中設計所以才會這麼多人，現在國中小大概不會有這麼多人，我們把它改成6×6的座位表，我們選一個空白的工作表，不要和學生資料在同一個工作表，然後為了方便我們選取座位格我們可以先在工作表中輸入資料例如數字，以利我們在選取時的操作，如下表我們在工作表中間放入數字資料，如果每排6個人我們可在一排的資料中輸入1~12，Excel自動填滿的功能，可以很快完成，再複製6排即可。。

在要放座位表的工作表中加入資料是方便我們建立座位表選取區，例如我們只要選資料格數字是偶數的資料格作為座位表所在的位置。

8.建立座位表選取區的方式和建立學生座號姓名的選取區的操作方式是一樣的，在要放置座位的的工作表中選取好座位區域如下圖所示，[注意]要在Excel工作表選取不連續資料格時，滑鼠點選時要同步按住鍵盤的Ctrl鍵不放，才可以選取不連續的資料格。

9.選完座位表資料格後，在『座位表排列工具』表單中按下『座位表的區域』右方的『選取』按鈕。會出現一個輸入『輸入座位表區域的名稱』的對話表單，輸入一個名稱例如『座位表區域』，即可在『座位表區域』下拉選單找到，快速的選取座位區域。

9.建立好座位表選區(學生資料填入的區域)，再來我們開始來設計整個座位表的外觀，首先我們將Excel工作表的格線隱藏，讓工作表呈現白底的狀態，我們可以直接在Excel2007功能區點選『檢視』功能區，在『顯示/隱藏』選單中將『格線』取消。則工作表畫面會呈現如右圖。

10.再將標示用的數字資料清除，再利用『座位表排列工具』表單中『座位表區域』下拉選單中選取『座位表區域』，再利用『常用』/『儲存格』/『格式』/『儲存格格式』將選取區加上格線。

 

11.完成如下圖，但每排的間隔不需要那麼大，我們在將間隔的欄位選起來，調整間隔欄位的寬度，利用『常用』/『儲存格』/『格式』/『欄寬』設定為2

12.我們可以加入一個框線代表教室的範圍，利用『插入』/『圖案』/『矩形』 畫一個圓角矩形，利用『格式』/『圖案樣式』/『圖案填滿』/『無填滿』

得到下圖，我們可以再利用『插入』/『圖案』/『矩形』 畫一個矩形加在前面代表黑板或講台，再利用利用『格式』/『圖案樣式』/『圖案填滿』進行樣式設定完成後如下右圖，完成我們的座位表的外觀設計，現在只要將資料倒入座位表即可。

13.因為我們要建立特定座位的座位表，所以我們要將座位表選取區中指定四個座位給座號1,4,7,9這四位學生，所以我們先在座位表中選定四個位子，為了方便我們可以在資料格中輸入1表示這是特別座，再將這四個位置選起來儲存為座位表選取區，我們將它儲存為『特別座』，儲存之後在『座位表排列工具』表單中按下『座位表的區域』右方的『清除』按鈕清除前面選取資料。

，再將其他的沒有標記座位儲存為『非特別座』。

14.現在已經將座位表區域也設定完成，現在只要將座號姓名選取區與座位表選取區組合對應即可，如果只有一個座號姓名資料區與一個座位表區域，只要在『座號姓名範圍』的下拉選單先選定，再『座位區域』的下拉選單選定資料，設定是否依座號亂數填入或依照座號順序填入建立，選擇亂數建立則依座號亂數填入，如果依座號順序填入會按照座號順序填入座位表區域，座位表區域的先後順序是按照選取的先後順序。現在我們要建立的是有特定座位的座位表，所以整個座位表是由『特別座』與『非特別座』組合而成，所以我們要將『座位排列工具』表單中的『組合建立』選項打勾。在選取座號姓名資料時，先選取『座號姓名範圍』下拉選單的『特別座位』，再點選『非特別座位』，則在『座號姓名範圍』下拉選單下方的文字輸入方塊會出現’"特別座位,非特別座位"，代表組合建立座位表座號姓名資料來源有兩部分分別是『特別座位』與『非特別座位』。再來我們在『座位表區域』下拉選單中選取『特別座』後，再選取『非特別座』，注意順序不可顛倒，必須和『座號姓名範圍』選取先後秩序一致。選完則在『座位表區域』下拉選單下方的文字輸入方塊會出現’"特別座,非特別座"。再選定依座號亂數填入或依照座號順序填入建立，如果是選擇亂數建立可以選擇亂數的次數與一次可以建立多個座位表

15.再選定依座號亂數填入或依照座號順序填入建立，如果是選擇亂數建立可以選擇亂數的次數與一次可以建立多個座位表。我們選擇用亂數填入方式建立座位表，按下『座位排列工具』表單中的『建立座位表』按鈕，在『要建立的座位表數目』輸入3，則可完成三個特定安排座位的座位表。

三個座位表分別位在工作表sheet1，sheet1(2)與sheet1(3)之中。