Когато открием дублирани клетки само в една колона или списък, можем да приложим COUNTIF функция за бързо намиране и лесно преброяване на дублирани данни.
1. Изберете празна клетка освен колоната, в която ще намерите дубликати.

2. Въведете формулата =COUNTIF($C$3:$C$12, C3)>1, и натиснете Въведете ключ.

3. Плъзнете манипулатора за автоматично попълване на клетката с формула, за да копирате тази формула в други клетки.

Забележки: Във формулата =COUNTIF($C$3:$C$12, C3)>1,
(1) $C$3:$C$12 е колоната или списъкът, в който ще намерите дублирани стойности. Тъй като колоната е статична, когато копираме формулата в други клетки, тя обикновено е абсолютна препратка с „$“.
(2) C3 е първата клетка от посочената колона. Това е относителна препратка, защото трябва да се променя автоматично, когато копираме формулата в други клетки.
(3) Тази формула ще се върне TRUE or FALASE. TRUE означава, че съответната стойност е дубликат, докато FALSE показва, че съответната стойност е уникална в колоната.
(4) Понякога ВЯРНОТО или НЕВЯРНОТО може да не бъдат разбрани интуитивно. Можем да комбинираме оригиналната формула и функцията IF, за да върнем Дубликати директно.
=IF(COUNTIF($C$3:$C$12, C3)>1,"Дубликати","")

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

1. Изберете празна клетка освен втория списък с имена.

2. Въведете формулата =АКОГРЕШКА(VLOOKUP(D3,$B$3:$B$18,1,0),""), и натиснете Въведете ключ.

3. Плъзнете манипулатора за автоматично попълване на клетката с формула, за да копирате формулата в други клетки, както ви е необходимо.

Забележки: В горната формула,
(1) D3 е първата клетка във втория списък с имена. Препратката трябва да се променя автоматично, когато копираме формулата в други клетки, в резултат на това тя е относителна.
(2) $B$3:$B$18 е първият списък с имена. Това е абсолютна препратка, защото диапазонът трябва да остане статичен, когато копираме формулата в други клетки.
(3) Когато име се дублира с имена в първия списък, формулата ще върне името; в противен случай се връща празно.
(4) Можете също да приложите формулата =IF(COUNTIF($B$3:$B$18,D3)>0,"Дубликати","") за да намерите дублиращи се имена във втория списък в сравнение с първия списък. Тази формула ще се върне "дубликати" ако съответното име се дублира.

(5) Ако трябва да намерите дубликати в две колони в два листа, просто трябва да добавите името на листа преди препратката към сравнената колона. В нашия пример просто променете $B$3:$B$18 на Sheet1!$B$3:$B$18 във формулата.

Формулите, въведени по-горе, не съвпадат с главни и малки букви при намиране на дубликати, казват, че смятат, че „ябълка“ е дубликат с „ЯБЪЛКА“. Тук можете да приложите формула за масив, за да намерите дублирани стойности в една колона със съвпадащи случаи.

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

2. Въведете формулата на масива =АКО(СУМА((--ТОЧНО($C$3:$C$12,C3)))<=1,"","Дубликат"), и натиснете Ctrl + Превключване + Въведете ключове.

3. Плъзнете манипулатора за автоматично попълване на клетката с формула, за да копирате формулата на масива в други клетки.

Забележки: В горната формула за масив,
(1) $C$3:$C$12 е колоната, в която трябва да намерите дублирани стойности. Препратката е абсолютна, защото препратката е статична при копиране на формулата на масива в други клетки.
(2) C4 е първата клетка в колоната. Препратката е относителна, защото препратката трябва да се променя автоматично при копиране на формулата на масива в други клетки.
(3) Ако съответната клетка е дубликат, формулата за масива ще върне „Дублиране“, в противен случай се връща празно.

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

1. Изберете колоната, където ще маркирате дублирани клетки.

2. Щракнете Начало > Условно форматиране > Маркирайте правилата за клетки > Дублирани стойности. Вижте екранната снимка по-долу:

3. В изскачащия диалогов прозорец Дублирани стойности изберете Дубликат от първия падащ списък изберете сценарий за подчертаване от втория падащ списък и щракнете върху OK бутон.

бележки: Ако предварително зададените сценарии за осветяване не могат да отговорят на вашите нужди, можете да изберете Персонализиран формат от втория падащ списък и след това изберете цвета на маркирането, шрифта и границите на клетките, както ви е необходимо в изскачащия диалогов прозорец Форматиране на клетки.

След това ще видите, че всички дублирани клетки са маркирани в избраната колона, както е показано на екранната снимка по-долу.

бележки:  
(1) След като маркирате дублиращите се клетки, можем лесно да филтрираме тези дубликати. (Кликнете, за да видите как)
(2) След като маркираме дублиращите се клетки, можем лесно да премахнем дубликатите групово. (Кликнете, за да видите как)

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

1. Изберете диапазона (с изключение на заглавния ред), в който ще маркирате редове въз основа на дублирани клетки.

2. Щракнете Начало > Условно форматиране > Ново правило.

3. В диалоговия прозорец Ново правило за форматиране,
(1) Щракнете, за да изберете Използвайте формула, за да определите кои клетки да форматирате опция;
(2) Въведете формулата =COUNTIF($C$3:$C$12,$C3)>1 в Форматиране на стойности, където тази формула е вярна кутия;
Съвети: Във формулата, $C$3:$C$12 е колоната, съдържаща дублирани клетки, и $ C3 е първата клетка в колоната.
(3) Щракнете върху формат бутон.

4. В диалоговия прозорец Форматиране на клетки задайте цвета на запълване, шрифта, границите на клетките според нуждите и щракнете върху OK бутони последователно, за да запазите настройките.

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

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

1. Изберете диапазона с изключение на заглавния ред.

2. Щракнете Начало > Условно форматиране > Ново правило.

3. В диалоговия прозорец Ново правило за форматиране:
(1) Щракнете, за да изберете Използвайте формула, за да определите кои клетки да форматирате опция;
(2) в Форматиране на стойности, където тази формула е вярна поле, въведете формулата =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Щракнете върху формат бутон.

бележки: Във формулата =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $B$3:$B$12 е първата колона в диапазона и 3 долара е първата клетка в тази колона;
(2) $C$3:$C$12 е втората колона в диапазона и $ C3 е първата клетка в колоната;
(3) $D$3:$D$12 е третата колона в диапазона и $D3 е първата клетка в колоната;
(4) Ако има повече колони във вашия диапазон, можете да добавите препратките към колони и техните първи клетки последователно във формулата.

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

Досега дублиращите се редове са идентифицирани и маркирани в избрания диапазон. Вижте екранна снимка:

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

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

2. Щракнете Начало > Условно форматиране > Ново правило.

3. В диалоговия прозорец Ново правило за форматиране:
(1) Щракнете, за да маркирате Използвайте формула, за да определите кои клетки да форматирате опция;
(2) в Форматиране на стойности, където тази формула е вярна поле, въведете формулата =COUNTIF($C$3:$C3, C3)>1;
Съвети: За да маркирате редове въз основа на дубликати в една колона, въведете формулата =COUNTIF($C$3:$C3, $C3)>1.
(3) Щракнете върху формат бутон.

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

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

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

1, Натиснете Друг + F11 клавишите заедно, за да отворите прозореца на Microsoft Visual Basic за приложения.

2. В прозореца щракнете Поставете > Модулии след това поставете кода по-долу в прозореца на новия модул.

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3, Натиснете F5 клавиша или щракнете върху бягане икона за стартиране на тази VBA.

4. В изскачащия диалогов прозорец Kutools за Excel изберете колоната, където ще маркирате дублирани клетки с различни цветове, и щракнете върху OK бутон.

След това ще видите, че всяка поредица от дублирани стойности е маркирана с цвят.

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

