Un set de valori într-o celulă excel. Protejarea celulelor Excel de datele introduse incorect. Restricţionarea introducerii datelor

Selectați o celulă sau întreaga zonă pe care doriți să o verifice Excel în timp ce introduceți datele. Acum accesați panglica de meniu „Date | Lucrul cu date | Verificarea datelor”. În fereastra următoare, setați condițiile de scanare. În câmpul Tip de date, alegeți dintre opțiuni precum Întregul, Real, Listă, Data, Ora, Lungimea textului sau Altele.

Când definiți o condiție, orice tip de date este acceptabil. Dacă selectați opțiunea „Altele”, puteți seta valorile și mai precis. Deci, de exemplu, setați data doar de la 01.01.2017 la 31.12.2017. Dacă trebuie să setați date exacte, selectați opțiunea „List” și introduceți datele, separate prin punct și punct și virgulă.

Pregătiți-vă datele în avans introducându-le într-o foaie de calcul Excel. Faceți clic pe butonul din câmpul „Sursă” și accesați foaia corespunzătoare din registrul de lucru Excel. Selectați o zonă și confirmați selecția apăsând tasta .

Acum a apărut un meniu derulant lângă câmpul specificat la verificarea datelor, din care puteți selecta valoarea necesară. Dacă doriți să eliminați datele pentru validare, selectați celulele pe care nu doriți să le mai valideze Excel. Acum accesați panglica de meniu „Date | Lucrul cu date | Verificare date” și faceți clic pe „Ștergeți tot”.


De asemenea, puteți furniza informații despre formatul de date așteptat sau date incorecte tuturor colegilor care au acces la acest fișier. Pentru a face acest lucru, setați orice verificare a datelor la textul corespunzător care comunică aceste informații. În fereastra Validare intrare, lângă fila Opțiuni, găsiți filele Mesaj de intrare și Mesaj de eroare. Bifați caseta de selectare „Afișează sfatul explicativ dacă celula este actuală”.

Acum puteți seta titlul și introduceți textul mesajului. De îndată ce cursorul este mutat într-o anumită celulă, această informație va apărea ca un tooltip. De asemenea, puteți pregăti un mesaj de eroare în același formular. Dacă utilizatorul introduce date incorecte, va apărea mesajul pe care l-ați creat, Excel nu va permite trecerea valorilor incorecte, iar utilizatorul va trebui să introducă informațiile corecte. Abia atunci mesajul de eroare va dispărea.

Fișierele Microsoft Excel sunt destul de des folosite pentru a stoca date care sunt partajate de mai mulți utilizatori diferiți. Uneori utilizatorii greșesc și introduc date incorecte, ceea ce provoacă probleme de diferite grade de complexitate, în funcție de importanța datelor. Cu toate acestea, o astfel de situație poate fi ușor evitată.

Mai ales pentru acest caz, Excel are capacitatea de a verifica corectitudinea datelor înregistrate în anumite celule. Dacă introduceți date nevalide, Excel va emite un avertisment sau vă va împiedica să continuați operațiunea.

Pentru a activa verificarea, trebuie să selectați celulele protejate, apoi să mergeți la fila „Date” și să selectați elementul „Verificarea datelor”.

În fereastra care se deschide, în fila „Parametri”, selectați mai întâi tipul de date care pot fi scrise în celulele selectate, apoi setați restricții pentru tipul de date selectat. Există destul de multe opțiuni, de exemplu, ca restricție puteți specifica o anumită gamă de numere, o perioadă de timp/date, text de o anumită lungime sau să specificați un fișier cu o listă de valori acceptabile. De asemenea, este posibil să setați o formulă pentru a calcula valori valide pe baza conținutului altor celule.

De exemplu, să indicăm orice număr întreg nu mai mare de 100000.

În fila „Mesaj de eroare”, selectați acțiunea care ar trebui să aibă loc dacă introducerea este incorectă. Puteți alege una dintre cele trei opțiuni:

Stop - oprește operațiunea, împiedicând introducerea datelor incorecte;
Avertizare - la introducerea datelor incorecte se emite un avertisment, dar utilizatorului i se oferă posibilitatea de a continua operațiunea;
Mesaj—Utilizatorul este notificat că datele introduse sunt nevalide, dar introducerea datelor este permisă.

Aici puteți adăuga și titlul și textul mesajului pe care utilizatorul le va vedea atunci când introduce o valoare incorectă. Acest lucru ar trebui să-l ajute pe utilizator să înțeleagă ce face greșit.

Și așa arată mesajul când încercați să introduceți date care nu îndeplinesc condiția specificată.

Pentru ajutor suplimentar, există o opțiune de a lăsa un indiciu în fila Mesaj de intrare.

Acest sfat explicativ va apărea lângă o celulă când o selectați și va rămâne pe ecran până când treceți la o altă celulă.

