Рубрики
Анализ

Как вынести формулу корреляции в ячейку

Ранее на этом сайте я публиковал статью о том, как построить достоверную статистическую корреляцию стандартными средствами Microsoft Excel. Статья вызвала интерес у читателей, и я решил немного развить тему.

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

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

Второй способ — с помощью встроенных функций листа. Нам потребуются функции SLOPE (НАКЛОН) и INTERCEPT (ОТРЕЗОК). У обоих функций абсолютно одинаковый синтаксис: в скобках необходимо указать два отрезка данных — значения Y и значения X.

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

Если вы часто сталкиваетесь с подобными задачами в работе, я настоятельно рекомендую вам обратить внимание на эту книгу. Она описывает широкий круг вопросов, связанных со статистическими обработками, прогнозированием, моделированием в Excel на живых рабочих примерах.
Читайте также про графический факторный анализ с помощью графиков-водопадов (Waterfall charts) и графиков-торнадо (tornado charts).

Рубрики
Анализ

Что делать, если достоверная корреляция не получается

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

Недавно на работе прилетел интересный запрос. Рассчитать достоверную корреляцию между курсом рубль/доллар США и ценой на нефть. По смыслу понятно, что она есть. И она должна быть достаточно хорошей. Проблемы появляются, когда начинаешь строить корреляцию на основе статистики.

Рабочий файл с данными и графиками вы можете скачать по ссылке ниже.

Итак, шаг первый — нашли данные здесь и здесь.

Шаг второй (необязательный) — построили график с двумя крывыми, отложенными на разных осях, чтобы визуально оценить, есть ли корреляция вообще.

Вроде бы, зависимость есть.

Строим точечный график, добавляем на него тренд с уравнением и достоверностью. R^2 получается 18% — считай, корреляции нет.

Считаем на разных отрезках (хотя бы от 30 значений) — достоверной корреляции нет. Хоть убей. Почему?

Смотрим на первый график еще раз, и тут нас осеняет по нескольким пунктам:

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

Второе, и самое важное. При уменьшении цены нефти, рубль слабеет сильно. А при росте нефти, крепнет весьма неторопливо. Вот оно! Именно поэтому нет статистически достоверной корреляции. Метод малых квадратов, используемый в Microsoft Excel для построения формулы корреляционной зависимости, направление изменения просто не учитывает. Отсюда вывод — построить достоверную статистическую корреляцию этих двух показателей не получится.

Но не сдаваться же, в конце концов. Задайте себе вопрос, зачем эта корреляция вообще нужна. Обычно для прогнозирования, но для прогнозирования чего: спадов (консервативные сценарии) или роста (оптимистичные сценарии). А дальше смотрите, какой метод анализа для вас считается допустимым.

Если вы прогнозируете рецессию как в 2008 году, возьмите этот период и на его основе делайте выводы. Если вы прогнозируете постепенный рост как в 2009-2012 — ответ тот же, делайте анализ на основе данных этого периода.

Пошаговая инструкция, как построить корреляцию — здесь.

Рубрики
Анализ

График с выделением конкретного бара цветом

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

Если верить Джину Желязны («Говори на языке диаграмм»), то около 10 процентов людей имеет проблемы с цветовосприятием. В частности, они могут воспринимать разные цвета одинаково, но все зрячие люди отличают разные оттенки одного и того же цвета.

Разберем пример как это сделать.

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

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

Постройте график как обычно.

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

У вас должна получиться следующая диаграмма.

Если вы часто строите круговые диаграммы, эта статья также может оказаться вам полезной.

Рубрики
Анализ

Как правильно строить круговую диаграмму

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

Зачем нужна круговая диаграмма?

Она нужна, если вы хотите показать структуру чего-то.

Обратить внимание на долю чего-то в структуре или сравнить долю в динамике.

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

Примеры использования круговых диаграмм.

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

Я также хотел бы обратить ваше внимание на несколько моментов, которые кажутся мне важными.

(1) Не размещайте на диаграмме лишнюю информацию. Покажите только то, что помогает донести вашу идею.
(2) Много разных цветов — это вовсе не помощь человеку, читающему вашу диаграмму.
(3) Выделяйте цветом только то, что вы хотите выделить. Об этом подробнее здесь.
(4) Я слышал про два варианта расположения блоков на круговой диаграмме. Так Джин Желязны в своей книге «Говори на языке диаграмм» предлагает располагать их в порядке убывания важности по часовой стрелке (от 12 часов направо идет самый важный). Дона Вонг в своей книге «Wall Street Journal Guide to informational graphics» предлагает ставить самый важный блок первым от 12 часов по часовой стрелке, а дальше размещать блоки в порядке убывания важности против часовой стрелки. Лично мне больше импонирует второй вариант (именно его вы видите на графиках).

