Konten
Misalkan kita perlu dengan cepat dan dengan sedikit usaha membuat kalender tahunan yang secara otomatis menampilkan tanggal tahapan proyek (atau liburan karyawan, atau pelatihan, dll.)
Benda kerja
Mari kita mulai dengan yang kosong:
Seperti yang Anda lihat, semuanya sederhana di sini:
- Baris adalah bulan, kolom adalah hari.
- Sel A2 berisi tahun pembuatan kalender. Dalam sel A4:A15 – jumlah bulan tambahan. Kita akan membutuhkan keduanya sedikit kemudian untuk membentuk tanggal di kalender.
- Di sebelah kanan tabel adalah nama-nama tahapan dengan tanggal mulai dan berakhir. Anda dapat menyediakan sel kosong terlebih dahulu untuk tahap baru yang ditambahkan di masa mendatang.
Mengisi kalender dengan tanggal dan menyembunyikannya
Sekarang mari kita isi kalender kita dengan tanggal. Pilih sel pertama C4 dan masukkan fungsi di sana TANGGAL (TANGGAL), yang menghasilkan tanggal dari tahun, bulan, dan nomor hari:
Setelah memasukkan rumus, itu harus disalin ke seluruh rentang dari 1 Januari hingga 31 Desember (C4:AG15). Karena selnya sempit, alih-alih tanggal yang dibuat, kita akan melihat tanda hash (#). Namun, saat Anda mengarahkan mouse ke sel tersebut, Anda dapat melihat konten sebenarnya di tooltip:
Untuk menghindari kisi-kisi, kita dapat menyembunyikannya dengan format khusus yang cerdas. Untuk melakukan ini, pilih semua tanggal, buka jendela Format Sel dan di tab Jumlah (jumlah) Pilih opsi Semua format (Kebiasaan). Kemudian di lapangan Sebuah tipe masukkan tiga titik koma berturut-turut (tanpa spasi!) dan tekan OK. Isi sel akan disembunyikan dan kisi-kisi akan hilang, meskipun tanggal dalam sel, pada kenyataannya, akan tetap ada – ini hanya visibilitas.
Sorotan panggung
Sekarang, dengan menggunakan pemformatan bersyarat, mari tambahkan sorotan tonggak ke sel dengan tanggal tersembunyi. Pilih semua tanggal dalam rentang C4:AG15 dan pilih pada tab Beranda — Pemformatan Bersyarat — Buat Aturan (Beranda — Pemformatan bersyarat — Buat Aturan). Di jendela yang terbuka, pilih opsi Gunakan rumus untuk menentukan sel mana yang akan diformat (Gunakan rumus untuk menunda sel mana yang akan diformat) dan masukkan rumus:
Rumus ini memeriksa setiap sel tanggal dari C4 hingga akhir tahun untuk melihat apakah berada di antara awal dan akhir setiap pencapaian. Outputnya akan menjadi 4 hanya ketika kedua kondisi yang diperiksa dalam tanda kurung (C4>=$AJ$13:$AJ$4) dan (C4<=$AK$13:$AK$1) menghasilkan BENAR logis, yang ditafsirkan Excel sebagai 0 ( baik , FALSE seperti 4, tentu saja). Juga, berikan perhatian khusus pada fakta bahwa referensi ke sel awal CXNUMX adalah relatif (tanpa $), dan rentang tahapan - absolut (dengan dua $).
Setelah mengklik OK kita akan melihat tonggak sejarah di kalender kita:
Menyoroti persimpangan
Jika tanggal beberapa tahapan tumpang tindih (pembaca yang cermat pasti sudah memperhatikan momen ini untuk tahap 1 dan 6!), maka akan lebih baik untuk menyoroti konflik ini di bagan kita dengan warna berbeda menggunakan aturan pemformatan bersyarat lainnya. Ini praktis satu-ke-satu mirip dengan yang sebelumnya, kecuali bahwa kami mencari sel yang termasuk dalam lebih dari satu tahap:
Setelah mengklik OK aturan seperti itu akan dengan jelas menyoroti tumpang tindih tanggal di kalender kami:
Menghapus hari ekstra dalam beberapa bulan
Tentu saja, tidak semua bulan memiliki 31 hari, jadi hari-hari tambahan di bulan Februari, April, Juni, dll. Akan lebih baik jika secara visual ditandai sebagai tidak relevan. Fungsi TANGGAL, yang membentuk kalender kita, di sel tersebut secara otomatis akan menerjemahkan tanggal ke bulan berikutnya, yaitu 30 Februari 2016 akan menjadi 1 Maret. Artinya, jumlah bulan untuk sel tambahan tersebut tidak akan sama dengan nomor bulan di kolom A Ini dapat digunakan saat membuat aturan pemformatan bersyarat untuk memilih sel tersebut:
Menambahkan akhir pekan
Secara opsional, Anda dapat menambahkan ke kalender dan akhir pekan kami. Untuk melakukan ini, Anda dapat menggunakan fungsi HARI (HARI KERJA), yang akan menghitung jumlah hari dalam seminggu (1-Senin, 2-Selasa…7-Minggu) untuk setiap tanggal dan menyoroti tanggal yang jatuh pada hari Sabtu dan Minggu:
Untuk tampilan yang benar, jangan lupa untuk mengonfigurasi urutan aturan yang benar di jendela dengan benar. Beranda — Pemformatan Bersyarat — Kelola Aturan (Beranda — Pemformatan bersyarat — Kelola Aturan), karena aturan dan isian bekerja persis dalam urutan logis yang akan Anda bentuk dalam dialog ini:
- Video tutorial tentang penggunaan pemformatan bersyarat di Excel
- Cara Membuat Jadwal Proyek (Gantt Chart) Menggunakan Pemformatan Bersyarat
- Cara membuat garis waktu proyek di Excel