Решение задачи корреляционно-регрессионного анализа в интегрированных системах



 

На основе приведенных в табл. 3 данных необходимо:

n сформулировать экономическую постановку задачи;

n провести логический и графический анализ исходных данных и построить эмпирическую линию регрессию;

n рассчитать коэффициент корреляции;

n провести подбор уравнения регрессии и определить параметры;

n построить теоретическую линию регрессии;

n рассчитать ошибку уравнения регрессии, теоретическое корреляционное отношение;

n осуществить прогноз результативного признака по значению указанного фактора для найденного уравнения

 

                                                                                    Таблица 3

                        Даны выборочные значения (x,y):

x 0.2 1.3 1.7 2.5 2.8 3.6 4.1 5.2 5.4
y 1.1 2 1.9 2.1 2.3 2.5 3.1 3.2 3.7
№п\п 1 2 3 4 5 6 7 8 9

 

Рассмотрим технологию решения задачи в интегрированной системе Excel.

В системе Excel расчет параметров регрессии выполняется при вызове команды /Сервис/Математика/Регрессия, которая открывает окно для задания условий расчета:

· /Независимые -задает координаты столбца или блока столбцов, в которых введены значения независимых переменных;

· /Зависимая - задает столбец со значениями зависимой величины;

· /Блок вывода -  задает адрес блока, в который будут выведены результаты регрессионного анализа;

· /Начало координат -  выбор в дополнительном окошке варианта расчета уравнения регрессии с константой B (альтернатива НЕТ - линия регрессии не проходит через начало координат) или без нее (альтернатива ДА);

· /Выполнить - инициализация расчета параметров регрессии;

· /Отменить - команда, отменяющая все заданные установки;

· /Выход- возврат в электронную таблицу.

Рассмотрим пример расчета параметров регрессии для двух показателей X и Y. Данные по задаче и отчет по решению приведены на рис. 1.

 

 

Р и с. 1. Пример расчета параметров регрессии для двух показателей X и Y (B не равно 0)

 

Для расчета параметров регрессии необходимо выполнить:

1. Ввести исходные данные, т. е. ряды независимых и зависимой переменных. Каждый ряд последовательно вводится в клетки одного столбца. Если независимых переменных несколько, то они обязательно вводятся в соседние столбцы. Зависимая переменная может быть введена в любой столбец.

2. Активизировать пункт меню /Сервис/Математика/Регрессия, который открывает окно задания условий расчета.

3. Выбрать пункт меню /Независимые и задать координаты блока, содержащего значения независимых переменных. В примере на рис. 1. это блок A3..A11.

4. Выбрать пункт меню /Зависимые и указать координаты блока, содержащего значения зависимой переменной (на рис. 1 - блок B3..B11).

5. Выбрать пункт меню /Блок вывода и задать координаты блока, в который будут выведены результаты регрессионного анализа (на рис. 1 - блок D1..G9). В этом пункте меню достаточно задать координаты верхнего левого угла блока.

6. Выбрать пункт меню /Начало координат и задать вид линии регрессии:

· Нет- линия регрессии не проходит через начало координат, а постоянный член B не равен 0 (см. рис.1.);

· Да- линия регрессии проходит через начало координат, а постоянный член B равен 0.

По умолчанию действует установка НЕТ.

7. Инициировать расчет параметров регрессии с помощью меню /Выполнить, после чего результаты регрессионного анализа автоматически помещаются в блок вывода.

Результаты регрессионного анализа в системе можно интерпретировать в графическом виде. Построим график регрессии для данных, приведенных в табл.1. Для этого в окне меню /График нужно установить следующие настройки:

*  /Тип Графика - выбрать X-Y график;

*  /Определить Серии: X-серия- блок A3..A11;

                                    1-я серия- блок B3..B11;

*  /Настроить Серии/Формат - указать вид вывода "Маркер".

При нажатии клавиши F10 на экран выводится график (рис.2). На нем в виде отдельных точек-маркеров указаны исходные экспериментальные данные.

 

Р и с. 2. График эмпирической регрессии

 

 Для построения линии регрессии необходимо построить в любом столбце (например в столбце H) блок вспомогательных данных, соответствующих выбранному виду уравнения регрессии Y = A*X + B или Y = A*X. Формулы в клетках вспомогательного блока будут иметь следующий вид (табл.4):

 

                                                       Таблица 4

                   Содержимое вспомогательного блока

