Andmete valideerimise rakendamine lahtritele

Saate kasutada andmete valideerimist, et piirata, millist tüüpi andmeid kasutajad saavad lahtritesse sisestada. Üks levinumaid andmete valideerimise kasutusviise on ripploendi loomine. Selles videos antakse lühiülevaate andmete valideerimisest.

Näidiste allalaadimine

Alla saate laadida nii selles artiklis oleva näidistöövihiku kui ka kõik andmete valideerimise näited. Saate järgida artiklis toodud juhist või luua ise andmete valideerimise stsenaariumi.

Laadi alla Exceli andmete valideerimise näited

Andmete valideerimise rakendamine lahtrile või vahemikule

Märkus. : Selle jaotise kolm esimest juhist käsitlevad eri andmetüüpide lisamist. Toimingud 4-8 käivad konkreetselt ripploendi loomise kohta.

  1. Valige valideerimiseks vähemalt üks lahter.

  2. Klõpsake menüü Andmed jaotises Andmeriistad nuppu Andmete valideerimine.

    Andmete valideerimine asub menüü Andmed jaotises Andmeriistad
  3. Valige vahekaardi Sätted ripploendis Luba väärtus Loend.

    Dialoogiboksi Andmete valideerimine vahekaart Sätted
  4. Väljale Allikas tippige loendiväärtused ja eraldage need komadega. Siin on mõned näited.

    1. Vastuse piiramiseks kahe valikuvariandiga (nt küsimuse „Kas teil on lapsi?“ korral) tippige Jah, Ei.

    2. Hankija toodete kvaliteedi hindamise piiritlemiseks kolme hinnanguga tippige Madal, Keskmine, Kõrge.

      Märkus. : Neid juhiseid on üldiselt soovitatav kasutada üksnes loendiüksuste jaoks, mis tõenäoliselt kunagi ei muutu. Kui teil on loend, mis võib muutuda või kui soovita aja jooksul üksusi lisada või eemaldada, on mõistlik järgida hoopis alltoodud häid tavasid.

      Head tavad: loendikirjete loomiseks võite viidata ka töövihikus mujal asuvale lahtrivahemikule. Kõige tõhusam viis loendi loomiseks on see esmalt vormindada Exceli tabelina (valige Avaleht > Laadid > Vorminda tabelina ja valige seejärel endale sobivaim tabelilaad). Edasi valige tabeli andmesisu vahemik (ainult loendit ja mitte tabeli päist – antud juhul „Osakond“ – sisaldav tabeli osa) ning sisestage selle jaoks nimi veeru A kohal oleval nimeväljal.

      Pange loendile tähendusega nimi ja sisestage see nimeväljale

    Selle asemel, et tippida väärtused andmete valideerimise dialoogiboksi väljale Allikad, lisate sinna hoopis just määratud nime, mille ette on lisatud võrdusmärk (=).

    Tabeli nime alustamine märgiga =

    Tabelit on hea kasutada, sest loendisse üksuste lisamisel või nende sealt eemaldamisel värskendatakse andmete valideerimise loendit automaatselt.

    Märkus. : Selleks, et keegi teie loendeid redigeerida ei saaks, on mõistlik loendid paigutada eraldi töölehele (vajadusel ka peidetud kujul).

  5. Veenduge, et ruut Lahtrisisene ripploend oleks märgitud. Muidu ei näe te lahtri kõrval rippnoolt.

    Lahtri kõrval kuvatav lahtrisisene ripploend
  6. Tühjade (null-) väärtuste käsitlemise määramiseks märkige või tühjendage ruut Ignoreeri tühje.

    Märkus. : Kui teie lubatud väärtused põhinevad määratud nimega lahtrivahemikul ja vahemikus on kuskil tühi lahter, lubab ruudu Ignoreeri tühje märkimine sisestada valideeritud lahtrisse suvalisi väärtusi. See kehtib ka valideerimisvalemite poolt viidatud lahtrite kohta: kui mõni viidatud lahter on tühi, lubab ruudu Ignoreeri tühje märkimine sisestada valideeritud lahtrisse suvalisi väärtusi.

  7. Kontrollige, kas andmete valideerimine toimib õigesti. Proovige lahtritesse sisestada nii kehtivaid kui vigaseid andmeid, et kontrollida, kas sätted töötavad ja teated kuvatakse nii, nagu eeldate.

