Paragraf
Pawulangan iki nerangake carane cepet ngatasi kahanan ing ngendi fungsi VPR (VLOOKUP) ora pengin nggarap Excel 2013, 2010, 2007 lan 2003, lan carane ngenali lan ndandani kesalahan umum lan ngatasi watesan VPR.
Ing sawetara artikel sadurunge, kita wis njelajah macem-macem aspek fungsi kasebut VPR ing Excel. Yen sampeyan wis maca kanthi teliti, saiki sampeyan kudu dadi ahli ing bidang iki. Nanging, ora ana alesan manawa akeh ahli Excel percaya VPR salah sawijining fitur sing luwih rumit. Wis Bunch saka watesan lan fitur sing dadi sumber akeh masalah lan kasalahan.
Ing artikel iki sampeyan bakal nemokake panjelasan prasaja saka kasalahan #AT (#N/A), #NAME? (#Jeneng?) lan #WARNA! (#VALUE!) sing katon nalika nggarap fungsi kasebut VPR, uga teknik lan cara kanggo ngatasi. Kita bakal miwiti karo kasus sing paling umum lan alasan sing paling jelas. VPR ora bisa, dadi luwih apik kanggo sinau conto ing urutan kang diwenehi ing artikel.
Ndandani kesalahan #N/A ing fungsi VLOOKUP ing Excel
Ing rumus karo VPR pesen kesalahan #AT (#N/A) tegese ora kasedhiya (ora ana data) - katon nalika Excel ora bisa nemokake nilai sing sampeyan goleki. Iki bisa kedadeyan amarga sawetara alasan.
1. Nilai sing dikarepake salah ejaan
Apike kanggo mriksa item iki dhisik! Kesalahan asring kedadeyan nalika sampeyan nggarap data sing akeh banget, dumadi saka ewonan garis, utawa nalika nilai sing digoleki ditulis ing rumus.
2. #N/A kesalahan nalika nelusuri cocog kira-kira karo VLOOKUP
Yen sampeyan nggunakake rumus kanthi kira-kira kahanan telusuran sing cocog, yaiku argumen range_lookup (range_lookup) BENER utawa ora ditemtokake, rumus sampeyan bisa uga nglaporake kesalahan # N / A ing rong kasus:
- Nilai kanggo nggoleki kurang saka nilai paling cilik ing larik sing katon munggah.
- Kolom telusuran ora diurutake kanthi urutan munggah.
3. #N / A kesalahan nalika looking for match pas karo VLOOKUP
Yen sampeyan nggoleki sing cocog, yaiku argumen range_lookup (range_lookup) iku PALSU lan nilai pas ora ketemu, rumus uga bakal laporan kesalahan # N / A. Sinau luwih lengkap babagan carane nggoleki sing cocog lan kira-kira sing cocog karo fungsi VPR.
4. Kolom telusuran ora paling kiwa
Kaya sing sampeyan ngerteni, salah sawijining watesan sing paling penting VPR iku ora bisa madhep ngiwa, mula kolom goleki ing meja sampeyan kudu paling kiwa. Ing praktik, kita asring lali babagan iki, sing nyebabake rumus sing ora bisa digunakake lan kesalahan. # N / A.
Keputusan: Yen ora bisa ngganti struktur data supaya kolom telusuran paling kiwa, sampeyan bisa nggunakake kombinasi fungsi INDEKS (INDEX) lan LUWIH DIBUKA (MATCH) minangka alternatif luwih fleksibel kanggo VPR.
5. Nomer diformat minangka teks
Sumber kesalahan liyane # N / A ing rumus karo VPR yaiku angka ing format teks ing tabel utama utawa tabel lookup.
Iki biasane kedadeyan nalika sampeyan ngimpor informasi saka database eksternal, utawa nalika sampeyan ngetik apostrof sadurunge nomer kanggo njaga nol utama.
Tandha nomer sing paling jelas ing format teks ditampilake ing gambar ing ngisor iki:
Kajaba iku, nomer bisa disimpen ing format Umum (Umum). Ing kasus iki, mung ana siji fitur sing bisa ditemokake - nomer kasebut didadekake siji ing pojok kiwa sel, dene kanthi standar didadekake siji ing sisih tengen.
Keputusan: Yen nilai siji, mung klik ing lambang kesalahan banjur pilih Ngonversi menyang Nomer (Convert to Number) saka menu konteks.
Yen iki kahanan karo akeh nomer, pilih lan klik-tengen ing wilayah sing dipilih. Ing menu konteks sing katon, pilih Format Sel (Format Sel) > tab Panggil (Nomer) > format Panggil (Numeric) banjur pencet OK.
6. Ana papan ing wiwitan utawa pungkasan
Iki minangka alesan paling ora jelas kanggo kesalahan kasebut. # N / A ing fungsi VPR, awit iku visual angel kanggo ndeleng spasi ekstra iki, utamané nalika nggarap tabel gedhe, nalika paling data mati-layar.
Solusi 1: Spasi ekstra ing tabel utama (ngendi fungsi VLOOKUP)
Yen spasi ekstra katon ing tabel utama, sampeyan bisa mesthekake yen rumus bisa digunakake kanthi bener kanthi nglampirake argumen kasebut goleki_nilai (lookup_value) dadi fungsi TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Solusi 2: Spasi ekstra ing tabel goleki (ing kolom goleki)
Yen spasi ekstra ana ing kolom telusuran - cara sing prasaja # N / A ing rumus karo VPR ora bisa nyingkiri. Tinimbang VPR Sampeyan bisa nggunakake rumus array kanthi kombinasi fungsi INDEKS (INDEX), LUWIH DIBUKA (MATCH) lan 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))
Amarga iki minangka rumus array, aja lali pencet Ctrl + Shift + Ketik tinimbang biasanipun Ketikkanggo ngetik rumus kanthi bener.
Kesalahan #VALUE! ing rumus karo VLOOKUP
Umume kasus, Microsoft Excel nglaporake kesalahan #WARNA! (#VALUE!) Nalika nilai sing digunakake ing rumus ora cocog karo jinis data. bab VPR, banjur biasane ana rong alasan kanggo kesalahan #WARNA!.
1. Nilai sing sampeyan goleki luwih saka 255 karakter
Ati-ati: fungsi VPR ora bisa nelusuri nilai sing ngemot luwih saka 255 karakter. Yen nilai sing sampeyan goleki ngluwihi watesan iki, sampeyan bakal nampa pesen kesalahan. #WARNA!.
Keputusan: Gunakake Bunch saka fitur INDEX+PERTANDINGAN (INDEX + MATCH). Ing ngisor iki rumus sing bakal ditindakake kanthi becik kanggo tugas iki:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Path lengkap menyang buku kerja panelusuran ora ditemtokake
Yen sampeyan njupuk data saka buku kerja liyane, sampeyan kudu nemtokake path lengkap menyang file kasebut. Luwih khusus, sampeyan kudu nyakup jeneng buku kerja (kalebu ekstensi) ing kurung kothak [ ], banjur jeneng lembar, diikuti karo tanda seru. Kabeh konstruksi iki kudu dilampirake ing apostrof, ing kasus jeneng buku utawa sheet ngemot spasi.
Punika struktur lengkap fungsi VPR kanggo nggoleki ing buku liyane:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Rumus nyata bisa katon kaya iki:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Rumus iki bakal nggoleki nilai sel A2 ing kolom B ing lembaran Sheet1 ing buku kerja Prices anyar lan extract nilai sing cocog saka kolom D.
Yen bagean saka path meja tilar, fungsi Panjenengan VPR ora bakal bisa lan bakal laporan kesalahan #WARNA! (sanajan buku kerja karo tabel goleki lagi mbukak).
Kanggo informasi luwih lengkap babagan fungsi VPRngrujuk file Excel liyane, deleng pelajaran: Nggoleki buku kerja liyane nggunakake VLOOKUP.
3. Argumen Column_num kurang saka 1
Iku hard kanggo mbayangno kahanan ngendi wong ngetik Nilai kurang saka 1kanggo nuduhake kolom saka ngendi kanggo extract nilai. Senajan iku bisa yen Nilai saka pitakonan iki diwilang dening fungsi Excel liyane nested ing VPR.
Dadi, yen kedadeyan sing argumentasi col_index_num (column_number) kurang saka 1fungsi VPR uga bakal nglaporake kesalahan #WARNA!.
Yen argumentasi col_index_num (column_number) luwih gedhe tinimbang jumlah kolom ing larik sing diwenehake, VPR bakal nglaporake kesalahan #REF! (#SSYL!).
Salah #NAME? ing VLOOKUP
Kasus sing paling gampang yaiku kesalahan #NAME? (#NAME?) - bakal katon yen sampeyan ora sengaja nulis jeneng fungsi kanthi kesalahan.
Solusi kasebut jelas - priksa ejaan sampeyan!
VLOOKUP ora bisa digunakake (watesan, kaveat lan keputusan)
Saliyane sintaksis sing rada rumit, VPR duwe watesan luwih akeh tinimbang fungsi Excel liyane. Amarga watesan iki, rumus ketoke prasaja karo VPR asring mimpin kanggo asil sing ora dikarepke. Ing ngisor iki sampeyan bakal nemokake solusi kanggo sawetara skenario umum ing ngendi VPR iku salah.
1. VLOOKUP ora cilik sensitif
fungsi VPR ora mbedakake antarane cilik lan nampa aksara cilik lan huruf gedhe minangka padha. Mulane, yen ana sawetara unsur ing tabel sing beda-beda mung ing kasus, fungsi VLOOKUP bakal bali unsur pisanan ketemu, preduli saka cilik.
Keputusan: Gunakake fungsi Excel liyane sing bisa nindakake telusuran vertikal (LOOKUP, SUMPRODUCT, INDEX, lan MATCH) kanthi kombinasi PASTIA sing mbedakake kasus. Kanggo rincian liyane, sampeyan bisa sinau saka pawulangan - 4 cara kanggo nggawe VLOOKUP cilik-sensitif ing Excel.
2. VLOOKUP ngasilake nilai pisanan sing ditemokake
Kaya sing wis dingerteni, VPR ngasilake nilai saka kolom diwenehi cocog kanggo pertandhingan pisanan ketemu. Nanging, sampeyan bisa duwe extract 2nd, 3rd, 4th, utawa pengulangan liyane saka nilai sing dikarepake. Yen sampeyan kudu ngekstrak kabeh nilai duplikat, sampeyan butuh kombinasi fungsi INDEKS (INDEX), Paling apik (cilik) lan LINE (ROW).
3. A kolom ditambahake utawa dibusak ing meja
Sayange, rumus VPR mandheg kerja saben-saben kolom anyar ditambahake utawa dibusak ing meja goleki. Iki kedadeyan amarga sintaks VPR mbutuhake sampeyan nemtokake sawetara lengkap panelusuran lan nomer kolom tartamtu kanggo extraction data. Alami, sawetara sing diwenehake lan nomer kolom ganti nalika sampeyan mbusak kolom utawa nglebokake sing anyar.
Keputusan: Lan maneh fungsi cepet-cepet mbantu INDEKS (INDEX) lan LUWIH DIBUKA (PERTANDINGAN). Ing rumus INDEX+PERTANDINGAN Sampeyan nemtokake kolom telusuran lan telusuran kanthi kapisah, lan minangka asil, sampeyan bisa mbusak utawa nglebokake kolom akeh sing dikarepake tanpa kuwatir yen kudu nganyari kabeh rumus telusuran sing gegandhengan.
4. Referensi sel sing garbled nalika nyalin rumus
Judhul iki nerangake inti saka masalah kasebut kanthi lengkap, ta?
Keputusan: Tansah nggunakake referensi sel absolut (kanthi simbol $) ing cathetan kisaran, contone $A$2:$C$100 or $A:$C. Ing garis rumus, sampeyan bisa kanthi cepet ngalih jinis link kanthi ngeklik F4.
VLOOKUP – nggarap fungsi IFERROR lan ISERROR
Yen sampeyan ora pengin medeni pangguna kanthi pesen kesalahan # N / A, #WARNA! or #NAME?, sampeyan bisa nuduhake sel kosong utawa pesen sampeyan dhewe. Sampeyan bisa nindakake iki dening manggonke VPR menyang fungsi IFERROR (IFERROR) ing Excel 2013, 2010 lan 2007 utawa gunakake sawetara fungsi Yen + ISERROR (IF+ISERROR) ing versi sadurungé.
VLOOKUP: nggarap fungsi IFERROR
Sintaks fungsi IFERROR (IFERROR) iku prasaja lan ngomong dhewe:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Tegese, kanggo argumen pisanan sampeyan nglebokake nilai sing bakal dicenthang kanggo kesalahan, lan kanggo argumen kapindho sampeyan nemtokake apa sing bakal bali yen ana kesalahan.
Contone, rumus iki ngasilake sel kosong yen nilai sing sampeyan goleki ora ditemokake:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Yen sampeyan pengin nampilake pesen sampeyan dhewe tinimbang pesen kesalahan standar fungsi kasebut VPR, sijine ing kuotasi, kaya mangkene:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: nggarap fungsi ISERROR
Wiwit fungsi IFERROR muncul ing Excel 2007, nalika nggarap versi sadurunge sampeyan kudu nggunakake kombinasi kasebut IF (IF) lan EOSHIBKA (ISERROR) kaya iki:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Contone, rumus IF+ISERROR+VLOOKUP, padha karo rumus IFERROR+VLOOKUPditampilake ing ndhuwur:
=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;ЛОЖЬ))
Sing kabeh kanggo dina iki. Muga-muga tutorial singkat iki bakal mbantu sampeyan ngatasi kabeh kesalahan sing bisa ditindakake. VPR lan nggawe rumus sampeyan bisa digunakake kanthi bener.