Заставьте данные говорить. Как сделать бизнес-дашборд в Excel. Руководство по визуализации данных - Алексей Сергеевич Колоколов

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

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

Расстояние между столбцами

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

На открывшейся панели справа в разделе «Параметры ряда» настраиваем значения:

● «Перекрытие рядов» – 0 %;

● «Боковой зазор» – 50 %.

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

Сортировка столбцов по убыванию

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

Очередность столбцов на диаграмме зависит от последовательности строк в сводной таблице. График динамики у нас выстроен по названию месяца – они сразу были упорядочены верно. А в выборках для двух других визуальных элементов нужно настроить эту сортировку в поле «Факт» от большего числа к меньшему.

Для сортировки столбцов на гистограмме есть два способа.

Способ 1

На диаграмме

Выделяем ряд столбцов на диаграмме «Расходы по подразделениям». Так как мы хотим сравнивать подразделения в первую очередь по фактическим результатам, у нас это столбцы «Факт». Правой кнопкой мыши вызываем контекстное меню и выбираем в нем «Сортировка» → «Сортировка по убыванию».

Способ 2

В сводной таблице

На листе «Черновик» ставим курсор в ячейку выборки «Расходы по подразделениям». Правой кнопкой мыши вызываем контекстное меню, выбираем «Сортировка» → «Сортировка по убыванию».

Порядок столбцов диаграммы на листе «Чистовик» изменится автоматически и примет логичный для восприятия вид.

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

Сортировка столбцов на линейчатой диаграмме

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

Даже если вы установите в таблице сортировку «По убыванию», как мы сделали с гистограммой, на диаграмме категории выстроятся ровно наоборот.

Чтобы исправить это, в выборке данных нужно настроить обратную сортировку – «По возрастанию». Тогда (о, чудо!) на линейчатой диаграмме они встанут по убыванию. Не буду останавливаться на причинах такого построения, просто смиритесь – Excel есть Excel.

То же самое происходит и с порядком столбцов «План» и «Факт». На соседней диаграмме и в легенде и в столбцах первым идет «План». И только в линейчатой диаграмме первым идет «Факт». Исправим это с помощью настройки формата оси.

Выделите категории на оси Y правой кнопкой мыши и в контекстном меню выберите пункт «Формат оси».

На появившейся панели в разделе «Параметры оси» укажите следующее:

● в группе «Горизонтальная ось пересекает» выберите «в максимальной категории»;

● в группе «Положение оси» поставьте галочку «Обратный порядок категорий».

Эти параметры автоматически расставят ряды в соответствии с последовательностью полей в выборке, и нам ничего не придется делать дополнительно.

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

Чек-лист оформления диаграммы

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

■ Включить метки (подписи) данных, выбрать для них размер шрифта 12 пт.

■ Изменить разрядность чисел до тысячи или миллиона, чтобы в подписях оставалось 3–4 знака.

■ Удалить шкалу и линии сетки.

■ Покрасить метки данных в цвет ряда (на тон темнее), фон для меток не добавлять.

■ Скрыть кнопки полей водной таблицы.

■ Разместить легенду сверху (иногда слева).

■ Убрать из легенды автоматические надписи.

■ Добавить название диаграммы, выбрать для него размер шрифта 16 пт.

■ Между столбцами диаграммы сократить боковой зазор до 50 %.

■ Настроить сортировку столбцов от большего к меньшему.

3.5 Настраиваем шаблон диаграммы

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

Мы сделаем шаблон из диаграммы «Расходы по подразделениям», которую уже настроили по нашему чек-листу.

Как сохранить шаблон

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

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

Не забывайте при сохранении давать шаблонам понятные названия, чтобы потом легко находить в списке нужную диаграмму. Наш шаблон сохранен с именем «Диаграмма1».

Новая диаграмма с помощью шаблона

Чтобы применить готовый шаблон к другой (ненастроенной) диаграмме, делаем следующее:

Откроется окно с меню слева – заходим в папку «Шаблоны» и выбираем наш сохраненный шаблон «Диаграмма1».

К выделенной диаграмме автоматически