Märkmed : 

  • Pärast ripploendi loomist veenduge, et see töötaks soovitud viisil. Kontrollige näiteks, kas lahter on teie kirjete kuvamiseks piisavalt lai.

  • Kui teie ripploendi kirjeteloend asub mõnel muul töölehel ja te soovite, et kasutajad ei saaks seda vaadata ega muuta, on soovitatav see tööleht peita ja kaitsta. Töölehe kaitsmise kohta leiate lisateavet teemast Lahtrite lukustamine nende kaitsmiseks.

  • Andmete valideerimise eemaldamine – Valige lahter või lahtrid, mis sisaldavad valideerimist, mida soovite kustutada, siis avage Andmed > Andmete valideerimine, vajutage andmete valideerimise dialoogiboksis nuppu Tühjenda kõik ja seejärel nuppu OK.

Järgmises tabelis on ära toodud muud andmete valideerimise tüübid ja viisid, kuidas saate neid oma töölehtedele lisada.

Soovitud toiming

Tehke järgmist

Andmete sisestamise piiritlemine teatud vahemikku jäävate täisarvudega.

  1. Täitke eeltoodud jaotises Andmete valideerimise rakendamine lahtrile või vahemikule toodud juhised 1–3.

  2. Valige loendist Luba väärtus Täisarv.

  3. Valige väljal Andmed soovitud piirangu tüüp. Näiteks ülem- ja alampiiri seadmiseks valige väärtus vahemikus.

  4. Sisestage minimaalne, maksimaalne või konkreetne lubatav väärtus.

    Dialoogiboks Valideerimiskriteeriumid

    Saate sisestada ka valemi, mis annab tulemuseks arvväärtuse.

    Oletagem näiteks, et valideerite andmeid lahtris F1. Kui soovite seada mahaarvamiste alampiiriks kahekordse sellesse lahtrisse sisestatud laste arvu, valige väljal Andmed väärtus suurem või võrdne ja sisestage väljale Miinimum valem =2*F1.

Andmete sisestamise piiritlemine teatud vahemikku jääva kümnendarvuga.

  1. Täitke eeltoodud jaotises Andmete valideerimise rakendamine lahtrile või vahemikule toodud juhised 1–3.

  2. Valige väljal Luba väärtus Kümnendsüsteem.

  3. Valige väljal Andmed soovitud piirangu tüüp. Näiteks ülem- ja alampiiri seadmiseks valige väärtus vahemikus.

  4. Sisestage minimaalne, maksimaalne või konkreetne lubatav väärtus.

    Saate sisestada ka valemi, mis annab tulemuseks arvväärtuse. Näiteks müügitöötaja komisjonitasude ja preemia 6% ülempiiri seadmiseks lahtris E1 valige väljal Andmed väärtus vahemikus ja sisestage väljale Maksimum valem =E1*6%.

    Märkus. : Kui soovite lubada kasutajatel sisestada protsente (nt 20%), valige loendiväljal Luba väärtus Kümnendsüsteem, valige väljal Andmed soovitud piirangu tüüp, sisestage minimaalne, maksimaalne või täpne väärtus kümnendarvuna (nt 0,2) ja kuvage seejärel andmete valideerimise lahter protsendina, valides selleks lahtri ja klõpsates menüü Avaleht jaotises Arv nuppu Protsendilaad  Viiktekst 1 .

Andmete sisestamise piiritlemine teatud kuupäevavahemikku jääva kuupäevaga

  1. Täitke eeltoodud jaotises Andmete valideerimise rakendamine lahtrile või vahemikule toodud juhised 1–3.

  2. Valige väljal Luba väärtus Kuupäev.

  3. Valige väljal Andmed soovitud piirangutüüp. Näiteks kindlale päevale järgnevate kuupäevade lubamiseks valige väärtus suurem kui.

  4. Sisestage lubatav alguskuupäev, lõpukuupäev, või konkreetne kuupäev.

    Saate sisestada ka valemi, mis annab tulemuseks kuupäeva. Näiteks ajapiirangu seadmiseks tänase kuupäeva ja kolme sellele järgneva päeva lubamiseks valige väljal Andmed väärtus vahemikus, sisestage väljale Alguskuupäev väärtus =TODAY() ja väljale Lõppkuupäev väärtus =TODAY()+3.

    Valideerimiskriteeriumide sätted sisestatava kuupäeva piiritlemiseks kindla ajavahemikuga

