Cara VLOOKUP Gambar dan Foto di Excel

Bagaimana cara VLOOKUP gambar atau Foto Di Excel? Artikel ini akan membahas bagaimana menggunakan fungi VLOOKUP supaya bisa digunakan untuk lookup Gambar. Oh iya, catatan ini melengkapi artikel sebelumnya mengenai cara Lookup Gambar menggunakan fungsi INDEX MATCH.

Note: Video Tutorial ada pada bagian bawah catatan pelajasan excel ini

Dalam artikel Lookup Gambar dengan INDEX MATCH sudah dijelaskan bahwa sebuah objek gambar harus link ke referensi range supaya bisa menangkap visual range tersebut dalam bentuk picture link. Sedangkan output dari rumus VLOOKUP adalah berupa value, bukan referensi. Oleh karena itu, maka fungsi VLOOKUP tidak bisa berdiri sendiri jika hendak digunakan dalam lookup Gambar.



Sebagai solusinya, maka kita bisa menggabungkan fungsi VLOOKUP dengan fungsi INDEX. Dalam rumus gabungan ini, fungsi VLOOKUP berperan untuk mencari nomor index baris. Kemudian tugas selanjutnya diserahkan ke fungsi INDEX. Mengapa fungsi INDEX? Karena fungsi index menghasilkan output berupa referensi cell.

Untuk lebih mudah memahami bagaimana cara kerja rumus VLOOKUP untuk gambar, mari kita simak penjelasan tips excel dan contoh berikut:

Anggaplah kita sudah memiliki sejumlah foto atau gambar jenis-jenis  hewan seperti terlihat dalam screenshot di atas.

Database binatang tersimpan dalam sebuah tabel terdiri atas list hewan/binatang lengkap dengan nomor urutnya serta foto binatang terkait (lihat kolom A s/d C). Selanjutnya kita ingin menampilkan foto binatang yang dicari di sel F2 berdasarkan nama binatang yang diinput di sel E2. (lihat kolom E dan F).

Bagaimana caranya menggunakan fungsi VLOOKUP untuk  gambar / foto dan dimana kita bisa menempatkan rumus untuk mencari Photo.

Ikuti langkah-langkah berikut.

Supaya memudahkan dalam pembuatan dan pembacaan rumus, disarankan untuk memberi nama range yang akan dijadikan referensi yaitu:

• Range A2:B5  = "listBinatang"
• Range C2:C5 = "listFotoBinatang"
• Range E2 = "namaBinatang"

Pemberian nama range bisa dilakukan dengan cara mem-block range yang akan diberi nama, misalnya A2:B5, kemudian nama range ("listBinatang") diketik pada name box yang posisinya terletak di sebelah kiri kotak formula, biasanya tepat di atas header kolom A.

Screenshot di bawah ini memperlihatkan bagaimana cara memberi nama pada range A2:B5 = "listBinatang"

Kemudian lakukan cara yang yang sama untuk memberi nama range C2:C5  ("listFotoBinatang") dan sel E2 ("namaBinatang")

Mengenai pemberian nama range di excel, juga dapat dibaca  pada artikel Cara Memberi Nama Range Menggunakan Name Box.

Langkah berikutnya adalah membuat nama referensi untuk foto binatang yang nantinya akan dijadikan sebagai referensi dalam picture link.

Melalui tab Formula  → klik tombol Define Name

Maka kemudian kita akan dibawa ke jendela New Name.

Pada field Name: ketikan "fotoBinatang", tentu saja tanpa tanda kutip
Setelah itu, pada field Refers To, copy paste atau ketik rumus di bawah ini :

=INDEX(listFotoBinatang,VLOOKUP(namaBinatang,listBinatang,2,0))

Selanjutnya klik OK.

Sekarang, saatnya kita menerapkan defined name yang sudah dibuat sebagai sebagai referensi picture link pada sebuah objek gambar sehingga objek gambar tersebut bisa memperlihatkan foto binatang tertentu.

