Как быстро найти расхождения между двумя таблицами в Excel

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

Ниже приведены подробные инструкции для всех актуальных версий Excel (2016–365).

Сравнение построчное с помощью формул

Этот метод идеален, когда нужно быстро проверить совпадение значений в двух соседних столбцах или листах без установки дополнительного ПО.

1. Откройте оба файла (или расположите таблицы на одном листе).
2. В свободном столбце рядом с данными введите формулу: =ЕСЛИ(A2=B2; "Совпадает"; "Различается") Где A2 — ячейка из первого набора, B2 — из второго.
3. Протяните формулу вниз до конца таблицы.
4. Для подсчета общего количества ошибок используйте: =СЧЁТЕСЛИ(C:C; "Различается")

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

Визуальный анализ через условное форматирование

Когда нужно быстро «глазами» оценить ситуацию в одной таблице, где данные продублированы в соседние столбцы для сверки:

1. Выделите диапазон первой таблицы (например, A2:D100).
2. Перейдите: Главная → Условное форматирование → Создать правило.
3. Выберите тип: «Использовать формулу для определения форматируемых ячеек».
4. Введите формулу: =A2<>$E2 (где A2 — первая ячейка диапазона, а $E2 — соответствующая ячейка во второй таблице для сравнения). Знак доллара фиксирует столбец сравнения.
5. Нажмите Формат, выберите яркую заливку и подтвердите.

Все ячейки, отличающиеся от эталона, подсветятся цветом. Метод работает с числами, датами и текстом.

Профессиональное сравнение больших данных в Power Query

Для файлов объемом от 10 000 строк или регулярных отчетов формулы замедлят работу Excel. Используйте встроенный инструмент Power Query (Данные → Получить данные). Он не требует знания программирования и обрабатывает миллионы строк.

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

1. Перейдите на вкладку Данные → Получить данные → Из файла → Из книги. Загрузите первый файл. Повторите для второго.
2. В редакторе Power Query выберите Главная → Объединить запросы → Объединить.
3. В окне настройки выберите ключевой столбец (уникальный идентификатор, например, Артикул или ID), по которому будут сопоставляться строки.
4. Тип соединения: «Полное внешнее» (чтобы увидеть строки, отсутствующие в одном из файлов) или «Внутреннее» (только общие строки).
5. После объединения разверните новый столбец и добавьте вычисляемый столбец с логикой: if [Цена_Файл1] <> [Цена_Файл2] then "Изменение цены" else "Без изменений"
6. Нажмите Закрыть и загрузить. Результат появится на новом листе.

Использование надстроек Inquire и специализированных инструментов

В корпоративных версиях Excel (Professional Plus, Microsoft 365) есть мощное скрытое дополнение Inquire.

Как активировать: Файл → Параметры → Надстройки → Внизу в управлении выберите «Надстройки COM» → Перейти → Поставьте галочку Inquire.

После активации появится новая вкладка. Функция Compare Files позволяет сопоставить две открытые книги целиком. Система выдаст детальный отчет в отдельном окне, показывая различия не только в значениях, но и в формулах, форматах ячеек, именах диапазонов и даже свойствах файлов.

Автоматизация через макросы VBA

Если стандартные инструменты не покрывают задачу (например, нужно сравнивать файлы с разной структурой столбцов), можно использовать макрос. Нажмите Alt + F11, вставьте модуль и используйте базовый шаблон:

Sub CompareTwoRanges()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Long, lastRow As Long
    
    Set ws1 = Sheets("Лист1") 'Имя первого листа
    Set ws2 = Sheets("Лист2") 'Имя второго листа
    lastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        If ws1.Cells(i, 1).Value <> ws2.Cells(i, 1).Value Then
            ws1.Cells(i, 3).Value = "Различие!"
            ws1.Cells(i, 3).Interior.Color = vbRed
        End If
    Next i
    MsgBox "Сравнение завершено"
End Sub

Примечание: Перед запуском убедитесь, что имена листов в коде совпадают с вашими.

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

Даже при одинаковых на вид данных результаты могут отличаться по техническим причинам:

• Разные форматы дат. Одна дата может храниться как число (44567), другая как текст ("01.01.2022"). Приведите всё к единому формату через функцию ТЕКСТ() или преобразование столбца.
• Невидимые пробелы. Часто возникают при копировании из 1С или веб-сайтов. Используйте функцию СЖПРОБЕЛЫ() (TRIM) перед сравнением.
• Регистр букв. Стандартное сравнение в Excel нечувствительно к регистру ("текст" = "Текст"). Если важен точный регистр, используйте функцию СОВПАД() (EXACT).
• Типы данных. Число 100 и текст "100" визуально идентичны, но формула = вернет ложь. Проверьте выравнивание (числа обычно справа, текст слева) или используйте функцию ТИП().

FAQ

Можно ли сравнить два разных файла, не открывая их одновременно? Стандартными формулами — нет, оба файла должны быть открыты. Однако Power Query и надстройка Inquire позволяют загружать данные из закрытых файлов или работать с ними в фоновом режиме после первоначальной настройки подключения.

Как сравнить таблицы, если строки перемешаны (нет одинакового порядка)? Формулы и простое условное форматирование не подойдут, так как они сравнивают ячейки по позициям (A1 с A1). В этом случае обязательно используйте Power Query, выполнив объединение по уникальному ключу (ID, артикул, фамилия), либо примените функцию ВПР (VLOOKUP) или ПОИСКПОЗ для поиска значений во второй таблице независимо от их расположения.

Почему формула показывает «Различается», хотя числа одинаковые? Скорее всего, одно из чисел имеет скрытые десятичные знаки (например, 10,0001 против 10), которые не отображаются из-за формата ячейки. Увеличьте количество знаков после запятой для проверки или округлите значения функцией ОКРУГЛ() перед сравнением.