3 Alternatif Rumus Cari Data Banyak Kriteria

Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.

Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.

Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.

Ada beberapa alternative formula yang dapat digunakan. Berikut 3 diantaranya:

1. INDEX MATCH
2. OFFSET MATCH
3. INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut harus dibuat dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita lanjutkan dengan Studi Kasus.

Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel

Anggaplah kita memiliki tabel data harga buah-buahan dari berbagai supplier. Label kolom tabel dari kiri ke kanan adalah:

• Kolom A = No
• Kolom B = Buah
• Kolom C = Supplier
• Kolom D = Harga

Selanjutnya misalnya kita harus mencari data harga buah dari suplier tertentu.

Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam tugas pencarian data ini, yaitu:

• Kriteria 1 = nama buah
• Kriteria 2 = nama suplier

Pertanyaannya: Bagaimana rumusnya untuk mendapatkan data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .

Contoh kasus dan pertanyaan tersebut dapat digambarkan sebagai berikut:

Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat sebenarnya akan terasa jika bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.

Selain itu, contoh yang ditampilkan juga hanya menggunakan 2 kriteria, namun pada dasarnya prinsip kerjanya adalah sama, baik menggunakan 2 kriteria, 3 kriteria atau lebih banyak lagi.

Selanjutnya mari kita bahas satu persatu.

Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array, sehingga ketika  selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.

Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH

Salah satu rumus terpopuler yang biasa digunakan untuk pencarian data adalah kombinasi fungsi INDEX dan MATCH.

Dalam hal contoh kasus di atas, kita bisa menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:

{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}

Cara Kerja Rumus INDEX MATCH

• Operator ampersand (&) digunakan untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom  kolom buah (B2:B8) dan supplier (C 2:C8)
• Fungsi MATCH berguna untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
• Angka yang diperoleh dari fungsi MATCH kemudian digunakan oleh fungsi INDEX sebagai row_index dalam range D2:D8. 
• Dan sel baris ke-3 dalam range D2:D8 adalah sel D4, sementara itu sel D4 berisi nilai 8000.
• Sehingga hasil akhir adalah 8000.

Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH

Fungsi OFFSET berguna untuk mendapatkan referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.

Dengan menggabungkannya dengan fungsi MATCH, maka kita akan menentukan berapa  baris jarak sel yang dicari dari sel acuan, kemudian mendapatkan nilai dari sel yang dicari tersebut.

{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}

Cara Kerja Rumus OFFSET-MATCH

• Fungsi MATCH berperan sama seperti halnya dalam rumus INDEX MATCH, dimana fungsi MATCH  dalam contoh tersebut menghasilkan angka 3.
• Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana dapat dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapatkan nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
• Dan sel dimaksud dengan kriteria tersebut adalah sel D4
• Sementara itu sel D4 berisi nilai 8.000.   Bilangan inilah yang merupakan hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string yang mereferensikan sebuah alamat sel tertentu.

Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.

{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}

Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

• Fungsi MATCH berguna untuk mendapatkan nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapatkan index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka  3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapatkan angka 4.
• Index Kolom dalam spreadsheet didapatkan menggunakan bantuan fungsi COLUMN,  dan kolom D2:D8 adalah 4 (kolom D)
• Ringkasnya rumus tersebut dapat dikonvert menjadi =INDIRECT(ADDRESS(4,4))
• ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
• Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapatkan nilai dalam sel $D$4
• Dan hasilnya adalah 8.000

  

Ringkasan.

Cari data dengan 2 kriteria atau lebih dapat dilakukan tanpa menggunakan kolom bantu, yaitu menggunakan rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya dapat digunakan sesuai kebutuhan fungsi lainnya.

Demikian semoga bermanfaat.

Salam

Silahkan dibaca juga artikel Belajar Excel Lainnya:

• 3 Alternative Rumus VLOOKUP Ke Kiri
• Fungsi MATCH dan Contoh Rumusnya
• Mengenal Fungsi dan Rumus VLOOKUP
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Mengenal Rumus Array

Cara Memisahkan Kata Per Huruf

Pembaca je-xcel yang budiman, barangkali kita pernah mengisi form-form berupa kotak isian text nama, alamat, nomor telepon dan sebagainya. Masing-masing isian dipisahkan per huruf, bukan per kata. Biasanya hal ini dijumpai dalam form-form tertentu semisal form registrasi rekening di bank, ataupun form-form lainnya.

Tahukan teman?... ternyata kita juga dapat mengerjakan hal itu di excel, tapi bukan dengan mengetik satu huruf per satu kotak/sel. Namun kita bisa merumuskannnya sehingga cukup hanya mengetikan kata-kata atau text lengkap (misal nama atau alamat) dalam satu sel saja. Tugas selanjutnya serahkan saja kepada rumus excel yang akan memisahkan text atau kata-kata tersebut secara otomatis menjadi per  huruf dalam kotak atau sel yang berbeda.

Sebelum membaca lebih lanjut, boleh dong dicek dulu videonya ..😊😊

Bagaimana cara memisahkan kata menjadi per huruf?

Cara memisahkan text menjadi per huruf cukup mudah, yaitu dengan memanfaatkan fungsi MID. Fungsi ini bekerja dengan cara mengambil satu atau beberapa huruf atau karakter sesuai nomor urut karakter tersebut dalam text.

Fungsi MID dapat dituliskan sebagai berikut:

MID(text,start_num,num_char)

Dimana:

• text =  text atau string utama yang akan kita ambil bagian karakter nya
• start_num =  nomor urut karakter pertama dari string yang akan diambil dari text utama
• num_char =  banyaknya huruf atau karakter yang akan diambil dari text utama, dimulai dari start_num.

Contoh Rumus:

=MID("BANDUNG",4,2)

