Praėjusį vakarą Craigo Crossmano „Computer America“ radijo laidoje Joe iš Bostono turėjo klausimą:
Turiu sąskaitos faktūros numerių stulpelį. Kaip galiu naudoti „Excel“ pažymėdamas dublikatus?
Aš pasiūliau naudoti sąlyginius formatus ir COUNTIF formulę. Čia pateikiama išsami informacija, kaip tą darbą atlikti.
Mes norime nustatyti sąlyginį formatavimą visam diapazonui, tačiau lengviau nustatyti sąlyginį formatą pirmajam diapazono langeliui ir tada nukopijuoti tą sąlyginį formatą. Mūsų atveju langelyje A1 yra sąskaitos faktūros numerio antraštė, todėl aš pasirenku langelį A2 ir iš meniu pasirinkite Formatas> Sąlyginis formatavimas. Sąlyginio formatavimo dialogas prasideda pradiniu išskleidžiamuoju meniu sakant „Langelio vertė yra“. Jei paliesite šalia esančią rodyklę, galite pasirinkti „Formulė yra“.
Pasirinkus „Formulė yra“, dialogo langas pakeičia išvaizdą. Vietoj laukelių „Tarp x ir y“ dabar yra vienas formulės langelis. Šis formulės langelis yra nepaprastai galingas. Galite įvesti bet kokią formulę, kurią galite išsvajoti, jei ta formulė bus vertinama kaip TIKRA arba IETIKRA.
Mūsų atveju turime naudoti formulę COUNTIF. Formulė, kurią reikia įvesti laukelyje, yra
=COUNTIF(A:A,A2)>1
Anglų kalba tai sako: „Peržiūrėkite visą A stulpelio diapazoną. Suskaičiuokite, kiek langelių tame diapazone yra tokia pati vertė, kokia yra A2. (Labai svarbu, kad formulės„ A2 “būtų nukreipta į dabartinė ląstelė - langelis, kuriame nustatote sąlyginį formatavimą. Taigi - jei jūsų duomenys yra E stulpelyje ir nustatote pirmąjį sąlyginį formatavimą E5, formulė būtų tokia =COUNTIF(E:E,E5)>0). Tada mes palyginsime, kad sužinotume, ar tai skaičiuojama yra> 1. Idealiu atveju, jei nėra dublikatų, skaičius visada bus 1 - nes langelis A2 yra diapazone - A stulpelyje turėtume rasti tiksliai vieną langelį, kuriame būtų tokia pati vertė kaip A2.
Spustelėkite mygtuką Formatuoti …
Dabar atėjo laikas pasirinkti nemalonų formatą. Šio langelių formatavimo dialogo lango viršuje yra trys skirtukai. Paprastai šriftas yra šriftas, todėl galite pasirinkti pusjuodį, raudoną šriftą, bet man patinka kažkas nemalonesnio. Dažniausiai spusteliu skirtuką „Raštai“ ir pasirenku ryškiai raudoną arba ryškiai geltoną. Pasirinkite spalvą, tada spustelėkite Gerai, kad uždarytumėte langelių formatavimo dialogą.
Pasirinktą formatą pamatysite laukelyje „Naudojamo formato peržiūra“. Spustelėkite Gerai, kad uždarytumėte sąlyginio formatavimo dialogo langą …
… ir nieko nevyksta. Oho. Jei pirmą kartą nustatote sąlyginį formatavimą, būtų labai malonu čia gauti atsiliepimų, kad tai pavyko. Bet nebent jums pasisekė, kad A2 langelyje esantis 1098 yra kitos langelio kopija, sąlyga nėra teisinga ir panašu, kad nieko neįvyko.
Turite nukopijuoti sąlyginį formatavimą iš A2 žemyn į kitas jūsų diapazono langelius. Kai žymeklio slenkstis yra A2, atlikite Redaguoti> Kopijuoti. Paspauskite „Ctrl“ + tarpo klavišą, kad pasirinktumėte visą stulpelį. Atlikite Redaguoti> Įklijuoti specialų. Dialogo lange Įklijuoti spustelėkite Formatai. Spustelėkite Gerai.
Tai nukopijuos sąlyginį formatavimą į visas stulpelio langelius. Dabar - pagaliau - pamatysite keletą langelių su raudonu formatu, nurodydami, kad turite dublikatą.
Informatyvu eiti į A3 langelį ir po kopijos pažvelgti į sąlyginį formatą. Pasirinkite A3, paspauskite od, kad būtų parodytas sąlyginis formatavimas. Formulės lauke Formulė yra pakeista, kad būtų galima suskaičiuoti, kiek kartų A3 pasirodo stulpelyje A: A.
Pastabos
Joe klausimu, jis turėjo tik 1700 sąskaitų faktūrų. Aš sukūriau 65536 langelius su sąlyginiu formatavimu ir kiekviena ląstelė lygina esamą langelį su kitomis 65536 ląstelėmis. „Excel 2005“ - su daugiau eilučių - problema bus dar blogesnė. Techniškai pirmojo žingsnio formulė galėjo būti:=COUNTIF($A$2:$A$1751,A2)>1
Be to, kopijuodami sąlyginį formatą į visą stulpelį, prieš atlikdami specialiųjų formatų įklijavimą, galėtumėte pasirinkti tik eilutes su duomenimis.
Daugiau
Kitas klausimas, kurį aprašiau po klausimo, yra tai, kad jūs tikrai negalite rūšiuoti stulpelio pagal sąlyginį formatą. Jei turite rūšiuoti šiuos duomenis taip, kad dublikatai būtų vienoje srityje, atlikite šiuos veiksmus. Pirmiausia prie B1 pridėkite antraštę „Kopijuoti?“. Įveskite šią formulę B2: =COUNTIF(A:A,A2)>1.
Kai langelio žymeklis yra B2, spustelėkite automatinio pildymo rankenėlę (mažas kvadratas apatiniame dešiniajame langelio kampe), kad nukopijuotumėte formulę iki galo.
Dabar galite rūšiuoti pagal B stulpelį mažėjančiu ir A didėjančiu būdu, kad probleminės sąskaitos faktūros būtų diapazono viršuje.
Šis sprendimas daro prielaidą, kad norite pažymėti Abi pasikartojančias sąskaitas faktūras, kad galėtumėte rankiniu būdu išsiaiškinti, kurią ištrinti ar ištaisyti. Jei nenorite pažymėti pirma grupė dublikato, galite pakoreguoti formulę turi būti: =COUNTIF($A$2:$A2,A2)>1. Svarbu įvesti dolerio ženklus tiksliai taip, kaip parodyta. Bus peržiūrimi visi langeliai tik iš dabartinės langelio į viršų, ieškant pasikartojančių įrašų.
Ačiū Joe iš Bostono už klausimą!
