Сводные таблицы

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

Вспомни: сводная таблица считает по уже готовым числам исходной таблицы — суммы и средние вместо ручного счёта мы уже делали функциями СУММ/СРЗНАЧ/СЧЁТ (тема 2.3), а если нужного поля (например, цены) в исходной таблице нет — его сначала подтягивают через ВПР из справочника (тема 3.6), и только потом строят сводную.

Что такое сводная таблица и требования к данным

Сводная таблица — это автоматическая сводка по исходной таблице: считает суммы, количества, средние и группирует данные по нужным категориям (например, по городам или товарам) — без единой формулы. Меняешь «угол обзора» в несколько кликов: по товарам, по менеджерам, по датам.

Перед построением сводной исходная таблица должна быть «сплошной»: одна строка — одна запись; заголовки строго в первой строке (по ним сводная ориентируется); без объединённых ячеек (они ломают структуру); без пустых строк внутри диапазона. Удобно сразу оформить исходный диапазон умной таблицей (Ctrl+T, тема 3.6) — тогда сводная сама подхватит новые строки.

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

В Excel: выдели таблицу → «Вставка» → «Сводная таблица» → выбери «на новом листе» или «на текущем». В Google Таблицах: «Данные» → «Сводная таблица», источник данных — диапазон. После создания появляются 4 области. Строки — по каким категориям разбивать (например, «Город»). Столбцы — дополнительная группировка (например, «Товар»). Значения — что считать (сумма, количество, среднее). Фильтры — какие данные оставить в расчёте (например, только «Москва»).

Исходная таблица: Дата | Город | Товар | Кол-во | Сумма

СТРОКИ: Город          СТОЛБЦЫ: Товар          ЗНАЧЕНИЯ: Сумма
---------------------------------------------------------------
              Молоко   Хлеб        <- столбцы (Товар)
Москва          500     300
СПб             200     150
  ^
  строки (Город)

Результат: матрица «Город x Товар», в ячейках — сумма продаж.

Тип расчёта, фильтрация, сортировка, обновление

Значения можно считать по-разному: сумма (общая выручка), количество (сколько строк/заказов), среднее (средний чек), максимум/минимум, % от итога (доля в общем объёме). Чтобы поменять: клик по полю в «Значениях» → «Настройки поля значений» → выбрать нужную функцию.

Чтобы оставить только нужные категории (например, только «Москва» и «СПб»), поле добавляют в область «Фильтры» и отмечают нужные значения. Строки сводной можно отсортировать (например, города по убыванию суммы). Важно: если исходная таблица изменилась, сводную нужно обновить вручную — в Excel правой кнопкой мыши по таблице → «Обновить»; в Google Таблицах сводная обновляется автоматически.

Сводная в связке с ВПР: бизнес-задача

На практике исходная таблица продаж часто содержит только код товара, а не цену или категорию — их подтягивают из справочника через ВПР (тема 3.6), и только потом строят сводную по посчитанной сумме. Так же решают задачи вида «найти товар с лучшей и худшей прибыльностью за период»: добавляют в значения поле «Прибыль» (выручка минус себестоимость) и сортируют сводную по этому полю — самый прибыльный товар окажется сверху, самый убыточный — снизу.

Проверь себя

Контрольный вопрос. Что делает сводная таблица?

Аавтоматически считает суммы/количества/средние и группирует данные по нужным категориям, без единой формулы

Бстроит диаграмму по исходной таблице

Всортирует исходную таблицу

Гзащищает данные от изменения

Подсказка: Ключевое — «без единой формулы» и «группирует».

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Что из перечисленного НЕ является требованием к исходным данным перед построением сводной?

Аобъединённые ячейки в шапке ускоряют построение

Бзаголовки должны быть в первой строке

Вкаждая строка = одна запись

Гне должно быть пустых строк внутри диапазона

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

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. В Excel сводную таблицу создают через…

А«Вставка» → «Сводная таблица»

Б«Данные» → «Проверка данных»

В«Главная» → «Условное форматирование»

Г«Рецензирование» → «Защитить лист»

Подсказка: Сводная — это вставляемый объект, как и умная таблица или диаграмма.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Поле, помещённое в область «Строки» сводной таблицы,…

Азадаёт, по каким категориям разбивать данные (например, по городам)

Бзадаёт, что именно считать

Вограничивает видимые данные

Гстроит диаграмму

Подсказка: Строки — это категории группировки, а не сами вычисления.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Поле, помещённое в область «Значения»,…

Азадаёт, что именно считать — сумму, количество, среднее

Бзадаёт категории группировки

Вограничивает данные

Гменяет цвет ячеек

Подсказка: Значения — это то, что реально вычисляется.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Таблица продаж: Город, Товар, Сумма. В сводной «Строки»=Город, «Столбцы»=Товар, «Значения»=Сумма. Что покажет такая сводная?

Аматрицу — сумму продаж каждого товара в каждом городе

Бсписок городов без чисел

Вдиаграмму продаж

Годин общий итог без разбивки

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

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Задание. Заказы: Иванов 5, Петров 3, Иванов 7, Сидоров 2, Петров 4 (менеджер, количество). Сводная: Строки=Менеджер, Значения=Количество (тип «Сумма»). Сколько заказов у Иванова покажет сводная? Впиши число.

Подсказка: Сложи все строки, где менеджер — Иванов.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Чтобы вместо суммы сводная показывала СРЕДНЕЕ значение, нужно…

Акликнуть по полю в «Значениях» → «Настройки поля значений» → выбрать «Среднее»

Бпересчитать исходную таблицу вручную

Вудалить поле и добавить заново

Гизменить область «Строки»

Подсказка: Тип расчёта меняется прямо у поля в «Значениях», без пересборки сводной.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. После изменения исходных данных сводная таблица в Excel…

Ане обновится сама — нужно нажать «Обновить» (правой кнопкой мыши по таблице)

Бобновится сама мгновенно

Висчезнет

Гпокажет ошибку

Подсказка: В Google Таблицах сводная обновляется автоматически — в Excel нет.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Задание. Напиши формулу сам. В таблице продаж код товара лежит в A2, а справочник товаров — в диапазоне $E$2:$F$50, где первый столбец — код, второй — цена. Нужно подтянуть цену к строке продажи с точным совпадением, и так, чтобы при копировании формулы вниз диапазон справочника не съезжал (он уже записан абсолютной ссылкой). Впиши формулу целиком, со знаком равно; разделитель аргументов — точка с запятой.

Подсказка: Четыре аргумента ВПР: что ищем (ячейка с кодом), где ищем (справочник с долларами), номер столбца с ценой, признак точного совпадения.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Чтобы в сводной таблице оставить только два конкретных города, их…

Адобавляют в область «Фильтры» и отмечают нужные значения

Будаляют из исходной таблицы

Всортируют по алфавиту

Гвыделяют цветом

Подсказка: Ограничение видимых данных — это и есть работа области «Фильтры».

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Сводная таблица магазина показывает выручку и себестоимость по товарам. Нужно найти товар с ЛУЧШЕЙ и товар с ХУДШЕЙ прибыльностью за весь период. Что для этого нужно сделать?

Адобавить поле «Прибыль» (выручка минус себестоимость) в значения и отсортировать сводную по этому полю

Ботсортировать исходную таблицу по алфавиту

Вприменить условное форматирование к исходной таблице

Гпостроить диаграмму без сортировки

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

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Контрольный вопрос. Отметь все верные утверждения про сводные таблицы (не менее двух вариантов).

Выберите все верные варианты.

Аобласть «Строки» задаёт категории группировки

Бобласть «Значения» может считать не только сумму, но и среднее/количество/максимум

Всводную можно фильтровать по значению одного из полей

Гсводная в Excel обновляется автоматически при любом изменении исходных данных

Добъединённые ячейки в исходных данных — обязательное требование

Подсказка: Два варианта приписывают сводной то, чего она не делает.

На курсе задания проверяются автоматически. Решить первое задание бесплатно →  ·  Подобрать курс

Итог

Итог. Сводная таблица группирует и считает данные без формул — но исходная таблица должна быть «сплошной» (без объединённых ячеек и пустых строк). Строки и столбцы задают категории, значения — что считать (сумма, среднее, количество и другие), фильтры — что оставить. Тип расчёта меняется в «Настройках поля значений». Обновляют сводную вручную (Excel) или она обновляется сама (Sheets). Для бизнес-задач сводную часто комбинируют с ВПР — сначала подтягивают нужное поле в исходную таблицу, потом строят сводную и сортируют по нужному показателю.

← Назад к главе 4  ·  ↑ В начало урока  ·  ⌂ В начало курса  ·  Вперёд: Диаграммы →