Apakah Pencarian Gambar bisa dilakukan di excel? Pertanyaan ini sangat menarik sekali untuk dibahas. Jika pembaca mengikuti blog ini, pada pembahasan sebelumnya sudah pernah dijelaskan beberapa cara untuk melakukan pencarian data atau lookup. Namun output yang dihasilkan adalah berupa value data. Nah, dalam kesempatan ini je-xcel akan membahas bagaimana melakukan lookup menggunakan rumus INDEX MATCH untuk menghasilkan output berupa gambar.
Tonton dulu video sederhana mengenai cara Lookup Gambar pada microsoft Excel
Oke dengan melihat video di atas semoga dapat memberikan gambaran bagai mana cara mencari gambar menggunakan rumus di excel.
Bagi anda yang lebih mudah memahami dengan cara membaca dibanding menonton, silahkan lanjutkan membaca postingan ini.
Sebelum membahas bagaimana caranya, mari kita perhatikan contoh soal berikut:
Anggaplah kita sudah memiliki sejumlah foto atau gambar buah-buahan seperti terlihat dalam screenshot di atas.
Data base buah-buah tersimpan dalam sebuah tabel terdiri atas list nama buah dan fotonya (lihat kolom A dan B). Selanjutnya kita ingin menampilkan foto buah yang dicari di sel E2 berdasarkan nama buah yang diinput di sel D2. (lihat kolom D dan E).
Bagaimana caranya lookup gambar / foto dan dimana kita bisa menempatkan rumus untuk mencari gambar.
Ikuti langkah-langkah berikut.
Supaya memudahkan pembuatan rumus, disarankan untuk memberi nama range yang akan dijadikan referensi yaitu:
Range A2:A5= "listBuah"
Range B2:B5 = "listFotoBuah"
Range D2 = "namaBuah"
Pemberian nama range bisa dengan cara menyeleksi range yang akan diberi nama, misalnya A2:A5, kemudian ketikan nama range ("listBuah") pada name box yang biasanya terletak di pojok kiri, tepat di atas header kolom atau sebelah kiri kotak formula.
Untuk lebih jelasnya, perhatikan screen shot di bawah ini menggambarkan cara memberi nama pada range A2:A5 = "listBuah"
Lakukan hal yang sama pada range B2:B5("listFotoBuah") dan sel D2 ("namaBuah")
Langkah selanjutnya adalah membuat nama referensi untuk foto buah.
Melalui tab Formula, klik tombol Define Name
Selanjutnya kita akan dibawa ke jendela New Name.
Pada field Name: ketikan "fotoBuah" tanpa tanda kutip
Lalu pada field Refers To, ketikan rumus berikut:
=INDEX(listFotoBuah,MATCH(namaBuah,listBuah,0))
Selanjutnya klik OK.
Langkah terakhir adalah menerapkan defined name yang sudah kita buat pada gambar supaya bisa berubah secara otomatis sesuai kata kunci yang kita input.
Copy salah satu gamber yang ada dalam list foto buah, dan paste pada kotak sel E2. Dalam keadaan gambar hasil copy masih terseleksi, di kotak formula ketikan rumus berikut:
=fotoBuah
Setelah itu tekan Enter.
Mengenai langkah terakhir ini, lebih jelasnya dapat dilihat dalam screenshot di bawah ini.
Jika semua langkah-langkah yang sudah dipaparkan di atas sudah diikuti dengan benar, maka sampai pada tahap ini kita sudah berhasil membuat formula untuk melakukan lookup gambar.
Cobalah dengan mengganti nama buah yang ada di sel D2 dengan “Apel”,, “Durian”, “Pisang” dan “Semangka”.
Anda juga bisa menambahkan validation list di sel D2 (melalui tab Data → Data Validation validation criteria: Allow = “list” dan source “=listBuah”). Dengan cara ini kita bisa mengganti nama buah yang diinginkan dengan mudah. Cukup dengan cara cara klik nama buah yang ada di dropdown list seperti dapat dilihat dalam gambar gif berikut ini:
Apakah Kita Bisa menggunakan rumus VLOOKUP Gambar?
Dalam tehnik yang kita bahas di sini, rumus VLOOKUP tidak bisa diterapkan sebagai fungsi utama untuk lookup gambar, karena akan menyebagkan error atau gambar / foto tidak muncul sesuai yang diharapkan.
Sebagai solusinya, kita bisa menggunakan rumus INDEX MATCH untuk mencari gambar. Namun rumus INDEX MATCH ini pun tidak bisa diketikan secara langsung di kotak formula, melainkan diketiksebagai referensi pada dynamic range yang dalam contoh pembahasan ini diberi nama “fotoBuah”. Define name “namaBuah” ini yang kemudian dijadikan sebagai referensi rumus pada sebuah objek gambar.
Selanjutnya mari kita mencoba mencari tahu kenapa kita tidak bisa menggunakan VLOOKUP untuk mencari gambar (dalam metode yang dibahas dalam artikel ini)
Jawabannya: ternyata fungsi VLOOKUP memberikan hasil sebuah value secara langsung, sedangkan rumus INDEX MATCH menghasilkan sebuah referensi range (cell).
Contoh rumus dalam screenshot di bawah ini mudah-mudahan bisa menguatkan pernyataan di atas..
Dapat kita lihat dari gambar di atas. Baik rumus VLOOKUP maupun INDEXMATCH sama-sama menghasilkan data yang benarketika digunakan untuk mencari data.
Fungsi CELL digunakan untuk menguji apakah sebuah hasil rumus berupa value atau sebuah referensi range. Logikanya, sebuah range mestinya memiliki sebuah alamat atau address, sedangkan sebuah value tidak memiliki alamat.
Anda akan mendapatkan pesan error ketika mencoba menggunakan rumus CELL pada VLOOKUP, sedangkan Rumus CELL INDEX MATCH menghasilkan output sebuah text yang merupakan alamat sel. Alamat sel tersebut menunjukan posisi hasil yang diperoleh (contoh: alamat sel $B$2 mereferensikan sel yang berisi text “Foto Apel”
Kenyataan ini juga menambah list bukti keunggulan rumus INDEX MATCH dibandingkan VLOOKUP.
Sampai sini pembahasan bagaimana melakukan lookup gambar. Mudah-mudahan penjelasannya mudah difahami dan tentunya semoga bermanfaat bagi pembaca semua.Kritik dan saran dari pembaca sangat diharapkan jika ada yang salah dalam artikel ini.
Cara mudah memahami fungsi dan rumus VLOOKUP di excel adalah dengan cara mempraktekannya secara langsung untuk memecahkan kasus kasus olah data excel. Dalam postingan kali ini, saya mengajak pembaca untuk mengupas beberapa contoh penerapan fungsi VLOOKUP di dalam rumus excel.
Namun Sebelum membaca bagian ini, diharapkan anda sudah memahami dasar-dasar penggunaan fungsi VLOOKUP terlebih dahulu.
Teman-teman juga bisa merefresh kembali dasar-dasar cara menggunakan fungsi VLOOKUP dengan menyimak video ini:
Secara garis besar, bahasan belajar excel kali ini adalah:
Rumus VLOOKUP Excel dengan dua kriteria atau criteria ganda
Rumus VLOOKUP untuk Mendapatkan keberadaan nilai pertama, kedua, ketiga dan Seterusnya
VLOOKUP dua dimensi
Rumus VLOOKUP Excel dengan Kriteria Ganda
Kita sudah mengenal kecanggihan rumus VLOOKUP untuk mencari nilai tertentu dalam sebuah database dan mengexplorasi data lainnya yang terkait dengan nilai tersebut.
Namun sebagaimana kita ketahui, Fungsi ini hanya mengizinkan satu kriteria nilai yang dicari.
Sedangkan dalam prakteknya, kita sering menemukan kasus dimana ada beberapa kondisi dengan berbagai criteria yang harus dilihat dan diobservasi lebih lanjut.
Contoh VLOOKUP dengan 2 Criteria
Misalnya : Di sebuah perkebunan, kita ingin mengetahui berapa sih hasil panen dari masing – masing blok panen per tenaga kerja pemanen? .
Dengan kata lain, kita memiliki 2 kriteria yang harus diperhatikan untuk mencari nilai angka hasil panen.
Criteria yang dimaksud adalah:
Nama Blok
Nama Pemanen
Perhatikan ilustrasi berikut:
Kita sudah mengetahui bahwa VLOOKUP hanya dapat membaca satu kolom pertama untuk pencarian nilai (sebuah kriteria) dan mendapatkan nilai dari kolom lainnya dari sebuah tabel.
Sementara dalam contoh kasus ini, kita memiliki 2 kriteria.
Lantas bagaimana solusinya?
Solusinya adalah menjadikan 2 kriteria menjadi 1 kriteria dengan cara menggabungkannya.
Dalam contoh kasus ini kita perlu menggabungkan nama blok dan nama pemanen di dalam sebuah kolom bantu.
Dalam ilustrasi di atas, saya sudah mempersiapkan sebuah kolom kosong (kolom A) untuk dijadikan sebagai kolom bantu.
Pada sel A2 , Ketikan rumus berikut =C2&D2
Kemudian copy rumus dari sel A2 ke baris berikutnya sampai dengan baris terakhir tabel.
Pada sel H4, ketikan rumus berikut =VLOOKUP(H2&H3,A:E,5,0)
Hasilnya kita mendapatkan data nilai hasil panen sesuai ilustrasi berikut:
Referensi Keseluruhan Data Dalam Kolom (Entire Column)
Perhatikan kembali rumus =VLOOKUP(H2&H3,A:E,5,0)
Untuk parameter table_array, saya menggunakan referensi A:E.
Lho.. ko kenapa hanya kolom, tidak ada barisnya?.
Memang sengaja, karena pada prakteknya, penggunaan fungsi VLOOKUP sering melibatkan data yang sangat banyak, ribuan, puluhan ribu, bahkan sampai batas kemampuan jumlah baris yang disediakan oleh excel.
Jadi, A:E artinya semua data yang ada pada kolom A sampai kolom E, dari baris pertama sampai baris terakhir lembar kerja excel (entire column).
Ini penting supaya jika ada penambahan data pada baris selanjutnya, kita tidak perlu merubah referensi dalam rumus VLOOKUP.
Ada sih, cara lain untuk referensi tabel, yaitu dengan menggunakan data tabel (excel 2007 ke atas) atau data list (excel 2003). Tapi bukan disini pembahasannya supaya tidak rancu dengan istilah data table. Dan lupakan saja paragraf ini supaya tidak membingungkan ... :-)
VLOOKUP 2 Kriteria dari Sheet yang Berbeda
Jika anda sudah terbiasa dengan excel. Kemungkinan besar anda harus bekerja dengan beberapa sheet. Termasuk dalam menggunakan rumus VLOOKUP.
Jadi bagaimana caranya melakukan VLOOKUP kriteria ganda antar sheet.
Sebenarnya caranya sama saja. Hanya saja referensi tabel_array nya mengikat pada sheet tertentu.
Misalnya tabel array terletak dalam sheet bernama data, maka rumus=VLOOKUP(H2&H3,A:E,5,0) menjadi =VLOOKUP(H2&H3,data!A:E,5,0)
VLOOKUP dengan dengan 3 kriteria atau Lebih
Dengan cara yang sama kita juga bisa membuat VLOOKUP dengan lebih dari dua kriteria.
Trik nya simple saja
Buat kolom bantu pada bagian paling kiri tabel
Isi kolom bantu tersebut dengan rumus untuk menggabungkan kriteria yang masih terpisah di kolom lainnya. Bisa dengan rumus menggunakan operator & (ampersand) ataupun menggunakan fungsi CONCATENATE
Buat rumus VLOOKUP dimana parameter lookup_value merupakan penggabungan beberapa kriteria yang sama dengan penggabungan pada kolom bantu.
Jika belum faham, silahkan baca kembali ke bagian atas yaitu VLOOKUP dengan dua criteria. Karena berapapun kriteria yang digunakan, caranya sama... :-) hanya tinggal modifikasi rumus pada kolom bantu dan rumus VLOOKUP –nya.
Sekali lagi : Perlu diingat seputar penggunaan VLOOKUP dengan criteria ganda
Penggunaan rumus VLOOKUP untuk mencari dengan kriteria ganda harus melibatkan sebuah kolom bantu pada tabel_array
Kolom bantu harus terletak pada bagian kiri dari sebuah tabel, kecuali jika menggunakan rumus gabungan VLOOKUP dan CHOOSE dimana kita bisa meletakan kolom bantu di sembarang posisi. Silahkan baca kembali artikel VLOOKUP bagian pertama jika anda belum memahami dasar-dasar VLOOKUP dari kanan ke kiri.
Kolom bantu harus berisi nilai unik. Nilai unik ini berupa gabungan text kriteria yang ditetapkan
Untuk menggabungkan criteria dapat menggunakan fungsi CONCATENATE atau operator &. Silahkan dipilih mana yang lebih mudah dan nyaman bagi anda. Kalau saya sendiri biasanya menggunakan operator &
Rumus CONCATENATE dapat dituliskan dengan syntak L
=CONCATENATE(tex1, tex1, text3,...)
Menggabungkan text dengan operator & dapat dituliskan dengan syntak:
=text1&text2&text3&...
Tanda titik 3 (...), baik dalam syntax CONCATENATE maupun operator &, artinya kita dapat menambahkan text berikutnya sesuai kebutuhan.
Misalnya: jika kita menginginkan data dengan tanggal, blok dan pemanen (3 kriteria) maka kita harus membuat rumus yang dapat diilustrasikan sebagai berikut:
Rumus pada kolom bantu:
=CONCATENATE(tanggal,blok,pemanen) atau tanggal&blok&pemanen
Rumus VLOOKUP untuk Mendapatkan keberadaan nilai pertama, kedua, ketiga dan Seterusnya
Sebagaimana kita ketahui bahwa fungsi VLOOKUP akan menghasilkan nilai dari kriteria yang posisinya ditemui paling atas dalam sebuah tabel. Sehingga jika ada criteria atau nama yang sama dalam table, maka kita tidak bisa mengharapkan nilai yang lain selain yang dijumpai pertama pada table.
Namun dengan sedikit modifikasi pada kolom bantu, kita dapat menemukan nilai hasil panen pekerja panen yang sama sesuai nomor urut kemunculan nama tersebut dalam tabel.
Bagaimana caranya? Gunakan rumus COUNTIF pada kolom bantu untuk menambahkan nomor urut kemunculan pada nama pemanen.
Menggunakan tabel yang sama seperti contoh sebelumnya, Ketikan rumus pada sel A2 =D2&COUNTIF($D$2:D2;D2) , kemudian copy rumus tersebut ke baris berikutnya pada kolom bantu (A)
Kemudian modifikasi label dan data kriteria yang diketahui
Sel G2 = Nama Pemanen
Sel G3 = Nomor urut
Sel G4 = Hasil Panen
Sel H2 = Isi dengan Nama pemanen yang akan dicari hasil panennya
Sel H3 = Isi dengan Nomor urut kemunculan nama pemanen dalam tabel
Sel H4 = Tuliskan rumus =VLOOKUP(H2&H3,A:E,5,0)
Untuk lebih jelasanya, perhatikan ilustrasi berikut:
VLOOKUP Dua Dimensi
Melakukan lookup 2 dimensi artinya adalah mencari nilai tertentu dengan didasarkan pada kriteria 2 arah yang terletak pada kolom dan baris. Kata lainnya adalah mencari nilai yang merupakan pertemuan antara baris dan kolom tertentu.
Mari kita telaah lebih jauh penerapan VLOOKUP 2 dimensi ini dalam kasus handling data excel.
Misalnya kita memiliki sekumpulan data dengan struktur baris dan kolom yang menggambarkan data hasil panen per pemanen per bulan.
Nama pemanen disusun dalam struktur baris. Nama bulan disusun dalam struktur kolom.
Pertanyaanya: Bagaimana caranya untuk mendapatkan data hasil panen seorang pemanen pada bulan tertentu?
Perhatikan ilustrasi berikut.
Sebelum melakukan pencarian data menggunakan rumus, mari coba lakukan secara manual, dengan mata kepala.
Nama pemanen dan bulan sudah diketahui yaitu Ogah pada bulan April
Dengan mata kepala kita mencari hasil panen si Ogah di dalam tabel dengan menelusuri baris Ogah dan Berhenti pada kolom April
Akhirnya diketahui bahwa hasil panen si Ogah pada bulan April adalah 13.500
Bagaimana cara kerja otak kita untuk mendapatkan angka tersebut?
Caranya adalah melihat intersepsi atau garis temu antara baris nama pemanen (Ogah) dan Kolom nama bulan (Apr)
Selanjutnya mari kita cari penyelesaiannya dengan logika excel
Nama pemanen dan nama bulan yang sudah diketahui terletak pada sel E10 dan E11
Tabel terletak pada range B2:N8
Nama pemanen terletak pada bagian tabel bagian kiri, ini cocok untuk penggunaan rumus VLOOKUP
Secara umum, rumus untuk mendapatkan angka hasil panen si Ogah adalah
=VLOOKUP(E10,B2:N8,nomor_kolom,0)
Perhatikan parameter nomor_kolom. Ini menunjukan nomor urut kolom dalam tabel.
Pertanyaannya: Bagaimana caranya untuk mendapatkan nomur urut tabel yang sesuai dengan nama bulan yang ditentukan.
Ini dapat diatasi menggunakan rumus MATCH
Fungsi MATCH digunakan untuk mencari nomor urut nilai atau text tertentu baik dalam baris maupun kolom
Dalam contoh kasus ini digunakan untuk mencari nomor urut bulan tertentu dalam struktur kolom
Secara umum, rumus untuk mendapatkan nomor urut menggunakan fungsi MATCH adalah
=MATCH(text,range,lingkup)
Text dalam contoh kasus ini adalah nama bulan yang terletak dalam sel E11. Range adalah lokasi dalam struktur baris maupun kolom dimana salah satu sel dalam range tersebut berisi text yang dicari, range dimaksud dalam contoh adalah B2:N2. Lingkup tidak usah dipertanyakan saat ini, Isi saja 0 atau FALSE.
Jadi rumus untuk mencari nomor urut bulan dapat dituliskan sebagai berikut
=MATCH(E11,B2:N2,0)
Rumus tersebut dapat digabung dengan rumus VLOOKUP menggantikan parameter nomor_kolom, sehingga rumusnya menjadi:
=VLOOKUP(E10,B2:N8,MATCH(E11,B2:N2,0),0)
Sampai pada tahap ini, kita sudah berhasil melakukan VLOOKUP 2 dimensi.
Alternative lain Lookup 2 Dimensi
Selain menggunakan rumus VLOOKUP MATCH, juga ada beberapa rumus yang dapat dijadikan sebagai alternative untuk melakukan Lookup 2 dimensi.
Berikut adalah contoh-contohnya, namun hanya untuk sebagai gambaran saja bahwa “Banyak Jalan Menuju Roma”. Untuk detailnya insya Alloh akan dibahas dalam kesempatan lain.
Menggunakan fungsi SUM dalam rumus array, tekan CTR + SHIFT + ENTER setelah mengetik rumus berikut:
=SUM((B3:B8=E10)*(C2:N2=E11)*C3:N8)
Alternative Lookup 2 Dimensi menggunakan nama range dan operator spasi (operator intersepsi)
Untuk alternative cara ini saya ambil heading khusus karena ini pengetahuan baru bagi saya. Terus terang belum terfikir sebelumnya mengenai adanya operator intersepsi. Dan cara ini ternyata sangat sederhana dan mudah.
Kembali ke contoh: Seleksi tabel B2:N8
Buat nama range berdasarkan kolom dan baris
Untuk excel 2007,2010,2013 dan 2016 dengan cara masuk dalam tab Formulas, kemudian klik Create from. Berikut ilustrasinya menggunakan excel 2007
Bagi yang masih menggunakan excel 2003. Perintah Create name ini bisa diakses dari menu Insert--> Name-->Create
Setelah muncul dialog box create name, pilih Top Row dan Left Column --> kemudian tekan OK
Maka excel akan meng-create nama range berdasarkan label kolom dan baris. Bisa dicek hasilnya pada name box yang terletak pada bagian atas lembar kerja excel sebelah kiri.
Setelah itu kita dapat dengan mudah membuat rumus berdasarkan label kolom dan baris
Contohnya:
=ogah apr(hasil Ogah pada bulan Apr) =unyil mar(hasil Unyil pada bulan Mar) =kinoy sep(hasil Kinoy pada bulan Sep)
Jika menginginkan rumus yang lebih dinamis, dapat dibantu menggunakan fungsi INDIRECT, misalnya dikarenakan nama pemanen dan nama bulan yang dicari berada di sel E10 dan E11, maka rumus dapat dituliskan sebagai berikut =INDIRECT(E10) INDIRECT(E11)
Perhatikan ruang kosong (spasi) antara nama range (misal ogah apr). Tanda spasi tersebut merupakan operator excel untuk mendapatkan intersepsi atau garis temu antara duah buah range.
Demikian juga tanda spasi tersebut memiliki kegunaan yang sama jika digunakan diatara 2 fungsi INDIRECT.
Walah-walah mungkin bahasan di atas yang terakhir agak jauh dari fokus pembahasan mengenai rumus VLOOKUP yach..
Tapi tak apalah yang penting kita dapat lebih memahami berbagai alternative rumus untuk tujuan yang sama. Pada akhirnya terserah anda mau menggunakan cara yang mana.
Cukup disini dulu ya..
Belajar Excel ! Excellent
Salam...
Diakui atau tidak, masih banyak pengguna excel yang belum memahami atau bahkan belum tau apa itu rumus VLOOKUP.
Padahal fungsi excel yang satu ini sangat penting dan sangat berguna terutama jika kita bekerja dengan banyak data yang terdiri atas bayak baris.
Untuk pembaca yang sudah pakar, hal ini pasti tidak asing. Mohon dikoreksi saja jika ada yang salah dalam artikel tutorial VLOOKUP Excell ini.
Sebelum melanjutkan pembahasan mengenai fungsi VLOOKUP secara lebih terperinci. Alangkah baiknya anda untuk menyimak video sederhana ini. Video ini menunjukan mengenai bagaimana dasar-dasar cara menggunakan fungsi VLOOKUP. Simak baik-baik ya...
Nah, dari Video diatas, teman-teman bisa mendapatkan gambaran bagaimana cara menggunakan fungsi VLOOKUP, dan apa kegunaannya.
Selanjutnya kira-kira apa yang akan kita bahas perihal rumus VLOOKUP secara lebih terperinci?
•Apa itu Rumus VLOOKUP
•Rumus VLOOKUP antar Sheet
•Rumus VLOOKUP antar Workbook atau File Excel Yang Berbeda
•Referensi Nama Range Cell dalam Rumus VLOOKUP
•Rumus VLOOKUP untuk Klasifikasi Grade Nilai
•Bisakah Rumus VLOOKUP Bekerja dari Kanan Ke Kiri?
......
Berikut ilustrasi contoh kasus dimana fungsi VLOOKUP diperlukan.
Misalnya: ada data nama peserta sebuah kegiatan dan disimpan dalam sebuah tabel terdiri atas 4 kolom yaitu Nomor, Nama, Jenis Kelamin dan Alamat.
Kemudian seseorang meminta anda untuk menemukan data yang terkait dengan nomor urut tertentu.
Bagaimana caranya supaya untuk memperoleh data dimaksud, secara cepat menggunakan rumus.
Jawabnya : ya pakelah rumus VLOOKUP, bukan diPELUK yach ...:-)
Apa Rumus VLOOKUP itu ?
VLOOKUP merupakan akronim dari Vertikal Lookup, atau secara harfiah lookup dapat diartikan sebagai melihat dengan seksama. Jadi fungsi VLOOKUP digunakan untuk melihat dengan seksama tabel secara vertikal (dari atas ke-bawah). Adapun kegunaannya adalah untuk mendapatkan nilai dari kolom lain yang terkait dengan data yang sudah diketahui dalam baris yang sama.
Berikut syntax atau cara penulisan fungsi VLOOKUP dalam lembar kerja m s excel
Ups.. agak beratakan terjemahan bahasa indonesia nya. Tapi tak apalah, yang penting saya dan anda dapat dapat memahaminya.
Nilai_dilihat adalah data berupa angka maupun text yang akan dicari
Tabel_dilihat adalah tabel melihat nilai_dilihat dimana posisinya pada kolom pertama (paling kiri) dari tabel tersebut
Nomor_kolom adalah nomor urut kolom dalam tabel_dilihat untuk mendapatkan data tertentu yang terkait dengan nilai_dilihat
Lingkup_lihat adalah cara pencarian apakah menginginkan data yang persis sama atau pendekatan. Parameter ini bersifat opsional tetapi sangat penting. Nilai 0 atau FALSE jika menginginkan hasil yang persis sama atau nilai 1 atau TRUE jika menginginkan data pendekatan. Secara default jika parameter ini diabaikan maka akan menggunakan nilai 1 atau TRUE. Dalam prakteknya mungkin kita akan lebih sering menggunakan 0 atau FALSE. Oleh karena itu parameter ini wajib diisi meskipun bersifat opsional.
Kembali ke contoh kasus:
Bagaimana cara mendapatkan data Nama, Jenis Kelamin dan Alamat dari tabel data peserta dengan cepat menggunakan rumus?
Berikut ilustrasi jawabannya:
Belum Faham ?
Kira-kira beginilah penjelasannya dengan analogi:
Lihat Seksama Dengan Mata Kepala
Lihat Seksama Dengan Rumus VLOOKUP
Kita ingin melihat Nama Peserta nomor 10
Kita ingin melihat Nama Peserta nomor 10 mengunakan bantuan rumus excel VLOOKUP
Lihat dengan seksama dari atas ke bawah pada kolom Nomor sampai dijumpai 10
Rumus mengecek satu persatu dari atas kebawah kolom nomor (paling kiri) tabel A7:D22 sampai dijumpai 10
Setelah nomor 10 dijumpai, Mata kita bergeser ke sebelah kanannya, pada kolom berikutnya yaitu kolom ke-2 yang berisi Nama
Begitu 10 dijumpai, excel mengecek pada kolom urutan ke-2 sesuai rumus =VLOOKUP(10,A7:D22,2,0)
Berteriaklah : Ya Aku Tahu Kau Nomor 10, Namamu Wati kan?
Excel memberitahu anda: nomor urut 10 kolom ke-2 adalah si Wati
Anda Penasaran Si Wati itu laki-laki atau perempuan. Lihat kolom sebelahnya lagi, kolom ke-3
Modifikasi rumus untuk mendapatkan data jenis kelamin yang terletak pada kolom ke-3 dari tabel
=VLOOKUP(10,A7:D22,3,0)
Anda Tersenyum : Oh si Wati itu perempuan toh
Excel memberitahu anda: Nomor urut 10 , kolom ke-3 adalah perempuan
Anda masih penasaran : Si Wati itu orang mana ya? Sambil melihat kolom sebelahnya lagi, kolom ke-4
Modifikasi rumus untuk mendapatkan data Alamat yang terletak pada kolom ke-4 dari tabel
=VLOOKUP(10,A7:D22,4,0)
Anda tersenyum lebar sambil berfikir dan membayangkan dalam peta. Cirebon itu dimana ya
Excel memberitahu anda: Nomor urut 10 , kolom ke-4 adalah Cirebon
Selanjutnya: Terserah anda :-)
Selanjutnya : Terserah anda juga.:-)
Karena kita ingin mencari data secara cepat pada berbagai nomor urut, maka angka 10 itu terletak dalam sebuah cell. Misal cell C1, maka rumusnya menjadi sebagai berikut:
Nama , =VLOOKUP(C1,A7:D22,2,0)
Jenis Kelamin, =VLOOKUP(C1,A7:D22,3,0)
Alamat, =VLOOKUP(C1,A7:D22,4,0)
Cobalah: ganti angka nomor urut di sel C1, maka kita akan dapati data terkait nomor urut tersebut secara otomatis.
Sampai disini seharusnya cara kerja rumus VLOOKUP sudah dapat difahami.
Rumus VLOOKUP Beda Sheet
Pada contoh diatas, kita menggunakan rumus vlookup untuk melihat seksama nilai tertentu pada tabel yang berada pada sheet yang sama dengan rumus dibuat.
Bagaimana jika tabel terletak pada sheet yang beda, misalnya pada sheet dengan nama “Data”, pada range A7:D22
Bagaimana rumus atau formulanya?
Tidak Sulit, cukup ketikan rumus dengan referensi tabel_dilihat pada sheet data
•Nama =VLOOKUP(C1,DATA!A7:D22,2,0)
•Jenis Kelamin =VLOOKUP(C1,DATA!A7:D22,2,0)
•Alamat =VLOOKUP(C1,DATA!A7:D22,2,0)
Perhatikan formula di atas, untuk parameter nilai_dilihat dan tabel_dilihat mengacu pada referensi. Sebenarnya tidak perlu mengetikan langsung, cukup pilih referensi yang sesuai menggunakan mouse.
Contoh cara mengetik rumus VLOOKUP antar sheet.
•Ketik =VLOOKUP(
•Pilih referensi nilai_dilihat menggunakan mouse yaitu sel C1
•Ketik koma (,)
•Masuk atau Pilih sheet “DATA”
•Pilih tabel pada range A7:D22 di dalam sheet “DATA”
•Ketik koma (,)
•ketik parameter nomor_kolom , misal 2 (kolom ke-2)
•Ketik koma (,)
•Ketik parameter lingkup_lihat, misalnya angka 0 atau False
•Ketik tutup kurung
Oh iya ... Saya lupa, anda sebenarnya sudah paham untuk hal remeh cara mengetik rumus seperti ini. Tapi tak apalah, biar artikel ini agak panjang sikit. :-)
Rumus VLOOKUP antar Workbook Atau File Excel Yang Berbeda
Bagaimana jika tabel data terletak di workbook atau file excel lainnya? Bagaimana rumus atau formula-nya?
Untuk yang ini juga tidak terlalu sulit. Yakin ! apalagi kalau sudah terbiasa dengan excel dan sering menggunakan formula. Hampir sama dengan pembuatan rumus VLOOKUP antar sheet.
Cukup ketikan =VLOOKUP( , kemudian pilih referensi nya menggunakan mouse, untuk parameter nilai_dilihat dan tabel_lihat.
Misalnya tabel peserta terletak dalam sebuah file excel bernama peserta.xlsx. Maka rumus vlookup menjadi:
Perhatikan penggunaan tanda $. Biasanya jika kita mengetikan formula dengan referensi dari workbook atau file lain maka otomatis referensi menggunakan atribut $ atau absolute. Artinya range referensi dikunci dan akan tetap meskipun rumus di copy ke sel lain.
Referensi Nama Range Cell dalam Rumus VLOOKUP
Cara lain yang lebih simple dan lebih mudah dibaca (readable) ialah penggunakan nama range sebagai referensi. Tata cara penamaan range cell sudah dibahas dalam artikel lainnya, silahkan dibaca Memberikan Nama Cell dan Range Menggunakan Name Box
Atau kalau anda tidak mau repot membuka artikel lain, ikuti saja langkah berikut:
Membuat nama range cell adalah dengan cara menyeleksi range cell (misal tabel) kemudian ketikan nama range pada name box yang biasanya terletak pada kiri atas lembar kerja excel (disebelah kiri tanda fx). Misalnya nama range tabel data peserta kita namakan dataPeserta.
Lalu apa kaitan nama range sel dengan rumus vlookup?
Menggunakan nama sel atau nama range dalam rumus vlookup menjadikan rumus lebih readable, atau lebih mudah dibaca dan dimengerti alurnya.
Penggunaan nama range cell juga menjadikan referensi yang sama. Tidak peduli lokasi tabel baik dalam sheet yang sama atau tidak, tetapi masih dalam satu file.
Misalnya:
Dengan menamai tabel data peserta dengan nama dataPeserta, maka rumus vlookup dalam satu sheet maupun antar sheet menjadi sama yaitu:
Nama=VLOOKUP(C1,dataPeserta,2,0)
Jenis kelamin=VLOOKUP(C1,dataPeserta,3,0)
Alamat=VLOOKUP(C1,dataPeserta,4,0)
Secara analogi, dapat dijelaskan bahwa rumus VLOOKUP diatas adalah mencari data sesuai nilai pada C1. Pencarian dilakukan pada kolom pertama dari tabel bernama dataPeserta. Selanjutnya mengembalikan nilai dari kolom ke 2 (nama), 3 (Jenis Kelamin) dan 4 (Alamat) pada baris yang sama dengan nilai ditemukan.
Rumus VLOOKUP untuk Klasifikasi Grade Nilai
Kebanyakan pengguna excel sudah familiar dengan cara melakukan klasifikasi data menggunakan fungsi IF. Namun kalau kita membuat rumus IF bertingkat, kita akan memerlukan rumus IF yang semakin panjang dengan semakin banyaknya kriteria yang digunakan.
Semakin panjang rumus, tentunya semakin sulit untuk mengikuti alur logikanya. Dalam kata lain, penggunaan rumus IF dengan kriteria yang banyak menjadikan rumus tidak readable.
Contoh Kasus:
Dalam mengklasifikasikan grade nilai ujian mahasiswa di sebuah perguruan tinggi, ditentukan kriteria sebagai berikut:
Nilai
Grade
< 20
E
20-29
E+
30-39
D
40-49
D+
50-59
C
60-69
C+
70-79
B
80-89
B+
90-100
A
Bagaimana cara merumuskan grade nilai ujian mahasiswa tersebut?
Bagi anda yang maniak dengan fungsi IF mungkin akan langsung mengatakan: Wah ini harus menggunakan rumus IF nich...!
Perhatikan bahwa untuk klasifikasi nilai menjadi 9 grade (E , E+ , D , D+ , C , C+ , B , B+ , A ), kita memerlukan fungsi IF bertingkat 8.
Rumus di atas tidak dapat digunakan pada excel 2003 karena excel versi tersebut hanya mengizinkan fungsi IF bertingkat sampai level 7.
Bahkan, meskipun anda sudah menggunakan versi excel 2007, 2010, 2013 atau 2016, penggunaan rumus tersebut tetap saja menurut saya kurang praktis.
Kenapa?
Karena rumus terlalu panjang. Rumus ini juga kurang fleksibel, misalnya jika ada perubahan dari kriteria grade, maka kita harus melakukan edit kembali pada rumus IF.
Apa Solusinya?
Gunakan rumus VLOOKUP
Dalam hal ini menggunakan VLOOKUP TRUE, artinya nilai parameter range_lookup atau lingkup_lihat harus bernilai TRUE atau 1.
Caranya:
Buat tabel bantu nilai dan grade,
Kolom pertama berisi nilai minimum masing-masing grade dan harus berurut dari terkecil ke tertinggi.
Kolom kedua berisi grade
Perhatikan range berwarna kuning (C3:D11) dalam ilustrasi berikut adalah referensi kriteria grade nilai mahasiswa yang harus dimasukan kedalam rumus
Perhatikan sel D15 pada ilustrasi. Rumus pada sel tersebut adalah:
=VLOOKUP(C15,$C$3:$D$11,2)
Atau
=VLOOKUP(C15,$C$3:$D$11,2,1)
Atau
=VLOOKUP(C15,$C$3:$D$11,2,TRUE)
Terbukti..! Rumus VLOOKUP sangat jauh lebih pendek dibandingkan menggunakan rumus IF.
Kita juga dapat memberi nama pada referensi tabel kriteria nilai grade mahasiswa sehingga rumus menjadi lebih readable. Misalnya tabel kriteria kita beri nama kriteriaGrade, maka rumus tersebut menjadi:
=VLOOKUP(C15,kriteriaGrade,2)
atau
=VLOOKUP(C15,kriteriaGrade,2,1)
atau
=VLOOKUP(C15,kriteriaGrade,2,TRUE)
Anda perhatikan bahwa rumus VLOOKUP ini sangat simple. Rumus inipun tidak akan bertambah panjang dengan bertambahnya kriteria yang digunakan. Hal ini karena kriteria terletak dalam tabel lain yang dijadikan referensi tabel_dilihat. Hal ini sangat berbeda dengan rumus IF yang akan bertambah panjang dengan bertambahnya kriteria.
Namun pada akhirnya terserah anda untuk menggunakan rumus yang mana. Jika lebih nyaman dengan rumus IF, silahkan saja dengan resiko harus menulis formula yang panjang tentunya.
Kalau saya sich, lebih suka yang simple dan fleksible. Maka saya memilih VLOOKUP.
Bisakah Rumus VLOOKUP bekerja dari Kanan Ke Kiri.
Pertanyaan ini sering muncul diantara pengguna excel dan acapkali dilontarkan dalam forum-forum diskusi. Para pakar excel dalam grup biasanya langsung menyarankan untuk menggunakan fungsi INDEX MATCH sebagai alternative pengganti VLOOKUP.
Rumus INDEX MATCH memang banyak memiliki keunggulan dibanding VLOOKUP, Namun, karena kita sedang membahas VLOOKUP, maka solusi yang akan ditawarkan adalah tetap menggunakan rumus VLOOKUP.
Lalu bagaimana caranya? karena VLOOKUP hanya dapat bekerja mulai dari kolom pertama (kolom paling kiri) sebuah tabel untuk mencari sebuah nilai.
Tenang...
Ternyata nomor urut kolom dalam sebuah tabel dapat kita manipulasi.
Caranya? Gunakan fungsi CHOOSE
Contoh kasus:
Dalam data mahasiswa seperti contoh ilustrasi sebelumnya dari kolom pertama sampai kolom ke-4 yaitu NIM, Nama Mahasiswa, Nilai, Grade.
Seandainya kita mengetahui nama mahasiswa namun belum tahu NIM Nya. Bagai mana cara mendapatkan NIM dengan rumus? padahal kolom NIM berada disebelah kiri Kolom Nama.
Disinilah kita harus memerintahkan VLOOKUP supaya dapat bekerja dari kanan ke kiri.
Perhatikan terutama rumus VLOOKUP untuk mendapatkan Nomor Induk Mahasiswa (NIM) yaitu:
=VLOOKUP(B29,CHOOSE({1,2},B15:B25,A15:A25),2,0)
Data yang sudah diketahui yaitu nama mahasiswa terletak kolom kedua pada tabel. Namun dengan menggunakan bantuan fungsi CHOOSE, kita dapat menjadikan kolom tersebut seakan-akan sebagai kolom pertama.
Perhatikan contoh rumus yang dengan font warna biru dan merah. Fungsi CHOOSE mendefinisikan kolom ke-1 adalah range B15:B25 yang berisi nama mahasiswa. Sedangkan kolom ke-2 adalah range A15:A25 yang berisi data NIM
Penting dalam penggunaan fungsi CHOOSE
Perhatikan angka dalam tanda kurung kurawal {} yaitu 1 dan 2 yang dipisahkan tanda koma ( , ). Jika seting komputer anda menggunakan tanda koma sebagai pemisah desimal, tentunya rumus tersebut akan menghasilkan error.
Pada kasus demikian maka tanda koma dalam kurung kurawal harus diganti tanda back slash ( \ ) dan koma pemisah parameter rumus diganti dengan tanda semi colon ( ; )
Sehingga rumusnya menjadi sebagai berikut:
=VLOOKUP(B29;CHOOSE({1\2};B15:B25;A15:A25);2;0)
Sampai pada tahap ini dapat disimpulkan bahwa dalam fungsi VLOOKUP excel dapat bekerja dari kanan ke kiri. Tentunya dengan bantuan fungsi CHOOSE.
...................
Sebagai penutup artikel ini. Perlu ditekankan kembali bahwa VLOOKUP merupakan salah satu fungsi Excel yang sangat disarankan untuk dikuasai. Penulis sendiri sangat merasakan manfaat dari rumus ini terutama pada saat bekerja dengan banyak data, apakah itu untuk pengelompokan data, mencari padanan atau data terkait, mencari nama lain dari sebuah data dan sebagainya.
"Penggunaan Rumus VLOOKUP Terbukti Dapat Menghemat Waktu"
Untuk itu, jika pembaca baru mengenal rumus ini, penulis sangat menyarankan untuk mempelajarainya lebih lanjut.
