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

1 / 18

114 ISSN 0236-3933. Вестник МГТУ им. Н.Э. Баумана. Сер. Приборостроение. 2017. № 5
УДК 004.9 DOI: 10.18698/0236-3933-2017-5-114-131
ПАРНАЯ РЕГРЕССИЯ В MICROSOFT EXCEL С ИСПОЛЬЗОВАНИЕМ
P-СПЛАЙНОВ
В.И. Аникин1
О.В. Аникина 2
О.М. Гущина 2
1 Поволжский государственный университет сервиса,
Тольятти, Самарская обл., Российская Федерация
2 Тольяттинский государственный университет,
Тольятти, Самарская обл., Российская Федерация
Аннотация Ключевые слова
Теоретически показана возможность построения рекурсив-
ных регрессионных моделей на базе усеченных полиномиаль-
ных P-сплайнов. По принципу «программирование без про-
граммирования», без написания программного кода VBA в
Microsoft Excel построены общие и рекурсивные модели полу-
параметрической P-сплайновой регрессии с использованием
усеченных полиномов первого, второго и третьего порядков,
отличающиеся впечатляюще простой табличной структурой.
Предложенные методы создания табличных P-сплайновых
моделей существенно расширяют возможности Microsoft Excel
как простого и эффективного средства сглаживания и регрес-
сионного анализа случайных выборок данных
Microsoft Excel, табличная
модель, регрессионный анализ,
полупараметрическая регрессия,
P-сплайн, B-сплайн
Поступила в редакцию 12.10.2016
©МГТУ им. Н.Э. Баумана, 2017
Введение. Одной из причин, побудившей авторов к исследованиям по теме ра-
боты, является наличие в Microsoft Excel известного и давнего дефекта, затруд-
няющего строгий регрессионный анализ данных в этой программе, а именно,
встроенные средства Microsoft Excel позволяют проводить через случайные точ-
ки данных стандартные линии тренда (линейную, полиномиальную и др.), а
также показывать на графике вид их уравнения, однако в ряде случаев коэффи-
циенты уравнения выводятся Microsoft Excel неверно. Например, если через слу-
чайные точки yi, полученные по формуле  
3 2 0, 5 ,
i i i y x rnd     где rndi —
случайное число с равномерным распределением вероятностей на отрезке [0, 1];
 = 0,2 — амплитуда аддитивной случайной добавки; 0, , 100, i   x = [0, 10],
провести полиномиальную линию тренда пятого порядка, то Microsoft Excel по-
казывает для нее уравнение:
y = 0,0002x5 – 0,0063x 4 + 0,0638x3 – 0,3134x2 + 0,8914x + 0,3102. (1)
Если по уравнению (1) на том же графике построить линию регрессии, то
при x > 4 она сильно отличается от линии тренда, проведенной Microsoft Excel
(рис. 1, а).