Диаграмма с параллельными координатами – или еще один вид графика, доступный в Excel 5

Уже не в первый раз мы выполняем проекты, в которых возникает потребность в одновременном сравнении нескольких компаний по множеству показателей.  Для этого самым оптимальным выбором является диаграмма, использующая параллельные координаты (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

Германия впереди по числу очков, но насколько успешны были остальные команды? Придется внимательно сравнивать все показатели. А если изобразить те же данные в виде диаграммы с параллельными координатами, чтобы понять, какая из команд с большей вероятностью пройдет в полуфинал? Получим совершенно другой результат!

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

Исходная таблица содержит следующую информацию:

Делаем:

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 хочется отметить сравнение стран по занимаемым им позициям при оценке инновационного потенциала.