Rumus SUMPRODUCT EXACT Menghitung Jumlah Text Case Sensitive

Terbongkar, satu lagi kegunaan fungsi SUMPRODUCT: Menghitung jumlah text secara case sensitive atau peka huruf besar/kecil. Namun untuk membuat rumus perhitungan jumlah text dengan case sensitive ini, fungsi SUMPRODUCT tidak dapat berdiri sendiri, tetapi harus dengan bantuan fungsi lainnya, yaitu fungsi EXACT. Bagaimana cara membuatnya? Tidak terlalu sulit. Mari kita lihat dan buktikan.

Sebelum melangkah ke contoh, alangkah baiknya mengingat dulu terminologi fungsi yang akan digunakan.

Fungsi SUMPRODUCT digunakan untuk Mengalikan komponen-komponen terkait dalam array yang diberikan, kemudian mengembalikan jumlah dari setiap hasil perkalian tersebut. Syntax fungsi ini adalah SUMPRODUCT(array1, [array2], [array3], ...) , dimana array1, array2 dan seterusnya adalah array atau sekumpulan data yang komponen-komponennya ingin dikalikan dan dijumlahkan. Jika SUMPRODUCT hanya memiliki satu parameter array, maka hasil yang diperoleh adalah penjumlahan elemen-elemen yang terdapat dalam satu array tersebut.

Fungsi EXACT digunakan untuk membandingkan dua string teks dan akan mengembalikan TRUE jika kedua string itu sama persis, jika tidak akan mengembalikan FALSE. EXACT bersifat sensitive atau peka terhadap huruf besar kecil tapi mengabaikan perbedaan pemformatan. Syntax fungsi ini =EXACT(text1, text2), dimana text1 dan text2 adalah dua string yang akan dibandingkan

Contoh Rumus Menghitung Text Case Sensitive

Baiklah supaya lebih mudah difahami, mari kita pelajari melalui contoh. Misalnya sudah tersedia text “DKI JAKARTA” pada range A1:A6, namun cara penulisan “DKI JAKARTA” tersebut berbeda-beda huruf besar atau kecilnya.  Selanjutnya kita hanya ingin menghitung text  “DKI JAKARTA” dengan  huruf besar semuanya.

Sebagaimana sudah disampaikan pada bagian awal, untuk keperluan ini, kita akan menggunakan rumus yang berupa gabungan fungsi SUMPRODUCT dan EXACT.

Adapun rumusnya dapat dituliskan sebagai berikut:

=SUMPRODUCT(--EXACT("DKI JAKARTA",A1:A6))

Jika text ("DKI JAKARTA") diletakan dalam sebuah sel sebagai referensi, maka rumusnya dapat diganti menjadi :

=SUMPRODUCT(--EXACT(A10,A1:A6))

Sebagai gambaran penerapannya dalam lembar kerja excel dapat dilihat dalam screenshot di bawah ini.

Cara Kerja Rumus SUMPRODUCT dan EXACT

Bagaimana excel bisa membedakan huruf besar dan kecil serta dapat memberikan informasi jumlah text case sensitive yang tepat kepada usernya yang menggunakan formula SUMPRODUCT EXACT? Mari kita perhatikan penjelasannya.

Silahkan di test terlebih dahulu, cara kerja contoh rumus di atas dengan melakukan konversi parameter-parameter rumus seperti  yang diperlihatkan dalam gambar di bawah ini.

Keterangan: untuk mengkoversi parameter rumus, silahkan di seleksi porsi rumus (highlight hitam),  kemudian tekan tombol F9.

• Fungsi EXACT digunakan untuk membandingkan string "DKI JAKARTA" dengan string yang ada pada setiap sel dalam range A1:A6. Pengecekan perbandingan ini besifat case sensitif. Jika persis sama maka akan menghasilkan nilai TRUE, dan jika tidak sama atau tidak persis sama maka akan  menghasilkan nilai FALSE. Hal ini bisa dilihat setelah rumus EXACT  di konversi maka hasilnya berupa array {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}  urutan TRUE dan FALSE mengikuti hasil pengecekan text pada range A1:A6. 

• Selanjutnya, array berisi data TRUE dan FALSE harus dikonversi lagi menjadi 1  untuk TRUE  dan untuk FALSE, supaya dapat dikalkulasi selanjutnya. Untuk proses ini digunakan bantuan tanda double unary atau tanda double minus (--). Sehingga array menjadi {0;1;1;0;1;0}

• Langkah akhir, adalah tugas SUMPRODUCT untuk mengeksekusi parameter array {0;1;1;0;1;0}. Karena hanya ada satu array, maka SUMPRODUCT tidak mengalikan elemen, tetapi langsung menjumlahkannya. Ini pendapat pribadi saya, apa yang terjadi di dalam system saya tidak mengetahuinya, apakah mengalikan elemen array dengan bilangan 1 kemudian menjumlahkannya, atau bypass langsung menjumlah masing-masing elemennya. Yang jelas jika SUPRODUCT hanya memiliki satu parameter array saja maka hasilnya sama dengan penjumlahan elemen-elemen dalam satu array. Jadi jumlah text "DKI JAKARTA" = 0 + 1 + 1 + 0 + 1 + 0 = 3

Saya kira cukup disini pembahasan mengenai cara menggunakan rumus SUMPRODUCT dan EXACT untuk menghitung jumlah text atau string secara case sensitive (peka huruf besar dan kecil).

Semoga bermanfaat. 

Artikel Terkait:

Rumus SUMPRODUCT Menghitung Data Unik

Rumus SUMPRODUCT Penjumlahan Bersyarat

Cara Mudah Memahami Rumus SUMPRODUCT

Referensi

https://support.office.com/id-id/article/sumproduct-fungsi-sumproduct-16753e75-9f68-4874-94ac-4d2145a2fd2e

https://support.office.com/id-id/article/exact-fungsi-exact-d3087698-fc15-4a15-9631-12575cf29926

Menghitung Jumlah Data Unik Dengan SUMPRODUCT

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:

Rumus SUMPRODUCT untuk Penjumlahan Bersyarat

Cara Mudah Memahami Rumus SUMPRODUCT

Fungsi SUMPRODUCT

Rumus SUMPRODUCT Untuk Penjumlahan Bersyarat

Ada beberapa alternatif rumus excel yang dapat digunakan untuk melakukan penjumlahan bersyarat, salah satunya adalah dengan menggunakan Fungsi SUMPRODUCT. Mengapa menggunakan SUMPRODUCT? karena fungsi ini tersedia, baik versi excel baru, maupun versi lama bersama dengan fungsi SUMIF. Dibandingkan dengan fungsi SUMIF, SUMPRODUCT lebih unggul dalam kaitan kemampuannya yaitu dapat menyelesaikan tugas penjumlahan bersyarat dengan kriteria lebih dari satu jenis.

Penjelasan mengenai cara menggunakan fungsi SUMPRODUCT sendiri sudah disampaikan dalam artikel sebelumnya: Cara Mudah Memahami Rumus SUMPRODUCT. Silahkan dilihat-lihat dulu jika anda belum begitu familiar dengan fungsi ini.

Jika anda sudah mulai familiar dengan fungsi SUMPRODUCT, mari kita lanjutkan ke pembahasan cara Menggunakan Fungsi SUMPRODUCT utuk Penjumlahan Bersyarat. Model penjumlahan seperti ini sering disebut juga Penjumlahan Dengan Kriteria atau Penjumlahan Dengan Kondisi (conditional sum)

Contoh Kasus.

Anggaplah kita memiliki data penjualan produk buah-buahan yang disimpan dalam sebuah tabel terdiri atas tiga kolom. Kolom pertama berisi data tanggal, kolom kedua berisi data Nama Barang, dan kolom Ketiga berisi data Nilai penjualan. Bentuk tabel digambarkan dalan screenshot di bawah ini.

Selanjutnya tugas kita adalah melakukan rekapitulasi penjumlahan dengan syarat atau kriteria sebagai berikut:

1. Menjumlahkan nilai penjualan buah mangga di semua tanggal (Kondisi Tunggal)

2. Menjumlahkan nilai penjualan buah mangga pada tanggal 2 dan 3 April 2018 (Beberapa Kondisi)

Mari kita intip sati per satu.

Contoh 1: Penjumlahan Dengan Kondisi / Kriteria Tunggal Untuk Menghitung Penjualan Buah Mangga.

Untuk kasus ini, hanya ada satu kriteria atau kondisi yang diperlukan, yaitu Buah Mangga. Secara umum rumus SUMPRODUCT ini dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(nama_buah=”buah mangga”), penjualan)

Karena kita menggunakan tabel dimana nama buah terdapat pada range B2:B13 dan data nilai penjumlahan ada di range C2:C13, maka rumus untuk menghitung nilai penjumlahan buah mangga di semua tanggal adalah sebagai berikut:

=SUMPRODUCT(--(B2:B13="Buah Mangga"),C2:C13)

