Formule za preverjanje veljavnosti podatkov po meri

Ta vadnica vam bo pokazala, kako ustvariti formule po meri pri preverjanju podatkov v Excelu in Google Preglednicah

Preverjanje podatkov - začeti se mora - Excel

Lahko napišemo formulo po meri, da zagotovimo, da se podatki v celici začnejo z določenim besedilom.

1. Označite zahtevano območje, na primer: B3: B8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.

3. Izberite Po meri v spustnem polju Dovoli in vnesite naslednjo formulo:

= TOČNO (LEVO (B3,4), "FRU-")

Formula uporablja 2 funkciji TOČNO in LEVO ugotoviti, ali so prvi 4 znaki, vneseni v celico, enaki "FRU-"

4. Če želite vnesti sporočilo za svojega uporabnika, kliknite Vhodno sporočilo.

5. Prepričajte se, da je potrditveno polje »Prikaz vhodnega sporočila, ko je izbrana celica”, Nato vnesite želeni naslov in sporočilo.
6. Če želite dodati opozorilo o napaki, kliknite Opozorilo o napaki.

7. Slog lahko spremenite iz Stop enemu ali drugemu Opozorilo ali Informacije če želite, nato vnesite naslov in sporočilo o napaki.
8. Kliknite v redu.
9. Če ste uporabljali Vnos Možnost sporočila, na zaslonu se prikaže komentar, ki uporabnika obvesti o pravilu.

10. V celico B3 vnesite “FRI-124”. Če ste izbrali Stop, se prikaže naslednje polje s sporočilom.

11. Če ste izbrali Opozorilo, potem se prikaže to sporočilo. To vam omogoča nadaljevanje, če ugotovite, da so podatki pravilni.

12. Če ste izbrali Informacije, potem se prikaže to sporočilo.

13. Če kliknete v redu, boste lahko nadaljevali z napačnimi podatki, vnesenimi v celico.
14. Kliknite Prekliči za izhod iz sporočila oz v redu za vnos besedila v celico.

Preverjanje podatkov dovoljuje velike črke samo v Excelu

Lahko napišemo formulo po meri, da zagotovimo, da podatki v celici dovoljujejo velike črke le, če je v celico vneseno besedilo.

1. Označite zahtevano območje, na primer: B3: B8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. Izberite Po meri v spustnem polju Dovoli in vnesite naslednjo formulo:

= TOČNO (B3, ZGORNJE (B3))

Formula uporablja 2 funkciji TOČNO in ZGORNJI da ugotovite, ali je besedilo, vneseno v celico, z velikimi tiskanimi črkami. Celice z mešanico števila in besedila se štejejo za besedilo, številke pa se v pravilu ne upoštevajo.

4. Kliknite v redu.
5. V celico B3 vnesite »fru-124«.
6. Če ste uporabili možnost Opozorilo o napaki, se bo prikazalo vaše opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.
8. Vnesite »123456« v celico B3.
9. To bo dovoljeno, ker gre za številko in ne za besedilo.

V naslednjem primeru bomo zagotovili, da je v celico mogoče vnesti samo veliko besedilo.

Validacija podatkov Dovoli samo veliko besedilo v Excelu

Formulo po meri lahko zapišemo v Data Validation, s katerim lahko zagotovimo, da podatki v celici dovoljujejo samo veliko besedilo

OPOMBA: če vnesete podatke v celico, ki se začne z besedilom, vendar vsebuje številke, bo Excel upošteval besedilo informacij.

1. Označite zahtevano območje, na primer: B3: B8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. Izberite Po meri v spustnem polju Dovoli in vnesite naslednjo formulo:

= AND (TOČNO (B3, ZGORNJE (B3)), ISTEKST (B3))

Formula uporablja 4 funkcije IN, TOČNO, ZGORNJE in BESEDILO da bi ugotovili, ali je besedilo, vneseno v celice, z velikimi tiskanimi črkami IN ugotoviti, ali so vneseni podatki dejansko besedilo in ne čisto število.

4. Kliknite v redu.
5. V celico B3 vnesite »fru-124«.
6. Če ste uporabili možnost Opozorilo o napaki, se bo prikazalo vaše opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči za izhod iz sporočila ali Poskusi znova za vnos pravilnega besedila v celico.
8. Vnesite »123456« v celico B3.
9. Ponovno boste dobili sporočilo o napaki.
10. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

Validacija podatkov preprečuje presledke v Excelu

Lahko napišemo formulo po meri, da zagotovimo, da v podatke, vnesene v niz celic, ne vnesemo presledkov.

1. Označite zahtevano območje, na primer: B3: B8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. Izberite Po meri v spustnem polju Dovoli in vnesite naslednjo formulo:

= B3 = ZAMENJA (B3, ””, “”)

Formula uporablja funkcijo SUBSTITUTE za preverjanje, ali presledki ne obstajajo.

4. Kliknite v redu.
5. V celico B4 vnesite “FRU - 124”.
6. Če ste uporabili možnost Opozorilo o napaki, se bo prikazalo vaše opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

Validacija podatkov preprečuje podvojene podatke v Excelu

Lahko napišemo formulo po meri, da preprečimo vnos podvojenih podatkov v vrsto celic.

1. Označite zahtevano območje, na primer: B3: B8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Stranka in vnesite naslednjo formulo:

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

Formula uporablja COUNTIF funkcijo in uporabo ABSOLUTES v območju B3: B8, da zagotovite, da je to seznam, ki ga COUNTIF funkcija pogleda, ko preveri, ali obstajajo podvojene vrednosti.

4. Kliknite v redu.
5. V celico D4 vnesite »FRU-123«.
6. Če ste uporabili možnost Opozorilo o napaki, se prikažeta opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

Preverjanje podatkov obstaja na seznamu v Excelu

Lahko napišemo formulo po meri in tako zagotovimo, da je v celico vneseno samo določeno besedilo.

1. Označite zahtevano območje, na primer: D3: D8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Po meri in vnesite naslednjo formulo:

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

Formula uporablja COUNTIF funkcijo in uporabo ABSOLUTES v razponu F3: F8, da zagotovite, da je to seznam, ki ga COUNTIF funkcija pogleda, ko preveri, ali je vneseno pravilno besedilo.

4. Kliknite v redu.
5. V celico D4 vnesite »Single«.
6. Če ste uporabili možnost Opozorilo o napaki, se bo prikazalo vaše opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

Preverjanje podatkov ne obstaja na seznamu v Excelu

Lahko uporabimo formulo po meri, da zagotovimo, da določeno besedilo ni vneseno v celico.

1. Označite zahtevano območje, na primer: C3: C8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Stranka in vnesite naslednjo formulo:

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

Formula uporablja COUNTIF funkcijo in uporabo ABSOLUTES v razponu F3: F8, da zagotovite, da je to seznam, ki ga COUNTIF funkcija pogleda, ko preveri, ali je vneseno pravilno besedilo.

4. Kliknite v redu.
5. V celico C4 vnesite »govedina«.
6. Če ste uporabili možnost Opozorilo o napaki, se bo prikazalo vaše opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

V Excelu so vnesene samo številke za preverjanje podatkov

S formulo po meri lahko zagotovimo, da je v celico vnesena samo številka.

1. Označite zahtevano območje, na primer: E3: E8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Stranka in vnesite naslednjo formulo:

= ISNUMBER (F3: F8)

Formula uporablja funkcijo ISNUMBER za zagotovitev vnosa števila v celice v obsegu.

4. Kliknite v redu.
5. V celico F4 vnesite "devet".
6. Če ste uporabili možnost Opozorilo o napaki, se prikažeta opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

Preverjanje podatkov ne presega vrednosti v Excelu

S formulo po meri lahko zagotovimo, da vnesene vrednosti v celico ne presegajo določene vrednosti.

1. Označite zahtevano območje, na primer: E3: E8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Stranka in vnesite naslednjo formulo:

= E3 <= $ G $ 6

Formula an APSOLUTNO v območju G6, da se prepriča, da je to vrednost, ki jo pravilo preveri, ko se podatki vnesejo v E3.

4. Kliknite v redu.
5. Vnesite "9" v celico E4.
6. Če ste uporabili možnost Opozorilo o napaki, se prikažeta opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

Preverjanje podatkov ne presega skupne vrednosti v Excelu

S formulo po meri lahko zagotovimo, da vrednosti, vnesene v obseg celic, ne presegajo določene skupne vrednosti za obseg

1. Označite zahtevano območje, na primer: F3: F8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Stranka in vnesite naslednjo formulo:

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

Formula uporablja SUM funkcijo in uporabo ABSOLUTES v razponu F3: F8, da zagotovite, da je to seznam, ki ga SUM funkcija pogleda, ko preveri, ali skupni obseg ni večji od vrednosti, vnesene v H6.

4. Kliknite v redu.
5. Vnesite »40« v celico F4.
6. Če ste uporabili možnost Opozorilo o napaki, se prikažeta opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči, da zaprete sporočilo, ali Ponovno poskusite znova vnesti pravilno besedilo v celico.

Validacija podatkov tedenski datumi samo v Excelu.

S formulo po meri lahko zagotovimo, da se pri uporabi datumov v Excelu vnesejo le delovni dnevi.

1. Označite zahtevano območje, na primer: G3: G8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Stranka in vnesite naslednjo formulo:

= TEDEN (F3,2) <6

Funkcija tedenski dan preveri, ali dan v datumu ni sobota ali nedelja.

4. Spremenite datum v G5, da bo prikazan sobota (npr. 9^th Maja 2022).
5. Če ste uporabili možnost Opozorilo o napaki, se prikažeta opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

7. Kliknite Prekliči ali Poskusi znova, da vnesete drug datum.

Prihodnji datum preverjanja podatkov Samo v Excelu

Ustvarimo lahko formulo po meri, ki uporabniku omogoča le vnos datuma, ki je v prihodnosti.

1. Označite zahtevano območje, na primer: G3: G8.
2. Na traku izberite Podatki> Podatkovna orodja> Preverjanje podatkov.
3. V spustnem meniju Dovoli izberite Stranka in vnesite naslednjo formulo:

= G3> DANES ()

Formula uporablja DANES funkcijo, da preverite, ali je datum, vnesen v celico, večji od današnjega.

4. Spremenite datum v G5 na včeraj.
5. Če ste uporabili možnost Opozorilo o napaki, se prikažeta opozorilo in sporočilo o napaki po meri. Če te možnosti niste uporabili, se prikaže standardno opozorilo.

6. Kliknite Prekliči ali Poskusi znova, da vnesete drug datum.

Preverjanje podatkov se mora začeti z v Google Preglednicah

1. Označite zahtevano območje, na primer: B3: B8.
2. V meniju izberite Podatki> Preverjanje podatkov.

3. Obseg celic bo že zapolnjen.

4. Izberite Formula po meri je s spustnega seznama Merila.

5. Vnesite formulo.

= TOČNO (LEVO (B3,4), "FRU-")

6. Izberite eno ali drugo Pokaži opozorilo ali Zavrni vnos če so podatki neveljavni.

7. Po potrebi lahko vnesete nekaj besedila pomoči za preverjanje.

8. Kliknite Shrani.

9. Kliknite B3, če si želite ogledati besedilo pomoči pri preverjanju

10. Tip FRI-123
11. Če ste izbrali Pokaži opozorilo, se prikaže naslednje sporočilo.

12. Druga možnost je, če ste izbrali Zavrni vnos za neveljavne podatke, boste preprečili vnos podatkov in na zaslonu se prikaže naslednje sporočilo.

Preostali primeri formul po meri v Google Preglednicah delujejo na popolnoma enak način.