Cara Menampilkan Data dan Foto Dengan Menggunakan Vlookup

Bermula posting ini saya tulis untuk memberikan tanggapan terhadap beberapa pengunjung blog yang menanyakan cara membuat database yang dilengkapi dengan foto di sheet, hingga saat melakukan pencarian data akan memperlihatkan foto pemilik data yang tercatat pada database, dan juga tentang cara VlookUp dengan menampilkan foto pada form tertentu. Pada intinya sama yaitu mencantumkan foto pada database, ketika melakukan proses pencarian atau VlookUp dapat menampilkan foto dengan datanya pada form tertentu yang terdapat pada sheet.

Menurut pengalaman saya, aplikasi excel yang menyimpan foto dalam worksheet akan berat dalam melakukan proses aktifitasnya. Foto yang tersimpan didalam database yang terdapat dalam Worksheet akan membuat ukurang file cendrung lebih besar. Semakin banyak foto yang di sematkan dalam worksheet maka ukuran file akan membengkak dengan ukurannya yang akan lebih besar. Hal ini membuat aplikasi akan berjalan lebih lambat. Dalam penggunaan foto sering sekali saya merekomendasikan agar menggunakan cara yang telah saya posting di Cara Masukkan/Insert Foto Kedalam Aplikasi Dengan Userfrom VBA Excel.

Cara Menampilkan Data dan Foto 

Namun demikian, untuk memperkaya pengetahuan kita tentang excel tidak salahnya juga kita sama-sama mempelajari cara menampilkan data dan foto yang terdapat dalam database dengan menggunakan rumus Vlookup. Seperti kita ketahui bersama bahwa sering sekali aplikasi yang digunakan untuk merecord data personal diperlukan filtur tambahan yang bisa di fungsikan untuk menampilkan foto sebagai pengenal data. Contoh aplikasi yang biasanya dibutuhkan foto adalah Aplikasi Penerimaan Siswa Baru, Aplikasi Buku Induk Siswa, Aplikasi Kartu Siswa, Aplikasi Kartu Ujian, Aplikasi Karyawan, dan banyak lainnya. 

Sebagai bahan uji coba, pada posting ini kita akan sama-sama merancang aplikasi kartu peserta ujian siswa. Simak dengan teliti posting yang saya sajikan ini, agar anda dapat merancang aplikasi yang sudah dilengkapi dengan foto pada database dan dapat menampilkan foto tersebut pada tiap biodata yang akan di print.

Cara Menampilkan Data dan Foto Dengan Menggunakan Vlookup

1. Merancang Database dan Desain Form

Buka excel, Ubah nama Sheet1 menjadi "Data" dan Sheet2 menjadi "Kartu" dan Sheet3 menjadi "Set". Pada Sheet Data buatlah tabel seperti yang terlihat pada gambar gif diatas. Sheet Data akan menjadi sumber database yang terdiri dari tiap kolom headers table yaitu No, Nis, NISN, Nomor Peserta, Nama Lengkap, Tempat Lahir dan kolom terakhir Tanggal Lahir. Isikan data sebanyak 20 baris kebawah yang dilengkapi dengan foto pada tiap datanya.

Pada Sheet Kartu rancanglah sebuah desain kartu ujian sebanyak 8 kartu. Cantumkan header pada cell E2 dengan nama lembaga pendidikan, Alamat jalan pada cell E3 dan E4. Pada cell C6 tulislah "Kartu Peserta Ujian". Data yang diperlukan yaitu pada cell  E8 tulislah"Nama Lengkap", pada cell E9 tulislah "T/Tgl Lahir", Pada E10 tulislah "NIS/NISN", Pada Cell E11 tulislah "ID Number". Untuk logo lembaga masukkan dengan cara klik Developer, pilih ribbon control, klik Insert dan masukkan Image yang terdapat pada Active x Control ke tiap kartu peserta ujian yang akan kita jadikan sebagai logo dan foto, Perhatikan gambar dibawah dengan tanda panah warna hijau. Properties Image pada setiap kartu jangan diubah. Masukkan juga Spin Button seperti terlihat pada gambar perhatikan panah warna merah, masukkan juga CommandButton perhatikan pada gambar dibawah dengan panah warnah biru.

Gambar 1

Berikutnya kita akan menata pada sheet Set yang merupakan profil dari pada lembaga yang terdiri dari Nama Sekolah, Alamat, Nomor Telpon, Judul Id Card, Fotter Id Card. Tambahkan Image dengan cara pilih Developer klik Ribbon Insert yang berapa pada Control dan pilih Image yang berada pada ActiveX Control. Image ini akan kita gunakan sebagai sumber logo yang akan kita jadikan sebagai acuan logo pada Image yang dijadikan logo pada Sheet Kartu. Tambahkan juga satu buah CommandButton yang ada pada ActiveX Control. Perhatikan gambar dibawah ini. Panah biru untuk Image dan panah merah untuk CommandButton.

