Решение типовой задачи в среде Excel

2.1. Постановка задачи*

Исследуется зависимость добычи угля на 1 рабочего (Y) от толщины угольного пласта (Х) по данным, представленным в таблице 1.

 

Таблица 1. Зависимость добычи угля от толщины угольного пласта

№ шахты
Толщина пласта (м)
Добыча угля /1рабочий (усл. ед.)

 

Требуется:

1. Построить поле корреляции. Визуально определить, можно ли зависимость Y (X) считать линейной.

2. Оценить функцию линейной регрессии Y по X. Пояснить, в чем смысл ее параметров.

3. На поле корреляции показать линию регрессии.

4. Дать прогноз добычи угля на одного рабочего для двух открываемых шахт: со значениями толщины пласта 8 м и 15 м.

5. Определить абсолютное значение коэффициента корреляции между переменными Х и Y. Является ли связь между этими переменными тесной?

6. Определить остаточную, общую и регрессионную суммы квадратов. Пояснить их смысл. Найти коэффициент детерминации и пояснить его смысл.

7. Используя F -статистику, оценить на уровне a=0, 05 значимость полученного уравнения регрессии.

8. По критерию Стьюдента проверить значимость коэффициента линейной регрессии.

Выполнение задания в среде Excel

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

Поле корреляции строится как точечная диаграмма. Для построения достаточно выделить две строки таблицы данных, содержащие координаты точек (x, y), любым способом вызвать мастер диаграмм, выбрать тип диаграммы «точечная» и далее следовать мастеру. По расположению точек наблюдений (см. рис.3) можно предположить наличие линейной связи между X и Y.