Dalam dunia yang didorong oleh data ini, seseorang memerlukan pelbagai alat untuk mengurus data. Data dalam masa nyata sangat besar dan mengambil butiran mengenai sebilangan data tertentu pastinya menjadi tugas yang melelahkan tetapi dengan VLOOKUP di Excel , tugas ini dapat dicapai dengan satu baris perintah. Dalam artikel ini, anda akan belajar tentang salah satu yang penting Fungsi Excel iaitu Fungsi VLOOKUP.
Sebelum meneruskan, mari kita lihat dengan cepat semua topik yang dibincangkan di sini:
- Apakah VLOOKUP dalam Excel?
- Bagaimanakah ia berfungsi?
- Padanan Tepat
- Padanan anggaran
- Perlawanan Pertama
- Kepekaan Kes
- Kesalahan
- Pencarian Dua Hala
- Menggunakan Wildcard
- Jadual Carian Pelbagai
Apakah VLOOKUP dalam Excel?
Di Excel, VLOOKUP adalah fungsi terbina dalam yang digunakan untuk mencari dan mengambil data tertentu dari lembaran excel. V bermaksud Vertikal dan untuk menggunakan fungsi VLOOKUP di Excel, data mesti disusun secara menegak. Fungsi ini sangat berguna apabila anda mempunyai sejumlah besar data dan hampir mustahil untuk mencari beberapa data tertentu secara manual.
Bagaimanakah ia berfungsi?
Fungsi VLOOKUP mengambil nilai iaitu nilai pencarian dan mula mencarinya di lajur paling kiri. Apabila terjadinya nilai carian pertama, ia mula bergerak tepat di baris itu dan mengembalikan nilai dari lajur yang anda tentukan. Fungsi ini dapat digunakan untuk mengembalikan pencocokan tepat dan hampir (Padanan lalai adalah padanan anggaran).
Sintaks:
Sintaks fungsi ini adalah seperti berikut:
VLOOKUP (look__alue, table_array, col_index_num, [range_lookup])
di mana,
- look__value adalah nilai yang harus diperhatikan pada lajur pertama jadual yang diberikan
- jadual_indeks adalah jadual dari mana data akan diambil
- col_index_num adalah lajur dari mana nilainya diambil
- jangkauan_kawasan adalah nilai logik yang menentukan sama ada nilai carian mestilah padanan sempurna atau padanan anggaran ( BENAR akan mencari padanan terdekat SALAH memeriksa padanan tepat)
Padanan Tepat:
Apabila anda mahu fungsi VLOOKUP mencari padanan tepat dengan nilai carian, anda harus menetapkannya jangkauan_kawasan nilai kepada SALAH. Lihat contoh berikut yang merupakan jadual yang terdiri daripada maklumat pekerja:
Sekiranya anda ingin mencari penunjukan mana-mana pekerja ini, anda boleh melakukan seperti berikut:
- Pilih sel di mana anda ingin memaparkan output dan kemudian ketikkan tanda “=”
- Gunakan fungsi VLOOKUP dan sediakan look__value (Di sini, ia akan menjadi ID pekerja)
- Kemudian masukkan parameter lain iaitu table_array , col_index_num dan tetapkan jangkauan_kawasan nilai kepada SALAH
- Oleh itu, fungsi dan parameternya adalah: = VLOOKUP (104, A1: D8, 3, FALSE)
Fungsi VLOOKUP mula mencari ID pekerja 104 dan kemudian bergerak ke arah kanan di barisan di mana nilainya dijumpai. Ia berterusan sehingga col_index_num dan mengembalikan nilai yang ada pada kedudukan itu.
Padanan anggaran:
Ciri fungsi VLOOKUP ini membolehkan anda mendapatkan semula nilai walaupun anda tidak mempunyai padanan tepat untuk nilai loopup_value. Seperti yang telah disebutkan sebelumnya, untuk membuat VLOOKUP mencari perkiraan yang hampir sama, Anda harus menetapkannya jangkauan_kawasan nilai menjadi BENAR. Lihat contoh berikut di mana tanda dipetakan bersama dengan gred dan kelas yang menjadi milik mereka.
- Sama seperti yang anda lakukan untuk pertandingan yang tepat, ikuti langkah yang sama
- Sebagai ganti nilai range_lookup, gunakan TRUE dan bukannya FALSE
- Oleh itu, fungsi bersama parameternya adalah: = VLOOKUP (55, A12: C15, 3, TRUE)
Dalam jadual yang disusun dalam urutan menaik, VLOOKUP mula mencari perkiraan yang hampir dan berhenti pada nilai terbesar berikutnya yang lebih kecil daripada nilai carian yang telah anda masukkan. Kemudian bergerak tepat di baris itu dan mengembalikan nilai dari lajur yang ditentukan. Dalam contoh di atas, nilai carian adalah 55 dan nilai carian terbesar seterusnya di lajur pertama ialah 40. Oleh itu, output adalah Kelas Kedua.
Perlawanan Pertama:
Sekiranya anda mempunyai jadual yang terdiri daripada beberapa nilai pencarian, VLOOKUP berhenti pada perlawanan pertama dan mengambil nilai dari baris tersebut di lajur yang ditentukan.
Lihat gambar di bawah:
ID 105 diulang dan apabila nilai pencarian ditentukan sebagai 105, VLOOKUP telah mengembalikan nilai dari baris yang mempunyai pertama kali nilai pencarian.
Kepekaan Kes:
Fungsi VLOOKUP tidak peka huruf besar kecil. Sekiranya anda mempunyai nilai pencarian yang dalam huruf besar dan nilai yang ada dalam jadual kecil, VLOOKUP masih akan mengambil nilai dari baris di mana nilainya ada. Lihat gambar di bawah:
Seperti yang anda lihat, nilai yang saya tentukan sebagai parameter adalah 'RAFA' sedangkan nilai yang ada dalam tabel adalah 'Rafa' tetapi VLOOKUP masih mengembalikan nilai yang ditentukan. Sekiranya anda mempunyai padanan yang tepat walaupun dengan casing tersebut, VLOOKUP tetap akan mengembalikan pertandingan pertama dari nilai pencarian tanpa mengira kes yang digunakan. Lihat gambar di bawah:
Kesalahan:
Adalah wajar untuk menghadapi kesalahan setiap kali kita menggunakan fungsi. Begitu juga, anda boleh menghadapi ralat ketika menggunakan fungsi VLOOKUP dan beberapa kesalahan biasa adalah:
- #NAMA
- # N / A
- #REF
- # NILAI
Ralat #NAME:
Kesalahan ini pada dasarnya adalah untuk memberitahu anda bahawa anda telah melakukan beberapa kesalahan dalam sintaks. Untuk mengelakkan kesalahan sintaksis, lebih baik menggunakan Function Wizard yang disediakan oleh Excel untuk setiap fungsi. Fungsi Wizard membantu anda dengan maklumat mengenai setiap parameter dan jenis nilai yang perlu anda masukkan. Lihat gambar di bawah:
Seperti yang anda lihat, Function Wizard memberitahu anda untuk memasukkan sebarang jenis nilai sebagai pengganti parameter lookup_value dan juga memberikan penerangan ringkas mengenai perkara tersebut. Begitu juga, apabila anda memilih parameter lain, anda juga akan melihat maklumat mengenai parameter tersebut.
Ralat # N / A:
Kesalahan ini dikembalikan sekiranya tidak ada yang sesuai untuk nilai carian yang diberikan. Sebagai contoh, jika saya memasukkan 'AFA' dan bukan 'RAFA', saya akan mendapat ralat # N / A.
Untuk menentukan beberapa mesej ralat untuk dua kesalahan di atas, anda dapat memanfaatkan fungsi IFNA. Sebagai contoh:
Ralat #REF:
Kesalahan ini dihadapi semasa anda merujuk pada lajur yang tidak terdapat dalam jadual.
tukar rentetan kepada tarikh di java
Ralat # NILAI:
Kesalahan ini dihadapi semasa anda meletakkan nilai yang salah pada parameter atau ketinggalan beberapa parameter wajib.
Pencarian Dua Hala:
Pencarian dua hala merujuk pada pengambilan nilai dari jadual dua dimensi dari mana-mana sel jadual yang dirujuk. Untuk melakukan carian dua hala menggunakan VLOOKUP, anda perlu menggunakan fungsi MATCH bersamanya.
Sintaks MATCH adalah seperti berikut:
PERTANDINGAN (look__value, look__array, match_type)
- look__value adalah nilai yang hendak dicari
- cari_array adalah julat sel yang terdiri daripada nilai carian
- padanan_jenis boleh berupa nombor iaitu 0, 1 atau -1 mewakili padanan tepat, kurang daripada dan lebih besar daripada masing-masing
Daripada menggunakan nilai kod keras dengan VLOOKUP, anda boleh menjadikannya bypass dinamik dalam rujukan sel. Pertimbangkan contoh berikut:
Seperti yang anda lihat pada gambar di atas, fungsi VLOOKUP mengambil rujukan sel sebagai F6 untuk nilai pencarian dan nilai indeks lajur ditentukan oleh fungsi MATCH. Apabila anda membuat perubahan pada salah satu dari nilai-nilai ini, output juga akan berubah sesuai. Lihat gambar di bawah ini di mana saya telah mengubah nilai yang ada di F6 dari Chris ke Leo dan outputnya juga telah diperbaharui dengan sewajarnya:
Sekiranya saya mengubah nilai G5, atau kedua-dua F6 dan G5, formula ini akan berfungsi dengan tepat untuk menunjukkan hasil yang sesuai.
Anda juga boleh membuat senarai drop-down untuk menjadikan tugas mengubah nilai sangat berguna. Dalam contoh di atas, ini harus dilakukan pada F6 dan G5. Inilah cara anda boleh membuat senarai lungsur:
- Pilih Data dari tab pita
- Dari kumpulan Alat Data, pilih Pengesahan Data
- Buka panel Tetapan dan dari Benarkan, pilih Senarai
- Tentukan susunan senarai sumber
Inilah penampilannya setelah anda membuat senarai drop-down:
Menggunakan Kad Liar:
Sekiranya anda tidak mengetahui nilai carian yang tepat tetapi hanya sebahagian daripadanya, anda boleh menggunakan wildcard. Di Excel, simbol '*' mewakili watak wildcard. Simbol ini memberitahu Excel bahawa urutan yang muncul sebelum, sesudah atau di antara mesti dicari dan mungkin terdapat sebilangan besar watak sebelum atau sesudahnya. Sebagai contoh, dalam jadual yang telah saya buat, jika memasukkan 'erg' bersama dengan kad liar di kedua sisi, VLOOKUP akan mengembalikan output untuk 'Sergio' seperti gambar di bawah:
Jadual Carian Pelbagai:
Sekiranya anda mempunyai beberapa jadual pencarian, anda dapat menggunakan fungsi IF bersamanya untuk melihat salah satu jadual berdasarkan beberapa keadaan tertentu. Sebagai contoh, jika ada jadual yang menyimpan data dari dua pasar raya dan anda perlu mengetahui keuntungan yang dihasilkan oleh masing-masing berdasarkan penjualan, anda boleh melakukan seperti berikut:
Buat jadual utama seperti berikut:
Kemudian buat dua jadual dari mana keuntungan harus diambil.
Setelah ini selesai, buat julat Dinamakan untuk setiap jadual yang baru dibuat. Untuk membuat julat bernama, ikuti langkah-langkah yang diberikan di bawah:
- Pilih jadual keseluruhan jadual yang ingin anda berikan nama
- Dari tab pita, pilih Rumus dan kemudian dari kumpulan Nama Ditentukan, pilih Tentukan Nama
- Anda akan melihat kotak dialog berikut
- Berikan apa sahaja nama pilihan anda
- Klik OK
Setelah ini dilakukan untuk kedua-dua jadual, anda boleh menggunakan julat bernama ini dalam fungsi IF seperti berikut:
Seperti yang anda lihat, VLOOKUP telah mengembalikan nilai yang sesuai untuk mengisi lajur Untung mengikut pasar raya tempat mereka berada. Daripada menulis formula di setiap sel lajur Keuntungan, saya hanya mempunyai menyalin formula untuk menjimatkan masa dan tenaga.
Ini membawa kita ke akhir artikel ini mengenai VLOOKUP di Excel. Saya harap anda jelas dengan semua yang telah dikongsi dengan anda. Pastikan anda berlatih sebanyak mungkin dan kembalikan pengalaman anda.
Ada soalan untuk kami? Sila sebutkan di bahagian komen blog 'VLOOKUP in Excel' ini dan kami akan menghubungi anda secepat mungkin.
Untuk mendapatkan pengetahuan mendalam mengenai teknologi terkini dan pelbagai aplikasinya, anda boleh mendaftar secara langsung dengan sokongan 24/7 dan akses seumur hidup.