Apply data validation to cells

Voit käyttää tietojen kelpoisuuden tarkistamista käyttäjien soluun syöttämien tietojen tai arvojen tyypin rajoittamiseen. Yksi yleisimmistä tietojen kelpoisuustarkistuksen käyttökohteista on avattavan luettelon luominen. Katso tämä video, jossa annetaan lyhyt yleiskatsaus tietojen kelpoisuustarkistuksesta.

Lataa Microsoftin esimerkkejä

Voit ladata esimerkkityökirjan, joka sisältää kaikki tästä artikkelista löytyvät tietojen kelpoisuuden tarkistamista koskevat esimerkit. Voit seurata tai luoda omia tietojen kelpoisuuden skenaarioita.

Lataa Excel-tietojen kelpoisuuden esimerkkejä

Tietojen kelpoisuuden tarkistamisen lisääminen soluun tai alueeseen

Huomautus: Kolme ensimmäistä vaihetta koskevat tietojen kelpoisuuden tarkistamisen tyypin lisäämistä. Vaiheet 4–8 ovat avattavan luettelon luomista varten.

  1. Valitse vähintään yksi solu, jonka kelpoisuuden haluat tarkistaa.

  2. Valitse Tiedot-välilehden Datatyökalut-ryhmästä Tietojen kelpoisuuden tarkistaminen.

    Tietojen kelpoisuuden tarkistaminen on Tiedot-välilehden Datatyökalut-ryhmässä
  3. Valitse Asetukset-välilehden Salli-ruudusta Luettelo.

    Asetukset-välilehti Tietojen kelpoisuuden tarkistaminen -valintaikkunassa
  4. Kirjoita Lähde-ruutuun luetteloarvot pilkulla erotettuina. Esimerkki:

    1. Jos haluat rajoittaa vastausten määrän kahteen (esim. ”Onko sinulla lapsia?”), kirjoita Kyllä,Ei.

    2. Jos haluat, että tavarantoimittajan palvelun laatua voidaan kuvata vain kolmella vaihtoehtoisella arvosanalla, kirjoita Heikko,Keskinkertainen,Hyvä.

      Huomautus: Näitä vaiheita suositellaan yleensä vain luettelokohteille, jotka eivät todennäköisesti muutu. Jos luettelo saattaa muuttua tai jos kohteita pitää lisätä tai poistaa ajan mittaan, sinun kannattaa noudattaa alla olevaa parhaiden käytäntöjen vaihetta.

      Parhaat käytännöt: Voit luoda luettelomerkintöjä myös viittaamalla toisaalla työkirjassa sijaitsevaan solualueeseen. Tehokkain tapa on luoda luettelo ja muotoilla se Excel-taulukoksi (valitse Aloitus-välilehdessä Tyylit > Muotoile taulukoksi > valitse haluamasi taulukkotyyli). Valitse seuraavaksi taulukon leipätekstin tietoalue, joka on se taulukon osa, jossa on vain luettelo eikä taulukon otsikkoa (tässä tapauksessa Osasto). Anna alueelle kuvaava nimi A-sarakkeen yllä olevaan nimiruutuun.

      Kirjoita luettelolle kuvaava nimi Nimi-kenttään.

    Sen sijaan, että kirjoittaisit tietojen kelpoisuuden tarkistamisessa Lähde-ruutuun luettelon arvot, lisäät juuri määrittämäsi nimen ja sen eteen yhtäläisyysmerkin (=).

    Kirjoita taulukon nimeä ennen =-merkki

    Taulukon käyttämisen paras puoli on siinä, että tietojen kelpoisuuden tarkistamisen taulukko päivittyy automaattisesti sitä mukaa kun lisäät tai poistat kohteita luettelossa.

    Huomautus: Luettelot kannattaa sijoittaa erilliseen laskentataulukkoon (piilotettuun sellaiseen tarvittaessa), jotta kukaan ei voi muokata niitä.

  5. Varmista, että Avattava luettelo -valintaruutu on valittu. Muussa tapauksessa avattavan luettelon nuoli ei tule näkyviin solun viereen.

    Solussa oleva avattava luetteloruutu näkyy solun vieressä
  6. Jos haluat määrittää, kuinka tyhjäarvot (null) käsitellään, valitse Ohita tyhjät -valintaruutu.

    Huomautus:  Jos sallitut arvot perustuvat solualueeseen, jolla on määritetty nimi, ja kyseisellä alueella on tyhjä solu, Ohita tyhjät -valintaruudun valinta sallii kaikki mahdolliset arvot tarkistettavassa solussa. Tämä koskee myös kaikkia soluja, joihin viitataan kelpoisuustarkistuskaavoilla: jos jokin viitattu solu on tyhjä, Ohita tyhjät -valintaruudun valinta sallii kaikki mahdolliset arvot tarkistettavassa solussa.

  7. Testaa kelpoisuuden tarkistaminen, jotta näet, toimiiko se oikein. Kirjoita sekä oikeita että vääriä arvoja soluihin ja varmista, että asetukset toimivat oikein ja sanomat tulevat näkyviin tarkoittamallasi tavalla.

