Cari Pelbagai Data dengan Excel VLOOKUP

Dengan menggabungkan ExcelVLOOKUP fungsi denganKOLUM fungsi kita boleh membuat formula pencarian yang membolehkan anda untuk mengembalikan beberapa nilai dari satu baris pangkalan data atau jadual data.

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.

Rumusan pencarian memerlukan bahawa KOLUM fungsi bersarang di dalamnya VLOOKUP. Sarang fungsi melibatkan memasuki fungsi kedua sebagai salah satu hujah untuk fungsi pertama.

Dalam tutorial ini, KOLUM fungsi akan dimasukkan sebagai indeks kolum nombor hujah untuk VLOOKUP. Langkah terakhir dalam tutorial melibatkan menyalin formula carian kepada lajur tambahan untuk mendapatkan nilai tambahan untuk bahagian yang dipilih.

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 kepada G1.
• Masukkan julat kedua ke dalam sel D4 kepada G10.

Kriteria carian dan formula carian yang dibuat semasa tutorial ini akan dimasukkan 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.

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.

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

1. Sorotan sel D5 kepada G10 dalam lembaran kerja untuk memilihnya.
2. Klik pada Kotak Nama terletak di atas lajur A.
3. Taipkan Jadual ke dalam Kotak Nama.
4. Tekan butang Masukkan kekunci pada papan kekunci.
5. Sel D5 kepada G10 kini mempunyai nama julat Jadual. Kami akan menggunakan nama untuk VLOOKUP pelbagai jadual hujah 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 VLOOKUP kotak dialog. 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 formula carian dua dimensi akan dipaparkan.
2. Klik pada Tab formula daripada riben.
3. Klik pada Lookup & Rujukan pilihan dalam riben untuk membuka fungsi drop-down.
4. Klik pada VLOOKUP dalam senarai untuk membuka kotak dialog fungsi.

05 dari 10

Memasuki Argumen Nilai Lookup

Biasanya, lookup nilai sepadan dengan medan data dalam lajur pertama jadual data. Dalam contoh kami, lookup nilai merujuk kepada nama bahagian perkakasan yang kami ingin cari maklumat. Jenis data yang dibenarkan untuk lookup nilai adalah data teks, nilai logik, nombor, dan rujukan sel.

Rujukan Sel Absolute

Biasanya, apabila formula disalin di Excel, rujukan sel berubah untuk mencerminkan lokasi baru mereka. Jika ini berlaku, D2, rujukan sel untuk lookup nilai, akan berubah apabila formula disalin mencipta kesilapan dalam sel F2 dan G2.

Rujukan sel mutlak tidak berubah apabila formula disalin.

Untuk mengelakkan kesilapan, kami akan menukar rujukan sel D2 ke dalam rujukan sel mutlak. Rujukan sel mutlak dicipta dengan menekan F4 kekunci pada papan kekunci. Melakukannya menambah tanda dolar di sekitar rujukan sel seperti $ D $ 2.

1. Klik pada lookup_value baris dalam kotak dialog.
2. Klik pada sel D2 untuk menambah rujukan sel ini kepada lookup_value baris - ini adalah sel di mana kita akan menaip nama bahagian yang kita cari maklumat
3. Tanpa mengalihkan titik pemasukan, tekan F4 kekunci pada papan kekunci untuk menukar D2 ke dalam rujukan sel mutlak $ D $ 2.
4. Tinggalkan VLOOKUP kotak dialog fungsi terbuka 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 argumen nilai pencarian (langkah sebelumnya dalam tutorial), manakala lajur kedua akan dicari oleh formula carian untuk mencari maklumat yang kami tentukan.

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

1. Klik pada table_array baris dalam kotak dialog.
2. Taipkan Jadual untuk memasukkan nama jarak untuk hujah ini.
3. Tinggalkan VLOOKUP kotak dialog fungsi terbuka 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 nombor indeks lajur hujah.

Dalam contoh ini, bagaimanapun, kami mempunyai tiga lajur yang kami ingin memulangkan data dari supaya kami memerlukan cara untuk mengubahnya dengan mudah nombor indeks lajur tanpa menyunting formula carian kami - ini adalah di mana KOLUM fungsi masuk.

Dengan memasukkannya sebagai nombor indeks lajur hujah, ia akan berubah apabila formula carian disalin dari sel D2 kepada sel E2 dan F2 kemudian dalam tutorial.

Fungsi Sarang

The KOLUM Oleh itu, fungsi berfungsi sebagai VLOOKUP's nombor indeks lajur hujah; ini dicapai dengan bersarang KOLUM fungsi dalam VLOOKUP di dalam Col_index_num baris kotak dialog.

Memasuki Fungsi COLUMN Secara Manual

Apabila fungsi bersarang, Excel tidak membenarkan kami membuka kotak dialog fungsi kedua untuk memasuki hujah-hujahnya. The KOLUM Oleh itu, fungsi mesti dimasukkan secara manual di dalam Col_index_num talian. The KOLUM fungsi hanya mempunyai satu hujah - yang Rujukan hujah yang merupakan rujukan sel.

