FORMULA RUMUS EXCEL CARA MENCARI PENJUMLAHAN BERTINGKAT – MULTI LEVEL SUM DENGAN FUNGSI SUMPRODUCT – Belajar Microsoft Excel : Tips, Tricks & Tutorial

FORMULA RUMUS EXCEL CARA MENCARI PENJUMLAHAN BERTINGKAT – MULTI LEVEL SUM DENGAN FUNGSI SUMPRODUCT

Assalamualaikum Warohmatullahi Wabarokatuh

Sodara2 sekaliYan para pembatja jang boediman … artikel kali ini kembali mengambil topik MY FAVE EXCEL FUNCTION … dan sebagai bintang tamunya adalah fungsi SUMPRODUCT …. fungsi SUMPRODUCT masih sOdaraan dengan fungsi2 SUM lainya seperti SUMIF, SUMIFS, DSUM dll … dan yang menjadi Problema de Excellente yang akan dipecahkan dengan fungsi SUMPRODUCT kali ini adalah bagaimana cara melakukan penjumlahan bertingkat atau Multilevel Sum

“Penjumlahan Bertingkat” maksudnya gimana yah ❓ 🙄  🙄 🙄 🙄 …. saya sendiri jugaK pusing merangkai kata2 untuk menyusun depinisi Multilevel Sum ini … silahkan sOdara lihat aja dulu tabel berikut ini

multilevelsum2.jpg

seperti yang bisa sOdara lihat pada gambar diatas …. tabel tersebut terdiri atas 3 kolom yaitu No ,Uraian dan Jumlah … pada kolom No berisi data2 kode akun yang bisa berisi 1 digit, 2 digit , 3 digit atau 5 digit angka …. jumlah digit ini menunjukkan tingkatan / level dari kode akun tersebut

  • Level 1 adalah kode akun yang terdiri atas 1 digit angka (warna biru)
  • Level 2 adalah kode akun yang terdiri atas 2 digit angka (warna hijau)
  • Level 3 adalah kode akun yang terdiri atas 3 digit angka (warna ungu)
  • Level 4 adalah kode akun yang terdiri atas 5 digit angka (warna abu-abu)

Pada gambar diatas Level 4 telah berisi data … sedangkan level lainnya masih harus dicari jumlahnya dengan formula … aturannya sbb:

  • Level 3 menjumlahkan kode2 yang 3 digit angka depannya sama, misal kode 113 berisi jumlah dari kode 11301 , 11302 dan 11303
  • Level 2 menjumlahkan kode2 yang 2 digit angka depannya sama … misal kode 11 berisi jumlah dari kode 111 , 113 , 116 dst
  • Level 1 menjumlahkan kode2 yang 1 digit angka depannya sama … misal kode 1 berisi jumlah dari kode 11 , 12 , 13 dst

Okeeh mari kita coba memecahkan Problema de Excellente  dengan fungsi SUMPRODUCT … fungsi SUMPRODUCT akan mengalikan komponen-komponen terkait dalam array yang diberikan, dan hasilnya adalah jumlah dari setiap hasil perkalian tersebut.

Sintaks fungsi SUMPRODUCT ini sebagai berikut

SUMPRODUCT(array1, [array2], [array3], …)

Sintaks fungsi SUMPRODUCT memiliki argumen berikut:

  • array1 … argumen ini diperlukan jadi harus diisi … merupakan argumen array pertama yang komponen-komponennya ingin sOdara kalikan lalu tambahkan
  • array2, array3,… argumen ini bersifat Opsional … argumen array ke-2 sampai ke-255 yang komponen-komponennya ingin sOdara kalikan lalu tambahkan

Dalam SUMPRODUCT argumen yang berupa array harus memiliki dimensi yang sama. Jika tidak, SUMPRODUCT memberikan hasil nilai kesalahan/error #VALUE! … fungsi  SUMPRODUCT akan menganggap data2 dalam array yang bukan numerik seperti angka nol … simak penampakan gambar ilustrasi berikut

SUMPRODUCT.jpg

Gambar diatas mengilustrasikan bagaimana fungsi SUMPRODUCT bekerja … seperti yang sOdara lihat pada gambar diatas … tabel menempati Range B3:C7 … dalam penjelasan diatas sudah saya kasih cetak tebal bahwa fungsi SUMPRODUCT akan mengalikan komponen dalam kemudian akan menambahkan / menjumlahkannya

adapun yang dikalikan adalah array1 (B3:B7) dengan array2 (C3:C7) … hasilnya seperti yang ada pada Range E3:E7 … hasil perkalian tersebut kemudian dijumlahkan seperti yang bisa sOdara lihat pada E9 … dan hasil penghitungannya adalah 80

