Идентифицирането на дублирани стойности в две колони в Excel е често срещана задача при анализа на данни. Това помага при установяване на грешки при въвеждане на данни, дублиращи се записи или за целите на почистването на данни. Тази статия ще ви напътства как ефективно и точно да идентифицирате дубликати или съвпадения в две колони.

Сравнете две колони, за да намерите дублирани стойности

• Маркирайте дубликати в две колони с условно форматиране
• Изберете и маркирайте дубликати в две колони с мощен инструмент – Kutools
• Намерете и извлечете дубликати в две колони с формули
• Изберете дубликати в две колони с VBA код

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

• Сравнете две колони в един ред с формули
• Изберете и маркирайте съвпадения в същия ред с полезен инструмент - Kutools
• Сравнете две колони и маркирайте съвпаденията в един и същи ред

Сравнете две колони и изтеглете съвпадащи данни

Сравнете две колони, за да намерите дублирани стойности

За да намерите дублиращи се стойности между две колони, има няколко налични метода в зависимост от вашите изисквания, като маркиране на дублирания за визуален преглед или извличането им за задълбочен анализ. В този раздел ще представим някои бързи трикове за изпълнение на тази задача в Excel.

Маркирайте дубликати в две колони с условно форматиране

Открояването на дубликати в две колони в Excel е ефективен начин за идентифициране на повтарящи се данни, особено в рамките на големи набори от данни, където ръчният преглед е непрактичен. В този случай условното форматиране е полезна функция за решаване на тази задача.

Стъпка 1: Изберете данните в колоната, от която искате да маркирате дубликати

В този пример ще избера A2: A10, вижте екранната снимка:

Стъпка 2: Приложете функцията за условно форматиране

1. Кликнете Начало > Условно форматиране > Ново правило, вижте екранна снимка:
   
2. в Ново правило за форматиране диалогов прозорец, моля, извършете следните операции:
   • 2.1 Изберете Използвайте формула, за да определите кои клетки да форматирате от Изберете тип правило списъчна кутия;
   • 2.2 Въведете следната формула в Форматиране на стойности, където тази формула е вярна текстово поле;

     =COUNTIF($B$2:$B$10, A2)>0

   • Забележка: В горната формула, B2: B10 представлява списъка с данни, с който искате да сравните, A2 е първата клетка от колоната, от която искате да маркирате дубликатите. Тази формула проверява дали стойността в клетка A2 се намира някъде в колона B. Променете препратките към клетките, за да отговарят на вашите данни.
   • 2.3 След това щракнете формат бутон.
   • 
3. В изскочилата Форматиране на клетки диалогов прозорец, задайте един цвят, който искате да маркирате дублиращите се елементи. И щракнете OK.
   
4. Когато се върне на Ново правило за форматиране кликнете върху OK бутон.

Резултат:

Сега стойностите, които се дублират и в двете колони A и B, вече са маркирани в колона A, както е показано на следната екранна снимка:

Изберете и маркирайте дубликати в две колони с мощен инструмент – Kutools

Понякога може да се наложи не само да маркирате, но и да изберете дубликати, за да ги копирате и поставите на друго място в работната си книга. В такива случаи, Kutools за ExcelЕ Изберете Същите и различни клетки функцията е идеален избор. Той може да идентифицира дублиращи се или уникални стойности, като маркира и избира клетките, от които се нуждаете.

Кликнете Kutools > Изберете > Изберете Същите и различни клетки, В Изберете Същите и различни клетки диалогов прозорец, моля, извършете следните операции:

1. Изберете изходните данни и сравнените данни в Намерете стойности в намлява Според кутии отделно;
2. Изберете Всеки ред под Базиран на раздел;
3. Изберете Същите стойности от Какво раздел;
4. Задайте цвят на фона за маркиране на дублиращите се стойности под Обработка на резултатите раздел;
5. Най-накрая щракнете OK бутон.
   

Резултат:

