TRIKOVI U EXCELU #204: Ko to tamo PowerPivotira? Ili: Gde su stvarne granice PowerPivota?

TRIKOVI U EXCELU #204: Ko to tamo PowerPivotira? Ili: Gde su stvarne granice PowerPivota?

Ko to tamo power pivotira

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:

  • Kažem vam, importovao sam preko 100 miliona redova u Excel 2010 fajl.

4Maajtemolimvas

  • 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?

5Tataneverujemi

  • Pa vi meni ne vjerujete! Hajde da se kladimo. Importovaću 100 miliona redova iz baze podataka firme sa mreže u Excel.

1Jesamvoziomilione

  • Gledajte sada…

 

2negledakudvozi

  • Pa on nije normalan, pašće nam mreža, serveri, baza podataka…  sve!

U međuvremenu je PowerPivot počeo da importuje podatke iz baze brzinom od oko 70 hiljada redova u sekundi.

Bandera

Kolege su gledale Excel kao da ga prvi put vide. A onda…

8pazimilionredova

  • Pazi, milion redova!

 

3mojmiskojegenije

  • Saaaamo bez panike. Moj PowerPivot je genije za ove stvari.

I Excel je nastavio da importuje podatke: dva miliona , pet, deset, dvadeset miliona…

Nakon 30 miliona rekli smo zapanjenim kolegama da navrate malo kasnije kada se import završi. Sto miliona, dvije stotine…  Zaustavio se na 319.068.202 reda.

320miliona

  • 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.

I baš kada su počeli da se vraćaju svojim poslovima komentarišući ono što su upravo vidjeli, dobacio sam, onako, nonšalantno:

7samobezpanike

  • .. 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!

10mastapricate

  • Bez VLOOKUP-a?! Sto miliona redova?! Pa šta pričate?

11cutibrebudalo

  • Ćuti, budalo! Hoćeš li da nam obori kompletan sistem firme?

12neverujetemi

  • Šta, ne vjerujete mi?
  • Vjerujemo, vjerujemo…
  • Čak i ja vjerujem.
  • E, sad ćete da vidite.

6jelvidisista

  • 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…

U tabeli iz primjera nazivi artikala su na engleskom a za izvještaj nam je trebao prevod. U tu svrhu smo napravili drugu tabelu sa prevodima artikala.

Prevod

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.

Relacija

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.

PP300

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.

pivotizvisetabela

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).

Mjera

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.

MjeraUpivotu

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.

13necesproci

  • 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.

Tvrdoglav po prirodi, pokušavao sam da prođem bez pražnjenja TEMP foldera jer mi je to išlo na živce. Rezultat je uvijek bio isti:

14busihahaha

  • Nema dovoljno memorije, hahaha. Nema dovoljno memorije, hahaha. Nema dovoljno memorije, hahaha.

I tako…

15dajtepare

  • Hajde, briši sve iz TEMP foldera, sve!

Nakon pražnjenja TEMP foldera sve je bilo u redu.

I tako… Kada danas vide PowerPivot na djelu, ostale kolege govore svojim šefovima:

16cuti

  • Tata, hoću i ja onako.

A šefovi po pravilu izgovaraju antologijsku rečenicu:

17itatabisine

  • I tata bi, sine.

P. S.

Inače sam strastveni kockar, tj. volim da se kladim. A najviše volim da se kladim sam sa sobom jer tako pobjeđujem u svakom slučaju. U međuvremenu smo se ja i ja opkladili da će PowerPivot importovati milijardu redova. Opkladu sam dobio. :)

Na stranici www.excelbezbola.com, u gornjem desnom uglu je link za preuzimanje dotičnog fajla sa nešto više od milijardu redova. Ne brinite:  vjerovali ili ne, veličina fajla je samo 1,5 MB.

Autor

Almir A.  Budalica

Microsoft Certified Trainer

http://www.excelbezbola.com

+387-61-509-288 (Viber)


1 Komentar

  1. Emir :
    16.11.2018 u 12:02|Permalink

    Potvrđujem da gore navedeno uistinu funkcioniše kroz PowerPivot tako brrrrzo, a i kroz druga rješenja tako sporooooooooooooooooooooooooooooooooooo :)

Napiši komentar

Newsletter pretplata

