Как сделать сводную таблицу в Excel
В таблице три тысячи строк продаж, и руководитель просит сумму по каждому менеджеру и товару. Формулами это займёт полчаса, а новый вопрос потребует начинать заново. Сводная таблица делает такой отчёт за минуту: вы просто перетаскиваете названия столбцов в нужные поля, и Excel всё считает сам.
Рассказываем, как подготовить данные, собрать сводную таблицу, поменять расчёт и обновить её после добавления новых строк.
- В исходной таблице нужны заголовки столбцов и не должно быть объединённых ячеек и пустых строк.
- Выделите любую ячейку данных и нажмите Вставка → Сводная таблица.
- Перетащите поля в области Строки, Столбцы и Значения.
- Способ расчёта меняется через Параметры поля значений.
- После изменения исходных данных нажмите Обновить: само собой это не происходит.
Как подготовить данные
Сводная таблица строится из обычного списка: каждая строка — отдельная запись, каждый столбец — один показатель. Для примера возьмём такой список:
| Дата | Менеджер | Товар | Количество | Сумма |
|---|---|---|---|---|
| 12.03 | Иванова | Стулья | 4 | 12 000 |
| 12.03 | Петров | Столы | 1 | 18 500 |
| 13.03 | Иванова | Столы | 2 | 37 000 |
Проверьте данные перед началом:
- У каждого столбца есть одно короткое название в первой строке.
- Нет объединённых ячеек и полностью пустых строк и столбцов.
- В столбце суммы только числа, а не текст вроде «12 000 руб.». Такие значения Excel считать не будет.
- Даты записаны датами, а названия написаны одинаково. «Иванова» и «Иванова » с пробелом Excel посчитает разными людьми.
Преобразуйте диапазон в умную таблицу сочетанием Ctrl+T. Тогда при добавлении новых строк источник сводной таблицы расширится сам.
Как создать сводную таблицу
- Щёлкните по любой ячейке внутри данных.
- Откройте вкладку Вставка и нажмите Сводная таблица. В некоторых версиях есть дополнительный пункт Из таблицы или диапазона.
- Проверьте диапазон данных в окне.
- Выберите На новый лист и нажмите ОК.
Откроется новый лист: слева будет заготовка отчёта, справа панель Поля сводной таблицы со списком столбцов и четырьмя областями внизу. Если панели не видно, щёлкните по заготовке и нажмите Анализ сводной таблицы → Список полей.
Как разложить поля
Теперь перетаскивайте названия столбцов в области. Каждая из них отвечает за свою часть отчёта.
- Строки — то, что идёт сверху вниз слева: например, менеджеры.
- Столбцы — то, что идёт слева направо: например, товары.
- Значения — то, что считаем: обычно сумма.
- Фильтры — поле для отбора над отчётом: например, дата или город.
В нашем примере перетащите Менеджер в Строки, Товар в Столбцы, Сумма в Значения. Excel сразу покажет таблицу, где в строках менеджеры, в столбцах товары, а в ячейках суммы продаж. Справа и снизу появятся итоги.

Поля можно перетаскивать как угодно, и данные при этом никуда не денутся. Попробуйте поменять Менеджера и Товар местами или добавить второе поле в Строки: получится вложенная группировка, например товары внутри каждого менеджера.
Как поменять расчёт
По умолчанию числовой столбец суммируется, а текстовый считается количеством записей. Иногда нужен другой показатель.
- Щёлкните правой кнопкой по любому числу в области значений.
- Выберите Параметры поля значений.
- На вкладке Операция выберите Сумма, Количество, Среднее, Максимум или Минимум.
- Чтобы отображать долю, откройте вкладку Дополнительные вычисления и выберите, например, % от общей суммы.
- Нажмите Числовой формат, чтобы добавить разделители разрядов.
- Нажмите ОК.
Одно поле можно добавить в значения дважды: в одном столбце показывайте сумму, а в другом — её долю. Если сумма показывается как количество, значит, в столбце есть текст или пустые ячейки. Очистите данные и обновите отчёт.
Как сгруппировать даты и добавить фильтры
Если перетащить дату в Строки, Excel может сам сгруппировать её по месяцам и кварталам. Если он этого не сделал, щёлкните правой кнопкой по любой дате в отчёте, выберите Группировать и отметьте нужные единицы, например Месяцы и Годы. Если пункт недоступен, среди дат есть текстовые значения.
Для отбора данных есть два инструмента. Стрелка в заголовке Названия строк работает как обычный фильтр. Удобнее использовать срезы: кнопки с вариантами поверх отчёта. На вкладке Анализ сводной таблицы нажмите Вставить срез и отметьте нужные столбцы. Теперь отбор делается одним щелчком по кнопке, и сразу видно, что выбрано.
Как обновить таблицу
Сводная таблица хранит копию данных и не следит за их изменениями. Поэтому после правок или новых строк нужно обновить её вручную.
- Одна таблица: щёлкните правой кнопкой по отчёту и выберите Обновить или нажмите Alt+F5.
- Все таблицы книги: Данные → Обновить всё или Ctrl+Alt+F5.
- При открытии файла: Анализ сводной таблицы → Параметры → Данные и флажок Обновить при открытии файла.
Если новые строки не попали в отчёт, значит, источник указан как фиксированный диапазон. Откройте Анализ сводной таблицы → Источник данных и выберите расширенный диапазон. С умной таблицей этой проблемы не будет.
Если нужно увидеть строки за каким-то числом, дважды щёлкните по этой ячейке в отчёте. Excel создаст новый лист с записями, из которых оно сложилось.
Попробуйте сначала на небольшом списке: одно поле в Строки, одно в Значения. Когда итог совпадёт с вашим ожиданием, добавляйте столбцы и срезы.
Частые вопросы
Можно ли править цифры прямо в сводной таблице?
Нет, это итог расчёта. Измените исходный список и обновите отчёт. Если попытаться изменить значение, Excel покажет сообщение об ошибке.
Как убрать слово «Итог» и заголовки вроде «Названия строк»?
На вкладке Конструктор в меню Макет отчёта выберите Показать в табличной форме. Итоги управляются там же, через кнопки Промежуточные итоги и Общие итоги.
Как построить диаграмму из сводной таблицы?
Выделите ячейку отчёта и выберите Вставка → Сводная диаграмма. Такая диаграмма реагирует на фильтры и срезы вместе с отчётом.
© kak2.ru



Оставить комментарий