Caranya: Buat obyek gambar baru dengan cara meng-copy salah satu gambar yang ada dalam list foto binatang (misalnya foto singa) dan paste pada kotak sel F2. Dalam keadaan gambar hasil copy masih terseleksi, di kotak formula ketikan rumus berikut:

=fotoBinatang

Setelah itu tekan Enter.

Mengenai langkah terakhir ini, lebih jelasnya dapat dilihat dalam screenshot di bawah ini.

Jika semua langkah-langkah yang sudah diuraikan di atas sudah diikuti dengan benar, maka sampai pada tahap ini kita sudah berhasil membuat bagaimana menggunakan fungsi VLOOKUP untuk gambar.

Silahkan diuji hasil latihan kita dengan cara menggonta-ganti nama binatang yang ada di sel E2 dengan "Singa", "Gajah", "Kuda" dan "Harimau".

Kita juga bisa mengganti nama binatang yang diinginkan dengan mudah dan cepat melalui dropdown list jika menambahkan validation list pada sel E2 ("namaBinatang"). Caranya: melalui tab Data → klik Data Validation → masuk ke jendela data validation,  criteria: Allow = "list" dan source = "=listBinatang".

Maka hasilnya dapat dilihat dalam gambar gif di bawah ini.

Penjelasan cara kerja rumus VLOOKUP untuk Gambar?

Fungsi VLOOKUP dalam pencarian gambar sebenarnya hanya berperan sebagai fungsi pembantu saja, sedangkan fungsi utamanya adalah fungsi INDEX. Fungsi VLOOKUP digunakan untuk mencari nomor index gambar yang dicari. Selanjutnya nomor index gambar digunakan oleh fungsi INDEX untuk mendapatkan alamat referensi range foto / gambar yang dicari.

Oleh karena itu. Pada tabel binatang dan fotonya, pada kolom ke-2 ditampilkan nomor urut binatang. Nomor tersebut harus di susun berurutan dari mulai angka1 sebagai index pertama.

Mari kita lihat kembali rumus yang dijadikan referensi dalam defined name fotoBinatang.

=INDEX(listFotoBinatang,VLOOKUP(namaBinatang,listBinatang,2,0))

Anggaplah kita ingin mencari tahu foto Harimau itu seperti apa sich?

Di sel E2 (nama range = "namaBinatang") ketik "harimau" tanpa tanda kutip

Maka rumus VLOOKUP akan mecari harimau pada kolom ke-1 dalam range A2:B5 (nama range = "listBinatang") dan memberikan output value = 4, yaitu nilai yang terletak pada kolom ke-2 dalam range "listBinatang" yang sejajar dengan value "harimau" di kolom pertama.

Value 4 yang merupakan output dari rumus VLOOKUP, kemudian dijadikan sebagai index row dalam fungsi INDEX, sehingga rumus dapat dituliskan sebagai berikut:

fotoBinatang =INDEX(listFotoBinatang,4)

Rumus INDEX tersebut akan menghasilkan output berupa referensi baris ke-4 dalam range C2:C5 (nama range = “listFotoBinatang”). Dan referensi dimaksud adalah sel C5 yang berisi foto harimau. Sehingga munculah Foto Harimau pada obyek gambar yang menggunakan referensi fotoBinatang.

Pada contoh yang sudah dibahas di atas, kita menggunakan contoh foto binatang sebabah bahan latihan. Dalam pekerjaan sehari-hari mungkin kita bisa menerapkannya untuk VLOOKUP Foto orang, misalnya untuk VLOOKUP Foto Mahasiswa / Pelajar, VLOOKUP Foto Anggota Organisasi dan VLOOKUP Foto Karyawan. Anda juga bisa mencobanya untuk VLOOKUP Foto barang, dan lain sebagainya.

Sampai disini mudah-mudahan pembahasan bagaimana menggunakan rumus VLOOKUP untuk gambar dapat mudah difahami, dan tentu harapannya semoga bermanfaat bagi pembaca semua.  Namun tentu saja penulis tidak luput dari kesalahan. Kritik dan saran dari pembaca sangat diharapkan jika ada menemukan kesalahan baik dalam rumus maupun penjelasan yang kurang tepat dalam artikel ini.

