Задание:  1. Закрепите практически выполнение корреляционного и регрессионного анализа выполнил приведенный ниже алгоритм в Microsoft Office Excel. Выполните анализ полученных результатов (значимость, точность) и дайте краткое разъяснение в предметной области (сельское хозяйство). 2. Постройте задание для решения задач вашей магистерской диссертации методом коррелляционного и регрессионного анализа. Опишите исходные данные (число факторов и их название, длины выборок). Какие ожидаются результаты применения методов. 

 Алгоритм проведения корреляционного и регрессионного  анализов в Microsoft Office Excel

Проверим зависимость между балом пашни (x) и урожайностью многолетних трав (y), для чего набираем в ячейках A2:K3 следующие данные:

Строим точечную диаграмму: выделяем набранную таблицу (ячейки A2–K3) и жмем на пиктограмму  на панели инструментов или Вставка– Диаграмма, в закладке Стандартные выбираем Точечная и первый сверху из имеющихся примеров жмем Далее, в закладке Диапазон данных отмечаем Ряды в строках – Далее. В закладке Заголовки в окошке Ось Х (категорий) набираем «Балл пашни» (может отличаться для различных индивидуальных заданий, в этом случае пишется название первого сравниваемого параметра), в окошке Ось Y (значений) «Урожайность многолетних трав», в закладке Легенда снимаем галочку с показателя «Добавить легенду»– Далее – Поместить диаграмму на имеющемся листе – Готово.

Добавляем линию тренда, для чего кликаем на маркере точки данных правой клавишей и выбираем пункт Добавить линию тренда (см. рис. 4).

Рис. 4

В закладке Тип выбирается Линейная, в закладке Параметры отмечаются пункты показывать уравнение на диаграмме и поместить на диаграмму величину достоверной аппроксимации – ОК. В итоге будет построена линия тренда и составлено уравнение линейной регрессии. Находим артефакты – значения, которые сильно отдалены от линии тренда и не вписываются в общую картину (рис. 5). Более правильно выявлять артефакт через расчеты приведенные в п. 1.2. Удаляем эти значения из таблицы данных (в указанном примере случае очищаются от цифр ячейки С2:С3), MS Excel автоматически пересчитает уравнение регрессии. В некоторых случаях (при нелинейной корреляции), можно использовать другие варианты линий тренда, например логарифмическую, степенную или экспоненциальную.

Рис. 5

Рассчитываем коэффициент корреляции установив курсор в клетку В5 используя команду КОРРЕЛ: Вставка – Функция (или иконка f_x  на панели инструментов) – выбираем в категориях Статистические – функцию КОРРЕЛ – сворачиваем появившееся окно нажатием на кнопку  напротив поля Массив 1. Выделяем ячейки со значениями х (B2:К2), далее в поле Массив 2 выделяем ячейки со значениями y (В3:К3), разворачиваем окно, нажав на эту же кнопку и жмем ОК.

Оцениваем значимость коэффициента корреляции (r) по критерию Стьюдента по формуле  и сравниваем с табличным (критическим) значением, если фактическое значение больше критического, то корреляционная связь существенна, если меньше – недостоверна (вид формул на рис. 6).

Рис. 6

Регрессионный анализ проводится с помощью надстройки «Пакет анализа», для последовательность команд Сервис – Анализ данных – Регрессия, в поле «Входной интервал» указываем значения для Y и Х (A3:K3 и A2:K2 соответственно), в «Параметрах вывода» выбираем «Выходной интервал» и указываем там ячейку на этом же листе, отмечаем параметры «Уровень надежности» (значение можно изменять, в нашем случае указываем 95%), нажимаем [OK]. Если удалялся артефакт, то необходимо скопировать первоначальные значения в другие ячейки, поскольку значения во входном интервале должны быть непрерывными.