Excel i Power BI za profesionalce

Excel je kao što mnogi znaju prije svega namijenjen raznoraznim izračunima ili kalkulacijama (tablični kalkulator) i formule su suština Excela. Bez njih to ne bi bilo to.

Kao što sam već napisao, u novije vrijeme on prerasta okvire gore spomenute kategorije aplikacija i postaje moćan “Self service BI” alat, no bez poznavanja pisanja formula rad s Excelom jednostavno nije potpun i nema baš previše smisla. Postoje naravno situacije u kojima će Pivotica riješiti neku brzinsku analizu, ali i u tom slučaju za zahtjevnije zadatke neke pomoćne formule su itekako dobrodošle (npr. uz GETPIVOTDATA funkciju).

Svaka formula počinje znakom “=“, a iza može sadržavati konstante (brojeve, tekst ili logičke vrijednosti), reference na druge ćelije, reference na pojedina polja tabela (Tables i structured table references), imena (Defined formulas), funkcije i dr.

Primjeri nekih jednostavnih formula:

=5+10*4-8

=A1+A2

=SUM(Brojevi)*(1+PDV) –> (imenovani rasponi i konstante) – “defined formulas” ili “defined names”

=[Količina]*[Cijena] –> formule u tabelama (Tables) – “structured table references”

Formule se obično rekalkuliraju automatski, osim ako su postavke u Excelu postavljene na manualnu kalkulaciju.

Mnogi toga možda nisu svjesni, pa evo da pojasnimo stvar.

Nedavno me prijatelj pitao kako to da njemu formula koju je kopirao prema dolje daje u svim ćelijama jednake rezultate. Naravno, brojke u ćelijama na koje se odnosila ta formula nisu bile jednake i rezultati su se trebali razlikovati od ćelije do ćelije.

Što se dogodilo u tom slučaju?

Postavke za kalkulaciju u Excelu mogu biti postavljene na automatsku ili manualnu kalkulaciju, kao što se može vidjeti na slici ispod.

Automatska kalkulacija podrazumijeva rekalkulaciju formula pri promjeni parametara s kojima formula računa (u ovom slučaju različiti brojevi u ćelijama).

Svaki put kada se broj u ćeliji na koju se odnosi formula promijeni, rezultat koji daje formula će se osvježiti.

Kod manualne kalkulacije to nije slučaj, pa će upisana formula koju smo kopirali i u drugim ćelijama davati jednak rezultat kao i u prvoj ćeliji u koju smo je upisali.

Ako želimo osvježiti rezultate svih formula, potrebno je pritisnuti F9 ili uključiti automatsku kalkulaciju u postavkama (File, Options, Formulas, Calculation options)

Eto, ukratko o postavkama vezanim uz kalkulaciju formula, tako da se ne čudite što je s vašim Excelom ako vam se nešto slično dogodi. To je vjerojatno prva stvar koju trebate provjeriti.

Inače, manualnu kalkulaciju treba izbjegavati, jer postoji opasnost da se model prije npr. ispisa ili pregleda rezultata zaboravi osvježiti, pa to može dovesti do ozbiljnih problema i pogrešnih zaključaka.

U iznimnim situacijama, kada je odziv (rekalkulacija formula) modela vrlo spor, možete uključiti manualnu kalkulaciju, raditi na modelu i na kraju svakako rekalkulirati model!

Razloga za sporu kalkulaciju može biti više, najčešće je to kompleksnost modela s velikim brojem formula (tisuće ili stotine tisuća), volatilnim funkcijama i dr.

Prosječan korisnik koji ne radi s velikim brojem podataka i ne koristi npr. VLOOKUP funkcije nad tisućama redaka vjerojatno to neće doživjeti, ali to je prilično uobičajena situacija u praksi.

Jednom prilikom sam vidio primjer u kojem je kolegica radila na jednom modelu koji čak i nije bio toliko kompleksan, ali je bio povezan (linkan) na nekoliko Excel datoteka od kojih su neke bile dislocirane, čak i u drugoj državi u sklopu korporativne mreže. Naravno, takav model je pri svakom upisu podatka u ćeliju trokirao i trebalo je proći nekoliko sekundi da bi se moglo nastaviti raditi.

U takvoj situaciji sam savjetovao kolegici da isključi automatsku rekalkulaciju i da svakako ima to na umu prije ispisa ili spremanja modela na kraju dana.

Postoje i situacije kada model jednostavno nije optimiziran po pitanju kalkulacije formula, pa će nešto trajati i trajati dok se rekalkulira, dok će uz određene zahvate, model biti sasvim ugodan za rad.

Nedavno sam radio jedan vrlo kompleksan kalkulacijski model u kojem je trebalo napisati i VBA funkcije i koristiti ih na više mjesta, a radilo se o prilično velikom broju podataka (tabele od 15-ak tisuća redaka i do 100-tinjak stupaca). Kalkulacija je trajala neko vrijeme, tako da smo se odlučili za pristup koji sam malo prije spomenuo, isključivanje automatske kalkulacije, unos ulaznih parametara u model te manualnu rekalkulaciju na kraju. Postojala su i neka druga ograničenja zašto se išlo u tom smjeru, ali to sada nije toliko bitno.

Ako trebate isključiti automatsku rekalkulaciju modela da biste mogli normalno raditi s Excelom, to je često pokazatelj da model nije dobro napravljen i potrebno ga je restrukturirati i optimizirati.

Manualna rekalkulacija radnog lista ili radne bilježnice – neki prečaci tipkovnicom

Kao što postoji u Excelu na korisničkoj strani, tako i u VBA postoji objektni model koji podržava različite postavke kalkulacije kao i rekalkulaciju na zahtjev.

Application.Calculation = xlCalculationAutomatic

Application.Calculation = xlCalculationManual

Application.Calculate

Pri izradi VBA aplikacija često se kalkulacija stavlja na manualnu prije pokretanja obrade podataka kako se procesor ne bi opterećivao brojnim rekalkulacijama tijekom zapisivanja podataka u ćelije, a nakon obrade se forsira rekalkulacija. To vrlo često znatno ubrzava izvršavanje programskog koda pri radu s velikom količinom podataka i velikim brojem formula. Naravno, postoje i neke druge tehnike kako se podaci mogu obraditi u memoriji, a samo se rezultati na kraju ispisuju u ćelije radnog lista.

Pri radu s Pivoticom (Pivot table) nemojte zaboraviti osvježiti rezultat pri promjeni podataka na kojima se ona temelji. Refresh!

I za kraj, kao što u naslovu i piše, ako radite s Wordom i poljima (Fields), npr. računate s formulama u tabelama, tamo ne postoji automatska rekalkulacija, potrebno je to napraviti s F9!