Excel在留一法交叉验证实验教学中的作用

冯琦胜，高新华

(兰州大学 草地农业科技学院;草地农业系统国家重点实验室，兰州 730020)

Excel在留一法交叉验证实验教学中的作用

冯琦胜，高新华

(兰州大学 草地农业科技学院;草地农业系统国家重点实验室，兰州 730020)

为了使学生更好地理解和掌握留一法交叉验证的计算过程，在实验教学中利用Excel分步完成留一法交叉验证的计算过程。这不仅实现了教学内容与计算机软件应用的有机融合，而且细化了计算方法，有利于学生的学习和掌握，达到了激发学生学习兴趣，提高实验教学质量的效果。

Excel软件；留一法交叉验证；实验教学；应用

交叉验证(cross validation)由于没有任何前提假定，可以应用于各种模型选择中，因此具有应用的普遍性，又由于操作的简便性，使其成为一种行之有效的模型选择方法[1-4]。留一法交叉验证(leave-one-out cross validation)是交叉验证方法中最常见的一种方法，是草业信息模拟模型实验课的重要内容[5]。

虽然,Matlab等专业软件提供了相应的计算函数，但它既不利于学生对交叉验证理论的理解，专业的Matlab语言又对学生造成一定的障碍。而利用Excel软件进行相应的计算和分析，既避免了烦琐的计算，又使得学生对留一法交叉验证的计算思路有了清晰的理解和认识。本文主要介绍在留一法交叉验证教学中使用Excel软件的方法。

1 留一法交叉验证方法简介

留一法交叉验证的方法[6]：首先，从N个观测数据集中选择一个观测数据作为验证数据；然后，使用剩下的观测数据拟合一个模型，并用最先被排除的那个观测值来验证这个模型的精度，如此重复N次。模型的预测能力由RMSEP(root-mean-square error of prediction)以及观测值与模型预测结果之间的相关系数r来衡量。两者都常用来描述模型的不确定性，RMSEP常被用来量化模型精度，而r常被用来评估模型的准确性。RMSEP的计算公式为：

式中:E(yi)表示第i个实际观测值；yi为模型反演出的第i个预测值；n是观测样本总数。

RMSEP数值越低，表明回归模型更精确。相关系数r越接近于1，表示模型精度越高[7]。

2 使用方法

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

本实验选取了归一化植被指数(NDVI)与草地生物量(kg)之间的24个数据，分析NDVI反演草地生物量的最优模型，如图1所示。为便于理解和操作，备选模型包括线性模型(y=ax+b)、指数模型(y=a×ebx)、对数模型(y=a×ln(x)+b)和乘幂模型(y=a×xb)。先计算上述4种模型的拟合参数。Excel提供的LINEST函数可使用最小二乘法对已知数据进行最佳直线拟合，并返回描述此直线的数组[8-9]，利用该函数，结合数学知识即可得到上述4种模型的拟合参数。具体计算如表1所示。

图1 练习数据

模型名称拟合参数a拟合参数b线性模型INDEX(LINEST(B2：B25，A2：A25)，1)INDEX(LINEST(B2：B25，A2：A25)，2)指数模型EXP(INDEX(LINEST(LN(B2：B25)，A2：A25)，2))INDEX(LINEST(LN(B2：B25)，A2：A25)，1)对数模型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 留一法交叉验证的实现

根据留一法交叉验证的思路，应先从24个样本中选取1个样本作为验证样本，剩余的23个样本作为训练样本，计算拟合公式，再用验证样本检验拟合精度。因此，首先，假设选取了第1个样本，那么就可以通过修改4种模型的参数计算公式中的数据引用范围，计算得到除第1个样本之外的23个样本的拟合函数；然后，代入第一个样本的NDVI值，得到4种模型对第1个样本的拟合值。例如，将线性模型的拟合参数分别改为INDEX(LINEST(B3:B25,A3:A25),1)和INDEX(LINEST(B3:B25,A3:A25),2)，得到根据剩余23个样本计算出的参数a和b分别为2 211.82和-238.56。这样，就得到了第1个拟合函数y=2 211.82x-238.56，将第1个样本的NDVI值0.286代入，即可得到第1个样本的拟合值为394.02。采用同样的方法也可得到其他3种模型相应的拟合值。如此重复24次，即可完成对24个样本的估计。