Langkah2 diatas bisa disingkat dengan menggunakan fungsi SUMPRODUCT … caranya cukup dengan bikin formula berikut seperti pada E11

=SUMPRODUCT(B3:B7*C3:C7)

Dan hasilnya sama yaitu 80 … dengan ilustrasi dan penjelasan tadi semoga sOdara2 sekaliYan bisa memahami cara kerja fungsi SUMPRODUCT ini

selanjutnya kita aplikasikan fungsi SUMPRODUCT ini pada kasus Problema de Excellente diatas … untuk menyelesaikan penghitungan tersebut fungsi SUMPRODUCT perlu dikombinasikan dengan beberapa fungsi lainnya karena syarat penjumlahannya cukup komplek … adapun fungsi yang diperlukan tersebut adalah :

  • fungsi LEFT, fungsi ini akan memberikan hasil berupa karakter pertama atau beberapa karakter dalam string teks berdasarkan jumlah karakter yang bisa sOdara tentukan.
    Misal =LEFT(“superman”;5) akan menghasilkan kata “super”
  • fungsi LEN , fungsi ini digunakan untuk menghitung jumlah karakter dalam string
    misal =LEN(“superman”) akan menghasilkan 8

dan berikut adalah formula hasil kombinasi fungsi2 diatas untuk menyelesaikan Problema de Excellente diatas … pertama kita bahas dulu formula pada cell D3 yaitu

=SUMPRODUCT((LEFT(B4:$B$76;LEN(B3))=B3)*(D4:$D$76)*(LEN(B4:$B$76)=5))

Mari kita bedah formulanya dan untuk lebih jelasnya silahkan sOdara lihat gambar berikut

segment.jpg
seperti yang sOdara lihat … formula diatas terbagi menjadi beberapa segmen

segmen 1 (LEFT(B4:$B$76;LEN(B3))=B3)

LEN(B3) adalah jumlah karakter pada B3 … hasilnya 1

LEFT(B4:$B$76;LEN(B3)) maksudnya adalah karakter ke-1 dari data2 pada Range B4:$B$76 … hasilnya bisa 1 , 2 atau 3

Sehingga LEFT(B4:$B$76;LEN(B3))=B3 … bisa diterjemahkan sebagai berikut

“karakter ke-1 dari data2 pada Range B4:$B$76 sama dengan 1”

Hasilnya bisa TRUE jika sama dengan 1 atau FALSE jika tidak sama dengan 1 … nilai TRUE adalah 1 dan FALSE adalah 0 … seperti yang bisa sOdara lihat pada Range F4:F76

segmen 2 (D4:$D$76)

Cukup jelas … data2 pada range D4:$D$76 … seperti yang bisa sOdara lihat pada G4:G76

segmen 3 (LEN(B4:$B$76)=5)

LEN(B4:$B$76) adalah jumlah / panjang karakter pada B3 … hasilnya bisa 1 , 2 , 3 atau 5

Sehingga LEN(B4:$B$76)=5 … bisa diterjemahkan

“jumlah karakter dari data2 pada Range B4:$B$76 sama dengan 5”

hasilnya bisa TRUE jika sama dengan 5 atau FALSE jika tidak sama dengan 5 … nilai TRUE adalah 1 dan FALSE adalah 0 … seperti yang bisa sOdara lihat pada H4:H7

Hasil dari tiga segmen tersebut lalu dikalikan seperti yang lihat pada Range I4:I76 … kemudian djumlahkan seperti pada I78

seperti itulah ilustrasi dari formula yang ada pada cell D3 … semoga sOdara sekaliYan bisa memahaminya … formula tersebut kemudian bisa langsung sOdara copy ke baris2 dibawahnya baik untuk Level 1 , Level 2 atau Level 3Level 4 enggak dong kan disini letak datanya 🙂 😉

multilevelsum.jpg

demikiyan penjelasan singkat tentang penggunaan fungsi SUMPRODUCT untuk memecahkan masalah penjumlahan bertingkat … silahkan sOdara download contoh filenya MultiLevelSum.xls yang bisa sOdara download gratis dari folder 2017/08AUG …. cukup sekiYan artikel kali ini … semoga manpaat bagi sOdara2 sekaliYan …. dan MDLMDL

bingung cara downloadnya ??

Baca aja halaman download dengan klik icon di samping

feed , email and my social media

2 Comments

Jika sOdara menemukan sesuatu yang bermanpaat di marih .... silahkan kasih komennya