Kā iestatīt nosacījumu formatējumu programmā Excel datumiem

Nosacījuma formatēšana programmā Excel var uzskatīt par vērtīgu utilītu, kas var palīdzēt vizuāli uzlabot datu analīzi un prezentāciju izklājlapā, izmantojot īpašu formatējumu, pamatojoties uz iepriekš definētiem noteikumiem vai nosacījumiem. Šajā rakstā tiks parādīts, kā programmā Excel iestatīt datumu nosacītu formatējumu. Mēs esam iekļāvuši arī detalizētus piemērus, kurus varat izmēģināt, lasot ceļvedi.

Kā iestatīt nosacījumu formatējumu programmā Excel datumiem

Nosacījuma formatēšana ne tikai palīdz izcelt šūnas, izmantojot krāsu kodus, bet arī formatē rindas, kolonnas un šūnas, kas sastāv no datumiem tā, lai to varētu uzskatīt par vizuāli nozīmīgu un pievilcīgu.

1. Iepriekš noteikts
2. Lietotājs definēts
3. Izveidojiet pielāgotu formatēšanas kārtulu
4. Brīvdienu izcelšana programmā Excel
5. Datumu nosacīta formatēšana, pamatojoties uz vairākiem nosacījumiem
6. Gaidāmo datumu un kavējumu izcelšana

Noteikti rūpīgi izpētiet informāciju programmā Excel. Tā piedāvā daudzas iespējas, no kurām dažas var būt ārpus šīs rokasgrāmatas darbības jomas.

1] Iepriekš definēti nosacījumformatēšanas datuma noteikumi ar datumiem

Nosacījuma formatēšana ar datumiem var būt noderīga, strādājot ar datu kopām, kas ietver ar datumu saistītu informāciju. Programmā Excel ir pieejamas gandrīz desmit dažādas iespējas šūnu formatēšanai, pamatojoties uz datumiem, kā parādīts tālāk.

• Lai formatētu šūnu ar datumu kā datu tipu, noklikšķiniet uz Sākums > Nosacījuma formatēšana > Iezīmējiet šūnu kārtulas > Datums, kurā notiek

• Nosacītā formatējuma opcijas var izvēlēties no nākamā loga nolaižamajām izvēlnēm. Pirmajā nolaižamajā izvēlnē mēs varam izvēlēties dienu diapazonu, bet formatēšanas opcijas var izvēlēties no otrās.

• Klikšķis labi lai piemērotu izvēlētos formatēšanas noteikumus.

2] Lietotāja definēti nosacījumformatēšanas datuma noteikumi ar datumiem

Papildus iepriekš definētu kārtulu kopai programma Excel nodrošina arī iespēju pielāgot šos noteikumus, pamatojoties uz lietotāja prasībām. Ir vairāki veidi, kā iestatīt pielāgojumus, kā parādīts tālāk.

Pielāgota formāta izmantošana

1. Atveriet Notiek datums formatēšanas logu, veicot iepriekš minētās darbības.
2. Pēc dienas diapazona izvēles atlasiet Pielāgots formāts no formatēšanas opciju nolaižamās izvēlnes.
3. The Formatēt šūnu logu tiks parādīts iepriekš minēto iemeslu dēļ, kur mēs varam izvēlēties krāsu, apmali, fontu un efektus un pēc tam noklikšķiniet uz Labi, lai piemērotu izmaiņas.

Izveidojiet jaunus noteikumus

Šī metode ietver pilnīgi jaunas nosacījumformatēšanas kārtulas izveidi, atšķirībā no iepriekš minētās metodes, kurā mēs varam izvēlēties no dažiem iepriekš definētiem noteikumiem un izveidot tikai pielāgotu formātu. Jauna noteikuma izveide ietver:

• Atlasot šūnas, kurām jāpiemēro formatējums.
• Uz Mājas Cilne, noklikšķiniet Nosacījuma formatēšana > Jauna kārtula , kam vajadzētu atvērt Jauns formatēšanas kārtulas logs.

• Minētajā logā mēs varam izvēlēties kādu no pieejamajiem Noteikumu veidi , kam seko kārtulas apraksta rediģēšana, lai iestatītu nosacījumformatēšanas kritērijus.
• Kad tas ir pabeigts, Formāts pogas var noklikšķināt loga apakšējā labajā stūrī, lai izvēlētos formatējumu minētajam noteikumam.
• Uz Formatēt šūnas Atvērtajā logā mēs varam izvēlēties fontu, apmali, šūnu krāsu, efektus utt. un vienreiz šajā logā noklikšķināt uz Labi, kam seko Jauns formatēšanas noteikums Izmaiņu piemērošanas logs.

