Valoare introdusă nevalidă. Cum să preveniți introducerea de valori incorecte în Excel folosind validarea datelor. Configurarea unei condiții de verificare

Este ușor să faceți greșeli când introduceți o mulțime de informații în celulele tabelului. În EXCEL, există un instrument pentru verificarea datelor introduse imediat după apăsarea tastei ENTER - Data Validation.

Instrument Verificarea datelor() nu ar fi atât de popular dacă funcționalitatea sa ar fi limitată doar la verificarea în sine. Tehnicile de bază pentru lucrul cu acest instrument sunt enumerate pe scurt mai jos.

A. Verificarea valorilor introduse

După cum puteți vedea în figura de mai jos, condițiile de verificare a valorii introduse pot fi configurate foarte flexibil.

Puteți permite introducerea valorilor într-o celulă de un anumit tip doar selectând condiția necesară din lista derulantă:

• Întreg. Numai numerele întregi aparținând unui anumit interval pot fi introduse într-o celulă;
• Valabil. În celulă pot fi introduse doar numere, inclusiv cele cu o parte zecimală (nu puteți introduce text, dar puteți introduce o dată);
• Data de. Se presupune că în celulă vor fi introduse date cuprinse între 01/01/1900 și 31/12/9999. Citiți mai multe despre formatul Data în articol
• Timp. Se presupune că în celula cu Acest control Acest tip va introduce ora. De exemplu, figura de mai jos arată o condiție când este permisă introducerea orei într-o celulă care aparține doar celei de-a doua jumătăți a zilei, adică. de la 12:00:00 la 23:59:59. În loc să introduceți plictisitor valoarea 12:00:00, puteți utiliza echivalentul numeric al lui 0,5. Capacitatea de a introduce numere în loc de timp rezultă din faptul că orice dată în EXCEL este asociată cu un număr întreg pozitiv și, prin urmare, timpul (din moment ce face parte din zi) corespunde părții fracționale a numărului (de exemplu, 0,5). este amiaza). Echivalentul numeric pentru 23:59:59 ar fi 0,99999.

• Lungimea textului. Doar un anumit număr de caractere poate fi introdus într-o celulă. Cu această restricție, puteți introduce atât numere, cât și date, principalul lucru este că numărul de caractere introdus nu contrazice limita de lungime a textului. De exemplu, dacă numărul de caractere este limitat la mai puțin de 5, nu puteți introduce o dată ulterioară 13/10/2173, deoarece corespunde numărului 99999, iar 14/10/2173 este deja 100000, i.e. 6 caractere. Este interesant că, dacă există o limită de, de exemplu, mai mică de 5 caractere, nu veți putea introduce formula =SQRT(2) în celulă, deoarece rezultat =1.4142135623731 (în funcție de precizia specificată în EXCEL), dar =ROOT(4) - puteți, deoarece rezultatul =2, iar acesta este doar 1 caracter.
• Listă. Probabil cel mai interesant tip de date. În acest caz, introducerea de valori într-o celulă poate fi limitată la o listă definită anterior. De exemplu, dacă specificați unitatea de măsură a unui produs ca sursă, separată prin punct și virgulă buc;kg;mp;cub.m, atunci nu veți putea selecta nimic altceva decât aceste 4 valori din listă. În sursă, puteți specifica o serie de celule care conțin o listă pregenerată sau un link către . Un exemplu este dat în articol
• O alta. Celula are voie să introducă valori care îndeplinesc criterii mai complexe. Pentru a seta criteriile, trebuie să utilizați o formulă. Să luăm în considerare această condiție mai detaliat.

Cu tipul selectat O alta,în câmp Formulă trebuie să introduceți o formulă pentru a calcula valoarea logică. Dacă se evaluează la TRUE, atunci o astfel de valoare poate fi introdusă în celulă, dacă FALSE, atunci valoarea dvs. nu poate fi introdusă. Ca argument de formulă, trebuie să utilizați o referință la celula în sine la care este aplicată. Verificarea datelor sau o referire la o celulă care depinde de ea. De exemplu,

Când selectați mai multe celule, acolo unde este necesar, nu uitați să furnizați o referință absolută la celule (de exemplu, 1 USD: 20 USD ).

Când utilizați instrumentul Verificarea datelor, se presupune că constantele vor fi introduse în celulă ( 123, produs1, 05/01/2010 etc.), deși nimeni nu interzice introducerea formulelor. În acest caz, rezultatul calculului formulei va fi în continuare verificat. În general, nu recomand introducerea de formule în celule cu verificarea datelor - este ușor să fii confuz. În acest caz, vă sfătuiesc să utilizați .

