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

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

Iklan
2 Comments

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.