Подготовка данных к отчёту в Excel: ошибки новичков

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

Что понадобится до начала

Инструкция рассчитана на настольный Excel для Microsoft 365 и файл CSV с табличными данными. Веб-версия и Excel для Mac могут показывать команды иначе; Power Query и логика проверки при этом остаются теми же. Подготовьте исходный файл и уточните у того, кто его выгрузил, что означает одна строка, какие поля уникальны и за какой период собраны данные. Без этих ответов Excel не отличит допустимое повторение от ошибки.

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

Подготовьте данные: шесть шагов

1. Определите смысл строк и будущие показатели. Например, одна строка может означать заказ, товарную позицию в заказе или платёж. Решите, какие суммы и количества нужны в отчёте и по каким полям вы будете группировать данные. Проверьте результат: можете словами объяснить, что представляет собой одна строка и какой показатель хотите посчитать.
2. Импортируйте CSV через Power Query, а не двойным щелчком. В Excel откройте «Данные» → «Из текста/CSV», выберите файл и проверьте предварительный просмотр. Убедитесь, что разделитель разбивает значения на отдельные столбцы, а кириллица читается нормально. Нажмите «Преобразовать данные», чтобы открыть редактор Power Query. Проверка: заголовки стоят над своими столбцами, а содержимое не собралось в одну колонку.
3. Назначьте типы столбцов. Полям с датами задайте тип «Дата», суммам и количеству — числовой, идентификаторам с ведущими нулями — текстовый. Например, код 00125 не должен превращаться в 125: это уже другое значение. Обратите внимание на региональные настройки: дата 03/04/2025 может означать 3 апреля или 4 марта, а запятая и точка в числе могут трактоваться по-разному. Проверка: несколько дат и сумм совпадают с исходным файлом, идентификаторы сохранили нули.
4. Проверьте пропуски, пробелы и повторы. Отфильтруйте пустые значения в полях, без которых нельзя построить отчёт: например, в дате или сумме. Удаляйте повторяющиеся строки только после проверки ключа и смысла данных. Два одинаковых товара в разных заказах — не дубль; две записи с одним уникальным ID заказа могут требовать разбирательства. Проверка: вы знаете, какие строки исключены и почему, а число записей после очистки не изменилось случайно.
5. Сверьте результат с источником. Запишите число строк и итог по важному показателю до и после преобразований. Сравните их с исходной выгрузкой или контрольным значением из системы, откуда пришёл файл. Если менялся период или удалялись записи, объясните разницу. Проверка: расхождение либо отсутствует, либо полностью объясняется документированными фильтрами и преобразованиями.
6. Загрузите очищенную таблицу и постройте сводную. В редакторе Power Query выберите «Закрыть и загрузить». Вставьте сводную таблицу на отдельный лист и добавляйте в значения только поля с подходящим типом: сумму — как сумму, число заказов — как подсчёт уникального идентификатора, если именно это требуется. Проверка: итог сводной совпадает с контрольной суммой, а фильтр по периоду не включает лишние даты.

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

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

Если не получилось

• Весь CSV попал в один столбец. Проверьте разделитель в окне импорта: запятая, точка с запятой или табуляция. Неверный выбор часто не вызывает сообщения об ошибке — просто меняет разметку. Вернитесь к предварительному просмотру и выберите разделитель, при котором столбцы разделились правильно.
• Дата не распознаётся или месяц и день поменялись местами. Не исправляйте столбец заменой символов наугад. В Power Query задайте тип данных с учётом локали источника и проверьте несколько значений, которые можно сверить с системой выгрузки. Если в одной колонке смешаны разные форматы дат, сначала выясните причину и исправьте источник либо обработайте форматы отдельно.
• Вместо результата формулы отображается #ЗНАЧ!. Проверьте, не попал ли в числовой столбец текст: пробел, знак валюты или число, записанное с другим десятичным разделителем. Преобразуйте поле в Power Query и посмотрите, какие строки не удалось привести к числу. Не заменяйте ошибочные значения нулём, пока не выяснили, что они означают.
• Появилось сообщение «Невозможно сгруппировать этот выбор» при группировке дат в сводной. Обычно в поле даты есть пустые или текстовые значения. Исправьте тип столбца и разберите проблемные строки в исходной таблице или Power Query, затем обновите сводную. Точная формулировка сообщения может отличаться между версиями и языками Excel.

Что делать дальше

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