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

Как сделать сводную таблицу по рабочей выгрузке? Выделите исходные данные, выберите «Вставка» → «Сводная таблица» и разместите отчет на новом листе. Затем перетащите филиал в строки, месяц даты в столбцы, расходы в значения, а перевозчика в фильтр. Проверьте, что Excel складывает рубли, а не считает записи.

Разберем это на расходах на доставку. Вымышленные данные двух филиалов за сентябрь и октябрь помогут пройти весь путь: от исходных строк до отчета, который можно сверить вручную. Основные шаги ниже относятся к настольному Excel для Windows; в веб-версии расположение настроек отличается.

Подготовьте исходник без промежуточных итогов

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

Уберите объединенные ячейки, пустые строки внутри диапазона и строки «Итого». Иначе сводная может повторно посчитать уже сложенные расходы. Проверьте написание филиалов: «Тула» и «Тула » с лишним пробелом могут попасть в разные группы.

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

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

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

Выделите A1:D13 и выберите «Вставка» → «Таблица». Подтвердите наличие заголовков. Такой источник удобнее фиксированного диапазона: новые строки, вошедшие в таблицу Excel, учитываются после обновления сводной.

Теперь выберите любую ячейку таблицы и откройте «Вставка» → «Сводная таблица». Проверьте источник, выберите размещение «На новый лист» и подтвердите создание. Рядом с пустым отчетом появится список полей.

В Excel для Интернета команда также находится на вкладке «Вставка», но источник и место размещения настраиваются в боковой панели. Ориентируйтесь на раздел своей версии в официальной инструкции Microsoft.

Расставьте поля и соберите месяцы

Перетащите поля в области списка:

Если даты остались отдельными днями, в настольном Excel нажмите правой кнопкой на дату внутри сводной и выберите «Группировать». Укажите месяцы. Для выгрузки за несколько лет добавьте годы: иначе одноименные месяцы разных лет окажутся вместе. Автоматически созданные кварталы уберите из столбцов, если они не нужны. Эти действия описаны в справке Microsoft о группировке.

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

Схема на вымышленных данных, не снимок интерфейса Excel. Показаны все перевозчики; суммы в рублях.

Без фильтров отчет должен выглядеть так:

Если Excel показывает «Количество» вместо «Суммы»

Число записей не отвечает на вопрос о расходах. В нашем источнике 12 операций: общий итог 12 при функции «Количество» означает подсчет заполненных значений, а не рублей.

Откройте меню поля Расходы_руб в области значений, затем «Параметры поля значений» и выберите «Сумма». В разных версиях подпись окна может быть короче — «Параметры поля». Описание функций есть в справке Microsoft.

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

Фильтр меняет базу сравнения

В фильтре «Перевозчик» выберите «Альфа». Теперь в отчете должны остаться расходы только этого перевозчика:

Итог 6400 сверяйте с четырьмя исходными строками «Альфы». Он не должен равняться 22000: эта сумма относится ко всем перевозчикам. Для полного отчета верните выбор всех элементов и проверьте также фильтры строк и столбцов.

Добавили строку? Проверьте источник и обновите

Сводная не обязана сразу отражать изменения исходника. После правок нажмите правой кнопкой внутри нее и выберите «Обновить».

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

У таблицы Excel тоже проверьте границу: новая строка должна стать ее частью. Обновление не исправляет неправильно выбранный источник.

Перед передачей отчета снимите фильтры и сравните три контроля: 10000 за сентябрь, 12000 за октябрь, 22000 за весь период. Затем проверьте отдельную ячейку: Тула за сентябрь — 1200 + 1800 + 1000 = 4000. Общий итог может совпасть случайно, а такая сверка обнаружит ошибку в разрезах.

Что поручить агенту, а что открыть в Excel самому

Агенту можно поставить задачу на подготовку агрегированного XLSX. Например:

По приложенной выгрузке сложи Расходы_руб по филиалам и календарным месяцам. Сохрани исходные строки отдельным листом. Добавь таблицу по перевозчику «Альфа» и контрольные суммы. Верни XLSX и укажи, какие строки исправлены или исключены. Если результат — обычная агрегированная таблица, явно подпиши это.

Реальный экран создания задачи Otdano. На кадре форма поручения, а не выполненный отчет по этому примеру.

Обычная таблица с итогами и нативный объект PivotTable — разные результаты. У PivotTable можно менять расположение полей и обновлять данные через инструменты Excel. Наличие строк, столбцов и общего итога в XLSX само по себе этого не доказывает. Для данного примера выполнение в Otdano не запускалось; создание нативной PivotTable здесь не подтверждено.

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