馮琦勝，高新華

(蘭州大學(xué) 草地農(nóng)業(yè)科技學(xué)院;草地農(nóng)業(yè)系統(tǒng)國家重點(diǎn)實(shí)驗(yàn)室，蘭州 730020)

Excel在留一法交叉驗(yàn)證實(shí)驗(yàn)教學(xué)中的作用

馮琦勝，高新華

(蘭州大學(xué) 草地農(nóng)業(yè)科技學(xué)院;草地農(nóng)業(yè)系統(tǒng)國家重點(diǎn)實(shí)驗(yàn)室，蘭州 730020)

為了使學(xué)生更好地理解和掌握留一法交叉驗(yàn)證的計(jì)算過程，在實(shí)驗(yàn)教學(xué)中利用Excel分步完成留一法交叉驗(yàn)證的計(jì)算過程。這不僅實(shí)現(xiàn)了教學(xué)內(nèi)容與計(jì)算機(jī)軟件應(yīng)用的有機(jī)融合，而且細(xì)化了計(jì)算方法，有利于學(xué)生的學(xué)習(xí)和掌握，達(dá)到了激發(fā)學(xué)生學(xué)習(xí)興趣，提高實(shí)驗(yàn)教學(xué)質(zhì)量的效果。

Excel軟件；留一法交叉驗(yàn)證；實(shí)驗(yàn)教學(xué)；應(yīng)用

交叉驗(yàn)證(cross validation)由于沒有任何前提假定，可以應(yīng)用于各種模型選擇中，因此具有應(yīng)用的普遍性，又由于操作的簡便性，使其成為一種行之有效的模型選擇方法[1-4]。留一法交叉驗(yàn)證(leave-one-out cross validation)是交叉驗(yàn)證方法中最常見的一種方法，是草業(yè)信息模擬模型實(shí)驗(yàn)課的重要內(nèi)容[5]。

雖然,Matlab等專業(yè)軟件提供了相應(yīng)的計(jì)算函數(shù)，但它既不利于學(xué)生對(duì)交叉驗(yàn)證理論的理解，專業(yè)的Matlab語言又對(duì)學(xué)生造成一定的障礙。而利用Excel軟件進(jìn)行相應(yīng)的計(jì)算和分析，既避免了煩瑣的計(jì)算，又使得學(xué)生對(duì)留一法交叉驗(yàn)證的計(jì)算思路有了清晰的理解和認(rèn)識(shí)。本文主要介紹在留一法交叉驗(yàn)證教學(xué)中使用Excel軟件的方法。

1 留一法交叉驗(yàn)證方法簡介

留一法交叉驗(yàn)證的方法[6]：首先，從N個(gè)觀測(cè)數(shù)據(jù)集中選擇一個(gè)觀測(cè)數(shù)據(jù)作為驗(yàn)證數(shù)據(jù)；然后，使用剩下的觀測(cè)數(shù)據(jù)擬合一個(gè)模型，并用最先被排除的那個(gè)觀測(cè)值來驗(yàn)證這個(gè)模型的精度，如此重復(fù)N次。模型的預(yù)測(cè)能力由RMSEP(root-mean-square error of prediction)以及觀測(cè)值與模型預(yù)測(cè)結(jié)果之間的相關(guān)系數(shù)r來衡量。兩者都常用來描述模型的不確定性，RMSEP常被用來量化模型精度，而r常被用來評(píng)估模型的準(zhǔn)確性。RMSEP的計(jì)算公式為：

式中:E(yi)表示第i個(gè)實(shí)際觀測(cè)值；yi為模型反演出的第i個(gè)預(yù)測(cè)值；n是觀測(cè)樣本總數(shù)。

RMSEP數(shù)值越低，表明回歸模型更精確。相關(guān)系數(shù)r越接近于1，表示模型精度越高[7]。

2 使用方法

2.1 基于Excel的回歸模型建立方法

