Microsoft Office Excel
uređuje Dragan Grbić

Filtriranje cele radne sveske i konsolidacija

Nivo:
NIVO 2 - Među ovim prilozima se nalaze preporuke koje se uglavnom odnose na bolje snalaženje i efikasniji rad: dodatni trikovi koji će biti svakom od pomoći i koji upućuju na prag napredne upotrebe programa.

U toku konsultacija i kurseva koje držim, često volim da ponavljam korisnicima Excela da značajan element efikasnosti čini dobra i celishodna priprema podataka, pri čemu je dobro da poznajete ograničenja koja postoje u raznim analitičkim alatima. Takođe, stalno naglašavam kako je dobro da organizujete podatke po principu "jedna celina - jedan radni list" i da čuvate integritet podataka na taj način, sprečavajući nedoslednosti ili čak i nehotično narušavanje izvornih podataka. Ovoga sam se setio nedavno, kada mi je jedan čitalac uputio pitanje na koje sam morao da odgovorim uslovno negativno. Pitanje je glasilo: "kako da filtriram celu radnu svesku, koja se sastoji od više radnih listova, tako da podatke dobijem kao posebni radni list?"...

Klasičnim putem ne možete postavljati filter na celu radnu svesku. Nosilac filtriranja je uređena lista podataka, a nju možete organizovati samo na jednom radnom listu. Da biste uspešno filtrirali listu, ona mora da bude neprekinuta, sa zaglavljem u prvom redu. Međutim, daleko od toga da nema rešenja: ako podataka nema previše, možete ubaciti novi radni list, pa da sve podatke iskopirati na njega, i to tako da zajedničko zaglavlje prenesete samo jednom, a onda da sastavite sve redove podataka. To je moguće i automatizovati kroz VBA proceduru; evo primera koji uopšte nije optimizovan (što i nije toliko bitno sa stanovišta da služi za jednokratnu radnju). Preduslov je da svi radni listovi imaju liste sa istim profilom koji počinje zaglavljem od ćelije A3.

Sub SastaviListe()
' sastavljanje više listi u jednu
' preduslov: svi radni listovi imaju liste
' liste počinju zaglavljem od A3
' i sve imaju isti broj kolona
' broj redova nije bitan

Application.ScreenUpdating = False

Sheets.Add Before:=Worksheets(1)
Sheets(1).Name = "zbirno"
Sheets(1).Activate
ActiveSheet.Range("A1").Value = "Sastavljene liste"
ActiveSheet.Range("A3").Select

' prenos zaglavlja sa drugog lista
Sheets(2).Select
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select

' prenos sadržaja
For i = 2 To Sheets.Count
  Sheets(i).Select
  Range("A4").Select
  Range(Selection, Selection.End(xlDown)).Select
  Range(Selection, Selection.End(xlToRight)).Select
  Selection.Copy
  Sheets(1).Select
  ActiveSheet.Paste
  Selection.End(xlDown).Select
  ActiveCell.Offset(1, 0).Range("A1").Select
Next

Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Preneto je " & Sheets.Count - 1 & " listi"

End Sub

S druge strane, vredi razmisliti i o preseljenju rešenja iz Excela u Access. Strukture kojima raspolažete u tom programu daleko prevazilaze uslovna ograničenja koja nameće Excel: jedna Access tabela može da okupi mnogo više podatka nego radni list u Excelu, a ako želite da simulirate sadržaj pojedinih radnih listova, to ćete postići krajnje jednostavnim upitom (npr. SELECT upit po opsegu datuma). Postoji i varijanta da izvorne podatke iz Excela povežete u Access, pa da jednim JOIN upitom povežete sve imenovane liste. To je možda pomalo zametan posao, ali ima smisla ukoliko je struktura polazne radne sveske unapred poznata.

