Sąlyginio formatavimo pridėjimas programoje „Excel“leidžia langeliui arba langelių diapazonui taikyti skirtingas formatavimo parinktis, atitinkančias konkrečias jūsų nustatytas sąlygas. Tokių sąlygų nustatymas gali padėti tvarkyti skaičiuoklę ir palengvinti jos nuskaitymą. Formatavimo parinktys, kurias galite naudoti, apima šrifto ir fono spalvos pakeitimus, šrifto stilius, langelių kraštines ir skaičių formatavimo pridėjimą prie duomenų.
„Excel“turi integruotas parinktis, skirtas dažniausiai naudojamoms sąlygoms, pvz., rasti skaičių, kurie yra didesni arba mažesni už tam tikrą reikšmę, arba skaičių, kurie yra didesni arba mažesni už vidutinę vertę. Be šių iš anksto nustatytų parinkčių, taip pat galite sukurti pasirinktines sąlyginio formatavimo taisykles naudodami "Excel" formules.
Šios instrukcijos taikomos „Excel 2019“, 2016, 2013, 2010 ir „Excel“, skirta „Microsoft 365“.
Kelių sąlygų taikymas programoje Excel
Galite taikyti daugiau nei vieną taisyklę tiems patiems duomenims, kad išbandytumėte skirtingas sąlygas. Pavyzdžiui, biudžeto duomenims gali būti nustatytos sąlygos, pagal kurias taikomi formatavimo pakeitimai, kai pasiekiamas tam tikras išlaidų lygis, pvz., 50%, 75% ir 100% viso biudžeto.
Tokiomis aplinkybėmis „Excel“pirmiausia nustato, ar įvairios taisyklės prieštarauja, ir, jei taip, programa vadovaujasi nustatyta pirmumo tvarka, kad nustatytų, kurią sąlyginio formatavimo taisyklę taikyti duomenims.
Duomenų, viršijančių 25 % ir 50 % padidėjimą, radimas
Toliau pateiktame pavyzdyje dvi priskirtos sąlyginio formatavimo taisyklės bus taikomos ląstelių diapazonui nuo B2 iki B5.
- Pirmoji taisyklė patikrina, ar duomenys ląsteliuose A2:A5 yra didesni už atitinkamą reikšmę B2:B5 daugiau nei 25%.
- Antra taisykle patikrinama, ar tie patys duomenys A2:A5 viršija atitinkamą reikšmę B2:B5 daugiau nei 50%.
Kaip matyti aukščiau esančiame paveikslėlyje, jei kuri nors iš aukščiau nurodytų sąlygų yra teisinga, langelio arba langelių fono spalva diapazone B1:B4 pasikeis.
- Duomenims, kurių skirtumas yra didesnis nei 25%, langelio fono spalva pasikeis į žalią.
- Jei skirtumas didesnis nei 50%, langelio fono spalva pasikeis į raudoną.
Taisyklės, naudotos šiai užduočiai atlikti, bus įvestos naudojant dialogo langą New Formatting Rule. Pradėkite įvesdami pavyzdinius duomenis į langelius A1 iki C5, kaip matyti aukščiau esančiame paveikslėlyje.
Paskutinėje pamokos dalyje prie langelių C2:C4 pridėsime formules, kurios rodo tikslų procentinį skirtumą tarp reikšmių langeliuose A2:A5 ir B2:B5; tai leis mums patikrinti sąlyginio formatavimo taisyklių tikslumą.
Sąlyginio formatavimo taisyklių nustatymas
Pirma, taikysime sąlyginį formatavimą, kad surastume 25 proc. ar didesnį padidėjimą.
Funkcija atrodys taip:
=(A2-B2)/A2>25 %
- Darbalapyje paryškinkite ląsteles B2 iki B5.
- Spustelėkite Pagrindinis skirtuką juostelėje.
- Spustelėkite Sąlyginio formatavimo piktogramą juostoje, kad atidarytumėte išskleidžiamąjį meniu.
-
Pasirinkite Nauja taisyklė, kad atidarytumėte dialogo langą Nauja formatavimo taisyklė.
- Skiltyje Pasirinkite taisyklės tipą, spustelėkite paskutinę parinktį: Naudokite formulę, kad nustatytumėte, kuriuos langelius formatuoti.
- Įveskite aukščiau pažymėtą formulę į žemiau esantį laukelį Formatuokite reikšmes, kuriose ši formulė yra teisinga:
- Spustelėkite mygtuką Format, kad atidarytumėte dialogo langą. Spustelėkite skirtuką Užpildyti ir pasirinkite spalvą.
- Spustelėkite OK, kad uždarytumėte dialogo langus ir grįžtumėte į darbalapį.
- ląstelių B3 ir B5 fono spalva turėtų pasikeisti į pasirinktą spalvą.
Dabar pritaikysime sąlyginį formatavimą, kad surastume 50 ar daugiau procentų padidėjimą. Formulė atrodys taip:
- Pakartokite pirmus penkis aukščiau nurodytus veiksmus.
-
Įveskite aukščiau pateiktą formulę žemiau esančiame laukelyje Formatuokite reikšmes, kuriose ši formulė yra teisinga:
- Spustelėkite mygtuką Format, kad atidarytumėte dialogo langą. Spustelėkite skirtuką Užpildyti ir pasirinkite kitą spalvą, nei darėte atlikdami ankstesnius veiksmus.
- Spustelėkite OK, kad uždarytumėte dialogo langus ir grįžtumėte į darbalapį.
langelio B3 fono spalva turėtų likti tokia pati, o tai rodo, kad procentinis skirtumas tarp skaičių langeliuose A3 irB3 yra didesnis nei 25 proc., bet mažesnis arba lygus 50 proc. langelio B5 fono spalva turėtų pasikeisti į naują pasirinktą spalvą, nurodant, kad procentinis skirtumas tarp skaičių langeliuose A5 ir B5 yra didesnis nei 50 proc.
Sąlyginio formatavimo taisyklių tikrinimas
Norėdami patikrinti, ar įvestos sąlyginio formatavimo taisyklės yra teisingos, į langelius C2:C5 galime įvesti formules, kurios apskaičiuos tikslų procentų skirtumą tarp skaičių diapazonuoseA2:A5 ir B2:B5.
C2 langelio formulė atrodo taip:
=(A2-B2)/A2
- Spustelėkite ląstelę C2, kad ji taptų aktyvia ląstele.
- Įveskite aukščiau pateiktą formulę ir paspauskite klaviatūros klavišą Enter.
- 10 % atsakymas turėtų būti rodomas ląstelėje C2, nurodant, kad skaičius ląstelėje A2 yra 10 % didesnis už skaičių langelyje ląstelė B2.
- Gali tekti pakeisti formatavimą ląstelėje C2, kad atsakymas būtų rodomas procentais.
- Naudokite užpildymo rankenėlę, kad nukopijuotumėte formulę iš ląstelės C2 į cells C3 į C5.
- Atsakymai į ląsteles C3 iki C5 turėtų būti 30%, 25% ir 60%.
Atsakymai šiuose langeliuose rodo, kad sąlyginio formatavimo taisyklės yra tikslios, nes skirtumas tarp ląstelių A3 ir B3 yra didesnis nei 25 procentų, o skirtumas tarp ląstelių A5 ir B5 yra didesnis nei 50 procentų.
Ląstelė B4 nepakeitė spalvos, nes skirtumas tarp langelių A4 ir B4 yra lygus 25 proc., o sąlyginio formatavimo taisyklė nurodė, kad, norint pakeisti fono spalvą, reikalingas didesnis nei 25 proc. procentas.
Sąlyginio formatavimo pirmumo tvarka
Kai tam pačiam duomenų diapazonui taikote kelias taisykles, „Excel“pirmiausia nustato, ar taisyklės prieštarauja. Suderinamos taisyklės, kai formatavimo parinktys negali būti taikomos tiems patiems duomenims.
Mūsų pavyzdyje taisyklės prieštarauja, nes abiem naudojama ta pati formatavimo parinktis – keičiama fono langelio spalva.
Jei antroji taisyklė yra teisinga (reikšmės skirtumas tarp dviejų langelių yra didesnis nei 50 proc.), tada pirmoji taisyklė (vertės skirtumas didesnis nei 25 proc.) taip pat yra teisinga.
Kadangi langelis vienu metu negali turėti dviejų skirtingų spalvų fonų, „Excel“turi žinoti, kurią sąlyginio formatavimo taisyklę ji turėtų taikyti.
„Excel“eiliškumo tvarka nurodo, kad pirmiausia taikoma taisyklė, kuri yra aukščiau dialogo lango Sąlyginio formatavimo taisyklių tvarkytuvė sąraše.
Kaip parodyta aukščiau esančiame paveikslėlyje, antroji šioje mokymo programoje naudojama taisyklė sąraše yra aukščiau, todėl turi viršenybę prieš pirmąją taisyklę. Dėl to langelio B5 fono spalva yra žalia.
Pagal numatytuosius nustatymus naujos taisyklės patenka į sąrašo viršų; norėdami pakeisti tvarką, dialogo lange naudokite rodyklių mygtukus aukštyn ir žemyn.
Neprieštaraujančių taisyklių taikymas
Jei dvi ar daugiau sąlyginio formatavimo taisyklių neprieštarauja, abi taikomos, kai kiekvienos taisyklės tikrinama sąlyga tampa teisinga.
Jei pagal pirmąją sąlyginio formatavimo taisyklę mūsų pavyzdyje langelių diapazonas B2:B5 būtų suformatuotas oranžine kraštine, o ne oranžine fono spalva, dvi sąlyginio formatavimo taisyklės nebūtų nesuderinamas, nes abu formatai gali būti taikomi netrukdant kitam.
Sąlyginis formatavimas prieš įprastą formatavimą
Esant prieštaravimų tarp sąlyginio formatavimo taisyklių ir rankiniu būdu taikomų formatavimo parinkčių, sąlyginio formatavimo taisyklė visada turi viršenybę ir bus taikoma vietoj bet kokių rankiniu būdu pridėtų formatavimo parinkčių.