Perlu diperhatikan dalam meletakkan Image yang ada pada Sheet Set. Letakkan sudut kiri atas Image tepat pada sudut sisi Cell E4. Perhatikan dengan teliti gambar dibawah, agar tampilan Logo pada tiap kartu akan terlihat tepat.

Gambar 2

Masih pada Sheet Set, Klik kanan pada Image1, pilih Properties. Pada Properties Image1 cari tulisan PictureSizeMode dan ubah modenya menjadi 1-fmPictureSizeModeStretch atau anda dapat menyesuaikan pilihan menurut selera anda sendiri. Selanjutnya klik kanan pada CommandButton1 pilih Properties cari tulisan Caption. Ganti Caption CommandButton1 menjadi Change. Tombol ini akan kita gunakan untuk memasukkan kode yang berfungsi mencari gambar yang akan di tampilkan di Image sebagai logo. Sampai disini kita sudah siap pada tahap Merancang Database dan Desain,

2. Memasukkan Formula dan Kode VBA

Pada Sheet Set pastikan bahwa pada ribbon Control pada pilihan Design Mode aktif. Klik kanan pada CommandButton1 dengan Caption Change pilih View Code. Pada jendela Editor Visual Basic masukkan kode berikut ini. Kode berikut ini berfungsi membuka explorer untuk mengambil foto yang akan kita pilih sebagai logo.

Private Sub CommandButton1_Click()
    ambilfoto = Application.GetOpenFilename
    If ambilfoto = "False" Then Exit Sub
    Image1.Picture = LoadPicture(ambilfoto)
    Range("E4").Value = ambilfoto
End Sub

Keterangan :

"ambilfoto = Application.GetOpenFilename" merupakan kode untuk membuka DialogBox explorer untuk memilih file. "If ambilfoto = "False" Then Exit Sub" Kode ini akan menghentikan atau mengcancel proses jika foto yang tidak dipilih. "Image1.Picture = LoadPicture(ambilfoto)" merupakan kode yang berfungsi untuk memberikan perintah pada Image1 agar mengambil gambar yang dipilih dan menempatkannya dalam Image. "Range("E4").Value = ambilfoto" merupakan kode untuk merekam letak gambar yang dipilih link gambar akan muncul di cell E4.

Jika sudah meletakkan kode diatas pada CommandButton1 dengan Caption Change, saya sarankan untuk menguji dengan cara mengklik tombol Change. Bila DialogBox Explorer sudah muncul silahkan dipilih gambar yang akan dijadikan logo hingga gambar tersebut terlihat pada Image1 pada Sheet Set.

Pada Sheet Data kita akan membuat beberapa Define Name yang menjadi acuan yang akan digunakan untuk data yang akan ditampilkan pada Sheet Kartu. Klik Tab Formulas pada Ribbon Defined Names pilih Define Name hingga muncul DialogBox. Isikan berdasarkan nama dan range pada Refers to berikut ini tanpa tanda petik.

Name "DataSiswa"Refers to "=Data!$A$4:$H$24"

Name "Urut" Refers to "=Data!$A$4:$A$24"

Name "Pic" Refers to "=Data!$H$4:$H$24"

Name "Image1" Refers to "=INDEX(Pic,MATCH(Kartu!$W$4,Urut,0),1)"

Name "Image2" Refers to "=INDEX(Pic,MATCH(Kartu!$W$5,Urut,0),1)"

Name "Image3" Refers to "=INDEX(Pic,MATCH(Kartu!$W$6,Urut,0),1)"

Name "Image4" Refers to "=INDEX(Pic,MATCH(Kartu!$W$7,Urut,0),1)"

Name "Image5" Refers to "=INDEX(Pic,MATCH(Kartu!$W$8,Urut,0),1)"

Name "Image6" Refers to "=INDEX(Pic,MATCH(Kartu!$W$9,Urut,0),1)"

Name "Image7" Refers to "=INDEX(Pic,MATCH(Kartu!$W$10,Urut,0),1)"

Name "Image8" Refers to "=INDEX(Pic,MATCH(Kartu!$W$11,Urut,0),1)"