Адрес ячейки Формула вычислений
        H3         H4          ...         H11 +F$8*A3+$G$2   +F$8*A3+G$2          ... +F$8*A11+G$2

 

Затем в меню /График/Определить Серии для серии 2 (2-я серия) следует определить блок H3..H11, а в меню /Настроить Серии/Формат указать вид вывода "Линии". После этого нажатием клавиши F10 на экран выведется график с экспериментальными точками и линией регрессии (рис.3).

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

*  вместо общепринятого значения коэффициента корреляции R выводится значение R-квадрат, поэтому для получения нормального значения R следует использовать формулу @SQRT(G4);

* значение "Стандартное отклонение Y" (СТО) на самом деле означает ошибку отклонения от регрессии, используемую для ограничения доверительной зоны линии регрессии (в интервале СТО укладывается 68 % всех экспериментальных данных, 2СТО - 95 %). На графике (рис.4) эти интервалы изображены штрих-пунктирной и пунктирной линиями.

 

 

Р и с.3. График с экспериментальными точками и линией регрессии

 

 

Р и с. 4. Доверительная зона линии регрессии

 

К сожалению, формируемая в системе таблица регрессии имеет один существенный недостаток. Она не дает оценки достоверности результатов регрессионного анализа, которая считается необходимой в любых современных методиках обработки экспериментальных данных. Для устранения этого недостатка можно путем несложных вычислений добавить в таблицу проверку достоверности по любой известной методике (например, по t-критерию Стьюдента) на основе уже имеющихся в таблице результатов. Для примера (табл.1) можно дополнительно вычислить коэффициент корреляции R, ошибку коэффициента корреляции Sr и фактическое значение критерия достоверности Стьюдента tf по формулам:

R = @SQRT(G4),

Sr = @SQRT((1-G4)/G6),

tf = @SQRT(G4×G6/(1-G4)).

Эти формулы можно поместить в любые свободные клетки таблицы. Затем по таблице значений критерия Стьюдента для числа степеней свободы указанного в клетке G6, можно найти его теоретическое значение и определить уровень достоверности коэффициента корреляции R.

 

Варианты заданий 

 

 

Вариант 1.

   

Вариант 2.

  п/п Кол-во изделий Отказ  оборудования, %       п/п Кол-во заболеваний Охват профилак- тикой ,%
1 10 25     1 5 60
2 20 20     2 10 50
3 30 15     3 12 40
4 50 10     4 18 30
5 40 5     5 15 20
  ? 30       ? 10

 

Вариант 3.

   

Вариант 4.

  п/п Прибыль тыс руб Износ  оборудования %       п/п Качество продукции % Импортное сырье %
1 25 80     1 40 20
2 20 85     2 30 14
3 18 82     3 25 10
4 10 90     4 15 12
5 15 95     5 10 8
  ? 92       ? 30

 

 

Вариант 5.

   

Вариант 6.

  п/п Урожайность зерновых ц/га Кол-во минер. удобрений ц/га       п/п Урожайность зерновых ц/га Кол-во минер. удобрений ц /га
1 14 0.3     1 15 0.4
2 16 0.5     2 12 0.2
3 15 0.4     3 14 0.5
4 20 0.8     4 15 0.6
5 18 0.6     5 18 0.5
  ? 0.7         0.7

 

Вариант 7.

   

Вариант 8.

  п/п Производство изделий Работа второй смены %       п/п Валов. Произв.  Молока т Доля молока населения %
1 10 5     1 20 70
2 20 15     2 25 80
3 25 10     3 18 60
4 35 20     4 30 90
5 30 25     5 10 50
  ? 35       ? 85

 

Вариант 9.

   

Вариант 10.

  П/п Перевозка пассажиров тыс чел Качество обслуживания %       п/п Прибыль предприятия тыс.руб. Объем используемых основных средств%
1 40 60     1 10 12
2 50 70     2 7 15
3 30 50     3 5 16
4 20 40     4 12 10
5 25 45     5 15 8
  ? 80       ? 5

 


Дата добавления: 2018-04-15; просмотров: 148; Мы поможем в написании вашей работы!

Поделиться с друзьями:






Мы поможем в написании ваших работ!