Šioje temoje aprašomos dažniausios VLOOKUP klaidingo funkcijos rezultato priežastys ir pateikiami pasiūlymai, kaip vietoj jų naudoti INDEXir MATCH.

Patarimas: Be to, žr. sparčiųjų nuorodų kortelę: VLOOKUP trikčių diagnostikos patarimai, kurie pateikia dažnai pasitaikančių #NA problemų patogiuose PDF failuose. Galite bendrinti PDF su kitais arba spausdinti savo nuorodai.

Problema: peržvalgos reikšmė nėra pirmajame argumento table_array stulpelyje

Vienas VLOOKUP apribojimas yra tas, kad jis gali ieškoti reikšmių tik kairiajame lentelės masyvo stulpelyje. Jei peržvalgos reikšmė nėra pirmame masyvo stulpelyje, matysite klaidą #N/A.

Šioje lentelėje norime gauti vienetų, parduotų už "Kale", skaičių.

#NA klaida VLOOKUP: peržvalgos reikšmė nėra pirmajame lentelės masyvo stulpelyje

Klaidos #N/A, nes peržvalgos reikšmė "Kale" rodoma antrajame argumento A2:C10 stulpelyje ("Table_array". Šiuo atveju Excel ieškoma stulpelyje A, o ne stulpelyje B.

Sprendimas: Galite pabandyti išspręsti šią problemą koreguodami savo VLOOKUP, kad būtų galima nurodyti tinkamą stulpelį. Jei tai neįmanoma, bandykite perkelti stulpelius. Tai taip pat gali būti labai neįmanoma, jei turite didelių arba sudėtingų skaičiuoklių, kuriose langelių reikšmės yra kitų skaičiavimų rezultatai, arba galbūt yra kitų loginių priežasčių, kodėl tiesiog negalite perkelti stulpelių aplink. Sprendimas yra naudoti funkcijų INDEX ir MATCH derinį, kuris gali ieškoti reikšmės stulpelyje neatsižvelgiant į jo vietą peržvalgos lentelėje. Žr. paskesnį skyrių.

Verčiau naudokite INDEX/MATCH

INDEX ir MATCH yra puikios parinktys daugeliu atvejų, kai VLOOKUP neatitinka jūsų poreikių. Pagrindinis INDEX/MATCH pranašumas yra tas, kad galite ieškoti reikšmės stulpelyje bet kurioje peržvalgos lentelės vietoje. INDEX grąžina reikšmę iš nurodytos lentelės / diapazono pagal jos padėtį. FUNKCIJA MATCH grąžina santykinę reikšmės padėtį lentelėje / diapazone. Naudokite index ir MATCH kartu formulėje, norėdami ieškoti reikšmės lentelėje / masyve, nurodydami santykinę reikšmės padėtį lentelėje / masyve.

Užuot naudojus VLOOKUP, index/match yra keli pranašumai:

  • Naudojant INDEX ir MATCH, grąžinama reikšmė neturi būti tame pačiame stulpelyje kaip peržvalgos stulpelis. Tai skiriasi nuo VLOOKUP, kurioje grąžinama reikšmė turi būti nurodytame diapazone. Kaip tai svarbu? Naudodami VLOOKUP turite žinoti stulpelio numerį, kuriame yra grąžinama reikšmė. Nors tai gali atrodyti sudėtinga, gali būti sudėtinga, kai turite didelę lentelę ir turite suskaičiuoti stulpelių skaičių. Be to, jei į lentelę įtrauksite / pašalinsite stulpelį, turite iš naujo susekite ir atnaujinkite col_index_num argumentą. Naudojant INDEX ir MATCH nereikia skaičiuoti, nes peržvalgos stulpelis skiriasi nuo stulpelio, kuriame yra grąžinama reikšmė.

  • Naudodami INDEX ir MATCH galite nurodyti eilutę arba stulpelį masyve arba nurodyti abu. Tai reiškia, kad galite ieškoti reikšmių vertikaliai ir horizontaliai.

  • INDEX ir MATCH galima naudoti norint ieškoti reikšmių bet kuriame stulpelyje. Skirtingai nei VLOOKUP – kai galite ieškoti reikšmės tik pirmajame lentelės stulpelyje– INDEX ir MATCH veiks, jei peržvalgos reikšmė yra pirmajame stulpelyje, paskutiniame arba bet kurioje tarpo vietoje.

  • INDEX ir MATCH suteikia galimybę lanksčiai pateikti dinaminę nuorodą į stulpelį, kuriame yra grąžinama reikšmė. Tai reiškia, kad į lentelę galite įtraukti stulpelių nesulaužę INDEX ir MATCH. Kita vertus, VLOOKUP lūžiai, jei reikia įtraukti stulpelį į lentelę, nes ji pateikia statinę nuorodą į lentelę.

  • INDEX ir MATCH suteikia daugiau lankstumo su atitikmenimis. INDEX ir MATCH gali rasti tikslų atitikmenį arba reikšmę, kuri yra didesnė arba mažesnė už peržvalgos reikšmę. VLOOKUP ieškos tik arčiausiai reikšmės atitikmens (pagal numatytuosius nustatymus) arba tikslios reikšmės. VLOOKUP pagal numatytuosius nustatymus taip pat daro prielaidą, kad pirmasis lentelės masyvo stulpelis rūšiuojamas abėcėlės tvarka ir tarkime, kad lentelė nėra nustatyta taip, VLOOKUP grąžins pirmąjį artimiausią atitikmenį lentelėje, o tai gali būti ne ieškomi duomenys.

