Kā salīdzināt divas kolonnas programmā Excel un noņemt dublikātus (izcelt, krāsot, pārvietot)

Šī raksta lasīšana prasīs apmēram 10 minūtes. Nākamo 5 minūšu laikā varat ērti salīdzināt divas slejas programmā Excel un noskaidrot, vai tajās nav dublikātu, dzēst vai izcelt krāsaini. Tātad, ir pienācis laiks!

Excel ir ļoti jaudīga un patiešām lieliska lietojumprogramma lielu datu apjomu izveidei un apstrādei. Ja jums ir vairākas darbgrāmatas ar datiem (vai tikai viena milzīga tabula), iespējams, vēlaties salīdzināt 2 kolonnas, atrast dublētās vērtības un pēc tam ar tām kaut ko darīt, piemēram, dzēst, iezīmēt vai notīrīt saturu . Kolonnas var atrasties vienā tabulā, būt blakus vai nebūt blakus, var atrasties uz 2 dažādām lapām vai pat dažādās grāmatās.

Iedomājieties, ka mums ir 2 kolonnas ar cilvēku vārdiem – 5 vārdi katrā kolonnā A un 3 vārdi kolonnā B. Jums ir jāsalīdzina nosaukumi šajās divās kolonnās un jāatrod dublikāti. Kā jūs saprotat, tie ir fiktīvi dati, kas ņemti tikai, piemēram. Reālās tabulās mums ir darīšana ar tūkstošiem vai pat desmitiem tūkstošu ierakstu.

A variants: abas kolonnas atrodas vienā lapā. Piemēram, kolonna A un kolonnu B.

B variants: Kolonnas atrodas uz dažādām lapām. Piemēram, kolonna A uz lapas Sheet2 un kolonnu A uz lapas Sheet3.

Programmā Excel 2013, 2010 un 2007 ir iebūvēts rīks Noņemt dublikātus (Noņemt dublikātus), taču šajā situācijā tas ir bezspēcīgs, jo nevar salīdzināt datus 2 kolonnās. Turklāt tas var noņemt tikai dublikātus. Nav citu iespēju, piemēram, izcelt vai mainīt krāsas. Un punkts!

Tālāk es jums parādīšu iespējamos veidus, kā salīdzināt divas slejas programmā Excel, kas ļaus jums atrast un noņemt ierakstu dublikātus.

• Salīdziniet 2 kolonnas un meklējiet dublikātus, izmantojot formulas
  
  • A iespēja: abas kolonnas atrodas vienā lapā
  • B iespēja: kolonnas atrodas dažādās lapās vai dažādās darbgrāmatās
• Atrasto dublikātu apstrāde
  
  • Rādīt tikai dublētās rindas kolonnā A
  • Mainiet krāsu vai iezīmējiet atrastos dublikātus
  • Noņemiet dublikātus no pirmās kolonnas

Salīdziniet 2 slejas programmā Excel un atrodiet ierakstu dublikātus, izmantojot formulas

A iespēja: abas kolonnas atrodas vienā lapā

1. Pirmajā tukšajā šūnā (mūsu piemērā tā ir šūna C1) mēs ierakstām šādu formulu:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Mūsu formulā A1 šī ir pirmā šūna pirmajā kolonnā, kuru mēs salīdzināsim. 1 USD и 10000 USD šīs ir otrās kolonnas pirmās un pēdējās šūnas adreses, ar kurām mēs veiksim salīdzināšanu. Ņemiet vērā absolūtās atsauces — pirms kolonnu burtiem un rindu numuriem ir dolāra zīme ($). Es izmantoju absolūtās atsauces, lai, kopējot formulas, šūnu adreses paliktu nemainīgas.

   Ja vēlaties kolonnā atrast dublikātus B, mainiet atsauces, lai formula izskatītos šādi:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Tā vietā "Tikai"Un"Dublikāts» Varat rakstīt savas etiķetes, piemēram, «Nav atrasts"Un"atrasts"vai atstājiet tikai"Dublikātsun otrās vērtības vietā ievadiet atstarpes rakstzīmi. Pēdējā gadījumā šūnas, kurām nav atrasti dublikāti, paliks tukšas, un, manuprāt, šāds datu attēlojums ir visērtākais turpmākai analīzei.

2. Tagad kopēsim mūsu formulu uz visām kolonnas šūnām C, līdz pat apakšējai rindai, kurā ir kolonnas dati A. Lai to izdarītu, pārvietojiet peles rādītāju uz šūnas apakšējo labo stūri C1, rādītājs būs melns krustojums, kā parādīts zemāk esošajā attēlā:Noklikšķiniet un turiet nospiestu peles kreiso pogu un velciet rāmja apmali uz leju, iezīmējot visas šūnas, kurās vēlaties ievietot formulu. Kad visas vajadzīgās šūnas ir atlasītas, atlaidiet peles pogu:

   

Padoms: Lielās tabulās formulas kopēšana būs ātrāka, ja izmantosit īsinājumtaustiņus. Iezīmējiet šūnu C1 un nospiediet Ctrl + C (lai kopētu formulu starpliktuvē), pēc tam noklikšķiniet uz Ctrl + Shift + beigas (lai atlasītu visas C kolonnas netukšās šūnas) un visbeidzot nospiediet Ctrl + V (lai ievietotu formulu visās atlasītajās šūnās).

3. Lieliski, tagad visas dublētās vērtības ir atzīmētas kā “Dublikāts":

B iespēja: divas kolonnas ir uz dažādām lapām (dažādās darbgrāmatās)

1. Darblapas pirmās tukšās kolonnas pirmajā šūnā Sheet2 (mūsu gadījumā tā ir B kolonna) ievadiet šādu formulu:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Šeit Sheet3 ir tās lapas nosaukums, kurā atrodas 2. kolonna, un $1: $10000 ir šūnu adreses no 1. līdz pēdējai šajā 2. kolonnā.

