Тема2. Уравнение регрессии
2.1. общий подход к построению уравнения регрессии на примере линейной модели
Пример 9. Решение задачи нелинейной оптимизации с помощью средства поиска решений на примере линейного уравнения регрессии:
В ячейку В2 поместил формулу =7*LOG(10;10);
В ячейку А2 поместил формулу =СТЕПЕНЬ(78;LOG(1;78));
В ячейку F3 ввел формулу =СУММКВРАЗН(B2:B7;E2+D3*A2:A7);
Провел операцию Данные – Поиск решения и получил результат:
x |
y |
|
m |
b |
|
=СТЕПЕНЬ(78;LOG(1;78)) |
=7*LOG(10;10) |
|
|
|
|
2 |
9 |
|
1,88571412875188 |
5,39999996034584 |
=СУММКВРАЗН(B2:B7;E3+D3*A2:A7) |
3 |
12 |
|
|
|
|
4 |
13 |
|
|
|
|
5 |
14 |
|
|
|
|
6 |
17 |
|
|
|
|
x |
y |
|
m |
b |
|
1 |
7 |
|
|
|
|
2 |
9 |
|
1,885714 |
5,4 |
1,771428571 |
3 |
12 |
|
|
|
|
4 |
13 |
|
|
|
|
5 |
14 |
|
|
|
|
6 |
17 |
|
|
|
|
2.2. Функции рабочего листа для уравнения линейной регрессии
Пример 10. Определение параметров m и b с помощью функции НАКЛОН и ОТРЕЗОК:
Для нахождения m надо ввести в ячейку D2 формулу =НАКЛОН(B2:B7;A2:A7);
Для нахождения b надо ввести в ячейку E2 формулу =ОТРЕЗОК(B2:B7;A2:A7$);
Получил следующий результат:
x |
y |
|
m |
b |
|
=СТЕПЕНЬ(78;LOG(1;78)) |
=7*LOG(10;10) |
|
=НАКЛОН(B2:B7;A2:A7) |
=ОТРЕЗОК(B2:B7;A2:A7) |
|
2 |
9 |
|
1,88571412875188 |
5,39999996034584 |
=СУММКВРАЗН(B2:B7;E3+D3*A2:A7) |
3 |
12 |
|
|
|
|
4 |
13 |
|
|
|
|
5 |
14 |
|
|
|
|
6 |
17 |
|
|
|
|
x |
y |
|
m |
b |
|
1 |
7 |
|
1,885714286 |
5,4 |
|
2 |
9 |
|
1,885714129 |
5,39999996 |
1,771428571 |
3 |
12 |
|
|
|
|
4 |
13 |
|
|
|
|
5 |
14 |
|
|
|
|
6 |
17 |
|
|
|
|
Пример 11. Нахождение m и b с помощью точечного графика:
Выделил диапазон А2:В7, затем вставил точечную диаграмму (Вставка – Диаграммы – Точечная);
На появившуюся диаграмму щелкнул правой кнопкой мыши, в появившемся диалоговом окне выбрал команду Добавить линию тренда;
В диалоговом окне Линия тренда выбрал параметр – Линейная; установил флажки на пунктах Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации;
Нашел теоретические значения у введя в ячейку С2 формулу =$D$2*A2+$E$2 и растянув на диапазон С2:С7:
x |
y |
Теор. Значение у |
m |
b |
|
=СТЕПЕНЬ(78;LOG(1;78)) |
=7*LOG(10;10) |
=$D$2*A2+$E$2 |
=НАКЛОН(B2:B7;A2:A7) |
=ОТРЕЗОК(B2:B7;A2:A7) |
|
2 |
9 |
=$D$2*A3+$E$2 |
1,88571412875188 |
5,39999996034584 |
=СУММКВРАЗН(B2:B7;E3+D3*A2:A7) |
3 |
12 |
=$D$2*A4+$E$2 |
|
|
|
4 |
13 |
=$D$2*A5+$E$2 |
|
|
|
5 |
14 |
=$D$2*A6+$E$2 |
|
|
|
6 |
17 |
=$D$2*A7+$E$2 |
|
|
|
x |
y |
Теор. Значение у |
m |
b |
|
1 |
7 |
7,285714286 |
1,885714286 |
5,4 |
|
2 |
9 |
9,171428571 |
1,885714129 |
5,39999996 |
1,771428571 |
3 |
12 |
11,05714286 |
|
|
|
4 |
13 |
12,94285714 |
|
|
|
5 |
14 |
14,82857143 |
|
|
|
6 |
17 |
16,71428571 |
|
|
|
Задание 3
Построение линейную модель по следующим данным:
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
=НАКЛОН(A2:H2;A1:H1) |
=ОТРЕЗОК(A2:H2;A1:H1) |
=13*LOG(10;10) |
19 |
26 |
30 |
37 |
44 |
49 |
=СТЕПЕНЬ(78;LOG(55;78)) |
|
|
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
6,011904762 |
7,07142857 |
13 |
19 |
26 |
30 |
37 |
44 |
49 |
55 |
|
|
|
Построение экспоненцианальной модели:
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
=НАКЛОН(A2:H2;A1:H1) |
=ОТРЕЗОК(A2:H2;A1:H1) |
Теоретическое значение |
|
|
|
|
|
|
|
=13*LOG(10;10) |
19 |
26 |
30 |
37 |
44 |
49 |
=СТЕПЕНЬ(78;LOG(55;78)) |
|
|
|
=$J$1*A1+$K$1 |
=$J$1*B1+$K$1 |
=$J$1*C1+$K$1 |
=$J$1*D1+$K$1 |
=$J$1*E1+$K$1 |
=$J$1*F1+$K$1 |
=$J$1*G1+$K$1 |
=$J$1*H1+$K$1 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
6,011904762 |
7,071429 |
Теоретическое значение |
|
|
|
|
|
|
|
13 |
19 |
26 |
30 |
37 |
44 |
49 |
55 |
|
|
|
13,08333333 |
19,09524 |
25,10714 |
31,11905 |
37,13095 |
43,14286 |
49,15476 |
55,16667 |
