Sąlyginis formatavimas Excel programoje – išsamiai su pavyzdžiais

Jei reikia vizualiai pateikti informaciją, labai patogu tai padaryti naudojant sąlyginį formatavimą. Šia paslauga besinaudojantis vartotojas sutaupo labai daug energijos ir laiko. Norint gauti reikiamą informaciją, pakanka greitai peržiūrėti failą.

Kaip atlikti sąlyginį formatavimą „Excel“.

„Sąlyginį formatavimą“ galite rasti pačiame pirmajame juostos skirtuke, eidami į skyrių „Stiliai“. 

Tada akimi reikia rasti rodyklės piktogramą šiek tiek dešinėje, perkelti žymeklį į ją. Tada atsidarys nustatymai, kuriuose galėsite lanksčiai konfigūruoti visus reikiamus parametrus. 

Tada turite pasirinkti atitinkamą operatorių, kad palygintumėte norimą kintamąjį su skaičiumi. Yra keturi palyginimo operatoriai – didesnis nei, mažesnis už, lygus ir tarp. Jie yra išvardyti taisyklių meniu. 

Toliau nurodėme skaičių seriją diapazone A1:A11.

Suformatuokime tinkamą duomenų rinkinį. Tada atidarome nustatymų meniu „Sąlyginis formatavimas“ ir nustatome reikiamus duomenų atrankos kriterijus. Pavyzdžiui, mūsų atveju pasirinksime kriterijų „Daugiau“. 

Atsidarys langas su parinkčių rinkiniu. Kairėje lango dalyje yra laukas, kuriame reikia nurodyti skaičių 15. Dešinėje yra nurodytas informacijos paryškinimo būdas, jei jis atitinka anksčiau nurodytą kriterijų. Rezultatas bus rodomas iš karto.

Tada užbaikite konfigūraciją mygtuku Gerai.

Kam skirtas sąlyginis formatavimas?

Iš dalies galima teigti, kad tai įrankis, kurį išmokus darbas su Excel niekada nebus toks pat. Taip yra todėl, kad tai labai palengvina gyvenimą. Užuot kiekvieną kartą rankiniu būdu nustatę langelio formatavimą, atitinkantį tam tikrą sąlygą, taip pat bandydami patys patikrinti, ar jis atitinka šį kriterijų, jums tereikia vieną kartą nustatyti nustatymus, o tada „Excel“ viską padarys pati.

Pavyzdžiui, galite padaryti, kad visi langeliai, kurių skaičius didesnis nei 100, taptų raudoni. Arba nustatykite, kiek dienų liko iki kito mokėjimo, ir tada žaliai nuspalvinkite langelius, kuriuose iki galutinio termino dar nėra pakankamai toli. 

Paimkime pavyzdį.

Šioje lentelėje rodomas sandėlyje esančios atsargos. Jame yra stulpeliai. kaip produktas, vidutiniai pardavimai (matuojami vienetais per savaitę), atsargos ir kiek savaičių liko šio produkto.

Be to, pirkimo vadybininko užduotis yra nustatyti tas pareigybes, kurias būtina papildyti. Norėdami tai padaryti, reikia pažvelgti į ketvirtą stulpelį iš kairės, kuriame fiksuojamos prekių atsargos pagal savaitę.

Tarkime, kad panikos priežasties nustatymo kriterijus yra trumpesnis nei 3 savaičių inventorius. Tai rodo, kad turime parengti užsakymą. Jei prekių atsargos yra mažesnės nei dvi savaitės, tai reiškia, kad reikia skubiai pateikti užsakymą. Jei lentelėje yra daug pozicijų, rankiniu būdu patikrinti, kiek savaičių liko, yra gana problematiška. Net jei naudositės paieška. O dabar pažiūrėkime, kaip atrodo lentelė, kurioje raudonai paryškintos ribotos prekės.

Ir iš tiesų, naršyti yra daug lengviau. 

Tiesa, šis pavyzdys yra edukacinis, kuris, lyginant su realiu paveikslu, kiek supaprastintas. O sutaupytos sekundės ir minutės reguliariai naudojant tokias lenteles virsta valandomis. Dabar užtenka pažvelgti į lentelę, kad suprastum, kurių prekių trūksta, o ne valandų valandas analizuoti kiekvieną langelį (jei tokių prekinių prekių yra tūkstančiai).

Jei yra „geltonų“ prekių, tuomet reikia pradėti jas pirkti. Jei atitinkama padėtis yra raudona, turite tai padaryti nedelsiant. 

Pagal kitos ląstelės vertę

Dabar pažvelkime į šį praktinį pavyzdį.

Tarkime, kad turime tokią lentelę ir susiduriame su užduotimi paryškinti eilutes, kuriose yra tam tikros reikšmės. 

