Чтобы анализировать большие массивы данных, не обязательно покупать дорогое программное обеспечение или разрабатывать сложные алгоритмы. Большинство функций заложено в сводных таблицах, которые легко построить в Excel, Google Sheets и других офисных программах. Рассказываем, как создавать, управлять такими таблицами и какую пользу они приносят бизнесу.
Подготовка данных
Чтобы корректно создать сводную таблицу, оперировать показателями, для начала подготовьте исходные данные. К формату такой информации предъявляют несколько требований:
- массив размещают в плоской таблице;
- у каждого столбца есть заголовок;
- в одном столбце — один формат данных: текст, число, дата;
- отсутствуют пустые строки, ячейки.
Если в таблице есть некорректные данные, заранее избавьтесь от них в исходном файле, чтобы не делать лишних действий потом. Проверьте форматирование: если 2 строчки из 50 будут в формате текста, а остальные — в формате дат, система не распознает их.
В качестве источника можно использовать часть таблицы. Если из массива данных нужны для анализа не все показатели, то поставьте рядом колонки с переменными, которые понадобятся.
Выбор инструмента для построения сводных таблиц
У большинства пользователей со словом «сводная таблица» связаны инструменты Excel. Сейчас собрать аналитический инструмент можно и в других аналогичных сервисах, программах. Рассказываем, где построить сводную таблицу, плюсы и минусы разных инструментов:
- Microsoft Excel. Самый популярный инструмент с наиболее полным функционалом: дополнительные вычисления, срез данных, группировка, настройка стиля. Сформировать таблицу просто. Нажимаете на Листе с исходными данными вкладку «Вставка», а затем команду «Сводная таблица». Пользователь выбирает диапазон данных, а также может сформировать ее на новом листе или на этом же.
- Google таблицы. Функционал уже немного урезан. Нельзя сделать дополнительные вычисления, менять стиль таблицы,увидеть срез данных. Преимущество — доступ к таблице сразу нескольким пользователям и возможность редактировать ее в онлайн режиме совместно.
- LibreOffice и OpenOffice. Инструменты подходят для большей части задач, но значительно уступают Excel. Минус — могут возникнуть проблемы с форматированием, а плюс — инструменты полностью бесплатные.
Если предстоит серьезная аналитическая работу, нужно увидеть тенденции, закономерности, рекомендуем пользоваться функционалом Excel. Для простых задач подойдут инструменты Google таблиц, LibreOffice и OpenOffice.
Шаги по созданию сводной таблицы
Не будем разбирать каждую функцию сводных таблиц, поскольку инструмент гибкий и возможности не охватить полностью в одной статье. Рассмотрим вкратце шаги, как создать простой, удобный массив данных на примере Excel:
- Выбираем источник данных. На Листе с массивом переходим во вкладку «Таблица», затем «Вставка» и нажимаем «Сводная таблица». Появится окно, где указан диапазон исходных данных и Лист, где создается сводная. Если диапазон нужен не весь, выделяете мышкой колонки, которые нужны, и нажимаете аналогичные команды.
- Столбцы для анализа. Выбираем, по каким столбцам нужно увидеть значения в сводной таблице. Перетаскиваем из списка, который появился справа, названия нужных столбцов в поле «Название строк». Если их несколько, сложится иерархия. В результате данные соберутся в одной колонке — сбоку остается место для их значений.
- Выбираем разрез. Если нужно посмотреть данные в динамике, перетаскиваем название столбца с этими разрезами в поле «Название столбцов». Если там были название месяцев, то пользователь увидит значение показателя за каждый месяц в динамике.
- Агрегирующие функции. Перетаскиваем в поле «Значения» название столбца с показателями, которые нужно вычислить. Здесь же можно выбрать способ вычисления: нажмите после перетаскивания на эту ячейку, выберите «Параметры полей значений» и укажите формулу, которая нужна. Например, количество показывает количество строчек с конкретным значением, а сумма складывает все значения по выбранной строчке и т.д.
Далее сформируйте сводную таблицу под свои цели. Пользователь может переставлять столбцы, строки, добавлять расчетные поля и т.д.
Интерпретация и анализ сводных таблиц
Главная сложность, с которой сталкиваются пользователи — правильная интерпретация получившейся формы. Если разобраться с главными функциями, свод покажется простым и понятным. Разберем основные моменты.
Чтение и понимание данных в сводных таблицах
Со столбцами и строками подобных проблем обычно не возникает. Сложность — понять, что означает конкретная цифра в ячейках. Чтобы понять идет ли речь о количестве строк с одним названием, сумме, процентах или доле, посмотрите в поле «Значения». Там в названии отражена формула, которую применили для вычислений.
Если в таблице есть иерархия, итоговые данные каждого уровня выделяются жирным цветом. Так пользователь понимает, где информация по позиции, а где — по группе.
Использование сводных таблиц для выявления трендов и паттернов
Пользователь может сформировать данные так, что увидит изменения показателей на протяжении нескольких периодов. Эти временные отрезки переносят в столбцы. В таком виде аналитики ищут закономерности, выявляют тенденции и паттерны. Еще один плюс такой формы — возможность увидеть зависимости между периодами или явлениями.
Ошибки, которые следует избегать при анализе сводных таблиц
Если не обратить внимание на боковую панель, любая ошибка в отчете может быть неправильно истолкована. Чаще всего неверная трактовка возникает из-за следующих недочетов:
- неправильная формула в колонке «Значения» — таблица показывает количество строк, а пользователь неверно может счесть это за сумму по столбцу
- исходные данные — неправильный формат приводит к тому, что сводная или не видит этих строк или пишет в них ошибку
- обновление — в исходных данных добавили новую информацию, но не обновили таблицу, тогда результат не поменяется
Чтобы не допускать ошибок, нужно правильно подготовить исходные данные и следить за панелью с колонками, за счет которых формируется таблица.
Дополнительные возможности и продвинутые техники
Если нужно составить детальный отчет, провести аналитический или маркетинговый анализ, смотреть на совещании данные в нескольких разрезах, сводная таблица предлагает дополнительный функционал. Посмотрим, как они реализованы и для чего нужны.
Фильтрация и сортировка данных в сводных таблицах
Автоматически данные встают в колонки и столбцы в произвольном порядке. Чтобы нагляднее увидеть картину по данным, используйте функции сортировки и фильтра. Убрать лишние данные можно несколькими способами:
- перенести в поле «Фильтр» колонку, по которой нужно отобрать значения, нажать на нее и выбрать, какие данные отображать в сводной;
- нажать на треугольник, который находится в названии строки или столбца и воспользоваться фильтром
Чтобы сортировать данные внутри таблицы, нажмите на тот же треугольник и выберите тип сортировки. Второй вариант — нажать на ячейку внутри таблицы правой кнопкой и выбрать функцию «Сортировка».
Создание дополнительных расчетных полей
Если нужно сделать дополнительные расчеты, которые не получается сформировать с помощью панели, воспользуйтесь другой функцией. Переходите во вкладку «Анализ» — «Вычисление» — «Поля, элементы и наборы» — «Вычисляемое поле».
Далее введите имя нового поля и формулу, по которой оно будет вычислять значения между колонками. Затем оно появится в правой панели. Управляйте им аналогично, как и полями из исходной таблицы.
Использование сводных таблиц в различных областях бизнеса
Инструмент открывает множество возможностей для аналитики. Именно поэтому таблицы используют в разных отделах компании:
- Бухгалтерия. Можно быстро анализировать экономические показатели, статьи расходов, динамику, а значит прогнозировать будущие результаты.
- Маркетинг. Помогает собирать информацию по клиентской базе, результаты рекламных кампаний и другие данные по продажам.
- Исследования. Инструмент помогает анализировать большие статистические данные, рассматривать в нескольких разрезах результаты экспериментов, искать закономерности.
Также сводные таблицы используют для процессов логистики, анализа социальных данных, в разработке. Инструмент не требует дополнительных вложений, но дает результаты не хуже других аналитических сервисов.
Выводы
В статье рассмотрели только базовые функции сводных таблиц, а также часть областей, где их применяют. Инструмент считается гибким, функциональным и не требует длительного изучения. Функции подойдут для практического применения в любой области, где нужно анализировать большие массивы данных.