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

Самый простой способ получить уравнение регрессии и нанести линию регрессии на график — построить диаграмму разброса и вызвать встроенную, автоматическую функцию

Trendline

Линия тренда.

Название этой функции достаточно своеобразное. К нему тоже надо отнестись с пониманием. На самом деле ТРЕНД — это основная тенденция, общее направление при изменении значений во времени. Подробнее мы рассмотрим тренд в третьей работе на тему «Динамика». Фактически здесь строится линия регрессии. При построении линии тренда тоже используют методы регрессионного анализа, только вместо «иксов» берут моменты времени.

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

Щёлкнем по диаграмме разброса левой кнопкой мыши. Как показано на рисунке, справа от поля графика нажмём кнопку со знаком ПЛЮС:

Chart Elements

Элементы диаграммы.

Выбираем в качестве элемента графика

Trendline

Линия тренда.

Указываем линейную функцию:

Linear

Линейный.

Автоматическое построение регрессии

Есть и другой способ управлять элементами графика. Щёлкнем левой кнопкой мыши по графику и выберем в верхнем меню:

Design — Add Chart Element — Trendline — Linear

Конструктор — Добавить элемент диаграммы — Линия тренда — Линейный.

Добавление линии тренда

Добавим на график уравнение регрессии.

Design — Add Chart Element — Trendline — More Trendline options

Конструктор — Добавить элемент диаграммы — Линия тренда — Дополнительные параметры линии тренда.

В окне настройки элементов графика выбираем:

Format Trendline — Trendline options — Display Equation on chart

Формат линии тренда — Параметры линии тренда — показывать уравнение на диаграмме.

Вывод уравнения регрессии

На графике действительно появляется уравнение. Чтобы рассмотреть его получше, перетащим это уравнение мышкой на свободное место.

Уравнение регрессии на графике

Сравним полученное уравнение с нулевым вариантом задания. Значения коэффициентов похожи. Конечно, есть небольшое различие. Это случайная ошибка, вызванная случайным разбросом Еи ограниченным объёмом выборки N.

Кроме линии регрессии, есть другой способ изучения той же зависимости — УСЛОВНОЕ СРЕДНЕЕ, то есть среднее при выполнении некоторого условия. Это среднее арифметическое значений результативного признака YПРИ УСЛОВИИ, что соответствующие значения факторного признака Xпопадают в заданный интервал.

Вот пример условного среднего: средний вес людей, у которых рост окажется в диапазоне от 160 до 170 см. Мы выбираем людей ростом от 160 до 170 см, измеряем их вес и находим среднее значение веса только по этой группе. Здесь рост — это факторный признак Х, а вес — это результативный признак Y. Мы получили средний «игрек», а условие определяли по «иксу».

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

В нашем примере возьмём 10 интервалов по 100 единиц, чтобы охватить диапазон значений от 1000 до 2000.

Для нахождения условного среднего можно использовать функцию

SUMIF

СУММЕСЛИ.

Функция позволяет вычислить сумму при выполнении заданного условия. Формат функции следующий:

SUMIF (range, criteria, [sum_range])

СУММЕСЛИ (диапазон; критерий; [диапазон_суммирования])

range — диапазон — диапазон ячеек;

criteria — критерий — условие ;

sum_range — диапазон_суммирования — диапазон ячеек для суммирования. Если диапазон не указан, то суммируются значения из диапазона, указанного в первом аргументе.

Для определения средних значений фактора Хв каждом интервале группировки используем только два первых аргумента функции.

Рассмотрим примеры условных сумм — см. формулы.

Условные суммы

Первая формула вычисляет сумму значений фактора, не превышающих верхнюю границу первого интервала. Сюда попадут все значения из первого интервала, а также все точки, которые окажутся левее этого интервала.

Вторая формула определяет сумму значений фактора, попадающих во второй интервал.

В третьей формуле мы фиксируем номера строк для диапазона исходных данных с помощью символа $. Это позволит нам скопировать формулу и заполнить весь столбец.

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

Функция SUMIF

При вычислении среднего арифметического нужно поделить сумму значений на их количество.

Для определения количества элементов используем функцию

COUNTIF

СЧЕТЕСЛИ.

Формула для расчета условного среднего фактора Хполучается довольно громоздкой — см. формулы.

Расчёт условного среднего

Изучите формулы и найдите следующие элементы:

— диапазон ячеек от А2до А121на листе 04;

— верхняя граница первого интервала на листе 05;

— верхняя граница второго интервала на листе 05.

Чтобы не запутаться, проведём наши расчёты по частям. Сначала найдём суммы и количества значений Х,не превышающих верхней границы. Затем определим разности соседних ячеек. Затем проведём деление и в результате получим среднее значение Хв каждом интервале группировки.

Вычисление условного среднего значения результативного признака Yнемного сложнее. Здесь проверяется условие попадания факторного признака Хв интервал группировки, а сумма считается по столбцу результативного признака Y. Для этого используется третий аргумент функции SUMIF — см. формулу.

Условное среднее Y (X)

Для копирования формулы фиксируем номера строк с помощью знака $.

Вычисление условного среднего

После вычислений наносим линию условного среднего на диаграмму разброса. Для этого нам потребуется ломаная линия с маркерами точек.