Cara Kerja :

• Rumus SUMPRODUCT akan mengecek konten setiap sel pada range B2:B3 apakah berisi text “Buah Mangga”. Jika iya maka akan menghasilkan nilai TRUE, jika sebaliknya maka bernilai FALSE.  Fungsi SUMPRODUCT juga akan mengambil konten nilai penjualan dari setiap sel pada range C2:C13.

• Jika dikonversi menjadi array maka rumus di atas akan nampak seperti di bawah ini.

SUMPRODUCT(--({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}),{1000000;1500000;2000000;1000000;2000000;2500000;1500000;3000000;2000000;1000000;2000000;2500000})

• Nilai TRUE dan FALSE, supaya bisa diproses selanjutnya maka harus dikonversi dahulu menjadi data numerik. Untuk tugas ini dilimpahkan ke double unary. Perhatikan 2 buah tanda minus (--) sebelum argumen pertama. Tanda inilah yang disebut double unary. Operator ini berguna untuk merubah TRUE menjadi 1, sedangkan FALSE menjadi 0, sehingga dalam tampilan array, rumus tadi akan nampak seperti ini.

=SUMPRODUCT({1;0;0;1;0;0;1;0;0;1;0;0},{1000000;1500000;2000000;1000000;2000000;2500000;1500000;3000000;2000000;1000000;2000000;2500000})

• Ingat kembali mengenai tugas fungsi SUMPRODUCT yaitu melakukan perkalian komponen-komponen terkait dalam array yang diberikan, kemudian mengembalikan jumlah dari setiap hasil perkalian tersebut. Sehingga jika dihitung dengan operasi matematika penjumlahan, rumus di atas dapat dituliskan seperti berikut:

= (1x1.000.000) + (0x1.500.000) + (0x2.000.000) + (1x1.000.000) + (0x2.000.000) + (0x2.500.000) + (1x1.500.000) + (0x3.000.000) + (0x2.000.000) + (1x1.000.000) + (0x2.000.000) + (0x2.500.000)

=1.000.000 + 0 + 0 + 1.000.000 + 0 + 0 + 1.500.000 + 0 + 0 + 1.000.000 + 0 + 0

= 4.500.000

Contoh 2: Penjumlahan dengan kriteria ganda untuk menghitung total penjualan buah mangga pada tanggal 2 s.d 3 April 2018

Untuk menglakukan penjumlahand alam contoh ke-2 ini, kita perlu mempertimbangkan 3 kondisi:

Kondisi ke-1 : Tanggal terbawah, dalam  hal ini tgl 2 April 2018

Kondisi ke-2 : Tanggal teratas, dalam hal ini adalah tanggal 3 April 2018

Kondisi ke-3 : Nama buah yang akan dijumlahkan nilai penjualannya.

Rumus Umum

=SUMPRODUCT(--(tanggal>=tanggal_terbawah),--(tanggal<=tanggal_teratas),--(nama_buah="Buah Mangga"),C2:C13)

Penting: 

• Penulisan tanggal pada kolom tanggal harus benar yaitu harus berupa tanggal, jangan berupa text yang nampak seperti tanggal.
• Untuk menuliskan tanggal dalam rumus SUMPRODUCT, kita bisa menggunakan bantuan fungsi DATE. Fungsi ini dapat dituliskan dengan syntax DATE(tahun,bulan,tanggal).

Setelah kedua poin diatas difahami, maka selanjutnya kita bisa membuat rumus SUMPRODUCT untuk menghitung jumlah nilai penjualan buah mangga pada tanggal 2 s.d 3 April 2018. 

Adapun rumusnya dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(A2:A13>=DATE(2018,4,2)),--(A2:A13<=DATE(2018,4,3)),--(B2:B13="Buah Mangga"),C2:C13)

=2.500.000

Cara Kerja:

• Rumus sumproduct akan mengecek 3 kondisi yang harus terpenuhi, ketiga kondisi tersebut berupa array bernilai TRUE dan FALSE hasil pengecekan tanggal bawah, tanggal atas dan nama buah. TRUE dan FALSE tersebut kemudian dikonversi menjadi bilangan 1 dan 0 oleh operatur double unary (--). 
• Fungsi SUMPRODUCT kemudian melakukan perkalian masing-masing komponen array yang terkait (sebaris). Jika ada angka 0 dalam salah satu komponen yang dikalikan, maka akan dihasilkan bilangan 0.  Ini artinya semua syarat harus terpenuhi atau bernilai 1  supaya nilai penjumlahan dapat terhitung. Terakhir fungsi SUMPRODUCT menjumlahkan semua hasil perhitungan tadi. Dan hasilnya adalah 2.500.000

Tips: Anda dapat menggunakan tombol shortcut F9 untuk mengecek masing-masing elemen array dalam rumus SUMPRODUCT. Cukup seleksi salah satu argumen fungsi, kemudian tekan F9. Cara penggunakan shortcut F9 untuk mengecek array ini sudah dijelaskan pada pembahasan rumus SUMPRODUCT sebelumnya yang berjudul : Cara Mudah Memahami Rumus SUMPRODUCT.

Demikian pembahasan singkat mengenai cara menggunakan fungsi SUMPRODUCT untuk membuat rumus penjumlahan bersyarat. 

Semoga bermanfaat.

Silahkan dilihat juga contoh rumus excel lainnya:

Cara Mudah Memahami Rumus SUMPRODUCT

Fungsi SUMPRODUCT

Cara Mudah Memahami Rumus SUMPRODUCT

Fungsi SUMPRODUCT merupakan salah satu fungsi Excel yang  cukup penting untuk difahami dan dikuasai. Tersedia sejak excel versi lawas, Rumus SUMPRODUCT masih cukup berguna meskipun pada excel versi baru sudah tersedia fungsi-fungsi lain yang dapat menggantikan fungsi ini. Ada beberapa tugas yang dapat diselesaikan dengan fungsi yang satu ini. Misalnya: Penjumlahan dengan kriteria atau penjumlahan bersyarat (alternative SUMIF dan SUMIFS), menghitung dengan Syarat (alternative COUNTIF dan COUNTIFS), Alternative VLOOKUP 2 dimensi, sebagai alternAtive rumus array, menghitung jumlah nilai unik, dan lain sebagainya.  

Tulisan ini ditujukan untuk pembaca yang belum begitu familiar dengan fungsi SUMPRODUCT di Excel, namun ada keinginan untuk mengetahui dan memahami bagaimanan fungsi ini bekerja dan bagaimana cara memanfaatkannya.

Penjelasan Fungsi SUMPRODUCT.

Pada dasarnya fungsi SUMPRODUCT bekerja dengan cara mengalikan komponen-komponen terkait dalam array yang diberikan, kemudian mengembalikan jumlah dari setiap hasil perkalian tersebut.

Fungsi ini dapat dituliskan dengan syntax berikut:

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

Array adalah kumpulan dari nilai-nilai data bertipe sama dalam urutan tertentu yang menggunakan sebuah nama yang sama.

Jika anda masih pemula, mungkin agak bingung dengan penjelasan di atas. Apalagi dengan adanya istilah array, karena memang tidak semua pengguna excel familiar dengan istilah ini. Untuk memudahkan pemahaman, mari kita perhatikan contoh ilustrasi berikut:

Screenshot di atas menggambarkan data jumlah barang beserta harganya. Tugas yang diminta adalah bagaimana menghitung kebutuhan uang belanja untuk membeli semua barang tersebut.

Dengan menggunakan rumus matematika sederhana, kita dapat menghitungnya seperti ini:

Jumlah uang belanja = ( jumlah tahu x harga tahu) + (jumlah tempe x harga tempe) + (jumlah oncom x harga  oncom)

= (3 x 100) + (2 x 200) + (1 x 100)

= 800

Model data di atas jika dihitung menggunakan rumus SUMPRODUCT, maka dapat dituliskan sebagai berikut:

=SUMPRODUCT({3;2;1},{100;200;100})

Perhatikan list bilangan dalam tanda kurung kurawal. Itulah yang dimaksud dengan array. Ada dua array dalam contoh rumus di atas, yaitu array jumlah barang dan array harga barang. Fungsi SUMPRODUCT sendiri bertugas untuk mengalikan elemen array jumlah dan array harga yang posisinya sama, kemudian menjumlankan hasilnya.

Rumus SUMPRODUCT Range Vertikal.

Dalam praktek excel sendiri, array bisa berupa referensi range. Seperti contoh gambar di atas, array jumlah berupa range B2:B4 dan array harga berupa range C2:C4.

Maka rumus SUMPRODUCT juga dapat dituliskan sebagai berikut:

 =SUMPRODUCT(B2:B4,C2:C4)

Dari gambar di atas, kita dapat mengetahui bahwa rumus SUMPRODUCT mengalikan bilangan dari kolom jumlah dan kolom harga pada baris yang sama, kemudian menjumlahkan hasilnya. Ini artinya rumus SUMPRODUCT bekerja secara vertikal.