Rumus tersebut akan mengahasilkan text  "DU",  yaitu 2 karakter dimulai dari karakter ke 4

Mari kita coba selanjutnya... Bagaimana jika kita hanya mengambil satu huruf saja per nomor urut karakter dalam text.

=MID("BANDUNG",1,1) menghasilkan  huruf  "B"   (karakter ke 1)
=MID("BANDUNG",2,1) menghasilkan  huruf  "A"   (karakter ke-2)
=MID("BANDUNG",3,1) menghasilkan  huruf  "N"   (karakter ke-3)
=MID("BANDUNG",4,1) menghasilkan  huruf  "D"   (karakter ke-4)
=MID("BANDUNG",5,1) menghasilkan  huruf  "U"   (karakter ke-5)
=MID("BANDUNG",6,1) menghasilkan  huruf  "N"   (karakter ke-6)
=MID("BANDUNG",7,1) menghasilkan  huruf  "G"   (karakter ke-7)

Nah, Konsep tersebut kemudian dapat kita manfaatkan untuk memisahkan kata-kata menjadi huruf atau karakter-karakter tunggal.

Contoh Rumus Untuk Memisahkan Kata Per Huruf

Ada beberapa metode rumus yang dapat digunakan untuk memisahkan text kata-kata menjadi per huruf atau per karakter. Namun entah apapun metode yang digunakan, cara kerjanya hampir sama yaitu mendapatkan angka nomor urut karakter, dan menjadikannya sebagai argumen start_num dalam fungsi MID.

Contoh 1: Memisahkan Kata Menjadi Per huruf Menggunakan Fungsi MID dan Baris  Bantu

Cara Kerja Rumus:

Perhatikan contoh rumus pada gambar diatas.

• Baris bantu pada range (C2:V2) digunakan berisi nomor urut 1 s.d 20 digunakan untuk mendefinisikan argumen start_num dalam fungsi MID. Dengan kata lain berguna untuk mendefinisikan nomor urut huruf dalam text yang akan dimunculkan dalam kolom tersebut.
• Perhatikan rumus pada sel C4 yaitu =MID($B4,C$2,1)  (sesuai tanda panah)
• $B4 adalah text utama, berupa data nama yang akan kita ambil per hurufnya. Tanda dolar ($) dimaksudkan supaya kalau rumus tersebut dicopy ke samping, maka referensi kolomnya akan tetap mengikat ke kolom B yang berisi list Nama
• C$2 berperan sebagai argumen star_num yaitu nomor urut karakter dalam text yang akan diambil.
• Angka 1, menunjukan hanya satu hurus saja yang akan diambil dimulai dari nomur urut (star_num)
• Copy rumus tersebut ke semua kotak di sebelahnya.
• Anda dapat menambahkan baris baru untuk  item list nama yang lainnya, dan copy rumus tadi.
• Selesai...

Contoh 2: Memisahkan kata per huruf menggunakan rumus kombinasi fungsi MID dan COLUMNS

Cara Kerja Rumus:

• Perhatikan rumus pada sel C4 yaitu =MID($B4,COLUMNS($B:B),1)  (sesuai tanda panah)
• $B4 mengikat pada text nama yang akan dipisahkan per karakter, tanda dolar hanya sebelum kolom menandakan referensi sel semi absolute supaya jika rumus dicopy ke sel lain maka referensi kolom tidak berubah relative, tetapi baris bisa berubah.
• COLUMNS($B:B) berguna untuk mendapatkan jumlah kolom pada range $B:B,  rumus tersebut akan menghasilkan angka 1 karena range B:B hanya memiliki 1 kolom. Tanda $ hanya pada salah satu label huruf B dimaksudkan supaya jika dicopy ke samping maka kolom pertama akan terkunci, tetapi kolom akhir tidak terkunci, sehingga jika rumus dicopy ke sel sebelahnya, alamat range akan berubah menjadi $B:C , $B:D, $B:E dan seterusnya dan memberikan angka 2, 3, 4 dan seterusnya
• Angka yang dihasilkan fungsi COLUMNS digunakan oleh fungsi MID sebagai argumen start_num untuk mendapatkan satu huruf atau satu karakter dari text utama.
• Silahkan rumus di sel C4 di copy paste ke sel lainnya.
• Kelemahan dari metode ini adalah hasil akan menjadi tidak relevan jika ada insert column.

Contoh 3: Memisahkan Kata per Huruf Menggunakan Rumus Array CSE

Cara Kerja Rumus:

