Da li imate situacije da jednu istu operaciju u Excel-u morate da ponavljate na više sheet-ova?
Rad sa više radnih listova istovremeno spada u napredne tehnike tj. napredni Excel i Excel Guru ovu temu obrađuje na naprednom kursu Excel-a (Business Excel III – Master Excel kursu). Ipak smatramo da ova tema zaslužuje posebnu pažnju pa ćemo je zato pomenuti u ovom postu.
Ispričaću vam moje iskustvo i prvi susret sa ovim problemom. Neki od vas će prepoznati sebe u ovoj priči jer radite sličan posao. Svakako možete odmah preći na deo koji je posvećen samoj tehnici – kliknite ovde.
U početku bavljenja poslom analitičara dobio sam zadatak da na mesečnom nivou održavam između ostalog i tabele mesečne prodaje za 8 tržišta. Manuelan posao, posla mnogo a vremena početkom svakog meseca – u manjku.
Prvi problem – fajl je nasleđen!
Drugi problem – koristi se dugi niz godina, gomila ljudi je navikla na njega – ništa ne dirati!!! Samo se unose mesečne količine prodaje u kolone (po potrebi se dodaju redovi ako se lansira neki novi proizvod).
Treći problem – iza “kraja” tabele (poslednje kolone) postoji čitava armija sakrivenih kolona sa parametrima koje vuku odredjene formule da bi se računali određeni podskupovi vezani za proizvode, koji treba i da ostanu skriveni, ali sadrže i sledeću mesečnu kolonu koja mora da se prikaže.
Četvrti problem – osam tržista, osam radnih listova.
Peti problem – fajl treba relativno brzo osvežiti svakog meseca da bi kolege imale uvid šta se dešavalo na tržistima (zbog daljeg planiranja i korekcija).
Kako nisam imao iskustva sa obradom ovakvih fajlova, našao sam se u popriličnom problemu.
Prvi update je verovatno trajao 5-6 sati. A kako je izgledao? Otvori fajl, skroluj do desne ivice, uradi unhide kolona sa desne strane (pojavi se i kolona koju treba popuniti), promeni zaglavlje te kolone (piše da je projekciona vrednost a treba označiti da je aktuelna), sakrij ponovo sve kolone na desno (sem ove nove koju treba popuniti) pa tako puta 8.
Dodavanje novog proizvoda je bilo slična operacija. Da bi se zadržala ista struktura, bez obzira da li na nekim tržištima ima ili nema tog proizvoda mora se isti dodati u sve radne listove. Znači Insert reda, popunjavanje podataka osvežavanje formula… I opet po osam ponavljanja.
O ručnom popunjavanju podataka ovoga puta neću ni da pišem jer problem sa različitim nazivima istog proizvoda, nemogućnost rada sa vlookup-om i nedostatak maping fajlova je tema za neku drugu priliku.
I opet uz pojedine optimizacije i sitne promene radio sam na tom fajlu uz zahvalnost sudbini što ne radim celo evropsko tržište 😊, ali nisam ni znao ni razmišljao o tome kako da ovu pripremu fajla ubrzam.
A onda je kako to obično biva stigao novi zadatak… Trebalo je opet na mesečnom nivou obraditi fajl koji je eksportovan iz AC Nielsen baze podataka. Ko je ikada radio sa tim eksportom zna da, iako potpuno jednostavne strukture (u redovima meseci po godinama a u kolonama podfamilije ili familije proizvoda), je problematičan zbog toga što se izvozi u više desetina radnih listova (40+) a u nekim opcijama je to narastalo i na preko 80. Zbog potrebe obrade podataka trebalo je u svaki radni list ubaciti dve kolone, imenovati ih i ispisati u jednoj formule za zbir a u drugoj za računanje proseka po vrsti proizvoda (koloni).
Zadatak krajnje jednostavan ali ako to treba ponoviti 45 puta sa bar minut potrošen po strani, ako ste koncentrisani – baš se oduži. Uz raznorazna ometanja iz okoline, potrebno je preko sat vremena za ovaj posao, bez daljih obaveza vezanih za obradu.
I onda konačno, kad je prevelika muka naterala, našlo se i rešenje. Naravno napredni Excel je morao da uđe u igru!
Selektovanje listova sa kojima ćemo raditi se vrši jednostavno, na jedan od sledeća tri načina:
– ako nam trebaju odredjeni radni listovi koji nisu u nizu, izborom tabulatora jednog lista i uz CTRL sa tastature biramo i ostale.
– ako nam trebaju radni listovi koji su u nizu onda biramo prvi i potom uz SHIFT biramo poslednji u nizu (Excel će ih obeležiti sve)
– i ako nam trebaju svi onda desni klik na prvi od njih i birate opciju iz popup menija Select All Sheets.
Ipak treba biti oprezan, iako je moguće, nije preporučljivo pisati formule koje uzimaju podatke iz drugih radnih listova. Ako recimo označimo 10 radnih listova Sheet1 – Sheet10 i u polje A3 upisemo formulu =B3+Sheet2!B3, u sve radne listove biće upisana ova formula koja će biti adresirana na svoju B3 ćeliju i B3 ćeliju Sheeta2. Naravno ovo nije pogrešno ako nam je zaista potreban podatak iz Sheeta2, ali ako očekujemo da će Excel automatski povećati i broj radnog lista, moramo znati da se to neće dogoditi.
Ovu naprednu tehniku ima smisla koristiti u situacijama kada imate fajl koji ima mnogo istih sheet-ova, pa je ponavljanje aktivnosti moguće izbeći na ovaj način gde napredni excel dolazi do punog izražaja. Svakako primena ove tehike može naći svoje mesto i u nekim drugim scenarijima, ono što je važno je da sada znate za ovu tehniku i sigurni smo da će te već prepoznati situacije gde je možete primeniti.
Dejan je preko dve decenije proveo radeći u nekoliko domaćih i tri multinacionalne kompanije. Većinu ovog vremena je bio upućen na napredno korišćenje Excel-a a poslednjih 5 godina radio je kao profesionalni analitičar na poslovima Analitičara prodaje i Biznis analitičara. Poseduje bogato poslovno iskustvo i veliko tehničko znanje koje pokriva širok spektar - Excel, VBA, Tableau, DAX-PBI.
Dejan je preko dve decenije proveo radeći u nekoliko domaćih i tri multinacionalne kompanije. Većinu ovog vremena je bio upućen na napredno korišćenje Excel-a a poslednjih 5 godina radio je kao profesionalni analitičar na poslovima Analitičara prodaje i Biznis analitičara. Poseduje bogato poslovno iskustvo i veliko tehničko znanje koje pokriva širok spektar - Excel, VBA, Tableau, DAX-PBI.