Jak opravit chybu #NENÍ_K_DISPOZICI u funkce SVYHLEDAT

V tomto tématu jsou popsané nejčastější důvody, proč nefunguje funkce SVYHLEDAT.

Tip: Podívejte se na stručnou referenční kartu: Funkce SVYHLEDAT – tipy pro odstraňování potíží, která popisuje nejčastější důvody chyby #NENÍ_K_DISPOZICI při použití funkce SVYHLEDAT v praktickém souboru PDF (Portable Document Format). Soubor PDF můžete sdílet s ostatními nebo si ho můžete vytisknout.

Problém: Hledaná hodnota není v prvním sloupci v argumentu pole.

Jedním z hlavních omezení funkce SVYHLEDAT je, že hledá jenom hodnoty v levém krajním sloupci tabulky. Pokud hledaná hodnota není v prvním sloupci tabulky, zobrazí se chyba #NENÍ_K_DISPOZICI.

V následující tabulce chceme načíst počet prodaných kusů kapusty.

Chyba #NENÍ_K_DISPOZICI u funkce SVYHLEDAT: Hledaná hodnota není v prvním sloupci matice tabulky

Příčinou chyby je, že hledaná hodnota „Kapusta“ je ve druhém sloupci (Produkt) v argumentu pole A2:C10. Excel ji hledá ve sloupci A, nikoli ve sloupci B.

Řešení: Můžete zkusit opravit funkci SVYHLEDAT tak, aby odkazovala na správný sloupec. Pokud to není možné, zkuste přesunout sloupce. Nemusí to být vždy praktické, třeba když máte velké nebo složité tabulky, kde jsou hodnoty buněk výsledkem jiných výpočtů. Mohou také existovat jiné logické důvody, proč sloupce nejdou jednoduše přesunout. Řešením je společné použití funkcí INDEX a POZVYHLEDAT, které hodnotu vyhledají ve sloupci bez ohledu na jeho umístění v tabulce.

Použití funkcí INDEX/POZVYHLEDAT místo SVYHLEDAT

Funkce INDEX/POZVYHLEDAT můžete použít tam, kde nevyhovuje funkce SVYHLEDAT. Největší výhodou funkcí INDEX/POZVYHLEDAT je, že můžete hledat hodnotu ve sloupci, který je v tabulce na libovolném místě. Funkce INDEX vrátí hodnotu ze zadané tabulky nebo oblasti na základě její pozice a funkce POZVYHLEDAT vrátí relativní pozici hodnoty v tabulce nebo oblasti. Společným použitím funkcí INDEX a POZVYHLEDAT ve vzorci najdete v tabulce nebo matici hodnotu tím, že zadáte její relativní pozici v tabulce nebo matici.

Funkce INDEX/POZVYHLEDAT mají oproti funkci SVYHLEDAT řadu výhod:

  • V případě funkcí INDEX a POZVYHLEDAT nemusí být vrácená hodnota ve stejném sloupci, ve kterém ji hledáte. Funkce SVYHLEDAT naopak musí mít vrácenou hodnotu v zadané oblasti. Proč je to důležité? U funkce SVYHLEDAT musíte znát číslo sloupce, které obsahuje vrácenou hodnotu. Zdá se to být jednoduché, ale u velké tabulky může být obtížné spočítat počet sloupců. Pokud do tabulky přidáte sloupce nebo je z ní odeberete, musíte argument čísla indexu sloupce přepočítat a aktualizovat ho. Když použijete funkce INDEX a POZVYHLEDAT, nemusíte nic počítat, protože prohledávaný sloupec je jiný než sloupec s vrácenou hodnotou.

  • U funkcí INDEX a POZVYHLEDAT můžete zadat řádek nebo sloupec matice (nebo můžete zadat obojí). To znamená, že hodnoty můžete hledat svisle i vodorovně.

  • Funkce INDEX a POZVYHLEDAT je možné použít k hledání hodnot v libovolném sloupci. Na rozdíl od funkce SVYHLEDAT, která hledá hodnotu jen v prvním sloupci tabulky, budou funkce INDEX a POZVYHLEDAT fungovat bez ohledu na to, jestli je hledaná hodnota v prvním nebo posledním sloupci (nebo někde mezi nimi).

  • Funkce INDEX a POZVYHLEDAT umožňují používat flexibilní dynamické odkazy na sloupec, který obsahuje vrácenou hodnotu. To znamená, že do tabulky můžete přidávat sloupce bez poškození funkcí INDEX a POZVYHLEDAT. Naproti tomu funkce SVYHLEDAT se při přidání sloupce do tabulky poruší, protože používá statický odkaz.

  • Funkce INDEX a POZVYHLEDAT nabízejí větší flexibilitu, co se týče hledaných výsledků. Funkce INDEX a POZVYHLEDAT mohou najít úplně stejnou, větší nebo menší hodnotu, než je hledaná hodnota. Funkce SVYHLEDAT najde jen nejbližší (výchozí nastavení) nebo úplně stejnou hodnotu. Funkce SVYHLEDAT také implicitně předpokládá, že první sloupec v matici tabulky je seřazený podle abecedy. Pokud tabulka není takto seřazená, funkce SVYHLEDAT vrátí první nejbližší hodnotu v tabulce, což nemusí být hodnota, kterou skutečně hledáte.

Syntaxe

Pokud chcete vytvořit syntaxi funkcí INDEX/POZVYHLEDAT, musíte použít argument oblast/odkaz funkce INDEX a vnořit do něj syntaxi funkce POZVYHLEDAT. Příklad:

=INDEX(matice nebo odkaz; POZVYHLEDAT(co;prohledat;[shoda])

Teď funkcemi INDEX/POZVYHLEDAT nahradíme funkci SVYHLEDAT v předchozím příkladu. Syntaxe bude vypadat takto:

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

Slovní popis funkce:

=INDEX(chceme vrátit hodnotu z matice C2:C10; která odpovídá funkci POZVYHLEDAT(Kapusta; která je někde v matici B2:B10; a vrácená hodnota je první hodnota odpovídající zboží Kapusta))

Použití funkcí INDEX a POZVYHLEDAT k nahrazení funkce SVYHLEDAT

Vzorec hledá první hodnotu v matici C2:C10, která odpovídá zboží Kapusta (v buňce B7), a vrátí hodnotu v buňce C7 (100), což je první hodnota, která odpovídá zboží Kapusta.

Problém: Přesná shoda se nenašla

Pokud má argument oblasti vyhledávání hodnotu NEPRAVDA, nedokáže funkce SVYHLEDAT najít v datech přesnou shodu a vrátí chybu #NENÍ_K_DISPOZICI.

Řešení: Pokud určitě víte, že odpovídající data v tabulce existují, ale funkce SVYHLEDAT je nedokáže najít, zkontrolujte, jestli odkazované buňky neobsahují skryté mezery nebo netisknutelné znaky. Ověřte také správný datový typ buněk. Například buňky s čísly by měly mít formát Číslo, nikoli Text.

K vyčištění dat v buňkách použijte funkce VYČISTIT nebo PROČISTIT.

Problém: Hledaná hodnota je menší než nejmenší hodnota v matici

Pokud argument oblasti vyhledávání sice je nastavený na hodnotu PRAVDA, ale hledaná hodnota je menší než nejmenší hodnota v dané matici, zobrazí se chyba #NENÍ_K_DISPOZICI. Hodnota PRAVDA hledá v matici přibližnou shodu a vrátí nejblíže menší hodnotu, než jakou hledáte.

V následujícím příkladu je hledaná hodnota 100, ale v oblasti B2:C10 není žádná hodnota menší než 100, a proto se zobrazí chyba.

Chyba #NENÍ_K_DISPOZICI u funkce SVYHLEDAT, když je hledaná hodnota menší než nejmenší hodnota v matici

Řešení:

  • Opravte hledanou hodnotu tak, jak potřebujete.

  • Pokud hledanou hodnotu nemůžete změnit a potřebujete u shodných hodnot větší flexibilitu, měli byste místo funkce SVYHLEDAT použít funkce INDEX/POZVYHLEDAT. Funkce INDEX/POZVYHLEDAT umí hledat hodnoty, které jsou větší, menší nebo rovny hledané hodnotě. Další informace o použití funkcí INDEX/POZVYHLEDAT místo funkce SVYHLEDAT najdete v předchozí části tohoto tématu.

Problém: Prohledávaný sloupec není seřazený vzestupně.

Pokud je argument oblasti vyhledávání nastavený na hodnotu TRUE a jeden z prohledávaných sloupců není seřazený vzestupně (A–Z), zobrazí se chyba #NENÍ_K_DISPOZICI.

Řešení:

  • Změňte funkci SVYHLEDAT tak, aby hledala přesnou hodnotu. Uděláte to tak, že argument oblasti vyhledávání nastavíte na hodnotu NEPRAVDA. Hodnota NEPRAVDA řazení nevyžaduje.

  • K vyhledání hodnoty v neseřazené tabulce použijte funkce INDEX/POZVYHLEDAT.

Problém: Hodnota je velké číslo s plovoucí desetinnou čárkou.

Pokud máte v buňkách časové hodnoty nebo čísla s velkým počtem desetinných míst, vrátí Excel chybu #NENÍ_K_DISPOZICI kvůli omezené přesnosti čísel s plovoucí desetinnou čárkou. Čísla s číslicemi za desetinnou čárkou se ukládají jako čísla s plovoucí desetinnou čárkou. (Excel ukládá jako čísla s plovoucí desetinnou čárkou i časové hodnoty.) Excel nedokáže uložit čísla s velkým počtem desetinných míst. Pokud má tato funkce správně fungovat, je potřeba čísla s plovoucí desetinnou čárkou zaokrouhlit na 5 desetinných míst.

Řešení: Zkraťte čísla tím, že je zaokrouhlíte na pět desetinných míst funkcí ZAOKROUHLIT.

Máte konkrétní otázku k funkci?

Zadejte dotaz do komunitního fóra Excelu

Pomozte nám vylepšit Excel

Máte nějaké návrhy na zlepšení dalších verzí Excelu? Pokud ano, podívejte se prosím na témata v části Názor uživatele Excelu.

Viz taky

Oprava chyby #NENÍ_K_DISPOZICI

SVYHLEDAT: Jak se vyhnout chybě #NENÍ_K_DISPOZICI

Excelové funkce VVYHLEDAT, SVYHLEDAT a VYHLEDAT vracejí nesprávné hodnoty

Aritmetické operace s plovoucí desetinnou čárkou mohou v aplikaci Excel poskytovat nepřesné výsledky

Stručná referenční karta: Opakování funkce SVYHLEDAT

Funkce SVYHLEDAT

Přehled vzorců v Excelu

Jak se vyhnout nefunkčním vzorcům

Zjišťování chyb ve vzorcích

Všechny funkce Excelu (podle abecedy)

Všechny funkce Excelu (podle kategorie)

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

Děkujeme vám za zpětnou vazbu.

Děkujeme vám za váš názor. Vypadá to, že bude užitečné, když vás spojíme s některým z našich agentů z podpory Office.

×