Slučajno generisani podaci

Koju zajedničku muku često dele programeri i „izveštajci“ (analitičari, zaduženi za pravljenje izveštaja)? Sasvim sigurno, radi se o nedostatku odgovarajućih podataka, koji su im potrebni da bi pokrenuli prikaz izveštaja i videli da li on radi. Podatke možemo uneti i ručno. Ali hej: zamislite sebe da ručno unosite 100.000 zapisa !?! U ovom tekstu biće demonstrirana jednostavna tehnika za pravljenje tabela sa podacima za testiranje i najzahtevnijih izveštaja.

U primeru koji sledi cilj nam je da napravimo Pivot tabelu koja će analizirati podatke o prodaji za 100.000 zapisa koji predstavljaju prodajne transakcije, nastale u periodu od 01.01.2015 do 31.12.2015. Transakcije će biti slučajno generisane na osnovu tabele sa artiklima, koju ćemo uneti kao zaseban radni list (Artikli), a koje sam preuzeo sa Internet prezentacije „Mačkovog podruma“ (dobro urađena prezentacija i još bolja vina). Tabela se sastoji iz tri kolone: ŠIFRA, NAZIV ARTIKLA i CENA, a u njima su upisane pripadajuće šifre i artikli sa cenama. Glavnu tabelu (Transakcije) smestićemo u radni list Transakcije, gde ćemo slučajnim putem da generišemo količine i datume. Da počnemo!

Najpre ćemo u radnom listu Transakcije da popunimo zaglavlje tabele vrednostima: RBR, ŠIFRA, NAZIV ARTIKLA, KOLIČINA, CENA i IZNOS. Zatim, treba definisati redne brojeve za 100.000 transakcija. To ćemo da uradimo tako što kliknemo na ćeliju ispod oznake RBR, u ovu ćeliju unesemo vrednost 1, a zatim odaberemo sva polja u zadatoj koloni pritiskom kombinacije tastera CTRL+SHIFT+Strelica dole. Na Home traci alata dalje treba da otvorimo meni Fill, a zatim da odaberemo opciju Series. U dijalog prozoru Fill Series vrednost Step Value treba da bude 1, a Stop Value 100.000. Potvrđivanjem izbora smo eksplicitno zadali komandu Excel-u da u selektovanoj oblasti popuni redne brojeve za 100.000 transakcija.

Zatim treba da generišemo artikle. Kliknućemo na prvu ćeliju ispod naziva ŠIFRA, a dalje ćemo da unesemo formulu:

=RANDBETWEEN(1,9)

Ova formula na slučajan način generiše šifru artikla, kao broj između 1 i 9. Ukoliko se pozicioniramo iznad simbola kvadratića, u donjem desnom uglu ćelije, i izvršimo dvostruki klik formula će biti automatski iskopirana u sve preostale vrednosti ove kolone, odnosno za svih 100.000 artikala. Pošto treba da na slučajan način generišemo i količine to ćemo da uradimo tako što ćemo da unesemo sličnu formulu ispod naziva KOLIČINA i ponovimo postupak kopiranja. Formula koju ćemo da unesemo glasi:

=RANDBETWEEN(1,10)*6

Ovom formulom slučajno biramo broj između 1 i 10 i množimo ga sa brojm 6 (broj vina u kutiji). Ovim kažemo da je naš imaginarni kupac kupio između 1 i 10 kutija određenog vina.

Podaci koje smo generisali pomoću funkcije RANDBETWEEN su promenljive prirode. Svaki put kada ručno ili automatski pokrenemo proces kalkulacije podaci će se promeniti. Pošto želimo da jedanput generisane vrednosti ostanu iste, formule treba da pretvorimo u vrednosti. To činimo tako što najpre selektujemo celu kolonu, pokrenemo kopiranje pomoću prečice CTRL+C, a zatim u Home traci alata, iz menija Paste odaberemo opciju Paste Values. Ovaj postupak treba ponoviti za kolone ŠIFRA i KOLIČINA.

Kada imamo šifre artikala, lepo bi bilo da iz tabele artikala preuzmemo njihove nazive i cene. Iskoristićemo funkciju VLOOKUP, pomoću koje kreiramo dve formule:

=VLOOKUP(B2,Artikli!$A$1:$C$10,2)

=VLOOKUP(B2,Artikli!$A$1:$C$10,3)

Prvi argument funkcije je u oba slučaja ćelija iz kolone ŠIFRA (B2), drugi argument je fiksirana adresa tabele artikala Artikli!$A$1:$C$10), a treći je indeks kolone iz koje preuzimamo podatke. U prvoj formuli to je kolona sa nazivima artikala (2), a u drugoj je reč o koloni sa cenama (3). Funkcija VLOOKUP ima i četvrti opcioni parametar koji je ovom prilikom izostavljen jer je tabela artikala uređena po šifri artikla, pa za njim nema potrebe. Nakon što unesemo formule bilo bi dobro da cene formatiramo kao decimalan broj sa dve decimale, što ćemo najbrže da uradimo pomoću prečice CTRL+SHIFT+1. Na kraju, formule treba da prekopiramo u preostale ćelije u kolonama NAZIV ARTIKLA i CENA.

Poslednji korak je kreiranje formule za izračunavanje iznosa. Ispod vrednosti IZNOS unosimo formulu:

=D2*E2

Zatim, formulu formatiramo kao decimalan broj sa dve decimale (CTRL+SHIFT+1) i iskopiramo je u preostale ćelije kolone IZNOS.

Na kraju opisanog postupka, za ovu jednostavnu analizu, dobili smo tabelu sa slučajno generisanim podacima za 100.000 transakcija.