Визуализация данных в электронных таблицах: Google и Excel

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

Что значит визуализация данных

Визуализация данных в программе Excel – эффективный метод трансформации комплексных наборов данных в интерактивные, интуитивно понятные визуализированные материалы, комфортные для анализирования и восприятия пользователем.

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

Виды диаграмм в Эксель и когда их использовать

Основные типы диаграмм:

• Столбчатые – для сопоставления информации между различными группами (сведения могут быть относительными или абсолютными).
• Линейные – для отслеживания изменения данных во времени.
• Круговые – для отображения процента каждого компонента в суммарном объеме информации.
• Гистограммы – для графического представления распределения сведений и сопоставления значений (относительных) между разными группами.
• С областями – схожи с линейными графиками, но зоны под линиями подчеркнуты цветом для лучшей визуализации набора сведений и их динамики во времени.
• Точечные – визуальное отображение зависимости между 2 числовыми переменными.
• «Водопады» (мостовые или каскадные), показывающие накопленную динамику значения, поочередно представляя негативные и позитивные значения факторов.
• Тепловые карты или heatmap – диаграммы, использующие цвета для презентации значений информации.

Инструкция по формированию диаграммы в программе Эксель

Порядок постройки диаграммы в офисном приложении Эксель следующий:

1. Подготовка данных. Перед формированием графика удостоверьтесь, что информация организована в табличном виде.
2. Выборка. Выделите интервал сведений для визуала и проконтролируйте, что собраны все нужные сведения и наименования столбцов.
3. Вставка диаграммы. В опции «Вставка» отметьте тип диаграммы, оптимальный для представления соответствующей информации.
4. Настройка графика. В настройках «Формат» и «Конструктор» сможете поменять цветовые схемы, настроить оси, добавить к графикам легенды и названия.

Настройка диаграмм

Сделать визуализацию данных в Гугл-таблицах понятной и эстетичной позволит настройка графиков:

• Стили и темы. Можно избрать предложенные методы либо создайте свой стиль или персональную тему. Чтобы это сделать, перейдите в настройку «Конструктор» и далее в «Стили диаграмм».
• Заголовки и подписи. Для их создания откройте опцию «Макет» и затем «Метки данных» и «Заголовок диаграммы».
• Работа с осями. Кликните правой клавишей мышки по оси и затем по параметру «Формат оси», где сможете менять числовое представление, настраивать лимиты значений.
• Добавление изображений и аннотаций. Это позволит подчеркнуть главные моменты и придать графику больше информативности.

Как сформировать дашборд в Google-таблицах

Сначала создайте новую Google-таблицу и присвойте ей имя, например: «Дашборд 2». Предварительно сформируйте 2 листа файла:

• «Объединенная таблица» – тут будут «склеиваться» различные таблицы Google.
• «Дашборд» – макет создаваемого dashboard: для наполнения его информацией из сводных табличных форм.

На вкладке «Сводная таблица 2» увидите область для установления индикаторов к визуалам. Она создастся в авторежиме после интеграции источников сведений – ей нужно будет лишь дать наименование. Первым делом сформируйте задачи и цели создаваемой инфопанели и установите, какую информацию она должна включать. Затем зайдите во вкладку «Вставить диаграмму». На листе отобразится пустой прямоугольник (плитка), где будете размещать визуализированные компоненты.

Каждая последующая плитка добавляется обычным копированием. По линиям-подсказкам сможете выровнять блоки для создаваемых визуалов. Обязательно оставляйте место для названия.

Далее подключите сведения из различных Google-таблиц. Например, у нас имеются файлы «Таблица 1» и «Таблица 2» со следующим набором столбцов:

• № заказов;
• товарные секторы;
• ФИО менеджеров;
• названия заказов;
• контрагенты;
• локация;
• план;
• факт;
• даты оформления сделок;
• даты расчета за продукцию;
• статусы офферов.

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

Далее в ранее сформированный лист «Объединенная таблица» подтяните соответствующие источники. Сделать это можно посредством:

• SQL «select * where Col1 is not null order by Col10»;
• { };
• IMPORTRANGE или QUERY.

Теперь сгенерируйте сводную таблицу и выберите соответствующие сведения. На листе «Объединенная таблица» поставьте курсор на незанятой ячейке и перейдите в опцию «Вставка» → «Создание сводной таблицы».

Проверьте «Диапазон данных»: укажите 1-й столбец и его 1-ю строчку (не включайте наименование) и заключительный столбец (не указывайте последнюю строчку), далее выберите «Новый лист» – «Создать» и именуйте его «Сводная таблица 2» – в нее будут собираться индикаторы для 1-го визуального компонента.

После этого проведите выборку сведений. На листе со сводной таблице выделите ячейку со значением – отобразится панель, в которой выберите индикатор «Факт, тысяч рублей»: он переместится в выбранную ячейку.

