Tips dan Trik Menggunakan Spreadsheet - VLOOKUP
tpp.science.tech@intra.net - 29 Nov 2022 14:23
Spreadsheet - atau calc, atau excel; Merupakan sebuah inovasi teknologi yang berhasil meningkatkan produktivitas digital banyak manusia.
Hampir di setiap kebutuhan administratif, dari kantor kecil rumahan hingga kantor besar dengan ribuan karyawan; spreadsheet masih menjadi salah satu solusi yang tak tergantikan.
Bahkan di saat spreadsheet lahir, sempat ada kekhawatiran dari para programmer bahwa program yang mereka buat dapat digantikan dengan mudah oleh spreadsheet ini.
Kita sering kali memakai spreadsheet, bahkan mungkin setiap hari. Namun apakah kita sudah paham dan dapat menggunakan fiturnya dengan baik?
Yuk mari kita ulas sedikit tips dan trik umum yang biasa digunakan ketika menggunakan spreadsheet! Pertama-tama, formula yang umumnya sering dipakai, yaitu VLOOKUP.
Apa itu VLOOKUP
VLOOKUP merupakan sebuah formula yang bertugas untuk 'mencari data di kumpulan data, dan memberikan hasil sesuai dengan data yang ditemukan'
VLOOKUP merupakan singkatan dari Vertical Lookup, yang artinya adalah pencarian secara vertikal.
Lantas, anda pasti bertanya-tanya 'kalau ada yang vertikal, apakah ada yang horizontal?'
Jawabannya ada! Namanya HLOOKUP. Bahkan di Microsoft Excel memiliki fitur XLOOKUP, yang mencari secara horizontal maupun vertikal.
Lantas, anda pasti bertanya-tanya 'kalau ada yang vertikal, apakah ada yang horizontal?'
Jawabannya ada! Namanya HLOOKUP. Bahkan di Microsoft Excel memiliki fitur XLOOKUP, yang mencari secara horizontal maupun vertikal.
Namun secara basic, yang sering kita temui dan dipakai dibanding HLOOKUP dan XLOOKUP adalah VLOOKUP.
Dalam VLOOKUP, kita membutuhkan 4 parameter yaitu:
Dalam VLOOKUP, kita membutuhkan 4 parameter yaitu:
- Lookup_value
Kata-kata atau nilai yang hendak kita cari - Table_array
Kumpulan data yang mengandung data yang hendak kita cari - Col_index_num
Nomor baris di kumpulan data dimana nilainya yang kita inginkan untuk menjadi hasil dari VLOOKUP - Range_lookup
Perilaku VLOOKUP, apakah hendak 'sama persis' atau 'mendekati' dalam menemukan kata-kata yang hendak dicari
VLOOKUP memiliki rumus seperti berikut:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Memusingkan? Jangan takut, mari kita coba dulu. Cara termudah untuk belajar sesuatu adalah dengan contoh.
Contoh penggunaan
Contoh data
Bayangkan anda merupakan penjual buah dan memiliki tabel data buah yang anda jual seperti di atas.
Kemudian, anda hendak mencari data 'berapa harga Apel' ya? Karena kolom 'Buah' disusun secara vertikal, maka kita pakai VLOOKUP dengan rumus:
=VLOOKUP(A7,A2:C4,3,0)
A7 berarti Cell yang berisi kata-kata 'Apel', A2:C4 merupakan range dari kumpulan data yang kita miliki, dan angka 3 merupakan kolom ke 3 pada range kumpulan data A2:C4, yaitu harga.
Sedangkan angka 0 terakhir berarti FALSE, yang artinya kita mencari data yang kata-katanya 'mendekati'. Hal ini berguna apabila data yang kita cari tidak harus sama persis (misalkan tidak harus sama persis huruf besar dan huruf kecilnya).
Menggunakan VLOOKUP
Setelah menekan enter, maka harga apel langsung tertampil. Mudah bukan?
Hasil rumus VLOOKUP
Setelah mencoba contoh di atas, pasti anda berpikir: 'Kenapa tidak di ctrl+F atau dicari saja, kan lebih mudah?'
Eits, kekuatan VLOOKUP sebetulnya bukan hanya untuk mencari dan menemukan saja, namun pada kekuatan otomatisasinya.
Dengan kata lain, anda bisa membuat program simpel hanya dengan modal VLOOKUP ini saja. Yuk simak contoh implementasi di bawah.
Contoh implementasi pada form
Masih merupakan lanjutan dari contoh di atas, namun anda sebagai penjual buah memiliki lebih banyak jenis buah dan parameter seperti berikut:
Contoh data stok buah
Kemudian anda sebagai penjual buah yang melek dengan digital, berpikir:
'Apabila pembeli dapat memesan buah saya secara online, pasti mereka dan saya akan termudahkan. Pembeli dapat memesan dari rumah dengan mudah, saya dapat me-rekap pesanan user dan membeli buah di pedagang sesuai dengan jumlah yang diinginkan pembeli. Sehingga tidak over-stock atau under-stock!'
Namun tentunya membuat program 'pesan buah online' tidaklah mudah dan tidaklah murah. Anda musti berlangganan hosting, belajar cara membuat program, mengurus hosting tersebut, dan sebagainya. Bisa-bisa bukannya untung malah buntung!
Solusi apa ya yang bisa diambil dengan cepat, murah, mudah, dan simpel?
Yap, cukup bermodalkan spreadsheet anda dapat membuat sebuah 'Form' simpel yang dapat diberikan kepada pembeli untuk mereka isi. Misalnya seperti berikut:
Yap, cukup bermodalkan spreadsheet anda dapat membuat sebuah 'Form' simpel yang dapat diberikan kepada pembeli untuk mereka isi. Misalnya seperti berikut:
Contoh form pesanan buah
Anda membuat sheet baru yang berisikan form untuk diinput oleh pembeli. Namun, anda ingin agar jumlah biaya yang perlu dibayarkan oleh pembeli tertampil sehingga pembeli tahu berapa uang yang perlu mereka siapkan.
Tidak mungkin menyerahkan ke pembeli untuk menginput harga sendiri, sehingga kita musti serahkan ke sistem. Kita bisa gunakan VLOOKUP.
Pada form harga satuan, kita dapat mengisi dengan rumus VLOOKUP:
=VLOOKUP(B8,$Database.A2:H12,6,0)
VLOOKUP pada harga satuan
Untuk rumus VLOOKUP di atas jika di 'bahasa manusia' kan berarti:
'Ambil data yang diinputkan pada B8 dan cari di sheet Database A2-H16, jika ditemukan datanya, maka ambil data yang ada di kolom nomor 6'
Setelah menekan enter, maka tertampil #N/A. Lhoh kenapa? Karena kode buah di cell B8 kosong, sehingga VLOOKUP tidak menemukan datanya. Hal ini normal-normal saja.
VLOOKUP yang mencari di sumber data yang kosong akan menampilkan #N/A
Ketika kita input kode pisang kepok (yaitu PI1), maka harganya akan otomatis tertampil. Mantab bukan?
Ketika input kode buah, harga satuan langsung tertampil
Apa yang kurang? Tentu saja total harga. Terdapat 3 jenis total harga, yaitu total harga eceran, total harga paket A, total harga paket B.
Rumus dari total harga eceran adalah jumlah yang diinput pada Cell D8 dikali dengan harga satuan. Sehingga formulanya:
=D8*VLOOKUP(B8,$Database.A2:H12,6,0)
Menghitung total harga eceran
Ketika kita input misalnya angka '5' di kolom eceran, maka otomatis total harga eceran akan dihitung oleh spreadsheet.
Total harga eceran yang mengikuti harga di database & dihitung berdasarkan jumlahnya
Next! Total harga paket A dan paket B memiliki kemiripan yang sama dengan cara kerja total harga eceran.
Bedanya hanya di sheet database, harga paket A dan paket B merupakan di kolom ke 7 dan kolom ke 8. Sehingga formulanya:
Bedanya hanya di sheet database, harga paket A dan paket B merupakan di kolom ke 7 dan kolom ke 8. Sehingga formulanya:
Total Harga Paket A:
=E8*VLOOKUP(B8,$Database.A2:H12,7,0)
=E8*VLOOKUP(B8,$Database.A2:H12,7,0)
Total Harga Paket B:
=F8*VLOOKUP(B8,$Database.A2:H12,8,0)
=F8*VLOOKUP(B8,$Database.A2:H12,8,0)
Kemudian untuk Total Harga di kolom J kita dapat menggunakan SUM untuk menjumlahkan total harga eceran, paket A, dan paket B.
Sedangkan bagian total yang harus dibayar, kita dapat menggunakan SUM untuk menjumlahkan semua total harga yang ada di kolom J.
Hasil akhirnya adalah seperti ini:
Form Pemesanan Buah Simpel
Bagian terbaiknya adalah ketika anda hendak mengganti harga eceran salah satu buah, anda tinggal menggantinya di sheet database dan maka harga pada form akan mengikuti secara otomatis!
Anda dapat mendownload spreadsheet contoh di sini:
Contoh di atas merupakan salah satu saja implementasi VLOOKUP, yang sebetulnya bisa lebih diexplore dan digunakan pada data-data yang kompleks.
Tentu saja contoh di atas dapat lebih 'dipoles' kembali dengan lebih baik, namun menurut kami yang terpenting adalah para pembaca dapat mengerti secara garis besar apa itu VLOOKUP dan kegunaannya.
VLOOKUP merupakan salah satu contoh kecil saja dari sekian banyak formula yang dapat digunakan di dalam spreadsheet. Sekarang terbayang kan, mengapa spreadsheet dianggap sebagai program yang kompleks dan besar.
Kedepannya, kita akan coba membahas formula lain yang mungkin akan berguna untuk dunia pekerjaan kita semua. Apabila ada formula yang ingin kita bahas bersama, dapat anda tulis di kolom komentar di bawah.
Keep Learning!