Lentelėje su keliais šimtais eilučių svarbiausi įrašai pasimeta: sąskaita, kurios terminas praėjo prieš savaitę, prekė, kurios liko dvi pakuotės, užsakymas, įvestas du kartus. Sąlyginis formatavimas juos nuspalvina pats, kai tik sąlyga išsipildo, ir nuspalvinimas dingsta, kai problema išsprendžiama.
Paruoštos taisyklės
Home → Conditional Formatting meniu yra kelios grupės:
- Highlight Cells Rules: didesnis už, mažesnis už, tarp, lygus, tekstas, kuriame yra, data ir Duplicate Values.
- Top/Bottom Rules: 10 didžiausių, 10 % mažiausių, virš vidurkio.
- Data Bars, Color Scales, Icon Sets: juostos ar spalvos pagal dydį, kai norisi vaizdo, o ne vienos ribos.
Dažniausiai užtenka jų. Pavyzdžiui, dublikatams: pažymėkite stulpelį, pasirinkite Highlight Cells Rules → Duplicate Values ir pasikartojančios reikšmės bus nuspalvintos. Plačiau apie dublikatų paiešką ir šalinimą rašiau atskirame straipsnyje kaip rasti ir pašalinti dublikatus. Jei dublikatus pagal kelis stulpelius norite iš karto pašalinti ar pažymėti, naudokite dublikatų šalinimo įrankį.
Visa eilutė pagal formulę
Paruoštos taisyklės spalvina tik tą langelį, kurį tikrina. Kad nuspalvintumėte visą eilutę, reikia formulės.
- Pažymėkite visą lentelę be antraštės, pavyzdžiui A2:F500.
- Conditional Formatting → New Rule → Use a formula to determine which cells to format.
- Įrašykite formulę pirmai pažymėtai eilutei.
- Paspauskite Format ir pasirinkite spalvą.
Formulė, kuri nuspalvina eilutę, kai C stulpelyje esanti būsena yra „Atšaukta“:
=$C2="Atšaukta"
Doleris prieš C užrakina stulpelį, todėl kiekvienas eilutės langelis tikrina C stulpelį. Prieš 2 dolerio nėra, todėl kiekviena eilutė tikrina savo reikšmę. Tai dažniausia klaida: be dolerio prieš raidę spalvinamas tik vienas langelis, o su doleriu prieš skaičių visa lentelė spalvinama pagal antrą eilutę.
Pavyzdžiai, kurie praverčia kasdien
| Ką paryškinti | Formulė |
|---|---|
| Vėluojančios neapmokėtos sąskaitos (C terminas, D apmokėjimo data) | =AND($C2<TODAY();$D2="") |
| Terminas per artimiausias 7 dienas | =AND($C2>=TODAY();$C2<=TODAY()+7) |
| Likutis mažesnis už užsakymo tašką (D likutis, E taškas) | =$D2<$E2 |
| Užsakymas, kurio kodas pasikartoja A stulpelyje | =COUNTIF($A:$A;$A2)>1 |
| Kas antra eilutė, kad būtų lengviau skaityti | =MOD(ROW();2)=0 |
| Savaitgalio datos | =WEEKDAY($A2;2)>5 |
Kaip apskaičiuoti užsakymo tašką, kurį naudoja trečia taisyklė, aprašiau straipsnyje užsakymo taškas ir saugos atsarga. Daugiau formulių su datomis ir terminais rasite straipsnyje datų skirtumas Excel.
Kelios taisyklės vienoje lentelėje
Kai taisyklių kelios, Excel jas tikrina iš eilės. Conditional Formatting → Manage Rules parodo visas taisykles, jų tvarką ir intervalus, kuriems jos taikomos. Ten galite:
- perkelti svarbesnę taisyklę aukščiau, pavyzdžiui, raudoną „vėluoja“ virš geltonos „artėja terminas“;
- pažymėti Stop If True, kad po pirmos tinkamos taisyklės kitos nebūtų taikomos;
- pataisyti stulpelį Applies to, jei taisyklė pritaikyta ne tam intervalui.
Kodėl taisyklės dauginasi
Jei Manage Rules lange matote dešimtis vienodų taisyklių su keistais intervalais, tai įvyko kopijuojant ir įterpiant eilutes. Kiekvienas įklijavimas gali sukurti taisyklės kopiją. Tokį failą sunku prižiūrėti, ir jis lėtėja. Ištrinkite pasikartojančias taisykles ir palikite vieną, pritaikytą visam stulpeliui ar lentelei. Kai duomenys paversti lentele (Ctrl+T), taisyklė persikelia į naujas eilutes pati.
Google Sheets
Google Sheets taisyklės nustatomos per Format → Conditional formatting. Visai eilutei pasirinkite Custom formula is ir rašykite tą pačią formulę, pavyzdžiui =$C2<TODAY(). Google Sheets formulėse argumentai skiriami pagal failo regiono nustatymus, todėl gali tekti rašyti kablelį vietoje kabliataškio.
Kai spalvų nebeužtenka
Sąlyginis formatavimas parodo problemą, bet jos neperduoda kitam žmogui. Jei kas rytą kažkas atsidaro failą tik tam, kad pažiūrėtų, kas nuspalvinta raudonai, ir tada rašo laišką tiekėjui ar klientui, tą patį sąrašą galima paruošti ir išsiųsti automatiškai.