Очистка данных в Excel для выгрузок из 1С, банка, CRM и интернет-магазина

Выгрузка из интернет-магазина выглядит аккуратно, но сумма цен даёт ноль, а «Миксер Philips» не находится поиском. Справка Microsoft советует до любых правок создать резервную копию исходных данных в отдельной книге и привести их к табличному виду^[1]. Без копии ошибку в формуле очистки уже не с чем сверить.

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

Ключевые факты: очистка данных в Excel

• Очистка данных — это набор отдельных приёмов: поиск и замена, регистр, пробелы, непечатаемые знаки, числа-текст и разбиение столбцов^[1].
• ПЕЧСИМВ удаляет первые 32 непечатаемых знака с кодами 0–31^[2].
• Справка Microsoft советует перед очисткой создать резервную копию исходных данных в отдельной книге^[1].
• СЖПРОБЕЛЫ не удаляет неразрывный пробел с кодом 160 — для него нужна ПОДСТАВИТЬ^[3].
• Кнопка «Преобразовать в число» отличается от ЗНАЧЕН тем, что меняет сами ячейки, а формула выводит число в новую ячейку^[4].

Как начать очистку данных в Excel: копия, порядок шагов и признаки

Очистка данных начинается с резервной копии исходной выгрузки в отдельной книге. По справке Microsoft главные приёмы — поиск и замена, регистр, пробелы и непечатаемые знаки, числа и даты, сохранённые как текст, и разбиение столбцов^[1]. Порядок важен: сначала структура, потом текст, в конце числа.

Справка советует удалять повторяющиеся строки только после проверки фильтром уникальных значений^[1]. Разберите выгрузку магазина в тренажёре: урок раскладывает её по столбцам, превращает цены в числа и чистит названия.

Разобравшись с порядком, переходим к самым коварным ошибкам — символам, которых не видно.

Чем ПЕЧСИМВ отличается от СЖПРОБЕЛЫ и ПОДСТАВИТЬ: невидимые символы

Очистка невидимых символов требует трёх функций: ПЕЧСИМВ, ПОДСТАВИТЬ и СЖПРОБЕЛЫ. Очистка данных в Excel от невидимого мусора идёт по кодам символов: ПЕЧСИМВ удаляет только коды 0–31^[2], а СЖПРОБЕЛЫ — только обычный пробел с кодом 32^[3].

Формула =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПЕЧСИМВ(B2);СИМВОЛ(160);" ")) сначала удаляет управляющий символ, затем меняет неразрывный пробел на обычный, а в конце сжимает пробелы. На примере длина «Миксер Philips» падает с 15 до 14, а «Тостер Redmond» — с 16 до 14^[5]. ПОДСТАВИТЬ, по справке Microsoft, меняет символы Юникода с высокими кодами, включая 160, на знаки ASCII^[1].

Однако чистый текст ещё не делает число числом — у цен своя очистка.

Как превратить числа-текст в числа: ЗНАЧЕН, кнопка ошибки и умножение

Очистка чисел-текста заканчивается преобразованием: кнопкой ошибки, ЗНАЧЕН или умножением на 1. По справке Microsoft числа-текст выравниваются по левому краю и часто помечены зелёным индикатором; кнопка ошибки предлагает «Преобразовать в число»^[4].

1. Выделите столбец цен с зелёными треугольниками.
2. Нажмите кнопку ошибки рядом с выделением и выберите «Преобразовать в число».
3. Если кнопки нет, введите 1 в пустую ячейку, скопируйте её, выделите цены и выполните специальную вставку с умножением^[4].
4. Проверьте сумму столбца: она должна перестать быть нулевой.

Очистка данных в Excel для цен заканчивается формулой =ЗНАЧЕН(D2): она превращает текст «1490» в число; в примере =СУММ(D2:D3) для цен с апострофом даёт 0, а =ЗНАЧЕН(D2)+ЗНАЧЕН(D3) — 3840^[5]. По справке Microsoft ЗНАЧЕН понимает любой формат числа, даты или времени: =ЗНАЧЕН("1 000 ₽") возвращает 1000, а неподходящий текст — #ЗНАЧ!^[6]. Перед сменой формата справка советует удалить пробелы и непечатаемые знаки функциями СЖПРОБЕЛЫ и ПЕЧСИМВ^[4].

Остаётся понять, когда ручная очистка формулами — не лучший путь.

Когда очистка формулами не подойдёт

Очистка формулами не подойдёт, если выгрузка приходит регулярно или символы не входят в коды 0–31. Каждое условие проверяется до того, как писать формулы на весь столбец.

• Если выгрузка приходит каждую неделю, ручная очистка не подойдёт: справка Microsoft предлагает записать макрос или создать код^[1].
• При знаках Юникода с кодами 127, 129, 141, 143, 144 или 157 ПЕЧСИМВ не поможет: она чистит только коды 0–31^[2].
• Когда у вас Excel для Microsoft 365 или Excel 2021 с Copilot, в английском интерфейсе есть пункт Data → Clean Data; он находит несогласованный текст, смешанные форматы и лишние пробелы, но выпускается постепенно и лучше работает с таблицами до 100 столбцов и 50 000 строк^[7].
• При данных в Google Таблицах текст обрезается при вводе, но с неразрывными пробелами функция TRIM не работает^[8].

Русское название пункта Clean Data справка на русском языке не подтверждает, поэтому ищите его по расположению на вкладке «Данные»; пункт есть только у клиентов Copilot^[7]. Если очистка пошла не так, Ctrl+Z отменит последнее действие^[9]. ПЕЧСИМВ, СЖПРОБЕЛЫ и ЗНАЧЕН справка относит к Excel 2016, 2019, 2021, 2024 и Microsoft 365, а версии 2021, 2024 и Microsoft 365 — и для Mac^[2].

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