本實(shí)驗(yàn)選取了歸一化植被指數(shù)(NDVI)與草地生物量(kg)之間的24個(gè)數(shù)據(jù)，分析NDVI反演草地生物量的最優(yōu)模型，如圖1所示。為便于理解和操作，備選模型包括線性模型(y=ax+b)、指數(shù)模型(y=a×ebx)、對(duì)數(shù)模型(y=a×ln(x)+b)和乘冪模型(y=a×xb)。先計(jì)算上述4種模型的擬合參數(shù)。Excel提供的LINEST函數(shù)可使用最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描述此直線的數(shù)組[8-9]，利用該函數(shù)，結(jié)合數(shù)學(xué)知識(shí)即可得到上述4種模型的擬合參數(shù)。具體計(jì)算如表1所示。

圖1 練習(xí)數(shù)據(jù)

模型名稱擬合參數(shù)a擬合參數(shù)b線性模型INDEX(LINEST(B2：B25，A2：A25)，1)INDEX(LINEST(B2：B25，A2：A25)，2)指數(shù)模型EXP(INDEX(LINEST(LN(B2：B25)，A2：A25)，2))INDEX(LINEST(LN(B2：B25)，A2：A25)，1)對(duì)數(shù)模型INDEX(LINEST(LN(B2：B25)，A2：A25)，1)INDEX(LINEST(B2：B25，LN(A2：A25))，2)乘冪模型EXP(INDEX(LINEST(LN(B2：B25)，LN(A2：A25))，2))INDEX(LINEST(LN(B2：B25)，LN(A2：A25))，1)

2.2 留一法交叉驗(yàn)證的實(shí)現(xiàn)

根據(jù)留一法交叉驗(yàn)證的思路，應(yīng)先從24個(gè)樣本中選取1個(gè)樣本作為驗(yàn)證樣本，剩余的23個(gè)樣本作為訓(xùn)練樣本，計(jì)算擬合公式，再用驗(yàn)證樣本檢驗(yàn)擬合精度。因此，首先，假設(shè)選取了第1個(gè)樣本，那么就可以通過修改4種模型的參數(shù)計(jì)算公式中的數(shù)據(jù)引用范圍，計(jì)算得到除第1個(gè)樣本之外的23個(gè)樣本的擬合函數(shù)；然后，代入第一個(gè)樣本的NDVI值，得到4種模型對(duì)第1個(gè)樣本的擬合值。例如，將線性模型的擬合參數(shù)分別改為INDEX(LINEST(B3:B25,A3:A25),1)和INDEX(LINEST(B3:B25,A3:A25),2)，得到根據(jù)剩余23個(gè)樣本計(jì)算出的參數(shù)a和b分別為2 211.82和-238.56。這樣，就得到了第1個(gè)擬合函數(shù)y=2 211.82x-238.56，將第1個(gè)樣本的NDVI值0.286代入，即可得到第1個(gè)樣本的擬合值為394.02。采用同樣的方法也可得到其他3種模型相應(yīng)的擬合值。如此重復(fù)24次，即可完成對(duì)24個(gè)樣本的估計(jì)。

借助Excel強(qiáng)大的計(jì)算能力，上述工作也可采用如下方式實(shí)現(xiàn)。首先，將A2:B25單元格的數(shù)據(jù)復(fù)制，粘貼到A26:B49單元格，也就是將樣本數(shù)據(jù)向下復(fù)制一份；然后，在C2單元格輸入如下公式：“=INDEX(LINEST(B3:B25,A3:A25),1)*A2+INDEX(LINEST(B3:B25,A3:A25),2)”，即按照A3:B25單元格數(shù)據(jù)擬合線性模型參數(shù)，再代入A2單元格的NDVI數(shù)值計(jì)算得到第1個(gè)樣本的線性模型的生物量擬合值；最后，將鼠標(biāo)移至C2單元格的右下角，使用Excel的自動(dòng)填充功能將計(jì)算公式向下填充至C25單元格，這樣就自動(dòng)完成了對(duì)24個(gè)樣本的線性估計(jì)。同理，在D2單元格輸入“=EXP(INDEX(LINEST(LN(B3:B25),A3:A25),2))*EXP(INDEX(LINEST(LN(B3:B25),A3:A25),1))*A2)”，向下填充至D25單元格，完成對(duì)24個(gè)樣本的指數(shù)模型估計(jì)；在E2單元格輸入“=INDEX(LINEST(B3:B25,LN(A3:A25)),1)*LN(A2)+INDEX(LINEST(B3:B25,LN(A3:A25)),2)”，向下填充至E25單元格，完成對(duì)24個(gè)樣本的對(duì)數(shù)模型估計(jì)；在F2單元格輸入“=EXP(INDEX(LINEST(LN(B3:B25),LN(A3:A25)),2))*POWER(A2,INDEX(LINEST(LN(B3:B25),LN(A3:A25)),1))”，向下填充至F25單元格，完成對(duì)24個(gè)樣本的乘冪模型估計(jì)。上述計(jì)算結(jié)果如圖2所示。

