Как сравнить два списка в Excel

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

Сравнение двух списков в Excel

Конечно, вы можете сравнить два списка вручную. Но это займет много времени. В Excel имеется собственный интеллектуальный инструментарий, который позволит не только быстро сравнивать данные, но и получать информацию, которую не так-то просто достать глазами. Предположим, у нас есть два столбца с координатами A и B. В них повторяются некоторые значения.

Постановка задачи

Итак, нам нужно сравнить эти столбцы. Процедура сравнения двух документов следующая:

1. Если уникальные ячейки каждого из этих списков одинаковы, а общее количество уникальных ячеек одинаково и ячейки одинаковы, то эти списки можно считать одинаковыми. Порядок расположения значений в этом списке не имеет большого значения.
2. О частичном совпадении списков можно говорить, если сами уникальные значения одинаковы, но количество повторений разное. Поэтому такие списки могут содержать разное количество элементов.
3. На то, что два списка не совпадают, указывает другой набор уникальных значений.

Все эти три условия одновременно являются условиями нашей задачи.

Решение проблемы

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

Чтобы сравнить два списка, выполните следующие действия:

1. В отдельном столбце мы создаем список уникальных значений, специфичных для обоих списков. Для этого используем формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Саму формулу необходимо записать в виде формулы массива.
2. Давайте определим, сколько раз каждое уникальное значение встречается в массиве данных. Вот формулы для этого: =СЧЁТЕСЛИ(Список1,D5) и =СЧЁТЕСЛИ(Список2,D5).
3. Если и количество повторов, и количество уникальных значений одинаково во всех списках, входящих в эти диапазоны, то функция возвращает значение 0. Это указывает на то, что совпадение составляет XNUMX%. В этом случае заголовки этих списков приобретут зеленый фон.
4. Если весь уникальный контент есть в обоих списках, то возвращается по формулам =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) значение будет равно нулю. Если Е1 не содержит нуля, но такое значение содержится в ячейках Е2 и F2, то в этом случае диапазоны будут распознаны как совпадающие, но только частично. При этом заголовки соответствующих списков станут оранжевыми.
5. И если одна из описанных выше формул вернет ненулевое значение, списки будут совершенно несовпадающими.

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

Пример тестирования

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

Для сравнения данных мы используем диапазон A5:B19, в который поочередно вставляем эти пары списков. О том, каким будет результат сравнения, мы поймем по цвету исходных списков. Если они совсем разные, то это будет красный фон. Если часть данных совпадает, то желтый. В случае полной идентичности соответствующие заголовки будут зеленого цвета. Как сделать цвет в зависимости от того, какой будет результат? Для этого требуется условное форматирование.

Нахождение различий в двух списках двумя способами

Опишем еще два метода поиска различий в зависимости от того, синхронны списки или нет.

Вариант 1. Синхронные списки

Это простой вариант. Предположим, у нас есть такие списки.

Чтобы определить, сколько раз значения не сошлись, можно воспользоваться формулой: =СУММПРОИЗВ(—(A2:A20<>B2:B20)). Если в результате мы получили 0, это означает, что два списка одинаковы.

Вариант 2: Перетасованные списки

Если списки не идентичны в порядке содержащихся в них объектов, необходимо применить такую ​​функцию, как условное форматирование и раскрасить повторяющиеся значения. Или используйте функцию COUNTIF, с помощью которого мы определяем, сколько раз элемент из одного списка встречается во втором.

Как сравнить 2 столбца по строкам

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

Пример. Как сравнить 2 столбца на предмет совпадений и различий в одной строке

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

Нам следует записать такую ​​формулу, чтобы понять, одинаковые значения в обоих столбцах или нет: =ЕСЛИ(A2=B2, «Соответствие», «»). Логика этой функции очень проста: она сравнивает значения в ячейках А2 и В2 и, если они совпадают, выводит значение «Совпадает». Если данные разные, он не возвращает никакого значения. Вы также можете проверить ячейки, чтобы увидеть, есть ли между ними совпадения. В этом случае используется формула: =ЕСЛИ(A2<>B2, «Не совпадает», «»). Принцип тот же, сначала проводится проверка. Если окажется, что ячейки соответствуют критерию, то выводится значение «Не соответствует».

Также в поле формулы можно использовать следующую формулу для отображения как «Совпадать», если значения одинаковы, так и «Не совпадать», если они разные: =ЕСЛИ(A2=B2; «Совпадать», «Не соответствовать»). Вместо оператора равенства можно также использовать оператор неравенства. Только порядок значений, которые будут отображаться в этом случае, будет немного другим: =ЕСЛИ(A2<>B2, «Не совпадают», «Совпадают»). После использования первой версии формулы результат будет следующим.

