Centralizarea datelor din mai multe sheet-uri in Excel


Din cand in cand mai public si guest post-uri, cum zicea si trupa TAXI, “e un hobby de-al meu“. De bucurie scriu si cu verde 🙂 . In acest articol Tudor vorbeste despre centralizarea datelor din mai multe sheet-uri. Hai sa vedem ce ne invata. 


Consolidate excel thumb2

In acest articol, ma refer la centralizare unor pachete de date cu aceiasi structura (acelasi cap de tabel) dar cu dimensiuni si date diferite, situate in foi diferite sau chiar in fisiere diferite. Pentru astfel de cazuri am identificat doua moduri de abordare a acestei probleme in functie de situatie.

Cazul I. Proiect nou

Daca necesitatea centralizarii unor pachete de date de acelasi tip apare intr-un proiect dezvoltat de dumneavoastra solutia este simpla, refaceti proiectul. Excel va pune la dispozitie o mare varietate de functii si metode prin care puteti filtra si extrage datele in functie de orice criteriu, doar prin cateva click-uri.



Prin urmare nu aveti nici un motiv sa fragmentati datele pe care veti lucra, inregistrandu-le in foi separate. Daca veti alege inregistrarea datelor in foi separate in functie de luna calendaristica, de exemplu incasarile din ianuarie in foaia „ianuarie”, cele din februarie in foaia „februarie” si asa mai departe, solicitarea sefului de ai prezenta o situatie mai atipica pe intergul an o sa va strice ziua rau de tot.

Excel este construit pentru a manipula pachete compacte de date, daca le fragmentati practic va restrictionati singur accesul la aceste resurse. Pentru prelucrari intr-o baza de date astfel fragmentata va raman foarte putine solutii.

O parte dintre ele voi incerca sa le abordez in partea a treia a articolului. In concluzie, in orice proiect pe care il dezvoltati in Excel, cautati un mod de abordare in care sa pastrati datele de acelasi tip intr-o forma compacta, in aceiasi foaie, fara linii sau coloane goale intercalate, fara subtotaluri sau cap de tabel intre linii, fara celule unite, aranjate intr-o forma asemanatoare cu cea dintr-o baza de date.

Adica o linie reprezinta o inregistrare, o coloana un camp – caracteristica comuna tuturor inregistrarilor. Nu incercati sa „infrumusetati ” baza de date. In orice fisier care se respecta aceasta va fi ascunsa iar accesul utilizatorilor la ea va fi restrictionat si conditionat. Orice dashboard, grafic sau gadget care va incanta privirea are undeva in spatele lui, cel putin, o mica baza de date aranjata dupa criteriile de mai sus.

Cazul II. Situatiile in care nu se pot aplica cele descrise mai sus

Daca sunteti nevoit sa lucrati pe un fisier conceput chiar de seful si nu il puteti ofensa prin refacerea integrala sau din orice alte motive trebuie sa reuniti mai multe pachete de date cu aceiasi structura intr-o baza de date unica, care sa fie conectata la surse si sa se actualizeze odata cu modificarea sursei (adaugare, modificare valori, stergerea unei linii), inseamna ca aveti o problema.

Asa cum am precizat mai sus, nu exista solutii universal valabile care sa rezolve aceasta situatie. Voi incerca in continuare sa trec in revista unele solitii cu specificarea limitelor in care ot fi aplicate.

A. Sinteza valorica

A. Daca se urmareste doar o sinteza valorica (suma, numar, medie, valoare maxima/minima, variatia valorilor) puteti folosi metoda Consolidate din meniul Data-Data Tools. Se aplica doar pentru valorile numerice si doar pentru anumite moduri (ordine) de aranjare a datelor.

Consolidarea se face doar insotita de una dintre operatiile incluse in meniu (Sum, Count, Average, etc). Tot in meniu gasiti checkbox-ul „Create links to source data” prin bifarea caruia se vor mentine actualizate rezultatele consolidarii fata de modificarea valorilor din sursa. Consolidare nu va adauga automat liniile noi introduse daca au etchete noi („Left column” diferit).

Vezi mai multe despre Consolidate aici.

B. Volum relativ redus

B. Daca datele sursa au un volum relativ redus, de ordinul zecilor sau maxim cateva sute de linii, intr-un numar determinat de foi, puteti incerca reunirea acestora prin formule.

Centralizarea datelor in Excel 1

In exemplul prezentat se presupune ca datele se afla in Sheet1 si Sheet2 coloanele A:D incepand cu A1 si implica folosirea a cinci coloane ajutatoare care pot fi amplasate intr-o foaie separata sau pot fi ascunse (vezi fisierul exemplu „CentralizareFormula.xlsx”).

1. Se creaza o lista cu denumirea foilor din care se extrag datele (“Nume foaie”=A2:A3)

2. Se calculeaza numarul liniilor completate in fiecare foaie (“Nr Linii”=B2:B3) cu formula:

= COUNT(INDIRECT("'"&$A2&"'!$A:$A"))+COUNTIF(INDIRECT("'"&$A2&"'!$A:$A");"?*")-1

3. Se calculeaza pozitia din noua lista pentru fiecare pachet de din datele sursa (“Cumul Linii”=C2:C3) cu formula:

=SUM($B$1:B1)+1

4. Se creaza lista adreselor (incepand cu D2 si E2, formulele se trag in jos astfel incat sa acopere numarul total de linii din toate sursele).

Pentru D2 si trasa in jos:

=IF(ROW()-1>SUM($B$2:$B$3);"";LOOKUP(ROW()-1;$C$2:$C$3;$A$2:$A$3))

Pentru E2 si trasa in jos:

=IF(D2="";"";ROW()-1-MATCH(D2;$D$2:$D$21;0)+1)

5. Se extrag datele (formula se introduce in G2 si se trage in jos suficient cat sa acopere toate datele sursa si trasa in dreapta cu numarul corespunzator de coloane din datele sursa)

=IFERROR(INDIRECT(ADDRESS($E2+1;COLUMNS($A:A);1;TRUE;$D2));"")

Centralizarea datelor in Excel 2

C. Volum de date ridicat

C. Daca volumul datelor este mare si utilizarea formulelor conduce la o functionare lenta a fisierului sau numarul surselor este variabil, unica solutie este folosirea VBA (Visual Basic for Application).

Abordarea difera in functie de conditiile concrete in care sunt aranjate datele sursa, de cerintele de aranjare a rezultatelor si de modul in care se doreste actualizarea rezultatelor, de aceea nu veti gasi o solutie universal valabila pentru orice situatie, acestea fiind de regula adaptate unor conditii concrete, bine definite.


Mersi Tudor, te mai astept si cu alte articole, si raspunsuri pe Forum.