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

Обновлено: 21.11.2024

Хотите сравнить два столбца в Excel, чтобы определить уникальные и повторяющиеся данные? Вот несколько способов сделать это.

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

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

1. Как выделить повторяющиеся данные

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

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

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

2. Как выделить уникальные данные

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

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

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

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

3. Выделите строки с идентичными данными

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

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

Выполните следующие действия, чтобы использовать условное форматирование Excel для сравнения двух наборов данных:

Выберите данные, которые хотите сравнить (не включая заголовки), и откройте вкладку "Главная".
Нажмите "Условное форматирование" и выберите "Новое правило".
В разделе "Выберите тип правила" нажмите "Использовать формулу", чтобы определить, какие ячейки следует форматировать.
Введите =$A2=$B2 в поле под форматом значений, где эта формула верна. Здесь A и B соответствуют двум сравниваемым столбцам.

При сравнении двух столбцов в Excel с помощью этого метода вы также можете выделить строки с разными данными. Выполните описанные выше шаги и на шаге 5 введите формулу =$A2<>$B2 в поле «Формат значений», где эта формула является истинной.

4. Определите совпадения с TRUE или FALSE

Вы можете добавить новый столбец при сравнении двух столбцов Excel. Используя этот метод, вы добавите третий столбец, в котором будет отображаться TRUE, если данные совпадают, и FALSE, если данные не совпадают.

Для третьего столбца используйте формулу =A2=B2, чтобы сравнить первые два столбца. Если вы считаете, что ваша электронная таблица будет выглядеть слишком переполненной строками ИСТИНА и ЛОЖЬ, вы можете установить фильтр в Excel, чтобы в нем отображались только значения ИСТИНА.

5. Сравните два столбца с помощью функции ЕСЛИ

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

Вместо значений ИСТИНА или ЛОЖЬ вы можете установить значение для совпадающих или разных данных. В этом примере мы будем использовать значения «Данные совпадают» и «Данные не совпадают».

Формула, которую мы будем использовать для столбца, показывающего результаты: =ЕСЛИ(A2=B2,"Данные совпадают","Данные не совпадают").

6. Сравните два столбца с помощью функции ВПР и найдите совпадающие данные

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

Используйте функцию =ВПР(B2,$A$2:$A$14,1,0) для столбца, отображающего результаты. Просто убедитесь, что вы настроили диапазон данных.

Чтобы избежать путаницы, обновите функцию ВПР до функции ЕСЛИОШИБКА. Если вам нужно найти данные, которые находятся в столбце B, а также в столбце A, используйте формулу =ЕСЛИОШИБКА(ВПР(B2,$A$2:$A$14,1,0),"Данные не совпадают").

7. Как сравнить два столбца и извлечь данные

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

Примечание. Если вы хотите защитить свои результаты от орфографических ошибок, используйте формулу =ВПР("*"&D2&"*",$A$2:$B$14,2,0). Здесь звездочка (*) играет роль подстановочного знака, заменяя любое количество символов.

Простое сравнение данных

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

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

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

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

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

Чтобы сравнить два столбца в Excel построчно, напишите обычную формулу ЕСЛИ, которая сравнивает первые две ячейки. Введите формулу в другой столбец той же строки, а затем скопируйте ее в другие ячейки, перетащив маркер заполнения (небольшой квадрат в правом нижнем углу выбранной ячейки). При этом курсор изменится на знак плюса:

Формула совпадений

Чтобы найти ячейки в одной строке с одинаковым содержимым, A2 и B2 в этом примере, формула выглядит следующим образом:

Формула различий

Чтобы найти ячейки в одной строке с разными значениями, просто замените знак равенства на знак отсутствия равенства (<>):

Совпадения и различия

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

Результат может выглядеть примерно так:

Как видите, формула одинаково хорошо обрабатывает числа, даты, время и текстовые строки.

Пример 2. Сравнение двух списков на совпадения с учетом регистра в одной строке

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

=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "")

Чтобы найти различия в одной и той же строке с учетом регистра, введите соответствующий текст ("Уникальный" в данном примере) в 3-й аргумент функции ЕСЛИ, например:

=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "Уникальный")

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

На листах Excel несколько столбцов можно сравнивать по следующим критериям:

Найти строки с одинаковыми значениями во всех столбцах (пример 1)
Найти строки с одинаковыми значениями в любых двух столбцах (пример 2)

Пример 1. Поиск совпадений во всех ячейках одной строки

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

=ЕСЛИ(И(A2=B2, A2=C2), "Полное совпадение", "")

