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.

image

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.

Anunțuri

7 gânduri despre “Excel: Custom consolidation

  1. Buna ziua. Eu as avea o problema cu centralizarea a circa 800 de sheet-uri dintr-un singur fisier xls, intr-un singur sheet..fisierul provine dintr-un fisier pdf cu 800 de pagini, extras cu Nitro pdf(care nu are optiunea de centralizare intru-un singur sheet).

    Apreciază

    • @Deltady: Structura datelor din fiecare foaie de calcul? Numele foilor de calcul este dupa un algoritm?
      @T: Nu e mai greu de facut replicarea formulei? Eu o scriu in patratica aferenta codului 1 si ziua de luni si o copii in jos si in dreapta.

      Apreciază

  2. Structura este formata din urmatoarele campuri/coloane: Judet, Cod fiscal, Intreprindere, Adresa, Localitate, Telefon, Fax. Pe fiecare pagina se repeta capul de tabel de mai sus. Numele fiecarui sheet este: Table 1, Table 2, ….Table 784. Multumesc

    Apreciază

    • @deltady: dupa capul de tabel, cred ca nu prea ai cum sa faci consolidare acolo. Ci trebuie sa faci copierea datelor intr-un centralizator. Daca ar fi dupa capul meu, cred ca as face un macro pentru treaba asta. Daca poti sa-mi trimiti fisierul undeva pot sa-mi fac o idee mai buna. Si sa nu uiti de datele de facturare :)
      @T: Scriam la inceputul articolului: „În practică, practic cam câți oameni sunt care lucrează, cam atâtea metode sunt.” :)

      Apreciază

Lasă un răspuns

Completează mai jos detaliile tale sau dă clic pe un icon pentru a te autentifica:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare / Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare / Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare / Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare / Schimbă )

Conectare la %s