Rumus SUMPRODUCT Range Horizontal

Selain bekerja secara vertikal, rumus SUMPRODUCT dapat bekerja secara horizontal seperti diperlihatkan dalam gambar di bawah ini.

Perhatikan gambar di atas. Rumus SUMPRODUCT dalam contoh tersebut bekerja dengan cara mengalikan elemen-elemen data yang satu kolom dari baris data jumlah dan baris data harga.

Merubah referensi range menjadi array.

Pada prinsipnya, fungsi SUMPRODUCT bekerja pada array, dan kita bisa melihat bagaimana range referensi dalam bentuk array. Untuk itu silahkan diperhatikan contoh rumus yang digunakan dalam contoh terakhir. SUMPRODUCT Horizontal.

=SUMPRODUCT(B2:D2,B3:D3)

Seleksi sel dimana rumus tersebut berada, kemudian gerakan kursor pada formula bar, seleksi bagian rumus B2:D2  kemudian tekan tombol F9,  kemudian seleksi bagian rumus B3:D3 dan tekan F9 sekali lagi.

Hasil dari proses ini, kita akan mendapatkan rumus SUMPRODUCT dengan parameter array. Dalam kata lain, parameter referensi range berubah menjadi array, seperti diperlihatkan dalam gambar ini. 

Perhatikan penggunaan tanda koma (,) sebagai pemisah masing masing elemen array. Jenis tanda ini  akan tergantung setting komputer yang digunakan. Untuk komputer yang saya gunakan, kebetulan pemisah elemen array horizontal berupa tanda koma ( , ) , sedangkan array vertikal berupa tanda titik koma ( ; ).

Sampai disini dulu pembahasan mengenai Cara Mudah Memahami dan Menggunakan Fungsi SUMPRODUCT di Excel. Harapannya, ini bisa memberikan manfaat bagi pembaca.

Terimakasih.

Artikel terkait:

• Rumus SUMPRODUCT Untuk Penjumlahan Bersyarat
• Menghitung Data Unik Dengan Fungsi SUMPRODUCT
• Rumus SUMPRODUCT Menghitung Case Sensitive

Rumus LOLOS GAGAL Tanpa Fungsi IF

Ternyata tidak harus menggunakan fungsi IF untuk bisa membuat rumus keterangan “LOLOS” / “GAGAL”, keterangan “BAIK” / “BURUK”, keterangan “GANTENG” / “JELEK” ataupun keterangan kategori apapun yang terdiri atas dua pilihan. Hebatnya lagi, bahkan tidak diperlukan Fungsi excel apapun. Sangat praktis, cukup menggunakan rumus matematika pengurangan sederhana, dan tentu saja dengan sedikit modifikasi format number.

Penasaran bagaimana caranya? Yuk diintip langkah-langkahnya.

Contoh Kasus:

Anggaplah anda anda ditugaskan untuk merekap skor seleksi awal sebuah penyaringan bakat. Seorang peserta dinyatakan lolos ke tahap berikutnya jika mencapai skor minimal. Bagaimana cara anda melakukannya dengan menggunakan rumus EXCEL?

Kebanyakan user Excel mungkin akan langsung melahap tugas tersebut dengan cara menggunakan rumus IF yang alur logikanya seperti ini:  =IF(score >= nilai_minimum,”LOLOS”,”GAGAL”) . Yang artinya: Jika score peserta melebihi atau sama dengan nilai minimum, maka dinyatakan “LOLOS”, sebaliknya jika score minimum tidak tercapai, maka peserta dinyatakan “GAGAL”.

Fungsi IF untuk kasus ini memang sangat sederhana dan sangat mudah, Namun karena kita ingin mencoba yang tidak biasa, maka lupakanlah sejenak fungsi IF, dan mari berfikir out of the box.

Kembali ke masalah kategori. Tujuan rumus yang kita buat hanya mendapatkan satu kriteria dari dari 2 pilihan kategori. Jika  ini dianalogikan dengan bilangan, kita hanya perlu nilai positif atau negatif. Nilai positif jika score minimun tercapai (dalam kasus ini angka nol dimasukan ke positif). Dan nilai negatif jika score minimum tidak tercapai.

Nah angka positif dan negatif inilah yang dapat kita olah format number-nya sehingga bisa menampilkan keterangan yang kita inginkan yaitu :

Kategori “LOLOS” jika score lebih dari atau sama dengan nilai minimum.

Kategori “GAGAL” jika keadaan sebaliknya yaitu nilai minim tidak tercapai.

