Uvjetno oblikovanje u Excelu može se smatrati vrijednim uslužnim programom koji može pomoći u vizualnom poboljšanju analize podataka i prezentacije u proračunskoj tablici primjenom određenog oblikovanja na temelju unaprijed definiranih pravila ili uvjeta. Ovaj će post objasniti kako možete postaviti uvjetno oblikovanje u Excelu za datume. Također smo uključili detaljne primjere koje možete isprobati dok čitate vodič.
Kako postaviti uvjetno oblikovanje u Excelu za datume
Uvjetno oblikovanje ne samo da pomaže u isticanju ćelija pomoću kodova boja, već također oblikuje retke, stupce i ćelije koje se sastoje od datuma na način koji se može smatrati vizualno smislenim i privlačnim.
- Unaprijed definirano
- Korisnik definiran
- Stvorite prilagođeno pravilo oblikovanja
- Označavanje praznika u Excelu
- Uvjetno oblikovanje datuma na temelju više uvjeta
- Isticanje nadolazećih datuma i odgoda
Obavezno pažljivo prođite kroz detalje u Excelu. Nudi mnogo opcija, od kojih neke mogu biti izvan opsega ovog vodiča.
1] Unaprijed definirana pravila datuma uvjetnog oblikovanja s datumima
Uvjetno oblikovanje s datumima može biti korisno pri radu sa skupovima podataka koji uključuju informacije povezane s datumom. Excel nudi gotovo deset različitih opcija za oblikovanje ćelija na temelju datuma, kao što je prikazano u nastavku:
- Da biste formatirali ćeliju s datumom kao vrstom podataka, kliknite na Početna > Uvjetno oblikovanje > Pravila za označavanje ćelija > Datum koji se pojavljuje
- Opcije uvjetnog oblikovanja mogu se odabrati iz padajućih izbornika u sljedećem prozoru. Prvi padajući izbornik omogućuje nam odabir raspona dana, a opcije oblikovanja mogu se odabrati s drugog.
- Klik u redu za primjenu odabranih pravila oblikovanja.
2] Korisnički definirana pravila uvjetnog oblikovanja datuma s datumima
Osim skupa unaprijed definiranih pravila, Excel također nudi mogućnost prilagodbe tih pravila na temelju korisničkih zahtjeva. Postoji više načina za postavljanje prilagodbi, kao što je prikazano u nastavku:
Korištenje prilagođenog formata
- Otvori Datum koji se događa formatirajte prozor slijedeći gore navedene korake.
- Nakon odabira raspona dana odaberite Prilagođeni format iz padajućeg izbornika opcija oblikovanja.
- The Prozor za formatiranje ćelija će se pojaviti zbog gore navedenog, gdje možemo odabrati boju, obrub, font i efekte po našem izboru, a zatim kliknuti OK za primjenu promjena.
Stvorite nova pravila
Ova metoda uključuje stvaranje potpuno novog pravila za uvjetno oblikovanje, za razliku od gornje metode, u kojoj možemo birati između nekoliko unaprijed definiranih pravila i stvoriti samo prilagođeni format. Stvaranje novog pravila uključuje:
- Odabir ćelija za koje će se primijeniti oblikovanje.
- Na Dom Tab, kliknite Uvjetno oblikovanje > Novo pravilo , koji bi trebao otvoriti Prozor novog pravila oblikovanja.
- Na navedenom prozoru možemo birati između dostupnih Vrste pravila , nakon čega slijedi uređivanje opisa pravila za postavljanje kriterija za uvjetno oblikovanje.
- Nakon dovršetka, Format možete kliknuti gumb u donjem desnom kutu prozora kako biste odabrali oblikovanje za navedeno pravilo.
- Na Formatiraj ćelije prozor koji se otvori, možemo odabrati Font, Obrub, Boju ćelije, Efekte itd., prema izboru i jednom kliknuti U redu na ovom prozoru, nakon čega slijedi Novo pravilo oblikovanja Prozor za primjenu promjena.
su moji dodaci ažurirani
Bilješka: Pravila oblikovanja mogu se koristiti za stvaranje i primjenu više od jednog pravila za isti skup podataka na temelju različitih uvjeta/kriterija.
Čitati: Excelov filtar datuma ne grupira prema mjesecu
3] Kako stvoriti prilagođeno pravilo oblikovanja s datumima
U gornjem primjeru vodili smo korake za stvaranje novog pravila za uvjetno oblikovanje na temelju već predviđenih kriterija. Excel također omogućuje primjenu navedenih pravila oblikovanja na korisnički definirane kriterije.
Na primjer, pretpostavimo da trebamo istaknuti ćelije s datumima starijim od 30 ili 60 dana. U tom slučaju, isto se također može implementirati primjenom datumskih formula i funkcija dostupnih u Excelu, kao što je ilustrirano u nastavku:
A] Istaknite trenutne i starije datume
Pod, ispod Odaberite novo pravilo u opciji Uvjetno oblikovanje odaberite Koristite formulu da odredite koju ćeliju oblikovati , upišite:
=$H4=DANAS() istaknuti današnjicu
=$H4>DANAS() istaknuti buduće dane
=$H4
=$H4<=(DANAS()-30) za isticanje datuma koji su stariji od 30 dana.
=$H4<=(DANAS()-60) za isticanje datuma koji su stariji od 60 dana.
Gornja formula provjerava odabrani datumski raspon za trenutni datum, a kada se pronađe podudaranje, promijenite boju pozadine u ljubičastu kako je odabrano u opciji Format. The $ koristi se pored H znači da će stupac ostati konstantan, ali će redovi biti promjenjivi.
- Jednom Opis pravila je spomenuto, font, boje i efekti mogu se odabrati klikom na gumb Format.
- Kliknite na u redu na prozoru Format Cells, nakon čega slijedi isto na prozoru New Rule, kako bi promjene bile učinkovite.
B] Istakni na temelju korisnički definiranog raspona datuma
Slične metode mogu se primijeniti za oblikovanje raspona datuma koje su odredili korisnici. Na primjer, ako trebamo istaknuti datume koji su stariji od 30 dana, ali manje od 45 dana, funkcija TODAY() može se koristiti u kombinaciji s logičkim operatorom kao što je AND za određivanje kriterija koji uključuju dva uvjeta:
- Odaberite ćelije za koje treba primijeniti oblikovanje i otvorite prozor Novo pravilo.
- u Uredite opis pravila polje, unesite donju formulu,
=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))
Gornja formula koristi I za označavanje žutom bojom datuma koji su stariji od 30 dana, ali manje od 45 dana za cijeli odabir, počevši od ćelije br. H2.
C] Isticanje vikenda
U istom rasponu, korištenjem formule WEEKDAY(), mogu se istaknuti i datumi koji padaju na vikende:
- Odaberite ćelije za koje treba primijeniti oblikovanje i otvorite prozor Novo pravilo.
- u Uredite opis pravila polje, unesite donju formulu,
=WEEKDAY($H2, 2)>5
The sintaksa formule Weekday može se predstaviti kao WEEKDAY(serial_number, [return_type]), pri čemu serial_number predstavlja broj ćelije datuma koji pokušavamo provjeriti (SA1 u ovom slučaju).
The povratne_vrste parametar predstavlja tip za tjedan koji počinje od ponedjeljka(1) do nedjelje(7), uzimajući u obzir da je početni dan u tjednu ponedjeljak (predstavljen sa 1), a posljednji dan u tjednu kao nedjelja (predstavljen sa 7).
Međutim, return_types nisu obavezni u formuli i predstavljeni su u trećim zagradama. >5 koristi se za označavanje subote(6) i nedjelje(7) za navedeni tip povrata.
besplatna hiper v sigurnosna kopija
D] Označavanje redaka na temelju određenog datuma
Ova opcija može biti korisna pri radu s velikim količinama podataka u kojima je potrebno istaknuti određeni datum. Isto se može postići određivanjem numeričke vrijednosti datuma ili korištenjem DATEVALUE() funkcija. Brojčana vrijednost mora se prvo odrediti kako bi se primijenilo uvjetno oblikovanje za određeni datum jer Excel trajno pohranjuje datume kao uzastopne serijske brojeve. Da biste pronašli brojčanu vrijednost određenog datuma,
- Kliknite desnom tipkom miša na ćeliju s datumom, kliknite na Format Cells > Number, a zatim izaberite Općenito . Na ćeliji bi se prikazala 5-znamenkasta brojčana vrijednost, koja se može zabilježiti za buduću upotrebu.
- Kliknite Odustani nakon što je broj zabilježen budući da ne moramo mijenjati format datuma za ćeliju.
- Kliknite na Uvjetno oblikovanje>Novo pravilo> Odaberite novo pravilo > Upotrijebite formulu da odredite koju ćeliju oblikovati i upišite sljedeću formulu,
=$G5=45249
- Koristiti DATEVALUE() funkciju, isto se može učiniti unosom dolje navedene formule,
=$G5=DATEVALUE(“11/19/2023”)
$ koji se koristi pored C označava da će stupac ostati konstantan, ali će redovi biti promjenjivi.
Bilješka: Niz serijskih brojeva ovih datuma u Excelu počinje od 1 sv siječnja 1900., kao redni broj 1. Dakle, ako uzmemo u obzir datum 19-Nov-2023, on bi bio označen kao 45249 jer je 45248 dana nakon 1 sv siječnja 1900.
Čitati: Kako pretvoriti serijski broj u datum u programu Microsoft Excel
4] Označavanje praznika u Excelu
Budući da praznici mogu varirati ovisno o demografskim podacima, Excel nema ugrađene funkcije za isticanje državnih praznika. Stoga, da bismo identificirali praznike, moramo imati datume zabilježene u drugom stupcu i primijeniti countif formula za provjeru pojavljivanja datuma praznika u podatkovnoj tablici.
Na primjer, imamo nekoliko datuma spomenutih u stupcu D i popis praznika u stupcu G. Kroz uvjetno oblikovanje, možemo istaknuti praznike CRVENOM bojom u stupcu D, ako ih ima, uspoređujući ih s datumima praznika spomenutim u stupcu G pomoću dolje navedenih koraka:
- Otvori Novo pravilo dijaloški okvir i odaberite Upotrijebite formulu da biste odredili koje ćelije oblikovati pod, ispod Odaberite vrstu pravila.
- Unesite formulu =COUNTIF($G:$G,$D3)
- Kliknite na Format > Fill i odaberite boju CRVENA iz palete i kliknite na u redu .
The countif() provjerava pojavljivanje datuma navedenih u ćeliji G3 – G5 s datumima navedenim u stupcu D počevši od ćelije D3.
računalo sa sustavom Windows 10 neće se uključiti
5] Uvjetno oblikovanje datuma na temelju više uvjeta
Pretpostavimo radni list u kojem imamo pritužbe u vezi s raznim kućanskim predmetima. List sadrži datume kada su pritužbe podnesene te datume dospijeća i zatvaranja, prema potrebi.
Upotrebom uvjetnog oblikovanja, označimo pritužbe BIJELO za one koje su ZATVORENE, CRVENO za one kojima je prošao rok, ali još nisu zatvorene, i PLAVO za one kojima je rok još stigao.
Proces bi uključivao stvaranje dva pravila uvjetnog oblikovanja za iste podatke, jedno za one koji još nisu dosegli datum dospijeća i drugo za one koji su prekoračili datum dospijeća. Učiniti tako,
- Otvorite prozor za novo pravilo uvjetnog oblikovanja i odaberite Upotrijebite formulu da biste odredili koje ćelije oblikovati pod, ispod Odaberite vrstu pravila.
- Unesite dolje navedenu formulu
=AND($I10="",$H10>$K)
- Klik Format , odaberite PLAVU boju iz palete i kliknite OK.
- Stvorite drugu Novo pravilo s istom vrstom pravila i unesite dolje navedenu formulu
=AND($I10="",$H10<$K)
- Pritisnite Format, odaberite CRVENU boju iz palete i kliknite OK.
U ovoj formuli prvo provjeravamo je li stupac Datum zatvaranja PRAZAN ili nije kako bismo utvrdili da je pritužba još uvijek otvorena I zatim provjerite je li DATUM DOSPIJA veći od trenutnog datuma ili manji kako biste utvrdili približava li se datum dospijeća ($H10>$K ) ili nadmašen ($H10<$K ) . Datum u ćeliji K6 predstavlja trenutni datum.
6] Isticanje nadolazećih datuma i odgoda
Da bismo razumjeli kako možemo istaknuti kašnjenja s obzirom na datume u Excel tablici, razmotrimo primjer u kojem imamo popis dionica s njihovim nazivima i datumima isteka. Putem uvjetnog oblikovanja, ako trebamo označiti one koji trebaju isteći ZELENOM, a one koji su već istekli CRVENOM,
- Odaberite raspon podataka i otvorite Novo pravilo dijaloški okvir.
- Odaberite Oblikujte samo ćelije koje sadrže pod, ispod Odaberite vrstu pravila
- U Uredi opis pravila , izaberite Vrijednost ćelije , Veći od , =Sada()+30 u odgovarajućim padajućim izbornikom.
- Kliknite na Format, odaberite GREEN iz palete boja , i kliknite u redu .
- Ponovno otvorite a Novo pravilo i izabrati isto Vrsta pravila, kao gore.
- U Uredi opis pravila , izaberite Vrijednost ćelije , Manje od , =Sada() u odgovarajućim padajućim izbornikom.
- Kliknite na Format i odaberite CRVENA s palete boja i kliknite u redu .
Zaključak
Ovaj vodič ima za cilj sveobuhvatno pokriti opcije i funkcije koje se mogu koristiti za primjenu uvjetnog oblikovanja u radnim listovima programa Excel na temelju datuma. Može pomoći u razumijevanju kako primijeniti pravila oblikovanja za poboljšanje vizualizacije i analize podataka. Savladavanje uvjetnog oblikovanja također može pomoći u prepoznavanju trendova, provjeri rokova i postavljanju prioriteta zadataka, što pomaže u donošenju informiranih odluka.
Čitati : Kako da označite retke s datumima pomoću uvjetnog oblikovanja u Excelu
Kako mogu stvoriti uvjetno oblikovanje u Excelu na temelju datuma?
Najprije odaberite ćelije s datumom da biste stvorili uvjetno oblikovanje u Excelu na temelju datuma. Kliknite 'Početna' > 'Uvjetno oblikovanje' > 'Novo pravilo'. Odaberite 'Oblikuj ćelije koje sadrže', odaberite 'Datumi koji se događaju' pod pravilom, odaberite specifične datumske kriterije, a zatim postavite željeni format klikom na 'Oblikuj'. Prijavite se pritiskom na 'OK'. Ovo brzo ističe ključne datume u vašim podacima.
Kako mogu uvjetno oblikovati rokove u Excelu?
Najprije odaberite ćelije s datumima da biste uvjetno formatirali rokove u Excelu. Zatim kliknite na 'Uvjetno oblikovanje' na kartici 'Početna'. Odaberite 'Novo pravilo', odaberite 'Oblikuj samo ćelije koje sadrže', postavite pravilo na 'Vrijednost ćelije' i 'manje od' i unesite rok ili formulu. Kliknite 'Format', odaberite svoj stil oblikovanja i pritisnite 'OK' za primjenu.
