Sąlyginis formatavimas „Excel“: galutinis vadovas su pavyzdžiais

Sąlyginis formatavimas yra viena iš paprasčiausių, bet galingiausių „Excel“ skaičiuoklių funkcijų.

Kaip rodo pavadinimas, „Excel“ galite naudoti sąlyginį formatavimą, kai norite paryškinti langelius, atitinkančius nurodytą sąlygą.

Tai suteikia galimybę greitai pridėti vaizdinį analizės sluoksnį prie savo duomenų rinkinio. Galite sukurti šilumos žemėlapius, rodyti didėjančias/mažėjančias piktogramas, „Harvey“ burbulus ir daug daugiau naudodami sąlyginį „Excel“ formatavimą.

Sąlyginio formatavimo naudojimas „Excel“ (pavyzdžiai)

Šioje pamokoje parodysiu septynis nuostabius sąlyginio formato naudojimo „Excel“ pavyzdžius:

• Greitai nustatykite dublikatus naudodami „Excel“ sąlyginį formatavimą.
• Paryškinkite langelius, kurių vertė didesnė/mažesnė nei skaičius duomenų rinkinyje.
• Duomenų rinkinio viršutinių/apatinių 10 (arba 10%) reikšmių paryškinimas.
• Klaidų/ruošinių paryškinimas naudojant „Excel“ sąlyginį formatavimą.
• Šilumos žemėlapių kūrimas naudojant „Excel“ sąlyginį formatavimą.
• Pažymėkite kiekvieną N eilutę/stulpelį naudodami sąlyginį formatavimą.
• Ieškokite ir paryškinkite naudodami „Excel“ sąlyginį formatavimą.

1. Greitai nustatykite dublikatus

Sąlyginis „Excel“ formatavimas gali būti naudojamas duomenų rinkinio dublikatams identifikuoti.

Štai kaip galite tai padaryti:

• Pasirinkite duomenų rinkinį, kuriame norite paryškinti dublikatus.
• Eikite į Pradžia -> Sąlyginis formatavimas -> Ląstelių taisyklių paryškinimas -> Pasikartojančios vertės.
• Dialogo lange Pasikartojančios vertės įsitikinkite, kad kairėje esančiame išskleidžiamajame meniu pasirinktas Dublikatas. Naudodami dešinįjį išskleidžiamąjį meniu galite nurodyti taikytiną formatą. Yra keletas esamų formatų, kuriuos galite naudoti, arba nurodykite savo formatą naudodami pasirinktinio formato parinktį.
• Spustelėkite Gerai.

Tai akimirksniu paryškintų visas ląsteles, kurių pasirinktame duomenų rinkinyje yra dublikatas. Jūsų duomenų rinkinys gali būti viename stulpelyje, keliuose stulpeliuose arba netoliese esančiame langelių diapazone.

Taip pat žr: Galutinis „Excel“ dublikatų paieškos ir pašalinimo vadovas.

2. Pažymėkite langelius, kurių vertė didesnė/mažesnė nei skaičius

Galite naudoti sąlyginį formatavimą „Excel“, kad greitai paryškintumėte langelius, kuriuose yra didesnių/mažesnių reikšmių nei nurodyta vertė. Pvz., Paryškinti visas langelius, kurių pardavimo vertė yra mažesnė nei 100 milijonų, arba pažymėti langelius, kurių ženklai yra mažesni už ribą.

Štai žingsniai, kaip tai padaryti:

• Pasirinkite visą duomenų rinkinį.
• Eikite į pagrindinį puslapį -> Sąlyginis formatavimas -> Ląstelių taisyklių paryškinimas -> Didesnis nei… / Mažiau nei…
• Atsižvelgiant į pasirinktą parinktį (didesnę ar mažesnę), atsidarys dialogo langas. Tarkime, jūs pasirenkate parinktį „Didesnis nei“. Dialogo lange įveskite numerį kairėje esančiame lauke. Siekiama paryškinti langelius, kurių skaičius didesnis už šį nurodytą skaičių.

• Naudodami išskleidžiamąjį meniu dešinėje, nurodykite formatą, kuris turi būti taikomas langeliams, kurie atitinka sąlygą. Yra keletas esamų formatų, kuriuos galite naudoti, arba nurodykite savo formatą naudodami pasirinktinio formato parinktį.
• Spustelėkite Gerai.

Tai akimirksniu paryškintų visas duomenų rinkinio ląsteles, kurių vertės yra didesnės nei 5.Pastaba: Jei norite paryškinti reikšmes, didesnes nei 5, turėtumėte dar kartą taikyti sąlyginį formatavimą pagal kriterijų „Lygus“.

