Цель работы: научиться использовать возможности MS Excel для проведения статистического анализа связей.
ЗАДАНИЕ 1.
Известны данные о размере потребительских расходов на продукты питания в среднем на члена домашнего хозяйства, руб. в месяц, и динамика среднедушевых доходов населения с 2004 по 2017 годы.
| Годы | |||||||
| Расходы, руб. | 1 206,40 | 1 406,20 | 1 604,00 | 1 857,30 | 2 393,60 | 2 651,00 | 2 999,20 |
| Доходы, руб. | 6 399,00 | 8 088,30 | 10 154,80 | 12 540,20 | 14 863,60 | 16 895,00 | 18 958,40 |
| Годы | |||||||
| Расходы, руб. | 3 324,50 | 3 551,95 | 3 794,01 | 4 171,22 | 4 719,10 | 5 193,00 | 5 230,30 |
| Доходы, руб. | 20 780,0 | 23 221,10 | 25 928,20 | 27 766,60 | 30 466,60 | 30 747,00 | 31 477,40 |
Необходимо оценить тесноту связи между признаками и составить уравнение регрессии расчетным методом, с помощью встроенных функций, с помощью анализа данных.
1. Определим что является факторным признаком, что результативным. В данном случае возможность расходования средств зависит от доходов, поэтому в качестве переменной Х выберем доходы, а переменной Y - расходы
2. Построим поле корреляции. Каждая пара наблюдений (расходы и доходы за определенный год) отображается на плоскости в виде точки с соответствующими координатами. Совокупность таких точек называется полем корреляции. Чтобы построить в Excel такой график, по виду которого можно выдвинуть гипотезу о форме связи исследуемых величин, выберем на ленте интерфейса (вкладка Вставка, группа Диаграммы) тип диаграммы Точечная с маркерами:
2.
Рисунок 1 – Поле корреляции
Построенная диаграмма позволяет сделать вывод о линейной связи между переменными.
3. Параметры уравнения регрессии y=a+bx рассчитываются по формулам:
Для определения параметров регрессии составим расчетную таблицу и вычислим коэффициенты a и b:
Рисунок 2 – Расчетная таблица для определения параметров регрессии
Рисунок 3 – Результаты расчета коэффициентов регрессии
Таким образом, уравнение парной линейной регрессии имеет вид:
y=43,45+0,156x
В этой модели наклон b найденной прямой представляет собой количество единиц измерения переменной Y, приходящихся на одну единицу измерения переменно й X. Эта величина характеризует среднюю величину изменения переменной Y (положительного или отрицательного) на заданном отрезке оси X. Сдвиг а представляет собой среднее значение переменной Y, когда переменная X равна 0. Таким образом, в данном случае мы видим, что если значения доходов увеличить на 1 рубль, значение расходов на продукты питания вырастет на 0,156 рубля.
4. Уравнение регрессии всегда дополняется показателем тесноты связи изучаемых факторов. Для линейной регрессии в качестве такого показателя выступает линейный коэффициент парной корреляции 
Где
= 
, 
Знак коэффициента характеризует направление, а абсолютная величина - тесноту связи.
Качественно оценить тесноту линейной корреляционной связи между x и y можно с помощью таблицы Чеддока:
| Диапазон изменения | Характер тесноты связи |
| 0,1 – 0,3 | слабая |
| 0,3 – 0,5 | умеренная |
| 0,5 – 0,7 | заметная |
| 0,7 – 0,9 | высокая |
| 0,9 – 0,99 | весьма высокая |
Определим тесноту связи между переменными (найдем коэффициент корреляции). Почти все необходимые данные для расчета у нас имеются в расчетной таблице, кроме значения 
. Дополним таблицу столбцом y2.
Рисунок 4 – Таблица для расчета коэффициента корреляции
Находим среднее квадратическое отклонение для обеих переменных, вносим в заранее подготовленную ячейку, а затем вычисляем коэффициент корреляции.
Рисунок 5 – Расчет средних квадратических отклонений и коэффициента корреляции
Рисунок 6 – Результаты расчета
Таким образом, связь прямая, очень высокая. При анализе среднедушевых доходов становится понятным, что чем выше доходы, тем больше человек тратит на продукты питания. Однако, если мы в нашей расчетной таблице заменим столбец абсолютных значений расходов на продукты питания столбцом с долей расходов на питание в общих доходах, то получим следующее:
Рисунок 7 – Расчет корреляции при замене абсолютного значения расходов на долю в доходах
Как видно из расчетов, коэффициент корреляции стал отрицательным. Это означает, что чем выше доходы, тем больше человек может позволить себе расходов на товары непродовольственной группы и иные расходы (связь обратная, умеренная).
5. Рассмотрим расчет необходимых показателей с помощью встроенных функций.
Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую.
Уравнение для прямой линии имеет следующий вид:
y = mx + b
Синтаксис функции: ЛИНЕЙН(известные_значения_y; [известные_значения_x]; [конст]; [статистика])
Известные_значения_y: множество значений y, которые известны для соотношения y = mx + b.
Известные_значения_x.: множество значений x, которые уже известны для соотношения y = mx + b.
Конст. Необязательный аргумент. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если аргумент конст имеет значение ИСТИНА или опущен, то константа b вычисляется обычным образом. Если аргумент конст имеет значение ЛОЖЬ, то значение b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.
Статистика. Необязательный аргумент. Логическое значение, которое указывает, требуется ли возвратить дополнительную регрессионную статистику. В данном исследовании этим параметром мы пользоваться не будем.
Данную формулу необходимо вводить как формулу массива из двух значений по строке (необходимо выделить две ячейки горизонтально, ввести формулу массива и нажать Ctrl+Shift+Enter).
Рисунок 8 – Ввод функции ЛИНЕЙН
Рисунок 9– Параметры линейного уравнения регрессии
Таким образом, уравнение парной линейной регрессии имеет вид:
y=43,45+0,156x
Если необходимо отобразить дополнительные статистики, выделите диапазон на один столбец больше, чем столбцов с переменными х, и высотой 5 строк. Поскольку у нас лишь одна переменная х, выделим диапазон 2 столбца по 5 строк. Третьему и четвертому аргументам присвоим значения ИСТИНА, чтобы b считалось обычным образом, и для вывода дополнительных статистик. После ввода формулы нажатием Ctrl+Shift+Enter, результат должен соответствовать рисунку ниже:
Рисунок 10 – Вывод дополнительных статистик
Параметры регрессионной статистики для линейной модели выводятся в следующем порядке:
Рисунок 11 – Вывод регрессионной статистики
Рассчитаем коэффициент корреляции с помощью функции КОРРЕЛ. Синтаксис функции имеет следующий вид:
КОРРЕЛ(массив1;массив2), где Массив1 — диапазон ячеек со значениями переменной х, Массив2 —диапазон ячеек со значениями y.
В нашем случае формула будет выглядеть следующим образом:
Расчет коэффициента дает результат 0,991561092.
6. Проведем регрессионный анализ с использованием Анализа данных/Регрессия. Значения параметров, установленных в одноименном диалоговом окне, представлены на рисунке.
Рисунок 12 – Диалоговое окно «Регрессия»
После нажатия «ОК», программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).
Рисунок 13 – Вывод регрессионного анализа
Из множества полученных коэффициентов выбираем необходимые нам:
Множественный r – коэффициент корреляции;
r2 – это квадрат коэффициента корреляции, он показывает сколько процентов вариации результата объясняется изменчивостью признака. То есть в нашем случае 98% вариации расходов на продукты питания объясняется изменчивостью объемов доходов. Кроме того, сдвиг b0 переменной Y равен –16,032, а наклон b1 = 0,0308;
первые два коэффициента по столбцу в последней таблице – это характеристики регрессионной прямой.
Добавим линию тренда на поле корреляции:
Рисунок 14 – Добавление линии тренда
В диалоговом окне выбираем линейную линию тренда, ставим флажки на «Показывать уравнение на диаграмме».
Рисунок 15 – Линия тренда на поле корреляции
ЗАДАНИЕ 2.
На сайте Росстата выберите две переменные, которые могут совместно изменяться. Обоснуйте (логически) это совместное изменение. Для расчета коэффициента корреляции и нахождения уравнения регрессии выберите не менее 10 показателей каждого признака (не менее 10 временных периодов). Рассчитайте необходимые показатели расчетным путем и с помощью функций. Постройте поле корреляции.
Лабораторная работа №7