Для того чтобы найти уравнение регрессии средствами MS Excel создадим сначала необходимые таблицы. В первую очередь необходимо рассчитать необходимые параметры для составления корреляционной таблицы.
Рис.1 Фрагмент окна Excel c табличными данными х и у.
Рис.2 Фрагмент окна Excel, отображающий максимальное и минимальное значения х и у.
Пояснения:
1) В ячейки A22:A51 в ячейки B22:B51 вводим значения X и Y.
2) Для расчёта средних значений первоначальных данных X и Y в ячейку A52 вводим формулу =СУММ(A22:A51)/30, а в ячейку B52 вводим формулу =СУММ(B22:B51)/30.
3) Чтобы рассчитать максимальное и минимальное значение X, в ячейки B54 и B55 вводим формулы =МАКС(A22:A51) и =МИН(A22:A51) соответственно.
4) Аналогично, чтобы рассчитать максимальное и минимальное значение Y, в ячейки B57 и B58 вводим формулы =МАКС(B22:B51) и =МИН(B22:B51) соответственно.
Рис.3 Фрагмент окна Excel, отображающий исходные данные и корреляционную таблицу.
Пояснения:
Используя начальные данные можно составить корреляционную таблицу.
1) В ячейки C1:G1 и в ячейки A3:A7 вводим интервалы изменения исходных значений X и Y.
2) В ячейки C2:G2 вводим середины интервалов по признаку X
3 В ячейки B3:B7 вводим середины интервалов по признаку Y.
4) В ячейку H3 вводим формулу =СУММ(C3:G3) и копируем её в ячейки H4:H7.
5) В ячейку C8 вводим формулу =СУММ(C3:C7) и копируем её в ячейки D8:H8.
6) В ячейку C9 вводим формулу =(B3*C3+B4*C4+B5*C5+B6*C6+B7*C7)/C8.
7) В ячейку D9 вводим формулу =(B3*D3+B4*D4+B5*D5+B6*D6+B7*D7)/D8.
8) В ячейку E9 вводим формулу =(B3*E3+B4*E4+B5*E5+B6*E6+B7*E7)/E8.
9) В ячейку F9 вводим формулу =(B3*F3+B4*F4+B5*F5+B6*F6+B7*F7)/F8.
10) В ячейку I3 вводим формулу =(C2*C3+D2*D3+E2*E3+F2*F3+G2*G3)/H3.
11) В ячейку I4 вводим формулу =(C2*C4+D2*D4+E2*E4+F2*F4+G2*G4)/H4.
12) В ячейку I5 вводим формулу =(C2*C5+D2*D5+E2*E5+F2*F5+G2*G5)/H5.
13) В ячейку I6 вводим формулу =(C2*C6+D2*D6+E2*E6+F2*F6+G2*G6)/H6.
14) В ячейку I7 вводим формулу =(C2*C7+D2*D7+E2*E7+F2*F7+G2*G7)/H7.
Рис.4 Фрагмент окна Excel, отображающий вычисление среднего значения х и у и коэффициента групповой корреляции.
Рис.5 Фрагмент окна Excel, отображающий вспомогательную таблицу для вычисления коэффициента корреляции.
Пояснения:
1) В ячейку L2 вводим формулу
=(C2*C8+D2*D8+E2*E8+F2*F8+G2*G8+H2*H8)/30.
2) В ячейку M2 вводим формулу
=(B3*H3+B4*H4+B5*H5+B6*H6+B7*H7+B8*H8)/30.
3) В ячейки N2:N6 и в ячейки O2:O6 вводим соответствующие средние интервальные значения X и Y.
4) В ячейку P2 вводим формулу =N2-$L$2 и копируем её в ячейки P3:P6.
5) В ячейку Q2 вводим формулу =O2-$M$2 и копируем её в ячейки Q3:Q6.
6) В ячейку R2 вводим формулу =P2*Q2, затем в ячейку R3 вводим формулу =P3*Q3 и т.д. до ячейки R6.
7) В ячейку R8 вводим формулу =СУММ(R2:R6).
8) В ячейку S2 вводим формулу =P2^2 и копируем её в ячейки S3:S6.
9) В ячейку T2 вводим формулу =Q2^2 и копируем её в ячейки T3:T6.
10) В ячейку W3 вводим формулу =(W2-$L$2)*($V$3-$M$2)*C3 и копируем её до ячейки АА3.
11) В ячейку W4 вводим формулу =(W2-$L$2)*($V$4-$M$2)*C4 и копируем её до ячейки АА4.
12) В ячейку W5 вводим формулу =(W2-$L$2)*($V$5-$M$2)*C5 и копируем её до ячейки АА5.
13) В ячейку W6 вводим формулу = (W2-$L$2)*($V$6-$M$2)*C6 и копируем её до ячейки АА6.
14) В ячейку W7 вводим формулу = (W2-$L$3)*($V$7-$M$2)*C7 и копируем её до ячейки АА7.
15) В ячейку X11 вводим формулу = S2*$W$8, копируем её до ячейки X15.
16) В ячейку Z11 вводим формулу = T2*$AB$3, копируем её до ячейки Z15
17) В ячейку V16 вводим формулу = СУММ(V11:V15).
18) В ячейку X16 вводим формулу =СУММ(X11:X15).
19) В ячейку Z16 вводим формулу = СУММ(Z11:Z15).
20) В ячейку X17 вводим формулу =X16/30.
21) В ячейку Z17 вводим формулу =Z16/30.
22) В ячейку X18 вводим формулу =КОРЕНЬ(X17).
23) В ячейку Z18 вводим формулу =КОРЕНЬ(Z17)
24) В ячейку L9 введём формулу =AB8/(X18*Z18). В этой ячейке будет находиться значение коэффициента корреляции для группированных данных.
2.1 Нахождение уравнений регрессии.
1) Найдём уравнение регрессии по группированным данным.
Для того чтобы вычислить коэффициенты уравнения регрессии по группированным данным необходимо вычислить значение параметров по формулам, приведенным в теории.
Пояснения:
1) В ячейку L32 вводим формулу =Х18.
2) В ячейку L33 вводим формулу =Z18.
3) В ячейку L34 вводим формулу =L33/L32.
В итоге получили уравнение регрессии по групповым данным:
У=1,2777Х-132,250
2.2 Нахождение уравнения регрессии с помощью метода наименьших квадратов.
Аппроксимируем функцию
1) В ячейку P24 вводим формулу =A22^2 и копируем её в ячейки P25:P53.
2) В ячейку Q24 вводим формулу =A22*B22 и копируем её в ячейки Q25:Q53.
3) В ячейку R24 вводим формулу =A22 и копируем её в ячейки R25:R53
В ячейку T24 вводим формулу =$L$9*$L$34*(R24-$L$2)+$M$2 и копируем её в ячейки T25:T53.
В ячейку W24 вводим формулу =(B22-V24)^2 и копируем её в ячейки W25:W53.
В ячейку Z24 вводим формулу =(B22-T24)^2 и копируем её в ячейки Z25:Z53.
4) В ячейку P54 вводим формулу =СУММ(P24:P53)
5) В ячейку Q54 вводим формулу =СУММ(Q24:Q53).
Рис.7 Фрагмент окна Excel, отображающий полученное уравнение линейной регрессии.
В итоге получили уравнение регрессии по групповым данным: y=1,2777X-133,195.
2.3 Нахождение коэффициентов детерминированности.
Рис.8 Фрагмент окна Excel, отображающий вычисление коэффициента детерминированности для линейного и группового уравнений.
Пояснения:
Для линейного уравнения
1) В ячейку W24 вводим формулу =(B22-V24)^2 и копируем её в ячейки W25:W53.
Для группового уравнения
2) В ячейку Z24вводим формулу =(B22-T24)^2 и копируем её в ячейки Z25:Z53.
3) В ячейку Х24 вводим формулу =(B22-$B$52)^2 и копируем её в ячейки Х25:Х53.
4) В ячейку АА24 вводим формулу =(B22-$M$2)^2 и копируем её в ячейки АА25:АА53.
5) В ячейку W54 вводим формулу =СУММ(W24:W53).
6) В ячейку X54 вводим формулу =СУММ(X24:X53).
7) В ячейку Z54 вводим формулу =СУММ(Z24:Z53).
8) В ячейку AA54 вводим формулу =СУММ(AA24:AA53).
9) В ячейку W56 вводим формулу =1-W54/X54 .
10) В ячейку Z56 вводим формулу =1-Z54/AA54.
2.4 Построение графиков зависимостей и линии тренда
Рис.10: График зависимостей
3. Вычисление с помощью среды Turbo Pascal
В данной задаче требуется с помощью подпрограммы вычислить Sx, Sy по данным, объединенным в группы, по формулам:
Пусть n – количество измеренных параметров,
i = 1, 2, …, n, где i – текущий индекс,
p = (p1, p2, …, pn) – массив чисел,
zср – средние значения элементов массива p;
Обозначим:
Результат:
Описание формальных параметров и переменных для подпрограммы представлены в таблице 3.2.
Таблица 3.2
Таблица формальных параметров и переменных подпрограммы
Имя переменной | Тип переменной | Примечание |
p | Mas | массив чисел |
zср | Real | средние значения элементов массива p |
s | Real | |
scp | Real | |
sq | Real | выходные данные |
i | Integer | текущий индекс |
Описание переменных программы представлены в таблице 3.3.