Excel在效度計算與檢驗(yàn)中的應(yīng)用

摘? 要 效度是教育測量中最基本、最重要的問題，是選擇和評價教育測量質(zhì)量的重要指標(biāo)。人們常用統(tǒng)計分析方法計算與檢驗(yàn)效度。對Excel而言，計算與檢驗(yàn)效度非常準(zhǔn)確且極為簡便。以計算與檢驗(yàn)點(diǎn)二列相關(guān)系數(shù)為例，作一簡要介紹。

關(guān)鍵詞 教育測量;Excel;函數(shù);效度;點(diǎn)二列相關(guān)

中圖分類號：G633.6? ? 文獻(xiàn)標(biāo)識碼：B

文章編號：1671-489X（2019）03-0052-03

1 前言

對于任何一種測量來說，信度都是必不可少的條件，但并非唯一的條件。而對教育測量來說，效度顯得更為重要。在教育測量中，效度是某一測驗(yàn)?zāi)軌蛘_地測量它所要測量的特征或功能的程度。測量結(jié)果與要考察的內(nèi)容越吻合，則效度越高;反之，則效度越低。效度是相對的，僅針對特定目標(biāo)而言，因此只有程度上的差別。不同于信度反映測驗(yàn)一致性的程度，效度反映測驗(yàn)的準(zhǔn)確性程度。效度是教育測量中最基本、最重要的問題，是選擇和評價教育測量質(zhì)量的重要指標(biāo)。使用傳統(tǒng)方法計算與檢驗(yàn)效度系數(shù)容易出錯且相當(dāng)?shù)托?，而使用Excel技術(shù)卻是非常準(zhǔn)確且極為簡便的。

2 效度系數(shù)計算與檢驗(yàn)的原理

考驗(yàn)效度的方法很多，其中J.W.弗倫奇和W.B.米歇貝（French & Michbel）根據(jù)測驗(yàn)?zāi)繕?biāo)，把效度分為內(nèi)容效度、構(gòu)想效度和效標(biāo)關(guān)聯(lián)效度。這種分類為美國心理學(xué)會在1974年發(fā)行的《教育與心理測驗(yàn)的標(biāo)準(zhǔn)》一書所采納，成為通行的效度分類方法。其中，效標(biāo)關(guān)聯(lián)效度指測驗(yàn)分?jǐn)?shù)與效度標(biāo)準(zhǔn)（簡稱效標(biāo)）的一致程度，分為同時效度和預(yù)測效度。效標(biāo)是衡量測驗(yàn)有效性的參照標(biāo)準(zhǔn)，常見的效標(biāo)主要有學(xué)業(yè)成就、等級評定、臨床診斷、特殊訓(xùn)練成績、實(shí)際工作表現(xiàn)和其他測驗(yàn)成績。由于可以進(jìn)行量化分析，因而在教育測量中，效標(biāo)關(guān)聯(lián)效度應(yīng)用最為廣泛。

效標(biāo)關(guān)聯(lián)效度的計算方法較多，常用相關(guān)法、分組法、回歸法、命中取舍法、預(yù)期表法。相關(guān)法又包括積差相關(guān)、二列相關(guān)、點(diǎn)二列相關(guān)、四分相關(guān)、Ф相關(guān)、列聯(lián)相關(guān)等。由于相關(guān)系數(shù)是一種統(tǒng)計量，因此，這種效度又稱統(tǒng)計效度。一種測驗(yàn)與效標(biāo)之間的相關(guān)系數(shù)稱為效度系數(shù)。效度系數(shù)越大，測驗(yàn)的效度越高。

點(diǎn)二列相關(guān)的一列變量是等距或等比的總體分布為正態(tài)的連續(xù)測量數(shù)據(jù)，另一列是真實(shí)二分稱名數(shù)據(jù)。