ÎN.Afișează un comentariu dacă celula este cea curentă.

Folosiți fila Mesaj de ieșire pentru a afișa comentariul.

Spre deosebire de nota obișnuită ( Recenzie/ Comentează/ Creează un comentariu), care dispare după ce cursorul mouse-ului părăsește celula (dacă opțiunea nu este activă Afișați toate notele), acest comentariu este afișat ori de câte ori este selectată o celulă.

CU.Afișează un mesaj de eroare detaliat.

După introducerea unei valori incorecte Verificarea datelor poate afișa un mesaj detaliat despre ceea ce a fost greșit. Acesta este un fel de analog Msgbox() din VBA.

D.Crearea intervalelor conectate (liste)

E. Utilizarea referințelor la alte foi în reguli

În EXCEL 2007 Verificarea datelor, ca și în, nu puteți specifica direct o legătură către intervalele unei alte foi, de exemplu, astfel =Sheet2!$A$1 . Vă permite să ocoliți această limitare folosind .

Dacă în Verificarea datelor trebuie să faceți, de exemplu, o referire la o celulă A1 altă foaie, apoi trebuie mai întâi să definiți celula respectivă și apoi să faceți referire la aceasta Numeîn regulă Verificarea datelor.

În Excel 2010, pe de altă parte, puteți utiliza reguli de validare a datelor care fac referire la valori din alte foi. În Excel 2007 și Excel 97-2003, acest tip de validare a datelor nu este acceptat și nu apare pe foaia de lucru. Cu toate acestea, toate regulile de validare a datelor rămân disponibile în registrul de lucru și sunt aplicate atunci când redeschideți registrul de lucru în Excel 2010, cu excepția cazului în care au fost modificate în Excel 2007 sau Excel 97-2003.

F. Cum funcționează verificarea antecedentelor

Verificarea datelor declanșat în mod explicit atunci când valorile sunt introduse într-o celulă de la tastatură și apoi este apăsată o tastă INTRODUCE. În acest caz, apare o fereastră care descrie eroarea.

Dacă valorile sunt introduse prin Clipboard (Lipiți valori) sau folosind o comandă rapidă de la tastatură CTRL+ D(copiind valoarea din celulă de sus) sau sunt copiate de sus în jos, atunci verificarea nu este efectuată în mod explicit. În plus, atunci când copiați valori, puteți șterge din greșeală regulile cu totul Verificări de date, de exemplu, dacă celula sursă nu este definită Verificarea datelor, iar datele din acesta sunt inserate prin Clipboardși folosind o combinație de taste CTRL+V.

Să explicăm cu un exemplu. Să presupunem că la celulă A1 aplicat Verificarea datelor supuse verificării O alta, unde =ROW(A1)=1 este introdus în câmpul formulă, adică. pentru toate celulele din primul rând condiția Verificări de date va lua valoarea TRUE, pentru alte rânduri - FALSE, indiferent de conținutul celulei.

Acum să selectăm celula A2 și apăsați CTRL+D. Valoare de la A1 va fi copiat în A2 împreună cu starea Verificări de date. În ciuda faptului că acum condiția Verificări de date va evalua FALS și nu va fi afișat niciun mesaj de avertizare. Pentru a vă asigura că datele din celule îndeplinesc condițiile definite în Verificarea datelor, trebuie să apelați comanda de meniu Încercuiește informațiile incorecte (). Celulele cu date incorecte vor fi conturate în ovale roșii. Acum să selectăm din nou celula A2 și apăsați tasta F2(intrați în modul Editare), apoi faceți clic INTRODUCE- va apărea o fereastră cu un mesaj că valoarea introdusă este incorectă.

Există o altă modalitate de a ocoli verificările de fundal. Să presupunem că intrarea într-o celulă este limitată la valori de la 1 la 3. Acum, în orice altă celulă fără Verificări de date introduceți valoarea 4. Selectați această celulă, în Bara de formule selectați valoarea 4 și copiați-o în Clipboard. Acum să selectăm celula cu Verificarea datelorși apăsați CTRL+V. Valoarea a fost introdusă în celulă! In afara de asta, Verificarea datelor a rămas neatins, spre deosebire de cazul când, prin Clipboard, de exemplu, este inserată o valoare din WORD. Pentru a vă asigura că datele dintr-o celulă nu îndeplinesc condițiile definite în Verificarea datelor, trebuie să apelați comanda de meniu Încercuiește informațiile incorecte (Date/ Lucrul cu date/ Validarea datelor/ Încercuiește datele incorecte).

G. Căutați celule cu validarea datelor