Huomautukset: 

  • Varmista avattavan luettelon luotuasi, että se toimii haluamallasi tavalla. Kannattaa esimerkiksi tarkistaa, että solu on riittävän leveä kaikkien merkintöjen näyttämiseen.

  • Jos avattavan luettelon merkinnät sisältävä luettelo on toisessa laskentataulukossa ja haluat estää käyttäjiä näkemästä sitä tai tekemästä siihen muutoksia, voit piilottaa tai suojata kyseisen laskentataulukon. Lisätietoja laskentataulukon suojauksesta on artikkelissa Solujen suojaaminen lukitsemalla.

  • Poista tietojen kelpoisuuden tarkistaminen – Valitse solu tai solut, jotka sisältävät poistettavan kelpoisuuden, ja siirry sitten kohtaan Tiedot > Tietojen kelpoisuuden tarkistaminen. Paina tietojen kelpoisuuden tarkistamisen valintaikkunassa Tyhjennä kaikki -painiketta ja sitten OK.

Seuraavassa taulukossa luetellaan muita tietojen kelpoisuuden tarkistamistekniikoita ja näytetään eri tapoja lisätä niitä laskentataulukoihin.

Haluttu vaikutus:

Toimi seuraavasti:

Syötettävien tietojen rajoittaminen tietyllä välillä oleviin kokonaislukuihin.

  1. Noudata yllä olevan kohdan Tietojen kelpoisuuden tarkistamisen lisääminen soluun tai alueeseen vaiheita 1–3.

  2. Valitse Salli-luettelosta Kokonaisluku.

  3. Valitse Tiedot-ruudusta haluamasi rajoitukset. Voit esimerkiksi määrittää ylä- ja alarajan valitsemalla on välillä.

  4. Anna pienin tai suurin arvo tai määritä tarkka sallittu arvo.

    Kelpoisuustarkistuksen ehtojen valintaikkuna

    Voit myös kirjoittaa kaavan, joka palauttaa luvun.

    Oletetaan esimerkiksi, että olet vahvistamassa solun F1 tietojen kelpoisuutta. Jos esimerkiksi haluat määrittää, että vähennysten alaraja on kaksi kertaa lasten lukumäärä kyseisessä solussa, valitse Tiedot-ruudusta on suurempi tai yhtä suuri kuin ja kirjoita Minimi-ruutuun kaava =2*F1.

Syötettävien tietojen rajoittaminen tietyllä välillä oleviin desimaalilukuihin.

  1. Noudata yllä olevan kohdan Tietojen kelpoisuuden tarkistamisen lisääminen soluun tai alueeseen vaiheita 1–3.

  2. Valitse Hyväksy-ruudusta Desimaali.

  3. Valitse Tiedot-ruudusta haluamasi rajoitukset. Voit esimerkiksi määrittää ylä- ja alarajan valitsemalla on välillä.

  4. Anna pienin tai suurin arvo tai määritä tarkka sallittu arvo.

    Voit myös kirjoittaa kaavan, joka palauttaa luvun. Voit esimerkiksi määrittää, että myyntipalkkion ja bonusten enimmäismäärä on enintään 6 prosenttia myyjän palkasta (solussa E1), valitsemalla Tiedot-ruudusta on pienempi tai yhtä suuri kuin ja kirjoittamalla kaavan =E1*6%Maksimi-ruutuun.

    Huomautus: Jos haluat sallia käyttäjän kirjoittaa prosenttilukuja, esimerkiksi "20 %", valitse Salli-ruudussa Desimaali, valitse haluamasi rajoitus Tiedot-ruudussa, kirjoita pienin arvo, suurin arvo tai tietty arvo desimaalilukuna, esimerkiksi 0,2, ja muotoile tietojen kelpoisuustarkistussolu prosenttiluvuksi valitsemalla solu ja napsauttamalla sitten Aloitus-välilehden Luku-ryhmässä olevaa Prosenttityyli-painiketta Painikkeen kuva .

Syötettävien tietojen rajoittaminen päivämäärävälillä oleviin päivämääriin.

  1. Noudata yllä olevan kohdan Tietojen kelpoisuuden tarkistamisen lisääminen soluun tai alueeseen vaiheita 1–3.

  2. Valitse Hyväksy-ruudusta Päivämäärä.

  3. Valitse Tiedot-ruudusta haluamasi rajoitukset. Voit esimerkiksi hyväksyä tiettyä päivää myöhäisemmät päivämäärät valitsemalla on suurempi kuin.

  4. Anna alkamispäivä tai päättymispäivä tai määritä tarkka sallittu päivämäärä.

    Voit myös kirjoittaa kaavan, joka palauttaa päivämäärän. Voit esimerkiksi määrittää päivämäärävälin kuluvan päivän ja 3 päivän kuluttua olevan päivän välillä valitsemalla on välilläTiedot-ruudussa ja kirjoittamalla =TÄMÄ.PÄIVÄ()Alkamispäivä-ruutuun ja =TÄMÄ.PÄIVÄ()+3Päättymispäivä-ruutuun.

    Kelpoisuustarkistuksen asetukset päivämäärän syötön rajoittamiseksi aikavälillä oleviin aikoihin

Syötettävien tietojen rajoittaminen aikavälillä oleviin kellonaikoihin.

  1. Noudata yllä olevan kohdan Tietojen kelpoisuuden tarkistamisen lisääminen soluun tai alueeseen vaiheita 1–3.

  2. Valitse Hyväksy-ruudusta Aika.

  3. Valitse Tiedot-ruudusta haluamasi rajoitukset. Voit esimerkiksi hyväksyä tiettyä kellonaikaa ennen olevat ajat valitsemalla on pienempi kuin.

  4. Kirjoita alkamis- tai päättymisaika tai jokin tietty hyväksyttävä kellonaika. Jos haluat määrittää tarkan ajan, käytä aikamuotoilua hh:mm.

    Oletetaan esimerkiksi, että olet määrittänyt soluun E2 alkamisajan (klo 8.00) ja soluun F2 päättymisajan (klo 17.00) ja haluat rajata tapaamisajat kyseisten kellonaikojen väliin. Valitse tällöin välillä-vaihtoehto Tiedot-ruudussa, kirjoita =E2Alkamisaika-ruutuun ja kirjoita sitten =F2Päättymisaika-ruutuun.

    Kelpoisuustarkistuksen asetukset ajan syötön rajoittamiseksi aikavälillä oleviin kellonaikoihin

Syötettävien tietojen pituuden rajoittaminen.

  1. Noudata yllä olevan kohdan Tietojen kelpoisuuden tarkistamisen lisääminen soluun tai alueeseen vaiheita 1–3.

  2. Valitse Hyväksy-ruudusta Tekstin pituus.

  3. Valitse Tiedot-ruudusta haluamasi rajoitukset. Voit esimerkiksi hyväksyä tietyn määrän merkkejä valitsemalla on pienempi tai yhtä suuri kuin.

  4. Tässä tapauksessa halutaan rajoittaa merkintä 25 merkkiin, joten valitaan Tiedot-ruudussa pienempi tai yhtä suuri kuin ja kirjoitetaan Enintään-ruutuun 25.

    Esimerkki tietojen kelpoisuuden tarkistamisesta, kun tekstin pituutta on rajoitettu

