Рад са листовима пуним редова и колона у Екселу може постати лавиринт када се појаве дуплирани записи. У многим ситуацијама, дупликати су корисни (нпр. за откривање учесталости), али у другим Они ометају, збуњују и доводе до грешака у анализи. Савладавање алата проналажење и управљање њима прави разлику између чистог листа и бескорисног.
У овом чланку ћете видети, детаљно и корак по корак, Како пронаћи, означити, филтрирати и обрисати дупликате вредности у ЕкселуВидећете брзе методе са условним форматирањем, опцијом Уклони дупликате, трикове са формулама попут COUNTIF за откривање тачних појављивања и технике за идентификацију дуплиране пуне редове комбиновање колона. Поред тога, укључујемо практичне савете и студију случаја из стварног живота за откривање дупликата референца (колона А) и клијент (колона Б) на листу са хиљадама редова.
Шта је дупликат у Екселу и када га треба открити?
Ексел сваку вредност која се појављује више пута сматра дупликатом. у анализираном опсегу: не само два пута, већ и три, четири или више пута. Ово се односи и на појединачне ћелије и на комбинације које дефинишете (нпр. пар „Референца + Купац“).
Постоје два уобичајена разлога за њихово тражење: исправити грешке у подацима (случајно поновљени уноси) или, напротив, означите их јер су релевантни за анализу (на пример, понављања која указују на поновљену активност). Овај водич функционише у програмима Excel 2016, 2019, 2021 и Microsoft 365 без икаквих већих промена.
Ако сте у журби, ево кратког водича за основне ствари: Изаберите опсег, примените условно форматирање и означите дупликатеУ наредним одељцима, детаљније ћемо се позабавити филтерима, безбедним брисањем и прилагођеним формулама.
- Почетна > Условно форматирање > Правила за истицање ћелија > Дуплиране вредности и изаберите формат боје.
- Да бисте видели само оне који се понављају, активни филтер и филтрира према примењеној боји.
- Ако треба да обришете, користите Подаци > Уклони дупликате и означите колоне које дефинишу критеријуме.
Истакните дупликате помоћу условног форматирања
Највизуелнији приступ за почетак је дупликати ознака у боји, тако да вам се прикажу чим покрећете страницу. Ова метода не брише податке; само их истиче.
1) Изаберите опсег за претрагу дупликата. Можете означити целе колоне или редове кликом на његово заглавље или превлачењем одређене области мишем; такође можете држати притиснут тастер CTRL да бисте изабрали ћелије које нису суседне.
2) Идите на Почетна > Условно форматирање > Правила за истицање ћелија > Дуплиране вредностиОтвориће се прозор са падајућим менијем за избор формата.
3) У „вредности са„одаберите стил (нпр. светлоцрвено попуњавање са тамним текстом) и потврдите са „ОК“. Одатле, Дуплиране ћелије ће бити аутоматски обојене у избору.
Веома користан додатак: у истом дијалошком оквиру можете променити „Дуплирати“ у „Јединствен„Ако је оно што желите истакните јединствене вредности уместо поновљених. Ова опција олакшава проналажење уноса који се појављују само једном.
Прикажи само дупликате помоћу филтера
Ако је ваш лист опсежан, чак и ако га означите бојом, Незгодно је стално скроловати да бисте пронашли дупликатеИдеално би било да их комбинујете са филтером да бисте видели само дотичне редове.
1) Изаберите колоне које желите да филтрирате и идите на Подаци > Филтер (у групи „Сортирај и филтрирај“). Ово додаје падајуће стрелице насловима.
2) У обојеном заглављу колоне отворите мени и користите Филтрирај по боји (или бојом фонта, ако сте је тако применили). Овако, Excel ће приказивати само редове који садрже означене дупликате, привремено скривајући остатак.
Овим триком избегавате ручно пролажење кроз хиљаде редова; На први поглед ћете видети који се уноси понављају и можете одлучити шта ћете са њима као целином.
Безбедно уклањање дупликата
Када то схватите, можда ћете желети да обришете дуплиране уносе. Опција Подаци > Уклони дупликате трајно брише дупликате редова из опсега који изаберете, зато је паметно бити опрезан.
Претходна препорука: направите копију опсега или листа пре уклањања дупликата. На тај начин, ако нешто не иде по плану, можете се вратити уназад без икакве драме.
1) Изаберите опсег ћелија (или се уверите да је активна ћелија у Ексел табели) и отворите Подаци > Уклони дупликатеУ услузи Microsoft 365, наћи ћете га у групи „Алати за податке“.
2) Ако ваша листа има наслове, означите „Моја листа има заглавља”. После, активира колоне које дефинишу критеријуме дуплирањаНа пример, ако желите да сматрате ред дупликатом само ако се цео ред подудара, изаберите све колоне; ако је довољан јединствени кључ (нпр. „ИД“), изаберите само ту колону.
3) Кликните на „ОК“. Ексел задржаће први изглед сваког записа и обрисаће све остале дуплиране редове унутар изабраног опсега. Ништа ван опсега се не додирује нити помера.
Имајте на уму да брисањем само једне колоне могу остати редови који деле ту вредност, али се разликују у другим колонама; пажљиво бирајте колоне у складу са својим циљем (један кључ, неколико колона или све).
Бројите одређена појављивања: тачно 3, 4 или N пута
Понекад не желите све дупликате, већ само оне који се понављају тачан број пута. Са правило условног форматирања На основу формуле можете означити само оне који испуњавају N појављивања.
Пример: У колони А опсега А2:А10, означите само вредности које се појављују тачно 3 пута са овом формулом: =COUNTIF($A$2:$A$10;A2)=3.
Корак по корак: 1) Изаберите циљни опсег. 2) Идите на Почетна> Условно форматирање> Ново правило3) Изаберите „Користите формулу да бисте одредили које ћелије треба форматирати„. 4) Напишите горњу формулу. 5) Притисните „Формат„да одлучите о боји и потврдите.“ Биће форматиране само ћелије чија се вредност појављује 3 пута унутар задатог опсега.
Како функционише? Функција COUNTIF има два аргумента: опсег за бројање y критеријум (ћелија у активном реду, као што је А2). Постављање „=3“ на „>=3“ даје вам понављајуће три или више пута, а са „=2“ бисте видели само дупле вредности.
Дуплирајте целе редове: пронађите идентична подударања у свим колонама
Истицање појединачних ћелија може бити збуњујуће у табелама са више колона: видите пуно црвене боје, али можда Не припадају све ћелије истом дуплираном редуИдеално је проценити комплетне редове.
Веома робусна техника је стварање помоћна колона који спаја све релевантне колоне из сваког реда и тражи дупликате на основу тог спајања. На пример, у F3 откуцајте: =CONCAT(B3:E3) или ако желите, =Б3&Ц3&Д3&Е3и повуците надоле.
Затим, примените условни формат (дупликат вредности) на помоћну колону F. На овај начин, Брзо идентификујете који су редови идентични у свим разматраним колонама, јер се њихов спојени „потпис“ подудара.
Ако желите да форматирате цео ред само када се његов потпис понавља, користите правило формуле показујући на помоћну колону. Изаберите све колоне са подацима (Б:Е у примеру) и креирајте правило са: =COUNTIF($F$3:$F$9;$F3)>1Дакле, сваки ред је обојен ако се његова вредност у F појављује више пута.
Не желите да помоћна колона буде видљива? Сакриј то Кликните десним тастером миша на заглавље и изаберите „Сакриј“. Механизам ће и даље радити, али ће ваша табела бити чистија.
Избегавање лажних дупликата: размаци и друге неочекиване грешке
Класичан окидач за фантомске дупликате су преостали размаци. За Ексел, „Хуан“, „Хуан“ и „Хуан“ су различитиПре анализе, препоручљиво је нормализовати.
користите функцију SPACES(текст) да уклоните размаке са леве/десне стране и оставите само један између речи. Можете га комбиновати са СМЕНА o МИНУСЦ да хомогенизујете велика/мала слова ако ваша анализа то захтева.
Примените ове трансформације на помоћне колоне, а затим копирајте и налепите као вредности (Специјално лепљење > Вредности) ако желите да замените оригинале без формула. Ово ће смањити лажне дупликате услед форматирања.
Погледајте само дупликате и сакријте остатак помоћу филтера
Када означите дупликате помоћу условног форматирања, можете приказати само оне записе са Алатка за филтер да раде удобније.
1) Изаберите заглавља колона, идите на Подаци > Филтер и активирајте филтрирање. 2) У филтеру колоне са примењеним форматирањем, изаберите Филтрирај по боји3) Ексел сакриће све редове без те боје, остављајући видљивим само дупликате.
Ово је савршено за прегледајте, извезите или обришите дупликате одједном без потребе за ручним претраживањем. Када завршите, обришите филтер да бисте поново видели целу табелу.
Кратки водич: Пронађите дупликате у неколико кликова
Ако више волите да будете директни, на било ком листу: Почетна > Условно форматирање > Правила за истицање ћелија > Дуплиране вредностиИзаберите боју и то је то. Поступак је идентичан за стотине или хиљаде редова.
Запамтите да је овај метод не брише податке, само их истиче. Да бисте деловали на њих (приказали само поновљене, обрисали их итд.), комбинујте га са Филтер или са уклоните дупликате у зависности од тога шта вам треба.
Практичан случај: дупликати због комбинације поља (Референца у А + Клијент у Б)
Замислите табелу са ~1700 редова, колона A:Z, где А је референца поруџбине, а Б је име купца.Желите да пронађете тачне редове у којима се понавља исти пар „Референца + Купац“ и да будете у могућности да елиминишете додатне редове без претраживања једног по једног.
Опција 1 (брзо брисање): идите на Подаци > Уклони дупликате, означите „Моја листа има заглавља“ ако је применљиво и бира само колоне А и БНа овај начин, Excel ће задржати први ред сваке А+Б комбинације и обрисати остале. Ово је трајна радња, тако да прво направите копију.
Опција 2 (визуелни материјал за преглед пре): креирајте помоћна колона, на пример у AA, са формулом =А2&»|»&Б2 да спојите референцу и клијента. Затим примените Условно форматирање > Дуплиране вредности о АА. На крају, активирајте Филтер и филтрира према боји примењеној у АА на види само поновљене комбинацијеНа овај начин можете да одете до сваког реда и ручно одлучите да ли да га обришете.
Опција 3 (условно форматирање без помоћних елемената): Изаберите целу област података и креирајте нову. ново правило формуле која проверава да ли се комбинација A+B у реду појављује више пута унутар опсега. Најједноставнији и најтранспарентнији начин је, међутим, коришћење помоћне функције јер смањити грешке и омогућава вам филтрирање по боји.
Савет: Ако постоје проблеми са размацима или великим/малим словима, креирајте „чисту“ помоћну функцију са =РАЗМЕР(ГОРЊИ(А2))&»|»&РАЗМЕР(ГОРЊИ(Б2))На овај начин ћете избећи лажне дупликате и открићете стварне подударности.
Пажљиво бирање колона приликом уклањања дупликата
Када користите функцију „Уклони дупликате“, размислите о критеријумима свог пословања: да ли вам је битан само кључ (А), пар (А и Б) или цео ред (А:З)? Означавање превише колона може спречити откривање дупликата. ако постоје минималне разлике у нерелевантним ћелијама.
Уместо тога, обележавање неколико колона може обрисати редове који нисте хтели да избришете Јер, чак и ако деле референцу, други важни детаљи се мењају. Прилагодите критеријуме својој стварности и, ако сте у недоумици, прво тестирајте на копији.
Званична метода наспрам напредних метода: када користити сваку од њих
За свакодневну употребу, пречице Условни формат y уклоните дупликате Више су него довољни. Брзи су, јасни и не захтевају сложене формуле.
Кад затреба специфични захтеви (нпр. појављивања тачно N пута, комбинација поља или истицање целих редова на основу потписа), формуле са COUNTIF а помоћне колоне вам пружају фину контролу.
Ако управљате огромним количинама података или понављајућим токовима, можда бисте требали размотрити Повер Куери да групишете, уклоните дупликате и документујете кораке. Ипак, за већину канцеларијских сценарија, Методе у овом водичу решавају без компликовања ствари.
Добре праксе при раду са дупликатима
бацк уп са вашег листа или полигона пре него што га елиминишете. То је најбоље осигурање од страха.
Претвори листу у Ексел табела (Ctrl+T) да бисте добили уграђене филтере, стилове и робусније референце.
Нормализује улазе помоћу ПРОСТОРИ и, где је то прикладно, ГОРЊИ/ДОЊИ. Они избегавају фантомске дупликате и недоследне резултате.
Користите доследне боје приликом истицања дупликата и укратко документујте своје критеријуме (које колоне дефинишу дуплирање). Олакшава преглед од стране треће стране.
• За брзи преглед, Истакните дупликате помоћу условног форматирања и изаберите боју; затим филтрирајте по тој боји да бисте видели само понављања. • Ако треба да очистите лист, уклоните дупликате То је директан начин, пажљивим избором колона које дефинишу понављање. • За напредне случајеве (тачно N појављивања или комплетни дуплирани редови), подржава процес у CONTAR.SI, правила са формулама и спојеним помоћним колонама. • А за комбинације попут „Референца + Клијент“, изаберите између брисања по колонама или провере помоћу помоћних, у зависности од тога да ли вам је потребна брзина или претходна контрола.
Применом ових метода, Прећи ћете са муке са поновљеним регистрацијама на то да их имате под контролом., било да их идентификујете, анализирате или обришете безбедно и без бескрајног листања кроз табелу.
