Validarea datelor în Microsoft Excel

În acest tutorial vă voi arăta cum sunt datele validate în Microsoft Excel.

Criteriu de validare – condiție logică

Funcția de validare a datelor vă permite să configurați reguli care determină ce date puteți introduce într-o celulă.
De exemplu, doriți să limitați introducerea datelor într-o anumită celulă (din coloana Valoare vânzări) la numere cuprinse între 10000 și 99999. Dacă utilizatorul face o intrare nevalidă, puteți afișa un mesaj de alertă personalizat. Sau puteți afișa un mesaj de întâmpinare.

Pentru a realiza acest lucru, urmați pașii:

• Selectați coloana Valoare vânzări
• Din fila Data – grupul Data Tools – alegeți Data Validation
• În caseta de dialog Data Validation, tab-ul Settings, în câmpul Allow: alegeți opțiunea Whole Number, în câmpul Data: alegeți between, în câmpul Minimum: scrieți valoarea 10000 iar în câmpul Maximum scrieți valoarea 99999.

• În tab-ul Input Message, în câmpul Title: scrieți titlul mesajului de întâmpinare Atenție!, iar în Input message: mesajul Valorile trebuie să facă parte din intervalul închis [10000,99999].

• În tab-ul Error Alert, câmpul Title: scrieți titlul mesajului: ATENȚIE!, iar în câmpul Error message: scrieți Valoarea este în afara intervalului permis! și apăsați butonul OK.

Dacă selectați o celulă care are validare de date, apare mesajul de întâmpinare introdus anterior.

Dacă scrieți o valoare în afara intervalului specificat apare mesajul de eroare.

Valorile care erau deja introduse în respectiva coloană nu au fost luate în considerare (chiar dacă sunt în afara intervalului specificat).

• Pentru a evidenția aceste valori, în fila Data – grupul Data Tools – Data Validation – alegeți Circle Invalid Data. Valorile din afara intervalului vor fi încercuite cu roșu.

Pentru găsirea celulelor cu validare de date dintr-o foaie de lucru, în fila Home – grupul Editing– faceți click pe Find &Select și apoi pe Data Validation.

După ce ați găsit celulele care conțin validare de date, aveți posibilitatea să modificați, să copiați sau să eliminați setările de validare (din fila Data – grupul Data Tools – Data Validation – Clear All din caseta Data Validation).

Criteriu de validare – liste

Data Validation este un instrument de restricționare a valorilor pe care le putem introduce într-o celulă. Presupunem că într-o celulă dorim să introducem numele unui județ.

Pentru a ne asigura că nu scriem greșit acea denumire vom crea o listă de tip drop-down și vom completa denumirea județului respectiv selectându-l din listă.

Sunt două variante de a introduce o listă în meniul derulant.

Prima variantă constă în scrierea “de mână” a listei respective, iar a doua variantă constă în importarea ei dintr-o zonă a foii de calcul în care am scris-o în prealabil.

Cazul 1

Selectăm zona căreia dorim să-i impunem regula de validare, deschidem fereastra Data Validation (din fila Data – grupul Data Tools – Data Validation).

Din meniul Allow alegem opțiunea List.

Introducem lista județelor separate cu “,” (virgulă) sau “;” (punct și virgulă), în funcție de setările regionale ale sistemului de operare. După care apăsăm OK și folosim lista.

Cazul 2

Dacă avem o listă (cu 11 județe, spre exemplu) într-o foaie Excel, o putem utiliza ca sursă pentru Data Validation – List.
În fișierul din imagine, avem un tabel structurat astfel:

• Pe prima coloană avem lista celor 11 județe a căror denumire o regăsim și pe coloane.
• Pe următoarele coloane avem listele localităților din județele din antet.

Utilizăm facilitatea Create from selection pentru a numi fiecare coloană din tabel cu numele din antetul acesteia (de ex. prima coloană se va numi JUDETE).

Pentru aceasta selectăm tabelul, apoi din fila Formulas – grupul Defined Names – dăm click pe Create from selection. În fereastra Create Names from Selection debifăm opțiunea Left Column păstrând doar opțiunea Top row.

În zona căreia dorim să-i aplicăm o regulă de validare vom utiliza lista “JUDETE” pentru a alege din lista județelor.

Liste de validare dependente

Ne dorim acum ca după selectarea județului să putem alege orașul doar din lista orașelor județului respectiv. Pentru aceasta, vom folosi funcția Indirect care returnează adresa zonei scrisă ca text în celula C3 (în cazul nostru, adresa zonei cu denumirea județului respectiv).

Sintaxa funcției INDIRECT este:

INDIRECT(text_ref, [a1])

Semnificație argumente:

• text_ref – Este o referință la o celulă care conține o referință în stil A1, în stil R1C1, un nume definit ca referință sau o referință la o celulă ca șir text
• [a1] – Opțional. Este o valoare logică ce specifică ce tip de referință este conținut în celula text_ref.
  • Dacă a1 este TRUE sau omis, text_ref este interpretat ca stil de referință A1.
  • Dacă a1 este FALSE, text_ref este interpretat ca stil de referință R1C1.

La alegerea unui județ, lista orașelor se va limita doar la orașele din județul respectiv.

Data Validation cu selecții multiple

Instrumentul Data Validation, cu opțiunea List, permite introducerea în celula care are impusă o regulă de validare a unui singur element din lista respectivă.

Dacă dorim să alegem mai multe valori în celulele care au impusă regula de validare avem nevoie de un cod VBA care să ruleze în mod automat la modificarea conținutului celulelor cu regula de validare.

Pentru a realiza acest lucru, parcurgem următoarele etape:

• Selectăm celula (sau domeniul de celule) unde dorim să apară lista derulantă și îi aplicăm o regulă de validare de tip List. Sursa este reprezentată de domeniul de celule care conține valorile pe care le dorim în lista derulantă.

• Deschidem editorul Visual Basic.
  Pentru aceasta, vom afișa fila Developer (din Excel Options – Customize Ribbon – bifăm caseta Developer)

În fila Developer – grupul Code – apăsăm butonul Visual Basic (sau folosim combinația de taste ALT+F11), ca să deschidem editorul VBA.

În stânga este lista fișierelor deschise și numele foilor de calcul ale acestora. Dați dublu click pe numele foii care conține celulele cu lista de validare (în cazul nostru Sheet1) și copiați codul de mai jos. Acest cod permite repetițiile la selecția din listă.

Observație: Celula selectată unde dorim să apară selecția multiplă este D3. Din această cauză Target.Address = „$D$3”.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
On Error GoTo Exitsub
If Target.Address = "$D$3" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
  GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = "" Then
     Target.Value = Newvalue
    Else
     Target.Value = Oldvalue & ", " & Newvalue
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub