КОВАРИАЦИЯ.В • Excel-Translator

Примеры использования функции КОРРЕЛ в Excel

Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.

Чтобы упростить ее понимание, разобьем на несколько несложных элементов.

  1. Найдем средние значения переменных, используя функцию СРЗНАЧ:
  2. СРЗНАЧ.

  3. Посчитаем разницу каждого y и yсредн., каждого х и хсредн. Используем математический оператор «-».
  4. Разница.

  5. Теперь перемножим найденные разности:
  6. Умножение разниц.

  7. Найдем сумму значений в данной колонке. Это и будет числитель.
  8. Сумма значений.

  9. Для расчета знаменателя разницы y и y-средн., х и х-средн. Нужно возвести в квадрат.
  10. Квадрат.

  11. Находим суммы значений в полученных колонках (с помощью функции АВТОСУММА). Перемножаем их. Результат возводим в квадрат (функция КОРЕНЬ).
  12. АВТОСУММА.

  13. Осталось посчитать частное (числитель и знаменатель уже известны).

Между переменными определяется сильная прямая связь.

Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.

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

Зарплата сотрудников.

Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».

  1. На вкладке «Данные» в группе «Анализ» открываем пакет «Анализ данных» (для версии 2007). Если кнопка недоступна, нужно ее добавить («Параметры Excel» – «Надстройки»). В списке инструментов анализа выбираем «Корреляция».
  2. Анализ данных.

  3. Нажимаем ОК. Задаем параметры для анализа данных. Входной интервал – диапазон ячеек со значениями. Группирование – по столбцам (анализируемые данные сгруппированы в столбцы). Выходной интервал – ссылка на ячейку, с которой начнется построение матрицы. Размер диапазона определится автоматически.
  4. Корреляция.

  5. После нажатия ОК в выходном диапазоне появляется корреляционная матрица. На пересечении строк и столбцов – коэффициенты корреляции. Если координаты совпадают, то выводится значение 1.

Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.

Изобразим наглядно корреляционные отношения с помощью графиков.

  1. Сильная прямая связь между y и х1.
  2. Сильная прямая связь.

  3. Сильная обратная связь между y и х2. Изменения значений происходят параллельно друг другу. Но если y растет, х падает. Значения y увеличиваются – значения х уменьшаются.
  4. Сильная обратная связь.

  5. Отсутствие взаимосвязи между значениями y и х3. Изменения х3 происходят хаотично и никак не соотносятся с изменениями y.

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

Если связь имеется, то влечет ли увеличение одного параметра повышение (положительная корреляция) либо уменьшение (отрицательная) другого. Корреляционный анализ помогает аналитику определиться, можно ли по величине одного показателя предсказать возможное значение другого.

Коэффициент корреляции обозначается r. Варьируется в пределах от 1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

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

Ставим курсор в любую ячейку и нажимаем кнопку fx.

  1. В категории «Статистические» выбираем функцию КОРРЕЛ.
  2. Аргумент «Массив 1» – первый диапазон значений – время работы станка: А2:А14.
  3. Аргумент «Массив 2» – второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.

Чтобы определить тип связи, нужно посмотреть абсолютное число коэффициента (для каждой сферы деятельности есть своя шкала).

Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.

Пример 1.

На практике эти две методики часто применяются вместе.

Пример:

  1. Строим корреляционное поле: «Вставка» – «Диаграмма» – «Точечная диаграмма» (дает сравнивать пары). Диапазон значений – все числовые данные таблицы.
  2. Поле корреляции.

  3. Щелкаем левой кнопкой мыши по любой точке на диаграмме. Потом правой. В открывшемся меню выбираем «Добавить линию тренда».
  4. Добавить линию тренда.

  5. Назначаем параметры для линии. Тип – «Линейная». Внизу – «Показать уравнение на диаграмме».
  6. Линейная линия тренда.

  7. Жмем «Закрыть».

Теперь стали видны и данные регрессионного анализа.

Пример 1. В таблице Excel содержатся данные о курсе доллара и средней зарплате сотрудников фирмы на протяжении нескольких лет. Определить взаимосвязь между курсом валюты и средней зарплатой.

Описание аргументов:

  • B3:B13 – диапазон ячеек, в которых хранятся данные о среднем курсе доллара;
  • C3:C13 – диапазон ячеек со значениями средней зарплаты.

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