Tą patį procesą galima atlikti norint paryškinti langelius, kurių vertė yra mažesnė už nurodytas vertes.

3. Paryškinimas viršuje/apačioje 10 (arba 10%)

Sąlyginis „Excel“ formatavimas gali greitai nustatyti 10 geriausių elementų arba 10% geriausių duomenų rinkinio. Tai gali būti naudinga situacijose, kai norite greitai pamatyti geriausius kandidatus pagal balus ar didžiausias sandorio vertes pardavimo duomenyse.

Panašiai taip pat galite greitai nustatyti apatinius 10 elementų arba 10% apatinių duomenų rinkinio elementų.

Štai žingsniai, kaip tai padaryti:

• Pasirinkite visą duomenų rinkinį.
• Eikite į Pradžia -> Sąlyginis formatavimas -> Viršutinės / apatinės taisyklės -> 10 geriausių elementų (arba %) / 10 apatinių elementų (arba %).
• Atsižvelgiant į tai, ką pasirinksite, bus atidarytas dialogo langas. Tarkime, kad pasirinkote 10 geriausių elementų, tada jis atidarys dialogo langą, kaip parodyta žemiau:
• Naudodami išskleidžiamąjį meniu dešinėje, nurodykite formatą, kuris turi būti taikomas langeliams, kurie atitinka sąlygą. Yra keletas esamų formatų, kuriuos galite naudoti, arba nurodykite savo formatą naudodami pasirinktinio formato parinktį.
• Spustelėkite Gerai.

Tai akimirksniu paryškintų 10 geriausių pasirinkto duomenų rinkinio elementų. Atminkite, kad tai veikia tik ląstelėse, kuriose yra skaitinė reikšmė.

Be to, jei duomenų rinkinyje yra mažiau nei 10 langelių ir pasirenkate parinktis, kad paryškintumėte 10 geriausių elementų/10 apatinių elementų, tada visos ląstelės būtų paryškintos.

Štai keletas pavyzdžių, kaip veikia sąlyginis formatavimas:

4. Paryškinimo klaidos/tuščios vietos

Jei „Excel“ dirbate su daugybe skaitinių duomenų ir skaičiavimų, žinotumėte, kaip svarbu identifikuoti ir apdoroti langelius, kuriuose yra klaidų arba kurie yra tušti. Jei šios ląstelės bus naudojamos tolesniuose skaičiavimuose, tai gali sukelti klaidingų rezultatų.

Sąlyginis formatavimas „Excel“ gali padėti greitai nustatyti ir paryškinti langelius, kuriuose yra klaidų arba kurie yra tušti.

Tarkime, kad turime duomenų rinkinį, kaip parodyta žemiau:

Šiame duomenų rinkinyje yra tuščias langelis (A4) ir klaidos (A5 ir A6).

Štai veiksmai, skirti paryškinti tuščias arba klaidas turinčias ląsteles:

• Pasirinkite duomenų rinkinį, kuriame norite paryškinti tuščius langelius ir langelius su klaidomis.
• Eikite į pagrindinį puslapį -> Sąlyginis formatavimas -> Nauja taisyklė.
• Dialogo lange Nauja formatavimo taisyklė pasirinkite Naudoti formulę, kad nustatytumėte, kurias langelius formatuoti.
• Skiltyje „Taisyklės aprašo redagavimas“ esančiame lauke įveskite šią formulę:
  = ARBA (ISBLANK (A1), ISERROR (A1))
  • Aukščiau pateikta formulė tikrina visas ląsteles, ar nėra dviejų sąlygų - ar ji tuščia, ar ne, ar yra klaida, ar ne. Jei kuri nors iš sąlygų yra TRUE, ji grąžina TRUE.
• Nustatykite formatą, kurį norite taikyti tuščioms arba klaidoms skirtoms ląstelėms. Norėdami tai padaryti, spustelėkite mygtuką Formatuoti. Bus atidarytas dialogo langas „Formatuoti langelius“, kuriame galėsite nurodyti formatą.
• Spustelėkite Gerai.

Tai akimirksniu paryškintų visas ląsteles, kurios yra tuščios arba kuriose yra klaidų.

