Misal ada data seperti gambar dibawah. Ada daftar merek motor, tipe motor berikut dengan harganya diworksheet “Daftar Motor”
Bagaimanakah cara membuat rumus excel dengan layout seperti dibawah (worksheet “List”).
Sebuah combobox berisi 3 data merek sepeda motor. Jika salah satu merek dipilih maka akan keluar daftar semua tipe dan harga motor untuk merek yang sama dengan data didalam combobox. Jika data merek dicombobox diganti maka otomatis daftar tipe dan harga motor akan berubah menyesuaikan dengan merek yang dipilih.
Fungsi apakah yang akan digunakan untuk menampilkan semua tipe dan harga motor?.
Fungsi VLOOKUP tidak bisa digunakan untuk menyelesaikannya, karena fungsi VLOOKUP hanya bisa menampilkan data pertama yang ditemukan, sedangkan layout yang diinginkan adalah menampilkan lebih dari satu data sekaligus.
Tips dan trik sebelum ini sudah membahas tentang kelemahan fungsi VLOOKUP, berikut dengan solusinya. Bisakah solusi tersebut bisa digunakan untuk menampilkan layout seperti gambar diatas?
Jawabannya BISA :).
Langkah pertama adalah membuat kolom bantuan disebelah kiri daftar merek motor, tipe motor dan harga motor diworksheet “Daftar Motor”.
Hasilnya menjadi seperti gambar dibawah.
Formula yang digunakan untuk kolom bantuan adalah sebagai berikut:
=B2&”|”&COUNTIF($B$2:B2,B2)
Langkah kedua adalah membuat combobox yang berisi data 3 merek sepeda motor diworksheet “List”.
Combobox dibuat dengan menggunakan menu data validation.
Pada bagian allow diisi dengan List, kemudian pada bagian source diisi dengan 3 merek sepeda motor yang dipisahkan dengan tanda koma.
Langkah ketiga adalah merangkai fungsi VLOOKUP diworksheet “List” menggunakan data yang ada didalam combobox dan memanfaatkan kolom bantuan yang sudah dibuat.
Letakkan kursor dicell A4
Ketik formula untuk fungsi VLOOKUP yang akan mencari type motor untuk merek yang ada dicombobox.
Hasilnya seperti gambar dibawah.
Tipe motor pertama untuk merek Honda adalah VERZA150.
Formula yang digunakan adalah sebagai berikut:
VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,3,FALSE)
Langkah selanjutnya adalah mencari harga untuk tipe motor VERZA150.
Letakkan kursor dicell B4.
Ketik formula untuk fungsi VLOOKUP yang akan mencari harga motor untuk merek yang ada dicombobox.
Hasilnya seperti gambar dibawah.
Harga untuk tipe motor VERZA150 adalah 19.565.000
Formula yang digunakan adalah sebagai berikut:
VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,4,FALSE)
Tipe motor untuk merek HONDA dan harganya bisa ditampilkan. Bagaimana untuk menampilkan tipe-tipe motor kedua, ketiga dan seterusnya.
Caranya mudah cukup kopi range A4:B4 kemudian paste dirange A5:A20
Hasilnya seperti gambar dibawah.
Ada error #N/A dibagian bawah. Ini terjadi karena merek Honda hanya memiliki 14 data tipe motor. Data ke-15 sampai ke-17 tidak ada dan menghasilkan error #N/A.
Untuk menghilangkan error #N/A bisa menggunakan fungsi IFNA (Excel 2013) atau fungsi IF dan fungsi ISNA (Excel sebelum 2013)
Formula jika menggunakan fungsi IFNA
Fungsi VLOOKUP untuk menampilkan data tipe motor.
=IFNA(VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,3,FALSE)
,””)
Fungsi VLOOKUP untuk menampilkan data harga motor.
=IFNA(VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,4,FALSE)
,””)
Formula jika menggunakan fungsi IF dan ISNA
Fungsi VLOOKUP untuk menampilkan data tipe motor.
=IF(ISNA(VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,3,FALSE)),””
,VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,3,FALSE))
Fungsi VLOOKUP untuk menampilkan data harga motor.
=IF(ISNA(VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,4,FALSE)),””
,VLOOKUP($B$1&”|”&ROWS($A$4:A4)
,’Daftar Motor’!$A$2:$D$25
,4,FALSE))
Hasilnya seperti gambar dibawah.
Error #N/A menghilang dan menampilkan data tipe motor dan harganya saja.
Jika data combobox dicell B2 diubah maka daftar tipe motor dibawahnya akan ikut berubah menyesuaikan dengan perubahan merek motor.
File Latihan
File latihan bisa didownload disini.
