Синоћ у емисији Цомпутер Америца Цраига Цроссмана, Јое из Бостона имао је питање:
Имам колону бројева рачуна. Како могу да користим Екцел за обележавање дупликата?
Предложио сам употребу условних формата и формуле ЦОУНТИФ. Ево детаља о томе како то учинити.
Желимо да поставимо условно форматирање за читав опсег, али је лакше поставити условни формат за прву ћелију у опсегу, а затим копирати тај условни формат. У нашем случају, ћелија А1 има наслов броја фактуре, па ћу одабрати ћелију А2 и у менију изаберите Формат> Цондитионал Форматтинг. Дијалог условног форматирања започиње почетним падајућим падајућим натписом „Вредност ћелије је“. Ако додирнете стрелицу поред овога, можете да изаберете „Формула Ис“.
Након одабира „Формула Ис“, дијалошки оквир мења изглед. Уместо оквира за „Између к и и“, сада постоји један оквир са формулом. Овај оквир са формулама је невероватно моћан. Можете да укуцате било коју формулу за коју можете да сањате, све док ће се та формула процењивати на ТРУЕ или ФАЛСЕ.
У нашем случају морамо да користимо формулу ЦОУНТИФ. Формула за унос у поље је
=COUNTIF(A:A,A2)>1
На енглеском, ово каже, „прегледајте читав опсег колоне А. Пребројите колико је ћелија у том опсегу исте вредности као оно у А2. (Заиста је важно да„ А2 “у формули показује на тренутна ћелија - ћелија у коју постављате условно форматирање. Дакле - ако су ваши подаци у колони Е, а прво условно форматирање постављате у Е5, формула би била =COUNTIF(E:E,E5)>0). Затим упоређујемо да бисмо видели да ли се то рачуна је> 1. У идеалном случају, без дупликата, бројање ће увек бити 1 - јер је ћелија А2 у опсегу - требало би да пронађемо тачно једну ћелију у колони А која садржи исту вредност као А2.
Кликните дугме Формат…
Сада је време да одаберемо непријатан формат. На врху овог дијалога Формат Целлс налазе се три картице. Картица Фонт је обично прва, тако да можете одабрати подебљани, црвени фонт, али волим нешто неугодније. Обично кликнем на картицу Обрасци и одаберем јарко црвену или јарко жуту. Изаберите боју, а затим кликните У реду да бисте затворили дијалог Формат Целлс.
Одабрани формат видећете у пољу „Преглед формата за употребу“. Кликните ОК да бисте затворили дијалог Условно форматирање …
… и ништа се не догађа Вау. Ако први пут постављате условно форматирање, било би заиста лепо добити овде повратне информације да је функционисало. Али, осим ако немате довољно среће да је 1098 у ћелији А2 дупликат неке друге ћелије, услов није тачан и изгледа да се ништа није догодило.
Морате да копирате условно форматирање из А2 у друге ћелије у вашем опсегу. Са прагом курсора у А2, урадите Едит> Цопи. Притисните Цтрл + размакницу да бисте изабрали целу колону. Урадите> Налепи посебно. У дијалогу Специјално лепљење кликните на Формати. Кликните ОК.
Ово ће копирати условно обликовање у све ћелије у колони. Сада - коначно - видите неке ћелије са црвеним обликовањем, што указује да имате дупликат.
Информативно је отићи до ћелије А3 и погледати условни формат након копије. Изаберите А3, притисните од да бисте отворили условно форматирање. Формула у пољу Формула је промењена како би се рачунало колико се пута А3 појављује у колони А: А.
Напомене
У Јоеовом питању, имао је само 1700 рачуна у асортиману. Подесио сам 65536 ћелија са условним форматирањем и свака ћелија упоређује тренутну ћелију са 65536 других ћелија. У програму Екцел 2005 - са више редова - проблем ће бити још гори. Технички, формула у првом кораку је могла бити:=COUNTIF($A$2:$A$1751,A2)>1
Такође, приликом копирања условног формата у целу колону, уместо тога могли сте да изаберете само редове са подацима пре него што налепите Специјалне формате.
Више
Друго питање које сам описао након питања је да заиста не можете сортирати колону на основу условног формата. Ако требате да сортирате ове податке тако да се дупликати налазе у једном подручју, следите ове кораке. Прво додајте наслов у Б1 под називом „Дупликат?“. Типе ову формулу у Б2: =COUNTIF(A:A,A2)>1.
Помоћу показивача ћелије у Б2 кликните на ручицу за аутоматско попуњавање (мали квадрат у доњем десном углу ћелије) да бисте копирали формулу до краја.
Сада можете да сортирате према силазној колони Б и узлазној А да би фактуре са проблемима биле на врху опсега.
Ово решење претпоставља да желите да истакнете ОБОЈ дупликата рачуна тако да можете ручно да откријете који ћете избрисати или исправити. Ако не желите да означите прву појаву у два примерка, можете да подесите формулу да буде: =COUNTIF($A$2:$A2,A2)>1. Важно је унети знаке долара тачно онако како је приказано. Ово ће гледати само све ћелије из тренутне ћелије, у потрази за дупликатима уноса.
Хвала Јоеу из Бостона на питању!
