Toto téma popisuje nejběžnější důvody funkce SVYHLEDAT pro chybný výsledek funkce a nabízí návrhy, jak místo toho použít funkce INDEX a POZVYHLEDAT .
Tip: Podívejte se také na stručnou referenční kartu: Funkce SVYHLEDAT – tipy pro odstraňování potíží, které představují běžné důvody chyb #NENÍ_K_DISPOZICI v praktickém souboru PDF. 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 omezení funkce SVYHLEDAT je, že může hledat pouze hodnoty ve sloupci tabulky, který je nejvíce vlevo. 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.
K chybě #NENÍ_K_DISPOZICI došlo, protože ve druhém sloupci (Plodiny) argumentu table_array A2:C10 se zobrazí vyhledávací hodnota Kapusta. V tomto případě ji Excel hledá ve sloupci A, ne 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. To nemusí být vždy proveditelné, 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. Podívejte se na další část.
Zvažte použití funkce INDEX/POZVYHLEDAT.
Funkce INDEX a POZVYHLEDAT jsou vhodnými možnostmi pro mnoho případů, kdy funkce SVYHLEDAT nevyhovuje vašim potřebám. Hlavní 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 podle její pozice. Funkce POZVYHLEDAT vrátí relativní pozici hodnoty v tabulce nebo oblasti. Použijte zároveň funkce INDEX a POZVYHLEDAT ve vzorci, abyste v tabulce nebo matici našli hodnotu tak, že zadáte její relativní pozici v tabulce nebo matici.
Funkce INDEX/POZVYHLEDAT mají oproti funkci SVYHLEDAT řadu výhod:
-
U funkcí INDEX a POZVYHLEDAT nemusí být vrácená hodnota ve stejném sloupci jako je prohledávaný sloupec. To se liší od funkce SVYHLEDAT, ve které musí být vrácená hodnota v zadaném rozsahu. 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 col_index_num 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 dynamické odkazy na sloupec, který obsahuje vrácenou hodnotu.To znamená, že můžete do tabulky přidat sloupce bez porušení 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. Tento formulář má následující podobu:
=INDEX(matice nebo odkaz; POZVYHLEDAT(co;prohledat;[shoda])
Pomocí funkce INDEX/POZVYHLEDAT nahradíme funkci SVYHLEDAT z výše uvedeného příkladu. Syntaxe bude vypadat takto:
=INDEX(C2:C10;POZVYHLEDAT(B13;B2:B10;0))
Slovní popis funkce:
=INDEX(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í položce Kapusta))
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.
Zvažte také použití funkce VYČISTIT nebo PROČISTIT k vyčištění dat v buňkách.
Problém: Hledaná hodnota je menší než nejmenší hodnota v matici
Pokud argument oblasti vyhledávání sice je nastavený na hodnotu PRAVDA, a 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.
Ř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, viz předchozí sekce tohoto článku. 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. Pro hodnotu NEPRAVDA není nutné žádné řazení.
-
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 nemůže uložit čísla s velkým počtem desetinných míst. Aby tato funkce správně fungovala, musíte čí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.
Potřebujete další pomoc?
Kdykoli se můžete zeptat odborníka z komunity Excel Tech nebo získat podporu v komunitách.
