Gestionarea datelor în Excel: tehnici avansate de validare, curățare și structurare

Un fișier Excel dezordonat costă ore de muncă în plus la fiecare raport. Validarea datelor la intrare, structurarea corectă a tabelelor și automatizarea curățării prin Power Query sunt cele trei practici care elimină 80% din erorile de date. Acest ghid prezintă tehnicile concrete, cu formule exacte și pași Power Query.

1. Validarea datelor — previne erorile înainte să apară

Data Validation (Date → Data Validation) permite definirea regulilor acceptate pentru fiecare coloană. Se configurează o singură dată și blochează automat introducerea valorilor incorecte.

Validare cu listă fixă (Dropdown)

// Data → Data Validation → Allow: List → Source:
Activ,Inactiv,Suspendat

// Sau referință la un range dintr-o altă foaie:
=Nomenclatoare!$A$2:$A$50

// Mesaj de eroare personalizat:
// Error Alert → Title: "Valoare invalidă" → Message: "Selectează din listă"

Validare numerică cu formulă personalizată

// Permite doar valori între 0 și 100000, cu mesaj:
Data Validation → Allow: Decimal → Between: 0 și 100000

// Validare cu formulă — nu permite duplicate în coloana A:
Data Validation → Allow: Custom → Formula:
=COUNTIF($A$2:$A$1000,A2)=1

// Validare dată — doar zile lucrătoare:
=AND(A2>=DATE(2024,1,1), WEEKDAY(A2,2)<=5)

2. Structurarea datelor ca tabele Excel (Ctrl+T)

Tabelele structurate (Insert → Table sau Ctrl+T) oferă referințe dinamice, filtrare automată și compatibilitate perfectă cu Power Query și Tabele Pivot. Un tabel Excel are întotdeauna un nume și coloane cu anteturi clare.

// Creare tabel:
Selectează orice celulă din date → Ctrl+T → "My table has headers" ✓

// Redenumire tabel (obligatoriu):
Table Design → Table Name: tbl_Vanzari

// Referințe structurate în formule — mai clare decât A2:A1000:
=SUM(tbl_Vanzari[Valoare])
=AVERAGEIF(tbl_Vanzari[Status],"Activ",tbl_Vanzari[Valoare])

// Calcularea unui total în ultima coloană:
=[@Cantitate]*[@PretUnitar]*(1-[@Discount])
// Coloana se completează automat pe toate rândurile

Avantajele tabelelor față de range-uri normale

3. Curățarea datelor cu formule

Înainte de analiză, datele importate din sisteme externe conțin adesea spații extra, caractere invizibile, majuscule inconsistente sau valori lipsă. Aceste formule rezolvă cele mai frecvente probleme:

// Elimina spații la început, final și duble din interior:
=TRIM(A2)

// Elimina caractere non-printabile (tab, newline, ASCII <32):
=CLEAN(A2)

// Combinat — cel mai sigur pentru text importat:
=TRIM(CLEAN(A2))

// Standardizare majuscule:
=PROPER(A2)     // Primul Caracter Mare
=UPPER(A2)      // TOT MAJUSCULe
=LOWER(A2)      // tot minuscule

// Extragere cod numeric dintr-un text mixt "RON 1.250,00":
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"RON ",""),".",""))

// Verificare dacă o celulă conține doar cifre:
=ISNUMBER(VALUE(A2))

// Înlocuire valori lipsă (goale) cu 0 sau text:
=IF(ISBLANK(A2), 0, A2)
=IFERROR(VLOOKUP(B2,tbl_Ref,2,0), "Negăsit")

4. Curățare automată cu Power Query

Power Query aplică aceiași pași de curățare la fiecare actualizare a datelor, fără intervenție manuală. Odată configurat, Ctrl+Alt+F5 reprocessează tot fișierul în câteva secunde.

Pași de curățare frecvenți în Power Query

// Acces: Data → Get Data → From Table/Range (sau From File)
// Toți pașii de mai jos se înregistrează în "Applied Steps"

// 1. Elimina rânduri cu erori:
//    Home → Remove Rows → Remove Errors

// 2. Trim și Clean pe coloane text (clic dreapta pe antet coloană):
//    Transform → Text → Trim
//    Transform → Text → Clean

// 3. Schimba tipul coloanei:
//    Clic pe iconița tip lângă antet → Date, Whole Number, Text, etc.

// 4. Filtrare rânduri invalide:
//    Dropdown antet → uncheck valorile invalide (null, erori, spații)

// 5. Adaugă coloană calculată:
//    Add Column → Custom Column:
=if [Status] = "Activ" then [Valoare] * 1.19 else [Valoare]

// 6. Unpivot (transformare din wide în long format):
//    Selectezi coloanele de valori → Transform → Unpivot Columns

// Cod M pentru trim pe toate coloanele text simultan:
= Table.TransformColumns(Source, List.Transform(
    List.Select(Table.ColumnNames(Source), each Table.Column(Source,_){0} is text),
    each {_, Text.Trim, type text}
  ))

5. Detectarea duplicatelor și inconsistențelor

// Marcare duplicate cu Conditional Formatting:
// Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values

// Formulă pentru identificarea duplicatelor (returnează TRUE dacă e duplicat):
=COUNTIF($A$2:$A$1000,A2)>1

// XLOOKUP pentru reconciliere între două liste — găsire valori lipsă:
=XLOOKUP(A2, tbl_Referinta[Cod], tbl_Referinta[Denumire], "LIPSĂ")

// Numărarea valorilor unice dintr-o coloană:
=SUMPRODUCT(1/COUNTIF(A2:A1000,A2:A1000))

// Sau cu UNIQUE (Excel 365):
=COUNTA(UNIQUE(A2:A1000))

// Găsire prima apariție a unui duplicat:
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicat","Prima apariție")

6. Consolidarea datelor din mai multe surse

Când datele vin din mai multe fișiere sau foi, Power Query este soluția optimă față de VLOOKUP-uri manuale.

// Combinare fișiere din folder:
// Data → Get Data → From File → From Folder → calea folderului
// Combine → Combine & Transform Data → selectezi foaia

// Merge (JOIN) între două tabele (echivalent SQL JOIN):
// Power Query → Home → Merge Queries → selectezi cheia de join
// Join kind: Left Outer (păstrează toate rândurile din stânga)

// Cod M pentru Left Join:
= Table.NestedJoin(
    tbl_Vanzari, {"CodClient"},
    tbl_Clienti, {"Cod"},
    "ClientiExpandat",
    JoinKind.LeftOuter
  )

// Append (UNION) — adaugă rânduri din alt tabel cu aceleași coloane:
// Home → Append Queries → selectezi tabelul sursă

Pentru proiecte complexe de structurare și automatizare a datelor în Excel, Excel Group MD oferă consultanță specializată adaptată nevoilor fiecărei companii.