Eile õhtul Craig Crossmani raadiosaates Computer America tekkis Joost Bostonist küsimus:
Mul on arve numbrite veerg. Kuidas ma saan duplikaatide märkimiseks Exceli kasutada?
Soovitasin kasutada tingimuslikke vorminguid ja valemit COUNTIF. Siin on üksikasjad selle kohta, kuidas seda tööd teha.
Soovime seadistada tingliku vormingu kogu vahemikule, kuid lihtsam on seadistada vahemiku esimesele lahtrile tingimusvorming ja seejärel see tingimusvorming kopeerida. Meie puhul on lahtril A1 arve numbri pealkiri, nii et valin lahter A2 ja menüüst valiku Vorming> Tingimuslik vormindamine. Tingimusliku vormindamise dialoog algab esialgse rippmenüüga "Lahtri väärtus on". Kui puudutate selle kõrval olevat noolt, saate valida "Vormel on".
Pärast valemi "Vormel on" valimist muudab dialoogiboks välimust. "X ja y vahel" väljade asemel on nüüd üks valemikast. See valemikast on uskumatult võimas. Võite sisestada mis tahes valemi, mille võite unistada, kui see valem on TÕENE või VALE.
Meie puhul peame kasutama valemit COUNTIF. Lahtrisse sisestatav valem on
=COUNTIF(A:A,A2)>1
Inglise keeles öeldakse nii: "vaadake kogu veeru A vahemikku. Loendage, mitu selle vahemiku lahtrit on sama väärtusega kui A2-s. (On tõesti oluline, et valemi" A2 "osutab praegune lahter - lahter, milles seadistate tingimusliku vormindamise. Seega - kui teie andmed on veerus E ja seadistate esimese tingimusliku vormindamise veerus E5, oleks valem järgmine =COUNTIF(E:E,E5)>0). Seejärel võrreldakse, et näha, kas see arv on> 1. Ideaalis on ilma duplikaatideta loend alati 1 - kuna lahter A2 on vahemikus - peaksime veerust A leidma täpselt ühe lahtri, mis sisaldab sama väärtust kui A2.
Klõpsake nuppu Vorminda …
Nüüd on aeg valida ebameeldiv vorming. Selle lahtrite vormindamise dialoogi ülaosas on kolm vahelehte. Vahekaart Font on tavaliselt esimene, nii et võite valida rasvase ja punase fondi, kuid mulle meeldib midagi ebameeldivamat. Tavaliselt klõpsan vahekaarti Mustrid ja valin kas erepunase või erekollase. Valige värv ja seejärel klõpsake dialoogi Lahtrite vormindamine sulgemiseks nuppu OK.
Valitud vormingut näete kastis "Kasutatava vormingu eelvaade". Tingimusliku vormindamise dialoogi sulgemiseks klõpsake nuppu OK …
… ja midagi ei juhtu. Vau. Kui olete tingimusliku vormindamise seadistamine esimest korda, oleks siin tore saada tagasisidet, et see toimis. Kuid kui teil pole õnne, et lahtris A2 olev 1098 on mõne teise lahtri duplikaat, pole see tingimus tõsi ja tundub, et midagi ei juhtunud.
Peate tingimusliku vormingu A2-st kopeerima oma vahemiku teistesse lahtritesse. Kui kursori künnis on A2-s, tehke Redigeerimine> Kopeeri. Kogu veeru valimiseks vajutage klahvikombinatsiooni Ctrl + tühikuklahv. Tehke Muuda> Kleebi eriline. Klõpsake dialoogis Kleebi spetsiaalne nuppu Vormingud. Klõpsake nuppu OK.
See kopeerib tingimusliku vormingu kõikidesse veeru lahtritesse. Nüüd - lõpuks - näete mõnda lahtrit punase vorminguga, mis näitab, et teil on duplikaat.
Informatiivne on minna lahtrisse A3 ja vaadata koopia järel tingimuslikku vormingut. Tingimusliku vormingu kuvamiseks valige A3, vajutage od. Valemi lahtris Valem on muudetud, et arvutada, mitu korda A3 kuvatakse veerus A: A.
Märkused
Joe küsimuses oli tal vahemikus ainult 1700 arvet. Olen seadistanud tingimusliku vormindusega 65536 lahtrit ja iga lahter võrdleb praegust lahtrit 65536 muu lahtriga. Excelis 2005 - rohkemate ridadega - on probleem veelgi hullem. Tehniliselt oleks võinud esimeses etapis olla järgmine valem:=COUNTIF($A$2:$A$1751,A2)>1
Tingimusliku vormingu kogu veergu kopeerides oleksite võinud enne erivormingute kleepimise tegemist valida ainult andmetega read.
Veel
Teine küsimus, mida ma pärast küsimust kirjeldasin, on see, et te ei saa veergu tingimusliku vormingu alusel sorteerida. Kui peate need andmed sortima nii, et duplikaadid oleksid ühes piirkonnas, toimige järgmiselt. Esiteks lisage B1-le pealkiri "Duplicate?". Tüüp selle valemi B2: =COUNTIF(A:A,A2)>1.
Kui lahtrikursor asub B2-s, klõpsake valemi kopeerimiseks kogu vahemikus automaatse täitmise käepidemele (väike ruut lahtri paremas alanurgas).
Nüüd saate sortida veergude B järgi kahanevalt ja A kasvavalt, et probleemarved oleksid vahemiku ülaosas.
See lahendus eeldab, et soovite esile tõsta MÕLED duplikaadiarveid, et saaksite käsitsi aru saada, mida kustutada või parandada. Kui te ei soovi, et tähistada esimene esinemine duplikaadi, saate reguleerida valem: =COUNTIF($A$2:$A2,A2)>1. Oluline on sisestada dollarimärgid täpselt nii, nagu näidatud. See vaatab kõiki lahtrid ainult praegusest lahtrist üles, otsides duplikaatkirjeid.
Aitäh küsimuse eest Joe Bostonist!
