Kā lietot Excel pielāgotos nosacīto formatēšanas noteikumus datumiem

Ar nosacījumu formatējumu pievienošana šūnai programmā Excel jūs varat izmantot dažādas formatēšanas opcijas, piemēram, krāsu, kad šajā šūnā esošie dati atbilst jūsu iestatītajiem nosacījumiem.

Vieglāk izmantojot nosacīto formatējumu, ir pieejamas iepriekš iestatītas opcijas, kas aptver visbiežāk lietotās situācijas, piemēram:

• Datumi
• Dublikātu dati
• Vērtības virs vai zem vidējās vērtības šūnu diapazonā

Datumu gadījumā iepriekš iestatītās iespējas ļauj precīzi pārbaudīt savus datus par datumiem, kas ir tuvu pašreizējam datumam, piemēram, vakar, rīt, pagājušajā nedēļā vai nākamajā mēnesī.

Ja vēlaties pārbaudīt datumus, kas neatbilst uzskaitītajām iespējām, tomēr varat pielāgot nosacīto formatējumu, pievienojot savu formulu, izmantojot vienu vai vairākas Excel datēšanas funkcijas.

01 no 06

Pārbaude par 30, 60 un 90 dienu pagājušā termiņa datumiem

Ar nosacījumu formātu pielāgošana, izmantojot formulas, tiek veikta, nosakot jaunu noteikumu, ko Excel ievēro, novērtējot šūnā esošos datus.

Šajā soli pa solim aprakstītais piemērs nosaka trīs jaunus nosacījuma formatēšanas noteikumus, kas pārbaudīs, vai atlasītajā šūnu diapazonā ievadītie datumi ir pagājuši 30 dienas, pēdējās 60 dienās vai pēdējās 90 dienās.

Formulas, kas tiek izmantotas šajos noteikumos, atņem noteiktu skaitu dienu no pašreizējā datuma šūnās no C1 līdz C4.

Pašreizējais datums tiek aprēķināts, izmantojot TODAY funkciju .

Lai šī apmācība darbotos, jums jāievada datumi, kas atbilst iepriekšminētajiem parametriem.

Piezīme . Excel izmanto nosacījumu formatējumu kārtībā no augšas uz leju, ka noteikumi ir uzskaitīti dialoglodziņā Nosacījumu formatēšanas noteikumu pārvaldnieks, kā redzams attēlā, kas atrodas augšpusē.

Kaut arī dažām šūnām var piemērot vairākus noteikumus, pirmais noteikums, kas atbilst nosacījumam, tiek piemērots šūnām.

02 no 06

Pārbaude par datumiem, kas pagājušas 30 dienas

1. Iezīmējiet šūnas no C1 līdz C4, lai tos atlasītu. Tas ir diapazons, uz kuru mēs piemērojam nosacījuma formatēšanas noteikumus
2. Lentas izvēlnes cilnē Sākums .
3. Noklikšķiniet uz ikonas Nosacījuma formatēšana, lai atvērtu nolaižamo izvēlni.
4. Atlasiet opciju Jauna kārtula . Tas atver dialoglodziņu Jauna formatēšanas kārtula.
5. Noklikšķiniet uz Lietot formulu, lai noteiktu, kuras šūnas formatēšanas opcijai.
6. Ievadiet šādu formulu lodziņā zem formāta vērtības, kur šī vērtība ir taisnība, dialoglodziņa apakšējā pusē:
   = TODAY () - C1> 30
   Šī formula pārbauda, ​​vai datumi šūnās no C1 līdz C4 ir pagājušas vairāk nekā 30 dienas
7. Noklikšķiniet uz pogas Formatēt , lai atvērtu dialoglodziņu Format Cells.
8. Noklikšķiniet uz cilnes Aizpildīt, lai skatītu fona piepildīšanas krāsu opcijas.
9. Atlasiet fona aizpildījuma krāsu, lai atbilstu šajā apmācībā sniegtajam piemēram, izvēlieties gaiši zaļu.
10. Noklikšķiniet uz cilnes Fonts, lai skatītu fonta formāta opcijas
11. Zem krāsu sadaļas iestatiet fonta krāsu uz baltu, lai atbilstu šai apmācībai.
12. Divreiz noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu un atgrieztos darblapā.
13. Šūnu C1 līdz C4 fona krāsa mainīsies uz atlasītās krāsas krāsu, lai gan šūnās nav datu.

03 no 06

Noteikumu pievienošana datumiem, kas ilgst vairāk nekā 60 dienas

Izmantojot pārvaldes noteikumu opciju

Tā vietā, lai atkārtotu visas iepriekš minētās darbības, lai pievienotu nākamos divus noteikumus, mēs izmantosim iespēju Pārvaldīt kārtulas, kas ļaus mums vienlaikus pievienot papildu noteikumus.