接下來就可以根據(jù)RMSEP公式，分別計(jì)算各個(gè)模型擬合結(jié)果和原始數(shù)據(jù)之間的擬合精度及相關(guān)系數(shù)。RMSEP的計(jì)算分兩步完成：(1)分別計(jì)算4種模型擬合誤差的平方。在G2單元格輸入公式“=(＄B2-C2)^2”，計(jì)算出第1個(gè)樣本的生物量(B2)與線性函數(shù)擬合結(jié)果(C2)之間的誤差平方，并將該公式向下、向右填充至J25單元格，如圖3所示。公式中符號(hào)＄為絕對(duì)引用。(2)在G26單元格輸入公式“=SQRT(SUM(G2:G25)/24)”計(jì)算線性函數(shù)的RMSEP。同理，可計(jì)算其余3種模型的RMSEP值。

觀測(cè)值與模型預(yù)測(cè)結(jié)果之間的相關(guān)系數(shù)r可使用CORREL()函數(shù)計(jì)算得到。如實(shí)測(cè)生物量(B2:B25)與線性模型擬合得到的生物量(C2:C25)之間的相關(guān)系數(shù)r可在G27單元格輸入公式“=CORREL(＄B＄2:＄B＄25,C2:C25)”計(jì)算得到；實(shí)測(cè)生物量與其他3種模型的擬合結(jié)果之間的相關(guān)系數(shù)可將G27單元格公式向右填充至J27單元格自動(dòng)計(jì)算得到，結(jié)果如圖3所示。至此就完成了留一法交叉驗(yàn)證的計(jì)算。

根據(jù)計(jì)算結(jié)果可以看出，對(duì)于本例的24個(gè)樣本數(shù)據(jù)，在4種備選模型中，指數(shù)模型的RMSEP值最小，為285.74；r值最高，達(dá)0.78。因此，指數(shù)模型更適合于反映NDVI與草地生物量之間的關(guān)系。知道了最適模型，就可以使用Excel的回歸分析工具構(gòu)建相應(yīng)的回歸模型，具體步驟可參見文獻(xiàn)[10-13]，在此不再累述。

圖3 留一法交叉驗(yàn)證的結(jié)果

3 結(jié)束語

草業(yè)信息模擬模型的建立是草業(yè)信息學(xué)實(shí)驗(yàn)課的重要內(nèi)容。交叉驗(yàn)證是模型選擇過程中的常用方法，而留一法交叉驗(yàn)證是交叉驗(yàn)證的一種方法。其優(yōu)點(diǎn)是：(1)每一個(gè)分類器或模型都是用所有的樣本來訓(xùn)練模型，最接近樣本，評(píng)估所得的結(jié)果比較可靠; (2)實(shí)驗(yàn)沒有隨機(jī)因素，整個(gè)過程是可重復(fù)的。

在留一法交叉驗(yàn)證的實(shí)驗(yàn)教學(xué)中使用Excel軟件，使原來相對(duì)抽象的理論轉(zhuǎn)變?yōu)橐子诶斫獾挠?jì)算過程，使理論教學(xué)同實(shí)踐緊密結(jié)合，學(xué)生在掌握交叉驗(yàn)證理論的同時(shí)，熟練掌握具體計(jì)算的方法與步驟，并能對(duì)計(jì)算的結(jié)果有更進(jìn)一步地認(rèn)識(shí)，達(dá)到實(shí)驗(yàn)教學(xué)目的。同時(shí)，也鞏固了學(xué)生對(duì)Excel相關(guān)函數(shù)的理解和使用方法，鍛煉了學(xué)生分析和解決問題的能力，提升了學(xué)生對(duì)Excel軟件的掌握。

