На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы можете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные совпадения или уникальные значения. Хорошо, обратный отсчет начался!
Все мы время от времени сравниваем данные в Excel. Microsoft Excel предлагает ряд опций для сравнения и сравнения данных, но большинство из них ориентированы на поиск по одному столбцу. Встроенное средство удаления дубликатов, доступное в Excel 2019-2010, не может справиться с этой задачей, поскольку не может сравнивать данные между двумя столбцами. Кроме того, он может удалять только дубликаты. К сожалению, других возможностей, таких как выделение или раскраска, нет :-(.
Как сравнить 2 столбца в Excel по строкам.
При анализе данных в Excel одной из наиболее распространенных задач является сравнение данных из нескольких столбцов в каждой отдельной строке. Эту задачу можно выполнить с помощью функции ЕСЛИ, как показано в следующих примерах.
1. Проверяем совпадения или различия в одной строке.
Чтобы выполнить это построчное сравнение, используйте популярную функцию ЕСЛИ, которая сравнивает первые две ячейки каждой. Введите его в другой столбец той же строки, затем скопируйте вниз, перетащив маркер заливки (маленький квадрат в правом нижнем углу). Курсор изменится на знак плюса:
Чтобы найти местоположения с одинаковым содержимым в соответствующей строке, A2 и B2 в этом примере, напишите:
= ЕСЛИ (LA2 = B2, «Соответствует»; «»)
Чтобы найти позиции в одной строке с разным содержанием, просто замените «=» знаком неравенства:
= ЕСЛИ (LA2 B2; «НЕ соответствует»;””)
И, конечно же, ничто не мешает находить совпадения и различия по формуле:
= ЕСЛИ (LA2 = B2; «Соответствует»; «НЕ СООТВЕТСТВУЕТ»)
Результат может выглядеть так:
Как видите, числа, даты, время и текст обрабатываются одинаково хорошо.
2. Сравниваем построчно с учетом регистра.
Как вы могли заметить, формулы в предыдущем примере игнорируют регистр при сравнении текстовых значений, как в строке 10 на скриншоте выше. Если вы хотите найти совпадения с учетом регистра, используйте функцию EXACT):
= ЕСЛИ (EXACT (A2; B2); «То же»; «»)
Чтобы найти различия с учетом регистра в одной строке, введите соответствующий текст (например, «Уникальный») в третий аргумент функции ЕСЛИ:
= ЕСЛИ (EXACT (A2; B2); «Равно»; «Уникальный»)
Сравните несколько столбцов построчно
Мы можем поставить перед собой следующие цели:
- Найдите строки с одинаковыми значениями во всех из них.
- Найдите строки с одинаковым значением в любых двух.
Пример 1. Найдите полное совпадение по одной строке.
Если в вашей таблице три или более столбца, и вы хотите найти строки с одинаковыми записями во всех из них, вам подойдет функция ЕСЛИ с оператором И:
= ЕСЛИ (И (LA2 = B2; A2 = C2), «Точное совпадение»; «»)
Если в вашей таблице много столбцов, более элегантным решением будет использование функции СЧЁТЕСЛИ :
= ЕСЛИ (СЧЁТЕСЛИ ($ A2: $ C2; $ A2) = 3, «Точное совпадение»; «»)
где 3 — количество сравниваемых столбцов.
Или вы можете использовать —
= ЕСЛИ (СЧЁТЕСЛИ ($ A2: $ C2; $ A2) = СЧЁТ (A2: C2), «Точное совпадение»; «»)
Пример 2. Найдите хотя бы 2 совпадения в данных.
Если вы ищете способ сравнить данные, чтобы увидеть, есть ли две или более ячеек с одинаковым значением в одной строке, используйте функцию ЕСЛИ с оператором ИЛИ:
= ЕСЛИ (ИЛИ (A2 = B2; B2 = C2; A2 = C2), «То же самое»; «»)
Если есть много данных для сравнения, ваша конструкция OR может стать слишком громоздкой. В этом случае лучшим решением было бы добавить несколько функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько раз текущее значение первого столбца встречается во всех данных справа от него, второй СЧЁТЕСЛИ определяет то же самое для значения второго столбца и т.д. Если счетчик равен 0, возвращается заголовок «Все уникальные», в противном случае — «Найдены идентичные». Например:
= ЕСЛИ (СЧЁТЕСЛИ (B2: D2; A2) + СЧЁТЕСЛИ (C2: D2, B2) + (C2 = D2) = 0, «Все уникальные»; «То же самое найдено»)
Я также могу предложить более компактный вариант обнаружения совпадений: формулу массива:
{= ЕСЛИ (СУММ (СЧЁТЕСЛИ (A2: D2; A2: D2))> 4; «Соответствует»;»»)}
или
{= ЕСЛИ (СУММ (СЧЁТЕСЛИ (A2: D2; A2: D2))> СЧЁТ (A2: D2); «Соответствует»;»»)}
Попробуйте — получите тот же результат. Также не забудьте нажать Ctrl + Shift + Enter, чтобы ввести все правильно.
Как сравнить два столбца в Excel на совпадения и различия?
Предположим, у нас есть 2 списка данных в Excel и мы хотим найти все значения (числа, даты или текстовые записи), которые находятся в столбце A, но не в столбце B. То есть мы сравниваем исходные данные из A в B
Для этого вы можете встроить функцию COUNTIF ($ B: $ B; $ A2) = 0 в логический тест SE и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено хотя бы 1 совпадение).
Например, следующая формула ЕСЛИ / СЧЁТЕСЛИ ищет значение в A2 во всем столбце 2. Если совпадений не найдено, она возвращает «Нет совпадений в B», в противном случае — пустую строку:
= ЕСЛИ (СЧЁТЕСЛИ ($ B: $ B; $ A2) = 0, «Нет совпадений в B»; «»)
Примечание. Если ваша таблица имеет фиксированное количество строк, вы можете указать конкретный диапазон (например, $ B2: $ B20) вместо всего $ B: $ B, чтобы ускорить выполнение программы для больших наборов данных.
Того же результата можно достичь, используя функцию ЕСЛИ вместе с ЕОШИБКА и ПОИСК:
= IF (ISERROR (SEARCH ($ A2; $ B $ 2: $ B $ 10,0)), «Уникальный», «Найдено в B»)
Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы вставить ее правильно):
= ЕСЛИ (СУММ (- ($ B $ 2: $ B $ 10 = $ A2)) = 0; «»; «Найдено в B»)
Если вы хотите, чтобы одно выражение определяло как повторяющиеся, так и уникальные значения, заключите текст соответствия в пустые кавычки («») в любой из приведенных выше формул. Например:
= ЕСЛИ (СЧЁТЕСЛИ ($ B: $ B; $ A2) = 0, «Уникальный», «Повторяющийся»)
Думаю, вы понимаете, что точно так же можно, наоборот, сравнивать Б с А.
Как сравнить два списка в Excel и извлечь совпадающие данные?
Иногда может потребоваться не только отобразить два столбца в две разные таблицы, но также извлечь соответствующие записи из второй таблицы. В Microsoft Excel для этой цели предусмотрена специальная функция: функция ВПР.
Также в отдельной статье мы подробно рассмотрели 4 способа сравнения таблиц с использованием формулы ВПР.
В качестве альтернативы вы можете использовать более мощную и универсальную комбинацию ИНДЕКС и ПОИСК.
Например, следующее выражение сравнивает названия продуктов в столбцах D и A, и если совпадение найдено, соответствующая цифра продаж извлекается из B. Если совпадения не найдено, возвращается ошибка # N.
= ИНДЕКС ($ B $ 2: $ B $ 6, ПОИСК ($ D2; $ A $ 2: $ A $ 6,0))
Сообщение об ошибке в таблице выглядит некрасиво. Поэтому мы обрабатываем это выражение с помощью ISERROR:
= ЕСЛИ ОШИБКА (ИНДЕКС ($ B $ 2: $ B $ 6, ПОИСК ($ D2; $ A $ 2: $ A $ 6.0));»»)
Теперь мы видим пустое число или значение. Без ошибок.
Как выделить совпадения и различия в 2 столбцах.
При сравнении наборов данных в Excel вы можете захотеть «увидеть» элементы, которые присутствуют в одном, но отсутствуют в другом. Вы можете раскрасить такие места любым цветом по вашему выбору, используя формулы. А вот несколько примеров с подробными инструкциями.
1. Выделите совпадения и различия построчно.
Чтобы сравнить два столбца в Excel и выбрать те позиции в первом, которые имеют идентичные записи во втором в той же строке, выполните следующие действия:
- Выберите область, которую хотите выделить.
- Щелкните Условное форматирование> Новое правило…> Использовать формулу.
- Создайте правило с простой формулой, например = $ B2 = $ A2 (при условии, что строка 2 является первой строкой данных, не включая заголовок таблицы). Пожалуйста, дважды проверьте, что вы используете относительную строковую ссылку ($ unsigned), как написано выше.
Чтобы выделить различия между столбцами A и B, создайте правило с формулой = $ B2 $ A2
Если вы новичок в условном форматировании Excel, см. Подробные инструкции в разделе Как условно нарисовать строку или столбец.
2. Выделите уникальные записи в каждом столбце.
При сравнении двух списков в Excel можно выделить 3 типа элементов:
- Только элементы в первом списке (уникальные)
- Только элементы во втором (уникальном) списке
- Пункты, которые есть в обоих списках (дубликаты).
О дублированном выборе: см. Пример выше. Теперь давайте посмотрим, как выделить неповторяющиеся элементы в каждом из списков.
Допустим, ваш список 1 находится в столбце A (A2: A8), а список 2 — в столбце C (C2: C8). Правила условного форматирования создаются с использованием следующих формул:
Выделите уникальные значения в списке 1 (столбец A): = COUNTIF ($ A $ 2: $ A $ 8; C $ 2) = 0
Выделите уникальные значения в списке 2 (столбец C): = COUNTIF ($ C $ 2: $ C $ 8, $ A2) = 0
И получите следующий результат:
3. Выделите дубликаты в 2 столбцах.
Если вы внимательно следовали предыдущему примеру, у вас не должно возникнуть проблем с настройкой COUNTIF для поиска совпадений, а не различий. Все, что вам нужно сделать, это установить счетчик на значение больше нуля:
Давайте повторно воспользуемся условным форматированием с формулой.
Выделите совпадения в списке 1 (столбец A): = COUNTIF ($ A $ 2: $ A $ 8; C $ 2)> 0
Выделите совпадения в списке 2 (столбец C): = COUNTIF ($ C $ 2: $ C $ 8, $ A2)> 0
Выделите цветом различия и совпадения в нескольких столбцах
При сравнении значений в нескольких наборах данных строка за строкой самый быстрый способ выделить одно и то же — создать правило условного форматирования. И самый быстрый способ скрыть различия — использовать инструмент «Выбрать группу ячеек», как показано в примерах ниже.
1. Как выделить совпадения.
Чтобы выделить строки, которые имеют одинаковое значение по всей длине, создайте правило условного форматирования на основе одного из следующих выражений:
= И ($ A2 = $ B2; $ A2 = $ C2)
или
= СЧЁТЕСЛИ ($ A2: $ C2; $ A2) = 3
Где A2, B2 и C2 — самые высокие значения в диапазоне, а 3 — количество столбцов для сравнения.
Конечно, вам не нужно ограничиваться только сравнением трех столбцов. Вы можете использовать аналогичные формулы для выделения строк с одинаковым значением в 4, 5, 6 или более столбцах.
И еще один способ выделить цветом повторяющиеся значения в нескольких столбцах. Давайте снова воспользуемся условным форматированием. Выделите нужную область, затем на ленте в меню условного форматирования выберите «Правила выбора ячеек — повторяющиеся значения». Определяем желаемый дизайн, получаем изображение, подобное тому, что вы видите ниже.
Кстати, на последнем шаге вы можете выбрать не повторяющиеся значения, а уникальные значения. Метод, конечно, несложный, но, возможно, вам он пригодится.
2. Как выделить различия.
Чтобы быстро выделить элементы с разными значениями в каждой отдельной строке, вы можете использовать функцию Excel «Выбрать группу ячеек».
- Выберите диапазон ячеек, который вы хотите сравнить. В этом примере я выбрал диапазон от A2 до C10.
По умолчанию самая высокая координата выбранного диапазона является активной ячейкой, и все значения в одной строке будут сравниваться с ней. Когда область выделена, она имеет белый цвет, а все остальные ячейки в выбранном диапазоне выделяются серым цветом. В этом примере активен A2, поэтому столбец сравнения — A.
Чтобы изменить столбец сравнения, используйте клавишу Tab для перемещения в диапазоне слева направо или клавишу Enter для перемещения сверху вниз. Если вам нужно двигаться снизу вверх, зажмите SHIFT и снова используйте TAB — вы переместитесь не вниз, а вверх. Вы увидите движение точки белого, и активный столбец изменится соответствующим образом.
Примечание. Чтобы выбрать несмежные столбцы для сравнения, выберите первый диапазон, удерживайте нажатой клавишу CTRL, затем выберите «Далее». Активная ячейка будет в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу TAB или Enter, как описано выше.
- На вкладке «Главная» нажмите «Найти и выделить»> «Выбрать группу ячеек». Затем выберите Line Differences и нажмите OK» .
- Элементы, значения которых отличаются от ячеек сравнения в каждой строке, выделяются. Если вы хотите заполнить выбранные ячейки цветом, просто щелкните значок «Цвет заливки» на ленте и выберите нужный цвет.
Как сравнить два значения в отдельных столбцах.
Фактически, сравнение двух ячеек — это особый случай сравнения двух столбцов в Excel построчно, за исключением того, что вам не нужно копировать формулы.
Например, чтобы сравнить ячейки A1 и C1, вы можете использовать:
Для совпадений: = SE (A1 = C1; «Совпадения»; «»)
Для различий: = SE (LA1 C1; «Уникальный»; «»)
Чтобы узнать о других способах сравнения ячеек в Excel, см. Раздел «Как сравнить значения в ячейках Excel .
Вам могут потребоваться более сложные формулы для более эффективного анализа данных, и вы можете найти несколько хороших идей в следующих уроках:
- Использование функции ЕСЛИ в Excel
- Функция ЕСЛИ: проверка условий с помощью текста
Быстрый способ сравнения двух столбцов или списков без формул.
Теперь, когда вы знаете, что Excel предлагает для сравнения и сопоставления столбцов, позвольте мне показать вам обходной путь, который позволяет сравнить 2 списка с разным количеством столбцов на предмет дубликатов (совпадений) и уникальных значений (различий).
Ultimate Suite может искать идентичные и уникальные записи в одной и той же таблице, а также сравнивать две таблицы на одном листе, на двух разных листах или даже в разных книгах.
В этой статье мы сосредоточимся на функции под названием «Сравнить таблицы», которая специально разработана для сравнения двух списков для любого указанного столбца. Сравнение двух наборов данных в нескольких столбцах — настоящая проблема как для формул Excel, так и для условного форматирования, но этот инструмент легко справляется с этим.
Для начала рассмотрим простейший случай: сравните два столбца на совпадения и различия.
Допустим, у нас есть два списка продуктов. Нам нужно сравнить их друг с другом, как мы делали раньше с помощью формул.
Запустите инструмент сравнения таблиц и выберите первый столбец. При необходимости включите создание резервной копии листа.
На втором этапе мы выбираем второй столбец для сравнения.
На третьем шаге вам нужно точно указать, что мы ищем: дубликаты или уникальные значения.
Далее указываем столбцы для сравнения. Поскольку столбцов всего две, здесь все довольно просто:
На пятом шаге выберите, что делать с найденными значениями: удалить, выделить, закрасить, скопировать или переместить. Вы можете добавить столбец статуса так же, как мы это делали ранее, используя функцию ЕСЛИ. Используя формулы, вы можете рисовать только по ячейкам. Здесь диапазон возможностей намного шире. Но мы выберем простой и наглядный вариант: заполним ячейки цветом.
Ячейки в списке 1, дубликаты которых есть в списке 2, будут окрашены.
Теперь повторяем все шаги, описанные выше, только сравним список 2 с первым. И вот что у нас получается:
Незаштрихованные ячейки содержат уникальные значения. Красиво и ясно.
Теперь попробуем сравнить несколько столбцов одновременно. Допустим, у нас есть две копии отчета о продажах. Они находятся на нескольких листах нашей книги Excel. Перечень товаров точно такой же, но сами цифры продаж кое-где различаются.
Действуя точно так же, как описано выше, мы выбираем эти две таблицы для сравнения. На третьем этапе мы выбираем поиск уникальных значений, чтобы мы могли выделить и выделить несоответствия в данных.
Установим соответствие столбцов, как показано на рисунке ниже.
Для наглядности давайте снова выберем цвет заливки для несовпадающих значений.
И вот результат. Несоответствующие линии окрашены.
Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite для Excel.
Вот способы, которыми вы можете сравнивать столбцы в Excel на наличие повторяющихся и уникальных значений.
Если у вас есть какие-либо вопросы или что-то остается неясным, напишите мне комментарий, и я буду рад прояснить его более подробно. Спасибо за прочтение!
