Tuesday, May 29, 2018

Rumus Penjumlahan Berdasarkan Grup Bulan

Menjumlahkan nilai berdasarkan grup bulan yang akan dibahas dalam kesempatan ini tidak mempertimbangkan tahun. Artinya data pada bulan yang sama namun tahun berbeda akan turut terjumlah. Oleh karena itu cara ini disarankan hanya digunakan terhadap satu tahun periode data saja. Adapun fungsi yang digunakan adalah SUMPRODUCT dan MONTH. Sebagai alternative, juga boleh menggunakan kombinasi SUM dan MONTH dalam bentuk formula array.

Contoh:



 =SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Contoh Rumus Excel Penjumlahan Grup Bulan



Rumus Umum:


=SUMPRODUCT((MONTH(rgTanggal)=MONTH(bulan))*rgJumlah)

Penjelasan:


rgTanggal = range berupa sebuah kolom dalam tabel berisi data ber-type tanggal (date)

bulan = sebuah sel berisi type data tanggal dengan format “mmmm” nampak nama bulannya saja. Contoh: tgl 1/1/2018 dengan format "mmmm" akan nampak sebagai bulan "January"

rgJumlah = range berupa sebuah kolom yang sejajar dengan rgTangal berisi nilai – niai yang akan dijumlahkan

Cara Kerja Rumus


Perhatikan contoh rumus sesuai gambar pada sel E5 sesuai gambar di atas:
=SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Mari kita pecah rumus tersebut menjadi beberapa langkah:

Langkah 1:  
=(MONTH(A5:A14)=MONTH(D5) )

Expresi ini berguna untuk mengecek nilai bulan pada masing-masing sel di dalam range A5:A14 apakah sama atau tidak dengan nilai bulan pada data tanggal di sel D5. Proses ini menghasilkan array berisi  data boolean TRUE dan FALSE.  Pada formula bar silahkan diseleksi bagian rumus (MONTH(A5:A14)=MONTH(D5)), kemudian tekan F9, maka kita akan memperoleh sebuah array berisi TRUE dan FALSE

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Langkah 2:  
=(MONTH(A5:A14)=MONTH(D5) * B5:B14
= hasilLangkah1 * B5:B14

Langkah ke-2 ini akan mengalikan hasil dari langkah ke-1 dengan range B5:B14
Nilai dalam range B5:B14 jika dituliskan dalam bentuk array adalah:
{2;1;2;3;4;2;3;4;4;4}

Sehingga rumus =hasilLangkah1*B5:B14 dapat dijabarkan sebagai berikut:
={TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{2;1;2;3;4;2;3;4;4;4}

Sebagai mana  kita ketahui TRUE sepadan dengan nilai 1, sedangkan FALSE sepadan dengan nol. Sehingga perkalian tersebut akan menghasilkan array berikut:

={2;1;0;3;0;0;0;0;0;0}

Hasil dari langkah 2 ini jika berdiri sendiri hanya akan menghasilkan nilai 2 yaitu elemen pertama dalam array. Untuk menjumlahkan semua elemen dalam array, maka diperlukan bantuan fungsi SUMPRODUCT

Langkah 3:  
=SUMPRODUCT((MONTH(A5:A14)=MONTH(D5))*B5:B14)
=SUMPRODUCT(hasilLangkah2)

Fungsi SUMPRODUCT berguna untuk menjumlahkan semua elemen array hasil langkah 2, sehingga rumus dapat dijabarkan sebagai berikut:

=SUMPRODUCT({2;1;0;3;0;0;0;0;0;0})

Dan hasil akhirnya adalah 6. Hasil ini merupakan penjumlahan semua nilai pada bulan Januari.

Catatan: 
Pada penjelasan di atas semua alamat range menggunakan referensi relative dengan tujuan supaya lebih mudah membacanya. Agar referensi rgTanggal dan rgJumlah tidak bergeser pada saat rumus di copy paste ke sel di bawahnya, maka disarankan menggunakan referensi absolute. Oleh karenanya contoh rumus di atas dapat diedit kembali menjadi sebagai berikut:

=SUMPRODUCT((MONTH($A$5:$A$14)=MONTH(D5))*$B$5:$B$14)

Alternative Rumus Penjumlahan Berdasarkan Grup Bulan.

Selain menggunakan SUMPRODUCT, kita juga bisa memanfaatkan fungsi SUM dalam bentuk formula array.

Caranya:

Ketik rumus berikut di sel E5

=SUM((MONTH(A5:A14)=MONTH(D5))*B5:B14)

Begitu selesai mengetik rumus, lanjutkan dengan menekan Ctrl + Shift + Enter untuk menjadikannya sebagai formula array, maka pada formula Bar kita akan melihat rumus tadi berada dalam tanda kurung kurawal {}.

{=SUM((MONTH(A5:A14)=MONTH(D5))*B5:B14)}

Cara kerja rumus array ini hampir sama dengan rumus SUMPRODUCT.

Demikian pembahasan singkat mengenai contoh rumus excel untuk rekapitulasi penjumlahan nilai berdasarkan grup bulan.

Contoh Rumus Excel Lainnya



2 comments:

  1. hallo kak, aku coba rumusnya kenapa hasilnya 0 ya?

    ReplyDelete
  2. hallo kak, kalau mau membuat rumus bulan selanjutnya gimana ya...
    misal di kolom A1 tertulis January, nah di A2 mau nya otomatis jadi February
    atas bantuannya terima kasih

    ReplyDelete

Terimakasih sudah berkunjung, Silahkan berkomentar.