1 Pokretanje-zatvaranje Microsoft Excel-a 2003

2 Izgled prozora Microsoft Excela 2003

3 Podešavanje Excela

4 Otvaranje novog dokumenta

5 Spremanje dokumenta

6 Zatvaranje dokumenta

7 Pregled LISTA prije ispisa

8 Određivanje margine Radnog lista

9 Kreiranje Zaglavlja i Podnožja Radnog lista

10 Ispisa Lista-tablice

11 Kreiranje mapa u Excelu

12 Kretanje radnom tablicom

13 Označavanje ćelija

14 Matematički operatori

15 Unos podataka u ćelije

16 Unos i obrada teksta u ćelijama

17 Promjena podataka i brisanje sadržaja ćelije

18 Spajanje i dijeljenje ćelija

19 Automatska ispuna - brzi unos podataka

20 Ispuna ćelija posebnim znakovima

21 Naredbe Poništi i Ponovi poništeno

22 Premještanje i Kopiranje sadržaja ćelije

23 Umetanje i brisanje Ćelija, Stupaca i Redova

24 Promjena veličine Ćelija, Stupaca i Redova

MICROSOFT EXCEL - evidencija radnnog vremena Excel - obrazac - oduzimanje i zbrajanje radnih sati - vremena - špranca - šihterica

EVIDENCIJA RADNIH SATI

RADNIKA(zbrajanje i oduzimanje radnih sati)

 

EVIDENCIJA RADNOG VREMENA u EXCELU (evidencija o radnom vremenu radnika)

Prema Pravilniku o prijenosu podataka o radnom vremenu mobilnih radnika i o vođenju evidencije radnog vremena djelatnika u knjigovodstvu potrebno je voditi radno vrijeme radnika,  iskoristite Excel i kreirajte obrazac (šprancu, predlošku, template) za evidenciju radnog vremena radnika.

U koliko želite sami kreirati jednostavnu tablicu za knjigovodstvo (šprancu ili šihtericu) i voditi evidenciju radnih sati radnika (evidenciju radnog vremena radnika u Excelu) pogledajte ovaj tutorijal vodič koji vam može pomoći u kreiranju i postavljanju formula i funkcija da bi kompletirali obrazac.

PRIMJER 1.

ŠIHTERICA za proračun radnih sati radnika (Radnici i radno vrijeme)

U ovom primjeru prikazat ću vođenje radnih sati za svakog radnika (djelatnika) posebno a na kraju automatski svi podaci se kopiraju u zbirnu tablicu (sve ostalo je vaša kreativnost i mašta).

