Monday, June 25, 2018

Rumus Penjumlahan Setiap Baris ke-N

Dalam artikel sebelumnya sudah dibahas mengenai contoh rumus untuk menjumlahkan nilai setiap beberapa baris konstan atau setiap N Baris. Masih ada kaitan dengan pembahasan tersebut, dalam kesempatan ini akan dibahas contoh rumus untuk menjumlahkan nilai setiap kelipatan baris tertentu atau setiap baris ke-N dari sebuah range (harap dibedakan: pembahasan sebelumnya mengenai penjumlahan setiap N baris, sedangkan pada pembahasan kali ini mengenai penjumlahan setiap baris ke-N)

Untuk membuat rumus penjumlahan setiap baris ke-N diperlukan kombinasi beberapa fungsi, antara lain: SUMPRODUCT, MOD, dan ROW.

Contoh Rumus:



=SUMPRODUCT((MOD(ROW(B5:B14)-9,5)=0)*B5:B14)

Contoh Rumus Excel Menjumlahkan Setiap Baris ke-N


Contoh rumus tersebut digunakan untuk menjumlahkan setiap kelipatan baris ke-5 pada range B5:B14, dimulai dari baris ke-9.

Rumus Umum:


=SUMPRODUCT((MOD(ROW(range)-barisAwal,N)=0)*range)

Penjelasan:


  • Range = range berisi data nilai yang akan dijumlahkan setiap baris ke-N nya, contoh range B5:B14
  • barisAwal = baris start untuk memulai penjumlahan, contoh baris 9, karena bilangan pertama yang ingin kita jumlahkan terletak pada baris ke-9, dan selanjutnya akan dijumlahkan dengan baris ke-5 berikutnya yaitu baris (9+5) = baris ke-14 (perhatikan gambar di atas)
  • N = Nilai kelipatan baris yang ingin kita jumlahkan.  Contoh : setiap baris ke-5


Cara Kerja Rumus


Perhatikan struktur contoh rumus:

=SUMPRODUCT((MOD(ROW(B5:B14)-9,5)=0)*B5:B14)

Silahkan dilihat bagian rumus ROW(B5:B14)-9

Bagian rumus tersebut berguna untuk menghitung nomor urut baris pada range B5:B14 relative terhadap baris ke-9. Untuk melihat nilainya, dalam spreadsheet, silahkan diseleksi bagian rumus ROW(B5:B14)-9   dan tekan F9, maka rumus akan menjadi:

=SUMPRODUCT((MOD({-4;-3;-2;-1;0;1;2;3;4;5},5)=0)*B5:B14)

Selanjutnya fungsi MOD berguna untuk menghitung sisa pembagian. Jika bilangan dalam array habis di bagi lima maka fungsi MOD akan menghasilkan bilangan Nol. Bilangan dalam array ({-4;-3;-2;-1;0;1;2;3;4;5} yang memenuhi kriteria habis di bagi 5 adalah bilangan 0 dan 5. 

Selanjutnya fungsi MOD dikompare dengan bilangan 0 untuk mendapatkan nilai boolean TRUE dan FALSE.

Silahkan di seleksi bagian rumus (MOD({-4;-3;-2;-1;0;1;2;3;4;5},5)=0) kemudian tekan F9, sehingga rumus menjadi sebagai berikut:

=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}*B5:B14)

Perhatikan nilai TRUE dalam array berada tepat setiap urutan kelipatan ke-5.
Dengan cara yang sama, seleksi bagian rumus B5:B14 dan kemudian tekan F9 untuk analisa rumus lebih lanjut:

=SUMPRODUCT({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}*{2;1;2;3;8;2;3;4;4;13})

Sebagaimana kita ketahui, dalam operasi perkalian, nilai boolean TRUE akan diperlakukan sebagai bilangan 1, sedangkan nilai FALSE sebagai bilangan 0. 

Karena nilai TRUE terletak pada urutan  ke-5 dan ke-10, maka hasil akhirnya akan berupa penjumlahan elemen ke-5 dan ke-10 pada array {2;1;2;3;8;2;3;4;4;13} yaitu 8 + 13 = 21.

Sampai disini pembahasan mengenai contoh rumus untuk penjumlahan setiap baris ke-N. Mudah-mudahan penjelasan yang sangat singkat ini dapat mudah difahami.

Contoh Rumus Excel Lainnya


No comments:

Post a Comment

Terimakasih sudah berkunjung, Silahkan berkomentar.