Failas veikia gerai tol, kol jį pildo vienas žmogus. Kai prisijungia trys kolegos, stulpelyje „Kiekis“ atsiranda „5 vnt.“, datos įrašomos trimis formatais, o tas pats klientas turi keturis pavadinimus. Formulės tada ima rodyti ne tai, ką turėtų. Duomenų tikrinimas sustabdo daugumą tokių klaidų dar įvedimo metu.
Kur jį rasti
Pažymėkite langelius ar visą stulpelį ir pasirinkite Data → Data Validation. Lietuviškoje Excel versijoje tai Duomenys → Duomenų tikrinimas. Lange yra trys skirtukai:
- Settings: kas leidžiama įvesti.
- Input Message: užuomina, kuri pasirodo pažymėjus langelį.
- Error Alert: ką Excel daro, kai įvesta reikšmė netinka.
Dažniausios taisyklės
| Ko reikia | Allow | Nustatymai |
|---|---|---|
| Tik sveiki skaičiai nuo 1 | Whole number | greater than or equal to, Minimum 1 |
| Kaina su centais, ne neigiama | Decimal | greater than or equal to, Minimum 0 |
| Data ne ateityje | Date | less than or equal to, End date =TODAY() |
| Data šiais metais | Date | between, =DATE(YEAR(TODAY());1;1) ir =DATE(YEAR(TODAY());12;31) |
| Kodas lygiai 8 simbolių | Text length | equal to, Length 8 |
| Reikšmė iš sąrašo | List | Source: sąrašas ar intervalas |
Apie sąrašus plačiau rašiau straipsnyje išskleidžiamasis sąrašas Excel, ten aprašyta ir tai, kaip padaryti, kad sąrašas pasipildytų pats.
Savos taisyklės su formule
Pasirinkus Custom, galima įrašyti bet kokią formulę, kuri grąžina TRUE arba FALSE. Formulė rašoma pirmam pažymėto intervalo langeliui, o Excel ją pritaiko kitiems taip pat, kaip nutempus formulę.
Kad tas pats kodas A stulpelyje nepasikartotų:
=COUNTIF($A:$A;A2)=1
Kad langelyje nebūtų tarpų, pavyzdžiui, el. pašto adrese ar prekės kode:
=ISERROR(FIND(" ";A2))
Kad nuolaida D stulpelyje būtų įvesta tik tada, kai C stulpelyje pasirinkta „Taip“:
=OR(C2="Taip";D2="")
Kad kiekis neviršytų likučio, kuris yra lape „Likučiai“:
=B2<=XLOOKUP(A2;Likučiai!A:A;Likučiai!C:C;0)
Svarbiausia vieta yra doleriai. $A:$A turi būti užrakintas, kad visi langeliai tikrintų tą patį stulpelį, o A2 ne, kad kiekviena eilutė tikrintų savo reikšmę.
Stop, Warning ar Information
Error Alert skirtuke yra trys elgesio tipai:
- Stop neleidžia įvesti netinkamos reikšmės. Tinka kodams, kiekiams ir datoms.
- Warning paklausia, ar tikrai norite tęsti. Tinka, kai reikšmė neįprasta, bet galima, pavyzdžiui, labai didelis užsakymas.
- Information tik praneša ir įrašo reikšmę.
Klaidos pranešimą parašykite žmogui suprantamai: ne „Reikšmė netinkama“, o „Įveskite kiekį sveiku skaičiumi, be vnt.“. Tada mažiau klausimų ateis jums.
Kur tikrinimas nesuveikia
Duomenų tikrinimas turi kelias spragas, apie kurias verta žinoti:
- Įklijavimas. Jei kas nors nukopijuoja reikšmę iš kito failo ir įklijuoja, taisyklė ne tik nesuveikia, bet gali būti ir ištrinta. Paste Special → Values taisyklę palieka, paprastas Ctrl+V ją perrašo.
- Seni duomenys. Taisyklė tikrina tik naujai įvedamas reikšmes. Jau esamas klaidas parodo Data → Data Validation → Circle Invalid Data, kuris apibrėžia netinkamas reikšmes raudonai.
- Naujos eilutės. Jei taisyklė nustatyta A2:A100, 101 eilutė nebus tikrinama. Paverskite duomenis lentele (Ctrl+T), tada taisyklė persikels į naujas eilutes.
Jei klaidingi duomenys jau yra, pirmiausia juos sutvarkykite. Kaip tai padaryti, aprašiau straipsnyje kaip sutvarkyti netvarkingą Excel lentelę.
Google Sheets
Google Sheets taisyklės nustatomos per Data → Data validation. Yra tos pačios galimybės: skaičiai, datos, sąrašai ir sava formulė (Custom formula is). Pasirinkus „Reject the input“, netinkama reikšmė neįrašoma, o „Show a warning“ ją įrašo ir pažymi.
Kai failą pildo daug žmonių
Duomenų tikrinimas gerai saugo nuo atsitiktinių klaidų. Jis nesaugo nuo to, kad kas nors ištrins formulę, perrašys kito žmogaus eilutę ar išsaugos savo failo kopiją. Kai failą kasdien pildo keli žmonės ir nuo jo priklauso užsakymai ar likučiai, verta pagalvoti apie formą arba nedidelę sistemą, kurioje kiekvienas mato tik savo įvedimo laukus. Ženklai, kad metas tai daryti, surašyti straipsnyje kada įmonei nebeužtenka Excel.