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

By Mirko Peters · 2026-11-12

Как создать сводную таблицу в Excel: подготовьте данные, выберите Вставка > Сводная таблица, перетащите поля, добавьте срезы, затем обновите таблицу и устраните проблемы. Пошаговое руководство для начинающих.

Automatically translated with AI from the original post. Read the English original

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

Ключевые выводы

• Чтобы создать сводную таблицу, выделите данные, откройте вкладку «Вставка», затем «Сводная таблица», выберите новый или существующий лист и нажмите «ОК».
• Прежде всего нужны чистые данные: одна строка заголовков, уникальный непустой заголовок в каждом столбце, никаких объединённых ячеек и никаких промежуточных итогов в исходных данных.
• Перетаскивайте поля в «Строки», «Столбцы» и «Значения». Числовые поля по умолчанию суммируются, но столбец, который Excel считает текстовым, по умолчанию получает операцию «Количество».
• Сводная таблица не всегда обновляется сама. После изменения исходных данных щёлкните её правой кнопкой мыши и выберите «Обновить» или нажмите Alt+F5.
• Оформите исходные данные как таблицу Excel, чтобы новые строки учитывались при обновлении сводной таблицы.

Сводная таблица не меняет ваши данные. Она читает источник и показывает его сводку. Поэтому вы можете сколько угодно перетаскивать поля, не рискуя исходными строками.

Подготовьте исходные данные

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

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

Последний пункт существенно влияет на результат. Microsoft объясняет, что числовые поля значений по умолчанию получают операцию Сумма, но если Excel считает поле текстовым, по умолчанию используется Количество. Достаточно одной ячейки со значением «N/A» или «n/a» в столбце сумм, чтобы это произошло. Если сводная таблица показывает количество там, где вы ожидали итоги, сначала проверьте исходный столбец.

Далее преобразуйте список в таблицу Excel: щёлкните ячейку с данными и нажмите Ctrl+T. Microsoft рекомендует использовать таблицу в качестве источника, а на той же странице обзора сказано, что таблицы подхватывают новые данные при обновлении сводной таблицы. Обычный диапазон останавливается на последней строке, с которой был создан. Динамический именованный диапазон тоже может расширяться, согласно той же странице, но таблицу проще настроить и легче понять следующему пользователю.

Если данные сначала нужно преобразовать, например столбцы по месяцам, которые должны быть строками, Microsoft рекомендует использовать Power Query для отмены сводки (unpivot) перед созданием таблицы. На нашей странице Power Query описан этот инструмент.

Как создать сводную таблицу в Excel по шагам

Эти шаги относятся к Excel для Windows, как описано на странице Microsoft.

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

В диалоговом окне есть необязательный флажок Добавить эти данные в модель данных. Для первой сводной таблицы оставьте его снятым. Он предназначен для случаев, когда нужно объединить несколько таблиц или работать с очень большими объёмами данных. Кнопка со стрелкой у команды «Сводная таблица» также предлагает пункты Получить из внешнего источника данных, Получить из модели данных и Получить из Power BI. Если дойдёт до связей между таблицами, посмотрите Power Pivot.

Пусть Excel предложит макет

Если вы не знаете, что построить, команда Вставка > Рекомендуемые сводные таблицы покажет несколько готовых макетов для ваших данных. На странице Microsoft о сводных таблицах, которую я открывал, эта функция не описана пошагово, но там сказано, что рекомендации сводных таблиц входят в состав подключённых возможностей Microsoft 365 и используют службы ИИ для анализа данных. Если в вашей организации отключены подключённые возможности, рекомендаций не будет. Ручной способ, описанный выше, работает всегда.

Постройте макет с помощью панели «Поля сводной таблицы»

На панели каждый заголовок столбца исходных данных отображается как поле. Можно установить флажок у поля или перетащить его в одну из областей. Вот что Microsoft делает по умолчанию при установке флажка:

• Нечисловые поля попадают в Строки.
• Поля даты и времени попадают в Столбцы как иерархия.
• Числовые поля попадают в Значения.

Любое поле можно потом перетащить в другую область. Пример: для списка продаж установите флажок у поля Регион (попадёт в Строки) и у поля Сумма (попадёт в Значения), и вы получите итог по каждому региону. Перетащите Продукт в Столбцы, и те же итоги разобьются по продуктам.

Вот что делает каждая область:

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

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

Изменить вычисление

Чтобы изменить способ вычисления значения, нажмите стрелку рядом с полем в области «Значения» и выберите Параметры поля значений. На вкладке Операция можно переключаться между «Сумма», «Количество», «Среднее», «Максимум», «Минимум» и другими вариантами из списка. Excel автоматически обновляет «Пользовательское имя», и его можно изменить. Имя вроде «Sum of Amount» подойдёт для личного использования, но если вы делитесь отчётом, переименуйте его во что-то понятное. Microsoft рекомендует переименовывать только после завершения настройки, так как при смене вычисления имя снова меняется.

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

Отображение значений в процентах

Вкладка Дополнительные вычисления в окне «Параметры поля значений» меняет смысл числа. Она может показывать каждое значение как долю от итога, например % от общего итога. Чтобы вывести сумму и её процент рядом, перетащите одно и то же поле в область «Значения» дважды и задайте для каждой копии свой параметр. Это удобный приём для пары «Выручка» и «Доля выручки».

Фильтрация с помощью срезов и фильтров отчёта

Когда таблица настроена, обычно возникает вопрос, как посмотреть на её отдельную часть. Есть три инструмента, и все они описаны на страницах Microsoft.

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