Perhatikan screenshot di bawah ini.

Dari gambar tersebut , kita mengetahui bahwa pada kolom keterangan hanya cukup diketikan rumus operasi pengurangan yaitu nilai score dikurangkan standar kelolosan, dalam hal ini standar score 75. 

Tugas kita sekarang adalah merubah bilangan tersebut menjadi keterangan “LOLOS” atau “GAGAL”. Jika NOL atau Bilangan Positif berarti LOLOS dan jika Negatif berarti GAGAL.

Untuk tujuan tersebut, kita bisa menggunakan bantuan format number.

• Seleksi data kolom keterangan, kemudian tekan CTR+1 atau klik kanan >> Format Cells
• Masuk ke Tab Number >> pilih category Custom
• Pada kotak input Type, tuliskan custom number berikut:  "LOLOS";"GAGAL"

• Lalu klik OK.

Dan… Jrenggg, kita berhasil membuat keterangan “LOLOS” dan “GAGAL” seperti dalam gambar ini.

Penting diperhatikan:

Cara membuat kategori LOLOS atau GAGAL seperti ini tidak merubah value atau konten dalam sel. Cara ini hanya merubah tampilan nilai nol dan positif menjadi text “LOLOS”, dan nilai negatif menjadi text “GAGAL”. Sehingga jika kita membuat sebuah rumus matematika yang menggunakan referensi data di kolom keterangan maka kita masih bisa mendapatkan bilangan dan tidak error. Sedangkan operasi matematika akan error jika mengacu pada referensi text.

Demikian pembahasan singkat ini mengenai cara membuat keterangan “LOLOS” / “GAGAL” tanpa menggunakan fungsi IF. Harapannya ini bisa bermanfaat dan dapat menambah wawasan kita mengenai pilihan alternatif dalam menjalankan tugas-tugas menggunakan excel. Ingat: Tujuan mungkin hanya 1, Tetapi Jalan yang dilalui bisa berbeda.

Terimakasih.

Artikel terkait:

Fungsi IF Bertingkat Untuk Penilaian Prestasi Karyawan

Rumus IF untuk Keterangan LULUS tidak Lulus
Rumus IF untuk Menghitung Jam Lembur

Fungsi IF

Rumus VLOOKUP Untuk Apa Sich?

Rumus VLOOKUP untuk apa ya? Pertanyaan ini kerap muncul dalam sebuah presentasi kecil-kecilan yang menampilkan data excel. Ironis, meskipun fungsi ini sudah sangat populer di kalangan pengguna excel, namun kenyataannya masih saja ada user yang belum mengerti dan memanfaatkan fungsi VLOOKUP. Padahal, tidak sedikit diantaranya sudah sangat lama berkecimpung mengoperasikan excel.

Mungkin pembaca belum memanfaatkan fungsi VLOOKUP karena tidak tahu untuk apa dan bagaimana cara menggunakannya. Mudah-mudahan penjelasan singkat berikut ini dapat membantu memahami bagaimana pentingnya fungsi VLOOKUP dan bagaimana cara menggunakannya untuk apa saja dalam tugas-tugas terkait pencarian data.

Sebelum melanjutkan pembahasan, video sederhana berikug akan membuat anda lebih faham mengenai apa fungsi dan cara menggunakan fungsi VLOOKUP. Simak baik-baik ya...

VLOOKUP seperti yang tersirat dari namanya : Vertical Lookup yaitu fungsi yang berguna untuk mencari data apa saja secara vertikal dari atas ke bawah dalam sebuah kolom tertentu. Kemudian jika data yang dicari tersebut sudah ketemu, maka Vlookup akan memberikan informasi dengan mengambil data dari kolom lainnya sesuai nomor urut kolom tertentu yang ditentukan dalam rumus.

Adapun syntax fungsi VLOOKUP adalah sebagai berikut:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Jika di-Indonesia-kan, bahasa VLOOKUP tersebut dapat ditulis kira-kira seperti ini.

VLOOKUP(NilaiDicari, Tabel, NomorUrutKolom,[LingkupPencarian])

Atau secara singkat dapat dijabarkan bahwa VLOOKUP mencari NilaiDicari pada Kolom pertama dari Tabel dan mengembalikan nilai dari NomorUrutKolom yang sebaris dengan posisi nilaiDicari yang sudah ditemukan dalam Tabel. Lingkup pencarian menentukan apakah mode true (pendekatan nilai) atau false (bukan pendekatan nilai, nilai harus matching).

Contoh Kasus.

