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

Rumus VLOOKUP Untuk Mencari Nama Barang

Salah satu kegunaan rumus VLOOKUP adalah untuk mencari nama barang berdasarkan code yang sudah diketahui. Tidak perlu repot-repot melelahkan mata untuk mencari nama barang dalam sebuah list data. Namun cukup buat saja sebuah rumus VLOOKUP menggunakan program Ms Excel. Selanjutnya kita cukup menginput code tertentu, maka nama barang seketika akan muncul. 

Sebelum melanjutkan terlebih dahulu mari kita simak video yang menjelaskan cara membuat rumus VLOOKUP PALING GAMPANG berikut ini.

Untuk membuat rumus menggunakan fungsi VLOOKUP ini tidaklah terlalu sulit. Silahkan perhatikan baik-baik contoh berikut:

Contoh dibawah ini akan lebih mudah difahami jika anda meng-copy tabel contoh ke dalam program Ms Excel. Copy kan tepat pada sel A1.

A1	B	C	D
2	Code                 :	11345
3	Nama Barang :	=VLOOKUP(C2,B7:C13,2,0)	>> Mencari code 11345 (sel C2) pada range B7:C13 kolom pertama (kolom code) dan mendapatkan data pada kolom ke-2 (kolom nama barang) yang sebaris dengan code yang dicari. Untuk code 11345, nama barangya = mendoan. Jika kode barang yang sesuai tidak ditemukan maka rumus akan menghasilkan nilai error #N/A
4
5
6	Code	Nama Barang
7	11232	Pisang Goreng
8	11234	Ikan Asin
9	11345	Mendoan
10	11355	Bala-Bala
11	11357	Tempe Kering
12	11564	Kue Donat
13	12664	Tahu Bulat

Cara kerja rumus VLOOKUP untuk mencari nama barang berdasarkan code yang sudah diketahui:

1. Tentukan code yang akan dicari, bisa diketikan langsung dalam rumus VLOOKUP, namun disarankan code tersebut diletakan dalam sebuah sel seperti contoh tabel di atas yaitu sel C2 yang berisi nilai code 11345
2. Tentukan sebuah tabel yang memiliki kolom berisi list code dan kolom lainnya berisi list nama barang. Pastikan list code harus berada pada kolom paling kiri atau kolom pertama. Dalam contoh diatas kita menggunakan sebuah tabel pada range B7:C1 yang berisi 2 kolom. Kolom ke-1 berisi list code, dan kolom ke-2 berisi list nama barang. Anda bisa memodifikasi cara penulisan range B7:C1 menjadi $B$7:$C$1 supaya jika dicopy ke sel lainnya akan tetap mengacu pada referensi range tersebut. Alternative lainnya adalah dengan membuat nama range.
3. Tentukan urutan kolom dalam tabel yang berisi nama barang, dalam contoh di atas adalah kolom ke-2. Angka ini bisa disesuaikan tergantung tabel referensi yang tersedia. Dalam praktek nyata mungkin saja anda memiliki tabel lain yang berisi banyak kolom. Namun yang perlu diingat adalah kolom yang menjadi kunci pencarian harus terletak di sebelah kiri.
4. Angka 0 (Nol) menunjukan bahwa rumus VLOOKUP akan mencari benar-benar angka yang matching dengan code yang diketahui, bukan sekedar angka pendekatan. Jika menghilangkan angka nol pada parameter paling akhir dari rumus VLOOKUP, maka mungkin saja rumus akan menghasilkan nilai error ataupun hasil  yang tidak benar.

Demikian penjelasan ringkas cara menggunakan Rumus VLOOKUP untuk mencari nama barang. 

Semoga bermanfaat.

Artikel Terkait:
Cara VLOOKUP Gambar dan Foto di Excel
Rumus VLOOKUP Untuk Pengelompokan Nilai
Rumus IF Untuk Range Nilai Ujian
Rumus VLOOKUP

Rumus IF Untuk Range Nilai Ujian

Dalam penilaian akhir ujian siswa, Nilai siswa tidak hanya disajikan dalam bentuk bilangan, tetapi juga biasanya dikelompokan dalam kategori huruf, misalnya A, B, C, D, dan E, dimana masing-masing huruf tersebut mewakili range nilai tertentu. Untuk mengerjakan tugas ini, excel dapat dengan mudah melakukannya secara otomatis dengan bantuan rumus IF. Nah pembahasan kali ini akan membahas bagaimana cara menggunakan Rumus IF untuk konversi range nilai menjadi huruf.

Contoh kasus.

Anggaplah kita ditugaskan membuat formula excel untuk menyajikan kategori range nilai siswa menjadi 5 kelompok nilai A, B, C, D dan E dengan ketentuan klasifikasi range pengelompokan nilai sebagai berikut:

• Kelompok A, nilai 90 atau lebih

• Kelompok B, nilai 80 lebih tetapi kurang dari 90

• Kelompok C, nilai 70 lebih tetapi kurang dari 80

• Kelompok D, nilai 60 lebih tetapi kurang dari 70

• Kelompok E, nilai kurang dari 60

Perlu dingat bahwa:

Untuk penglompokan nilai dengan range meningkat atau menurun, maka kita memerlukan sejumlah N-1 rumus IF, dimana N = jumlah kategori.

Sehingga untuk pengelompolan 2 kategori diperlukan 1 fungsi IF, contohnya adalah rumus IF untuk keterangan lulus dan tidak lulus. Untuk mengelompokan nilai menjadi 3 kategori diperlukan 2 fungsi IF bertingkat, contohnya kategori negatif, netral dan positif.

Maka untuk menyajikan range nilai siswa  menjadi 5 kategori, kita memerlukan 4 fungsi IF.