Раскрывающиеся списки фильтров. Стрелка в заголовке строки или столбца открывает меню с пунктами Фильтры по метке и Фильтры по значению. В разделе «Фильтры по значению» находится Первые 10, которая показывает первые или последние элементы по числу, проценту или сумме. В списке элементов снимите флажок Выделить все и отметьте только нужные, либо введите текст в поле Поиск.

Фильтрация через контекстное меню. Выделите элементы в таблице, щёлкните правой кнопкой мыши, выберите Фильтр, а затем Оставить только выбранные элементы или Скрыть выбранные элементы. Команда Очистить фильтр удаляет фильтр.

У фильтров отчёта (область «Фильтры») есть полезная возможность: Отобразить страницы фильтра отчёта создаёт отдельный лист для каждого значения поля фильтра. Так можно быстро получить отдельный лист для каждого региона.

Обновление сводной таблицы при изменении данных

Сводная таблица представляет собой снимок источника на момент её создания или последнего обновления. Если в источник добавляется новая строка, таблица может не отобразить её сразу. На странице Microsoft, посвящённой обновлению данных сводной таблицы, перечислены следующие варианты:

1. Обновление одной таблицы. Выделите сводную таблицу, перейдите на вкладку Анализ сводной таблицы и выберите Обновить в группе «Данные» либо нажмите Alt+F5. Также подойдёт щелчок правой кнопкой мыши по таблице и выбор Обновить.
2. Обновление всего. Откройте стрелку под кнопкой Обновить и выберите Обновить все. Будут обновлены все сводные таблицы в книге.
3. Обновление при открытии. Выберите таблицу, откройте Параметры на вкладке «Анализ сводной таблицы», перейдите на вкладку Данные и установите флажок Обновлять данные при открытии файла.

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

Что делать, если новые строки всё равно не отображаются?

Скорее всего, исходный диапазон слишком мал. Если данные оформлены как таблица Excel, новые строки подхватываются автоматически при обновлении. Если это обычный диапазон, сводная таблица по-прежнему ссылается на старые ячейки. Исправьте это так, как Microsoft описывает в статье об изменении исходных данных сводной таблицы: выберите сводную таблицу, перейдите на вкладку Анализ, дважды выберите Изменить источник данных (сначала кнопку, затем пункт меню), выберите Выберите таблицу или диапазон, введите диапазон в поле «Таблица или диапазон» и нажмите ОК. Microsoft добавляет, что если данные сильно меняются, например добавляются или удаляются столбцы, часто более разумным выбором будет новая сводная таблица.

Если сводная таблица использует модель данных книги, изменить источник данных таким способом нельзя.

Удаление или очистка сводной таблицы

В статье Microsoft об удалении сводной таблицы описаны два способа. Первый: выделите всю сводную таблицу и нажмите Delete. Если появилось сообщение «Не удается изменить эту часть отчета сводной таблицы», нажмите Ctrl+A, чтобы выделить всё, и снова нажмите Delete. Второй: щёлкните ячейку в сводной таблице, перейдите в Анализ сводной таблицы > Выделить > Вся сводная таблица и удалите её оттуда. Если сводная таблица занимает лист одна, можно удалить весь лист.

Будьте осторожны со вторым способом, если на том же листе находятся исходные данные. На странице Microsoft не говорится, что происходит с источником, поэтому прежде чем что-либо удалять, убедитесь, что ваши данные находятся на другом листе. Срезы — отдельные объекты. Чтобы удалить срез, выделите его и нажмите Delete.

Когда сводная таблица выдаёт неверные результаты

• Количество вместо сумм. В столбце значений есть текстовая ячейка. Очистите данные или задайте для операции «Итоги по» значение «Сумма», а затем проверьте, что ни одна ячейка не отображает числа как текст.
• Поле отсутствует в списке. У столбца может не быть заголовка, или диапазон его не включает. Проверьте первую строку исходных данных и поле «Таблица или диапазон» в окне «Изменить источник данных».
• Старые числа. Вы не обновили данные. Нажмите Alt+F5.
• Даты ведут себя как текст. Если столбец с датами сохранён как текст, Excel не может распознать его как даты. Преобразуйте значения в настоящие даты в исходных данных и обновите сводную таблицу.
• Макет выглядит перемешанным. Проверьте исходные данные на наличие объединённых ячеек или второй строки заголовков. Требования Microsoft к данным исключают и то, и другое.

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

Распространённые ошибки

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

Связанные темы для изучения

• Группировка дат по месяцам или кварталам. Частый следующий шаг для данных о продажах и времени.
• Срезы для нескольких таблиц. Подключите один срез к нескольким сводным таблицам, чтобы получить простую панель мониторинга.
• Сводные диаграммы. Диаграмма, связанная со сводной таблицей, которая меняется вместе с ней.
• Power Query перед сводной таблицей. Если экспорт содержит по одному столбцу на каждый месяц, сначала выполните преобразование или объединение данных в Power Query.
• Модель данных и меры. Объединяйте несколько таблиц с помощью Power Pivot, когда одного списка уже недостаточно.

Следующий шаг

Возьмите один из своих списков, нажмите Ctrl+T, чтобы превратить его в таблицу, и создайте на её основе сводную таблицу, поместив только одно поле в «Строки» и одно в «Значения». Когда это заработает, добавьте срез и один раз обновите данные после изменения числа в источнике. Вот и весь цикл создания сводной таблицы в Excel: подготовка, построение, фильтрация и обновление.