CoreGPT
Microsoft 36511 мин чтения

Сводная таблица в Excel: как сделать пошагово (с примерами)

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

Сводная таблица в Excel: как сделать пошагово (с примерами)

Чтобы сделать сводную таблицу в Excel, щёлкните любую ячейку в данных и выберите Вставка → Сводная таблица → Из таблицы или диапазона (в некоторых версиях достаточно нажать Вставка → Сводная таблица). Проверьте диапазон, выберите На новый лист и нажмите ОК. Затем в области Поля сводной таблицы установите флажок у текстового поля, например «Регион» (оно попадёт в Строки), и у числового, например «Продажи» (оно попадёт в Значения). Excel сам посчитает сумму продаж по каждому региону, формулы не нужны.

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

Большинство проблем со сводными таблицами возникает ещё до нажатия кнопки «Вставка»: пустые заголовки, пустые строки или числа, сохранённые как текст. Две минуты подготовки избавят от большей части ошибок.

Что нужно сделатьГде это находится
Создать сводную таблицуВставка → Сводная таблица → Из таблицы или диапазона
Позволить Excel предложить вариантВставка → Рекомендуемые сводные таблицы
Сгруппировать по полюПеретащите поле в Строки (или Столбцы)
Просуммировать, посчитать или усреднить полеПеретащите его в Значения, затем Параметры поля значений
Отфильтровать весь отчётПеретащите поле в Фильтры или выберите Анализ сводной таблицы → Вставить срез
Сгруппировать даты по месяцам или годамЩёлкните дату правой кнопкой → Группировать
Показать новые данныеАнализ сводной таблицы → Обновить (Alt+F5)
Указать другой диапазонАнализ сводной таблицы → Изменить источник данных

В русской версии Excel названия вкладок и кнопок приведены по официальной локализации Microsoft. Если у вас английский интерфейс, ориентируйтесь на оригинальные названия: Insert → PivotTable → From Table/Range, PivotTable Fields, Rows, Columns, Values, Filters.

Шаг 1. Подготовьте данные

В этом руководстве в качестве примера используется список продаж со столбцами Дата, Регион, Продукт и Продажи, по одному заказу в строке. Прежде чем начать, убедитесь, что:

  • Есть ровно одна строка заголовков. У каждого столбца короткий уникальный заголовок. Никаких пустых заголовков, объединённых ячеек и второй строки заголовков.
  • Внутри данных нет пустых строк и столбцов. Когда Excel сам определяет диапазон, он останавливается на первой полностью пустой строке или столбце.
  • В каждом столбце один тип данных. Если в столбце «Продажи» есть текст вроде «н/д» или числа, сохранённые как текст, сводная таблица будет считать количество вместо суммы.
  • В данных нет строк с промежуточными или общими итогами. Сводная таблица создаст их сама.

После этого оформите список как таблицу: щёлкните ячейку в нём, нажмите Ctrl+T (или Вставка → Таблица), оставьте флажок Таблица с заголовками и нажмите ОК. Этот шаг необязателен, но полезен: строки, добавленные в таблицу, попадут в сводную таблицу при следующем обновлении, а в обычный диапазон — нет.

Шаг 2. Вставьте сводную таблицу (Excel для Windows)

Способ работает в Excel для Microsoft 365, Excel 2024, 2021, 2019 и 2016. Названия команд ниже приведены для Microsoft 365; в старых версиях то же диалоговое окно открывается при нажатии кнопки Сводная таблица.

  1. Щёлкните любую ячейку внутри таблицы или диапазона данных. Excel выделит весь список сам.
  2. Выберите Вставка → Сводная таблица → Из таблицы или диапазона.
  3. В диалоговом окне Сводная таблица из таблицы или диапазона (в старых версиях оно называется Создание сводной таблицы) проверьте поле Таблица или диапазон. В нём отображается имя таблицы (например, Таблица1) или диапазон вроде Лист1!$A$1:$D$121. Если диапазон указан неверно, выделите нужные ячейки на листе, не закрывая окно.
  4. Выберите, где разместить сводную таблицу:
    • На новый лист — отчёт появится на новом листе. Это самый безопасный вариант.
    • На существующий лист — отчёт появится там, где вы укажете в поле Диапазон. Оставьте достаточно свободного места справа и снизу, потому что сводная таблица растёт по мере добавления полей.
  5. Пока оставьте флажок Добавить эти данные в модель данных снятым (он нужен для объединения нескольких таблиц).
  6. Нажмите ОК.

