Сводные таблицы в Excel: как сделать и что анализировать

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

Подготовьте плоскую таблицу: заголовок в каждой колонке, одна строка на одно событие, даты как даты и суммы как числа. Выделите диапазон и превратите его в таблицу Excel через Home → Format as Table. Затем выберите Insert → PivotTable, разместите отчёт на новом листе и перетащите дату в Rows, сумму в Values. Проверьте, что в Values стоит Sum, а не Count. Для разбивки добавьте тип брони в Columns.

Сводная не исправляет исходные данные. Если в одном бронировании несколько билетов, сначала добейтесь одной строки на бронь. Если источник поменялся, обновите отчёт и сверьте общий итог с контрольной суммой. Для дашборда, который читает руководитель, сводная станет расчётным листом; оформление и принятие решения — отдельный шаг.

В этом примере контроль — 23 496 оформленных броней на 1 884 807 100 ₽ за 3–6 августа 2026 года. Это полные четыре дня учебной базы, а не случайная выборка и не сумма оплат после возвратов. Если ваша сводная показывает другое число, остановитесь до построения графика: сначала проверьте выбранный диапазон, фильтры и способ агрегации.

Скачайте один и тот же срез для всех шагов

Для практики скачайте XLSX с типизированными датами и суммами или CSV с теми же строками. В книге лист «Брони» содержит таблицу Bookings. Листы «По дням» и «Типы» пригодятся в уроке Power Query. CSV и главный лист книги имеют 23 496 одинаковых ключей book_ref; в них нет имени пассажира, телефона, почты и номера билета.

Источник — учебная база «Авиаперевозки», производная от demo-small Postgres Professional по лицензии MIT. Даты перенесены на 3283 дня и показываются по московскому времени. Подробности преобразования и лицензия находятся в описании датасета. Поле total_amount означает стоимость оформленной брони в рублях. Одна бронь может содержать один или несколько билетов; ticket_bucket делит их на 1 и 2+.

Файл намеренно ограничен четырьмя днями, чтобы открываться как учебный пример. В других статьях кластера встречается полная неделя 3–9 августа: её сумма больше, потому что там ещё три дня. Не смешивайте числа двух периодов в одном заголовке. Перед каждым расчётом записывайте даты и зерно строк прямо над отчётом.

Проверьте зерно до первого клика

Плоская таблица для этого вопроса хранит одну строку на оформленную бронь. Это означает, что 23 496 строк должны содержать 23 496 разных book_ref. Если взять таблицу билетов и соединить её с бронированиями напрямую, сумма по броне повторится один раз на каждый билет. Дорогие многоместные брони станут особенно тяжёлыми в итоге, а внешне аккуратная сводная скроет источник ошибки.

В выгрузке число билетов уже рассчитано отдельно на уровне book_ref, затем присоединено к брони. Сравните общее число строк с количеством уникальных ключей; оба равны 23 496. Для своего источника проверьте также пустые ключи, дату, цену и единицу валюты. Сводная умеет группировать и суммировать, но не умеет догадаться, что две строки означают одну транзакцию.

Не переносите в плоскую таблицу итоги, вставленные между строками. Если после каждого дня есть строка «Всего», сводная сложит её с отдельными бронями и удвоит деньги. Шапка должна занимать одну строку, без объединённых ячеек; пустых столбцов и подзаголовков внутри диапазона быть не должно. Итоги и пояснения держите вне источника.

Сделайте из диапазона таблицу Excel

В готовом XLSX диапазон уже оформлен как таблица Bookings. Если вы начали с CSV, импортируйте его, проверьте тип book_date и выделите любую ячейку внутри данных. Выберите Home → Format as Table, подходящий спокойный стиль и подтвердите, что первая строка содержит заголовки. Таблица Excel расширяет источник сводной, когда вы добавляете строки непосредственно в неё; обычный фиксированный диапазон легко забыть расширить.

