Microsoft Office Excel
uređuje Dragan Grbić

Izračunavanje radnog staža iz više perioda

Nivo:
NIVO 3 - Prilozi na ovom nivou su namenjeni naprednim korisnicima, onima koji se već dobro snalaze u programima i onima koji već imaju iskustva u naprednim tehnikama. Ovom nivou pripadaju preporuke i trikovi koje bi trebalo da savlada svako ko teži dugotrajnom i efikasnom radu u Office programima.

Nakon nedavnog priloga o baratanju datumima u kome smo opisali neočekivanu, ali legitimnu upotrebu nedokumentovane funkcije DATEDIF u Excelu, otvorile su nam se razne mogućnosti za baratanje složenijim strukturama koje inače ne bi bilo lako izvesti bez programiranja...

Javio nam se kolega Miroslav Mrkonjić iz Bijeljine, koji je zamolio za pomoć u rešavanju problema izračunavanja zbira radnog staža u slučaju da postoji više pojedinačnih perioda. Kolega je naglasio da automatiku sabiranja perioda treba izvesti ključem "jedan mesec = 30 dana", što olakšava problem usaglašavanja perioda.

U suštini, problem nije komplikovan ukoliko kao ključ angažujemo u Excelu nedokumentovanu, ali izuzetno efikasnu funkciju DATEDIF; o ovoj funkciji smo goovorili u nedavnom prilogu o baratanju datumima, gde se pokazalo da faktički nema preciznijeg načina da se razlike između datuma izračunaju.

Da biste lakše pratili ovo rešenje, preuzmite datoteku sa primerom. Sve adrese ćelija u ovom prilogu prate iste adrese u radnoj svesci sa primerom.

Kako ćemo ovo rešiti? Najbolje je, u ime jednostavnosti i lakše kontrole, da to učinimo dvofazno. Najpre, svaki pojedinačni period radnog staža ćemo razložiti na tri vrednosti (godine, meseci i dani). Ako se datumi početka i kraja jednog perioda radnog staža nalaze u ćelijama A5 i B5, onda će te vrednosti biti izvedene trima funkcijama:

godine: =DATEDIF(A5;B5;"y")
meseci: =DATEDIF(A5;B5;"ym")
  dani: =IF(A5<>"";DATEDIF(A5;B5;"md")+1;0)

Obratite pažnju na treću formulu: prosto utvrđivanje razlike nije dovoljno - mora se uračunati i dan od kojeg se oduzima, pošto takođe pripada radnom stažu!

Uzgred, ovaj detalj o danu više je ispravljen na osnovu veoma brze reakcije jednog posetioca samo nekoliko minuta nakon objavljivanja ovog članka. Kolega Slobodan Nemčev je ostavio komentar na blogu Praktikuma na Webu. Koristim i ovu prigodu da mu se zahvalim na ispravci!

Sad valja napraviti dve tabele: jednu u kojoj se nalaze datumi svih perioda radnog staža, a u drugu ćem osmestiti sve parcijalne vrednosti razlike na osnovu tri pomenute funkcije. Na kraju, treba sabrati sve vrednosti tri kolone u drugoj tabeli i time je prva faza rešenja završena.

Naravno, to ne može tako da ostane, jer su mogući zbirovi sa više od 30 dana u mesecu i više od 12 meseci u godini. Drugu fazu čini upravo ispravka tih vrednosti. Za potrebe priče, recimo da se zbir godina nalazi u ćeliji D21, meseci u E21, a dana u F21. Ispravke valja činiti zdesna nalevo, jer će najpre ispravka dana menjati vrednosti meseci, a zatim ćemo ispraviti mesece na godine. U prvom koraku, dane ispravljamo ovim izrazom:

ćelija F24:   =MOD(F21;30)

Dakle, ako je vrednost dana npr. 47, dobićemo vrednost 17. Jedan mesec se prenosi i treba ga dodati prethodnom zbiru meseci. To ćemo učiniti ovako:

ćelija E24:   =E21+INT(F21/30)

Preostalo je da ispravimo i vrednost meseci i godina, po sasvim sličnom obrascu:

ćelija E25, ispravka meseci:   =MOD(E24;12)
ćelija D25, ispravka godina:   =D21+INT(E24/12)

Konačno imamo ispravne vrednosti, pa je ostalo samo da se to "okupi na gomilu" da bi bilo čitljivije:

ćelija D27:   =D25&" godina, "&E25&" meseci, "&F24&" dana"

Ali, hajde da budemo profesionalci, pa da sprečimo ispis poput "12 godina, 0 meseci, 17 dana" i da svaku instancu nule u rezultatu preskočimo, dobijajući tekst poput "12 godina, 17 dana":

=IF(D25<>0;D25&" godina";"")&IF(E25<>0;", "&E25&" meseci";"")&IF(F24<>0;", "&F24&" dana";"")

I to bi bilo to. Eventualno, ako vam zatreba da izračunate više od petnaest perioda radnog staža, tada ćete morati da dodate još nekoliko redova na radni list, negde unutar zone polaznih tabela. Samo pripazite da prenesete i potrebne formule u ćelije tabele sa međurezultatima.

Naravno, celo rešenje bi se reverznim umetanjem delova moglo svesti na jedan jedini izraz (dug 29 metara), ali to je zaista izlišno, jer ovo rešenje svakako nije izvodljivo u jednoj dimenziji, nego se sačinjava u dva koraka. Tako je celishodno zato što bi u Excelu bilo teško organizovati podatke za više radnika sa više perioda radnog staža; dabome, to nije nemoguće, ali možete se upitati koliko je takvo rešenje celishodno. U takvom slučaju biste morali da skrivate međurešenja ili da programirate u VBA, a tada se kao mnogo logičnije nameće rešenje u Accessu umesto ovog u Excelu.

Ali, o tome drugi put...

Da biste lakše pratili ovo rešenje, preuzmite datoteku sa primerom. Sve adrese ćelija u ovom prilogu prate iste adrese u radnoj svesci sa primerom.

Ovaj prilog možete komentarisati ako pristupite najavi priloga na blogu Praktikuma na Webu