Excel: Custom consolidation

Consolidarea sau centralizarea reprezintă una din problemele principale ale celor care lucrează cu mai multe foi de calcul din Excel, pe care la anumite perioade de timp trebuie să le centralizeze.

Prezentul articol dorește să prezinte o metodă de rezolvare a acestei probleme.

În mare parte la consolidare trebuie să încercăm să ne încadrăm în anumite restricții, mai ales în contextul în care lucrăm cu fișiere multiple:

• Structura fișierelor să fie identică:  același tip și număr de coloane; liniile să aibă coduri din același interval;
• Respectarea convențiilor de nume: Ex: numele fișierelor de genul numeagent-luna.xlsx; numele foilor de calcul sau a tabelelor de date etc.

De cele mai multe ori teoria este atât de simplă pentru că poți să predai ce vrei, cum vrei, sau cum ai învățat acasă. În practică, practic cam câți oameni sunt care lucrează, cam atâtea metode sunt.

Mai jos avem un model de fișier de vânzări de la 3 agenți pe o perioadă de 5 zile. Datele despre vânzări sunt în cifre să presupunem.

 

 

https://r.office.microsoft.com/r/rlidExcelEmbed?su=7981371974048657041&Fi=SD6EC38785F56DAE91!2212&ak=t%3d0%26s%3d0%26v%3d!AAAU-nfKVpuMcbw&kip=1

Descărcare fișier Excel

Fiecare agent vinde o anumită gamă de sortimente iar la finalul săptămânii folosind o metodă sau alta un operator încearcă să facă centralizarea datelor.

În mare parte operațiunile manuale care nu au nici o fel de productivitate și care sunt sortite cel mai adesea greșelilor de operare, le-am numit eu generic: MDT (Metoda De la Țară).

În foaia MDT1

Datele din această foaie de calcul sunt preluate cu copy/paste din 3 foi de calcul cu date despre vânzările agenților.

După care am realizat sortarea după cod produs, și am realizat sub-totaluri cu sumă pentru fiecare zi, grupate după coduri.

Dezavantaje:

• datele nu sunt dinamice
• dacă sunt prea multe fișiere de centralizat operațiunile de deschidere închidere sunt consumatoare de timp.
• nu mai cunoaștem sursa originală a datelor
• riscuri de operare.

În foaia MDT2

Datele din această foaie de calcul sunt preluate cu copy/paste special din 3 foi de calcul cu date despre vânzările agenților.

Operațiunea este simplă dar dacă ordinea codurilor sau numărul de linii este diferit se pierde consistența datelor.

Dezavantaje:

• datele nu sunt dinamice
• dacă sunt prea multe fișiere de centralizat operațiunile de deschidere închidere sunt consumatoare de timp.
• nu mai cunoaștem sursa originală a datelor
• riscuri de operare
• necesitatea respectării numărului de linii și consistență a datelor

În foaia MO

Datele din această foaie de calcul sunt preluate cu Data, Consolidate din 3 foi de calcul cu date despre vânzările agenților.

Avantaj:

– rapiditatea cu care sunt centralizate datele

Dezavantaje:

• Nu este dinamic
• Când zonele de date sunt formatate ca tabel, nu putem folosi opțiunea Top Row!
• De fiecare data când se schimbă structura unui tabel (numărul de coloane sau linii), trebuie să refacem consolidarea!

Funcționează și cu date din fișiere diferite.

În foaia MP

Datele din această foaie de calcul sunt preluate cu VLOOKUP din 3 foi de calcul cu date despre vânzările agenților și se realizează însumarea datelor.

În același timp este realizată tratarea erorilor de tip #N/A folosind funcția ISNA()

Dezavantaje:

• complexitatea formulei
• modul greoi de extensibilitate a formulei

Avantaje:

• complet dinamic
• nu are importanță numărul de linii dintr-o parte sau alta
• funcționează foarte bine din tabele de date

Funcționează și cu date din fișiere diferite.

Formula de calcul pentru primul cod de produs:

=IF(ISNA(VLOOKUP($A3;AgentA;B$1;FALSE))=TRUE;0;VLOOKUP($A3;AgentA;B$1;FALSE))+IF(ISNA(VLOOKUP($A3;AgentB;B$1;FALSE))=TRUE;0;VLOOKUP($A3;AgentB;B$1;FALSE))+IF(ISNA(VLOOKUP($A3;AgentC;B$1;FALSE))=TRUE;0;VLOOKUP($A3;AgentC;B$1;FALSE))

 

Sper să vă fie util.

PS. Dacă mai aveți idei de îmbunătățire sau tot felul de probleme ciudate din practică vă rog să le pasați aici cu încredere pentru că le dau la studenți să le rezolve/optimizeze.