Диалоговое окно «Сводная таблица из таблицы или диапазона» в Excel: в разделе «Выберите таблицу или диапазон» в поле «Таблица или диапазон» указана Таблица1; выбран вариант «На новый лист» вместо «На существующий лист», поле «Диапазон» пусто, флажок «Добавить эти данные в модель данных» снят, кнопка «ОК» выбрана по умолчанию

Excel откроет новый лист с пустой сводной таблицей слева и областью Поля сводной таблицы справа. Если область исчезла, щёлкните внутри сводной таблицы или выберите Анализ сводной таблицы → Список полей.

Шаг 3. Постройте отчёт в области «Поля сводной таблицы»

В верхней части области перечислены все заголовки ваших данных в виде полей. Внизу находятся четыре области:

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

Чтобы посчитать продажи по регионам:

  1. Установите флажок у поля Регион. Текстовые поля автоматически попадают в Строки.
  2. Установите флажок у поля Продажи. Числовые поля автоматически попадают в Значения и превращаются в Сумма по полю Продажи.

Область «Поля сводной таблицы» в Excel с полями Дата, Регион, Продукт и Продажи; флажки стоят у полей Регион и Продажи; внизу четыре области: Фильтры и Столбцы пусты, в Строках находится Регион, в Значениях — Сумма по полю Продажи

Теперь у вас по одной строке на регион и Общий итог внизу. Excel выстраивает регионы по алфавиту (от А до Я): в примере это Восток — 1 265, Запад — 1 440, Север — 1 545 и Юг — 1 890, общий итог — 6 140. Суммы сначала не наследуют денежный формат из ваших данных; как его добавить, рассказано в шаге 4.

Перетаскивание даёт больше контроля, чем флажки. Перетащите Продукт в Столбцы, чтобы увидеть каждый продукт в отдельном столбце, или в Фильтры, чтобы просматривать один продукт за раз из раскрывающегося списка над отчётом. Если поместить в Строки и «Регион», и «Продукт», получится вложенный отчёт. Чтобы убрать поле, снимите флажок или перетащите поле за пределы области.

Шаг 4. Замените «Сумму» на «Количество», «Среднее» или другое вычисление

Excel суммирует числовые поля и считает количество для текстовых. Чтобы изменить это:

  1. В области Значения щёлкните Сумма по полю Продажи и выберите Параметры поля значений (или щёлкните правой кнопкой любое число в сводной таблице и выберите Параметры поля значений).
  2. На вкладке Операция выберите Количество, Среднее, Максимум, Минимум или другой вариант.
  3. При желании введите более понятное название в поле Пользовательское имя, например «Число заказов».
  4. Нажмите Числовой формат, чтобы показать результаты в виде денежных значений или с разделителями разрядов.
  5. Нажмите ОК.

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

Шаг 5. Отсортируйте и отфильтруйте сводную таблицу

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

Шаг 6. Сгруппируйте даты по месяцам, кварталам или годам

Когда вы добавляете поле даты в Строки, современные версии Excel для Windows часто сами группируют его по годам, кварталам и месяцам. Чтобы выбрать группировку самостоятельно:

  1. Щёлкните правой кнопкой любую дату в сводной таблице и выберите Группировать.
  2. В диалоговом окне Группирование выберите один или несколько вариантов в списке С шагом, например Месяцы и Годы (выбирайте оба, если данные охватывают больше одного года, иначе январь каждого года попадёт в одну строку).
  3. Нажмите ОК.

Чтобы отменить группировку, щёлкните дату правой кнопкой и выберите Разгруппировать. Чтобы отключить автоматическую группировку, включите параметр Отключить автоматическую группировку столбцов даты и времени в сводных таблицах в разделе Файл → Параметры → Данные. Если появляется сообщение «Выделенный объект нельзя объединить в группу», в столбце с датами есть пустые ячейки или текст, который только выглядит как дата: исправьте исходные данные и обновите отчёт.

Шаг 7. Обновите сводную таблицу и измените источник данных

Сводная таблица не обновляется сама при изменении данных:

  • Щёлкните внутри сводной таблицы и выберите Анализ сводной таблицы → Обновить либо щёлкните её правой кнопкой и выберите Обновить (Alt+F5 в Windows).
  • Чтобы обновить все сводные таблицы в книге, выберите Анализ сводной таблицы → Обновить → Обновить все (Ctrl+Alt+F5).

