Тема: Теория 8
С чего начать, когда берешься за визуализацию? Как собрать понятную презентацию? Как разобраться в облаке диаграмм и графиков? Куда идет инфографика? Кто об этом говорит и как? Все, что вы хотели знать об инфографике, но не знали, у кого спросить. А может, у вас есть хороший совет? Присылайте — опубликуем!
Дайте волю Экселю
Не торопитесь делать работу сразу с самого утра. После открытия экселевские файлы должны подышать.
Box & Whisker, Treemap, Sunburst — Новые диаграммы для Office 2016 (Часть 2)
Как мы уже писали, скоро выходит новый Office 2016, и в нем будут новые диаграммы. Про три из них мы уже рассказали в прошлом обзоре (Мост, Гистограмма, Парето). А теперь – еще три: Ящик с усами, Treemap, Sunburst.
Ящик с усами (Box & Whisker) – статистика в заданных отрезках
Как и Гистограмма, Ящик с усами показывает, как распределяется информация. Однако эта диаграмма помогает глубже разобраться в деталях. Ящик с усами компактно отражает ключевые моменты в одной диаграмме — диапазон, квартили, среднее значение и выбросы. В книжном магазине, например, такая диаграмма может пригодиться для анализа распределения книг по жанрам. Ящик с усами автоматически группирует книги по жанру и показывает, как распределяются цены внутри группы. В нашем примере хорошо видно, что наибольший диапазон имеют цены на Романтику, и выброс – 300$ за книгу.
Treemap – анализ нескольких иерархических отношений одновременно
С помощью диаграммы Treemap можно легко визуализировать и сгруппировать большое количество данных. Эта диаграмма представляет обзор и помогает сравнить аналогичные или конкурирующие товары.
Для владельца книжного магазина важно понимать, какой жанр книг приносит наибольшую прибыль. Treemap позволяет идентифицировать размер прибыли в каждой отдельно взятой категории жанра, которые сгруппированы по цветам и сходству. Размер каждой ячейки обозначает размер прибыли, которую приносит категория в рамках определенного жанра. Из диаграммы видно, что основная прибыть идет за счет детских книг и романтики. При этом наиболее прибыльны категории 1st readers и Young Adult.
Sunburst – анализ каждого уровня иерархии
Тогда как Treemap полезна при сравнении размера групп, Sunburst хорошо отражает иерархические отношения и позволяет их проанализировать. На этой диаграмме хорошо видно, какие сегменты имеют наибольшее значение и какова их иерархия на разных уровнях. Макет Sunburst помогает интуитивно уловить, как каждый из элементов влияет на общее целое. Sunburst идеально подходит для того, чтобы показать любое количество уровней и категорий.
Благодаря новым диаграммам для Word, Excell и Powerpoint новый Office обладает хорошим набором для сторителлинга. Каждую таблицу также можно адаптировать при помощи инструментов, которые вам уже давно знакомы – поменять стиль, макет, добавить новые элементы.
После выпуска Office 2016, благодаря подписке Office 365, будут добавлены еще и новые диаграммы. А пока можно начать с превью и изучить шаблон для подготовки данных.
Office blogs (Перевод с англ. — Инфографер)
Мост, Гистограмма, Парето — новые диаграммы для Office 2016 (Часть 1)
В 2016 Microsoft представит новый Office 2016, и в нем — новые графики и диаграммы. Уже можно скачать превью и все попробовать. Новых диаграмм будет шесть. Сегодня — про три из них — Каскадная диаграмма (она же мост), Гистограмма и Диаграмма Парето.
Каскадная диаграмма (waterfall, bridge, мост, каскадная диаграмма, летающие кирпичи) – визуализация финансовых результатов
Отчет о финансовых результатах – удобный способ проследить расходы и доходы. Однако это не самый лучший вариант, если нужно быстро и кратко представить информацию, например, коллегам. С помощью каскадной диаграммы можно легко продемонстрировать финансовые данные и проследить, как каждый из параметров влияет на итоговую сумму. В примере ниже – отчет книжного магазина, в котором очень ясно видно, что стоимость инвентаря сократила общую прибыль почти на половину, а операционные расходы – еще на треть.
Гистограмма – анализ и распределение
Гистограммы обычно используются в статистике и показывают частоту повторов в заданных пределах. Например, для владельца книжного магазина важно иметь в стоке книги разных ценовых категорий для привлечения разных целевых аудиторий. Гистограмма может ясно показать, насколько этот критерий выдержан. На гистограмме ниже горизонтальная ось представляет стоимость книг, а столбики — количество книг. На этом примере видно, что распределение книг в магазине вполне удачное.
Диаграмма Парето – наибольшая степень воздействия
Используя эту диаграмму, можно автоматически отсортировать параметры по частоте повторений, а потом проследить, как они влияют на общую картину. Если вернуться к примеру книжного магазина, то можно использовать такую диаграмму, например, для отслеживания причин возвратов книг. В данной диаграмме каждая колонка представляет причину возврата, а линейный график – то, насколько каждая из причин влияет на общую картину. Из столбиковой диаграммы мы видим, что «Дефект» стал причиной возврата 2 025 книг, а из линейного графика, что это составляет 40% всех возвратов. Проработав три основные причины – дефекты, ошибки в ценах, не тот товар – можно устранить 80% возвратов. Диаграмма Парето – отличный инструмент для расстановки приоритетов.
Продолжение следует.
Office blogs (Перевод с англ. — Инфографер)
Диаграмма с параллельными координатами – или еще один вид графика, доступный в Excel
Уже не в первый раз мы выполняем проекты, в которых возникает потребность в одновременном сравнении нескольких компаний по множеству показателей. Для этого самым оптимальным выбором является диаграмма, использующая параллельные координаты (parallel coordinates chart). Основное ее преимущество — легкость акцентирования внимания на выбираемых пользователем категориях при сохранении возможности сопоставления их с остальными, когда главное – не показать абсолютные значения, а увидеть относительное положение в сравнении с другими. Впрочем, как могли заметить самые внимательные и постоянные читатели нашего блога, про данный тип диаграмм уже достаточно много сказано и написано, поэтому в данном посте хочется поделиться найденными нами секретами создания параллельных координат стандартными средствами Excel.
Где это может быть применено? В любой сфере жизни. Показать ли, какие выручка, прибыль и EBITDA у компаний-конкурентов или посмотреть турнирную таблицу (всего сыграно матчей, выигрыши, ничьи, забитые голы и другие показатели) на чемпионате Европы по футболу – в любом случае, диаграмма с параллельными координатами сможет выручить.
К примеру, все представляют себе турнирную таблицу. Какие же результаты показали команды, сыграющие в четвертьфиналах?
Команда И
В
Н
П
Мячи
Очки
Чехия 3
2
0
1
4-5
6
Португалия 3
2
0
1
5-4
6
Испания 3
2
1
0
6-1
7
Франция 3
1
1
1
3-3
4
Германия 3
3
0
0
5-2
9
Греция 3
1
1
1
3-3
4
Англия 3
2
1
0
5-3
7
Италия 3
1
2
0
4-2
5
Германия впереди по числу очков, но насколько успешны были остальные команды? Придется внимательно сравнивать все показатели. А если изобразить те же данные в виде диаграммы с параллельными координатами, чтобы понять, какая из команд с большей вероятностью пройдет в полуфинал? Получим совершенно другой результат!
Как и в любой другой инфографике, при создании данного типа графика важны два аспекта – обработка данных и расстановка визуальных акцентов. С точки зрения технической реализации — требуется около получаса времени и базовое понимание смысла работы с элементами форм в Эксель. Готовы? Начинаем!
Гантт в Экселе — 4 варианта
Достаточно часто у меня возникает потребность в том, чтобы нарисовать диаграмму Гантта — проектов мы ведём много. И не всегда хочется трогать графические программы — потому что открыть Adobe Illustrator, конечно же, проще всего, но.. когда нужно быстро набросать план, и визуализировать сроки/этапы проекта — тратить на это полчаса нет возможности.
Итак, открываем Эксель. У меня на вооружении .. четыре способа. Обычно на тренингах я пропагандирую смотреть на эксель не как на таблицу с набором предзаданных шаблонов диаграмм, а как на визуализатор логики. Логика она вообще штука хорошая. Зная всего несколько функций, не применяя никаких макросов, можно рисовать очень много полезных вещей.
Первый мой вариант основан на условном форматировании ячеек, второй — функцию REPEAT, третий использует диаграмму типа waterfall, а четвёртый — эксплуатирует точечную диаграмму. Сегодня рассказываю про первые два, простые.
Дано: для простоты примем, что наш план работ выстроен в единую последовательность, каждый последующий пункт плана работ начинается после окончания предыдущего. Известно, когда должен начаться проект, и сколько времени уйдёт на каждый его этап. Простая последовательность, без задержек и промежутков, без наслоений.
Вычисляем:
1. Записываем в столбец пункты (задачи) плана работ.
2. Для каждой задачи пишем её длительность (число, может измеряться в любых единицах, например, в количестве дней)
3. Вычисляем абсолютное количество дней, которое пройдёт с начала проекта, до начала / до конца задачи.
В итоге, мы получили массив из двух чисел: номер дня начала, и номер дня окончания задачи.
Строим вариант 1.
Делаем из ячеек большую таблицу, закрашенные ячейки которой будут представлять себя временные бары диаграммы:
1. Делаем счётчик: по горизонтали, в строке заголовка откладываем числа от 1 до N, где N должно превышать суммарную длительность проекта (В днях). Это дни проекта.
2. Для удобства, делаем все ячейки узкими
3. В каждой ячейке получившейся таблицы, записываем формулу
=ЕСЛИ(И(G$4>=$D5,G$4<($E5+1)),1,0)
иными словами, ЕСЛИ (номер_дня_счётчика лежит в пределах между день_начала и день_окончания), то значение ячейки будет равно 1, иначе 0
4. Форматируем ячейку условным форматированием (conditional formatting, надеюсь, вы с этим знакомы?):
Если значение ячейки = 1, то красим цвет фона и цвет шрифта в единый цвет (например, светло-синий), а значения равные 0 красим в белый.
Итог:
Простейший таймлайн готов. Он привязан к строкам таблицы, поэтому удобен для оперативной работы.
Строим вариант 2.
Он не настолько красив, но более элегантен. Всё что нам нужно сделать — это символом «|» повторить нужное количество раз (дней), с нужным оттступом. Этот символ можно нарисовать в ячейке нужное количество раз функцией REPEAT (ПОВТОР). Этот вариант мы уже рассматривали вкратце, но не лишне повторить в ином контексте.
Формула до безобразия проста:
ПОВТОР(«|»,E5-D5+1)
Мы получили нужное количество палочек, соответствующее количеству дней на задачу.
Нам остаётся только добавить спереди них количество дней, которые пройдут до момента начала задачи:
ПОВТОР(» «,D5-1),
и итоговая формула выглядит вот так:
= СЦЕПИТЬ( ПОВТОР(» «,D5-1), ПОВТОР(«|»,E5-D5+1) )
Итоговый результат незатейлив, но для пятиминутки идеален:
Кажется, вполне удобно 🙂 Останется добавить подписи дней, маркер текущего дня, и станет лучше. А если ещё и добавить разбивку бара задачи на выполненную/не выполненную части (например, задача выполнена на 30%), то всё станет вообще волшебно. Догадываетесь, как это сделать?
Ждите продолжения в следующей серии!
PS Что-то красивой инфографики с диаграммой Гантта никак не найду. Накидайте парочку в комментарии?
Простые диаграммы в Excel — дополнительные возможности
В прошлый раз я рассказывал о том, как строить микро-диаграммы в ячейках Excel. На самом деле это ещё не всё, что можно вытворять. Подключаем фантазию, и..
…Чего ооочень не хватало в той диаграмме — так это подписей. Добавим их следующим образом:
=ПОВТОР(«|»,D2)&» «&D2
=REPT(«|»,D2)&» «&D2
Диаграмма в ячейке с подписями
…Превращаем время в диаграмму Гантта. Наверное, самое полезно-ценное применение. В Excel просто так Ганнта не построить, MS Project вряд ли у кого-то есть, а привязать нормально к строкам с данными — ещё сложнее. Итак: полоски показывают промежутки времени, отведённые на определённую задачу.
=ПОВТОР(» «,(D2-МИН($D$2:$D$10)))&ПОВТОР(«|»,1.5*(E2-D2+1))
=REPT(» «,(D2-MIN($D$2:$D$10)))&REPT(«|»,1.5*(E2-D2+1)),
где D2 — ячейка с начальной датой, E2 — ячейка с конечной датой, а колонка D2:D10 есть колонка всех начальных дат
Умножение на 1.5 необходимо для того, чтобы соотнести ширины: в шрифте Arial пробел равен примерно полторы символа «I»
Гантт в ячейке Excel
..Добавляем к графикам маркеры-окончания, а сами графики делаем пунктирами. Остаётся по-прежнему читабельно, а график принимает совсем иной вид.
Диаграмма с маркером-окончанием в ячейке Excel
Кто не верит — смотрите файл со всеми этими графиками.
Давайте ещё что-нибудь эдакое придумаем?
Простые диаграммы внутри ячеек в Excel
Есть один интересный приемчик в работе с эксель-данными. Раньше о нём упоминала Ира, но кратко и давно — а поэтому освежим архивы истории.
Допустим, у вас есть таблица. Много строк. И есть колонка, по которому все эти строки сравниваются. Например: продажи по каждому магазину торговой сети, выручка по товарным категориям, тиражи изданий, или что-либо неважно. Главное — что график строить вроде как незачем, а сравнить значения в таблице хочется. Например, хочется нам посмотреть, какие из моделей Volvo на сайте auto.ru выставляются активнее всего (вдруг вам это действительно понадобилось?).
Исходные данные, перетащенные в Excel:
Исходные данные для графика внутри ячейки
Что делать? Строить диаграмму прямо в ячейке. Эксель 2010 умеет делать микро-чарты (спарклайны), но мы сейчас не об этом. Нам нужно не динамику в ячейке построить, а сравнить между собой строки. В работу берём формулу REPT («Повтор» в русской версии).
Итак, пишем в строке формулы для ячейки А3 магический код:
= REPT ( «I» ; A2), где А2 — это ячейка с количеством машин
Результат: ячейка заполнилась символами I, в количестве равном цифре каждой ячейки.
Результат выдачи - количество в каждой ячейке
Останется лишь поставить такую ширину столбца, который позволит увидеть всю длину.
Зачем все это? Только лишь для того, чтобы оставить таблицу чистой и аккуратной, не городить графиков, и при этом сделать таблицу читабельнее.
Как заметили самые смекалистые — такие диаграммы как будто бы не работают на больших числах. А вот нет — делаем дополнительную промежуточную колонку, в которой тысячые и миллионные разряды убираем делением, и приводим все так чтобы значения не превышали 50-100.
Итоговый график в ячейке
Это еще не все. Идем дальше: визуализируем таким же способом положительные и отрицательные значения. Отрицательные строятся в одной колонке, положительные в другой. Формула контролирует, что именно отображать.
Пример: статистика изменений в предложениях всё того же Volvo (это уже выдумано, пример-то надо продолжать). Результат:
Выигрыши - проигрыши диаграмма в ячейке Excel
Как?
1. Строим модуль значения каждой строки, так как REPT ничего не знает про отрицательные числа
2. Строим в двух колонках REPTы так, что в одной колонке только положительные, в другой только отрицательные
3. Форматируем, и приводим в порядок. Works!
Кстати, все подобные экзерсисы — отличный тренажер для логики. Love it!
Бизнес-инфографику — в массы!
Друзья, мы начинаем новую рубрику! Давно уже мы говорим о том, что инфографика имеет значительный потенциал применения в бизнесе, и даже выполняем для клиентов заказы с применением только базовых инструметнтов — но мало уделяем этому на нашем блоге. Пора исправлять ситуацию. Будем пропагандировать!
Бизнес-инфографика — так и назовём новую регулярную рубрику.
Excel — бумажный прототип
Основные темы для освещения:
- Использование на практике принципов инфографики в стандартных офисных программах.
- Специальное бизнес-ПО для визуализации.
- Уроки Excel и интересные трюки в программировании на Visual Basic для ежеднево использования.
Приглашаю к совместному творчеству на блоге всех, у кого Эксель является часто используемым инструментом, а также тех кто владеет навыками VBA (visual basi for applications).