TRIKOVI U EXCELU #204: Ko to tamo PowerPivotira? Ili: Gde su stvarne granice PowerPivota?
Gledam nedavno po 389. put „Ko to tamo peva“ i scenu kada Miško vozi vezanih očiju. To me podsjetilo na situaciju kada sam sa kolegom Emirom četvrti ili peti put u firmi tražio prelazak na Excel verzije 2010 jer podržava PowerPivot. Ranije su nas svaki put odbili zbog „nedostatka licenci“, čitaj: novca a najviše zbog nemogućnosti da povjeruju da Excel može da skladišti i obradi toliku količinu podataka.
Razgovor je išao otprilike ovako:
Kolege su gledale Excel kao da ga prvi put vide. A onda...
Inače bi se ovo radilo pomoću VLOOKUP-a ili kombinacije INDEX/MATCH. Ali na 320 miliona redova...
U PowerPivot smo importovali tabelu sa prevodom a zatim tabele relaciono povezali tako da naziv na engleskom u velikoj tabeli odgovara jednom redu u drugoj tabeli koji je u drugoj koloni sadržavao prevod. Ko je ikada pravio bazu podataka bar u Accessu, zna o čemu govorim.
Na ovaj način nije bilo potrebe da se u velikoj tabeli pravi pomoćna kolona u koju bi se donijele vrijednosti iz tabele sa prevodima. Doduše, isprobali smo u tu svrhu funkciju RELATED (ekvivalent VLOOKUP-a i INDEX/MATCH-a). Rezultate za 319 miliona redova smo dobili za nešto više od minut i pol.
Brzo? Ne! Za mjerila PowerPivota to je jaaaako sporo. Mnogo bolji pristup je pravljenje pivot tabele od kolona iz više relaciono povezanih tabela.
Zahvaljujući uspostavljenoj relaciji koja Excelu „objašnjava“ da je veza između dvije tabele preko zajedničke kolone (baš kao kod VLOOKUP-a) pivot tabela nastala iz PowerPivota je u stanju da koristi kolone iz više tabela. Ovo je još jedna velika prednost PowerPivota u odnosu na klasične pivot tabele.
U ovom primjeru kreirana je tabela sa brojčanim pokazateljima iz velike tabele i oznakama proizvoda iz tabele sa prevodima. Tačnost je osigurana kreiranjem relacije između tabela.
Najvažnije od svega je da su uparivanje i kalkulacija izvršeni momentalno.
Također, izvedene brojčane vrijednosti je mnogo bolje kreirati kao tzv. mjere u pivot tabeli nego kao kalkulisane kolone u prozoru PowerPivota. Razlog je u veličini fajla i naročito u brzini kalkulacije.
Ukoliko bismo u gornjem primjeru pokušali dobiti prosječnu (tačnije, ponderisanu) cijenu proizvoda prostim djeljenjem prihoda sa količinom, sporiji način bi bio da se u prozoru PowerPivota kreira kalkulisana kolona na 320 miliona redova, što bi trajalo oko jedne minute.
Mnogo efikasniji način po pitanju brzine i veličine fajla je kreiranje mjera u pivot tabeli. Mjere se kalkulišu momentalno jer se izvršavaju samo na podacima koji se prikazuju u pivot tabeli a ne na svim podacima.
Da biste kreirali mjeru, na traci PowerPivota odaberite New Measure, dajte joj ime i unesite formulu (u ovom slučaju količnik ukupnog broja prodanih komada i ukupnog prihoda).
Nakon toga dodajte mjeru kao i svaku drugu kolonu u pivot tabelu. Et voilà! Završeno bez VLOOUP-a, bez INDEX/MATCH-a i, što je najvažnije, u tren oka.
Inače, da ne bih kvario tok priče, nisam odmah napomenuo da je jako dobro prije osvježavanja podataka u PowerPivotu isprazniti TEMP folder kako bi PowerPivot imao što više prostora.
Lokacija TEMP foldera je: %USERPROFILE%\AppData\Local\Temp.
Kada sam prvi put pokušao ovu akrobatiku sa stotinama miliona redova, dobio sam grešku jer PowerPivot nije imao dovoljno memorijskog prostora.
- Kažem vam, importovao sam preko 100 miliona redova u Excel 2010 fajl.
- Ma, daj, pa znaš da Excel ima ograničenje od milion redova po radnom listu, šta pričaš? Opet nisi uzeo lijekove jutros, je li tako?
- Pa vi meni ne vjerujete! Hajde da se kladimo. Importovaću 100 miliona redova iz baze podataka firme sa mreže u Excel.
- Gledajte sada...
- Pa on nije normalan, pašće nam mreža, serveri, baza podataka... sve!
Kolege su gledale Excel kao da ga prvi put vide. A onda...
- Pazi, milion redova!
- Saaaamo bez panike. Moj PowerPivot je genije za ove stvari.
- Jeste li vidjeli?
- Jesmo, u redu, ubjedili ste nas, tražićemo da nam odobre instalaciju nove verzije Excela. A koliko to zadovoljstvo košta?
- Ništa, u pitanju je besplatan dodatak. A verzije od 2013 i novije imaju PowerPivot kao sastavni dio Excela.
- .. Heh, a prije dvije godine PowerPivot je, isto za opkladu, na sto miliona redova upario podatke iz druge tabele za sekundu, i to bez VLOOKUP-a i INDEX/MATCH-a!
- Bez VLOOKUP-a?! Sto miliona redova?! Pa šta pričate?
- Ćuti, budalo! Hoćeš li da nam obori kompletan sistem firme?
- Šta, ne vjerujete mi?
- Vjerujemo, vjerujemo...
- Čak i ja vjerujem.
- E, sad ćete da vidite.
- Jel'ima VLOOKUP-a, a? Jel' vidiš INDEX/MATCH igdje? Vidiš li?...
- Ne vidim, pa šalio sam se.
- Nemoj, Almire, molim te.
- Ti ćeš moj PowerPivot da nazivaš lažovom...
Inače bi se ovo radilo pomoću VLOOKUP-a ili kombinacije INDEX/MATCH. Ali na 320 miliona redova...
U PowerPivot smo importovali tabelu sa prevodom a zatim tabele relaciono povezali tako da naziv na engleskom u velikoj tabeli odgovara jednom redu u drugoj tabeli koji je u drugoj koloni sadržavao prevod. Ko je ikada pravio bazu podataka bar u Accessu, zna o čemu govorim.
Na ovaj način nije bilo potrebe da se u velikoj tabeli pravi pomoćna kolona u koju bi se donijele vrijednosti iz tabele sa prevodima. Doduše, isprobali smo u tu svrhu funkciju RELATED (ekvivalent VLOOKUP-a i INDEX/MATCH-a). Rezultate za 319 miliona redova smo dobili za nešto više od minut i pol.
Brzo? Ne! Za mjerila PowerPivota to je jaaaako sporo. Mnogo bolji pristup je pravljenje pivot tabele od kolona iz više relaciono povezanih tabela.
Zahvaljujući uspostavljenoj relaciji koja Excelu „objašnjava“ da je veza između dvije tabele preko zajedničke kolone (baš kao kod VLOOKUP-a) pivot tabela nastala iz PowerPivota je u stanju da koristi kolone iz više tabela. Ovo je još jedna velika prednost PowerPivota u odnosu na klasične pivot tabele.
U ovom primjeru kreirana je tabela sa brojčanim pokazateljima iz velike tabele i oznakama proizvoda iz tabele sa prevodima. Tačnost je osigurana kreiranjem relacije između tabela.
Najvažnije od svega je da su uparivanje i kalkulacija izvršeni momentalno.
Također, izvedene brojčane vrijednosti je mnogo bolje kreirati kao tzv. mjere u pivot tabeli nego kao kalkulisane kolone u prozoru PowerPivota. Razlog je u veličini fajla i naročito u brzini kalkulacije.
Ukoliko bismo u gornjem primjeru pokušali dobiti prosječnu (tačnije, ponderisanu) cijenu proizvoda prostim djeljenjem prihoda sa količinom, sporiji način bi bio da se u prozoru PowerPivota kreira kalkulisana kolona na 320 miliona redova, što bi trajalo oko jedne minute.
Mnogo efikasniji način po pitanju brzine i veličine fajla je kreiranje mjera u pivot tabeli. Mjere se kalkulišu momentalno jer se izvršavaju samo na podacima koji se prikazuju u pivot tabeli a ne na svim podacima.
Da biste kreirali mjeru, na traci PowerPivota odaberite New Measure, dajte joj ime i unesite formulu (u ovom slučaju količnik ukupnog broja prodanih komada i ukupnog prihoda).
Nakon toga dodajte mjeru kao i svaku drugu kolonu u pivot tabelu. Et voilà! Završeno bez VLOOUP-a, bez INDEX/MATCH-a i, što je najvažnije, u tren oka.
Inače, da ne bih kvario tok priče, nisam odmah napomenuo da je jako dobro prije osvježavanja podataka u PowerPivotu isprazniti TEMP folder kako bi PowerPivot imao što više prostora.
Lokacija TEMP foldera je: %USERPROFILE%\AppData\Local\Temp.
Kada sam prvi put pokušao ovu akrobatiku sa stotinama miliona redova, dobio sam grešku jer PowerPivot nije imao dovoljno memorijskog prostora.
- Nećeš proći dok ne isprazniš TEMP folder. Nećeš proći dok ne isprazniš TEMP folder. Nećeš proći dok ne isprazniš TEMP folder.
- Nema dovoljno memorije, hahaha. Nema dovoljno memorije, hahaha. Nema dovoljno memorije, hahaha.
- Hajde, briši sve iz TEMP foldera, sve!
- Tata, hoću i ja onako.
- I tata bi, sine.