Turinys
Šioje pamokoje paaiškinama, kaip greitai susidoroti su situacija, kai funkcija VPR (VLOOKUP) nenori dirbti su Excel 2013, 2010, 2007 ir 2003, ir kaip nustatyti ir ištaisyti įprastas klaidas bei įveikti apribojimus VPR.
Keliuose ankstesniuose straipsniuose išnagrinėjome įvairius funkcijos aspektus VPR programoje Excel. Jei atidžiai juos perskaitėte, dabar turėtumėte būti šios srities ekspertas. Tačiau ne be reikalo mano daugelis „Excel“ ekspertų VPR viena iš sudėtingesnių funkcijų. Jis turi daugybę apribojimų ir funkcijų, kurios tampa daugelio problemų ir klaidų šaltiniu.
Šiame straipsnyje rasite paprastus klaidų paaiškinimus #AT (#N/A), #NAME? (#NAME?) ir #VALUE! (#VALUE!), kurie atsiranda dirbant su funkcija VPR, taip pat kovos su jais būdai ir metodai. Pradėsime nuo dažniausiai pasitaikančių atvejų ir akivaizdžiausių priežasčių. VPR neveikia, todėl geriau studijuoti pavyzdžius tokia tvarka, kokia jie pateikiami straipsnyje.
„Excel“ funkcijos „VLOOKUP“ klaidos #N/A taisymas
Formulėse su VPR klaidos pranešimas #AT (#N/A) reiškia nėra (nėra duomenų) – pasirodo, kai „Excel“ neranda ieškomos reikšmės. Taip gali nutikti dėl kelių priežasčių.
1. Neteisingai parašyta norima reikšmė
Gera idėja pirmiausia patikrinti šį elementą! Rašybos klaidos dažnai pasitaiko, kai dirbate su labai dideliais duomenų kiekiais, susidedančiais iš tūkstančių eilučių, arba kai ieškoma reikšmė įrašoma į formulę.
2. #N/A klaida ieškant apytikslės atitikties su VLOOKUP
Jei naudojate formulę su apytikslės atitikties paieškos sąlyga, ty argumentu range_lookup (range_lookup) yra TRUE arba nenurodyta, jūsų formulė gali pranešti apie klaidą # N / A dviem atvejais:
- Reikšmė, kurią reikia ieškoti, yra mažesnė už mažiausią ieškomo masyvo reikšmę.
- Paieškos stulpelis nerūšiuojamas didėjančia tvarka.
3. #N/A klaida ieškant tikslios atitikties su VLOOKUP
Jei ieškote tikslios atitikties, ty argumento range_lookup (range_lookup) yra FALSE ir tiksli reikšmė nerasta, formulė taip pat praneš apie klaidą # N / A. Sužinokite daugiau, kaip ieškoti tikslių ir apytikslių funkcijos atitikčių VPR.
4. Paieškos stulpelis nėra kairėje
Kaip tikriausiai žinote, vienas reikšmingiausių apribojimų VPR tai, kad jis negali būti nukreiptas į kairę, todėl jūsų lentelės paieškos stulpelis turi būti kairėje. Praktikoje mes dažnai apie tai pamirštame, o tai lemia neveikiančią formulę ir klaidą. # N / A.
Sprendimas: Jei neįmanoma pakeisti duomenų struktūros, kad paieškos stulpelis būtų kairėje, galite naudoti funkcijų derinį RODYKLĖ (INDEKSAS) ir DAUGIAU ATSKIRTA (MATCH) kaip lankstesnė alternatyva VPR.
5. Skaičiai formatuojami kaip tekstas
Kitas klaidų šaltinis # N / A formulėse su VPR yra skaičiai teksto formatu pagrindinėje lentelėje arba paieškos lentelėje.
Paprastai taip nutinka, kai importuojate informaciją iš išorinių duomenų bazių arba prieš skaičių įvedate apostrofą, kad išliktų nulis priekyje.
Aiškiausi teksto formato skaičiaus ženklai parodyti paveikslėlyje žemiau:
Be to, skaičiai gali būti saugomi formatu bendras (Generolas). Šiuo atveju pastebima tik viena ypatybė – skaičiai lygiuojami prie kairiojo langelio krašto, o pagal numatytuosius nustatymus – prie dešiniojo krašto.
Sprendimas: Jei tai viena reikšmė, tiesiog spustelėkite klaidos piktogramą ir pasirinkite Konvertuoti į skaičių (Konvertuoti į skaičių) iš kontekstinio meniu.
Jei taip yra su daugybe skaičių, pasirinkite juos ir dešiniuoju pelės mygtuku spustelėkite pasirinktą sritį. Pasirodžiusiame kontekstiniame meniu pasirinkite Langelių formatavimas (Formatuoti langelius) > skirtuką Skaičius (Skaičius) > formatas Skaičius (Skaičių) ir paspauskite OK.
6. Pradžioje arba pabaigoje yra tarpas
Tai mažiausiai akivaizdi klaidos priežastis. # N / A funkcijoje VPR, nes vizualiai sunku įžvelgti šias papildomas erdves, ypač dirbant su didelėmis lentelėmis, kai dauguma duomenų yra už ekrano ribų.
1 sprendimas: papildomos vietos pagrindinėje lentelėje (kur yra funkcija VLOOKUP)
Jei pagrindinėje lentelėje atsiranda papildomų tarpų, galite užtikrinti, kad formulės veiktų tinkamai, įtraukdami argumentą lookup_value (lookup_value) į funkciją TRIM (APKARPYTI):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
2 sprendimas: papildomi tarpai peržvalgos lentelėje (peržvalgos stulpelyje)
Jei paieškos stulpelyje yra papildomų tarpų – paprasti būdai # N / A formulėje su VPR negalima išvengti. Vietoj VPR Galite naudoti masyvo formulę su funkcijų deriniu RODYKLĖ (INDEKSAS), DAUGIAU ATSKIRTA (MATCH) ir TRIM (APKARPYTI):
=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))
Kadangi tai yra masyvo formulė, nepamirškite paspausti Ctrl + Shift + Enter vietoj įprastų įeitikad teisingai įvestumėte formulę.
Klaida #VALUE! formulėse su VLOOKUP
Daugeliu atvejų „Microsoft Excel“ praneša apie klaidą #VALUE! (#VALUE!), kai formulėje naudojama reikšmė neatitinka duomenų tipo. Kalbant apie VPR, tada dažniausiai yra dvi klaidos priežastys #VALUE!.
1. Reikšmė, kurios ieškote, yra ilgesnė nei 255 simboliai
Būkite atsargūs: funkcija VPR negali ieškoti verčių, kuriose yra daugiau nei 255 simboliai. Jei ieškoma reikšmė viršija šią ribą, gausite klaidos pranešimą. #VALUE!.
Sprendimas: Naudokite daugybę funkcijų INDEX+MATCH (INDEX + MATCH). Žemiau yra formulė, kuri puikiai tiks šiai užduočiai:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Visas kelias į paieškos darbaknygę nenurodytas
Jei nuskaitote duomenis iš kitos darbaknygės, turite nurodyti visą to failo kelią. Tiksliau, laužtiniuose skliaustuose [ ] turite įrašyti darbaknygės pavadinimą (įskaitant plėtinį), po to lapo pavadinimą ir šauktuką. Visa ši konstrukcija turi būti apostrofuota, jei knygos ar lapo pavadinime yra tarpų.
Čia yra visa funkcijos struktūra VPR ieškoti kitoje knygoje:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Tikroji formulė gali atrodyti taip:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Ši formulė ieškos langelio vertės A2 stulpelyje B ant lapo Sheet1 darbo knygelėje Naujos Kainos ir ištraukite atitinkamą reikšmę iš stulpelio D.
Jei kuri nors lentelės kelio dalis praleista, jūsų funkcija VPR neveiks ir praneš apie klaidą #VALUE! (net jei darbaknygė su paieškos lentele šiuo metu yra atidaryta).
Norėdami gauti daugiau informacijos apie funkciją VPRnuorodas į kitą Excel failą, žr. pamoką: Kitos darbaknygės paieška naudojant VLOOKUP.
3. Argumentas Column_num yra mažesnis nei 1
Sunku įsivaizduoti situaciją, kai kas nors įveda vertę, mažesnę nei 1nurodyti stulpelį, iš kurio reikia išgauti reikšmę. Nors tai įmanoma, jei šio argumento reikšmę apskaičiuoja kita „Excel“ funkcija, įdėta VPR.
Taigi, jei taip atsitiks, kad argumentas col_index_num (stulpelio_numeris) mažesnis nei 1funkcija VPR taip pat praneš apie klaidą #VALUE!.
Jei argumentas col_index_num (stulpelio_numeris) yra didesnis nei nurodyto masyvo stulpelių skaičius, VPR praneš apie klaidą #REF! (#SSYL!).
Klaida #NAME? VLOOKUP
Paprasčiausias atvejis yra klaida #NAME? (#NAME?) – pasirodys, jei netyčia parašysite funkcijos pavadinimą su klaida.
Sprendimas akivaizdus – patikrinkite rašybą!
VLOOKUP neveikia (ribojimai, įspėjimai ir sprendimai)
Be gana sudėtingos sintaksės, VPR turi daugiau apribojimų nei bet kuri kita „Excel“ funkcija. Dėl šių apribojimų, atrodytų, paprastos formulės su VPR dažnai sukelia netikėtų rezultatų. Žemiau rasite kelių dažniausiai pasitaikančių scenarijų sprendimus VPR yra negerai.
1. VLOOKUP neskiriamos didžiosios ir mažosios raidės
Funkcija VPR neskiria didžiųjų ir mažųjų raidžių ir priima mažąsias bei didžiąsias raides kaip vienodus. Todėl, jei lentelėje yra keli elementai, kurie skiriasi tik didžiosiomis raidėmis, funkcija VLOOKUP grąžins pirmąjį rastą elementą, neatsižvelgiant į didžiąsias ir mažąsias raides.
Sprendimas: Naudokite kitą „Excel“ funkciją, kuri gali atlikti vertikalią paiešką (LOOKUP, SUMPRODUCT, INDEX ir MATCH) kartu su TIKSLASA, kuri išskiria atvejį. Norėdami gauti daugiau informacijos, galite pasimokyti iš pamokos – 4 būdai, kaip „Excel“ padaryti VLOOKUP didžiąsias ir mažąsias raides.
2. VLOOKUP grąžina pirmąją rastą reikšmę
Kaip jūs jau žinote, VPR grąžina reikšmę iš nurodyto stulpelio, atitinkančią pirmą rastą atitiktį. Tačiau galite leisti jam išgauti 2, 3, 4 ar bet kurį kitą norimos reikšmės kartojimą. Jei reikia išgauti visas pasikartojančias reikšmes, jums reikės funkcijų derinio RODYKLĖ (INDEKSAS), MAŽIAUSIAI (MAŽAS) ir LINIJA (EILUTE).
3. Lentelėje buvo pridėtas arba pašalintas stulpelis
Deja, formulės VPR nustokite veikti kiekvieną kartą, kai į paieškos lentelę įtraukiamas arba pašalinamas naujas stulpelis. Taip atsitinka dėl sintaksės VPR reikia nurodyti visą paieškos diapazoną ir konkretų duomenų išgavimo stulpelio numerį. Natūralu, kad ištrinant stulpelį arba įterpiant naują pasikeičia nurodytas diapazonas ir stulpelio numeris.
Sprendimas: Ir vėl funkcijos skuba padėti RODYKLĖ (INDEKSAS) ir DAUGIAU ATSKIRTA (MATCH). Formulėje INDEX+MATCH Atskirai apibrėžiate paieškos ir gavimo stulpelius, todėl galite ištrinti arba įterpti tiek stulpelių, kiek norite, nesijaudindami, kad reikės atnaujinti visas susijusias paieškos formules.
4. Kopijuojant formulę langelių nuorodos iškraipomos
Ši antraštė išsamiai paaiškina problemos esmę, tiesa?
Sprendimas: Visada naudokite absoliučias langelių nuorodas (su simboliu $) įrašo diapazoną, pavyzdžiui $ A $ 2: $ C $ 100 or $A:$C. Formulės juostoje galite greitai pakeisti nuorodos tipą spustelėdami F4.
VLOOKUP – darbas su funkcijomis IFERROR ir ISERROR
Jei nenorite gąsdinti vartotojų klaidų pranešimais # N / A, #VALUE! or #NAME?, galite rodyti tuščią langelį arba savo pranešimą. Tai galite padaryti padėdami VPR į funkciją IFERROR (IFERROR) programose Excel 2013, 2010 ir 2007 arba naudokite daugybę funkcijų JEI+KLAIDA (IF+ISERROR) ankstesnėse versijose.
VLOOKUP: darbas su IFERROR funkcija
Funkcijos sintaksė IFERROR (IFERROR) yra paprastas ir kalba pats už save:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Tai yra, į pirmąjį argumentą įterpiate vertę, kuri turi būti patikrinta, ar nėra klaidos, o antrajam argumentui nurodote, ką grąžinti, jei randama klaida.
Pavyzdžiui, ši formulė grąžina tuščią langelį, jei ieškoma reikšmė nerasta:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Jei norite rodyti savo pranešimą, o ne standartinį funkcijos klaidos pranešimą VPR, sudėkite į kabutes, pavyzdžiui:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: darbas su funkcija ISERROR
Nuo funkcijos IFERROR pasirodė Excel 2007, dirbdami su ankstesnėmis versijomis turėsite naudoti derinį IF (IF) ir EOSHIBKA (ISERROR) kaip tai:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Pavyzdžiui, formulė IF+ISERROR+VLOOKUP, panašus į formulę IFERROR+VLOOKUPparodyta aukščiau:
=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;ЛОЖЬ))
Tai viskas siandienai. Tikiuosi, kad ši trumpa pamoka padės išspręsti visas galimas klaidas. VPR ir kad jūsų formulės veiktų tinkamai.