Tietojen validoinnin mukautetut kaavat

Tämä opetusohjelma näyttää, kuinka voit luoda mukautettuja kaavoja Excelin ja Google Sheetsin tietojen validoinnissa

Tietojen validointi - on aloitettava - Excel

Voimme kirjoittaa mukautetun kaavan varmistaaksemme, että solun tiedot alkavat tietyllä tekstillä.

1. Korosta tarvittava alue, esim.: B3: B8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.

3. Valitse Mukautettu avattavasta Salli-ruudusta ja kirjoita sitten seuraava kaava:

= TARKKA (VASEN (B3,4), ”FRU-”)

Kaava käyttää 2 toimintoa TARKKA ja VASEN sen selvittämiseksi, ovatko ensimmäiset 4 soluun syötettyä merkkiä samat kuin "FRU-"

4. Jos haluat lisätä syöttöviestin käyttäjälle, napsauta Syöttöviesti.

5. Varmista, että valintaruutu "Näytä syöttösanoma, kun solu on valittu”On valittuna ja kirjoita sitten haluamasi otsikko ja viesti.
6. Jos haluat lisätä virheilmoituksen, napsauta Virhehälytys.

7. Voit muuttaa tyyliä kohdasta Lopettaa joko Varoitus tai Tiedot jos tarvitset, ja kirjoita sitten otsikko ja virheilmoitus.
8. Klikkaus OK.
9. Jos olet käyttänyt Syöttö Viesti -vaihtoehto, näyttöön tulee kommentti, joka ilmoittaa käyttäjälle säännöstä.

10. Kirjoita soluun B3 ”FRI-124”. Jos olet valinnut Lopettaa, seuraava viestiruutu tulee näkyviin.

11. Jos valitsit Varoitus, tämä viestiruutu tulee näkyviin. Tämän avulla voit jatkaa, jos huomaat, että tiedot ovat oikein.

12. Jos olet valinnut Tiedot, tämä viestiruutu tulee näkyviin.

13. Jos napsautat OK, voit jatkaa soluun syötettyjen virheellisten tietojen kanssa.
14. Klikkaus Peruuttaa poistuaksesi viestistä tai OK kirjoittaaksesi tekstin soluun.

Tietojen tarkistus sallii vain isot kirjaimet Excelissä

Voimme kirjoittaa mukautetun kaavan varmistaaksemme, että solun tiedot sallivat vain isot kirjaimet vain, jos soluun syötetään tekstiä.

1. Korosta tarvittava alue, esim.: B3: B8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse Mukautettu avattavasta Salli-ruudusta ja kirjoita sitten seuraava kaava:

= TARKKA (B3, YLÄ (B3))

Kaava käyttää 2 toimintoa TARKKA ja YLÄ sen selvittämiseksi, onko soluun syötetty teksti isoilla kirjaimilla. Soluja, joissa on luku- ja tekstiseos, pidetään tekstinä ja numerot jätetään huomiotta säännössä.

4. Klikkaus OK.
5. Kirjoita soluun B3 “fru-124”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä valitsemalla Peruuta tai kirjoita oikea teksti uudelleen soluun valitsemalla Yritä uudelleen.
8. Kirjoita soluun B3 ”123456”.
9. Tämä on sallittua, koska se on numero eikä teksti.

Seuraavassa esimerkissämme varmistamme, että soluun voidaan kirjoittaa vain isoja kirjaimia.

Tietojen tarkistus sallii vain isot kirjaimet Excelissä

Voimme kirjoittaa mukautetun kaavan Tietojen validointia voidaan käyttää varmistamaan, että solun tiedot sallivat vain isot kirjaimet

HUOMAUTUS: jos syötät tietoja soluun, joka alkaa tekstillä, mutta sisältää numeroita, Excel ottaa huomioon tietotekstin.

1. Korosta tarvittava alue, esim.: B3: B8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse Mukautettu avattavasta Salli-ruudusta ja kirjoita sitten seuraava kaava:

= JA (EXACT (B3, UPPER (B3)), ISTEXT (B3))

Kaava käyttää 4 toimintoa JA, EXACT, YLÄ ja TEKSTI sen selvittämiseksi, onko soluihin syötetty teksti isoilla kirjaimilla JA sen selvittämiseksi, ovatko syötetyt tiedot todellisuudessa tekstiä eikä puhdasta numeroa.

