Pogojno oblikovanje v Excelu se lahko šteje za dragocen pripomoček, ki lahko pomaga vizualno izboljšati analizo podatkov in predstavitev v preglednici z uporabo določenega oblikovanja na podlagi vnaprej določenih pravil ali pogojev. Ta objava bo povedala, kako lahko nastavite pogojno oblikovanje v Excelu za datume. Vključili smo tudi podrobne primere, ki jih lahko preizkusite med branjem vodnika.
Kako nastaviti pogojno oblikovanje v Excelu za datume
Pogojno oblikovanje ne le pomaga poudariti celice z uporabo barvnih kod, ampak tudi oblikuje vrstice, stolpce in celice, sestavljene iz datumov, na način, ki se lahko šteje za vizualno smiselnega in privlačnega.
- Vnaprej določeno
- Uporabnik definiran
- Ustvarite pravilo oblikovanja po meri
- Označevanje praznikov v Excelu
- Pogojno oblikovanje datumov na podlagi več pogojev
- Označevanje prihajajočih datumov in zamud
Pazljivo pregledajte podrobnosti v Excelu. Ponuja številne možnosti, od katerih nekatere morda presegajo obseg tega vodnika.
1] Vnaprej določena datumska pravila pogojnega oblikovanja z datumi
Pogojno oblikovanje z datumi je lahko koristno pri delu z nizi podatkov, ki vključujejo informacije, povezane z datumom. Excel ponuja skoraj deset različnih možnosti za oblikovanje celic na podlagi datumov, kot je prikazano spodaj:
- Če želite formatirati celico z datumom kot vrsto podatkov, kliknite Domov > Pogojno oblikovanje > Pravila za označevanje celic > Datum, ki se pojavi
- Možnosti pogojnega oblikovanja lahko izberete v spustnih menijih v naslednjem oknu. Prvi spustni meni nam omogoča izbiro dnevnega obsega, v drugem pa lahko izbiramo možnosti oblikovanja.
- Kliknite v redu za uporabo izbranih pravil oblikovanja.
2] Uporabniško definirana datumska pravila pogojnega oblikovanja z datumi
Poleg nabora vnaprej določenih pravil Excel ponuja tudi možnost prilagajanja teh pravil glede na zahteve uporabnikov. Obstaja več načinov za nastavitev prilagoditev, kot je prikazano spodaj:
kako poslati ctrl alt del na oddaljeno namizje
Uporaba oblike po meri
- Odprite Datum poteka formatirajte okno tako, da sledite zgoraj navedenim korakom.
- Ko izberete obseg dneva, izberite Oblika po meri iz spustnega menija možnosti oblikovanja.
- The Okno Oblikuj celice se bo pojavilo zaradi zgoraj navedenega, kjer lahko izberemo barvo, obrobo, pisavo in učinke po lastni izbiri in nato kliknemo V redu, da uveljavimo spremembe.
Ustvari nova pravila
Ta metoda vključuje ustvarjanje povsem novega pravila za pogojno oblikovanje, za razliko od zgornje metode, pri kateri lahko izbiramo med nekaj vnaprej določenimi pravili in ustvarimo samo obliko po meri. Ustvarjanje novega pravila vključuje:
- Izbira celic, za katere naj se uporabi oblikovanje.
- Na domov Zavihek, klik Pogojno oblikovanje > Novo pravilo , ki naj bi odprla Novo okno pravila oblikovanja.
- Na omenjenem oknu lahko izbiramo med razpoložljivimi Vrste pravil , čemur sledi urejanje opisa pravila za nastavitev meril za pogojno oblikovanje.
- Ko je dokončan, Oblika lahko kliknete gumb v spodnjem desnem kotu okna, da izberete oblikovanje za omenjeno pravilo.
- Na Oblikuj celice okno, ki se odpre, lahko po izbiri izberemo pisavo, obrobo, barvo celice, učinke itd. in v tem oknu enkrat kliknemo V redu, čemur sledi Novo pravilo oblikovanja Okno za uveljavitev sprememb.
Opomba: Pravila oblikovanja se lahko uporabljajo za ustvarjanje in uporabo več kot enega pravila za isti nabor podatkov na podlagi različnih pogojev/meril.
Preberite: Datumski filter Excel ne razvršča po mesecih
3] Kako ustvariti pravilo oblikovanja po meri z datumi
V zgornjem primeru smo krmarili po korakih za ustvarjanje novega pravila za pogojno oblikovanje na podlagi že podanih kriterijev. Excel omogoča tudi uporabo omenjenih pravil oblikovanja na uporabniško določenih kriterijih.
Denimo, da moramo poudariti celice z datumi, ki so starejši od 30 dni ali 60 dni. V tem primeru je mogoče isto izvesti tudi z uporabo datumskih formul in funkcij, ki so na voljo v Excelu, kot je prikazano spodaj:
A] Označite trenutne in starejše datume
Spodaj Izberite novo pravilo v možnosti Pogojno oblikovanje izberite S formulo določite, katero celico želite oblikovati , tip:
=$H4=DANES() poudariti današnji dan
=$H4>DANES() za poudarjanje prihodnjih dni
=$H4
=$H4<=(DANES()-30) za poudarjanje datumov, ki so starejši od 30 dni.
=$H4<=(DANES()-60) za poudarjanje datumov, ki so starejši od 60 dni.
lsass exe visoko cpu
Zgornja formula preveri izbrano časovno obdobje za trenutni datum in ko najde ujemanje, spremeni barvo ozadja v vijolično, kot je izbrano v možnosti Oblika. The $ uporablja poleg H pomeni, da bo stolpec ostal konstanten, vrstice pa spremenljive.
- Ko je Opis pravila je omenjeno, lahko izberete pisavo, barve in učinke s klikom na gumb Oblika.
- Kliknite na v redu v oknu Oblikuj celice, ki mu sledi isto v oknu Novo pravilo, da bodo spremembe veljale.
B] Označite na podlagi uporabniško določenega časovnega obdobja
Podobne metode je mogoče uporabiti za oblikovanje časovnih obdobij, ki jih določijo uporabniki. Na primer, če moramo poudariti datume, ki so starejši od 30 dni, vendar manj kot 45 dni, lahko funkcijo TODAY() uporabimo v kombinaciji z logičnim operatorjem, kot je IN, da podamo merila, ki vključujejo dva pogoja:
- Izberite celice, za katere je treba uporabiti oblikovanje, in odprite okno Novo pravilo.
- V Uredite opis pravila polje vnesite spodnjo formulo,
=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))
Zgornja formula uporablja IN da z rumeno označite datume, ki so starejši od 30 dni, vendar manj kot 45 dni za celoten izbor, začenši s celico št. H2.
C] Poudarjanje vikendov
V istem obsegu lahko z uporabo formule WEEKDAY() označimo tudi datume, ki so ob koncu tedna:
- Izberite celice, za katere je treba uporabiti oblikovanje, in odprite okno Novo pravilo.
- V Uredite opis pravila polje vnesite spodnjo formulo,
=WEEKDAY($H2, 2)>5
The sintaksa formule Weekday lahko predstavimo kot WEEKDAY(serial_number, [return_type]), pri čemer serial_number predstavlja številko celice datuma, ki ga poskušamo preveriti (SA1 v tem primeru).
The povratne_vrste parameter predstavlja vrsto tedna od ponedeljka(1) do nedelje(7), pri čemer je začetni dan tedna ponedeljek (predstavljen z 1), zadnji dan v tednu pa nedelja (predstavljen z 7).
Vendar so return_types neobvezni v formuli in so predstavljeni v tretjih oklepajih. >5 se uporablja za označevanje sobote(6) in nedelje(7) za podano vrsto vračila.
D] Označevanje vrstic na podlagi določenega datuma
Ta možnost je lahko priročna pri delu z velikimi količinami podatkov, kjer je treba poudariti določen datum. Enako lahko dosežete z določitvijo številčne vrednosti datuma ali uporabo DATEVALUE() funkcijo. Numerično vrednost je treba določiti na začetku, da se uporabi pogojno oblikovanje za določen datum, saj Excel trajno shranjuje datume kot zaporedne serijske številke. Če želite najti številčno vrednost določenega datuma,
- Z desno miškino tipko kliknite celico z datumom, kliknite na Oblikuj celice > Število, in nato izberite Splošno . V celici bi bila prikazana 5-mestna številska vrednost, ki si jo lahko zabeležite za prihodnjo uporabo.
- Kliknite Prekliči, ko je številka zapisana, saj nam ni treba spremeniti oblike zapisa datuma za celico.
- Kliknite na Pogojno oblikovanje>Novo pravilo> Izberite novo pravilo > S formulo določite, katero celico želite oblikovati in vnesite naslednjo formulo,
=$G5=45249
pretvori serijo v exe -
- Uporabljati DATEVALUE() funkcijo, lahko enako storite tudi z vnosom spodnje formule,
=$G5=DATEVALUE(“11/19/2023”)
$ poleg C pomeni, da bo stolpec ostal konstanten, vrstice pa spremenljive.
Opomba: Zaporedje serijskih številk teh datumov v Excelu se začne z 1 st januar 1900, kot zaporedna številka 1. Če torej upoštevamo datum 19. november 2023, bi bil oštevilčen kot 45249, saj je 45248 dni po 1. st januarja 1900.
Preberite: Kako pretvoriti serijsko številko v datum v programu Microsoft Excel
4] Označevanje praznikov v Excelu
Ker se prazniki lahko razlikujejo glede na demografske podatke, Excel nima vgrajenih funkcij za poudarjanje državnih praznikov. Če želimo torej prepoznati praznike, moramo imeti datume zabeležene v drugem stolpcu in uporabiti countif formulo za preverjanje, ali se na podatkovnem listu pojavljajo praznični datumi.
Na primer, imamo nekaj datumov, omenjenih v stolpcu D, in seznam praznikov v stolpcu G. S pogojnim oblikovanjem lahko označimo praznike z RDEČO barvo v stolpcu D, če obstajajo, tako da jih primerjamo z datumi praznikov, omenjenimi v stolpcu G. z uporabo spodaj navedenih korakov:
- Odprite Novo pravilo pogovorno okno in izberite S formulo določite, katere celice želite oblikovati Spodaj Izberite Vrsta pravila.
- Vnesite formulo =COUNTIF($G:$G,$D3)
- Kliknite na Oblika > Izpolni in izberite barvo RDEČA iz palete in kliknite v redu .
The štetje() preveri pojavljanje datumov, omenjenih v celici G3 – G5, z datumi, omenjenimi v stolpcu D, začenši s celico D3.
5] Pogojno oblikovanje datumov na podlagi več pogojev
Predpostavimo delovni list, na katerem imamo pritožbe glede različnih gospodinjskih predmetov. Na listu so navedeni datumi, ko so bile vložene pritožbe, ter datumi zapadlosti in zaključka, kot je primerno.
S pogojnim oblikovanjem označimo reklamacije z BELO za tiste, ki so bile ZAKLJUČENE, z RDEČO za tiste, ki jim je rok potekel, vendar še niso zaprte, in z MODRO za tiste, ki so še dosegle rok.
Postopek bi vključeval ustvarjanje dveh pravil pogojnega oblikovanja za iste podatke, enega za tiste, ki še niso dosegli roka, in drugega za tiste, ki so rok presegli. Narediti tako,
- Odprite okno za pogojno oblikovanje Novo pravilo in izberite S formulo določite, katere celice želite oblikovati Spodaj Izberite vrsto pravila.
- Vnesite spodnjo formulo
=AND($I10="",$H10>$K)
- Kliknite Oblika , izberite MODRO barvo iz palete in kliknite OK.
- Ustvari drugo Novo pravilo z isto vrsto pravila in vnesite spodnjo formulo
=AND($I10="",$H10<$K)
spremenite aktivno ime omrežja windows 10
- Kliknite Oblika, izberite RDEČO barvo na paleti in kliknite V redu.
V tej formuli najprej preverimo, ali je stolpec Datum zaprtja PRAZEN ali ne, da ugotovimo, ali je pritožba še vedno odprta. IN nato preverite, ali je ROK zapadlosti daljši od trenutnega datuma ali nižji, da ugotovite, ali se rok bliža ($H10>$K ) ali presežen ($H10<$K ) . Datum v celici K6 predstavlja trenutni datum.
6] Označevanje prihajajočih datumov in zamud
Da bi razumeli, kako lahko poudarimo zamude glede na datume v Excelovem listu, razmislimo o primeru, kjer imamo seznam delnic z njihovimi imeni in datumi poteka. S pogojnim oblikovanjem, če moramo tiste, ki jim poteče, označiti z ZELENO in tiste, ki so že potekle, z RDEČO,
- Izberite obseg podatkov in odprite Novo pravilo pogovorno okno.
- Izberite Oblikujte le celice, ki vsebujejo Spodaj Izberite Vrsta pravila
- notri Uredi opis pravila , izberite Vrednost celice , Večji kot , =Zdaj()+30 v ustreznih spustnih menijih.
- Kliknite na oblika, iz barvne palete izberite ZELENO , in kliknite v redu .
- Ponovno odprite a Novo pravilo in izberi isto Vrsta pravila, kot zgoraj.
- notri Uredi opis pravila , izberite Vrednost celice , Manj kot , =Zdaj() v ustreznih spustnih menijih.
- Kliknite na Oblika in izberite RDEČA iz barvne palete in kliknite v redu .
Zaključek
Namen te vadnice je celovito pokriti možnosti in funkcije, ki jih je mogoče uporabiti za uporabo pogojnega oblikovanja v Excelovih delovnih listih na podlagi datumov. Lahko pomaga razumeti, kako uporabiti pravila oblikovanja za izboljšanje vizualizacije in analize podatkov. Obvladovanje pogojnega oblikovanja lahko pomaga tudi pri prepoznavanju trendov, preverjanju rokov in določanju prednostnih nalog nalog, kar pomaga pri sprejemanju premišljenih odločitev.
Preberi : Kako označite vrstice z datumi s pogojnim oblikovanjem v Excelu
Kako v Excelu ustvarim pogojno oblikovanje na podlagi datumov?
Najprej izberite datumske celice, da ustvarite pogojno oblikovanje v Excelu na podlagi datumov. Kliknite »Domov« > »Pogojno oblikovanje« > »Novo pravilo«. Izberite »Oblikuj celice, ki vsebujejo«, pod pravilom izberite »Datumi, ki se pojavljajo«, izberite posebne datumske kriterije in nato nastavite želeno obliko s klikom na »Oblika«. Prijavite se tako, da pritisnete »V redu«. To hitro poudari ključne datume v vaših podatkih.
Kako pogojno oblikujem datume zapadlosti v Excelu?
Najprej izberite svoje celice z datumi za pogojno oblikovanje datumov zapadlosti v Excelu. Nato na zavihku »Domov« kliknite »Pogojno oblikovanje«. Izberite »Novo pravilo«, izberite »Oblikuj samo celice, ki vsebujejo«, nastavite pravilo na »Vrednost celice« in »manj kot« ter vnesite rok ali formulo. Kliknite »Oblika«, izberite svoj slog oblikovanja in pritisnite »V redu«, da ga uporabite.