Для расчета реализации планового показателя поделите «Факт» на «План». В комбинированной таблице скопируйте ячейку с «фактом» и вставьте рядом. Затем в панели («Значения») выберите 2-й индикатор – «План, тысяч рублей» – он отобразится рядом.

В следующей ячейке запишите через символ «=» стандартную формулу деления: получите коэффициент реализации плана – останется лишь поставить подпись под рассчитанным результатом.

Аналогично можно вычислить индикатор «Конверсия», разделив количество офферов с пометкой «Закрыто» на их суммарный объем.

Далее копируйте 1-ю сводную таблицу, установите курсор на свободном поле, удалите в нем прежние опции, выберите «Значения» → «Факт, тысяч рублей». В поле «Суммировать по» кликните в перечне по «COUNT» (вместо «SUM») – эта операция суммирует в столбце число ячеек, вошедших в фильтр. В «Фильтре» отметьте «Закрыто» и нажмите кнопку «ОК».

Аналогично подсчитывается суммарный объем офферов и затем вычисляется конверсия.

Теперь добавьте карточки KPI. В предварительно разработанный макет вставьте информацию для них:

• доход от реализации;
• реализация плана;
• объем офферов;
• конверсия офферов.

Для чего дважды нажмите мышкой на желаемую плитку: запустится «Редактор диаграмм»:

• В опции «Вид диаграммы» отметьте столбчатую → «Сводка».
• Укажите ячейку со значением для 1-й карточки.
• Отметьте адрес ячейки («Сводная таблица 2’!A2»), затем кликните по «ОК».

Индикатор отобразится на желаемой плитке KPI. Чтобы отредактировать единицу измерения, поставьте курсор на нужную ячейку, перейдите в «Формат» → «Числа» и отметьте, допустим, рубль РФ.

Для добавочных функций визуальных элементов щелкните 2 раза по желаемой плитке – справа отобразится функция «Редактор диаграмм» с опциями «Добавочные» и «Настройки».

Теперь можно сформировать графики. Для создания дашборда в Google-таблицах предусмотрены различные типы визуалов. В нашей ситуации будут использоваться столбчатые диаграммы для индикаторов изменения продаж и статистической информации по различным группам.

На листе «Сводная таблица 2» скопируйте ячейки с информацией 1-й карточки и вставьте в свободную локацию на листе. Выберите в скопированной сводной табличной форме желаемую ячейку. Во всплывшей панели удалите значения предшествующего визуализированного компонента.

Выберите для создаваемого графика «Динамика продаж» характеристику «Значения» для оси Y и строчки для X. Для правильного отображения дат по X отфильтруйте их по месяцу и году. «Итог» в сформированной комбинированной табличной форме лучше удалить, поскольку как визуальный элемент этот параметр бесполезен.

Далее перейдите на лист «Дашборд», двойным кликом выделите желаемый компонент на макете. В «Редакторе диаграмм» выберите вид визуала, укажите адреса ячеек сведенной табличной формы с нужного листа. Затем выберите компонент для Y и значение для X – получите график изменения продаж.

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

Идентично соберите и настройте остальные плитки о продажах по:

• продавцам;
• районам;
• сегментам.

Актуализация данных в дашборде происходит в авторежиме при обновлении информации в любой из подсоединенных табличных форм. Допустим, добавьте в «Таблицу-источник 1» 1 строчку: ее нужно лишь копировать, поменяв «Дату оффера» (поскольку в нашем примере «QUERY» прописан по времени). Актуализированные данные тут же отобразятся в файле с dashboard: и на листе «Объединенная таблица», куда транслируются сведения из источников, и на визуалах.

Рекомендации, как лучше визуализировать информацию в Excel

Лучшие практики по визуализации данных в Эксель:

• Подбирайте оптимальную разновидность графика под соответствующую задачу. Это позволит лучше презентовать данные и сделать информацию понятнее.
• Периодически обновляйте сведения. Проконтролируйте, что графики также актуализируются в автоматическом режиме.
• Применяйте минималистический подход – не перегружайте графику. Удостоверьтесь, что он передает ключевые данные и с легкостью прочитывается.
• Проверяйте точность источников. Перед формированием диаграммы удостоверьтесь, что сведения являются верными.
• Применяйте имеющиеся в Excel фильтры и формулы (совместно с графиками) для углубленного анализирования информации.

Заключение

Визуализация данных в электронных таблицах позволит трансформировать «голые» цифры в содержательные сведения. В офисной программе Excel представлены универсальные инструменты для эффективного отображения информации. Используя приведенные выше примеры визуала в бизнесе, значительно улучшите наглядность и результативность презентаций, аналитических материалов и отчетных форм.