Сводные таблицы Excel: как сделать сводную таблицу в Экселе

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

Как подготовить данные для сводной таблицы Excel

Сводная таблица работает с плоской таблицей: одна строка — одна операция, один столбец — один признак.

• У каждого столбца есть заголовок в первой строке, без объединённых ячеек.
• Нет пустых строк и промежуточных итогов внутри данных — как удалить пустые строки.
• Даты сохранены как даты, суммы — как числа.
• Лучше превратить диапазон в таблицу (Ctrl+T): тогда новые строки сами попадут в сводную при обновлении.

Пример исходных данных: дата, регион, менеджер, товар, количество, выручка.

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

1. Щёлкните по любой ячейке таблицы с данными.
2. «Вставка» → «Сводная таблица» → «Из таблицы или диапазона». Excel сам определит диапазон.
3. Выберите «На новый лист» и нажмите «ОК».
4. Справа откроется панель «Поля сводной таблицы». Перетащите «Регион» в область «Строки».
5. Перетащите «Выручка» в область «Значения» — появится сумма выручки по каждому региону.
6. Перетащите «Дата» в «Столбцы» — Excel сгруппирует даты по годам, кварталам и месяцам.

Если не знаете, с чего начать, нажмите «Вставка» → «Рекомендуемые сводные таблицы»: Excel предложит готовые варианты по вашим данным.

Области сводной таблицы Excel

Сводная таблица эксель: попробуйте сами

Ниже — работающая модель сводной таблицы. Меняйте поля в строках и столбцах и тип вычисления.

Сумма, количество, среднее и доля

По умолчанию числа суммируются. Чтобы изменить вычисление, щёлкните по полю в области «Значения» → «Параметры полей значений»:

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

Одно поле можно добавить в «Значения» дважды: например, выручку суммой и долей от общей суммы рядом.

Группировка дат и чисел

Щёлкните правой кнопкой по дате в сводной таблице → «Группировать» и выберите месяцы, кварталы или годы. Так же группируют числа: например, суммы заказов по интервалам 0–50 000, 50 000–100 000 и так далее.

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

Срезы — кнопки для фильтрации отчёта в один клик: «Анализ сводной таблицы» → «Вставить срез» → выберите поле, например «Менеджер». Срез можно подключить к нескольким сводным таблицам сразу, чтобы фильтровать весь отчёт.

Сводная таблица не пересчитывается автоматически. После изменения исходных данных нажмите «Данные» → «Обновить всё» или щёлкните по сводной правой кнопкой → «Обновить». Если данные приходят выгрузками каждый месяц, подготовьте их в Power Query — тогда обновление очистит данные и пересчитает отчёт одной кнопкой.

Частые проблемы со сводными таблицами Excel

• Сумма считается как «Количество» — в столбце с числами есть текст или пустые ячейки. Исправьте данные и обновите сводную.
• Новые строки не попадают в отчёт — источник задан фиксированным диапазоном. Сделайте исходные данные таблицей через Ctrl+T.
• Даты не группируются — среди дат есть текстовые значения или пустые ячейки.
• Дубли в строках — одно и то же значение записано по-разному: «Москва» и «Москва » с пробелом.

Вычисляемые поля в сводной таблице Excel

Если нужно посчитать показатель, которого нет в данных, — например, маржу — добавьте вычисляемое поле: «Анализ сводной таблицы» → «Поля, элементы и наборы» → «Вычисляемое поле». Формула записывается через имена полей: =Выручка-Себестоимость. Поле появится в списке и его можно положить в «Значения».

Сводная диаграмма

«Анализ сводной таблицы» → «Сводная диаграмма» строит график, связанный со сводной: при смене полей и фильтров он перестраивается. Вместе со срезами получается простой интерактивный дашборд прямо в Excel. Если отчёт нужен команде и обновляется ежедневно, его удобнее собрать в Power BI.

Как ссылаться на значения сводной

Если щёлкнуть по ячейке сводной таблицы при вводе формулы, Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — она находит значение по названиям полей и не ломается, когда таблица меняет размер. Чтобы получать обычные ссылки вида B5, отключите опцию в «Анализ сводной таблицы» → «Параметры» → «Создать GetPivotData».

Частые вопросы