Sintaksė

Norėdami sukurti INDEX/MATCH sintaksę, turite naudoti masyvo / nuorodos argumentą iš funkcijos INDEX ir įdėti MATCH sintaksę į jos vidų. Tai forma:

=INDEX(masyvas arba nuoroda, MATCH(lookup_value,lookup_array,[match_type])

Panaudokime INDEX/MATCH, kad pakeistų VLOOKUP iš aukščiau pateiktame pavyzdyje. Sintaksė atrodys taip:

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

Paprastai anglų kalba tai reiškia:

=INDEX(grąžina reikšmę iš C2:C10, kuri bus MATCH(Kale, kuri yra kur nors masyve B2:B10, kurioje grąžinama reikšmė yra pirmoji reikšmė, atitinkanti "Kale"))

Funkcijos INDEX ir MATCH gali būti naudojamos kaip VLOOKUP pakeitimas

Formulė ieško pirmos reikšmės langelyje C2:C10, kuri atitinka "Kale" (B7) ir grąžina reikšmę langelyje C7 (100), kuri yra pirmoji reikšmė, atitinkanti "Kale".

Problema: tikslus atitikmuo nerastas

Kai range_lookup argumentas FALSE ( ir VLOOKUP negali rasti tikslaus atitikmens jūsų duomenyse), jis pateikia #N/A klaidą.

Sprendimas: Jei esate tikri, kad jūsų skaičiuoklėje yra atitinkami duomenys, o VLOOKUP jų negaus, užtruks patikrinti, ar nurodyti langeliai neturi paslėptų tarpų arba nespausdina simbolių. Taip pat įsitikinkite, kad langeliai turi tinkamą duomenų tipą. Pavyzdžiui, langeliai su skaičiais turi būti suformatuoti kaip Skaičius, o ne Tekstas.

Taip pat apsvarstykite galimybę naudoti funkciją CLEAN arba TRIM, kad išvalytų duomenis langeliuose.

Problema: peržvalgos reikšmė yra mažesnė už mažiausią masyvo reikšmę

Jei range_lookup argumentas nustatytas kaip TRUE (teisinga), o peržvalgos reikšmė yra mažesnė už mažiausią masyvo reikšmę, matysite klaidą #N/A. TRUE ieško apytikslio atitikmens masyve ir grąžina artimiausią reikšmę, mažesnę už peržvalgos reikšmę.

Toliau pateiktame pavyzdyje peržvalgos reikšmė yra 100, bet diapazone B2:C10 nėra reikšmių, kurios būtų mažesnės nei 100; taigi klaida.

Klaida N/A VLOOKUP, kai peržvalgos reikšmė yra mažesnė už mažiausią masyvo reikšmę

Sprendimas:

  • Jei reikia, pataisykite peržvalgos reikšmę.

  • Jei negalite pakeisti peržvalgos reikšmės ir reikia daugiau lankstumo su atitinkančiomis reikšmėmis, apsvarstykite galimybę vietoj VLOOKUP naudoti INDEX/MATCH – žr. šio straipsnio skyrių. Naudodami INDEX/MATCH, galite ieškoti reikšmių, didesnių už, mažiau arba lygias peržvalgos reikšmei. Daugiau informacijos apie INDEX/MATCH, o ne VLOOKUP, žr. ankstesnį šios temos skyrių.

Problema: peržvalgos stulpelis nerūšiuojamas didėjimo tvarka

Jei range_lookup argumentas nustatytas kaip TRUE (teisinga), o vienas iš peržvalgos stulpelių nerūšiuojamas didėjimo (A-Z) tvarka, matysite klaidą #N/A.

Sprendimas:

  • Pakeiskite funkciją VLOOKUP, kad ieškote tikslios atitikties. Norėdami tai padaryti, nustatykite argumento range_lookupfalse. False rūšiavimas nebūtinas.

  • Naudokite funkciją INDEX/MATCH, jei norite ieškoti nerūšiuotos lentelės reikšmės.

Problema: reikšmė yra didelis slankiojo kablelio skaičius

Jei langeliuose yra laiko reikšmių arba didelių dešimtainių skaičių, Excel grąžins #N/A klaidą dėl slankiojo kablelio tikslumo. Slankiojo kablelio skaičiai yra skaičiai, kurie seka po dešimtainio kablelio. (Excel laiko reikšmes saugo kaip slankiojo kablelio skaičius.) Excel negalima saugoti skaičių su labai dideliais slankiojo kablelio taškais, todėl norint, kad funkcija veiktų tinkamai, slankiojo kablelio skaičius reikės suapvalinti iki 5 dešimtainių vietų.

Sprendimas:sutrumpinkite skaičius suapvalindami juos iki penkių dešimtainių vietų naudodami funkciją ROUND.

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės arba gauti pagalbos iš Atsakymų bendruomenės.

Taip pat žr.

Reikia daugiau pagalbos?

Tobulinkite savo įgūdžius
Ieškoti mokymo

Ar ši informacija buvo naudinga?

Ar esate patenkinti kalbos kokybe?
Kas turėjo įtakos jūsų įspūdžiams?

Dėkojame už jūsų atsiliepimus!

×