1. Изберете колоната или диапазона, където искате да намерите и маркирайте дублиращите се клетки.

2. Щракнете Kutools > Изберете > Изберете Дублирани и уникални клетки.

3. В диалоговия прозорец Избор на дублирани и уникални клетки поставете отметка на необходимите опции и щракнете върху Ok бутони, за да завършите операцията.

бележки: В диалоговия прозорец Избор на дублирани и уникални клетки,
(1) Ако трябва да изберете или маркирате всички дубликати с изключение на първите инстанции, проверете Дубликати (с изключение на първия) опция. В противен случай проверете Всички дубликати (включително първия) опция.
(2) Ако трябва да маркирате дубликатите, отбележете Запълване на задния цвят опция и задайте цвят за подчертаване според нуждите.
(3) Ако искате да изберете или маркирате редове въз основа на дубликати в избраната колона, отбележете Изберете цели редове опция.
(4) Ако искате да изберете или маркирате дублиращи се стойности със съвпадащи случаи, отбележете Различаващ главни от малки букви опция.

Kutools за Excel също предоставя невероятен инструмент – Сравнете клетки, за да ни помогне лесно да намираме и маркираме дублирани клетки в две колони.

1. Щракнете Kutools > Сравнете клетки за да отворите диалоговия прозорец Избор на еднакви и различни клетки.

2. В диалоговия прозорец Избор на еднакви и различни клетки посочете двете колони в Намерете стойности в намлява Според кутии, проверете Същите стойности опция и отбележете други опции, ако искате.

Забележки:
(1) Ако трябва да намерите дублиращи се редове, проверете Всеки ред опция; и за да намерите дублирани клетки, проверете Единична клетка опция в Базиран на раздел;
(2) Отбележете Запълване на задния цвят опция и задайте цвят за подчертаване, ако трябва да маркирате дублиращите се редове или клетки;
(3) Отбележете Избрани цели редове опция, ако трябва да изберете или маркирате целия ред въз основа на дубликатите;
(4) Отбележете Различаващ главни от малки букви опция, ако искате да намерите или маркирате дубликати, чувствителни към главни и малки букви.

3. Щракнете Ok бутони последователно, за да завършите настройките.

След това ще видите дубликатите в колоната, която сте посочили в Намерете стойности в са идентифицирани и маркирани.

Този метод ще ви насочи да идентифицирате и маркирате дублирани клетки чрез правило за условно форматиране и след това лесно да филтрирате по цвета на маркирането в Excel.

1. Приложете условно форматиране, за да намерите и маркирате дубликати в указаната колона. (Кликнете, за да видите как)

2. Щракнете, за да изберете заглавката на колоната на посочената колона, и щракнете Дата > филтър.

3. Продължете, за да щракнете върху иконата на филтъра  в заглавката на колоната и изберете Филтър по цвяти след това изберете указания цвят за условно форматиране в падащия списък. Вижте екранна снимка:

Тогава ще видите, че само редовете с дублирани клетки са филтрирани. Вижте екранна снимка:

Като алтернатива можем също да идентифицираме дубликати с формула в помощна колона и след това да филтрираме дубликати с помощната колона лесно в Excel.

1. Освен оригиналните данни, добавете помощна колона и въведете Дубликат като заглавие на колона.

2. Изберете първата празна клетка под заглавката на колоната, въведете формулата =IF(COUNTIF($C$3:$C$12,C3)>1,"Дубликат","")и плъзнете манипулатора за автоматично попълване на тази клетка, за да копирате тази формула в други клетки.

бележки: В горната формула, $C$3:$C$12 е колоната, съдържаща дублирани данни, и C3 е първата клетка (с изключение на заглавната клетка) в колоната.

3. Щракнете, за да изберете заглавката на колоната – Дубликати, и щракнете Дата > филтър.

4. След това щракнете върху иконата на филтъра  в заглавката на колоната отбележете само Дубликат, и кликнете върху OK бутон. Вижте екранна снимка:

Досега се филтрират само редове с дублиращи се стойности. Вижте екранна снимка:

Като цяло можем да приложим = COUNTIF (диапазон, критерии) за да преброите общия брой на определена стойност, появяваща се в посочения диапазон. Казва броене колко пъти „Apple“ се появява в списъка A2:A10, можем да приложим формулата =COUNTIF(A2:A10, "Apple") за да преброите броя на тази дублирана стойност.

Обаче формулата =COUNTIF(диапазон, критерии) отчита само указаната дублирана стойност. Какво ще кажете за преброяване на дублирана стойност с два или множество критерии? И какво ще стане, ако преброим дубликати с малки и малки букви с критерии? Методите по-долу могат да ви помогнат да разрешите тези проблеми.

Можем да приложим формула за масив, за да преброим чувствителни към малки и главни букви дублирани стойности с критерии в Excel. Например, за да преброите колко пъти стойността "Apple" се появява в списъка B2:B21 със съвпадащи случаи, можете да го направите, както следва:

1. Изберете празна клетка.

2. Въведете формулата =СУМА(--ТОЧНО(B2:B20,E2)).

3, Натиснете Ctrl + Превключване + Въведете за връщане на резултата от преброяването.

бележки: Във формулата на масива,
(1) B2: B20 е колоната, в която ще броите дубликати.
(2) E2 е клетката, съдържаща определената стойност, която искате да преброите броя на срещанията. Можете да промените препратката към клетката към стойността с кавички, казва "ябълка".

Понякога може да искате да преброите дубликатите с два или повече критерия. Можете да приложите COUNTIFS функция, за да го направите.
Например, има таблица с продажби на плодове, както е показано на екранната снимка по-долу. Тук трябва да преброим времето на повторение на apple, който е продаден на 7/5/2020 и сумата на продажбите е по-голяма от 300. Можете да преброите дубликатите с тези критерии, както следва:

1. Изберете празна клетка.

2. Въведете формулата =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3, Натисни Въведете ключ за получаване на резултата от преброяването.

бележки: В горната формула,
(1) B3: B20 е (първата) колона с дата и G4 е критериите за дата;
(2) C3:C20 е (втората) колона с плодове и G3 е критериите за плодове;
(3) D3: D20 е (третата) колона със сумата и ">300" е критериите за сумата.
(4) Ако има повече колони и критерии във вашата таблица, можете да добавите препратката към колоната и критериите.

За да преброите всички дубликати в колона с изключение на първите срещания, моля, направете следното:

1. Изберете празна клетка до колоната.

2. Въведете формулата =АКО(COUNTIF($B$3:B3,B3)>1,"ДА","")и след това плъзнете манипулатора за автоматично попълване надолу, за да копирате тази формула в други клетки.

бележки: В горната формула,
(1) $B$3:B3 е диапазонът, в който броите дубликатите. В $B$3:B3 B3 ще се промени автоматично, когато копирате формулата в други клетки.
(2) B3 е първата клетка в указаната колона.
(3) Тази формула ще върне ДА или празно. ДА показва, че съответната стойност е дубликат, докато празното означава уникална.

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

3. Изберете празна клетка.

4. Въведете формулата =COUNTIF(C3:C16;"ДА"), и натиснете Въведете ключ.

бележки: В горната формула,
(1) C3:C16 е диапазонът, който приложихме формулата за идентифициране на дубликати в последната стъпка.
(2) ДА е стойността, върната от последната формула.

След това получаваме общия брой дублирани стойности в указаната колона. Общият брой дубликати не включва първите повторения.

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

1. Изберете празна клетка.

2. Въведете формулата =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3, Натиснете Ctrl + Превключване + Въведете за връщане на резултата от преброяването.

бележки: В горната формула, B3: B16 е посочената колона, в която искаме да преброим дубликати, включително първите срещания.

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

1. Изберете колоната, в която ще преброите броя на дублиращите се стойности, и щракнете Kutools > Изберете > Изберете Дублирани и уникални клетки.

