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

Сводная таблица — инструмент Excel и Google Таблиц, который за несколько щелчков собирает итоги из большой таблицы: выручку по менеджерам, продажи по месяцам, количество заказов по городам. Формулы писать не нужно — вы перетаскиваете названия столбцов в нужные области, а программа сама группирует и считает. Ниже — как подготовить данные, как построить сводную в Excel и в Google Таблицах и какие ошибки встречаются чаще всего.

Содержание

• Когда нужна сводная таблица
• Как подготовить данные
• Как сделать сводную таблицу в Excel
• Как сделать сводную таблицу в Google Таблицах
• Полезные приёмы
• Частые ошибки
• Где научиться

---

Когда нужна сводная таблица

Представьте выгрузку продаж за квартал: несколько тысяч строк, в каждой дата, менеджер, город, товар и сумма. Руководитель просит ответить на три вопроса: кто из менеджеров продал больше всех, как менялась выручка по месяцам и какие товары лучше идут в каждом городе. С фильтрами и формулами это час работы. Сводная таблица отвечает на каждый вопрос за минуту — и так же быстро перестраивается, когда появляется следующий.

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

Сводная таблица строится правильно, только если исходные данные аккуратные. Проверьте пять вещей:

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

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

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

1. Встаньте в любую ячейку с данными.
2. Откройте вкладку «Вставка» → «Сводная таблица». Excel сам выделит диапазон и предложит разместить сводную на новом листе — согласитесь.
3. Справа появится список полей — это названия ваших столбцов. Под ним четыре области: «Фильтры», «Столбцы», «Строки» и «Значения».
4. Перетащите «Менеджер» в «Строки», а «Сумма» — в «Значения». Готово: выручка по каждому менеджеру и общий итог.
5. Чтобы увидеть продажи по месяцам, перетащите «Дата» в «Столбцы». Если Excel не сгруппировал даты сам, щёлкните по любой дате в сводной правой кнопкой → «Группировать» → «Месяцы».

По умолчанию числа суммируются. Чтобы посчитать количество сделок или среднюю сумму, щёлкните по полю в области «Значения» → «Параметры полей значений» и выберите нужную операцию.

Важно: сводная в Excel не обновляется сама. Когда исходные данные изменились, щёлкните по сводной правой кнопкой → «Обновить».

Как сделать сводную таблицу в Google Таблицах

1. Выделите данные вместе с заголовками.
2. Откройте меню «Вставка» → «Сводная таблица», выберите «Новый лист» и нажмите «Создать».
3. Справа откроется редактор с теми же областями: «Строки», «Столбцы», «Значения» и «Фильтры». Поля добавляются кнопкой «Добавить» рядом с каждой областью.
4. Добавьте «Менеджер» в «Строки» и «Сумма» в «Значения» — получится та же сводка, что и в Excel. Операция выбирается в поле «Суммировать по»: SUM — сумма, COUNTA — количество, AVERAGE — среднее.

Главное отличие от Excel: сводная в Google Таблицах пересчитывается автоматически, как только меняются исходные данные. А если таблицу ведут несколько человек, все сразу видят свежие итоги.

Полезные приёмы

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

Частые ошибки

• Сводная показывает старые цифры. В Excel её нужно обновить вручную; проверьте также, что новые строки попали в диапазон, — для этого и нужна «умная» таблица.
• Вместо суммы стоит «Количество». В столбце с числами есть текст или пустые ячейки, и Excel решил, что это не числа. Исправьте данные и снова выберите «Сумму».
• Даты не группируются по месяцам. Часть дат записана текстом. Приведите столбец к формату даты.
• Один и тот же менеджер в двух строках. В данных его имя написано по-разному — с лишним пробелом или опечаткой.

Где научиться

Сводным таблицам и диаграммам в онлайн-курсе OnStudy «Excel и Google Таблицы с нуля до Pro» посвящён отдельный модуль из шести уроков — с разбором Excel и Google Таблиц на рабочих примерах. Всего в курсе 54 урока: от первых формул и функции ВПР до макросов и Power Query.