Andmete valideerimise kohandatud valemid

See õpetus näitab teile, kuidas Excelis ja Google'i arvutustabelites andmete valideerimisel kohandatud valemeid luua

Andmete valideerimine - tuleb alustada - Excel

Me võime kirjutada kohandatud valemi, et tagada lahtris olevate andmete algus teatud tekstiga.

1. Tõstke esile vajalik vahemik, nt: B3: B8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.

3. Valige Kohandatud rippmenüüst Luba ja tippige järgmine valem:

= TÄPSE (VASAK (B3,4), "FRU-")

Valem kasutab 2 funktsiooni TÄPSELT ja VASAK et teha kindlaks, kas esimesed neli lahtrisse sisestatud märki on identsed sõnaga „FRU-”

4. Kui soovite oma kasutajale sisestussõnumi lisada, klõpsake nuppu Sisestussõnum.

5. Veenduge, et linnuke oleks "Kuva sisendteade, kui lahter on valitud”Ja seejärel sisestage soovitud pealkiri ja sõnum.
6. Kui soovite lisada veateate, klõpsake nuppu Veahoiatus.

7. Stiili saate muuta Peatus kummalegi Hoiatus või Teave kui vaja, ja sisestage seejärel pealkiri ja veateade.
8. Klõpsake nuppu Okei.
9. Kui olete kasutanud Sisend Sõnumi valik, ekraanile ilmub kommentaar, mis teavitab kasutajat reeglist.

10. Tippige lahtrisse B3 „FRI-124”. Kui olete valinud Peatus, kuvatakse järgmine sõnumikast.

11. Kui valisite Hoiatus, siis ilmub see sõnumikast. See võimaldab teil jätkata, kui leiate, et andmed on õiged.

12. Kui olete valinud Teave, siis ilmub see sõnumikast.

13. Kui klõpsate Okei, lubatakse teil jätkata lahtrisse sisestatud valede andmetega.
14. Klõpsake nuppu Tühista sõnumist väljumiseks või Okei teksti sisestamiseks lahtrisse.

Andmete valideerimine lubab Excelis ainult suurtähti

Võime kirjutada kohandatud valemi tagamaks, et lahtri andmed lubavad suurtähti ainult siis, kui lahtrisse sisestatakse tekst.

1. Tõstke esile vajalik vahemik, nt: B3: B8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige Kohandatud rippmenüüst Luba ja tippige järgmine valem:

= TÄPSELT (B3, ÜLEMINE (B3))

Valem kasutab 2 funktsiooni TÄPSELT ja ÜLES et määrata, kas lahtrisse sisestatud tekst on suurtähtedega. Numbri ja teksti seguga lahtreid loetakse tekstiks ja reegleid ignoreeritakse.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse B3 „fru-124”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.
8. Tippige lahtrisse B3 „123456”.
9. See on lubatud, kuna see on number ja mitte tekst.

Järgmises näites tagame, et lahtrisse saab sisestada ainult suurtähti.

Andmete valideerimine lubab Excelis ainult suurtähti

Me võime kirjutada kohandatud valemi andmete valideerimist saab kasutada tagamaks, et lahtri andmed lubavad ainult suurtähti

MÄRKUS. Kui sisestate teabe lahtrisse, mis algab tekstiga, kuid sisaldab numbreid, arvestab Excel teabeteksti.

1. Tõstke esile vajalik vahemik, nt: B3: B8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige Kohandatud rippmenüüst Luba ja tippige järgmine valem:

= JA (TÄPSELT (B3, ÜLEMINE (B3)), ISTEXT (B3))

Valem kasutab 4 funktsiooni JA, TÄPSELT, ÜLES ja TEKST et määrata, kas lahtritesse sisestatud tekst on suurtähtedega JA et teha kindlaks, kas sisestatud teave on tegelikult tekst ja mitte puhas number.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse B3 „fru-124”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.
8. Tippige lahtrisse B3 „123456”.
9. Saate uuesti veateate.
10. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Andmete valideerimine Excelis tühikute ärahoidmine

Saame kirjutada kohandatud valemi tagamaks, et lahtrite vahemikku sisestatud andmetesse ei sisestata tühikuid.

1. Tõstke esile vajalik vahemik, nt: B3: B8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige Kohandatud rippmenüüst Luba ja tippige järgmine valem:

= B3 = ASENDUS (B3, ””, “”)

Valem kasutab funktsiooni SUBSTITUTE, et kontrollida tühikute puudumist.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse B4 “FRU - 124”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Andmete valideerimine takistab Exceli duplikaate

Saame kirjutada kohandatud valemi, mis takistab meil topeltteabe sisestamist lahtrivahemikku.

1. Tõstke esile vajalik vahemik, nt: B3: B8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba Klient ja tippige järgmine valem:

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

Valem kasutab COUNTIF funktsiooni ja kasutusalasid ABSOLUUTID vahemikus B3: B8, et tagada, et see on nimekiri, mida COUNTIF funktsioon vaatab, kui ta kontrollib, kas on dubleeritud väärtusi.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse D4 “FRU-123”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Andmete valideerimine on Exceli loendis olemas

Saame kirjutada kohandatud valemi, et tagada lahtrisse ainult konkreetse teksti sisestamine.

1. Tõstke esile vajalik vahemik, nt: D3: D8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba kohandatud ja tippige järgmine valem:

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

Valem kasutab COUNTIF funktsiooni ja kasutusalasid ABSOLUUTID vahemikus F3: F8, et tagada, et see on nimekiri COUNTIF funktsioon vaatab, kui kontrollib õige teksti sisestamist.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse D4 „Single”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Andmete valideerimist Exceli loendis ei eksisteeri

Saame kasutada kohandatud valemit, et kindlat teksti lahtrisse ei sisestataks.

1. Tõstke esile vajalik vahemik, nt: C3: C8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba Klient ja tippige järgmine valem:

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

Valem kasutab COUNTIF funktsiooni ja kasutusalasid ABSOLUUDID vahemikus F3: F8, et tagada, et see on nimekiri COUNTIF funktsioon vaatab, kui kontrollib õige teksti sisestamist.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse C4 „Beef”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Excelisse sisestatakse ainult andmete valideerimise numbrid

Saame kasutada kohandatud valemit, et tagada lahtrisse ainult arvu sisestamine.

1. Tõstke esile vajalik vahemik, nt: E3: E8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba Klient ja tippige järgmine valem:

= ISNUMBER (F3: F8)

Valem kasutab funktsiooni ISNUMBER tagamaks, et vahemiku lahtritesse sisestatakse arv.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse F4 „üheksa“.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Andmete valideerimine ei ületa Excelis väärtust

Saame kasutada kohandatud valemit tagamaks, et lahtrisse sisestatud väärtused ei ületaks määratud väärtust.

1. Tõstke esile vajalik vahemik, nt: E3: E8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba Klient ja tippige järgmine valem:

= E3 <= $ G $ 6

Valem an ABSOLUUTNE vahemikus G6, et tagada see väärtus, mida reegel kontrollib andmete sisestamisel E3 -sse.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse E4 “9”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Andmete valideerimine ei ületa Excelis kokku

Saame kasutada kohandatud valemit tagamaks, et lahtrivahemikku sisestatud väärtused ei ületa vahemiku määratud koguväärtust

1. Tõstke esile vajalik vahemik, nt: F3: F8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba Klient ja tippige järgmine valem:

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

Valem kasutab SUM funktsiooni ja kasutusalasid ABSOLUUTID vahemikus F3: F8, et tagada, et see on nimekiri SUM funktsioon vaatab, kui kontrollib, et vahemiku kogusumma ei oleks suurem kui H6 sisestatud väärtus.

4. Klõpsake nuppu Okei.
5. Tippige lahtrisse F4 „40”.
6. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Sõnumist väljumiseks klõpsake nuppu Tühista või proovige uuesti, et sisestada lahtrisse õige tekst.

Andmete valideerimise nädalapäevad Kuupäevad ainult Excelis.

Me saame kasutada kohandatud valemit, et tagada Excelis kuupäevade kasutamisel ainult nädalapäevad.

1. Tõstke esile vajalik vahemik, nt: G3: G8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba Klient ja tippige järgmine valem:

= NÄDALAPÄEV (F3,2) <6

Nädalapäeva funktsioon kontrollib, kas kuupäeva sees olev päev ei ole laupäev ega pühapäev.

4. Muutke G5 kuupäeva, et näidata laupäeva (nt 9^th Mai 2022).
5. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

7. Alternatiivse kuupäeva sisestamiseks klõpsake nuppu Tühista või Proovi uuesti.

Andmete valideerimise tulevane kuupäev Ainult Excelis

Saame luua kohandatud valemi, mis võimaldab kasutajal sisestada ainult tuleviku kuupäeva.

1. Tõstke esile vajalik vahemik, nt: G3: G8.
2. Valige lindil Andmed> Andmetööriistad> Andmete valideerimine.
3. Valige rippmenüüst Luba Klient ja tippige järgmine valem:

= G3> TÄNA ()

Valem kasutab TÄNA funktsiooni, et kontrollida, kas lahtrisse sisestatud kuupäev on suurem kui tänane kuupäev.

4. Muutke G5 kuupäev eilsesse.
5. Kui olete kasutanud suvandit Veahoiatus, kuvatakse teie kohandatud hoiatus- ja veateade. Kui te pole seda võimalust kasutanud, kuvatakse standardhoiatus.

6. Alternatiivse kuupäeva sisestamiseks klõpsake nuppu Tühista või Proovi uuesti.

Andmete valideerimine peab algama Google'i arvutustabelites

1. Tõstke esile vajalik vahemik, nt: B3: B8.
2. Valige menüüst Andmed> Andmete valideerimine.

3. Lahtrite vahemik on juba täidetud.

4. Valige Kohandatud valem on ripploendist Kriteeriumid.

5. Sisestage valem.

= TÄPSE (VASAK (B3,4), "FRU-")

6. Valige kas Kuva hoiatus või Sisestuse tagasilükkamine kui andmed on kehtetud.

7. Vajadusel saate sisestada valideerimise abiteksti.

8. Klõpsake nuppu Salvesta.

9. Valideerimise abiteksti vaatamiseks klõpsake nuppu B3

10. Tüüp FRI-123
11. Kui olete valinud Kuva hoiatus, kuvatakse järgmine teade.

12. Teise võimalusena, kui olete valinud Keelake valede andmete sisestamine, ei saa te andmeid sisestada ja ekraanile ilmub järgmine teade.

Ülejäänud kohandatud valeminäited Google'i arvutustabelites toimivad täpselt samamoodi.