Sunday, September 08, 2013

Macro Excel Pengganti VLookup

Macro Excel Pengganti VLookup

Untuk mencari data di sebuah tabel di dalam worksheet, Excel menyediakan beberapa fungsi. Kita dapat menggunakan fungsi VLookup, HLookup, atau fungsi Index yang sering kali dikombinasikan dengan fungsi Match.
Misalnya, Anda memiliki sebuah tabel yang berisi informasi siswa di sebuah sekolah. Ada 6 kolom di sana. Kolom pertama berisi NISN, kolom kedua berisi nomor induk, selanjutnya adalah nama, kemudian tempat lahir, tanggal lahir, dan alamat.
Lalu di sheet lain pada file yang sama, sel A4 sudah berisi nomor induk salah seorang siswa. Anda ingin mengisi sel B4 dengan nama siswanya, sel C4 berisi tanggal lahir, dan sel D4 berisi NISN. Bagaimana caranya? (Perhatikan bahwa Nomor Induk pada tabel pertama tidak terurut.)
Untuk memudahkan, Anda dapat memberi beberapa label pada tabel tersebut. Pada sheetpertama, sorotlah range A2:F11, lalu klik Insert > Name > Define…. Ketikkan “tabel_siswa” (tanpa tanda petik) pada ruas Names in workbook, lalu klik tombol OK. Dengan demikian, sekarang “tabel_siswa” akan mengacu pada range A2:F11. Ulangi hal yang sama untukrange B2:F11 (beri nama “data_siswa”), dan “nomor_induk” untuk range B2:B11. Catatan:dari tiga label yang kita buat, tidak semuanya kita perlukan secara bersamaan. Tergantung metode mana di bawah ini yang akan Anda pakai.

Fungsi VLookup, Index, dan Match

Kembali ke sheet berikutnya (gambar kedua), kita akan mengisi nilai di sel B4, C4, dan D4 dengan mengacu pada data yang telah tersedia di sel A4. Dengan fungsi standar Excel, setidaknya ada dua cara yang dapat kita gunakan.
Cara pertama adalah dengan menggunakan fungsi VLookup. Tulislah di sel B4 formula ini:=VLOOKUP(A4,data_siswa,2,FALSE). Argumen FALSE kita pakai karena kolom Nomor Induk tidak terurut. Sementara itu, nilai sel C4 dapat diisi dengan menulis formula=VLOOKUP(A4,data_siswa,4,FALSE).
Sekarang, bagaimana mengisi sel D4? Fungsi VLookup tidak dapat melakukan ini karena data acuan kita adalah kolom Nomor Induk, dan VLookup hanya bisa mengindeks data pada kolom di sebelah kanan kolom acuan. Padahal data NISN yang kita butuhkan berada di sebelah kiri kolom acuan kita.
Cara kedua adalah dengan fungsi Index yang akan kita kombinasikan dengan Match. Di sel B4, Anda bisa menulis ini: =INDEX(tabel_siswa,MATCH(A4,nomor_induk,FALSE),3). Nilai sel C4 adalah =INDEX(tabel_siswa,MATCH(A4,nomor_induk,FALSE),5). Nilai sel D4? Tentu saja ini:=INDEX(tabel_siswa,MATCH(A4,nomor_induk,FALSE),1).
Dengan fungsi Index, Anda bisa mengisi semua kolom.

Menulis Makro

Anda bisa juga membuat fungsi sendiri dengan membuat makro. Misalnya, dengan hanya mengetikkan =Siswa(n,m), Anda dapat memperoleh data yang diinginkan.
Tulislah kode makro berikut ini di bagian Module. (Anda yang belum tahu bagaimana caranya, bisa membaca petunjuk singkatnya pada tulisan lain di blog ini.
Function Siswa(ByVal NIS, Order)
 
    Check = WorksheetFunction.CountIf(Range("nomor_induk"), NIS)
 
    If Check = 0 Then
        Siswa = "Tidak ada"
    ElseIf Check = 1 Then
        With Range("nomor_induk")
            Siswa = .Find(What:=NIS, LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, Order)
        End With
    Else: Siswa = "Data lebih dari satu"
    End If
 
End Function
Dengan fungsi yang kita buat ini, kita dapat mengisi sel B4 dengan =Siswa(A4,1). Sel C4 berisi =Siswa(A4,3), dan sel D4 berisi =Siswa(A4,-1).
Dari beberapa metode di atas, Anda bisa memilih salah satu yang sesuai dengan kebutuhan Anda.

Labels: