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

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

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

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

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

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

Кликом мыши выделяем любой ряд столбиков.

Вызываем контекстное меню.

Выбираем пункт «Формат ряда данных».

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

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

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

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

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

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

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

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

Способ 1

На диаграмме

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

Способ 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

выделяем диаграмму целиком или область построения на ней;

в контекстном меню (правой кнопкой мыши) выбираем «Изменить тип диаграммы».

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

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