Ringkasan
Anda boleh menyediakan grid kalendar dinamik pada lembaran kerja Excel dengan rangkaian formula, seperti yang dijelaskan dalam artikel ini. Dalam contoh yang ditunjukkan, formula dalam B6 adalah:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
di mana "permulaan" adalah julat bernama K5, dan mengandungi tarikh 1 September 2018.
Penjelasan
Catatan: Contoh ini menganggap tarikh mula akan diberikan sebagai yang pertama bulan ini. Lihat di bawah untuk formula yang akan mengembalikan hari pertama bulan semasa secara dinamik.
Dengan susun atur grid seperti yang ditunjukkan, masalah utama adalah mengira tarikh di sel pertama dalam kalendar (B6). Ini dilakukan dengan formula ini:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Formula ini menunjukkan hari Ahad sebelum hari pertama bulan dengan menggunakan fungsi PILIH untuk "mengembalikan" bilangan hari yang tepat ke hari minggu sebelumnya. PILIH berfungsi dengan sempurna dalam keadaan ini, kerana ia membenarkan nilai sewenang-wenangnya untuk setiap hari dalam seminggu. Kami menggunakan ciri ini untuk mengembalikan sifar hari ketika hari pertama bulan itu adalah hari Ahad. Maklumat lebih lanjut mengenai masalah ini disediakan di sini.
Dengan hari pertama ditubuhkan di B6, formula lain di grid hanya menambah tarikh sebelumnya dengan satu, bermula dengan formula di C6:
=IF(B6"",B6,$H5)+1
Rumus ini menguji sel dengan segera ke kiri untuk mendapatkan nilai. Sekiranya tiada nilai dijumpai, ia menarik nilai dari lajur H pada baris di atas. Catatan $ H5 adalah rujukan campuran, untuk mengunci lajur kerana formula disalin di seluruh grid. Formula yang sama digunakan di semua sel kecuali B6.
Peraturan pemformatan bersyarat
Kalendar menggunakan formula pemformatan bersyarat mengubah format untuk membayangi bulan sebelumnya dan bulan depan, dan untuk menyoroti hari semasa. Kedua-dua peraturan tersebut diterapkan ke seluruh grid. Untuk bulan sebelumnya dan berikutnya, formula adalah:
=MONTH(B6)MONTH(start)
Untuk hari ini, formula adalah:
=B6=TODAY()
Untuk maklumat lebih lanjut, lihat: Pemformatan bersyarat dengan formula (10 contoh)
Tajuk kalendar
Tajuk kalendar - bulan dan tahun - dikira dengan formula ini di sel B4:
=start
Diformat dengan format nombor tersuai "mmmm yyyy". Untuk memusatkan tajuk di atas kalendar, julat B4: H4 mempunyai penjajaran mendatar yang ditetapkan ke "pusat merentasi pilihan". Ini adalah pilihan yang lebih baik daripada menggabungkan sel, kerana ia tidak mengubah struktur grid dalam lembaran kerja.
Kalendar berkekalan dengan tarikh semasa
Untuk membuat kalendar yang dikemas kini secara automatik berdasarkan tarikh semasa, anda boleh menggunakan formula seperti ini di K5:
=EOMONTH(TODAY(),-1)+1
Rumus ini mendapat tarikh semasa dengan fungsi HARI INI, kemudian mendapat hari pertama bulan semasa menggunakan fungsi EOMONTH. Ganti HARI INI () dengan tarikh tertentu untuk membina kalendar pada bulan yang berbeza. Maklumat lanjut mengenai bagaimana EOMONTH berfungsi di sini.
Langkah untuk membuat
- Sembunyikan garisan grid (pilihan)
- Tambahkan sempadan ke B5: H11 (7R x 7C)
- Namakan K5 "mula" dan masukkan tarikh seperti "1 September 2018"
- Formula dalam B4 = permulaan
- Format B4 sebagai "mmmm yyyy"
- Pilih B4: H4, tetapkan penjajaran ke "Pusat merentasi pilihan"
- Dalam julat B5: H5, masukkan singkatan hari (SMTWTFS)
- Formula dalam B6 = mula-PILIH (WEEKDAY (mula), 0,1,2,3,4,5,6)
- Pilih B6: H11, terapkan format nombor khusus "d"
- Formula dalam C6 = JIKA (B6 "", B6, $ H5) +1
- Salin formula dalam C6 ke sel yang tersisa di grid kalendar
- Tambahkan peraturan pemformatan bersyarat Sebelumnya / Seterusnya (lihat formula di atas)
- Tambahkan peraturan pemformatan bersyarat semasa (lihat formula di atas)
- Tukar tarikh dalam K5 ke tarikh "pertama bulan" yang lain untuk diuji
- Untuk kalendar yang berterusan, formula dalam K5 = EOMONTH (HARI INI (), - 1) +1