Если в вашей таблице много столбцов, более элегантным решением будет использование функции СЧЁТЕСЛИ:

=ЕСЛИ(СЧЁТЕСЛИ($A2:$E2, $A2)=5, "Полное совпадение", "")

Где 5 — количество сравниваемых столбцов.

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

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

=ЕСЛИ(ИЛИ(A2=B2, B2=C2, A2=C2), "Соответствие", "")

Если нужно сравнить много столбцов, оператор ИЛИ может стать слишком большим. В этом случае лучшим решением будет добавление нескольких функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько столбцов имеют то же значение, что и в 1-м столбце, второй СЧЁТЕСЛИ подсчитывает, сколько из оставшихся столбцов равны 2-му столбцу, и так далее. Если счетчик равен 0, формула возвращает «Уникальный», в противном случае — «Совпадение». Например:

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2,A2)+СЧЁТЕСЛИ(C2:D2,B2)+(C2=D2)=0,"Уникальный","Совпадение")

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

Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые строки), которые находятся в столбце A, но не в столбце B.

Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B, $A2)=0 в логическую проверку ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (как минимум 1 совпадение). найдено).

Например, следующая формула ЕСЛИ/СЧЕТЕСЛИ ищет по всему столбцу B значение в ячейке A2. Если совпадений не найдено, формула возвращает «Нет совпадений в B», в противном случае — пустую строку:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "")

Совет. Если в вашей таблице фиксированное количество строк, вы можете указать определенный диапазон (например, $B2:$B10), а не весь столбец ($B:$B), чтобы формула работала быстрее с большими наборами данных.

Того же результата можно добиться, используя формулу ЕСЛИ со встроенными функциями ЕОШИБКА и ПОИСКПОЗ:

=IF(ISERROR(MATCH($A2,$B$2:$B$10,0)),"Нет совпадений в B","")

Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):

=IF(SUM(--($B$2:$B$10=$A2))=0, "Нет совпадений в B", "")

Если вы хотите, чтобы одна формула определяла как совпадения (дубликаты), так и различия (уникальные значения), поместите некоторый текст для совпадений в пустые двойные кавычки ("") в любой из приведенных выше формул. Например:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "Соответствует в B")

Как сравнить два списка в Excel и найти совпадения

Иногда может потребоваться не только сопоставить два столбца в двух разных таблицах, но и извлечь совпадающие записи из таблицы поиска. В Microsoft Excel для этого предусмотрена специальная функция — функция ВПР. В качестве альтернативы вы можете использовать более мощную и универсальную формулу INDEX MATCH. Пользователи Excel 2021 и Excel 365 могут выполнить эту задачу с помощью функции XLOOKUP.

=ВПР(D2, $A$2:$B$6, 2, ЛОЖЬ)

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

Если вам не очень удобно работать с формулами, вы можете выполнить эту работу с помощью быстрого и интуитивно понятного решения — Мастера объединения таблиц.

Сравните два списка и выделите совпадения и различия

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

Пример 1. Выделение совпадений и различий в каждой строке

Чтобы сравнить два столбца и Excel и выделить ячейки в столбце A, которые имеют идентичные записи в столбце B в той же строке, выполните следующие действия:

Выберите ячейки, которые хотите выделить (вы можете выбрать ячейки в одном столбце или в нескольких столбцах, если хотите закрасить целые строки).
Нажмите Условное форматирование > Новое правило… > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок столбца). Убедитесь, что вы используете относительную ссылку на строку (без знака $), как в приведенной выше формуле.

Чтобы выделить различия между столбцами A и B, создайте правило со следующей формулой:

=$B2<>$A2

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

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

Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:

Элементы, находящиеся только в первом списке (уникальные)
Элементы, находящиеся только во втором списке (уникальные)
Элементы, которые есть в обоих списках (дубликаты) — показано в следующем примере.

В этом примере показано, как раскрасить элементы только в одном списке.

Предположим, что ваш список 1 находится в столбце A (A2:A6), а список 2 — в столбце C (C2:C5). Вы создаете правила условного форматирования со следующими формулами:

Выделить уникальные значения в списке 1 (столбец A):

Выделить уникальные значения в списке 2 (столбец C):

И получите следующий результат:

Пример 3. Выделение совпадений (дубликатов) между двумя столбцами

Если вы точно следовали предыдущему примеру, у вас не возникнет трудностей с настройкой формул СЧЁТЕСЛИ, чтобы они находили совпадения, а не различия. Все, что вам нужно сделать, это установить счетчик больше нуля:

