Использование электронных таблиц при обучении студентов построению линейной регрессионной модели (часть 2) Текст научной статьи по специальности «Математика»

международный научный журнал «символ науки»

№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

международный научный журнал «символ науки»

№7/2015

ISSN 2410-700Х

Y Эксп.

где ' - экспериментальное значение ординаты точки;

Y Теор.

- теоретическое значение ординаты точки;

n

- количество экспериментальных точек.

Расчет значения Ei по формуле (1) с использованием Excel приведен на рис. 1.

слчис - (- X V & | =В 2- $А $17* А 2- $А $14

А в С □ Е F

1 Xi Yi XiA2 XPYi Ei=Yi-ArX-AO

2 ■—1 Н и 25 45 =В2-Ш17*А2-Ш14

3 7 8 49 56 -3,576417125

4 9 17 81 153 -0,686438651

5 11 23 121 253 -0,796460177

6 13 30 169 390 0',0-93518297

7 16 40 256 640 0,928486008

8 18 49 324 882 3.818464482

9 20 49 400 980 -2,291557044

10 23 56 529 1288 4,456589333

11 25 70 625 1750 3,433389141

12 сумма

13 АО

14 ; -9,8086Ef

15 Xi Yi Y=ArX*-A0-S

16 А1 5 9 -3,553434461

17 4 Г| ! 3,055011! 7 l"l 8 4 ~Т 2,556587065 Г| ГГГГППГП 4

Рисунок 1 - Вычисление ошибки Ei

Также необходимо рассчитать суммарную ошибку F по формуле:

F (Л,, А) = Х E2

-1 (2)

Расчет значения суммарной ошибки по формуле (2) с использованием Excel приведен на рис. 2 (ячейка G12).

СЛЧИС - (" X ✓ £ =CyMM[G2:Gll)

А В С □ Е F G

1 Xi Yi XiA2 XPYi Ei=Yi-A1*X-A0 EiA2

2 5 9 25 45 3,533604401 ' 12,4863600^

3 7 8 49 56 -3,576417125 12,79075945

4 9 17 81 153 -O',686438651 0,471198022

5 11 23 121 253 -0,796460177 0,634348814

6 13 30 169 390 0,093518297 0,008745672

7 16 40 256 640 0,928486008 0,862086267

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 сумма =CyMM(G2:G11}

Рисунок 2 - Вычисление суммарной ошибки

Таким образом, суммарная ошибка составляет:

F(Л,, Л) ~ 78.735

Далее вычислим значение ® по формуле:

49

МЕЖДУНАРОДНЫЙ НАУЧНЫЙ ЖУРНАЛ «СИМВОЛ НАУКИ» №7/2015 ISSN 2410-700Х

Расчет значения & по формуле (3) с использованием Excel приведен на рис. 3.

слчис ▼ С X У' А =HOPEHb(G12/lO)

А | В С I □ I Е I F | G I H I I I

1 Xi Yi XiA2 Xi’Yi Ei=Yi-APX-AO EiA2 сигма

2 5 э 25 45 3,533604401 12,48636006 G12/1 □>

3 7 8 49 56 -3,576417125 12,79075945

4 Э 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 СО со ho 3,818464482 14,580671

Э 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,734752453

Рисунок 3 - Вычисление значения & Затем вычислим значение S по формуле:

S

&

сте(ш-с^(Д)) (4)

Расчет значения

S

по формуле (4) с использованием Excel приведен на рис. 4.

СЛЧИС - C X V' A =12/COS (ATA N (A 17))

A | В C I □ 1 E | F G | H I I I

1 Xi Yi XiA2 Xi’Yi Ei=Yi-APX-AO EiA2 сигма

2 5 9 25 45 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 ,N(A17»

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 -9,80866

15 Xi Yi Y=A1*X+A0-S Y=APX+A0+S Попадание

16 A1 5 9 -3,553434461 14,48622566 попадает

17 ! 3,055011] 7 8 2,556587065 20,59624719 попадает

Рисунок 4 - Вычисление значения S Таким образом, & ~ 2 81 и S ~ 9 02 .

Если в полосу, ограниченную линиями Y = ДX + Д — S и Y = ДX + Д + S , попадет 68.26% или

более из всех экспериментальных точек, то можно сделать вывод о том, что наша гипотеза о линейности верна [2-3].

С использованием Excel проверим, попадают ли точки внутрь заданного интервала.

На рис. 5 показана формула для проверки на попадание экспериментальной точки внутрь найденного интервала.

50

международный научный журнал «символ науки»

№7/2015

ISSN 2410-700Х

слчис

X у/ J& =ЕСЛИ{И(Е1й>Е16;Е1&<й16);,|попадает";"не попадает")

□

1 Xi Yi ХИ2 Xi*Yi Ei=Yi-A1*X-A0 ЕИ2

2 5 9 25 45 3,533604401 12,48636006

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

7 16 40 256 640 0,928486008 0,862086267

8 18 49 324 882 3,818464482 14,580671

Э 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 11,788161

12 сумма 78,73475245

13 АО

14 -9,80866

15 Xi Yi Y=ArX-*-A0-S Y=A1,X-*-A0+S Попадание

16 А1 5I_ 91 -3,5534344611 14,48Б225БЙюпадает") 1

Рисунок 5 - Проверка на попадание точки внутрь интервала

Далее вычисляется процент попадания точек в полосу, ограниченную линиями Y = Д1X + Д — S и Y = AX + Д + S (рис. 6).

СЛЧИС X X V £ =(СЧЁТЕСЛИМН(Н15:Н25;г'попадаетг')*100)/10

A | В I C I □ l E F I G H j 1

1 Xi Yi Xi A2 Xi*Yi Ei=Yi-A1’X-A0 EiA2 сигма

2 5 9 25 45 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 Б40 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 ДО

14 -9,80866

15 Xi Yi Y=ArX+AD-S Y-ArX+AO+S Попадание Процент попадания

16 A1 5 9 -3,553434461 14,48622565 попадает ипадаетТЮОУЮ |

17 3,055011 7 8 2,556587065 20,59624719 попадает

18 9 17 8,666608591 26,70626871 попадает

19 11 23 14,77663012 32,81629024 попадает

20 13 30 20,88665164 38,92631175 попадает

21 16 40 30.05168393 48.09134405 попадает

22 18 49 36,16170546 54,20136558 попадает

23 20 49 42,27172698 60,3113871 попадает

24 23 56 51,43675927 69,47641939 попадает

25 25 70 57,5467808 75,58644092 ропадает .

Рисунок 6 - Вычисление процента попадания точек в полосу

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

СЛЧИС X V* £■ =ЕСЛИ(Лб>=58,2б;Г1Да";г'Нет"}

А I В I С I □ I Е F I G I H |i| j I

1 Xi Yi Xi А2 Xi*Yi Ei=Yi-A1’X-AD EiA2 сигма

2 5 9 25 45 3,533604401 1248636006 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

5 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 525 1750 3,433389141 11,788161

12 сумма 78,73475245

13 АО

14 -9,80866

15 Xi Yi Y=A1*X+A0-S Y=A1*X+ACN-S Попадание Процент попадания

16 А1 5 9 -3,553434461 14.48622566 попадает I

17 3,055011 7 8 2,556587065 20,59624719 попадает

18 9 17 8,666608591 26,70626871 попадает

19 11 23 14.77663012 32,81629024 попадает Верна ли гипотеза

20 13 30 20,88665164 38,92631176 попадает |б8.26:"Да':"Нет"» I

21 16 40 30.05168393 48.09134405 попадает

22 18 49 36.16170546 54.20136558 попадает

23 20 49 42.27172698 60.3113871 попадает

24 23 56 51.43675927 69.47641939 попадает

25 25 70 57.5467808 75.58644092 попадает

Рисунок 7 - Вывод о том, верна ли гипотеза

51

________МЕЖДУНАРОДНЫЙ НАУЧНЫЙ ЖУРНАЛ «СИМВОЛ НАУКИ» №7/2015 ISSN 2410-700Х____________

Окончательные расчеты показывают, что 10 точек из 10 (то есть 100%) попадают в полосу, ограниченную линиями Y = ДX + Д — S и Y = ДX + Д + S , из чего заключаем: зависимость между

входом и выходом исследуемого объекта линейная, то есть выдвинутая нами гипотеза верна.

Таким образом, на данном примере продемонстрированы преимущества автоматизации процесса построения линейной регрессионной модели с использованием электронных таблиц (на примере Excel) в процессе обучения студентов. К данным преимуществам относятся:

1) возможность вычисления значений элементов таблиц по заданным формулам;

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

3) использование различных средств управления атрибутами текста (изменение размера шрифта, начертания, цвета фона ячейки и т.д.).

Список использованной литературы:

1. Леоненков А.В. Решение задач оптимизации в среде MS EXCEL. СПб : БХВ-Петербург, 2005. 690 с.

2. Мухин О.И. Лекция «Линейные регрессионные модели».

URL: http://stratum.pstu.ac.ru/education/textbooks/modelir/lection02.html.

3. Мухин О.И. Практика «Регрессионные модели».

URL: http://stratum.pstu.ac.ru/education/textbooks/modelir/practice01.html.

4. Файзрахманов Р.А., Липатов И.Н. Автоматизация научных исследований : учебное пособие. Пермь : Изд-во ПГТУ, 2011. 161 с.

© А.Б. Федоров, И.С. Полевщиков, 2015

УДК 631.3

Хажметов Лиуан Мухажевич

доктор техн. наук, профессор КБГАУ им. В.М. Кокова, Шекихачев Юрий Ахметханович доктор техн. наук, профессор КБГАУ им. В.М. Кокова,

г. Нальчик, РФ Аушев Магомед Хусеинович доцент Ингушского ГУ, г. Магас, РФ, E-mail: shek-fmep@mail.ru

КОМБИНИРОВАННЫЙ АГРЕГАТ ДЛЯ ПРЕДПОСЕВНОЙ ПОДГОТОВКИ ПОЧВ И

ОПТИМИЗАЦИЯ ПАРАМЕТРОВ

Аннотация

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

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

Почва, комбинированный агрегат, измельчение, выравнивание, оптимизация, параметры.

Анализ конструкций комбинированных агрегатов с выравнивающими устройствами для предпосевной подготовке почв показал, что зарубежные комбинированные агрегаты имеют большую стоимость и при этом допускают глыбы размером до 100 мм и гребни высотой до 4 см. Отечественные комбинированные агрегаты, предназначенные для предпосевной подготовке почвы, проводят обработку почвы на глубину до 16...20 см,

52