Buat kalender di Excel dengan satu rumus dengan menggunakan rumus yang dimasukkan array.
Lihat gambar ini:
Rumus itu,, =Cooladalah rumus yang sama di setiap sel dari B5: H10! Lihat:
Itu dimasukkan dalam array setelah B5: H10 pertama kali dipilih. Pada artikel ini Anda akan melihat apa yang ada di balik rumus tersebut.
Ngomong-ngomong, ada sel yang belum muncul yang merupakan bulan untuk ditampilkan. Artinya, sel J1 berisi =TODAY(), (dan saya menulis ini pada bulan Desember) tetapi jika Anda mengubahnya menjadi 8/5/2012, Anda akan melihat:
Ini Mei 2012. Oke, pasti keren! Mulailah dari awal, dan lanjutkan ke rumus ini di kalender dan lihat cara kerjanya.
Juga, asumsikan bahwa hari ini adalah 8 Mei 2012.
Pertama, lihat gambar ini:
Rumusnya tidak masuk akal. Itu akan, jika dikelilingi oleh =SUM, tetapi Anda ingin melihat apa yang ada di balik rumus, jadi Anda akan memperluasnya dengan memilihnya dan menekan tombol F9.
Angka di atas menjadi gambar di bawah ini saat tombol F9 ditekan.
Perhatikan bahwa ada titik koma setelah 3 - ini menunjukkan baris baru. Kolom baru diwakili oleh koma. Jadi, Anda akan memanfaatkannya.
Jumlah minggu dalam satu bulan bervariasi, tetapi tidak ada kalender yang membutuhkan lebih dari enam baris untuk mewakili bulan apa pun, dan tentu saja, semuanya memiliki tujuh hari. Lihat gambar ini:
Masukkan nilai 1 hingga 42 secara manual di B5: H10, dan jika Anda memasukkan =B5:H10dalam sel lalu memperluas bilah rumus, Anda akan melihat apa yang diperlihatkan di sini:
Perhatikan penempatan titik koma - setelah setiap kelipatan 7 - menunjukkan baris baru. Ini adalah awal dari rumus, tetapi alih-alih yang panjang, Anda dapat menggunakan rumus yang lebih pendek ini. Pilih B5: H10. Tipe
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
sebagai rumusnya, tapi jangan tekan Enter.
Untuk memberi tahu Excel bahwa ini adalah rumus array, Anda harus menahan Ctrl + Shift dengan tangan kiri Anda. Sambil menahan Ctrl + Shift, tekan Enter dengan tangan kanan Anda. Lalu, lepaskan Ctrl + Shift. Untuk sisa artikel ini, rangkaian penekanan tombol ini akan disebut Ctrl + Shift + Enter.
Jika Anda melakukan Ctrl + Shift + Enter dengan benar, tanda kurung kurawal akan muncul di sekitar rumus di bilah rumus dan angka 1 hingga 42 akan muncul di B5: H10 seperti yang ditunjukkan di sini:
Perhatikan bahwa Anda mengambil angka 0 sampai 5 yang dipisahkan oleh titik koma (baris baru untuk masing-masing) dan mengalikannya dengan 7, secara efektif memberikan ini:
Orientasi vertikal dari nilai-nilai ini yang ditambahkan ke orientasi horizontal dari nilai 1 sampai 7 menghasilkan nilai yang sama seperti yang ditunjukkan. Perluasan ini identik dengan apa yang Anda miliki sebelumnya. Misalkan sekarang Anda menambahkan HARI INI ke nomor-nomor ini?
Catatan: Mengedit rumus array yang sudah ada sangat rumit. Dengan hati-hati, ikuti langkah-langkah berikut: Pilih B5: H10. Klik di Formula Bar untuk mengedit formula yang ada. Ketik + J1 tapi jangan tekan Enter. Untuk menerima rumus yang diedit, tekan Ctrl + Shift + Enter.
Hasil untuk 8 Mei 2012 adalah:
Nomor-nomor ini adalah nomor seri (jumlah hari sejak 1/1/1900). Jika Anda memformatnya sebagai tanggal pendek:
Jelas tidak benar, tetapi Anda akan sampai di sana. Bagaimana jika Anda memformatnya sebagai "d" untuk hari dalam sebulan:
Hampir terlihat seperti satu bulan, tetapi tidak ada bulan yang dimulai dengan tanggal sembilan setiap bulan. Ah, ini satu masalah. Anda menggunakan J1 yang berisi 5/8/2012, dan Anda benar-benar perlu menggunakan tanggal pertama setiap bulan. Jadi misalkan Anda memasukkan =DATE(YEAR(J1),MONTH(J1),1)J2:
Sel J1 berisi 5/8/2012 dan sel J2 mengubahnya ke bulan pertama dari apa pun yang dimasukkan di J1. Jadi jika Anda mengubah J1 dalam rumus kalender menjadi J2:
Lebih dekat, tapi masih belum benar. Satu penyesuaian lebih lanjut diperlukan, dan itu adalah Anda perlu mengurangi hari kerja di hari pertama. Artinya, sel J3 berisi =WEEKDAY(J2). 3 mewakili hari Selasa. Jadi sekarang jika Anda mengurangi J3 dari rumus ini, Anda mendapatkan:
Dan itu sebenarnya tepat untuk Mei 2012!
Oke, Anda sangat dekat. Yang masih salah adalah tanggal 29 dan 30 dari April akan muncul di kalender Mei, dan 1 hingga 9 Juni juga muncul. Anda perlu menghapus ini.
Anda dapat memberi nama rumus untuk referensi yang lebih mudah. Sebut saja "Cal" (belum "keren"). Lihat gambar ini:
Kemudian Anda dapat mengubah rumus menjadi =Cal(masih Ctrl + Shift + Enter):
Sekarang Anda dapat mengubah rumus untuk membaca bahwa jika hasilnya di baris 5 dan hasilnya di atas 20, katakanlah, maka hasil itu harus kosong. Baris 5 akan berisi minggu pertama setiap bulan sehingga Anda tidak akan pernah melihat nilai lebih dari 20 (atau angka di atas tujuh akan salah - angka seperti 29 yang Anda lihat di sel B5 dari gambar di atas berasal dari bulan sebelumnya). Jadi Anda bisa menggunakan =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Pertama, perhatikan bahwa sel B5: D5 kosong. Rumusnya sekarang berbunyi "jika ini adalah baris 5, maka jika HARI hasilnya lebih dari 20, kosongkan".
Anda dapat terus menghapus angka rendah di akhir - nilai bulan depan. Berikut cara melakukannya dengan mudah.
Edit rumus dan pilih referensi akhir untuk "Cal"
Mulailah mengetik IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) untuk menggantikan Cal terakhir.
Rumus terakhir seharusnya
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Tekan Ctrl + Shift + Enter. Hasilnya harus:
Dua hal yang harus dilakukan. Anda dapat mengambil rumus ini dan memberinya nama, "Keren":
Kemudian gunakan itu dalam rumus yang ditunjukkan di sini:
Omong-omong, nama yang ditentukan diperlakukan seolah-olah dimasukkan dalam array.
Apa yang tersisa untuk dilakukan adalah memformat sel dan memasukkan Hari dalam seminggu dan nama bulannya. Jadi, Anda memperlebar kolom, menambah tinggi baris, menambah ukuran font, dan meratakan teks:
Kemudian letakkan batas di sekitar sel:
Gabungkan dan pusatkan bulan & tahun dan format:
Kemudian matikan garis kisi, dan voila:
Artikel tamu ini berasal dari Excel MVP Bob Umlas. Ini dari buku, Excel Outside the Box. Untuk melihat topik lain di buku, klik di sini.