Piezīme: Formatēšanas kārtulas var izmantot, lai izveidotu un lietotu vairāk nekā vienu noteikumu vienai un tai pašai datu kopai, pamatojoties uz dažādiem nosacījumiem/kritērijiem.

Lasīt: Excel datumu filtrs negrupē pēc mēneša

šim failam nav saistīta programma šīs darbības veikšanai

3] Kā izveidot pielāgotu formatēšanas kārtulu ar datumiem

Iepriekš minētajā piemērā mēs veicām darbības, lai izveidotu jaunu nosacījumu formatēšanas kārtulu pamatojoties uz jau sniegtajiem kritērijiem. Excel arī ļauj piemērot minētos formatēšanas noteikumus uz lietotāja definētiem kritērijiem.

Piemēram, pieņemsim, ka mums ir jāizceļ šūnas ar datumiem, kas ir vecāki par 30 dienām vai 60 dienām. Tādā gadījumā to pašu var ieviest arī, izmantojot programmā Excel pieejamās datuma formulas un funkcijas, kā parādīts tālāk:

A] Iezīmējiet pašreizējos un vecākus datumus

Zem Atlasiet jaunu kārtulu opcijā Nosacītā formatēšana izvēlieties Izmantojiet formulu, lai noteiktu, kuru šūnu formatēt , ierakstiet:

=$H4=ŠODIEN() lai izceltu mūsdienu

=$H4>ŠODIEN() lai izceltu nākamās dienas

=$H4<ŠODIEN() lai izceltu iepriekšējās dienas

=$H4<=(ŠODIEN()-30) lai izceltu datumus, kas ir vecāki par 30 dienām.

=$H4<=(ŠODIEN()-60) lai izceltu datumus, kas ir vecāki par 60 dienām.

Iepriekš minētā formula pārbauda atlasīto datumu diapazonu pašreizējam datumam, un, kad atbilstība ir atrasta, mainiet fona krāsu uz Purpursarkanu, kā atlasīts no opcijas Formatēt. The $ izmanto blakus H nozīmē, ka kolonna paliktu nemainīga, bet rindas būtu mainīgas.

šo datoru nevar jaunināt uz Windows 10

• Reiz Noteikuma apraksts ir minēts, fontu, krāsas un efektus var izvēlēties, noklikšķinot uz pogas Formatēt.
• Klikšķiniet uz labi logā Format Cells, kam seko tas pats logā New Rule, lai izmaiņas būtu efektīvas.

B] Izcelt, pamatojoties uz lietotāja definētu datumu diapazonu

Līdzīgas metodes var izmantot lietotāju norādīto datumu diapazonu formatēšanai. Piemēram, ja mums ir jāizceļ datumi, kas ir vecāki par 30 dienām, bet ir mazāki par 45 dienām, funkciju TODAY() var izmantot kopā ar loģisko operatoru, piemēram, UN, lai norādītu kritērijus, kas ietver divus nosacījumus:

• Atlasiet šūnas, kurām jāpiemēro formatējums, un atveriet logu New Rule.
• Iekš Rediģējiet kārtulas aprakstu laukā ievadiet zemāk esošo formulu,

=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))

Iepriekš minētā formula izmanto UN operators, lai dzeltenā krāsā iezīmētu datumus, kas ir vecāki par 30 dienām, bet ir mazāki par 45 dienām visai atlasei, sākot no šūnas Nr. H2.

C] Nedēļas nogales izcelšana

Tajā pašā diapazonā, izmantojot WEEKDAY() formulu, var izcelt arī datumus, kas ietilpst nedēļas nogalēs:

• Atlasiet šūnas, kurām jāpiemēro formatējums, un atveriet logu New Rule.
• Iekš Rediģējiet kārtulas aprakstu laukā ievadiet zemāk esošo formulu,

=WEEKDAY($H2, 2)>5

The sintakse no nedēļas dienas formulas var attēlot kā WEEKDAY(sērijas_numurs, [atgriešanas_veids]), kur sērijas_numurs apzīmē tā datuma šūnas numuru, kuru mēs cenšamies pārbaudīt (šajā gadījumā SA1).

The atgriešanas_veidi parametrs apzīmē veidu nedēļai, kas sākas no pirmdienas (1) līdz svētdienai (7), ņemot vērā nedēļas sākuma dienu kā pirmdienu (attēlo ar 1) un pēdējo nedēļas dienu kā svētdienu (apzīmē ar 7).