連續(xù)變量是在一定區(qū)間內(nèi)可以任意取值的變量，其數(shù)值是連續(xù)不斷的，相鄰兩個數(shù)值可作無限分割。等距變量和等比變量屬于連續(xù)變量。等距變量既有測量單位，又有相對零點(diǎn)，只能進(jìn)行加、減運(yùn)算，如考試分?jǐn)?shù)、溫度。等比變量既有標(biāo)準(zhǔn)的測量單位，又有絕對零點(diǎn)，可以進(jìn)行加、減、乘、除運(yùn)算，如身高、體重。

稱名變量只說明某一事物與其他事物在屬性上的不同或類別上的差異，數(shù)值一般都是整數(shù)形式，只計算個數(shù)，并不說明事物之間差異的大小。如果稱名變量只有兩類結(jié)果，則稱為二分稱名變量。二分變量又分為真正的二分變量和人為的二分變量兩種。真正的二分變量是離散型二分變量，如男、女。人為的二分變量，是指該變量本身是一個連續(xù)型的測量數(shù)據(jù)，兩種結(jié)果之間本來是一個連續(xù)統(tǒng)一體，但被某種人為規(guī)定的標(biāo)準(zhǔn)劃分為兩個類別。

在這種情況下，一個測量結(jié)果很明顯地要么屬于這個類別，要么屬于另一個類別，兩種類別之間一般也不會被看作是連續(xù)的，如合格、不合格。所謂點(diǎn)二列，就是連續(xù)數(shù)列的數(shù)據(jù)點(diǎn)與真正二分?jǐn)?shù)列中的點(diǎn)存在一一對應(yīng)的關(guān)系，這兩個數(shù)列就是點(diǎn)二列。點(diǎn)二列相關(guān)系數(shù)的計算公式為：

式中，是與二分稱名變量的一個值對應(yīng)的連續(xù)變量的平均數(shù)，是與二分稱名變量的另一個值對應(yīng)的連續(xù)變量的平均數(shù)，p與q是二分稱名變量兩個值各自所占的比例（p+q=1），St是連續(xù)變量全部數(shù)據(jù)的標(biāo)準(zhǔn)差。

點(diǎn)二列相關(guān)系數(shù)的取值在-1～1之間，絕對值越接近1，相關(guān)越高，其顯著性檢驗(yàn)常用t檢驗(yàn)法和臨界值法。t檢驗(yàn)統(tǒng)計量公式為：

使用臨界值法時，如果實(shí)際計算的rpb大于等于臨界值，則拒絕零假設(shè)，認(rèn)為兩個變量之間存在真正相關(guān);如果實(shí)際計算的rpb小于臨界值，則不能拒絕零假設(shè)，認(rèn)為兩個變量之間不存在真正相關(guān)。積差相關(guān)系數(shù)r的臨界值可由自由度為n-2的t分布臨界值計算得到，公式為：

二列相關(guān)的兩列數(shù)據(jù)均屬于正態(tài)分布，其中一列變量為等距或等比的測量數(shù)據(jù)，另一列是人為二分稱名數(shù)據(jù)。計算二列相關(guān)系數(shù)有兩個等效公式：

二列相關(guān)系數(shù)的顯著性檢驗(yàn)可近似地進(jìn)行Z檢驗(yàn)：

點(diǎn)二列相關(guān)與二列相關(guān)的區(qū)別在于二分變量是否為正態(tài)分布。如果不是十分明確，此時就不管二分變量是真正的二分變量還是人為的二分變量，就統(tǒng)一用點(diǎn)二列相關(guān);只有確認(rèn)二分變量是正態(tài)分布時，才選用二列分布。

除了用于計算效度，點(diǎn)二列相關(guān)、二列相關(guān)還常用于計算判斷題、主觀題的區(qū)分度，而區(qū)分度又要受難度的影響。其他各類相關(guān)都有相應(yīng)的計算公式與檢驗(yàn)方法，這里不再贅述。

3 效度系數(shù)計算與檢驗(yàn)的實(shí)例

在Excel中，計算與檢驗(yàn)效度系數(shù)是非常方便、快捷的，本文以點(diǎn)二列相關(guān)法為例作一介紹。

