Office

Сводная таблица в Excel: как сделать и настроить для начинающих

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

Сводная таблица в Excel: как сделать и настроить для начинающих
Фото: Svetlisashkov, CC BY-SA 4.0, Wikimedia Commons
Содержание
  1. Что такое сводная таблица и зачем она нужна
  2. Как подготовить данные
  3. Как создать сводную таблицу пошагово
  4. Четыре области: как раскладывать поля
  5. Как изменить способ расчёта
  6. Фильтры, сортировка и срезы
  7. Группировка дат и чисел
  8. Обновление данных
  9. Сводная таблица из нескольких листов
  10. Диаграмма по сводной таблице и частые ошибки

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

Сводная таблица - это отчёт, который Excel строит автоматически на основе вашего списка данных. Она группирует строки по выбранному признаку и считает итоги: сумму, количество, среднее, минимум или максимум. Исходная таблица при этом не меняется.

Типичные задачи: сколько каждый менеджер продал за квартал, какой товар приносит больше выручки, как менялись расходы по месяцам. Раньше для этого использовали набор формул вроде СУММЕСЛИ, а сводная таблица делает то же быстрее и без ошибок в диапазонах.

Как подготовить данные

Сводная таблица строится только по аккуратному списку. От подготовки зависит, получится ли отчёт с первого раза.

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

Удобно превратить диапазон в умную таблицу сочетанием Ctrl+T. Тогда новые строки автоматически попадут в отчёт после обновления.

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

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

Пока таблица пуста, это нормально. Дальше нужно наполнить её полями, то есть заголовками столбцов вашего списка.

Четыре области: как раскладывать поля

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

ОбластьЧто в ней происходитПример
СтрокиЗначения идут сверху вниз, по одному на строкуТовар
СтолбцыЗначения идут слева направо в шапкеМесяц
ЗначенияЧисла, которые считает ExcelСумма по полю Выручка
ФильтрыОтбор всего отчёта по признакуМенеджер

Попробуйте так: перетащите Товар в Строки, Выручку - в Значения. Excel выведет список товаров и общую выручку по каждому. Затем добавьте Месяц в Столбцы, и получится матрица по месяцам.

Панель полей сводной таблицы с полями в областях Строки, Столбцы и Значения
Распределение полей по четырём областям

Как изменить способ расчёта

По умолчанию числовые поля суммируются, а текстовые считаются по количеству. Это можно поменять.

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

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

Фильтры, сортировка и срезы

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

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

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

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

Числа группируются так же: например, возраст или сумму заказа можно разбить на интервалы по 10 или по 1000. Ошибка "Выделенный объект нельзя объединить в группу" означает, что в столбце есть пустые ячейки или текст вместо дат. Заполните пропуски или исправьте формат.

Обновление данных

Сводная таблица не пересчитывается мгновенно при каждом изменении исходных данных. После правки списка щёлкните внутри отчёта и нажмите Обновить на вкладке Анализ сводной таблицы либо сочетание Alt+F5. Чтобы обновить все сводные таблицы книги, выберите Обновить все на вкладке Данные.

Если вы дописали строки ниже списка, а таблица не была умной, расширьте диапазон через Изменить источник данных. С умной таблицей, созданной по Ctrl+T, это происходит само.

Открыть инструмент отдельной страницей

Для небольших задач хватит формулы: соберите её в конструкторе формул Excel. Если нужно подтянуть в список данные из другой таблицы до создания отчёта, пригодится функция ВПР.

Сводная таблица из нескольких листов

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

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

Диаграмма по сводной таблице и частые ошибки

Выделите ячейку в отчёте и на вкладке Анализ сводной таблицы нажмите Сводная диаграмма. Она связана с таблицей: при смене фильтров перестраиваются обе. Об оформлении графиков подробнее в уроке как сделать диаграмму в Excel.

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

Другие инструкции по Office собраны в рубрике Office и документы.

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

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

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

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

Обычно в столбце с числами есть текст или пустые ячейки, и Excel считает его текстовым. Исправьте формат на числовой, обновите отчёт, либо вручную выберите Сумма в параметрах полей значений.

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

Щёлкните внутри отчёта и нажмите Обновить на вкладке Анализ сводной таблицы или Alt+F5. Для всех отчётов книги используйте Обновить все на вкладке Данные.

Можно ли сгруппировать даты по месяцам?

Да. Щёлкните правой кнопкой по дате в отчёте, выберите Группировать и отметьте Месяцы, Кварталы или Годы. Все ячейки столбца должны быть настоящими датами.

Нужно ли знать формулы для сводных таблиц?

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

Нашли ошибку или устаревший пункт меню? Напишите на admin@programistik.top с адресом страницы. Перед правкой реестра, системных файлов и разделов диска делайте резервную копию.

Читайте по теме