Ringkasan
Anda dapat menyetel kisi kalender dinamis pada lembar kerja Excel dengan serangkaian rumus, seperti yang dijelaskan di artikel ini. Dalam contoh yang ditampilkan, rumus di B6 adalah:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
dengan "mulai" adalah rentang bernama K5, dan berisi tanggal 1 September 2018.
Penjelasan
Catatan: Contoh ini mengasumsikan tanggal mulai akan diberikan sebagai tanggal pertama setiap bulan. Lihat di bawah untuk rumus yang secara dinamis akan mengembalikan hari pertama bulan ini.
Dengan tata letak grid seperti yang ditunjukkan, masalah utamanya adalah menghitung tanggal di sel pertama di kalender (B6). Ini dilakukan dengan rumus ini:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Rumus ini menghitung hari Minggu sebelum hari pertama setiap bulan dengan menggunakan fungsi PILIH untuk "memutar kembali" jumlah hari yang benar ke hari Minggu sebelumnya. PILIH bekerja dengan sempurna dalam situasi ini, karena memungkinkan nilai sewenang-wenang untuk setiap hari dalam seminggu. Kami menggunakan fitur ini untuk memutar mundur nol hari ketika hari pertama setiap bulan adalah hari Minggu. Detail lebih lanjut tentang masalah ini disediakan di sini.
Dengan hari pertama ditetapkan di B6, rumus lain dalam kisi hanya menambah tanggal sebelumnya satu per satu, dimulai dengan rumus di C6:
=IF(B6"",B6,$H5)+1
Rumus ini menguji sel langsung ke kiri untuk sebuah nilai. Jika tidak ada nilai yang ditemukan, ia menarik nilai dari kolom H di baris di atas. Catatan $ H5 adalah referensi campuran, untuk mengunci kolom saat rumus disalin di seluruh kisi. Rumus yang sama digunakan di semua sel kecuali B6.
Aturan pemformatan bersyarat
Kalender menggunakan rumus pemformatan bersyarat mengubah pemformatan menjadi bayangan bulan sebelumnya dan masa depan, dan untuk menyorot hari ini. Kedua aturan tersebut diterapkan ke seluruh kisi. Untuk bulan sebelumnya dan selanjutnya rumusnya adalah:
=MONTH(B6)MONTH(start)
Untuk hari ini, rumusnya adalah:
=B6=TODAY()
Untuk detail selengkapnya, lihat: Pemformatan bersyarat dengan rumus (10 contoh)
Judul kalender
Judul kalender - bulan dan tahun - dihitung dengan rumus ini di sel B4:
=start
Diformat dengan format angka kustom "mmmm yyyy". Untuk memusatkan judul di atas kalender, rentang B4: H4 memiliki perataan horizontal yang disetel ke "pusat di seluruh pilihan". Ini adalah opsi yang lebih baik daripada menggabungkan sel, karena tidak mengubah struktur kisi di lembar kerja.
Kalender abadi dengan tanggal hari ini
Untuk membuat kalender yang terupdate secara otomatis berdasarkan tanggal sekarang, Anda dapat menggunakan rumus seperti ini di K5:
=EOMONTH(TODAY(),-1)+1
Rumus ini mendapatkan tanggal sekarang dengan fungsi HARI INI, lalu mendapatkan hari pertama bulan ini menggunakan fungsi EOMONTH. Ganti TODAY () dengan tanggal tertentu untuk membuat kalender di bulan yang berbeda. Detail lebih lanjut tentang cara kerja EOMONTH di sini.
Langkah-langkah untuk membuat
- Sembunyikan garis kisi (opsional)
- Tambahkan perbatasan ke B5: H11 (7R x 7C)
- Beri nama K5 "mulai" dan masukkan tanggal seperti "1 September 2018"
- Rumus di B4 = mulai
- Format B4 sebagai "mmmm yyyy"
- Pilih B4: H4, setel perataan ke "Center across selection"
- Dalam rentang B5: H5, masukkan singkatan hari (SMTWTFS)
- Rumus di B6 = start-CHOOSE (WEEKDAY (start), 0,1,2,3,4,5,6)
- Pilih B6: H11, terapkan format angka khusus "d"
- Rumus di C6 = IF (B6 "", B6, $ H5) +1
- Salin rumus di C6 ke sel yang tersisa di kisi kalender
- Tambahkan aturan pemformatan bersyarat Sebelumnya / Berikutnya (lihat rumus di atas)
- Tambahkan aturan pemformatan bersyarat saat ini (lihat rumus di atas)
- Ubah tanggal di K5 ke tanggal "bulan pertama" lainnya untuk diuji
- Untuk kalender abadi, rumus di K5 = EOMONTH (TODAY (), - 1) +1