Contoh yang sederhana, namun penting dan sering dijumpai dalam pekerjaan adalah bagaimana menggali keterangan data karyawan berdasarkan NIK yang sudah diketahui. Kita tinggal memasukan NIK, maka otomatis nama dan data lainnya yang terkait karyawan tersebut akan terdisplay.

Dalam hal ini kita memiliki tabel data karyawan terdiri atas 5 kolom dengan urutan kolom sebagai berikut:

Kolom ke-1 = berisi data NIK

Kolom ke-2 = berisi data Nama

Kolom ke-3 = berisi data Jenis Kelamin

Kolom ke-4 = berisi data Tanggal Lahir

Kolom ke-5 = berisi data Asal Kota

Kemudian satu-satunya informasi yang sudah kita miliki adalah nomor NIK karyawan. Nah, berbekal kata kunci tersebut, selanjutnya bagaimana kita dapat menggali informasi lainnya? Disinilah rumus VLOOKUP memainkan perannya.

Perhatikan ilustrasi dalam screenshot di bawah ini.

Berdasarkan gambar diatas, kita hanya disediakan satu kotak isian yaitu data NIK yang harus diketika manual, sedangkan data-data lainnya akan muncul secara otomatis setelah kita menginput NIK.

Perhatikan rumus pada baris ke-dua yang dapat menghasilkan data nama “Ratna”. Kenapa excel bisa tahu bahwa yang memiliki NIK 12562 adalah si Ratna. 

Adapun rumusnya adalah sebagai berikut.

=VLOOKUP(C$1,$A$12:$E$16,2,0)

Kemudian coba bandingkan dengan syntax fungsi VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Mari kita jelaskan masing – masing komponen fungsi.

• Lookup_value  yaitu nilai yang dicari. Dalam hal ini adalah konten sel C1 yaitu NIK Karyawan 12562
• Table_array, yaitu tabel dimana dilakukan pencarian dan pengambilan data. Dalam contoh diatas tabel tersebut berada pada range A12:E16.  Perhatikan : kolom pertama tabel tersebut harus berisi nata NIK Karyawan yaitu kolom kunci untuk proses pencarian.
• Col_index_num yaitu nomor urut kolom dalam tabel dimana nantinya rumus VLOOKUP akan mengambil data yang diperlukan. Contoh angka 2 berarti untuk mengambil data pada kolom ke-2, yaitu data nama.
• Range_lookup bersifat opsional, namun sangat penting, yaitu mode lingkup pencarian. Angka 0 menunjukan pencarian data bersifat matching, dan tidak dipengaruhi oleh urutan data apakah bersifat berurutan atau acak. Kita gunakan angka 0 karena kita tahu kita harus mencari NIK yang tepat sama dengan NIK kunci, dan uturan NIK pada tabel pun tidak berurutan (ascending atau descending)
• Kesimpulan: Rumus VLOOKUP mencari NIK 12562 (konten C1) pada tabel pertama range A12:E16, dan memberikan informasi dari kolom ke-2 yang sejajar dengan posisi NIK yang dicari. Hasilnya : Ratna.

Berikutnya kita hanya tinggal merubah parameter col_index_num atau nomor urut kolom untuk mendapatkan informasi lainnya yaitu jenis kelamin (kolom ke-3), tanggal lahir (kolom ke-4) dan asal kota (kolom ke-5).

Sampai disini pembahasan singkat untuk menjawab pertanyaan: Rumus VLOOKUP Untuk Apa? Mudah-mudahan contoh dan penjelasannya mudah difahami sehingga bisa diterapkan dalam pekerjaan, khususnya jika kita sering berkecimpung dengan database excel yang besar dimanaVLOOKUP akan sangat berguna dalam tugas pencarian data.

Terimakasih.

Artikel terkait

Rumus VLOOKUP Untuk Tanggal

Rumus VLOOKUP Untuk Mencari Nama Barang

Rumus VLOOKUP Untuk Pengelompokan Nilai

Rumus VLOOKUP Untuk Tanggal

Penggunaan rumus VLOOKUP untuk tanggal penting ketika kita perlu mencari data terkait aktifitas pada tanggal tertentu, misalnya status absensi karyawan pada tanggal tertentu, agenda penting pada tanggal tertentu, pengaturan tim kerja pada range tanggal tertentu, dan lain sebagainya. 

Bagaimana cara menggunakan rumus VLOOKUP untuk tanggal? 

Yuk kita perhatikan dan ikuti baik-baik contoh sederhana penggunaan fungsi VLOOKUP ini untuk mencari data berdasarkan tanggal yang sudah diketahui.

