[Excel] Godišnja kumulacija (mjesečno dodavanje podataka)

[ Sudarica @ 18.01.2009. 09:44 ] @

Molim pomoć,

imam problem kod unosa podataka u godišnju kumulaciju.Pošto se asortiman mjenja često trebala bi pomoć oko kumulacije. Sada imam složenu kumulaciju pomoću funkcija, ali to nije dobro.
Imam radni list "godišnja kumulacija" u koji svaki mjesec dodajem podatke iz radnog lista "mjesec"(iz kojega brišem svaki mjesec podatke i uljupljujem druge iz druge datoteke). Svaki put moram mjenjati funkcije u godišnjoj kumulaciji i dodavati nove šifre ili kolone koje do tada nisam imala i provjeravati totale dali su u redu uglavnom jako komplicirano.

Dali postoji mogućnost da u radni list "godišnja kumulacija" dodam iznose iz radnog lista "mjesec" tako da mu naziv stupca bude jedan uvjet a šifra drugi uvjet. Ako nema toga naziva stupca ili šifre da ubaci novi stupac sa nazivom i nekako ga istakne (npr.plavom bojom),a za šifru ako je nema doda novi red u grupi koja počinje određenim slovom a ako nema onda ga doda prema sortu kamo spada i za njega napravi total i uključi ga normalno u krajnji zbroj. Naprimjer ako počinje sa šifrom A doda ga u tu grupu a ako počinje sa šifrom M koje nema da ju doda na kraju ispred konačnog zbroja napravi total za nju i uključi u konačan zbroj.

dodaću primjer jer mislim da će tako biti jasnije

[ Jpeca @ 19.01.2009. 06:51 ] @

Pretpostavljam da je ovo najlakše uraditi koristeći konsolidaciju - pogledaj primer.
1/ Na listu za godišnju kumulaciju selektuju ćeliju A1,pa aktiviraj Data -> Consolidate.
2/ U prozoru konsolidacije za selektuješ i dodaješ (Add) opsege iz mesečnih listova koje treba sabrati u godišnju kumulaciju. U opsege iz mesečnih listova uključi i naslove kolona i redova.
3/ Uključi Use label in top row i first column

I to je sve! Na listu će ti se dodati sve kolone i redovi koji se pojavljuju u pojedinačnim opsezima.

Ukoliko je potrebno istaći kolone/redove koji su "novi" to možeš uraditi pomoću Uslovnog formatiranja (Conditional Formating). Za nazive kolona:
1/ Imenuj početnu listu, npr iz meseca januara koja ti služi kao osnova za upoređivanje. Ja sam je nazvao ListaJan.
2/ Selektuj nazive kolona u godišnjoj kumulaciji i aktiviraj uslovno formatiranje.
3/ Za uslovno formatiranje primeni formulu

Code:
=COUNTIF(ListaJan;B$1)=0

4/ Zadaj format koji želiš.

Na taj način nazivi iz lista godišnje kumulacije biće formatirani ukoliko ne postoje u nazivima kolona iz januara meseca.

_{[Ovu poruku je menjao Jpeca dana 19.01.2009. u 20:49 GMT+1]}

[ Sudarica @ 01.02.2009. 14:16 ] @

Ovo je potpuno u redu i radi savršeno. U prvom stupcu uvijek mora biti kriterij po kojem želim konsolidaciju i to je to.

Ali može biti više prikaza kumulacija, meni bi trebalo nešto pomoću čega bi odredila kakvu kumulaciju želim. Ako je kumulacija nižeg ranga da mi zadrži podatke iz viših rangova. Napromjer hoću kumulaciju po Broju kupca a da mi zadrži podatke iz stupca mjesto, šifra i tržište.

Također bi trebala odabrati šta želim kumulirati. Nekada mi treba samo stupac jabuke i kruške a nekada sve.
Komplicirano, ali jako potrebno.

Hvala i
Pozdrav

(ja nemogu napraviti Upload uz poruku)

_{[Ovu poruku je menjao Sudarica dana 01.02.2009. u 15:27 GMT+1]}

[ Jpeca @ 02.02.2009. 10:48 ] @

Za dobijanje različitih sumarnih izveštaja u Excelu se koriste izvedene tabele (Pivot). U jednoj radnoj svesci možeš imati više različitih pivot tabela na osnovu istih izvornih podataka (po broju kupca, mestu, tržištu...), a pivot tabele ti i omogućuju da sam izveštaj dinamički menjaš (sve ili samo Jabuke i kruške). Da bi formirala izvednenu tabelu podaci moraju na odgovarajući način da budu pripremljeni. Ukoliko su svi detalji koji ti trebaju sadržani u rekapitulaciji onda rekapitulaciju možeš da uzmeš kao izvor podataka za izvedenu tabelu u tvom slučaju.
Ukoliko ti trebaju neki detalji sa pojedinačnih (mesečnih) listova u zbirnom izveštaju, kojih nema u rekapitulaciji, onda možeš da formiraš izvedenu tabelu na osnovu podataka iz mesečnih listova Multiple Consolidation Ranges.

[ Sudarica @ 08.02.2009. 09:14 ] @