1. Ja nepieciešams, iezīmējiet šūnas no C1 līdz C4.
2. Noklikšķiniet uz lentes izvēlnes cilnes Sākums .
3. Noklikšķiniet uz ikonas Nosacījuma formatēšana, lai atvērtu nolaižamo izvēlni.
4. Izvēlieties opciju Pārvaldības noteikumi, lai atvērtu dialoglodziņu Nosacījumu formatēšanas noteikumu pārvaldnieks.
5. Dialoglodziņa augšējā kreisajā stūrī noklikšķiniet uz opcijas Jauna kārtula
6. Noklikšķiniet uz Lietot formulu, lai noteiktu, kuras šūnas formatē opciju no saraksta dialoglodziņa augšdaļā.
7. Ievadiet šādu formulu lodziņā zem formāta vērtības, kur šī vērtība ir taisnība, dialoglodziņa apakšējā pusē:
   = TODAY () - C1> 60

   Šī formula pārbauda, ​​vai datumi šūnās no C1 līdz C4 ir vairāk nekā 60 dienas agrāk.

8. Noklikšķiniet uz pogas Formatēt , lai atvērtu dialoglodziņu Format Cells.
9. Noklikšķiniet uz cilnes Aizpildīt, lai skatītu fona piepildīšanas krāsu opcijas.
10. Atlasiet fona piepildījuma krāsu; lai saskaņotu piemēru šajā apmācībā, izvēlieties dzeltenu.
11. Divreiz noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu un atgrieztos dialoglodziņā Nosacījumu formatēšanas noteikumu pārvaldnieks.

04 no 06

Noteikumu pievienošana datumiem, kas ilgāk par 90 dienām

1. Lai pievienotu jaunu kārtulu, atkārtojiet no 5. līdz 7. darbību.
2. Formulas lietošanai:
   = TODAY () - C1> 90
3. Atlasiet fona piepildījuma krāsu; lai saskaņotu piemēru šajā apmācībā, izvēlieties apelsīnu.
4. Iestatiet fonta krāsu uz baltu, lai atbilstu šai apmācībai.
5. Divreiz noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu un atgrieztos dialoglodziņā Nosacījumu formatēšanas noteikumu pārvaldnieks
6. Vēlreiz noklikšķiniet uz Labi , lai aizvērtu šo dialoglodziņu un atgrieztos darblapā .
7. Šūnu no C1 līdz C4 fona krāsa mainīsies līdz pēdējai izvēlētajai aizpildījuma krāsai.

05 no 06

Nosacītu formatēšanas noteikumu testēšana

Kā redzams apmācības attēlā, mēs varam pārbaudīt nosacījuma formatēšanas noteikumus šūnās no C1 līdz C4, ievadot šādus datumus:

• Pašreizējais datums - šūnai vajadzētu mainīt noklusējuma balto fonu ar melnu tekstu, jo neviens nosacījuma formatēšanas noteikumi netiek piemēroti.
• 40 dienas pirms pašreizējā datuma - šūnai vajadzētu mainīties uz gaiši zaļu fonu ar baltu tekstu.
  
  (Piezīme: ja rodas grūtības aprēķināt šādu datumu, kā redzams attēlā, formula = TODAY () - 40 ievadīs datumu 40 dienas pirms pašreizējā datuma.)
• 70 dienas pirms pašreizējā datuma - šūna jāmaina dzeltenā fona ar baltu tekstu.
• 100 dienas pirms pašreizējā datuma - šūnai vajadzētu mainīties uz tumši sarkanu fonu ar baltu tekstu.

06 no 06

Alternatīvie nosacītā formatēšanas noteikumi

Ja jūsu darblapā jau ir redzams pašreizējais datums, un lielākā daļa darblapu to dara - alternatīva formula, kas paredzēta iepriekš minētajiem, var izmantot šūnu atsauci uz šūnu, kurā tiek parādīts pašreizējais datums, nevis TODAY funkcijas izmantošana.

Piemēram, ja datums tiek rādīts šūnā B4, formula parasti tiek ievadīta, lai nosacīti formatētu datumus, kas ir ilgāki par 30 dienām, varētu būt:

= $ B $ 4> 30

Dolāra zīmes ($), kas aptver šūnu atsauci B4, neļauj mainīt šūnu atsauci, ja nosacījuma formēšanas noteikums tiek kopēts citās darblapas šūnās.

Dolāra zīmes rada to, ko sauc par absolūtu šūnu atsauci .

Ja dolāra zīmes tiek izlaistas un nosacījuma formatēšanas noteikums tiek kopēts, galamērķa šūnā vai šūnās, visticamāk, parādīsies #REF! kļūdas ziņojums.