Anggaplah data nama siswa terletak pada kolom A, data nilai pada kolom B dan kategori huruf terletak dalam kolom C. Baris pertama berisi header kolom, sedangkan data dimulai pada baris ke-2. Selanjutnya maka kita dapat mengetikan rumus IF pada sel C2 sebagai berikut:

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","E"))))

Copy rumus tersebut sampai ke baris akhir data, sehingga kita bisa mendapatkan kategori nilai huruf pada masing-masing baris, seperti diperlihatkan dalam screenshot berikut:

Cara kerja rumus IF untuk range nilai (5 kategori)

Adapun cara kerja rumus IF untuk mendapatkan nilai huruf kategori tersebut dapat dijelaskan sebagai berikut:

• Fungsi IF pertama akan mengecek apakah nilai memenuhi syarat pertama (>=90). Jika iya maka rumus akan langsung menghasilkan nilai kategori pertama yaitu huruf A. jika tidak maka lanjut ke IF ke-2
• Fungsi IF ke-2 akan mengecek apakah nilai memenuhi syarat ke-2 (>=80). Jika iya maka rumus akan langsung menghasilkan nilai kategori kedua yaitu huruf B. jika tidak maka lanjut ke IF ke-3
• Fungsi IF ke-3 akan mengecek apakah nilai memenuhi syarat ke-3 (>=70). Jika iya maka rumus akan langsung menghasilkan nilai kategori ketiga yaitu huruf C. jika tidak maka lanjut ke IF ke-4
• Fungsi IF ke-4 akan mengecek apakah nilai memenuhi syarat ke-4 (>=60). Jika iya maka rumus akan langsung menghasilkan nilai kategori ketiga yaitu D. 
• Jika range nilai tidak memenuhi syarat empat kategori sebelumnya maka rumus akan menghasilkan nilai kategori ke-5 haitu huruf E. ini terjadi jika nilai kurang dari 60.

Demikian cara menggunakan rumus IF untuk pengelompokan range nilai, beserta penjelasan ringkas bagaimana rumus IF bertingkat ini bekerja.

Semoga bermanfaat.

Artikel Terkait:

Rumus IF Untuk Keterangan Lulus / Tidak Lulus

Rumus VLOOKUP Untuk Pengelompokan Nilai

Rumus VLOOKUP Untuk Pengelompokan Nilai Ujian

Rumus VLOOKUP ternyata dapat menggantikan rumus IF dalam pengelompokan nilai. Bagi anda yang masih sering dibingungkan dengan penggunaan rumus IF bertingkat, maka beralih ke penggunaan fungsi VLOOKUP bisa memberikan anda alternative baru dan memberikan solusi yang tepat dan mudah karena rumus ini jauh lebih sederhana dan pendek dibandingkan menggunakan fungsi IF.

Studi kasus

Anggaplah anda ingin mengkategorikan rentang nilai 0 sd 100 menjadi 10 kelompok nilai.

• Kelompok A, nilai 90 atau lebih
• Kelompok B, nilai 80 ke atas tetapi kurang dari 90
• Kelompok C, nilai 70 ke atas tetapi kurang dari 80
• Kelompok D, nilai 60 ke atas tetapi kurang dari 70
• Kelompok E, nilai 50 ke atas tetapi kurang dari 60
• Kelompok F, nilai 40 ke atas tetapi kurang dari 50
• Kelompok G, nilai 30 ke atas tetapi kurang dari 40
• Kelompok H, nilai 20 ke atas tetapi kurang dari 30
• Kelompok I, nilai 10 ke atas tetapi kurang dari 20
• Kelompok J, nilai 0 ke atas tetapi kurang dari 10

Bayangkan jika kita lebih memilih menggunakan rumus IF untuk mengelompokan data sesuai kriteria di atas. Tentu kita harus memeras otak lebih bayak untuk menyelesaikannya. Dan jika kebetulan masih menggunakank versi excel yang lama (2013 ke sana) pengelompokan ini tidak bisa dilakukan menggunakan rumus IF, karena limitasi IF bertingkat pada versi excel tersebut.

Untuk excel versi 2017 atau yang lebih baru, fungsi IF memang bisa menjadi solusi kasus tersebut. Tetapi menurut saya hal ini kurang efisien. Jika ada rumus yang lebih pendek, kenapa harus bersusah payah membuat rumus yang panjang.

Yuk, kita perhatikan penggunaan rumus IF untuk menyelesaikan kasus ini (asumsi nilai yang dicek ada di sel A1.

=IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D",IF(A1>=50,"E",IF(A1>=40,"F",IF(A1>=30,"G",IF(A1>=20,"H",IF(A1>=10,"I","J")))))))))

Rumus yang cukup panjang bukan??

Selanjutnya mari kita lihat dan bandingkan jika kita menggunakan rumus VLOOKUP

Buat list nilai kategori nilai dalam sebuah range misalnya pada range E1:F10 seperti diperlihatkan dalam screenshot di bawah ini.

Ketikan rumus VLOOKUP berikut pada sel B1:

=VLOOKUP(A1,E1:F10,2)

Maka hasilnya akan nampak seperti gambar berikut:

Silahkan di-test dengan memasukan sebarang angka pada sel A1, maka secara otomatis sel B1 akan berisi huruf kategori nilai A s.d J tergantung pada bilangan yang kita input di sel A1.

Keunggulan menggunakan fungsi VLOOKUP untuk mengelompokan nilai adalah rumus yang sederhana dan simple. Selain itu, kita juga dapat dengan mudah merubah kriteria nilai dan kategorinya pada tabel bantu, tanpa harus merubah atau mengutak-atik rumus.

Demikian semoga bermanfaat.

Artikel terkait:

• Rumus IF