Сега стойностите, които се дублират в двете колони A и B, са маркирани и избрани в колона A, готови за копиране и поставяне във всички желани клетки. Вижте екранна снимка:

Намерете и извлечете дубликати в две колони с формула

За да намерите и извлечете дубликати между две колони, можете да използвате формула за определяне и извличане на дубликати.

Моля, копирайте и поставете следната формула в празна клетка, където искате да поставите резултата, след което плъзнете манипулатора за попълване надолу в колоната, за да приложите тази формула към други клетки.

=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)

Забележка: В горната формула, A2 е първата клетка от колоната, от която искате да намерите дубликатите; B2: B10 представлява списъка с данни, с който искате да сравните.

Резултат:

Както можете да видите, ако данните в колона A съществуват в колона B, стойността ще бъде показана; в противен случай клетките ще останат празни.

Изберете дубликати в две колони с VBA код

Този раздел ще ви преведе през стъпките за създаване на VBA код, който идентифицира и избира дублирани стойности между две колони.

Стъпка 1: Отворете редактора на VBA модул и копирайте кода

1. Натискане Alt + F11 за да отворите Microsoft Visual Basic за приложения прозорец.
2. В отворения прозорец щракнете Поставете > Модули за създаване на нов празен модул.
3. След това копирайте и поставете кода по-долу в празния модул.
   VBA код: Намерете и изберете дублиращите се стойности между две колони

   Sub Compare()
   'Update by Extendoffice
   Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
   xTitleId = "KutoolsforExcel"
   On Error Resume Next
   Set Range1 = Application.Selection
   Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8)
   Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)
   Application.ScreenUpdating = False
   For Each Rng1 In Range1
       xValue = Rng1.Value
       For Each Rng2 In Range2
           If xValue = Rng2.Value Then
               If outRng Is Nothing Then
                   Set outRng = Rng1
               Else
                   Set outRng = Application.Union(outRng, Rng1)
               End If
           End If
       Next
   Next
   outRng.Select
   Application.ScreenUpdating = True
   End Sub
   

Стъпка 2: Изпълнете този VBA код

1. След като поставите този код, моля, натиснете F5 ключ за изпълнение на този код. В първото поле за подкана изберете списъка с данни, от който искате да изберете дубликати. И след това щракнете OK.
   
2. Във второто поле за подкана изберете списъка с данни, с който искате да сравните, и след това щракнете OK, вижте екранна снимка:
   

Резултат:

Сега дублиращите се стойности от колони A и B са избрани в колона A, което ви позволява да запълните клетките с цвят или да ги копирате и поставите, ако е необходимо.

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

В Excel сравняването на две колони ред по ред често е необходимо за проверка за съвпадения, което помага при задачи като проверка на записи или анализиране на тенденции в данните. Excel има различни начини за това, от лесни формули до специални функции, така че можете да изберете най-добрия за нуждите на вашите данни. Нека да разгледаме някои лесни методи, за да свършите тази работа ефективно.

Сравнете две колони в един ред с формули

Формулите на Excel предлагат лесен, но мощен подход за сравнение на данни между колони. Ето как можете да ги използвате. Да предположим, че имате данни в колона A и колона B, за да проверите дали данните в двете колони съвпадат, следните формули може да ви помогнат:

• Използване на оператора Equal To (=): най-простият начин за сравняване на две клетки
• IF функция: за да направите сравнението си по-информативно
• ТОЧНА функция: сравнение с малки и големи букви

Използване на оператора Equal To (=):

Моля, въведете или копирайте следната формула, натиснете Въведете и след това плъзнете манипулатора за запълване надолу, за да получите всички резултати. Ще върне TRUE, ако стойностите в един и същи ред на колони A и B са идентични, и FALSE, ако не са. Вижте екранна снимка:

=A2=B2

IF функция:

Ако искате да направите сравнението по-информативно, можете да използвате IF функция за показване на персонализирани съобщения.

