Функция сравнения двух таблиц в Excel на совпадения формулы

Зачастую людям, работающим в Эксель, нужно сравнить таблицы или списки между собой. Есть большое количество способов и обходных путей для этого. Но в основном они требуют слишком много времени и не всегда действенны. В этой статье будут рассмотрены самые удобные методы и формулы сравнения таблиц.

Способы сравнения

Группы сравнения:

— списки на одном листе;
— таблицы на нескольких листах;
— табличные диапазоны в разных файлах.

Вариант 1: формула

Использовать будем формулу равенства. Если данные не совпали — ЛОЖЬ, совпали — ИСТИННА.

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



Записываем знак "=" в дополнительной ячейке на рабочей области. Нажимаем на первую ячейку для сравнения, ставим снова «=» и нажимаем по второй напротив.



Нажимаем Enter и в ячейке появится результат.



Копируем формулу для других ячеек с помощью маркера заполнения. Наводим курсор на ячейку с формулой, чтобы появился крест и, зажимая, тянем вниз.



После копирования формулы, отображение результата будет автоматически.




Для выявления количества "ЛЖИ" выделяем любую другую ячейку и нажимаем на "Вставить функцию".



Появится окно, в котором отмечаем категорию "Математические" и функцию "СУММПРОИЗВ".



Появится окно аргументов.

Синтаксис функции:

=СУММПРОИЗВ(массив1;массив2;...)

Выделяем первую область сравнения и пишем знак "<>", что означает неравенство. Далее выделяем вторую область. Ставим скобки по обе стороны формулы и перед ней ставим два символа "-".



После записи формулы результат появится автоматически.



Если делать сравнение на разных листах, то формула будет такой:

=B2=Лист2!B2



Вариант 2: группы ячеек

Инструмент выделения групп ячеек работает только при условии, что таблицы на одном листе.

Порядок действий:

1. выделение массивов;
2. вкладка "Главная";
3. "Найти и выделить";
4. в перечне выбрать "Выделение группы ячеек...".



В появившемся окне нажимаем на "Выделить...".



Отмечаем пункт "Отличия по строкам".



Результат будет выведен автоматически и "ЛОЖЬ" выделится другим оттенком.



Вариант 3: форматирование

1 Способ форматирования

Последовательность:

1. выбрать основную область;
2. вкладка "Главная";
3. "Стили";
4. кликните на "Условное форматирование";
5. "Управление правилами".



В окне диспетчера кликните на "Создать правило".



Появится окно. Кликните на "Использовать формулу" и ниже под "Форматировать ячейки" введите формулу адресов ячеек. Перед ней поставьте знак "=".



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



Кликните на "ОК".



В "Диспетчере правил" также кликните на "ОК".



Выбранным ранее цветом, не соответствующие друг с другом ячейки будут выделены.



2 Способ форматирования

Выделить все области.



Последовательность:

1. вкладка "Главная";
2. "Стили";
3. "Условное форматирование";
4. Подпункт "Правила выделения ячеек";
5. Пункт "Повторяющиеся значения".



Сравните скриншот ниже и то, что высветилось у вас. Если все правильно, то кликните "ОК".



Одинаковые ячейки будут выделены другим цветом.



Можно изменить ракурс окрашивания на разные ячейки.



Теперь выделены разные ячейки.



Вариант 4: комплексная формула

Функция "СЧЁТЕСЛИ" является сложной. Она выполняет подсчет количества ячеек и выглядит так:

=СЧЁТЕСЛИ(диапазон;критерий)

Кликните на любую другую ячейку и на "Вставить функцию".



В "Мастере функций" выберите категорию "Статистические" и функцию "СЧЁТЕСЛИ".



Разместив курсор в поле для "Диапазона", выделите фамилии во второй таблице. Для того, чтобы сделать адрес абсолютным, нажмите на F4. Далее в поле для "Критерия" также разместите данные о первой таблице. Оставляем ссылку относительной.



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



Копируем формулу маркером заполнения.



Значение "0" показывает, что формула не нашла совпадений каких-либо фамилий.



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

Выделяем "СЧЁТЕСЛИ" в ячейке и пишем перед формулой "ЕСЛИ".



В поле для выражения дописываем к формуле "=0". Ниже в поле для истинны вписываем "СТРОКА" и координаты первой ячейки второй таблицы.



Значение "ЛОЖЬ" отобразится напротив тех ячеек, которые имеют совпадения.



Маркером заполнения копируем формулу.



Справа проставляете количество строк второй таблицы по порядку.



Справа от только что заполненной колонки выделяем ячейку и "Вставить функцию".



В "Мастере функций" категория "Статистические" и функцию "НАИМЕНЬШИЙ".



В поле "Массив" ввести координаты столбца подсчитанных значений. В поле "К" координаты ячейки последнего столбца.



Число "3" означает наименьшую по нумерации ячейку, которая не имеет совпадений. Далее копируйте формулу маркером заполнения.



Выделяем первую ячейку в последнем столбце и перед формулой вводим "ИНДЕКС", скобку и точку с запятой. Выделяете формулу и кликните на "Вставить функцию".



Сверяем данные на скриншоте и кликните "ОК".



В поле "Массив" вводим адрес области второй таблицы. Делаем все ссылки абсолютными.



Копируйте формулу маркером заполнения. Результат будет показан в виде записи фамилий, не имеющих повторений.



Вариант 5: массивы в разных книгах

Если таблицы находятся в разных файлах, то их надо открыть одновременно. Для сравнения можно использовать любые формулы, кроме тех, где требуется одно месторасположение для таблиц.


Как открыть файлы Excel в разных окнах

Сравнение таблиц в Excel на совпадения

Заключение

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

Рейтинг:(голосов:1)