Парная регрессия в Microsoft Excel с использованием P-сплайнов Текст научной статьи по специальности «Математика»

УДК 004.9

DOI: 10.18698/0236-3933-2017-5-114-131

ПАРНАЯ РЕГРЕССИЯ В MICROSOFT EXCEL С ИСПОЛЬЗОВАНИЕМ P-СПЛАЙНОВ

В.И. Аникин1 О.В. Аникина2 О.М. Гущина2

1 Поволжский государственный университет сервиса, Тольятти, Самарская обл., Российская Федерация

2 Тольяттинский государственный университет, Тольятти, Самарская обл., Российская Федерация

anikin_vi@mail.ru

blue-waterfall@yandex.ru

g_o_m@tltsu.ru

Аннотация

Теоретически показана возможность построения рекурсивных регрессионных моделей на базе усеченных полиномиальных P-сплайнов. По принципу «программирование без программирования», без написания программного кода VBA в Microsoft Excel построены общие и рекурсивные модели полупараметрической Р-сплайновой регрессии с использованием усеченных полиномов первого, второго и третьего порядков, отличающиеся впечатляюще простой табличной структурой. Предложенные методы создания табличных Р-сплайновых моделей существенно расширяют возможности Microsoft Excel как простого и эффективного средства сглаживания и регрессионного анализа случайных выборок данных

Ключевые слова

Microsoft Excel, табличная модель, регрессионный анализ, полупараметрическая регрессия, P-сплайн, B-сплайн

Поступила в редакцию 12.10.2016 © МГТУ им. Н.Э. Баумана, 2017

Введение. Одной из причин, побудившей авторов к исследованиям по теме работы, является наличие в Microsoft Excel известного и давнего дефекта, затрудняющего строгий регрессионный анализ данных в этой программе, а именно, встроенные средства Microsoft Excel позволяют проводить через случайные точки данных стандартные линии тренда (линейную, полиномиальную и др.), а также показывать на графике вид их уравнения, однако в ряде случаев коэффициенты уравнения выводятся Microsoft Excel неверно. Например, если через случайные точки yi, полученные по формуле ji = -^x^ + 2e^rndi -0,5), где rndi — случайное число с равномерным распределением вероятностей на отрезке [0, 1]; в = 0,2 — амплитуда аддитивной случайной добавки; i = 0,..., 100, x = [0, 10], провести полиномиальную линию тренда пятого порядка, то Microsoft Excel показывает для нее уравнение:

y = 0,0002x5 - 0,0063x4 + 0,0638x3 - 0,3134x2 + 0,8914x + 0,3102. (1)

Если по уравнению (1) на том же графике построить линию регрессии, то при x > 4 она сильно отличается от линии тренда, проведенной Microsoft Excel (рис. 1, а).

Рис. 1. Линия тренда с параметрами уравнения (1) до (а) и после (б) их коррекции

Кроме того, в реальности существуют случайные наборы данных, для которых невозможно подобрать подходящую стандартную линию тренда Microsoft Excel, а в более сложных случаях нельзя даже указать вид уравнения этой линии, например, для непериодических временных рядов.

В Интернете можно найти несколько бесплатных надстроек Microsoft Excel, например, SRS1 Cubic Spline for Excel V2.5 Released, Mathanxl.3.1, и коммерческая надстройка XLSTAT для проведения линии кубического сплайна через заданные точки (xi, y). Эти надстройки являются удобными инструментами интерполяции и экстраполяции данных, но не позволяют сглаживать стохастические данные, т. е. вычислять для них нелинейную линию регрессии (тренда). В работе [1] предложен метод сглаживания случайных данных путем их локального усреднения, реализованный табличными средствами Microsoft Excel, без программирования на VBA, однако приведенные примеры практического применения этого метода не доказывают его эффективность.

В настоящее время широкое применение нашла мощная техника сглаживания данных с помощью штрафованных P- и B-сплайнов [2, 3], для которых теоретически и экспериментально доказана несмещенность оценки линии математического ожидания случайных данных [4]. К сожалению, ни одной публикации по использованию штрафованных сплайнов в Microsoft Excel авторами обнаружено не было.

Цель настоящей работы — разработка и исследование общих и рекурсивных табличных моделей полупараметрической P-сплайновой регрессии в Microsoft Excel, позволяющих преодолеть затруднения, связанные с указанной ошибкой, а также существенно расширяющих возможности Microsoft Excel как простого и эффективного средства сглаживания и регрессионного анализа случайных данных.

Исследования, представленные в настоящей работе, показали возможность реализации этой техники чисто табличными средствами Microsoft Excel (про-

стой код УБЛ, использованный в табличных моделях, предназначен исключительно для автоматизации работы пользователя и без него можно обойтись). Созданные табличные модели Р-сплайновой регрессии на основе усеченных полиномов первого, второго и третьего порядков, особенно рекурсивные модели, отличаются впечатляющей простотой и высоким качеством сглаживания.