Dacă sunt multe celule pe foaia cu Verificarea datelor, apoi puteți folosi instrumentul ( Acasă/ Găsiți și selectați/ Selectarea unui grup de celule).

Opțiune Verificarea datelor Acest instrument vă permite să selectați celulele care sunt supuse validării datelor (specificate folosind comanda Date / Lucrul cu date / Validarea datelor). La selectarea unui comutator Toata lumea toate astfel de celule vor fi selectate. La selectarea opțiunii Aceste la fel Sunt evidențiate doar acele celule pentru care sunt setate aceleași reguli de validare a datelor ca și pentru celula activă.

Să presupunem că trebuie să introduceți valori de o dimensiune strict limitată în anumite celule. De exemplu, TIN, care trebuie să fie format din 10 caractere.

Dar adesea, când lucrezi în grabă, erorile sub formă de caractere mai mult sau mai puține se strecoară în astfel de date. Prin urmare, pentru anumite celule, puteți limita numărul de caractere introduse la un anumit număr.

Pentru aceasta:

1. Selectați celulele (puteți avea întregul rând(e), coloana(e))

2. În Meniu, selectați „Date - Verificare”.

3. În fereastra care apare
în coloana „Tip de date”, selectați „lungimea textului”,
în coloana „Valoare” - „egal”,
în coloana „Lungime” indicăm „10” (pentru TIN).

4. În fila „Mesaj de eroare”, puteți seta un titlu individual și un mesaj despre această eroare. De exemplu:

5. După ce ați introdus textul mesajului, faceți clic pe OK.

Acum, dacă încercați să introduceți valori care nu sunt egale ca lungime cu 10 caractere în celulele pe care le-ați specificat, va apărea un mesaj de eroare.

Mai mult, nu va fi posibilă introducerea datelor de altă dimensiune.

Dacă limita de lungime nu este importantă, atunci în fila „mesaj de eroare”, selectați „avertisment” în loc de „oprire” ca implicit.

Acum va apărea mesajul de eroare, dar vi se va oferi posibilitatea de a introduce valori de altă dimensiune.

Dacă creați o foaie de lucru care solicită utilizatorilor să introducă date, puteți limita introducerea la un anumit interval de date sau numere sau vă puteți asigura că sunt introduse numai numere întregi pozitive. Excel poate restricționa introducerea datelor în anumite celule utilizând validarea datelor, vă poate solicita să introduceți date valide atunci când o celulă este evidențiată și să afișeze un mesaj de eroare atunci când utilizatorul introduce date nevalide.

Restricţionarea introducerii datelor

Restricţionarea introducerii datelor

Solicitați intrări valide

Când utilizatorii fac clic pe o celulă care are cerințe de introducere a datelor, puteți afișa un mesaj care explică ce date sunt valide.

Afișarea unui mesaj de eroare la introducerea datelor nevalide

Dacă aveți restricții de date și utilizatorul introduce date nevalide într-o celulă, puteți afișa un mesaj care descrie eroarea.

Adăugați validarea datelor la o celulă sau la un interval de celule

Notă: Primii doi pași din această secțiune sunt pentru adăugarea oricărui tip de validare a datelor. Pașii 3-7 sunt special pentru crearea unei liste derulante.

Selectați una sau mai multe celule cărora doriți să aplicați verificarea.

Pe fila Date in grup Lucrul cu date faceți clic pe butonul Verificarea datelor.

Pe fila OpțiuniÎn capitolul Permite faceți clic pe butonul Listă.

În câmp Sursă introduceți valori separate prin punct și virgulă. De exemplu, introduceți „ scazut mediu ridicat".

Asigurați-vă că caseta de selectare este bifată Lista de valori valide. În caz contrar, săgeata derulantă nu va apărea lângă celulă.

Pentru a specifica modul de gestionare a valorilor goale (nule), bifați sau debifați caseta de selectare Ignorați celulele goale.

Testați dacă validarea datelor funcționează corect. Încercați să introduceți date valide și apoi nevalide într-o celulă pentru a vă asigura că setările de validare sunt aplicate așa cum doriți și că mesajele dvs. apar la momentul potrivit.

Note:

Utilizarea altor tipuri de validare a datelor

Tabelul listează alte tipuri de validare a datelor și arată cum să le aplicați datelor din foaia de lucru.

Exemple de formule pentru validarea datelor

Note:

Exemplele de mai jos folosesc opțiunea personalizată la crearea formulelor condiționate. În acest caz, conținutul câmpului „Date” nu contează.

Capturile de ecran din acest articol sunt din Excel 2016; dar funcționalitatea este aceeași în Excel pentru web.

