Уже не в первый раз мы выполняем проекты, в которых возникает потребность в одновременном сравнении нескольких компаний по множеству показателей. Для этого самым оптимальным выбором является диаграмма, использующая параллельные координаты (parallel coordinates chart). Основное ее преимущество — легкость акцентирования внимания на выбираемых пользователем категориях при сохранении возможности сопоставления их с остальными, когда главное — не показать абсолютные значения, а увидеть относительное положение в сравнении с другими. Впрочем, как могли заметить самые внимательные и постоянные читатели нашего блога, про данный тип диаграмм уже достаточно много сказано и написано, поэтому в данном посте хочется поделиться найденными нами секретами создания параллельных координат стандартными средствами Excel.
Где это может быть применено? В любой сфере жизни. Показать ли, какие выручка, прибыль и EBITDA у компаний-конкурентов или посмотреть турнирную таблицу (всего сыграно матчей, выигрыши, ничьи, забитые голы и другие показатели) на чемпионате Европы по футболу — в любом случае, диаграмма с параллельными координатами сможет выручить.
К примеру, все представляют себе турнирную таблицу. Какие же результаты показали команды, сыграющие в четвертьфиналах?
Германия впереди по числу очков, но насколько успешны были остальные команды? Придется внимательно сравнивать все показатели. А если изобразить те же данные в виде диаграммы с параллельными координатами, чтобы понять, какая из команд с большей вероятностью пройдет в полуфинал? Получим совершенно другой результат!
Как и в любой другой инфографике, при создании данного типа графика важны два аспекта — обработка данных и расстановка визуальных акцентов. С точки зрения технической реализации — требуется около получаса времени и базовое понимание смысла работы с элементами форм в Эксель. Готовы? Начинаем!
Исходная таблица содержит следующую информацию:
Делаем:
1. В целях «чистоты эксперимента» будем работать с нормированными данными, для чего в первую очередь занесем имеющуюся информацию в ячейки H5: M13.
2. Определим минимум и максимум значений каждого из параметров. Получившиеся наибольшие и наименьшие значения расположим в двух верхних строках (H1:M2 соответственно). К примеру, в ячейке H1 укажем =МАКС(H6:H13), в H2 =МИН(H6:H13), протянув данные формулы на все показатели, вплоть до последнего столбца.
3. Приступаем к нормированию: узнаем, как соотносятся значения каждого из показателя для страны (например, Чехии) с результатами по другим странам. Для этого вводим в ячейку B6 следующую формулу =(H6-H$3)/(H$2-H$3). Получим 50% (то есть результат средний по сравнению с минимальным числом побед — одной, и максимальным числом — три). Отлично, идем дальше и используем работающую формулу для диапазона B6: G13
Уже есть данные для графика (по диапазону A6:G13) стандартными средствами Excel, однако сложно сказать, что он будет отличаться особой красотой и наглядностью.
4. Чтобы иметь возможность выбирать нужные нам страны и акцентировать внимание именно на них, выделим для этого 2 специальные ячейки (например, N1 и N2 для первой и второй страны соответственно). Эти ячейки буду связаны с нашими элементами управления (относительный порядковый номер страны в имеющейся таблице, к примеру 1 и 2 для Чехии и Португалии).
5. Следующим шагом в диапазоне A1:G2 расположим значения показателей для выбранных пользователем команд. Так, информация по выигрышам первой команды может быть получена следующим образом: =ИНДЕКС(B$6:B$13;$N1) — что означает поиск по всем ячейкам для конкретного показателя B6:B13 для номера страны из ячейки N1. Проверим: в A1 сейчас появится названия команды, автоматически находимой по запросу пользователя — в случае работы с первой страной это Чехия
6. Что произойдет, если изменить значения в N1 и N2? Попробуйте! Появились значения для других команд? Отлично, идем дальше.
7. Важно, что диалог с пользователем может осуществляться через разные элементы управления, для нашего примера выберем «выпадающий» список (combo box). Добавим соответствующие элементы с панели разработчика и отформатируем для работы с диапазоном данных A1:G1 в привязке к ячейке N1 (A2:G2 и N2 для управления вторым рядом соответственно).
8. Секрет построения данного графика прост и с точки зрения графики — все линии перекрашиваются в не привлекающий лишнего внимания серый цвет, выделяя необходимые данные яркими цветами. Подготовим график для этого: удалим легенду, границы и горизонтальные оси (если таковые были), добавим вертикальные оси (для которых определим, что максимальное значение оси Y будет равным 1 благодаря проведенному нормированию). Также все ряды представим в виде тонких серых линий без маркеров.
9. Наконец, финальные штрихи — скопируем ячейки A1:G2, перейдем на график и используем «Специальную вставку», чтобы добавить эти значения в качестве двух новых рядов данных. Выделим первую линию, к примеру, красным — а вторую синим цветом и увеличим их толщину. При желании можно также добавить легенду и подписи данных (для чего для каждой из подписей данных двух линий через индексирование (подобно шагу 5) подставляем необходимые абсолютные (а не относительные значения).
Вуаля! — наш дизайн готов и радует зрителя своим минимализмом, простотой восприятия и легкостью редактирования и изменения размерности массива исходных данных.
Хочется сравнить Чехию и Португалию? 2 секунды — и результат перед глазами! Кажется, у Португалии все-таки больше шансов…А как считаете вы?
P.S. Файл для тренировки можно скачать по ссылке
P.P.S. Также как достаточно интересный вариант визуализации такого рода (с легкостью переносимый в Эксель) не можем не привести сравнение различных моделей автомобилей, в результате демонстрирующее соотношение развиваемых ими скоростей в привязке с стоимости.
И напоследок — самое интересное: в качестве крайне показательного (и более того, интерактивного!) примера от GE хочется отметить сравнение стран по занимаемым им позициям при оценке инновационного потенциала.