4. Klikkaus OK.
5. Kirjoita soluun B3 “fru-124”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä valitsemalla Peruuta tai kirjoita oikea teksti uudelleen soluun valitsemalla Yritä uudelleen.
8. Kirjoita soluun B3 ”123456”.
9. Saat jälleen virheilmoituksen.
10. Poistu viestistä valitsemalla Peruuta tai kirjoita oikea teksti uudelleen soluun valitsemalla Yritä uudelleen.

Tietojen validointi Estää välilyöntejä Excelissä

Voimme kirjoittaa mukautetun kaavan varmistaaksemme, että solualueella syötettyihin tietoihin ei syötetä välilyöntejä.

1. Korosta tarvittava alue, esim.: B3: B8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse Mukautettu avattavasta Salli-ruudusta ja kirjoita sitten seuraava kaava:

= B3 = KORVAUS (B3, ””, “”)

Kaava käyttää SUBSTITUTE -toimintoa tarkistaakseen, että välilyöntejä ei ole.

4. Klikkaus OK.
5. Kirjoita soluun B4 ”FRU - 124”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä valitsemalla Peruuta tai kirjoita oikea teksti uudelleen soluun valitsemalla Yritä uudelleen.

Tietojen validointi estää kaksoiskappaleet Excelissä

Voimme kirjoittaa mukautetun kaavan, joka estää meitä syöttämästä päällekkäisiä tietoja solualueelle.

1. Korosta tarvittava alue, esim.: B3: B8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Asiakas ja kirjoita seuraava kaava:

= COUNTIF ($ B $ 3: $ B $ 8, B3) <2

Kaava käyttää COUNTIF toiminto ja käyttötarkoitukset EHDOTTOMIA alueella B3: B8 varmistaaksesi, että tämä on luettelo, jonka COUNTIF -toiminto tarkastelee tarkistettaessa, onko arvoja päällekkäisiä.

4. Klikkaus OK.
5. Kirjoita soluun D4 ”FRU-123”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä napsauttamalla Peruuta tai yritä kirjoittaa oikea teksti uudelleen soluun uudelleen.

Tietojen validointi on Excelin luettelossa

Voimme kirjoittaa mukautetun kaavan varmistaaksemme, että vain tietty teksti syötetään soluun.

1. Korosta tarvittava alue, esim.: D3: D8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Mukautettu ja kirjoita seuraava kaava:

= COUNTIF ($ F $ 6: $ F $ 8, D3)> 0

Kaava käyttää COUNTIF toiminto ja käyttötarkoitukset EHDOTTOMIA alueella F3: F8 varmistaaksesi, että tämä on luettelo COUNTIF toiminto tarkistaa, kun se tarkistaa, että oikea teksti on syötetty.

4. Klikkaus OK.
5. Kirjoita soluun D4 ”Single”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä valitsemalla Peruuta tai kirjoita oikea teksti uudelleen soluun valitsemalla Yritä uudelleen.

Tietojen validointia ei ole Excelin luettelossa

Voimme käyttää mukautettua kaavaa varmistaaksemme, että tiettyä tekstiä ei syötetä soluun.

1. Korosta tarvittava alue, esim.: C3: C8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Asiakas ja kirjoita seuraava kaava:

= COUNTIF ($ F $ 6: $ F $ 8, C3) = 0

Kaava käyttää COUNTIF toiminto ja käyttötarkoitukset EHDOTTOMIA alueella F3: F8 varmistaaksesi, että tämä on luettelo COUNTIF toiminto tarkistaa, kun se tarkistaa, että oikea teksti on syötetty.

4. Klikkaus OK.
5. Kirjoita soluun C4 ”Naudanliha”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä napsauttamalla Peruuta tai yritä kirjoittaa oikea teksti uudelleen soluun uudelleen.

Vain tietojen validoinnin numerot syötetään Excelissä

Voimme käyttää mukautettua kaavaa varmistaaksemme, että soluun syötetään vain numero.

1. Korosta tarvittava alue, esim.: E3: E8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Asiakas ja kirjoita seuraava kaava:

= LUKU (F3: F8)

Kaava käyttää ISNUMBER -funktiota varmistaakseen, että luku syötetään alueen soluihin.

4. Klikkaus OK.
5. Kirjoita soluun F4 ”yhdeksän”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä napsauttamalla Peruuta tai yritä kirjoittaa oikea teksti uudelleen soluun uudelleen.

Tietojen validointi ei ylitä arvoa Excelissä

Voimme käyttää mukautettua kaavaa varmistaaksemme, että soluun syötetyt arvot eivät ylitä määritettyä arvoa.