Круговая диаграмма — это лишь один частный случай графического представления данных, который дает хорошее представление о структуре описываемого явления. Если же вам необходимо сделать факторный анализ или анализ чувствительности к определенным факторам, попробуйте построить график-водопад (Waterfall) и торнадо (Tornado)

Идея данной статьи навеена книгой Джина Желязны «Говори на языке диаграмм».

Более подробную информацию о ней смотрите здесь.

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

Купть ее можно здесь.

Рубрики
Анализ

Как рассчитать корреляцию в Microsoft Excel

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

Давайте посчитаем их сами! Для примера, я решил попробовать посчитать корреляцию рубля к доллару через евро. Давайте разберем, как это делается подробно.

Эта статья рассчитана на продвинутый уровень владения Microsoft Excel. Если у вас нет времени читать всю статью, вы можете скачать файл и разобраться с ним самостоятельно.

Если вы часто сталкиваетесь с необходимостью сделать что-то подобное, настоятельно рекомендую подумать о покупке книги Статистические вычисления в среде Excel.

Что важно знать о корреляциях

Чтобы рассчитать достоверную корреляцию, необходимо иметь достоверную выборку, чем больше она будет, тем достовернее будет результат. Для целей данного примера я взял ежедневную выборку курсов валют за 10 лет. Данные есть в свободном доступе, я их брал с сайта http://oanda.com.

Что я, собственно, сделал

(1) Когда у меня были исходные данные, я начал с того, что проверил степень корреляции этих двух наборов данных. Для этого я воспользовался функцией CORREL (КОРРЕЛ) — о ней есть немного информации здесь. Она возвращает степень корреляции двух диапазонов данных. Результат, прямо скажем, получился не особенно впечатляющим (всего около 70%). А вообще говоря, степень соотношения двух величин принято считать, как квадрат этой величины, то есть корреляция получилась достоверной приблизительно на 49%. Это очень мало!

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

(3) Из графика очевидно, что на диапазоне около 35 рублей за евро корреляцию начинает рвать на две части. Из-за этого она и получилась недостоверной. Необходимо было определить в связи с чем это происходит.

(4) По цвету видно, что эти данные относятся к 2007, 2008, 2009 годам. Конечно! Периоды экономических пиков и спадов обычно недостоверны статистически, что и произошло в данном случае. Поэтому я попробовал исключить из данных эти периоды (ну и для проверки, я проверрил степень корреляции данных в этом периоде). Степень корреляции только этих данных составляет 0.01%, то есть она отсутствует в принципе. Зато без них данные коррелируют приблизительно на 81%. Это уже достаточно достоверная корреляция. Вот график с функцией.

Дальнейшие шаги

Теоретически, функцию корреляции можно уточнить, если перевести ее из линейной в экспоненциальную или логарифмическую. При этом статистическая достоверность корреляции вырастает приблизительно на один процент, но сложность применения формулы повышается неимоверно. Поэтому для себя я ставлю вопрос: а нужно ли это на самом деле? Решать вам — для каждого конкретного случая.

Вывод

Анализ данных и графиков корреляции позволяет улучшить ее достоверность очень существенно. Основываясь на полученных результатах можно сказать, что курсы коррелируют в периоды нормальной экономики (не на пике и дне) на 81% по формуле EUR/RUR=23.279EUR/USD + 7.2776.

Расчеты вы можете посмотреть, скачав файл внизу.

Читайте также, что делать, если статистически достоверная корреляция не получается даже после танцев с бубном и о том, как вынести формулу корреляции в ячейку на листе Microsoft Excel.
Если вы часто сталкиваетесь с подобными задачами в работе, я настоятельно рекомендую вам обратить внимание на эту книгу. Она описывает широкий круг вопросов, связанных со статистическими обработками, прогнозированием, моделированием в Excel на живых рабочих примерах.
Читайте также про графический факторный анализ с помощью графиков-водопадов (Waterfall charts) и графиков-торнадо (tornado charts).

Рубрики
Анализ

