Excel答案非唯一考核點自動閱卷技術分析

陳永松

0 引言

Excel軟件的使用是計算機基礎最重要的實驗教學環節之一，在Excel實驗系統和考試系統中實現自動閱卷，對提高學生學習效果和減輕教師工作量都大有幫助。現有的Excel操作題自動閱卷研究，基本上都是利用VBA技術實現自動閱卷[1-3]，但多數研究只介紹答案唯一考核點閱卷方法[1,3]。Excel有一些操作，答案并不唯一，如果不進行適當的處理，很容易出現誤判、漏判。Excel最可能出現出現答案非唯一的考核點包括高級篩選、數據庫函數條件區域的設置和復雜函數的使用，本文將針對這兩種考核點，舉例說明實現準確自動閱卷的方法。

1 條件區域自動閱卷技術分析

高級篩選和數據庫函數的使用都是Excel最重要的考核題型之一，其中條件區域的設置可能會有n種結果。例如從數據清單中高級篩選出學年總成績大于等于400但小于500或者女學生信息，就可能有多種不同的定義條件區域方法，其中的兩種定義方法如表1和表2所示，在標準答案中要窮舉各種可能的條件區域是不現實的。可采用排序法[4]實現條件區域的自動閱卷。即對學生定義的條件區域依次按第一行、第二行、……排序，然后再按第一列、第二列、……進行排序，經過調整后將學生答案和標準答案進行比較，即可實現自動閱卷。例如表1定義的條件區域經過排序處理后可轉換成表2，這樣只需將標準答案設置成表2形式（如果提供其他答案可以按同樣的方法進行轉換后作為標準答案）即可實現對不同答案的檢驗。

?

?

2 公式函數自動閱卷技術分析

針對函數使用的多樣性，文獻[2]提到了一種方法，先判斷考生是否輸入函數，如果是函數則檢驗考生文檔中應用函數計算出來的結果值，如果與參考答案的結果值相同則認為函數正確。這種方法用“=結果”的方式輸入函數會誤判為正確，為避免這種情況，使用這種方法進行閱卷必須同時檢驗函數名或運算符，只有函數名、運算符、結果都正確才能認為是正確的。但即使這樣，還是很容易產生錯判。例如使用 RANK函數并進行復制操作，有一種典型錯誤是第2個參數誤用相對坐標，但如果考生不用復制，而是每個單元格分別輸入函數，則計算出來的結果值和參考答案的結果值相同，這種方法將誤判為正確。為提高閱卷的準確率，函數的閱卷應以函數表達式的檢驗為主。前面所介紹的排序法也可用于函數閱卷，例如4個數相加有多種組合，但排序后就剩一種組合了。除排序法外，還有四種方法可用于函數閱卷，下面分別進行介紹。

2.1 等效引用法

這種方法適用于使用不同方式引用單元格都正確的函數閱卷。例如根據A1:E49數據清單的數據(車間在C列，利潤在E列)，使用SUMIF函數在單元格H2計算G2單元格內容對應車間的利潤合計，然后復制公式到 H3:H10區域中。這時H2輸入函數“=SUMIF($C$2:$C$49,$G2,$E$2:$E$49)”是正確的，然而輸入函數時部分或全部列號沒有加上“$”符號也是正確的。如果將每種可能答案都列舉不太現實。采用等效引用法進行閱卷，閱卷時對復制的區域進行分析，如果復制區域的列標相同，則將參考答案和學生答案中列標前的“$”全部去掉，如果結果相同判斷為正確。在本例中，只要學生輸入的函數等效于“=SU MIF(C$2:C$49,G2,E$2:E$49)”則結果正確。同樣如果復制區域的行號相同，或者輸入的函數含有工作表、工作薄地址，可采用類似的方法進行處理。

2.2 列舉法

對于只有少數幾種正確結果的函數可采用列舉法。例如根據H2:H26的總分，使用RANK函數在單元格I2計算H2單元格對應總分的名次，成績越高名次越靠前，然后復制公式到I3:I26區域，經等效引用法處理后仍有三種答案正確，即“=RANK (H2,H$2:H$26,FALSE)”、“=RANK(H2,H$2:H$26)”和“=RANK(H2,H$2:H$26,0)”，這時可以用列舉法，即只要是這三種答案中的一個就認為是正確的。

