Алгоритмы выполнения задачи

Алгорит.1. Расчет параметров уравнения линейной регрессии и проверки адекватности модели исходным данным

1. Сервис => Анализ данных => Регрессия => ОК;

2. Входной интервал Y <= диапазон ячеек табл. 1 со значениями о бъема продаж (Е4:Е33);

3. Входной интервал X – диапазон ячеек табл. 1 со значениями факторных признаков (В4:D33);

4. Метки в первой строке/Метки в первом столбце – НЕ активизировать;

5. Уровень надежности <= 68,3 (или 68.3);

6. Константа – ноль – НЕ активизировать;

7. Выходной интервал <= адрес ячейки заголовка первого столбца первой выходной результативной таблицы (А38);

8. Новый рабочий лист и Новая рабочая книга – НЕ активизировать;

9. Остатки – НЕ активизировать;

10. Стандартизованные остатки – НЕ активизировать;

11. График остатков – НЕ активизировать;

12. График подбора – НЕ активизировать;

13. График нормальной вероятности – НЕ активизировать;

ОК.

В результате указанных действий осуществляется вывод трех выходных таблиц (таблицам необходимо присвоить номера Табл.2 – Табл.4), начиная с ячейки, указанной в поле Выходной интервал диалогового окна инструмента Регрессия (пример структуры выходных таблиц приведен на рис. 1).

Таблица 2

Таблица 3

Таблица 4

Рис.1. Структура выходных таблиц

Рассчитанные в сгенерированных таблицах коэффициенты регрессии позволяют построить уравнение, выражающее зависимость объема продаж от факторных признаков:

У=а₀+а₁Х₁+а₂Х₂+а₃Х₃

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

Анализ адекватности регрессионной модели преследует цель оценить, насколько построенная теоретическая модель взаимосвязи признаков отражает фактическую зависимость между этими признаками.

Оценка соответствия построенной регрессионной модели исходным (фактическим) значениям признаков выполняется в 4 этапа:

1) оценка статистической значимости коэффициентов уравнения а₀, а₁, а₂, а₃ и определение их доверительных интервалов для заданного уровня надежности;

2) определение практической пригодности построенной модели на основе оценок линейного коэффициента корреляции r и индекса детерминации R²;

3) проверка значимости уравнения регрессии в целом по F -критерию Фишера;

4) оценка погрешности регрессионной модели.

Для анализа коэффициентов а₀, а₁, а₂, и а₃ линейного уравнения регрессии используется табл.4, в которой:

– значения коэффициентов а₀, а₁, а₂, и а₃ приведены в ячейках В54 и В57 соответственно;

– рассчитанный уровень значимости коэффициентов уравнения приведен в ячейках Е54 и Е57;

– доверительные интервалы коэффициентов с уровнем надежности Р=0,95 и Р=0,683 указаны в диапазоне ячеек F54:I57.

1. Определение значимости коэффициентов уравнения

Уровень значимости – это величина α =1– Р, где Р – заданный уровень надежности (доверительная вероятность).

Режим работы инструмента Регрессия использует по умолчанию уровень надежности Р=0,95. Для этого уровня надежности у ровень значимости равен α = 1 – 0,95 = 0,05. Этот уровень значимости считается заданным.

В инструменте Регрессия надстройки Пакет анализа для каждого из коэффициентов а₀, а₁, а₂, и а₃ вычисляется у ровень его значимости α_р, который указан в результативной таблице (Табл.4термин "Р- значение "). Если рассчитанный для коэффициентов а₀, а₁, а₂, и а₃ уровень значимости α_р, меньше заданного уровня значимости α = 0,05, то этот коэффициент признается неслучайным (т.е. типичным для генеральной совокупности), в противном случае – случайным.

Если один или несколько коэффициентов а₁, а₂, и а₃ признается случайными, то соответствующие им факторы необходимо исключить.

Для этого необходимо скопировать Лист 1 Рабочего файла на Лист 1(2) по следующему алгоритму.