2. В диалоговия прозорец Избор на дублирани и уникални клетки отметнете Дубликати (с изключение на първия) or Всички дубликати (включително първия) опция, както ви е необходима, и щракнете върху Ok бутон.

3. След това се избират всички дублиращи се стойности, включително или без първите срещания, и едновременно с това излиза диалогов прозорец, който показва колко клетки са избрани. Вижте екранната снимка по-горе.

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

1. Изберете празна клетка.

2. Въведете формулата =SUMPRODUCT(--(ISNUMBER(MATCH(B3:B12,D3:D18,0)))).

3, Натисни Въведете ключ.

бележки: В горната формула,
(1) B3: B12 е първата колона с имена, в която ще броите дубликати.
(2) D3: D18 е втората колона с имена, въз основа на които ще броите дубликати.

Като алтернатива можем да използваме добавка на трета страна, Kutools за Excel, за лесно преброяване на общия брой дублиращи се клетки между две колони.

1. Щракнете Kutools > Изберете > Изберете Същите и различни клетки.

2. В диалоговия прозорец Избор на еднакви и различни клетки,
(1) Посочете двете колони в Намерете стойности в намлява Според кутии отделно.
(2) Проверете Единична клетка опция.
(3) Проверете Същите стойности опция.

4, Кликнете на Ok бутон.

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

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

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

1. Изберете празна клетка.

2. Въведете формулата =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&"")).

3, Натисни Въведете ключ за получаване на резултата от преброяването.

бележки: В горната формула, C3:C19 е посочената колона, в която искате да преброите всяка дублирана стойност веднъж.

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

1. Изберете празна клетка.

2. Въведете формулата на масива =SUM(IFERROR(1/IF(C3:C19<>"", FREQUENCY(IF(EXACT(C3:C19, TRANSPOSE(C3:C19)), MATCH(ROW(C3:C19), ROW(C3:C19) ), ""), СЪОТВЕТСТВИЕ (РЕД(C3:C19), РЕД(C3:C19))), 0), 0)).

3, Натиснете Ctrl + Превключване + Въведете клавиши заедно, за да върнете резултата от преброяването.

бележки: В горната формула на масива C3:C19 е определената колона, където ще преброите всяка серия от дубликати веднъж със съвпадащи случаи.

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

1. Изберете празна клетка.

2. Щракнете Kutools > Помощник за формула > Статистически > Преброяване на клетки с уникални стойности (включете първата дублирана стойност).

3. В диалоговия прозорец Помощник за формули посочете колоната, в която ще броите дубликати веднъж Обхват и щракнете върху Ok бутон.

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

Можем да приложим Общо функция за преброяване на всяка поредица от дублирани стойности в колона в Excel.

1. Изберете колоната, в която ще преброите всяка поредица от дублирани стойности, и щракнете Дата > Сортирай от А до Я or Сортирайте от Z до A.

2. В изскачащия диалогов прозорец Предупреждение за сортиране проверете Разширете селекцията и щракнете върху Вид бутон.

След това ще видите, че селекцията е сортирана по дублиращите се стойности на посочената колона.

3. Задръжте селекцията избрана и щракнете Дата > Общо.

4. В диалоговия прозорец Междинна сума,
(1) Изберете посочената колона от При всяка промяна в падащ списък;
(2) Изберете Броя от Използвайте функцията падащ списък;
(3) Отбележете само посочената колона в Добавете междинна сума към списъчна кутия;
(4) Щракнете върху OK бутон.

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

Можем също така да създадем обобщена таблица за бързо преброяване на всяка поредица от дублиращи се стойности лесно в Excel.

1. Изберете диапазона, съдържащ определената колона, и щракнете Поставете > Осева таблица.

2. В диалоговия прозорец Създаване на обобщена таблица посочете мястото, където ще намерите новата обобщена таблица, и щракнете върху OK бутон.

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

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

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

1. Добавете празна колона вдясно към оригиналния диапазон от данни и наименувайте новата колона като Броя.

