Полный гид по сравнению данных в Excel

Чтобы быстро найти различия в Excel, используйте условное форматирование для визуального поиска дубликатов, формулу =ЕСЛИ(A1<>B1; "Разница"; "ОК") для построчного сравнения столбцов или инструмент Power Query для сопоставления целых файлов и больших массивов данных. Выбор метода зависит от объема информации: до 1000 строк справятся формулы, для тысяч строк и разных файлов незаменим Power Query.

Поиск дубликатов и уникальных значений в одном столбце

Самый быстрый способ найти повторы — встроенное условное форматирование. Это не требует знания формул и работает мгновенно.

1. Выделите диапазон данных (например, A2:A100).
2. Перейдите на вкладку Главная → Условное форматирование → Правила выделения ячеек → Повторяющиеся значения.
3. В диалоговом окне выберите цвет подсветки и нажмите ОК. Все дубликаты окрасятся.

Для программного подсчета количества уникальных записей в диапазоне A2:A100 используйте формулу: =СУММ(1/СЧЁТЕСЛИ(A2:A100; A2:A100)) (Вводите как формулу массива, если у вас старая версия Excel, или просто нажмите Enter в Excel 365).

Сравнение двух столбцов построчно

Когда нужно сопоставить данные в столбцах А и В (например, план и факт, или два списка артикулов), эффективнее всего использовать логические формулы.

Базовое сравнение

В ячейку C1 введите формулу: =ЕСЛИ(A1=B1; "Совпадает"; "Различие") Протяните формулу вниз до конца таблицы. Ячейки со словом "Различие" укажут на несовпадения.

Игнорирование регистра и пробелов

Иногда данные выглядят одинаково, но содержат лишние пробелы или разный регистр букв ("Товар" и "товар "). Для точного сравнения очистите данные прямо в формуле: =ЕСЛИ(СЖПРОБЕЛЫ(СТРОЧН(A1))=СЖПРОБЕЛЫ(СТРОЧН(B1)); "ОК"; "Ошибка") Функции СЖПРОБЕЛЫ и СТРОЧН приведут текст к единому виду перед сравнением.

Визуальное выделение различий

Если формулы создавать не хочется, можно подсветить разные ячейки цветом:

1. Выделите оба столбца (A и B).
2. Главная → Условное форматирование → Создать правило.
3. Выберите Использовать формулу... и введите: =$A1<>$B1.
4. Задайте формат заливки (например, красный) и нажмите ОК.

Сопоставление данных на разных листах

Часто требуется проверить, есть ли значение из текущего листа на другом листе книги (например, сверка остатков). Здесь простая проверка A1=B1 не подойдет, так как данные могут быть в разном порядке. Используйте функцию ВПР (или XПРОСМОТР в новых версиях).

Формула для проверки наличия значения из ячейки A1 (Лист1) в столбце A на Лист2: =ЕСЛИОШИБКА(ВПР(A1; Лист2!$A:$A; 1; 0); "Не найдено")

• Если значение найдено, формула вернет его.
• Если нет — появится текст "Не найдено".

Для сравнения числовых значений с допустимой погрешностью (например, копеечные расхождения из-за округления) используйте модуль разницы: =ЕСЛИ(ABS(A1-B1)<0.01; "Равно"; "Разница")

Сравнение целых файлов и больших массивов данных

Когда объем данных превышает 10–20 тысяч строк или нужно сравнить два разных .xlsx файла, формулы начинают тормозить файл. Лучшее решение — надстройка Power Query (встроена в Excel 2016 и новее, находится во вкладке Данные).

Алгоритм действий в Power Query:

1. Откройте новый файл Excel. Перейдите на вкладку Данные → Получить данные → Из файла → Из книги.
2. Выберите первый файл для сравнения. В навигаторе отметьте нужный лист и нажмите Преобразовать данные.
3. Повторите шаг 2 для второго файла (загрузите его как второй запрос).
4. В редакторе Power Query выберите Главная → Объединить запросы → Объединить.
5. В открывшемся окне выделите ключевые столбцы в обеих таблицах (по которым идет сравнение, например, "Артикул").
6. Тип соединения выберите Полное внешнее (Full Outer), чтобы увидеть все строки из обоих файлов, включая те, что есть только в одном из них.
7. После объединения разверните полученные столбцы и добавьте Условный столбец:
   • Логика: Если [Столбец_Файл1] равно [Столбец_Файл2], то "Совпадает", иначе "Различие".
8. Нажмите Закрыть и загрузить. Результат появится на новом листе в виде умной таблицы.

Частые ошибки при сравнении

• Лишние пробелы. Часто после выгрузки из 1С или CRM в конце ячеек стоят невидимые пробелы. Из-за этого "Иванов " и "Иванов" считаются разными значениями. Всегда используйте СЖПРОБЕЛЫ() для очистки.
• Числа как текст. Иногда числа хранятся в формате текста (зеленый треугольник в углу ячейки). При сравнении 100 (число) и "100" (текст) Excel покажет ошибку. Преобразуйте текст в числа через Данные → Текст по столбцам → Готово.
• Относительные ссылки. При протягивании формул убедитесь, что диапазоны закреплены знаком доллара ($), если это необходимо (например, $A$1:$A$100), иначе область поиска сместится.

FAQ

Как сравнить два файла, если в них разный порядок строк? Используйте функцию ВПР (VLOOKUP) или XПРОСМОТР (XLOOKUP), ища значение из первого файла во втором по уникальному ключу (артикул, ИНН, ID). Простое построчное сравнение здесь не сработает.

Можно ли выделить различия цветом без формул? Да, с помощью условного форматирования с формулой =$A1<>$B1. Также в старых версиях был макрос, но условное форматирование надежнее и не требует включения макросов.

Почему ВПР возвращает ошибку #Н/Д? Это означает, что искомое значение не найдено в таблице. Проверьте наличие лишних пробелов, соответствие типов данных (текст/число) и убедитесь, что указан режим точного поиска (аргумент ЛОЖЬ или 0).