Taigi, jei projektas vis dar vykdomas (ty dar nebaigtas, todėl pažymėtas raide „P“), tada jo foną turime padaryti raudoną. Užbaigti projektai pažymėti žalia spalva.

Mūsų veiksmų seka šioje situacijoje bus tokia:

1. Pasirinkite verčių diapazoną. 
2. Spustelėkite mygtuką „Sąlyginis formatavimas“ - „Sukurti taisyklę“.
3. Atkreipkite dėmesį, kad mūsų atveju formulę turite taikyti taisyklės forma. Toliau mes naudojame funkciją IFkad paryškintumėte atitinkančias linijas. 

Tada užpildykite eilutes, kaip parodyta šiame paveikslėlyje.

Svarbu! Stygos turi būti visiškai nurodytos. Jei kalbame apie ląstelę, tai šiuo atveju ji yra sumaišyta (su stulpelio fiksavimu).

Panašiai sukuriama taisyklė tiems darbo projektams, kurie iki šiol nebuvo užbaigti. 

Štai kaip atrodo mūsų kriterijai.

Ir galiausiai, mes turime tokią duomenų bazę.

Kelios sąlygos

Aiškumo dėlei paimkime diapazoną A1:A11 iš lentelės, kad parodytume, kaip realiame praktiniame pavyzdyje galite naudoti kelias sąlygas langelio formatavimui nustatyti.

Pačios sąlygos yra tokios: jei skaičius langelyje viršija reikšmę 6, tada jis paryškinamas raudonai. Jei jis yra žalias, tada spalva yra žalia. Ir galiausiai didžiausi skaičiai, daugiau nei 20, bus paryškinti geltonai. 

Yra keletas būdų, kaip sąlyginis formatavimas atliekamas pagal kelias taisykles.

1. 1 būdas. Mes pasirenkame savo diapazoną, po kurio sąlyginio formatavimo nustatymų meniu pasirinkite tokią langelio pasirinkimo taisyklę kaip "Daugiau". Skaičius 6 parašytas kairėje, o formatavimas nustatytas dešinėje. Mūsų atveju užpildas turėtų būti raudonas. Po to ciklas kartojamas du kartus, bet jau nustatomi kiti parametrai – atitinkamai daugiau nei 10 ir žalia spalva bei daugiau nei 20 ir geltona spalva. Jūs gausite tokį rezultatą.
   

   

2. 2 būdas. Eikite į pagrindinį „Excel“ įrankio „Sąlyginis formatavimas“ nustatymų meniu. Ten randame meniu „Sukurti taisyklę“ ir kairiuoju pelės mygtuku spustelėkite šį elementą.
   

   

    Tada pasirinkite elementą „Naudoti formulę…“ (paryškintą paveikslėlyje raudonu rėmeliu) ir nustatykite pirmąją sąlygą. Po to spustelėkite Gerai. Ir tada ciklas kartojamas tolesnėms sąlygoms taip pat, kaip aprašyta aukščiau, tačiau naudojama formulė.

   

Svarbu į tai atsižvelgti. Mūsų pavyzdyje kai kurios ląstelės atitinka kelis kriterijus vienu metu. „Excel“ išsprendžia šį konfliktą taip: pirmiausia taikoma aukščiau pateikta taisyklė. 

Ir čia yra ekrano kopija, kad būtų geriau suprasti.

Pavyzdžiui, mes turime skaičių 24. Jis atitinka visas tris sąlygas vienu metu. Tokiu atveju bus užpildymas, atitinkantis pirmąją sąlygą, nepaisant to, kad jis labiau patenka į trečiąją. Todėl, jei svarbu, kad skaičiai virš 20 būtų užpildyti geltona spalva, trečioji sąlyga turi būti į pirmą vietą. 

Sąlyginis datos formatavimas

Dabar pažiūrėkime, kaip veikia sąlyginis formatavimas su datomis. Turime tokį šaunų asortimentą.

Tokiu atveju turite nustatyti sąlyginio formatavimo taisyklę, pvz., „Data“.

Po to atsidaro dialogo langas su visu sąlygų rinkiniu. Išsamiai su jais galite susipažinti šioje ekrano kopijoje (jie išvardyti kairėje ekrano pusėje sąrašo pavidalu).

Mes tiesiog turime pasirinkti tinkamą ir spustelėti mygtuką „Gerai“.

Matome, kad tos datos, kurios nurodo paskutinę savaitę, kai buvo sudarytas šis diapazonas, buvo paryškintos raudonai. 

Naudojant formules

Standartinių sąlyginio formatavimo taisyklių rinkinys yra gana didelis. Tačiau situacijos yra skirtingos, ir standartinio sąrašo gali nepakakti. Tokiu atveju galite sukurti savo formatavimo taisyklę naudodami formulę. 

