Cara untuk membetulkan ralat #N/A dalam fungsi VLOOKUP

Penting: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Topik ini menerangkan sebab paling biasa sebagai mengapa VLOOKUP anda mungkin gagal.

Petua: Merujuk kepada Kad rujukan pantas: Petua penyelesaian masalah VLOOKUP yang menerangkan sebab biasa untuk isu #NA dengan VLOOKUP dalam fail PDF (Format dokumen mudah alih) berguna. Anda boleh berkongsi PDF dengan orang lain atau mencetak bagi rujukan anda sendiri.

Masalah: Nilai carian tidak berada dalam lajur pertama dalam argumen table_array

Salah satu kekangan terbesar VLOOKUP adalah bahawa ia hanya boleh mencari nilai pada lajur yang paling kiri dalam tatasusunan Jadual. Jadi jika nilai carian anda tidak berada dalam lajur pertama tatasusunan, anda akan melihat ralat #N/A.

Dalam Jadual berikut, kami ingin mendapatkan bilangan unit yang dijual Kale.

Ralat #NA dalam VLOOKUP: nilai carian tidak berada dalam lajur pertama tatasusunan Jadual

Ralat berlaku kerana nilai carian "Kale" dalam lajur kedua (hasil) dalam A2:C10 argumen table_array , jadi Excel mencari untuknya dalam lajur A, tidak lajur B.

Penyelesaian: anda boleh cuba untuk membaiki ini dengan melaraskan VLOOKUP anda untuk rujukan lajur yang betul. Jika itu tidak mungkin, kemudian cuba bergerak lajur anda. Yang mungkin sangat praktikal jika anda mempunyai hamparan yang besar atau kompleks yang nilai sel adalah hasil pengiraan lain atau mungkin terdapat sebab logik mengapa anda hanya tidak boleh mengalihkan lajur penggunaannya. Penyelesaian adalah menggunakan gabungan fungsi INDEX dan MATCH, yang boleh mencari nilai dalam lajur tanpa mengira lokasi kedudukan dalam Jadual carian.

Gunakan INDEKS/MATCH dan bukannya VLOOKUP

INDEKS/MATCH boleh digunakan apabila VLOOKUP memenuhi keperluan anda. Kelebihan terbesar INDEKS/PADANAN adalah bahawa anda boleh mencari nilai dalam lajur pada mana-mana lokasi dalam Jadual carian. INDEKS mengembalikan nilai daripada jadual/julat tertentu berdasarkan posisinya dan MATCH mengembalikan kedudukan relatif nilai dalam Jadual/julat. Menggunakan INDEX dan MATCH bersama-sama dalam formula, anda boleh mencari nilai dalam Jadual/tatasusunan dengan menentukan nilai yang kedudukan relatif dalam Jadual/tatasusunan.

Terdapat beberapa manfaat menggunakan INDEKS/PADANAN atas VLOOKUP:

  • Dengan INDEX dan MATCH, nilai dikembalikan tidak perlu dalam lajur yang sama seperti lajur carian, berbeza dengan VLOOKUP di nilai dikembalikan mempunyai berada dalam julat yang ditentukan. Bagaimanakah perkara ini? Dengan VLOOKUP, anda perlu mengetahui nombor lajur yang mengandungi nilai dikembalikan. Walaupun ini bunyinya seperti tiada masalah besar, ia boleh menjadi rumit apabila anda mempunyai jadual besar dan mempunyai untuk mengira bilangan lajur. Juga, jika anda Tambah/Alih keluar yang dalam jadual anda, anda perlu bacakanlah dan mengemas kini argumen col_index_num. Dengan INDEX dan MATCH, tanpa mengira akan diperlukan seperti lajur carian adalah berbeza daripada lajur yang mempunyai nilai dikembalikan.

  • Dengan INDEX dan MATCH, anda boleh menentukan sama ada baris atau lajur dalam tatasusunan atau juga menentukan kedua-duanya. Ini bermakna anda boleh mencari nilai secara menegak dan mendatar.

  • INDEX dan MATCH boleh digunakan untuk mencari nilai dalam sebarang lajur. Tidak seperti yang anda boleh hanya mencari nilai dalam lajur pertama dalam Jadual VLOOKUP, INDEX dan MATCH akan berfungsi jika nilai carian anda dalam lajur pertama, terakhir atau mana-mana sahaja di antara.

  • INDEX dan MATCH menawarkan kefleksibelan membuat dinamik rujukan kepada lajur yang mengandungi nilai dikembalikan. Apakah maksudnya adalah anda boleh menambah lajur pada jadual anda dan INDEX dan MATCH akan bukan pemisah. Sebaliknya, VLOOKUP pemisah jika anda mempunyai untuk menambah lajur pada jadual sebagai rujukan kepada statik jadual yang dibuat olehnya.

  • INDEX dan MATCH menawarkan lebih kefleksibelan dengan padanan. INDEX dan MATCH boleh mencari padanan yang tepat, nilai lebih besar atau kecil daripada nilai carian. VLOOKUP hanya akan melihat paling sepadan dengan nilai (secara lalai) atau nilai yang tepat. VLOOKUP juga menganggap secara lalai Lajur pertama dalam tatasusunan Jadual Diisih mengikut abjad dan katakan jadual anda tidak menyediakan cara, VLOOKUP akan mengembalikan pertama paling sepadan dalam Jadual, yang mungkin tidak data yang anda cari.

Sintaks