Назовите таблицу коротко и стабильно: Bookings. Это имя понадобится для формул на дашборде. До построения отчёта проверьте, что даты сортируются хронологически и формат ячейки не маскирует текст под дату. Суммы должны быть числами: если Excel считает денежный столбец как Count, скорее всего часть значений пришла текстом из CSV.

При импорте CSV сначала задайте кодировку UTF-8 и типы столбцов, а не открывайте файл двойным щелчком, если ваша локаль иначе читает разделитель или дату. У book_ref тип должен остаться текстовым: это идентификатор, не количество. У суммы — целое число без символа ₽ внутри ячейки; валютный знак задаётся числовым форматом.

Создайте сводную и разложите поля по областям

Щёлкните по таблице, затем Insert → PivotTable и выберите новый лист. В списке полей переместите book_date в Rows, ticket_bucket в Columns, total_amount в Values. Для контрольного числа броней добавьте book_ref в Values и задайте Count. У нас ключ уникален, поэтому Count строк совпадёт с числом броней. Для поля из другого источника это совпадение нужно проверить отдельно.

Область Filters полезна, если нужно выбрать один фиксированный сегмент для всего отчёта; Columns — если хочется видеть сегменты рядом. Если положить дату в Filters, получится один итог за выбранный день, но исчезнет динамика. Для четырёх дней лучше оставить дату в Rows. Перетаскивание поля в другое место меняет вопрос к данным, а не только внешний вид таблицы.

Сначала соберите наиболее простой вариант: дата и сумма. Проверьте 3 августа — 452 158 300 ₽ и 5 665 броней. Только после этого добавляйте разрез 1/2+. Такой порядок даёт точку сверки: если после нового поля общий итог поменялся, проблема не в оформлении диаграммы, а в фильтре, диапазоне или подготовке данных.

Сумма, количество и среднее отвечают на разные вопросы

Для total_amount в Value Field Settings выберите Sum: он отвечает, на какую сумму оформлены брони. Count того же поля даст число непустых значений, а Average — среднюю стоимость одной брони. Для 23 496 строк сумма составляет 1 884 807 100 ₽, средняя около 80 218,21 ₽. Если положить в Values ticket_count и выбрать Sum, получите число билетов, а не число броней. Подпишите показатель точно, иначе сравните несравнимое.

Средняя стоимость по группам тоже не складывается. Для броней с одним билетом она равна примерно 57 193 ₽, с несколькими — примерно 124 869 ₽; общую среднюю нужно вычислять из общей суммы и общего числа броней. Нельзя взять среднее двух средних без учёта размеров групп. Сводная сделает правильный расчёт, если использует исходные строки, но готовые агрегаты на отдельном листе потребуют взвешивания.

Когда поле total_amount попало в сводную как Count, проверьте исходные ячейки и способ импорта. Microsoft прямо описывает такой симптом для чисел, распознанных как текст. Простая смена Count на Sum может не решить проблему: сначала приведите источник к числовому типу, затем обновите сводную и снова сверьте сумму с 1 884 807 100 ₽.

Покажите долю, не теряя рублёвый итог

Перетащите total_amount в Values второй раз. Для первого экземпляра оставьте Sum, для второго выберите Show Values As → % of Grand Total. Тогда справа от 998 204 100 ₽ у группы 2+ появится доля около 52,96% от всей суммы; на группу с одним билетом приходится около 47,04%. Доля считается от суммы броней, а не от числа броней. По количеству многоместных броней 7 994 из 23 496 — около 34,02%.

В отчёте укажите знаменатель прямо в названии поля: «Доля суммы от итога, %». Если пользователь отфильтрует одну дату, итог станет суммой выбранного дня, а процент пересчитается относительно него. Для сравнения стабильной базы «от всей недели» такая сводная уже не подходит без отдельной меры или контрольного расчёта: фильтр меняет смысл доли.