Если вы добавили строки под обычным диапазоном, одного обновления недостаточно. Выберите Анализ сводной таблицы → Изменить источник данных → Изменить источник данных, выделите новый диапазон (или введите имя таблицы, например Таблица1) и нажмите ОК. Если один раз указать в качестве источника таблицу, эта проблема исчезнет навсегда.

Рекомендуемые сводные таблицы: пусть Excel построит отчёт за вас

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

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

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

Excel для интернета. Выделите данные и выберите Вставка → Сводная таблица. В области Вставка сводной таблицы выберите Новый лист или Существующий лист, чтобы построить отчёт самостоятельно, либо выберите один из предложенных вариантов (рекомендации доступны при наличии подписки на Microsoft 365). Устанавливайте флажки у полей в области Поля сводной таблицы, как обычно. Чтобы изменить вычисление, щёлкните значение правой кнопкой и выберите Суммировать значения по. Чтобы обновить отчёт, щёлкните сводную таблицу правой кнопкой и выберите Обновить.

Почему не работает сводная таблица

«Имя поля сводной таблицы задано неверно». В исходном диапазоне есть хотя бы один столбец без заголовка — часто это пустой столбец на краю диапазона или объединённая ячейка в заголовке. Задайте заголовок каждому столбцу, отмените объединение ячеек в заголовках и убедитесь, что в диапазон не попал пустой столбец.

Новые данные не отображаются. Сначала обновите отчёт. Если строк всё равно нет, они находятся за пределами источника: фиксированный диапазон вроде $A$1:$D$121 игнорирует строку 122. Исправьте это через Изменить источник данных, а лучше укажите в качестве источника таблицу Excel.

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

«Ссылка на источник данных недопустима». Исходный диапазон или таблица были удалены или переименованы, либо в имени файла книги есть квадратные скобки. Исправьте диапазон через Изменить источник данных или сохраните файл под именем без [ ].

«Отчет сводной таблицы не может перекрывать другой отчет сводной таблицы». Две сводные таблицы на одном листе разрослись и столкнулись. Перенесите одну из них на другой лист (Анализ сводной таблицы → Переместить сводную таблицу) или оставьте между ними больше места.

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

Для чего нужна сводная таблица? Чтобы быстро подводить итоги по длинным спискам: продажи по регионам, заказы по месяцам, средняя стоимость по категориям. Вместо формул СУММЕСЛИМН или СЧЁТЕСЛИМН вы просто перетаскиваете поля.

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

Обновляется ли сводная таблица автоматически? Нет. Обновите её командой Анализ сводной таблицы → Обновить или нажатием Alt+F5, либо включите параметр Обновить при открытии файла в Параметрах сводной таблицы.

Как удалить сводную таблицу? Щёлкните внутри неё, выберите Анализ сводной таблицы → Выделить → Вся сводная таблица и нажмите Delete. Если она расположена на отдельном листе, можно просто удалить лист.

Чем сводная таблица отличается от таблицы в Excel? Таблица Excel (Ctrl+T) — это сами ваши данные с фильтрами и автоматическим расширением. Сводная таблица — это отчёт только для чтения, построенный на их основе: чтобы изменить число, нужно отредактировать источник и обновить отчёт. Вместе они работают лучше всего.

Получайте ответы из данных быстрее

Сводная таблица показывает итоги, но понять, что они значат, всё равно приходится вам. CoreGPT for Excel читает ваш лист за вас: спросите «что выделяется в продажах за третий квартал?», и он укажет самые сильные регионы, необычные падения и тренды прямо рядом с вашей книгой. Начать можно бесплатно, он работает в Excel для Windows, Mac и интернета.

Попробуйте: CoreGPT for Excel

Читайте также:

Бесплатные ИИ-инструменты

Читайте также

Microsoft 365

Условное форматирование в Excel: как сделать (с примерами формул)

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

Читать далее
Microsoft 365

Как пронумеровать страницы в Ворде (Word) и начать нумерацию с любой страницы

Как пронумеровать страницы в Ворде за несколько кликов, убрать номер с титульного листа, начать нумерацию со 2-й или 3-й страницы, сделать «Страница X из Y» и римские цифры для вводной части, а также исправить номера, которые сбрасываются или везде равны 1.

Читать далее
Microsoft 365

Как заблокировать ячейки в Excel (и защитить только формулы)

Как заблокировать отдельные ячейки или только формулы на листе Excel, защитить лист с паролем или без него, разрешить редактирование выбранных диапазонов и исправить типичные проблемы с защитой в Excel для Windows, Mac и Интернета.

Читать далее