Dalam video ini, kita akan melihat cara menghitung dan menyoroti tanggal kedaluwarsa.
Katakanlah perusahaan Anda telah memulai program keanggotaan dan atasan Anda baru saja mengirimi Anda sekumpulan data. Dia memberi Anda daftar 1000 orang yang telah memperbarui keanggotaan dalam setahun terakhir ini, dan dia mencari beberapa hal.
Pertama, dia ingin Anda menghitung tanggal kedaluwarsa satu tahun ke depan, pada hari terakhir di bulan yang sama keanggotaan diperpanjang.
Kedua, dia ingin melihat berapa hari yang tersisa sebelum kedaluwarsa.
Ketiga, dia ingin melihat status "Kedaluwarsa" untuk semua keanggotaan yang sudah kedaluwarsa, dan "Segera kedaluwarsa" untuk keanggotaan yang kedaluwarsa dalam 30 hari ke depan.
Akhirnya, dia berkata akan menyenangkan melihat anggota yang sudah kedaluwarsa disorot dengan warna pink, dan yang kedaluwarsa segera disorot dengan warna kuning.
Juga - bisakah dia meminumnya sebelum pertemuan makan siangnya pada siang hari?
Umm, tentu.
Pertama, mari kita ubah data ini menjadi Tabel Excel yang tepat. Ini akan membuat rumus lebih mudah untuk dimasukkan, karena Excel akan secara otomatis menyalinnya, saat kita melanjutkan.
Sekarang mari kita hitung tanggal kedaluwarsa. Ini seharusnya terjadi pada akhir bulan yang sama satu tahun kemudian, tetapi mari kita gunakan retasan sederhana terlebih dahulu, untuk masuk ke dalam kasarnya. Seperti yang Anda lihat di video sebelumnya, tanggal hanyalah nomor seri, jadi kita bisa memasukkan rumus yang menambahkan 365 hari ke tanggal diperpanjang.
Ini adalah awal yang bagus. Kami dapat menyelesaikan solusi secara kasar dan kembali lagi untuk memperbaikinya nanti.
Saat Anda memecahkan masalah yang lebih kompleks di Excel, ada baiknya untuk memvalidasi pendekatan keseluruhan, lalu kembali ke detail di bagian akhir. Anda tidak ingin terjebak pada hal kecil sejak awal, terutama jika pendekatannya dapat berubah.
Sekarang kita memiliki tanggal kedaluwarsa, sekarang kita dapat menghitung hari yang tersisa. Ini perlu diperbarui secara otomatis di masa mendatang, jadi kami akan menggunakan fungsi TODAY, yang selalu mengembalikan tanggal hari ini.
Rumusnya hanyalah E5 dikurangi TODAY (). Ketika saya menekan kembali, kami mendapatkan hari tersisa sebelum kedaluwarsa. Angka negatif menunjukkan keanggotaan sudah kedaluwarsa.
Untuk status, kami akan menggunakan rumus IF bertingkat sederhana. Jika hari tersisa kurang dari nol, keanggotaan kedaluwarsa. Jika tidak, jika hari tersisa kurang dari 30, statusnya harus "Segera kedaluwarsa". Kalau tidak, statusnya bukan apa-apa.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Selanjutnya kita perlu membuat aturan pemformatan bersyarat yang menyoroti nilai-nilai ini.
Pertama, pilih data, dan atur sel aktif ke sudut kanan atas. Kemudian, buat aturan rumus yang menguji sel aktif untuk nilai "Kedaluwarsa". Kolom harus dikunci.
Sekarang ulangi proses yang sama untuk keanggotaan yang "Segera kedaluwarsa".
Ini terlihat bagus. Kami hanya perlu menetapkan tanggal kedaluwarsa untuk mendarat di hari terakhir setiap bulan.
Nah, ternyata ada fungsi keren bernama EOMONTH (untuk akhir bulan) yang mendapat hari terakhir dalam sebulan di masa lalu atau masa depan.
Tanggal mulai adalah tanggal diperbarui, dan bulan adalah 12.
Dan begitulah, semua yang diinginkan bos Anda, dan Anda masih punya waktu untuk minum kopi sebelum makan siang.
Kursus
Formula IntiPintasan terkait
Sisipkan tabel Ctrl + T ⌃ + T Perluas atau ciutkan pita Ctrl + F1 ⌘ + ⌥ + R Pilih tabel Ctrl + A ⌘ + A Pindahkan sel aktif searah jarum jam dalam pemilihan Ctrl + . ⌃ + .