Video:

Terimakasih, dan Salam Sukses untuk semua.

Artikel Terkait:

• Lookup Gambar Dengan Fungsi INDEX MATCH
• VLOOKUP : Rumus Jitu Yang Wajib Dikuasi
• Memahami Rumus VLOOKUP Dengan Studi Kasus
• Menghapus Semua Gambar Sekaligus pada Excel

Membuat Daftar Isi Otomatis

Halo teman, jumpa lagi dengan je-xcel. Setelah sekian lama vakum menulis karena kesibukan kerja. Alhamdulillah, kali ini saya bisa kembali sedikit berbagi  tips excel.

Dalam kesempatan ini kita akan membahas bagaimana membuat daftar isi atau index worksheet secara otomatis.

Dengan semakin banyaknya lembar kerja atau worksheet pada sebuah file excel, maka mungkin kita akan merasa kesulitan untuk navigasi antar sheet. Nah, untuk itu diperlukan sebuah alat bantu sebuah worksheet berisi list index beserta hyperlinknya yang dapat di-generate secara otomatis.

Membahas mengenai otomatisasi di excel, maka tentunya tidak bisa lepas dari yang namanya VBA. Nah, dalam hal ini kita akan gunakan kode VBA untuk meng-generate list index dan hyperlinknya.

Anggaplah kita memiliki sebuah file excel yang terdiri atas beberapa worksheet berisi data. Kemudian ada sebuah worksheet berisi index data atau daftar isi. Atau lebih jelasnya dapat dilihat dalam screenshot di bawah ini.

Tugas selanjutnya adalah bagaimana meng-generate daftar isi worksheet index dan membuat hyperlink ke data terkait, serta membuat hyperlink balik dari worksheet data menuju sheet index.

Adapun caranya sangat mudah, cukup ikuti langkah sederhana berikut ini:

1. Pastikan tab developer pada aplikasi excel anda sudah aktif (excel 2007 atau yang lebih baru)  dan pastikan setting macro security sudah enable.
2. Klik kanan pada tab sheet index, kemudian klik view code.
   
   
   
3. Selanjutnya kita akan dibawa ke jendela VBA seperti terlihat pada gambar berikut:
   
   
   
4. Copy code berikut ke dalam private modul sheet1(index)
   
   Private Sub Worksheet_Activate()
   Dim ws As Worksheet, index As Integer
   Application.ScreenUpdating = False
   Me.Cells.Clear
   Me.Cells(1, 1).Name = "Index"
   Me.Cells(1, 1).Value = "Index"
   Me.Cells(1, 2).Value = "Keterangan"
   For Each ws In ThisWorkbook.Worksheets
     If ws.Name <> Me.Name Then
       index = index + 1
       Me.Cells(index + 1, 1).Value = index
       ws.Cells(1, 1).Name = "index" & index
       ws.Cells(1, 1).Value = "<< index"
       Me.Hyperlinks.Add Me.Cells(index + 1, 2), "", "index" & index, "Lihat Data", ws.Name
       ws.Hyperlinks.Add ws.Cells(1, 1), "", "index", "Lihat index", "<< Index"
     End If
   Next
   Application.ScreenUpdating = True
   End Sub
   
   
   
   
   
   
   
   
5. Kemudian close jendela VBA, selanjutnya kembali ke spreadsheet excel.
6. Sampai dengan tahap ini, code VBA sudah dapat digunakan untuk meng-generate list index, membuat hyperlink ke sheet target serta membuat link back dari sheet data ke sheet index.

Untuk membuktikan bahwa code VBA dapat bekerja dengan baik, silahkan dicoba cara kerjanya dan lihat hasilnya dengan cara berpindah ke sheet lain selain sheet index, kemudian kembali ke sheet index. Maka secara otomatis pada sheet index akan di-generate daftar isi berupa nomor index dilengkapi keterangannya sesuai nama-nama sheet yang ada dalam workbook excel. Selain itu pada masing-masing keterangan, sudah dilengkapi hyperlink yang mengarah pada worksheet terkait.

