Sąlyginio formatavimo įrankis suteikia galimybę atlikti duomenų stebėjimą, aktualius duomenis vizualiai išskiriant duomenų lentelėje pagal konkrečią sąlygą. Kitaip tariant, nurodžius sąlygą, ją atitinkantys langeliai pakeičia formatą ir pasidaro aiškiai matomi.
Sąlyginio formatavimo įrankis yra įrankių juostos HOME kortelėje, komandų grupėje STYLES (žr. 1 pav.).
1 pav. Sąlyginio formatavimo įrankis
Spustelėjus šio įrankio piktogramą, išsiskleidžiančio sąrašo viršuje pateikiamos dvi siūlomų formatavimo taisyklių grupės: HIGHLIGHT CELLS RULES ir TOP/BOTTOM RULES. Abiejose taisyklių grupėse yra greitosios taisyklių parinktys, sukurtos Excel skaičiuoklėje. Pirmosios grupės taisyklės pateikiamos žemiau (žr. 2 pav.).
2 pav. Langelių paryškinimo taisyklės
Pirmajame sąraše pateikiamos jau sudarytos langelių paryškinimo taisyklės. Žemiau esančioje lentelėje pateikiami jų paaiškinimai (žr. 1 lentelę).
1 lentelė
Langelių žymėjimo taisyklės
| Taisyklė | Kas paryškinama |
| Greater Than… | Reikšmės, didesnės už nurodytą |
| Less Than… | Reikšmės, mažesnės už nurodytą |
| Between… | Reikšmės, esančios nurodytame intervale |
| Equal To… | Reikšmės, lygios nurodytai reikšmei |
| Text That Contains… | Reikšmės, kuriose yra įrašytas nurodytas tekstas arba teksto dalis |
| A Date Occuring… | Reikšmės, kuriose įrašytos tam tikro laikotarpio datos |
| Duplicate Values… | Pasikartojančios reikšmės |
Pasirinktomis taisyklėmis leidžiama paryškinti tuos langelius, kuriuose skaičiai, tekstas arba datos atitinka nurodytą sąlygą. Sąlygų grupėje HIGHLIGHT CELLS RULES… nurodytos tokios taisyklės, kuriomis sąlygoje įvesta reikšmė tikrinama su langeliuose įrašyta reikšme.
Sąlygų grupėje TOP/BOTTOM RULES nurodytos sąlygos, paryškinančios didžiausias arba mažiausias reikšmes nurodytame masyve. Šioje sąlygų grupėje esančios sąlygos aprašytos 2 lentelėje.
2 lentelė
Didžiausių arba mažiausių reikšmių paryškinimo taisyklės
| Taisyklė | Kas paryškinama |
| Top 10 Items… | Nurodytas kiekis didžiausių reikšmių |
| Top 10 %… | Nurodytas procentas didžiausių reikšmių |
| Bottom 10 Items… | Nurodytas kiekis mažiausių reikšmių |
| Bottom 10 %… | Nurodytas procentas mažiausių reikšmių |
| Above Average… | Reikšmės, didesnės už pažymėto masyvo vidurkį |
| Below Average… | Reikšmės, mažesnės už pažymėto masyvo vidurkį |
Šiomis taisyklėmis aprašomas tokių langelių žymėjimas, kuriuose įrašytos reikšmės yra didžiausios arba mažiausios, lyginant visas pažymėtame masyve įrašytas reikšmes. Tam, kad šio įrankio veikimo principas pagal nurodytas taisykles taptų aiškesnis, panagrinėkime konkretų pavyzdį.
Žemiau esančiame paveiksle pateikta skaičių lentelė, kurioje dauginamos pirmoje eilutėje ir pirmame stulpelyje esančios reikšmės. Reikia išskirti tuos langelius, kuriuose įrašytos reikšmės yra intervale tarp 25 ir 75:
3 pav. Pasirinkto skaičių intervalo paryškinimas lentelėje
Prieš nurodant reikiamą sąlygą langeliams žymėti pažymima sritis, kuriai sąlyga bus taikoma. Skaičiams, esantiems nurodytame intervale, išskirti naudojama taisyklė BETWEEN. Ją pasirinkus pasirodo nustatymų langas, kurio pirmuose dviejuose laukeliuose nurodomos intervalo pradžios ir pabaigos reikšmės. Jas įrašius, skaičių lentelėje matomi paryškinti langeliai. Laukelyje dešinėje pasirenkamas langelių žymėjimo būdas. Žymėjimo būdo parinkčių sąrašas nurodytas 4 paveiksle.
4 pav. Langelių žymėjimo būdai
Išskleistame sąraše nurodomos greitosios langelių žymėjimo pagal nurodytą sąlygą parinktys. Jeigu nė vienas iš nurodytų žymėjimo būdų nėra tinkamas, galima sukurti kitokį, pasirinkus sąrašo apačioje esančią parinktį CUSTOM FORMAT… Tuomet atsiveria langelių formatavimo langas, kuriame yra visos galimos formatavimo parinktys (žr. 5 pav.).
5 pav. Langelių formatavimo parinktys
Šiame lange esančiose keturiose kortelėse nurodytos skaičių, teksto formato, rėmelių pritaikymo langeliams ir langelių fono spalvos nustatymo parinktys. Dažniausiai naudojamos parinktys paskutinėje kortelėje FILL, kur nurodoma langelių fono spalva sąlygas atitinkantiems langeliams žymėti. Tokiu būdu stebimos reikšmės išskiriamos konkrečiai pasirinkta spalva, nekeičiant skaičių ar teksto formato. Spustelėjus lentelės apačioje esantį mygtuką OK, patvirtinta formato parinktis pritaikoma langeliams.
Sąlyginio formatavimo įrankio naudojimas patogus tuo, kad langelių reikšmės, esančios pažymėtoje srityje, sekamos nuolat, taigi stebimos reikšmės bus nuolat išryškintos (žr. 6 pav.).
6 pav. Langelių reikšmių stebėjimas realiu laiku
Pakeitus skaičius viršutinėje eilutėje, lentelės reikšmės pasikeičia, tačiau sąlyginio formatavimo įrankis seka pakeitimus ir žymi sąlygą atitinkančius langelius (žalia spalva žymimi langeliai, kurių reikšmės yra intervale nuo 25 iki 75). Taigi, aktualios reikšmės bus pastebimos lengviau.
Dirbant didelėse lentelėse ne visada patogu naudoti jau sukurtas sąlyginio formatavimo taisykles, nes jos leidžia žymėti tik konkrečius langelius, atitinkančius nurodytas sąlygas. Galima nurodyti, kad, suradus sąlygą atitinkančią reikšmę, būtų žymima visa su įrašu susijusi eilutė. Tokiu atveju sąlyginio formatavimo sąlyga aprašoma naudojant formules, prieš tai pažymėjus visus lentelės duomenis be stulpelių pavadinimų. Taip pat formulėmis aprašomos tokios sąlygos, kurių nėra išsiskleidžiančiame sąlygų pasirinkimo sąraše. Įrankių juostoje spustelėjus piktogramą CONDITIONAL FORMATTING, pasirenkama trečia nuo apačios parinktis NEW RULE… Atlikus šiuos veiksmus, matoma tokia lentelė (žr. 7 pav.):
7 pav. Taisyklių užrašymas formulėmis
Atsivėrusio lango pirmajame lauke SELECT A RULE TYPE reikia pasirinkti paskutinį nurodytą įrašą USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT. Laukelyje, esančiame žemiau, įrašoma formulė reikalingai sąlygai tikrinti. Gali būti rašomos tik loginės sąlygos, ir tada, kai formulės rezultatas – „Tiesa“, langelis spalvinamas, kitu atveju – praleidžiamas. Užrašius sąlygą, pasirenkamas žymėjimo formatas, paspaudus mygtuką FORMAT… Sąlygai įrašyti spaudžiamas mygtukas OK.
Tarkime, kad sąskaitų apmokėjimo tikrinimo dokumente reikia paryškinti eilutes, kai sąskaitos apmokėjimas vėluoja. Pavyzdys pateikiamas žemiau esančiame paveiksle (žr. 8 pav.).
8 pav. Vėluojančių apmokėjimų tikrinimas
Pažymėjus visą duomenų sritį, neįtraukiant stulpelių pavadinimų, sąlyginio formatavimo įrankyje pasirenkama aprašyti sąlygą formule. Formulės laukelyje įrašoma tokia formulė: Pirmoji „Apmokėti iki“ stulpelio data < Šios dienos data. Tikrinama, ar „Apmokėti iki“ stulpelyje esanti data yra mažesnė už šios dienos datą, ir jeigu atsakymas – „Tiesa“, žymima visa eilutė.
Sąlyginio formatavimo formulėse didelę svarbą turi langelių adresų arba masyvų užrakinimas tinkamoje vietoje. Pavyzdyje nurodytoje formulėje tikrinamos „Apmokėti iki“ stulpelio datos adresas rakinamas tik stulpelyje, nes reikia patikrinti tik šiame stulpelyje įrašytas datas. Eilutės numeris nerakinamas, nes formulėje tikrinimas vykdomas įtraukiant visas šio stulpelio datas. Šios dienos datos adresas rakinamas visai, nes formulėje jis negali pasikeisti.
Vienoje lentelėje tuo pačiu metu gali veikti ne viena, bet kelios sąlyginio formatavimo taisyklės. Tarkime, kad reikia paryškinti ir tokias eilutes, kur iki apmokėjimo termino pabaigos yra likę mažiau nei penkios dienos (žr. 9 pav.).
9 pav. Apmokėjimo terminų tikrinimas
Pažymėjus duomenų sritį, užrašoma tokia sąlyginio formatavimo taisyklė: „Apmokėti iki“ stulpelio data – Šios dienos data < 5. Jeigu apmokėjimo termino pabaigos datos ir šios dienos datos skirtumas yra mažesnis nei penkios dienos, visa eilutė pažymima geltona spalva. Pritaikius šią taisyklę, lentelėje nebematomos raudonai pažymėtos eilutės, kurios nurodo, jog sąskaitos apmokėjimas vėluoja. Taip yra todėl, kad vėliau įrašyta sąlyginio formatavimo taisyklė įgauna prioritetą prieš anksčiau įrašytas taisykles.
Esant reikalui galima pakoreguoti užrašytų taisyklių prioritetinę tvarką. Tokie veiksmai atliekami paspaudus sąlyginio formatavimo piktogramą įrankių juostoje ir pasirinkus paskutinę parinktį MANAGE RULES… (žr. 10 pav.).
10 pav. Sąlyginio formatavimo taisyklių valdymas
Atsivėrusio lango pirmajame laukelyje reikia pasirinkti, kurioje dokumento vietoje įrašytas taisykles norima matyti sąraše. Jeigu prieš atveriant šį langą stovima toje srityje, kurioje aprašytos taisyklės, tinka parinktis CURRENT SELECTION. Kitu atveju reikėtų rinktis antrąją parinktį THIS WORKSHEET, kad būtų rodomos visos lape aprašytos taisyklės. Pele pasirinkus reikiamą taisyklę, mygtukais EDIT RULE… arba DELETE RULE… galima atitinkamai redaguoti arba panaikinti įrašytą taisyklę. Tais atvejais, kai reikia pakeisti taisyklių hierarchiją, naudojami rodyklių mygtukai. Kuo aukščiau taisyklė užrašyta, tuo didesnį ji turi prioritetą. Taigi pavyzdyje nurodytu atveju, norint matyti paryškintus tiek vėluojančius, tiek artėjančius prie termino pabaigos įrašus, pasirinkus raudonai žymimą taisyklę, rodyklės į viršų mygtuku ji užkeliama į patį viršų ir turės didžiausią prioritetą. Rezultatas matomas žemiau esančiame paveiksle (žr. 11 pav.).
11 pav. Kelių sąlyginio formatavimo taisyklių suderinimas
Pakeitus sąlyginio formatavimo taisyklių prioritetus abi taisyklės tapo suderintos tarpusavyje. Matomi ir vėluojantys apmokėjimai, ir tos sąskaitos, kurių apmokėjimo terminas jau greitai baigsis.
Taigi, naudojant sąlyginio formatavimo įrankį, galima patogiu būdu stebėti pokyčius ar dominančius duomenų lentelės įrašus, žinant, kad nebus praleistas nė vienas įrašas. Jeigu tinkamos Excel skaičiuoklės siūlomos sąlygos, jomis pasinaudoti galima keleto mygtukų paspaudimu. Kitais atvejais reikiamą taisyklę teks aprašyti formulėmis, tačiau stebėjimas taps dar patogesnis.
KONTROLINIAI KLAUSIMAI:
- Kaip paryškinti tam tikro laikotarpio datas?
- Kokiu būdu nustatomas pasirinktinis langelių paryškinimas?
- Kaip aprašyti taisyklę, kurios nėra tarp numatytųjų?
- Kurioje vietoje galima koreguoti arba panaikinti sukurtas taisykles?
