Cari Pelbagai Data dengan Excel VLOOKUP

Dengan menggabungkan fungsi VLOOKUP Excel dengan fungsi COLUMN kita boleh membuat formula pencarian yang membolehkan anda memulangkan beberapa nilai dari satu baris pangkalan data atau jadual data.

Dalam contoh yang ditunjukkan dalam imej di atas, formula carian menjadikannya mudah untuk mengembalikan semua nilai - seperti harga, nombor bahagian, dan pembekal - yang berkaitan dengan pelbagai keping perkakasan.

01 dari 10

Pulangkan Berbilang Nilai dengan Excel VLOOKUP

Mengikuti langkah-langkah yang disenaraikan di bawah mencipta formula carian yang dilihat pada imej di atas yang akan mengembalikan beberapa nilai dari rekod data tunggal.

Formula pencarian memerlukan fungsi COLUMN bersarang di dalam VLOOKUP.

Sarang fungsi melibatkan memasuki fungsi kedua sebagai salah satu hujah untuk fungsi pertama.

Dalam tutorial ini, fungsi COLUMN akan dimasukkan sebagai argumen nombor indeks kolom untuk VLOOKUP.

Langkah terakhir dalam tutorial melibatkan menyalin formula carian kepada lajur tambahan untuk mendapatkan nilai tambahan untuk bahagian yang dipilih.

Kandungan Tutorial

• Memasuki Data Tutorial
• Mewujudkan Julian Dinamakan untuk Jadual Data
• Memulakan Fungsi VLOOKUP
• Memasuki Argumen Nilai Lookup menggunakan Rujukan Sel Mutlak
• Memasuki Argumen Arahan Jadual
• Memasuki Fungsi COLUMN Nested
• Melengkapkan Fungsi VLOOKUP
• Menyalin Formula Lookup dengan Pemasangan Isi
• Mendapatkan Data dengan Formula Lookup

02 dari 10

Masukkan Data Tutorial

Langkah pertama dalam tutorial adalah memasukkan data ke dalam lembaran kerja Excel.

Untuk mengikuti langkah-langkah dalam tutorial masukkan data yang ditunjukkan dalam imej di atas ke dalam sel berikut.

• Masukkan julat atas data ke dalam sel D1 hingga G1
• Masukkan julat kedua ke dalam sel D4 hingga G10

Kriteria carian dan formula carian yang dibuat semasa tutorial ini akan dimasukkan ke dalam baris 2 lembaran kerja.

Tutorial tidak termasuk pemformatan yang dilihat pada imej, tetapi ini tidak akan mempengaruhi bagaimana formula carian berfungsi.

Maklumat mengenai pilihan pemformatan yang serupa dengan yang dilihat di atas boleh didapati dalam Tutorial Pemformatan Dasar Excel ini .

Langkah Tutorial

1. Masukkan data seperti yang dilihat dalam imej di atas ke dalam sel D1 hingga G10

03 dari 10

Mewujudkan Julian Dinamakan untuk Jadual Data

Julat bernama adalah cara mudah untuk merujuk kepada pelbagai data dalam formula. Daripada menaip dalam rujukan sel untuk data, anda boleh menaip nama julat.

Kelebihan kedua untuk menggunakan julat bernama adalah bahawa rujukan sel untuk julat ini tidak pernah berubah walaupun formula disalin ke sel lain dalam lembaran kerja.

Oleh itu, nama julat adalah alternatif untuk menggunakan rujukan sel mutlak untuk mengelakkan kesilapan apabila menyalin formula.

Nota: Nama julat tidak termasuk tajuk atau nama lapangan untuk data (baris 4) tetapi hanya data itu sendiri.

Langkah Tutorial

1. Sorot sel D5 ke G10 dalam lembaran kerja untuk memilihnya
2. Klik pada Peti Nama yang terletak di atas lajur A
3. Taip "Jadual" (tiada petikan) di Kotak Nama
4. Tekan kekunci ENTER pada papan kekunci
5. Sel D5 hingga G10 kini mempunyai nama julat "Jadual". Kami akan menggunakan nama untuk hujah array meja VLOOKUP kemudian dalam tutorial

