Поиск отличий в 2 текстовых строках

15519 19.12.2022 Скачать пример

Если вы когда-нибудь использовали в своих вычислениях в Excel логические функции (типа ЕСЛИ) или функции подстановки (типа ВПР), то, вполне возможно, уже сталкивались с ситуацией, когда две визуально неотличимых ячейки Microsoft Excel почему-то считает неодинаковыми, что автоматически приводит к неработоспособности функций, ломает фильтрацию, сводные таблицы и т.д. Причин для такого может быть много:

лишние пробелы
использование английских букв похожих на русские и наоборот (русская "эс" и английская "си", например)
использование цифр вместо букв (ноль вместо заглавной "О")
разные версии тире (короткое, длинное, среднее)
неразрывный пробел вместо обычного
невидимые символы переноса строки (Alt+Enter)
... и т.д.

Вопрос в том, где именно эти отличия находятся и как их быстро найти в исходных данных?

Самое простое - это сделать за пару минут прямо в Excel своими руками универсальный сравниватель, который будет наглядно показывать в чем именно не совпадают две любые заданные текстовые строки:

Сравниватель 2 текстовых строк

В жёлтые ячейки нужно будет скопировать и вставить исходные тексты.

Затем каждую строку мы разбираем на отдельные символы с помощью функции ПСТР (MID) в ячейке D4:

Разбираем текст на символы

Если у вас последние версии Excel 2021 или Office 365, то для подобного также можно использовать новую функцию ПОСЛЕД (SEQUENCE):

Используем функцию ПОСЛЕД для разделения текста на символы

Дополнительно имеет смысл вывести над непустыми зелёными ячейками коды каждого символа, чтобы чётко видеть причину отличий, если они будут. Это легко сделать с помощью функции КОДСИМВ (CHAR):

Коды каждого символа

Ну и для пущей наглядности можно добавить ещё и правило условного форматирования, чтобы подсветить красным отличия в конкретных символах. Для этого выделяем наши зелёные ячейки с символами, на которые мы разобрали каждый текст и выбираем Главная - Условное форматирование - Создать правило (Home - Conditional Formatting - Create Rule). В открывшемся окне выбираем последний тип правила Использовать формулу для определения форматируемых ячеек (Use a formula to detect cells to format) и вводим в нижнее поле формулу для проверки результатов посимвольного сравнения:

Подсветка отличий условным форматированием

Обратите особое внимание на знаки доллара, используемые здесь для закрепления ссылок на сравниваемые ячейки - мы фиксируем строки (3 и 6), а не столбцы (D).

Общее количество отличий, т.е. несовпадений можно легко вычислить с помощью вот такой формулы:

Количество несовпадений

Здесь мы поячеечно (т.е. посимвольно) сравниваем наши зеленые диапазоны, получая на выходе логические значения ИСТИНА и ЛОЖЬ (TRUE и FALSE). Затем используется двойное бинарное отрицание - 2 знака минус, выполняющие функцию безобидного двойного умножения на минус 1, что заставляет Excel конвертировать логические значения в 1 и 0 соответственно. Потом останется лишь сложить все получившиеся нули и единицы, но мы, в данном случае, делаем это не с помощью стандартной функции СУММ (SUM), а используем её "старшего брата" - функцию СУММПРОИЗВ (SUMPRODUCT), которая позволяет вводить формулы массива (а это как раз она!) без сочетания клавиш Ctrl+Shift+Enter.

И пару общих рекомендаций вдогон:

Используйте шрифты, созданные для программирования (Consolas, FiraCode, Monaco...) - их дизайн разработан так, чтобы похожие в других шрифтах символы (например цифра 1 и строчная латинская "L" или ноль и заглавная "О") выглядели по-разному.
Для быстрого поиска латиницы в русском тексте можно просто задать для ячеек шрифт, в котором кириллица и латиница выглядят по-разному (например, Bauhaus93, Copper и т.п.)

В любом случае, можете просто скачать файл по ссылке в начале этой статьи и просто использовать этот готовый сравниватель для поиска "блох" в ваших данных. С ним это будет сделать гораздо легче.