Этот вариант формулы нечувствителен к регистру. Поэтому, если значения в одном столбце отличаются от других только тем, что они написаны заглавными буквами, то программа не заметит этой разницы. Чтобы сделать сравнение чувствительным к регистру, вам нужно использовать функцию в критериях EXACT. Остальные аргументы оставлены без изменений: =ЕСЛИ(ТОЧНО(A2,B2), «Соответствие», «Уникальный»).

Как сравнить несколько столбцов на наличие совпадений в одной строке

Анализировать значения в списках можно по целому набору критериев:

1. Найдите те строки, которые везде имеют одинаковые значения.
2. Найдите те строки, где есть совпадения всего в двух списках.

Давайте рассмотрим несколько примеров того, как действовать в каждом из этих случаев.

Пример. Как найти совпадения в одной строке в нескольких столбцах таблицы

Предположим, у нас есть ряд столбцов, содержащих необходимую нам информацию. Перед нами стоит задача определить те строки, в которых значения совпадают. Для этого необходимо использовать следующую формулу: =ЕСЛИ(И(A2=B2,A2=C2), «совпадение», « «).

Если в таблице слишком много столбцов, то вам просто нужно использовать ее вместе с функцией IF оператор COUNTIF: =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2,$A2)=3;»совпадение»;» «). Число, используемое в этой формуле, указывает количество столбцов, которые необходимо проверить. Если отличается, то нужно написать столько, сколько актуально для вашей ситуации.

Пример. Как найти совпадения в одной строке в любых двух столбцах таблицы

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

Мы используем эту формулу: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Возможна ситуация, когда в таблице много столбцов. В этом случае формула будет огромной, и на подбор всех необходимых комбинаций может уйти много времени. Чтобы решить эту задачу, вам нужно использовать функцию COUNTIF: =ЕСЛИ(СЧЕТЕСЛИ(B2:D2,A2)+СЧЕТЕСЛИ(C2:D2,B2)+(C2=D2)=0; «Уникальная строка»; «Не уникальная строка»)

Мы видим, что всего у нас есть две функции COUNTIF. Первым мы поочередно определяем, сколько столбцов имеют сходство с А2, а вторым проверяем количество сходств со значением В2. Если в результате вычислений по этой формуле мы получим нулевое значение, это говорит о том, что все строки в этом столбце уникальны, если больше, то есть сходство. Следовательно, если в результате расчета по двум формулам и сложения итоговых результатов мы получаем нулевое значение, то возвращается текстовое значение «Уникальная строка», если это число больше, то пишется, что данная строка не уникальна.

Как сравнить 2 столбца в Excel на предмет совпадений

Теперь давайте возьмем пример. Допустим, у нас есть таблица с двумя столбцами. Вам нужно проверить, совпадают ли они. Для этого нужно применить формулу, где также будет использоваться функция IF, а оператор COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, «В столбце B нет совпадений», «В столбце B есть совпадения»)

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

Как сравнить 2 столбца в Excel на предмет совпадений и выделить цветом

Чтобы облегчить визуальное определение совпадающих столбцов, вы можете выделить их цветом. Для этого вам нужно использовать функцию «Условное форматирование». Посмотрим на практике.

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

Чтобы определить совпадения и выделить их, необходимо сначала выбрать диапазон данных, в котором будет осуществляться проверка, а затем открыть пункт «Условное форматирование» на вкладке «Главная». Там выберите «Повторяющиеся значения» в качестве правила выбора ячеек.

После этого появится новое диалоговое окно, в котором в левом всплывающем списке находим опцию «Повторяющийся», а в правом списке выбираем цвет, который будет использоваться для выделения. После того, как мы нажмем кнопку «ОК», фон всех ячеек, имеющих сходство, будет выбран. Затем просто сравните столбцы на глаз.

Поиск и выделение совпадающих строк

Техника проверки совпадения строк немного отличается. Сначала нам нужно создать дополнительный столбец, и там мы будем использовать объединенные значения с помощью оператора &. Для этого необходимо написать формулу вида: =A2&B2&C2&D2.

Выбираем столбец, который был создан и содержит объединенные значения. Далее выполняем ту же последовательность действий, что описана выше для столбцов. Повторяющиеся строки будут выделены указанным вами цветом.

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