2. Изберете заедно оригиналния диапазон от данни и новата колона и щракнете Kutools > Обединяване и разделяне > Разширено комбиниране на редове.

3. В диалоговия прозорец Разширено комбиниране на редове,
(1) Щракнете, за да изберете посочената колона, в която ще преброите всяка серия от дублиращи се стойности, и щракнете Първичен ключ.
(2) Щракнете, за да изберете новата колона (Брой), и след това щракнете Изчисли > Броя.
(3) Посочете комбинации или видове изчисления за други колони, ако е необходимо.
(4) Щракнете върху Ok бутон.

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

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

1. Добавете празна колона вдясно към оригиналните данни.

2. Въведете формулата =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") в първата клетка на добавената празна колона.

3. Плъзнете манипулатора за автоматично попълване на тази клетка с формула, за да копирате формулата в други клетки.

бележки: В горната формула,
(1) $C$3:$C$14 е указаната колона, в която искате да преброите дублиращите се стойности по ред.
(2) C3 е първата клетка в указаната колона.
(3) Ако съответната стойност се дублира, тази формула ще върне пореден номер 1, 2, 3… въз основа на реда на появяване; ако съответната стойност е уникална, тази формула ще върне празно.

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

1. Изберете колоната, в която искате да премахнете всички дублирани стойности, с изключение на първите срещания.

2. Щракнете Дата > Премахнете дубликатите.

3. В диалоговия прозорец Премахване на предупреждение за дубликати проверете Продължете с текущата селекция и щракнете върху Премахване на дубликати бутон.

Съвети: За да премахнете редове въз основа на дублиращите се стойности в селекцията, проверете Разширете селекцията опция.

4. В диалоговия прозорец Премахване на дубликати проверете само определената колона и щракнете върху OK бутон.

Съвети: Ако сте проверили Разширете селекцията опция в последната стъпка, всички колони ще бъдат изброени тук. Въпреки това трябва да проверите само посочената колона.

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

Можете също да приложите Разширен филтър функция за лесно премахване на всички дублирани стойности от посочената колона.

1. Щракнете Дата > Подробно.

2. В диалоговия прозорец Разширен филтър,
(1) Проверете Копирайте на друго място опция;
(2) в Диапазон на списъка изберете посочената колона, от която ще премахнете дублирани стойности;
(3) в Копирай в кутия, посочете диапазона, в който ще поставите колоната;
(4) Отбележете Само уникални записи опция.
(5) Щракнете върху OK бутон.

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

Можете също да приложите VBA за бързо премахване на дублирани стойности, с изключение на първите срещания от колона в Excel.

1, Натиснете Друг + F11 клавиши, за да отворите прозореца на Microsoft Visual Basic for Application.

2. Щракнете Поставете > Модулии след това поставете под VBA кода в прозореца на новия модул.

Под ExtendOffice_RemoveAllDeplicate()
Dim xRg като диапазон
Dim xURg, xFRg, xFFRg като диапазон
Dim xI, xFNum, xFFNum като цяло число
Dim xDc като обект
Дим xDc_keys
Dim xBol Като булево
Dim xStr като низ
Dim xWs като работен лист
Dim xURgAddress като низ
При грешка Продължете напред
Задайте xRg = Application.InputBox("Изберете диапазон:", "Kutools за Excel", "", , , , , 8)
Ако xRg не е нищо, тогава излезте от Sub
Задайте xURg = Intersect(xRg.Worksheet.UsedRange, xRg)
Задайте xWs = xURg. Работен лист
Задайте xDc = CreateObject("scripting.dictionary")
xURgAddress = xURg.Address
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
За xFNum = 1 Към xURg.Count
Задайте xFRg = xURg.Item(xFNum)
If (Not IsError(xFRg)) Тогава
Ако xFRg.Value <> "" And (Not IsError(xFRg)) Тогава
За xFFNum = xFNum + 1 към xURg.Count
Задайте xFFRg = xURg.Item(xFFNum)
If Not IsError(xFFRg) Тогава
Ако xFFRg.Value = xFRg.Value Тогава
xDc(xFFRg.Address) = ""
Край Ако
Край Ако
Следващия
Край Ако
Край Ако
Следващия
xStr = ""
xDc_keys = xDc.Keys

