Очистка данных: дубликаты и пустые значения

Вы выгрузили из 1С в Excel продажи за полгода: 18 420 строк. Общая сумма 2 500 млн сумов, а в стандартном отчёте 1С 2 462,5 млн. Откуда разница в 37,5 млн сумов? Данные выгрузили дважды: диапазоны дат пересеклись, и один день записан два раза. Если анализировать такие данные «нечищеными», ABC, оборачиваемость и прогноз будут построены на этой ошибке. Чистка скучна, но время и деньги, которые она экономит, стоят больше.

Порядок

1. Сохраните исходный файл, работу ведите в копии.
2. Уберите лишние пробелы (TRIM) и приведите к одному регистру: «Кабель » и «Кабель» для системы два разных значения.
3. Найдите повторяющиеся строки.
4. Разберите пустые поля.
5. Отметьте резко выбивающиеся значения, например количество 10 000 вместо 10.
6. Сверьте итог с отчётом 1С: это контрольная сумма.

Дубликаты: что можно удалять

Дубликаты обычно приходят из трёх мест: выгрузки с пересекающимися датами, склейка нескольких файлов или филиалов в одну таблицу, повторный ввод документа. Определите ключ строки: номер документа и номер строки. 312 строк совпадают во всех полях: документ, строка, дата, товар, количество. Если их удалить, останется 18 420 − 312 = 18 108 строк, а сумма составит 2 500 − 37,5 = 2 462,5 млн сумов: она совпадает с отчётом 1С. Это и есть признак того, что чистка прошла верно.

Будьте осторожны: две одинаковые строки по одному товару за одну минуту в двух разных документах не дубликат, а две продажи. Если удалять только по товару, количеству и времени, вы потеряете реальные продажи. Сначала отметьте формулой COUNTIFS (дубликат = 2 и больше), потом удаляйте.

Пустое значение не ноль

• Категория пуста: 540 строк (2,9 процента). Подтяните по коду товара из справочника (VLOOKUP). Для не найденных 42 запишите «Неизвестно» и покажите их число в отчёте.
• Себестоимость пуста: 96 строк. Не подставляйте ноль! Если выручка 400 000 сумов, а себестоимость равна нулю, прибыль выглядит как 100 процентов, тогда как при себестоимости 75 процентов она 25. По 96 строкам выручка 96 × 400 000 = 38,4 млн сумов, настоящая прибыль 9,6 млн, а с нулевой себестоимостью 38,4 млн: 28,8 млн сумов ложной прибыли. Возьмите цену последнего прихода или пометьте строку и исключите её из расчёта маржи.
• Количество пусто: проверьте по документу, если не нашли, исключите строку из анализа и запишите в журнал.

Отрицательное количество (у нас 410 строк) не ошибка, а возврат товара. Не удаляйте его.

Число или текст?

Числа из 1С в Excel часто оказываются текстом: в «1 200,50» есть пробел, а десятичный знак не подходит под настройки Excel. Признак: ячейка выровнена по левому краю, сумма меньше ожидаемой. Проверка: ISNUMBER(A2). Исправление: убрать пробел через SUBSTITUTE и применить VALUE. То же с датами: если «12.03.2026» текст, сгруппировать по месяцам нельзя.

Журнал очистки

На каждое правило запишите одну строку: что сделано, на сколько строк повлияло, как повлияло на сумму.

• Дубликаты (документ + строка): 312 строк, −37,5 млн сумов.
• Категория заполнена: 498 строк, «Неизвестно» осталось 42.
• Себестоимость взята из цены последнего прихода: 96 строк.
• Числа в виде текста исправлены: 1 240 ячеек.
• Возвраты (отрицательное количество): 410 строк, не менялись.

С журналом через полгода можно применить те же правила и сравнить результаты. Чтобы повторять работу не вручную, а по одним правилам, используйте Power Query в Excel или формулы Google Sheets.

Вывод

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