• Perhatikan rumus pada range C4:V4 (sesuai tanda panah)
• Rumus tersebut dibuat dengan cara menyeleksi range C4:V4, kemudian mengetikan rumus =MID(B4,COLUMN(1:1),1), dan setelah itu tekan CTR+SHIFT+ENTER.
• Perhatikan kembali komponen rumus {=MID(B4,COLUMN(1:1),1)}
• Tanda kurung kurawal {} menandakan rumus array dimana tanda tersebut muncul secara otomatis ketika kita menekan CTR+SHIFT+ENTER setelah mengetik/mengedit rumus.  
• B4 berperan sebagai referensi berisi text utama yang akan diambil per hurufnya
• COLUMN(1:1) merupakan kunci untuk mendapatkan nomor urut karakter, dimana jika fungsi tersebut dimasukan dalam fungsi array maka akan menghasilkan angka 1 s.d 16384 di excel 2007 s.d 2016, sedangkan di excel 2003 akan menghasilkan angka 1 s.d 256 sesuai jumlah kolom yang diakomodir oleh masing-masing versi excel tersebut.
• Sebenarnya tidak masalah apakah kita menggunakan (COLUMN(1:1), COLUMN(1:2), COLUMN(3:10), dan sebagainya. Hasilnya akan sama dalam kasus penyelesaian masalah yang menjadi topik posting blog saat yang sedang anda baca ini.
• Elemen array yang dihasilkan rumus COLUMN(1:1) kemudian dijadikan sebagai parameter start_num dalam rumus MID.  Dan tentunya hanya beberapa elemen array saja yang digunakan sesuai jumlah karakter dalam kata atau text nama yang diproses.
• Untuk mengcopy rumus array tidak bisa satu sel per sel, tetapi harus dengan cara menyeleksi semua sel dalam lingkup array, dalam contoh yaitu range  C4:V4, kemudian paste pada sel C5, C6 dan seterusnya sesuai baris item list nama yang tersedia.

Ringkasan.

Untuk memisahkan kata-kata atau text menjadi per huruf dapat dilakukan menggunakan fungsi MID. Ada beberapa alternative metode yang dapat digunakan, diantaranya: Memisahkan text menjadi perhuruf menggunakan fungsi MID + baris bantu, menggunakan kombinasi fungsi MID dan COLUMNS, serta menggunakan rumus Array fungsi MID dan COLUMN.

Sekian, semoga bermanfaat.

Belajar Excel.! Excellent!

Artikel terkait:

• Cara Meggabungkan Kata di Excel
• Cara Menghilangkan Spasi Berlebih
• Belajar Rumus Array Untuk Pemula
• Fungsi Pengolah Text di Excel

Rumus Excel Menghitung Nilai Unik Dalam List

Menghitung banyaknya nilai unik dalam list merupakan salah satu jenis tugas yang dapat diselesaikan menggunakan microsoft excel. Ada beberapa cara yang dapat digunakan, dua cara diantaranya adalah menggunakan rumus excel array CSE dan SUMPRODUCT

Anggap saja kita memiliki daftar pesanan belanja dari beberapa pelanggan yang meminta bantuan kita untuk mencarikan dan membelikannya.

Selanjutnya kita perlu melakukan pengelompokan jenis barang  yang sama sehingga bisa ditentukan selanjutnya, kemana saja harus pergi untuk mendapatkan barang tersebut.

Bagaimana cara melakukannya menggunakan microsoft excel, aplikasi kesayangan kita ini?

Rumus berikut dapat bekerja dengan baik untuk menyelesaikan tugas tersebut:

• Rumus array CSE :  
• {=SUM(1/COUNTIF(range_list,range_list))}

• Rumus SUMPRODUCT : 
• =SUMPRODUCT(1/COUNTIF(range_list,range_list))

Seumpamanya daftar belanjaan disimpan dalam lembar kerja excel pada range B2:B10, maka formula yang dapat digunakan untuk untuk menghitung nilai unik dalam daftar belanja tersebut adalah sebagai berikut:



Rumus array:

{=SUM(1/COUNTIF(B2:B10,B2:B10))}

Atau

Rumus SUMPRODUCT:

=SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10))

Catatan: Untuk rumus array, tanda kurang kurawal {} jangan diketikan secara langsung pada formula, melainkan dengan cara menekan CTR+SHIFT+ENTER setelah mengetikan formula.

Perhatikan screenshot berikut:

Cukup mudah bukan?

Bagaimana Cara Kerja Rumus Excel untuk Menghitung Nilai Unik?

Mari kita lihat lebih dalam, bagaimana cara kerja rumus diatas sehingga bisa memberikan informasi banyaknya atau frekuensi nilai unik dalam list secara tepat.

Kita ambil contoh rumus SUMPRODUCT, karena sebenarnya cara kerjanya sama saja dengan rumus array tanpa perlu menekan CSE.

• Perhatikan kembali rumus =SUMPRODUCT(1/COUNTIF(B2:B10,B2:B10))
• Seleksi sel dimana rumus tersebut ditempatkan, kemudian double klik, atau tekan F2, atau klik di formula bar
• Dengan menggunakan kursor, blok bagian rumus COUNTIF(B2:B10,B2:B10),
• Tekan F9
• Formula berubah menjadi :   =SUMPRODUCT(1/{2;2;2;2;1;2;1;1;2})
• Angka dalam tanda kurung kurawal merupakan data array yang menunjukan banyaknya item yang sama dalam list pada range B2:B10, yaitu
• Mangga   = 2
• Pisang   = 2
• Jeruk    = 2
• Jeruk    = 2
• Lengkeng = 1
• Pisang   = 2
• Semangka = 1
• Manggis  = 1
• Mangga   = 2
• Selanjutnya blok bagian rumus  1/{2;2;2;2;1;2;1;1;2}, 
• Tekan F9
• Formula pun berubah  =SUMPRODUCT({0.5;0.5;0.5;0.5;1;0.5;1;1;0.5})
• Perhatikan angka desimal dalam array merupakan  hasil pembagian dari bilangan 1 dengan bilangan dalam array sebelumnya. 
• Mangga   = 1/2 = 0.5
• Pisang   = 1/2 = 0.5
• Jeruk    = 1/2 = 0.5
• Jeruk    = 1/2 = 0.5
• Lengkeng = 1/1 = 1
• Pisang   = 1/2 = 0.5
• Semangka = 1/1 = 1
• Manggis  = 1/1 = 1
• Mangga   = 1/2 = 0.5
• Blok rumus =SUMPRODUCT({0.5;0.5;0.5;0.5;1;0.5;1;1;0.5})
• Tekan F9
• Formula pun berubah menjadi hasil akhir yaitu bilangan 6
• Bilangan tersebut merupakan hasil penjumlahan bilangan desimal dalam array
• = 0.5 + 0.5 + 0.5 + 0.5 + 1 + 0.5 + 1 + 1 + 0.5
• = 6
• Untuk memperjelas gambaran bagaimana rumus perhitungan nilai unik bekerja, silahkan perhatikan SS berikut:

Catatan: Shortcut F9 dapat digunakan untuk merubah bagian formula yang dipilih menjadi hasil kalkulasi dari bagian formula tersebut.

...

Ringkasan:

Banyaknya nilai unik dalam sebuah list pada excel dapat dihitung menggunakan rumus array dan SUMPRODUCT. Cara kerja dari rumus array dan SUMPRODUCT untuk menghitung nilai unik ini dapat kita pelajari dan kita telusuri logika kerjanya dengan menggunakan bantuan shortcut F9 untuk menguji bagian-bagian dari formula sampai didapatkan hasil akhir.

Semoga bermanfaat.

Belajar Excel

Excellent...!

...

Artikel Terkait:

• Rumus Excel SUMPRODUCT : Satu Fungsi - Multi Guna
• Cara Mudah Memahami Rumus Array
• Rumus Excel COUNTIF : Menghitung Dengan Syarat
• Fungsi SUM dan Rumus Penjumlahan Pada Excel

Rumus Excel SUMPRODUCT - Satu Fungsi, Multi Guna.

Apa sebenarnya 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 bagian selanjutnya akan dibahas contoh-contoh kegunaan praktis 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 gabungan antara SUM dan PRODUCT.

Mengenai fungsi SUM, saya kira mayoritas 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 dapat 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 digunakan untuk mendapatkan hasil perkalian antara dua buah bilangan atau lebih.

Contoh:

Rumus =PRODUCT(bil1,bil2,bil3) sama saja hasilnya dengan rumus =bil1*bil2*bil3.

Itulah mengapa, nampaknya fungsi PRODUCT kurang diminati dan jarang digunakan. Hal ini karena rumus perkalian lebih familiar dan lebih mudah 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 dapat menggunakan fungsi SUMPRODUCT untuk melakukan penjumlahan / perhitungan bersyarat ganda.

Sedangkan pada Excel 2007 dan versi yang lebih baru, tugas tersebut juga dapat dilakukan oleh fungsi SUMIFS dan COUNTIFS. Meskipun demikian, Fungsi SUMPRODUCT juga tetap dapat digunakan pada versi ini.

Fungsi SUMPRODUCT juga dapat digunakan untuk melakukan 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 pasti akan menemukan kemiripan antara rumus array dengan SUMPRODUCT.

Dan memang SUMPRODUCT bekerja terhadap array, meskipun dalam aplikasinya tidak perlu menggunakan shortcut CSE seperti biasanya jika kita menggunakan rumus array.

Syntax dan Cara Penggunaan Fungsi SUMPRODUCT

Pada dasarnya fungsi SUMPRODUCT bekerja untuk melakukan perkalian antara bilangan dalam array, dan kemudian menjumlahkan hasil perkalian tersebut.

Adapun syntax fungsi SUMPRODUCT sangat simple dan sangat jelas.

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

Array1, array2 dan seterusnya adalah range sel atau array yang masing-masing elemennya ingin kita kalikan kemudian hasil perkaliannya dijumlahkan.

Jumlah maksimum argumen array yang dapat digunakan dalam fungsi SUMPRODUCT adalah 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 setelah mengetikan formula, seperti halnya rumus biasa.

Hal-hal yang perlu diperhatikan seputar penggunaan Fungsi SUMPRODUCT:

• Semua aray dalam rumus SUMPRODUCT harus memiliki 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 logika yang bernilai TRUE atau FALSE, maka dalam kebanyakan kasus, kita perlu mengkonversinya menjadi 1 atau 0 menggunakan double unary operator (--), atau menggunakan fungsi SIGN
• Fungsi SUMPRODUCT tidak mendukung penggunaan karakter wildcard.

Dasar-Dasar Penggunaan Fungsi SUMPRODUCT pada Excel.

Untuk membantu memahami cara kerja fungsi SUMPRODUCT, mari kita perhatikan contoh kasus dibawah ini:

Anggaplah anda memiliki daftar belanja buah-buahan yang dibuat menggunakan 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 menggunakan kolom bantu) banyaknya biaya yang diperlukan?

Mari kita lihat gambar berikut:

Perhatikan:

Untuk mendapatkan jumlah biaya yang diperlukan, kita bisa menghitungnya menggunakan rumus perkalian dan penambahan biasa.

=B2*C2+B3*C3+B4*C4+B5*C5

Atau jika referensi tersebut diganti dengan bilangan, maka rumus dapat dituliskan sebagai berikut:

=3*10000+4*5000+5*8000+3*6000
=108.000

Rumus diatas memang sangat mudah untuk difahami. Cara kerjanya sederhana, cukup dengan cara mengalikan quantity produk dengan harganya, kemudian hasilnya dijumlahkan dengan hasil perkalian quantity dan harga produk lainnya.

Bayangkan…

Seandaiya kita memiliki ratusan sampai ribuan barang yang harus dihitung..

Rumus model diatas, sepertinya bukanlah solusi yang tepat, kecuali jika anda memiliki 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 diperlukan hanya penyesuaian referensi range datanya saja.

Misalnya: jika baris data mencapai 1000, maka atur saja batas akhir range yang digunakan, misalnya B2:B1000 dan C2:C1000.

Menurut saya, cara ini sangat Simple dan Mudah.

Bagaimana menurut anda?

Contoh-Contoh Rumus – Bagaimana Menggunakan Fungsi SUMPRODUCT pada Excel

Melakukan perkalian pasangan elemen-elemen antara dua atau lebih range dan kemudian menjumlahkan hasil perkaliannya merupakan bentuk paling mendasar dan sederhana dari penggunaan fungsi SUMPRODUCT pada Excel.

