Kai dirbate su dideliais kiekiais duomenų, nesunku pamiršti duomenis ir matyti, kad jie kelis kartus rodomi lentelėje. Kai kurie dublikatai dedami tyčia, o kiti yra klaidos. Kad ir kaip būtų, galbūt norėsite, kad šie dublikatai būtų automatiškai paryškinti.
Rasti pasikartojančius langelius mažoje skaičiuoklėje lengva, tačiau dirbant su dideliais sudėtingais duomenų rinkiniais gali būti gana sunku tai padaryti rankiniu būdu. Laimei, programoje „Excel“ yra integruotų įrankių, leidžiančių paryškinti pasikartojančias vertes. Šioje pamokoje parodysime, kaip paryškinti pasikartojančius duomenis naudojant sąlyginio formatavimo funkciją programoje „Excel“.
Pažymėkite dublikatus su sąlyginiu formatavimu programoje „Excel“. Paprastai galbūt norėsite rasti dublikatus programoje „Excel“, nes dažniausiai dublikatai ten atsiranda per klaidą ir turėtų būti ištrinti arba dublikatai yra svarbūs analizei ir turi būti pažymėti programoje „Excel“.
Yra du būdai, kaip rasti pasikartojančias vertes naudojant sąlyginį formatavimą programoje „Excel“. Jie yra:
- Pažymėkite dublikatus naudodami pasikartojančios vertės taisyklę
- Pažymėkite dublikatus naudodami „Excel“ tinkintą formulę (COUNTIF ir COUNTIFS)
Pažymėkite dublikatus naudojant pasikartojančios vertės taisyklę
Tarkime, kad turime šį duomenų rinkinį:
Pirmiausia pasirinkite langelių diapazoną, kuriame yra pasikartojančių reikšmių. Tada eikite į skirtuką „Pagrindinis“, juostelės skiltyje „Stiliai“ spustelėkite „Sąlyginis formatavimas“. Išskleidžiamajame meniu perkelkite žymeklį ant pirmosios parinkties „Paryškinti langelio taisykles“ ir vėl bus rodomas taisyklių sąrašas iššokančiame laukelyje. Čia pasirinkite parinktį „Dubliuoti reikšmes“.
Kai spustelėsite pasikartojančias vertes, pasirodys dialogo langas Duplicate Values. Čia galite pasirinkti pasikartojančių reikšmių formatavimo tipą. Galite pasirinkti iš spalvų, kad užpildytumėte tik langelius, tik šriftui, kaip kraštinę arba pasirinktinį formatą, jei norite. Tada spustelėkite „Gerai“, kad uždarytumėte dialogo langą.
Čia kaip pavyzdį pasirenkame „Žalias užpildymas tamsiai žaliu tekstu“.
Kai pasirinksite formatavimo tipą, jis paryškins visas pasikartojančias vertes pasirinktame diapazone, kaip parodyta toliau.
Paryškinti Dubliuojasi Udainuoti COUNTIF formulę
Kitas būdas paryškinti pasikartojančias reikšmes yra sąlyginio formatavimo naudojimas naudojant paprastą COUNTIF formulę viename stulpelyje arba keliuose stulpeliuose.
Pasirinkite duomenų diapazoną, kuriame norite paryškinti dublikatus. Tada skirtuke „Pagrindinis“ spustelėkite parinktį „Sąlyginis formatavimas“. Išskleidžiamajame meniu spustelėkite parinktį „Nauja taisyklė“.
Tai atvers dialogo langą Nauja formatavimo taisyklė.
Dialogo lange Nauja formatavimo taisyklė pasirinkite parinktį „Naudoti formulę, kad nustatytumėte, kurias langelius formatuoti“, esančią sąrašo laukelyje Pasirinkti taisyklės tipą, tada įveskite šią COUNTIF formulę, kad suskaičiuotumėte dublikatus.
=COUNTIF($A$1:$C$11,A1)>1
Tada spustelėkite mygtuką „Formatas“, kad patektumėte į dialogo langą Format Cells. Dialogo lange Format Cells galite pasirinkti užpildymo spalvą iš spalvų paletės, kad paryškintumėte langelius, tada spustelėkite „Gerai“. Norėdami formatuoti dublikatus, pasirenkame mėlyną užpildymo spalvą.
Tada dar kartą spustelėkite „Gerai“, kad uždarytumėte dialogo langą. Formulė paryškins visas langelių reikšmes, kurios rodomos daugiau nei vieną kartą.
Visada įveskite viršutinio kairiojo langelio formulę pasirinktame diapazone (A1:C11). „Excel“ automatiškai nukopijuoja formulę į kitus langelius.
Taip pat galite apibrėžti taisykles, kaip norite. Pavyzdžiui, jei norite rasti reikšmes, kurios lentelėje rodomos tik du kartus, įveskite šią formulę (dialogo lange Nauja formatavimo taisyklė):
=COUNTIF($A$1:$C$11,A1)=2Rezultatas:
Kartais galbūt norėsite matyti tik dublikatus ir išfiltruoti unikalias vertes. Norėdami tai padaryti, pasirinkite diapazoną, eikite į skirtuką Pagrindinis, spustelėkite parinktį „Rūšiuoti ir filtruoti“ viršutiniame dešiniajame „Excel“ kampe ir pasirinkite parinktį „Filtras“.
Pirmajame kiekvieno stulpelio langelyje bus rodomas išskleidžiamasis meniu, kuriame galėsite apibrėžti filtro kriterijų. Spustelėkite išskleidžiamąjį meniu pirmame stulpelio langelyje ir pasirinkite „Filtruoti pagal spalvą“. Tada pasirinkite mėlyną spalvą.
Dabar matysite tik paryškintas ląsteles ir galėsite su jomis daryti ką tik norite.
Raskite ir pažymėkite pasikartojančias eilutes programoje „Excel“, naudodami COUNTIFS formulę
Jei norite rasti ir paryškinti pasikartojančias eilutes programoje „Excel“, naudokite COUNTIFS, o ne COUNTIF.
Pasirinkite diapazoną, eikite į skirtuką „Pagrindinis“ ir „Stilių“ grupėje spustelėkite „Sąlyginis formatavimas“. Išskleidžiamajame meniu spustelėkite parinktį „Nauja taisyklė“.
Dialogo lange Nauja formatavimo taisyklė pasirinkite parinktį „Naudoti formulę, kad nustatytumėte, kurias langelius formatuoti“ sąrašo laukelyje Pasirinkti taisyklės tipą, tada įveskite toliau pateiktą COUNTIFS formulę:
=COUNTIFS($A$1:$A$20,$A1,$B$1:$B$20,$B1,$C$1:$C$20, $C1)>1Aukščiau pateiktoje formulėje diapazonas A1:A20 reiškia A stulpelį, B1:B20 reiškia B stulpelį, o C1:C20 reiškia C stulpelį. Formulė skaičiuoja eilučių skaičių pagal kelis kriterijus (A1, B2 ir C1). .
Tada spustelėkite mygtuką „Formatuoti“, kad pasirinktumėte formatavimo stilių ir spustelėkite „Gerai“.
Dabar „Excel“ paryškina tik pasikartojančias eilutes, kaip parodyta toliau.
Viskas.