Andmete sisestamise piiritlemine teatud ajavahemikku jääva kellaajaga.

  1. Täitke eeltoodud jaotises Andmete valideerimise rakendamine lahtrile või vahemikule toodud juhised 1–3.

  2. Valige väljal Luba väärtus Kellaaeg.

  3. Valige väljal Andmed soovitud piirangutüüp. Näiteks kindlale kellaajale eelnevate kellaaegade lubamiseks valige väärtus väiksem kui.

  4. Sisestage lubatava vahemiku algus- või lõppkellaaeg või lubatav kindel kellaaeg. Kui soovite sisestada konkreetse kellaaja, kasutage kellaajavormingut hh:mm.

    Oletagem näiteks, et olete lahtrisse E2 sisestanud algusaja (8:00 EL) ja lahtrisse F2 lõpuaja (17:00 PL) ning soovite koosolekuaegu piirata nende kahe ajapunkti vahele jääva ajaperioodiga. Selleks valige väljal Andmed väärtus vahemikus, sisestage väljale Algusaeg väärtus =E2 ja väljale Lõpuaeg väärtus =F2.

    Valideerimissätted sisestatava kellaaja piiritlemiseks ajavahemikuga

Sisestatavate andmete pikkuse piiritlemine.

  1. Täitke eeltoodud jaotises Andmete valideerimise rakendamine lahtrile või vahemikule toodud juhised 1–3.

  2. Valige väljal Luba väärtus Teksti pikkus.

  3. Valige väljal Andmed soovitud piirangutüüp. Näitaks kindla arvu märkide lubamiseks valige väärtus väiksem või võrdne.

  4. Antud juhul soovime sisestatavate andmete pikkuse piiritleda 25 tärgiga. Seega tuleks väljal Andmed valida väärtus väiksem kui või võrdub ja väljale Maksimum väärtus 25.

    Näide andmete valideerimisest, mille korral tekstipikkus piiratud

Teise lahtri sisul põhinev lubatu arvutamine.

  1. Täitke eeltoodud jaotises Andmete valideerimise rakendamine lahtrile või vahemikule toodud juhised 1–3. Valige loendiväljal Luba soovitud andmetüüp.

  2. Valige väljal Andmed soovitud piirangutüüp.

  3. Klõpsake välja Andmed all asuvat välja või välju, mida soovite kasutada lubatu määramiseks.

    Näiteks kui soovite mõne konto jaoks lubada ainult kirjeid, mille tulemusena ei ületata lahtrisse E1 sisestatud eelarvesummat, valige Luba >Täisarv, Andmed> väiksem või võrdne ning sisestage väljale Maksimum väärtus >= =E1.

    Muu lahtrisisu alusel arvutamine valideerimissätete abil

Märkus. : Järgmistes näidetes on kasutatud suvandit Kohandatud, mis võimaldab tingimuste määramiseks kirjutada valemeid. Te ei pea muretsema selle pärast, mis on kuvatud väljal Andmed, kuna see väli on suvandi Kohandatud korral keelatud.

Kontrollige

Sisestage valem

Toote ID-d sisaldav lahter (C2) peab algama standardse eesliitega „ID-“ ja nime pikkus peab olema vähemalt 10 märki (suurem kui 9).

=AND(LEFT(C2; 3) ="ID-";LEN(C2) > 9)

Näide 6. Valemite kasutamine andmete valideerimisel

Toote nime lahter (D2) tohib sisaldada ainult teksti.

=ISTEXT(D2)

Näide 2 Valemite kasutamine andmete valideerimisel

Sünnipäeva sisaldav lahter (B6) peab sisaldama suuremat arvu, kui lahtris B4 määratud aastate arv.

= IF(B6<=(TODAY()-(365*B4));TRUE;FALSE)

Näide andmete valideerimisest, mille korral on sisestamiseks määratud minimaalne vanus

Lahtrivahemik A2:A10 sisaldab ainult üheseid väärtusi.

= COUNTIF(€A€2:€A€10;A2) = 1

Näide 4 Valemite kasutamine andmete valideerimisel

Märkus. : Esmalt peate lahtri A2 jaoks sisestama andmete valideerimise valemi ja seejärel kopeerima lahtri A2 lahtrivahemikku A3:A10, et funktsiooni COUNTIF teine argument vastaks praegusele lahtrile. Seda selleks, et osa A2)=1 muutuks osaks A3)=1; A4)=1 jne.

Lisateave

Veenduge, et lahtris B4 oleva meiliaadressi kirje sisaldaks sümbolit „@“.