04 dari 10

Membuka Peti Dialog VLOOKUP

Walaupun mungkin hanya menaip formula carian kami terus ke dalam sel dalam lembaran kerja, ramai orang merasa sukar untuk mengekalkan sintaks lurus - terutama untuk rumit yang rumit seperti yang kita gunakan dalam tutorial ini.

Satu alternatif, dalam kes ini, adalah menggunakan kotak dialog VLOOKUP. Hampir semua fungsi Excel mempunyai kotak dialog yang membolehkan anda memasukkan setiap hujah fungsi pada baris berasingan.

Langkah Tutorial

1. Klik pada sel E2 lembaran kerja - lokasi di mana hasil carian dua dimensi formula akan dipaparkan
2. Klik pada tab Rumus pada reben
3. Klik pada pilihan Lookup & Rujukan dalam reben untuk membuka senarai drop-down fungsi
4. Klik pada VLOOKUP dalam senarai untuk membuka kotak dialog fungsi

05 dari 10

Memasuki Argumen Nilai Lookup menggunakan Rujukan Sel Mutlak

Biasanya, nilai carian sepadan dengan medan data dalam lajur pertama jadual data.

Dalam contoh kami, nilai carian merujuk kepada nama bahagian perkakasan yang kami ingin cari maklumat.

Jenis data yang dibenarkan untuk nilai carian ialah:

• data teks
• nilai logik (TRUE atau FALSE sahaja)
• nombor
• rujukan sel kepada nilai dalam lembaran kerja

Dalam contoh ini, kami akan memasukkan rujukan sel ke mana nama bahagian akan ditempatkan - sel D2.

Rujukan Sel Absolute

Dalam langkah seterusnya dalam tutorial, kami akan menyalin formula carian dalam sel E2 ke sel F2 dan G2.

Biasanya, apabila formula disalin di Excel, rujukan sel berubah untuk mencerminkan lokasi baru mereka.

Jika ini berlaku, D2 - rujukan sel untuk nilai carian - akan berubah apabila formula disalin mencipta kesilapan dalam sel F2 dan G2.

Untuk mengelakkan kesilapan, kami akan menukarkan rujukan sel D2 ke dalam rujukan sel mutlak .

Rujukan sel mutlak tidak berubah apabila formula disalin.

Rujukan sel mutlak dibuat dengan menekan kekunci F4 pada papan kekunci. Melakukannya menambah tanda dolar di sekitar rujukan sel seperti $ D $ 2

Langkah Tutorial

1. Klik pada baris lookup_value dalam kotak dialog
2. Klik pada sel D2 untuk menambah rujukan sel ini ke baris lookup_value . Ini adalah sel di mana kita akan menaip nama bahagian yang kita cari maklumat
3. Tanpa mengalihkan titik pemasukan, tekan kekunci F4 pada papan kekunci untuk menukar D2 ke dalam rujukan sel mutlak $ D $ 2
4. Biarkan kotak dialog fungsi VLOOKUP dibuka untuk langkah seterusnya dalam tutorial

06 dari 10

Memasuki Argumen Arahan Jadual

Arahan meja ialah jadual data yang mencari formula pencarian untuk mencari maklumat yang kami mahukan.

Arahan meja mesti mengandungi sekurang-kurangnya dua lajur data .

• lajur pertama mengandungi hujah nilai pencarian (langkah terdahulu dalam tutorial)
• yang kedua, dan mana-mana lajur tambahan, akan dicari oleh formula carian untuk mencari maklumat yang kami nyatakan.

Hujah array jadual mesti dimasukkan sebagai julat yang mengandungi rujukan sel untuk jadual data atau sebagai nama julat .

Untuk contoh ini, kami akan menggunakan nama jarak yang dicipta dalam langkah 3 tutorial.

Langkah Tutorial

