Macro Excel Pengganti VLookup

6 Februari 2008 by Eka | 31 comments

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 sheet pertama, 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 untuk range 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.

Tags: , , , , ,

Entri yang Mungkin Terkait

Komentar. Silakan berikan komentar Anda. Beberapa tag HTML diperbolehkan. Anda juga dapat mendaftar di Gravatar untuk menampilkan foto Anda.

  1. Raynold’s avatar

    Raynold on 22 Februari 2008 at 07:10

    Waaahhhhh……………
    Terima kasih…. terima kasih….. atas pencerahannya…..
    Saya gak tau kalo hasil pencarian ternyata bisa disimpan dalam variabel..
    bener-bener harus banyak belajar nih…

    Sekali lagi terima kasih…

    Raynold

  2. runs’s avatar

    runs on 11 Maret 2008 at 10:46

    Saya tertarik dgn tulisan anda mengenai trik di mic.excel

    Saya mempunyai data yg banyak di excel, dan kesulitan jika mencari 1 atau 2 kata di file excel yg bejibun worksheetnya

    pertanyaan : apakah kita bisa membuat semacam search engine di excel untuk file tertentu, sehingga jika saya mau mencari misalnya kata “aku” di sebuah file excel tinggal entry kata “aku” dan klik tombol (jadi bukan ctrl-f)

    thanks,

    -runs-balikpapan-

  3. eka’s avatar

    eka on 17 Maret 2008 at 08:33

    runs: Alih-alih membuatnya, mengapa Anda tidak install saja Google Desktop? :)

  4. eko ps’s avatar

    eko ps on 18 Maret 2008 at 09:45

    Mas, kalo mau buat formulir dalam satu sheet lalu datanya otomatis masuk database dalam sheet lainnya tapi masih satu file seperti dalam acces, kira2 bagaimana? mohon pencerahannya?

  5. azhar’s avatar

    azhar on 22 Maret 2008 at 09:06

    aku senang dengan situs ini krn aku bisa lebih mengerti tentang excel dan masih banyak hal-hal yang menarik lainnya, thanks atas infonya

  6. yudie’s avatar

    yudie on 27 Maret 2008 at 10:00

    terimakasih ,forum ni sangat bermanffat bagi saya

    ane jga pgn nanya ni bro
    klo mo bikin soal excel untuk siswa yang terproteksi dan secara otomatis
    menilai hasil dari pekerjaan pada lembar kerja gimana ya.di situ nantinya juga ada step by step yang harus dibaca layaknya di vb ato delpi.
    ato lebih jelasnya coba download di

    http://www.smeapgri-tng.sch.id/online/html/?tab=download&fileid=modul-03.zip

    tolong kasih tau ni kodenya biar bisa dimodifikasi

    terimakasih

  7. maman’s avatar

    maman on 8 April 2008 at 10:51

    kolom a kolom b kolom c
    1 a 10
    1 b 20
    1 c 30
    1 d 40
    2 e 50
    2 f 60
    2 g 70

    mas, mo nanya dung.bagaimana caranya kalo kita ingin hasilnya tu kolom c dengan melihat kondisi pada kolom a dan kolom b (misal 10 pada kolom c itu adalah 1 Pada kolom a dan a pada kolom b). karena kalo pake vlookup biasa bisa-bisa salah soalnya 1 pada kolom a itu tidak hanya satu.
    saya tunggu jawabannya ya mas,klo boleh lewat email saya aja.trima kasih

  8. denada mariana’s avatar

    denada mariana on 7 Mei 2008 at 14:54

    kalau mau belajar excel yang lengkap di http://www.XL-mania.com

  9. daeng’s avatar

    daeng on 9 Mei 2008 at 09:40

    tolong saya buatkan rumus membaca foto melalui cell.

    salam hormat
    ttd
    daeng

  10. pembeg’s avatar

    pembeg on 12 Mei 2008 at 08:49

    wahhhhhh…….
    gw baru tw klo gw bisa blajar d net tntang vlookup…kapan2 gw bs bljar itu tuh….ahahah….psti enak bgt…thx

  11. dudes’s avatar

    dudes on 16 Mei 2008 at 22:19

    Klo bisa ditambahkan cara menggunakan Fungsi database pada Excel, misal Dsum, Dcount, yg pasti Database lah.. soalnya saya jg ingin belajar

  12. hartono.ananto’s avatar

    hartono.ananto on 30 Mei 2008 at 02:25

    Dulu gwe kepengen belajar Macro, abis lihat buku XL-mania yang judulnya “50 Jurus Rahasia Excel” (atau 50 Jurus Rahasia Microsoft Excel ya?) jadi ilfeel mau belajar macro. Ternyata banyak hal ajaib yang bisa dikerjain pake fungsi doang.

  13. hartono.ananto’s avatar

    hartono.ananto on 30 Mei 2008 at 02:26

    Oh ya, jangan ngaku jagoan Excel kalau belum join XL-mania… ikutan di http://www.XL-mania.com!

  14. mulyoe’s avatar

    mulyoe on 4 Juni 2008 at 17:21

    salam hormat,

    saya mau konsultasi :

    bagaimana mencari angka dengan 3 (tiga) acuan atau dari pivot data excel dimana ketiga acuan sangat mendukung angka tersebut yang terdiri dari merk toko, jenis jualan, dan merk jualan. misalnya :
    toko A menjual ROTI dengan merk ENAK 1 buah
    toko B menjual ROTI dengan merk ENAK 1 buah
    toko C menjual KERUPUK denan merk ASYIK 1 bungkus
    toko D menjual KERUPUK dengan merk ASYIK 1 bungkus

    dimana apabila kita cari menurut :
    merk toko maka A=1 B=1 C=1 dan D=1
    jenis jualan maka ROTI=2 dan KERUPUK=2
    merk jualan maka ENAK=2 dan ASYIK=2

    setelah data kita masukkan ke pivot data (excel) apa rumus untuk memecahkan per toko dengan jenisnya ?

    TRIM’S

  15. sis joko’s avatar

    sis joko on 21 Agustus 2008 at 14:58

    mas numpang naanya nih…

    misalnya kita punya kasus ingin mengotomatiskan data masuk ke sheet laen tanpa pake inputan di sheet yang ingin di otomatiskan tersebut..

    gimana rumusnya…
    sedangkan kolom nya ada di sheet yang 1 lage…

  16. Fadli’s avatar

    Fadli on 25 Agustus 2008 at 23:30

    Muantep,
    Baru tau’ dari temen belajar macro bisa dari sini,
    makasih Mas elmunya…

  17. joko ariyanto’s avatar

    joko ariyanto on 29 Agustus 2008 at 00:28

    Terimakasih banyak benar2 sangat membantu untuk kerja saya…thanks..semoga menjadi amal bakti anda dan Allah membalas yang lebih baik…amin

  18. Ismail’s avatar

    Ismail on 12 November 2008 at 10:02

    Terimakasih atas pencerahannya, namun saya masih bingung membuat formula Macro nya, bagaimana caranya?

    Terimakasih

    Salam

    ISMAIL

  19. fadeel’s avatar

    fadeel on 24 November 2008 at 10:13

    makasih banget …..
    ini yang saya cari dari dulu ….

  20. ivory’s avatar

    ivory on 10 Februari 2009 at 10:44

    whiiiiiihiiii…….

    bguz bgt maz…..

  21. Dimyati Arif Lamongan’s avatar

    Dimyati Arif Lamongan on 11 Februari 2009 at 23:49

    Assalamu alaikum Wr. Wb.

    Makasih banyak mas untuk pengubah bilangan menjadi teks, tapi yang q inginkan tanpa ada rupiah atau sen, jadi cukup bilangannya saja,
    contoh:
    150 — keluar “seratus lima puluh”
    makasih
    mohon infonya
    Wassalamualaikum

  22. eduard’s avatar

    eduard on 22 April 2009 at 15:10

    pak, saya mau nanya cara membuat klickbox di excel lewat marcro bagaimana ya, tolong di jelaskan dan dikasih contoh
    terima kasih banyak

  23. agus’s avatar

    agus on 17 Juni 2009 at 17:23

    mas nanya kalo untuk data yang sama lebih dari satu, script lanjutannya apa untuk menampilkan semua data yg sama ?
    makasih.

    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

  24. Tryas’s avatar

    Tryas on 23 Juni 2009 at 07:51

    Assalam….
    Thanks mas…. bisa minta kiriman file modul2 makro nya gak?thanks…

  25. yudi rabie’s avatar

    yudi rabie on 29 Juni 2009 at 09:48

    mas,

    klo bikin makro seperti fungsi untuk memasukan secara otomatis ke nama sheet yg kita tuju, misalkan saya membuat suatu jurnal akuntasi, kode jurnalnya itu bisa langsung memasuk ke sheet sesuai dengan kode GL bagai mana yach?, trima kasih atas bantuannya

    rgds,
    yudi rabie

  26. gunawan’s avatar

    gunawan on 4 September 2009 at 12:04

    terima kasih atas pencerahannya. hidup jadi lebih berati kalo kita bisa menguasai satu jenis dan untuk itu kita bisa memaksimalkan semua pasti hidup kita tidak akan susah. makanya harus lebih baik dna lebih semangat untuk belajar. selamat atas usaha anda mementuk forum ini. sukses untuk anda.

  27. yayan’s avatar

    yayan on 17 September 2009 at 12:22

    bagaimana membuat tulisan berjalan di excell dengan macro

  28. Fitra’s avatar

    Fitra on 11 November 2009 at 14:45

    mas,mau nanya..
    kalau mau bikin fungsi pencarian data yang ada dalam excel itu sendiri kemudian menampilkannya dalam sel2 yang kita inginkan dengan menggunakan macro gimana ya?
    mohon gambaran script untuk pencerahannya.
    Terima Kasih

  29. Tresna Wijaya’s avatar

    Tresna Wijaya on 27 November 2009 at 13:55

    Thanks aza dulu krn baru mau nyoba nih… thanks3x…..

  30. lucas minarta’s avatar

    lucas minarta on 27 Desember 2009 at 10:24

    hallo pak priatna apa khabar,
    mau tanya gimana cara melihat jawaban pak priatna atas pertanyaan super rekan2 diatas. suatu pengalaman yg luar biasa kl sy dpt melihatnya. trims ya pak.

  31. donny’s avatar

    donny on 12 Februari 2010 at 07:27

    tempat belajar excel dan macro paling lengkap ya XL-mania.com


Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 30720 bytes) in /home/priarid1/public_html/wp-content/plugins/SK2/sk2_plugins/sk2_javascript_plugin.php on line 132