Lectia 12 Consolidarea (Centralizare, Cumulare, Sintetizare)

Scopul lectiei

Ce se întelege prin Consolid 838l112i are

(Centralizare)

Cum se face si la ce foloseste

Ce este centralizarea

EXCEL-ul întelege prin "Consolidate"- (Centralizare, Sintetizare) cumularea datelor din mai multe tabele, (care în general au exact aceiasi structura), într-un singur tabel (lista) care de asemenea are aceiasi structura.

Cu alte cuvinte având datele (Fig 12.1) din ianuarie, februarie si martie 2005 sa obtinem automat tabelul (lista) pentru trimestrul 1/2005.

Tabele Sursa

Tabel  Rezultat (Cumulat)

Fig 12.1 (Totul pe aceiasi Foaie de lucru)

În final vom avea:

O serie de Tabele Sursa si un Tabel Rezultat (Cumulat).

Tabelul Rezultat (Cumulat) poate fi legat de Tabelele Sursa sau nu.

Daca Tabelul Rezultat este legat atunci orice modificare facuta în unul sau mai multe Tabele Sursa se va regasi în Tabelul Rezultat.

Recunoastem ca un Tabel Rezultat (Cumulat) este legat de Tabele Sursa prin aceea ca Tabelul Rezultat (Cumulat) contine formule.

Tabelele Sursa si Tabelul Rezultat se afla pe aceiasi Foaie de lucru.

Observati în Fig 12.2 ca Tabelele Sursa au formule, dar în Tabelul Rezultat (Cumulat) nu exista formule. Daca tabelul (lista) Rezultat se gaseste pe aceiasi Foaie de lucru cu tabelele Sursa, el nu va contine formule, deci nu este legat.

Fig. 12.2 (Tabelul Consolidat este pe aceiasi Foaie de lucru cu tabelele Sursa)

În listele ce vor urma denumirile coloanelor sunt scrise prescurtat. Pentru întelegerea lor avem urmatoarea legenda:

Tabelul Rezultat se afla pe alta Foaie de lucru

Fig. 12.4 (Formulele existente în Tabelul Rezultat din Fig 12.3 dar nu toate ca în Fig 12.7)

În acest Tabel Rezultat (Cumulat) (Fig. 12.4) exista si niste plusuri (semnul +) care pot

fi "deschise" vezi Fig 12.5 si Fig 12.6 (se face clic cu butonul stâng pe ele). Seamana cu cele de la Subtotaluri. Semnul plus când apare în partea stânga a ecranului înseamna ca sunt rânduri ascunse. Urmariti secventa rândurilor 1, 5, 9 si observati care rânduri sunt ascunse .

	Se poate face clic cu butonul stâng al mausului pe semnul plus

Aici s-a facut clic cu butonul stâng si semnul plus s-a transformat în minus. Daca se face clic cu butonul stâng pe minus el se transforma în plus si rândurile se ascund. Verificati ca: 15+98+55 = 168

Fig 12.5 (Tabelul Rezultat cu total expandat)

Pentru mag 2

Pentru mag 1

Ianuarie 2005 Februarie 2005 Martie 2005 Ianuarie 2005 Februarie 2005 Martie 2005

Fig 12.6 (Tabelul rezultat, nivelul 2, cu toate expandarile facute. Nu mai sunt rânduri ascunse)

Urmariti si formulele din Tabelul Rezultat (Cumulat) când semnul plus din stânga este activat (s-a transformat în minus) Fig 12.7. (Atentie foile nu au fost redenumite în prelabil). Registrul se numeste Consolid, adica fisierul se numeste Consolid.xls.

În coloana B apare numele Registrului în care se gaseste Foaie1.

Fig. 12.7 (Toate formulele din Tabelul Rezultat)

Cu alte cuvinte în Tabelul Rezultat (Cumulat) se gasesc "ascunse" datele din Tabelele Sursa (Fig 12.7) si aceste date pot fi vizualizate Fig 12.6.

Cum se realizeaza aceasta cumulare (centralizare)

Urmarim încasarile, platile si profitul a doua magazine, pe durata unui trimestru. Fiecare luna este cuprinsa în Foaie de lucru separata. Rezultatul îl centralizam în alta Foaie de lucru. S-a redenumit foile ca în Fig 12.8.

Fig 12.8

Pentru luna ianuarie 2006 s-au stabilit forma tabelului si formulele din Fig 12.9, ca si pentru celelalte luni si întreg trimestrul.