【例】有一位教師自編一套測驗(yàn)題，用來預(yù)測一年后的英語成績。自編測驗(yàn)題按百分制評分，一年后進(jìn)行英語測驗(yàn)，把學(xué)生分為及格與不及格兩類進(jìn)行評定，及格記為1，不及格記為0，測驗(yàn)結(jié)果如圖1所示。試估計自編測驗(yàn)題的預(yù)測效度并進(jìn)行檢驗(yàn)。（α=0.05）

【分析】自編測驗(yàn)題得分為連續(xù)變量，一年后的英語測驗(yàn)為二分稱名變量（效標(biāo)），由于難以判斷二分變量是否為正態(tài)分布，本例就計算點(diǎn)二列相關(guān)系數(shù)。

【解】為便于理解，將兩次測驗(yàn)的成績與點(diǎn)二列相關(guān)系數(shù)的計算與檢驗(yàn)都布局在一張Excel工作表中，布局如圖1所示。在F列各單元格輸入函數(shù)公式，以實(shí)現(xiàn)自動計算。

F2單元格的公式為“=COUNT（B3：B16）”。COUNT函數(shù)計算包含數(shù)字的單元格以及參數(shù)列表中數(shù)字的個數(shù)。

F3單元格的公式為“{=AVERAGE（IF（C3：C16，B3：B16））}”。

式中，IF函數(shù)是用于邏輯比較的函數(shù)，其語法形式為“如果（內(nèi)容為TRUE，則執(zhí)行某些操作，否則就執(zhí)行其他操作）”，第三參數(shù)為“FALSE”或“0”時可省略。IF函數(shù)在這里的作用是將C列空單元格對應(yīng)的B列的數(shù)據(jù)轉(zhuǎn)換為邏輯值FALSE。IF函數(shù)判定的結(jié)果為“{65;12;26;44;66;45;60;

FALSE;28;FALSE;FALSE;28;35;30}”，該結(jié)果作為AVERAGE函數(shù)的參數(shù)。AVERAGE函數(shù)返回參數(shù)的算術(shù)平均值，如果區(qū)域或單元格引用參數(shù)包含文本、邏輯值或空單元格，則這些值將被忽略，但包含零值的單元格將被計算在內(nèi)，所以這里只計算“{65;12;26;44;66;45;60;28;28;35;30}”等11個數(shù)字的平均值?！皗}”為數(shù)組公式的標(biāo)志，不要手動鍵入該花括號。在編輯欄輸入公式后，按下“Ctrl+Shift+

Enter”組合鍵，就會自動鎖定數(shù)組公式。

F4單元格的公式為“{=AVERAGE（IF（C3：C16，TRUE，B3：

B16））}”。式中，IF函數(shù)在這里的作用是將C列不為空的單元格對應(yīng)的B列的數(shù)據(jù)轉(zhuǎn)換為邏輯值TRUE。IF函數(shù)判定的結(jié)果為“{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;25;TRUE;

24;32;TRUE;TRUE;TRUE}”，該結(jié)果作為AVERAGE函數(shù)的參數(shù)。AVERAGE函數(shù)只計算“{25;24;32}”等三個數(shù)字的平均值。

F5單元格的公式為“=COUNTIF（C3：C16，1）/F2”。式中，COUNTIF是一個用于統(tǒng)計滿足某個條件的單元格數(shù)量的統(tǒng)計函數(shù)，其語法形式為“=COUNTIF（要檢查哪些區(qū)域，要查找哪些內(nèi)容）”。

F6單元格的公式為“=1-F5”。

F7單元格的公式為“=STDEV.P（B3：B16）”。式中，STDEV.P函數(shù)計算基于以參數(shù)形式給出的整個樣本總體的標(biāo)準(zhǔn)偏差（忽略邏輯值和文本）。如果只計算樣本的標(biāo)準(zhǔn)偏差，則使用STDEV.S函數(shù)。這里沒有使用原始公式或推演公式進(jìn)行計算。

