Sinoć je u radio emisiji Computer America Craiga Crossmana Joe iz Bostona imao pitanje:
Imam stupac s brojevima računa. Kako mogu koristiti Excel za označavanje duplikata?
Predložio sam upotrebu uvjetnih formata i formule COUNTIF. Ovdje su detalji o tome kako to učiniti.
Želimo postaviti uvjetno formatiranje za cijeli raspon, ali lakše je postaviti uvjetni format za prvu ćeliju u rasponu, a zatim kopirati taj uvjetni format. U našem slučaju, ćelija A1 ima naslov broja računa, pa ću odabrati ćeliju A2 i na izborniku odabrati Format> Uvjetno oblikovanje. Dijalog uvjetnog formatiranja započinje početnim padajućim padajućim tekstom "Vrijednost ćelije je". Ako dodirnete strelicu pored toga, možete odabrati "Formula Je".
Nakon odabira "Formula Is", dijaloški okvir mijenja izgled. Umjesto okvira za "Između x i y", sada postoji jedan okvir s formulom. Ovaj okvir s formulama nevjerojatno je moćan. Možete upisati bilo koju formulu koju možete izmisliti, sve dok se ta formula procjenjuje na TRUE ili FALSE.
U našem slučaju trebamo koristiti formulu COUNTIF. Formula za upisivanje u okvir je
=COUNTIF(A:A,A2)>1
Na engleskom, ovo kaže, "pregledajte cijeli raspon stupca A. Izbrojite koliko je ćelija u tom rasponu iste vrijednosti kao ono u A2. (Zaista je važno da" A2 "u formuli ukazuje na trenutna ćelija - ćelija u koju postavljate uvjetno formatiranje. Dakle - ako su vaši podaci u stupcu E i postavljate prvo uvjetno formatiranje u E5, formula bi bila =COUNTIF(E:E,E5)>0). Zatim uspoređujemo kako bismo vidjeli hoće li se to računati je> 1. U idealnom slučaju, bez duplikata, brojanje će uvijek biti 1 - jer je ćelija A2 u rasponu - trebali bismo pronaći točno jednu ćeliju u stupcu A koja sadrži istu vrijednost kao A2.
Pritisnite gumb Format…
Sada je vrijeme da odaberete neugodan format. Na vrhu ovog dijaloškog okvira Format Cells nalaze se tri kartice. Kartica Font obično je prva, pa biste mogli odabrati podebljani, crveni font, ali volim nešto neugodnije. Obično kliknem karticu Uzorci i odaberem svijetlo crvenu ili svijetlo žutu boju. Odaberite boju, a zatim kliknite U redu da biste zatvorili dijaloški okvir Format Cells.
Odabrani format vidjet ćete u okviru "Pregled formata za upotrebu". Kliknite U redu da biste zatvorili dijaloški okvir Uvjetno oblikovanje …
… i ništa se ne događa. Vau. Ako prvi put postavljate uvjetno formatiranje, bilo bi jako lijepo ovdje dobiti povratnu informaciju da je to uspjelo. Ali, osim ako nemate dovoljno sreće da je 1098 u ćeliji A2 duplikat neke druge ćelije, stanje nije točno i čini se da se ništa nije dogodilo.
Morate kopirati uvjetno formatiranje iz A2 prema dolje u ostale ćelije u vašem rasponu. S pragom pokazivača u A2, učinite Uredi> Kopiraj. Pritisnite Ctrl + razmaknicu da biste odabrali cijeli stupac. Uredi> Posebno zalijepi. U dijaloškom okviru Zalijepi posebno kliknite Formati. Kliknite U redu.
Ovo će kopirati uvjetno formatiranje u sve stanice u stupcu. Sada - konačno - vidite neke ćelije s crvenim oblikovanjem, što znači da imate duplikat.
Informativno je otići do ćelije A3 i pogledati uvjetni format nakon kopije. Odaberite A3, pritisnite od za otvaranje uvjetnog oblikovanja. Formula u polju Formula je promijenjena kako bi se brojalo koliko se puta A3 pojavljuje u stupcu A: A.
Bilješke
Prema Joeovom pitanju, u asortimanu je imao samo 1700 računa. Postavio sam 65536 ćelija s uvjetnim formatiranjem i svaka stanica uspoređuje trenutnu ćeliju s 65536 drugih ćelija. U programu Excel 2005 - s više redaka - problem će biti još gori. Tehnički, formula u prvom koraku mogla je biti:=COUNTIF($A$2:$A$1751,A2)>1
Također, prilikom kopiranja uvjetnog formata u cijeli stupac, mogli ste umjesto toga odabrati samo retke s podacima prije nego što zalijepite posebne formate.
Više
Drugo pitanje koje sam opisao nakon pitanja je da stvarno ne možete sortirati stupac na temelju uvjetnog formata. Ako trebate sortirati ove podatke tako da se duplikati nalaze u jednom području, slijedite ove korake. Prvo dodajte naslov B1 pod nazivom "Duplikat?". Tip ovu formulu u B2: =COUNTIF(A:A,A2)>1.
Pokazivačem ćelije u B2 kliknite ručku automatskog popunjavanja (mali kvadrat u donjem desnom kutu ćelije) da biste kopirali formulu do kraja.
Sada možete sortirati prema stupcu B silazno i A uzlazno kako bi fakture s problemima bile na vrhu raspona.
Ovo rješenje pretpostavlja da želite istaknuti OBOJ duplikata računa tako da možete ručno otkriti koji ćete izbrisati ili ispraviti. Ako ne želite označiti prvu nastanak duplikat, možete prilagoditi formulu biti: =COUNTIF($A$2:$A2,A2)>1. Važno je unijeti znakove dolara točno onako kako je prikazano. Ovo će pregledati sve ćelije samo iz trenutne ćelije prema gore, tražeći duplicirane unose.
Hvala Joeu iz Bostona na pitanju!
