Datu validācijas pielāgotās formulas

Šī apmācība parādīs, kā izveidot pielāgotas formulas datu validācijā programmā Excel un Google izklājlapas

Datu validācija - jāsāk ar - Excel

Mēs varam uzrakstīt pielāgotu formulu, lai nodrošinātu, ka dati šūnā sākas ar noteiktu tekstu.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: B3: B8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.

3. Izvēlieties Pielāgots nolaižamajā lodziņā Atļaut un pēc tam ierakstiet šādu formulu:

= PRECĪZS (LEFT (B3,4), ”FRU-“)

Formulā tiek izmantotas 2 funkcijas PRECĪZI un KREISIS lai noteiktu, vai pirmās 4 šūnā ievadītās rakstzīmes ir identiskas “FRU-”

4. Ja vēlaties savam lietotājam pievienot ievades ziņojumu, noklikšķiniet uz Ievades ziņojums.

5. Pārliecinieties, vai ir atzīmēta izvēles rūtiņa "Rādīt ievades ziņojumu, kad ir atlasīta šūna”Ir atzīmēts un pēc tam ierakstiet vajadzīgo nosaukumu un ziņojumu.
6. Ja vēlaties pievienot brīdinājumu par kļūdu, noklikšķiniet uz Brīdinājums par kļūdu.

7. Jūs varat mainīt stilu no Apstāties uz abiem Brīdinājums vai Informācija ja nepieciešams, un pēc tam ierakstiet nosaukumu un kļūdas ziņojumu.
8. Klikšķis labi.
9. Ja esat izmantojis Ievadi Ziņojuma opcija, ekrānā parādīsies komentārs, kas informē lietotāju par kārtulu.

10. Šūnā B3 ierakstiet “FRI-124”. Ja esat izvēlējies Apstāties, parādīsies šāds ziņojumu lodziņš.

11. Ja izvēlējāties Brīdinājums, tad parādīsies šis ziņojumu lodziņš. Tas ļauj turpināt, ja konstatējat, ka dati ir pareizi.

12. Ja esat izvēlējies Informācija, tad parādīsies šis ziņojumu lodziņš.

13. Ja noklikšķināsit labi, jums būs atļauts turpināt ar nepareiziem šūnā ievadītajiem datiem.
14. Klikšķis Atcelt lai izietu no ziņas vai labi lai ievadītu tekstu šūnā.

Datu validācija Atļaut lielos burtus tikai programmā Excel

Mēs varam uzrakstīt pielāgotu formulu, lai nodrošinātu, ka šūnas dati atļauj lielos burtus tikai tad, ja šūnā ir ievadīts teksts.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: B3: B8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Izvēlieties Pielāgots nolaižamajā lodziņā Atļaut un pēc tam ierakstiet šādu formulu:

= PRECĪZS (B3, UPPER (B3))

Formulā tiek izmantotas 2 funkcijas PRECĪZI un UPPER lai noteiktu, vai šūnā ievadītais teksts ir ar lielajiem burtiem. Šūnas ar skaitļu un teksta sajaukumu tiek uzskatītas par tekstu, un skaitļi noteikumā tiek ignorēti.

4. Klikšķis labi.
5. Šūnā B3 ierakstiet “fru-124”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.
8. Šūnā B3 ierakstiet “123456”.
9. Tas būs atļauts, jo tas ir skaitlis, nevis teksts.

Nākamajā piemērā mēs nodrošināsim, ka šūnā var ievadīt tikai lielos burtus.

Datu validācija programmā Excel atļauj tikai lielos tekstus

Mēs varam uzrakstīt pielāgotu formulu sadaļā Datu validāciju var izmantot, lai nodrošinātu, ka šūnas dati atļauj tikai lielos burtus

PIEZĪME. Ja ievadāt informāciju šūnā, kas sākas ar tekstu, bet satur ciparus, programma Excel ņem vērā informācijas tekstu.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: B3: B8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Izvēlieties Pielāgots nolaižamajā lodziņā Atļaut un pēc tam ierakstiet šādu formulu:

= UN (PRECĪZS (B3, UPPER (B3)), ISTEXT (B3))

Formulā tiek izmantotas 4 funkcijas UN, EXACT, UPPER un TEKSTS lai noteiktu, vai šūnās ievadītais teksts ir ar lielajiem burtiem UN lai noteiktu, vai ievadītā informācija patiesībā ir teksts, nevis tīrs skaitlis.

4. Klikšķis labi.
5. Šūnā B3 ierakstiet “fru-124”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.
8. Šūnā B3 ierakstiet “123456”.
9. Jūs atkal saņemsit kļūdas ziņojumu.
10. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Datu validācija Novērst atstarpes programmā Excel

Mēs varam uzrakstīt pielāgotu formulu, lai nodrošinātu, ka šūnu diapazonā ievadītajos datos netiek ievadītas atstarpes.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: B3: B8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Izvēlieties Pielāgots nolaižamajā lodziņā Atļaut un pēc tam ierakstiet šādu formulu:

= B3 = AIZSTĀDE (B3, ””, “”)

Formula izmanto funkciju SUBSTITUTE, lai pārbaudītu, vai atstarpes nepastāv.

4. Klikšķis labi.
5. Šūnā B4 ierakstiet “FRU - 124”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Datu validācija novērš dublējumus programmā Excel

Mēs varam uzrakstīt pielāgotu formulu, lai neļautu mums ievadīt dublētu informāciju šūnu diapazonā.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: B3: B8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Klients un pēc tam ierakstiet šādu formulu:

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

Formulā tiek izmantots COUNTIF funkcija un pielietojums ABSOLŪTI diapazonā B3: B8, lai pārliecinātos, ka šis ir saraksts COUNTIF funkcija pārbauda, ​​pārbaudot, vai nav dublējušās vērtības.

4. Klikšķis labi.
5. Šūnā D4 ierakstiet “FRU-123”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Datu validācija eksistē Excel sarakstā

Mēs varam uzrakstīt pielāgotu formulu, lai šūnā tiktu ievadīts tikai konkrēts teksts.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: D3: D8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Pielāgots un pēc tam ierakstiet šādu formulu:

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

Formulā tiek izmantots COUNTIF funkcija un pielietojums ABSOLŪTI diapazonā F3: F8, lai pārliecinātos, ka šis ir saraksts COUNTIF funkcija skatās, pārbaudot, vai tiek ievadīts pareizs teksts.

4. Klikšķis labi.
5. Šūnā D4 ierakstiet “Viens”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Datu validācija Excel sarakstā neeksistē

Mēs varam izmantot pielāgotu formulu, lai nodrošinātu, ka šūnā netiek ievadīts konkrēts teksts.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: C3: C8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Klients un pēc tam ierakstiet šādu formulu:

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

Formulā tiek izmantots COUNTIF funkcija un pielietojums ABSOLŪTI diapazonā F3: F8, lai pārliecinātos, ka šis ir saraksts COUNTIF funkcija skatās, pārbaudot, vai tiek ievadīts pareizs teksts.

4. Klikšķis labi.
5. Šūnā C4 ierakstiet “Liellopu gaļa”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Programmā Excel tiek ievadīti tikai datu validācijas numuri

Mēs varam izmantot pielāgotu formulu, lai nodrošinātu, ka šūnā tiek ievadīts tikai skaitlis.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: E3: E8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Klients un pēc tam ierakstiet šādu formulu:

= ISNUMBER (F3: F8)

Formula izmanto funkciju ISNUMBER, lai nodrošinātu, ka diapazona šūnās tiek ievadīts skaitlis.

4. Klikšķis labi.
5. Šūnā F4 ierakstiet “deviņi”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Datu validācija nepārsniedz vērtību programmā Excel

Mēs varam izmantot pielāgotu formulu, lai nodrošinātu, ka šūnā ievadītās vērtības nepārsniedz norādīto vērtību.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: E3: E8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Klients un pēc tam ierakstiet šādu formulu:

= E3 <= $ G $ 6

Formula an ABSOLŪTS diapazonā G6, lai pārliecinātos, ka šī ir vērtība, ko noteikums pārbauda, ​​ievadot datus E3.

4. Klikšķis labi.
5. Šūnā E4 ierakstiet “9”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Datu validācija programmā Excel nepārsniedz kopsummu

Mēs varam izmantot pielāgotu formulu, lai nodrošinātu, ka šūnu diapazonā ievadītās vērtības nepārsniedz noteikto diapazona kopējo vērtību

1. Iezīmējiet vajadzīgo diapazonu, piemēram: F3: F8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Klients un pēc tam ierakstiet šādu formulu:

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

Formulā tiek izmantots SUM funkcija un pielietojums ABSOLŪTI diapazonā F3: F8, lai pārliecinātos, ka šis ir saraksts SUM funkcija pārbauda, ​​pārbaudot, vai diapazona kopsumma nav lielāka par H6 ievadīto vērtību.

4. Klikšķis labi.
5. Šūnā F4 ierakstiet “40”.
6. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt, lai izietu no ziņojuma, vai Mēģiniet vēlreiz, lai šūnā atkārtoti ievadītu pareizo tekstu.

Datu validācijas nedēļas dienu datumi tikai programmā Excel.

Mēs varam izmantot pielāgotu formulu, lai nodrošinātu, ka datumi programmā Excel tiek ievadīti tikai darba dienās.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: G3: G8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Klients un pēc tam ierakstiet šādu formulu:

= NEDĒĻAS DIENA (F3,2) <6

Darbdienas funkcija pārbauda, ​​vai datumā iekļautā diena nav sestdiena vai svētdiena.

4. Mainiet datumu G5, lai parādītu sestdienu (piemēram, 9^tūkst 2022. gada maijs).
5. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

7. Noklikšķiniet uz Atcelt vai Mēģināt vēlreiz, lai ievadītu citu datumu.

Datu validācijas nākotnes datums tikai programmā Excel

Mēs varam izveidot pielāgotu formulu, lai lietotājs varētu ievadīt tikai datumu, kas ir nākotnē.

1. Iezīmējiet vajadzīgo diapazonu, piemēram: G3: G8.
2. Lentē atlasiet Dati> Datu rīki> Datu validācija.
3. Nolaižamajā lodziņā Atļaut atlasiet Klients un pēc tam ierakstiet šādu formulu:

= G3> ŠODIEN ()

Formulā tiek izmantots ŠODIEN funkciju, lai pārbaudītu, vai šūnā ievadītais datums ir lielāks par šodienas datumu.

4. Mainiet datumu G5 uz vakardienu.
5. Ja esat izmantojis opciju Kļūdas brīdinājums, parādīsies jūsu pielāgotais brīdinājums un kļūdas ziņojums. Ja neesat izmantojis šo iespēju, parādīsies standarta brīdinājums.

6. Noklikšķiniet uz Atcelt vai Mēģināt vēlreiz, lai ievadītu citu datumu.

Datu validācija jāsāk ar Google izklājlapām

1. Iezīmējiet vajadzīgo diapazonu, piemēram: B3: B8.
2. Izvēlnē izvēlieties Dati> Datu validācija.

3. Šūnu diapazons jau būs aizpildīts.

4. Izvēlieties Pielāgota formula ir no nolaižamā saraksta Kritēriji.

5. Ierakstiet formulu.

= PRECĪZS (LEFT (B3,4), ”FRU-“)

6. Izvēlieties vai nu Rādīt brīdinājumu vai Noraidīt ievadi ja dati ir nederīgi.

7. Ja nepieciešams, varat ierakstīt validācijas palīdzības tekstu.

8. Noklikšķiniet uz Saglabāt.

9. Noklikšķiniet uz B3, lai redzētu validācijas palīdzības tekstu

10. Tips FRI-123
11. Ja esat izvēlējies Rādīt brīdinājumu, parādīsies šāds ziņojums.

12. Alternatīvi, ja esat izvēlējies Noraidīt nederīgu datu ievadi, jūs nevarēsit ievadīt datus, un ekrānā parādīsies šāds ziņojums.

Pārējie pielāgotās formulas piemēri Google izklājlapās darbojas tieši tāpat.