Vakar vakarā Kreiga Krosmana radio Amerikas datorā Džo no Bostonas bija jautājums:
Man ir rēķina numuru kolonna. Kā es varu izmantot programmu Excel, lai atzīmētu dublikātus?
Es ierosināju izmantot nosacītos formātus un formulu COUNTIF. Šeit ir informācija par to, kā padarīt šo darbu.
Mēs vēlamies iestatīt nosacītu formatējumu visam diapazonam, taču ir vieglāk iestatīt nosacīto formātu pirmajai šūnai diapazonā un pēc tam kopēt šo nosacīto formātu. Mūsu gadījumā šūnā A1 ir rēķina numura virsraksts, tāpēc es atlasīšu šūnu A2 un izvēlnē atlasīšu Formatēt> Nosacītais formatējums. Dialogs Nosacījuma formatēšana sākas ar sākotnējo nolaižamo izvēlni “Šūnas vērtība ir”. Ja pieskaraties bultiņai blakus, varat izvēlēties “Formula ir”.
Pēc izvēles "Formula ir" dialoglodziņš maina izskatu. "Starp x un y" lodziņu vietā tagad ir viena formulas rūtiņa. Šis formulas lodziņš ir neticami spēcīgs. Jūs varat ierakstīt jebkuru formulu, par kuru varat sapņot, ja vien šī formula tiks novērtēta kā PATIESA vai PATIESA.
Mūsu gadījumā mums jāizmanto formula COUNTIF. Formula, kas jāievada lodziņā, ir
=COUNTIF(A:A,A2)>1
Angļu valodā tas saka: "Izskatiet visu A slejas diapazonu. Saskaitiet, cik daudz šūnu šajā diapazonā ir tāda pati vērtība kā A2. (Ir ļoti svarīgi, lai formulas" A2 "norādītu uz pašreizējā šūna - šūna, kurā iestatāt nosacīto formatējumu. Tātad - ja jūsu dati atrodas E slejā un pirmo nosacīto formatēšanu iestatāt E5, formula būtu šāda =COUNTIF(E:E,E5)>0). Tad mēs salīdzinām, lai redzētu, vai šis skaitlis ir ir> 1. Ideālā gadījumā bez dublikātiem skaits vienmēr būs 1 - jo šūna A2 ir diapazonā - mums A kolonnā jāatrod tieši viena šūna, kurā ir tāda pati vērtība kā A2.
Noklikšķiniet uz pogas Formatēt …
Tagad ir pienācis laiks izvēlēties nepatīkamu formātu. Šūna Formatēt šūnas augšpusē ir trīs cilnes. Cilne Fonts parasti ir pirmā, tāpēc jūs varat izvēlēties treknu, sarkanu fontu, bet man patīk kaut kas nepatīkamāks. Es parasti noklikšķinu uz cilnes Patterns un izvēlos vai nu spilgti sarkanu, vai spilgti dzeltenu. Izvēlieties krāsu un pēc tam noklikšķiniet uz Labi, lai aizvērtu Formatēt šūnas.
Atlasītais formāts tiks parādīts lodziņā “Izmantojamā formāta priekšskatījums”. Noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu Nosacītā formatēšana …
… un nekas nenotiek. Oho. Ja jūs pirmo reizi iestatāt nosacītu formatēšanu, būtu ļoti patīkami šeit saņemt atsauksmes, ka tas darbojas. Bet, ja vien jums nav paveicies, ka 1098 šūnā A2 ir kādas citas šūnas dublikāts, nosacījums nav patiess, un izskatās, ka nekas nav noticis.
Jums ir jākopē nosacītais formatējums no A2 uz citām diapazona šūnām. Kad kursora palodze atrodas A2, veiciet Rediģēt> Kopēt. Nospiediet Ctrl + atstarpes taustiņš, lai atlasītu visu kolonnu. Veiciet rediģēšanu> Īpaša ielīmēšana. Dialoglodziņā Īpašā ielīmēšana noklikšķiniet uz Formāti. Noklikšķiniet uz Labi.
Tas kopēs nosacīto formatējumu visās kolonnas šūnās. Tagad - beidzot - jūs redzat dažas šūnas ar sarkanu formatējumu, kas norāda, ka jums ir dublikāts.
Ir informatīvi doties uz A3 šūnu un apskatīt nosacīto formātu pēc kopijas. Atlasiet A3, nospiediet od, lai atvērtu nosacīto formatējumu. Formula lodziņā Formula ir mainīta, lai saskaitītu, cik reizes A3 parādās kolonnā A: A.
Piezīmes
Pēc Džo jautājuma viņam diapazonā bija tikai 1700 rēķini. Esmu izveidojis 65536 šūnas ar nosacītu formatējumu, un katra šūna salīdzina pašreizējo šūnu ar 65536 citām šūnām. Programmā Excel 2005 - ar vairākām rindām - problēma būs vēl sliktāka. Tehniski formula pirmajā solī varēja būt:=COUNTIF($A$2:$A$1751,A2)>1
Turklāt, kopējot nosacīto formātu visā kolonnā, pirms īpašo formātu ielīmēšanas jūs varētu izvēlēties tikai rindas ar datiem.
Vairāk
Cits jautājums, kuru es aprakstīju pēc jautājuma, ir tāds, ka jūs patiešām nevarat kārtot kolonnu, pamatojoties uz nosacītu formātu. Ja jums šie dati ir jāšķiro tā, lai dublikāti būtu vienā apgabalā, rīkojieties šādi. Vispirms pievienojiet virsrakstu B1 ar nosaukumu "Kopēt?". Type šo formulu B2: =COUNTIF(A:A,A2)>1.
Kad šūnas rādītājs atrodas B2, noklikšķiniet uz automātiskās aizpildīšanas roktura (mazais kvadrāts šūnas apakšējā labajā stūrī), lai kopētu formulu visā diapazonā.
Tagad jūs varat kārtot pēc B slejas dilstošā un A augošā secībā, lai problēmu rēķini būtu diapazona augšdaļā.
Šis risinājums pieņem, ka vēlaties izcelt abus rēķinu dublikātus, lai jūs varētu manuāli saprast, kurus izdzēst vai labot. Ja jūs nevēlaties, lai atzīmētu pirmais gadījums par dublikātu, varat pielāgot formulu, lai būtu: =COUNTIF($A$2:$A2,A2)>1. Ir svarīgi ievadīt dolāra zīmes tieši tā, kā parādīts. Tas apskatīs visas šūnas tikai no pašreizējās šūnas uz augšu, meklējot ierakstu dublikātus.
Paldies Džo no Bostonas par jautājumu!
