TRIKOVI U EXCELU #205: Šta se dobije kada Dekart čita ruski roman u PowerPivotu?

Upozorenje: ovaj tekst objašnjava kako za nekoliko minuta kreirati tekst od 10.000 ili više rečenica „šuplje priče“. Ne pokušavajte ovo kod kuće!

Jedne prilike Odjel Marketinga nije bio siguran kako da pripremi prigodan govor koji je Direktor trebalo da održi.

“Svečana garnitura”

Tada sam se sjetio „Svečane garniture“ iz romana „Zlatno tele“ Iljfa i Petrova. Zato sam razmišljao kako da napravim nešto slično te da proširim listu opcija.

Slika 1. Prve dvije liste opcija

Inače, „Svečana garnitura“ je osmišljena kao osnova za kreiranje govora, pisanje pamfleta, brošura, radio-emisija itd. Sastoji se od 4 dijela rečenice (uvoda, početka, razrade i zaključka). Kreirane su 4 liste. Pojmovi u listama su tako uopšteni da se kombinovanjem bilo kojeg izraza iz svake od 4 liste dobija savršeno smislena rečenica. Naravno, iako savršeno smislena, rečenica ne govori ni o čemu konkretnom.

Slika 2. Druge dvije liste opcija

Na primjer: „Kao što vjerovatno znate, sadašnja struktura organizacije započinje proces koji ima za cilj usavršavanje sustava obrazovanja kadrova koji je prilagođen potrebama“, ili: „Na taj način, novi model aktivnosti omogućava nam bolju procjenu stavova u skladu sa zadacima koje je utvrdila organizacija.“ itd.

Do tada je išlo glatko: iskoristio sam pojmove iz „Zlatnog teleta“ tako što sam neke izbacio, druge malo izmjenio a treće dodao.

Zašto ovo spominjem? Zato što mi je sada potreban Excel. Napravio sam 4 padajuće liste gdje je svaka sadržavala opcije za jedan dio rečenice (Uvod, početak, razrada i zaključak).

Padajuće liste

U odvojenom redu sam napravio rezultat (rečenicu) koji je bio spoj odabranih opcija iz padajućih listi.

Slika 3. Generisanje rečenice na osnovu odabranih opcija iz 4 liste

Za više detalja o kreiranju padajućih listi pogledajte tekst ovdje.

Promjenom odabira u padajućim listama mjenjao se rezultat, odnosno rečenica.

Ipak, ovo nije bilo dovoljno dobro zato što je rezultat bio samo jedna rečenica a bio mi je potreban čitav govor.

Dakle, trebalo je da kreiram listu svih mogućih kombinacija 4 liste. Ukoliko je svaka od 4 liste imala 10 opcija, trebalo je da kreiram 10⁴ (10 x 10 x 10 x 10) ili 10.000 rečenica.

Ni ovo nije predstavljalo problem: jednostavnim kopiranjem sam za nekoliko minuta napravio 10.000 kombinacija spremnih za upotrebu.

Kakve veze ima Rene Dekart?

Ali, ne lezi vraže! Šta ako korisnik „garniture“ želi da proširi liste? Morao bi svaki put da ažurira padajuće liste pa da iznova kreira sve moguće kombinacije. Na primjer, kada sam proširio 4 liste na po 14 članova, dobio sam 14⁴ (14 x 14 x 14 x 14) ili 38.416 kombinacija. Ipak, bilo je potrebno ponovo ručno praviti sve kombinacije, dakle nepraktično, naročito ukoliko uzmete u obzir Excel vještine prosječnog marketing stručnjaka.

Inače, kada svaki elemenat jednog skupa uparujete sa svakim elementom drugog skupa, skup takvih parova se naziva Dekartov proizvod. Više o Dekartovom proizvodu možete pronaći ovdje.

Dakle, bilo mi je potrebno rješenje koje će automatski generisati sve kombinacije nakon što se liste ažuriraju. Drugim rješima, bio mi je potreban automatizovan Dekartov proizvod za 4 liste.

I treći igrač ulazi u igru: PowerPivot i relacioni model bez relacija

E, ovdje u igru ulazi PowerPivot. Zašto? Zato što u sebi sadrži relacioni model baza podataka. Kada 4 tabele koristite unutar relacionog modela, vrlo je jednostavno kreirati Dekartov proizvod od elemenata koji čine te tabele.

Trik je u tome da se relacija između tabela uopšte ne kreira. Čudno? Relacioni model bez relacija? Paaaaa, u jednu ruku jeste, ali u drugu nije. Otprilike kao kad majka pita sina narkomana da li se drogira a on odgovara: – U jednu ruku da a u drugu ruku ne.

Slika 4. Relacioni model u PowerPivotu (bez relacija)

Kada u upitu u PowerPivotu odaberete elemente iz sve 4 tabele, relacioni model će u nedostatku relacije između tabela napraviti sve kombinacije, tj. Dekartov proizvod. A to je upravo ono što nam treba.

Dodavanje tabele u “Data Model”

Posljednja stvar koja nam je potrebna je da se tabele u relacionom modelu PowerPivota automatski ažuriraju kada dođe do izmjena u Excel prozoru, tj. kada se stavke u 4 liste ažuriraju u prozoru Excela. Ipak ne treba očekivati da marketing stručnjaci otvaraju prozor PowerPivota.

Ovo smo postigli tako što smo svaku tabelu dodali u „Data Model“ („Add to Data Model“). Na taj način je postignuto da kada se lista promjeni u Excel prozoru, ista se ažurira i u PowerPivot modelu u pozadini.

Slika 5. Dodavanje tabele u “Data model” PowerPivota

Dekart na djelu

Finalni korak je kreiranje rezultata: u prozoru PowerPivota odaberite kreiranje pivot tabele. U Excel prozoru u pivot tabelu dodajte po jednu kolonu iz svake od 4 tabele. Usljed nedostatka relacije između tabela, kreiraće se sve kombinacije.

Slika 6. Kreiranje pivot tabele sa svim kombinacijama

Na kraju, kada se ažuriraju liste, dovoljno je osvježiti pivot tabelu. Kako je to moguće?

Kada se liste ažuriraju, one se ažuriraju i u PowerPivot modelu jer su dodane u „Data Model“. Ažuriranje pivot tabele iznova kreira Dekartov proizvod i vraća sve kombinacije.

Dekart kao Del boy: navali narode, tri rečenice za evro

Nakon toga možete prodavati govore „na kilo“. Na primjer, pomoću funkcije =RANDOM() možete nasumično „izvući“ rečenicu. Kopirajte ovu formulu u nekoliko ćelija i pritiskajte tipku F9 onoliko puta koliko je potrebno dok ne dobijete željenu kombinaciju rečenica.

Slika 7. Prodavanje govora “na kilo”

P. S.

Ah, da… Vjerovatno ste primjetili da kada sam kreirao pivot tabelu, kreirao sam je od kolona iz više tabela. Ovo je moguće samo kada se koristi PowerPivot. Ali o tome više u ovom tekstu. Samo ću reći da VLOOKUP a i INDEX/MATCH možete da bacite u smeće jer vam više neće trebati a pojavljuje se i Miško za volanom autobusa.