Oprava chyby #NEDOSTUPNÝ vo funkcii VLOOKUP

Poznámka:  Radi by sme vám čo najrýchlejšie poskytovali najaktuálnejší obsah Pomocníka vo vašom vlastnom jazyku. Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Dali by ste nám v dolnej časti tejto stránky vedieť, či boli pre vás tieto informácie užitočné? Tu nájdete anglický článok pre jednoduchú referenciu.

Táto téma popisuje najbežnejšie dôvody chybné výsledok funkcie VLOOKUP , a poskytuje návrhy namiesto toho použiť funkcií INDEX a MATCH .

Tip: Označovať Stručná referenčná karta: Tipy na riešenie problémov čo predstavuje najbežnejšie dôvody na #NA problémy v súbore PDF pohodlné. Môžete s ostatnými zdieľať súbor PDF alebo vytlačiť pre vlastné potreby.

Problém: Hľadaná hodnota nie je v prvom stĺpci v argumente pole_tabuľky

Jedno obmedzenie VLOOKUP je, že len môžete vyhľadať hodnoty na ľavý krajný stĺpec v poli tabuľky. Ak váš hľadanou hodnotou, nie je v prvom stĺpci poľa, zobrazí sa chyba #nedostupný.

V nasledujúcej tabuľke chceme načítať množstvo predaného kelu.

Chyba #NEDOSTUPNÝ vo funkcii VLOOKUP: Hľadaná hodnota nie je v prvom stĺpci poľa tabuľky

Chyba #nedostupný výsledkov za hľadanou hodnotou "Kel" sa zobrazí v druhom stĺpci argumentu Pole_tabuľky A2:C10 (zelenina a ovocie). V tomto prípade program Excel hľadá ju v stĺpci A, nie stĺpca B.

Riešenie: môžete sa pokúsiť tento problém riešiť úpravou vašej VLOOKUP neodkazuje správne stĺpca. Ak to nie je možné, potom skúste stĺpcov. Ktoré môže byť veľmi to nie je možné, ak máte veľké alebo zložité tabuľky hodnôt buniek, kde sa nachádzajú výsledky iné výpočty, alebo možno existujú ďalšie logické dôvody, prečo jednoducho nemôžete pohybovať stĺpce. Riešenie je použiť kombináciu funkcií INDEX a MATCH, ktoré môžete vyhľadať hodnoty v stĺpci bez ohľadu na jeho miesto miesto v tabuľke s vyhľadávaním. Nájdete v ďalšej časti.

Odporúčame namiesto toho použiť INDEX a MATCH

INDEX a MATCH sú dobré možnosti pre mnohých prípadoch, v ktorom funkcia VLOOKUP nespĺňa vaše potreby. Hlavnou výhodou INDEX a MATCH je, že môžete vyhľadať hodnoty v stĺpci na ľubovoľnom mieste v vyhľadávaciu tabuľku. Funkcia INDEX vráti hodnotu zo zadaného tabuľka alebo rozsah – podľa svoju pozíciu. Funkcia MATCH vráti relatívnu pozíciu hodnoty v tabuľka alebo rozsah. Použitie funkcií INDEX a MATCH spolu vo vzorci na vyhľadanie hodnoty v poli tabuľka alebo zadaním relatívnu pozíciu hodnoty v poli tabuľky.

Existuje niekoľko výhod používania INDEX a MATCH namiesto funkcia VLOOKUP:

  • Pomocou funkcií INDEX a MATCH, vrátená hodnota nemusí byť v tom istom stĺpci ako vyhľadávací stĺpec. Toto je odlišné od funkcie VLOOKUP, v ktorom vrátenú hodnotu musí byť v danom rozsahu. Ako je to dôležité? Pri použití funkcie VLOOKUP, musíte vedieť číslo stĺpca, ktorý obsahuje vrátenú hodnotu. Keď to nemusí vyzerať náročná, môže byť náročný, keď máte veľké tabuľky a spočítať počet stĺpcov. Tiež, ak ste pridať alebo odstrániť stĺpec v tabuľke, máte prepočítanie a aktualizovať argument číslo_indexu_stĺpca . Pomocou funkcií INDEX a MATCH, žiadne počítanie nevyžaduje ako vyhľadávací stĺpec sa líši od stĺpca, ktorý obsahuje vrátenú hodnotu.

  • Funkcií INDEX a MATCH, môžete určiť buď riadok alebo stĺpec v poli – alebo ich zadať oba. Znamená to, že môžete vyhľadať hodnoty zvislo aj vodorovne.

  • Funkcií INDEX a MATCH možno použiť na vyhľadanie hodnôt v každom stĺpci. Na rozdiel od funkcie VLOOKUP, v ktorom môžete len vyhľadať hodnoty v prvom stĺpci tabuľky – funkcií INDEX a MATCH bude fungovať, ak váš hľadanou hodnotou je v prvom stĺpci, posledný alebo kdekoľvek medzi nimi.

  • Funkcií INDEX a MATCH ponúkajú flexibilitu dynamické odkazu na stĺpec, ktorý obsahuje vrátenú hodnotu. Znamená to, že môžete pridať stĺpce do tabuľky bez prerušenia funkcií INDEX a MATCH. Na druhej strane VLOOKUP zlomy riadkov, ak chcete pridať stĺpec do tabuľky, pretože statický odkazuje na tabuľku.

  • Funkcií INDEX a MATCH ponúka väčšiu flexibilitu zodpovedajúcich. INDEX a MATCH môžete nájsť presnú zhodu, alebo hodnota, ktorá je väčší alebo menší než za hľadanou hodnotou. Funkcia VLOOKUP vyhľadá len najviac podobá na hodnotu (v predvolenom nastavení) alebo presnú hodnotu. VLOOKUP tiež predpokladá, že v predvolenom nastavení, zoradený prvý stĺpec tabuľky a Predpokladajme, že tabuľky nie je nastavený tak, funkcia VLOOKUP vráti prvý najviac podobá v tabuľke, ktorý nemusí byť údajov, ktoré hľadáte.