Р-сплайны и метод наименьших квадратов. В литературе различают два класса нелинейных регрессий:

1) регрессии, нелинейные относительно включенных в анализ объясняющих переменных, но линейные по оцениваемым параметрам;

2) регрессии, нелинейные по оцениваемым параметрам.

Здесь рассмотрены нелинейные регрессии первого класса, что позволяет при нахождении параметров уравнения регрессии эффективно использовать метод наименьших квадратов (МНК) [5, 6]. Известно, что этот метод нашел широкое практическое применение в самых различных задачах оптимизации ввиду уникальных статистических свойств: несмещенности, состоятельности и эффективности оценки неизвестных линейных параметров.

Идея Р-сплайнов довольно простая. Гладкая неизвестная функция регрессии аппроксимируется функциональной параметрической формой, образованной набором базисных функций большой размерности. Размерность базиса выбирается так, чтобы достигалась достаточная гибкость аппроксимации, тогда как коэффициенты базисных функций штрафуются, обеспечивая желаемую гладкость результирующего функционала.

Пусть имеется п+1 пар точек (х;, у), удовлетворяющих модели у; = у(х;)+ 8;, где I = 0,..., п; у(х) — неизвестная функция регрессии; 8; — независимые случайные добавки с нулевым средним значением и постоянной дисперсией с2. Моделируем функцию у(х) Р-сплайном степени р с усеченным полиномиальным базисом:

ßpk — вес кусочных функций (x - <^k)p+; ^k — фиксированные координаты узлов P-сплайна.

Как правило, степень полинома, число и положение узлов P-сплайна мало влияют на точность оценки функции регрессии, и можно использовать от 35 до 40 равномерно отстоящих друг от друга узлов для большинства случайных выборок данных и всех гладких функций, не содержащих большого числа осцил-ляций [4].

Если по каким-либо причинам этот выбор не устраивает, то, определяя новые узлы или сдвигая существующие, P-сплайн можно легко модифицировать,

K

у (x) = ßo +ßiX + ... + ßpXp + Yßpk (xУ+ ,

где

при x >^k; при x <^k;

чтобы он лучше подходил под искомые данные. Нетрудно понять, что варьирование числа узлов Р-сплайна равносильно добавлению к регрессионной модели большего числа предсказывающих переменных. К сожалению, можно добавить слишком много узлов и «переобучить» модель, тем самым подчеркивая в данных случайные флуктуации, которые несущественны.

Для того чтобы избежать «переобучения», на величины весов р^, р^, ..., $рк базисных функций накладываются ограничения (штрафы). Существует несколько критериев штрафования, из них простейшим и одним из лучших является критерий

£ ррс < с,

г=1

где С — некоторая константа.

Представленные соображения можно сформулировать математически в векторно-матричном виде, для чего определим вектор ут = [уо, ..., у„], а также матрицы X и D вида

X =

1 x0 ... x0 ... (x0-Ç1 )) ... (x0-Çk)

\p+

1 xn . •• xn . •• (xn ) . •• (xn ÇK )

\P+

D

0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1

.. 0

.. 0

.. 0

.. 0

0 0 0 0 ... 1

Тогда для заданного сглаживающего параметра X оценка вектора неизвестных параметров р регрессионной модели, где рт = [Ро, Р1, Рр1, ..., Ррк], может быть получена путем минимизации целевой функции

Р = ||у - 12 + ХртDр. (2)

Дифференцируя выражение (2) по р и приравнивая производную нулю, получаем

АЛр* - Xт (у - Xр* ) = 0,

откуда

Р* =(Xт X + A,D ) Xт y ; y = X (Xт X + \D ) Xт y.

(3)

Недостатком этого приближения является то, что сглаживание контролируется одним общим, задаваемым пользователем параметром Л, что может вызвать некоторые затруднения, когда данные содержат как быстро, так и медленно осциллирующие участки.

Эффективное решение уравнения (3) табличными средствами невозможно, так как для этого к матрице Х(ХтХ+ХО)-1Хту необходимо применить сингулярное разложение [7], встроенная поддержка которого в Microsoft Excel отсутствует. Отметим, что работа [7] содержит работоспособный программный код R алгоритма решения уравнения (3), который можно использовать для сравнения полученных здесь результатов с результатами выполнения этого кода.

Вместо векторно-матричного решения уравнения (3) для нахождения функции регрессии у(х) авторами настоящей работы в Microsoft Excel решена оптимизационная задача:

минимизировать целевую функцию

F = Ê (у - у ) + х£ß^, (4)

i=0 i=1

где у — случайная выборка данных; у — P-сплайн с усеченным полиномиальным базисом р-го порядка.

При решении этой задачи возникает проблема подбора наилучшего значения сглаживающего параметра X. Необходимость оптимизации обусловлена тем, что при X = 0 сглаживание отсутствует (переобученная модель), а при больших значениях X оно слишком велико (линия регрессии игнорирует быстрые средние локальные изменения в данных).

