Ольков_С_Г_Аналитическая юриспруденция

Решение:

1 шаг: Введем таблицу данных в диапазон А1:Е16 электронной таблицы ППП Excel:

2 шаг: Если просто вычислить коэффициенты регрессионных уравнений с помощью обычного МНК (метода наименьших квадратов) с использованием функции ЛИНЕЙН, то получим два уравнения. Первое для таблицы №1, а второе для таблицы №2.

Таблица №1.

10 ДА – детективное агентство.

316

Функция ЛИНЕЙН определяет параметры линейного регрессиионного уравнения. Чтобы воспользоваться ей выделим курсором, удерживая ЛКМ (левая клавиша мыши), свободную область 5х3 (5строк и 3 столбца) таблицы для вывода результата. В мастере функций (из статистических функций) выберем ЛИНЕЙН и щелкнем OK. Появится окно «Аргументы функции», имеющее 4 поля:

1)Известные значения у, куда введем (можно путем выделения) диапазон значений зависимой переменной;

2)Известные значения x, куда введем значения независимых переменных (можно путем выделения массива);

3)Константа – логическое значение, указывающее на наличие или отсутствие свободного члена в уравнении. Если вводим в

это поле нуль, то свободный член не выводится. Если вводим единицу, то свободный член присутствует.

4)Статистика – логическое значение, отвечающее за

дополнительную информацию. Если таковая необходима, то в данное поле вводится 1. Если нет, то вводится 0.

После того, как все 4 поля заполнены, нажимаем клавишу ОK. В верхнем левом углу выделенной области появится первое значение, а для того чтобы появились все остальные – нажимаем клавишу F2, а далее комбинацию клавиш Ctrl Shift Enter.

Получим нижеследующую таблицу результатов вычислений:

317

8 8

0,95032 3,75537

69 #Н/Д

114,788

612 #Н/Д

3237,69 169,234

95 #Н/Д

Параметры линейного уравнения показаны в верхней строке, и читать их в ППП Excel нужно справа налево:

y1 =12,37 +0,00144 y2 −0,536 x1 . Под свободным членом во второй строке показано его среднеквадратическое отклонение. Аналогично показаны средние квадратические отклонения параметров b1 и b2. В третьей строке показано значение множественного R2=0,95, а справа от него среднее квадратическое отклонение зависимой переменной. В первом столбце четвертой строки показано значение F-статистики=114,7. Правее его в этой же строке указано число степеней свободы df=12. В первом столбце последней строки указана регрессионная сумма квадратов, а рядом с ней во втором столбце пятой строки – остаточная сумма квадратов.

При желании получить более подробную информацию стоит воспользоваться таким инструментом как «Анализ данных», где выбрать «регрессию», и провести соответствующие вычисления.

Таблица №2. Для второго уравнения.

318

4 шаг: определим коэффициенты приведенной формы для первого уравнения, применяя функцию ЛИНЕЙН к вектору у2 (B2:B16) и вектору Х (G2:H16).

Результат вычислений приведем, например, в ячейках A18:B18 (в поля константа и статистика введем нуль):

5 шаг: создаем столбец коэффициентов приведенной формы, переместив значение из ячейки A18 в ячейку B19:

6 шаг: вычислим прогнозные значения у2 в ячейках J2:J16, умножая матрицу X – ячейки G2:H16, на вектор коэффициентов приведенной формы (ячейки B18:B19): =МУМНОЖ(G2:H16;B18:B19). Здесь порядок таков: 1) выделим столбец J2:J16 и зададим равенство в первой ячейке; 2) войдем в мастер функций, где в окне «математические» выберем функцию МУМНОЖ. Заполним поля (можно путем протаскивания с

320

помощью ЛКМ) и нажмем клавишу ОK. Появится первое значение. Нажмем клавишу F2, а далее комбинацию клавиш Ctrl Shift Enter, получив заполненный столбец J2:J16:

J

у2

(прогноз)

10164,3551

12023,15512

27527,73025

23810,13021

53034,8456

26539,28523

6446,755062

31245,33029

40421,24037

10636,71519

11625,16021

18071,91527

35435,29041

39152,89045

47060,45062

7 шаг: найдем коэффициенты регрессионного уравнения для переменной y1, взяв в качестве независимых переменных: 1) прогнозные значения для у2 и 2) значения переменной х1.

321

Выбираем функцию ЛИНЕЙН и вводим соответствующие значения, получая результат:

второго уравнения, применяя функцию ЛИНЕЙН к вектору у1 (A2:A16) и вектору Х (G2:H16).

Результат вычислений приведем, например, в ячейках D21:E21 (в поля константа и статистика введем нуль):

D E

322

9 шаг: создаем столбец коэффициентов приведенной формы, переместив значение из ячейки D21 в ячейку E22:

10 шаг: вычислим прогнозные значения у1 в ячейках M2:M16, умножая матрицу X – ячейки G2:H16, на вектор коэффициентов приведенной формы (ячейки E21:E22):

=МУМНОЖ(G2:H16;B18:B19). Здесь порядок таков: 1) выделим столбец M2:M16 и зададим равенство в первой ячейке; 2) войдем в мастер функций, где в окне «математические» выберем функцию МУМНОЖ. Заполним поля (можно путем протаскивания с помощью ЛКМ) и нажмем клавишу ОK. Появится первое значение. Нажмем клавишу F2, а далее комбинацию клавиш Ctrl Shift Enter, получив заполненный столбец M2:M16:

M

y1

прогноз 18,1561711

21,4640354

49,3954707

42,7797423

94,3112126

47,7044565

11,5404427

56,0111992

72,4763561

18,452828

20,1438423

31,684285

62,9235845 69,539313

83,0674268

11 шаг: найдем коэффициенты регрессионного уравнения для переменной y2, взяв в качестве независимых переменных: 1) прогнозные значения для у1 и 2) значения переменной х2.

323

Выбираем функцию ЛИНЕЙН и вводим соответствующие значения, получая результат:

125576464 37966869

21 #Н/Д

Таким образом, мы получили второе уравнение структурной формы модели:

324