Kita beralih pada Sheet Kartu, kita akan mengatur Scrol Bar terlebih dahulu. Klik kanan pada Scrol Bar pilih Format Control, pada DiologBox Format Control atur Current value "1", Minimum value "1", Maximum value "20" dan Cell Link "$U$3" (Perhatikan Gambar1 pada kotak merah). Pada cell W3 tulislah rumus "=U3-1", pada cell W4 tulis rumus "=(W3*8)+1", Pada cell W5 tulislah rumus "=W4+1", pada cell W6 tulislah rumus "=W5+1", pada cell W7 tulislah rumus "=W6+1", pada cell W8 tulislah rumus "=W7+1", pada cell W9 tulislah rumus "=W8+1", pada cell W10 tulislah rumus "=W9+1" pada cell W11 tulislah rumus "=W10+1" (Tanpa tanda pentik yeah..! Perhatikan Gambar1 pada kotak warna Hijau).

Berikutnya masih pada Sheet Kartu kita akan melakukan penulisan rumus Vlookup data dan foto. Vlookup data sebagai berikut :

Pada cell G8 tulislah rumus

 =VLOOKUP($W$4,DataSiswa,5)

Pada cell G9 tulislah rumus 

=PROPER(VLOOKUP($W$4,DataSiswa,6)) & "," & TEXT(VLOOKUP($W$4,DataSiswa,7),"dd-mm-yyyy")

Pada cell G10 tulislah rumus 

=VLOOKUP($W$4,DataSiswa,2) & "/" & VLOOKUP($W$4,DataSiswa,3)

Pada cell G11 tulislah rumus Vlooup 

=VLOOKUP($W$4,DataSiswa,4)

Untuk cell lainnya pada tiap kartu silahkan disesuaikan sendiri Vlookupnya yeah..!!! jika saya tulis semua nanti anda akan kurang kreatif dalam menglogikakan Vlookup.

Pada Sheet Kartu setiap Image yang menjadi logo pasang kode dibawah ini pada Formula Bar. Caranya klik pada Image, dalam kodisi Image terpilih tulislah kode berikut ini pada Formula Bar dan enter. Begitu seterusnya dilakukan pada setiap Image yang dijadikan logo.

=Set!E4

Masih pada Sheet Kartu kita akan melakukan Vlookup Foto yang ada pada database ke Image yang ada pada sheet Kartu. Dalam kodisi Image yang dijadikan foto terpilih tulislah kode berikut ini pada Formula Bar dan enter. Begitu seterusnya dilakukan pada setiap Image yang dijadikan foto, anda hanya menyesuikan antara urutan angka data pada database dengan setiap Image pada data kartu yang sudah siap di Vlookup

Image untuk foto data nomor 1 pada database

=Image1

Image untuk foto data nomor urut 2 pada database

=Image2

Image untuk foto data nomor urut 3 pada database

=Image3

Image untuk foto data nomor urut ke berikutnya dapat disesuaikan dengan Define Name yang sudah ada hingga Image8.

Pada Tombol Cetak yang terletak pada Sheet kartu kita dapat melakukan perintah cetak dengan memasang kode berikut ini. Sesuaikan dengan kebutuhan dan kondisi Sheet yang anda buat.

Private Sub CommandButton1_Click()
Dim Ws As Worksheet: Set Ws = Sheet11
With Ws
.Activate
.PageSetup.PrintArea = "B2:S5250"
.PageSetup.Zoom = 100
.PageSetup.PaperSize = xlPaperA4
.PageSetup.LeftMargin = Application.CentimetersToPoints(0.21)
.PageSetup.RightMargin = Application.CentimetersToPoints(0.13)
.PageSetup.BottomMargin = Application.CentimetersToPoints(1.5)
.PageSetup.TopMargin = Application.CentimetersToPoints(1.5)
.PageSetup.CenterHorizontally = True
.PageSetup.CenterVertically = False
.PrintPreview
End With
End Sub

Bagi anda yang bergelut dalam bidang pendidikan sudah pasti menjalani proses pendaftaran penerimaan siswa baru, mengisi buku induk, membuat kartu siswa, dan membuat kartu ujian tiap semester. Pada penerimaan siswa baru sering sekali guru yang menjadi penitia akan meminta foto pada colon siswa untuk bisa di tempelkan pada nomor ujian seleksi siswa. Dengan berpedoman pada posting ini, anda akan dapat merancang aplikasi yang sudah dilengkapi dengan filtur foto, yang akan memperjelas data. Secara bertahap anda pasti akan mengembangkannya dengan menambahkan filtur lainnya untuk memudahkan pekerjaan. 

Demikian Posting Cara Menampilkan Data dan Foto Dengan Menggunakan Vlookup yang dapat saya sajikan dengan pembahasan yang tidak beraturan, mohon dimaafkan atas segala kekurangan, dan bila ada kesalahan dalam penyajian atau membuat anda bingung, silahkan di sampaikan di kolom komentar dengan bijaksana. Semoga apa yang saya tuliskan pada posting ini dapat menjadi manfaat bagi yang membutuhkan.

Berbagi :