Syntax

Vytvoriť syntax pre INDEX a MATCH, musíte použitie argumentu poľa alebo odkazu z funkcie INDEX a vnoriť syntax MATCH vnútri. Túto formu:

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

Nahradenie VLOOKUP v príklade vyššie môžete použiť INDEX a MATCH. Syntax bude vyzerať takto:

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

Ak to zjednodušíme, táto syntax znamená:

= INDEX (vrátiť hodnotu zo C2:C10, ktorá bude zodpovedať (kel, ktorá sa niekde v poli B2: B10, v ktorom je vrátená hodnota prvú hodnotu zodpovedajúcu kel))

Namiesto funkcie VLOOKUP je možné použiť funkcie INDEX a MATCH

Vzorec vyhľadá prvú hodnotu v rozsahu C2:C10, ktorá zodpovedá hodnote Kel (v bunke B7), a vráti hodnotu v bunke C7 (100), ktorá je prvou hodnotou zodpovedajúcou hodnote Kel.

Problém: Presná zhoda sa nenašla

Ak je hodnota argumentu FALSE – a funkcia VLOOKUP patrí Nepodarilo sa nájsť presnú zhodu údajov – vráti chybovú hodnotu #nedostupný.

Riešenie: Ak ste si istí, relevantných údajov existuje v tabuľkovom hárku a VLOOKUP nie je zachytenie ho, trvať, overte, že hodnoty v bunkách nemajú skryté medzery ani netlačiteľné znaky. Presvedčte sa, či bunky podľa správneho typu údajov. Napríklad bunky s číslami by malo byť sformátovaných čísloa nie Text.

Zvážte tiež použitie funkcie CLEAN alebo TRIM na vyčistenie údajov do buniek.

Problém: Hľadaná hodnota je menšia ako najmenšia hodnota v poli

Ak je hodnota argumentu nastavená na hodnotu TRUE, a hľadaná_hodnota menšia ako najmenšia hodnota v poli, zobrazí sa chyba #nedostupný. TRUE vyzerá približnej zosúladenia poľa a vráti najbližšie hodnota menšia než hodnota v poli vyhľadávania.

V nasledujúcom príklade je hľadaná hodnota 100, no v rozsahu B2:C10 nie sú žiadne hodnoty menšie ako 100, a to spôsobuje chybu.

Chyba #NEDOSTUPNÝ vo funkcii VLOOKUP, ak je hľadaná hodnota menšia ako najmenšia hodnota v poli

Riešenie:

  • Opravte hľadanú hodnotu podľa potreby.

  • Ak nie je možné zmeniť za hľadanou hodnotou a potrebujete väčšiu flexibilitu so zodpovedajúcimi hodnotami, zvážte použitie INDEX a MATCH namiesto funkcie VLOOKUP, pozrite si časť vyššie v tomto článku. Pomocou funkcie INDEX a MATCH, môžete vyhľadať hodnoty väčšie ako, na menšie alebo rovné za hľadanou hodnotou. Ďalšie informácie o používaní INDEX a MATCH namiesto funkcie VLOOKUP, nájdete v predchádzajúcej časti tejto témy.

Problém: Vyhľadávací stĺpec nie je zoradený vo vzostupnom poradí

Ak je hodnota argumentu nastavená na hodnotu TRUE, a nie je niektorý z vyhľadávacích stĺpcov zoradené vzostupne (A-Z) – zobrazí sa chyba #nedostupný.

Riešenie:

  • Zmena funkcie VLOOKUP, ak chcete nájsť presnú zhodu. Tak, že nastavená hodnota argumentu False. Žiadne zoraďovanie je nevyhnutné pre FALSE.

  • Na vyhľadanie hodnoty v nezoradenej tabuľke použite funkcie INDEX a MATCH.

Problém: Hodnota je veľké číslo s pohyblivou desatinnou čiarkou

Ak máte hodnoty času alebo veľké desatinné čísla v bunkách, Excel vráti chybovú hodnotu #nedostupný z dôvodu plávajúci bod presnosť. Čísla s pohyblivou desatinnou sú čísla, ktoré nasledujú po desatinnej čiarky. (Excel ukladá hodnoty času ako čísla s pohyblivou desatinnou.) Excel nemôže uložiť čísla plávajúce bodmi veľmi veľké, takže pre danú funkciu správne pracovať s pohyblivou rádovou čiarkou čísla potrebovať zaokrúhlené na 5 desatinných miest.

Riešenie: Skráťte čísla ich zaokrúhlením na päť desatinných miest s použitím funkcie ROUND.

Máte otázku ku konkrétnej funkcii?

Uverejnite otázku vo fóre komunity používateľov Excelu

Pomôžte nám pri zlepšovaní Excelu

Máte nejaký nápad, ako by sme mohli zlepšiť nasledujúcu verziu Excelu? Ak áno, pozrite sa na témy na lokalite Excel User Voice.

Pozrite tiež

Rozšírte svoje zručnosti práce s balíkom Office
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×