rezumat
Puteți configura o grilă dinamică de calendar pe o foaie de lucru Excel cu o serie de formule, așa cum se explică în acest articol. În exemplul prezentat, formula din B6 este:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
unde „start” este intervalul numit K5 și conține data de 1 septembrie 2018.
Explicaţie
Notă: Acest exemplu presupune că data de începere va fi furnizată ca prima lună. Vedeți mai jos o formulă care va reveni dinamic în prima zi a lunii curente.
Cu aspectul grilei așa cum se arată, principala problemă este calcularea datei în prima celulă din calendar (B6). Acest lucru se face cu această formulă:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Această formulă descrie duminica anterioară primei zile a lunii, utilizând funcția ALEGE pentru a „întoarce” numărul corect de zile la duminica precedentă. CHOOSE funcționează perfect în această situație, deoarece permite valori arbitrare pentru fiecare zi a săptămânii. Folosim această caracteristică pentru a reveni la zero zile când prima zi a lunii este duminică. Mai multe detalii despre această problemă sunt furnizate aici.
Odată cu prima zi stabilită în B6, celelalte formule din grilă incrementează pur și simplu data anterioară cu una, începând cu formula din C6:
=IF(B6"",B6,$H5)+1
Această formulă testează celula imediat la stânga pentru o valoare. Dacă nu se găsește nicio valoare, extrage o valoare din coloana H din rândul de mai sus. Notă $ H5 este o referință mixtă, pentru a bloca coloana pe măsură ce formula este copiată în toată grila. Aceeași formulă este utilizată în toate celulele, cu excepția B6.
Reguli de formatare condiționată
Calendarul folosește formule de formatare condiționată pentru a modifica formatarea pentru a umbra lunile anterioare și viitoare și pentru a evidenția ziua curentă. Ambele reguli sunt aplicate întregii rețele. Pentru lunile anterioare și următoare, formula este:
=MONTH(B6)MONTH(start)
Pentru ziua curentă, formula este:
=B6=TODAY()
Pentru mai multe detalii, consultați: Formatarea condiționată cu formule (10 exemple)
Antet de calendar
Titlul calendarului - lună și an - se calculează cu această formulă în celula B4:
=start
Formatat cu formatul de număr personalizat „mmmm aaaa”. Pentru a centra titlul deasupra calendarului, intervalul B4: H4 are o aliniere orizontală setată la „centrare pe selecție”. Aceasta este o opțiune mai bună decât îmbinarea celulelor, deoarece nu modifică structura grilei din foaia de lucru.
Calendar perpetuu cu data curentă
Pentru a crea un calendar care se actualizează automat pe baza datei curente, puteți utiliza formula de acest fel în K5:
=EOMONTH(TODAY(),-1)+1
Această formulă obține data curentă cu funcția TODAY, apoi primește prima zi a lunii curente folosind funcția EOMONTH. Înlocuiți TODAY () cu orice dată dată pentru a construi un calendar într-o lună diferită. Mai multe detalii despre modul în care funcționează EOMONTH aici.
Pași pentru a crea
- Ascundeți liniile grilei (opțional)
- Adăugați un chenar la B5: H11 (7R x 7C)
- Denumiți K5 „start” și introduceți data ca „1 septembrie 2018”
- Formula în B4 = start
- Formatați B4 ca „mmmm aaaa”
- Selectați B4: H4, setați alinierea la „Centrare peste selecție”
- În intervalul B5: H5, introduceți abrevierile de zi (SMTWTFS)
- Formula în B6 = start-CHOOSE (WEEKDAY (start), 0,1,2,3,4,5,6)
- Selectați B6: H11, aplicați formatul de număr personalizat „d”
- Formula în C6 = IF (B6 "", B6, $ H5) +1
- Copiați formula în C6 în celulele rămase din grila calendaristică
- Adăugați o regulă de formatare condițională Anterioară / Următoare (a se vedea formula de mai sus)
- Adăugați regula de formatare condițională curentă (a se vedea formula de mai sus)
- Schimbați data în K5 cu o altă dată „prima lună” de testat
- Pentru calendarul perpetuu, formula în K5 = EOMONTH (TODAY (), - 1) +1