Графический факторный анализ — торнадо (tornado chart)

Вы хотите проверить, как ваши доходы зависят от продажных цен, от объема производства, от цены материалов или от количества сотрудников. И что важно, от чего из этого ваши доходы зависят больше всего. Тогда вам нужен график торнадо (tornado chart).

Как выглядит график торнадо (tornado chart)

Строится он как обычная линейчатая диаграмма (название — по версии microsoft; по мне это обычный столбчатый график, повернутый на 90 градусов). Каждый показатель в этом графике — это сумма, на которую изменится доход при увеличении или уменьшении какого-то конкретного фактора на, скажем, 10 процентов. Для каждого фактора рассчитывается два отклонения: влияющее на доход положительно и отрицательно. Если их отсортировать от того, который имеет наибольший эффект к тому, который наименьший эффект, получится график, внешне напоминающий смерч — отсюда его название.

Подготовка данных для построения графика торнадо (tornado chart)

(1) Определимся с показателем «чувствительности» (sensitivity) — стандартным отклонением каждого из факторов в процентах. Он должен быть одинаковым для каждого фактора, иначе график будет непонятен читающему.
(2) Перечислим все факторы, влияние которых на доход мы хотим проанализировать и пропишем их базовый уровень.
(3) Расчитаем положительный и отрицательный эффекты от изменения базового уровня каждого из факторов на доход. Помните, что в зависимости от типа фактора и его отношения с искомым показателем, положительный и отрацательный эффекты могут быть разными.
(4) Сортируем факторы в порядке убывания общей волатильности и строим линейчатый график по этим данным.

График торнадо готов! Пример постороения этого графика смотрите в приложенном файле.

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

Если вы хотите сравнить два показателя, которые отличаются друг от друга из-за нескольких факторов, то вам необходим график «водопад» («вотерфол», или «Waterfall»). О нем читайте в этой статье.

Если у вас есть вопросы, пожалуйста, пишите их на электронную почту edward@youcanexcel.ru.

О том, как строить разнообразные графики в Microsoft Excel читайте в книге Библия пользователя. Такую книгу стоит иметь дома или на работе.

Рубрики
Анализ

Графический факторный анализ — водопады (Waterfall charts)

Конкретно у нас на работе вотерфолы пользуются бешеной популярностью у менеджмента. Они легко читаются, отлично смотрятся, требуют минимум подготовки пользователя, широко известны массам. И главное — они очень хорошо доносят необходимую нам идею! Если вам необходимо сравнить два показателя и объяснить причины, из-за чего они отличаются — эта статья для вас.

С ними есть только одна проблема — такого типа графика нет в стандартной функциональности Microsoft Excel. Но обо всем по-очереди.

Выглядит стандартный вотерфол так

Строго говоря, это разновидность широкоиспользуемого в биржевой практике типа графика «свечи» без минимумов и максимумов: он только показывает точки входа и выхода, или в нашем случае — эффект на некий показатель от набора конкретных факторов.

Несколько столбцов от оси абсцисс, означающие сравниваемые данные (их может быть два или больше).

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

В нашем примере, основной анализируемые показатель — чистый доход (NIAT). Сравнивается два варианта доходов: бюджетный показатель и фактический доход. Эти два показателя показаны серым цветом. Столбцы синего цвета — факторы, показывающие увеличение дохода; красные — уменьшение.

Как построить вотерфол (waterfall)

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

График — обычный столбчатый. Проблема в том, что некоторые столбцы начинаются не с горизонтальной оси, а с места, где заканчивается предыдущий столбец. Эта проблема решается очень легко, строится столбчатый график, в котором столбцы ставятся один на другой (Stacked columns).

Нижний столбец делается прозрачным. Для этого необходимо выделить нижний столбец, нажать на него правой кнопкой, выбрать «формат» (format data series). Выбрать пункт «заливка» (Fill) и выбрать решим «без заливки» (No fill).

Подготовка данных для построения вотерфола (waterfall)

Входные данные, которые вам понадобятся — это только объем основного показателя после применения каждого из факторов. Остальные данные рассчитываются из него.

На основе этих данных строится таблица, в которой указываются

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

На основе этих данных строится график.

Пример расчета и построения графика смотрите в приложенном файле.

По данной теме можно посмотреть эту книгу.

Читайте также о том, как и зачем строить графики торнадо (Tornado), а также о том, как правильно строить круговую диаграмму.