Проверка арифметики проста: 998 204 100 ÷ 1 884 807 100 ≈ 0,5296. Не округляйте доли до целых процентов, если затем хотите сравнивать небольшие изменения между днями. Но и четыре знака после запятой здесь не помогают решению; двух обычно достаточно.

Группируйте даты только после проверки их типа

У нас четыре дневные даты: поместите book_date в Rows и получите четыре строки. Для длинного периода можно щёлкнуть по дате в сводной и выбрать Group, затем Days, Months или Years. Прежде чем перейти к месяцу, проверьте, что неполный текущий месяц не сравнивается с полным прошлым. Для недельных отчётов зафиксируйте, с какого дня начинается неделя и по какому часовому поясу записана дата.

Если Group не работает или дни выстраиваются лексикографически, вероятно, в источнике текст или пустые значения. Наш XLSX хранит book_date как дату; в CSV это ISO-строка, тип при импорте нужно назначить. Формат вывода «03.08» сам по себе не превращает текст в календарную дату. Для одного дня группировка по месяцам бессмысленна: она скрывает распределение.

В учебном срезе каждый день полный и сумма растёт от 452,16 до 485,60 млн ₽. Это описание четырёх наблюдений, а не доказательство сезонного тренда: период слишком короток. Сводная поможет быстро увидеть последовательность, а решение о причине изменения потребует сравнения с аналогичными днями и другой информации.

Вычисляемое поле: простой коэффициент и опасная средняя

В классической сводной Excel есть Calculated Field через PivotTable Analyze → Fields, Items & Sets. Им можно получить простой показатель из числовых полей, когда смысл операции сохраняется после агрегации. Например, если в каждой строке есть стоимость и подтверждённая скидка, разница между суммами даст сумму до скидки. В нашей выгрузке отдельной колонки скидки нет, поэтому такую цифру мы не создаём.

Не используйте вычисляемое поле для «средней цены билета», деля total_amount на ticket_count, пока не определили вопрос. Среднее построчных отношений и отношение двух общих сумм различаются. В классической сводной расчёт вычисляемого поля выполняется над агрегатами полей; результат может отличаться от ожидания «посчитать формулу для каждой исходной строки». Если нужен проверяемый коэффициент, подготовьте поле на уровне строки либо создайте меру в модели данных.

Для наших данных безопасный показатель — средняя сумма одной брони: общая сумма ÷ число book_ref, около 80 218,21 ₽. Он использует два ясно названных агрегата. Перед добавлением производной метрики запишите числитель, знаменатель, период и фильтры. Если в знаменателе ноль, отображайте отсутствие значения, а не бесконечность или молчаливый ноль.

Срез и временная шкала управляют выборкой

Для быстрого переключения между типами брони выберите сводную и PivotTable Analyze → Insert Slicer, затем поле ticket_bucket. Кнопки 1 и 2+ сделают активный фильтр видимым читателю. Для дат Microsoft предлагает Insert Timeline: это отдельный фильтр времени с выбором масштаба. В учебном четырёхдневном файле его польза ограничена, но в годовом отчёте он помогает не путать дни и месяцы.

После выбора только 2+ сумма станет 998 204 100 ₽ на 7 994 бронях. Это проверка работы среза. Если на экране осталась карточка 1 884 807 100 ₽, а график уже отфильтрован, читатель видит разные генеральные совокупности как одну. В Excel срез связывается с другими сводными только при общем источнике; подключение выполняется через Report Connections.

Покажите состояние фильтра в заголовке отчёта. Отдельная кнопка очистки фильтра нужна не ради красоты: забытый выбранный сегмент — одна из самых частых причин «пропажи» денег. Для фиксированной печатной таблицы с одним разрезом обычный Filters может быть понятнее интерактивного среза.

Обновление: новый файл не обновляет отчёт сам собой