1. Klik pada baris table_array dalam kotak dialog
2. Taip "Jadual" (tiada petikan) untuk memasukkan nama jarak untuk hujah ini
3. Biarkan kotak dialog fungsi VLOOKUP dibuka untuk langkah seterusnya dalam tutorial

07 daripada 10

Sarang Fungsi COLUMN

Biasanya VLOOKUP hanya mengembalikan data dari satu lajur jadual data dan lajur ini ditetapkan oleh argumen nombor lajur lajur .

Dalam contoh ini, bagaimanapun, kami mempunyai tiga lajur yang kami ingin mengembalikan data dari supaya kami memerlukan cara untuk menukar nombor indeks lajur dengan mudah tanpa menyunting formula carian kami.

Di sinilah fungsi COLUMN masuk. Dengan memasukinya sebagai argumen nombor indeks lajur , ia akan berubah apabila formula lookup disalin dari sel D2 ke sel E2 dan F2 kemudian di dalam tutorial.

Fungsi Sarang

Oleh itu fungsi COLUMN bertindak sebagai argumen nombor indeks kolum VLOOKUP.

Ini dicapai dengan bersarang fungsi COLUMN di dalam VLOOKUP dalam bar Col_index_num kotak dialog.

Memasuki Fungsi COLUMN Secara Manual

Apabila fungsi bersarang, Excel tidak membenarkan kami membuka kotak dialog fungsi kedua untuk memasuki hujah-hujahnya.

Oleh itu, fungsi COLUMN mesti dimasukkan secara manual dalam baris Col_index_num .

Fungsi COLUMN hanya mempunyai satu hujah - hujah Rujukan yang merupakan rujukan sel.

Memilih Argumen Rujukan Fungsi COLUMN

Tugas fungsi COLUMN adalah untuk mengembalikan bilangan lajur yang diberikan sebagai hujah Rujukan .

Dalam erti kata lain, ia menukar huruf lajur ke nombor dengan lajur A menjadi lajur pertama, lajur B yang kedua dan sebagainya.

Oleh kerana medan data pertama yang kita mahu dikembalikan ialah harga item - yang berada dalam lajur dua jadual data - kita boleh memilih rujukan sel bagi mana-mana sel dalam lajur B sebagai Argumen Rujukan untuk mendapatkan nombor 2 untuk argumen Col_index_num .

Langkah Tutorial

1. Dalam kotak dialog fungsi VLOOKUP, klik pada baris Col_index_num
2. Taipkan lajur nama fungsi diikuti dengan pendakap bulat terbuka " ( "
3. Klik pada sel B1 dalam lembaran kerja untuk memasukkan rujukan sel tersebut sebagai hujah Rujukan
4. Taipkan pendakap pusingan penutup " ) " untuk melengkapkan fungsi COLUMN
5. Biarkan kotak dialog fungsi VLOOKUP dibuka untuk langkah seterusnya dalam tutorial

08 dari 10

Memasuki Argumen Penjejakan Penduduk VLOOKUP

Argumen Range_lookup VLOOKUP adalah nilai logik (TRUE atau FALSE sahaja) yang menunjukkan sama ada anda mahu VLOOKUP untuk mencari padanan tepat atau anggaran ke Lookup_value.

• Jika TRUE atau jika hujah ini diabaikan, VLOOKUP mengembalikan sama ada padanan tepat kepada Lookup_value, atau, jika padanan tepat tidak dijumpai, VLOOKUP mengembalikan nilai seterusnya seterusnya. Untuk formula untuk melakukan ini, data dalam lajur pertama Table_array mesti disusun dalam urutan menaik .
• Jika FALSE, VLOOKUP hanya akan menggunakan padanan tepat pada Lookup_value. Jika terdapat dua atau lebih nilai dalam lajur pertama Table_array yang sepadan dengan nilai carian, nilai pertama yang dijumpai digunakan. Jika padanan tepat tidak dijumpai, ralat # N / A dikembalikan.