За xI = 1 към UBound(xDc_keys)
Ако xStr = "" Тогава
xStr = xDc_keys(xI)
Задайте xURg = xWs.Range(xStr)
Друго
xStr = xStr & "," & xDc_keys(xI)
Задайте xURg = Application.Union(xWs.Range(xDc_keys(xI)), xURg)
Край Ако
Следващия
Debug.Print xStr
xWs.Активирай
xURg.Избери
Selection.Delete Shift:=xlUp
xWs.Range(xURgAddress).Select
Application.ScreenUpdating = xBol
End Sub

3, Натиснете F5 клавиша или щракнете върху бягане бутон за стартиране на този VBA.

4. В изскачащия диалогов прозорец посочете диапазона, от който ще премахнете дублиращите се стойности, и щракнете върху OK бутон.

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

Забележка: Този VBA код е чувствителен към главни и малки букви.

Можем да маркираме всички дублиращи се стойности, включително първите срещания в колона или списък с правило за условно форматиране, и след това да филтрираме всички дублиращи се стойности чрез цвета на подчертаване. След това можем да изберем всички филтрирани дублирани клетки и след това да ги премахнем групово.

1. Приложете условното форматиране, за да маркирате дублиращи се стойности. (Кликнете, за да видите как)

2. Изберете колоната, от която ще премахнете дублиращите се стойности (включително първите срещания), и щракнете Дата > филтър.

3. Щракнете върху иконата Филтър  в заглавката на колоната на посочената колона. В падащото меню изберете Филтър по цвяти след това задайте цвета на осветяването от подменюто.

След това всички дублиращи се стойности се филтрират.

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

5. След това всички дублиращи се стойности се премахват групово. Задръжте филтрирания списък избран и щракнете филтър > Дата отново, за да отмените филтъра.

Досега ще видите, че всички дублиращи се клетки, включително първите срещания, се премахват групово и остават само уникални стойности.

бележки: Този метод ще премахне редове въз основа на дублиращите се стойности в указаната колона.

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

1. Добавете помощна колона до посочената колона, въведете формулата =COUNTIF($B$3:$B$11,B3) в първата клетка на помощната колона и след това плъзнете манипулатора за автоматично попълване надолу, за да копирате тази формула в други клетки. Вижте екранна снимка:

Забележка: В горната формула, $B$3:$B$11 е указаната колона, от която ще премахнете дублирани стойности и B3 е първата клетка в указаната колона.

2. Изберете помощната колона и щракнете Дата > филтър.

3. Щракнете върху иконата на филтъра  в заглавката на помощната колона, след което в падащото меню проверете всички стойности освен 1, и кликнете върху OK бутон. Вижте екранна снимка:

4. Сега всички дублирани стойности са филтрирани. Изберете филтрираните клетки в помощната колона, щракнете с десния бутон и изберете Изтриване на реда в контекстното меню.

5. В изскачащия диалогов прозорец за повторно потвърждение щракнете върху OK бутон, за да продължите напред.

6. Сега всички дублирани стойности и техните редове се премахват групово. Продължете, за да щракнете Дата > филтър отново, за да отмените филтъра.

След това ще видите, че всички дублиращи се стойности, включително първите срещания, са изтрити групово.

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

1. Изберете колоната, от която ще премахнете дубликати.

2. Щракнете Kutools > Изберете > Изберете Дублирани и уникални клетки.

3. В диалоговия прозорец Избор на дублирани и уникални клетки отметнете Всички дубликати (включително първия) и щракнете върху Ok бутон.