Memilih Argumen Rujukan Fungsi COLUMN

The KOLUM tugas fungsi adalah untuk mengembalikan bilangan lajur yang diberikan sebagai Rujukan hujah. Dalam erti kata lain, ia menukar huruf lajur ke dalam nombor dengan lajur A menjadi lajur pertama, lajur B yang kedua dan sebagainya.

Oleh kerana bidang data pertama, apa yang kami mahu dikembalikan ialah harga item, yang berada dalam lajur dua jadual data. Kita boleh memilih rujukan sel untuk sebarang sel lajur B sebagai Rujukan Argumen untuk mendapatkan nombor itu 2 untuk Col_index_num hujah.

1. Di dalam VLOOKUP kotak dialog fungsi, klik pada Col_index_num talian.
2. Taipkan nama fungsi kolum diikuti dengan kurungan bulat terbuka.
3. Klik pada sel B1 dalam lembaran kerja untuk memasukkan rujukan sel itu sebagai Rujukan hujah.
4. Jenis A tutup pendakap bulat untuk melengkapkan KOLUM fungsi
5. Tinggalkan VLOOKUP kotak dialog fungsi terbuka untuk langkah seterusnya dalam tutorial.

08 dari 10

Memasuki Argumen Penjejakan Penduduk VLOOKUP

VLOOKUP's Range_lookup hujah adalah nilai logik (BENAR atau SALAH) yang menunjukkan sama ada anda mahu VLOOKUP untuk mencari padanan tepat atau anggaran untuk Lookup_value.

• Jika BENAR atau jika hujah ini diabaikan: VLOOKUP mengembalikan sama ada padanan tepat kepada Lookup_valueAtau, jika perlawanan yang tepat tidak dijumpai, VLOOKUP mengembalikan nilai terbesar seterusnya. Untuk formula untuk melakukan ini, data dalam lajur pertama Table_array mestilah disusun mengikut urutan menaik.
• Jika SALAH: VLOOKUP hanya akan menggunakan padanan tepat kepada Lookup_value. Jika terdapat dua atau lebih nilai dalam lajur pertama Table_array yang sepadan dengan nilai carian, nilai pertama yang dijumpai digunakan. Jika perlawanan yang tepat tidak dijumpai, a # N / A ralat dikembalikan.

Dalam tutorial ini, kerana kami mencari maklumat khusus mengenai item perkakasan tertentu, kami akan menetapkannya Range_lookup sama dengan Salah.

1. Klik pada Range_lookup baris dalam kotak dialog.
2. Taip perkataan Salah dalam baris ini untuk menunjukkan bahawa kita mahu VLOOKUP untuk mengembalikan padanan tepat untuk data yang kami cari.
3. Klik okey untuk melengkapkan formula carian dan kotak dialog yang rapat.
4. Memandangkan kami masih belum memasuki kriteria carian ke dalam sel D2 a # N / A ralat akan hadir dalam sel E2.
5. Kesilapan ini akan diperbetulkan apabila kami akan menambah kriteria carian dalam langkah terakhir tutorial.

09 dari 10

Menyalin Formula Lookup

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, kita mahu ia mengambil data dari lajur 2, 3, dan 4 daripada jadual data - iaitu harga, nombor bahagian, dan nama pembekal apabila kami memasukkan nama separa sebagai Lookup_value.

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

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

Selain itu, Excel tidak mengubah rujukan sel mutlak $ D $ 2 dan julat yang dinamakan Jadual sebagai formula disalin.

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

1. Klik pada sel E2, di mana formula carian dicari, menjadikannya sel aktif.
2. Letakkan penunjuk tetikus lebih persegi hitam di penjuru kanan sebelah bawah - penunjuk akan berubah menjadi tanda tambah untuk menandakan pemfailan penuh.
3. Klik butang tetikus kiri dan seret pemegang pengisi menyeberang ke sel G2.
4. Siaran butang tetikus dan sel F3 harus mengandungi formula pencarian dua dimensi.
5. Sekiranya dilakukan dengan betul, sel F2 dan G2 sekarang juga harus mengandungi # N / A kesilapan yang ada di dalamnya 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 anda ingin mengambil semula Lookup_value sel (D2) dan tekan Masukkan kekunci pada papan kekunci.

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

1. Klik pada sel D2 dalam lembaran kerja.
2. Taipkan Widget ke dalam sel D2 dan tekan Masukkan kekunci pada papan kekunci.
3. Maklumat berikut harus dipaparkan dalam sel E2 kepada G2.
   1. E2: $ 14.76 - harga widget
   2. F2: PN-98769 - nombor bahagian untuk widget
   3. G2: Widget Inc. - nama pembekal untuk widget
4. Uji VLOOKUP rumus array lagi dengan menaip nama bahagian-bahagian lain ke dalam sel D2 dan memerhatikan keputusan dalam sel E2 kepada G2