Dalam tutorial ini, kerana kami mencari maklumat khusus tentang item perkakasan tertentu, kami akan menetapkan Range_lookup sama dengan Palsu .

Langkah Tutorial

1. Klik pada baris Range_lookup dalam kotak dialog
2. Ketik kata Palsu dalam baris ini untuk menunjukkan bahawa kami mahu VLOOKUP mengembalikan padanan tepat untuk data yang kami cari
3. Klik OK untuk melengkapkan formula carian dan kotak dialog yang rapat
4. Oleh kerana kita belum memasuki kriteria pencarian ke dalam sel D2, ralat # N / A akan ada di sel E2
5. Kesilapan ini akan diperbetulkan apabila kami akan menambah kriteria carian dalam langkah terakhir tutorial

09 dari 10

Menyalin Formula Lookup dengan Pemasangan Isi

Rumus pencarian dimaksudkan untuk mengambil data dari beberapa lajur jadual data pada satu masa.

Untuk melakukan ini, formula carian mesti berada di semua bidang yang kami mahukan maklumat.

Dalam tutorial ini, kami ingin mendapatkan data dari lajur 2, 3, dan 4 jadual data - iaitu harga, nombor bahagian, dan nama pembekal apabila kami masukkan nama bahagian sebagai Lookup_value.

Oleh kerana data dibentangkan dalam corak biasa dalam lembaran kerja , kita boleh menyalin formula carian dalam sel E2 ke sel F2 dan G2.

Oleh kerana formula disalin, Excel akan mengemaskini rujukan sel relatif dalam fungsi COLUMN (B1) untuk mencerminkan lokasi baharu formula.

Selain itu, Excel tidak mengubah rujukan sel mutlak $ D $ 2 dan jadual Julat bernama kerana formula disalin.

Terdapat lebih daripada satu cara untuk menyalin data dalam Excel, tetapi mungkin cara paling mudah adalah dengan menggunakan Pemasangan Isi .

Langkah Tutorial

1. Klik pada sel E2 - di mana formula carian dicari - untuk menjadikannya sel aktif
2. Letakkan penunjuk tetikus di atas kotak hitam di sudut kanan bawah. Penunjuk akan berubah ke tanda plus " + " - ini adalah pemegang pengisi
3. Klik butang tetikus kiri dan seret pemegang pengisi ke seluruh sel G2
4. Lepaskan butang tetikus dan F3 sel mesti mengandungi formula carian dua dimensi
5. Sekiranya dilakukan dengan betul, sel-sel F2 dan G2 seharusnya juga mengandungi ralat # N / A yang terdapat dalam sel E2

10 daripada 10

Memasuki Kriteria Carian

Setelah formula carian telah disalin ke sel yang diperlukan, ia boleh digunakan untuk mendapatkan maklumat dari jadual data.

Untuk berbuat demikian, taipkan nama item yang anda ingin dapatkan ke dalam sel Lookup_value (D2) dan tekan kekunci ENTER pada papan kekunci.

Sebaik sahaja selesai, setiap sel yang mengandungi formula carian harus mengandungi sekeping data yang berbeza mengenai item perkakasan yang anda cari.

Langkah Tutorial

1. Klik pada sel D2 dalam lembaran kerja
2. Taip Widget ke dalam sel D2 dan tekan kekunci ENTER pada papan kekunci
3. Maklumat berikut harus dipaparkan dalam sel E2 hingga G2:
   • E2 - $ 14.76 - harga widget
   • F2 - PN-98769 - nombor bahagian untuk widget
   • G2 - Widget Inc. - nama pembekal untuk widget
4. Uji formula array VLOOKUP dengan memasukkan nama bahagian-bahagian lain ke dalam sel D2 dan memerhatikan keputusan dalam sel E2 hingga G2

Jika mesej ralat seperti #REF! muncul dalam sel E2, F2, atau G2, senarai mesej ralat VLOOKUP ini boleh membantu anda menentukan di mana masalahnya terletak.