Jika perlu menambahkan sheet baru, atau merubah nama sheet data, maka tidak perlu report untuk mengedit list index, karena code VBA akan menyelesaikan tugas ini secara otomatis setiap kali kita mengaktifkan sheet index.

Silahkan dicoba kembali dengan cara menambahkan sheet baru misalnya nama sheetnya “data baru”. Setelah itu, kemudian kembali masuk ke sheet index. Maka kita akan mendapati sheet baru secara  otomatis terdaftar dalam index.

Selain itu pada sel A1 dari setiap sheet data akan tercipta secara otomatis hyperlink yang mengarah ke sheet index.

List index, hyperlink ke sheet data, serta hyperlink balik ke sheet index akan disegarkan secara otomatis setiap kali user masuk atau mengaktifkan sheet index. Disinilah letak keuntungannya sehingga user tidak perlu capek membuat hyperlink secara manual setiap kali ada perubahan pada nama sheet ataupun penambahan sheet baru.

Setelah selesai, maka file excel latihan ini dapat di simpan. Jika menggunakan excel 2007 atau yang lebih baru, jangan lupa untuk save as sebagai  excel macro – enable workbook atau excel binary workbook. Jika tidak, maka code macro akan terhapus dan tidak dapat digunakan.

Catatan: jika code yang dicontohkan dalam tutorial ini tidak bekerja sesuai harapan, maka kemungkinan setting macro security pada aplikasi microsoft excel yang anda gunakan belum pas, sehingga macro tidak diizinkan untuk dijalankan. Silahkan periksa kembali setting macro security nya.

Demikian tips singkat mengenai bagaimana membuat list index atau daftar isi secara otomatis menggunakan VBA pada microsoft excel. Semoga bermanfaat.

Silahkan dibaca juga tips excel lainnya:

• Macro untuk menghapus hyperlink tanpa merubah format
• Cara cepat membuat ribuan hyperlink sekaligus
• Cara cepat isi sel kosong
• Mengatur target hyperlink pada posisi yang tepat.

Penggunaan Rumus OFFSET untuk Transpose Data Excel

Cara ke-4 untuk melakuan transpose / merubah orientasi kolom dan baris data excel adalah menggunakan rumus kombinasi fungsi OFFSET serta ROW dan COLUMN. Fungsi OFFSET ini berguna untuk mendapatkan nilai berdasarkan posisi relative sebuah range terhadap range acuan.

Adapun syntax dari fungsi OFFSET adalah sebagai berikut:

  =OFFSET(reference, rows, cols, [height], [width])

Tulisan ini tidak akan menjelaskan secara detail fungsi OFFSET dan parameter fungsinya, namun hanya akan menjelaskan kaitan fungsi OFFSET dengan proses transpose data. Untuk transpose data, parameter fungsi OFFSET yang diperlukan adalah reference, rows dan cols.





Reference = range acuan, bisa berupa sebuah sel atau beberapa sel. Dalam kasus transpose data, maka range acuan yang diperlukan adalah sebuah sel.
Rows=posisi relatif sel yang dicari nilainya terhadap sel acuan
Cols = posisi relatif sel yang dicari nilainya terhadap sel acuan
Selanjutnya mari kita praktekan kembali pada lembar kerja excel menggunakan tabel data asli seperti cara pertama.

• Menggunakan contoh kasus seperti pembahasan Transpose bagian pertama (Copy Paste Transpose), Bagian Kedua (TRANSPOSE) dan bagian ketiga (INDEX), Pada bagian ke-4 ini menggunakan fungsi OFFSET, Masukan rumus pada sel D1 sebagai berikut: 

=OFFSET($A$1,COLUMN(A1)-COLUMN($A$1),ROW(A1)-ROW($A$1))

• Copy sel D1 tersebut ke range D1:I12 Sehingga mendapatkan data transpose pada range D1:I2.
• Jika anda menginginkan format data transpose identik dengan format data asli, lakukan copy paste special format transpose dari data asli ke range D1:I10 dengan cara sebagai berikut: seleksi range data asli --> CTR+C  --> klik kanan cel D1 --> paste special --> pada dialog box tick formats dan ticks transpose --> Ok

