Buat kalendar di Excel dengan satu formula dengan menggunakan formula yang dimasukkan array.
Lihat gambar ini:
Formula itu, =Cooladalah formula yang sama di setiap sel dari B5: H10! Lihat:
Ia dimasukkan array apabila B5: H10 pertama kali dipilih. Dalam artikel ini anda akan melihat apa yang ada di sebalik formula.
Ngomong-ngomong, ada sel yang belum ditunjukkan yang merupakan bulan untuk dipaparkan. Maksudnya, sel J1 berisi =TODAY(), (dan saya menulisnya pada bulan Disember) tetapi jika anda menukarnya menjadi 5/8/2012, anda akan melihat:
Ini Mei 2012. OK, pasti hebat! Mulakan dari awal, dan dapatkan formula ini dalam kalendar dan lihat bagaimana ia berfungsi.
Juga, anggap bahawa hari ini adalah 8 Mei 2012.
Pertama, lihat gambar ini:
Formula itu tidak masuk akal. Mungkin, jika dikelilingi oleh =SUM, tetapi anda ingin melihat apa yang ada di balik formula, jadi anda akan mengembangkannya dengan memilihnya dan menekan kekunci F9.
Gambar di atas menjadi rajah di bawah apabila kekunci F9 ditekan.
Perhatikan bahawa ada titik koma selepas angka 3 - ini menunjukkan baris baru. Lajur baru dilambangkan dengan koma. Oleh itu, anda akan memanfaatkannya.
Bilangan minggu dalam sebulan berbeza, tetapi tidak ada kalendar yang memerlukan lebih dari enam baris untuk mewakili setiap bulan, dan tentu saja, semuanya mempunyai tujuh hari. Lihat gambar ini:
Masukkan nilai 1 hingga 42 secara manual di B5: H10, dan jika anda memasukkan =B5:H10dalam sel dan kemudian mengembangkan bar formula, anda dapat melihat apa yang ditunjukkan di sini:
Perhatikan penempatan titik koma - setelah setiap gandaan 7 - menunjukkan baris baru. Ini adalah permulaan formula, tetapi bukannya yang panjang, anda boleh menggunakan formula yang lebih pendek ini. Pilih B5: H10. Jenis
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
sebagai formula, tetapi jangan tekan Enter.
Untuk memberitahu Excel ini adalah formula tatasusunan, anda harus menahan Ctrl + Shift dengan tangan kiri. Sambil memegang Ctrl + Shift, tekan Enter dengan tangan kanan anda. Kemudian, lepaskan Ctrl + Shift. Untuk sisa artikel ini, set penekanan tombol ini akan dipanggil Ctrl + Shift + Enter.
Sekiranya anda melakukan Ctrl + Shift + Enter dengan betul, pendakap keriting akan muncul di sekitar formula di bar formula dan angka 1 hingga 42 akan muncul di B5: H10 seperti yang ditunjukkan di sini:
Perhatikan bahawa anda mengambil angka 0 hingga 5 yang dipisahkan dengan titik koma (baris baru untuk masing-masing) dan mengalikannya dengan 7, dengan berkesan memberikan ini:
Orientasi menegak nilai-nilai ini ditambahkan pada orientasi mendatar nilai 1 hingga 7 menghasilkan nilai yang sama seperti yang ditunjukkan. Pengembangan ini sama dengan yang anda miliki sebelumnya. Katakan sekarang anda menambah HARI INI ke nombor ini?
Catatan: Mengedit formula array yang ada sangat sukar. Berhati-hati, ikuti langkah berikut: Pilih B5: H10. Klik di Formula Bar untuk mengedit formula yang ada. Taip + J1 tetapi jangan tekan Enter. Untuk menerima formula yang diedit, tekan Ctrl + Shift + Enter.
Hasil untuk 8 Mei 2012 adalah:
Nombor-nombor ini adalah nombor siri (bilangan hari sejak 1/1/1900). Sekiranya anda memformatnya sebagai tarikh pendek:
Jelas tidak betul, tetapi anda akan sampai di sana. Bagaimana jika anda memformatnya hanya sebagai "d" untuk hari bulan:
Hampir kelihatan seperti bulan, tetapi tidak ada bulan yang bermula dengan kesembilan bulan. Ah, inilah satu masalah. Anda menggunakan J1 yang mengandungi 5/8/2012, dan anda benar-benar perlu menggunakan tarikh pertama bulan ini. Oleh itu, andaikan anda memasukkan =DATE(YEAR(J1),MONTH(J1),1)J2:
Sel J1 mengandungi 5/8/2012 dan sel J2 mengubahnya menjadi yang pertama pada bulan apa sahaja yang dimasukkan di J1. Oleh itu, jika anda menukar J1 dalam formula kalendar menjadi J2:
Lebih dekat, tetapi masih tidak betul. Satu penyesuaian lebih lanjut diperlukan, dan itu adalah anda perlu mengurangkan hari kerja pada hari pertama. Maksudnya, sel J3 mengandungi =WEEKDAY(J2). 3 mewakili hari Selasa. Oleh itu, jika anda mengurangkan J3 dari formula ini, anda mendapat:
Dan itu betul untuk Mei 2012!
Baiklah, Anda benar-benar dekat. Yang masih salah ialah 29 dan 30 dari bulan April muncul dalam kalendar Mei, dan 1 Jun hingga 9 juga muncul. Anda perlu membersihkannya.
Anda boleh memberikan nama formula untuk rujukan yang lebih mudah. Sebut saja "Cal" (belum "sejuk"). Lihat gambar ini:
Kemudian anda boleh menukar formula menjadi =Cal(masih Ctrl + Shift + Enter):
Sekarang anda boleh mengubah formula untuk membaca bahawa jika hasilnya berada di baris 5 dan hasilnya lebih dari 20, katakan, maka hasilnya harus kosong. Baris 5 akan mengandungi minggu pertama setiap bulan, jadi anda tidak akan pernah melihat nilai lebih dari 20 (atau nombor lebih dari tujuh akan salah - angka seperti 29 yang anda lihat dalam sel B5 dari angka di atas adalah dari bulan sebelumnya). Oleh itu, anda boleh menggunakan =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Pertama, perhatikan bahawa sel B5: D5 kosong. Formula sekarang berbunyi "jika ini adalah baris ke-5, maka jika HARI hasilnya melebihi 20, tunjukkan kosong".
Anda boleh terus membuang angka rendah pada akhir - nilai bulan depan. Inilah cara untuk melakukan ini dengan mudah.
Edit formula dan pilih rujukan akhir untuk "Cal"
Mulailah menaip IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) untuk menggantikan Cal akhir.
Formula terakhir semestinya
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Tekan Ctrl + Shift + Enter. Hasilnya mestilah:
Dua perkara lagi yang perlu dilakukan. Anda boleh mengambil formula ini dan memberikannya nama, "Hebat":
Kemudian gunakannya dalam formula yang ditunjukkan di sini:
By the way, nama yang ditentukan diperlakukan seolah-olah mereka dimasukkan array.
Yang tinggal ialah memformat sel dan memasukkan Hari dalam seminggu dan nama bulan. Oleh itu, anda melebarkan lajur, meningkatkan ketinggian baris, menambah saiz fon, dan menyelaraskan teks:
Kemudian letakkan sempadan di sekitar sel:
Gabungkan dan pusatkan bulan & tahun dan formatlah:
Kemudian matikan garisan grid, dan voila:
Artikel tetamu ini adalah dari Excel MVP Bob Umlas. Ia adalah dari buku, Excel Outside the Box. Untuk melihat topik lain dalam buku, klik di sini.