Ali, posle svega, nisam mogao da odolim, a da se ne vratim ovom pitanju. Jedna od iskustvenih pouka posle mnogo vremena provedenog u Excelu kaže da nijedan problem nije nesalomiv ako iskombinujete razne tehnike; recimo, jednom ću vam preneti najluđu ikad viđenu tehniku sortiranja u Excelu (uz pomoć formula (!) - to sam video u jednom blogu). Usput ću vas podsetiti na zanimljivu i retko korišćenu tehniku u Excelu: to je konsolidacija tabela ili svođenje više tabela na jednu zbirnu. Ova tehnika krije u sebi nekoliko izrazito zanimljivih detalja, ali sad ću da se osvrnem samo na neke posebne osobine. Naglašavam da je ova tehnika primerena samo ako želite da imate trajnije rešenje sastavljanja podataka iz više uređenih lista koje ne menjaju broj redova, ali se podaci u njima često menjaju. Slučaj je možda poseban, ali overite ovu tehniku, jer ćete možda i sami dobiti neku korisnu ideju iz nje.

Pravilo kaže da se konsolidacija definiše kao stalni element radnog lista: kada je jednom definišete u dijalogu Data / Consolidate (Podaci / Konsoliduj), ona ostaje tako zabeležena čak i ako izbrišete sve podatke u dobijenoj tabeli. Kada radite sa više malenih tabela, praksa je da se sve one strpaju na jedan radni list, a da se i konsolidacija obavi na istom mestu. Ali, "moje" načelo podele na više radnih listova sugeriše da ne činite tako: počećemo od novog, praznog radnog lista na kome ćemo formirati konsolidaciju.

Pre konkretne izvedbe, moram da naglasim još nešto: glavne "vođice" podataka za konsolidaciju su zaglavlja redova i kolona. Ako radite sa listama koje imaju samo zaglavlje kolona u gornjim redovima, završićete sa sabranim podacima, što ne mora da bude loše, ali to možda nije ono što ste hteli da dobijete. Zato ćemo napraviti konsolidovanu tabelu tako što ćemo od svake liste napraviti "dvodimenzionalnu" tabelu dodajući joj redne brojeve u novoj, ubačenoj koloni na levoj strani.

Dakle, u prvoj listi ubacite novu kolonu A: kliknite desnim tasterom miša na zaglavlje kolone, pa u kontekstnom meniju odaberite Insert (Umetni). U zaglavlje nemojte dodavati obeležje, ali počev od prvog reda podataka ubacite brojeve 1 i 2, obeležite ih, pa kliknite dvaput na ručicu za popunu u donjem desnom uglu opsega i dobićete redne brojeve do poslednjeg reda. Overite poslednji broj, pa ponovite postupak, ali tako da redni brojevi počinju od većih brojeva nego što je poslednji redni broj na prethodnom listu.

Kada to učinite za sve uređene liste, pređite na prazan radni list, aktivirajte ćeliju koja će biti prva u novoj listi, pa pozovite dijalog za konsolidaciju. Aktivirajte polje reference, pa pređite na radni list sa prvom listom, obeležite je i dodajte u spisak referenci klikom na dugme Add (Dodaj); ponovite postupak za sve liste, ne zatvarajući dijalog. Uočićete zgodnu pomoć: ako su sve liste iste dimenzije, onda će one biti već obeležene nakon prvog ubacivanja. Nakon definisanja referenci, uključite polja za potvrdu za primenu oznaka u gornjem redu i levoj koloni, pa aktivirajte formiranje klikom na OK.

I to je to: dobićete novu listu koja sadrži sve jedinstvene redove, a sa njom ste slobodni da činite šta god poželite. Kada poželite da rekonstruišete listu novim podacima, izbrišite dobijenu listu podataka, pa ponovo pozovite dijalog i samo kliknite na OK; ako je veličina neke liste promenjena, izmenite njenu referencu, ali pazite da dodate jedinstvene redne brojeve (slobodno unesite neki proizvoljno velik broj).

Namerno vam nisam predložio da u dijalogu uključite održavanje veze konsolidovane tabele sa polaznim podacima, jer to završava malo drugačijom strukturom rezultujuće tabele (probajte ako ste zainteresovani). Toliko za sad; u jednom od narednih priloga ću vam pokazati kako da upotrebite konsolidaciju da biste sabrali samo željene podatke.