Сводные таблицы в Excel и Google Таблицах: пошаговое руководство

Короткий ответ

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

Большинство проблем со сводными возникает не в самой сводной, а в исходных данных. Поэтому начинаем с подготовки.

Шаг 1. Подготовьте исходные данные

Проверьте таблицу по списку:

• одна строка заголовков, у каждого столбца есть уникальное название;
• одна строка — одна запись: один заказ, одна продажа, одна операция;
• нет объединённых ячеек, пустых строк и столбцов внутри данных;
• нет строк «Итого» и промежуточных итогов — сводная посчитает их ещё раз;
• в каждом столбце один тип данных: даты — настоящие даты, суммы — числа.

В Excel превратите диапазон в «умную таблицу»: Ctrl+T. Тогда новые строки автоматически попадут в источник сводной. В Google Таблицах указывайте диапазон целыми столбцами, например Продажи!A:F.

Быстрая проверка типов: функция =ЕЧИСЛО(A2) для настоящей даты или суммы возвращает ИСТИНА. Числа, прижатые к левому краю, обычно хранятся как текст.

Шаг 2. Постройте сводную

• Excel: Вставка → Сводная таблица → выберите источник и новый лист.
• Google Таблицы: Вставка → Сводная таблица → диапазон и новый лист.

Пример на таблице продаж (дата, менеджер, город, товар, сумма, статус):

1. Менеджер — в «Строки».
2. Сумма — в «Значения», функция «Сумма».
3. Город — в «Столбцы».
4. Статус — в «Фильтры», оставьте только оплаченные.

Проверьте функцию агрегирования. Если в числовом столбце есть текст или пустые ячейки, Excel может выбрать «Количество» вместо «Суммы». Исправляется в параметрах поля значений. Там же можно показать значения как % от общей суммы — полезно для долей менеджеров или городов.

Шаг 3. Группировка по датам

Посмотреть продажи по месяцам, а не по дням:

• Excel: перетащите дату в «Строки». Современные версии часто группируют даты сами; если нет — правый клик по дате в сводной → Группировать → отметьте Месяцы и Годы. Если выбрать только месяцы, январь разных лет сольётся в одну строку.
• Google Таблицы: добавьте дату в «Строки», затем правый клик по любой дате в сводной → Создать группу дат → например, «Год-Месяц».

Если группировка недоступна, значит, в столбце есть значения, которые не являются датами: текст, ошибки, «дата» в виде строки.

Шаг 4. Вычисляемые поля

Вычисляемое поле — новый показатель на основе существующих, например маржа.

• Excel: Анализ сводной таблицы → Поля, элементы и наборы → Вычисляемое поле. Формула вида =(Сумма-Себестоимость)/Сумма.
• Google Таблицы: в «Значения» → Добавить → Вычисляемое поле, формула вида =SUM('Сумма')-SUM('Себестоимость') и пользовательский способ агрегирования.

Важная тонкость Excel: вычисляемое поле работает с итогами полей, а не построчно. Для доли или маржи это правильно. А вот =Цена*Количество перемножит сумму всех цен на сумму всех количеств — получится бессмыслица. Построчные расчёты добавляйте столбцом в исходные данные.

Шаг 5. Фильтры и срезы

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

Почему сводная «ломается»

Если при обновлении появляется сообщение о наложении сводных, значит, две сводные стоят слишком близко на одном листе. Разнесите их по разным листам.