содржина
Оваа статија ќе ви одземе околу 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 (за да ги изберете сите непразни ќелии во колоната В) и на крајот притиснете Ctrl + V (за вметнување на формулата во сите избрани ќелии).
- Одлично, сега сите дупликат вредности се означени како „Дупликат":
Опција Б: две колони се на различни листови (во различни работни книги)
- Во првата ќелија од првата празна колона на работниот лист Sheet2 (во нашиот случај тоа е колона Б) внесете ја следната формула:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")тука Sheet3 е името на листот на кој се наоѓа втората колона и $ A $ 1: $ A $ 10000 се адреси на ќелии од 1 до последно во оваа втора колона.
- Копирајте ја формулата во сите ќелии во колона B (исто како опција А).
- Го добиваме овој резултат:
Обработка на пронајдени дупликати
Одлично, најдовме записи во првата колона кои се присутни и во втората колона. Сега треба да направиме нешто со нив. Рачното поминување низ сите дупликати записи во табела е доста неефикасно и одзема премногу време. Има подобри начини.
Прикажи само дупликат редови во колоната А
Ако вашите колони немаат заглавија, тогаш треба да ги додадете. За да го направите ова, поставете го курсорот на бројот што ја претставува првата линија и тој ќе се претвори во црна стрелка, како што е прикажано на сликата подолу:
Десен-клик и изберете од контекстното мени вметнување (Вметни):
Дајте имиња на колоните, на пример, „Име"И"Дупликат?» Потоа отворете го јазичето податоци (Податоци) и притиснете филтри (Филтер):
После тоа кликнете на малата сива стрелка до „Дупликат?« за да го отворите менито за филтри; отштиклирајте ги сите ставки во оваа листа освен Дупликат, и притиснете OK.
Тоа е сè, сега ги гледате само тие елементи од колоната А, кои се дупликат во колоната В. Има само две такви ќелии во нашата табела за обука, но, како што разбирате, во пракса ќе има многу повеќе од нив.
За повторно прикажување на сите редови од колона А, кликнете на симболот за филтер во колоната В, која сега изгледа како инка со мала стрелка и изберете Избери ги сите (Селектирај се). Или можете да го сторите истото преку лентата со кликнување податоци (Податоци) > Изберете & Филтрирај (Сортирај и филтрирај) > Отстрани (Исчисти) како што е прикажано на екранот подолу:
Променете ја бојата или означете ги пронајдените дупликати
Ако белешките „Дупликат“ не е доволно за вашите цели и сакате да означите дупликат ќелии со различна боја на фонтот, боја на пополнување или некој друг метод…
Во овој случај, филтрирајте ги дупликатите како што е прикажано погоре, изберете ги сите филтрирани ќелии и кликнете Ctrl + 1за да го отворите дијалогот Форматирај ќелии (формат на ќелија). Како пример, да ја смениме бојата на пополнување на ќелиите во редовите со дупликати во светло жолта. Се разбира, можете да ја промените бојата на полнење со алатката Пополнете табот (Боја за полнење). Почетна (Дома), но предноста на полето за дијалог Форматирај ќелии (Cell Format) со тоа што можете да ги конфигурирате сите опции за форматирање во исто време.
Сега дефинитивно нема да пропуштите ниту една ќелија со дупликати:
Отстранување на дупликат вредности од првата колона
Филтрирајте ја табелата така што ќе се прикажат само ќелиите со дупликат вредности и изберете ги тие ќелии.
Ако 2-те колони што ги споредувате се на различни листови, односно во различни табели, кликнете со десното копче на избраниот опсег и изберете Избриши го редот (Отстранете ја линијата):
прес OKкога Excel ќе побара од вас да потврдите дека навистина сакате да го избришете целиот ред на листот и потоа да го исчистите филтерот. Како што можете да видите, остануваат само редови со единствени вредности:
Ако 2 колони се на истиот лист, блиску еден до друг (соседен) или не блиску еден до друг (не соседно), тогаш процесот на отстранување на дупликатите ќе биде малку покомплициран. Не можеме да го отстраниме целиот ред со дупликат вредности, бидејќи тоа ќе ги отстрани ќелиите и од втората колона. Така да оставите само уникатни записи во колона А, направете го ова:
- Филтрирајте ја табелата за да прикаже само дупликат вредности и изберете ги тие ќелии. Десен-клик на нив и изберете од контекстното мени Исчистете ја содржината (јасна содржина).
- Исчистете го филтерот.
- Изберете ги сите ќелии во колона А, почнувајќи од ќелијата A1 сè до дното што ги содржи податоците.
- Кликнете на податоци (Податоци) и притиснете Подреди од А до Ш (Подреди од А до Ш). Во полето за дијалог што се отвора, изберете Продолжете со тековниот избор (Подреди во наведениот избор) и кликнете на копчето Црна (сортирање):
- Избришете ја колоната со формулата, повеќе нема да ви треба, отсега имате само единствени вредности.
- Тоа е тоа, сега колумната А содржи само единствени податоци што ги нема во колоната В:
Како што можете да видите, отстранувањето дупликати од две колони во Excel користејќи формули не е толку тешко.