1. Korosta tarvittava alue, esim.: E3: E8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Asiakas ja kirjoita seuraava kaava:

= E3 <= $ G $ 6

Kaava an EHDOTTOMASTI alueella G6 sen varmistamiseksi, että tämä on arvo, jonka sääntö tarkistaa, kun tiedot syötetään E3: een.

4. Klikkaus OK.
5. Kirjoita soluun E4 ”9”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä napsauttamalla Peruuta tai yritä kirjoittaa oikea teksti uudelleen soluun uudelleen.

Tietojen validointi ei ylitä yhteensä Excelissä

Voimme käyttää mukautettua kaavaa varmistaaksemme, että solualueelle syötetyt arvot eivät ylitä alueen määritettyä kokonaisarvoa

1. Korosta tarvittava alue, esim.: F3: F8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Asiakas ja kirjoita seuraava kaava:

= SUMMA ($ F $ 3: $ F $ 8) <= $ H $ 6

Kaava käyttää SUMMA toiminto ja käyttötarkoitukset EHDOTTOMIA alueella F3: F8 varmistaaksesi, että tämä on luettelo SUMMA toiminto tarkistaa, kun se tarkistaa, ettei alueen kokonaismäärä ole suurempi kuin H6: ssa annettu arvo.

4. Klikkaus OK.
5. Kirjoita soluun F4 “40”.
6. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Poistu viestistä napsauttamalla Peruuta tai yritä kirjoittaa oikea teksti uudelleen soluun uudelleen.

Tietojen validointiviikon päivämäärät vain Excelissä.

Voimme käyttää mukautettua kaavaa varmistaaksemme, että vain arkipäivät syötetään, kun päivämääriä käytetään Excelissä.

1. Korosta tarvittava alue, esim.: G3: G8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Asiakas ja kirjoita seuraava kaava:

= WEEKDAY (F3,2) <6

Arkitoiminto tarkistaa, ettei päivämäärän sisältämä päivä ole lauantai tai sunnuntai.

4. Muuta G5: n päivämäärää näyttämään lauantai (esim. 9^th Toukokuuta 2022).
5. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

7. Anna vaihtoehtoinen päivämäärä napsauttamalla Peruuta tai Yritä uudelleen.

Tietojen validoinnin tuleva päivämäärä vain Excelissä

Voimme luoda mukautetun kaavan, joka sallii käyttäjän syöttää vain tulevaisuuden päivämäärän.

1. Korosta tarvittava alue, esim.: G3: G8.
2. Valitse valintanauhasta Tiedot> Datatyökalut> Tietojen tarkistus.
3. Valitse avattavasta Salli-ruudusta Asiakas ja kirjoita seuraava kaava:

= G3> TODAY ()

Kaava käyttää TÄNÄÄN -toiminto tarkistaa, onko soluun syötetty päivämäärä suurempi kuin tämän päivän päivämäärä.

4. Muuta G5: n päivämäärä eilen.
5. Jos olet käyttänyt Virhehälytys -vaihtoehtoa, mukautettu varoitus- ja virhesanoma tulee näkyviin. Jos et ole käyttänyt tätä vaihtoehtoa, vakiovaroitus tulee näkyviin.

6. Anna vaihtoehtoinen päivämäärä napsauttamalla Peruuta tai Yritä uudelleen.

Tietojen validointi on aloitettava Google Sheetsissä

1. Korosta tarvittava alue, esim.: B3: B8.
2. Valitse valikosta Tiedot> Tietojen validointi.

3. Solualue on jo täytetty.

4. Valitse Mukautettu kaava on avattavasta Ehdot -luettelosta.

5. Kirjoita kaava.

= TARKKA (VASEN (B3,4), ”FRU-”)

6. Valitse jompikumpi Näytä varoitus tai Hylkää syöttö jos tiedot ovat virheellisiä.

7. Voit tarvittaessa kirjoittaa validoinnin ohjetekstiä.

8. Napsauta Tallenna.

9. Napsauta B3 nähdäksesi validoinnin ohjetekstin

10. Tyyppi FRI-123
11. Jos olet valinnut Näytä varoitus, seuraava viesti tulee näkyviin.

12. Vaihtoehtoisesti, jos olet valinnut Hylkää virheellisten tietojen syöttö, et voi syöttää tietoja ja seuraava viesti tulee näyttöön.

Muut Google Sheetsin mukautetun kaavan esimerkit toimivat täsmälleen samalla tavalla.