2. Kopējiet formulu visās kolonnas šūnās B (tāds pats kā A variants).
3. Mēs iegūstam šādu rezultātu:

Atrasto dublikātu apstrāde

Lieliski, mēs esam atraduši ierakstus pirmajā kolonnā, kas ir arī otrajā kolonnā. Tagad mums kaut kas ar viņiem jādara. Manuāla visu dublēto ierakstu pārbaude tabulā ir diezgan neefektīva un aizņem pārāk daudz laika. Ir labāki veidi.

Rādīt tikai dublētās rindas kolonnā A

Ja jūsu kolonnām nav galvenes, tās ir jāpievieno. Lai to izdarītu, novietojiet kursoru uz skaitļa, kas apzīmē pirmo rindiņu, un tas pārvērtīsies melnā bultiņā, kā parādīts attēlā zemāk:

Ar peles labo pogu noklikšķiniet un konteksta izvēlnē atlasiet Ievietošana (Ievietot):

Piešķiriet kolonnām nosaukumus, piemēram, "Vārds"Un"Dublikāts?» Pēc tam atveriet cilni Datums (Dati) un nospiediet filtrs (Filtrs):

Pēc tam noklikšķiniet uz mazās pelēkās bultiņas blakus "Dublikāts?« lai atvērtu filtra izvēlni; noņemiet atzīmi no visiem vienumiem šajā sarakstā, izņemot Dublikātsun nospiediet OK.

Tas arī viss, tagad jūs redzat tikai tos kolonnas elementus А, kas kolonnā tiek dublēti В. Mūsu treniņu tabulā ir tikai divas šādas šūnas, taču, kā jūs saprotat, praksē to būs daudz vairāk.

Lai vēlreiz parādītu visas kolonnas rindas А, kolonnā noklikšķiniet uz filtra simbola В, kas tagad izskatās kā piltuve ar nelielu bultiņu, un atlasiet Izvēlieties visus (Izvēlēties visus). Vai arī varat darīt to pašu, izmantojot lenti, noklikšķinot Datums (Dati) > Atlasīt un filtrēt (Kārtot un filtrēt) > skaidrs (Notīrīt), kā parādīts zemāk esošajā ekrānuzņēmumā:

Mainiet krāsu vai iezīmējiet atrastos dublikātus

Ja piezīmes "Dublikāts"Jūsu mērķiem nepietiek, un vēlaties atzīmēt dublētās šūnas ar citu fonta krāsu, aizpildījuma krāsu vai kādu citu metodi…

Šādā gadījumā filtrējiet dublikātus, kā parādīts iepriekš, atlasiet visas filtrētās šūnas un noklikšķiniet uz Ctrl + 1lai atvērtu dialoglodziņu Formatēt šūnas (šūnas formāts). Piemēram, mainīsim šūnu aizpildījuma krāsu rindās ar dublikātiem uz spilgti dzeltenu. Protams, ar rīku var mainīt aizpildījuma krāsu Aizpildīt (Aizpildījuma krāsa) cilne Sākumlapa (Mājas), bet dialoglodziņa priekšrocība Formatēt šūnas (Šūnas formāts), jo varat vienlaikus konfigurēt visas formatēšanas opcijas.

Tagad jūs noteikti nepalaidīsit garām nevienu šūnu ar dublikātiem:

Vērtību dublikātu noņemšana no pirmās kolonnas

Filtrējiet tabulu tā, lai tiktu rādītas tikai šūnas ar dublētām vērtībām, un atlasiet šīs šūnas.

Ja abas salīdzināmās kolonnas atrodas dažādās lapās, tas ir, dažādās tabulās ar peles labo pogu noklikšķiniet uz atlasītā diapazona un atlasiet Dzēst rindu (Noņemt rindu):

prese OKkad programmā Excel tiek lūgts apstiprināt, ka tiešām vēlaties dzēst visu lapas rindu, un pēc tam notīrīt filtru. Kā redzat, paliek tikai rindas ar unikālām vērtībām:

Ja vienā lapā ir 2 kolonnas, tuvu viens otram (blakus) vai ne tuvu viens otram (nav blakus), tad dublikātu noņemšanas process būs nedaudz sarežģītāks. Mēs nevaram noņemt visu rindu ar dublētām vērtībām, jo ​​tādējādi tiks noņemtas šūnas arī no otrās kolonnas. Tātad, lai kolonnā atstātu tikai unikālus ierakstus А, dari šo:

1. Filtrējiet tabulu, lai parādītu tikai dublētās vērtības, un atlasiet šīs šūnas. Ar peles labo pogu noklikšķiniet uz tiem un konteksta izvēlnē atlasiet Notīrīt saturu (skaidrs saturs).
2. Notīriet filtru.
3. Atlasiet visas kolonnas šūnas А, sākot no šūnas A1 līdz apakšai, kurā ir dati.
4. Noklikšķiniet Datums (Dati) un nospiediet Kārtot no A līdz Z (Kārtot no A līdz Z). Atvērtajā dialoglodziņā atlasiet Turpināt ar pašreizējo atlasi (Kārtot norādītajā atlasē) un noklikšķiniet uz pogas melns (šķirošana):
5. Izdzēsiet kolonnu ar formulu, tā jums vairs nebūs vajadzīga, turpmāk jums ir tikai unikālas vērtības.
6. Tas ir viss, tagad kolonna А satur tikai unikālus datus, kas nav kolonnā В:

Kā redzat, dublikātu noņemšana no divām Excel kolonnām, izmantojot formulas, nav tik sarežģīta.