Что такое сводная таблица в Excel, зачем она нужна и как её создать с нуля. Пошаговое руководство по созданию первой сводной таблицы, переносу полей, сортировке и фильтрации по сотрудникам и товарам, а также разбор базовых функций сводных таблиц.
Сводные таблицы в Excel с нуля: зачем нужны и как создать (Часть 1)
Сводные таблицы в Excel: фильтры, обновление и макросы (Часть 2)
Сводные таблицы в Excel с нуля: зачем нужны и как создать (Часть 1)
Сводная таблица — мощный и эффективный инструмент Excel для анализа данных. Если у вас есть большая таблица с сотнями или даже тысячами строк, вручную сводить итоги и вычислять показатели — задача не из лёгких. Наверняка вы сталкивались с ситуацией, когда нужно быстро получить сумму продаж по каждому менеджеру или подсчитать количество проданных товаров по категориям.
Сводные таблицы позволяют сделать это буквально в несколько кликов, автоматически обобщая данные и выделяя важные закономерности. Давайте разберёмся, зачем нужны сводные таблицы и как создать свою первую сводную таблицу с нуля.
Что такое сводная таблица и зачем она нужна
Сводная таблица в Excel — это интерактивный сводный отчёт, который быстро обобщает большие объёмы данных и помогает анализировать информацию под разными углами. Проще говоря, сводная таблица собирает информацию из исходного набора данных и отображает сводные показатели: суммы, количества, средние значения и т.д. Это как волшебная «сводка», где вы сами выбираете, что хотите увидеть.
Зачем нужны сводные таблицы?
Представьте, что вы ведёте учет продаж в Excel. Каждый день ваши сотрудники добавляют новые строки: кто продал, какой товар, в каком регионе, на какую сумму. Через месяц таких записей накапливается огромное количество. Руками подсчитывать итоги по каждому сотруднику или по каждому товару очень долго. Вот основные преимущества, которые дают сводные таблицы:
- Мгновенное подведение итогов: Сводная таблица за секунды суммирует или подсчитывает данные по заданным категориям (например, суммарную выручку каждого менеджера).
- Гибкая группировка данных: Вы легко переключаете «разрез» анализа – например, сегодня смотрите продажи по сотрудникам, а завтра по товарам или регионам – не создавая новых формул.
- Фильтрация и сравнение: Можно быстро отфильтровать данные по интересующему критерию (например, показать продажи только выбранного товара) и сравнить показатели.
- Поиск закономерностей: Благодаря сводным таблицам выявляются тренды и аномалии. Например, видно, в каком месяце продажи выросли или какой товар отстаёт по продажам.
Иными словами, сводная таблица превращает большой «плоский» список данных в информативный отчёт. Визуализация тоже становится проще: на основе сводной таблицы буквально одним кликом строятся диаграммы, наглядно показывающие анализ.
Как создать сводную таблицу с нуля
Перейдём к практике. Создание сводной таблицы в Excel на самом деле довольно простое, даже если вы ни разу этого не делали. Рассмотрим пошагово на примере.
Предположим, у нас есть исходная таблица продаж с колонками: Дата, Регион, Менеджер, Товар, Количество, Выручка. В ней много строк с подробными транзакциями. Наша задача – получить сводный отчёт, например: суммарную выручку по каждому менеджеру и каждому товару.
Шаг 1. Подготовьте данные.
Убедитесь, что исходная таблица оформлена правильно:
- В первой строке таблицы должны быть заголовки столбцов (наименования полей), например: Дата, Регион, Менеджер, Товар, Количество, Выручка.
- В таблице не должно быть полностью пустых столбцов или строк. Также убедитесь, что каждый столбец содержит данные одного типа: в столбце Дата только даты, в Выручка только числовые значения и т.д.
- Желательно оформить диапазон данных как умную таблицу Excel (вкладка Вставка → Таблица). Это поможет при обновлении данных, но на первом этапе не обязательно.
Шаг 2. Вставьте сводную таблицу.
Выделите любую ячейку внутри вашей исходной таблицы с данными. Далее перейдите на вкладку Вставка на ленте и нажмите кнопку Сводная таблица (Pivot Table). Excel предложит диалоговое окно: - Выбор диапазона данных: обычно Excel автоматически подхватывает весь диапазон вашей таблицы.
Проверьте, что область выбрана правильно (например, Лист1!$A$1:$F$500). - Куда поместить сводную таблицу: рекомендуется выбрать «Новый лист», тогда сводная таблица появится на отдельном новом листе. Так удобнее работать, чтобы не мешала исходная таблица.
Нажмите OK, и Excel создаст пустую сводную таблицу на новом листе. Пока она пустая, но справа на экране вы должны увидеть панель Поля сводной таблицы.
Шаг 3. Панель полей сводной таблицы.
Это сердце настройки сводного отчёта. В верхней части панели перечислены все заголовки полей из вашей исходной таблицы: Дата, Регион, Менеджер, Товар, Количество, Выручка. А ниже – четыре области для настройки:
- Фильтры (Report Filter) – сюда можно поместить поле, по которому будет фильтроваться весь отчёт (например, поле Дата, чтобы отчёт показывал данные за определённый период).
- Колонки (Columns) – сюда перетаскивают поле, значения которого станут заголовками столбцов сводной таблицы.
- Строки (Rows) – сюда поместите поле, значения которого будут перечислены в левой колонке сводной таблицы (заголовки строк).
- Значения (Values) – здесь указываются поля, по которым будут рассчитываться числовые итоги (суммы, счёт, средние и т.д.).
Шаг 4. Формирование сводной таблицы – перетаскиваем поля.
Теперь начинается самое интересное: мы «конструируем» отчет, перенося нужные поля в нужные области. Сделаем сводную таблицу, показывающую сумму выручки по каждому менеджеру и товару:
- Менеджер – перетащите этот элемент мышкой в область Строки. В сводной таблице сразу появятся списком все имена менеджеров из исходных данных, каждый в своей строке.
- Товар – перетащите поле Товар в область Колонки. Теперь в верхней строке сводной таблицы отобразятся названия всех товаров (категории товаров) в отдельных колонках.
- Выручка – перетащите поле Выручка в область Значения. По умолчанию Excel суммирует числовое поле, поэтому в ячейках сводной таблицы вы увидите суммы выручки.
Отлично! Мы получили первую сводную таблицу. В первой колонке сводного отчёта перечислены менеджеры, в первой строке – товары, а на пересечении – сумма выручки данного менеджера по данному товару. Также Excel автоматически добавил итоги по строкам и столбцам: итого по каждому менеджеру (строка Итого) и итого по каждому товару (столбец Итого).
Сортировка и фильтрация данных в сводной таблице
Сводная таблица уже сейчас даёт ценную информацию, но зачастую её нужно отфильтровать или отсортировать для более глубокого анализа. К счастью, в сводной таблице сразу доступны средства фильтрации и сортировки, похожие на обычные фильтры Excel.
Сортировка.
Допустим, мы хотим узнать, какой менеджер принес наибольшую выручку в целом. В нашем отчёте это можно сделать так: - Наведите курсор на любое значение в столбце Итого по менеджерам. Например, на сумму выручки первого менеджера. - Кликните правой кнопкой и выберите Сортировка → По убыванию.
Excel отсортирует строки сводной таблицы так, что менеджер с наибольшей общей выручкой окажется первым. - Аналогично можно сортировать и по любому конкретному товару: щёлкните правой кнопкой по цифре в колонке нужного товара и выберите сортировку.
Фильтрация.
Часто бывает нужно сосредоточиться на конкретных данных, скрыв всё лишнее. Сводные таблицы позволяют гибко фильтровать информацию:
- Фильтр по значениям строк или столбцов: Рядом с названиями полей Менеджер и Товар (над сводной таблицей) есть небольшие кнопки со значком фильтра (стрелочка). Если нажать на кнопку возле Менеджер, откроется список всех менеджеров с флажками.
Можно снять отметки со всех и выбрать, например, только одного сотрудника – тогда таблица покажет данные только по нему. Аналогично и с товарами. - Фильтр отчёта: Мы пока не добавили поле в область Фильтры, но можем это сделать. Если, скажем, добавить поле Регион в секцию Фильтры, то над сводной таблицей появится выпадающий список Регион.
Выбрав в нём конкретное значение (например, "Север" или "Юг"), вы отфильтруете весь отчёт по выбранному региону – сводная таблица будет показывать данные только по этому региону. - Срезы: Более наглядный способ фильтрации – использовать срезы. На вкладке Анализ (или Работа со сводными таблицами) есть кнопка Вставить срез. Срез – это отдельное окно с кнопками для быстрого фильтра по значению поля.
Например, можно вставить срез по полю Товар, и тогда на листе появятся яркие кнопки с названиями товаров. Кликайте по любому товару – и сводная таблица тут же покажет данные только для него. Срезы особенно удобны, когда вы демонстрируете отчёт и хотите интерактивно переключать фильтры.
Комбинируя сортировки и фильтры, вы можете ответить на конкретные вопросы. Например, кто из менеджеров выполнил план по продажам определённого товара или какие товары проседают в конкретном регионе. Сводная таблица позволяет за минуты получить ответ, на который вручную ушли бы часы работы с формулами.
Кстати, если вам нравятся такие полезные советы по Excel, не забудьте подписаться на наш Telegram-канал и канал на Дзен, где мы регулярно делимся фишками и примерами. Будем рады также видеть ваши вопросы или истории в комментариях!
Базовые функции и вычисления в сводных таблицах
Наша первая сводная таблица складывала выручку, но сводные таблицы умеют не только суммировать. Давайте посмотрим на базовые функции, которые можно применить к полям Значений:
- Сумма: стандартно применяется к числовым полям, как в нашем примере с выручкой.
- Количество: если перетащить в значения текстовое поле (например, Товар), Excel автоматически переключится на функцию "Количество", то есть будет считать число записей. Вы также можете явно задать "Количество" для числового поля, если нужно посчитать количество транзакций, а не сумму.
- Среднее: рассчитывает среднее значение по выбранному полю. Полезно, если вы хотите узнать, например, средний чек продажи.
- Максимум и Минимум: отображают соответственно максимальное или минимальное значение.
- Произведение, Стандартное отклонение, Дисперсия: эти и некоторые другие статистические функции доступны в опциях, хотя используются реже.
Чтобы изменить функцию агрегации, кликните по любому числу в поле Значения сводной таблицы правой кнопкой мыши и выберите Значения поля → Настройка поля значений.... В открывшемся окне можно выбрать нужную функцию: сумму, среднее, максимум и т.д.
Там же доступны и дополнительные вычисления, например отображение значений в процентах от итога или накопительным итогом. Попробуйте поэкспериментировать с этими настройками — сводная таблица мгновенно пересчитает результаты под новую функцию.
Кроме того, Excel позволяет добавлять в сводную таблицу вычисляемые поля. Это более продвинутый функционал: вы создаёте пользовательскую формулу прямо внутри сводной таблицы на основе существующих данных.
Например, если в исходных данных нет поля "Средний чек", вы можете вычислить его, разделив Выручку на Количество продаж через вычисляемое поле. Но такие возможности мы подробно рассмотрим уже в следующей части.
Заключение
В первой части мы разобрали основы работы со сводными таблицами: от создания до базовых операций сортировки и фильтрации. Теперь вы знаете, зачем нужны сводные таблицы и как буквально за несколько минут подготовить из сырых данных аккуратный сводный отчёт.
Важно не бояться экспериментировать: вы всегда можете поменять поля местами, добавить или убрать их – Excel перестроит отчёт автоматически.
В следующей части мы углубимся в более продвинутые приёмы: рассмотрим тонкости настройки фильтров (например, использование нескольких фильтров и срезов одновременно), расчёты показателей по регионам, правильное обновление данных источника и связанные с этим нюансы. Кроме того, поделимся полезными советами, как упростить работу со сводными таблицами с помощью макросов VBA.
Чтобы не пропустить продолжение и новые уроки, подписывайтесь на наш Telegram и Дзен каналы.
А если у вас уже есть опыт работы со сводными таблицами, расскажите об этом в комментариях – какие открытия сделали вы? Нам очень важно знать ваше мнение!