Akan tetapi, keanggunan dan kehebatan fungsi SUMPRODUCT tidak terbatas pada definisi dasarnya saja. Ada banyak tugas kompleks yang dapat diselesaikan menggunakan fungsi multiguna ini.

Kehebatan apa saja yang dapat diperoleh menggunakan fungsi SUMPRODUCT?

Mari kita bongkar satu persatu dalam contoh-contoh rumus yang akan digali lebih jauh dalam belajar excel kali ini.

Silahkan dibaca terus artikel ini ya 😉

Fungsi SUMPRODUCT Dengan Satu Kondisi

Semakin lama dan sering menggunakan excel, maka kita akan menyadari sebenarnya ada banyak jalan untuk menyelesaikan tugas excel tertentu.

Namun untuk kaitan membandingkan dua atau lebih array, terutama yang berhubungan dengan kalkulasi yang bersyarat ganda, maka hanya ada dua cara yang paling efektif, yaitu: Menggunakan Rumus Array Atau menggunakan rumus SUMPRODUCT..

Mengenai rumus array sudah dibahas dalam artikel Memahami Rumus Array Untuk Pemula. Sedangkan untuk rumus SUMPRODUCT, inilah inti pembahasan belajar excel kali ini.

Anggaplah kita memiliki tabel dalam lembar kerja excel dengan susunan sebagai berikut:

- Kolom A berisi list produk
- Kolom B berisi target penjualan (Rp)
- Kolom C berisi aktual penjualan (Rp)
- Untuk contoh, baris data dibatasi s.d baris 10.

Pertanyaan: Bagaimana cara menghitung banyaknya item produk yang tidak mencapai target 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, contoh 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 bisa memberikan nilai akhir = 4 ?

Dengan menggunakan mouse, seleksi porsi  (--(C2:C10<B2:B10) pada formula bar, kemudian tekan F9. Untuk lebih jelasnya perhatikan gambar berikut:

Apa yang kita dapatkan?

Kita akan melihat array yang berisi nilai boolean TRUE dan FALSE.

Perhatikan kembali logika menggunakan 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 jika kondisi sebaliknya maka akan bernilai FALSE (salah)

Selanjutnya, apa guna tanda minus kembar (--) atau yang secara tehnik disebut double unary operator?

Operator ini digunakan untuk mengkorversi nilai TRUE dan FALSE menjadi 1 dan 0.

Melanjutkan langkah berikutnya, pada formula bar seleksi bagian rumus berikut,
--{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}

Kemudian tekan F9. Akan lebih mudah memahami dengan memperhatikan ilustrasi berikut:

Dari ilustrasi diatas, kita dapat melihat bahwa nilai TRUE berubah menjadi 1, sedangkan FALSE berubah menjadi 1 dan 0.  Inilah gunanya tanda minus kembar, atau double unary operator, merubah nilai logika menjadi numerik sehingga bisa dikalkulasi lebih lanjut.

Tahap akhir; Bilangan 1 dan 0 dijumlahkan sehingga menghasilkan nilai akhir = 4


Masih ingin tahu alternative lainnya?

Double unary operator ternyata dapat 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)) dapat diganti menjadi =SUMPRODUCT(SIGN(C2:C10<B2:B10))


Ternyata masih ada Alternative Lainnya:

Selain menggunakan double unary operator dan fungsi SIGN, kita juga dapat mengkonversi nilai logika 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 bisa difahami.

Rumus SUMPRODUCT dengan Dua Kondisi

Selanjutnya mari kita pelajari lebih jauh dengan contoh rumus untuk menghitung banyaknya data yang memenuhi beberapa kondisi tertentu atau bersyarat ganda.

Kita ambil saja 2 kondisi sebagai contoh, karena 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 dapat menggunakan 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 digunakan untuk melakukan perkalian elemen sejajar dalam array kemudian menjumlahkan hasilnya.

Mari kita perhatikan kembali rumus untuk menghitung berapa kali penjulan buah mangga yang tidak mencapai target sesuai contoh sebelumnya.

=SUMPRODUCT(--(A2:A10="mangga"),--(C2:C10<B2:B10))

Bagaimana bisa diperoleh hasil = 2 ?