2.3 特殊參數驗證法

如果函數某個參數為TRUE或任意非零值，則該函數有無數種正確結果。例如根據F2:F8的總桿數，使用RANK函數在單元格H2計算F2單元格對應總桿數的名次，排名原則為總桿數越少排名越前，并復制函數到H3:H8區域。其中一種正確答案是“=RANK(F2,F$2:F$8,1)”，第 3個參數可以是TRUE或任意非零值。這類函數可以采取特殊參數驗證法進行閱卷，即先驗證函數名是否正確，然后提取參數子串(本例為“F2,F$2:F$8,1”)，然后按“，”進行分割將每個參數作為一個元素存到數組中，再對每個參數進行逐一對照，如果前2個參數匹配且第3個參數為TRUE或非零數值則結果正確。

2.4 等效條件法

這種方法適用于嵌套IF函數的閱卷。例如根據D3單元格的成績，在單元格G3中使用IF函數計算學生等級，如果成績>=80為優秀，成績<80但>=60為合格，成績<60為不合格，然后復制公式到G4:G22區域中。這時函數為“=IF(D3>=80,"優秀",IF(D3>= 60,"合格","不合格"))”是正確的，函數為“=IF (AND(D3>=60,D3<80),"合格",IF(D3<60,"不合格", IF(D3>=80,"優秀")))”也是正確的，如果將各種可能正確結果都列舉出來幾乎是不可能的。

使用等效條件法進行閱卷，教師只需提供任意一種正確答案，下面以參考答案“=IF(D3>=80,"優秀",IF(D3<60,"不合格","合格"))”為例，說明這種方法的使用。首先從函數字符串中提取第1個條件“D3>=80”、第1個返回值“優秀”、第1個條件的反向條件“D3<80”；然后取第2個條件：“D3<60”、第2個返回值“不合格”、第2個條件的反向條件“D3 >=60”，再將第2個條件和第1個條件的反向條件合并得“AND(D3<80,D3<60)”，簡化后得“D3<60”；第 2個無條件，返回值為“合格”，合并第 1、2個條件的反向條件得“AND(D3<80,D3>=60)”。轉化后結果如表3所示。閱卷時將學生答題函數按照同樣的方法進行轉換，如果能得到表3結果則認為正確。

表3 IF函數條件轉換結果

3 閱卷結果分析

經過本文所介紹方法進行處理后，在理論上仍會有誤判、錯判的情況發生，例如對B2:E2進行求和，如果考生輸入=SUM(B2:C2,D2:E2)將誤判為錯誤，但采用這種方法輸入函數的學生微乎其微。為了檢驗本文介紹方法的有效性，針對函數考核點，分別采用本文所介紹方法和文獻[2]所介紹方法對實際考卷進行閱卷處理，然后對比分數有差異的考卷，發現文獻[2]所介紹方法誤判率遠高于本文所介紹方法。而在條件區域考核點方面，經過本文所介紹方法處理后閱卷準確性更是大為提高。

4 結束語

現有Excel操作題閱卷方案雖然很多，但是在對答案非唯一題型進行閱卷時容易出現錯誤。本文針對Excel最有可能出現答案非唯一的條件區域的設置和函數的使用，介紹了五種有效的解決方法，包括排序法、等效引用法、列舉法、特殊參數驗證法和等效條件法。經過本院多次實驗和考試檢驗，將本文所介紹方法用于Excel自動閱卷當中，能夠使閱卷準確性得到比較大的提高。

[1]李愛玲,宋志剛.基于VBA的Excel文檔自動評閱技術研究[J].科學技術與工程,2011,11(34):8504-8508.

[2]彭作民.EXCEL文檔自動閱卷評分算法設計[J].南京師范大學學報（工程技術版）,2007,7(3):70-73.

[3]林海寧.基于VBA技術的Excel自動評閱系統[J].現代計算機：下半月版,2012,(14):78-80.DOI:10.3969

[4]梁里寧.用VBA實現Excel工作表的自動排序[J].辦公自動化（綜合版）,2009,(1):60.