Если вы добавили строки в Bookings, выберите PivotTable Analyze → Refresh; для всех связанных объектов используйте Refresh All. Затем проверьте крайние даты, число строк и общий итог. Обновление пересчитывает отчёт по источнику, но не исправляет ошибочный импорт, дубли или невошедший в таблицу диапазон. Если источник был файлом, убедитесь, что отчёт подключён именно к его актуальной версии.

Сохраните рядом с дашбордом строку «Данные по 06.08.2026, выгрузка обновлена …». Дата открытия книги не равна дате обновления данных. Если новые брони дописываются к незакрытому дню, не сравнивайте его сумму с полным предыдущим днём: такой график почти всегда показывает ложное падение.

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

Пять ошибок, из-за которых сводная выглядит правдоподобно и врёт

Первая — сумма импортирована текстом, и Excel показывает Count вместо Sum: руководитель принимает число строк за деньги. Вторая — соединение бронирований с билетами до агрегации: дорогая бронь считается несколько раз, после чего сегмент 2+ кажется больше, чем он есть. Третья — в исходный диапазон попала строка итога: показатель удвоен ещё до диаграммы.

Четвёртая — объединённая шапка или пустые столбцы нарушают распознавание полей: часть данных остаётся вне отчёта. Пятая — фильтр среза остался на одном сегменте, а заголовок всё ещё обещает весь бизнес. Шестая — свежие строки дописаны ниже фиксированного диапазона и не попали в Refresh. У всех шести ошибок одно следствие: управленческое решение опирается на неверную совокупность.

Поймайте их до оформления: напишите отдельный контрольный итог и число уникальных ключей. Если общий итог сводной не равен 1 884 807 100 ₽, не подгоняйте формулу KPI вручную. Исправляйте источник или настройку агрегации и перезапускайте сверку. Диаграмма с красивой подписью не компенсирует потерянные строки.

Когда сводная лучше формулы, а когда её уже мало

Сводная хороша, когда один аналитик исследует локальный набор строк и быстро меняет разрезы: день, тип брони, сумма, количество. Формула вроде SUMIFS полезна, если расположение ячеек заранее задано и отчёт небольшой. Но десятки независимых формул с собственными условиями легко расходятся по датам. В сводной фильтр и агрегация видны в структуре полей.

Если один отчёт нужен нескольким командам, обновляется автоматически, объединяет базы с разными правами доступа и требует единого определения метрик, пора думать о подготовленной модели и BI. Excel остаётся хорошим прототипом: он обнаруживает вопросы, которые нужно зафиксировать до переноса. Переносите в BI не оформление листа, а проверенные определения, контрольные запросы и ожидаемые числа.

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

В LibreOffice Calc инструмент тоже называется «Сводная таблица»: официальная справка ведёт через Данные → Сводная таблица или Вставка → Сводная таблица. Области полей похожи по смыслу, но команды Excel для срезов и временной шкалы не переносите в Calc без отдельной проверки. Описанный здесь путь по меню относится именно к Excel.

Частые вопросы и следующий шаг

«Почему сводная считает количество вместо суммы?» Обычно Excel распознал число как текст или в поле есть неоднородные значения. Проверьте тип источника, очистите значения, обновите сводную и только затем задайте Sum. «Как обновить сводную после новых строк?» Добавьте строки внутрь таблицы Bookings, нажмите Refresh и сверьте новый диапазон.

«Можно ли построить несколько сводных из одного источника?» Да. Для общего среза они должны использовать один источник; проверьте Report Connections и протестируйте на группе 2+. «Почему процент изменился после фильтра?» % of Grand Total делит на видимый после фильтра итог, поэтому знаменатель стал другим. «Можно ли посчитать уникальные брони по билетам?» Да, но сначала определите зерно и подготовьте данные или модель; простой Count билетов не равен числу броней.

Дальше превратите этот расчёт в дашборд на трёх листах. Если исходные CSV приходят каждый день, начните с повторяемой подготовки данных. Данные для реальных дашбордов часто собирают запросом; отработать агрегацию можно в тренажёре «SQL с нуля».