Sinoči je v radijski oddaji Computer America Craiga Crossmana Joe iz Bostona imel vprašanje:
Imam stolpec številk računov. Kako lahko z Excelom označim dvojnike?
Predlagal sem uporabo pogojnih formatov in formule COUNTIF. Tu so podrobnosti o tem, kako to delati.
Želimo nastaviti pogojno oblikovanje za celoten obseg, vendar je lažje nastaviti pogojni format za prvo celico v obsegu in nato kopirati to pogojno obliko. V našem primeru ima celica A1 naslov številke računa, zato bom izbral celico A2 in v meniju izberite Oblika> Pogojno oblikovanje. Pogovorno okno Oblikovanje se začne z začetnim spustnim menijem, ki pravi »Vrednost celice je«. Če se dotaknete puščice poleg tega, lahko izberete "Formula Je".
Po izbiri "Formula Is" se pogovorno okno spremeni. Namesto polj za "Med x in y" je zdaj eno polje s formulo. Ta škatla s formulami je neverjetno zmogljiva. Vtipkate lahko katero koli formulo, ki si jo lahko omislite, če bo ta formula ocenjena na TRUE ali FALSE.
V našem primeru moramo uporabiti formulo COUNTIF. Formula za vnos v polje je
=COUNTIF(A:A,A2)>1
V angleščini to pravi, "preglejte celoten obseg stolpca A. Preštejte, koliko celic v tem obsegu ima enako vrednost kot tisto v A2. (Resnično je pomembno, da" A2 "v formuli kaže na trenutna celica - celica, v katero nastavljate pogojno oblikovanje. Torej - če so vaši podatki v stolpcu E in nastavite prvo pogojno oblikovanje v E5, bi bila formula =COUNTIF(E:E,E5)>0). Nato primerjamo, da ugotovimo, ali to šteje je> 1. V idealnem primeru bo brez dvojnikov štetje vedno 1 - ker je celica A2 v obsegu - bi morali v stolpcu A najti točno eno celico, ki vsebuje enako vrednost kot A2.
Kliknite gumb Format…
Zdaj je čas, da izberemo neprijetno obliko. Na vrhu tega pogovornega okna Oblika celic so trije zavihki. Zavihek Pisava je običajno prvi, zato lahko izberete krepko rdečo pisavo, vendar mi je všeč nekaj bolj neprijetnega. Običajno kliknem zavihek Vzorci in izberem živo rdečo ali živo rumeno. Izberite barvo in kliknite V redu, da zaprete pogovorno okno Format Cells.
Izbrano obliko boste videli v polju "Predogled oblike za uporabo". Kliknite V redu, da zaprete pogovorno okno Pogojno oblikovanje …
… in nič se ne zgodi. Vau. Če prvič nastavljate pogojno oblikovanje, bi bilo res lepo, če bi tukaj dobili povratne informacije, da je delovalo. Toda razen če imate srečo, da je 1098 v celici A2 dvojnik neke druge celice, stanje ne drži in zdi se, da se ni nič zgodilo.
Kopirati morate pogojno oblikovanje iz A2 v druge celice v vašem obsegu. S pragom kazalke v A2 naredite Uredi> Kopiraj. Pritisnite Ctrl + preslednica, da izberete celoten stolpec. Ali Uredi> Prilepi posebno. V pogovornem oknu Prilepi posebno kliknite Oblike. Kliknite V redu.
To bo kopiralo pogojno oblikovanje v vse celice v stolpcu. Zdaj - končno - vidite nekaj celic z rdečim oblikovanjem, kar pomeni, da imate dvojnik.
Informativno je, da odprete celico A3 in si ogledate pogojno obliko po kopiji. Izberite A3, pritisnite od, da se prikaže pogojno oblikovanje. Formula v polju Formula je spremenjena, da prešteje, kolikokrat se A3 prikaže v stolpcu A: A.
Opombe
V Joejevem vprašanju je imel v razponu le 1700 računov. Nastavil sem 65536 celic s pogojnim oblikovanjem in vsaka celica primerja trenutno celico s 65536 drugimi celicami. V Excelu 2005 - z več vrsticami - bo težava še hujša. Tehnično bi lahko bila formula v prvem koraku:=COUNTIF($A$2:$A$1751,A2)>1
Pri kopiranju pogojne oblike v celoten stolpec bi lahko namesto tega izbrali samo vrstice s podatki, preden prilepite posebne formate.
Več
Druga težava, ki sem jo opisal po vprašanju, je, da stolpca resnično ne morete razvrstiti na podlagi pogojne oblike. Če morate te podatke razvrstiti tako, da so dvojniki na enem območju, sledite tem korakom. Najprej dodajte naslov B1, imenovan "Duplicate?". Vrsta to formulo v B2: =COUNTIF(A:A,A2)>1.
S kazalcem celice v B2 kliknite ročico za samodejno izpolnjevanje (mali kvadrat v spodnjem desnem kotu celice), da kopirate formulo do konca.
Zdaj lahko razvrstite po stolpcu B padajoče in A naraščajoče, da imate račune s težavami na vrhu obsega.
Ta rešitev predvideva, da želite označiti OBO podvojena računa, tako da boste lahko ročno ugotovili, katere želite izbrisati ali popraviti. Če ne želite označiti prvi nastop v dveh izvodih, ki jih lahko prilagodite formulo, da je: =COUNTIF($A$2:$A2,A2)>1. Pomembno je, da vpišete dolarske znake natančno tako, kot je prikazano. Tako bodo prikazane samo vse celice iz trenutne celice navzgor in iskane podvojene vnose.
Hvala Joeju iz Bostona za vprašanje!