La	Introduceți formula
Valoarea din celula care conține codul produsului (C2) începe întotdeauna cu prefixul standard „ID-” și are cel puțin 10 (mai mult de 9) caractere.	= ȘI (STÂNGA (C2; 3) = „ID-”; LUNGIME (C2) >9)
	=ETEXT(D2)
Valoarea din celula care conține data nașterii cuiva (B6) a fost mai mare decât numărul de ani enumerat în celula B4.	=IF(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)
Toate datele din intervalul de celule A2:A10 au conținut valori unice.

Pentru a face foaia de lucru mai ușor de utilizat de către utilizatori, adăugați liste derulante în celule. Listele drop-down permit utilizatorilor să selecteze articole dintr-o listă pe care o creați.

Video

Pe o nouă foaie de lucru, introduceți datele care doriți să apară în lista derulantă. Este de dorit ca elementele din listă să fie conținute într-un tabel Excel. Dacă nu este cazul, puteți converti rapid lista într-un tabel selectând orice celulă din interval și apăsând CTRL+T.



Note:

1. Selectați celula din foaia de lucru în care doriți să plasați lista verticală.

   Pe panglică, deschideți fila Dateși apăsați butonul Verificarea datelor.

   Notă: Dacă butonul Verificarea datelor indisponibilă, foaia poate fi protejată sau partajată. Deblocați anumite zone ale registrului de lucru protejat sau anulați accesul la foaia de lucru, apoi repetați pasul 3.

   Pe fila Opțiuniîn câmp Tip de date selectați elementul Listă.

   Faceți clic pe câmp Sursăși evidențiați intervalul listei. În exemplu, datele se află pe foaia Orașe în intervalul A2:A9. Rețineți că rândul antetului nu se află în interval, deoarece nu este una dintre opțiunile disponibile pentru selecție.

   

   Dacă puteți lăsa celula goală, bifați caseta de selectare Ignorați celulele goale.

   Bifeaza casuta Lista de valori valide

   Deschide fila Sugestie de intrare.

   
   

2. Deschide fila Mesaj de eroare.

   
   

3. Nu știu ce opțiune să selectez în câmp Vedere?

Lucrul cu o listă derulantă

dacă lățimile coloanelor și înălțimile rândurilor se modifică la afișarea tuturor intrărilor.

Descărcarea exemplelor

Vă sugerăm să descărcați un exemplu de carte cu mai multe exemple de verificare a datelor, similar cu exemplul din acest articol. Puteți să le utilizați sau să vă creați propriile scripturi de verificare a datelor. Descărcați exemple de validare a datelor Excel.

Puteți introduce datele mai rapid și mai precis, limitând valorile dintr-o celulă la opțiuni dintr-o listă derulantă.

Mai întâi, creați o listă de elemente valide pe foaia de lucru, apoi sortați sau aranjați-le în ordinea dorită. Aceste elemente pot servi ulterior ca sursă pentru o listă derulantă de date. Dacă lista este mică, o puteți face referire cu ușurință și puteți introduce articole direct în verificatorul de date.

Vezi si

După ce ați creat meniul derulant, asigurați-vă că funcționează corect. De exemplu, este recomandat să verificați

Pentru a reduce inexactitatea introducerii datelor, folosesc adesea liste bazate pe o serie de celule. Să luăm în considerare utilizarea acestui instrument folosind exemplul de păstrare a foii de pontaj.

Pentru început, pe o foaie separată (acest lucru nu este necesar), vom plasa o listă de valori acceptabile într-o coloană sau un rând (Fig. 1a); vezi și fișierul Excel, foaia „Lista”.

Orez. 1. Lista numelor de familie: (a) în ordine aleatorie; (b) în ordine alfabetică.

Descărcați în format, exemple în format

Această listă de nume de familie va apărea în lista derulantă în ordinea în care apar în această coloană. Este clar că pentru ușurința căutării este mai bine să le sortați alfabetic (Fig. 1b).

Să dăm listei noastre numele gamei. Pentru a face acest lucru, selectați un interval; în cazul nostru, aceasta este zona A2:A21 și introduceți numele intervalului, așa cum se arată în Fig. 2; în cazul nostru, acestea sunt „nume de familie”:

Orez. 2. Denumirea intervalului

Să selectăm zona în care vom introduce numele de familie (vezi fișier Excel, foaia „Intrare”). În exemplul nostru – A2:A32 (Fig. 3). Să mergem la fila Date, grupul de lucru pentru date, selectați comanda Data Validation:

Orez. 3. Verificarea datelor