Contoh 1: Rumus VLOOKUP untuk Tanggal Tunggal - mengetahui status kehadiran karyawan pada tanggal tertentu.

Untuk membuat rumus ini, langkah pertama yang harus kita persiapkan adalah list data absensi karyawan. List tersebut berisi kolom tanggal tunggal (setiap hari).

Contoh paling sederhana nya kita ambil list status absensi untuk satu orang karyawan saja, anggaplah namanya Jono. List tanggal dan status absensi kita taroh di kolom A dan B. Kemudian tanggal yang dicari kita taruh di sel lainnya, anggaplah di sel D3.

Maka rumus VLOOKUP untuk mencari status absensi si Jono adalah sebagai berikut:

=VLOOKUP(D2,A3:B11,2)

Adapun kenampakannya dalam spread sheet mungkin akan seperti dalam gambar berikut:

Untuk membuat rumus VLOOKUP tanggal tanpa menggunakan referensi sel yang berfungsi menyimpan tanggal yang dicari maka kita dapat mengetikan tanggal secara langsung pada rumus VLOOKUP. Dalam hal ini kita perlu menggunakan bantuan fungsi DATEVALUE  untuk mengkonversi text tanggal menjadi nilai tanggal.

Misalnya untuk mencari status kehadiran karyawan pada tanggal 10-04-2018, rumusnya dapat dituliskan sebagai berikut:

=VLOOKUP(DATEVALUE("04-06-2018"),A2:B11,2,0)

Formula di atas jika menggnakan list data absensi si Jono maka akan menghasilkan nilai mangkir.

Perhatikan format penulisan tanggal sebagai parameter fungsi DATEVALUE

("04-06-2018" dibaca sebagai tanggal 6 bulan 4 tahun 2018,  bukan tanggal 4 bulan 6 tahun 2018. Hal tersebut tergantung regional setting pada komputer  yang kita gunakan.

Contoh 2: Rumus VLOOKUP Untuk Mengetahui Team Kerja Yang Bertugas Pada Range Tanggal Tertentu

Misalnya dalam sebuah organisasi kerja, sudah diatur jadwal team yang bertugas dalam range tanggal yang ditentukan. Nah, kita bisa menggunakan rumus VLOOKUP  untuk mengetahui tim mana sih yang seharusnya bertugas pada tanggal tertentu.

Untuk keperluan ini, seperti biasa perlu dipersiapkan terlebih dahulu list range tanggal dan data tim mana yang bekerja pada range tanggal tersebut. Anggaplah begini:

• Tanggal 1 – 5 April  2018  = Tim A
• Tanggal 6 – 10 April 2018 = Tim B
• Tanggal 11- 15 April 2018 = Tim C
• Tanggal 16-20 April 2018 = Tim A
• Dan seterusnya…

Supaya  jadwal tugas tersebut bisa dibaca oleh fungsi VLOOKUP, maka harus dibuat seperti dalam gambar berikut:

Selanjutnya, anggaplah kita ingin membuat rumus yang secara otomatis dapat memberikan informasi mengenai tim mana yang bertugas pada tanggal tertentu.

Anggaplah tanggal yang dicari diletakan di sel E2, maka rumusnya dapat diketikan di sel E3 sebagai berikut:

=VLOOKUP(E2,A2:B5,2)

Penting diperhatikan:

• List range tanggal harus disusun berurutan dari terkecil ke yang terbesar, perhatikan kolom A.
• Rumus VLOOKUP akan mengelompokan tanggal yang dicari ke tanggal yang sama dari tanggal yang dicari, atau tanggal yang lebih kecil terbesar dari tanggal yang dicari.
• Dalam kasus nomor 2 ini sebenarnya cukup diperlukan 3 parameter rumus vlookup saja. Tetapi jika mau, kita juga bisa menambahkan angka 1 atau TRUE sebagai parameter ke-4. Sehingga rumus terakhir dapat juga dituliskan  =VLOOKUP(E2,A2:B5,2,1)  atau  =VLOOKUP(E2,A2:B5,2,TRUE)

  

Demikian pembahasan ringkas dan sederhana mengenai rumus VLOOKUP untuk tanggal. Semoga bermanfaat.

Terimakasih.

Artikel terkait:

Rumus VLOOKUP Untuk Mencari Nama Barang

Rumus VLOOKUP Untuk Pengelompokan Nilai
Rumus VLOOKUP Untuk Apa Sich?
Menghitung Data Antara 2 Tanggal