Существует несколько автоматических методов решения этой проблемы.

1. Оптимизация параметра X по одному из критериев [7]:

- перекрестной проверки (cross-validation) CV(X);

- обобщенной перекрестной проверки GCV(X);

- информационному критерию Акаике (Akaike) AIC(X);

- скорректированному критерию Акаике AICc(X).

2. Оптимизация параметра X по критерию максимального правдоподобия: Xbest = с / Gß [4], где с — несмещенное стандартное отклонение вектора остатков у - у; Gß — стандартное отклонение значений параметров ßp = [ßp1, — , ß рк ] полиномиального P-сплайна.

Одним из недостатков табличной реализации в Microsoft Excel полиномиальных P-сплайнов является громоздкость получаемой электронной таблицы, обусловленная необходимостью расчета в отдельных ячейках Ыобщ = (K + р + 1)(n + 1) значений базисных функций сплайна.

Однако для полиномиальных P-сплайнов существует возможность строить рекурсивные табличные модели, объем эквивалентных вычислений в которых сокращается до пересчета Nек = (р + 1)(n + 1) ячеек, т. е. более чем на порядок

при больших значениях К. Так, при п = 100, К = 40, р = 1 для общей регрессионной модели имеем Ыобщ ~ 4200, а для рекурсивной — ЛТрек ~ 200. Действительно, вводя обозначения

y (p ) = y(p ) (ß0>...)ß p, x ) = ßo +ß!X + ... + ß pxp + X ß pk (x 4k)

k=1

\Р+

и дважды дифференцируя это выражение по х, получаем

dy(p) dx

K-1

= ßi + 2ß2x +... + pßpxp-1 + p ^ßpk (x)p-1)+ ;

k=1

dy( p ) = dx

ßl +ß2x +... + ßpxp-1 + Zßpk (x -Çk )(p-l) k=1

p-1

-Z(p - m )ß"

„•m-1.

m=1