• Dengan memperhatikan formula di atas, prinsip kerja formula OFFSET hampir sama dengan INDEX yaitu membalik parameter kolom dengan baris.

Demikian semoga bermanfaat

Belajar Excel..! Excellent

Artikel Terkait

5 Cara Transpose Data Excel

Penggunaan Formula Array TRANSPOSE

Transpose Data Menggunakan Rumus INDEX

Penggunaan Rumus INDEX untuk Transpose Data Excel

Pada postingan sebelumnya kita sudah belajar bagaimana melakukan transpose data excel menggunakan cara copy paste special transpose dan menggunakan rumus array transpose. Pada kesempatan ini mari kita lanjutkan pada pembahasan cara ke-3 yaitu transpose data excel menggunakan rumus yang sedikit lebih panjang, yaitu menggunakan rumus INDEX dikombinasikan
Fungsi INDEX berguna untuk mendapatkan nilai dari sel berdasarkan posisi sel tersebut dalam sebuah range referensi.

Adapun syntax dari fungsi INDEX adalah :  =INDEX(reference, row_num, [column_num],[area_num])

• Reference =range sebagai referensi, misalnya sebuah tabel yang kita gunakan sebagai data asli sebelum di-transpose
• Row_num = posisi nomor baris dalam referensi
• Column_num = posisi nomor kolom dalam referensi, bersifat opsional namun sangat penting dalam kaitan transpose data tabel
• Area_num = jumlah area dalam referensi, bersifat opsional dan jarang digunakan

Untuk mendapatkan nilai pada baris ke-1 dan kolom ke-2 pada sebuah referensi, kita dapat menuliskan rumus sbb:  =INDEX(referensi,1,2).

Sebaliknya, untuk mendapatkan nilai pada baris ke-2 dan kolom ke-1 kita dapat menuliskan rumus sbb:  =INDEX(referensi,2,1).

 Perhatikan penggunaan parameter row_num dan column_num, kita harus menuliskan row terlebih dahulu sebelum column. Dan jika dibalik antara row dan column maka kita mendapakan nilai transpose dari tabel data referensi.

Dengan bantuan fungsi COLUMN dan ROW  yang berguna untuk mendapatkan nomor kolom dan baris dari sebuah sel, maka kita dapat menyusun fungsi INDEX untuk melakukan transpose data tabel.
Baiklah supaya lebih jelas, mari kita praktekan pada lembar kerja excel.

• Dengan menggunakan data yang sama dengan contoh cara pertama, copy range A1:B6 --> klik kanan sel D1 --> paste special --> pada dialog box tick formats dan tick transpose --> klik OK.  Proses ini akan mengcopy transpose format dari data asli.

• Ketikan formula berikut pada sel D1 

=INDEX($A$1:$B$6,COLUMN(A1)-COLUMN($A$1)+1,ROW(A1)-ROW($A$1)+1)

• Perhatikan tanda dolar ($) digunakan untuk menandakan absolute reference supaya tidak berubah jika dicopy ke sel lainnya. Dalam hal ini sel A1 yang letaknya pada posisi baris ke-1 dan kolom ke-1 digunakan sebagai rujukan untuk menghitung posisi sel lainnya dalam formula.

• Sorot sel D1 --> klik kanan + copy atau CTR+C --> seleksi range D1:I2 --> klik kanan --> paste special --> pada dialog box tick Formulas --> Ok.  Langkah ini mengcopy formula dari sel D1 ke range D1:I2 yang tidak lain adalah tabel  transpose dari range A1:B6

• Berhasil...! Kita sudah membuat data transpose menggunakan rumus kombinasi fungsi INDEX, COLUMN dan ROW.

Sekian dan semoga bermanfaat:

Belajar Excel..! Excellent..!

Artikel Terkait:

• 5 Cara Transpose Data Excel
• Penggunaan Rumus TRANSPOSE Excel
• Transpose Tabel Menggunakan Fungsi INDIRECT ADDRESS