Fungsi SUMPRODUCT juga ternyata dapat digunakan untuk menghitung jumlah data unik pada excel. Hal ini tentu saja menambah daftar kemampuan fungsi SUMPRODUCT, disamping untuk melakukan penjumlahan bersyarat seperti yang sudah dibahas dalam artikel sebelumnya. Namun untuk menghitung data unik, fungsi SUMPRODUCT tidak dapat berdiri sendiri. Fungsi ini harus digabung dengan fungsi excel lainnya yaitu COUNTIF. Bagaimana caranya? Cukup ikuti langkah-langkah dan contoh sederhana yang akan dijelaskan dalam artikel ini.
Contoh Kasus.
Misalnya kita memiliki data list daftar belanja. Namun dalam list tersebut ada beberapa item barang belanjaan yang muncul lebih dari satu kali. Dalam kata lain ada duplikat data.
Anggaplah daftar belanja tersebut adalah buah-buahan, seperti dalam gambar di bawah ini.
Dengan mengandalkan ketelitian mata, sebenarnya kita bisa menghitung secara manual, ada berapa sich sebenarnya jenis buah-buahan dalam list di atas. Dengan sedikit upaya kita bisa mengetahui 4 jenis buah yaitu Mangga, Jeruk, Sirsak dan Pisang).
Jika list data hanya sedikit dan perhitungan diperlukan hanya untuk sekali waktu saja, memang cara hitung manual tidak terlalu bermasalah. Lain halnya jika kita mempunyai list data yang besar, dan perlu dilakukan analisa data unik supaya tidak terjadi double perhitungan.
Untuk contoh, kita gunakan list data yang kecil, supaya lebih mudah membuat gambar screenshotnya, karena kalau kebesaran, tidak muat layar komputernya 😊😊🙏🙏
Kembali ke contoh. Bagaimana rumus untuk menghitung jumlah jenis buah dalam list belanja.
Kita bisa mengetikan rumusnya sebagai berikut:
=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))
Rumus di atas memberikan hasil 4.
Cara Kerja Rumus.
Sebagaimana kita ketahui bawha fungsi SUMPRODUCT bekerja dengan cara mengalikan komponen-komponen sejajar dalam array. Jika hanya ada satu array maka hasilnya akan sama dengan penjumlah elemen dalam array itu sendiri atau sama dengan hasil rumus SUM.
Sedangkan fungsi COUNTIF digunakan untuk menghitung masing-masing barang. Hasil perhitungan dengan COUNTIF tersebut kemudian dijadikan sebagai penyebut dalam pecahan dengan pebilang 1. Ini dimaksudkan supaya pada saat dijumlahkan nantinya menjadi 1.
Misalnya jika ada 4 mangga rumus COUNTIF mengembalikan nilai 4. Karena ada 4 mangga maka ¼ + ¼ ¼ + ¼ = 1. Ini artinya berapapun kemunculan duplikat dalaml list, maka akan dihitung sebagai satu.
Silahkan diperika cara kerja rumus tadi dengan menkonversi parameter formula menjadi array menggunakan bantuan tombol F9.
- Seleksi bagian rumus COUNTIF(A2:A12,A2:A12) kemudian tekan F9 dan tekan enter. Maka seketika rumus akan berumah menjadi.
=SUMPRODUCT(1/{4;2;4;1;4;4;2;4;4;4;4})
- Bilangan dalam tanda kurung kurawal merupakan jumlah kemunculan data yang sama dalam list.
Sekarang: seleksi bagian rumus 1/{4;2;4;1;4;4;2;4;4;4;4} kemudian tekan F9 sehingga rumus akan berubah menjadi:
=SUMPRODUCT({0.25;0.5;0.25;1;0.25;0.25;0.5;0.25;0.25;0.25;0.25})
Terakhir: SUMPRODUCT menjumlah elemen-elemen array tersebut, dan hasil akhirnya adalah 4 data unik.
'=0,25+0,5+0,25+1+0,25+0,25+0,5+0,25+0,25+0,25+0,25
= 4
Menghitung Jumlah Data Unik Dengan Mengabaikan Data Kosong
Jika kita perhatikan contoh rumus sebelumnya, kemungkinan akan menghasilan nilai error #DIV/0! apabila ada salah satu atau lebih sel yang kosong dalam referensi.
Kenapa bisa error? Untuk mengecek kenapa error, silahkah hapus konten salah satu sel, misalnya seperti gambar dibawah ini:
Kemudian kembali ke sel yang berisi rumus :
=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12)).
Pada formula bar, seleksi menggunakan kursor, bagian rumus COUNTIF(A2:A12,A2:A12), lalu tekan F9 sehingga rumus akan menjadi =SUMPRODUCT(1/{4;1;4;2;4;2;0;3;4;3;3})
Perhatikan elemen-elemen array dalam tanda kurung kurawal. Ternyata salah satunya bernilai 0 yang mencerminkan sel kosong.
Karena dalam perhitungan nilai unik ini kita menempatkan elemen array hasil COUNTIF sebagai pembagi, maka pembagian dengan bilangan 0 akan menyebabkan error #DIV/0!
Solusi:
Untuk mengatasi tersebut, supaya rumus mengabaikan sel kosong dan terbebas dari error, maka kita dapat memodifikasinya menjadi:
=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))
Dari gambar diatas, dapat disimpukan rumus ke-2 lebih disarankan daripada rumus pertama.
Demikian pembahasan mengenai cara menghitung data unik menggunakan SUMPRODUCT dan COUNTIF. Silahkan dicoba dan semoga bermanfaat.
Terimakasih.
Baca juga artikel terkait lainnya:
kk klw mau menambahkan satu kondisi bulan atau priode itu di tambaha apa ya
ReplyDelete