Strādājot ar lielu datu daudzumu, ir viegli pazaudēt datus un redzēt, ka tie tiek parādīti tabulā vairākas reizes. Daži dublikāti tiek ievietoti apzināti, bet citi ir kļūdaini. Jebkurā gadījumā, iespējams, vēlēsities, lai šie dublikāti tiktu automātiski izcelti.
Šūnu dublikātu atrašana nelielā izklājlapā ir vienkārša, taču, strādājot ar lielām, sarežģītām datu kopām, to var būt diezgan grūti izdarīt manuāli. Par laimi programmā Excel ir iebūvēti rīki, kas ļauj izcelt vērtību dublikātus. Šajā apmācībā mēs parādīsim, kā izcelt datu dublikātus, izmantojot nosacītās formatēšanas funkciju programmā Excel.
Programmā Excel iezīmējiet dublikātus ar nosacījumu formatējumu. Parasti, iespējams, vēlēsities atrast dublikātus programmā Excel, jo vairāk nekā bieži dublikāti tur ir kļūdas dēļ un tie ir jāizdzēš, vai arī dublikāti ir svarīgi analīzei un ir jāizceļ programmā Excel.
Ir divas metodes, kā programmā Excel atrast dublētās vērtības ar nosacījumu formatējumu. Viņi ir:
- Iezīmējiet dublikātus, izmantojot kārtulu Duplicate Value
- Iezīmējiet dublikātus, izmantojot Excel pielāgoto formulu (COUNTIF un COUNTIFS)
Iezīmējiet dublikātus, izmantojot dublikātu vērtību kārtulu
Pieņemsim, ka mums ir šāda datu kopa:
Vispirms atlasiet šūnu diapazonu, kurā ir dublētās vērtības. Pēc tam dodieties uz cilni Sākums, lentes sadaļā Stili noklikšķiniet uz Nosacījuma formatēšana. Nolaižamajā izvēlnē pārvietojiet kursoru virs pirmās opcijas “Izcelt šūnu noteikumus”, un tas atkal parādīs noteikumu sarakstu uznirstošajā lodziņā. Šeit izvēlieties opciju Dublēt vērtības.
Kad noklikšķināsit uz Dublētas vērtības, tiks parādīts dialoglodziņš Duplicate Values. Šeit varat izvēlēties dublēto vērtību formatējuma veidu. Varat izvēlēties kādu no krāsām, lai aizpildītu tikai šūnas, tikai fontam, kā apmali vai pielāgotu formātu, ja vēlaties. Pēc tam noklikšķiniet uz “OK”, lai aizvērtu dialoglodziņu.
Šeit mēs savā piemērā atlasām “Zaļais aizpildījums ar tumši zaļu tekstu”.
Kad esat atlasījis formatējuma veidu, tas iezīmēs visas dublētās vērtības atlasītajā diapazonā, kā parādīts tālāk.
Izcelt Dublikāti Udziedāt COUNTIF Formula
Vēl viena metode dublikātu vērtību izcelšanai ir nosacījuma formatējuma izmantošana ar vienkāršu COUNTIF formulu vienā kolonnā vai vairākās kolonnās.
Atlasiet datu diapazonu, kurā vēlaties izcelt dublikātus. Pēc tam cilnē Sākums un noklikšķiniet uz opcijas Nosacījuma formatēšana. Nolaižamajā izvēlnē noklikšķiniet uz opcijas Jauns noteikums.
Tiks atvērts dialoglodziņš Jauns formatēšanas kārtula.
Dialoglodziņā Jauna formatēšanas kārtula atlasiet opciju “Izmantot formulu, lai noteiktu, kuras šūnas formatēt” sarakstā Atlasīt kārtulas veidu, pēc tam ievadiet šo COUNTIF formulu, lai uzskaitītu dublikātus.
=COUNTIF($A$1:$C$11,A1)>1
Pēc tam noklikšķiniet uz pogas Formatēt, lai atvērtu dialoglodziņu Format Cells. Dialoglodziņā Šūnu formatēšana no krāsu paletes varat izvēlēties aizpildījuma krāsu, lai iezīmētu šūnas, un pēc tam noklikšķiniet uz Labi. Šeit mēs izvēlamies zilu aizpildījuma krāsu, lai formatētu dublikātus.
Pēc tam vēlreiz noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu. Formula iezīmēs visas šūnu vērtības, kas parādās vairāk nekā vienu reizi.
Vienmēr ievadiet formulu atlasītajā diapazonā (A1:C11) augšējā kreisajā šūnā. Programma Excel automātiski kopē formulu citās šūnās.
Varat arī definēt noteikumus, kā vien vēlaties. Piemēram, ja vēlaties atrast vērtības, kas tabulā parādās tikai divas reizes, tā vietā ievadiet šo formulu (dialoglodziņā Jauna formatēšanas kārtula):
=COUNTIF($A$1:$C$11,A1)=2Rezultāts:
Dažreiz jūs varētu vēlēties redzēt tikai dublikātus un filtrēt unikālas vērtības. Lai to izdarītu, atlasiet diapazonu, dodieties uz cilni Sākums, Excel augšējā labajā stūrī noklikšķiniet uz opcijas "Kārtot un filtrēt" un atlasiet opciju "Filtrs".
Katras kolonnas pirmajā šūnā tiks parādīta nolaižamā izvēlne, kurā varat definēt filtra kritēriju. Noklikšķiniet uz nolaižamās izvēlnes kolonnas pirmajā šūnā un atlasiet “Filtrēt pēc krāsas”. Pēc tam izvēlieties zilo krāsu.
Tagad jūs redzēsit tikai izceltās šūnas un varēsit ar tām darīt visu, ko vēlaties.
Atrodiet un iezīmējiet dublētās rindas programmā Excel, izmantojot COUNTIFS formulu
Ja vēlaties programmā Excel atrast un izcelt dublētās rindas, izmantojiet COUNTIFS, nevis COUNTIF.
Atlasiet diapazonu, dodieties uz cilni Sākums un grupā Stili noklikšķiniet uz Nosacījuma formatēšana. Nolaižamajā izvēlnē noklikšķiniet uz opcijas Jauns noteikums.
Dialoglodziņā Jauns formatēšanas noteikums atlasiet opciju “Izmantot formulu, lai noteiktu, kuras šūnas formatēt” sarakstā Atlasīt kārtulas veidu, pēc tam ievadiet tālāk norādīto COUNTIFS formulu.
= COUNTIFS($A$1:$A$20,$A1,$B$1:$B$20,$B1,$C$1:$C$20, $C1)>1Iepriekš minētajā formulā diapazons A1:A20 attiecas uz A kolonnu, B1:B20 attiecas uz kolonnu B un C1:C20 attiecas uz C kolonnu. Formula uzskaita rindu skaitu, pamatojoties uz vairākiem kritērijiem (A1, B2 un C1). .
Pēc tam noklikšķiniet uz pogas "Formatēt", lai atlasītu formatēšanas stilu, un noklikšķiniet uz "OK".
Tagad Excel izceļ tikai dublētās rindas, kā parādīts tālāk.
Tieši tā.