Dalam video ini, kita akan melihat cara mengira dan menonjolkan tarikh luput.
Katakan syarikat anda telah memulakan program keahlian dan bos anda baru saja menghantar sejumlah data kepada anda. Dia memberi anda senarai 1000 orang yang telah memperbaharui keahlian pada tahun lalu atau lebih, dan dia mencari beberapa perkara.
Pertama, dia mahu anda mengira tarikh luput satu tahun di masa hadapan, pada hari terakhir bulan yang sama keahliannya diperbaharui.
Kedua, dia ingin melihat berapa hari yang tinggal sebelum tamat.
Ketiga, dia ingin melihat status "Tamat tempoh" untuk mana-mana keahlian yang telah luput, dan "Tidak lama lagi" untuk keahlian yang akan tamat dalam 30 hari akan datang.
Akhirnya, dia mengatakan akan menyenangkan melihat anggota yang luput diserlahkan dengan warna merah jambu, dan mereka yang luput akan segera diserlahkan dengan warna kuning.
Juga - bolehkah dia memilikinya sebelum mesyuarat makan tengah hari pada waktu tengah hari?
Umm, pasti.
Pertama, mari kita menukar data ini ke Jadual Excel yang betul. Ini akan menjadikan formula lebih mudah dimasukkan, kerana Excel akan menyalinnya secara automatik, semasa kita pergi.
Sekarang mari kita mengira tarikh luput. Ini sepatutnya pada akhir bulan yang sama satu tahun kemudian, tetapi mari kita gunakan peretasan sederhana terlebih dahulu, untuk masuk ke taman permainan. Seperti yang anda lihat dalam video sebelumnya, tarikh hanyalah nombor siri, jadi kita hanya boleh memasukkan formula yang menambah 365 hari pada tarikh Diperbaharui.
Ini adalah permulaan yang baik. Kami boleh menyelesaikan penyelesaiannya dan kembali untuk menyelesaikannya kemudian.
Apabila anda menyelesaikan masalah yang lebih rumit di Excel, adalah idea yang baik untuk mengesahkan pendekatan keseluruhan, kemudian kembali ke butiran di akhir. Anda tidak mahu terjebak pada perkara kecil pada awalnya, terutamanya jika pendekatannya mungkin berubah.
Sekarang kita mempunyai tarikh luput, kita sekarang dapat mengira hari yang tersisa. Ini memerlukan kemas kini secara automatik pada masa akan datang, jadi kami akan menggunakan fungsi HARI INI, yang selalu kembali ke tarikh hari ini.
Rumusannya hanyalah E5 tolak HARI INI (). Apabila saya kembali, kami akan tinggal beberapa hari sebelum tamat. Nombor negatif menunjukkan keahlian sudah tamat.
Untuk status, kami akan menggunakan formula IF bersarang sederhana. Sekiranya hari yang tinggal kurang dari sifar, keahlian akan tamat. Jika tidak, jika hari yang tinggal kurang dari 30, statusnya hendaklah "Tidak lama lagi". Jika tidak, statusnya tidak ada.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Seterusnya kita perlu membuat peraturan pemformatan bersyarat yang menonjolkan nilai-nilai ini.
Pertama, pilih data, dan tetapkan sel aktif ke sudut kanan atas. Kemudian, buat peraturan formula yang menguji sel aktif untuk nilai "Expired". Lajur mesti dikunci.
Sekarang ulangi proses yang sama untuk keahlian yang "Tidak lama lagi".
Ini kelihatan baik. Kami hanya perlu menetapkan tarikh luput untuk mendarat pada hari terakhir bulan tersebut.
Baiklah, ternyata ada fungsi keren yang disebut EOMONTH (untuk akhir bulan) yang mendapat hari terakhir dalam sebulan pada masa lalu atau masa depan.
Tarikh mula tarikh diperbaharui, dan bulan adalah 12.
Dan di sana anda memilikinya, semua yang diinginkan oleh atasan anda, dan anda masih memerlukan secawan kopi sebelum makan tengah hari.
Kursus
Formula TerasJalan pintas yang berkaitan
Masukkan jadual Ctrl + T ⌃ + T Kembangkan atau runtuh pita Ctrl + F1 ⌘ + ⌥ + R Pilih jadual Ctrl + A ⌘ + A Gerakkan sel aktif mengikut arah jam mengikut pilihan Ctrl + . ⌃ + .