Ukoliko ti trebaju neki detalji sa pojedinačnih (mesečnih) listova u zbirnom izveštaju, kojih nema u rekapitulaciji, onda možeš da formiraš izvedenu tabelu na osnovu podataka iz mesečnih listova Multiple Consolidation Ranges.[/quote]

Multiple Consolidation Ranges ne znam što je to?

Mislite za svako pojedino područje konsolidirati podatke. Koliko područja toliko rekapitulacija?

Pozdrav

PS
Malo sam učila o pivot tabelama jer ste me potaknuli. One uglavnom služe za različite prikaze podataka ili imaju još neke mogućnosti a ja ih nisam pronašla.???

Hvala

[ Jpeca @ 08.02.2009. 18:24 ] @

Pivot tabele zahteveju malo šire objašnjenje nego što ja imam vremena, ali ovoj veoma korisnoj alatki naćićeš u svakoj ozbiljnijoj knjizi o Excelu.
U osnovi izvedene (pivot) tabele su interaktivne tabele koje se koriste za analizu podataka. Pretpostavka za izvedene tabele je unapred kreirana lista podataka koja sadži sve detalje bitne za analizu. Ovo često mogu biti dugačke i obimne liste. U izvedenoj tabeli podaci iz takve se sumiraju, prebrojavaju, sortiraju itd. tako da se dobiju značajne informacije za odlučivanje, npr. ukupan promet po proizvodu, kupcu, periodu itd. Na osnovu izvedene tabele mogu se kreirati i odgovarajući dijagrami.
U prvom koraku kreiranje izvedene tabele koristeći čarobnjaka (PivotTable Wizard) deklariše se šta je izvor podataka za izvedenu tabelu. Najčešće to je jednostavno neki opseg podataka na jednom listu. Kada je su podaci raštrikani na više opsega (obično na različitim listovima) onda se koristi termin multiple consolidation ranges.

[ Sudarica @ 10.02.2009. 18:44 ] @

Htjela bi da mi zbroji iz tri lista po nazivu kupca napr. Demo ali da mi ostane da je to broj kupca 32, mjesto Ljubljana,šifra c325 i tržište Zapad. Uvijek ti podaci moraju biti upareni. Dok zbrajam po mjestu jasno je da gubim broj kupca i naziv kupca ali mi ostaje šifra i tržište.
Stavit ću primjer, ako budem znala.

Hvala na strpljenju i pomoći.

[ Jpeca @ 14.02.2009. 16:46 ] @

Problem je što izvorne tabele od kojih praviš multiple consolidation range za pivot tabelu imaju različiti broj kolona, pa je dobijena pivot tabela ograničena u pogledu detalja koje u njoj možeš prikazati. Predlažem ti da napraviš jednu zbirnu tabelu sa svim detaljima iz pojedinih listova u kojem će podaci biti pogodnije organizovani za pivot tabelu. Svaki red original tabele prepisuje se u onoliko redova koliko imaš kolona za proizvode (jabuke, kruške, ...) kao na slici

Ukoliko izvorne tabele dobijaš u obliku koji si navela, ručno preorganizovanje je zamorno pa ti preporučujem da iskoristiš makro koji će obaviti ovo prepisivanje sa promenom organizacije. Makro koji ovde prilažem startuje se dok je aktivan list iz koga treba prepisati (izvorna tabela) i dodaje redove na list "Priprema"

Code:

Sub PripremaSve()
' Prepisuje podatke iz aktivnog lista
' na kraj lista Priprema
' u obliku koji je pogodan za Pivot tabelu
'
' P.Jovanovic za elitesecurity.org
'
Dim shSource As Worksheet, shDest
Const rwZaglavlje As Integer = 1 ' red u radnom listu u kome je zaglavlje
Const colProizvod As Integer = 6 ' kolona u radnom listu od koje pocinju proizvodi
Dim rw As Long, rwEnd As Long, rwS As Long, rwD As Long
Dim cl As Integer, colEnd As Integer, clT As Integer

Set shSource = ThisWorkbook.ActiveSheet
Set shDest = ThisWorkbook.Sheets("Priprema")
rwEnd = shSource.Cells(rwZaglavlje, 1).End(xlDown).Row - 1 ' poslednji red izvorne tabele
                                                            'ne prepisuju se poslednji redovi tabele sa sumama
colEnd = shSource.Cells(rwZaglavlje, 1).End(xlToRight).Column - 1 'poslednja kolona izvorne tabele
rwD = shDest.Cells(16555, 1).End(xlUp).Row + 1              'red za upis u odredisnu tabelu
For rwS = rwZaglavlje + 1 To rwEnd
  For cl = colProizvod To colEnd 'Formira se jedan red za svaki proizvod
     For clT = 1 To colProizvod - 1 ' Prepisuju se sve kolone iz reda do kolona sa proizvodima
          shDest.Cells(rwD, clT).Value = shSource.Cells(rwS, clT).Value
     Next clT
    shDest.Cells(rwD, colProizvod).Value = shSource.Cells(1, cl).Text  'Naziv proizvoda
    shDest.Cells(rwD, colProizvod + 1).Value = shSource.Cells(rwS, cl).Value 'Iznos
    rwD = rwD + 1
  Next cl
Next rwS
End Sub

Ti ga možeš doraditi da bolje odgovara tvojim potrebama.