• Seleksi sel dimana rumus tersebut ditempatkan
• Pada formula bar, seleksi bagian rumus (A2:A10="mangga"), kemudian 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 adalah menggunakan 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} , kemudian tekan F9, maka hasilnya adalah {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, kemudian menambahkan masing-masing hasilnya. Maka formula tersebut dapat dijelaskan dengan rumus =(1*0)+(0*1)+(0*0)+(0*0)+(1*1)+(0*0)+(0*0)+(0*1)+(1*1)
• Maka hasilnya adalah 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 sebenarnya tugas ketiga fungsi tersebut dapat 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 menggunakan excel 2003, karena SUMPRODUCT tersedia di semua versi excel, setidaknya pada versi 2003 s.d 2016 yang sudah saya gunakan. Untuk versi excel yang lebih tua dari 2003 silahkan dicek sendiri ya, kebetulan saya tidak punya versinya. 😓

1. SUMPRODUCT dengan Logika AND

Yang dimaksud logika AND adalah dimana kombinasi dua pernyataan atau lebih akan bernilai benar (TRUE) jika semua pernyataan bernilai (Benar)

Anggaplah kita memiliki data berikut yang merupakan data penjualan buah-buahan pada berbagai daerah dari tanggal 1 s.d 12 Desember.

Bagaimana cara mengetahui jumlah penjualan di daerah Ciamis, Berapa kali penjualannya dan Berapa rata-ratanya?

Disini kita menggunakan logika 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, logika AND dalam rumus SUMPRODUCT  juga dapat dipenuhi menggunakan bantuan operator asterisk (*)

Perhatikan contoh rumus berikut dengan asumsi baris terakhir data adalah baris 13.

• Berapa Jumlah Penjualan buah mangga di daerah  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 daerah 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 daerah 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 contoh cotoh rumus diatas, sudah sangat jelas bahwa  fungsi SUMPRODUCT dapat menggantikan fungsi SUMIFS, COUNTIFS dan AVERAGEIFS, meskipun sebagai pengganti AVERAGEIFS, perlu rumus yang lebih panjang.

Supaya lebih fleksible, kita bisa menaruh kriteria perhitungan dalam sebuah sel

Misalnya 

- Kriteria daerah pemasaran disimpan di sel C16

- Kriteria nama buah disimpan di sel C17

Maka rumus penjumlahan, frekuensi dan rata-rata dapat 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, karena merupakan hasil pembagian jumlah dengan frekuensi maka rumusnya dapat menggunakan referensi lokasi rumus jumlah dan frekuensi. Perhatikan ilustrasi berikut:

CONTOH2: Rumus SUMPRODUCT Dengan Logika OR

Logika OR akan bernilai benar jika kombinasi pernyataan memiliki minimal satu pernyataan yang bernilai benar (TRUE). 

Misalnya:

Untuk menghitung berapa banyak penjualan buah mangga dan buah pisang pada tabel contoh sebelumnya.

Mangga dan pisang sama-sama terletak dalam satu kolom dan kedua nya harus diperhitungkan dalam proses kalkulasi.

Ini artinya kita harus membuat rumus yang menghasilkan nilai TRUE apabila salah satu kondisi terpenuhi.

Kondisi tersebut adalah:

• Nama buah = "mangga" atau  "pisang"

Seperti halnya juga dalam rumus array, logika OR dalam rumus SUMPRODUCT dapat dibantu menggunakan operator plus (+).

Perhatikan contoh 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 logika AND dan OR sekaligus.

Sayangnya bahkan dalam versi excel terbaru pun, Satu fungsi SUMIFS dan COUNTIFS tidak bisa menghandle kasus ini. Salah satu cara yang dapat dilakukan jika tetap mau menggunakan kedua fungsi tersebut adalah menggunakan kombinasi dua atau lebih dari fungsi SUMIFS +  SUMIFS, atau COUNTIFS+COUNTIFS

Untungnya hal ini bisa dihandle oleh hanya Satu Fungsi SUMPRODUCT saja.

Melanjutkan contoh tabel penjualan buah-buahan sebelumnya, Misalnya kita ingin menghitung berapa kali frekuensi penjualan dan nilai penjualan buah mangga dan buah pisang di daerah ciamis.

Perhatikan syarat yang harus dipenuhi

• Lokasi penjualan  : Ciamis
• Nama Buah          : Mangga atau Pisang

Secara sederhana, kriteria ini dapat dituliskan dalam logika AND dan OR sebagai berikut:

AND(lokasi="ciamis",OR(buah="mangga",buah="pisang"))

Ekspresi atau pernyataan tersebut akan bernilai benar (TRUE) jika lokasi ="ciamis" dan buah ="mangga" atau buah="pisang"

Mari kita lihat dalam contoh di bawah ini:

Berapa kali frekuensi penjualan buah mangga dan pisang di daerah Ciamis?

Cara1: Menggunakan rumus COUNTIFS, diperlukan 2 fungsi:

=COUNTIFS(B2:B13,"ciamis",C2:C13,"mangga")+COUNTIFS(B2:B13,"ciamis",C2:C13,"pisang")

Cara2: Menggunakan rumus SUMPRODUCT, hanya diperlukan 1 fungsi:

=SUMPRODUCT((B2:B13="ciamis")*((C2:C13="mangga")+(C2:C13="pisang")))

Berapa Total nilai penjualan buah mangga dan pisang di daerah Ciamis?

Cara1: Menggunakan rumus SUMIFS , diperlukan 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 diperlukan 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 contoh buah-buahan. Kita bisa menggunakan rumus SUMPRODUCT untuk menghitung berat buah rata-rata tanpa harus menggunakan 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 jika diketahui data sebagai berikut:

Tanpa menggunakan kolom bantu, sebenarnya kita juga bisa 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 jika digunakan pada baris data yang banyak.

Oleh karenanya disarankan menggunakan SUMPRODUCT

Rumusnya seperti ini:

=SUMPRODUCT(jjg_blok, bjr_blok) / SUM(jjg_blok)

Dan jika diterapkan dalam lembar excel menjadi

=SUMPRODUCT(C2:C6,D2:D6)/SUM(C2:C6)

Cukup mudah bukan?

Atau bertele-tele?

Tergantung anda dan dari sudut mana anda memandangnya.

Rumus SUMPRODUCT Sebagai Alternative LOOKUP 2 Dimensi.

LOOKUP 2 dimensi disini artinya adalah mencari titik temu antara baris dan kolom sesuai syarat atau kriteria tertentu.

Contohnya:

Sebuah tabel data berisi penjualan buah-buahan per bulan.

• Baris menunjukan jenis buah
• Kolom menunjukan bulan per bulannya
• Data berisi nilai penjualan buah.

Bagaimana rumus untuk mendapatkan nilai penjualan buah tertentu pada bulan tertentu.

Perhatikan contoh dalam prinscreen berikut:

Perhatikan bahwa jumlah penjualan pisang pada bulan Maret adalah 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 dapat 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 dapat difahami.

SUMPRODUCT sebagai alternatif rumus array.

Jika kita sudah memahami rumus array dan memperhatikan contoh-contoh rumus SUMPRODUCT dari awal pembahasan sampai bagian akhir, kita bisa melihat bahwa ada banyak tugas yang dapat diselesaikan menggunakan fungsi SUMPRODUCT, juga dapat diselesaikan menggunakan rumus array (CSE).

Supaya masih hangat dalam benak pembaca, saya menggunakan contoh 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 penjelasan cara kerjanya. Hingga pada bagian akhir, kita bisa mengetahui bahwa rumus SUMPRODUCT ternyata bisa menjadi alternatif rumus-rumus dan fungsi lainnya termasuk rumus array.

Tidak salah jika dikatakan bahwa SUMPRODUCT sebagai Rumus Multi Guna

Demikian semoga bermanfaat.

Salam..

Artikel Terkait:

• Fungsi SUM dan Rumus Penjumlahan Pada Excel
• Fungsi SUMIF dan Rumus Penjumlahan Bersyarat
• Fungsi COUNTIF: Menghitung Dengan Syarat
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Rumus Excel Tidak Berfungsi? Inilah Penyebabnya!

Referensi:

• https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/

Belajar Rumus Array Untuk Pemula

Formula Array merupakan salah satu fitur penting dalam microsoft excel. Kemampuannya tidak diragukan lagi sangat powerful untuk meningkatkan efisiensi pengolahan data excel.

Namun sangat disayangkan sebagian besar pengguna excel belum memanfaatkan fasilitas ini. Menurut informasi, hanya sekitar 10 % saja pengguna Excel yang menerapkan rumus array dalam pekerjaannya.

Dalam kesempatan ini kita akan mempelajari prinsip dasar fungsi dan formula aray dan beberapa contoh penerapannya.



Secara garis besar, belajar excel kali ini akan membahas:

• Pengertian Fungsi dan Formula Array
• Rumus Array Untuk Mendapatkan Hasil Kalkulasi Ganda/Banyak
• Rumus Array Untuk Mendapatkan Hasil Kalkulasi Tunggal

Pengertian Fungsi dan Formula Array

Sebelum melangkah lebih jauh, alangkah baiknya kita memahami terlebih dahulu apakah yang dimaksud dengan istilah Array?

Array dapat diartikan sebagai kumpulan item data. Di dalam excel, kumpulan data dapat disusun dalam kolom, baris maupun kombinasi keduanya.

Lantas apa yang dimaksud dengan Fungsi dan Formula Aray?

Fungsi dan Formula array merupakan Fungsi dan Rumus excel yang bekerja pada kumpulan item data, alih-alih pada single data.

Sebuah formula array dapat menghasilkan data ganda maupun data tunggal.

Jika kita menggunakan formula biasa, kita cukup menekan Enter atau Ctr + Enter setelah mengetikan formula, maka untuk formula array kita harus menekan Ctr + Shift + Enter, oleh karena itu rumus array sering juga disebut formula CSE.

Kita akan mengenali sebuah formula array dengan ditandai kurung kurawal {} (curly braces). Untuk lebih jelasnya perhatikan ilustrasi berikut:

• Dalam ilustrasi diatas, kita memiliki data pupuk dan dosisnya per Hektar pada range A1:B6
• Seleksi range D1:E6 dan ketikan formula berikut =A1:B6   atau lebih mudahnya ketikan tanda samadengan (=) kemudian sorot range A1:B6
• Tekan Ctr + Shift + Enter

Perhatikan formula masing-masing cell pada range D1:E6. Apa yang dapat kita amati dari data tersebut? Ya kita mendapatkan rumus yang sama persis pada masing-masing cell di dalam range D1:E6, yaitu {=A1:B6}.

Lalu kenapa? meskipun masing-masing cell pada range D1:E6 memiliki formula yang sama persis, tetapi kita dapati hasil/nilainya berbeda pada masing-masing cell?

Jawabannya sudah jelas, yaitu karena kita menggunakan formula array. Rumus array bekerja pada koleksi item data dan dapat menghasilkan data ganda/banyak/multiple dengan hanya satu formula.

Perhatikan tanda kurung kurawal {} pada rumus array. Jangan mengetikan secara langsung tanda tersebut karena tidak akan berguna. Cukup tekan Ctr + Shift + Enter  setiap kali selesai mengetikan rumus array. Jika kita tekan F2 atau double click pada rumus aray,  tanda tersebut akan hilang.

Baiklah untuk menambah pemahaman kita, mari kita lanjutkan tahapan belajar rumus array berikutnya.

• Seleksi range D1:E6 yaitu data dengan formula array yang sudah kita buat pada tahapan sebelumnya.
• Tekan F2, lalu pada formula bar seleksi A1:B6 seperti ilustrasi berikut

• Tekan F9,  lalu tekan Ctr + Shift + Enter

• Perhatikan formula range A1:B6 dimana referensi range sudah berubah menjadi array data yang bersumber dari range tersebut. Tanda koma (,) berfungsi sebagai pemisah (delimiter) kolom, sedangkan titik koma atau semicolon berfungsi sebagai pemisah baris. Pemisah baris atau kolom ini bisa berbeda tergantung setting komputer. Jika komputer anda menggunakan tanda koma sebagai pemisah desimal, maka pemisah baris array biasanya menjadi tanda “\” .

Formula Array Untuk Mendapatkan Hasil Kalkulasi Ganda/Multiple/Banyak

Pada contoh formula array di atas sebenarnya sudah menggambarkan rumus array untuk multiple result, namun tentu saja dengan rumus yang sangat sederhana tanpa ada operasi matematika.

Baiklah mari kita praktekan lagi contoh berikut untuk lebih memahami penggunaan rumus array untuk mendapatkan hasil ganda menggunakan operasi matematika.

• Anggaplah kita memiliki data pada kolom A s.d C yaitu nama pupuk , dosis aplikasi per hektar dan luas hektar aplikasi 
• Selanjutnya kita ingin menghitung Jumlah Kebutuhan Pupuk pada kolom D dimana kebutuhan pupuk dihitung sebagai perkalian antara dosis dengan luas aplikasi.

• Tentu saja tidak ada yang dapat melarang anda untuk mengetikan rumus biasa =B1*C1 pada cell D1 kemudian mengcopy formula tersebut ke range D2:D6
• Namun jika anda ingin memahami formula array, mari lakukan hal yang berbeda. Seleksi range D2:D6 kemudikan ketikan formula berikut = B2:B6*C2:C6 kemudian tekan Ctr + Shift + Enter

Dengan cara tersebut kita mendapatkan formula array {=B2:B6*C2:C6} pada range D2:D6. Masing-masing sel akan memperlihatkan hasil jumlah pupuk yang merupakan hasil perkalian antara dosis dengan luas aplikasi pada row yang sama.

Satu rumus, Hasil beragam. Inilah yang dimaksud rumus array untuk mendapatkan hasil kalkulasi ganda atau multiple result.

Formula Array Untuk Mendapatkan Hasil Kalkulasi Tunggal

Salah satu manfaat dari rumus array adalah dapat menjadi jalan pintas kalkulasi sehingga dapat meminimalkan penggunaan kolom atau data bantuan.





Pada contoh sebelumnya kita sudah menghitung jumlah kebutuhan pupuk yang merupakan hasil perkalian antara dosis dengan luas aplikasi.

Nah sekarang bagaimana caranya untuk menghitung total jumlah kg pupuk tanpa memandang jenisnya?

Jika anda terbiasa dengan data excel, tentu saja akan memandang mudah hal ini. Ya kita tinggal menjumlahkan data pada kolom range (D2:D6) dengan menggunakan fungsi SUM atau menggunakan fasilitas Autosum.

Namun lupakan sejenak cara tersebut jika kita ingin memahami cara kerja rumus array.

Kita sebenarnya bisa mengabaikan penggunaan kolom D  dan dapat melakukan kalkulasi secara langsung dengan formula berikut:

=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6).

Ups, tunggu dulu... tapi bukan itu yang saya maksudkan.

Contoh rumus di atas memang dapat menjadi solusi. Namun bukan solusi yang tepat terutama jika anda ingin bekerja lebih cepat dengan data yang lebih banyak.

Untung contoh tabel yang digunakan hanya terdiri dari 5 baris dan 2 kolom data.

Bayangkan jika kita harus menggunakan rumus tersebut untuk ratusan baris, seberapa panjang formula yang harus dibuat?

Untuk itu, Rumus Array merupakah salah satu solusi yang tepat. Ikuti langkah-langkah berikut:

• Menggunakan data seperti contoh pada sebelumnya, kita akan mendapatkan total Kg pupuk dan ditempatkan pada cell C8
• Pada cell C8 ketikan rumus berikut  =SUM(B2:B6*C2:C6) kemudian tekan Ctr + Shift + Enter

Sampai pada tahap ini kita sudah berhasil mendapatkan data total jumlah kg pupuk dengan hanya menggunakan rumus yang pendek dan tanpa menggunakan kolom/data bantu.

Perhatikan penggunaan fungsi SUM dalam formula array. Dalam contoh tersebut, fungsi SUM berguna untuk menjumlahkan hasil perkalian masing-masing baris dalam rumus array.

