Как сделать сводную таблицу в Excel: пошаговая инструкция

Что такое сводная таблица и когда она нужна

Сводная таблица — это инструмент Excel, который группирует и суммирует данные из обычной таблицы по выбранным полям, не трогая при этом сами исходные данные. Она нужна, когда есть список записей (продажи, заказы, расходы) и нужно быстро получить итоги — по категориям, датам, сотрудникам — без написания формул СУММЕСЛИМН или СЧЁТЕСЛИМН вручную под каждый новый разрез данных.

Шаг 1. Подготовка исходных данных

Прежде чем строить сводную таблицу, стоит проверить исходный диапазон:

• у каждого столбца есть заголовок в первой строке, и заголовки не повторяются;
• в диапазоне нет полностью пустых строк или столбцов — они разрывают диапазон на части;
• в одном столбце данные одного типа (не смешаны числа и текст в одном поле, например «1200» и «около 1200 руб.»);
• нет объединённых ячеек — сводная таблица воспринимает такой диапазон заполненным только в первой ячейке.

Удобнее всего заранее оформить исходные данные как «умную таблицу» (выделить диапазон и нажать Ctrl+T) — тогда сводная таблица будет автоматически расширяться при добавлении новых строк снизу, и источник данных не придётся менять вручную.

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

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

Шаг 3. Распределение полей по областям

После создания появится панель «Поля сводной таблицы» с четырьмя областями:

• Фильтры — поле, по которому можно отфильтровать всю сводную таблицу целиком (например, показать данные только за один регион);
• Столбцы — значения этого поля станут заголовками столбцов сводной таблицы;
• Строки — значения этого поля станут заголовками строк;
• Значения — числовое поле, которое будет суммироваться, усредняться или подсчитываться на пересечении строк и столбцов.

Поля из списка сверху просто перетаскиваются мышью в нужную область. Например, чтобы увидеть сумму продаж по месяцам: поле «Месяц» — в «Строки», поле «Сумма» — в «Значения».

Шаг 4. Смена функции вычисления

По умолчанию для числовых полей в области «Значения» Excel использует сумму, а для текстовых — количество. Чтобы это изменить, щёлкните по полю в области «Значения» и выберите «Параметры полей значений» — там доступны сумма, количество, среднее, максимум, минимум и другие функции.

Как группировать даты по месяцам и годам

Если поле «Строки» или «Столбцы» содержит даты, щёлкните правой кнопкой по любой дате в сводной таблице и выберите «Группировать». В открывшемся окне можно выбрать один или сразу несколько уровней группировки — например, «Месяцы» и «Годы» одновременно, чтобы получить итоги по месяцам в разрезе по годам, а не единый список из всех месяцев подряд.

Как обновить сводную таблицу после изменения данных

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

Если новые строки добавлены не внутри прежнего диапазона, а ниже его границы, одного «Обновить» может быть недостаточно — нужно ещё раз указать источник данных через «Изменить источник данных» (либо изначально оформить исходные данные как умную таблицу, как описано в шаге 1).

Часто задаваемые вопросы

Что дальше

Если данные для сводной таблицы берутся из шаблона с оформлением — стоит заранее убедиться, что в источнике нет объединённых ячеек: сводная таблица считает их заполненными только частично. Как это устроено и как разъединить ячейки — в статье «Как объединить ячейки в Excel: способы и формулы».