CC BY
38
МЕЖДУНАРОДНЫЙ НАУЧНЫЙ ЖУРНАЛ «СИМВОЛ НАУКИ» №7/2015 ISSN 2410-700Х_________________
УДК 004
Федоров Андрей Борисович,
студент группы ЭВТ-11 Полевщиков Иван Сергеевич,
аспирант, ассистент
ФГБОУ ВПО «Пермский национальный исследовательский политехнический университет», г. Пермь
E-mail: i.s.polevshchikov@gmail.com
ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ ПРИ ОБУЧЕНИИ СТУДЕНТОВ ПОСТРОЕНИЮ
ЛИНЕЙНОЙ РЕГРЕССИОННОЙ МОДЕЛИ (ЧАСТЬ 1)
Аннотация
В статье описаны особенности автоматизации процесса построения линейной регрессионной модели с использованием электронных таблиц (на примере Microsoft Excel). Детально показан процесс вычисления коэффициентов модели.
Ключевые слова
Регрессионный анализ, линейная регрессионная модель, электронные таблицы.
Для автоматизации расчетов в различных областях деятельности в настоящее время нашли широкое применение электронные таблицы.
Рассмотрим особенности использования электронных таблиц при построении линейной одномерной регрессионной модели. Электронные таблицы удобно использовать при обучении студентов методике построения данных моделей, поскольку эти таблицы упрощают некоторые рутинные расчеты.
Задача регрессионного анализа состоит в том, чтобы, зная множество значений на входах и выходах, построить модель, то есть определить функцию ящика, по которой вход преобразуется в выход [1-3].
Решим на конкретном примере задачу регрессионного анализа, то есть, опираясь на имеющиеся экспериментальные данные, определим функцию черного ящика, по которой вход преобразуется в выход. Для автоматизации процесса расчетов воспользуемся программой Microsoft Excel [4].
Пусть в результате проведения измерений был получен набор из n = 10 экспериментальных точек. Полученные исходные данные занесены в табл. 1.
Таблица 1
Экспе
риментальные данные
i 1 2 3 4 5 6 7 8 9 10
X 5 7 9 11 13 16 18 20 23 25
Y. 9 8 17 23 30 40 49 49 56 70
Рассматривая экспериментальные данные, предположим, что они подчиняются линейному закону, т.е. выдвигаем гипотезу: Y = АхX + А0.
Для вычисления значений А0 и А1 воспользуемся формулами [1-2]:
А =
n n n n
ZYZX2-2 xy Z х,
i=1 i=1 i =1 i =1
nZ х2 - (I x, )2
i =1
i=1
(1)
n n
n
Z XiYi -Zy Z X,
a =
— i =1
i=1 i=1
nn
'2 \2
«Z X2 - (Z X,f
'-1 i-1 (2)
Для удобства составим с использованием Excel таблицу промежуточных вычислений, показанную на
рис. 1.
n
46
международный научный журнал «символ науки»
№7/2015
ISSN 2410-700Х
СУММ
X V .£ =Д2*В2
т Расчеты .xls * * jj
А В с D I
1 Xi Yi XiA2 XPYi
2 : с Э' 25 =A2‘B2
3 7 8 49 56
4 Э 17 81 153
5 11 23 121 253
6 13 30 169 390
7 18 40 256 640
8 18 49 324 882
Э 20 49 400 980
10 23 58 529 1288
11 25 70 625 1750
Рисунок 1 - Таблица промежуточных вычислений
Подставляя конкретные значения из таблицы (рис. 1) в формулы (1) и (2), вычисляем значения А0 и А1 соответственно.
Расчет значения А0 с использованием Excel показан на рис. 2.
СУММ (- X =((СУММ(В2:В11}*СУММ(С2:С11)}-(СУММ(О2:О11}*СУММ(А2:А11}})/(10*СУММ(С2:С11)-((СУММ(А2:А11)}л2})
m Расчеты.хЬ * xlR 1
- A В C D li- lu <3 I H | I | J | К | L
1 Xi Yi XiA2 XPYi Ei=Yi-A1*X-A0 EiA2 сигма
2 9' ' it 4^ 3.533604401 12.48636006 2.805971
3 7 8 49 56 -3.576417125 12.79075945
4 9 17 81 153 -0.686438651 0.471198022
5 11 23 121 253 -0.796460177 0.634348814
6 13 30 169 390 0.093518297 0.008745672 S
7 16 40 256 640 0.928486008 0.862086267 9.01983
8 18 49 324 882 3.818464482 14.580671
9 20 49 400 980 -2.291557044 5.251233685
10 23 56 529 1288 4.456589333 19.86118848
11 . 25. 70. . 625. . 1750. 3.433389141 11.788161
12 сумма 78.73475245
13 AO
14 iinrail
Рисунок 2 - Вычисление коэффициента А0
Расчет значения А1 с использованием Excel показан на рис. 3.
СУММ ▼ С X ✓ £ =(10*СУММ(Е]2:С11)-(СУММ(В2:В11)* СУММ(А2:А11)})/(10*СУММ(С2:С11НСУММ(А2:А11)|)А2)|
m Расчеты .xls * X | \щ\
A A в c D E “1 F G | H | i | J Г
1 Xi Yi XiA2 XPYi Ei=Yi-A1*X-A0 EiA2 сигма
2 ' 9' ' 2^ 4? 3.533604401 12.48636006 2.805971
3 7 8 49 56 -3.576417125 12.79075945
4 9 17 81 153 -0.686438651 0.471198022
6 11 23 121 253 -0.796460177 0.634348814
6 13 30 169 390 0.093518297 0.008745672 S
7 16 40 256 640 0.928486008 0.862086267 9.01983
8 18 49 324 882 3.818464482 14.580671
9 20 49 400 980 -2.291557044 5.251233685
10 23 56 529 1288 4.456589333 19.861 18848
11 . 25. . 70. . 625. 1750, 3.433389141 1 1.788161
12 сумма 78.73475245
13 A0
14 -9 80866
15 Xi Yi Y=A1*X+A0-S Y=A1"X+A0+S Попадание
16 A1 5 9 -3.553434461 14.48622566 попадает
17 A111P2) 7 8 2.556587065 20.59624719 попадает
Рисунок 3 - Вычисление коэффициента А1
47
международный научный журнал «символ науки»
№7/2015
ISSN 2410-700Х
Таким образом, получены следующие значения коэффициентов:
А0 *-9.81 А * 3.06
и
. Данные
значения обеспечивают прохождение графика Y = АхX + А0 как можно ближе одновременно ко всем
экспериментальным точкам [1-2].
Таким образом, мы получили следующее линейное уравнение (линейную регрессионную модель): Y = 3.06X — 9.81.
Продолжение данного исследования, а именно проверка полученной гипотезы, будет описано в следующей статье.
Список использованной литературы:
1. Мухин О.И. Лекция «Линейные регрессионные модели». URL:
http://stratum.pstu.ac.ru/education/textbooks/modelir/lection02.html.
2. Мухин О.И. Практика «Регрессионные модели». URL:
http://stratum.pstu.ac.ru/education/textbooks/modelir/practice01.html.
3. Файзрахманов Р.А., Липатов И.Н. Автоматизация научных исследований : учебное пособие. Пермь : Изд-во ПГТУ, 2011. 161 с.
4. Леоненков А.В. Решение задач оптимизации в среде MS EXCEL. СПб : БХВ-Петербург, 2005. 690 с.
© А.Б. Федоров, И.С. Полевщиков, 2015
УДК 004
Федоров Андрей Борисович,
студент группы ЭВТ-11 Полевщиков Иван Сергеевич,
аспирант, ассистент
ФГБОУ ВПО «Пермский национальный исследовательский политехнический университет», г. Пермь
E-mail: i.s.polevshchikov@gmail.com
ИСПОЛЬЗОВАНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ ПРИ ОБУЧЕНИИ СТУДЕНТОВ ПОСТРОЕНИЮ
ЛИНЕЙНОЙ РЕГРЕССИОННОЙ МОДЕЛИ (ЧАСТЬ 2)
Аннотация
В статье продолжено описание особенностей автоматизации процесса построения линейной регрессионной модели с использованием электронных таблиц (на примере Microsoft Excel). Детально описан процесс проверки полученной гипотезы.
Ключевые слова
Регрессионный анализ, линейная регрессионная модель, электронные таблицы.
В статье, посвященной предыдущей части исследования, было показано, как с помощью электронных таблиц (на примере Excel [1]) автоматизировать процесс вычисления коэффициентов линейной регрессионной модели [2-4].
Теперь необходимо проверить, имеем ли мы право принять полученную гипотезу Y = 3.06X — 9.81 как верную, или же она должна быть отклонена. Для этого в первую очередь необходимо рассчитать ошибку E, между точками заданной экспериментальной и полученной теоретической зависимостей по формуле [2-3]:
E = ^- — Y.r-P= Y — А„ — А ■ X,, i = и; (1)
48