借助Excel强大的计算能力，上述工作也可采用如下方式实现。首先，将A2:B25单元格的数据复制，粘贴到A26:B49单元格，也就是将样本数据向下复制一份；然后，在C2单元格输入如下公式：“=INDEX(LINEST(B3:B25,A3:A25),1)*A2+INDEX(LINEST(B3:B25,A3:A25),2)”，即按照A3:B25单元格数据拟合线性模型参数，再代入A2单元格的NDVI数值计算得到第1个样本的线性模型的生物量拟合值；最后，将鼠标移至C2单元格的右下角，使用Excel的自动填充功能将计算公式向下填充至C25单元格，这样就自动完成了对24个样本的线性估计。同理，在D2单元格输入“=EXP(INDEX(LINEST(LN(B3:B25),A3:A25),2))*EXP(INDEX(LINEST(LN(B3:B25),A3:A25),1))*A2)”，向下填充至D25单元格，完成对24个样本的指数模型估计；在E2单元格输入“=INDEX(LINEST(B3:B25,LN(A3:A25)),1)*LN(A2)+INDEX(LINEST(B3:B25,LN(A3:A25)),2)”，向下填充至E25单元格，完成对24个样本的对数模型估计；在F2单元格输入“=EXP(INDEX(LINEST(LN(B3:B25),LN(A3:A25)),2))*POWER(A2,INDEX(LINEST(LN(B3:B25),LN(A3:A25)),1))”，向下填充至F25单元格，完成对24个样本的乘幂模型估计。上述计算结果如图2所示。

接下来就可以根据RMSEP公式，分别计算各个模型拟合结果和原始数据之间的拟合精度及相关系数。RMSEP的计算分两步完成：(1)分别计算4种模型拟合误差的平方。在G2单元格输入公式“=(＄B2-C2)^2”，计算出第1个样本的生物量(B2)与线性函数拟合结果(C2)之间的误差平方，并将该公式向下、向右填充至J25单元格，如图3所示。公式中符号＄为绝对引用。(2)在G26单元格输入公式“=SQRT(SUM(G2:G25)/24)”计算线性函数的RMSEP。同理，可计算其余3种模型的RMSEP值。

观测值与模型预测结果之间的相关系数r可使用CORREL()函数计算得到。如实测生物量(B2:B25)与线性模型拟合得到的生物量(C2:C25)之间的相关系数r可在G27单元格输入公式“=CORREL(＄B＄2:＄B＄25,C2:C25)”计算得到；实测生物量与其他3种模型的拟合结果之间的相关系数可将G27单元格公式向右填充至J27单元格自动计算得到，结果如图3所示。至此就完成了留一法交叉验证的计算。

根据计算结果可以看出，对于本例的24个样本数据，在4种备选模型中，指数模型的RMSEP值最小，为285.74；r值最高，达0.78。因此，指数模型更适合于反映NDVI与草地生物量之间的关系。知道了最适模型，就可以使用Excel的回归分析工具构建相应的回归模型，具体步骤可参见文献[10-13]，在此不再累述。

图3 留一法交叉验证的结果

3 结束语

草业信息模拟模型的建立是草业信息学实验课的重要内容。交叉验证是模型选择过程中的常用方法，而留一法交叉验证是交叉验证的一种方法。其优点是：(1)每一个分类器或模型都是用所有的样本来训练模型，最接近样本，评估所得的结果比较可靠; (2)实验没有随机因素，整个过程是可重复的。

在留一法交叉验证的实验教学中使用Excel软件，使原来相对抽象的理论转变为易于理解的计算过程，使理论教学同实践紧密结合，学生在掌握交叉验证理论的同时，熟练掌握具体计算的方法与步骤，并能对计算的结果有更进一步地认识，达到实验教学目的。同时，也巩固了学生对Excel相关函数的理解和使用方法，锻炼了学生分析和解决问题的能力，提升了学生对Excel软件的掌握。

[1]范永东. 模型选择中的交叉验证方法综述[D]. 太原：山西大学，2013.

[2]王怀亮. 交叉验证在数据建模模型选择中的应用[J]. 商业经济，2011(5):20-21.

[3]于惠，冯琦胜，陈思宇，等. 基于微波植被指数的甘南草地生物量动态监测[J]. 兰州大学学报：自然科学版，2011，47(4):69-74.

[4]冯琦胜，高新华，黄晓东，等. 2001-2010年青藏高原草地生长状况遥感动态监测[J]. 兰州大学学报:自然科学版，2011，47(4):75-81,90.

[5]常生华，侯扶江. 草业科学专业实验教学探索与思考[J].实验室研究与探索，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应用大全[M]. 北京：人民邮电出版社，2013.

[9]Excel Home. Excel 2010函数与公式实战技巧精粹[M]. 北京：人民邮电出版社，2014.

[10]薛香，梁云娟，郜庆炉. Excel在方差分析实验教学中的应用[J]. 实验技术与管理，2010，27(1):94-96.

[11]范平，崔党群，詹克慧，等. Excel软件在生物统计实验教学中的综合开发应用[J]. 实验技术与管理，2003，20(2):65-69.

[12]高晓林. Excel在实验数据回归分析中的应用[J]. 实验技术与管理，2005，22(5):28-31.

[13]唐正姣，张光旭，欧阳贻德，等. 用Excel 2000处理精馏实验数据[J]. 实验技术与管理，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

长江学者和创新团队发展计划基金资助项目(IRT13019)。

冯琦胜(1983-)，男，博士，实验师，主要从事草地遥感和地理信息系统等方面的科研和教学工作。

TP

Adoi:10.3969/j.issn.1672-4550.2015.02.015