Šioje temoje aprašomos dažniausios VLOOKUP priežastys dėl klaidingo funkcijos rezultato ir pateikiami pasiūlymai, kaip naudoti INDEX ir MATCH .
Patarimas: Taip pat žr. sparčiųjų nuorodų kortelę: VLOOKUP trikčių diagnostikos patarimai , kurie pateikia įprastas priežastis #NA problemas patogiame PDF faile. Galite bendrinti PDF su kitais arba spausdinti savo nuorodai.
Problema: peržvalgos reikšmės nėra pirmajame argumento table_array stulpelyje
Vienas iš VLOOKUP apribojimų yra tai, kad jis gali ieškoti reikšmių tik kairiajame lentelės masyvo stulpelyje. Jei jūsų peržvalgos reikšmės nėra pirmame masyvo stulpelyje, matysite klaidą #N/A.
Šioje lentelėje norime gauti Kale parduotų vienetų skaičių.
Klaidos #N/A rezultatai, nes peržvalgos reikšmė Kale rodoma table_array argumento A2:C10 antrame stulpelyje (daržovės ir vaisiai). Šiuo atveju "Excel" ieško jos A stulpelyje, o ne B stulpelyje.
Sprendimas: Galite pabandyti išspręsti šią problemą koreguodami VLOOKUP, kad nurodytumėte teisingą stulpelį. Jei tai neįmanoma, bandykite perkelti stulpelius. Tai gali būti labai neįveiktina, jei turite didelių arba sudėtingų skaičiuoklių, kurių langelių reikšmės yra kitų skaičiavimų rezultatai, arba galbūt yra kitų loginių priežasčių, kodėl tiesiog negalite perkelti stulpelių. Sprendimas yra naudoti INDEX ir MATCH funkcijų derinį, kuris gali ieškoti reikšmės stulpelyje neatsižvelgiant į jos vietą peržvalgos lentelėje. Žr. kitą skyrių.
Verčiau naudokite INDEX/MATCH
INDEX ir MATCH yra geros galimybės daugeliu atvejų, kai funkcija VLOOKUP neatitinka jūsų poreikių. Pagrindinis INDEX/MATCH pranašumas yra tai, kad galite ieškoti reikšmės stulpelyje bet kurioje peržvalgos lentelės vietoje. Funkcija INDEX grąžina reikšmę iš nurodytos lentelės/diapazono pagal jos padėtį. FUNKCIJA MATCH grąžina santykinę reikšmės poziciją lentelėje / diapazone. Formulėje naudokite INDEX ir MATCH kartu, kad ieškotumėte reikšmės lentelėje / masyve nurodydami santykinę reikšmės padėtį lentelėje / masyve.
Yra keli INDEX/MATCH naudojimo vietoj VLOOKUP 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ėje įtraukiate / pašalinate stulpelį, turite perskaičiati ir atnaujinti 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 juos abu. Tai reiškia, kad galite ieškoti reikšmių tiek vertikaliai, tiek horizontaliai.
-
INDEX ir MATCH gali būti naudojamos ieškant reikšmių bet kuriame stulpelyje. Skirtingai nei VLOOKUP, kurioje galite ieškoti reikšmės tik pirmajame lentelės stulpelyje, INDEX ir MATCH veiks, jei jūsų peržvalgos reikšmė yra pirmajame stulpelyje, paskutinėje ar bet kurioje vietoje tarp jų.
-
INDEX ir MATCH leidžia lanksčiai naudoti dinamines nuorodas į stulpelį, kuriame yra grąžinama reikšmė. Tai reiškia, kad galite įtraukti stulpelių į lentelę netrikdydami INDEX ir MATCH. Kita vertus, funkcija VLOOKUP sugenda, jei reikia įtraukti stulpelį į lentelę, nes ji daro statinę nuorodą į lentelę.
-
INDEX ir MATCH siūlo daugiau lankstumo su atitikmenimis. INDEX ir MATCH gali rasti tikslų atitikmenį arba reikšmę, didesnę ar mažesnę už peržvalgos reikšmę. VLOOKUP ieškos tik artimiausio reikšmės atitikmens (pagal numatytuosius nustatymus) arba tikslios reikšmės. Pagal numatytuosius nustatymus funkcija VLOOKUP daro prielaidą, kad pirmas lentelės masyvo stulpelis surūšiuotas abėcėlės tvarka, ir tarkime, kad jūsų lentelė taip nenustatyta, funkcija VLOOKUP pateiks pirmą artimiausią atitikmenį lentelėje, o tai gali būti ne jūsų ieškomi duomenys.
Sintaksė
Norėdami sukurti INDEX/MATCH sintaksę, turite naudoti masyvo / nuorodos argumentą iš funkcijos INDEX ir įdėti MATCH sintaksę į ją. Tai gali būti:
=INDEX(masyvas arba nuoroda, MATCH(lookup_value,lookup_array,[match_type])
Panaudokime INDEX/MATCH, kad pakeistumėte VLOOKUP aukščiau pateiktame pavyzdyje. Sintaksė atrodys taip:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Paprastai kalbant, tai reiškia:
=INDEX(grąžina reikšmę iš C2:C10, kuri bus MATCH(Kale, kuri yra kažkur B2:B10 masyve, kuriame grąžinama reikšmė yra pirmoji reikšmė, atitinkanti Kale))
Formulė ieško pirmosios langelio C2:C10 reikšmės, kuri atitinka Kale (B7) ir grąžina reikšmę langelyje C7 (100), kuri yra pirmoji reikšmė, atitinkanti Kale.
Problema: tikslaus atitikmens nerasta
Kai argumentas range_lookup yra FALSE (klaidinga), o VLOOKUP nepavyksta rasti tikslios duomenų atitikties, ji grąžina klaidą #N /A.
Sprendimas: jei esate tikri, kad atitinkami duomenys yra skaičiuoklėje ir VLOOKUP jų nepagauna, užveskite laiko patikrinti, ar nurodyti langeliai neturi paslėptų tarpų ar nespausdinamų simbolių. Taip pat įsitikinkite, kad langeliai atitinka tinkamą duomenų tipą. Pavyzdžiui, langeliai su skaičiais turi būti suformatuoti kaip skaičius, o ne tekstas.
Taip pat duomenims langeliuose valyti apsvarstykite galimybę naudoti funkciją CLEAN arba TRIM .
Problema: peržvalgos reikšmė yra mažesnė nei mažiausia masyvo reikšmė
Jei argumentas range_lookup 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 apytikslės atitikties masyve ir pateikia 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 už 100; todėl klaida.
Sprendimas:
-
Prireikus ištaisykite peržvalgos reikšmę.
-
Jei peržvalgos reikšmės keisti negalite ir reikia daugiau lankstumo taikant atitinkančias reikšmes, apsvarstykite galimybę naudoti INDEX/MATCH vietoj VLOOKUP – žr. šio straipsnio aukščiau pateiktą skyrių. Naudodami INDEX/MATCH galite ieškoti reikšmių, didesnių, mažesnių arba lygių peržvalgos reikšmei. Daugiau informacijos apie INDEX/MATCH naudojimą vietoj VLOOKUP rasite ankstesniame šios temos skyriuje.
Problema: peržvalgos stulpelis nesurūšiuotas didėjimo tvarka
Jei argumentas range_lookup nustatytas kaip TRUE (ir vienas iš peržvalgos stulpelių nesurūšiuotas) didėjimo tvarka (A–Z), matysite klaidą #N/A.
Sprendimas:
-
Pakeiskite funkciją VLOOKUP, kad ieškotumėte tikslaus atitikmens. Norėdami tai padaryti, nustatykite argumento range_lookupreikšmę FALSE. Nėra būtina rūšiuoti naudojant FALSE.
-
Naudokite funkciją INDEX/MATCH, kad ieškotumėte reikšmės nesurūšiuotoje lentelėje.
Problema: reikšmė yra didelis slankiojo kablelio skaičius
Jei langeliuose yra laiko reikšmių arba didelių dešimtainių skaičių, programa "Excel" pateikia klaidą #N/A dėl slankiojo kablelio tikslumo. Slankiojo kablelio skaičiai yra skaičiai, einantys po dešimtainio kablelio. (Programa "Excel" laiko reikšmes saugo kaip slankiųjų kablelių skaičius.) Excel negali saugoti labai didelių slankiųjų taškų skaičių, todėl kad funkcija tinkamai veiktų, slankiųjų kablelių skaičius turi būti suapvalintas iki 5 dešimtainių skilčių.
Sprendimas: sutrumpinkite skaičius apvalindami juos iki penkių dešimtainių skilčių, naudodami funkciją ROUND .
Reikia daugiau pagalbos?
Visada galite paklausti „Excel“ technologijų bendruomenės specialisto arba gauti palaikymo bendruomenėse.
