Парная регрессия в Microsoft Excel с использованием P-сплайнов

17 / 18

В.И. Аникин, О.В. Аникина, О.М. Гущина
130 ISSN 0236-3933. Вестник МГТУ им. Н.Э. Баумана. Сер. Приборостроение. 2017. № 5
model we calculated the coefficient of determination, the
unbiased standard deviation of the residuals, the standard
deviation of the parameters of the basis functions, the best
value of smoothing parameter and the Durbin — Watson’s
autocorrelation coefficient. The advantages of the general
(non-recursive) P-spline spreadsheet models are their
versatility, the ability to use all the basic functions, in par-
ticular, B-splines, while their disadvantages are a large
amount of computations in the optimization process and a
bulky spreadsheet model. The recursive P-spline spread-
sheet models have such advantages as much smaller
amount of computation required in the optimization pro-
cess and a very simple structure of the spreadsheet model,
while the disadvantage is that only polynomial functions
can be used as the basis of P-splines. The proposed me-
thods for creating the spreadsheet P-spline models signifi-
cantly expand capabilities of Excel as a simple and effective
tool for smoothing and regression analysis of random data
samples
Received 12.10.2016
© BMSTU, 2017
REFERENCES
[1] Klasson K.T. Construction of spline functions in spreadsheets to smooth experimental data.
Advances in Engineering Software, 2008, vol. 39, iss. 5, pp. 422–429.
DOI: 10.1016/j.advengsoft.2007.03.006
[2] Eilers P.H.C., Marx B.D., Durban M. Twenty years of P-splines. SORT, 2015, vol. 39, no. 2,
pp. 149–186.
[3] Ruppert D., Wand M.P., Carroll R.J. Semiparametric regression during 2003–2007. Electronic
Journal of Statistics, 2009, no. 3, pp. 1193–1256. DOI: 10.1214/09-EJS525
Available at:
[4] Ruppert D., Wand M.P., Carroll R.J. Semiparametric regression. New York, Cambridge Univ.
Press, 2003. 404 p.
[5] Jang J.S.R., Sun C.T., Mizutani E. Neuro-fuzzy and soft computing: A computational approach
to learning and machine intelligence. New York, Prentice-Hall, 1997. 614 p.
[6] Van de Geer S.A. Least squares estimation. Encyclopedia of Statistics in Behavioral Science.
Vol. 2. Wiley, 2005, pp. 1041–1045.
[7] Griggs W. Penalized spline regression and its applications. 2013. 51 p.
Available at: (accessed:
16.09.2016).
[8] Anikin V.I., Anikina O.V. Effective technology of making table models in Excel. Infor-
matsionnye tekhnologii [Information Technologies], 2008, no. 10, pp. 74–77 (in Russ.).
[9] Anikin V.I., Anikina O.V., Zibrov P.F. Informatsionnye tekhnologii imitatsionnogo modeliro-
vaniya [Informational technologies of imitational simulation]. Formirovanie sovremennogo infor-
matsionnogo obshchestva — problemy, perspektivy, innovatsionnye podkhody. Materialy mezhdu-
narodnogo foruma [Forming modern informational society — problems, prospects, innovative
approaches. Proc. int. forum]. Saint-Petersburg, GOU VPO SPbGUAP Publ., 2011, pp. 181–189
(in Russ.).