Tomēr atgriešanas_veidi formulā nav obligāti un ir attēloti trešajās iekavās. >5 tiek izmantots, lai norādītajam atgriešanas veidam iezīmētu sestdienu(6) un svētdienu(7).

D] Rindu izcelšana, pamatojoties uz noteiktu datumu

Šī opcija var būt ērta, strādājot ar lielu datu apjomu, kad ir jāizceļ konkrēts datums. To pašu var panākt, nosakot datuma skaitlisko vērtību vai izmantojot DATEVALUE() funkciju. Sākotnēji ir jānosaka skaitliskā vērtība, lai konkrētam datumam piemērotu nosacījumu formatējumu, jo programma Excel pastāvīgi saglabā datumus kā secīgus sērijas numurus. Lai atrastu konkrēta datuma skaitlisko vērtību,

• Ar peles labo pogu noklikšķiniet uz šūnas ar datumu, noklikšķiniet uz Formatēt šūnas > Skaitlis, un tad izvēlieties Ģenerālis . Šūnā tiks parādīta 5 ciparu skaitliskā vērtība, ko var atzīmēt turpmākai atsaucei.

• Noklikšķiniet uz Atcelt, kad numurs ir atzīmēts, jo mums nav jāmaina šūnas datuma formāts.
• Klikšķiniet uz Nosacījuma formatēšana> Jauns noteikums> Atlasiet jaunu kārtulu > Izmantojiet formulu, lai noteiktu, kuru šūnu formatēt un ierakstiet šādu formulu,

=$G5=45249

• Izmantojot DATEVALUE() funkciju, to pašu var izdarīt, ievadot tālāk minēto formulu,

=$G5=DATEVALUE(“11/19/2023”)

$, kas tiek izmantots blakus C, nozīmē, ka kolonna paliktu nemainīga, bet rindas būtu mainīgas.

Piezīme: Šo datumu sērijas numuru secība programmā Excel sākas ar 1 ^st 1900. gada janvāris, kā sērijas numurs 1. Tādējādi, ja ņemam vērā datumu 2023. gada 19. novembris, tas tiktu numurēts kā 45249, jo tas ir 45 248 dienas pēc 1. ^st 1900. gada janvāris.

Lasīt: Kā pārvērst sērijas numuru uz datumu programmā Microsoft Excel

4] Brīvdienu izcelšana programmā Excel

Tā kā brīvdienas var atšķirties atkarībā no demogrāfiskajiem datiem, programmā Excel nav iebūvētu funkciju valsts svētku dienu izcelšanai. Tādējādi, lai noteiktu brīvdienas, datumi ir jāieraksta citā kolonnā un jāpiemēro grāfist formula, lai pārbaudītu brīvdienu datumu iestāšanos datu lapā.

Piemēram, mums ir daži datumi, kas minēti D slejā un svētku dienu saraksts G ailē. Izmantojot nosacītu formatējumu, mēs varam izcelt svētkus SARkanā slejā D, ja tādi ir, salīdzinot tos ar svētku datumiem, kas minēti G slejā. izmantojot tālāk minētās darbības:

• Atveriet Jauns noteikums dialoglodziņš un izvēlieties Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt zem Atlasiet Noteikuma veids.
• Ievadiet formulu =COUNTIF($G:$G,$D3)
• Klikšķiniet uz Formāts > Aizpildīt un izvēlieties krāsu SARKANS no paletes un noklikšķiniet uz labi .

The countif() pārbauda šūnā G3 – G5 minēto datumu iestāšanos ar D ailē minētajiem datumiem, sākot no šūnas D3.

5] Datumu nosacīta formatēšana, pamatojoties uz vairākiem nosacījumiem

Pieņemsim darblapu, kurā mums ir iesniegtas sūdzības par dažādiem sadzīves priekšmetiem. Lapā ir norādīti datumi, kad sūdzības ir iesniegtas, kā arī attiecīgie termiņi un beigu datumi.

Izmantojot nosacīto formatējumu, atzīmēsim BALTĀS sūdzības tām, kuras ir SLĒGTAS, SARKANU — tām, kurām ir pagājis termiņš, bet vēl nav aizvērtas, un ZILU — tām, kurām vēl ir pienācis termiņš.

ms virtuālais cd rom vadības panelis

Šajā procesā vieniem un tiem pašiem datiem ir jāizveido divi nosacījumformatēšanas kārtulas — viens tiem, kas vēl nav sasnieguši izpildes datumu, un otrs tiem, kuriem termiņš ir pārsnieguši. Darīt tā,