Dengan kata lain fungsi ini dapat menjadikan formula array untuk mendapatkan hasil kalkulasi tunggal.

Jika anda sudah memahami fungsi SUMPRODUCT, contoh formula array {=SUM(B2:B6*C2:C6)} menghasilkan nilai yang sama dengan formula =SUMPRODUCT(B2:B6,C2:C6)

Apakah sulit untuk mempelajari rumus array?

Ya pada awalnya mungkin kita harus agak mengkerutkan dahi ketika mempelajari rumus array.

Namun dengan seringnya mencoba dan latihan kita akan merasakan bagaimana formula array dapat menjadi solusi terbaik atas beberapa kasus excel yang tidak dapat diselesaikan dengan formula biasa.

Formula array juga dapat dijadikan sebagai jalan pintas formula dimana jika menggunakan rumus biasa memerlukan beberapa formula transisi pada kolom / baris bantu, sedangkan dengan formula array kita cukup menuliskan satu rumus saja.

Penting Diperhatilan Seputar Formula array.

• Kita tidak dapat mengedit dan menghapus satu atau sebagian cell dalam range rumus array. Karena merupakan satu kesatuan, maka edit atau hapus hanya bisa berlaku bagi semua cell dalam lingkup sebuah array.
• Formula array dapat diedit dengan cara menyeleksi semua sel dalam lingkup formula kemudian tekan F2 dilanjutkan dengan modifikasi atau editing formula.
• Setiap kali melakukan edit rumus array, jangan lupa untuk menekan CTR+SHIFT+ENTER. Jika tidak , maka akan terbaca sebagai formula biasa, bukan array.

Demikian penjelasan ringkas rumus array bagian pertama,  Pada beberapa postingan berikutnya, kita akan melanjutkan membahas lebih lanjut formula array yaitu:

• Pengertian Konstanta Array
• Logika AND dan OR dalam Formula Array
• Double Urinary dan fungsi SIGN serta Penggunaanya dalam Formula Array
• Kelemahan dan Kelebihan Formula Array

Demikian semoga bermanfaat, stay tuned terus ya... :-)

Belajar Excel..!  Excellent..!


Baca Juga Tips dan Tutorial Belajar Excel Lainnya:

• Cara Menggunakan Rumus array TRANSPOSE Excel
• Rumus SUMPRODUCT - Satu Fungsi Ragam Guna
• Cara Mudah Memahami Fungsi dan Formula Excel