Сводные таблицы в Excel — как создать и работать с ними

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

В работе Excel полезен для многих специалистов. Более комплексно программу изучают на курсах для аналитиков данных и бухгалтеров. 

Для чего нужны сводные таблицы 

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

Удобно и то, что сводную таблицу можно редактировать под свои запросы. Например, добавлять фильтр, менять расположение ячеек, добавлять новые и вводить нужные категории. Ещё один плюс сводной таблицы в быстроте её создания — готовый отчёт легко сделать за 5-10 минут. Допустим, вы работаете в агентстве недвижимости. Каждый сотрудник, осуществляющий сделки, вносил информацию о продажах в таблицу. В ней есть все необходимые вводные — площадь, количество комнат, район, стоимость, инициалы агента и другие характеристики. В конце квартала необходимо собрать данные и значения о всех продажах, чтобы проанализировать их. В статье расскажем, как составить для этого сводную таблицу в нужном виде для этого примера. 

Создание сводной таблицы

Перед тем, как группировать данные в сводную таблицу, нужно проверить требования к исходной таблице. В каждом столбце должен быть заголовок. В одном столбце размещается только один формат — например, текст или число. Проверьте, чтобы отчёт по умолчанию был без пустого места (строк и ячеек). Если они пустые: либо удалите, либо заполните их.

После этого в Microsoft Excel в меню нажмите на вкладку «Вставка» и выберите кнопку «Сводная таблица». Перед вами появится окно, где можно выбрать подходящий диапазон для экспорта, и тип листа, куда будут перенесены все данные для создания сводной таблицы. Нам надо выделить всю таблицу и выбрать «новый лист», а затем нажать «ок» в окне.

Теперь работа над сводной таблицей будет идти с новым листом отчёта. Вы можете сразу изменить его название, чтобы не запутаться в таблицах и границах области. Сводная таблица будет располагаться слева, а с правой стороны вы увидите область под названием «Поля сводной таблицы» (в зависимости от версии Excel поле может находиться как во вкладке на правой стороне, так на левой). Она нужна для настройки параметров и функций сводной таблицы. 

Настройка параметров и фильтров 

«Поля сводной таблицы» состоят из различных составных частей будущей сводной таблицы. Они объединены в один блок и находятся наверху. Вы можете увидеть названия столбцов — «Квадратные метры», «Количество комнат», «Жилой комплекс», «Район», «Цена», «Дата продажи», «ФИО агента». 

Чуть правее есть параметры в меню, обладающие важным функционалом:

• Значения — для выбора необходимых полей и произведения вычислений в сводной таблице. По стандарту данные суммируются, но есть и другие варианты. Можно умножить, выбрать среднее, стандартное или несмещённое отклонение, максимум или минимум. Результат зависит и от формата данных в сводной таблице. Для расчёта общей стоимости подойдёт операция сложение, при работе с текстовым форматом можно просмотреть количество соответствующих заданному параметру ячеек.
• Строки и столбцы — нужны для отражения расположения полей и областей. При выборе строк поля будут располагаться соответствующим образом, если выбрать столбец, то поля будут в виде него.
• Фильтры — благодаря им происходит фильтрация данных в сводной таблице. Фильтры находятся отдельно от общей таблицы, поэтому можно выбрать, какие данные нужно отразить обязательно, а какие стоит убрать. Так легко увидеть, в каких жилых комплексах чаще всего продаются квартиры, или понять, на какую стоимость агенты продали недвижимость в апреле или мае.

Работать с настройками сводной таблицы можно несколькими способами. Быстрее всего поставить галочку возле поля. Программа автоматически «подтянет» данные из исходной таблицы. Однако этот вариант не самый надёжный. Excel может создать и расположить данные так, что их потом будет неудобно анализировать. Лучше сделать это вручную: нужно выбрать подходящие поля и добавить их в желаемую область. Нам важно, чтобы в сводной таблице были отражены фамилия, имя и отчество того, кто совершил продажу, квадратные метры квартиры и её стоимость. Другие поля и разделы в панели пригодятся для настройки фильтров.

Погрузиться в тонкости работы с Microsoft Excel, узнать о лайфхаках и фишках, которые помогут оптимизировать выполнение даже самых сложных задач, вы можете на онлайн-курсах. Мы собрали проверенные варианты быстрого обучения Excel в небольшой список, курсы подойдут как новичкам, так и профессионалам своего дела:

• Excel для анализа данных — Skypro
• Excel. Базовый уровень — Бруноям 
• Excel для бухгалтера уровень PRO — Высшая школа Главбух

Фамилии агентов лучше расположить построчно. Для этого первым нужно зажать «ФИО агента» и перенести в «Строки», которые находятся ниже. Вы увидите, как слева отобразятся фамилии всех сотрудников, занимающихся продажами. Перенесём остальные параметры и данные в сводную таблицу — «Количество комнат» также надо перетащить в «Строки». В левой части появится ещё один блок. Сводная таблица автоматически распределит данные по квартирам между менеджерами, которые их продали. Нам ещё понадобится площадь и стоимость квартир. Для этого переносим поля с названиями столбца «Квадратные метры» и «Цена» в значения.

Перед нами появилась уже готовая сводная таблица с необходимыми категориями. Мы можем посмотреть, какие именно квартиры продал каждый сотрудник, какая площадь у них была и сколько они стоили. Для менеджера формируется итоговая заработанная за квартал сумма. Вы можете добавить в сводную таблицу те поля, которые нужны для отчётности. Например, посмотреть, в каких ЖК и местах больше всего новых продаж. Или оценить стоимость проданных жилых помещений по районам, где располагаются многоквартирные дома.

Фильтры

Для фильтрации нужно использование поля «Фильтры», описанное выше. В него нужно перетянуть в сводную таблицу все оставшиеся поля: «Жилой комплекс», «Район», «Дата продажи». Фильтрация будет отображаться над основной сводной таблицей. Мы можем попробовать отфильтровать данные по пункту «Жилой комплекс». Для этого достаточно выбрать подходящий вариант ЖК в блоке из фильтров и отменить значение «Выделить все». После закрытия окна в сводной таблице будут отображаться только те квартиры, которые продали в этом ЖК за весь отчётный период. Фильтры, как и другие параметры, удобны тем, что их можно настраивать под разные запросы и списки. Вы можете создать их все или выбрать для сводной таблицы тот, что нужен, как в нашем примере. О применении Excel для решения рабочих задач, оптимизации трудозатрат, повышения качества и эффективности работы подчиненных подробно рассказывают на курсе «Excel для карьеры и поиска работы».

Дополнительное вычисление 

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

Нажимаем на любую ячейку с ценой товара в сводной таблице. Щёлкните левой кнопкой мыши на «Дополнительные вычисления» и «% от общей суммы». Окно с разными типами значений закроется, а вместо цен в рублях будут проценты — как он продажи каждой квартиры, так и общие. Убирать полученные данные быстро можно при помощи знака «–», который находится рядом с ФИО работника. В дополнительных вычислениях сводной таблицы можно рассчитать следующий процент от суммы по столбцу или строке. 

Обновление данных

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

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

Итоги

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