Sallittavien tietojen laskeminen toisen solun sisällön perusteella.

  1. Noudata yllä olevan kohdan Tietojen kelpoisuuden tarkistamisen lisääminen soluun tai alueeseen vaiheita 1–3. Valitse Hyväksy-ruudusta haluamasi tietotyyppi.

  2. Valitse Tiedot-ruudusta haluamasi rajoitukset.

  3. Valitse ruudusta tai ruuduista Tiedot-ruudun alapuolelta solu, jonka avulla haluat määrittää sallittavat arvot.

    Jos esimerkiksi haluat hyväksyä kirjauksia tilille vain, jos tulos ei ylitä budjettia (E1), valitse Hyväksy > Kokonaisluku, tiedot, pienempi tai yhtä suuri kuin ja Maksimi >= =E1.

    Laskettavat kelpoisuustarkistuksen asetukset toisen solun sisällön perusteella

Huomautus: Seuraavissa esimerkeissä käytetään Mukautettu-vaihtoehtoa, jossa ehdot määritetään kaavojen avulla. Tiedot-ruudussa olevista tiedoista ei tarvitse välittää, sillä ominaisuus on poissa käytöstä Mukautettu-vaihtoehdossa.

Ehto:

Kaava:

Tuotetunnuksen sisältävän solun (C2) alussa on aina oltava etuliite ID- ja tunnuksen on oltava ainakin 10 (suurempi kuin 9) merkin pituinen.

=JA(VASEN(C2, 3) ="ID-",LEN(C2) > 9)

Esimerkki 6: Kaavat tietojen kelpoisuuden tarkistamisessa

Tuotteen nimen sisältävässä solussa (D2) on vain tekstiä.

=ONTEKSTI(D2)

Esimerkki 2: Kaavat tietojen kelpoisuuden tarkistamisessa

Solun, joka sisältää henkilön syntymäpäivän (B6), on oltava suurempi kuin vuosien lukumäärä, joka on määritetty solussa B4.

=JOS(B6<=(TÄNÄÄN()-(365*B4)),TOSI,EPÄTOSI)

Esimerkki tietojen kelpoisuuden tarkistamisesta, kun merkintää rajoitetaan vähimmäisiällä

Kaikki solualueen A2:A10 solut sisältävät ainutkertaisia arvoja.

=LASKE.JOS($A$2:$A$10,A2)=1

Esimerkki 4: Kaavat tietojen kelpoisuuden tarkistamisessa

Huomautus: Tietojen kelpoisuuden tarkistamiskaava on ensin syötettävä soluun A2. Sen jälkeen A2 on kopioitava soluihin A3:A10, jotta LASKE.JOS-kaavan toinen argumentti vastaa nykyistä solua. Eli kaavan osa A2)=1 muuttuu muotoon A3)=1, A4)=1 ja niin edelleen.

Lisätietoja

Varmista, että sähköpostiosoitemerkintä solussa B4 sisältää @-merkin.

=ONLUKU(ETSI("@",B4)

Esimerkki tietojen kelpoisuuden tarkistamisesta, kun varmistetaan, että sähköpostiosoite sisältää @-merkin

  • Miksi Tietojen kelpoisuuden tarkistaminen -komento ei ole käytettävissä valintanauhassa? Mahdollisia syitä siihen, miksi komento ei ole käytettävissä:

    • Microsoft Excel -taulukko voi olla linkitetty SharePoint-sivustoon Et voi määrittää tietojen kelpoisuuden tarkistamista SharePoint-sivustoon linkitettyyn Excel-taulukkoon. Jotta voit määrittää tietojen kelpoisuuden tarkistamisen, Excel-taulukon linkitys on poistettava tai Excel-taulukko on muunnettava alueeksi.

    • Saatat parhaillaan lisätä tietoja Tietojen kelpoisuuden tarkistaminen -komento ei ole käytettävissä Tiedot-välilehdessä, kun kirjoitat tietoja soluun. Lopeta tietojen lisääminen painamalla Enter- tai Esc-näppäintä.

    • Laskentataulukko on ehkä suojattu tai jaettu Et voi muuttaa tietojen kelpoisuuden tarkistamisen asetuksia, jos työkirja on jaettu tai suojattu. Jos haluat lisätietoja työkirjan jakamisen tai suojaamisen lopettamisesta, katso Työkirjan suojaaminen.

  • Voiko fonttikokoa muuttaa? Ei, fonttikoko on kiinteä. Ainoa tapa muuttaa näytön kokoa on säätää Excel-ikkunan oikeassa alakulmassa olevaa näytön zoomausta. Voit kuitenkin käyttää ActiveX-yhdistelmäruutua. Katso Luetteloruudun tai yhdistelmäruudun lisääminen laskentataulukkoon.

  • Onko keinoa, jolla tietojen kelpoisuuden tarkistamista voisi käyttää automaattisen täytön tai automaattisen valinnan yhteydessä kirjoitettaessa? Ei, mutta jos käytät ActiveX-yhdistelmäruutua, kyseinen toiminto on käytettävissä.

  • Voinko tehdä useita valintoja tietojen kelpoisuuden tarkistamisen luettelossa? Et, ellet käytä ActiveX-yhdistelmäruutua tai luetteloruutua.

  • Voinko valita kohteen tietojen kelpoisuuden tarkistamisen luettelosta ja täyttää sillä toisen luettelon? Kyllä! Tätä kutsutaan riippuvaisten tietojen kelpoisuuden tarkistamiseksi. Lisätietoja on artikkelissa Riippuvaisten avattavien luetteloiden luominen.

  • Miten voin poistaa kaikki tietojen kelpoisuuden tarkistamiset laskentataulukosta? Voit käyttää Siirry > Määräten -valintaikkunaa. Valitse Aloitus-välilehti > Muokkaaminen > Etsi ja valitse (tai paina näppäimistöstä F5 tai Ctrl+G), sitten Määräten > Tietojen kelpoisuuden tarkistaminen ja valitse joko Kaikki tai Sama (löydät näin solut, joissa on samat erityiset tietojen kelpoisuuden tarkistamisen asetukset).

    Siirry määräten -valintaikkuna

    Paina tietojen kelpoisuuden tarkistamisen valintaikkunan (Tiedot-välilehti > Tietojen kelpoisuuden tarkistaminen) vieressä Tyhjennä kaikki -painiketta ja sitten OK.

  • Voinko pakottaa käyttäjän antamaan tietoja soluun tai soluihin tietojen kelpoisuuden tarkistamisella? Et, mutta voit tarkistaa VBA:n (Visual Basic for Applications) avulla, onko käyttäjä antanut tietoja määrättyjen ehtojen mukaisesti, kuten ennen työkirjan tallentamista tai sulkemista. Jos käyttäjä ei ole tehnyt valintaa, voit peruuttaa tapahtuman ja estää jatkamisen, kunnes valinta on tehty.

  • Miten soluja voi värjätä tietojen kelpoisuuden tarkistamisen luettelovalinnan perusteella? Voit käyttää ehdollista muotoilua. Tässä tapauksessa kannattaa käyttää Muotoile vain solut, jotka sisältävät -vaihtoehtoa.

    Muotoile vain solut, joissa on asetus
  • Miten sähköpostiosoite vahvistetaan? Voit käyttää Mukautettu > Kaava -menetelmää ja tarkistaa, onko annetuissa tiedoissa @-merkkiä. Tässä tapauksessa käytettävä kaava on =ONLUKU(ETSI(“@”,D2)). ETSI-funktio etsii @-merkkiä. Jos se löytyy, funktio palauttaa merkin numeerisen kohdan tekstimerkkijonossa ja sallii tietojen syöttämisen. Jos sitä ei löydy, ETSI palauttaa virheen ja estää tietojen syöttämisen.

Onko sinulla jokin funktioihin liittyvä kysymys?

Lähetä kysymys Excel-yhteisön keskustelupalstalle

Auta meitä parantamaan Exceliä

Onko sinulla ehdotuksia, miten voimme parantaa seuraavaa Excel-versiota? Jos on, lue aiheet Excel User Voice -palvelussa.

Tutustu myös seuraaviin ohjeaiheisiin

Lisätietoja tietojen kelpoisuuden tarkistamisesta

Video: Avattavien luetteloiden luominen ja hallinta

Kohteiden lisääminen avattavaan luetteloon tai niiden poistaminen siitä

Avattavan luettelon poistaminen

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×