F8單元格的公式為“=（F3-F4）/F7*SQRT（F5*F6）”。式中，SQRT函數(shù)返回一個參數(shù)正的平方根。該式按照點(diǎn)二列相關(guān)系數(shù)的公式進(jìn)行計算。

F10單元格的公式為“=F2-2”。

F11單元格的公式為“=F8*SQRT（F2-2）/SQRT（1-F8^2）”。

該式按照t檢驗(yàn)統(tǒng)計量公式進(jìn)行計算。

F12單元格的公式為“=T.INV.2T（F9，F(xiàn)10）”或“=T.INV（1-F9/2，F(xiàn)10）”。式中，T.INV.2T函數(shù)返回學(xué)生t分布的雙尾反函數(shù)，其語法形式為“T.INV.2T（與學(xué)生t分布相關(guān)的概率，自由度數(shù)）”。T.INV函數(shù)則返回學(xué)生t分布的左尾反函數(shù)，其語法形式為“T.INV（t分布概率，自由度數(shù)）”。借助t分布的Excel函數(shù)，計算出點(diǎn)二列相關(guān)系數(shù)的臨界值，就免除了查臨界值表的手工操作，能顯著提高工作效率。

F13單元格的公式為“=SQRT（T.INV.2T（F9，F(xiàn)10）^2/（F10+

T.INV.2T（F9，F(xiàn)10）^2））”。因?yàn)閠分布的平方等于F分布，所以式中的“T.INV.2T（F9，F(xiàn)10）^2”也可以用“F.INV.RT（F9，1，F(xiàn)10）”代替。F.INV.RT函數(shù)返回右尾F概率分布函數(shù)的反函數(shù)值，其語法形式為“F.INV.RT（累積分布的概率值，分子自由度，分母自由度）。使用該函數(shù)計算t分布臨界值，也就不需要再查臨界值表了，工作效度得以提高不言自明。

F14單元格的公式為“=IF（F11>F12，"拒絕H0"，"不拒絕H0"）”。本例t=1.219

rα（n-2）=0.532 4，rpb小于其臨界值，故結(jié)論也是自編測驗(yàn)題的效度較低。

F9單元格可設(shè)置數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性），以方便選擇。使用“序列”類型，序列為“″0.01，0.05”。

兩次測驗(yàn)的成績、點(diǎn)二列相關(guān)系數(shù)的計算與檢驗(yàn)結(jié)果如圖1所示。

4 結(jié)語

在Excel工作表中可以按需要靈活地進(jìn)行布局。運(yùn)用函數(shù)公式時，要注意對單元格或區(qū)域的行、列的相對引用或絕對引用，以提高自動化計算水平。對B、C的原始數(shù)據(jù)，可以定義為名稱，嵌套在上述有關(guān)公式中，這樣就能做出一個高效模板，原始數(shù)據(jù)個數(shù)增加或減少都沒有關(guān)系，不必去刪除或插入行，或費(fèi)力地修改函數(shù)公式的引用，使得點(diǎn)二列相關(guān)這種效度系數(shù)的計算與檢驗(yàn)簡便易行。

此外，對信度、難度、區(qū)分度等教育測量指標(biāo)的計算，Excel也是輕而易舉的，完全可以滿足人們從事教育測量研究的需要?！?/p>

參考文獻(xiàn)

[1]朱德全，宋及慶.現(xiàn)代教育統(tǒng)計與測評技術(shù)[M].重慶：西南師范大學(xué)出版社，1998.

[2]張厚粲，徐建平，現(xiàn)代心理與教育統(tǒng)計學(xué)[M].北京：北京師范大學(xué)出版社，2015.

[3]馮伯麟.教育統(tǒng)計學(xué)[M].北京：人民教育出版社，2005.

[4]張運(yùn)明.Excel2016數(shù)據(jù)處理與分析實(shí)戰(zhàn)秘籍[M].北京：清華大學(xué)出版社，2018.