Seorang pembaca mengirimi saya masalah rumus yang menarik minggu ini, jadi saya pikir saya akan membagikannya sebagai tantangan rumus. Masalahnya begini:
Anda memiliki pembayaran bulanan tetap, tanggal mulai, dan jumlah bulan tertentu. Rumus apa yang dapat Anda gunakan untuk menjumlahkan total pembayaran menurut tahun, berdasarkan lembar kerja berikut:
Dengan kata lain, rumus apa yang berfungsi di E5, disalin ke I5, untuk mendapatkan jumlah setiap tahun yang ditampilkan?
Saya sendiri yang membuat rumus, tetapi saya juga ingin melihat ide-ide Anda. Jika Anda tertarik, tinggalkan komentar dengan rumus yang Anda usulkan.
Anda dapat menggunakan rentang bernama berikut dalam rumus Anda jika Anda suka: mos (C5), jumlah (C6), mulai (C7), akhir (C8).
Anda dapat mengunduh lembar kerja di bawah ini.
Jawab (klik untuk memperluas)Begitu banyak formula hebat! Terima kasih kepada semua orang yang meluangkan waktu untuk mengirimkan jawaban. Di bawah ini adalah pemikiran saya yang bertele-tele tentang masalah tersebut dan beberapa solusi di bawah ini.
Catatan: Saya tidak pernah menjelaskan bagaimana batas bulan harus ditangani. Saya baru saja mengikuti lembar kerja lain sebagai contoh. Informasi utamanya adalah 30 pembayaran, mulai 1 Maret: 10 pembayaran pada 2017, 12 pembayaran pada 2018, dan 8 pembayaran (saldo) pada 2019.
Jadi, jika Anda berjuang untuk memahami bagaimana Anda dapat mencoba memecahkan masalah seperti ini, pertama-tama fokuslah pada pembayaran. Setelah Anda mengetahui berapa banyak pembayaran dalam setahun, Anda bisa mengalikan angka itu dengan jumlahnya dan Anda selesai.
Jadi, bagaimana Anda bisa mengetahui jumlah pembayaran pada tahun tertentu? Di komentar di bawah, Anda akan menemukan banyak ide bagus. Ada beberapa pola yang saya perhatikan, dan saya telah mencantumkan beberapa di bawah ini. Ini sedang dalam proses …
Pola desain
IF + AND/OR + YEAR + MONTH
IF adalah standby tepercaya dalam banyak rumus, dan digunakan di banyak rumus yang disarankan untuk mencari tahu apakah tahun yang diinginkan adalah "dalam batas" dari tanggal mulai dan akhir. Dalam banyak kasus, IF digabungkan dengan OR atau AND untuk menjaga rumus tetap kompak.
IFERROR + DATEDIF + MAX + MIN
DATEDIF dapat mengembalikan perbedaan antara dua tanggal dalam bulan, jadi idenya di sini adalah menggunakan MAX dan MIN (untuk singkatnya, bukan IF) untuk menghitung tanggal mulai dan tanggal akhir untuk setiap tahun, dan biarkan DATEDIF mendapatkan bulan di antaranya. DATEDIF melempar kesalahan #NUM ketika tanggal mulai tidak kurang dari tanggal akhir, jadi IFERROR digunakan untuk menangkap kesalahan dan mengembalikan nol. Lihat rumus oleh 闫 强, Arun, dan David di bawah.
MAX + MIN + YEAR + MONTH
Rumus Robert dan Peter melakukan hampir semua pekerjaan dengan MAX dan MIN, tanpa IF yang terlihat. Luar biasa. Jika gagasan menggunakan MAX dan MIN untuk menggantikan IF baru bagi Anda, artikel ini menjelaskan konsepnya.
DAYS360
Fungsi Excel DAYS360 mengembalikan jumlah hari di antara dua tanggal berdasarkan tahun 360 hari. Ini adalah cara menghitung bulan berdasarkan gagasan bahwa setiap bulan memiliki 30 hari.
SUM + DATE
Ini adalah pendekatan saya yang tidak efisien (tapi elegan!) Menggunakan fungsi DATE dan konstanta array dengan angka untuk setiap bulan. fungsi DATE memutar tanggal untuk setiap bulan dalam setahun menggunakan konstanta array, dan logika boolean digunakan untuk memeriksa tumpang tindih.