• Atveriet nosacījumformatēšanas logu Jauns noteikums un atlasiet Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt zem Atlasiet kārtulas veidu.
• Ievadiet tālāk minēto formulu

=AND($I10="",$H10>$K)

• Klikšķis Formāts , atlasiet no paletes ZILO krāsu un noklikšķiniet uz Labi.
• Izveidojiet citu Jauns noteikums ar tādu pašu noteikumu veidu un ievadiet tālāk minēto formulu

=AND($I10="",$H10<$K)

• Noklikšķiniet uz Formatēt, paletes atlasiet RED krāsu un noklikšķiniet uz Labi.

Šajā formulā mēs vispirms pārbaudām, vai kolonna Slēgšanas datums ir tukša, lai noteiktu, vai sūdzība joprojām ir atvērta. UN pēc tam pārbaudiet, vai DUE DATE ir lielāks par pašreizējo datumu vai mazāks, lai noteiktu, vai termiņš tuvojas ($H10>$K ) vai pārsniegts ($H10<$K ) . Datums šūnā K6 apzīmē pašreizējo datumu.

6] Gaidāmo datumu un kavējumu izcelšana

Lai saprastu, kā mēs varam izcelt aizkavēšanos attiecībā uz datumiem Excel lapā, apskatīsim piemēru, kurā mums ir krājumu saraksts ar to nosaukumiem un derīguma termiņiem. Izmantojot nosacītu formatējumu, ja mums ir jāatzīmē tie, kuriem beidzas derīguma termiņš, ZAĻĀ krāsā un tie, kuriem jau ir beidzies derīguma termiņš, ir jāatzīmē SARKANĀ,

• Atlasiet datu diapazonu un atveriet Jauns noteikums dialoglodziņš.
• Izvēlieties Formatējiet tikai šūnas, kas satur zem Atlasiet Noteikuma veids
• In Rediģēt kārtulas aprakstu , izvēlieties Šūnas vērtība , Lielāks nekā , =Tagad()+30 attiecīgajos nolaižamajos izvēlnēs.
• Klikšķiniet uz Formāts, krāsu paletē atlasiet ZAĻU , un noklikšķiniet labi .

• Atkal atveriet a Jauns noteikums un izvēlies to pašu Noteikuma veids, kā iepriekš.
• In Rediģēt kārtulas aprakstu , izvēlieties Šūnas vērtība , Mazāk nekā , =Tagad () attiecīgajos nolaižamajos izvēlnēs.
• Klikšķiniet uz Formāts un atlasiet SARKANS no krāsu paletes un noklikšķiniet uz labi .

Secinājums

Šīs apmācības mērķis ir vispusīgi aptvert opcijas un funkcijas, ko var izmantot, lai lietotu nosacīto formatējumu Excel darblapās, pamatojoties uz datumiem. Tas var palīdzēt saprast, kā lietot formatēšanas noteikumus, lai uzlabotu datu vizualizāciju un analīzi. Nosacītā formatējuma apgūšana var arī palīdzēt noteikt tendences, pārbaudīt termiņus un noteikt uzdevumu prioritātes, palīdzot pieņemt pārdomātus lēmumus.

Lasīt : Kā iezīmējiet rindas ar datumiem, izmantojot nosacīto formatējumu programmā Excel

Kā programmā Excel izveidot nosacījumu formatējumu, pamatojoties uz datumiem?

Vispirms atlasiet datuma šūnas, lai programmā Excel izveidotu nosacītu formatējumu, pamatojoties uz datumiem. Noklikšķiniet uz 'Sākums' > 'Nosacījuma formatēšana' > 'Jauns noteikums'. Izvēlieties “Formatēt šūnas, kas satur”, zem kārtulas atlasiet “Datumi, kas radušies”, izvēlieties konkrētu datuma kritēriju un pēc tam iestatiet vēlamo formātu, noklikšķinot uz “Formatēt”. Piesakieties, nospiežot “OK”. Tas ātri izceļ galvenos datumus jūsu datos.

Kā nosacīti formatēt izpildes datumus programmā Excel?

Vispirms atlasiet šūnas ar datumiem, lai programmā Excel nosacīti formatētu izpildes datumus. Pēc tam cilnē Sākums noklikšķiniet uz Nosacījuma formatēšana. Izvēlieties “Jauna kārtula”, atlasiet “Formatēt tikai šūnas, kas satur”, iestatiet kārtulai “Šūnas vērtība” un “mazāks par” un ievadiet izpildes datumu vai formulu. Noklikšķiniet uz 'Formatēt', izvēlieties savu formatējuma stilu un nospiediet 'OK', lai lietotu.

• Vairāk