Rumus Excel Sumproduct - Satu Fungsi, Multi Guna.
Apa bergotong-royong kegunaan fungsi SUMPRODUCT? Mengapa fungsi ini dikatakan sebagai fungsi multi guna?. Mari kita lihat jawabannya dalam catatan pelajaran excel berikut ini.
Belajar excel kali ini memang khusus dipersembahkan untuk membahas mengenai rumus excel SUMPRODUCT dan kegunaan praktisnya.
Bahasan dimulai yang paling sederhana yaitu pengenalan basic syntax dan cara penggunaan fungsi tersebut. Kemudian pada penggalan selanjutnya akan dibahas contoh-contoh kegunaan mudah fungsi SUMPRODUCT, termasuk kegunaannya sebagai alternative rumus SUMIFS, COUNTIFS, AVERAGEIFS, Lookup 2 dimensi, dan Rumus Array CSE.
- Syntax dan Cara Penggunaan Fungsi SUMPRODUCT
- Contoh-Contoh Formula Bagaimana Menggunakan Fungsi SUMPRODUCT Pada Excel
- Rumus SUMPRODUCT Perhitungan Dengan Satu Kondisi
- Cara Kerja Rumus SUMPRODUCT Satu Kondisi
- Rumus SUMPRODUCT Perhitungan Dengan Kondisi Ganda
- Cara Kerja Rumus SUMPRODUCT Dua Kondisi
- Rumus SUMPRODUCT Untuk Menghitung Frekuensi Data, Menjumlah dan Merata-Rata Dengan Kriteria.
- Rumus SUMPRODUCT Untuk Menghitung Berat Rata-Rata
- Rumus SUMPRODUCT Sebagai Alternative Lookup 2 Dimensi
- Rumus SUMPRODUCT Sebagai Alternative Rumus Array
Apa yang terbayangkan oleh anda sewaktu pertama kali mendengar istilah rumus SUMPRODUCT? Mungkin anda mengenali fungsi ini sebagai campuran antara SUM dan PRODUCT.
Mengenai fungsi SUM, saya kira secara umum dikuasai pengguna excel sudah sangat familiar dengannya.
Betapa tidak, fungsi penjumlahan yang satu ini merupakan salah satu fungsi yang paling banyak digunakan. Bahkan termasuk oleh pengguna yang masih awam sekalipun.
Pembahasan lebih rinci mengenai fungsi SUM sanggup dibaca dalam artikel Fungsi SUM dan Rumus Penjumlahan Pada Excel
Bagaimana dengan fungsi PRODUCT?
Untuk yang satu ini, mungkin masih ada pengguna excel yang belum familiar. Padahal kenyataannya fungsi ini memang tersedia pada microsoft excel.
Fungsi PRODUCT dipakai untuk mendapat hasil perkalian antara dua buah bilangan atau lebih.
Contoh:
Rumus =PRODUCT(bil1,bil2,bil3) sama saja balasannya dengan rumus =bil1*bil2*bil3.
Itulah mengapa, nampaknya fungsi PRODUCT kurang diminati dan jarang digunakan. Hal ini alasannya rumus perkalian lebih familiar dan lebih gampang difahami oleh kebanyakan user dibandingkan fungsi PRODUCT.
Bagaimana dengan SUMPRODUCT?
SUMPRODUCT sungguh merupakan sebuah fungsi excel yang multiguna.
Jika anda pengguna excel 2003 atau versi yang lebih lama, maka anda sanggup memakai fungsi SUMPRODUCT untuk melaksanakan penjumlahan / perhitungan bersyarat ganda.
Sedangkan pada Excel 2007 dan versi yang lebih baru, kiprah tersebut juga sanggup dilakukan oleh fungsi SUMIFS dan COUNTIFS. Meskipun demikian, Fungsi SUMPRODUCT juga tetap sanggup dipakai pada versi ini.
Fungsi SUMPRODUCT juga sanggup dipakai untuk melaksanakan VLOOKUP 2 dimensi, yaitu rumus untuk mencari nilai dalam sel yang merupakan titik temu antara dua kriteria yang terletak dalam baris dan kolom.
Harap diperhatikan setiap contoh-contoh rumus yang akan dibahas. Jika anda sudah mengenal dan memahami rumus array, anda niscaya akan menemukan kemiripan antara rumus array dengan SUMPRODUCT.
Dan memang SUMPRODUCT bekerja terhadap array, meskipun dalam aplikasinya tidak perlu memakai shortcut CSE menyerupai biasanya jikalau kita memakai rumus array.
Syntax dan Cara Penggunaan Fungsi SUMPRODUCT
Pada dasarnya fungsi SUMPRODUCT bekerja untuk melaksanakan perkalian antara bilangan dalam array, dan lalu menjumlahkan hasil perkalian tersebut.
Adapun syntax fungsi SUMPRODUCT sangat simple dan sangat jelas.
SUMPRODUCT(array1, [array2], [array3], …)
Array1, array2 dan seterusnya ialah range sel atau array yang masing-masing elemennya ingin kita kalikan lalu hasil perkaliannya dijumlahkan.
Jumlah maksimum argumen array yang sanggup dipakai dalam fungsi SUMPRODUCT ialah 255 pada excel 2007,2010,2013 dan 2016. Sedangkan excel 2003 atau versi yang lebih lama, hanya mengizinkan maksimal 30 array.
Meskipun SUMPRODUCT bekerja dengan data array, tetapi fungsi ini tidak memerlukan penggunan shortcut array (CTR + Shift + Enter). Kita cukup menekan Enter sesudah mengetikan formula, menyerupai halnya rumus biasa.
Hal-hal yang perlu diperhatikan seputar penggunaan Fungsi SUMPRODUCT:
- Semua aray dalam rumus SUMPRODUCT harus mempunyai jumlah baris dan kolom yang sama. Jika tidak, maka rumus SUMPRODUCT akan menghasilkan nilai error.
- Jika array mengandung elemen yang bukan bilangan, maka elemen tersebut akan diperlakukan sebagai angka Nol.
- Jika elemen array berupa test budi yang bernilai TRUE atau FALSE, maka dalam kebanyakan kasus, kita perlu mengkonversinya menjadi 1 atau 0 memakai double unary operator (--), atau memakai fungsi SIGN
- Fungsi SUMPRODUCT tidak mendukung penggunaan abjad wildcard.
Dasar-Dasar Penggunaan Fungsi SUMPRODUCT pada Excel.
Untuk membantu memahami cara kerja fungsi SUMPRODUCT, mari kita perhatikan teladan masalah dibawah ini:
Anggaplah anda mempunyai daftar belanja buah-buahan yang dibentuk memakai microsoft excel.
- Kolom A berisi nama daftar belanja buah-buahan
- Kolom B berisi Kuantitas belanjaan
- Kolom C berisi data harga per satuan.
Pertanyannya: Bagaimana cara kita menghitung (tanpa memakai kolom bantu) banyaknya biaya yang diperlukan?
Mari kita lihat gambar berikut:
Perhatikan:
Untuk mendapat jumlah biaya yang diperlukan, kita sanggup menghitungnya memakai rumus perkalian dan penambahan biasa.
=B2*C2+B3*C3+B4*C4+B5*C5
Atau jikalau rujukan tersebut diganti dengan bilangan, maka rumus sanggup dituliskan sebagai berikut:
=3*10000+4*5000+5*8000+3*6000
=108.000
Rumus diatas memang sangat gampang untuk difahami. Cara kerjanya sederhana, cukup dengan cara mengalikan quantity produk dengan harganya, lalu balasannya dijumlahkan dengan hasil perkalian quantity dan harga produk lainnya.
Bayangkan…
Seandaiya kita mempunyai ratusan hingga ribuan barang yang harus dihitung..
Rumus model diatas, tampaknya bukanlah solusi yang tepat, kecuali jikalau anda mempunyai kesabaran tingkat dan ketelitian tinggi untuk mengetikan rumusnya.
Solusinya: Saatnya beralih ke penggunaan fungsi SUMPRODUCT.
=SUMPRODUCT(B2:B5,C2:C5)
Dengan rumus tersebut, sebanyak apapun baris data yang digunakan, anda tidak perlu repot mengetikan rumus yang panjang. Yang diharapkan hanya adaptasi rujukan range datanya saja.
Misalnya: jikalau baris data mencapai 1000, maka atur saja batas final range yang digunakan, contohnya B2:B1000 dan C2:C1000.
Menurut saya, cara ini sangat Simple dan Mudah.
Bagaimana berdasarkan anda?
Contoh-Contoh Rumus – Bagaimana Menggunakan Fungsi SUMPRODUCT pada Excel
Melakukan perkalian pasangan elemen-elemen antara dua atau lebih range dan lalu menjumlahkan hasil perkaliannya merupakan bentuk paling fundamental dan sederhana dari penggunaan fungsi SUMPRODUCT pada Excel.
Akan tetapi, keanggunan dan kehebatan fungsi SUMPRODUCT tidak terbatas pada definisi dasarnya saja. Ada banyak kiprah kompleks yang sanggup diselesaikan memakai fungsi multiguna ini.
Kehebatan apa saja yang sanggup diperoleh memakai fungsi SUMPRODUCT?
Mari kita bongkar satu persatu dalam contoh-contoh rumus yang akan digali lebih jauh dalam berguru excel kali ini.
Silahkan dibaca terus artikel ini ya 😉
Fungsi SUMPRODUCT Dengan Satu Kondisi
Semakin usang dan sering memakai excel, maka kita akan menyadari bergotong-royong ada banyak jalan untuk menuntaskan kiprah excel tertentu.
Namun untuk kaitan membandingkan dua atau lebih array, terutama yang bekerjasama dengan kalkulasi yang bersyarat ganda, maka hanya ada dua cara yang paling efektif, yaitu: Menggunakan Rumus Array Atau memakai rumus SUMPRODUCT..
Mengenai rumus array sudah dibahas dalam artikel Memahami Rumus Array Untuk Pemula. Sedangkan untuk rumus SUMPRODUCT, inilah inti pembahasan berguru excel kali ini.
Anggaplah kita mempunyai tabel dalam lembar kerja excel dengan susunan sebagai berikut:
- Kolom A berisi list produk
- Kolom B berisi sasaran penjualan (Rp)
- Kolom C berisi faktual penjualan (Rp)
- Untuk contoh, baris data dibatasi s.d baris 10.
Pertanyaan: Bagaimana cara menghitung banyaknya item produk yang tidak mencapai sasaran penjualan?
Jawaban: Gunakan fungsi SUMPRODUCT
Caranya: Ketikan rumus dibawah ini:
=SUMPRODUCT(--(C2:C10<B2:B10))
atau
=SUMPRODUCT((C2:C10<B2:B10)*1)
Bagaimana Cara Kerja Rumus SUMPRODUCT dengan Satu Kondisi
Mari kita lihat kembali lebih dalam, teladan rumus SUMPRODUCT untuk menghitung berapa kali nilai sel pada range C2:C10 lebih kecil dibandingkan dengan nilai sel sejajar pada range B2:B10.
=SUMPRODUCT(--(C2:C10<B2:B10))
Bagaimana rumus tersebut sanggup memperlihatkan nilai final = 4 ?
Dengan memakai mouse, seleksi porsi (--(C2:C10<B2:B10) pada formula bar, lalu tekan F9. Untuk lebih jelasnya perhatikan gambar berikut:
Kita akan melihat array yang berisi nilai boolean TRUE dan FALSE.
Perhatikan kembali budi memakai operator kurang dari (<).
Operator ini akan mengecek apakah nilai sel pada range C2:C10 kurang dari nilai sel sejajar pada range B2:B10. Jika iya maka akan bernilai TRUE (benar), dan jikalau kondisi sebaliknya maka akan bernilai FALSE (salah)
Selanjutnya, apa guna tanda minus kembar (--) atau yang secara tehnik disebut double unary operator?
Operator ini dipakai untuk mengkorversi nilai TRUE dan FALSE menjadi 1 dan 0.
Melanjutkan langkah berikutnya, pada formula kafetaria seleksi penggalan rumus berikut,
--{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}
Kemudian tekan F9. Akan lebih gampang memahami dengan memperhatikan ilustrasi berikut:
Dari ilustrasi diatas, kita sanggup melihat bahwa nilai TRUE berkembang menjadi 1, sedangkan FALSE berkembang menjadi 1 dan 0. Inilah gunanya tanda minus kembar, atau double unary operator, merubah nilai budi menjadi numerik sehingga sanggup dikalkulasi lebih lanjut.
Tahap akhir; Bilangan 1 dan 0 dijumlahkan sehingga menghasilkan nilai final = 4
Masih ingin tahu alternative lainnya?
Double unary operator ternyata sanggup digantikan oleh fungsi SIGN
Cobalah: buat rumus =SIGN(TRUE) atau =SIGN(FALSE) dan perhatikan hasilnya. Bandingkan dengan rumus =--(TRUE) atau =--(FALSE)
Hasilnya sama saja kan ?
Sehingga formula =SUMPRODUCT(--(C2:C10<B2:B10)) sanggup diganti menjadi =SUMPRODUCT(SIGN(C2:C10<B2:B10))
Ternyata masih ada Alternative Lainnya:
Selain memakai double unary operator dan fungsi SIGN, kita juga sanggup mengkonversi nilai budi TRUE dan FALSE dengan cara mengalikannya dengan bilangan 1.
=SUMPRODUCT((C2:C10<B2:B10)*1)
Jika tidak percaya, silahkan lakukan ujicoba rumus =TRUE*1 atau =FALSE*0, dan lihat hasilnya.
Sampai pada tahap ini, mudahan-mudahan cara kerja perhitungan dengan satu kondisi sudah sanggup difahami.
Rumus SUMPRODUCT dengan Dua Kondisi
Selanjutnya mari kita pelajari lebih jauh dengan teladan rumus untuk menghitung banyaknya data yang memenuhi beberapa kondisi tertentu atau bersyarat ganda.
Kita ambil saja 2 kondisi sebagai contoh, alasannya sebanyak apapun kondisi yang digunakan, prinsip kerjanya sama saja.
Anggaplah kita sebagai penjual buah-buahan. Misalnya ingin menghitung berapa kali penjualan buah mangga yang tidak mencapai target.
Untuk tujuan tersebut, kita sanggup memakai rumus berikut:
=SUMPRODUCT(--(A2:A10="mangga"),--(C2:C10<B2:B10))
Atau
=SUMPRODUCT((A2:A10="mangga")*(C2:C10<B2:B10))
Bagaimana Cara Kerja Rumus SUMPRODUCT Dengan Kriteria Ganda
Sebagaimana kita ketahui pada pembahasan awal, bahwa fungsi SUMPRODUCT dipakai untuk melaksanakan perkalian elemen sejajar dalam array lalu menjumlahkan hasilnya.
Mari kita perhatikan kembali rumus untuk menghitung berapa kali penjulan buah mangga yang tidak mencapai sasaran sesuai teladan sebelumnya.
=SUMPRODUCT(--(A2:A10="mangga"),--(C2:C10<B2:B10))
Bagaimana sanggup diperoleh hasil = 2 ?
- Seleksi sel dimana rumus tersebut ditempatkan
- Pada formula bar, seleksi penggalan rumus (A2:A10="mangga"), lalu tekan F9, maka kita akan dapatkan {TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
- Nilai boolean TRUE dan false tersebut harus dikonversi menjadi data numerik, salah satunya ialah memakai double unary operator (--), cobalah seleksi hasil tahap sebelumnnya, tetapi termasuk 2 tanda negatif yang mendahuluinya yaitu --{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE} , lalu tekan F9, maka balasannya ialah {1;0;0;0;1;0;0;0;1}
- Dengan cara dan tahapan yang sama, lakukan pada porsi rumus (C2:C10<B2:B10)
- Secara keseluruhan, rumus menjadi =SUMPRODUCT({1;0;0;0;1;0;0;0;1},{0;1;0;0;1;0;0;1;1})
- Ingat kembali cara kerja SUMPRODUCT untuk mengalikan elemen sejajar, lalu menambahkan masing-masing hasilnya. Maka formula tersebut sanggup dijelaskan dengan rumus =(1*0)+(0*1)+(0*0)+(0*0)+(1*1)+(0*0)+(0*0)+(0*1)+(1*1)
- Maka balasannya ialah 2
Menghitung Banyak data, Menjumlah dan Merata-rata Dengan Kriteria Ganda
Mulai excel versi 2007, kita mengenal adanya fungsi COUNTIFS, SUMIFS dan AVERAGEIFS untuk menghitung banyak (frekuensi) data, menjumlahkan dan merata-rata dengan kriteria ganda.
Pada excel 2003 dan versi yang lebih lama, ketiga fungsi tersebut belum tersedia.
Namun bergotong-royong kiprah ketiga fungsi tersebut sanggup dikerjakakan oleh fungsi SUMPRODUCT. Inilah salah satu alasan kenapa SUMPRODUCT disebut sebagai fungsi Multiguna.
Jadi jangan khawatir meskipun komputer anda sendiri atau komputer di kantor masih memakai excel 2003, alasannya SUMPRODUCT tersedia di semua versi excel, setidaknya pada versi 2003 s.d 2016 yang sudah saya gunakan. Untuk versi excel yang lebih renta dari 2003 silahkan dicek sendiri ya, kebetulan saya tidak punya versinya. 😓
1. SUMPRODUCT dengan Logika AND
Yang dimaksud budi AND ialah dimana kombinasi dua pernyataan atau lebih akan bernilai benar (TRUE) jikalau semua pernyataan bernilai (Benar)
Anggaplah kita mempunyai data berikut yang merupakan data penjualan buah-buahan pada banyak sekali tempat dari tanggal 1 s.d 12 Desember.
Bagaimana cara mengetahui jumlah penjualan di tempat Ciamis, Berapa kali penjualannya dan Berapa rata-ratanya?
Disini kita memakai budi AND, yaitu ada dua kenyataan yang harus sama sama benar (TRUE) yaitu:
- Lokasi penjualan = Ciamis
- Nama Buah = Mangga
Kedua hal tersebut harus terpenuhi sebagai syarat kalkulasi.
Seperti halnya dalam rumus array CSE, budi AND dalam rumus SUMPRODUCT juga sanggup dipenuhi memakai derma operator asterisk (*)
Perhatikan teladan rumus berikut dengan perkiraan baris terakhir data ialah baris 13.
• Berapa Jumlah Penjualan buah mangga di tempat Ciamis?
=SUMIFS(D2:D13,B2:B13,"Ciamis",C2:C13,"Mangga")
Atau
=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13))
• Berapa kali penjualan buah mangga di tempat Ciamis?
=COUNTIFS(B2:B13,"Ciamis",C2:C13,"Mangga",D2:D13,">0")
Atau
=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13>0))
• Berapa rata-rata Penjualan buah mangga di tempat Ciamis?
=AVERAGEIFS(D2:D13,B2:B13,"Ciamis",C2:C13,"Mangga"))
Atau
=SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13))/SUMPRODUCT((B2:B13="Ciamis")*(C2:C13="Mangga")*(D2:D13>0))
Dari teladan cotoh rumus diatas, sudah sangat terperinci bahwa fungsi SUMPRODUCT sanggup menggantikan fungsi SUMIFS, COUNTIFS dan AVERAGEIFS, meskipun sebagai pengganti AVERAGEIFS, perlu rumus yang lebih panjang.
Supaya lebih fleksible, kita sanggup menaruh kriteria perhitungan dalam sebuah sel
Misalnya
- Kriteria tempat pemasaran disimpan di sel C16
- Kriteria nama buah disimpan di sel C17
Maka rumus penjumlahan, frekuensi dan rata-rata sanggup dituliskan sebagai berikut:
Jumlah : =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13))
Frekuensi: =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13>0))
Rata-rata: =SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13))/SUMPRODUCT((B2:B13=C16)*(C2:C13=C17)*(D2:D13>0))
Untuk menghitung rata-rata, alasannya merupakan hasil pembagian jumlah dengan frekuensi maka rumusnya sanggup memakai rujukan lokasi rumus jumlah dan frekuensi. Perhatikan ilustrasi berikut:
CONTOH2: Rumus SUMPRODUCT Dengan Logika OR
Logika OR akan bernilai benar jikalau kombinasi pernyataan mempunyai minimal satu pernyataan yang bernilai benar (TRUE).
Misalnya:
Untuk menghitung berapa banyak penjualan buah mangga dan buah pisang pada tabel teladan sebelumnya.
Mangga dan pisang sama-sama terletak dalam satu kolom dan kedua nya harus diperhitungkan dalam proses kalkulasi.
Ini artinya kita harus menciptakan rumus yang menghasilkan nilai TRUE apabila salah satu kondisi terpenuhi.
Kondisi tersebut adalah:
- Nama buah = "mangga" atau "pisang"
Seperti halnya juga dalam rumus array, budi OR dalam rumus SUMPRODUCT sanggup dibantu memakai operator plus (+).
Perhatikan teladan rumus berikut:
Berapa kali (frekuensi) buah mangga dan pisang yang terjual tanpa memandang lokasi penjualannya
=SUMPRODUCT((C2:C13="mangga")+(C2:C13="pisang"))
Berapa nilai total penjualan buah mangga dan pisang?
=SUMPRODUCT((C2:C13="mangga")+(C2:C13="pisang"))
Atau
=SUMPRODUCT(((C2:C13="mangga")+(C2:C13="pisang"))*D2:D13)
Contoh 3: Formula SUMPRODUCT Dengan Kombinasi Logika AND dan OR
Dalam beberapa kasus, mungkin kita perlu memperhitungkan beberapa kondisi dengan budi AND dan OR sekaligus.
Sayangnya bahkan dalam versi excel terbaru pun, Satu fungsi SUMIFS dan COUNTIFS tidak sanggup menghandle masalah ini. Salah satu cara yang sanggup dilakukan jikalau tetap mau memakai kedua fungsi tersebut ialah memakai kombinasi dua atau lebih dari fungsi SUMIFS + SUMIFS, atau COUNTIFS+COUNTIFS
Untungnya hal ini sanggup dihandle oleh hanya Satu Fungsi SUMPRODUCT saja.
Melanjutkan teladan tabel penjualan buah-buahan sebelumnya, Misalnya kita ingin menghitung berapa kali frekuensi penjualan dan nilai penjualan buah mangga dan buah pisang di tempat ciamis.
Perhatikan syarat yang harus dipenuhi
- Lokasi penjualan : Ciamis
- Nama Buah : Mangga atau Pisang
Secara sederhana, kriteria ini sanggup dituliskan dalam budi AND dan OR sebagai berikut:
AND(lokasi="ciamis",OR(buah="mangga",buah="pisang"))
Ekspresi atau pernyataan tersebut akan bernilai benar (TRUE) jikalau lokasi ="ciamis" dan buah ="mangga" atau buah="pisang"
Mari kita lihat dalam teladan di bawah ini:
Berapa kali frekuensi penjualan buah mangga dan pisang di tempat Ciamis?
Cara1: Menggunakan rumus COUNTIFS, diharapkan 2 fungsi:
=COUNTIFS(B2:B13,"ciamis",C2:C13,"mangga")+COUNTIFS(B2:B13,"ciamis",C2:C13,"pisang")
Cara2: Menggunakan rumus SUMPRODUCT, hanya diharapkan 1 fungsi:
=SUMPRODUCT((B2:B13="ciamis")*((C2:C13="mangga")+(C2:C13="pisang")))
Berapa Total nilai penjualan buah mangga dan pisang di tempat Ciamis?
Cara1: Menggunakan rumus SUMIFS , diharapkan 2 fungsi
=SUMIFS(D2:D13,B2:B13,"ciamis",C2:C13,"mangga")+SUMIFS(D2:D13,B2:B13,"ciamis",C2:C13,"pisang")
Cara 2: Menggunakan rumus SUMPRODUCT, hanya diharapkan 1 fungsi
=SUMPRODUCT((B2:B13="ciamis")*((C2:C13="mangga")+(C2:C13="pisang"))*D2:D13)
Lebih jelasnya perhatikan ilustrasi dalam screenshot dibawah ini:
Formula SUMPRODUCT Untuk Menghitung Berat Rata-Rata
Masih berbicara dengan teladan buah-buahan. Kita sanggup memakai rumus SUMPRODUCT untuk menghitung berat buah rata-rata tanpa harus memakai kolom bantu.
Saya mencontohkan dengan buah kelapa sawit. Satuan hitung diukur dengan jumlah tandan atau sering disebut juga janjang kelapa sawit, sedangkan berat rata-ratanya diistilahkan Berat Janjang Rata-rata atau disingkat BJR.
Bagaimana menghitung BJR jikalau diketahui data sebagai berikut:
Tanpa memakai kolom bantu, bergotong-royong kita juga sanggup menghitungnya dengan rumus matematika biasa yaitu:
=(C2*D2+C3*D3+C4*D4+C5*D5+C6*D6)/SUM(C2:C6)
Namun rumus tersebut tidak efesien, terutama jikalau dipakai pada baris data yang banyak.
Oleh karenanya disarankan memakai SUMPRODUCT
Rumusnya menyerupai ini:
=SUMPRODUCT(jjg_blok, bjr_blok) / SUM(jjg_blok)
Dan jikalau diterapkan dalam lembar excel menjadi
=SUMPRODUCT(C2:C6,D2:D6)/SUM(C2:C6)
Cukup gampang bukan?
Atau bertele-tele?
Tergantung anda dan dari sudut mana anda memandangnya.
Rumus SUMPRODUCT Sebagai Alternative LOOKUP 2 Dimensi.
LOOKUP 2 dimensi disini artinya ialah mencari titik temu antara baris dan kolom sesuai syarat atau kriteria tertentu.
Contohnya:
Sebuah tabel data berisi penjualan buah-buahan per bulan.
- Baris menerangkan jenis buah
- Kolom menerangkan bulan per bulannya
- Data berisi nilai penjualan buah.
Bagaimana rumus untuk mendapat nilai penjualan buah tertentu pada bulan tertentu.
Perhatikan teladan dalam prinscreen berikut:
Perhatikan bahwa jumlah penjualan pisang pada bulan Maret ialah 7.500.000.
Jika kita baca dalam lembar kerja excel, data tersebut merupakan titik temu antara baris pisang dan kolom maret. Inilah yang disebut dengan lookup 2 dimensi.
Rumus tersebut sanggup dituliskan sebagai berikut:
=SUMPRODUCT((A2:A4="pisang")*(B1:G1="maret")*B2:G4)
Dan supaya lebih fleksible, maka nama buah dan bulan kita simpan dalam sel:
Nama buah : sel B10
Nama bulan : sel B11
Sehingga rumus menjadi:
=SUMPRODUCT((A2:A4=B10)*(B1:G1=B11)*B2:G4)
Sampai pada tahap ini mudah-mudahan sanggup difahami.
SUMPRODUCT sebagai alternatif rumus array.
Jika kita sudah memahami rumus array dan memperhatikan contoh-contoh rumus SUMPRODUCT dari awal pembahasan hingga penggalan akhir, kita sanggup melihat bahwa ada banyak kiprah yang sanggup diselesaikan memakai fungsi SUMPRODUCT, juga sanggup diselesaikan memakai rumus array (CSE).
Supaya masih hangat dalam benak pembaca, saya memakai teladan rumus SUMPRODUCT yang terakhir dibahas yaitu rumus alternative Lookup 2 dimensi.
=SUMPRODUCT((A2:A4=B10)*(B1:G1=B11)*B2:G4)
Rumus tersebut ternyata akan menghasilkan nilai yang sama dengan rumus array berikut:
{=SUM((A2:A4=B10)*(B1:G1=B11)*B2:G4)}
Terbukti….
Silahkan dicoba dengan rumus-rumus yang lainnya.
....
Demikian pembahasan mengenai fungsi SUMPRODUCT. Dimulai dari pembahasan syntax dan basic penggunaanya, Dilanjutkan dengan pembahasan contoh-contoh rumus dan klarifikasi cara kerjanya. Hingga pada penggalan akhir, kita sanggup mengetahui bahwa rumus SUMPRODUCT ternyata sanggup menjadi alternatif rumus-rumus dan fungsi lainnya termasuk rumus array.
Tidak salah jikalau dikatakan bahwa SUMPRODUCT sebagai Rumus Multi Guna
Demikian agar bermanfaat.
Salam..
Artikel Terkait:
0 Response to "Rumus Excel Sumproduct - Satu Fungsi, Multi Guna."
Posting Komentar