[1]范永東. 模型選擇中的交叉驗(yàn)證方法綜述[D]. 太原：山西大學(xué)，2013.

[2]王懷亮. 交叉驗(yàn)證在數(shù)據(jù)建模模型選擇中的應(yīng)用[J]. 商業(yè)經(jīng)濟(jì)，2011(5):20-21.

[3]于惠，馮琦勝，陳思宇，等. 基于微波植被指數(shù)的甘南草地生物量動(dòng)態(tài)監(jiān)測(cè)[J]. 蘭州大學(xué)學(xué)報(bào)：自然科學(xué)版，2011，47(4):69-74.

[4]馮琦勝，高新華，黃曉東，等. 2001-2010年青藏高原草地生長狀況遙感動(dòng)態(tài)監(jiān)測(cè)[J]. 蘭州大學(xué)學(xué)報(bào):自然科學(xué)版，2011，47(4):75-81,90.

[5]常生華，侯扶江. 草業(yè)科學(xué)專業(yè)實(shí)驗(yàn)教學(xué)探索與思考[J].實(shí)驗(yàn)室研究與探索，2013，32(7):161-164.

[6]Stone M. Cross-validatory choice and assessment of statistical predictions[J]. Journal of the Royal Statistical Society, Series B (Methodological)，1974，36(2):111-147.

[7]Olden J D, Jackson D A.Torturing data for the sake of generality: how valid are our regression models?[J]. Ecoscience，2000，7(5):501-510.

[8]Excel Home. Excel 2010應(yīng)用大全[M]. 北京：人民郵電出版社，2013.

[9]Excel Home. Excel 2010函數(shù)與公式實(shí)戰(zhàn)技巧精粹[M]. 北京：人民郵電出版社，2014.

[10]薛香，梁云娟，郜慶爐. Excel在方差分析實(shí)驗(yàn)教學(xué)中的應(yīng)用[J]. 實(shí)驗(yàn)技術(shù)與管理，2010，27(1):94-96.

[11]范平，崔黨群，詹克慧，等. Excel軟件在生物統(tǒng)計(jì)實(shí)驗(yàn)教學(xué)中的綜合開發(fā)應(yīng)用[J]. 實(shí)驗(yàn)技術(shù)與管理，2003，20(2):65-69.

[12]高曉林. Excel在實(shí)驗(yàn)數(shù)據(jù)回歸分析中的應(yīng)用[J]. 實(shí)驗(yàn)技術(shù)與管理，2005，22(5):28-31.

[13]唐正姣，張光旭，歐陽貽德，等. 用Excel 2000處理精餾實(shí)驗(yàn)數(shù)據(jù)[J]. 實(shí)驗(yàn)技術(shù)與管理，2005，22(12):69-72.

Application of Excel in the Experiment Teaching of Leave-one-out Cross Validation

FENG Qisheng, GAO Xinhua

( College of Pastoral Agriculture Science and Technology; State Key Laboratory of Grassland Agro-ecosystems, Lanzhou University, Lanzhou 730020, China)

Making students understand and master the calculation process of leave-one-out cross validation better, in experimental teaching, using Excel to finish the counting process of leave-one-out cross validation step by step. In this way, it does not only integrate teaching contents with computer software together, but also refine the calculation method. It is good for studying and mastering. It aims to motivate students，interests, and improve the efficiency of experimental teaching quality

Excel software; leave-one-out cross validation; experiment teaching;application

2014-07-15；修改日期: 2014-09-29

長江學(xué)者和創(chuàng)新團(tuán)隊(duì)發(fā)展計(jì)劃基金資助項(xiàng)目(IRT13019)。

馮琦勝(1983-)，男，博士，實(shí)驗(yàn)師，主要從事草地遙感和地理信息系統(tǒng)等方面的科研和教學(xué)工作。

TP

Adoi:10.3969/j.issn.1672-4550.2015.02.015