Contents [show]
Pelajaran ini menerangkan cara cepat menangani situasi di mana fungsi VPR (VLOOKUP) tidak mahu berfungsi dalam Excel 2013, 2010, 2007 dan 2003, dan cara mengenal pasti dan membetulkan ralat biasa serta mengatasi batasan VPR.
Dalam beberapa artikel sebelum ini, kami telah meneroka pelbagai aspek fungsi VPR dalam Excel. Jika anda telah membacanya dengan teliti, kini anda sepatutnya menjadi pakar dalam bidang ini. Walau bagaimanapun, bukan tanpa sebab ramai pakar Excel percaya VPR salah satu ciri yang lebih kompleks. Ia mempunyai banyak batasan dan ciri yang menjadi punca kepada banyak masalah dan ralat.
Dalam artikel ini anda akan menemui penjelasan mudah tentang ralat #AT (#N/A), #NAME? (#NAMA?) dan #NILAI! (#VALUE!) yang muncul apabila bekerja dengan fungsi VPR, serta teknik dan kaedah menanganinya. Kami akan mulakan dengan kes yang paling biasa dan sebab yang paling jelas. VPR tidak berfungsi, jadi lebih baik untuk mengkaji contoh dalam susunan yang diberikan dalam artikel.
Membetulkan ralat #N/A dalam fungsi VLOOKUP dalam Excel
Dalam formula dengan VPR mesej ralat #AT (#N/A) bermaksud tidak terdapat (tiada data) – muncul apabila Excel tidak dapat mencari nilai yang anda cari. Ini boleh berlaku atas beberapa sebab.
1. Nilai yang dikehendaki adalah salah ejaan
Idea yang baik untuk menyemak item ini dahulu! Typo sering berlaku apabila anda bekerja dengan jumlah data yang sangat besar, yang terdiri daripada beribu-ribu baris atau apabila nilai yang anda cari ditulis ke dalam formula.
2. #N/A ralat semasa mencari padanan anggaran dengan VLOOKUP
Jika anda menggunakan formula dengan anggaran keadaan carian padanan, iaitu hujah range_lookup (range_lookup) adalah BENAR atau tidak dinyatakan, formula anda mungkin melaporkan ralat # N / A dalam dua kes:
- Nilai untuk dicari adalah kurang daripada nilai terkecil dalam tatasusunan yang dicari.
- Lajur carian tidak diisih mengikut tertib menaik.
3. #N/A ralat semasa mencari padanan tepat dengan VLOOKUP
Jika anda sedang mencari padanan yang tepat, iaitu hujah range_lookup (range_lookup) adalah PALSU dan nilai tepat tidak ditemui, formula juga akan melaporkan ralat # N / A. Ketahui lebih lanjut tentang cara mencari padanan tepat dan anggaran dengan fungsi VPR.
4. Lajur carian bukan paling kiri
Seperti yang anda ketahui, salah satu batasan yang paling ketara VPR ia tidak boleh menghadap ke kiri, oleh itu lajur carian dalam jadual anda mestilah paling kiri. Dalam amalan, kita sering melupakan perkara ini, yang membawa kepada formula yang tidak berfungsi dan ralat. # N / A.
Keputusan: Jika tidak mungkin untuk menukar struktur data supaya lajur carian berada di paling kiri, anda boleh menggunakan gabungan fungsi INDEKS (INDEX) dan LEBIH TERDEDAH (MATCH) sebagai alternatif yang lebih fleksibel untuk VPR.
5. Nombor diformatkan sebagai teks
Satu lagi punca kesilapan # N / A dalam formula dengan VPR ialah nombor dalam format teks dalam jadual utama atau jadual carian.
Ini biasanya berlaku apabila anda mengimport maklumat daripada pangkalan data luaran, atau apabila anda menaip tanda kutip sebelum nombor untuk mengekalkan sifar pendahuluan.
Tanda-tanda nombor yang paling jelas dalam format teks ditunjukkan dalam rajah di bawah:
Di samping itu, nombor boleh disimpan dalam format Umum (Umum). Dalam kes ini, terdapat hanya satu ciri yang ketara - nombor dijajarkan ke tepi kiri sel, manakala secara lalai ia dijajarkan ke tepi kanan.
Keputusan: Jika ia adalah satu nilai, cuma klik pada ikon ralat dan pilih Tukar kepada Nombor (Tukar kepada Nombor) daripada menu konteks.
Jika ini adalah situasi dengan banyak nombor, pilih mereka dan klik kanan pada kawasan yang dipilih. Dalam menu konteks yang muncul, pilih Format Sel (Format Sel) > tab nombor (Nombor) > format nombor (Numeric) dan tekan OK.
6. Ada ruang di awal atau di akhir
Ini adalah sebab yang paling tidak jelas untuk kesilapan. # N / A dalam fungsi VPR, kerana secara visual sukar untuk melihat ruang tambahan ini, terutamanya apabila bekerja dengan jadual besar, apabila kebanyakan data berada di luar skrin.
Penyelesaian 1: Ruang tambahan dalam jadual utama (di mana fungsi VLOOKUP berada)
Jika ruang tambahan muncul dalam jadual utama, anda boleh memastikan bahawa formula berfungsi dengan betul dengan melampirkan hujah lookup_value (lookup_value) ke dalam fungsi TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Penyelesaian 2: Ruang tambahan dalam jadual carian (dalam lajur carian)
Jika ruang tambahan terdapat dalam lajur carian – cara mudah # N / A dalam formula dengan VPR tidak dapat dielakkan. Sebaliknya VPR Anda boleh menggunakan formula tatasusunan dengan gabungan fungsi INDEKS (INDEX), LEBIH TERDEDAH (PERLAWANAN) dan TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Memandangkan ini adalah formula tatasusunan, jangan lupa tekan Ctrl + Shift + Enter bukannya biasa Masukkanuntuk memasukkan formula dengan betul.
Ralat #VALUE! dalam formula dengan VLOOKUP
Dalam kebanyakan kes, Microsoft Excel melaporkan ralat #NILAI! (#VALUE!) apabila nilai yang digunakan dalam formula tidak sepadan dengan jenis data. Berkenaan VPR, maka biasanya terdapat dua sebab untuk ralat #NILAI!.
1. Nilai yang anda cari adalah lebih panjang daripada 255 aksara
Berhati-hati: fungsi VPR tidak boleh mencari nilai yang mengandungi lebih daripada 255 aksara. Jika nilai yang anda cari melebihi had ini, anda akan menerima mesej ralat. #NILAI!.
Keputusan: Gunakan sekumpulan ciri INDEKS+PADANAN (INDEX + PADANAN). Di bawah ialah formula yang sesuai untuk tugas ini:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Laluan penuh ke buku kerja carian tidak ditentukan
Jika anda mendapatkan semula data daripada buku kerja lain, anda mesti menentukan laluan penuh ke fail tersebut. Lebih khusus lagi, anda mesti memasukkan nama buku kerja (termasuk sambungan) dalam kurungan segi empat sama [ ], diikuti dengan nama helaian, diikuti dengan tanda seru. Semua binaan ini mesti disertakan dalam apostrof, sekiranya nama buku atau helaian mengandungi ruang.
Berikut ialah struktur lengkap fungsi tersebut VPR untuk mencari dalam buku lain:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Formula sebenar mungkin kelihatan seperti ini:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Formula ini akan mencari nilai sel A2 dalam lajur B pada helaian Sheet1 dalam buku kerja Harga Baru dan ekstrak nilai yang sepadan daripada lajur D.
Jika mana-mana bahagian laluan jadual ditinggalkan, fungsi anda VPR tidak akan berfungsi dan akan melaporkan ralat #NILAI! (walaupun buku kerja dengan jadual carian sedang dibuka).
Untuk maklumat lanjut tentang fungsi VPRmerujuk fail Excel lain, lihat pelajaran: Mencari buku kerja lain menggunakan VLOOKUP.
3. Bilangan Lajur Argumen adalah kurang daripada 1
Sukar untuk membayangkan situasi di mana seseorang memasukkan nilai kurang daripada 1untuk menunjukkan lajur dari mana untuk mengekstrak nilai. Walaupun mungkin jika nilai hujah ini dikira oleh fungsi Excel lain yang bersarang di dalamnya VPR.
Jadi, jika ia berlaku bahawa hujah col_index_num (nombor_lajur) kurang daripada 1fungsi VPR juga akan melaporkan ralat #NILAI!.
Jika hujah col_index_num (column_number) adalah lebih besar daripada bilangan lajur dalam tatasusunan yang diberikan, VPR akan melaporkan ralat #REF! (#SSYL!).
Ralat #NAME? dalam VLOOKUP
Kes paling mudah adalah kesilapan #NAME? (#NAME?) – akan muncul jika anda secara tidak sengaja menulis nama fungsi dengan ralat.
Penyelesaiannya adalah jelas – semak ejaan anda!
VLOOKUP tidak berfungsi (had, kaveat dan keputusan)
Sebagai tambahan kepada sintaks yang agak rumit, VPR mempunyai lebih banyak had daripada fungsi Excel yang lain. Kerana batasan ini, formula yang kelihatan mudah dengan VPR sering membawa kepada hasil yang tidak dijangka. Di bawah anda akan menemui penyelesaian untuk beberapa senario biasa di mana VPR salah.
1. VLOOKUP tidak sensitif huruf besar
fungsi VPR tidak membezakan antara huruf besar dan menerima huruf kecil dan huruf besar sebagai sama. Oleh itu, jika terdapat beberapa elemen dalam jadual yang berbeza hanya dalam kes, fungsi VLOOKUP akan mengembalikan elemen pertama yang ditemui, tanpa mengira kes.
Keputusan: Gunakan fungsi Excel lain yang boleh melakukan carian menegak (LOOKUP, SUMPRODUCT, INDEX dan MATCH) dalam kombinasi dengan TEPATA yang membezakan kes. Untuk butiran lanjut, anda boleh belajar daripada pelajaran – 4 cara untuk menjadikan VLOOKUP sensitif huruf besar-besaran dalam Excel.
2. VLOOKUP mengembalikan nilai pertama yang ditemui
Seperti yang telah anda ketahui, VPR mengembalikan nilai dari lajur yang diberikan sepadan dengan padanan pertama yang ditemui. Walau bagaimanapun, anda boleh memintanya mengekstrak nilai yang ke-2, ke-3, ke-4 atau sebarang pengulangan nilai yang anda inginkan. Jika anda perlu mengekstrak semua nilai pendua, anda memerlukan gabungan fungsi INDEKS (INDEX), KELEBIHAN (KECIL) dan LINE (ROW).
3. Lajur telah ditambah atau dialih keluar pada jadual
Malangnya, formula VPR berhenti berfungsi setiap kali lajur baharu ditambahkan atau dialih keluar pada jadual carian. Ini berlaku kerana sintaks VPR memerlukan anda untuk menentukan julat penuh carian dan nombor lajur khusus untuk pengekstrakan data. Sememangnya, kedua-dua julat yang diberikan dan nombor lajur berubah apabila anda memadamkan lajur atau memasukkan yang baharu.
Keputusan: Dan sekali lagi fungsi tergesa-gesa untuk membantu INDEKS (INDEX) dan LEBIH TERDEDAH (PERLAWANAN). Dalam formula INDEKS+PADANAN Anda secara berasingan mentakrifkan lajur carian dan dapatkan semula, dan sebagai hasilnya, anda boleh memadam atau memasukkan seberapa banyak lajur yang anda mahu tanpa perlu risau tentang perlu mengemas kini semua formula carian yang berkaitan.
4. Rujukan sel bercelaru apabila menyalin formula
Tajuk ini menerangkan intipati masalah secara menyeluruh, bukan?
Keputusan: Sentiasa gunakan rujukan sel mutlak (dengan simbol $) pada merekodkan julat, sebagai contoh $A$2:$C$100 or $A:$C. Dalam bar formula, anda boleh menukar jenis pautan dengan cepat dengan mengklik F4.
VLOOKUP – bekerja dengan fungsi IFERROR dan ISERROR
Jika anda tidak mahu menakutkan pengguna dengan mesej ralat # N / A, #NILAI! or #NAME?, anda boleh menunjukkan sel kosong atau mesej anda sendiri. Anda boleh melakukan ini dengan meletakkan VPR menjadi fungsi IFERROR (IFERROR) dalam Excel 2013, 2010 dan 2007 atau gunakan sekumpulan fungsi JIKA+ISERROR (IF+ISERROR) dalam versi terdahulu.
VLOOKUP: berfungsi dengan fungsi IFERROR
Sintaks fungsi IFERROR (IFERROR) adalah mudah dan bercakap untuk dirinya sendiri:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Iaitu, untuk argumen pertama anda memasukkan nilai untuk diperiksa untuk ralat, dan untuk argumen kedua anda menentukan perkara yang akan dikembalikan jika ralat ditemui.
Sebagai contoh, formula ini mengembalikan sel kosong jika nilai yang anda cari tidak dijumpai:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Jika anda ingin memaparkan mesej anda sendiri dan bukannya mesej ralat standard fungsi VPR, letakkan dalam petikan, seperti:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: berfungsi dengan fungsi ISERROR
Sejak fungsi IFERROR muncul dalam Excel 2007, apabila bekerja dalam versi terdahulu anda perlu menggunakan gabungan itu IF (JIKA) dan EOSHIBKA (ISERROR) seperti ini:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Sebagai contoh, formula JIKA+ISERROR+VLOOKUP, sama dengan formula IFERROR+VLOOKUPditunjukkan di atas:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Itu sahaja untuk hari ini. Saya harap tutorial ringkas ini akan membantu anda menangani semua kesilapan yang mungkin berlaku. VPR dan buat formula anda berfungsi dengan betul.