Norėdami tai padaryti, sąlyginio formatavimo meniu spustelėkite mygtuką „Sukurti taisyklę“ ir pasirinkite ekrano kopijoje rodomą elementą.

Eilutės (pagal langelio vertę)

Tarkime, kad turime paryškinti eilutę, kurioje yra langelis su tam tikra reikšme. Šiuo atveju turime atlikti tą pačią veiksmų seką, kaip ir aukščiau, bet eilutėms. Tai yra, galite pasirinkti, kuriam diapazonui turėtų būti taikomas sąlyginis formatavimas. 

Kaip sukurti taisyklę

Norėdami sukurti taisyklę, turite naudoti atitinkamą parinktį skiltyje „Sąlyginis formatavimas“. Pažvelkime į konkrečią veiksmų seką. 

Pirmiausia skirtuko „Pagrindinis“ juostoje turite rasti elementą „Sąlyginis formatavimas“. Jį galima atpažinti iš būdingų įvairiaspalvių skirtingų dydžių langelių, kurių šalia yra paveikslėlis su perbrauktu lygybės ženklu. 

Yra mygtukas „Sukurti taisyklę“. Taip pat galite spustelėti mygtuką „Tvarkyti taisykles“, po kurio atsidarys dialogo langas, kuriame taip pat galite sukurti taisyklę spustelėdami atitinkamą mygtuką viršutiniame kairiajame kampe. 

Šiame lange taip pat galite matyti tas taisykles, kurios jau buvo pritaikytos.

Ląstelių pasirinkimo taisyklės

Gerai, mes tiek daug kalbame apie tai, kaip sukurti taisykles, bet kas tai yra? Ląstelių pasirinkimo taisyklė nurodo funkcijas, į kurias atsižvelgia programa, nuspręsdama, kaip formatuoti joms tinkančius langelius. Pavyzdžiui, sąlygos gali būti didesnės už tam tikrą skaičių, mažesnės už tam tikrą skaičių, formulė ir pan. Jų yra visas rinkinys. Galite susipažinti su jais ir praktikuoti jų naudojimą „smėlio dėžėje“. 

Visų langelių formatavimas pagal jų reikšmes

Asmuo gali savarankiškai nustatyti diapazoną: visą eilutę, visą dokumentą arba vieną langelį. Programa gali sutelkti dėmesį tik į vienos ląstelės reikšmę. Tai suteikia procesui lankstumo. 

Formatuokite tik unikalius arba pasikartojančius langelius

Yra daug taisyklių, tiesiogiai prijungtų prie programos. Su jais galite susipažinti meniu „Sąlyginis formatavimas“. Visų pirma, norint, kad formatavimas būtų taikomas tik pasikartojančioms reikšmėms arba tik unikalioms, yra speciali parinktis - „Dubliuoti reikšmes“.

Pasirinkus šį elementą, atsivers langas su nustatymais, kuriame galima pasirinkti ir priešingą variantą – pasirinkti tik unikalias reikšmes. 

Formatuokite vertes žemiau ir virš vidurkio

Norėdami formatuoti reikšmes, kurios yra mažesnės arba didesnės nei vidutinės, tame pačiame meniu taip pat yra speciali parinktis. Tačiau reikia pasirinkti kitą submeniu – „Pirmosios ir paskutinės reikšmių pasirinkimo taisyklės“. 

Formatuojama tik pirmoji ir paskutinė reikšmės

Tame pačiame submeniu galima išskirti tik pirmąją ir paskutinę reikšmes specialia spalva, šriftu ir kitais formatavimo būdais. Pagal standartą yra galimybė pasirinkti pirmąjį ir paskutinį dešimt elementų, taip pat 10% viso langelių skaičiaus, įtraukto į šį diapazoną. Tačiau vartotojas gali savarankiškai nustatyti, kiek langelių jam reikia pasirinkti.

Formatuojami tik konkretaus turinio langeliai

Norėdami formatuoti tik langelius su tam tikru turiniu, turite pasirinkti formatavimo taisyklę Lygus arba Tekste yra. Skirtumas tarp jų yra tas, kad pirmuoju atveju eilutė turi visiškai atitikti kriterijų, o antruoju - tik iš dalies. 

Kaip matote, sąlyginis formatavimas yra daugiafunkcė „Excel“ programos savybė, suteikianti daugybę pranašumų jį įvaldžiusiam asmeniui. Žinome, kad iš pirmo žvilgsnio visa tai gali atrodyti sudėtinga. Tačiau iš tikrųjų jis patraukia rankas, kai tik pagal turinį arba pagal kitą kriterijų pasirodo tam tikras tekstas paryškinamas raudonai (arba kitaip suformatuojamas). Ši funkcija yra įtraukta į pagrindinį Excel žinių rinkinį, be jos neįmanomas net mėgėjiškas darbas su skaičiuoklėmis ir duomenų bazėmis.