Banyak orang mencoba menggunakan Excel sebagai database. Meskipun dapat berfungsi sebagai database, beberapa tugas yang akan sangat mudah dalam program database cukup kompleks di Excel. Salah satu tugas ini mencocokkan dua daftar berdasarkan bidang yang sama; ini dapat dengan mudah dilakukan dengan menggunakan Excel VLOOKUP. Anda akan menemukan fungsi VLOOKUP sangat berguna, jadi lihat contoh kapan dan bagaimana menggunakan fungsi ini di bawah.
Katakanlah agen perjalanan Anda mengirimi Anda laporan akhir bulan dari semua tempat yang pernah dikunjungi karyawan Anda. Laporan tersebut menggunakan kode bandara, bukan nama kota. Akan sangat membantu jika Anda dapat dengan mudah memasukkan nama kota yang sebenarnya, bukan hanya kodenya.
Di Internet, Anda menemukan dan mengimpor daftar yang menunjukkan nama kota untuk setiap kode bandara.
Tetapi, bagaimana Anda mendapatkan informasi ini pada setiap catatan dalam laporan?
- Gunakan fungsi VLOOKUP. VLOOKUP adalah singkatan dari "Vertical Lookup". Ini dapat digunakan kapan saja Anda memiliki daftar data dengan bidang kunci di kolom paling kiri.
- Mulai ketik fungsi
=VLOOKUP(,. Ketik Ctrl + A untuk mendapatkan bantuan dengan fungsi tersebut. - VLOOKUP membutuhkan empat parameter. Pertama adalah kode kota pada laporan asli. Dalam contoh ini, itu adalah sel D4
- Parameter berikutnya adalah rentang dengan tabel pencarian Anda. Sorot kisarannya. Pastikan untuk menggunakan F4 untuk membuat kisaran menjadi absolut. (Referensi absolut memiliki tanda dolar sebelum nomor kolom dan nomor baris. Saat rumus disalin, referensi akan terus mengarah ke I3: J351.
- Parameter ke-3 memberi tahu Excel di kolom mana nama kota ditemukan. Pada range I3: J351, nama kota ada di kolom 2. Masukkan 2 untuk parameter ini.
- Parameter ke-4 memberi tahu Excel jika kecocokan "hampir" OK. Dalam hal ini, tidak, jadi masukkan False.
- Klik OK untuk menyelesaikan rumus. Seret gagang isian untuk menyalin rumus ke bawah.
- Karena Anda memasukkan rumus absolut dengan cermat, Anda dapat menyalin kolom E ke kolom D untuk mendapatkan kota tujuan. Dalam hal ini, semua keberangkatan berasal dari Bandara Internasional Pearson di Toronto.
Meskipun contoh ini berhasil dengan sempurna, ketika pemirsa menggunakan VLOOKUP, biasanya itu berarti mereka mencocokkan daftar yang berasal dari sumber berbeda. Jika daftar berasal dari sumber yang berbeda, selalu ada perbedaan kecil yang membuat daftar sulit untuk dicocokkan. Berikut tiga contoh kesalahan yang mungkin terjadi dan cara memperbaikinya.
-
Satu daftar memiliki tanda hubung dan daftar lainnya tidak. Gunakan
=SUBSTITUTE()fungsi untuk menghapus tanda hubung. Pertama kali Anda mencoba VLOOKUP, Anda akan mendapatkan kesalahan N / A.Untuk menghapus tanda hubung dengan rumus, gunakan rumus PENGGANTI. Gunakan 3 argumen. Argumen pertama adalah sel yang berisi nilai. Argumen selanjutnya adalah teks yang ingin Anda ubah. Argumen terakhir adalah teks pengganti. Dalam kasus ini, Anda ingin mengubah tanda hubung menjadi tidak ada, jadi rumusnya adalah
=SUBSTITUTE(A4,"-","").Anda dapat menggabungkan fungsi itu di VLOOKUP untuk mendapatkan deskripsinya.
-
Yang ini halus, tetapi sangat umum. Satu daftar memiliki spasi kosong setelah entri. Gunakan = TRIM () untuk menghapus spasi berlebih. Saat pertama kali memasukkan rumus, Anda menemukan semua jawabannya adalah kesalahan N / A. Anda tahu pasti bahwa nilainya ada dalam daftar dan semuanya terlihat baik-baik saja dengan rumusnya.
Satu hal standar untuk diperiksa adalah berpindah ke sel dengan nilai pencarian. Tekan F2 untuk meletakkan sel dalam mode Edit. Setelah dalam mode edit, Anda dapat melihat bahwa kursor terletak satu spasi dari huruf terakhir. Ini menunjukkan bahwa ada spasi tambahan di entri.
Untuk mengatasi masalah tersebut, gunakan fungsi TRIM.
=TRIM(D4)akan menghapus spasi di depan, spasi di belakang, dan akan mengganti spasi ganda internal dengan satu spasi. Dalam hal ini, TRIM bekerja dengan sempurna untuk menghilangkan spasi tambahan.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)adalah rumusnya. -
Saya menyebutkan tip bonus dalam catatan pertunjukan: cara mengganti hasil # N / A untuk nilai yang hilang dengan kosong. Jika nilai pencarian Anda tidak ada dalam tabel pencarian, VLOOKUP akan mengembalikan kesalahan N / A.
Rumus ini menggunakan
=ISNA()fungsi untuk mendeteksi jika hasil rumus berupa kesalahan N / A. Jika Anda mendapatkan kesalahan, argumen ke-2 dalam fungsi IF akan memberi tahu Excel untuk memasukkan teks apa pun yang Anda inginkan.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP memungkinkan Anda menghemat waktu saat mencocokkan daftar data. Luangkan waktu untuk mempelajari penggunaan dasar dan Anda akan dapat melakukan tugas yang jauh lebih hebat di Excel.
