Seorang pembaca memberi saya masalah formula yang menarik minggu ini, jadi saya fikir saya akan membagikannya sebagai cabaran formula. Masalahnya ialah:
Anda mempunyai pembayaran bulanan tetap, tarikh mula, dan jumlah bulan tertentu. Formula apa yang boleh anda gunakan untuk menjumlahkan jumlah pembayaran berdasarkan tahun, berdasarkan lembaran kerja berikut:
Dengan kata lain, formula apa yang berfungsi di E5, disalin ke I5, untuk mendapatkan jumlah setiap tahun yang ditunjukkan?
Saya membuat formula sendiri, tetapi saya juga ingin melihat idea anda. Sekiranya anda berminat, tinggalkan komen dengan formula yang anda cadangkan.
Anda boleh menggunakan rentang bernama berikut dalam formula anda jika anda suka: mos (C5), jumlah (C6), mulai (C7), akhir (C8).
Anda boleh memuat turun lembaran kerja di bawah.
Jawapan (klik untuk mengembangkan)Begitu banyak formula hebat! Terima kasih kepada semua yang meluangkan masa untuk menghantar jawapan. Di bawah ini adalah pemikiran saya tentang masalah dan beberapa penyelesaian di bawah ini.
Catatan: Saya tidak pernah menjelaskan bagaimana had bulan harus dikendalikan. Saya hanya mengikuti lembaran kerja lain sebagai contoh. Maklumat utama mereka ialah 30 pembayaran, mulai 1 Mac: 10 pembayaran pada 2017, 12 pembayaran pada 2018, dan 8 pembayaran (bakinya) pada 2019.
Oleh itu, jika anda sukar untuk memahami bagaimana anda dapat menyelesaikan masalah seperti ini, fokuskan dahulu pembayaran. Setelah anda mengetahui berapa jumlah pembayaran dalam setahun, anda boleh menggandakan jumlah itu dengan jumlahnya dan anda sudah selesai.
Jadi, bagaimana anda dapat mengetahui jumlah pembayaran dalam satu tahun tertentu? Dalam komen di bawah, anda akan mendapat banyak idea bagus. Terdapat beberapa corak yang saya perhatikan, dan saya telah menyenaraikan beberapa di bawah. Ini sedang dijalankan …
Corak rekaan
IF + AND/OR + YEAR + MONTH
JIKA adalah siaga yang boleh dipercayai dalam begitu banyak formula, dan ia digunakan dalam banyak formula yang dicadangkan untuk mengetahui sama ada tahun minat "dalam batas" tarikh mula dan akhir. Dalam banyak kes, JIKA digabungkan dengan ATAU atau DAN untuk memastikan formula tetap padat.
IFERROR + DATEDIF + MAX + MIN
DATEDIF dapat mengembalikan perbezaan antara dua tarikh dalam beberapa bulan, jadi idenya di sini adalah menggunakan MAX dan MIN (untuk jangka pendek, bukannya IF) untuk mengira tarikh mula dan tarikh akhir untuk setiap tahun, dan membiarkan DATEDIF mendapatkan bulan antara. DATEDIF melemparkan ralat #NUM ketika tarikh mula tidak kurang dari tarikh akhir, jadi IFERROR digunakan untuk menangkap ralat dan mengembalikan sifar. Lihat formula oleh 闫 强, Arun, dan David di bawah.
MAX + MIN + YEAR + MONTH
Formula Robert dan Peter melakukan hampir semua kerja dengan MAX dan MIN, tanpa JIKA kelihatan. Hebat. Sekiranya idea menggunakan MAX dan MIN untuk menggantikan IF baru bagi anda, artikel ini menerangkan konsepnya.
DAYS360
Fungsi Excel DAYS360 mengembalikan bilangan hari antara dua tarikh berdasarkan tahun 360 hari. Ini adalah kaedah mengira bulan berdasarkan idea bahawa setiap bulan mempunyai 30 hari.
SUM + DATE
Ini adalah pendekatan saya yang tidak cekap (tetapi elegan!) Menggunakan fungsi DATE dan pemalar larik dengan nombor untuk setiap bulan. fungsi DATE menjarakkan tarikh untuk setiap bulan dalam setahun menggunakan pemalar larik, dan logik boolean digunakan untuk memeriksa pertindihan.