În caseta de dialog „Verificarea valorilor introduse”, accesați fila Parametri (Fig. 4). În câmpul „Tip de date”, selectați „Lista”. În câmpul „Sursă” indicăm: (a) zona de celule în care este stocată lista; această opțiune este potrivită dacă lista se află pe aceeași foaie Excel; (b) numele intervalului; Această opțiune poate fi folosită atât când lista se află pe aceeași foaie Excel, cât și când lista se află pe o altă foaie Excel (ca și în cazul nostru). În ambele cazuri, asigurați-vă că există un semn egal (=) înaintea linkului sau a numelui.

Orez. 4. Selectarea unei surse de date pentru listă: (a) pe aceeași foaie; (b) pe orice foaie

Și încă două opțiuni în fila „Opțiuni”:

• Ignorați celulele goale. Dacă caseta de selectare este bifată, Excel vă va permite să lăsați celula goală. Dacă caseta de selectare este debifată, puteți ieși din celulă numai după ce selectați unul dintre numele din listă. Particularitatea opțiunii este că Excel vă va permite să vă deplasați între celule (de exemplu, folosind tasta Enter sau săgețile sus/jos), dar nu puteți începe să tastați, apoi ștergeți toate caracterele și treceți la o altă celulă.
• Lista celulelor valide. Dacă nu există nicio bifă, atunci când plasați cursorul într-o celulă de intrare, pictograma listă nu va apărea lângă celulă și, prin urmare, nu veți putea selecta din listă. Deși toate celelalte proprietăți ale lucrului cu o listă vor fi în vigoare, Excel nu vă va permite să introduceți o valoare arbitrară într-o celulă.

Să mergem la fila „Mesaje de intrare” din fereastra „Verificarea valorilor de intrare”. Bifați caseta „Afișează sfatul explicativ dacă celula este curentă”. Introduceți titlul și textul mesajului în câmpurile corespunzătoare (Fig. 5). Ulterior, atunci când utilizatorul se află pe una dintre celulele zonei de intrare (în exemplul din Fig. 5 - în celula A6), mesajul creat de noi va fi afișat.

Orez. 5. Setarea mesajului de intrare

În fereastra „Verificarea valorilor introduse”, accesați fila „Mesaj de eroare” (Figura 6). Bifați caseta „Afișați mesajul de eroare”. În câmpul „Tip”, selectați tipul de mesaj. Introduceți titlul și textul mesajului. Mesajul pe care l-am creat va apărea ori de câte ori utilizatorul încearcă să introducă o valoare nevalidă.

Orez. 6. Setarea mesajelor de eroare

Tipuri acceptabile de mesaje de eroare (Fig. 7):

• Stop– previne introducerea datelor nevalide; buton Repeta vă permite să reveniți la intrare, buton Anulare Repeta.
• Avertizare– avertizează cu privire la introducerea de date nevalide, dar nu interzice o astfel de introducere; buton da vă permite să acceptați o introducere nevalidă; buton Nu vă permite să continuați să tastați (valoarea introdusă anterior în celulă devine disponibilă pentru editare); buton Anulareșterge o celulă și vă permite să începeți să tastați sau să treceți la tastarea în alte celule; butonul este selectat implicit Nu.
• Mesaj– informează despre introducerea datelor nevalide; deşi permite introducerea lor. Acest tip de mesaj este cel mai flexibil. Când apare un mesaj informativ, utilizatorul poate apăsa butonul Bine pentru a accepta introducerea datelor nevalide sau apăsați butonul Anulare a anula intrarea; butonul este selectat implicit BINE.

Orez. 7. Selectarea tipului de mesaj de eroare

Cateva comentarii. 1. Dacă ați introdus text prea lung în fereastra Mesaj a filei Mesaj de eroare, fereastra cu mesaje de eroare va fi prea largă (ca în Fig. 7); utilizați ruptura de linie Shift + Enter în punctul din mesaj în care doriți să împărțiți liniile (Figura 8).

Orez. 8. Caseta de mesaj de eroare latime redusa

2. Lățimea listei derulante este determinată de lățimea celulei pentru care se aplică validarea datelor. Păstrați celulele de intrare suficient de largi pentru a evita trunchierea intrărilor valide care sunt mai largi decât lista verticală. În caz contrar, puteți face o alegere greșită pe baza primelor litere ale cuvântului.

3. Numărul maxim de intrări din lista derulantă este limitat, deși nu prea mult :), și anume de numărul 32.767.

4. Dacă nu doriți ca utilizatorii să editeze lista de recenzii, plasați-o pe o foaie separată, apoi ascundeți și protejați această foaie.