1,0.
В электронной таблице формулы пересчитываются автоматически, а графики сами обновляются при изменении данных. Поэтому можно будет легко подобрать нужный разброс. Скопируйте графики и соберите их на отдельном листе с комментариями — какая корреляция и какая это теснота связи. При вставке графиков используйте режим вставки как изображение — Picture (U), а не как исходный график. В этом случае картинки не будут изменяться и обновляться.
Формулы
Вычислим коэффициент линейной корреляции вручную с помощью формул Excel.
Вот соотношение для расчётов — см. формулу.
Коэффициент корреляции
Для вычислений нам понадобятся промежуточные расчёты. Найдём суммы «иксов», «игреков», их квадратов и произведений, которые участвуют в формуле. Для этого на новом листе организуем вспомогательную таблицу. Внизу столбцов подсчитываем суммы, воспользовавшись кнопкой экспресс-анализа.
Промежуточные суммы
Когда найдены необходимые суммы, можно вычислить коэффициент корреляции. Нам потребуется функция извлечения квадратного корня:
SQRT (number)
КОРЕНЬ (число).
Формула не слишком сложная. При вводе в ячейку она легко умещается на экране. Поэтому разбивать её на части не потребуется.
Коэффициент корреляции
Сравнение результатов
Копируем полученное значение на отдельный лист для сравнения с предыдущими оценками. Записываем комментарии и сообщаем, насколько похожи оценки, полученные разными способами. А также, о чём говорят величина и знак коэффициента r — см. таблицу выше.
Регрессионный анализ
Переходим к регрессионному анализу. В статистике и бизнес-аналитике РЕГРЕССИЯ — это линия, которую проводят В СРЕДНЕМ по точкам. Кроме изображения линии на графике, здесь рассматривается уравнение этой линии. Задача регрессионного анализа — построить линию регрессии и получить уравнение регрессии.
ЛИНИЯ РЕГРЕССИИ проходит по большому количеству точек именно В СРЕДНЕМ. Она может не пройти ни через одну точку. Но на графике будет видно, как линия проходит по местам сгущения точек. Можно даже провести такую линию «на глазок», просто приложив линейку к графику.
УРАВНЕНИЕ РЕГРЕССИИ описывает нашу линию, которая проходит по точкам в среднем.
Если у нас прямая линия, а «икс» входит в уравнение в первой степени, то это ЛИНЕЙНОЕ УРАВНЕНИЕ — см. формулу.
Линейная регрессия
Для проведения регрессионного анализа в Excel имеется несколько способов:
— элемент диаграммы;
— статистическая надстройка;
— функция LINEST (ЛИНЕЙН);
— формулы с матричными операциями.
Уравнение регрессии и соответствующая линия регрессии — это пример ПАРАМЕТРИЧЕСКОЙ модели. В такой модели участвует небольшое, ограниченное количество параметров. В нашем случае несколько коэффициентов уравнения.
Существует и второй тип моделей — НЕПАРАМЕТРИЧЕСКИЕ. В таких моделях вместо красивого уравнения используется таблица с неограниченным количеством чисел или множество точек на графике. И это количество может меняться. В нашей работе мы рассмотрим пример непараметрической модели регрессии под названием УСЛОВНОЕ СРЕДНЕЕ. Мы построим эту модель с помощью методов сводки и группировки данных — этот подход подробно рассматривался в предыдущей работе.
Элемент диаграммы
Самый простой способ получить уравнение регрессии и нанести линию регрессии на график — построить диаграмму разброса и вызвать встроенную, автоматическую функцию
Trendline
Линия тренда.
Название этой функции достаточно своеобразное. К нему тоже надо отнестись с пониманием. На самом деле ТРЕНД — это основная тенденция, общее направление при изменении значений во времени. Подробнее мы рассмотрим тренд в третьей работе на тему «Динамика». Фактически здесь строится линия регрессии. При построении линии тренда тоже используют методы регрессионного анализа, только вместо «иксов» берут моменты времени.
Слово РЕГРЕССИЯ здесь более подходящее, оно более универсальное, хотя и незнакомое для массового потребителя. Видимо, разработчики решили, что слово ТРЕНД более привычно для пользователей. Оно часто встречается в рекламе и даже в названиях некоторых товаров.