Kao prvo, kreirao sam popisne liste tj. definirao imena za neke osnovne podatke koji se pojavljuju u tablici a to mogu biti )GO-godišnji, BO-bolovanje, SD-slobodan dan, P-praznik). Ove liste su nam važne da možemo upisom dotične liste dobiti rezultat radnih sati u formatu vremena. ( nije dobro u formuli postavljati za pojedinu listu BROJ kao sat, jer format formule tada neće dobro dati rezultat.

Dakle na jednom listu sam definiraop popisne liste. Taj list sam nazvao imenom "NEDIRAJ" a u stvari to nije bitno, jer ga možemo sakriti naredbom Oblikovanje(Format) => Lista (Sheet) => Sakrij (Hiden)

Za definiranje imena neke liste pogledajte link Kako se imenuje popisna lista

Sam popis definiranih lista (bolovanje, slobodan dan, godišnji, praznik) izgleda ovako:

1

Traži

25 Umetanje Simbola u tablicu

26 Umetanje slika u tablicu

27 Radnje vezane uz Radni List

28 Apsolutna i relativna adresa ćelije s imenom

29 Izrada Grafikona

30 Oblikovanje radnih tablica

31 Kreiranje LISTE - Baze podataka

32 Sortiranje podataka

33 Zamrzavanje dijela prozora Radnog lista

34 Filtriranje podataka

35 Oblikovanje Datuma

36 Zaključavanje ćelija

37 Zaštita dokumenta

38 Popis svih FUNKCIJAu Excelu

- Primjeri često korištenih formula

- Primjeri zadataka za ECDL

39 Sve alatne trake u Excelu

40 Pomoć u Excelu41 Oporavak

dokumenta42 Ograničenja u

Excelu43 Tipkovni prečaci44 Razno

-

Na prvom listu "Radnik1" vodi se evidencija radnih sati za dotičnog radnika. Uočite da sam u tablicu predvidio mogućnosti za BO - Bolovanje,  - 8 satiGO - Godišnji odmor, - 8 sati SD - Slobodan dan, - 0 sati P - Praznik, - 8 sati

Ćelije u stupcu "F" formatirane su kao Vrijeme (Time) 37:30:55 jer se na taj način prikazuju rezultati u obliku vremena kao h:mm:ss

Formula za računanje sati tj. oduzimanje sati (raspon vremena između) glasi:=IF(D2="SD";sd;IF(D2="BO";bo;IF(D2="P";p;IF(D2="GO";go;IF(D2>E2;E2+1-D2;E2-D2)))))

Što znači da će na osnovu podataka u stupcu "D" dati rezultat tj. oduzeti radne sate ili postaviti rezultat sa popisnih lista (ovisno o vrsti podatka)

Nakon popunjavanja radnih listova za svakog radnika pojedinačno, postoji radni list ZBIRNO na koji se automatski podaci sa Radnika1,2,3 linkaju i daju ukupan broj radnih sati. Naravno u ovom zbirnom listu može se dodati još nekih eventualno potrebnih podataka kao što su cijena radnog sata, ukupno bolovanja itd.

Naravno sve ovo se može smjestiti u jedan "folder" po mjesecima i na kraju napraviti zbirna knjiga cijele godine, gdje možemo proučavati koliko je koji radnik bio na GO, BO, SD i slično.

Primjer kompletne Šihterice za KLIZNO RADNO VRIJEME sa izvješćem i vođenjem radnih sati za sve djelatnike (radnike) na jednom radnom listu + izvješće za svakog radnika posebno pogledajte na ovom linku ŠIHTERICA i IZVJEŠĆE

Na slici ispod uočite da se radni sati prikazuju kao oblikovani kao BROJ ane ka vrijeme. Ovaj prikaz dobijemo tako što formulu pomnožimo sa 24. (08:00*24=8) i dobijemo decimalni broj. Naravno trebamo oblikovati ćeliju kao Brojčanu vrijednost.

2

Rad u tri smjene (prva smjena, druga smjena, treća smjena, noćni rad, klizno radno vrijeme)

Uočite kako izgleda izvješće o evidenciji rada i radnog vremena radnika, nakon ispisa bilo da želite Prvu,  Prvu i Drugu smjenu,  Prvu, Drugu i Treću smjenu ili Klizno radno vrijeme

3

PRIMJER 2.

Oduzimanje sati u Excelu

Oduzimanje radnih sati u Excelu svodi se na oduzimanje decimalnih brojeva jer Excel sate vidi kao decimalni broj.Vrijeme sata se unosi baš kako je prikazano na slici 08:00, 16:00 (dakle sa dvotočkom)

4

U tablici ispod možete uočiti dva stupca, "C" sa decimalnim brojevima i "D" sa ćelijama formatiranim kao vrijeme.U ćelijama C7 i D7 izvršeno je zbrajanje podataka u ćelijama iznad. Uočite da su rezultati zbrajanja sati isti (s malom razlikom pogleda na rezultat zbog oblikovanja (formata) ćelije.

Oduzimanje sati u ćelijama C2:C6 i D2:D6 izvedeno je sa formulom =IF(AND(A2;B2>0);IF(A2>B2;B2+1-A2;B2-A2);"")Ovdje uočite važnost oduzimanja noćnog vremena u periodu 22:00 sati uveče do 06:00 sati ujutro (22:00-06:00)a samo zbrajanje izvedeno je sa formulom =SUM(C2:C6)*24  i  =SUM(D2:D6) => Ova ćelija je formatirana kao 37:30:55 (vidi sliku)

PRIMJER 2a

Ako imamo situaciju da  zbroj radnih sati (vremena) želimo prikazati u tekstualnom obliku tada formatiramo ćeliju kao Custom i to na slijedeći način Custom => dd\d hh\h mm"min" (ili ovako d\d h\h mm"min").

sada nam se umjesto oblika vremena 50:15:00 vrijeme u satima u ćeliji E7 prikazuje kao 02d 02h 15min. U slučaju da rezultat u E7 želite konvertirati samo u čiste sate tada iskoristite formulu =DAY(E7)*24+HOUR(E7) koja će vratiti rezultat 50

5

Ako imamo već gotov tekstualni oblik u formatu vremena kao 02d 02h 15min i želimo ga konvertirati u ispravan prikaz sati (vremena) tada konvertirajmo tekst u oblik vremena u vremenski tekst pa ga prikažimo kao vrijeme (time)

dakle u ćeliju B2 upišimo formulu koja će obraditi ćeliju A2; =MID(A2;1;1)*24+MID(A2;4;1)&":"&MID(A2;6;3) pa u ćeliju C2 upišimo formulu =VALUE(B2)

Uočite da formula u B2 oduzima tekstualne dijelove iz samog teksta i pretvara u oblik sati (zato 2d množimo sa 24) ili jednom riječju izvlači brojeve iz teksta i kreira od njih sate i minute. I na kraju u C2 pretvaramo rezultat iz B2 u vremenski oblik sa formatom Time => 37:33:55

6

Da bi vam bilo jasnije raščlanimo formulu na više dijelova koja u konačnici daje rezultat 50:15 (48h+2h)  i 15min

=MID(A2;1;1)*24 => rezultat je 48=MID(A2;4;1) => rezultat je 2=MID(A2;6;3) => rezultat je 15

PRIMJER 3.

Oduzimanje radnih sati

Još jedan primjer oduzimanja radnih sati (vremena)

7

PRIMJER 3a.

Primjer oduzimanja radnih sati u situaciji kada znamo vrijednost vremena za DOLAZAK i TRAJANJE RADA-putovanja. Trebamo izračunati kada je radnik došao na posao ili kada je krenuo na put? Uočite na slici ispod formulu za izračun kada je radnik krenuo na putovanje. =A2+60-B2 (ili =A2+1-B2 ili =A2+10-B2 ). sada će se netko pitati zašto 60 a može 10 ili 1?  Isprobajte "Evaluate Formula"

U principu bi trebalo staviti broj 1 ali sam namjerno stavio 60 i ostale primjere da shvatite što se događa kada se oduzimaju sati ovisno o prvom ili drugom broju. Ako je prvi broj veći od drugoga nije problem oduzeti sate ali ako je prvi broj manji od drugoga tada jeste.  Dan ima 24 sata i ako je početk rada 17:47 i radnik je radio 14:21 sati tada je kraj radnog vremena u 8:08 idući dan. Ako formatirate sate kao generalni broj uočit ćete decimalne brojeve za svako specifično vrijeme. Ako na to vrijeme dodate neki cijeli broj npr: 1 ili 10 tada će kod oduzimanja biti ispravno izračunata operacija u suprotnom bi otišli u minus. Nadam se da ste shvatili, ako niste surfajte i googlajte ;)

PRIMJER 4.

Primjer vođenja redovnih i prekovremenih radnih sati u Excelu

8

Primjer evidencija radnih sati za radnika - redovni i prekovremeni radni sati

Naravno da svoju evidenciju radnih sati trebate oblikovati tako da unaprijed planirate eventualne mogućnosti za BO - bolovanje, GO- Godišnji odmor, P - Praznik , SD - Slobodan dan itd itd... (sve ovisi vodite li evidenciju skupno za sve radnike u jednom Sheetu ili za svakog radnika otvarate novi Sheet.) (izvorne temeES)

Uz to možete pogledati i ovaj tutorijal sa kompletnomevidencijom radnog vremena i obračuna sati. (ovaj primjer možete downloadati ovdje OBRAČUN PLAĆE)

KAKO ZBRAJATI BROJEVE U ĆELIJAMA GDJE JE BROJ KOMBINIRAN SA TEKSTOM (zbrajanje ćelija sa tekstom)

Ako ste se ikada zapitali kako bi mogli zbrojiti brojeve u nekoj ćeliji koja sadrži tekst i broj evo mali primjer kako se može zbrajati ćelije koje sadrže kombinacije teksta i broja.

Uzmimo za primjer da radite nekakvu šihtericu za praćenje radnih sati radnika gdje ima više vrsta smjena, pa čak i rad čuvara od 12 sati. Imate nekakve smjene radnika pa želite tekstualnim opisom prikazati u svakoj ćeliji kako je dotični radnik radio taj dan. Također pogledajte Kako izdvojiti broj iz ćelije koja sadrži kombinaciju teksta i broja

Na slici lijevo uočite upisane radne smjene tekstualno. Da bi na kraju dobili zbroj ti radnih smjena potrebno je ukloniti tekst iz naziva i ostale znakove. Potom ostatak zbrojiti. Ovo možemo uraditi slijedećom formulom polja:

=SUM(--RIGHT(A1:A5;2)) => ovo ja ARRAY formula i završavate je sa Ctrl+Shift+enter

NAPOMENA: Formula ostavlja samo dva zadnja broja (znaka) ako želite kombinirati više znamenki tada umjesto 2 u formuli stavite 3 itd...(ali tada sve znamenke moraju imati tri broja (008, 012, 120, 352, 054...)

9

PRIMJER 5.

Dnevni sati - noćni sati (rad danju i noćni rad - Radnici i radno vrijeme)

Još jedan od primjera za izračun radnih sati (dnevni rad i noćni rad). Na slici ispod imate tri načina vođenja rada danju i rada noću (dnevni radni sati i noćni radni sati).

Uočite različite formule. Imamo stupac C u kojem računamo ukupan broj radnih sati. U stupcu D računamo rad koji spada u dnevne radne sate i stupac E u kojem računamo noćne radne sate

Ćelija A2:B7 formatirane kao TIME - 13:30 Ćelije C2:C7, D2:D7, E2:E7 su formatirane kao Custom => [h]:mm C2 =B2-A2+(A2>B2) D2 =(A2>B2)*MEDIAN(0;B2-1/4;2/3)+MAX(0;MIN(11/12;B2+(A2>B2))-MAX(1/4;A2)) E2 =C2-D2 

Ćelije G2:G7, H2:H7 su formatirane kao Custom => [h]:mm G2 =IF(B2<A2;IF(B2>1/4;B2-1/4;0)+IF(A2<11/12;11/12-A2;0);MIN(11/12;B2)-MAX(1/4;A2)) H2 =IF(B2<A2;MIN(B2;1/4)+(1-MAX(A2;11/12));IF(A2<1/4;1/4-A2;0)+IF(B2>11/12;B2-11/12;0)) 

Ćelije J2:J7, K2:K7 su formatirane kao Custom => [h]:mm J2 =IF(B2<A2;IF(B2>1/4;B2-1/4;0)+IF(A2<11/12;11/12-A2;0);MAX(MIN(11/12;B2)-MAX(1/4;A2);0)) K2 =IF(B2<A2;MIN(B2;1/4)+(1-MAX(A2;11/12));IF(A2<1/4;MIN(1/4;B2)-A2;0)+IF(B2>11/12;B2-11/12;0))

PRIMJER 6.

Vođenje radnih sati, prekovremenih sati, dnevnih sati i noćnih sati sa pauzom za ručak (klizno radno vrijeme)

U koliko imate potrebu ili klizno radno vrijeme a uz to želite voditi vrijeme provedeno na pauzi za ručak, tada pogledajte primjer kako se može u Excelu izraditi tablica i voditi radni sati

Ćelije B3:E11 formatirane Custom => hh:mm F3 =(E3-B3+(E3<B3))*24

10

G3 =((E3-B3+(E3<B3))-(D3-C3+(D3<C3)))*24H3 =MAX(0;G3-$C$1)I3 =E3-B3+(B3>E3)-(D3-C3)J3 =(B3>E3)*MEDIAN(0;E3-1/4;2/3)+MAX(0;MIN(11/12;E3+(B3>E3))-MAX(1/4;B3))K3 =IF(I3>J3;I3-J3;"")

Napomena: U tablici ispod nije riješen problem kada neki djelatnik koristi pauzu na prijelazu između dan-noć (red broj 8 "Mile" pauza od 21:30-22:30), ako znamo da je početak noćnog rada od 22:00 sati. Tako da tablica u tom redu ima grešku ako izdvajamo radne sate za Dan i radne sate za Noć. (btw: nisam to još riješio)

PRIMJER 7.

Kako prikazati koji radnik je bio na bolovanju (godišnjem, odsutan) za određeni datum ili u dotičnom mjesecu

U ovom primjeru pokazat ću kako možemo u jednom stupcu prikazati tko je sve od radnika bio na bolovanju, godišnjem ili odsutan opravdano ili neopravdano na određeni dan (današnji dan) ili pak popis svih radnika koji su tokom mjeseca bili odsutni.

Situacija je slijedeća. Imamo Šihtericu i Sheet1 na kojem vodimo evidenciju dolaska radnika na posao.

11

Na ovoj Šihterici imenujmo nazive raspona (Define Name) i to

A2:A9 => radniciB2:AF9 => sati

Uočimo da su nam radnici tj. imena sortirana abecednim redom što je važno za prvi zadatak a to je

Kako prikazati tko je na današnji dan odsutan

Ovaj prikaz možemo izvršiti na istom Sheetu1 ili na drugom Sheetu2 (uočite dvije opcije)

Formule kada rezultate prikazujemo na istom Sheetu1

A1 => =TODAY()

12

A2 => =A2B2 => =OFFSET($A$1;MATCH(AH2;$A$2:$A$10;1);MATCH($AH$1;$B$1:$AF$1;1))

Formule kada rezultate prikazujemo na Sheetu2

AH1 => =TODAY()AH2 => =Sheet1!A2AI2 => =IF(OFFSET(Sheet1!$A$1;MATCH(A2;Sheet1!$A$2:$A$10;1);MATCH($A$1;Sheet1!$B$1:$AF$1;1))="B";"B";"")

Ako želimo na Sheetu2 imati sve oznake tada uklonimo funkciju IF. Dakle ove dvije opcije iznad prikazuju status radnika za određeni datum a to je današnji datum. Naravno mi možemo po potrebi upisati datum po želji, ne moramo koristiti funkciju TODAY(). Obavezno radnici moraju biti sortirani abecednim redom uzlazno u stupcu A.

Kako prikazati tko je u toku mjeseca sve bio na bolovanju ili odsutan i koliko dana

Za ovaj primjer koristit ćemo dodatni Sheet3 na kojem ćemo linkati imena radnika i prebrojati koliko puta se pojavljuje određeni uvjet. Uvjet u našem slučaju je B=bolovanje, G=godišnji, O=opravdano i N=neopravdano

dakle uočite formule (u ovom slučaju imena radnika NE moraju biti sortirana abecednim rdom)u stupcu A => Sheet1!=A2  => Ovim povlačimo podatke imena sa Sheeta1 iz stupca Au stupcu B => =COUNTIF(Sheet1!B2:AF2;Sheet1!$AL$1) => Za svako ime prebrojavamo uvjet koji se nalazi u AL1 na Sheetu1

Ovdje ćemo i imenovati raspon podataka pod nazivom "count" radi kasnijeg lakšeg uklapanja u formule

Kao prvo idemo postaviti našu tablicu za pregled svih radnika i broj dana za određeni uvjet. Uočite da se u ćeliji AL1 nalazi naš uvjet a biramo ga preko postavljene Validation List.

13

U ćeliju AK1 postavimo naslov podataka a to je IME radnika.U ćeliju AK2 upišimo slijedeću ARRAY formulu, formulu završavamo sa CTRL+SHIFT+ENTER. Ovdje je važno ono imenovanje raspona podataka u Šihterici na početku Primjera 7. ovog tutorijala.

{=IFERROR(INDEX(radnici;SMALL(IF((sati=$AL$1)*(COUNTIF($AK$1:AK1;radnici)=0);ROW(sati)-MIN(ROW(sati))+1;"");1));"")}

U ćeliju AL2 upišimo slijedeću formulu

=IFERROR(VLOOKUP(AK2;count;2;FALSE);"")

Kopirajmo formule prem dolje do određenog reda (broja radnika) i time smo završili zadatak. Izborom uvjeta preko padajuće liste automatski nam se prikazuje koji sve radnici zadovoljavaju uvjet za dotični mjesec i koliko dana je taj uvjet bio aktualan.

PRIMJER 8.

Evidencija radnih sati radnika za dnevnu i noćnu smjenu, Decimalni format i Time format

Još jedan primjer vođenja evidencije radnih sati djelatnika

D2 =IF(COUNT(B2:C2)<2;"";24*MOD(C2-B2;1))E2 =IF(C2>0;IF(COUNT(B2:C2)<2;"";24*MOD(C2-B2;1))/24;"")F2 =IF(C2>0;(B2>C2)*MEDIAN(0;C2-1/4;2/3)+MAX(0;MIN(11/12;C2+(B2>C2))-MAX(1/4;B2));"")G2 =E2-F2

14

Kao što znate Excel svako vrijeme vidi kao nekakav broj. Ako želite saznati više detalja o tome kako Excel računa elemente vremena (sati) tada pogledajte ovaj video ispod

Primjere obrasca možete skinuti ovdje: (evidencija radnog vremena obrazac)

- Primjer C Kompletan primjer vođenja evidencije radnog vremena i obračuna plaće- Download obrasaca u Excelu za mjesečnu evidenciju radnog vremena radnika (KLIKNI OVDJE)

15