Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Обратите внимание, что «Лист прогноза» не справится с анализом сезонности, если она у вас сразу на нескольких уровнях, — например, высокая посещаемость ресторана по пятницам-субботам в рамках недели и высокий сезон в некоторые месяцы в рамках года.

Сводные таблицы (Pivot Tables)

Файл с примерами: Сводные таблицы.xlsx

Сводные таблицы (Pivot Tables) — один из самых простых в применении и в то же время один из самых мощных инструментов анализа данных в Excel (и в Google Таблицах).

А еще это очень гибкий инструмент: можно буквально в пару кликов переделать сводную таблицу и изменить ее структуру, получив ответы на свои вопросы. Сводные существуют в Excel уже несколько десятилетий (с версии 97), хотя с каждой версией программы, разумеется, получают новые возможности и опции.

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

Какие данные подходят для построения сводных таблиц?

Данные для сводной таблицы

Данные для сводных таблиц должны отвечать следующим условиям.

• Быть представленными в табличном виде (не обязательно «официальная» таблица Excel, но обязательно прямоугольный диапазон без пустых строк или столбцов).

• В этом диапазоне должен быть хотя бы один или несколько столбцов с данными, у каждого из которых есть заголовок в первой строке (первой строке диапазона, но не обязательно всего рабочего листа Excel).

• Каждый столбец — это один параметр, и в каждом столбце есть только один тип данных (текст, числа, даты и так далее).

• Каждая строка описывает одну совокупность всех параметров (одну операцию, транзакцию, сотрудника или клиента, одну рекламную кампанию или одно действие пользователя).

Какими могут быть источники данных?

• Просто диапазон Excel (например, A1:E550). Это допустимый вариант, но если вы предполагаете, что к нему будут добавляться новые строки, то лучше использовать таблицу Excel.

• Диапазон Excel со столбцами целиком (например, A: E). В таком случае в сводную попадут вообще все строки на листе, включая (пока) пустые. Их придется фильтровать. К тому же это может отрицательно повлиять на быстродействие.

• Таблица (Table). В случае с ней вы не ссылаетесь на конкретные строки в качестве источника данных — вы используете таблицу в целом, обращаетесь к ней по имени, а не отдельным строкам, как к источнику, и при появлении новых данных в таблице они попадут в сводную.

• Внешний источник данных (например, база данных, информация с сайта, Google Таблица и т. д.). Данные из таких источников можно загрузить с помощью Power Query. Эта надстройка может быть установлена бесплатно в Excel 2010–2013 и является частью Excel 2016 и 2019 (и Excel для Mac в Microsoft 365). В последних версиях ее можно найти на ленте инструментов: Данные → Получить и преобразовать (Data → Get & Transform Data).

Каким бы ни был источник данных в сводной, но если эти данные изменились, то сводная сама не обновится (как это происходит с формулами). Ее нужно обновить (Refresh) — в контекстном меню по щелчку правой кнопки мыши.

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

Сводная создается через вкладку «Вставка» (Insert) на ленте. Там есть две опции: «Сводная таблица» и (начиная с версии 2013) «Рекомендуемые сводные таблицы» (Recommended PivotTables).

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

«Рекомендуемые сводные» (Recommended PivotTables) появились в версии Excel 2013. При выборе этого варианта Excel в большинстве случаев предложит вам несколько вариантов сводных таблиц.

В большинстве случаев это будет не то, что вам нужно на 100%, но от предложенных вариантов можно отталкиваться и дорабатывать их, так что это все равно может сэкономить время.

Настройка сводной таблицы. Список полей

После создания сводной появляется пока еще пустой отчет, а справа будет список полей сводной таблицы. Это столбцы (поля) из исходных данных.

Хотя в будущем этот список может пополниться, так как в сводных можно создавать новые поля.