dy(p\ = p;P(p-1)(ßi,...,ßp,x)-X (p-m)ßmxm-1;

dx

(5)

m=1

d2y(p) dx2

d2y(p ) =

K-2

= 2ß2 +...+p(p-1)ßpxp-2 + p(p-1) Xßpk (x-Çk)(p-2)+ ;

k=1

p-2

, 2 =р(р- 1):^(р"2)(р2,...,Рр,х)-Е(р-ш)(р-ш- 1)вш

ш—1

В конечных разностях равенства (5) и (6) имеют вид

у (р)- у (р) р-1

у' У;-1 — ру(р-1)(Ръ..., Рр, х)- 2 (р-ш)Ртхт-1;

ш—1

m-2

(6)

(p У (p )+ У (p )

y r - 2y i-1+y:

i-2

p-2

ft2

= p (p-1 )y(p-2 )(ß2,..., ßp, x )-Z(p - m )(p - m - 1)ßmx

m-2

откуда следуют рекурсивные формулы

y (p )=y (4+

m=1

p-1

p y(p-1)(ßb..., ßp, x, )-Z(p - m )ßkxf-1

m=1

h ;

(7)

y(p )= 2y(-p1) - y(-2 +

p-2

p( p - 1)y( p-2 ) ( ß2,..., ßp, x )- X ( p - m )( p - m - 1)ßmx

m -2

m =1

h2, (8)

где й — шаг дискретизации по оси х.

Следовательно, если известны значения у(р-1)(рь..., Рр, х;) полиномиального Р-сплайна порядка р-1, построенного для значений параметра Р1, ..., Рр, или значения у(р-2)(р2,..., Рр, х;) полиномиального Р-сплайна порядка р-2, постро-

енного для значений параметров Р2,..., Рp, то по формулам (7) и (8) легко найти значения P-сплайна y(p) (Ро,..., Рp, xi) порядка p при условии, что построена рекурсивная модель полиномиального P-сплайна первого порядка.

В частности, для полиномиальных P-сплайнов второго и третьего порядков имеем

у(2)= у(_1 + [2 y(1) (Pi, Р2, ^ )_№; (9)

y(3)= У(_1+[3 y(2)(Pi,..., Рз, x,)2Pi-Р2x,]h; (10)

y(3)= 2y(_)_y(_2+[6 ^(1)(P2, Рз, X)_2P2]h2. (11)

К достоинствам общих (нерекурсивных) P-сплайновых табличных моделей можно отнести их универсальность, возможность использования любых базисных функций, в частности, B-сплайнов [3], к их недостаткам — большой объем вычислений в ходе оптимизации и громоздкость табличной модели.

Достоинства рекурсивных P-сплайновых табличных моделей — существенно меньший объем вычислений и очень простая структура табличной модели, недостаток — в качестве базиса P-сплайнов можно использовать только усеченные полиномиальные функции.

Постановка модельных экспериментов. Модельные эксперименты включали в себя создание по формулам (8), (9), (11) общих и рекурсивных регрессионных P-сплайновых моделей и их сравнение в Microsoft Excel.

Случайные выборки входных пар данных (xi, y), применяемые в модельных экспериментах, генерировались по формулам yi = y(xi) = M(xi) + sNi(0, 1), где i = 0,..., n; M(x) — заданная детерминированная нелинейная функция, равная математическому ожиданию случайной функции y(x); Ni(0, 1) — стандартное нормальное распределение; s — амплитуда случайной аддитивной добавки.

В экспериментах использовано несколько функций M(x) с равномерным шагом дискретизации по оси x и заданными параметрами ас

M(Xi) = tfxi, xi = [0, 10], i = 0,...,100;

M(xi) = a0 + a1xi + a2sin (xi), xi = [0, 10], i = 0,...,100.

Разработка табличных моделей парной P-сплайновой регрессии выполнена по предложенной технологии алгоритмического табличного моделирования (АТМ) [8, 9]. Достоинством этой технологии является то, что искомую табличную модель создают не методом проб и ошибок, а основываясь на алгоритме решения задачи, что позволяет получать логически и структурно безупречные электронные таблицы.

Общий и рекурсивный алгоритмы решения задачи парной регрессии с использованием P-сплайна первогого порядка приведены ниже:

' Входы программы

' x0 - координата x первого элемента случайной выборки данных y ' n - число интервалов дискретизации по оси x ' h - равномерный шаг дискретизации по оси x ' y0...yn - элементы случайной выборки данных y

' nk - число элементов данных между соседними узлами P-сплайна ' £0...£k - ' координаты узлов P-сплайна на оси x ' в0, вх, вр1... epk - начальные значения коэффициентов базисных функций

' А - параметр сглаживания ' Параметры, переменные и код программы

k=div(n/nk) ' всего узлов сплайна, кроме левого начального ' === Цикл вычисления значений P-сплайна === ' i,j - счетчики циклов

for i=0 to n ' цикл для общей P-сплайновой табличной модели

xi=ifelse(i>0;xi-1+h;x0)

' цикл вычисления значений базисных функций fj(xi)

fi(xi)=P0

f2(xi)=Pi*xi for j=3 to k+2

fj (xi)=ifelse(xi-^j>0; ep(j-2)*(xi-^j);0) next

k+2

yi = ^fj (xi) ' i-е значение функции регрессии j=1

dyi=yi-yi ' i-й остаток

next

Fs = ^dy2 ' сумма квадратов остатков

i=0 k

i\ =XРр ' сумма квадратов сплайн-коэффициентов i=1

F=Fs+AFA ' значение целевой функции ' === Статистика остатков === R2=cor2(y,y) ' коэффициент детерминации

dy =mean(dy) ' среднее значение остатков s = -¡Jvar (dy )n / (n-1) ' стандартное отклонение

se = Vvar (Pp )

DW=2(1-cor(y, ут)) ' коэффициент автокорреляции Дарбина — Уотсона

Цикл for i=0 to n для рекурсивной P-сплайновой табличной модели:

for i=0 to n

xi=ifelse(i>0;xi-1+h;x0)

kki=ifelse(i=0;ei;ifelse(xi mod h=0;Ppi;0)) ' текущий коэффициент наклона ломаной линии P-сплайна kcuri=ifelse(i<2;k0;kcuri-1+ifelse(xi mod h=0;kki-1;0)) yi=ifelse(i=0; в0; yi_1+kcuri*hi) ' у-функция регрессии dyi=yi-yi ' i-й остаток

next

Псевдокод рекурсивного алгоритма отличается от псевдокода общего алгоритма лишь циклом вычислений по i, а структура рекурсивной табличной модели отличается от структуры простой модели парной параметрической регрессии двумя дополнительными вектор-столбцами kk и кшг высотой n+1 ячеек.

На основе приведенных алгоритмов были созданы и исследованы регрессионные табличные модели для входных модельных данных yi = a0 + a1 xi + a2 sin (xi) + + Ni (0,1) (табл. 1).

Таблица 1

Исследованные регрессионные P-сплайновые табличные модели

Тип модели Уравнение регрессии

Р-сплайновая первого порядка: общая рекурсивная 1 K y i — Zßmxf +2ßlk (x; -Çk )+ m—G k—1 y i — yi-l + ß;h

Р-сплайновая второго порядка: общая рекурсивная y ; — ¿ßmxr +: ß2k (x, -Çk )2+ m—G k—1 y (2)— y (-)+[2 y «(ßl, ß2, x; )-ßl У

Р-сплайновая третьего порядка: общая рекурсивная 3 K 3 y ; — 2ßmx,m +2ß2k (x, -Çk ) m—G k—1 y(3) — 2y-y(-2 + [6 y(l)(p2,p3,x,)-2ß2У

Общая В-сплайновая lK y ; — sßmxr+:ßlk5 (x,-Çk ) m—G k—1

Оптимизация параметров этих уравнений выполнена с помощью инструмента Microsoft Excel «Поиск решения» методом обобщенного приведенного градиента (ОПГ) при диалоговых настройках, приведенных в табл. 2. Таблица 2 Настройки диалогового окна Microsoft Excel «Поиск решения»

Параметр P-сплайновая регрессия

Целевая функция Рцел — ¿ (y, - y ; )2 + *£ ßPk i—g k—1

Изменяемые переменные ßт = (ßo,..., ßp, ßpl.....ßpK)

Ограничения |ßi|, |ßpi| á 50

Начальные значения изменяемых переменных для Р-сплайновой регрессии равны Ро = уср, Рь..., Рр = 0, Рр1,..., Ррк = 0.

Качество регрессии было оценено путем статистического анализа остатков dyt = y - yt, i = 0,..., n [10]. Для каждой табличной регрессионной модели вычис-

_ 1 n

лено среднее значение y =-^y t, коэффициент детерминации R2 = cor2(y, у),

n +1 i=0

несмещенное стандартное отклонение остатков 5 = ^var (dy ) П ^, стандартное

отклонение sp коэффициентов Рр и наилучшее значение параметра Xbest = 5 / sp (для P-сплайнов), коэффициент автокорреляции Дарбина — Уотсона DW = = 2[1-cor (dy(x), dy(x+h)].

Наилучшее значение параметра Xbest в P-сплайновых моделях регрессии оценивалось не только по формуле Xbest = s / sp, но и визуально, следуя принципу: в нескольких последовательных итерациях подбирается такое наименьшее значение X, при котором график линии регрессии становится гладким, и при этом обеспечивается равномерность и симметричность графика разброса остатков относительно оси x.

Результаты моделирования. Прежде чем представить результаты моделирования, поясним особенности построения регрессионных табличных моделей в Microsoft Excel. Для примера рассмотрим простую, но важную рекурсивную P-сплайновую модель первого порядка, так как без нее, как отмечалось, невозможно создание рекурсивных P-сплайновых моделей более высоких порядков. Структура этой модели показана на рис. 2. В соответствии с технологией АТМ константы и параметры модели сохраняются в изолированных ячейках, а изменяемые переменные модели — в вектор-столбцах. Интервал ячеек $U$3:$DR$5,

содержащий текущие и начальные значения параметров рт = (po, p1, pp1,____ ррюо)

и положения узлов = (^1,_, ^юо) P-сплайна максимального размера K = 100, не показан.

1 1 2 3 A 5 6 7 9 10 и 12 14 15 16

2 Л 5 6 s n 1» rik 4 Vcp 20,939 ow 2,177

ai 3 £ 1,5 К 25 R3 0,976 1.69

az 6 h 0,100 s 1,481 4-du 2,31

r> DflrOHKd Sp 1,615 1.65

A 0,9 67,209 Fi/л 217,1Л 277,62 S/S(l 0,917 1,69 * DW < 2,31

7 3 1 h h ¥ У Y-V M(v)

X Virtd

9 0 0 Sr7656 3,766 0,0 4,1« 3,714 4,922 ■•■1,21 5,000

и 0 ^766 Ù.ï 1г2йО -IM

LI 0 8,766 0,2 9,266 Л,976 6,675 -1,70 6,792

и С ÎU66 0.} Î.5ÎS e>9iî 7rHl IM 7.-67Э

13 1 -1,23821 3,766 0,4 9,343 9,784 6,423 1,36 6,537

14 С 7,SÎ7 0.5 Й.0М 9.033 -<Ш $.Î77

15 0 7,527 0,6 8,340 10,614 9,933 0,6В 10,188

ifi а 7,537 0.7 It« 12 11.S14 lDr6S6

Рис. 2. Структура рекурсивной P-сплайновой модели первого порядка

Важная особенность электронных таблиц — формулы и результаты расчета по ним сохраняются в одних и тех же ячейках. Это обеспечивает Microsoft Excel принципиально новые возможности визуализации данных по сравнению с другими средами моделирования, даже такими мощными, как MATLAB и R. В частности, с помощью заливки ячеек различными цветами и мощного инструмента условного форматирования: голубым цветом выделены входные ячейки модели, оранжевым — выходные ячейки, важные для пользователя, белым — ячейки с промежуточными формулами и значениями, которые изменять нельзя, зеленым — ячейки с какими-либо особенностями в формулах, желтым — ячейки с комментариями и полезными подсказками. Кроме того, во всех табличных моделях применен стиль ссылок R1C1, естественный для технологии алгоритмического табличного моделирования в Microsoft Excel.

В табличной модели (см. рис. 2) используют следующие параметры, переменные и формулы.

1. Входы модели:

R2C3:R5C3 — параметры a функции M(xi) = ao + aiXi + a2sin(xi); R2C6 — число интервалов n по координате x; n+1 — число случайных точек (xi, y); R3C6 — амплитуда случайной добавки s; R3C6 — значение равномерного шага h по координате x; R2C9 — число точек (xi, yi) между соседними узлами P-сплайна; R6C3 — значение параметра сглаживания Л; R9C2 — начальное значение i счетчика точек (xi, y); R9C6 — значение xo; R9C8:R109C8 — входной вектор-столбец данных y = (yo, y1, ..., yn).

2. Основные формулы модели: R3C9 := ЦЕЛОЕ(R2C6/R2C9);

R4C6 := СУММКВ(СМЕЩ(base;0;2;1;R3C9)); R6C9 := CyMM^CMEffiXtblBase^Än+U)); R6C10 := R6C9+R6C3*R6C4;

R9C3:R109C3 := ЕСЛИ(ОСТАТ(RC2;R2C9)=0;ЦЕЛОЕ(RC2/R2C9);,,,,); R9C4 := R3C22;

R10C4:R109C4 := ЕСЛИ(ОСТАТ(RC2;R2C9)=0;СMЕЩ(base;0;ЦЕЛОЕ(RC2/R2C9)+1);0) R9C5:R10C5 := R9C4;

R11C5:R109C5 := R[-1]C5+ЕСЛИ(ОСТАТ(RC2;R2C9)=1;R[-1]C4;0); R10C6:R109C6 := R[-1]C+R4C6;

R9C7:R109C7 := RC11+R3C6*НОРMОБР(СЛЧИС();0;1); R9C9 := base;

R10C9 := base+R3C22*RC6; R11C9:R109C9 := R[-1]C9+RC5*R4C6; R9C10:R109C10 := RC8-RC9;

R9C11:R109C11 := R2C3+R3C3*RC6+R4C3*SIN(RC6).

3. Выходы модели:

R6C10 — значение целевой функции; R2C13 — среднее значение y;; R3C13 — коэффициент детерминации R2; R4C13 — выборочное несмещенное стандартное отклонение остатков s; R5C13 — выборочное стандартное отклонение sp параметров ßp R6C13 — оценка наилучшего значения параметра сглаживания А; R2C16 — коэффициент Дарбина — Уотсона.

4. Именованные ячейки модели:

base = R3C21, x0 = R9C6, n = R2C6, h = R4C6, K = R3C9, target = R6C10, tblBase = = R9C2.

5. Начальные значения параметров Р перед поиском оптимального решения: рнач = (R2C13, 0, ..., 0).

6. Настройки диалогового окна «Параметры поиска решения»:

target — целевая ячейка; R3C21:R3C47 — изменяемые ячейки; R3C21:R3C47 < 50, R3C21:R3C47 > -50 — ограничения; поиск решения задачи выполняется методом ОПГ.

Отметим, что табличные модели парной параметрической регрессии очень похожи на описанную модель и отличаются от нее параметрами, отсутствием вектор-столбцов ik, k, ктек и видом формул в вектор-столбцах у и M(x).

Работа пользователя по оптимизации созданной P-сплайновой табличной модели сводится к следующим действиям:

1) задать требуемое число точек между узлами P-сплайна в ячейке R2C9;

2) скопировать вектор-строку начальных значений Рнач из ячеек R5C21:R5C122 в ячейки R3C21:R3C122 вектор-строки Р;

3) выполнить команду меню «Данные ^ Поиск решения», убедиться в правильности настроек диалогового окна «Параметры поиска решения», при необходимости исправить их и щелкнуть кнопку «Найти решение».

Следует отметить, что ввиду наличия случайной добавки значения ячеек вектор-столбца yn изменяются при каждом пересчете рабочего листа с табличной моделью. Поэтому значения (не формулы) ячеек из столбца yrnd необходимо один раз скопировать в ячейки вектор-столбца y и затем работать с этой фиксированной выборкой данных. То же самое требуется выполнить при изменении значения параметров ai во входных ячейках R2C3:R4C3, параметра £ в ячейке R3C6 и вида функции M(x) в ячейках R9C11:R109C11.

Изменение значений входных параметров n и h в ячейках R2C6, R4C6 требует следующей более серьезной модификации модели:

1) заменить значения n или h в ячейках R2C6, R4C6 требуемыми;

2) удалить лишние (при уменьшении n) или добавить с помощью маркера заполнения новые (при увеличении n) строки во все вектор-столбцы табличной модели;

3) скопировать значения ячеек из измененного вектор-столбца yn в ячейки вектор-столбца y;

4) скорректировать границы диапазонов данных, выводимых на графики, в диалоговом окне «Выбор источника данных»;

5) скорректировать диапазоны изменяемых ячеек и ограничений в диалоговом окне «Параметры поиска решения».

Для автоматизации работы пользователя в коде VBA были разработаны обработчик события Worksheet_Change(), выполняющий перечисленные действия (кроме действия 5) при любом изменении содержимого ячеек х0, n или h, а так-

же ассоциированный с внедренной кнопкой «Подгонка» макрос Solve(), который выполняет действие 5, затем копирует в вектор-строку р начальные значения Рнач и запускает поиск оптимального решения.

Используя специально созданную табличную модель парной параметрической регрессии для выборки данных у,- = tfxi + Ni (0,1), уточняем коэффициенты полинома пятой степени (см. ниже), определяющего стандартную линию тренда Microsoft Excel, и построим график линии регрессии (рис. 1, б) по этим коэффициентам. Полное совпадение кривых, приведенных на рис. 1, б, свидетельствует о том, что для нахождения стандарных линий тренда Microsoft Excel применяет МНК.

Корректировка параметров уравнения линии тренда в Microsoft Excel

05 04 03 02 01 00

Параметры

Microsoft Excel .......................0,0002 -0,0063 0,0638 -0,3134 0,8914 0,3102

Оптимальные

параметры...............................0,0002330 -0,006230 0,06307 -0,30971 0,88398 0,3137

Результаты P-сплайновой регрессии первого порядка для случайной выборки данных y- = Я0 + a1Xi + a2sin(xi) + sNi(0, 1), где a = [5, 3, 6]; в = 1,5; x = [0, 10]; h = 0,1, и трех значений параметра сглаживания X (штриховой линией показана функция M(x)) приведены на рис. 3.

Для наилучшего значения параметра X = 0,9 (рис. 3, в-д) коэффициент детерминации равен R2 = 0,970, т. е. кривая регрессии объясняет 97 % вариаций входных случайных данных. Вид функции остатков показывает, что их дисперсия постоянна, а автокорреляция между соседними значениями yi отсутствует, что подтверждается тестом Дарбина — Уотсона: DW = 2,17.

Эксперименты по нахождению оптимального значения параметра X при изменении случайной добавки в показали, что оценка Xbest ~ s / sp является хорошим приближением и может использоваться вместо сложного алгоритма поиска наилучшего значения Xbest, реализованного в программе R [7].

Стандартные отклонения остатков и коэффициентов в (и=100)

в ........................0,5 1,0 1,5 2,0 2,5 3,0 4,0

s ........................0,439 0,945 1,481 1,805 2,802 2,855 3,829

sp ....................... 1,642 1,517 1,615 1,755 1,600 1,564 1,464

С увеличением добавки в стандартные отклонения остатков, как и следует ожидать, линейно возрастают, а значения sp остаются постоянными. Последнее объясняется тем, что физически параметр sp представляет собой оценку среднего значения приращений ктек тангенсов углов наклона функции M(x) к оси x, которая для фиксированной выборки данных (x-, y) должна оставаться приблизительно постоянной. С увеличением амплитуды случайных добавок в наилучшее значение X также возрастает почти линейно.

е ж

Рис. 3. Функции регрессии (а, в, е) и остатков (б, г, ж) при X = 0 (а, б), 0,9 (в-д), 10 (е, ж), функция регрессии, полученная в программе Я при ХА1сс = 0,26 (д)

Функции, приведенные на рис. 4, показывают возможность сглаживания в Microsoft Excel случайных выборок данных не только с помощью Р-сплайнов, но и B-сплайнов.

Рис. 4. Базисные функции (а) и функции регрессии (б) при сглаживании случайных данных треугольными B-сплайнами (X = 3, е = 1,5, R2 = 0,989, s = 1,43)

Выводы. Аналитически доказана и экспериментально подтверждена возможность построения в Microsoft Excel общих, а также предложенных авторами рекурсивных регрессионных моделей на базе полиномиальных P-сплайнов первого, второго и третьего порядков.

Полученные результаты открывают перспективу широкого и эффективного применения электронных таблиц Microsoft Excel для сглаживания случайных данных и решения регрессионных задач, интерполяции и прогноза в экономике, социологии, биологии, медицине, экологии, физике и других областях.

ЛИТЕРАТУРА

1. Klasson K.T. Construction of spline functions in spreadsheets to smooth experimental data // Advances in Engineering Software. 2008. Vol. 39. Iss. 5. P. 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. P. 149-186.

3. Ruppert D, Wand M.P., Carroll R.J. Semiparametric regression during 2003-2007 // Electronic Journal of Statistics. 2009. No. 3. P. 1193-1256. DOI: 10.1214/09-EJS525

URL: https://www.ncbi.nlm.nih.gov/ pmc/articles/PMC2841361

4. Ruppert D., Wand M.P., Carroll R.J. Semiparametric regression. New York: Cambridge Univ. Press, 2003. 404 p.

5. JangJ.S.R., Sun C.T., Mizutani E. Neuro-fuzzy and soft computing: А 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. P. 1041-1045.

7. Griggs W. Penalized spline regression and its applications. 2013. 51 p.

URL: https://www.whitman.edu/Documents/Academics/Mathematics/Griggs.pdf (дата обращения: 16.09.2016).

8. Аникин В.И., Аникина О.В. Эффективная техника создания табличных моделей в Microsoft Excel // Информационные технологии. 2008. № 10. С. 74-77.

9. Аникин В.И., Аникина О.В., Зибров П.Ф. Информационные технологии имитационного моделирования // Формирование современного информационного общества — проблемы, перспективы, инновационные подходы. Материалы международного форума. СПб.: ГОУ ВПО СПбГУАП, 2011. С. 181-189.

10. Kutner M.H., Nachtsheim C.J., Neter J., Li W. Applied linear statistical models. New York: McGraw-Hill, 2004. 1396 p.

Аникин Валерий Иванович — д-р техн. наук, профессор, профессор кафедры «Информационный и электронный сервис» Поволжского государственного университета сервиса (Российская Федерация, 445017, Самарская обл., Тольятти, ул. Гагарина, д. 4).

Аникина Оксана Владимировна — канд. техн. наук, доцент кафедры «Прикладная математика и информатика» Тольяттинского государственного университета (Российская Федерация, 445020, Самарская обл., Тольятти, ул. Белорусская, д. 14).

Гущина Оксана Михайловна — канд. пед. наук, доцент, доцент кафедры «Прикладная математика и информатика» Тольяттинского государственного университета (Российская Федерация, 445020, Самарская обл., Тольятти, ул. Белорусская, д. 14).

Просьба ссылаться на эту статью следующим образом:

Аникин В.И., Аникина О.В., Гущина О.М. Парная регрессия в Microsoft Excel с использованием P-сплайнов // Вестник МГТУ им. Н.Э. Баумана. Сер. Приборостроение. 2017. № 5. C. 114-131. DOI: 10.18698/0236-3933-2017-5-114-131

PAIR REGRESSION IN MICROSOFT EXCEL BY USING OF P-SPLINES

V.I. Anikin1 O.V. Anikina2 O.M. Gushchina2

anikin_vi@mail.ru

blue-waterfall@yandex.ru

g_o_m@tltsu.ru

1 Volga Region State University of Servise, Togliatti, Samara Region, Russian Federation

2 Togliatti State University, Togliatti, Samara Region, Russian Federation

Abstract

The study theoretically shows the possibility of building recursive P-spline regression models based on the truncated polynomial P-splines. Without writing VBA code according to the principle of "programming without programming", we created general and recursive models of semi-parametric regression in Excel using polynomial P-splines of the first, second and third orders, having a dramatic simplicity in the spreadsheet structure. We optimized the parameters of the models by the generalized reduced gradient method using the Excel Solver tool. The simulation experiments confirmed high quality of the regression and computational efficiency of the spreadsheet models. The regression quality was evaluated by statistical analysis of the residuals. For each spreadsheet regression

Keywords

Microsoft Excel, spreadsheet model, regression analysis, semiparametric regression, P-spline, B-spline

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 particular, B-splines, while their disadvantages are a large amount of computations in the optimization process and a bulky spreadsheet model. The recursive P-spline spreadsheet models have such advantages as much smaller amount of computation required in the optimization process 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 methods for creating the spreadsheet P-spline models significantly expand capabilities of Excel as a simple and effective tool for smoothing and regression analysis of random data Received 12.10.2016 samples © 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: https://www.ncbi.nlm.nih.gov/pmc/articles/PMC2841361

[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: https://www.whitman.edu/Documents/Academics/Mathematics/Griggs.pdf (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.).

[10] Kutner M.H., Nachtsheim C.J., Neter J., Li W. Applied linear statistical models. New York, McGraw-Hill, 2004. 1396 p.

Anikin V.I. — Dr. Sc. (Eng.), Professor of Information and Electronic Service Department, Volga Region State University of Service (Gagarina ul. 4, Togliatti, Samara Region, 445017 Russian Federation).

Anikina O.V. — Cand. Sc. (Eng.), Assoc. Professor of Applied Mathematics and Computer Science Department, Togliatti State University (Belorusskaya ul. 14, Togliatti, Samara Region, 445020 Russian Federation).

Gushchina O.M. — Cand. Sc. (Ped.), Assoc. Professor of Applied Mathematics and Computer Science Department, Togliatti State University (Belorusskaya ul. 14, Togliatti, Samara Region, 445020 Russian Federation).

Please cite this article in English as:

Anikin V.I., Anikina O.V., Gushchina O.M. Pair Regression in Microsoft Excel by using of P-Splines. Vestn. Mosk. Gos. Tekh. Univ. im. N.E. Baumana, Priborostr. [Herald of the Bauman Moscow State Tech. Univ., Instrum. Eng.], 2017, no. 5, pp. 114-131. DOI: 10.18698/0236-3933-2017-5-114-131