Dengan menggabungkan fungsi VLOOKUP Excel dengan fungsi COLUMN anda boleh mencipta formula carian yang mengembalikan berbilang nilai daripada satu baris pangkalan data atau jadual data. Ketahui cara membuat formula carian yang mengembalikan berbilang nilai daripada satu rekod data.
Arahan dalam artikel ini digunakan untuk Excel 2019, 2016, 2013, 2010; dan Excel untuk Microsoft 365.
Barisan Bawah
Formula carian memerlukan fungsi COLUMN untuk bersarang di dalam VLOOKUP. Menyarang fungsi melibatkan memasukkan fungsi kedua sebagai salah satu argumen untuk fungsi pertama.
Masukkan Data Tutorial
Dalam tutorial ini, fungsi COLUMN dimasukkan sebagai argumen nombor indeks lajur untuk VLOOKUP. Langkah terakhir dalam tutorial melibatkan penyalinan formula carian ke lajur tambahan untuk mendapatkan nilai tambahan bagi bahagian yang dipilih.
Langkah pertama dalam tutorial ini ialah memasukkan data ke dalam lembaran kerja Excel. Untuk mengikuti langkah dalam tutorial ini, masukkan data yang ditunjukkan dalam imej di bawah ke dalam sel berikut:
- Masukkan julat teratas data ke dalam sel D1 hingga G1.
- Masukkan julat kedua ke dalam sel D4 hingga G10.
Kriteria carian dan formula carian yang dibuat dalam tutorial ini dimasukkan dalam baris 2 lembaran kerja.
Tutorial ini tidak termasuk pemformatan Excel asas yang ditunjukkan dalam imej, tetapi ini tidak menjejaskan cara formula carian berfungsi.
Buat Julat Dinamakan untuk Jadual Data
Julat bernama ialah cara mudah untuk merujuk kepada julat data dalam formula. Daripada menaip rujukan sel untuk data, taip nama julat.
Kelebihan kedua menggunakan julat bernama ialah rujukan sel untuk julat ini tidak pernah berubah walaupun formula disalin ke sel lain dalam lembaran kerja. Nama julat ialah alternatif kepada menggunakan rujukan sel mutlak untuk mengelakkan ralat semasa menyalin formula.
Nama julat tidak termasuk tajuk atau nama medan untuk data (seperti yang ditunjukkan dalam baris 4), hanya data.
-
Serlahkan sel D5 hingga G10 dalam lembaran kerja.
Image -
Letakkan kursor dalam Kotak Nama yang terletak di atas lajur A, taip Jadual, kemudian tekan Enter. Sel D5 hingga G10 mempunyai nama julat Jadual.
Image - Nama julat untuk argumen tatasusunan jadual VLOOKUP digunakan kemudian dalam tutorial ini.
Buka Kotak Dialog VLOOKUP
Walaupun boleh menaip formula carian terus ke dalam sel dalam lembaran kerja, ramai orang mendapati sukar untuk memastikan sintaks lurus - terutamanya untuk formula yang kompleks seperti yang digunakan dalam tutorial ini.
Sebagai alternatif, gunakan kotak dialog Argumen Fungsi VLOOKUP. Hampir semua fungsi Excel mempunyai kotak dialog di mana setiap argumen fungsi dimasukkan pada baris yang berasingan.
-
Pilih sel E2 lembaran kerja. Ini ialah lokasi di mana hasil formula carian dua dimensi akan dipaparkan.
Image -
Pada reben, pergi ke tab Formula dan pilih Cari & Rujukan.
Image -
Pilih VLOOKUP untuk membuka kotak dialog Function Arguments.
Image - Kotak dialog Argumen Fungsi ialah tempat memasukkan parameter fungsi VLOOKUP.
Masukkan Argumen Nilai Carian
Biasanya, nilai carian sepadan dengan medan data dalam lajur pertama jadual data. Dalam contoh ini, nilai carian merujuk kepada nama bahagian yang anda ingin cari maklumat. Jenis data yang dibenarkan untuk nilai carian ialah data teks, nilai logik, nombor dan rujukan sel.
Rujukan Sel Mutlak
Apabila formula disalin dalam Excel, rujukan sel berubah untuk mencerminkan lokasi baharu. Jika ini berlaku, D2, rujukan sel untuk nilai carian, menukar dan mencipta ralat dalam sel F2 dan G2.
Rujukan sel mutlak tidak berubah apabila formula disalin.
Untuk mengelakkan ralat, tukar rujukan sel D2 menjadi rujukan sel mutlak. Untuk membuat rujukan sel mutlak, tekan kekunci F4. Ini menambahkan tanda dolar di sekeliling rujukan sel seperti $D$2.
-
Dalam kotak dialog Function Arguments, letakkan kursor dalam kotak teks lookup_value. Kemudian, dalam lembaran kerja, pilih sel D2 untuk menambah rujukan sel ini kepada nilai_carian. Sel D2 ialah tempat nama bahagian akan dimasukkan.
Image -
Tanpa menggerakkan titik sisipan, tekan kekunci F4 untuk menukar D2 kepada rujukan sel mutlak $D$2.
Image - Biarkan kotak dialog fungsi VLOOKUP terbuka untuk langkah seterusnya dalam tutorial.
Masukkan Argumen Susunan Jadual
Susun atur jadual ialah jadual data yang dicari oleh formula carian untuk mencari maklumat yang anda inginkan. Tatasusunan jadual mesti mengandungi sekurang-kurangnya dua lajur data.
Lajur pertama mengandungi argumen nilai carian (yang telah disediakan dalam bahagian sebelumnya), manakala lajur kedua dicari oleh formula carian untuk mencari maklumat yang anda tentukan.
Argumen tatasusunan jadual mesti dimasukkan sama ada sebagai julat yang mengandungi rujukan sel untuk jadual data atau sebagai nama julat.
Untuk menambah jadual data pada fungsi VLOOKUP, letakkan kursor dalam kotak teks table_array dalam kotak dialog dan taipkan Tableuntuk memasukkan nama julat untuk hujah ini.
Sarang Fungsi COLUMN
Biasanya, VLOOKUP hanya mengembalikan data daripada satu lajur jadual data. Lajur ini ditetapkan oleh argumen nombor indeks lajur. Dalam contoh ini, bagaimanapun, terdapat tiga lajur dan nombor indeks lajur perlu ditukar tanpa mengedit formula carian. Untuk mencapai ini, sarangkan fungsi COLUMN di dalam fungsi VLOOKUP sebagai argumen Col_index_num.
Apabila fungsi bersarang, Excel tidak membuka kotak dialog fungsi kedua untuk memasukkan argumennya. Fungsi COLUMN mesti dimasukkan secara manual. Fungsi COLUMN hanya mempunyai satu hujah, hujah Rujukan, yang merupakan rujukan sel.
Fungsi COLUMN mengembalikan nombor lajur yang disediakan sebagai hujah Rujukan. Ia menukar huruf lajur kepada nombor.
Untuk mencari harga item, gunakan data dalam lajur 2 jadual data. Contoh ini menggunakan lajur B sebagai Rujukan untuk memasukkan 2 ke dalam argumen Col_index_num.
-
Dalam kotak dialog Function Arguments, letakkan kursor dalam Col_index_num kotak teks dan taipkan COLUMN(. (Pastikan anda memasukkan kurungan bulat terbuka.)
Image -
Dalam lembaran kerja, pilih sel B1 untuk memasukkan rujukan sel tersebut sebagai hujah Rujukan.
Image - Taip kurung bulat penutup untuk melengkapkan fungsi COLUMN.
Masukkan Argumen Carian Julat VLOOKUP
Argumen Range_lookup VLOOKUP ialah nilai logik (BENAR atau SALAH) yang menunjukkan sama ada VLOOKUP harus mencari padanan tepat atau anggaran dengan nilai_carian.
- TRUE atau Ditinggalkan: VLOOKUP mengembalikan padanan hampir kepada Lookup_value. Jika padanan tepat tidak ditemui, VLOOKUP mengembalikan nilai terbesar seterusnya. Data dalam lajur pertama Table_array mesti diisih dalam tertib menaik.
- FALSE: VLOOKUP 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 ditemui digunakan. Jika padanan tepat tidak ditemui, ralat N/A dikembalikan.
Dalam tutorial ini, maklumat khusus tentang item perkakasan tertentu akan dicari, jadi Range_lookup ditetapkan kepada FALSE.
Dalam kotak dialog Function Arguments, letakkan kursor dalam kotak teks Range_lookup dan taip False untuk memberitahu VLOOKUP supaya mengembalikan padanan tepat bagi data.
Pilih OK untuk melengkapkan formula carian dan menutup kotak dialog. Sel E2 akan mengandungi ralat N/A kerana kriteria carian belum dimasukkan ke dalam sel D2. Ralat ini bersifat sementara. Ia akan diperbetulkan apabila kriteria carian ditambahkan pada langkah terakhir tutorial ini.
Salin Formula Carian dan Masukkan Kriteria
Formula carian mendapatkan semula data daripada berbilang lajur jadual data pada satu masa. Untuk melakukan ini, formula carian mesti berada dalam semua medan yang anda inginkan maklumat.
Untuk mendapatkan semula data daripada lajur 2, 3 dan 4 jadual data (harga, nombor bahagian dan nama pembekal), masukkan nama separa sebagai nilai_carian.
Memandangkan data dibentangkan dalam corak biasa dalam lembaran kerja, salin formula carian dalam sel E2 kepada sel F2 dan G2 Semasa formula disalin, Excel mengemas kini rujukan sel relatif dalam fungsi COLUMN (sel B1) untuk mencerminkan lokasi baharu formula. Excel tidak mengubah rujukan sel mutlak (seperti $D$2) dan julat bernama (Jadual) semasa formula disalin.
Terdapat lebih daripada satu cara untuk menyalin data dalam Excel, tetapi cara paling mudah ialah menggunakan Pemegang Isian.
-
Pilih sel E2, di mana formula carian terletak, untuk menjadikannya sel aktif.
Image -
Seret pemegang isian ke sel G2. Sel F2 dan G2 memaparkan ralat N/A yang terdapat dalam sel E2.
Image -
Untuk menggunakan formula carian untuk mendapatkan maklumat daripada jadual data, dalam lembaran kerja pilih sel D2, taip Widget dan tekan Masukkan.
Image Maklumat berikut dipaparkan dalam sel E2 hingga G2.
- E2: $14.76 - harga widget
- F2: PN-98769 - nombor bahagian untuk widget
- G2: Widgets Inc. - nama pembekal untuk widget
-
Untuk menguji formula tatasusunan VLOOKUP, taipkan nama bahagian lain ke dalam sel D2 dan perhatikan keputusan dalam sel E2 hingga G2.
Image - Setiap sel yang mengandungi formula carian mengandungi sekeping data yang berbeza tentang item perkakasan yang anda cari.
Fungsi VLOOKUP dengan fungsi bersarang seperti COLUMN menyediakan kaedah yang berkuasa untuk mencari data di dalam jadual, menggunakan data lain sebagai rujukan carian.
