Membuat Fungsi VLOOKUP Sendiri untuk Cara Mengambil Data Lebih dari Satu Baris

Dalam dunia Excel, seringkali kita dihadapkan pada tugas pencarian dan penggabungan nilai dari dua rentang berbeda. Dengan bantuan VBA Macro, kita dapat mengambil data lebih dari satu baris yang membuat prosesnya menjadi lebih efisien. Pada artikel ini, kita akan membahas cara mencari nilai yang cocok antara dua rentang dan menggabungkannya ke dalam satu string dengan menggunakan VBA Macro, serta menambahkan teks pemisah koma untuk memudahkan analisis data.

Mengapa Kita Membutuhkan VBA Macro untuk Mengambil Data Lebih dari Satu Baris?

Excel memiliki fungsi pencarian bawaan seperti VLOOKUP, tetapi dalam beberapa kasus, kita mungkin memerlukan kontrol yang lebih besar atau pengolahan data kustom. Dengan menggunakan VBA Macro, kita dapat membuat fungsi sesuai kebutuhan kita seperti mengambil data lebih dari satu baris.

Langkah 1: Membuka Lembar Microsoft Excel

Pertama-tama sebelum melangkah ke cara mengambil data lebih dari satu baris, kita perlu membuka lembar Microsoft Excel terlebih dahulu. Setelah sheet terbuka sebagai contoh buat tabel seperti berikut:

Dan tugas kita adalah mengambil nama-nama di range C3:C14 yang memiliki nilai sesuai dengan yang ada di kolom sel E3 dan seterusnya. Untuk tugas inilah kita akan membutuhkan skrip VBA Macro.

Langkah 2: Membuka Editor VBA Macro

Buka editor VBA Macro di Excel. Caranya adalah dengan menekan ALT + F11. Ini akan membuka jendela editor VBA yang memungkinkan kita untuk menulis dan menjalankan skrip VBA. Urutannya klik ribbon Developer – Visual Basic. Kemudian klik kanan dan pilih Insert – Module…

Langkah 3: Menulis Fungsi MULTIVLOOKUP

Berikut adalah skrip VBA Macro untuk mengambil data lebih dari satu baris dengan mencari nilai yang cocok antara dua rentang dan menggabungkannya:

Function MULTIVLOOKUP(x As Range, y As Range, Optional z As String = "") As String
For Each a In x
    If a.Text = y.Text Then
    b = b & a.Offset(, 1).Text & z
    End If
Next
MULTIVLOOKUP = Left(b, Len(b) - 1)
End Function

Penjelasan Skrip VBA

Fungsi VBA (Visual Basic for Applications) Excel ini disebut MULTIVLOOKUP. Fungsi ini dirancang untuk melakukan pencarian dan penggabungan nilai dari satu rentang (range) ke dalam rentang lainnya berdasarkan nilai yang sesuai.

Mari kita bahas setiap bagian dari skrip VBA ini:

Function MULTIVLOOKUP(x As Range, y As Range, Optional z As String = "") As String

• Function MULTIVLOOKUP(x As Range, y As Range, Optional z As String = "") As String: Ini adalah deklarasi fungsi VBA. Fungsi ini bernama MULTIVLOOKUP dan menerima tiga parameter:
• x (Sebagai Range): Rentang yang berisi nilai yang akan dicocokkan.
• y (Sebagai Range): Rentang yang berisi nilai yang akan dicari.
• z (Sebagai String, Opsional): Parameter opsional yang akan digunakan untuk menambahkan teks tambahan pada hasil. Default-nya diatur sebagai string kosong (“”).

For Each a In x
    If a.Text = y.Text Then
        b = b & a.Offset(, 1).Text & z
    End If
Next

• For Each a In x: Ini adalah loop yang akan beriterasi melalui setiap sel dalam rentang x.
• If a.Text = y.Text Then: Ini adalah kondisi IF yang memeriksa apakah nilai teks dari sel dalam rentang x sama dengan nilai teks dari sel dalam rentang y.
• b = b & a.Offset(, 1).Text & z: Jika kondisi IF benar, nilai dari sel yang berdekatan (Offset(, 1)) dengan sel dalam rentang x akan ditambahkan ke variabel b bersamaan dengan teks dari parameter opsional z.

MULTIVLOOKUP = Left(b, Len(b) - 1)

• MULTIVLOOKUP = Left(b, Len(b) - 1): Pada akhirnya, fungsi ini mengembalikan nilai yang telah digabungkan ke dalam variabel b. Fungsi Left(b, Len(b) - 1) digunakan untuk menghapus karakter terakhir (yang merupakan karakter tambahan yang tidak dibutuhkan) dari string hasil penggabungan sebelum nilai dikembalikan.

Jadi, secara keseluruhan, fungsi ini mencari nilai yang cocok antara dua rentang dan menggabungkannya ke dalam satu string dengan teks tambahan opsional. String hasilnya kemudian dikembalikan sebagai output fungsi.

Langkah 4: Menggunakan Fungsi di Excel

Setelah menulis fungsi di atas, kita dapat langsung menggunakannya di sel Excel. Misalnya, untuk tabel contoh, ketik fungsi berikut di sel F3:

=MULTIVLOOKUP($B$3:$B$14;E3;", ")

Dalam contoh ini, , adalah teks pemisah koma yang akan ditambahkan di antara nilai-nilai yang cocok. Jika sudah copy dan paste fungsi tersebut ke sel dibawahnya hingga sel F8. Dan hasil akhirnya adalah:

Kesimpulan

Dengan menggunakan VBA Macro, kita dapat membuat fungsi kustom seperti MULTIVLOOKUP untuk menangani kasus-kasus khusus. Ini tidak hanya meningkatkan efisiensi, tetapi juga memberikan fleksibilitas dalam pengolahan data di Excel. Jadi, jangan ragu untuk menjelajahi dan memanfaatkan kekuatan VBA Macro untuk meningkatkan produktivitas Anda dalam bekerja dengan Excel. Semoga artikel ini bermanfaat!

Dan bagi yang membutuhkan file contoh di atas silahkan download file Excel tersebut dengan klik tautan di bawah ini: