На прочтение этой статьи у вас уйдет около 10 минут, а за следующие 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные совпадения или уникальные значения. Итак, обратный отсчет начался!
Все мы время от времени сравниваем данные в Excel. Microsoft Excel предлагает ряд опций для сравнения и сопоставления данных, но большинство из них ориентировано на поиск по одному столбцу. Встроенный инструмент Duplicate Remover, доступный в Excel 2019–2010, не может справиться с этой задачей, поскольку не может сравнивать данные между двумя столбцами. Кроме того, он может удалять только дубликаты. К сожалению, других вариантов, таких как выделение или раскрашивание, нет :-(.
В этом уроке мы рассмотрим несколько методов сравнения двух столбцов в Excel и поиска совпадений и различий между ними.
- Как сравнить два столбца построчно?
- Построчное сравнение нескольких столбцов.
- Проверяет совпадения и различия в двух столбцах.
- Как извлечь данные для сопоставления значений.
- Выделите совпадения и различия
- Как выделить уникальные значения и дубликаты сразу в нескольких столбцах?
- Как сопоставить два значения в разных столбцах?
- Быстрый способ сравнить два столбца или списка без формул.
Как сравнить 2 столбца в Excel по строкам.
При анализе данных в Excel одной из наиболее распространенных задач является сравнение данных в нескольких столбцах в каждой строке. Вы можете выполнить эту задачу, используя функцию ЕСЛИ, как показано в следующих примерах.
1. Проверяем совпадения или различия в одной строке.
Чтобы выполнить такое комплексное сравнение, используйте популярную функцию ЕСЛИ, которая сравнивает первые две ячейки в каждой. Введите его в другой столбец той же строки и скопируйте вниз, перетащив маркер заполнения (маленький квадрат в правом нижнем углу). Курсор изменится на знак плюс:
Чтобы найти позиции в соответствующей строке с одинаковым содержимым, A2 и B2 в этом примере, введите:
=ЕСЛИ(A2=B2, "Соответствие"; "")
Чтобы найти позиции в одной строке с разным содержимым, замените "=" на знак равенства:
=ЕСЛИ(A2B2, "НЕ СОВПАДЕНИЕ";””)
И, конечно же, ничто не мешает вам находить совпадения и различия по одной формуле:
=ЕСЛИ(A2=B2, "совпадает", "не совпадает")
Результат может выглядеть примерно так:
Как видите, числа, даты, время и текст обрабатываются одинаково хорошо.
2. Сравниваем построчно с учетом регистра.
Как вы могли заметить, формулы из предыдущего примера игнорируют регистр при сравнении текстовых значений, как в строке 10 на снимке экрана выше. Если вы хотите найти совпадения с учетом регистра, используйте функцию EXACT):
= ЕСЛИ (ТОЧНО (A2, B2); "То же самое"; "")
Чтобы найти различия с учетом регистра в одной строке, введите соответствующий текст (например, «Уникальный») в третий аргумент функции ЕСЛИ:
= ЕСЛИ (ТОЧНО (A2, B2); «То же самое», «Уникальное")
Сравните несколько столбцов построчно
Мы можем поставить перед собой следующие цели:
- Найдите строки с одинаковыми значениями во всех.
- Найдите строки с одинаковыми значениями в обоих.
Пример 1. Найдите полное совпадение по одной строке.
Если в вашей таблице три или более столбца и вы хотите найти строки с одной и той же записью во всех из них, вам подойдет функция ЕСЛИ с оператором И:
=ЕСЛИ(И(A2=B2, A2=C2), "Точное совпадение"; "")
Если в вашей таблице много столбцов, более элегантным решением будет использование функции СЧЁТЕСЛИ :
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2, $A2)=3, "Точное совпадение"; "")
где 3 — количество сравниваемых столбцов.
Или вы можете использовать —
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2, $A2)=СЧЕТ(A2:C2), "Точное совпадение"; "")
Пример 2. Найдите хотя бы 2 совпадения в данных.
Если вы ищете способ сравнить данные на наличие двух или более ячеек с одинаковым значением в одной строке, используйте функцию ЕСЛИ с оператором ИЛИ:
=ЕСЛИ(ИЛИ(A2=B2, B2=C2, A2=C2), "совпало"; "")
Если есть много данных для сравнения, ваша конструкция ИЛИ может стать громоздкой. В этом случае лучшим решением будет добавить несколько функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько раз текущее значение из первого столбца встречается во всех данных справа от него, второй СЧЁТЕСЛИ определяет то же самое для значения из второго столбца и так далее. Если счетчик равен 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 на совпадения и различия?
Предположим, у вас есть два списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые записи), которые есть в столбце A, но их нет в B. То есть мы сравниваем исходные данные из А с Б.
Для этого вы можете внедрить функцию СЧЁТЕСЛИ($B:$B;$A2)=0 в логическую проверку ЕСЛИ и посмотреть, возвращает ли она ноль (совпадение не найдено) или другое число (найдено хотя бы 1 совпадение).
Например, следующая формула ЕСЛИ/СЧЁТЕСЛИ ищет значение от A2 до столбца B. Если совпадение не найдено, возвращается «Нет совпадения в B», в противном случае — пустая строка:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B"; "")
Примечание. Если ваша таблица имеет фиксированное количество строк, вы можете указать конкретный диапазон (например, $B2:$B20) вместо всего $B:$B, чтобы программа работала быстрее с большими наборами данных.
Тот же результат может быть достигнут с помощью функции IF allece с ISERROR и MATCH:
=ЕСЛИ(ОШИБКА(ПОИСКПОЗ($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. Если совпадение не найдено, возвращается ошибка #Н/Д.
=ИНДЕКС($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 является первой строкой данных, не включая заголовок таблицы). Пожалуйста, дважды проверьте, что вы используете относительную ссылку на строку (без символа $), как написано выше.
Чтобы выделить различия между столбцами A и B, создайте правило с формулой =$B2$A2
Если вы не знакомы с условным форматированием Excel, пошаговые инструкции см в разделе Как условно закрасить строку или столбец.
2. Выделите уникальные записи в каждом столбце.
При сравнении двух списков в Excel можно различать три типа элементов:
- Элементы только в первом списке (уникальные)
- Предметы только во втором списке (уникальные)
- Элементы, которые есть в обоих списках (дубликаты).
О выделении дубликатов — см пример выше. А теперь давайте рассмотрим, как выделить неповторяющиеся элементы в каждом из списков.
Предположим, что список 1 находится в столбце A (A2:A8), а список 2 — в столбце C (C2:C8). Правила условного форматирования создаются с помощью следующих формул:
Выделите уникальные значения в листинге 1 (столбец A): =COUNTIF($A$2:$A$8,C$2)=0
Выделите уникальные значения в списке 2 (столбец C): =СЧЁТЕСЛИ($C$2:$C$8,$A2)=0
И получить следующий результат:
3. Выделите дубликаты в 2 столбцах.
Если вы тщательно следовали предыдущему примеру, у вас не возникнет проблем с указанием СЧЁТЕСЛИ для поиска совпадений, а не различий. Все, что вам нужно сделать, это установить счетчик больше нуля:
Мы снова используем условное форматирование с формулой.
Отметьте совпадения в списке 1 (столбец A): =СЧЁТЕСЛИ($A$2:$A$8,C$2)>0
Отметьте совпадения в списке 2 (столбец C): =СЧЁТЕСЛИ($C$2:$C$8,$A2)>0
Выделите цветом различия и совпадения в нескольких столбцах
При сравнении значений в нескольких наборах данных построчно самый быстрый способ выделить одинаковые — создать правило условного форматирования. А самый быстрый способ скрыть различия — использовать инструмент «Выбрать группу ячеек», как показано в следующих примерах.
1. Как выделить совпадения.
Чтобы выделить строки с одинаковыми значениями, создайте правило условного форматирования на основе одного из следующих выражений:
=И($A2=$B2; $A2=$C2)
или
=СЧЁТЕСЛИ($A2:$C2, $A2)=3
Где A2, B2 и C2 — верх вашего диапазона, а 3 — количество столбцов для сравнения.
Конечно, нельзя ограничиваться сравнением только 3-х столбцов. Вы можете использовать аналогичные формулы для выделения строк с одинаковым значением в 4, 5, 6 или более столбцах.
И еще один способ выделить повторяющиеся значения в нескольких столбцах. Мы снова используем условное форматирование. Выберите нужный диапазон, затем в меню «Условное форматирование» выберите «Правила выбора ячеек — Дублирование значений на ленте». Определяем желаемый дизайн, получаем изображение похожее на то, что вы видите ниже.
Кстати, на последнем этапе можно выбрать не повторяющиеся, а уникальные значения. Способ конечно простой, но возможно он будет вам полезен.
2. Как выделить различия.
Для быстрого выделения элементов с разными значениями в каждой строке можно использовать функцию Excel «Выделить группу ячеек».
- Выберите диапазон ячеек, которые вы хотите сравнить. В этом примере я выбрал диапазон от A2 до C10.
По умолчанию верхняя координата выбранного диапазона является активной ячейкой, и с ней будут сравниваться все значения в одной строке. Когда диапазон выбран, он имеет белый цвет, а все остальные ячейки в выбранном диапазоне отображаются серым цветом. В этом примере активен A2, поэтому столбец сравнения — A.
Чтобы изменить столбец сравнения, используйте клавишу TAB для перемещения по диапазону слева направо или клавишу Enter для перемещения сверху вниз. Если вам нужно двигаться снизу вверх, нажмите и удерживайте SHIFT, а затем снова используйте TAB — вы хотите двигаться не вниз, а вверх. Вы увидите, как перемещается белое пятно, и соответственно изменяется активный столбец.
Примечание. Чтобы выбрать несмежные столбцы для сравнения, выберите первый диапазон, нажмите и удерживайте клавишу CTRL, затем выберите следующий. Активная ячейка будет в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу TAB или Enter, как описано выше.
- На вкладке «Главная» нажмите «Найти» и выберите > «Выбрать группу ячеек». Затем выберите Line Differences и нажмите OK» .
- Позиции, значения которых отличаются от ячеек сравнения в каждой строке, подсвечиваются. Если вы хотите заполнить выбранные ячейки цветом, просто щелкните значок «Цвет заливки» на ленте и выберите нужный цвет.
Как сравнить два значения в отдельных столбцах.
На самом деле, сравнение двух ячеек — это особый случай, когда Excel сравнивает два столбца построчно, за исключением того, что вам не нужно копировать формулы.
Например, чтобы сравнить ячейки A1 и C1, вы можете использовать:
Для совпадений: =ЕСЛИ(A1=C1, "Соответствие"; "")
Для различий: =ЕСЛИ(A1C1, "Уникальный"; "")
Чтобы узнать о некоторых других способах сравнения ячеек в Excel, см раздел Как сравнивать значения в ячейках Excel .
Для более эффективного анализа данных вам могут понадобиться более сложные формулы, и вы можете найти несколько хороших идей в следующих руководствах:
- Использование функции ЕСЛИ в Excel
- Функция ЕСЛИ: проверка условий с текстом
Быстрый способ сравнения двух столбцов или списков без формул.
Теперь, когда вы знаете, что Excel может предложить для сравнения и сопоставления столбцов, позвольте мне показать вам альтернативное решение, которое может сравнивать 2 списка с разным количеством столбцов на наличие дубликатов (совпадений) и уникальных значений (различий).
Надстройка Ultimate Suite может искать идентичные и уникальные записи в одной таблице, а также сравнивать две таблицы, находящиеся на одном листе, на двух разных листах или даже в разных книгах.
В этой статье мы сосредоточимся на функции под названием «Сравнить таблицы», которая специально разработана для сравнения двух списков по любым указанным вами столбцам. Сравнение двух наборов данных в нескольких столбцах — настоящая проблема как для формул Excel, так и для условного форматирования, но этот инструмент легко с этим справляется.
Для начала рассмотрим самый простой случай — сравним два столбца на совпадения и различия.
Предположим, у нас есть два списка продуктов. Нам нужно сравнить их друг с другом, как мы это делали ранее с помощью формул.
Запустите инструмент сравнения таблиц и выберите первый столбец. При необходимости включите создание резервной копии листа.
На втором шаге выберите второй столбец для сравнения.
На третьем шаге нам нужно указать, что именно мы ищем — дубликаты или уникальные значения.
Затем укажите столбцы для сравнения. Так как колонок всего две, то здесь все довольно просто:
На пятом шаге вы выбираете, что делать с найденными значениями — удалять, выделять, раскрашивать, копировать или перемещать. Вы можете добавить столбец состояния так же, как мы делали ранее с функцией ЕСЛИ. С помощью формул также можно закрашивать только ячейки. Здесь спектр возможностей гораздо шире. Но мы выберем простой и наглядный вариант — закрасим ячейки цветом.
Ячейки в списке 1, имеющие дубликаты в списке 2, затенены.
А теперь повторим все действия, описанные выше, только сравним список 2 с первым. И вот что у нас получается:
Незаштрихованные ячейки содержат уникальные значения. Красиво и наглядно.
Теперь давайте попробуем сравнить сразу несколько столбцов. Допустим, у нас есть два экземпляра отчета о продажах. Они размещены на разных листах в нашей книге Excel. Список предметов точно такой же, но фактические цифры продаж в некоторых местах различаются.
Действуйте точно так же, как описано выше, и выберите эти две таблицы для сравнения. На третьем шаге мы выбираем поиск уникальных значений, чтобы мы могли выбрать и выделить именно отклонения в данных.
Установите назначение столбцов, как показано на рисунке ниже.
Для наглядности снова подбираем заливку цветом для ошибочных значений.
И вот результат. Линии, которые не совпадают, заштрихованы.
Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.
Вот способы сравнения столбцов в Excel на наличие дубликатов и уникальных значений.
Если у вас есть какие-либо вопросы или что-то непонятно, напишите мне комментарий, и я буду рад объяснить это дальше. Спасибо за чтение!
Функция ЕСЛИОШИБКА — примеры формул. В этой статье описывается, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или конкретным сообщением. Давайте покажем примеры того, как использовать функцию ЕСЛИОШИБКА с визуальным…
Функция MAX в Excel: как найти максимальное значение. В этой статье объясняется функция MAX со множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число в электронной таблице. MAX — один из самых простых…
9 способов сравнить две таблицы в Excel и найти разницу. В этом руководстве вы узнаете о различных методах сравнения таблиц Excel и увидите различия между ними. Узнайте, как отобразить две таблицы рядом, как использовать формулы, чтобы сообщить о различиях, выделить…
Сравнение ячеек в Excel. Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или нечувствительности к регистру. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству…
Вычислить номер столбца для извлечения данных в ВПР - Задача: Самый простой способ научиться указывать столбец, из которого функция ВПР должна извлекать данные. При этом мы не будем менять саму формулу, так как это может привести к случайным ошибкам.…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц — задача, которую Excel приходится решать довольно часто. Например, у нас есть старый прайс-лист и его новая версия. Вам нужно посмотреть, какие цены изменились и…
Как проверить правильность ввода данных в Excel? - Подтвердите правильность введенных данных галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода данных в соответствии с существующим списком допустимых значений. Если введено правильно, в отдельной колонке введите…
Функция ЕСЛИ: проверьте взаимосвязь с текстом. Рассмотрите возможность использования функции ЕСЛИ в Excel, если в ячейке есть текст. Условие ContentTest для полного совпадения текста.ЕСЛИ + ПОИСКПОЗИспользует функцию ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСК…
Визуализируйте данные с помощью функции ЕСЛИ. Функцию ЕСЛИ можно использовать для вставки в таблицу символов, визуально отображающих изменения в данных. Например, мы хотим показать в отдельной колонке в таблице, что есть увеличение или уменьшение продаж.…
3 примера того, как функция ЕСЛИ работает с датами. — На первый взгляд может показаться, что функцию ЕСЛИ можно использовать для работы с датами точно так же, как с только что рассмотренными нами числовыми и текстовыми значениями. К сожалению, это... Александр Трифунтов2021-07-27T17:24:45+03:0016 Ноябрь 20203 Комментарии
