Kopsavilkums
Jūs varat iestatīt dinamisku kalendāra režģi Excel darblapā ar formulu sēriju, kā paskaidrots šajā rakstā. Parādītajā piemērā formula B6 ir:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
kur "sākums" ir nosauktais diapazons K5, un tajā ir datums 2018. gada 1. septembris.
Paskaidrojums
Piezīme. Šajā piemērā tiek pieņemts, ka sākuma datums tiks norādīts kā mēneša pirmais. Zemāk skatiet formulu, kas dinamiski atgriezīs pašreizējā mēneša pirmo dienu.
Izmantojot režģa izkārtojumu, kā parādīts, galvenā problēma ir datuma aprēķināšana kalendāra pirmajā šūnā (B6). Tas tiek darīts, izmantojot šo formulu:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Šī formula nosaka svētdienu pirms mēneša pirmās dienas, izmantojot funkciju IZVĒLĒTIES, lai "atgrieztos" pareizajā dienu skaitā līdz iepriekšējai svētdienai. CHOOSE šajā situācijā darbojas nevainojami, jo ļauj pieļaut patvaļīgas vērtības katrai nedēļas dienai. Mēs izmantojam šo funkciju, lai atceltu nulles dienas, kad mēneša pirmā diena ir svētdiena. Sīkāka informācija par šo problēmu ir sniegta šeit.
Kad pirmā diena ir noteikta B6, pārējās režģa formulas vienkārši palielina iepriekšējo datumu par vienu, sākot ar formulu C6:
=IF(B6"",B6,$H5)+1
Šī formula tūlīt pārbauda šūnu pa kreisi, lai iegūtu vērtību. Ja vērtība nav atrasta, tā izrauj vērtību no kolonnas H augšējā rindā. Piezīme $ H5 ir jaukta atsauce, lai bloķētu kolonnu, jo formula tiek kopēta visā režģī. To pašu formulu izmanto visās šūnās, izņemot B6.
Nosacījuma formatēšanas noteikumi
Kalendārā tiek izmantotas nosacītas formatēšanas formulas, lai mainītu formatējumu, lai ēnotu iepriekšējos un nākamos mēnešus un lai izceltu pašreizējo dienu. Abi noteikumi tiek piemēroti visam tīklam. Iepriekšējiem un nākamajiem mēnešiem formula ir šāda:
=MONTH(B6)MONTH(start)
Pašreizējai dienai formula ir šāda:
=B6=TODAY()
Lai iegūtu sīkāku informāciju, skatiet: Nosacījuma formatēšana ar formulām (10 piemēri)
Kalendāra virsraksts
Kalendāra nosaukums - mēnesis un gads - tiek aprēķināts ar šo formulu B4 šūnā:
=start
Formatēts ar pielāgoto skaitļu formātu "mmmm gggg". Lai virsrakstu centrētu virs kalendāra, diapazonam B4: H4 horizontālā izlīdzināšana ir iestatīta uz “centrēt pāri atlasei”. Šī ir labāka iespēja nekā šūnu apvienošana, jo tā nemaina darblapas režģa struktūru.
Mūžīgais kalendārs ar pašreizējo datumu
Lai izveidotu kalendāru, kas tiek automātiski atjaunināts, pamatojoties uz pašreizējo datumu, varat izmantot šādu formulu K5:
=EOMONTH(TODAY(),-1)+1
Šī formula iegūst pašreizējo datumu ar funkciju TODAY, pēc tam iegūst kārtējā mēneša pirmo dienu, izmantojot funkciju EOMONTH. Nomainiet TODAY () ar jebkuru noteiktu datumu, lai izveidotu kalendāru citā mēnesī. Sīkāka informācija par EOMONTH darbību šeit.
Soļi, lai izveidotu
- Slēpt režģa līnijas (pēc izvēles)
- Pievienojiet apmali B5: H11 (7R x 7C)
- Nosauciet K5 kā "sākums" un ievadiet datumu, piemēram, "2018. gada 1. septembris"
- Formula B4 = sākums
- Formāts B4 kā "mmmm gggg"
- Atlasiet B4: H4, iestatiet izlīdzināšanu uz “Centrs atlasē”
- Diapazonā B5: H5 ievadiet dienu saīsinājumus (SMTWTFS)
- Formula B6 = start-CHOOSE (WEEKDAY (sākums), 0,1,2,3,4,5,6)
- Atlasiet B6: H11, lietojiet pielāgotu ciparu formātu "d"
- Formula C6 = IF (B6 "", B6, $ H5) +1
- Kopēt formulu C6 uz atlikušajām šūnām kalendāra režģī
- Pievienojiet iepriekšējo / nākamo nosacītā formatēšanas kārtulu (skatiet iepriekšējo formulu)
- Pievienot pašreizējo nosacītā formatēšanas kārtulu (skatiet iepriekš sniegto formulu)
- Pārbaudiet datumu K5 uz citu "pirmā mēneša" datumu
- Mūžīgajam kalendāram formula K5 = EOMONTH (TODAY (), - 1) +1