Забележки:
(1) За да изберете и изтриете дублирани стойности, с изключение на първите срещания, проверете Дубликати (с изключение на първия) опция.
(2) За да изберете и изтриете редове въз основа на дублирани стойности в посочената колона, проверете Изберете цели редове опция.
(3) За да изберете и изтриете дублирани стойности със съвпадащи регистри, проверете Различаващ главни от малки букви опция.
(4) За да изберете, маркирате и изтриете дублирани клетки или редове, проверете Запълване на задния цвят or Цвят на шрифта за запълване опции и задайте цветове за запълване или шрифт според нуждите си.

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

5. Щракнете с десния бутон върху избраните клетки и изберете Изтрий от контекстното меню.

6. В предстоящия диалогов прозорец за изтриване отметнете Преместете клетките нагоре и щракнете върху OK бутон.

Досега всички дублирани стойности, включително първите срещания, са премахнати групово.

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

Първият метод е да приложите вградения Премахване на дубликати функция за премахване на редове по дубликати в указаната колона. Просто изберете диапазона, в който ще премахнете редовете, щракнете Дата > Премахване на дубликати за да активирате функцията, отбележете само определената колона в диалоговия прозорец Премахване на дубликати и след това щракнете OK за да завършите операцията по премахване.

Можем също така да приложим функцията за условно форматиране и филтриране, за да премахнем редове въз основа на дублирани стойности в указаната колона. Първо, маркирайте редове въз основа на дублирани стойности в определена колона чрез правило за условно формиране (щракнете, за да видите как). Второ, филтрирайте гамата по цвят. Трето, изтрийте лесно всички филтрирани редове. Най-накрая изчистете или отменете филтъра и ще видите, че са останали само редове с уникални стойности в указаната колона.

Като алтернатива можете да добавите помощна колона и да приложите формулата =COUNTIF($C$3:$C$21,C3) за идентифициране на дубликати в указаната колона. След това филтрирайте числата, по-големи от 1, в помощната колона и лесно премахнете всички филтрирани редове. След като изчистите филтъра, ще видите, че в посочената колона са останали само редове с уникални стойности.

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

Kutools за Excel Разширено комбиниране на редове функцията също може бързо да премахва редове въз основа на дублиращите се стойности в указаната колона с първичен ключ.

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

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

2. В първата клетка на помощната колона (с изключение на заглавната клетка) въведете формулата =IF(ISERROR(MATCH(C2,$A$2:$A$13,0)),"Уникален","Дубликат"), след което плъзнете манипулатора за автоматично попълване надолу, за да копирате формулата в други клетки.

бележки: В горната формула,
(1) C2 е първата клетка в указаната колона, от която ще премахнете дублирани стойности;
(2) $A$2:$A$13 XNUMX е другата колона, с която трябва да сравним.
(3) Тази формула ще се върне Дубликат ако съответната стойност се дублира със стойности в другата колона и се връща Уникална ако се различава от стойностите в другата колона.

3. Изберете помощната колона и щракнете Дата > филтър.

4. Щракнете върху иконата на филтъра  в заглавката на помощната колона, след това в падащото меню само проверете Дубликат, и кликнете върху OK бутон.

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

6. След това всички дублирани стойности се премахват от указаната колона. Продължете, за да щракнете Дата > филтър отново, за да отмените филтъра.

След това ще видите, че в указаната колона са останали само уникални стойности. Можете да премахнете помощната колона, както ви е необходимо.

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

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

1. Щракнете Kutools > Изберете > Изберете Същите и различни клетки за да активирате тази функция.

2. В диалоговия прозорец Избор на еднакви и различни клетки посочете и двете колони в Намерете стойности в намлява Според кутии отделно, проверете Единична клетка намлява Същите стойности опции и щракнете върху Ok бутон. Вижте екранна снимка:

3. След това всички дублирани стойности в размер на две колони се избират в първата колона (колоната, която сте посочили в Намерете стойност в кутия). И щракнете върху OK бутон в изскачащия диалогов прозорец.

4. Можете да натиснете Изтрий клавиш, за да премахнете директно тези дублирани стойности, или щракнете с десния бутон върху тях и изберете Изтрий от контекстното меню.