✅ Как сравнить две колонки в Excel (на совпадения и различия)

Содержание

Сравните два столбца для точного совпадения строк
Сравнить два столбца и выделить совпадения
Сравните два столбца и найдите недостающие точки данных
Сравните два столбца и извлеките совпадающие данные

Посмотреть видео - Сравните две колонки в Excel на совпадения и различия

Я часто получаю один вопрос: «Как сравнить два столбца в Excel?».

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

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

Поскольку меня так часто спрашивают об этом, я решил написать это обширное руководство с намерением охватить большинство (если не все) возможных сценариев.

Если вы сочтете это полезным, передайте его другим пользователям Excel.

Обратите внимание, что методы сравнения столбцов, показанные в этом руководстве, не единственные.

В зависимости от вашего набора данных вам может потребоваться изменить или скорректировать метод. Однако основные принципы останутся прежними.

Если вы думаете, что есть что-то, что можно добавить к этому руководству, дайте мне знать в разделе комментариев.

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

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

Пример: сравнение ячеек в одной строке

Ниже приведен набор данных, в котором мне нужно проверить, совпадает ли имя в столбце A в столбце B или нет.

Если есть совпадение, мне нужен результат как «ИСТИНА», а если нет, то мне нужен результат как «ЛОЖЬ».

Следующая формула сделает это:

= A2 = B2

Пример: сравнение ячеек в одной строке (с использованием формулы ЕСЛИ)

Если вы хотите получить более наглядный результат, вы можете использовать простую формулу ЕСЛИ, чтобы вернуть «Соответствие», если имена совпадают, и «Несоответствие», если имена разные.

= ЕСЛИ (A2 = B2, «Совпадение», «Несоответствие»)

Примечание: если вы хотите сделать сравнение чувствительным к регистру, используйте следующую формулу ЕСЛИ:

= ЕСЛИ (EXACT (A2; B2); «Совпадение»; «Несоответствие»)

С приведенной выше формулой «IBM» и «ibm» будут считаться двумя разными именами, и приведенная выше формула вернет «Несоответствие».

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

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

Вот как это сделать:

Выберите весь набор данных.
Перейдите на вкладку «Главная».
В группе «Стили» выберите параметр «Условное форматирование».
В раскрывающемся списке нажмите «Новое правило».
В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
В поле формулы введите формулу: = $ A1 = $ B1
Нажмите кнопку «Формат» и укажите формат, который нужно применить к совпадающим ячейкам.
Щелкните ОК.

Это выделит все ячейки с одинаковыми именами в каждой строке.

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

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

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

Пример: сравнение двух столбцов и выделение совпадающих данных

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

Что-то вроде того, что показано ниже:

Обратите внимание, что список в столбце A больше, чем список в B. Также некоторые имена присутствуют в обоих списках, но не в одной строке (например, IBM, Adobe, Walmart).

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

Вот как это сделать:

Выберите весь набор данных.
Щелкните вкладку "Главная".
В группе «Стили» выберите параметр «Условное форматирование».
Наведите курсор на опцию Highlight Cell Rules.
Нажмите на повторяющиеся значения.
Убедитесь, что в диалоговом окне «Повторяющиеся значения» выбрано «Дублировать».
Укажите форматирование.
Щелкните ОК.

Вышеупомянутые шаги дадут вам результат, как показано ниже.

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

Пример: сравнение двух столбцов и выделение несовпадающих данных

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

Выберите весь набор данных.
Щелкните вкладку "Главная".
В группе «Стили» выберите параметр «Условное форматирование».
Наведите курсор на опцию Highlight Cell Rules.
Нажмите на повторяющиеся значения.
Убедитесь, что в диалоговом окне «Повторяющиеся значения» выбрано «Уникальное».
Укажите форматирование.
Щелкните ОК.

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

Сравните два столбца и найдите недостающие точки данных

Если вы хотите определить, присутствует ли точка данных из одного списка в другом списке, вам необходимо использовать формулы поиска.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите идентифицировать компании, которые представлены в столбце A, но не в столбце B,

Для этого я могу использовать следующую формулу ВПР.

= ISERROR (ВПР (A2; $ B $ 2: $ B $ 10,1,0))

Эта формула использует функцию ВПР, чтобы проверить, присутствует ли название компании из A в столбце B или нет. Если он присутствует, он вернет это имя из столбца B, иначе он вернет ошибку # N / A.

Эти имена, которые возвращают ошибку # N / A, отсутствуют в столбце B.

Функция ISERROR вернет TRUE, если результатом VLOOKUP является ошибка, и FALSE, если это не ошибка.

Если вы хотите получить список всех имен, для которых нет совпадений, вы можете отфильтровать столбец результатов, чтобы получить для всех ячеек значение ИСТИНА.

Вы также можете использовать функцию ПОИСКПОЗ, чтобы сделать то же самое;

= НЕ (ЕЧИСЛО (ПОИСКПОЗ (A2; $ B $ 2: $ B $ 10,0)))

Примечание. Лично я предпочитаю использовать функцию Match (или комбинацию ИНДЕКС / ПОИСКПОЗ) вместо ВПР. Я считаю его более гибким и мощным. Вы можете прочитать разницу между Vlookup и Index / Match здесь.

Сравните два столбца и извлеките совпадающие данные

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

Пример: извлечение совпадающих данных (точное)

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

Ниже приведена формула, которая сделает это:

= ВПР (D2; $ A $ 2: $ B $ 14,2,0)

или

= ИНДЕКС ($ A $ 2: $ B $ 14; ПОИСКПОЗ (D2; $ A $ 2: $ A $ 14,0); 2)

Пример: получение совпадающих данных (частично)

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

Для получения правильного результата этим формулам поиска необходимо точное совпадение. В функциях ВПР или ПОИСКПОЗ есть приблизительный вариант совпадения, но его здесь нельзя использовать.

Предположим, у вас есть набор данных, как показано ниже. Обратите внимание, что есть имена, которые не указаны в столбце 2 (например, JPMorgan вместо JPMorgan Chase и Exxon вместо ExxonMobil).

В таком случае можно использовать частичный поиск с использованием подстановочных знаков.

Следующая формула даст правильный результат в этом случае:

= ВПР ("*" & D2 & "*"; $ A $ 2: $ B $ 14,2,0)

или

= ИНДЕКС ($ A $ 2: $ B $ 14, ПОИСКПОЗ ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)

В приведенном выше примере звездочка (*) - это подстановочный знак, который может представлять любое количество символов. Когда значение поиска обрамлено им с обеих сторон, любое значение в столбце 1, которое содержит значение поиска в столбце 2, будет рассматриваться как совпадение.

Например, * Exxon * будет соответствовать ExxonMobil (поскольку * может представлять любое количество символов).

Вам также могут понравиться следующие советы и руководства по Excel: