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

Какой вопрос должна решить сводная таблица

Начните с одного предложения: «Хочу увидеть выручку каждого сотрудника за каждый месяц». В нём уже есть будущие элементы отчёта. Сотрудники станут строками, месяцы — столбцами, выручка — значениями, которые нужно суммировать.

Если вопрос звучит как «проанализировать продажи», уточните его до построения таблицы. Вам нужны суммы, число заказов, средний размер заказа или доля сотрудника в выручке? Это разные показатели. Они могут строиться по одному источнику, но отвечают на разные рабочие вопросы.

Полезно сразу записать, что означает одна строка исходного списка. В нашем примере одна строка — одна продажа с уже рассчитанной выручкой. Поэтому число исходных строк совпадает с числом продаж. Если в настоящей выгрузке один заказ занимает несколько строк по товарам, подсчёт строк даст другое значение.

Подготовим небольшой список продаж

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

Всего здесь шесть продаж на 50 000. В сентябре выручка равна 26 000, в октябре — 24 000. Эти числа запишите отдельно как контрольные. Они пригодятся, когда будете менять группировку и фильтры.

Для системной работы с такими задачами можно рассмотреть курс «Excel и Google Таблицы» от «Бруноям». В программе рядом со сводными таблицами представлены функции, большие таблицы и другие инструменты обработки данных. В описании также указаны практические задания и проверка наставником — это полезно, если вы хотите разбирать не только кнопки интерфейса, но и собственные ошибки в отчётах.

В самостоятельном примере сначала создадим простой отчёт. Затем намеренно поменяем источник и убедимся, что умеем правильно обновлять результат. Именно этот второй проход часто показывает, насколько хорошо устроен рабочий файл.

Создайте сводную таблицу на отдельном листе

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

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

В инструкциях Microsoft по созданию сводных таблиц отдельно отмечаются требования к исходному списку, включая заголовки и отсутствие пустых разделительных строк. Для нашего упражнения это означает, что все шесть продаж должны входить в одну цельную таблицу.

После создания появится список полей и области для строк, столбцов, значений и фильтров. Пока не перетаскивайте туда всё подряд. Вернитесь к исходному вопросу: выручка сотрудников по месяцам. Он подсказывает минимальный набор нужных полей.

Распределите поля по смыслу отчёта

Перенесите «Сотрудник» в строки, «Дата» — в столбцы, «Выручка» — в значения. Если даты ещё не сгруппированы, настройте группировку по месяцам. Для источника за несколько лет полезно сохранять и год, иначе одинаковые месяцы разных лет могут оказаться объединены.

Теперь проверьте способ вычисления в области значений. Нам нужна сумма выручки. Если Excel показывает количество вместо суммы, посмотрите на тип исходных данных и настройки поля. Числа, сохранённые как текст, могут мешать ожидаемому расчёту.

После правильной настройки результат нашего примера выглядит так:

Сначала сравните общий итог с контрольной суммой. Затем проверьте одну строку вручную: у Анны в сентябре две продажи — 12 000 и 6 000. Если они дают показанные 18 000, вы подтвердили логику конкретной группы, а не только совпадение общего числа.

Чем сумма отличается от количества и среднего

Одно и то же поле можно использовать для разных вычислений. Сумма выручки отвечает на вопрос о деньгах. Количество записей — о числе заполненных значений. Среднее — о средней величине по тем записям, которые попали в группу.

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

Со средними нужна дополнительная внимательность. Представим другой маленький пример: у одного сотрудника две продажи на 100 и 200, у второго — одна на 900. Средние по сотрудникам равны 150 и 900. Их простое среднее — 525, но средняя продажа по всем трём записям равна 400: (100 + 200 + 900)/3.

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

Добавьте фильтр и объясните изменение итога

Теперь перенесите «Регион» в область фильтров и оставьте только Север. В отчёте останутся северные продажи: 12 000, 8 000 и 9 000. Ожидаемый итог — 29 000. Остальные записи не удалены из источника, они просто не участвуют в текущем представлении.

Перед отправкой отчёта проверьте, заметно ли читателю ограничение по региону. Число 29 000 без подписи легко принять за общую выручку отдела. Для рабочего файла полезно рядом с названием отчёта указывать период и действующие условия отбора.

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

Для контроля верните показ всех регионов. Итог должен снова стать 50 000. Эта простая обратная проверка помогает понять, что уменьшение суммы связано с выбранным фильтром, а не с потерей данных при подготовке источника.

Почему дата не группируется по месяцам

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

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

После исправления источника обновите сводную таблицу и повторите группировку. Полезно проверить несколько исходных дат вручную: не перепутаны ли день и месяц, не появилось ли неожиданного года. Иначе технически успешная группировка объединит записи по неверно распознанным датам.

При регулярных выгрузках очистку можно сделать повторяемой процедурой, например через Power Query. Но сначала нужно понять конкретную проблему на маленьком примере. Автоматизация неверно выбранного преобразования лишь ускорит получение неправильного отчёта.

Как правильно добавить новые продажи

Верните все регионы и добавьте в исходную таблицу седьмую строку: 10.10.2026, Анна, Юг, 4 000. Проверьте, что строка действительно вошла в источник, а не записана рядом с ним. Теперь исходная выручка составляет 54 000.

Обновите сводную таблицу. Для ручного обновления можно использовать соответствующую команду на вкладке сводной таблицы или в её контекстном меню. В справке Microsoft об обновлении данных описаны доступные варианты.

После обновления проверьте несколько связанных чисел. Общий итог должен стать 54 000, октябрь — 28 000, итог Анны — 31 000. Если общий итог прежний, выясните, включена ли новая строка в источник и обновили ли вы именно нужную сводную таблицу.

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

Как оформить отчёт без потери смысла

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

Затем выберите формат чисел и порядок категорий. Одинаковые разряды и разделители помогают сравнивать суммы. Избыточные десятичные знаки могут затруднять чтение, но округление должно соответствовать задаче. Если вы показываете тысячи рублей, укажите это явно.

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

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

Как закрепить навык на своей рабочей задаче

Возьмите небольшой обезличенный список из собственной работы и сформулируйте два разных вопроса к нему. Например, сумма по подразделениям и число записей по месяцам. Постройте два представления и объясните, почему поля распределены именно так.

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

Если хотите последовательно освоить подготовку источников, функции и анализ, изучите курс «Excel и Google Таблицы» «Бруноям». Сопоставьте программу со своими примерами и уточните, как наставник разбирает ошибки в логике расчёта. Это поможет перейти от одной удачной сводной таблицы к отчётам, которые удобно обновлять и проверять.