=ISUMBER(FIND("@";B4)

Näide andmete valideerimisest, mille abil tagada meiliaadressis sümboli „@“ sisaldumine

  • Miks pole lindil lubatud andmete valideerimise käsk? Käsk ei pruugi olla saadaval järgmistel põhjustel.

    • Võimalik, et mõni Microsoft Exceli tabel on lingitud mõne SharePointi saidiga.  Andmete valideerimist ei saa rakendada mõne SharePointi saidiga lingitud Exceli tabelitele. Andmete valideerimise rakendamiseks peate Exceli tabeli lingi eemaldama või teisendama Exceli tabeli andmevahemikuks.

    • Võimalik, et sisestate parajasti andmeid. Käsk Andmete valideerimine on andmete lahtrisse sisestamise ajal menüüs Andmed keelatud. Andmete sisestamise lõpetamiseks vajutage sisestusklahvi (ENTER) või paoklahvi (ESC).

    • Tööleht võib olla kaitstud või ühiskasutuses. Ühiskasutatavas või kaitstud töövihikus ei saa andmete valideerimise sätteid muuta. Töövihiku ühiskasutuse või kaitsmise lõpetamise kohta leiate teavet teemast Töövihiku kaitsmine.

  • Kas saan muuta fondi suurust? Ei saa. Fondi suurus on fikseeritud. Kuvasuurust saate muuta üksnes Exceli akna paremas allnurgas olevate suumisuvandite abil. Saate siiski kasutada ActiveX-liitboksi. Lugege teemat Loendiboksi või liitboksi lisamine töölehele.

  • Kas tippimise ajal andmete valideerimise jaoks saab määrata automaattäite või -valiku funktsiooni? Ei saa, aga see funktsioon on olemas näiteks ActiveX liitboksis.

  • Kas andmete valideerimise loendis on võimalik teha mitu valikut? Ainult juhul, kui kasutate ActiveX liitboksi või ActiveX loendiboksi.

  • Kas saan andmete valideerimise loendist valida üksuse ning täita sellega mõne muu loendi? Jah! Seda toimingut nimetatakse sõltuvaks andmete valideerimiseks. Lisateavet leiate teemast Sõltuvate ripploendite loomine.

  • Kuidas eemaldada töölehelt kogu andmete valideerimine? Selleks võite kasutada dialoogiboksi Minek > Teisiti või valige menüüs Avaleht > Redigeerimine > Otsi ja vali (või vajutage klahvi F5 või klahvikombinatsiooni Ctrl + G), seejärel valige Erivalikud > Andmete valideerimine ning siis valige kas Kõik(kui soovite leida kõik lahtrid, millele on rakendatud andmete valideerimine) või Sama (kui soovite leida lahtrid, mis vastavad kindlatele andmete valideerimise sätetele).

    Dialoogiboks Erivalikud

    Järgmiseks avage andmete valideerimise dialoogiboks (menüü Andmed >Valideeri andmeid), vajutage nuppu Tühjenda kõik ja seejärel nuppu OK.

  • Kas andmete valideerimise abil saan jõustada lahtritesse väärtuste sisestamise? Ei, aga VBA (Visual Basic for Applications) abil saate kontrollida, kas keegi on teatud tingimuste korral lahtritesse midagi sisestanud näiteks enne söövihiku salvestamist või sulgemist. Kui valikut pole tehtud, saate sündmuse tühistada ning jätkamise keelata seni, kuni valik on tehtud.

  • Kuidas värvida lahtreid vastavalt andmete valideerimise loendi valikule? Saate kasutada tingimusvormingut. Sel juhul kasutage suvandit Vorminda ainult lahtrid, kus on.

    Ainult mõnd suvandit sisaldavate lahtrite vormindamine
  • Kuidas valideerida meiliaadressi? Saate valida Kohandatud > Valem ja kontrollida, kas sisestatud on @-märk. Sel juhul kasutatakse valemit =ISNUMBER(FIND(„@“,D2)). Funktsioon FIND otsib @-märki ja tagastab märgi leidmisel selle arvulise asukoha tekstistringis ning võimaldab andmete sisestamist. Kui @-märki ei leita, tagastab funktsioon FIND veaväärtuse ning andmeid sisestada ei saa.

Kas teil on konkreetse funktsiooniga seotud küsimus?

Postitage oma küsimus Exceli kogukonnafoorumis

Aidake meil Excelit täiustada

Kas teil on soovitusi selle kohta, kuidas saakime täiustada järgmist Exceli versiooni? Sellisel juhul tutvuge nende teemadega Exceli User Voice’i veebilehel.

Vaata ka

Lisateave andmete valideerimise kohta

Video: ripploendite loomine ja haldamine

Üksuste lisamine ripploendisse või sealt eemaldamine

Ripploendi eemaldamine

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×