Tabelele care reprezinta lunile se vor complecta treptat pe masura ce trece timpul. Tabelul pentru întreg trimestrul trebuie sa se modifice concomitent cu aparitia oricarei modificari corecte din orice luna. Întelegem prin modificari corecte numai modificarile efectuate în celulele unde nu sunt formule. Cu alte cuvinte celulele B2,B3,C2,C3 din fiecare tabel al fiecarei luni. De tabelul final nu ne atingem! Tot ce este în el trebuie sa rezulte din modificarile facute în Tabelele Sursa. Operatiunea de centralizare înseamna construirea mecanismul prin care Tabela Rezultat (Trim 1-2006) sa fie legata de Tabelele Sursa (Ian_2006, Feb_2006, Mar_2006).

Legarea se face prin formule introduse automat de catre EXCEL, asa cum este în Fig 12.7 sau în Fig 12.16.

Se apeleaza comanda Centralizare Fig 12.10. Clic pe Date Centralizare si rezulta Sintetizare.

Fig 12.10

În ferestruica Functie sa fie suma. În ferestruica Referinta vom colecta treptat câte o Tabela Sursa pe care o vom depune în Toate referintele.

Fig 12.11

Ferestruica Referinta are un "declansator" facem clic pe el si pe ecran ramâne numai ferestruica. Facem clic pe numele foii Ian_2006 si selectam cu mausul tabelul $A$2:$D$5. Automat apare în ferestruica ce se vede în Fig 12.11. Facem clic pe Adaugare si continutul ferestruici Regasire se copiaza în Toate referintele. Procedam la fel pentru celelalte 2 luni.

Obtinem în final Fig 12.12 si dupa ce facem clic pe OK, tabelul pe trimestru din Fig 12.13. Legatura este realizata prin formule.

Fig 12.12 (Toate referintele sunt adaugate)

Fig 12.13 (Tabele Sursa si Tabelul Rezultat)

Fig 12.14 (Ce se întâmpla daca mag 1, în martie, are încasari 11?

Atentie! Revenim la Fig 12.12. Aici trebuie sa bifati toate cele 3 casute din Fig 12.15

Daca nu sunt bifate cele 2 din stânga, Rândul de sus si Coloana din stânga, în tabelul rezultat nu mai apar denumirea rândurilor si a coloanelor. Vezi Fig 12.15 dreapta. Nu e bine!

Daca nu este bifata Crearea legaturi. atunci tocmai ce este mai important nu se realizeaza. Formulele din Fig 12.16 sunt create numai prin aceasta bifa.

Fig 12.16 (Formulele din Tabelul Rezultat)

În coloana B apare Centr. Acesta este numele Registrului, deci a fisierului (Centr.xls).

În coloanele C, D, E apar legaturile, prin formule, între Tabele sursa si Tabelul Rezultat numai sa facem clic pe plusurile care permit expandarea.

Fig 12.17

În Tabele Sursa aveti grija ca toate câmpurile numerice si goale sa aiba 0 (zero) în ele. Nu trebuie lasate spatii! Formatarea lor ca Numere este treaba ideala, dar punând zero, este acelasi lucru. În caz contrar, în celulele cu spatii nu apar formule, iar restul celulelor au formulele decalate.

Concluzii la Centralizare

Operatia de Centralizare poate fi numita si Cumulare sau Consolidare. Ultimul termen este cam "americanizat". Exista tendinta de a numi operatia Sintetizare, ceea ce nu consider ca este corect.

În lectia urmatoare vom vorbi de Tabele pivot, care se mai numesc Tabele de sinteza.

Tabelele de sinteza sunt si Tabelele Rezultat provenite din Cumulare si cele rezultate din Pivotare.

Traducerea Sintetizare din Fig 12.12 nu este inspirata, dar nici originalul american, nu este mai breaz. Poate în limba lor o fi o potrivire mai buna!? În concluzie termenul de Sintetizare sa ramâna un termen generic, valabil pentru mai multe operatii, la care poate fi supusa o BD în EXCEL.

EXCEL-ul este o unealta, nimic mai mult!

Utilizatorul este cel care poate folosi corect sau gresit aceasta unealta.

Daca vreti sa tineti o evidenta a stocurilor si pe fiecare Foaie de lucru sa fie o luna, nu faceti o centralizare a lor.

În Tabelul Rezultat veti avea cumulate toate stocurile initiale de la începutul fiecarei luni, stocurile finale de la sfârsitul fiecarei luni, ceea ce este o absurditate.

Intrarile si iesirile de asemenea vor fi cumulate, dar asta e corect. Ele dau "rulajul" ceea ce este foarte necesar.

Retineti ca Evidenta stocurilor pe un an are ca stoc initial, stocul initial al lunii ianuarie si stoc final, stocul final al lunii decembrie. Se poate face un tabel cumulat pentru un an, dar nu toate datele sunt de folosinta!