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

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

Вспомни: сводная таблица считает по уже готовым числам исходной таблицы — суммы и средние вместо ручного счёта мы уже делали функциями СУММ/СРЗНАЧ/СЧЁТ (тема 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  ·  ↑ В начало урока  ·  ⌂ В начало курса  ·  Вперёд: Диаграммы →