Pastaba: Jums nereikia naudoti viso diapazono A1: A7 formulėje su sąlyginiu formatu. Pirmiau minėta formulė naudoja tik A1. Kai taikote šią formulę visam diapazonui, „Excel“ tikrina po vieną langelį ir koreguoja nuorodą. Pavyzdžiui, tikrindamas A1, jis naudoja formulę = ARBA (ISBLANK (A1), ISERROR (A1)). Kai jis tikrina langelį A2, jis naudoja formulę = ARBA (ISBLANK (A2), ISERROR (A2)). Jis automatiškai koreguoja nuorodą (nes tai yra santykinės nuorodos), atsižvelgiant į tai, kuri ląstelė yra analizuojama. Taigi kiekvienai langeliui nereikia rašyti atskiros formulės. „Excel“ yra pakankamai protinga, kad pati pakeistų langelio nuorodą 🙂

Taip pat žiūrėkite: „IFERROR“ ir „ISERROR“ naudojimas „Excel“ klaidoms tvarkyti.

5. Šilumos žemėlapių kūrimas

Šilumos žemėlapis yra vaizdinis duomenų atvaizdavimas, kai spalva atspindi langelio vertę. Pvz., Galite sukurti šilumos žemėlapį, kuriame ląstelė, kurios didžiausia reikšmė yra nuspalvinta žalia spalva, o keičiantis raudonai, keičiasi.

Kažkas, kaip parodyta žemiau:

Aukščiau pateikto duomenų rinkinio reikšmės yra nuo 1 iki 100. Ląstelės paryškintos pagal joje esančią vertę. 100 gauna žalią, 1 - raudoną.

Štai žingsniai, kaip sukurti šilumos žemėlapius naudojant sąlyginį „Excel“ formatavimą.

• Pasirinkite duomenų rinkinį.
• Eikite į Pradžia -> Sąlyginis formatavimas -> Spalvų skalės ir pasirinkite vieną iš spalvų schemų.

Kai tik spustelėsite šilumos žemėlapio piktogramą, duomenų rinkinys bus pritaikytas formatavimui. Galite pasirinkti kelis spalvų gradientus. Jei nesate patenkinti esamomis spalvų parinktimis, galite pasirinkti daugiau taisyklių ir nurodyti norimą spalvą.

Pastaba: Panašiu būdu taip pat galite taikyti „Data Bard“ ir „Icon“ rinkinius.

6. Pažymėkite kiekvieną kitą eilutę/stulpelį

Galbūt norėsite paryškinti alternatyvias eilutes, kad padidintumėte duomenų skaitomumą.

Jie vadinami zebro linijomis ir gali būti ypač naudingi spausdinant duomenis.

Dabar yra du būdai, kaip sukurti šias zebrines linijas. Greičiausias būdas yra konvertuoti lentelės duomenis į „Excel“ lentelę. Jis automatiškai pritaikė spalvą kitoms eilėms. Daugiau apie tai galite paskaityti čia.

Kitas būdas yra naudoti sąlyginį formatavimą.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:

Štai veiksmai, skirti paryškinti alternatyvias eilutes naudojant sąlyginį „Excel“ formatavimą.

• Pasirinkite duomenų rinkinį. Anksčiau pateiktame pavyzdyje pasirinkite A2: C13 (be antraštės). Jei taip pat norite įtraukti antraštę, tada pasirinkite visą duomenų rinkinį.
• Atidarykite dialogo langą Sąlyginis formatavimas (Pagrindinis-> Sąlyginis formatavimas-> Nauja taisyklė) [Spartusis klavišas - Alt + O + D].
• Dialogo lange pasirinkite dialogo langą „Naudokite formulę, kad nustatytumėte, kurias langelius formatuoti“.
• Skiltyje „Taisyklės aprašo redagavimas“ esančiame lauke įveskite šią formulę:
  = ISODD (ROW ())

• Aukščiau pateikta formulė patikrina visas ląsteles ir, jei ląstelės eilutės numeris yra nelyginis, ji grąžina TRUE. Nurodytas sąlyginis formatas bus taikomas visoms ląstelėms, kurios pateikia TRUE.
• Nustatykite formatą, kurį norite taikyti tuščioms arba klaidoms skirtoms ląstelėms. Norėdami tai padaryti, spustelėkite mygtuką Formatuoti. Bus atidarytas dialogo langas „Formatuoti langelius“, kuriame galėsite nurodyti formatą.
• Spustelėkite Gerai.

Viskas! Alternatyvios duomenų rinkinio eilutės bus paryškintos.

Daugeliu atvejų galite naudoti tą pačią techniką. Viskas, ką jums reikia padaryti, tai naudoti atitinkamą formulę sąlyginiame formatavime. Štai keletas pavyzdžių:

