Автоматично премахване на дублирани редове в Excel

• 2019

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

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

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

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

Премахване на дублираща функция

Да предположим, че използвате Excel, за да следите адресите и подозирате, че имате дублирани записи. Вижте примерния работен лист на Excel по-долу:

Забележете, че записът "Jones" се появява два пъти. За да премахнете такива дублиращи се записи, кликнете върху раздела Данни на лентата и намерете функцията Премахване на дубликати в секцията Инструменти за данни . Кликнете върху Премахване на дубликати и се отваря нов прозорец.

Тук трябва да вземете решение въз основа на това, дали използвате надписи на върха на колоните. Ако го направите, изберете опцията с етикети Моите данни има заглавия . Ако не използвате етикети със заглавия, ще използвате стандартните обозначения на колоните на Excel, като колона А, колона Б и др.

За този пример ще изберем само колона А и щракнете върху бутона OK . Прозорецът на опциите се затваря и Excel премахва втория запис “Jones”.

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

Забележете, че въпреки че има три записа „Jones“, само две са идентични. Ако използваме процедурите по-горе, за да премахнем дублиращи се записи, ще остане само един запис „Jones“. В този случай трябва да разширим критериите си за вземане на решение, за да включим както първото, така и фамилното име, намерени съответно в колони А и Б.

За да направите това, кликнете отново върху раздела Данни на лентата и след това кликнете върху Премахване на дубликати . Този път, когато се появи прозорецът с опции, изберете колони А и Б. Щракнете върху бутона ОК и забележете, че този път Excel е премахнал само една от записите на „Мери Джоунс“.

Това е така, защото казахме на Excel да премахне дубликатите чрез съвпадение на записи въз основа на колони А и В, а не само на колона А. Колкото повече колони изберете, толкова повече критерии трябва да бъдат изпълнени, преди Excel да счита, че записът е дубликат. Изберете всички колони, ако искате да премахнете напълно дублирани редове.

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

Разширен метод на филтриране

Вторият начин за премахване на дублиранията е да се използва опцията за разширен филтър. Първо изберете всички данни в листа. След това в раздела Data в лентата кликнете върху Advanced в секцията Sort & Filter .

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

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

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

Маркирайте дублиращи се редове в Excel

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

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

 = A1 & B1 & C1 & D1 & E1 

В примера по-долу имам данни в колони A thru F. Но първата колона е идентификационен номер, така че изключвам това от формулата по-долу. Уверете се, че сте включили всички колони с данни, на които искате да проверите за дубликати.

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

 = COUNTIF ($ H $ 1: $ H $ 34, $ H1)> 1 

Тук използваме функцията COUNTIF и първият параметър е набор от данни, които искаме да разгледаме. За мен това беше колона H (която има формула за комбиниране на данни) от ред 1 до 34. Също така е добра идея да се отървете от заглавния ред, преди да направите това.

Също така ще искате да се уверите, че използвате знака за долар ($) пред буквата и номера. Ако имате 1000 реда данни и комбинираната формула за редове е в колона F, формулата ви ще изглежда така:

 = COUNTIF ($ F $ 1: $ F $ 1000, $ F1)> 1 

Вторият параметър има само знак за долар пред буквата на колоната, така че да е заключен, но не искаме да заключваме номера на реда. Отново, ще преместите това надолу за всичките си редове с данни. Трябва да изглежда така и дублираните редове трябва да имат TRUE в тях.

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

В диалоговия прозорец кликнете върху Използване на формула, за да определите кои клетки да форматирате .

В полето Под форматирани стойности, където тази формула е вярна:, въведете следната формула, замествайки P с вашата колона, която има стойностите TRUE или FALSE. Уверете се, че сте поставили знак за долар пред писмото на колоната.

 = $ Р1 = ИСТИНСКИ 

След като го направите, кликнете върху Format и кликнете върху раздела Fill. Изберете цвят, който ще се използва за маркиране на целия дублиращ се ред. Кликнете върху „OK“ и сега ще видите, че дублираните редове са маркирани.

Ако това не работи за вас, започнете отначало и го направете отново бавно. Трябва да се направи точно, за да може всичко това да работи. Ако пропуснете един символ $ по пътя, той няма да работи правилно.

Внимание с премахването на дублирани записи

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

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

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

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

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

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

Също така, не забравяйте да проверите нашата предишна статия за изтриване на празни редове в Excel. Наслади се!