Excel答案非唯一考核點(diǎn)自動閱卷技術(shù)分析

陳永松

0 引言

Excel軟件的使用是計(jì)算機(jī)基礎(chǔ)最重要的實(shí)驗(yàn)教學(xué)環(huán)節(jié)之一，在Excel實(shí)驗(yàn)系統(tǒng)和考試系統(tǒng)中實(shí)現(xiàn)自動閱卷，對提高學(xué)生學(xué)習(xí)效果和減輕教師工作量都大有幫助。現(xiàn)有的Excel操作題自動閱卷研究，基本上都是利用VBA技術(shù)實(shí)現(xiàn)自動閱卷[1-3]，但多數(shù)研究只介紹答案唯一考核點(diǎn)閱卷方法[1,3]。Excel有一些操作，答案并不唯一，如果不進(jìn)行適當(dāng)?shù)奶幚?，很容易出現(xiàn)誤判、漏判。Excel最可能出現(xiàn)出現(xiàn)答案非唯一的考核點(diǎn)包括高級篩選、數(shù)據(jù)庫函數(shù)條件區(qū)域的設(shè)置和復(fù)雜函數(shù)的使用，本文將針對這兩種考核點(diǎn)，舉例說明實(shí)現(xiàn)準(zhǔn)確自動閱卷的方法。

1 條件區(qū)域自動閱卷技術(shù)分析

高級篩選和數(shù)據(jù)庫函數(shù)的使用都是Excel最重要的考核題型之一，其中條件區(qū)域的設(shè)置可能會有n種結(jié)果。例如從數(shù)據(jù)清單中高級篩選出學(xué)年總成績大于等于400但小于500或者女學(xué)生信息，就可能有多種不同的定義條件區(qū)域方法，其中的兩種定義方法如表1和表2所示，在標(biāo)準(zhǔn)答案中要窮舉各種可能的條件區(qū)域是不現(xiàn)實(shí)的?？刹捎门判蚍╗4]實(shí)現(xiàn)條件區(qū)域的自動閱卷。即對學(xué)生定義的條件區(qū)域依次按第一行、第二行、……排序，然后再按第一列、第二列、……進(jìn)行排序，經(jīng)過調(diào)整后將學(xué)生答案和標(biāo)準(zhǔn)答案進(jìn)行比較，即可實(shí)現(xiàn)自動閱卷。例如表1定義的條件區(qū)域經(jīng)過排序處理后可轉(zhuǎn)換成表2，這樣只需將標(biāo)準(zhǔn)答案設(shè)置成表2形式（如果提供其他答案可以按同樣的方法進(jìn)行轉(zhuǎn)換后作為標(biāo)準(zhǔn)答案）即可實(shí)現(xiàn)對不同答案的檢驗(yàn)。

?

?

2 公式函數(shù)自動閱卷技術(shù)分析

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

2.1 等效引用法

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

2.2 列舉法

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

2.3 特殊參數(shù)驗(yàn)證法

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

2.4 等效條件法

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

使用等效條件法進(jìn)行閱卷，教師只需提供任意一種正確答案，下面以參考答案“=IF(D3>=80,"優(yōu)秀",IF(D3<60,"不合格","合格"))”為例，說明這種方法的使用。首先從函數(shù)字符串中提取第1個條件“D3>=80”、第1個返回值“優(yōu)秀”、第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)”。轉(zhuǎn)化后結(jié)果如表3所示。閱卷時將學(xué)生答題函數(shù)按照同樣的方法進(jìn)行轉(zhuǎn)換，如果能得到表3結(jié)果則認(rèn)為正確。

表3 IF函數(shù)條件轉(zhuǎn)換結(jié)果

3 閱卷結(jié)果分析

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

4 結(jié)束語

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

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

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

[3]林海寧.基于VBA技術(shù)的Excel自動評閱系統(tǒng)[J].現(xiàn)代計(jì)算機(jī)：下半月版,2012,(14):78-80.DOI:10.3969

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