• Pažymėkite alternatyvias lygias eilutes: = ISEVEN (ROW ())
• Pažymėkite alternatyvias pridėjimo eilutes: = ISODD (ROW ())
• Paryškinkite kas 3 eilutę: = MOD (ROW (), 3) = 0

7. Ieškokite ir paryškinkite duomenis naudodami sąlyginį formatavimą

Tai šiek tiek pažengęs sąlyginio formatavimo naudojimas. Tai atrodytų kaip „Excel“ roko žvaigždė.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, su produktų pavadinimu, pardavimų atstovu ir geografija. Idėja yra įvesti eilutę į langelį C2, ir jei ji atitinka bet kurios (-ių) langelio (-ių) duomenis, tai turėtų būti paryškinta. Kažkas, kaip parodyta žemiau:

Norėdami sukurti šią paieškos ir paryškinimo funkciją, atlikite toliau nurodytus veiksmus.

• Pasirinkite duomenų rinkinį.
• Eikite į pagrindinį puslapį -> Sąlyginis formatavimas -> Nauja taisyklė (Spartusis klavišas - Alt + O + D).
• Dialogo lange Nauja formatavimo taisyklė pasirinkite parinktį „Naudokite formulę, kad nustatytumėte, kurias langelius reikia formatuoti“.
• Skiltyje „Taisyklės aprašo redagavimas“ esančiame lauke įveskite šią formulę:
  = IR ($ C $ 2 ”), $ C $ 2 = B5)
• Nustatykite formatą, kurį norite taikyti tuščioms ląstelėms ar klaidoms. Norėdami tai padaryti, spustelėkite mygtuką Formatuoti. Bus atidarytas dialogo langas „Formatuoti langelius“, kuriame galėsite nurodyti formatą.
• Spustelėkite Gerai.

Viskas! Dabar, kai įvesite bet ką į langelį C2 ir paspausite klavišą Enter, jis paryškins visas atitinkančias ląsteles.

Kaip tai veikia?

Sąlyginio formatavimo formulė įvertina visas duomenų rinkinio ląsteles. Tarkime, įvedate Japoniją į langelį C2. Dabar „Excel“ įvertintų kiekvienos ląstelės formulę.

Formulė grąžintų langelį TRUE, kai bus įvykdytos dvi sąlygos:

• Langelis C2 nėra tuščias.
• C2 langelio turinys tiksliai atitinka duomenų rinkinio langelio turinį.

Taigi visos ląstelės, kuriose yra tekstas Japonija, paryškinamos.

Atsisiųskite pavyzdinį failą

Galite naudoti tą pačią logiką, kad sukurtumėte tokius variantus:

• Pažymėkite visą eilutę, o ne langelį.
• Paryškinkite net tada, kai yra dalinė atitiktis.
• Rašydami pažymėkite langelius/eilutes (dinaminis) [Jums patiks šis triukas :)].

Kaip pašalinti sąlyginį formatavimą „Excel“

Pritaikius sąlyginį formatavimą lieka, nebent pašalinsite jį rankiniu būdu. Kaip geriausia praktika, sąlyginis formatavimas turi būti taikomas tik toms ląstelėms, kuriose jums to reikia.

Kadangi tai nepastovi, „Excel“ darbaknygė gali būti lėta.

Norėdami pašalinti sąlyginį formatavimą:

• Pasirinkite langelius, iš kurių norite pašalinti sąlyginį formatavimą.
• Eikite į Pradžia -> Sąlyginis formatavimas -> Išvalyti taisykles -> Išvalyti pasirinktų langelių taisykles.
  • Jei norite pašalinti sąlyginį formatavimą iš viso darbalapio, pasirinkite Išvalyti taisykles iš viso lapo.

Svarbūs dalykai, kuriuos reikia žinoti apie sąlyginį formatavimą „Excel“

• Sąlyginis formatavimas nepastoviu. Tai gali lemti lėtą darbo knygą. Naudokite tik tada, kai reikia.
• Kai kopijuojate įklijuotus langelius, kuriuose yra sąlyginis formatavimas, taip pat nukopijuojamas sąlyginis formatavimas.
• Jei tam pačiam langelių rinkiniui taikote kelias taisykles, visos taisyklės lieka aktyvios. Bet kokio sutapimo atveju pirmenybė teikiama paskutinei taisyklei. Tačiau galite pakeisti tvarką, pakeisdami tvarką dialogo lange Tvarkyti taisykles.