Untuk membina sintaks yang INDEKS/PADANAN, anda perlu menggunakan argumen tatasusunan/rujukan daripada fungsi INDEX dan menyarangkan sintaks MATCH dalam ia. Agar ia kelihatan seperti:

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

Mari kita gunakan INDEKS/PADANAN untuk menggantikan VLOOKUP dalam contoh di atas. Sintaks akan kelihatan seperti ini:

= INDEX(C2:C10,MATCH(B13,B2:B10,0))

Dalam Bahasa Inggeris ringkas maksudnya:

= INDEKS (saya inginkan nilai yang dikembalikan dari C2:C10 yang akan sepadan DENGAN (Kale, iaitu tempat dalam tatasusunan B2: B10, iaitu nilai dikembalikan ialah nilai pertama yang sepadan Kale))

Fungsi INDEX dan MATCH boleh digunakan sebagai pengganti vlookup

Formula mencari nilai pertama dalam C2:C10 yang sepadan dengan Kale (dalam B7) dan mengembalikan nilai dalam C7 (100), yang merupakan nilai pertama yang sepadan dengan Kale.

Masalah: Padanan tepat tidak ditemui

Apabila argumen range_lookup adalah FALSE, dan VLOOKUP tidak dapat mencari padanan tepat dalam data anda, ia mengembalikan ralat #N/A.

Penyelesaian: Jika anda pasti data yang berkaitan wujud dalam hamparan anda dan VLOOKUP adalah menangkap ia, pastikan sel yang dirujuk tidak mempunyai ruang tersembunyi atau aksara bukan dicetak. Juga Pastikan sel mengikuti jenis data yang betul. Contohnya, sel dengan nombor perlu diformatkan sebagai nombor dan bukan teks.

Gunakan fungsi CLEAN atau MEMANGKAS untuk mengosongkan data dalam sel.

Masalah: Nilai carian adalah lebih kecil daripada nilai terkecil dalam tatasusunan

Jika argumen range_lookup disetkan kepada BENAR dan nilai carian adalah lebih kecil daripada nilai terkecil dalam tatasusunan, anda akan melihat ralat #N/A. Rupa BENAR anggaran yang sepadan dalam tatasusunan dan mengembalikan yang berhampiran nilai lebih kecil daripada nilai carian.

Dalam contoh berikut, nilai carian 100, tetapi terdapat nilai dalam julat B2:C10 yang lebih kecil daripada 100; maka ralat.

Ralat t/b dalam VLOOKUP apabila nilai carian adalah lebih kecil daripada nilai terkecil dalam tatasusunan

Penyelesaian:

  • Membetulkan nilai carian mengikut keperluan.

  • Jika anda tidak boleh mengubah nilai carian dan memerlukan lebih kefleksibelan dengan nilai sepadan, pertimbangkan untuk menggunakan INDEKS/MATCH dan bukannya VLOOKUP. Dengan INDEKS/PADANAN, anda boleh mencari nilai lebih besar daripada, kecil untuk atau sama dengan nilai carian. Untuk maklumat lanjut tentang menggunakan INDEKS/MATCH dan bukannya VLOOKUP, rujuk kepada Seksyen sebelumnya dalam topik ini.

Masalah: Lajur carian tidak Diisih dalam tertib menaik

Jika argumen range_lookup disetkan kepada BENAR, dan salah satu lajur carian anda tidak Diisih dalam tertib menaik (A-Z), anda akan melihat ralat #N/A.

Penyelesaian:

  • Ubah fungsi VLOOKUP untuk mencari padanan tepat. Untuk berbuat sedemikian, setkan argumen range_lookup kepada FALSE. Terdapat tanpa memerlukan proses pengisihan sebagai PALSU.

  • Gunakan fungsi INDEX/MATCH untuk mencari nilai dalam Jadual tidak Diisih.

Masalah: Nilai adalah nombor titik terapung besar

Jika anda mempunyai nilai masa atau nombor perpuluhan yang besar dalam sel, Excel mengembalikan ralat #N/A kerana terapung titik kepersisan. Nombor titik terapung ialah nombor yang mengikuti selepas titik perpuluhan. (Perhatikan bahawa Excel Menyimpan nilai masa sebagai nombor titik terapung.) Excel tidak boleh simpan nombor dengan titik terapung yang besar, jadi untuk fungsi untuk berfungsi dengan betul, terapung pada titik nombor perlu dibundarkan kepada 5 tempat perpuluhan.

Penyelesaian: memendekkan nombor dengan pembundaran mereka sehingga lima titik perpuluhan dengan fungsi ROUND .

Adakah anda mempunyai soalan fungsi tertentu?

Kiriman soalan dalam forum komuniti Excel

Membantu kami menambah baik Excel

Adakah anda mempunyai cadangan tentang cara kami boleh meningkatkan versi Excel yang seterusnya? Jika ya, sila lihat topik di Excel pengguna suara.

Nota: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Lihat Juga

Membetulkan ralat #N/A

VLOOKUP: #NA lanjut

HLOOKUP, VLOOKUP, LOOKUP mengembalikan nilai yang salah dalam Excel

Titik terapung aritmetik mungkin memberikan hasil yang tepat dalam Excel

Kad rujukan pantas: Ulang kaji VLOOKUP

Fungsi VLOOKUP

Gambaran keseluruhan Formula dalam Excel

Cara untuk mengelakkan formula yang rosak

Menggunakan penyemakan ralat untuk mengesan ralat dalam Formula

Fungsi semua Excel (berabjad)

Fungsi semua Excel (mengikut kategori)

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×