В таблице три тысячи строк продаж, и руководитель просит сумму по каждому менеджеру и товару. Формулами это займёт полчаса, а новый вопрос потребует начинать заново. Сводная таблица делает такой отчёт за минуту: вы просто перетаскиваете названия столбцов в нужные поля, и Excel всё считает сам.

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

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

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

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

Дата Менеджер Товар Количество Сумма
12.03 Иванова Стулья 4 12 000
12.03 Петров Столы 1 18 500
13.03 Иванова Столы 2 37 000

Проверьте данные перед началом:

  • У каждого столбца есть одно короткое название в первой строке.
  • Нет объединённых ячеек и полностью пустых строк и столбцов.
  • В столбце суммы только числа, а не текст вроде «12 000 руб.». Такие значения Excel считать не будет.
  • Даты записаны датами, а названия написаны одинаково. «Иванова» и «Иванова » с пробелом Excel посчитает разными людьми.

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

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

  1. Щёлкните по любой ячейке внутри данных.
  2. Откройте вкладку Вставка и нажмите Сводная таблица. В некоторых версиях есть дополнительный пункт Из таблицы или диапазона.
  3. Проверьте диапазон данных в окне.
  4. Выберите На новый лист и нажмите ОК.

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

Как разложить поля

Теперь перетаскивайте названия столбцов в области. Каждая из них отвечает за свою часть отчёта.

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

В нашем примере перетащите Менеджер в Строки, Товар в Столбцы, Сумма в Значения. Excel сразу покажет таблицу, где в строках менеджеры, в столбцах товары, а в ячейках суммы продаж. Справа и снизу появятся итоги.

Схема полей сводной таблицы Excel: менеджер в строках, товар в столбцах, сумма в значениях и готовый итог

Поля можно перетаскивать как угодно, и данные при этом никуда не денутся. Попробуйте поменять Менеджера и Товар местами или добавить второе поле в Строки: получится вложенная группировка, например товары внутри каждого менеджера.

Как поменять расчёт

По умолчанию числовой столбец суммируется, а текстовый считается количеством записей. Иногда нужен другой показатель.

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

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

Как сгруппировать даты и добавить фильтры

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

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

Как обновить таблицу

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

  • Одна таблица: щёлкните правой кнопкой по отчёту и выберите Обновить или нажмите Alt+F5.
  • Все таблицы книги: Данные → Обновить всё или Ctrl+Alt+F5.
  • При открытии файла: Анализ сводной таблицы → Параметры → Данные и флажок Обновить при открытии файла.

Если новые строки не попали в отчёт, значит, источник указан как фиксированный диапазон. Откройте Анализ сводной таблицы → Источник данных и выберите расширенный диапазон. С умной таблицей этой проблемы не будет.

Если нужно увидеть строки за каким-то числом, дважды щёлкните по этой ячейке в отчёте. Excel создаст новый лист с записями, из которых оно сложилось.

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

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

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

Нет, это итог расчёта. Измените исходный список и обновите отчёт. Если попытаться изменить значение, Excel покажет сообщение об ошибке.

Как убрать слово «Итог» и заголовки вроде «Названия строк»?

На вкладке Конструктор в меню Макет отчёта выберите Показать в табличной форме. Итоги управляются там же, через кнопки Промежуточные итоги и Общие итоги.

Как построить диаграмму из сводной таблицы?

Выделите ячейку отчёта и выберите Вставка → Сводная диаграмма. Такая диаграмма реагирует на фильтры и срезы вместе с отчётом.

© kak2.ru