Sebilangan besar orang cuba menggunakan Excel sebagai pangkalan data. Walaupun dapat berfungsi sebagai pangkalan data, beberapa tugas yang sangat mudah dalam program pangkalan data cukup rumit di Excel. Salah satu tugas ini adalah memadankan dua senarai berdasarkan bidang yang sama; ini dapat dicapai dengan mudah menggunakan Excel VLOOKUP. Anda akan mendapati fungsi VLOOKUP sangat berguna jadi lihat contoh kapan dan bagaimana menggunakan fungsi ini di bawah.
Katakan bahawa agensi pelancongan anda akan menghantar laporan akhir bulan kepada anda mengenai semua tempat yang dilalui oleh pekerja anda. Laporan tersebut menggunakan kod lapangan terbang dan bukannya nama bandar. Akan lebih baik jika anda dengan mudah memasukkan nama bandar sebenar dan bukan hanya kodnya.
Di Internet, anda menjumpai dan mengimport senarai yang menunjukkan nama bandar untuk setiap kod lapangan terbang.
Tetapi, bagaimana anda mendapatkan maklumat ini pada setiap rekod dalam laporan?
- Gunakan fungsi VLOOKUP. VLOOKUP bermaksud "Pencarian Vertikal". Ia boleh digunakan bila-bila masa anda mempunyai senarai data dengan medan kunci di lajur paling kiri.
- Mula menaip majlis itu,
=VLOOKUP(. Ketik Ctrl + A untuk mendapatkan bantuan dengan fungsi tersebut. - VLOOKUP memerlukan empat parameter. Pertama ialah kod bandar dalam laporan asal. Dalam contoh ini, itu adalah sel D4
- Parameter seterusnya adalah julat dengan jadual carian anda. Serlahkan julat. Pastikan menggunakan F4 untuk menjadikan julatnya mutlak. (Rujukan mutlak mempunyai tanda dolar sebelum nombor lajur dan nombor baris. Apabila formula disalin, rujukan akan terus menunjuk ke arah I3: J351.
- Parameter ke-3 memberitahu Excel di ruangan mana nama bandar dijumpai. Dalam julat I3: J351, nama kota ada di lajur 2. Masukkan 2 untuk parameter ini.
- Parameter ke-4 memberitahu Excel jika padanan "tutup" OK. Dalam kes ini, tidak, jadi masukkan Palsu.
- Klik OK untuk menyelesaikan formula. Seret pemegang isian untuk menyalin formula ke bawah.
- Oleh kerana anda memasukkan formula mutlak dengan teliti, anda boleh menyalin lajur E ke lajur D untuk mendapatkan bandar tujuan. Dalam kes ini, semua pelepasan adalah dari Lapangan Terbang Antarabangsa Pearson di Toronto.
Walaupun contoh ini berfungsi dengan sempurna, ketika penonton menggunakan VLOOKUP, biasanya ini bermaksud bahawa mereka memadankan senarai yang berasal dari sumber yang berbeza. Apabila senarai datang dari pelbagai sumber, selalu ada perbezaan yang menyukarkan senarai tersebut. Berikut adalah tiga contoh perkara yang boleh menjadi salah dan bagaimana membetulkannya.
-
Satu senarai mempunyai tanda sempang dan senarai yang lain tidak. Gunakan
=SUBSTITUTE()fungsi untuk menghilangkan tanda sempang. Kali pertama anda mencuba VLOOKUP, anda akan mendapat kesilapan N / A.Untuk membuang tanda sempang dengan formula, gunakan formula PENGGANTI. Gunakan 3 hujah. Argumen pertama ialah sel yang mengandungi nilai. Hujah seterusnya adalah teks yang ingin anda ubah. Hujah terakhir adalah teks pengganti. Dalam kes ini, anda mahu menukar tanda hubung menjadi sia-sia, jadi rumusnya adalah
=SUBSTITUTE(A4,"-","").Anda boleh membungkus fungsi itu dalam VLOOKUP untuk mendapatkan keterangan.
-
Yang ini halus, tetapi sangat biasa. Satu senarai mempunyai ruang kosong tertinggal selepas entri. Gunakan = TRIM () untuk membuang lebihan ruang. Apabila anda mula-mula memasukkan formula, anda dapati semua jawapan adalah kesalahan N / A. Anda pasti tahu bahawa nilainya ada dalam senarai dan semuanya kelihatan OK dengan formula.
Satu perkara standard yang perlu diperiksa adalah berpindah ke sel dengan nilai carian. Tekan F2 untuk meletakkan sel dalam mod Edit. Setelah berada dalam mod edit, anda dapat melihat bahawa kursor terletak satu jarak dari huruf akhir. Ini menunjukkan bahawa terdapat ruang ketinggalan dalam entri.
Untuk menyelesaikan masalah, gunakan fungsi TRIM.
=TRIM(D4)akan membuang ruang depan, ruang belakang, dan akan menggantikan ruang berkembar dalaman dengan satu ruang. Dalam kes ini, TRIM berfungsi dengan sempurna untuk menghilangkan ruang belakang.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)adalah formula. -
Saya menyebutkan tip bonus dalam nota persembahan: bagaimana mengganti hasil # N / A kerana nilai yang hilang dengan kosong. Sekiranya nilai carian anda tidak terdapat dalam jadual carian, VLOOKUP akan mengembalikan kesalahan N / A.
Rumus ini menggunakan
=ISNA()fungsi untuk mengesan jika hasil formula tersebut adalah kesalahan N / A. Sekiranya anda mendapat ralat, argumen ke-2 dalam fungsi IF akan memberitahu Excel untuk memasukkan teks yang anda mahukan.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP membolehkan anda menjimatkan masa ketika memadankan senarai data. Luangkan masa untuk mempelajari penggunaan asas dan anda akan dapat melakukan tugas yang jauh lebih hebat di Excel.
