| lekcija 2| lekcija 3 | lekcija 4 | lekcija 5
Mislim da već znate iz prošle lekcije da se datumi i vremena u Excelu pohranjuju u obliku rednih brojeva, čijom se početnom tačkom smatra određena 0. januara 1900. Srećom, u ćelijama ne vidimo ove brojeve, već datume i vremena koji su nam poznati, a koji se mogu pohraniti u različitim formatima. U ovoj lekciji ćete naučiti kako unositi datume i vremena u Excel da biste dobili željeno oblikovanje.
Unos datuma i vremena u Excel
Datumi i vremena u Excelu se mogu unijeti kao redni broj ili djelić dana, ali kao što i sami razumijete, to nije sasvim zgodno. Osim toga, s ovim pristupom, morat ćete svaki put primijeniti određeni format broja na ćeliju.
Excel nudi nekoliko formata za unos privremenih podataka. Ako koristite ovaj format, Excel će automatski pretvoriti uneseni datum (ili vrijeme) u redni broj (djelić dana) i primijeniti format na ćeliju Datumi(ili Vrijeme) postavljeno prema zadanim postavkama.
Na slici ispod prikazana je tabela opcija unosa datuma i vremena koje Excel podržava. Lijeva kolona prikazuje vrijednosti koje je potrebno unijeti u ćeliju, a desna kolona prikazuje rezultat konverzije. Vrijedi napomenuti da se datumima unesenim bez navođenja godine dodjeljuje tekuća godina, odnosno ona koja je postavljena u postavkama vašeg operativni sistem.
To nije sve moguće opcije, koji Excel podržava. Ali čak i ove opcije će vam biti dosta.
Neke opcije prikaza datuma prikazane u desnoj koloni mogu se razlikovati. Ovo zavisi od vaših regionalnih postavki i formata prikaza datuma i vremena u postavkama vašeg operativnog sistema.
Rad sa ćelijama u Microsoft Excel, koji sadrže datum ili vrijeme, imate mogućnost primijeniti različito oblikovanje na njih. Na primjer, u ćeliji možete prikazati samo dan u sedmici, ili samo mjesec i godinu, ili samo vrijeme.
Na kartici možete pristupiti svim mogućim formatima Broj dijaloški okvir Format ćelije. U kategoriji Datum Ugrađeni formati datuma su:
Da biste primijenili oblikovanje na ćeliju, samo odaberite potreban format u sekciji Tip i pritisnite OK. Potrebno formatiranje će biti primijenjeno. Ako vam ugrađeni formati brojeva nisu dovoljni, možete koristiti kategoriju Svi formati. Ovdje također možete pronaći puno zanimljivih stvari.
Ako vam nijedna od opcija ne odgovara, možete kreirati prilagođeni format broja. Prilično je lako napraviti ako koristite ugrađene formate brojeva kao predložak. Za kreiranje prilagođenog formata broja slijedite ove korake:
Kao što vidite, sve je prilično jednostavno!
U ovoj lekciji naučili smo kako prilagoditi format za prikaz datuma i vremena u programu Microsoft Excel, a također smo pogledali nekoliko korisnih opcija za njihovo unošenje u radni list. U sledećoj lekciji ćemo pričati o tome
Excel tabele pružaju mogućnost rada razne vrste tekstualne i numeričke informacije. Dostupna je i obrada datuma. U ovom slučaju može postojati potreba da se iz ukupne vrijednosti izdvoji određeni broj, na primjer, godina. Za to postoje posebne funkcije: GODINA, MJESEC, DAN i DAN U TJEDINI.
Primjeri korištenja funkcija za obradu datuma u Excelu
Excel tabele čuvaju datume, koji su predstavljeni kao niz numeričkih vrednosti. Počinje 1. januara 1900. godine. Ovaj datum će odgovarati broju 1. Štaviše, 1. januar 2009. je uključen u tabele kao broj 39813. To je upravo broj dana između dva određena datuma.
Funkcija YEAR se koristi slično kao i srodne:
- MJESEC;
- DAY;
Svi oni prikazuju numeričke vrijednosti prema gregorijanskom kalendaru. Čak i ako je unutra Excel tabela Za prikaz unesenog datuma odabran je hidžretski kalendar, a zatim kada izoluje godinu i druge vrijednosti komponenti pomoću funkcija, aplikacija će prikazati broj koji je ekvivalentan u gregorijanskom hronološkom sistemu.
Da biste koristili funkciju YEAR, morate unijeti sljedeću formulu funkcije s jednim argumentom u ćeliju:
GODINA (adresa ćelije sa datumom u numeričkom formatu)
Argument funkcije je obavezan. Može se zamijeniti sa "datum_u_numeric_format". U primjerima ispod, to možete jasno vidjeti. Važno je zapamtiti da kada se datum prikazuje kao tekst (automatska orijentacija prema lijevoj ivici ćelije), funkcija GODINA neće biti izvršena. Njegov rezultat će biti prikaz #VRIJEDNOST. Stoga, formatirani datumi moraju biti predstavljeni u numeričkom obliku. Dani, mjeseci i godine mogu se odvojiti tačkom, kosom crtom ili zarezom.
Pogledajmo primjer rada s funkcijom YEAR u Excelu. Ako trebamo dobiti godinu iz izvornog datuma, funkcija DESNO nam neće pomoći jer ne radi s datumima, već samo s tekstualnim i numeričkim vrijednostima. Za odvajanje godine, mjeseca ili dana od punog datuma, Excel pruža funkcije za rad s datumima.
Primer: Postoji tabela sa spiskom datuma i u svakom od njih je potrebno izdvojiti vrednost samo godine.
Unesimo izvorne podatke u Excel.
Da biste riješili problem, morate unijeti formulu u ćelije kolone B:
GODINA (adresa ćelije iz čijeg datuma treba izdvojiti vrijednost godine)
Kao rezultat, izdvajamo godine iz svakog datuma.
Sličan primjer funkcije MONTH u Excelu:
Primjer rada sa funkcijama DAY i WEEKDAY. Funkcija DAY može izračunati dan bilo kojeg dana od datuma:
Funkcija WEEKDAY vraća broj dana u sedmici (1-ponedeljak, 2-utorak... itd.) za bilo koji datum:
U drugom opcionom argumentu funkcije WEEKDAY, trebali biste navesti broj 2 za naš format za brojanje dana u sedmici (od ponedjeljka-1 do nedjelje-7):
Ako izostavite drugi neobavezni argument, tada će se koristiti zadani format (engleski Sunday-1 to Saturday-7).
Kreirajmo formulu od kombinacija funkcija INDEX i WEEKDAY:
Hajde da dobijemo razumljiviji oblik implementacije ove funkcije.
Primjeri praktične primjene funkcija za rad s datumima
Ove primitivne funkcije su vrlo korisne za grupisanje podataka prema: godinama, mjesecima, danima u sedmici i određenim danima.
Recimo da imamo jednostavan izvještaj o prodaji:
Moramo brzo organizirati podatke za vizualnu analizu bez korištenja pivot tablica. Da bismo to učinili, predstavljamo izvještaj u tabeli u kojoj možete jednostavno i brzo grupirati podatke po godini, mjesecu i danu u sedmici:
Sada imamo alat za rad sa ovim izvještajem o prodaji. Možemo filtrirati i segmentirati podatke na osnovu određenih vremenskih kriterija:
Osim toga, možete kreirati histogram za analizu najprodavanijih dana u sedmici, da biste razumjeli na koji dan u sedmici pada najveći broj prodaja:
U ovom obliku, vrlo je zgodno segmentirati izvještaje o prodaji na duge, srednje i kratke vremenske periode.
Vrijedi odmah napomenuti da nam nijedna od gore opisanih funkcija neće pomoći da bismo dobili razliku između dva datuma. Za ovaj zadatak trebate koristiti posebno dizajniranu funkciju RAZNDAT:
Vrsta vrijednosti u datumskim ćelijama zahtijeva poseban pristup prilikom obrade podataka. Stoga trebate koristiti odgovarajuće ovaj tip funkcije u Excelu.
Datumi su često važan dio analize podataka. Često postavljate pitanja kao što su: kada je proizvod kupljen, koliko će vremena biti potrebno da se završi zadatak u projektu ili koliki je prosječni prihod za fiskalni kvartal? Ispravan unos datuma je od suštinske važnosti kako biste osigurali tačne rezultate. Međutim za ispravno razumevanje Ovi rezultati moraju biti formatirani datumom kako bi bili lakši za razumijevanje.
Važno: Budući da su pravila koja određuju kako se datumi tumače bilo kojim programom za izračunavanje složena, morate biti što je moguće precizniji za svaki unos. Ovo će osigurati da kalkulacije datuma budu što tačnije.
O izračunima i formatima datuma
Excel pohranjuje datume kao sekvencijalne brojeve koji se nazivaju sekvencijalne vrijednosti. Na primjer, u programu Excel za Windows, 1. januar 1900. je redni broj 1, a 1. januar 2008. je redni broj 39448 jer je 39.448 dana nakon 1. januara 1900. godine.
Excel pohranjuje vrijednosti vremena kao decimale jer se vrijeme tretira kao djelić dana. Decimalni broj je vrijednost u rasponu od 0 (nula) do 0,99999999, što predstavlja vrijeme od 0:00:00 (12:00:00 am) do 23:59:59 (23:59:59 pm).
Budući da su vrijednosti datuma i vremena vrijednosti, mogu se dodavati, oduzimati i uključivati u druge proračune. Možete vidjeti datum kao rednu vrijednost, a vrijeme kao decimalnu vrijednost mijenjanjem formata ćelije koja sadrži datum ili vrijeme u uobičajeni format.
Razumijevanje dva sistema datuma
Excel za Mac i Excel za Windows podržavaju sisteme datuma 1900 i 1904 Podrazumevani sistem datuma za Excel za Windows je 1900; a zadani sistem datuma za Excel za Mac je 1904.
Excel za Windows je prvobitno bio zasnovan na sistemu datuma iz 1900. jer je poboljšao kompatibilnost sa drugim programima za tabelarne proračune razvijenim za operativni sistem MS-DOS i Microsoft Windows, i tako je postao standardni sistem datuma. Excel za Mac je prvobitno bio zasnovan na sistemu datuma iz 1904. jer je poboljšao kompatibilnost sa ranim Macintosh računarima, koji nisu podržavali datume pre 2. januara 1904. godine, pa je postao podrazumevani sistem datuma.
Donja tabela prikazuje Prvi datum i Datum posljednjeg perioda za svaki sistem datuma i sekvencijalno značenje povezano sa svakim datumom.
Budući da dva sistema datuma koriste različite početne dane, isti datum je predstavljen različitim serijskim brojevima u svakom sistemu datuma. Na primjer, 5. jul 2007. može imati dva različita uzastopna značenja u zavisnosti od korišćenog sistema datuma.
Razlika između dva sistema datuma je 1.462 dana; To znači da je serijski broj datuma u sistemu datuma iz 1900. uvijek 1.462 dana manji od serijske vrijednosti istog datuma u sistemu datuma iz 1904. Nasuprot tome, serijski broj datuma u sistemu datuma iz 1904. je uvijek 1.462 dana. manje od serijske vrijednosti istih datuma u sistemu datuma iz 1900. godine iznosi 4 godine i jedan dan (uključujući jedan prijestupni dan).
Promjena načina na koji se tumači dvocifrena godina
Važno: Da biste osigurali da se vrijednosti godine ispravno tumače, unesite četiri znamenke (na primjer, 2001. umjesto 01). Ako unesete četverocifrene godine, Excel neće tumačiti vijekove.
Ako unesete dvocifreni datum godine u tekstualnu ćeliju ili tekstualni argument u funkciji, kao što je "=godina" ("1/1/31"), Excel tumači godinu na sljedeći način:
00 – 29 se tumači kao godine od 2000. do 2029. Na primjer, kada se unese datum 5/28/19 Excel misli da bi datum mogao biti 28. maj 2019.
od 30 do 99 tumači se kao godine od 1930. do 1999. Na primjer, kada se unese datum 5/28/98 Excel misli da bi datum mogao biti 28. maj 1998.
U Microsoft Windows-u možete promijeniti način na koji se dvije cifre godine tumače za svakoga instalirane programe Windows.
Windows 10
Control Panel a zatim odaberite Control Panel.
U sekciji sat, jezik i region kliknite .
Kliknite na ikonu Jezik i regionalnim standardima.
U dijaloškom okviru region kliknite na dugme Dodatne opcije.
Otvorite karticu Datum .
Na terenu
Kliknite na dugme OK.
Windows 8
Traži Traži), unesite u polje za pretragu Element Control Panel Control Panel.
U sekciji sat, jezik i region kliknite .
U dijaloškom okviru region kliknite na dugme Dodatne opcije.
Otvorite karticu Datum .
Na terenu nakon što unesete dvije cifre godine, tumačite je kao godinu između okviri, mijenjajući gornju granicu za kapak.
Kako se gornja granica mijenja, godina se automatski mijenja na donju granicu.
Kliknite na dugme OK.
Windows 7
Kliknite na dugme Počni i odaberite Control Panel.
Odaberite stavku.
U dijaloškom okviru region kliknite na dugme Dodatne opcije.
Otvorite karticu Datum .
Na terenu nakon što unesete dvije cifre godine, tumačite je kao godinu između okviri, mijenjajući gornju granicu za kapak.
Kako se gornja granica mijenja, godina se automatski mijenja na donju granicu.
Kliknite na dugme OK.
Promijenite zadani format datuma da se godina prikaže kao četiri znamenke
Podrazumevano, kada unesete datume u radnu svesku, datumi se formatiraju tako da prikazuju dvocifrene godine. Kada promenite podrazumevani format datuma u drugi format koristeći ovu proceduru, prikaz datuma koji su prethodno uneti u radnu svesku biće promenjen u novi format dok se ne formatira u okviru za dijalog. Format ćelije" "(na kartici Dom u grupi broj kliknite na dugme pozovite dijaloški okvir).
Windows 10
U polje za pretragu na traci zadataka otkucajte Control Panel a zatim odaberite Control Panel.
U sekciji sat, jezik i region kliknite promijeniti format datuma, vremena i brojeva .
Kliknite na ikonu Jezik i regionalnim standardima.
U dijaloškom okviru region kliknite na dugme Dodatne opcije.
Otvorite karticu Datum .
Na užoj listi Format datuma
Kliknite na dugme OK.
Windows 8
Prevucite prstom od desne ivice ekrana, dodirnite Traži(ili ako koristite miša, pomaknite miša u gornji desni ugao ekrana, pomaknite ga dolje i kliknite Traži), unesite u polje za pretragu Element Control Panel a zatim pritisnite ili Izaberi Control Panel.
U sekciji sat, jezik i region kliknite promijeniti format datuma, vremena ili brojeva.
U dijaloškom okviru region kliknite na dugme Dodatne opcije.
Otvorite karticu Datum .
Na užoj listi Format datuma odaberite format koji koristi četiri znamenke za godinu (gggg).
Kliknite na dugme OK.
Windows 7
Kliknite na dugme Počni i odaberite Control Panel.
Odaberite stavku Regionalne i jezičke opcije.
U dijaloškom okviru region kliknite na dugme Dodatne opcije.
Otvorite karticu Datum .
Na užoj listi Format datuma odaberite format koji koristi četiri znamenke za godinu (gggg).
Kliknite na dugme OK.
Promjena sistema datuma u Excelu
Sistem datuma se automatski mijenja kada otvorite dokument kreiran na drugoj platformi. Na primjer, ako radite u programu Excel i otvorite dokument kreiran u programu Excel za Mac, " sistem datuma 1904" se instalira automatski.
Možete promijeniti sistem datuma slijedeći korake u nastavku.
Odaberite File > Opcije > Dodatno.
U sekciji prilikom izračunavanja ove knjige odaberite knjigu koju želite, a zatim potvrdite ili poništite izbor u polju koristite sistem datuma 1904 .
Problem: Problemi sa datumima između knjiga koje koriste različite sisteme datuma
Možete naići na probleme prilikom kopiranja i lijepljenja datuma, te prilikom kreiranja vanjskih veza između knjiga na osnovu dvije različiti sistemi datum Datumi se mogu objaviti četiri godine ranije ili kasnije od očekivanog datuma. Ovi problemi se mogu pojaviti ako koristite Excel za Windows, Excel za Mac ili oboje.
Na primjer, ako kopirate datum 5. jul 2007. iz radne knjige koja koristi sistem datuma 1900, a zatim ga zalijepite u radnu svesku koja koristi sistem datuma 1904, datum se pojavljuje kao 5. jul 2011., što je 1.462 dana kasnije. Dodatno, ako kopirate datum 5. jul 2007. iz radne knjige koja koristi sistem datuma 1904, a zatim ga zalijepite u radnu svesku koja koristi sistem datuma 1900, datum će biti 4. jul 2003., što je 1.462 dana ranije. Opće informacije možete pronaći u odeljku Sistemi datuma u Excelu.
Ispravite grešku kopiranja i lijepljenja
Da biste postavili datum na četiri godine i jedan dan kasnije, dodajte mu 1.462
List1!$1+1462
Da biste postavili datum na četiri godine i ranije, oduzmite 1.462 od njega. Primjer
List1!$1-1462
Više informacija
Uvijek možete postaviti pitanje stručnjaku za Excel Tech Community, zatražiti pomoć u zajednici Answers, a također i predložiti nova funkcija ili poboljšanje na web stranici Excel User Voice
Ako radite sa podacima zasnovanim na vremenu, možda ćete morati da umetnete određeni broj datuma u tabelu. Na primjer, u aplikaciji za praćenje prodaje možete unijeti niz datuma za kvartal, pazeći, naravno, da preskočite datume koji padaju na vikend.
Korištenje AutoComplete
Većina efikasan način unesite više datuma, što ne zahtijeva nikakve formule - koristite funkciju automatskog popunjavanja. Unesite prvi datum, a zatim povucite ručicu za popunjavanje ćelije dok desnom tipkom miša kliknete. Otpustite dugme miša i izaberite komandu kontekstnog menija Popunite po mjesecima(Sl. 108.1).
U nekim situacijama morate unijeti dva datuma prije nego što možete koristiti funkciju automatskog popunjavanja. Na primjer, ako trebate unijeti datume koji su uzastopni ponedjeljci, morate unijeti prva dva datuma u nizu. Zatim odaberite oba datuma, kliknite desnim klikom i odaberite unutra kontekstni meni stavka Popuniti po danu.
Korištenje formula
Drugi način unosa niza datuma zasniva se na korištenju formula. Prednost formule (umjesto funkcije automatskog dovršavanja) je u tome što možete promijeniti prvi datum, a svi ostali će se automatski ažurirati. Unesite datum početka u ćeliju, a zatim koristite formule (kopiranjem stupca) za kreiranje dodatni datumi. Za sljedeće primjere pretpostavite da ste u ćeliju A1 unijeli prvi datum u nizu i formulu u ćeliju A2. Zatim možete kopirati ovu formulu niz kolonu onoliko puta koliko je potrebno.
Da biste generisali niz datuma razdvojenih sa sedam dana, koristite ovu formulu: =A1+7.
Da kreirate niz datuma razdvojenih jednim mjesecom, koristite sljedeću formulu: =DATUM(GODINA(A1),MJESEC(A1)+1,DAN(A1))
Da kreirate niz datuma razdvojenih jednom godinom, koristite ovu formulu: =DATUM(GODINA(A1)+1,MJESEC(A1),DAN(A1)
Da biste kreirali niz koji uključuje samo radne dane (sve dane osim subote i nedjelje), koristite sljedeću formulu. Pretpostavlja da datum u ćeliji A1 nije praznik: =IF(WEEKDAY(A1)=6,A1+3,A1+1) . Ova formula se vraća serijski broj datume, tako da morate formatirati ćelije da biste prikazali datume.
Najjednostavniji i brz način Da biste unijeli trenutni datum ili vrijeme u ćeliju, pritisnite kombinaciju prečaca CTRL+“;” (trenutni datum) i CTRL+SHIFT+“;” (trenutno vrijeme).
Mnogo je efikasnije koristiti funkciju TODAY(). Na kraju krajeva, ne samo da postavlja, već i automatski ažurira vrijednost ćelije svaki dan bez intervencije korisnika.
Kako staviti trenutni datum u Excel
Da biste umetnuli trenutni datum u Excel, koristite funkciju DANAS(). Da biste to učinili, odaberite alat "Formule" - "Datum i vrijeme" - "DANAS". Ova funkcija nema argumente, tako da možete jednostavno upisati “=TODAY()” u ćeliju i pritisnuti ENTER.
Trenutni datum u ćeliji:
Ako je potrebno da se vrijednost u ćeliji automatski ažurira ne samo trenutni datum, ali tada je bolje koristiti funkciju “=TDATE()”.
Trenutni datum i vrijeme u ćeliji.
Kako postaviti trenutni datum u Excelu na zaglavlja i podnožja
Umetanje trenutnog datuma u Excel se implementira na nekoliko načina:
- Postavljanjem opcija zaglavlja i podnožja. Prednost ove metode je što su trenutni datum i vrijeme utisnuti na svim stranicama u isto vrijeme.
- Korištenje funkcije TODAY().
- Korišćenje kombinacije prečice CTRL+; – za postavljanje trenutnog datuma i CTRL+SHIFT+; – za postavljanje trenutnog vremena. Nedostatak je ovu metodu Vrijednost ćelije se neće automatski ažurirati na trenutne vrijednosti prilikom otvaranja dokumenta. Ali u nekim slučajevima nedostatak podataka je prednost.
- Korištenje VBA makroa pomoću funkcija u programskom kodu: Datum();Vrijeme();Sada().
Zaglavlja i podnožja vam omogućavaju da postavite trenutni datum i vrijeme na vrhu ili dnu stranica dokumenta koji će biti izlaz na pisač. Osim toga, podnožje nam omogućava da numerišemo sve stranice dokumenta.
Da biste napravili trenutni datum u Excelu i numerirali stranice pomoću zaglavlja i podnožja, učinite ovo:
Zaglavlja i podnožja nam omogućavaju više od postavljanja datuma i brojeva stranica. Možete dodati i mjesto za potpis osobe odgovorne za izvještaj. Na primjer, uredimo sada donji lijevi dio stranice u području zaglavlja i podnožja.