Kai tą pačią reikšmę į failą rašo keli žmonės, atsiranda „Vilnius“, „Vilniuje“, „vilnius“ ir „Vilnius “ su tarpu gale. Suvestinė lentelė jas rodo kaip keturis skirtingus miestus, o SUMIFS randa tik vieną. Išskleidžiamasis sąrašas šią problemą sprendžia pačioje pradžioje: žmogus renkasi iš sąrašo, o ne rašo.
Meniu pavadinimus rašau angliškai. Lietuviškame Excel jie išversti, bet yra tose pačiose vietose.
Greičiausias būdas
- Pažymėkite langelius, kuriuose turi būti sąrašas, pavyzdžiui visą stulpelį „Miestas“.
- Pasirinkite Data → Data Validation.
- Lauke Allow pasirinkite List.
- Lauke Source įrašykite reikšmes, atskirtas kabliataškiu:
Vilnius;Kaunas;Klaipėda. Angliškuose regiono nustatymuose skiriama kableliu.
Tinka trumpiems sąrašams, kurie nesikeičia, pavyzdžiui „Taip“ ir „Ne“ arba užsakymo būsenoms. Jei sąrašą reikės papildyti, teks vėl atidaryti šį langą kiekviename lape.
Sąrašas iš lentelės, kuris pasipildo pats
Ilgesnius sąrašus geriau laikyti atskirame lape:
- Sukurkite lapą „Sąrašai“, į stulpelį įrašykite reikšmes su antrašte „Miestas“.
- Paverskite jas Excel lentele (Ctrl+T) ir pavadinkite, pavyzdžiui, „Miestai“.
- Pasirinkite Formulas → Name Manager → New, pavadinimą įrašykite
MiestuSarasas, o lauke Refers to:=Miestai[Miestas]. - Data Validation lauke Source įrašykite
=MiestuSarasas.
Pavadintas intervalas reikalingas todėl, kad Data Validation lange tiesioginė nuoroda į lentelės stulpelį neveikia. Dabar įrašius naują miestą lentelės apačioje, jis iš karto atsiras visuose sąrašuose.
Sąrašas iš esamų duomenų
Microsoft 365 ir Excel 2021 versijose sąrašą galima sudaryti iš jau įvestų duomenų. Pagalbiniame lape į langelį A2 įrašykite:
=SORT(UNIQUE(Klientai[Miestas]))
Formulė išves surikiuotą miestų sąrašą be pasikartojimų. Data Validation lauke Source tada nurodykite =Sąrašai!$A$2#. Ženklas # reiškia „visas formulės rezultatas“, todėl sąrašas ilgės ir trumpės kartu su duomenimis.
Pranešimai ir draudimai
Data Validation lange yra dar du skirtukai:
- Input Message parodo užuominą, kai žmogus pažymi langelį, pavyzdžiui „Pasirinkite miestą iš sąrašo“.
- Error Alert nusako, kas nutinka įvedus reikšmę ne iš sąrašo. Stop neleidžia jos išsaugoti, Warning ir Information perspėja, bet leidžia. Jei norite tvarkingų duomenų, rinkitės Stop.
Kur patikrinimas nesuveikia
Data Validation tikrina tik tai, kas įvedama ranka. Jei kas nors nukopijuoja langelius iš kito failo ir įklijuoja, reikšmės patenka be patikrinimo, o kartais įklijavimas ištrina ir patį sąrašą.
Todėl prieš ruošdami ataskaitą pasirinkite Data → Data Validation → Circle Invalid Data. Excel apibrauks visas reikšmes, kurios neatitinka taisyklių. Jei tokių randate dažnai, problemą gali išspręsti tik kitas duomenų įvedimo būdas, pavyzdžiui forma.
Priklausomi sąrašai
Kartais antras sąrašas turi priklausyti nuo pirmo: pasirinkus kategoriją „Baldai“, rodomos tik baldų prekės. Klasikinis būdas yra kiekvienai kategorijai sukurti pavadintą intervalą tuo pačiu pavadinimu ir Source lauke rašyti =INDIRECT(A2). Tai veikia, bet pavadinimai turi tiksliai sutapti su kategorijomis ir negali turėti tarpų. Kai kategorijų daug ir jos keičiasi, tokį failą sunku prižiūrėti.
Google Sheets
Google Sheets sąrašas kuriamas per Data → Data validation, taisyklėje pasirenkant Dropdown arba Dropdown (from a range). Nuoroda į intervalą su tuščiomis eilutėmis apačioje, pavyzdžiui Sąrašai!A2:A, leidžia sąrašą papildyti be papildomų nustatymų.
Jei duomenų tvarka vis tiek byra, verta pažiūrėti ir į tai, ar failas apskritai tinka tokiam darbui. Apie tai rašiau straipsnyje kada įmonei nebeužtenka Excel.