Пример 2. Два сильных кандидата на руководящий пост воспользовались услугами двух различных пиар-агентств для запуска предвыборной компании, которая длилась 15 дней. Ежедневно проводился соцопрос независимыми исследователями, которые определяли процент поддержки одного и второго кандидата. Респонденты могли отдавать предпочтение первому, второму кандидату или выступать против обоих. Определить, насколько влияла каждая предвыборная кампания на степень поддержки кандидатов, какая из них оказалась более эффективной?

=КОРРЕЛ(A3:A17;B3:B17)

=КОРРЕЛ(A3:A17;C3:C17)

  • A3:A17 – массив ячеек, содержащий номера дней предвыборной кампании;
  • B3:B17 и C3:C17 – диапазон ячеек, содержащие данные о проценте поддержки первого и второго кандидатов соответственно.

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

Однако затем уровень поддержки стал снижаться, и к 15-му дню упал ниже начального значения. Отрицательное значение коэффициента корреляции свидетельствует о негативном эффекте кампании. Однако на события могли оказывать влияние различные факторы, например, опубликованные компрометирующие материалы.

=КОРРЕЛ(массив1;массив2)

  • массив1 – обязательный аргумент, содержащий диапазон ячеек или массив данных, которые характеризуют изменения свойства какого-либо объекта.
  • массив2 – обязательный аргумент (диапазон ячеек либо массив), элементы которого характеризуют изменение свойств второго объекта.

Примечания 1:

  1. Функция КОРРЕЛ не учитывает в расчетах элементы массива или ячейки из выбранного диапазона, в которых содержатся данные текстового или логического типов. Пустые ячейки также игнорируются. Текстовые представления числовых значений учитываются.
  2. Если необходимо учесть логические ИСТИНА или ЛОЖЬ в качестве числовых значений 1 или 0 соответственно, можно выполнить явное преобразование данных используя двойное отрицание «–».
  3. Размерности массив1 и массив2 или количество ячеек, переданных в качестве этих двух аргументов, должны совпадать. Если аргументы содержат разное количество точек данных, например, =КОРРЕЛ({1;2;3};{4;6;8;10}), результатом выполнения функции будет код ошибки #Н/Д.
  4. Если один из аргументов представляет собой пустой массив или массив нулевых значений, функция КОРРЕЛ вернет код ошибки #ДЕЛ/0!. Аналогичный результат выполнения данной функции будет достигнут в случае, если стандартное отклонение распределения величин в одном из массивов (массив1, массив2) равно 0 (нулю).
  5. Функция КОРРЕЛ производит расчет коэффициента корреляции по следующей формуле:
  6. расчет коэффициента корреляции по формуле.

Примечание 2: Коэффициент корреляции представляет собой количественную характеристику степени взаимосвязи между двумя свойствами объектов. Этот коэффициент может принимать значения из диапазона от -1 до 1, при этом:

  1. Если значение коэффициента приближается к 1 или -1, между двумя исследуемыми свойствами существует сильная прямая или обратная взаимосвязи соответственно.
  2. Если значение коэффициента стремится к 0,5 или -0,5, два свойства слабо прямо или обратно взаимосвязаны друг с другом соответственно.
  3. Если коэффициент корреляции близок к 0 (нулю), между двумя исследуемыми свойствами отсутствует прямая либо обратная взаимосвязи.

КОВАРИАЦИЯ.В • Excel-Translator

Примечание 3: Для понимания смысла коэффициента корреляции можно привести два простых примера:

  1. При нагреве вещества количество теплоты, содержащееся в нем, будет увеличиваться. То есть, между температурой и количеством теплоты (физическая величина) существует прямая взаимосвязь.
  2. При увеличении стоимости продукции спрос на нее уменьшается. То есть, между ценой и покупательной способностью существует обратная взаимосвязь.

Регрессионный анализ в Excel

Показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.

Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.

Регрессия бывает:

  • линейной (у = а bx);
  • параболической (y = a bx cx2);
  • экспоненциальной (y = a * exp(bx));
  • степенной (y = a*x^b);
  • гиперболической (y = b/x a);
  • логарифмической (y = b * 1n(x) a);
  • показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

У = а0 а1х1 … акхк.

Показатели x и y.

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

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

Активируем мощный аналитический инструмент:

  1. Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки».
  2. Надстройки.

  3. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем.
  4. Управление.

  5. Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК.

После активации надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

  1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия».
  2. Регрессия.

  3. Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять.
  4. Параметры регрессии.

  5. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

Пакет анализа.

R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.

Поделиться:
Нет комментариев

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть
Adblock detector