Моля, въведете или копирайте формулата по-долу, натиснете Въведете и след това плъзнете манипулатора за запълване надолу, за да получите всички резултати. Той ще върне Съвпадение, когато стойностите са еднакви, и Няма съвпадение, когато са различни. Вижте екранна снимка:

=IF(A2=B2, "Match", "No Match")

ТОЧНА функция:

Ако имате нужда от сравнение с малки и големи букви, ТОЧНА функция е начинът да вървим.

Моля, въведете или копирайте следната формула, натиснете Въведете и след това плъзнете манипулатора за запълване надолу, за да получите всички резултати. Той ще върне Съвпадение, когато стойностите съвпадат точно и Няма съвпадение, когато са различни. Вижте екранна снимка:

=IF(EXACT(A2,B2), "Match", "No match")   

Изберете и маркирайте съвпадения в същия ред с полезен инструмент - Kutools

Ако трябва да изберете и засенчите съвпаденията между две колони ред по ред, вместо да получите резултата в отделна колона, Kutools за Excel Сравнете клетки функция ще бъде отлична опция. Тя ви позволява бързо да изберете и приложите цвят на запълване към клетки, които съвпадат или се различават по стойност във всеки ред.

Кликнете Kutools > Сравнете клетки, В Сравнете клетки диалогов прозорец, моля, извършете следните операции:

1. Изберете данните от двете колони в Намерете стойности в намлява Според кутии отделно;
2. Изберете Същите клетки под Какво раздел;
3. Задайте цвят на фона за маркиране на съвпаденията под Обработка на резултатите раздел;
4. Най-накрая щракнете OK бутон.
   

Резултат:

Сега съвпаденията в същия ред са маркирани и избрани в колона A, което ги прави достъпни за копиране и поставяне във всички желани клетки. Вижте екранна снимка:

Сравнете две колони и маркирайте съвпаденията в един и същи ред

Сравняването на две колони и маркирането на съвпаденията в един и същи ред може да се направи ефективно с помощта на условно форматиране в Excel. Ето ръководство за идентифициране и маркиране на съвпадения на редове:

Стъпка 1: Изберете диапазона от данни

Изберете диапазона от данни, за който искате да маркирате съвпаденията на реда.

Стъпка 2: Приложете функцията за условно форматиране

1. Кликнете Начало > Условно форматиране > Ново правило. В Ново правило за форматиране диалогов прозорец, моля, извършете следните операции:
   • 2.1 Изберете Използвайте формула, за да определите кои клетки да форматирате от Изберете тип правило списъчна кутия;
   • 2.2 Въведете формулата по-долу в Форматиране на стойности, където тази формула е вярна текстово поле;

     =$B2=$A2

   • 2.3 След това щракнете формат бутон.
   • 
2. В изскочилата Форматиране на клетки диалогов прозорец, задайте един цвят, който искате да маркирате дублиращите се елементи. И щракнете OK.
   
3. Когато се върне на Ново правило за форматиране кликнете върху OK бутон.

Резултат:

Сега съвпадащите стойности в един и същи ред са маркирани наведнъж, вижте екранната снимка:

Сравнете две колони и изтеглете съвпадащи данни

Когато работите с два набора от данни в Excel и трябва да намерите общи елементи от единия списък в другия, формулите за търсене са вашето основно решение за извличане на тези съвпадения.

В Excel, ако имате списък с плодове в колона A и техните данни за продажби в колона B, сега искате да ги съпоставите с селекция от плодове в колона D, за да намерите съответните им продажби. Как можете да върнете относителните стойности от колона B в Excel?

Моля, приложете някоя от следните формули, от които се нуждаете, след което плъзнете манипулатора за попълване надолу, за да приложите тази формула към останалите клетки.

• Всички версии на Excel:

  =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)

• Excel 365 и Excel 2021:

  =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

Резултат:

Всички съответни стойности ще бъдат показани, ако бъде намерено съвпадение, в противен случай се връща грешка #N/A, вижте екранната снимка:

Свързани членове: