Сводная таблица из выгрузки 1С или CRM в Excel за 5 минут
Бухгалтерия или менеджеры присылают выгрузку из 1С или CRM: две тысячи строк, двадцать колонок. Руководителю нужен не этот массив, а ответ на простой вопрос: сколько по менеджерам, по клиентам, по месяцам. Раньше такой отчёт собирали часами формулами и ручной сортировкой. Сводная таблица делает то же за несколько минут, но только если выгрузка подготовлена. Разберём процесс по шагам: от «сырого» файла до готового отчёта.
Что такое сводная таблица и когда она нужна
Сводная таблица это инструмент Excel, который сам группирует строки выгрузки и считает итоги: суммы, количество, среднее. Вы не пишете формулы, а перетаскиваете названия колонок в нужные области. Она подходит, когда в данных есть повторяющиеся значения (клиент, менеджер, статус, месяц) и числа, которые нужно просуммировать (сумма, количество, часы).
Типичные задачи, где она экономит время:
- продажи по менеджерам и по месяцам;
- расходы по статьям и подразделениям;
- количество обращений по статусам;
- задолженность по контрагентам.
Если же вам нужен один итог по всей колонке, достаточно обычной суммы.
Шаг 1. Подготовьте выгрузку
Это самый важный этап. Сводная таблица ничего не исправит в данных, она их только агрегирует. Прежде чем строить отчёт, проверьте:
- Шаг 1. Заголовки в одной строке, без пустых и без объединённых ячеек. Над таблицей могут быть строки с названием отчёта: удалите их.
- Шаг 2. Нет пустых столбцов и пустых строк внутри данных.
- Шаг 3. Нет строк «Итого» и подытогов: иначе сводная посчитает их ещё раз и итог удвоится.
- Шаг 4. Суммы это числа, а не текст. Если число прижато к левому краю, а в ячейке видны пробелы-разделители, часто это текст. Преобразуйте: «Данные», «Текст по столбцам», «Готово».
- Шаг 5. Даты это даты. Проверьте, что поле можно сортировать от старых к новым.
Выгрузки 1С в виде отчёта часто приходят с иерархией (группировки, отступы, свёрнутые строки). Если есть возможность, выгружайте «плоский» список по строкам, без группировки, это экономит много времени.
Шаг 2. Создайте сводную таблицу
- Шаг 1. Поставьте курсор в любую ячейку данных.
- Шаг 2. Откройте вкладку «Вставка» и нажмите «Сводная таблица».
- Шаг 3. Excel сам выберет диапазон. Проверьте, что он охватывает все строки и колонки, и подтвердите размещение на новом листе.
- Шаг 4. Справа появится панель полей. Перетащите нужные поля в области.
Шаг 3. Расставьте поля
Четыре области панели полей работают так:
- Строки: то, по чему группируем (менеджер, клиент, статья);
- Столбцы: второй разрез (месяц, год, статус);
- Значения: то, что считаем (сумма, количество);
- Фильтры: то, чем отсекаем (период, подразделение).
Пример: перетащите «Менеджер» в строки, «Сумма» в значения, «Статус сделки» в столбцы. Получится матрица: по строкам менеджеры, по столбцам статусы, на пересечении суммы. Дважды щёлкните по любому числу, и Excel откроет на новом листе строки, из которых оно состоит: это быстрый способ проверить, откуда взялась цифра.
Если нужно не сумму, а количество, нажмите на поле в области «Значения», выберите «Параметры полей значений» и укажите «Количество».
Шаг 4. Сгруппируйте даты по месяцам
Если в выгрузке полная дата (12.10.2026), по ней отчёт будет разбит на сотни строк. Чтобы получить итоги по месяцам:
- Шаг 1. Перетащите поле «Дата» в строки.
- Шаг 2. Нажмите правой кнопкой мыши на любую дату в сводной таблице и выберите «Группировать».
- Шаг 3. Отметьте «Месяцы» и при необходимости «Годы».
Если пункт «Группировать» неактивен, значит, в колонке есть пустые ячейки или текст вместо дат. Вернитесь к подготовке данных.
Шаг 5. Оформите и обновляйте
Сводную таблицу легко привести в читаемый вид:
- Шаг 1. На вкладке «Конструктор» выберите макет и отключите лишние итоги.
- Шаг 2. Задайте числовой формат с разделителями тысяч: правой кнопкой по значению, «Числовой формат».
- Шаг 3. Для наглядности добавьте диаграмму: «Анализ сводной таблицы», «Сводная диаграмма».
- Шаг 4. Когда выгрузка обновилась, вставьте новые данные на место старых и нажмите «Данные», «Обновить всё».
Важно: если новая выгрузка длиннее прежней, а диапазон был задан жёстко, часть строк не попадёт в отчёт. Преобразуйте исходные данные в «умную» таблицу (Ctrl+T): она расширяется автоматически.
Пример: что получится за пять минут
Допустим, у вас выгрузка заказов за год: дата, клиент, менеджер, сумма. Вы ставите курсор в данные, вставляете сводную таблицу, переносите менеджера в строки, сумму в значения, дату в столбцы и группируете её по месяцам. Через минуту перед вами матрица «менеджер по месяцам». Ещё минута, и добавлена диаграмма, и вторая сводная таблица по клиентам с сортировкой по убыванию. Основное время уходит не на построение, а на подготовку выгрузки, поэтому, если файл «чистый», задача действительно решается за несколько минут.
Частые ошибки
Готовый вариант этой работы описан на странице отчёта из выгрузки, а пока рассмотрим, где обычно ошибаются при самостоятельной сборке.
- Объединённые ячейки в шапке выгрузки: сводная не создаётся или строится криво.
- Итоги внутри данных, отчёт показывает двойные суммы.
- Числа как текст: вместо суммы Excel считает «количество».
- Новые строки не попали в диапазон, потому что не обновили источник.
- Опечатки в справочниках: «ООО Ромашка» и «Ромашка ООО» это два клиента для сводной. Нормализуйте названия до построения отчёта.
- Правят цифры прямо в сводной. Менять нужно исходные данные, иначе при обновлении правки пропадут.
Не собирать каждый раз заново
Если отчёт нужен регулярно, имеет смысл один раз сделать шаблон, куда выгрузка просто вставляется. Такой файл готов: таблица «отчёт из выгрузки». Вы вставляете выгрузку и получаете сводки, топы и диаграммы без ручной настройки полей. Для разовой работы хватит и самостоятельно построенной сводной таблицы по шагам выше. Другие шаблоны для сводов и отчётов смотрите в разделе «Свод».