Prilagođene formule za provjeru valjanosti podataka

Ovaj će vam vodič pokazati kako stvoriti prilagođene formule u provjeri valjanosti podataka u Excelu i Google tablicama

Validacija podataka - mora početi s - Excel

Možemo napisati prilagođenu formulu kako bismo osigurali da podaci u ćeliji počinju određenim tekstom.

1. Označite potrebni raspon, npr .: B3: B8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.

3. Odaberi Prilagođen iz padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

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

Formula koristi 2 funkcije TOČNO i LIJEVO kako bi se utvrdilo jesu li prva 4 znaka unesena u ćeliju identična "FRU-"

4. Ako želite dodati ulaznu poruku za svog korisnika, kliknite Ulazna poruka.

5. Provjerite okvir "Prikaz ulazne poruke kada je odabrana ćelija”Je označeno, a zatim upišite naslov i poruku koju tražite.
6. Ako želite dodati upozorenje o pogrešci, kliknite Upozorenje o pogrešci.

7. Stil možete promijeniti iz Stop bilo kojem Upozorenje ili Informacija ako želite, a zatim upišite naslov i poruku o pogrešci.
8. Klik u redu.
9. Ako ste koristili Ulazni Opcija poruke, na zaslonu će se pojaviti komentar koji korisnika informira o pravilu.

10. Upišite "FRI-124" u ćeliju B3. Ako ste odabrali Stop, pojavit će se sljedeći okvir s porukom.

11. Ako ste odabrali Upozorenje, tada će se pojaviti ovaj okvir s porukom. To vam omogućuje nastavak ako utvrdite da su podaci točni.

12. Ako ste odabrali Informacija, tada će se pojaviti ovaj okvir s porukom.

13. Ako kliknete u redu, bit će vam dopušteno nastaviti s netočnim podacima unesenim u ćeliju.
14. Klik Otkazati za izlaz iz poruke ili u redu za unos teksta u ćeliju.

Validacija podataka dopušta velika slova samo u Excelu

Možemo napisati prilagođenu formulu kako bismo osigurali da podaci u ćeliji dopuštaju velika slova samo ako se tekst unese u ćeliju.

1. Označite potrebni raspon, npr .: B3: B8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberi Prilagođen iz padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

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

Formula koristi 2 funkcije TOČNO i GORNJI kako bi se utvrdilo je li tekst unesen u ćeliju velikim slovima. Ćelije s mješavinom broja i teksta smatraju se tekstom, a brojevi se zanemaruju u pravilu.

4. Klik u redu.
5. Upišite "fru-124" u ćeliju B3.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.
8. Upišite "123456" u ćeliju B3.
9. To će biti dopušteno jer je to broj, a ne tekst.

U našem sljedećem primjeru pobrinut ćemo se da se samo veliki tekst može unijeti u ćeliju.

Validacija podataka dopušta samo veliki tekst u Excelu

Možemo napisati prilagođenu formulu u Validacija podataka koja se može koristiti za osiguravanje da podaci u ćeliji dopuštaju samo velika slova

NAPOMENA: ako unesete podatke u ćeliju koja počinje tekstom, ali sadrži brojeve, Excel će razmotriti tekst informacija.

1. Označite potrebni raspon, npr .: B3: B8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberi Prilagođen iz padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

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

Formula koristi 4 funkcije I, TOČNO, GORNJE i TEKST kako bi se utvrdilo je li tekst koji se unosi u ćelije velikim slovima I kako bi se utvrdilo jesu li uneseni podaci zapravo tekst, a ne čisti broj.

4. Klik u redu.
5. Upišite "fru-124" u ćeliju B3.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.
8. Upišite "123456" u ćeliju B3.
9. Ponovno ćete dobiti poruku o pogrešci.
10. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

Validacija podataka sprječava prostore u Excelu

Možemo napisati prilagođenu formulu kako bismo osigurali da se u podatke unesene u niz ćelija ne unose razmaci.

1. Označite potrebni raspon, npr .: B3: B8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberi Prilagođen iz padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

= B3 = ZAMJENA (B3, ””, “”)

Formula koristi funkciju ZAMJENA za provjeru da prostori ne postoje.

4. Klik u redu.
5. Upišite "FRU - 124" u ćeliju B4.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

Validacija podataka sprječava dupliciranje u Excelu

Možemo napisati prilagođenu formulu kako bismo spriječili unos dupliciranih podataka u niz ćelija.

1. Označite potrebni raspon, npr .: B3: B8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Kupac s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

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

Formula koristi COUNTIF funkciju i upotrebu APSOLUTNO u rasponu B3: B8 kako bi bili sigurni da je ovo popis koji COUNTIF funkcija gleda kada provjerava ima li dupliciranih vrijednosti.

4. Klik u redu.
5. Upišite "FRU-123" u ćeliju D4.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

Validacija podataka postoji na popisu u Excelu

Možemo napisati prilagođenu formulu kako bismo osigurali da se samo određeni tekst unese u ćeliju.

1. Označite potrebni raspon, npr .: D3: D8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Prilagođeno s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

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

Formula koristi COUNTIF funkciju i upotrebu APSOLUTNO u rasponu F3: F8 kako bi bili sigurni da je ovo popis koji COUNTIF funkcija gleda kada provjerava je li unet ispravan tekst.

4. Klik u redu.
5. Upišite "Single" u ćeliju D4.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

Validacija podataka ne postoji na popisu u Excelu

Možemo koristiti prilagođenu formulu kako bismo osigurali da određeni tekst nije unesen u ćeliju.

1. Označite potrebni raspon, npr .: C3: C8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Kupac s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

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

Formula koristi COUNTIF funkciju i upotrebu APSOLUTNO u rasponu F3: F8 kako bi bili sigurni da je ovo popis koji COUNTIF funkcija gleda kada provjerava je li unet ispravan tekst.

4. Klik u redu.
5. Upišite "govedina" u ćeliju C4.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

U Excel se unose samo brojevi za provjeru valjanosti podataka

Možemo koristiti prilagođenu formulu kako bismo osigurali da se u ćeliju unese samo broj.

1. Označite potrebni raspon, npr .: E3: E8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Kupac s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

= ISNUMBER (F3: F8)

Formula koristi funkciju ISNUMBER kako bi osigurala unos broja u ćelije u rasponu.

4. Klik u redu.
5. Upišite "devet" u ćeliju F4.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

Validacija podataka ne prelazi vrijednost u Excelu

Možemo koristiti prilagođenu formulu kako bismo osigurali da vrijednosti unete u ćeliju ne prelaze navedenu vrijednost.

1. Označite potrebni raspon, npr .: E3: E8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Kupac s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

= E3 <= $ G $ 6

Formula an APSOLUTNO u rasponu G6 kako bi se osiguralo da je to vrijednost koju pravilo provjerava pri unosu podataka u E3.

4. Klik u redu.
5. Upišite “9” u ćeliju E4.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

Validacija podataka ne premašuje ukupnu vrijednost u Excelu

Možemo koristiti prilagođenu formulu kako bismo osigurali da vrijednosti unete u raspon ćelija ne prelaze specificiranu ukupnu vrijednost za raspon

1. Označite potrebni raspon, npr .: F3: F8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Kupac s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

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

Formula koristi IZNOS funkciju i upotrebu APSOLUTNO u rasponu F3: F8 kako bi bili sigurni da je ovo popis koji IZNOS funkcija gleda kada provjerava da li ukupni raspon nije veći od vrijednosti upisane u H6.

4. Klik u redu.
5. Upišite "40" u ćeliju F4.
6. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani za izlaz iz poruke ili Pokušaj ponovno za ponovni unos ispravnog teksta u ćeliju.

Validacija podataka Radni dani samo u Excelu.

Možemo koristiti prilagođenu formulu kako bismo osigurali da se unose samo radni dani kada se datumi koriste u Excelu.

1. Označite potrebni raspon, npr .: G3: G8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Kupac s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

= TJEDAN (F3,2) <6

Funkcija radni dan provjerava je li dan koji se nalazi unutar datuma subota ili nedjelja.

4. Izmijenite datum u G5 tako da prikazuje subotu (npr. 9^th Svibnja 2022).
5. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

7. Pritisnite Odustani ili Pokušaj ponovno za unos alternativnog datuma.

Validacija podataka Budući datum Samo u Excelu

Možemo stvoriti prilagođenu formulu koja će omogućiti korisniku samo unos datuma koji je u budućnosti.

1. Označite potrebni raspon, npr .: G3: G8.
2. Na vrpci odaberite Podaci> Podatkovni alati> Validacija podataka.
3. Odaberite Kupac s padajućeg okvira Dopusti, a zatim upišite sljedeću formulu:

= G3> DANAS ()

Formula koristi DANAS funkciju za provjeru je li datum unesen u ćeliju veći od današnjeg.

4. Izmijenite datum u G5 na jučer.
5. Ako ste koristili opciju Upozorenje o pogrešci, pojavit će se vaše prilagođeno upozorenje i poruka o pogrešci. Ako niste koristili ovu opciju, pojavit će se standardno upozorenje.

6. Pritisnite Odustani ili Pokušaj ponovno za unos alternativnog datuma.

Provjera valjanosti podataka mora započeti s u Google tablicama

1. Označite potrebni raspon, npr .: B3: B8.
2. U izborniku odaberite Podaci> Validacija podataka.

3. Raspon ćelija već će biti popunjen.

4. Odaberi Prilagođena formula je s padajuće liste Kriteriji.

5. Upišite formulu.

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

6. Odaberite bilo koji Pokaži upozorenje ili Odbijte unos ako su podaci neispravni.

7. Ako želite, možete upisati neki tekst pomoći za provjeru valjanosti.

8. Pritisnite Spremi.

9. Pritisnite B3 da biste vidjeli tekst pomoći za provjeru valjanosti

10. Tip FRI-123
11. Ako ste odabrali Prikaži upozorenje, pojavit će se sljedeća poruka.

12. Alternativno, ako ste odabrali Odbijte unos nevažećih podataka, bit ćete spriječeni u unosu podataka, a na zaslonu će se pojaviti sljedeća poruka.

Ostatak primjera prilagođenih formula u Google tablicama radi na potpuno isti način.