Autori Tagovi
  • Aleksandar Duković (4)
  • Aleksandar Nedeljković (1)
  • Aleksandar Nešić (1)
  • Aleksandar Pejčić (1)
  • Aleksandar Predić (2)
  • Aleksandar Teofilović (3)
  • Aleksandra Dutina (2)
  • Aleksandra Janković (1)
  • Aleksandra Paunić (1)
  • Aleksandra Đorđević (2)
  • Almir A. Budalica (19)
  • Ana Petonjić (1)
  • Ana Petrović (1)
  • Andreas Graf (1)
  • Andrej Beslać (1)
  • Andrej Kuku (1)
  • Andrija Pavlović (2)
  • Anja Atanasijević (1)
  • Anja Dadasović (1)
  • Biljana Dimitrijević (1)
  • Biljana Dukovic (1)
  • Biljana Masić (6)
  • Bjarte Bogsnes (1)
  • Bogdan Kravić (1)
  • Bojan Grahovac (1)
  • Bojan Pavlović (1)
  • Bojan Radoš (2)
  • Bojan Radun (1)
  • Bojan Šćepanović (304)
  • Bojana Pejčić (26)
  • Bojana Radović (1)
  • Bojana Vesić Anić (1)
  • Bojana Vesić Antić (1)
  • Branislav Lončar (3)
  • Branislav Vujović (1)
  • Branislav Zobenica (1)
  • Cmilja Tucaković (1)
  • Dalibor Pajić (1)
  • Daniela Laketic (31)
  • Danijela Medić (1)
  • Danijela Popović (1)
  • Danijela Resimić (1)
  • Danijela Tuco (1)
  • Darko Bjelić (2)
  • Darko Samardžija (1)
  • Darko Vlajkovic (27)
  • David Momčilović (1)
  • Davor Sakač (1)
  • Dejan Marković (1)
  • Dejan Đorđević (1)
  • Dejana Manić Kavgić (2)
  • Denis Trbović (3)
  • Desa Ćuk (1)
  • Dimitrije Stojanović (1)
  • Vojislav Marjanović (3)
  • Dragan Radosavljević (2)
  • Dragan Ranisavljević (1)
  • Dragan Vještica (3)
  • Dragana Gondžo (1)
  • Dragana Guzina (1)
  • Dragana Lukić (1)
  • Dragana Nerandžić (1)
  • Dragana Ovčarov (1)
  • Dragana Stojanović (2)
  • Dragana Đorđević (1)
  • Draško Marković (1)
  • Dražan Planinić (1)
  • Dušan Krejaković (1)
  • Excel genije! (151)
  • Fabrizio Renzi (1)
  • Goran Petrović (1)
  • Goran Popović (4)
  • Goran Slijepčević (1)
  • Goran Vasić (1)
  • Goran Vujasinović (1)
  • Gorana Golubovic Vuksanovic (1)
  • Goranka Radojčić (2)
  • Gordana Jankov (1)
  • Gostujuci autor (514)
  • Ilija Vujović (1)
  • Irina Zdravković (2)
  • Istok Pavlović (1)
  • Ivan Anđelković (1)
  • Ivan Bešker (1)
  • Ivan Ćirković (1)
  • Ivan Dačković (1)
  • Ivan Kostadinović (1)
  • Ivan Veselinović (3)
  • Ivan Đurić (1)
  • Ivana Dagović (1)
  • Ivana Jevtić (1)
  • Ivana Milić (1)
  • Ivana Popović (1)
  • Ivana Savović (1)
  • Ivana Višnjić (3)
  • Ivana Vlahović (1)
  • Jasenka Stekić (1)
  • Jelena Jovanović (1)
  • Jelena Lečić Mirčetić (1)
  • Jelena Radovanac (1)
  • Jelena Ristić (1)
  • Jelena Stojmenović (1)
  • Jelena Vučković (1)
  • Jovan Gligorijević (1)
  • Jovan Krstić (1)
  • Jovana Kondić (2)
  • Jovana Stanojčić (1)
  • Jovo Stokić (2)
  • Katarina Ćevriz (2)
  • Kristina Bojović (1)
  • Ksenija Karić (1)
  • Lazar Džamić (1)
  • Leo Pandžić (2)
  • Lidija Latinović (1)
  • Ljubomir Skupek (1)
  • Lucija Vesić (1)
  • Maja Mrkalj (1)
  • Maja Nikčević Rmandić (1)
  • Maja Petrović (1)
  • Maja Šobot (1)
  • Maja Verlašević (1)
  • Maja Zikic (1)
  • Mari Glavonjić (2)
  • Marija Đurović (1)
  • Marija Kostić (1)
  • Marija Rokvić (1)
  • Marija Švigir (32)
  • Marija Tatarević (3)
  • Marijana Krička (1)
  • Marina Mitić Jekić (1)
  • Mariora Andraš Tomić (1)
  • Marko Agatonović (2)
  • Marko Bekić (1)
  • Marko Devrnja (1)
  • Matjaž Fajfar (2)
  • Milan Listeš (2)
  • Milan Maglov (1)
  • Milan Manić (1)
  • Milan Marković (1)
  • Milan Milenković (1)
  • Milan Radivojevic (1)
  • Milan Simić (1)
  • Milan Zaletel (1)
  • Milan Đorđević (1)
  • Mile Mitrović (47)
  • Milena Janjić (3)
  • Milena Nikolov (1)
  • Milena Radosavljević Đorić (1)
  • Milena Rajić (1)
  • Milenko Dželetović (1)
  • Milica Altgelt (1)
  • Milica Ivic (1)
  • Milica Đorđević (1)
  • Miloš Cvetković (7)
  • Miloš Jauković (1)
  • Miloš Jeličić (2)
  • Miloš Nedeljković (1)
  • Miloš Pucarević (1)
  • Miloš Puzić (1)
  • Miloš Simić (1)
  • Miloš Vasić (1)
  • Miloš Zeković (1)
  • Miona Živkov-Ivanišević (4)
  • Mirjana Pašalić (2)
  • Jelena Jovanović (5)
  • Mladen Ranković (1)
  • Natalija Jegdić (1)
  • Natalija Mihajlović (1)
  • Natalija Pešić (2)
  • Natalija Popovic (1)
  • Nataša Kuzmanovski (1)
  • Nataša Stamenković (2)
  • Nataša Žikić Buha (1)
  • Nebojša Vlatković (1)
  • Neda Čičarević Tepić (1)
  • Neda Jovanović (1)
  • Nemanja Knežević (1)
  • Nemanja Nikolić (3)
  • Nenad Dlačić (1)
  • Nenad Kovačević (1)
  • Nenad Radunović (1)
  • Nenad Trajkovski (1)
  • Nevena Radovanović (1)
  • Nevena Vračar (1)
  • Nikola Avram (1)
  • Nikola Cvijanović (1)
  • Nikola Petreski (1)
  • Nikola Stokić (2)
  • Nikola Turkan (1)
  • Nikša Vušurović (1)
  • Olga Mirković (1)
  • Olivera Dostanić (1)
  • Olivera Krneta (1)
  • Olivera Nikodijević (1)
  • Petar Ćurčić (1)
  • Petar Popović (1)
  • Petar Živković (3)
  • Predrag Krasojevic (7)
  • Predrag Kudra (1)
  • Predrag Micić (1)
  • Predrag Petrović (5)
  • Rade Hajder (1)
  • Rade Radanović (2)
  • Rade Stojisavljević (1)
  • Radomir Petronijević (1)
  • Rajko Vasojević (1)
  • Roland Seeliger (1)
  • Sandra Rapo (1)
  • Sanja Jevđenijević (1)
  • Sanja Mrđanov (2)
  • Saša Stamenković (1)
  • Silvia Tomić (1)
  • Slavica Vučetić (1)
  • Slavica Vujičić (1)
  • Slaviša Lečić (2)
  • Slavko Vujnovic (7)
  • Slobodan Anić (2)
  • Slobodan Radoičić (1)
  • Slobodan Roksandić (1)
  • Slobodan Žepinić (1)
  • Snežana Glavonjić (1)
  • Snežana Trajkovski (1)
  • Snježana Pivaš (1)
  • Sonja Nenić Andreev (1)
  • Srdjan Grbić (1)
  • Srđan Grubić (1)
  • Srđan Pavlović (1)
  • Stefanija Georgieva (8)
  • Stevan Ćomić (1)
  • Svetlana Mirković Borčić (1)
  • Svetlana Pajić (1)
  • Svetlana Žikić (2)
  • Tamara Dragašević (1)
  • Tamara Lazić (3)
  • Tamara Matović (2)
  • Tatjana Bolpačić (22)
  • Tatjana Jovanović (1)
  • Tatjana Lukić (2)
  • Tijana Anđelić (1)
  • Tijana Drljević (1)
  • Tijana Rauš (1)
  • Tomislav Mimica (1)
  • Tomo Djekovic (1)
  • Vedran Babik (1)
  • Vedrana Božić (2)
  • Vedrana Vukša (1)
  • Velibor Ilić (1)
  • Veljko Stanojković (1)
  • Veljko Žarić (1)
  • Veroljub Zmijanac (1)
  • Vesna Stojanvić (1)
  • Violeta Kovačević (1)
  • Vladan Buha (1)
  • Vladan Matović (1)
  • Vladimir Petković (2)
  • Vladimir Popović (1)
  • Vladimir Stojković (1)
  • Yeghishe Avagyan (1)
  • Žaklina Teofilović (2)
  • Žarko Milovanović (1)
  • Žarko Savić (1)
  • Željko Ćulibrk (1)
  • Željko Vidojević (1)
  • Živka Mutlak (1)
  • Živorad Radovanović (1)
  • Zoran Blagojević (1)
  • Zoran Daljević (1)
  • Zorica Dinić (1)
  • Zorica Popović (1)
  • Đorđe Koprivica (2)
  • Đorđe Milinković (1)
  • Đorđe Živanović (1)
  • Najnoviji Broj Controlling Magazina

    Controlling magazin #12

    Tema:
    "SUPPLY CHAIN"

    Autori:
    Preko 60 autora: Actavis, Elektromreža Srbije, BASF, Uniqa osiguranje...

    Preuzmi elektronsko izdanje