Și dacă se întâmplă așa că utilizatorii au reușit totuși să „încurce”, este posibil să evidențiezi datele introduse incorect. Puteți face acest lucru selectând „Încercuiți datele incorecte” în meniul „Verificarea datelor”.

Pași simpli ca aceștia vor face viața mai ușoară utilizatorilor și vor ajuta la evitarea multor probleme atunci când lucrați în colaborare cu datele din Excel.

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 12/31/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, ora (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. Interesant, dacă limitați, de exemplu, mai puțin de 5 caractere, nu veți putea introduce formula =ROOT(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. Celulei 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,

La

Introduceți formula

Explicaţie

Celulă B2 conţinea doar text

ITEXT(B2)

ÎN Tip de date nicio opțiune de selectare a tipului Text, așa că trebuie să realizăm acest lucru indirect. Puteți Verificarea datelor se aplică direct pe celulă B2

Permiteți introducerea unei valori într-o celulă B1 numai dacă după introducerea valorii în celulă D1 va fi mai mult de 100, în D2 mai putin de 400

AND(D1>100;D2<400)

Verificarea datelor se aplică la celulă B1 . Mai mult, în celulă D1 a fost introdusă formula =B1*2, iar în D2 – formula =B1*3. Deși această formulă este echivalentă cu constrângerea Valabil cu un interval de la 50 la 133,33, dar cu conexiuni celulare mai complexe, această tehnică poate fi utilă

Valoarea din celula care conține vârsta angajatului ( C1 ), trebuie să fie întotdeauna mai mare decât numărul de ani întregi de muncă ( D1 ) plus 18 (vârsta minimă de angajare)

=DACĂ(C1>D1+18, ADEVĂRAT, FALS)

Când completați un tabel cu date despre vârstă și experiență de muncă, puteți seta această verificare pentru ambele celule ( C1 Și D1 ). Pentru a face acest lucru, trebuie să selectați 2 celule simultan, sunați Verificarea datelorși modificați ușor formula =DACĂ($C1>$D1+18, ADEVĂRAT, FALS)

Toate datele dintr-o serie de celule A1:A20 valori cuprinse

=COUNTIF($A$1:$A$20,A1)=1

=MATCH(A1,$A:$A,0)=RÂND(A1)

Trebuie selectate celule A1:A20 , sunați instrumentul Verificarea datelorși introduceți formula. A doua formulă poate fi utilizată pentru întreaga coloană A , pentru aceasta trebuie să selectați nu intervalul, ci întreaga coloană A

Valoarea din celula care conține numele codului produsului ( B5 ), a început întotdeauna cu prefixul standard „ID-” și avea cel puțin 10 caractere.

=AND(LEFT(B5,3)="ID-"; LENGTH(B5)>9)

Introducem verificarea datelor pentru celulă B5

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ă.

Utilizaț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, atunci 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ă.

    Selectați celulele cu date care ar trebui incluse în lista verticală (de exemplu, numele produselor).

    Selectați din meniu Inserare - Nume - Atribuire și introduceți un nume (orice nume este posibil, dar fără spații!) pentru intervalul selectat (de exemplu Bunuri). Clic Bine.

    Selectați celulele (puteți avea mai multe simultan) în care doriți să obțineți o listă derulantă și selectați din meniu Data validarii . Pe prima filă Opțiuni din lista derulantă Tip de date selecteaza o optiune Listă si intra in linie Sursă egal și numele intervalului (de ex. =Produse).

Clic Bine.

Restricționarea și interzicerea introducerii datelor într-o celulă

Cel mai interesant tip de cec – O alta . Aici ni se oferă posibilitatea de a avea un control mai mare asupra introducerii datelor. Există un singur câmp de completat – Formulă . Trebuie să scrieți o formulă în ea și de fiecare dată când introduceți valori într-o celulă, Excel va verifica expresia introdusă pentru TRUE (pentru a fi mai precis, va potrivi datele introduse cu formula). De exemplu, într-o celulă A1 introduceți numărul 1. Selectați celula ÎN 1și programați o verificare a datelor – O alta . În câmp Formulă hai sa intram: =B1=A1. Acum în celulă ÎN 1 Va fi posibil să introduceți doar o valoare care este complet identică cu valoarea din celulă A1.

Dacă valoarea din celulă în comparație cu formula de verificare este TRUE, atunci va fi introdusă, iar dacă FALSE, atunci va fi afișată o fereastră cu un mesaj de eroare:

Tab Mesaj de introdus.Aici puteți scrie textul care va fi afișat atunci când o celulă cu verificarea datelor este activată (un indiciu pentru acele celule în care trebuie să controlați introducerea datelor):

Tab Mesaj de eroare. Aici puteți specifica ce să faceți în cazul unei erori:

VedereStop – Dacă încercați să introduceți o valoare incorectă, programul nu vă va permite să o introduceți și va afișa un mesaj de eroare.

VedereMesaj – programul vă va permite să introduceți date incorecte, dar vă va anunța despre aceasta.

VedereAvertizare – Puteți introduce orice valoare într-o celulă, dar dacă introduceți o valoare care contrazice verificarea, apare un mesaj de avertizare care confirmă introducerea datelor.

Anexa 2

Vizualizarea și introducerea datelor printr-un formular

Utilizatorul poate adăuga oricând informații noi în listă, mergând pur și simplu la prima linie goală din partea de jos a listei și introducând date de la tastatură. Dar este și mai ușor să faci asta folosind un formular.

Selectați orice celulă din listă și selectați din meniu Date - Formular . Pe ecran va apărea un formular:

În bara de titlu a formularului, Excel afișează numele foii pe care se află lista. Chiar mai jos sunt anteturile tuturor coloanelor din listă. În colțul din dreapta sus al ferestrei, sunt afișate informații despre numărul total de linii din listă și numărul de linii afișate în prezent. În mod implicit, este afișat conținutul primului rând al listei. Deplasarea prin listă se face folosind bara de defilare din partea centrală a ferestrei sau folosind butoane ÎnapoiȘi Mai departe.

Pentru a adăuga o nouă intrare, utilizatorul trebuie să facă clic pe butonul Adăuga. Excel va afișa un formular gol, în rândurile căruia trebuie să introduceți valori noi și după ce ați terminat introducerea, faceți clic pe butonul Închide. Excel va extinde automat lista în jos, adăugând datele introduse după ultima linie.

Buton Criterii va afișa un formular gol cu ​​care puteți căuta înregistrări pe baza criteriilor introduse.

Buton Șterge va șterge intrarea curentă (rândul).

Sa luam in considerare cum să protejați celulele individuale în Excel . Există mai multe moduri. Puteți insera o listă derulantă într-o celulă. Utilizatorul va selecta cuvântul dorit din lista care apare. Pentru modalități de a crea o listă derulantă, consultați articolul „Lista derulantă în Excel”.
A doua modalitate de a proteja celulele Excel împotriva introducerii de date incorecte este funcția „Validarea datelor”.Acest lucru este necesar pentru completarea corectă a unui chestionar, a unei cereri, pentru a lucra cu un tabel în care angajatul este distras în mod constant etc.
Mai întâi, marcați celula sau intervalul de celule în care trebuie să setați restricții la introducerea datelor.Cum să selectați o serie de celule după numele său, consultați articolul „Interval în Excel».
Acum accesați fila „Date” din secțiunea „Lucrul cu datele”, faceți clic pe butonul „Verificarea datelor”.Va apărea fereastra „Verificarea valorilor introduse”. Pe „fila” Opțiuni " în linia "Tip de date" va fi un tip - "Orice valoare".
Trebuie să ne stabilim valoarea aici. Am instalat funcția „Integer”. Sunt indicate cele mai mici și cele mai mari numere care pot fi introduse.
De exemplu, chestionarul spune „vârsta”. Trebuie să indicați numărul complet de ani, dar să scrieți data nașterii.
Completați caseta de dialog astfel.
Faceți clic pe „OK”. Acum verificăm, introducem numere diferite, iar când am introdus numărul 1234 (acesta este mai mult de 100) și am apăsat „Enter”, a apărut această fereastră de avertizare.

Faceți clic pe butonul „Repetați” sau „Anulați” și scrieți numărul corect.
În fila Mesaj de intrare a casetei de dialog Validare intrare, puteți scrie o explicație despre ceea ce trebuie să scrieți aici. Și, când treceți cu mouse-ul peste această celulă, va apărea acest mesaj. De exemplu. În fereastra „Verificarea valorilor introduse”, în a treia filă „Mesaj de eroare”, puteți scrie textul mesajului care va fi afișat dacă numărul este introdus incorect.

Pentru a elimina această setare dintr-o celulă, accesați funcția „Verificarea datelor” și faceți clic pe butonul „Șterge toate”, apoi pe „OK”.
Puteți configura celulele tabelului sau formularului pentru a fi completate astfel încât să nu scrie doar data zilei lucrătoare. Pentru mai multe informații despre configurarea celulelor în acest mod, consultați articolul „Verificarea datelor în Excel”.
Puteți configura tabelul astfel încât să nu puteți introduce un singur nume. cuvânt, cod, adică de două ori. Citiți despre asta în „Preveniți introducerea valorilor duplicate în Excel”.
Pentru a controla momentul vânzării produselor, momentul plății facturilor, diferența de sumă etc., puteți instala funcția „Formatare condiționată”. De exemplu, dacă suma dintr-o celulă este mai mare de 6.000 de ruble, celula va fi colorată.
Vedeți cum să instalați această funcție în articolul "