ОДНОФАКТОРНЫЙ РЕГРЕССИОННЫЙ АНАЛИЗ

1. Создайте файл исходных данных в MS Excel (например, таблица 2)

2. Построение корреляционного поля

Для построения корреляционного поля в командной строке выбираем меню Вставка/ Диаграмма. В появившемся диалоговом окне выберите тип диаграммы: Точечная; вид: Точечная диаграмма, позволяющая сравнить пары значений (Рис. 22).

Рисунок 22 – Выбор типа диаграммы

Нажимаем кнопку Далее>. В появившемся диалоговом окне (Рис. 23) указываем диапазон значений, в нашем примере = Лист1!A2:B26 и указываем расположение данных: в столбцах.

Нажимаем кнопку Далее>. В следующем диалоговом окне (рис. 24) указываем название диаграммы, наименование осей. Нажимаем кнопку Далее>, и Готово.

Таким образом, получаем корреляционное поле зависимости y от x. Далее добавим на графике линию тренда, для чего выполним следующие действия:

1. В области диаграммы щелкнуть левой кнопкой мыши по любой точке графика, затем щелкнуть правой кнопкой мыши по этой же точке. Появляется контекстное меню (рис. 25).

2. В контекстном меню выбираем команду Добавить линию тренда.

3. В появившемся диалоговом окне выбираем тип графика (в нашем примере линейная) и параметры уравнения, как показано на рисунке 26.

Рисунок 27 – Корреляционное поле зависимости производительности труда от фондовооруженности

Аналогично строим корреляционное поле зависимости производительности труда от коэффициента сменности оборудования. (рисунок 28).

от коэффициента сменности оборудования

3. Построение корреляционной матрицы.

Для построения корреляционной матрицы в меню Сервис выбираем Анализ данных.

С помощью инструмента анализа данных Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Для этого необходимо проверить доступ к пакету анализа. В главном меню последовательно выберите Сервис/ Надстройки. Установите флажок Пакет анализа (Рисунок 29)

В диалоговом окне Анализ данных выбираем Корреляция(Рисунок 30).

После нажатия ОК в появившемся диалоговом окне указываем входной интервал (в нашем примере А2:D26), группирование (в нашем случае по столбцам) и параметры вывода, как показано на рисунке 31.

Результат расчетов представлен в таблице 4.

Таблица 4 – Корреляционная матрица

ОДНОФАКТОРНЫЙ РЕГРЕССИОННЫЙ АНАЛИЗ

С ПРИМЕНЕНИЕМ ИНСТРУМЕНТА РЕГРЕССИИ

Для проведения регрессионного анализа зависимости производительности труда от фондовооруженности в меню Сервис выбираем Анализ данных и указываем инструмент анализа Регрессия (Рисунок 32).

После нажатия ОК в диалоговом окне Регрессия указываем входной интервал Y (в нашем примере А2: А26) и входной интервал X (в нашем примере B2: B26), а также параметры вывода, остатки, нормальную вероятность как указано на рисунке 33.

Результат расчетов представлен в приложении В.

Аналогично производится парный регрессионный анализ по второй переменной (Приложение Г).

С ПРИМЕНЕНИЕМ “МАСТЕРА ФУНКЦИЙ”

Однофакторный регрессионный анализ можно осуществлять с помощью “Мастера функций” ППП Excel.

Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии y=a+bx. Порядок вычисления следующий:

1) введите исходные данные или откройте существующий файл, содержащий анализируемые данные;

2) выделите область пустых ячеек 5х2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1х2 – для получения только оценок коэффициентов регрессии;

3) активизируйте Мастер функций любым из способов:

а) в главном меню выберите Вставка/ Функция;

б) на панели инструментов Стандартная щелкните по кнопке Вставка функции;

4) в окне Категория (рис. 34) выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните по кнопке ОК;

5) заполните аргументы функции (рис. 35):

Известные_значения_y – диапазон, содержащий данные результативного признака;

Известные_значения_x – диапазон, содержащий данные факторов независимого признака;

Константа – логическое значение, которое указывает на наличие или отсутствие свободного члена в уравнении; если Константа = 1, то свободные член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;

Статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щелкните по кнопке ОК;

6) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите клавишу <F2>, а затем – на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>.

Рисунок 34 – Диалоговое окно Мастер функций

Рисунок 35 – Диалоговое окно ввода аргументов функции ЛИНЕЙН

Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:

Таблица 5

Для вычисления параметров экспоненциальной кривой в MS Excel применяется встроенная статистическая функция ЛГРФПРИБЛ. Порядок вычисления аналогичен применению функции ЛИНЕЙН.

Для данных из нашего примера результат вычисления функции ЛИНЕЙНпредставлен на рис. 36, функции ЛГРФПРИБЛ– на рис. 37.

Рисунок 36 – Результат вычисления функции ЛИНЕЙН