Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Обучающий практикум.docx
Скачиваний:
2
Добавлен:
01.07.2025
Размер:
2 Мб
Скачать
☆
  1. Вычисляем обратную матрицу .

  • Выделяем диапазон для размещения обратной матрицы (H12:I13).

  • В категории Математические выбираем функцию вычисления обратной матрицы =МОБР(H8:I9), в качестве массива указываем диапазон ячеек H8:I9, где размещена матрица, к которой надо вычислить обратную.

  • Затем следует нажать клавиши CTRL+SHIFT+ENTER.

4. Умножаем обратную матрицу на транспонированную матрицу .

  • Выделяем диапазон ячеек для результата умножения матриц (H16:Q17) размером 2*10. Результатом является массив с таким же числом строк, как обратная матрица , т.е. 2 и с таким же числом столбцов, как матрица , т.е. 10.

  • Затем вводится формула умножения матриц = МУМНОЖ(H12:I13, D4: E13).

  • Затем следует нажать клавиши CTRL+SHIFT+ENTER.

5. Умножаем матрицу на y.

  • Выделяем диапазон ячеек для результата умножения матриц (H19: H20) размером 2*1.

  • Затем вводится формула умножения матриц = МУМНОЖ(H16:Q17, B4: B13).

  • Затем следует нажать клавиши CTRL+SHIFT+ENTER.

В ячейках H19: H20 будут находиться параметры модели линейной регрессии а0 =102,5 и а1=46,87.

В матричной форме расчет параметров модели может быть представлен на рис. 3.8 -3.9.

3.8. Фрагмент рисунка 3.7 в режиме «Показать формулы».

Рис. 3.9. Вычисления параметров модели с помощью матричных функций МУМНОЖ и МОБР.

Решение 1) d)

Построение модели линейной регрессии зависимости стоимости ущерба, нанесенного пожаром от расстояния до ближайшей пожарной станции с использованием функции ЛИНЕЙН табличного процессора Excel.

Рассмотрим технологию оценки параметров модели линейной регрессии зависимости стоимости ущерба, нанесенного пожаром от расстояния до ближайшей пожарной станции на основании данных (табл. 1.2) с использованием с использованием функции ЛИНЕЙН.

Вначале выделяем диапазон для размещения результатов выполнения функции ЛИНЕЙН. На листе выделяется область высотой 5 строк и шириной равной количеству столбцов с данными, в нашем примере их два (F5:G9). Затем вызываем функцию ЛИНЕЙН.

При применении функции ЛИНЕЙН табличного процессора Excel на экран дисплея выдается диалоговое окно (рис. 3.10) для задания значений (у) и (х).

Рис. 3.10. Диалоговое окно функции ЛИНЕЙН

  • В данном окне в строке «Известные значения y» следует внести все 10 значений из столбца y, а в строке «Известные значения x» - соответственно все 10 значений из столбца x. Если необходимо выполнить оценку двух параметров – постоянного и регрессионного, то в строке «Конст» следует указать значение 1. Так как при оценке параметров модели нас, в первую очередь, интересуют статистические сведения, то в окне строки «Статистика» следует указать значение 1. После ввода всех значений, как показано на рис. 3.11, следует нажать клавиши CTRL+SHIFT+ENTER.

После этого, будет выдан результат, как показано на рис. 3.12.

3.11. Диалоговое окно функции ЛИНЕЙН после задания необходимых данных

Рис. 3.12.Результат выполнения функции ЛИНЕЙН

46,87

102,50

4,26

14,50

R2

0,94

18,01

F

121,10

8

ν

ESS

39276,94

2594,66

RSS

Слева и справа от полученных значений указаны их обозначения (наименования), которые поясняются ниже:

и – оценки параметров полученной парной регрессии (1-я строка);

и – стандартные ошибки оцененных параметров (2-я строка);

R2 – коэффициент детерминации (3-я строка, 1-ый столбец);

– стандартная ошибка полученной регрессии (3-я строка, 2-ой столбец);

F – статистика Фишера полученной парной регрессии (4-я строка, 1-ый столбец);

ν – степень свободы (3-я строка, 2-ой столбец);

RSS - (5-я строка, 1-ый столбец);

ESS - квадрат остатков для полученной парной регрессии (5-я строка, 2-ой столбец).

Итак, в первой строке выдаются значения параметров и , а во второй стоке соответственно их стандартные ошибки и .

В ячейках F8 и G8 будут находиться параметры модели линейной регрессии =102,5 и =46,87.

2) Проверим качество уравнения регрессии.

Качество модели оценивается коэффициентом детермина­ции R2.

=1-25,947/418,716=0,938

Величина R2 = 0,938 означает, что фактором расстояния до ближайшей пожарной станции можно объяснить 93,8% вариации (разброса) стоимости ущерба, нанесенного пожаром.

Точность модели оценим с помощью средней ошибки аппроксимации (2.11) Еотн=5,6%. Точность модели хорошая.

Оценим значимость уравнения регрессии с помощью критерия Фишера. Расчетное значение F- критерия вычислим по формуле (2.9)

Расчетное значение F- критерия Фишера можно найти в таблице Дисперсионный анализ протокола Excel.

Уравнение регрессии значимо на уровне α, если расчетное значение F>Fтабл., где Fтабл. – табличное значение F-критерия Фишера

Табличное значение F-критерия можно найти с помощью функции FРАСПОБР1. Табличное значение F-критерия при α=0.05 при и составляет 5,318.

Поскольку F >F , уравнение регрессии следует признать значимым.

Оценим значимость коэффициентов полученной модели, используя результаты отчета Excel. Это можно сделать тремя способами.

Коэффициент уравнения регрессии признается значимым в том случае, если:

  • Наблюдаемое значение t-статистики Стьюдента для этого коэффициента больше, чем критическое (табличное) значение статистики Стьюдента (для заданного уровня значимости, например, и числа степеней свободы df=n-k-1,где n – это число наблюдений, аk – число факторов в модели);

  • Р-значение t-статистики Стьюдента для этого коэффициента меньше, чем уровень значимости, например, ;

  • Доверительный интервал для этого коэффициента (вычисленный с некоторой доверительной вероятностью, например 95%) не содержит ноль внутри себя, т.е., если нижняя 95% и верхняя 95% границы доверительного интервала имеют одинаковые знаки.

Зна­чимость коэффициента проверим по второму и третьему способам, используя данные табл. 3.3.

Р-значение ( ) = 0,00 < 0,01 < 0,05.

Следовательно, коэффициент ( ) значим при 1%-ном уровне, а тем более при 5%-ном уровне значимости.

Нижняя 95% граница равна 3,70 и верхняя 95% граница равна 5,67, границы доверительного интервала имеют одинаковые знаки, следовательно, коэффициент ( ) значим.

3) Для того, чтобы оценить стоимость ущерба, нанесенного пожаром, если расстояние до ближайшей пожарной станции уменьшится на 10 % от своего среднего уровня, необходимо найти ожидаемое значение фактора х, затем следует подставить значение в полученную модель:

Доверительный интервал для прогнозов индивидуальных значений определяется из соотношения (2.18):

Коэффициент Стьюдента для m=8 степеней свободы и уровня значимости 0,01 равен 1,86; стандартная ошибка равна 1,801.

Таким образом, прогнозное значение =23,45 c вероятностью 90% будет находиться между верхней границей, равной 29,97 и нижней границей, равной 19,93.

Рис. 3.13. Выполнение пункта 3 в Excel.

Рис. 3.14. График модели парной регрессии зависимости стоимости ущерба, нанесенного пожаром от расстояния до ближайшей пожарной станции точечный и интервальный прогноз.