Как визуализировать данные в excel
Для полноценного визуального анализа хорошо использовать визуализацию данных. Но еще лучше применять динамические диаграммы и графики, чтобы предоставить аналитикам эффективный инструмент. На интерактивном динамическом дашборде можно выполнять более детальный визуальный анализ со срезами в разных периодах времени и сегментировании отчета по группам показателей.
Как сделать сводный отчет с визуализаций данных по таблице Excel
Для освоения примера создания дашборда в Excel смоделируем ситуацию. Из CRM системы были выгруженные в файл Excel через экспорт данных статистические показатели взаимодействия фирмы с ее клиентами:
- Уровень сервиса.
- Уровень качества.
- Продуктивность.
Все значения показателей относительны и поэтому измеряются в процентом соотношении к максимально возможному результату. Формировалась данная статистика в CRM системе – ежедневно на протяжении двух с половиной лет. А точнее отчетный период охватывает промежуток времени между датами: с 01.01.2019 по 31.07.2021 (2,5 года). Все статистические данные находятся в одной большой таблице аж на 3773 строки:
Необходимо с помощью средств визуализации данных в Excel преобразовать таблицу отчета в графический вид отображения информации для визуального анализа дальнейшего развития фирмы в области взаимодействия с клиентами. Идеальное решение – это построить дашборд с интерактивными возможностями изменения данных на графиках и диаграммах пользователем: сегментирование информации по месяцам или по годам, фильтрация значений показателей, масштабирование временных периодов и т.п.
Решим поставленную задачу в 3 шага:
- Подготовка исходных данных.
- Обработка значений для визуализации.
- Динамическая визуализация данных.
Шаг первый – подготовка данных для создания дашборда в Excel
Для подготовки данных нам необходимо лишь добавить один столбец с формулами к таблице на листе «Данные». В данном столбце с заголовком «Месяц» формула преобразует исходные даты в соответственное им названия месяцев, сокращенные до 3-х символов:
С подготовкой данных мы закончили и переходим к следующему шагу – обработка значений таблицы.
Настройка таблиц для элементов управления интерактивного дашборда
На основе данных обработки будут строиться графики и диаграммы, которые будут обладать возможностью динамического изменения по разным условиям.
Шаг второй – обработка исходных данных для диаграмм и графика
Создайте новый лист с именем «Обработка» и заполните его таблицами и значениями также как показано ниже на рисунке:
Все адреса ячеек должны в точности совпадать как выше на рисунке – это важно для дальнейшего использования формул в ячейках этих таблиц. В данный момент формулами заполнен только диапазон ячеек D3:F3, а остальные значения в таблицах введены вручную.
Так как наш сводный дашборд со средствами визуализации данных будет обладать интерактивным функционалом, нам потребуется связать переменными значениями обработку данных с элементами управления дашбордом. Поэтому не закончив еще второй шаг создаем новый лист с именем «Дашборд», на котором сначала создадим все интерактивные элементы управления.
Элементы управления динамическими диаграммами и графиком на дашборде
Перед тем как создавать элементы управления дашбордом сначала создадим основу из фигур на которой будем все размещать.
Основа и панель управления для дашборда
На листе «Дашборд» нарисуйте большую квадратную фигуру с размерами 17 см. на 25 см. выбрав инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Прямоугольник».
Создаем новый элемент основы с размерами 1,5x25 см., для этого выберите инструмент: «ВСТАВКА»-«Текст»-«Надпись»:
Для быстрого оформления верхней надписи основы дашборда воспользуйтесь готовым решением из ее дополнительного меню: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Стильный эффект: Черный, темный 1».
Добавляем основу для панели управления дашбордом рисуя снова прямоугольник с размерами высоты 15,45 см на 3,85 см ширины и придаем ему такой же стиль оформления, как и для надписи:
Блок кнопок для управления показателями по регионам
Далее на панели управления дашбордом создаем первый блок кнопок меню из прозрачного прямоугольника (размером 4,6 x 3) и надписью «РЕГИОН»:
Для удаления заливки прямоугольника выбираем опцию из меню: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Заливка фигуры»-«Нет заливки». И здесь же изменяем цвет контура «Контур фигуры»-«Цвет белый».
Такими же инструментами настраиваем цвет заливки и контура для надписи. А в разделе инструментов: «ГЛАВНАЯ»-«Шрифт» задаем белый цвет шрифта и размер 11 пунктов.
Теперь переходим непосредственно к созданию самих кнопок. Выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель»:
Рисуем первую кнопку переключатель с размерами 0,7 x 2,75 см. Затем двойным щелчком по тексту изменяем сам текст. После кликаем правой кнопкой мышки по кнопке переключателя и выбираем из появившегося контекстного меню опцию «Формат объекта». В появившемся диалоговом окне «Форматирование объекта» на вкладке «Элемент управления» в поле ввода «Связь с ячейкой:» указываем внешнюю ссылку на лист: =Обработка!$B$1. Дальше создаем еще 3 копии этой кнопки и подписываем их текст соответственно как показано выше на рисунке.
Таким образом внешней ссылкой мы создали первое подключение данных к элементам управления «Переключатели». По этой ссылке будут передаваться переменные значения для интерактивной обработки данных.
В зависимости от того какой выбран переключатель в ячейку Обработка!$B$1 будут передаваться переменные числовые значения от 1, 2, 3 или 4. Эти числа будут использованы в формулах на том же листе «Обработка».
Блок срезов временных периодов по годам
Следующий блок кнопок будет создан по другому принципу, но он также будет связан с листом «Обработка». Поэтому сразу переходим на данный лист и выделяем диапазон ячеек A16:C19, а затем выбираем инструмент: «ВСТАВКА»-«Таблицы»-«Таблица» CTRL+T:
В появившемся окне «Создание таблицы» просто нажимаем ОК. И сразу нам доступно ее дополнительное меню где отмечаем галочкой опцию: «РАБОТА С ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Параметры стилей таблиц»-«Строка итогов» CTRL+SHIFT+T. В строке итогов созданной таблицы под вторым столбцом выбираем опцию «Минимум», а под третьим – «Максимум».
Для создания блока кнопок управления привязанных к этой таблице выберите инструмент: «РАБОТА С ТАБЛИЦАМИ»-«КОНСТРУКТОР»-«Сервис»-«Вставить срез»:
В появившемся окне «Вставка срезов» отмечаем только одну первую галочку на против опции «ГОД» и жмем ОК. Для быстрого форматирования среза задаем ему готовый стиль: «ИНСТРУМЕНТЫ ДЛЯ СРЕЗА»-«ПАРАМЕТРЫ»-«Стили срезов»-«Серый цвет другой 1». И на конец указываем в параметрах среза размеры «Высота:»-3,33 см и «Ширина:»-3 см.
Очередной блок кнопок для панели управления дашбордом – ГОТОВ! Переносим его на главный лист с именем «ДАШБОРД» и красиво размещаем под первым блоком элементов управления (переключатели).
Выпадающий список для выбора месяца
Переходим к созданию следующего блока элементов управления на листе «ДАШБОРД». Копируем прямоугольник и надпись с первого блока кнопок-переключателей и вставляем под срезом. Надпись переименовываем на «МЕСЯЦ» высоту прямоугольника уменьшаем до 2см.
Внутрь прямоугольника нового блока размещаем новый элемент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком». Рисуем поле с размерами 0,7 см на 2,8 см:
Кликаем правой кнопкой мышки по элементу поле, из контекстного меню выбираем опцию «Формат объекта». В появившемся окне «Форматирование объекта» на вкладке «Элемент управления» указываем внешние ссылки для связи с обработкой данных. Изменяем 3 параметра:
- «Форматировать список по диапазону:» – здесь указываем внешнюю ссылку на диапазон ячеек с трехсимвольными названиями месяцев, из которого будут взяты значения для поля: =Обработка!$A$3:$A$14.
- «Связь с ячейкой:» - в этом поле ввода указываем внешнюю ссылку на ячейку куда будет передаваться параметр переменного числового значения (от 1-го и до 12-ти) для обработки данных: =Обработка!$A$1.
- «Количество срок списка:» - в этом поле ввода мы указываем сколько будет выводиться опций в элементе без полосы прокрутки. Значение 12 так как у нас 12 сокращенных названий месяцев.
Блок управления отображением показателей на графике
И наконец последний блок кнопок управления. Создаем его на основе прямоугольника и надписи из предыдущих блоков, просто скопировав их. Текст надписи изменяем на «ПОКАЗАТЕЛИ», а в прямоугольнике изменяем высоту на 3,8 см. Внутри прямоугольника 4-го блока размещаем новые кнопки воспользовавшись инструментом: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Флажок». Создаем 3 кнопки типа флажка с размерами 0,7 x 2,75 см и подписываем их так как показано на рисунке ниже:
Каждому флажку необходимо в параметрах указать свою внешнюю ссылку для передачи числовых параметров в обработку на другой соответственный лист:
- Сервис: =Обработка!$H$1
- Качество: =Обработка!$I$1
- Производительность: =Обработка!$J$1
В результате все элементы управления дашбордом готовы и настроены. Осталось закончить настройку обработки данных перед выводом на визуализацию с графиками и диаграммами. Там нам нужно заполнить ячейки незаполненных таблиц – формулами.
Обработка формул для динамических графиков и диаграмм в Excel
Перейдите на лист с именем «Обработка» и заполните диапазон ячеек D2:F2 ниже приведенной сложной формулой выборки значений по нескольким условиям из листа «Данные»:
После чего заполняем ячейки второй незаполненной таблицы в диапазоне H3:J14 другой сложной формулой:
Обработка данных – ГОТОВА! Сразу переходим к построению визуализации обработанных значений на дашборде.
Визуализация данных на интерактивном динамическом дашборде
На третьем шаге создания дашборда в Excel мы будем визуализировать исходные данные динамическими графиками и диаграммами. Начнем с создания динамических диаграмм, которые будут изменять свой внешний вид под воздействием элементов управления дашбордом.
Шаг третий – визуализация данных таблиц
На листе «ДАШБОРД» снова создайте фигуру прямоугольник с размерами 6,3 x 5 см. Преобразуйте ее в 3D фигуру выбрав инструмент: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Эффекты фигуры», здесь же «Заливка фигуры»-«Цвет»-Зеленый и «Контур фигуры»-«Нет контура»:
Переходим на лист «Обработка» выделяем диапазон ячеек D1:D3 и выбираем инструмент: «ВСТАВКА»-«Диаграммы»-«Кольцевая»:
Кликнув на кнопку плюс возле диаграммы снимаем все галочки с опций чтобы убрать лишние элементы.
Переносим диаграмму на лист «ДАШБОРД» и продолжаем ее форматировать. Кликнув правой кнопкой мышки по левой доли диаграммы вызываем контекстное меню из которого следует выбрать опцию «Формат точки данных». В появившемся дополнительном окне изменяем параметры: «Формат точи данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«Сплошная заливка»-«Цвет»-белый и здесь же «Прозрачность»-50%. А также «ГРАНИЦА»-«Нет линий»:
Снова кликнув правой кнопкой мышки по самой диаграмме и контекстным меню через опцию «Формат области диаграммы» открываем дополнительное окно где в разделе меню «ЗАЛИВКА» отмечаем опцию «Нет заливки» сделав таким образом диаграмму прозрачной. И здесь же отмечаем опцию «ГРАНИЦА»-«Нет линий».
Теперь кликаем правой кнопкой мышки по правой доли ряда данных диаграммы и из появившегося контекстного меню снова выбираем опцию «Формат точки данных» где изменяем только один параметр – цвет заливки на белый:
А сейчас кликаем правой кнопкой мышки по целому ряду диаграммы и выбираем из контекстного меню опцию «Формат ряда данных» где вносим свои параметры для добавления и настройки тени в окне: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ТЕНЬ»-«Размер»–105% и здесь же «Размытие»-15 пунктов и «Расстояние»-5 пунктов:
После чего добавляем на диаграмму 2-е надписи для которых настраиваем прозрачный фон, белый цвет шрифта и размер шрифта также как мы это делали с предыдущими надписями:
Надпись с показателем 48% будет динамически изменяться поэтому для нее следует сделать внешнюю ссылку на соответствующую ячейку. Для этого делаем активной надпись выделив ее одним кликом левой кнопкой мышки, а в строке формул вводим адрес для соответственной внешней ссылки =Обработка!D2 и нажимаем клавишу Enter на клавиатуре для подтверждения.
Далее создаем еще 2 диаграммы для дашборда по такому же принципу, как была создана первая диаграмма. Только эти диаграммы будут сделаны для диапазонов ячеек E1:E3 и F1:F3. У их прямоугольников будут другие фоновые цвета заливки: фиолетовый с кодом RGB: 159; 75; 228; и голубой с кодом RGB: 0; 182; 225:
Далее переходим к созданию многослойного динамического графика, на котором будет визуализироваться динамика изменений этих же показателей по месяцам.
График для отображения динамики изменения показателей за период
Перейдите на лист «Обработка» и выделите диапазон ячеек H2:J14 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«График»:
Не снимая выделения с графика выберите инструмент из его дополнительного меню: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Стили диаграмм»-«Стиль 6» и здесь же «Тип»-«Изменить тип диаграммы»:
В появившемся окне «Изменение типа диаграммы» для рада СЕРВИС выбираем тип «График с маркерами» для КАЧЕСТВО – «Гистограмма с группировкой» и для ПРОДУКТИВНОСТЬ – «График с областями и накоплением».
Изменяем цвета соответственно уже созданным диаграммам кликая по каждому ряду правой кнопкой мышки и выбирая из контекстного меню уже знакомую опцию «Формат ряда»:
Цвета фиолетовой гистограммы и голубого графика с областями немного сливаются, поэтому разграничим их тенями. Кликаем правой кнопкой мышки по фиолетовой гистограмме и вызываем контекстным меню инструмент: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Эффекты»-«ТЕНЬ»:
Также увеличиваем ширину столбцов фиолетовой гистограммы: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Боковой зазор» - 150 процентов.
Осталось лишь настроить внешний вид маркеров зеленого графика ряд СЕРВИС. Кликаем по нему правой кнопкой мышки и вносим параметры: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«ЗАЛИВКА»-«МАРКЕР»-«ПАРАМЕТРЫ МАРКЕРА»-«Размер»-9 и здесь же «ЗАЛИВКА»-«Сплошная заливка»-«Цвет белый»:
Ниже параметр «ГРАНИЦА»-«Сплошная линия»-«Цвет зеленый» и здесь же «Ширина»-2,25 пункта.
Для динамического изменения названия графика на листе обработка в ячейке B14 создадим следующую формулу:
Теперь мы будем просто ссылаться из названия графика на ячейку B14 также как мы это делали с подписями на диаграммах. Переносим график на главный лист «ДАШБОРД» и тестируем готовый результат:
Теперь при использовании элементов управления дашборд интерактивно взаимодействует с пользователем и динамически изменяет отображение диаграмм, графика и показателей.
Любую информацию легче воспринимать, если она представлена наглядно. Это особенно актуально, когда мы имеем дело с числовыми данными. Их необходимо сопоставить, сравнить. Оптимальный вариант представления – диаграммы. Будем работать в программе Excel.
Так же мы научимся создавать динамические диаграммы и графики, которые автоматически обновляют свои показатели в зависимости от изменения данных. По ссылке в конце статьи можно скачать шаблон-образец в качестве примера.
Как построить диаграмму по таблице в Excel?
- Создаем таблицу с данными.
- Выделяем область значений A1:B5, которые необходимо презентовать в виде диаграммы. На вкладке «Вставка» выбираем тип диаграммы.
- Нажимаем «Гистограмма» (для примера, может быть и другой тип). Выбираем из предложенных вариантов гистограмм.
- После выбора определенного вида гистограммы автоматически получаем результат.
- Такой вариант нас не совсем устраивает – внесем изменения. Дважды щелкаем по названию гистограммы – вводим «Итоговые суммы».
- Сделаем подпись для вертикальной оси. Вкладка «Макет» - «Подписи» - «Названия осей». Выбираем вертикальную ось и вид названия для нее.
- Вводим «Сумма».
- Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения.
- Уберем легенду (запись справа). Для нашего примера она не нужна, т.к. мало данных. Выделяем ее и жмем клавишу DELETE.
- Изменим цвет и стиль.
Выберем другой стиль диаграммы (вкладка «Конструктор» - «Стили диаграмм»).
Как добавить данные в диаграмму в Excel?
- Добавляем в таблицу новые значения - План.
- Выделяем диапазон новых данных вместе с названием. Копируем его в буфер обмена (одновременное нажатие Ctrl+C). Выделяем существующую диаграмму и вставляем скопированный фрагмент (одновременное нажатие Ctrl+V).
- Так как не совсем понятно происхождение цифр в нашей гистограмме, оформим легенду. Вкладка «Макет» - «Легенда» - «Добавить легенду справа» (внизу, слева и т.д.). Получаем:
Есть более сложный путь добавления новых данных в существующую диаграмму – с помощью меню «Выбор источника данных» (открывается правой кнопкой мыши – «Выбрать данные»).
Когда нажмете «Добавить» (элементы легенды), откроется строка для выбора диапазона данных.
Как поменять местами оси в диаграмме Excel?
- Щелкаем по диаграмме правой кнопкой мыши – «Выбрать данные».
- В открывшемся меню нажимаем кнопку «Строка/столбец».
- Значения для рядов и категорий поменяются местами автоматически.
Как закрепить элементы управления на диаграмме Excel?
Если очень часто приходится добавлять в гистограмму новые данные, каждый раз менять диапазон неудобно. Оптимальный вариант – сделать динамическую диаграмму, которая будет обновляться автоматически. А чтобы закрепить элементы управления, область данных преобразуем в «умную таблицу».
- Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».
- В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы:
- Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической:
Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» - «Таблица».
Как сделать диаграмму в процентах в Excel?
Представлять информацию в процентах лучше всего с помощью круговых диаграмм.
Исходные данные для примера:
- Выделяем данные A1:B8. «Вставка» - «Круговая» - «Объемная круговая».
- Вкладка «Конструктор» - «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами.
- Выбираем подходящий.
- Очень плохо просматриваются сектора с маленькими процентами. Чтобы их выделить, создадим вторичную диаграмму. Выделяем диаграмму. На вкладке «Конструктор» - «Изменить тип диаграммы». Выбираем круговую с вторичной.
- Автоматически созданный вариант не решает нашу задачу. Щелкаем правой кнопкой мыши по любому сектору. Должны появиться точки-границы. Меню «Формат ряда данных».
- Задаем следующие параметры ряда:
- Получаем нужный вариант:
Диаграмма Ганта в Excel
Диаграмма Ганта – это способ представления информации в виде столбиков для иллюстрации многоэтапного мероприятия. Красивый и несложный прием.
- У нас есть таблица (учебная) со сроками сдачи отчетов.
- Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
- Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
- Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» - «Использовать формулу для определения форматируемых ячеек».
- Вводим формулу вида: =И(E$2>=$B3;E$2 Готовые примеры графиков и диаграмм в Excel скачать:
Как сделать еженедельный график в Excel вместе с ежедневным.
Пример создания динамического синхронного еженедельного графика вместе с ежедневным. Синхронное отображение двух таймфреймов на одном графике.
В программе Excel специально разрабатывались диаграммы и графики для реализации визуализации данных.
Ольга Базалева, руководитель аналитического отдела в РБК, создатель онлайн-школы Excellent, автор книги «Мастерство визуализации данных» и автор блога, поделилась с Нетологией советами, как делать более удобные и понятные коллегам отчёты в Excel.
Сегодня не обойтись без умения работать с информацией: аналитикам, финансистам, маркетологам без этого совсем никуда, но даже в гуманитарных специальностях навык обрабатывать информацию всё больше и больше ценится. Взять хотя бы журналистику данных, которая набирает популярность.
Хотите, чтобы руководитель вас заметил и в глазах коллег вы стали классным профессионалом? Научитесь делать качественные отчёты.
ольга базалева
Создатель онлайн-школы визуализации данных Excellent
курс
Excel
Узнать больше
- Научитесь работать с любыми отчётами в Excel
- Разберётесь, как работать с формулами
- Сможете сортировать, анализировать и визуализировать данные
Встаньте на место начальника. Кому вы готовы платить больше? Специалисту, который присылает ему данные в таком виде?
1. Помните о наглядности
- Располагайте данные логично: обычно это от большего к меньшему.
- Всегда разделяйте разряды в больших числах: 10 000 000, а не 10000000.
- Убирайте лишние знаки после запятой.
- Убирайте яркую сетку, это визуальный мусор. Делайте линии светлыми, а иногда можно обойтись вовсе без сетки.
- Дизайн таблиц должен быть лаконичным и не отвлекать от сути.
- Выделяйте шапку и строки/столбцы с итогами.
- Выделяйте отрицательные значения красным, а второстепенные значения — серым. В Excel это можно сделать автоматически с помощью условного форматирования.
- Визуализируйте данные в таблицах. Можно строить гистограммы непосредственно в ячейках при помощи условного форматирования и создавать миниграфики, которые располагаются в одной ячейке (спарклайны).
Подборка курсов, бесплатных лекций и материалов по Excel
2. Используйте диаграммы
Визуально представленная информация в сравнении с обычным текстом и таблицами воспринимается быстрее и запоминается легче. Графики и диаграммы позволяют сразу увидеть тренды, взаимосвязи, всплески и падения.
3. Пишите выводы в сопроводительном письме
Если руководитель получил ответ на свой вопрос сразу из письма, ему даже не надо тратить время на открытие приложенного файла. Поверьте, это дорогого стоит.
Читать также
4. Делайте отчёты удобными
Если ваш отчёт большой и занимает 10−20 листов в Excel, сделайте навигацию по нему. Это можно реализовать одним из следующих способов:
- создать лист Summary, показать на нём главные цифры и сделать ссылки «Подробнее» с переходом на листы, содержащие все данные;
- сделать оглавление, куда добавить ссылки на каждый лист с аналитикой.
Читать также
5. Автоматизируйте обновление регулярных отчётов
Когда ваши отчёты станут обновляться в считаные минуты, высвободится масса дополнительного времени. Это время можно использовать для своего профессионального развития, а можно взять на себя дополнительную функциональность, что позволит обоснованно попросить повышения.
В Excel есть множество инструментов, которые помогут автоматизировать отчёты:
- настройте выгрузку всех необходимых данных на один лист в Excel, назовём этот лист «Source»;
- создайте необходимые аналитические таблицы на других листах;
- формулами в эти аналитические таблицы подтяните нужные данные с листа Source;
- сделайте связку Power Point — Excel. Тогда при изменении диаграмм в Excel, в презентации Power Point они будут обновляться автоматически.
Так вы создадите отчёт один раз и сможете быстро обновлять его в последующем, просто добавляя новые данные на лист Source, а все остальные таблицы и диаграммы тут же обновятся.
Когда ваши отчёты преобразятся, они станут в разы ценнее. Заказчики данных будут быстро получать ответы на свои вопросы, а набор сухих таблиц превратится в удобные и наглядные аналитические отчёты. Специалистов, которые умеют обрабатывать информацию и помогают принимать бизнес-решения ценят гораздо больше, чем тех, кто занимается простым заполнением непонятных таблиц.
Автор книги «Мастерство визуализации данных» и создатель блога data.vis Ольга Базалева поделилась приёмами создания профессиональных диаграмм в Excel.
В статье — продвинутые способы создания качественных диаграмм. С основными правилами визуализации данных можно ознакомиться в предыдущей публикации.
Приём 1: задать перекрытие столбцов на диаграмме
Когда и зачем применять. Когда нужно сравнить показатели текущего года и прошлого. Если на гистограмме или линейчатой диаграмме вывести текущий год на передний план, а прошлый оставить на заднем, информация будет легче считываться.
Как сделать:
Клик правой кнопкой мыши — ПКМ — по любому столбцу диаграммы.
Формат ряда данных.
Чтобы одни столбцы полностью перекрывали другие, выбирайте перекрытие 100%. Можно настроить другое соотношение перекрытия, например, 70%.
Приём 2: уменьшить расстояние между столбцами
Когда и зачем применять. Прием подходит для любых диаграмм динамики, например, результатов работы или выручки по месяцам. Сокращать расстояние между столбцами нужно, чтобы внимание не рассеивалось на них, динамика воспринималась целостной.
Как сделать:
ПКМ по ряду данных;
Формат ряда данных;
Выбрать боковой зазор в процентах.
Чтобы сохранить визуальную целостность и сделать акцент на данных, выбирайте зазор до 25-50%.
ольга базалева
Курс
EXCEL
Узнать больше
- Научитесь работать с любыми отчётами
- Узнаете, как сортировать и анализировать данные
- Сможете работать с формулами
Приём 3: отобразить среднее скользящее
Когда и зачем применять. Приём нужен при анализе динамики какого-либо показателя за длительный период с разбивкой по дням, когда ежедневные показатели значительно отличаются. Из-за сильных колебаний будет сложно рассмотреть тенденцию. Наглядность диаграммы улучшит скользящее среднее.
Как сделать:
- ПКМ по линии графика;
- Добавить линию тренда;
- Выбрать скользящее среднее;
- Установить период.
Инструмент сглаживает колебания и позволяет чётче увидеть тренд. Можно построить скользящее среднее за 21 день, 11, 5 или выбрать любое другое окно. Все зависит от уровня сглаживания, который вы хотите увидеть. Чем шире окно, тем более сглаженная линия.
Приём 4: оставить одну подпись на несколько диаграмм
Когда и зачем применять. Если вы построили две диаграммы с одинаковыми категориями данных на одном листе, оставьте одну подпись. Это поможет сэкономить место, избежать повторов одной и той же информации и исключить распространенную ошибку, когда на двух диаграммах категории расположены в разной последовательности.
Как сделать:
- Построить первую диаграмму;
- Расположить следующие поверх первой основной;
- Убрать с листа все лишние элементы: подписи рядов, линии сетки, белую заливку области диаграмм.
Подробнее процесс показан в видеоуроке.
Приём 5: добавить вспомогательную ось
Когда и зачем применять. Когда на одном графике отображается динамика двух показателей, которые сложно сравнить друг с другом. Например, показателей в рублях и процентах или с разным порядком чисел — выручкой в миллионном исчислении и средним чеком в тысячном. Ось помогает проще сопоставлять и считывать данные.
Подборка курсов, бесплатных лекций и материалов по Excel
Как сделать:
- ПКМ на ряду, который пойдет по вспомогательной оси;
- Формат ряда данных;
- Параметры ряда;
- Построить ряд «По вспомогательной оси».
Чтобы упростить восприятие информации, сделайте на диаграмме подсказки цветом. На примере все элементы ряда «Выручка», в том числе сами столбцы, название и подписи оси, окрашены в зеленый. Ряд «Средний чек» и его элементы — в красный.
На вспомогательной оси проще оценить динамику среднего чека, элементы двух рядов выделены разными цветами
Читать также
Подборка книг и блогов по визуализации данных
С выходом новой версии Microsoft Office появились и новые возможности. Разработчики доработали некоторые компоненты, сделали еще более удобным работу с программами. Нельзя обойти вниманием и Excel 2010 и новые возможности инфографики в нем. Поэтому в данной статье мы на примере расскажем, как работать с новыми компонентами Excel 2010.
Делаем сводную таблицу в Excel
В нашем распоряжении есть достаточно большая таблица. В ней огромное количество столбцов и строк. По этим данным нужно построить что-то вроде отчета, чтобы просмотреть результаты по какой-либо деятельности за определенный период. На вкладке «Вставка» нажимаем кнопку «Сводная таблица». Перед нами открывается диалоговое окно, в котором Excel в качестве диапазона данных выбрал всю таблицу. Нажимаем кнопку «ОК».
Теперь на новом листе появился макет сводной таблицы. В правой части окна перечислены все параметры, которые фигурировали в начальной таблице. Нам необходимо с помощью мыши перетащить их в поле «Название строк». В нашем случае это будут «Даты», «Менеджеры». Такие показатели как: «Объем продаж», «Выручка» и «Прибыль» мы перенесем в поле «Значения». Когда осуществляется перенос параметров в поле, таблица автоматически формируется и изменяется «на лету». Расположение элементов в «Название строк» играет большую роль. Если «Даты» будут расположены выше «Менеджеры», то данные будут разбиты на отдельные блоки по датам. Если же «Менеджеры» будут расположены в списке первыми, то сортировка будет проходить по именам сотрудников.
Когда таблица построена, перейдем к ее оформлению. Для начала изменим цветовую схему, применив к ней шаблон. Переходим на вкладку «Главная» и нажимаем на кнопку «Форматировать как таблицу». На экране появится список различных шаблонов форматирования, выбираем понравившийся нам и нажимаем на него. Excel автоматически определит границы таблицы, а если они окажутся заданы неверно, выделяем таблицу вручную и нажимаем кнопку «ОК». Таблица поменяла цветовую гамму и появилась возможность сортировки параметров.
Условное форматирование таблицы в Excel 2010
Не всегда удобно просматривать большое число значений и сравнивать их с плановыми. Предположим, что объем выручки на каждого менеджера в месяц должен составлять не менее 100 000 рублей. Но не обязательно оценивать показатели вручную, просматривая каждое значение: проще довериться встроенному компоненту Excel. Выделим область данных. Переходим на вкладку «Вставка — Условное форматирование — Набор значков» и из выпадающего меню выбираем понравившийся шаблон (допу́стим, светофор, так как с ним очень удобно работать). После выбора шаблона перед нами появится окно «Создание правил форматирования». Здесь необходимо напротив этих самых значков ввести показатели, при превышении которых работа сотрудника оценивается как: отличная, удовлетворительная и неудовлетворительная. Показатели вводятся в поле «Значение» напротив каждого из кружков, а параметр «Тип» в данном случае необходимо изменить с «Процент» на «Числа». В данном случае были заданы следующие показатели: 100 и 90 тысяч. (Третий параметр выставляется автоматически таким образом, чтобы включить все оставшиеся значения — в данном случае, меньше «удовлетворительного».) Нажимаем кнопку «ОК».
В ячейках всех значений появились кружки́ трех различных цветов. На основе представленной в таком виде информации намного проще оценить работу менеджеров за определенный период времени. Мы можем сравнить качество работы сотрудников, определить, кто из работников добивается наиболее выдающихся результатов, а кто, наоборот, требует к себе пристального внимания.
Но это еще не последний способ условного форматирования данных. В Excel 2010 появились такие инфографические элементы как «Гистограммы» и «Цветовые шкалы». Рассмотрим их более подробно. Выделим значения в ячейках и переедем «Вставка — Условное форматирование — Гистограммы». В выпадающем меню появится список шаблонов, при наведении на любой из них происходит предпросмотр результата. Выбираем понравившуюся цветовую схему и видим, что ячейки залиты горизонтальными столбцами разной величины. Они отображают в графическом виде те значения, которые присутствуют в ячейках. Если число будет введено со знаком минус, то график сместится в противоположную сторону от ячейки, указывая на отрицательные величины.
Компонент «Цветовые шкалы» заливает ячейку тем цветом, который соответствует введенному в нее значению. К примеру, ячейки с наименьшими значениями будет залиты красным цветом, со средними — желтым, а с наибольшими — зеленым. Цветовая схема может быть подобрана вами индивидуально, но суть примерно остается той же, что при использовании «Набора значков».
Срезы и не только
Но и это еще не все возможности визуализации данных, включенные в пакет Excel 2010. Рассмотрим еще такую удобную функцию, как «Срезы». Выбранные работники отработали в компании весьма внушительный срок и сложно при формировании сводной таблицы выделить ту или иную дату. Есть два способа добраться до определённой даты. Когда мы строим сводную таблицу, в правой части у нас расположены элементы, которые мы можем разместить в различные поля. Обращаемся к элементу «Даты» и вызываем выпадающее меню, путем нажатия на маркер со стрелочкой. Находим пункт «Фильтр по дате». Открывается огромный список с различными вариантами форматирования, нам нужна помесячная сортировка. Открываем «Все даты за период» и выбираем «Октябрь». Сводная таблица значительно сократилась, в ней остались значения только за октябрь. Это первый способ выборки данных.
Второй способ организуется с помощью новой функции «Срез» — интересного инструмента анализа цифровых данных. Перейдем к «Вставка — Срез». Открывается окно «Вставка среза», в нем нужно отметить тот показатель, по которому будет производиться выборка значений, то есть колонку таблицы, по которой вы сможете посмотреть срезы своего отчета. Отмечаем «Даты» и нажимаем кнопку «ОК». На листе отобразится рамка с записанными в нее значениями.
Перетащим ее в любое удобное для нас место и отрегулируем ее размер таким образом, чтобы можно было видеть все представленные в ней значения. Также можно изменить цвет среза, все шаблоны отображаются на верхней панели. Теперь мы можем одним щелчком выбрать определенную дату и увидеть, каких результатов за эти дни добились сотрудники. Данная функция на порядок удобнее, чем «Фильтр по дате», так как она более гибкая. С помощью нее вы можете выбрать сразу несколько значений, по которым будет идти выборка.
Инфокривые
Следующий способ визуального анализа данных — инфокривые. Делаем активной свободную ячейку напротив строк с данными. Во вкладке «Вставка» находим раздел «Инфокривые» (в использованной версии Excel 2010 они назывались почему-то «Сперклайны»). Выделяем диапазон данных (это будет наша строка), и нажимаем кнопку «ОК». Вы можете увидеть, как в выбранной нами ячейке построился мини-график, это и есть инфокривая.
Растянем эту ячейку на все остальные строки, потянув за край с точкой или сделав двойной клик на нем. При желании вы можете изменить стиль инфокривой, его выбор осуществляется на верхней панели в режиме конструктора инфокривой. Полученный график позволяет увидеть тенденцию, тренд. При огромном количестве данных инфокривая дает общий визуальный анализ всего множества. По ней с легкостью можно определить пики и падения, начало роста или его замедление.
Инфокривые бывают трех типов: «График» — как раз его мы и рассматривали; «Столбец» — отображает данные в виде маленьких столбцов, наглядно показывая максимальные и минимальные значения; «Выигрыш/проигрыш» — ячейка как бы разделяется на две части, и в нижней размещаются квадраты с отрицательными значениями, а в верхней — с положительными (ноль не отображается вовсе).
Вывод
Отталкиваясь от материала данной статьи, можно научиться не только быстро оформлять таблицу, но и проводить визуальный анализ данных. Мы познакомились с таким режимом, как сводная таблица, научились производить фильтрацию значений и условное форматирование цифровых значений, составлять срезы. Кроме этого, мы наглядно разобрались с новой функцией под названием «Инфокривые». Нельзя не отметить, что в Excel 2010 добавлены усовершенствования, и практически все новые функции направлены на облегчение труда специалиста и наглядное представление данных. Если вас заинтересовала новая функциональность табличного редактора MS Excel 2010, то вы можете приобрести Microsoft Office 2010 у партнеров компании 1CSoft.
Читайте также: