Formule personalizate de validare a datelor

Acest tutorial vă va arăta cum să creați formule personalizate în validarea datelor în Excel și Foi de calcul Google

Validarea datelor - trebuie să înceapă cu - Excel

Putem scrie o formulă personalizată pentru a ne asigura că datele dintr-o celulă încep cu un anumit text.

1. Evidențiați gama necesară, de exemplu: B3: B8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.

3. Selectați Personalizat din caseta derulantă Permiteți, apoi tastați următoarea formulă:

= EXACT (STÂNGA (B3,4), „FRU-“)

Formula folosește 2 funcții CORECT și STÂNGA pentru a determina dacă primele 4 caractere introduse în celulă sunt identice cu „FRU-”

4. Dacă doriți să adăugați un mesaj de intrare pentru utilizatorul dvs., faceți clic pe Mesaj de intrare.

5. Asigurați-vă că bifați caseta „Afișați mesajul de intrare când este selectată celula”Este bifat și apoi introduceți titlul și mesajul de care aveți nevoie.
6. Dacă doriți să adăugați o alertă de eroare, faceți clic pe Alertă de eroare.

7. Puteți schimba stilul din Stop la oricare Avertizare sau informație dacă aveți nevoie, apoi introduceți titlul și mesajul de eroare.
8. Clic Bine.
9. Dacă ați folosit Intrare Opțiunea Mesaj, un comentariu va apărea pe ecran pentru a informa utilizatorul despre regulă.

10. Tastați „FRI-124” în celula B3. Dacă ați selectat Stop, va apărea următoarea casetă de mesaj.

11. Dacă ați selectat Avertizare, atunci va apărea această casetă de mesaj. Acest lucru vă permite să continuați dacă stabiliți că datele sunt corecte.

12. Dacă ați selectat informație, atunci va apărea această casetă de mesaj.

13. Dacă faceți clic Bine, vi se va permite să continuați cu datele incorecte introduse în celulă.
14. Clic Anulare pentru a ieși din mesaj sau Bine pentru a introduce textul în celulă.

Validarea datelor permite numai majuscule în Excel

Putem scrie o formulă personalizată pentru a ne asigura că datele dintr-o celulă permit majuscule numai dacă este introdus text în celulă.

1. Evidențiați gama necesară, de exemplu: B3: B8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Personalizat din caseta derulantă Permiteți, apoi tastați următoarea formulă:

= EXACT (B3, SUPERIOR (B3))

Formula folosește 2 funcții CORECT și SUPERIOR pentru a determina dacă textul introdus în celulă este în majuscule. Celulele cu un amestec de număr și text sunt considerate text, iar numerele sunt ignorate în regulă.

4. Clic Bine.
5. Tastați „fru-124” în celula B3.
6. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.
8. Tastați „123456” în celula B3.
9. Acest lucru va fi permis deoarece este un număr și nu text.

În următorul nostru exemplu, ne vom asigura că numai textul cu majuscule poate fi introdus în celulă.

Validarea datelor permite numai text cu majuscule numai în Excel

Putem scrie o formulă personalizată în Validarea datelor poate fi utilizată pentru a ne asigura că datele dintr-o celulă permit doar text cu majuscule

NOTĂ: dacă introduceți informații într-o celulă care începe cu text, dar conține numere, Excel va lua în considerare textul informațional.

1. Evidențiați gama necesară, de exemplu: B3: B8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Personalizat din caseta derulantă Permiteți, apoi tastați următoarea formulă:

= ȘI (EXACT (B3, SUPERIOR (B3)), ISTEXT (B3))

Formula folosește 4 funcții ȘI, EXACT, SUPERIOR și TEXT pentru a determina dacă textul introdus în celule este în majuscule ȘI pentru a determina dacă informațiile introduse sunt de fapt text și nu un număr pur.

4. Clic Bine.
5. Tastați „fru-124” în celula B3.
6. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.
8. Tastați „123456” în celula B3.
9. Veți primi din nou mesajul de eroare.
10. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Validarea datelor Prevenirea spațiilor în Excel

Putem scrie o formulă personalizată pentru a ne asigura că nu sunt introduse spații în datele introduse într-o gamă de celule.

1. Evidențiați gama necesară, de exemplu: B3: B8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Personalizat din caseta derulantă Permiteți, apoi tastați următoarea formulă:

= B3 = SUBSTITUT (B3, ””, “”)

Formula folosește funcția SUBSTITUTE pentru a verifica dacă spațiile nu există.

4. Clic Bine.
5. Tastați „FRU - 124” în celula B4.
6. Dacă ați utilizat opțiunea Alertă de eroare, va apărea avertismentul personalizat și mesajul de eroare. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Validarea datelor împiedică duplicatele în Excel

Putem scrie o formulă personalizată pentru a ne împiedica să introducem informații duplicate într-o gamă de celule.

1. Evidențiați gama necesară, de exemplu: B3: B8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Client din caseta derulantă Permite, apoi tastați următoarea formulă:

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

Formula folosește COUNTIF funcția și utilizările ABSOLUTE în intervalul B3: B8 pentru a vă asigura că aceasta este lista pe care COUNTIF funcția analizează când verifică dacă există valori duplicate.

4. Clic Bine.
5. Tastați „FRU-123” în celula D4.
6. Dacă ați utilizat opțiunea Alertă de eroare, va apărea avertismentul personalizat și mesajul de eroare. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Validarea datelor există în Listă în Excel

Putem scrie o formulă personalizată pentru a ne asigura că numai textul specific este introdus într-o celulă.

1. Evidențiați gama necesară, de exemplu: D3: D8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Personalizat din caseta derulantă Permiteți, apoi tastați următoarea formulă:

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

Formula folosește COUNTIF funcția și utilizările ABSOLUTE în intervalul F3: F8 pentru a vă asigura că aceasta este lista pe care COUNTIF funcția se uită când verifică dacă se introduce textul corect.

4. Clic Bine.
5. Tastați „Single” în celula D4.
6. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Validarea datelor nu există în listă în Excel

Putem folosi o formulă personalizată pentru a ne asigura că textul specific nu este introdus într-o celulă.

1. Evidențiați gama necesară, de exemplu: C3: C8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Client din caseta derulantă Permite, apoi tastați următoarea formulă:

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

Formula folosește COUNTIF funcția și utilizările ABSOLUTE în intervalul F3: F8 pentru a vă asigura că aceasta este lista pe care COUNTIF funcția se uită când verifică dacă se introduce textul corect.

4. Clic Bine.
5. Tastați „Carne de vită” în celula C4.
6. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Validarea datelor Numere Numere sunt introduse în Excel

Putem folosi o formulă personalizată pentru a ne asigura că numai un număr este introdus într-o celulă.

1. Evidențiați gama necesară, de exemplu: E3: E8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Client din caseta derulantă Permite, apoi tastați următoarea formulă:

= ISNUMBER (F3: F8)

Formula folosește funcția ISNUMBER pentru a se asigura că un număr este introdus în celulele din interval.

4. Clic Bine.
5. Tastați „nouă” în celula F4.
6. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Validarea datelor nu depășește valoarea în Excel

Putem folosi o formulă personalizată pentru a ne asigura că valorile introduse într-o celulă nu depășesc o valoare specificată.

1. Evidențiați gama necesară, de exemplu: E3: E8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Client din caseta derulantă Permite, apoi tastați următoarea formulă:

= E3 <= $ G $ 6

Formula an ABSOLUT în intervalul G6 pentru a se asigura că aceasta este valoarea verificată de regulă atunci când datele sunt introduse în E3.

4. Clic Bine.
5. Tastați „9” în celula E4.
6. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Validarea datelor nu depășește totalul în Excel

Putem utiliza o formulă personalizată pentru a ne asigura că valorile introduse într-un interval de celule nu depășesc o valoare totală specificată pentru interval

1. Evidențiați gama necesară, de exemplu: F3: F8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Client din caseta derulantă Permite, apoi tastați următoarea formulă:

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

Formula folosește SUMĂ funcția și utilizările ABSOLUTE în intervalul F3: F8 pentru a vă asigura că aceasta este lista pe care SUMĂ funcția privește când verifică dacă totalul intervalului nu este mai mare decât valoarea introdusă în H6.

4. Clic Bine.
5. Tastați „40” în celula F4.
6. Dacă ați utilizat opțiunea Alertă de eroare, va apărea avertismentul personalizat și mesajul de eroare. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare pentru a ieși din mesaj sau Reîncercați pentru a reintroduce textul corect în celulă.

Date de validare a datelor săptămânale numai în Excel.

Putem folosi o formulă personalizată pentru a ne asigura că sunt introduse numai zilele săptămânii când datele sunt utilizate în Excel.

1. Evidențiați gama necesară, de exemplu: G3: G8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Client din caseta derulantă Permiteți, apoi tastați următoarea formulă:

= WEEKDAY (F3,2) <6

Funcția zilei săptămânii verifică pentru a vă asigura că ziua cuprinsă în dată nu este sâmbătă sau duminică.

4. Modificați data în G5 pentru a afișa o sâmbătă (de ex. 9^a Mai 2022).
5. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

7. Faceți clic pe Anulare sau Reîncercați pentru a introduce o dată alternativă.

Validarea datelor Data viitoare numai în Excel

Putem crea o formulă personalizată pentru a permite utilizatorului să introducă doar o dată care este în viitor.

1. Evidențiați gama necesară, de exemplu: G3: G8.
2. În Panglică, selectați Date> Instrumente de date> Validare date.
3. Selectați Client din caseta derulantă Permite, apoi tastați următoarea formulă:

= G3> AZI ()

Formula folosește AZI funcție pentru a verifica dacă data introdusă în celulă este mai mare decât data de astăzi.

4. Modificați data din G5 pentru ieri.
5. Dacă ați utilizat opțiunea Alertă de eroare, avertismentul personalizat și mesajul de eroare vor apărea. Dacă nu ați utilizat această opțiune, va apărea avertismentul standard.

6. Faceți clic pe Anulare sau Reîncercați pentru a introduce o dată alternativă.

Validarea datelor trebuie să înceapă în Foi de calcul Google

1. Evidențiați gama necesară, de exemplu: B3: B8.
2. În meniu, selectați Date> Validare date.

3. Gama de celule va fi deja completată.

4. Selectați Formula personalizată este din lista derulantă Criterii.

5. Introduceți formula.

= EXACT (STÂNGA (B3,4), „FRU-“)

6. Selectați oricare Afișați avertisment sau Respingeți intrarea dacă datele sunt nevalide.

7. Dacă doriți, puteți introduce un anumit text de ajutor pentru validare.

8. Faceți clic pe Salvare.

9. Faceți clic în B3 pentru a vedea textul de ajutor pentru validare

10. Tastați FRI-123
11. Dacă ați selectat Afișați avertisment, va apărea următorul mesaj.

12. Alternativ, dacă ați selectat Respingeți introducerea datelor nevalide, vi se va împiedica introducerea datelor și următorul mesaj apare pe ecran.

Restul exemplelor de formule personalizate din Foi de calcul Google funcționează exact în același mod.