Валентин Арьков - Статистический анализ взаимосвязи в Excel

Grouped By — Columns

Группирование — по столбцам.

Обратите внимание, что здесь имеется в виду расположение данных по столбцам, а не статистическая группировка, хотя на экране и присутствует слово ГРУППИРОВАНИЕ. Как говорил Козьма Прутков: «Не верьте глазам своим». Мы пока что просто описываем исходные данные и даже не начинали заниматься группировкой.

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

Output options — Output Range

Параметры вывода — Выходной интервал.

Результаты корреляционного анализа

На экран выводится таблица коэффициентов корреляции. На пересечении строки Yи столбца Хвыводится искомый коэффициент. Единичные коэффициенты на диагонали — это корреляция переменной с самóй собой.

Чтобы получить больше разрядов в дробной части, увеличим ширину столбца.

Точное значение коэффициента

Второй способ вычисления коэффициента корреляции — это готовая функция

CORREL (array1, array2)

КОРРЕЛ (диапазон_x; диапазон_y).

Два обязательных аргумента — это диапазоны ячеек Xи Y. Здесь «иксы» и «игреки» задаются по отдельности. Напомним, что в английской версии программы аргументы функции разделяют запятой, а в русской — точкой с запятой.

Вызов функции CORREL

Увеличиваем ширину столбца и сравниваем результаты расчётов с предыдущим разделом. Пока всё сходится.

Теперь на новом листе сгенерируйте данные с разным разбросом, то есть с разным множителем Sв уравнении. Определите значение коэффициента корреляции. Подберите величину случайного разброса, чтобы получить

0,3

0,5

0,7

1,0.

В электронной таблице формулы пересчитываются автоматически, а графики сами обновляются при изменении данных. Поэтому можно будет легко подобрать нужный разброс. Скопируйте графики и соберите их на отдельном листе с комментариями — какая корреляция и какая это теснота связи. При вставке графиков используйте режим вставки как изображение — Picture (U), а не как исходный график. В этом случае картинки не будут изменяться и обновляться.

Вычислим коэффициент линейной корреляции вручную с помощью формул Excel.

Вот соотношение для расчётов — см. формулу.

Коэффициент корреляции

Для вычислений нам понадобятся промежуточные расчёты. Найдём суммы «иксов», «игреков», их квадратов и произведений, которые участвуют в формуле. Для этого на новом листе организуем вспомогательную таблицу. Внизу столбцов подсчитываем суммы, воспользовавшись кнопкой экспресс-анализа.

Промежуточные суммы

Когда найдены необходимые суммы, можно вычислить коэффициент корреляции. Нам потребуется функция извлечения квадратного корня:

SQRT (number)

КОРЕНЬ (число).

Формула не слишком сложная. При вводе в ячейку она легко умещается на экране. Поэтому разбивать её на части не потребуется.

Коэффициент корреляции

Копируем полученное значение на отдельный лист для сравнения с предыдущими оценками. Записываем комментарии и сообщаем, насколько похожи оценки, полученные разными способами. А также, о чём говорят величина и знак коэффициента r — см. таблицу выше.

Переходим к регрессионному анализу. В статистике и бизнес-аналитике РЕГРЕССИЯ — это линия, которую проводят В СРЕДНЕМ по точкам. Кроме изображения линии на графике, здесь рассматривается уравнение этой линии. Задача регрессионного анализа — построить линию регрессии и получить уравнение регрессии.

ЛИНИЯ РЕГРЕССИИ проходит по большому количеству точек именно В СРЕДНЕМ. Она может не пройти ни через одну точку. Но на графике будет видно, как линия проходит по местам сгущения точек. Можно даже провести такую линию «на глазок», просто приложив линейку к графику.

УРАВНЕНИЕ РЕГРЕССИИ описывает нашу линию, которая проходит по точкам в среднем.

Если у нас прямая линия, а «икс» входит в уравнение в первой степени, то это ЛИНЕЙНОЕ УРАВНЕНИЕ — см. формулу.

Линейная регрессия

Для проведения регрессионного анализа в Excel имеется несколько способов:

— элемент диаграммы;

— статистическая надстройка;

— функция LINEST( ЛИНЕЙН);

— формулы с матричными операциями.

Уравнение регрессии и соответствующая линия регрессии — это пример ПАРАМЕТРИЧЕСКОЙ модели. В такой модели участвует небольшое, ограниченное количество параметров. В нашем случае несколько коэффициентов уравнения.