Выделить совпадения в списке 1 (столбец A):

Выделить совпадения в списке 2 (столбец C):

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

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

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

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

Где A2, B2 и C2 — самые верхние ячейки, а 3 — количество столбцов для сравнения.

Конечно, ни формула И, ни СЧЁТЕСЛИ не ограничиваются сравнением только 3 столбцов, вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбцах.

Пример 2. Сравните несколько столбцов и выделите различия в строках

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

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

Чтобы изменить столбец сравнения, используйте либо клавишу Tab для перемещения по выбранным ячейкам слева направо, либо клавишу Enter для перемещения сверху вниз.

Совет. Чтобы выбрать несмежные столбцы, выберите первый столбец, нажмите и удерживайте клавишу Ctrl , а затем выберите другие столбцы. Активная ячейка будет в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу Tab или Enter, как описано выше.

Как сравнить две ячейки в Excel

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

Например, для сравнения ячеек A1 и C1 можно использовать следующие формулы.

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

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

Теперь, когда вы знакомы с предложениями Excel для сравнения и сопоставления столбцов, позвольте мне показать вам наше собственное решение для этой задачи. Этот инструмент называется «Сравнить две таблицы» и входит в состав Ultimate Suite.

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

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

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

Повторяющиеся значения (совпадения) — элементы, существующие в обоих списках.
Уникальные значения (различия) — элементы, которые присутствуют в списке 1, но отсутствуют в списке 2.

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

Здесь доступно несколько различных вариантов. Для наших целей эти два наиболее полезны:

Выделение цветом: оттенки совпадают или различаются в выбранном цвете (например, условное форматирование в Excel).
Идентифицировать в столбце "Статус" — вставляет столбец Статус с метками "Дубликат" или "Уникальный" (как в формулах ЕСЛИ).

Для этого примера я решил выделить дубликаты следующим цветом:

И через мгновение получил следующий результат:

Для столбца Статус результат будет выглядеть следующим образом:

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

Я благодарю вас за чтение и рекомендую вам ознакомиться с другими полезными руководствами, которые у нас есть :)

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

Чтобы выделить все различия (строки 3, 6, 7 и 9) красным цветом, выполните следующие действия:

<р>1. Выберите данные в столбцах, которые вы хотите сравнить, и на ленте выберите Главная > Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.

<р>2. Во всплывающем окне (1) выберите «Уникальный» и (2) нажмите «ОК». Вы можете оставить формат по умолчанию (светло-красная заливка с темно-красным текстом).
Вы также можете выбрать Дублировать; в этом случае будут выделены одни и те же значения.

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

Сравните два столбца и выделите различия в Google Таблицах

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

<р>1. Выберите диапазон данных, который вы хотите сравнить (B2:C9), и в меню выберите Формат > Условное форматирование.

<р>2. В окне справа (1) выберите «Пользовательская формула» в разделе «Правила форматирования» и (2) введите формулу:

При работе с данными в Excel рано или поздно вам придется сравнивать данные. Это может быть сравнение двух столбцов или даже данных на разных листах/книгах.

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

Существует несколько способов сделать это в Excel, и в этом руководстве я покажу вам некоторые из них (например, сравнение с использованием формулы ВПР, формулы ЕСЛИ или условного форматирования).

Итак, приступим!

Оглавление

Сравнить два столбца (рядом)

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

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

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

Позвольте мне показать вам несколько простых способов сделать это.

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

Предположим, у вас есть приведенный ниже набор данных, и вы хотите узнать, в каких строках есть совпадающие данные, а в каких — разные данные.

Ниже приведена простая формула для сравнения двух столбцов (рядом):

Приведенная выше формула даст вам ИСТИНА, если оба значения одинаковы, и ЛОЖЬ, если они не совпадают.

Теперь, если вам нужно узнать все совпадающие значения, просто примените фильтр и отобразите только все ИСТИННЫЕ значения. И если вы хотите узнать все значения, которые отличаются, отфильтруйте все значения, которые являются ЛОЖЬМИ (как показано ниже):

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

Сравнить рядом с помощью функции ЕСЛИ

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

Это похоже на метод выше, где мы использовали оператор равенства (=), но с одним дополнительным преимуществом. При использовании функции ЕСЛИ вы можете выбрать значение, которое хотите получить при совпадении или различии.

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

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

Приведенная выше формула использует одно и то же условие, чтобы проверить, есть ли в двух ячейках (в одной строке) совпадающие данные или нет (A2=B2). Но поскольку мы используем функцию ЕСЛИ, мы можем попросить ее вернуть определенный текст в случае, если условие истинно или ложно.

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

