Daptar eusi
Palajaran ieu ngécéskeun kumaha gancang nungkulan kaayaan dimana hiji fungsi VPR (VLOOKUP) henteu hoyong damel di Excel 2013, 2010, 2007 sareng 2003, sareng kumaha ngaidentipikasi sareng ngalereskeun kasalahan umum sareng ngatasi watesan VPR.
Dina sababaraha tulisan sateuacana, kami parantos ngajalajah rupa-rupa aspék fungsina VPR dina Excel. Upami anjeun parantos maca sacara saksama, ayeuna anjeun kedah janten ahli dina widang ieu. Nanging, sanés alesan yén seueur ahli Excel percanten VPR salah sahiji fitur nu leuwih kompleks. Éta ngagaduhan seueur watesan sareng fitur anu janten sumber seueur masalah sareng kasalahan.
Dina artikel ieu anjeun bakal manggihan katerangan basajan ngeunaan kasalahan #AT (#N/A), #NAME? (#NAME?) jeung #Nilai! (#VALUE!) anu muncul nalika damel sareng fungsina VPR, kitu ogé téhnik jeung métode nungkulan aranjeunna. Urang bakal mimitian ku kasus anu paling umum sareng alesan anu paling jelas. VPR henteu dianggo, janten langkung saé pikeun diajar conto dina urutan anu dipasihkeun dina tulisan.
Ngalereskeun kasalahan #N/A dina fungsi VLOOKUP dina Excel
Dina rumus kalawan VPR pesen kasalahan #AT (#N/A) hartina henteu sayogi (euweuh data) - nembongan nalika Excel teu bisa manggihan nilai nu Anjeun keur pilari. Ieu bisa lumangsung pikeun sababaraha alesan.
1. Nilai nu dipikahoyong ieu salah eja
Ide nu sae pikeun pariksa heula item ieu! Typos sering lumangsung nalika anjeun damel sareng jumlah data anu ageung pisan, diwangun ku rébuan garis, atanapi nalika nilai anu anjeun milarian ditulis kana rumus.
2. #N / Kasalahan nalika milarian perkiraan anu cocog sareng VLOOKUP
Upami anjeun nganggo rumus kalayan perkiraan kaayaan milarian anu cocog, nyaéta argumen range_lookup (range_lookup) BENER atanapi henteu ditangtukeun, rumus anjeun tiasa ngalaporkeun kasalahan # N / A dina dua kasus:
- Nilai pikeun milarian langkung handap tina nilai pangleutikna dina susunan anu ditingali.
- Kolom pilarian henteu diurutkeun dina urutan naek.
3. #N / A kasalahan nalika néangan hiji patandingan pasti kalawan VLOOKUP
Lamun Anjeun keur pilari hiji patandingan pasti, ie argumen range_lookup (range_lookup) nyaéta PALSU jeung nilai pasti teu kapanggih, rumus ogé bakal ngalaporkeun kasalahan # N / A. Diajar langkung seueur ngeunaan cara milarian patandingan anu tepat sareng perkiraan sareng hiji fungsi VPR.
4. Pilarian kolom teu leftmost
Sakumaha anjeun terang, salah sahiji watesan anu paling penting VPR Ieu nu teu bisa nyanghareupan ka kénca, kituna kolom lookup dina tabel anjeun kudu leftmost. Dina prakték, urang mindeng poho ngeunaan ieu, nu ngabalukarkeun rumus non-kerja jeung kasalahan. # N / A.
Kaputusan: Upami teu mungkin pikeun ngarobih struktur data supados kolom milarian paling kénca, anjeun tiasa nganggo kombinasi fungsi Indéks (INDEX) jeung LEUWIH DIBUKA (cocog) salaku alternatif leuwih fleksibel pikeun VPR.
5. Nomer diformat salaku téks
Sumber sejen kasalahan # N / A dina rumus jeung VPR nyaéta angka dina format téks dina tabel utama atawa tabel lookup.
Ieu biasana kajadian nalika anjeun ngimpor inpormasi tina pangkalan data éksternal, atanapi nalika anjeun ngetik tanda kutip sateuacan nomer pikeun ngajaga nol anu nuju.
Tanda-tanda anu paling écés tina nomer dina format téks dipidangkeun dina gambar di handap ieu:
Sajaba ti éta, angka bisa disimpen dina format nu umum (Umum). Dina hal ieu, ngan aya hiji fitur noticeable - angka anu Blok ka ujung kénca sél, bari sacara standar aranjeunna Blok ka ujung katuhu.
Kaputusan: Lamun éta nilai tunggal, ngan klik dina ikon kasalahan tur pilih Ngarobah kana Nomer (Ngarobih kana Nomer) tina ménu kontéks.
Upami ieu kaayaan sareng seueur nomer, pilih aranjeunna teras klik-katuhu dina daérah anu dipilih. Dina menu konteks nu nembongan, pilih Sél Pormat (Format Sél) > tab jumlah (Jumlah) > formatna jumlah (Numeric) jeung pencét OK.
6. Aya rohangan di awal atawa di ahir
Ieu mangrupikeun alesan anu paling atra pikeun kasalahan. # N / A dina fungsi VPR, Kusabab éta visually hésé ningali spasi tambahan ieu, utamana lamun gawé bareng tabel badag, lamun lolobana data kaluar-layar.
Solusi 1: Spasi tambahan dina tabel utama (tempat fungsi VLOOKUP)
Lamun spasi tambahan némbongan dina tabel utama, anjeun bisa mastikeun yén rumus jalan leres ku enclosing argumen lookup_value (lookup_value) kana hiji fungsi motong (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solusi 2: spasi tambahan dina tabel lookup (dina kolom lookup)
Lamun spasi tambahan aya dina kolom pilarian - cara basajan # N / A dina rumus jeung VPR teu bisa dihindari. Sabalikna VPR Anjeun tiasa make rumus Asép Sunandar Sunarya kalawan kombinasi fungsi Indéks (INDEKS), LEUWIH DIBUKA (PERTANDINGAN) jeung motong (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))
Kusabab ieu rumus Asép Sunandar Sunarya, ulah poho pikeun mencet Ctrl + Shift + Lebetkeun tibatan biasa asuppikeun nuliskeun rumus anu bener.
Kasalahan #VALUE! dina rumus sareng VLOOKUP
Dina kalolobaan kasus, Microsoft Excel ngalaporkeun kasalahan #Nilai! (#VALUE!) Lamun nilai dipaké dina rumus teu cocog tipe data. Ngeunaan VPR, teras biasana aya dua alesan pikeun kasalahan #Nilai!.
1. Nilai nu Anjeun keur pilari leuwih panjang batan 255 karakter
Ati-ati: fungsi VPR teu tiasa milarian nilai anu ngandung langkung ti 255 karakter. Lamun nilai nu Anjeun keur pilari ngaleuwihan wates ieu, anjeun bakal nampa pesen kasalahan. #Nilai!.
Kaputusan: Paké kebat fitur INDEKS + tanding (INDÉKS + tanding). Di handap ieu rumus anu tiasa dianggo pikeun tugas ieu:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Jalur pinuh ka workbook pilarian teu dieusian
Lamun anjeun retrieving data tina workbook sejen, Anjeun kudu nangtukeun jalur pinuh ka file éta. Leuwih husus, anjeun kudu ngasupkeun ngaran workbook (kaasup extension) dina kurung kuadrat [ ], dituturkeun ku ngaran lambar, dituturkeun ku titik exclamation. Sadaya konstruksi ieu kedah diapit ku tanda kutip, upami nami buku atanapi lambar ngandung rohangan.
Ieu struktur lengkep fungsi VPR pikeun milarian dina buku anu sanés:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Rumus nyata tiasa katingali sapertos kieu:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Rumus ieu bakal milarian nilai sél A2 dina kolom B dina lambaran Lambaran1 dina workbook Harga Anyar sarta nimba nilai pakait tina kolom D.
Mun sagala bagian tina jalur tabel disingkahkeun, fungsi Anjeun VPR moal jalan na bakal ngalaporkeun kasalahan #Nilai! (Sanajan buku kerja sareng tabel lookup ayeuna dibuka).
Kanggo inpo nu langkung lengkep ihwal fungsi VPRngarujuk kana file Excel anu sanés, tingali pelajaran: Milarian buku kerja anu sanés nganggo VLOOKUP.
3. Argumen Column_num kurang ti 1
Hésé ngabayangkeun kaayaan dimana batur ngasupkeun nilai kirang ti 1pikeun nunjukkeun kolom ti mana nimba nilai. Sanajan mungkin lamun nilai argumen ieu diitung ku fungsi Excel sejen nested dina VPR.
Ku kituna, lamun eta kajadian eta argumen col_index_num (column_number) kirang ti 1fungsi VPR ogé bakal ngalaporkeun kasalahan #Nilai!.
Lamun argumen col_index_num (column_number) langkung ageung tibatan jumlah kolom dina susunan anu dipasihkeun, VPR bakal ngalaporkeun kasalahan #REF! (#SSYL!).
Kasalahan #NAME? dina VLOOKUP
Kasus pangbasajanna nyaéta kasalahan #NAME? (#NAME?) - bakal muncul upami anjeun ngahaja nyerat nami fungsi kalayan kasalahan.
Solusina écés - pariksa éjahan anjeun!
VLOOKUP henteu jalan (watesan, caveats sareng kaputusan)
Salian sintaksis anu rada rumit, VPR gaduh langkung watesan tibatan fungsi Excel anu sanés. Kusabab watesan ieu, rumus sigana basajan kalawan VPR sering ngakibatkeun hasil anu teu disangka-sangka. Di handap ieu anjeun bakal mendakan solusi pikeun sababaraha skenario umum dimana VPR salah.
1. VLOOKUP henteu sénsitip hurup
fungsi VPR teu ngabedakeun antara hurup jeung narima aksara leutik jeung hurup gede salaku sarua. Ku alatan éta, lamun aya sababaraha elemen dina tabel nu béda ngan bisi, fungsi VLOOKUP bakal balik unsur munggaran kapanggih, paduli hal.
Kaputusan: Anggo pungsi Excel anu sanés anu tiasa ngalaksanakeun panéangan nangtung (LOOKUP, SUMPRODUCT, INDEX, sareng MATCH) dina kombinasi sareng ATASA nu ngabedakeun pasualan. Pikeun langkung rinci, anjeun tiasa diajar tina palajaran - 4 cara ngajantenkeun VLOOKUP sénsitip-sénsitip dina Excel.
2. VLOOKUP mulih nilai munggaran kapanggih
Sakumaha anjeun parantos terang, VPR mulih nilai tina kolom dibikeun pakait jeung cocok munggaran kapanggih. Najan kitu, anjeun tiasa gaduh eta ekstrak 2nd, 3rd, 4th, atawa pengulangan séjén tina nilai nu Anjeun hoyongkeun. Upami anjeun kedah nimba sadaya nilai duplikat, anjeun peryogi kombinasi fungsi Indéks (INDEKS), Sahenteuna (LEUTIK) jeung garis (JARINGAN).
3. A kolom ieu ditambahkeun atawa dihapus kana tabél
Hanjakal, rumus VPR eureun gawé unggal waktos kolom anyar ditambahkeun atawa dihapus kana tabel lookup. Ieu lumangsung alatan sintaksis VPR merlukeun anjeun nangtukeun rentang pinuh ku pilarian sarta nomer kolom husus pikeun ékstraksi data. Alami, rentang anu dipasihkeun sareng nomer kolom robih nalika anjeun ngahapus kolom atanapi ngalebetkeun anu énggal.
Kaputusan: Sareng deui fungsina buru-buru ngabantosan Indéks (INDEX) jeung LEUWIH DIBUKA (PERTANDINGAN). Dina rumus INDEKS + tanding Anjeun misah nangtukeun kolom pilarian sarta dimeunangkeun, sarta salaku hasilna, Anjeun bisa mupus atawa nyelapkeun saloba kolom nu Anjeun hoyongkeun tanpa salempang ngeunaan kudu ngamutahirkeun sagala rumus pilarian patali.
4. Rujukan sél anu garbled nalika nyalin rumus
Judul ieu ngajelaskeun hakekat masalah sacara lengkep, leres?
Kaputusan: Salawasna nganggo rujukan sél mutlak (kalawan simbol $) dina catetan rentang, contona $A$2:$C$100 or $A:$C. Dina bar rumus, anjeun tiasa gancang ngagentos jinis tautan ku ngaklik F4.
VLOOKUP - damel sareng fungsi IFERROR sareng ISERROR
Upami anjeun henteu hoyong nyingsieunan pangguna ku pesen kasalahan # N / A, #Nilai! or #NAME?, anjeun tiasa ningalikeun sél kosong atanapi pesen anjeun nyalira. Anjeun tiasa ngalakukeun ieu ku nempatkeun VPR kana hiji fungsi IFERROR (IFERROR) dina Excel 2013, 2010 sareng 2007 atanapi nganggo sakumpulan fungsi IF + ISERROR (IF+ISERROR) dina vérsi saméméhna.
VLOOKUP: damel sareng fungsi IFERROR
Sintaksis fungsi IFERROR (IFERROR) basajan sareng nyarios sorangan:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Hartina, pikeun argumen kahiji anjeun nyelapkeun nilai pikeun dipariksa pikeun kasalahan, sarta pikeun argumen kadua anjeun nangtukeun naon balik lamun kasalahan kapanggih.
Contona, rumus ieu mulih sél kosong lamun nilai nu ditéang teu kapanggih:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Upami anjeun hoyong ningalikeun pesen anjeun nyalira tibatan pesen kasalahan standar fungsi VPR, lebetkeun kana tanda petik, sapertos kieu:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: gawé bareng fungsi ISERROR
Kusabab fungsi IFERROR némbongan dina Excel 2007, nalika damel di versi sateuacana anjeun kedah nganggo kombinasi éta IF (IF) jeung EOSHIBKA (ISERROR) sapertos kieu:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Contona, rumus IF + ISERROR + VLOOKUP, sarua jeung rumus IFERROR+VLOOKUPditémbongkeun di luhur:
=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;ЛОЖЬ))
Sakitu wae kanggo dinten ieu. Kuring miharep tutorial pondok ieu bakal nulungan anjeun nungkulan sagala kasalahan mungkin. VPR sareng ngajantenkeun rumus anjeun leres.