Съдържание
Прочитането на тази статия ще ви отнеме около 10 минути. В следващите 5 минути можете лесно да сравните две колони в Excel и да разберете дали има дубликати в тях, да ги изтриете или да ги маркирате с цвят. И така, времето дойде!
Excel е много мощно и наистина страхотно приложение за създаване и обработка на големи количества данни. Ако имате няколко работни книги с данни (или само една огромна таблица), тогава вероятно искате да сравните 2 колони, да намерите дублиращи се стойности и след това да направите нещо с тях, например да изтриете, маркирате или изчистите съдържанието. Колоните могат да бъдат в една и съща таблица, да са съседни или да не са съседни, могат да бъдат разположени на 2 различни листа или дори в различни книги.
Представете си, че имаме 2 колони с имена на хора – по 5 имена на колона A и 3 имена в колона B. Трябва да сравните имената в тези две колони и да намерите дубликати. Както разбирате, това са фиктивни данни, взети само за пример. В реалните таблици имаме работа с хиляди или дори десетки хиляди записи.
Вариант А: и двете колони са на един лист. Например колона A и колона B.
Вариант Б: Колоните са на различни листове. Например колона A върху листа Sheet2 и колона A върху листа Sheet3.
Excel 2013, 2010 и 2007 имат вграден инструмент Премахване на дубликати (Премахване на дубликати), но е безсилен в тази ситуация, тъй като не може да сравнява данни в 2 колони. Освен това може да премахва само дубликати. Няма други опции като подчертаване или промяна на цветовете. И точка!
След това ще ви покажа възможните начини за сравняване на две колони в Excel, което ще ви позволи да намерите и премахнете дублиращи се записи.
Сравнете 2 колони в Excel и намерете дублирани записи с помощта на формули
Вариант А: двете колони са на един лист
- В първата празна клетка (в нашия пример това е клетка C1) записваме следната формула:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
В нашата формула A1 това е първата клетка от първата колона, която ще сравним. 1 B$ и 10000 B$ това са адресите на първата и последната клетка от втората колона, с които ще извършим сравнението. Обърнете внимание на абсолютните препратки – буквите на колоните и номерата на редовете се предхождат от знак за долар ($). Използвам абсолютни препратки, така че адресите на клетките да останат същите при копиране на формули.
Ако искате да намерите дубликати в колона B, променете препратките, така че формулата да изглежда така:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")Вместо "Само"А"Дубликат» Можете да напишете свои собствени етикети, например «Не е намерено"А"Намерени“, или оставете само “Дубликат' и въведете знак за интервал вместо втората стойност. В последния случай клетките, за които не са открити дубликати, ще останат празни и според мен това представяне на данните е най-удобно за по-нататъшен анализ.
- Сега нека копираме нашата формула във всички клетки в колоната C, чак до долния ред, който съдържа данните в колоната A. За да направите това, преместете показалеца на мишката в долния десен ъгъл на клетката C1, показалецът ще приеме формата на черен мерник, както е показано на снимката по-долу:Щракнете и задръжте левия бутон на мишката и плъзнете границата на рамката надолу, като маркирате всички клетки, в които искате да вмъкнете формулата. Когато всички необходими клетки са избрани, отпуснете бутона на мишката:
Щракнете и задръжте левия бутон на мишката и плъзнете границата на рамката надолу, като маркирате всички клетки, в които искате да вмъкнете формулата. Когато всички необходими клетки са избрани, отпуснете бутона на мишката:
Съвет: В големи таблици копирането на формулата ще бъде по-бързо, ако използвате клавишни комбинации. Маркирайте клетка C1 и натиснете Ctrl + C (за да копирате формулата в клипборда), след което щракнете Ctrl+Shift+End (за да изберете всички непразни клетки в колона C) и накрая натиснете Ctrl + V (за да вмъкнете формулата във всички избрани клетки).
- Страхотно, сега всички дублиращи се стойности са маркирани като „Дубликат":
Вариант Б: две колони са на различни листове (в различни работни книги)
- В първата клетка на първата празна колона на работния лист Sheet2 (в нашия случай това е колона B) въведете следната формула:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Тук Sheet3 е името на листа, на който се намира втората колона, и $A$1:$A$10000 XNUMX са адресите на клетките от 1-ва до последната в тази 2-ра колона.
- Копирайте формулата във всички клетки в колона B (същото като опция А).
- Получаваме този резултат:
Обработка на намерени дубликати
Страхотно, открихме записи в първата колона, които присъстват и във втората колона. Сега трябва да направим нещо с тях. Преминаването през всички дублиращи се записи в таблица ръчно е доста неефективно и отнема твърде много време. Има по-добри начини.
Показване само на дублирани редове в колона A
Ако вашите колони нямат заглавки, тогава трябва да ги добавите. За да направите това, поставете курсора върху числото, което представлява първия ред, и то ще се превърне в черна стрелка, както е показано на фигурата по-долу:
Щракнете с десния бутон и изберете от контекстното меню вмъкване (Вмъкване):
Дайте имена на колоните, например „Име"А"дубликат?» След това отворете раздела Дата (Данни) и натиснете филтър (Филтър):
След това щракнете върху малката сива стрелка до „дубликат?« за отваряне на менюто за филтриране; премахнете отметката от всички елементи в този списък, с изключение на Дубликат, и натиснете OK.
Това е всичко, сега виждате само тези елементи от колоната А, които се дублират в колоната В. В нашата тренировъчна маса има само две такива клетки, но, както разбирате, на практика ще има много повече от тях.
За да покажете отново всички редове на колона А, щракнете върху символа за филтър в колоната В, който сега изглежда като фуния с малка стрелка, и изберете Избери всички (Избери всички). Или можете да направите същото чрез лентата, като щракнете Дата (Данни) > Изберете и филтрирайте (Сортиране и филтриране) > Изчисти (Изчистване), както е показано на екранната снимка по-долу:
Променете цвета или маркирайте откритите дубликати
Ако бележките „Дубликат” не е достатъчно за вашите цели и искате да маркирате дублирани клетки с различен цвят на шрифта, цвят на запълване или някакъв друг метод...
В този случай филтрирайте дубликатите, както е показано по-горе, изберете всички филтрирани клетки и щракнете Ctrl + 1за да отворите диалоговия прозорец Форматиране на клетки (формат на клетка). Като пример, нека променим цвета на запълване на клетки в редове с дубликати на ярко жълто. Разбира се, можете да промените цвета на запълване с инструмента Напълнете (Цвят на запълване). Начало (Начало), но предимство на диалоговия прозорец Форматиране на клетки (Формат на клетка), тъй като можете да конфигурирате всички опции за форматиране едновременно.
Сега определено няма да пропуснете клетки с дубликати:
Премахване на дублиращи се стойности от първата колона
Филтрирайте таблицата, така че да се показват само клетки с дублиращи се стойности, и изберете тези клетки.
Ако 2-те колони, които сравнявате, са на различни листове, тоест в различни таблици щракнете с десния бутон върху избрания диапазон и изберете Изтриване на реда (Премахване на ред):
Натискане OKкогато Excel ви помоли да потвърдите, че наистина искате да изтриете целия ред на листа и след това да изчистите филтъра. Както можете да видите, остават само редове с уникални стойности:
Ако 2 колони са на един лист, близо един до друг (съседни) или не близо един до друг (не съседни), тогава процесът на премахване на дубликати ще бъде малко по-сложен. Не можем да премахнем целия ред с дублирани стойности, тъй като това ще премахне клетките и от втората колона. Така че да оставите само уникални записи в колона А, направите това:
- Филтрирайте таблицата, за да показвате само дублирани стойности и изберете тези клетки. Щракнете с десния бутон върху тях и изберете от контекстното меню Изчистете съдържанието (прозрачно съдържание).
- Почистете филтъра.
- Изберете всички клетки в колона А, започвайки от клетката A1 чак до дъното, съдържащо данните.
- Щракнете върху Дата (Данни) и натиснете Сортирай от А до Я (Сортиране от А до Я). В диалоговия прозорец, който се отваря, изберете Продължете с текущата селекция (Сортирайте в рамките на указаната селекция) и щракнете върху бутона черно (Сортиране):
- Изтрийте колоната с формулата, вече няма да ви трябва, оттук нататък имате само уникални стойности.
- Това е всичко, сега колоната А съдържа само уникални данни, които не са в колоната В:
Както можете да видите, премахването на дубликати от две колони в Excel с помощта на формули не е толкова трудно.