Выделить строки с совпадающими данными (или разными данными)

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

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

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

Ниже приведены шаги по использованию условного форматирования для выделения строк с совпадающими данными:

Вышеуказанные шаги мгновенно выделят строки, в которых имена совпадают в обоих столбцах A и B (в одной и той же строке). А в случае, если имя отличается, эти строки не будут выделены.

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

Как это работает?

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

Когда мы используем $A2=$B2, он проверит каждую ячейку (в обоих столбцах) и увидит, равно ли значение в строке в столбце A значению в столбце B или нет.

В случае точного совпадения оно будет выделено указанным цветом, а в случае несовпадения — нет.

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

Сравнить два столбца с помощью функции ВПР (найти совпадающие/разные данные)

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

На самом деле такое случается редко.

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

В таких случаях нельзя использовать простой знак равенства или даже функцию ЕСЛИ.

Вам нужно что-то более мощное…

… что-то похожее на ВПР!

Позвольте мне показать вам два примера, где мы сравниваем два столбца в Excel, используя функцию ВПР для поиска совпадений и различий.

Сравнить два столбца с помощью функции ВПР и найти совпадения

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

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

Приведенная выше формула сравнивает два столбца (A и B) и дает вам имя, если имя находится в столбце B, а также A, и возвращает «Нет совпадения», если имя находится в столбце B, а не в Столбец А.

Вы также можете выполнить обратное сравнение, чтобы проверить, находится ли имя в столбце A, а также в столбце B. Следующая формула сделает это:

Сравнить два столбца с помощью функции ВПР и найти различия (отсутствующие точки данных)

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

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

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

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

Вот почему я включил функцию ВПР в функции ЕСЛИ и ЕОШИБКА. Вся эта формула дает значение — «Недоступно», когда имя отсутствует в столбце А, и «Доступно», когда оно присутствует.

Чтобы узнать все отсутствующие имена, вы можете отфильтровать столбец результатов на основе значения «Недоступно».

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

Распространенные запросы при сравнении двух столбцов

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

Q1. Как сравнить несколько столбцов в Excel в одной строке на совпадения? Также подсчитайте общее количество дубликатов.

Ответ. Мы дали процедуру сравнения двух столбцов в Excel для одной и той же строки выше. Но если вы хотите сравнить несколько столбцов в Excel для одной и той же строки, посмотрите пример

Здесь мы сравнили данные столбца A, столбца B и столбца C. После этого я применил приведенную выше формулу к столбцу D и получил результат.

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

Вопрос 2. Какой оператор вы используете для совпадений и различий?

Ответ. Ниже приведены используемые операторы:

Чтобы найти совпадения, используйте знак равенства (=)
Чтобы найти различия (несоответствия), используйте знак не-равно (<>)

В3. Как сравнить две разные таблицы и получить соответствующие данные?

Ответ. Для этого вы можете использовать функцию ВПР или функцию ИНДЕКС и ПОИСКПОЗ. Чтобы лучше понять это, мы возьмем пример.

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

Q4. Как удалить дубликаты в Excel?

Ответ. Чтобы удалить повторяющиеся данные, вам нужно сначала найти повторяющиеся значения.

Чтобы найти дубликат, вы можете использовать различные методы, такие как условное форматирование, Vlookup, оператор If и многие другие. В Excel также есть встроенный инструмент, с помощью которого можно просто выбрать данные и удалить дубликаты из столбца или даже из нескольких столбцов

В5. Я вижу, что в обоих столбцах есть совпадающее значение. Однако формулы, которыми вы поделились выше, не рассматривают их как точные совпадения. Почему?

Ответ: Excel считает что-то точным соответствием, когда каждый символ одной ячейки равен другому. Существует высокая вероятность того, что в вашем наборе данных есть начальные или конечные пробелы.

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

В7. Как сравнить два столбца, которые дают результат ИСТИНА, когда целочисленные значения всех первых столбцов не меньше целочисленных значений второго столбца. Для решения этой задачи мне не требуется условное форматирование, функция ВПР, оператор Если и любые другие формулы. Мне нужна формула, чтобы решить эту проблему.

Ответ. Для решения этой задачи можно использовать формулу массива.

Синтаксис <=AND(H6:H12>I6:I12)>. Это даст вам «Истина» в результате, когда значение столбца H больше, чем значение в столбце I, иначе результатом будет «Ложь».

Читайте также: