Что такое сводная таблица и когда она нужна
Сводная таблица - это инструмент табличного редактора, который берёт длинный список данных, например продажи по дням, расходы по категориям или результаты опроса, и превращает его в компактную таблицу с итогами. Вместо того чтобы вручную считать сумму по каждому месяцу или количество заказов по каждому клиенту, редактор делает это за вас: достаточно указать, какие столбцы исходных данных считать, а какие - группировать.
Сводная таблица нужна, когда исходных строк много - сотни или тысячи - и обычными формулами суммировать данные неудобно или долго. Она также полезна, если исходные данные постоянно пополняются: сводная таблица обновляется одним нажатием, а не пересчитывается заново вручную.
Типичные задачи: посчитать сумму продаж по каждому товару, узнать, сколько заказов пришло от каждого менеджера, сравнить расходы по месяцам, посчитать среднюю оценку по каждой группе в анкете.
Какой должна быть исходная таблица
Прежде чем строить сводную таблицу, стоит проверить исходные данные - от их структуры зависит, получится ли построение вообще.
- У каждого столбца должен быть заголовок в первой строке, и заголовок обязательно текстовый, а не число или дата.
- Заголовки не должны повторяться - два одинаковых названия столбца сбивают редактор.
- В таблице не должно быть полностью пустых строк или столбцов внутри диапазона данных - редактор считает пустую строку концом таблицы и не захватит то, что расположено ниже.
- Каждый столбец должен содержать данные одного типа: если в столбце с суммами часть ячеек - текст вроде "нет данных", такие строки не попадут в расчёт.
- Объединённые ячейки в исходной таблице лучше не использовать - они мешают редактору правильно определить границы строки.
Если данные скопированы из другой программы или выгружены из учётной системы, стоит один раз пройтись по таблице и убрать лишние пустые строки и объединения ячеек - это сэкономит время при построении.
Как вставить сводную таблицу
Ссылку на инструмент построения обычно можно найти на вкладке, отвечающей за вставку объектов в документ. Мы опишем общий порядок действий, который подходит для большинства табличных редакторов, включая Excel.
- Выделите исходную таблицу вместе с заголовками столбцов - или просто поставьте курсор внутрь таблицы, редактор сам определит границы.
- Откройте меню вставки и найдите пункт, отвечающий за сводную таблицу.
- Укажите, где разместить результат: на новом листе или на существующем, в определённом месте.
- Подтвердите создание - откроется пустой макет сводной таблицы и панель с полями, которая соответствует столбцам исходных данных.
На этом этапе таблица ещё пустая - в ней нет ни одной строки. Дальше нужно решить, какие поля куда поместить.
Как разложить поля по строкам, столбцам, значениям и фильтрам
Панель полей сводной таблицы обычно поделена на четыре зоны, и от того, куда попадает каждое поле, зависит вид итоговой таблицы.
- Строки - сюда помещают поле, по которому нужно сгруппировать данные вертикально: например, название товара или имя менеджера. Каждое уникальное значение станет отдельной строкой.
- Столбцы - похожий принцип, но группировка идёт по горизонтали. Например, если строки - это товары, а столбцы - месяцы, получится таблица "товар на пересечении с месяцем".
- Значения - сюда помещают то, что нужно посчитать: суммы, количество, среднее. Это единственная зона, где данные не группируются, а рассчитываются.
- Фильтры - поле здесь не отображается в самой таблице, но позволяет отфильтровать всю сводную таблицу по значению, например показать данные только за один регион.
Поле можно перетащить мышью из общего списка в нужную зону, а можно убрать обратно, если результат не устраивает. Экспериментировать безопасно: сводная таблица не меняет исходные данные, а только их отображает.
Одно и то же поле можно поместить сразу в несколько зон - например, название товара и в строки, и в фильтры одновременно, если нужно сначала отфильтровать список товаров, а затем ещё и посмотреть на них построчно. Порядок полей внутри одной зоны тоже имеет значение: если в строках стоят сначала регион, а потом менеджер, таблица сгруппирует менеджеров внутри каждого региона; если поменять поля местами, группировка пойдёт наоборот - по менеджерам, а внутри них по регионам.
Как группировать данные по датам и числовым интервалам
Если в исходной таблице есть столбец с датами - например, дата заказа, - помещённый в строки или столбцы сводной таблицы, он по умолчанию покажет каждую отдельную дату отдельной строкой, и при большом периоде список получится очень длинным. Чтобы этого избежать, поле с датами можно сгруппировать: щёлкните правой кнопкой по любой дате внутри сводной таблицы и найдите пункт группировки. Редактор предложит объединить даты по дням, месяцам, кварталам или годам - можно выбрать один уровень или сразу несколько, тогда таблица покажет вложенную структуру: год, а внутри него - месяцы.
Похожим образом можно сгруппировать и числовые значения, не связанные с датами - например, суммы заказов. Вместо того чтобы показывать каждую уникальную сумму отдельной строкой, редактор объединит значения в интервалы заданного шага: скажем, все заказы от нуля до тысячи в одну группу, от тысячи до двух тысяч - в следующую. Это удобно, когда нужно понять не точные цифры, а общее распределение - сколько заказов попадает в каждый ценовой диапазон.
Как изменить функцию агрегации
По умолчанию для числовых полей редактор обычно считает сумму, а для текстовых - количество значений. Это не всегда то, что нужно.
Чтобы поменять функцию расчёта, щёлкните по полю в зоне значений и найдите настройки поля - там будет список доступных операций: сумма, количество, среднее, максимум, минимум и другие. Например, если в зоне значений стоит сумма по столбцу с зарплатой, а вам нужна средняя зарплата по отделу, переключите функцию на среднее.
Полезно помнить: если поле показывает количество вместо суммы, скорее всего в исходном столбце есть хотя бы одна текстовая ячейка или пустая строка - редактор не смог определить столбец как числовой и переключился на подсчёт количества значений.
Как обновить сводную таблицу после изменения источника
Сводная таблица не пересчитывается сама по себе при изменении исходных данных - это частое заблуждение новичков. Если вы добавили новые строки в исходную таблицу, изменили сумму в одной из ячеек или удалили строку, сводная таблица покажет старые цифры, пока её не обновить вручную.
Обновление обычно делается одной командой - кнопкой "обновить" на панели инструментов сводной таблицы или через правый клик по самой таблице. Если в исходные данные добавлены новые строки за пределами первоначального диапазона, может понадобиться сначала расширить диапазон источника в настройках сводной таблицы, а потом обновить результат.
Как отсортировать и оформить результат
По умолчанию сводная таблица располагает строки в том порядке, в котором соответствующие значения встретились в исходных данных, или по алфавиту - это не всегда удобно для чтения. Сортировку можно задать по любому столбцу значений: щёлкните по заголовку столбца с суммой или количеством и выберите сортировку по убыванию, чтобы сразу увидеть товар с наибольшими продажами или менеджера с наибольшим числом заказов наверху списка.
Для наглядности к сводной таблице можно применить готовый стиль оформления - чередующуюся заливку строк, выделение итоговых строк жирным шрифтом - через ту же панель инструментов, что появляется при работе со сводной таблицей. Для числовых значений полезно сразу задать формат отображения: например, разделители разрядов для крупных сумм или отображение процентов, если в качестве функции выбрана доля от общего итога. Это делается через настройки формата ячеек, применённые к области значений сводной таблицы.
Частые ошибки новичков
- Забывают обновить таблицу после правки исходных данных и работают со старыми цифрами, не замечая расхождения.
- Оставляют пустые строки в середине исходной таблицы - из-за этого редактор захватывает не весь диапазон, и часть данных выпадает из расчёта.
- Путают зоны строк и значений - помещают числовое поле в строки, получают длинный список чисел вместо суммы.
- Не проверяют тип данных в столбце - смешение чисел и текста в одном столбце приводит к тому, что функция считает количество вместо суммы, и результат выглядит завышенным или заниженным без очевидной причины.
- Строят сводную таблицу поверх нефильтрованных данных - с повторяющимися строками или неисправленными опечатками в названиях категорий, из-за чего одна и та же категория считается как две разные.
Сводная таблица - инструмент, который прощает эксперименты: поля всегда можно переставить или убрать, а исходные данные при этом не пострадают. Если результат выглядит странно, в первую очередь стоит проверить исходную таблицу на пустые строки и смешение типов данных - это причина большинства ошибок при первом знакомстве с инструментом.