Toto téma popisuje nejčastější důvody funkce SVYHLEDAT pro chybný výsledek funkce a nabízí návrhy pro použití funkcí INDEX a MATCH.

Tip: Přečtěte si také stručnou referenční kartu: Tipy pro řešení potíží s funkcí SVYHLEDAT, které představují běžné důvody pro #NA problémů 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 jenom hodnoty v levém sloupci v matici tabulky. Pokud vyhledávací hodnota není v prvním sloupci matice, zobrazí se chybová #N/A.

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

Výsledkem #N/A, protože vyhledávací hodnota "Kale" se zobrazí ve druhém sloupci (Produce) argumentu table_array A2:C10. V takovém případě Excel hledá ve sloupci A, ne ve sloupci B.

Řešení:Tento problém můžete zkusit vyřešit úpravou funkce SVYHLEDAT tak, aby odkazovat na správný sloupec. Pokud to není možné, zkuste sloupce přesouvat. To může být také velmi nepraktické, pokud máte velké nebo složité tabulky, ve kterých jsou hodnoty buněk výsledkem jiných výpočtů – nebo existují i jiné logické důvody, proč sloupce jednoduše nemůžete přesouvat. Řešením je použít kombinaci funkcí INDEX a MATCH, která může vyhledat hodnotu ve sloupci bez ohledu na umístění ve vyhledávací tabulce. Podívejte se na další část.

Zvažte místo toho použití funkce INDEX/MATCH.

Funkce INDEX a SHODA jsou vhodné pro mnoho případů, kdy funkce SVYHLEDAT nesplňuje vaše potřeby. Hlavní výhodou INDEX/MATCH je, že můžete vyhledat hodnotu ve sloupci v libovolném umístění ve vyhledávací tabulce. Funkce INDEX vrátí hodnotu ze zadané tabulky nebo oblasti – podle její pozice. Funkce MATCH vrátí relativní pozici hodnoty v tabulce nebo oblasti. Pomocí funkce INDEX a SHODA společně ve vzorci vyhledejte hodnotu v tabulce nebo matici zadáním relativní pozice hodnoty v tabulce nebo matici.

Použití funkce INDEX/MATCH místo funkce SVYHLEDAT má několik výhod:

  • U hodnot INDEX a MATCH nemusí být vrácená hodnota ve stejném sloupci jako vyhledávací sloupec. To se liší od funkce SVYHLEDAT, ve které musí být vrácená hodnota v zadané oblasti. Jak na tom záleží? U funkce SVYHLEDAT musíte znát číslo sloupce, které obsahuje vrácenou hodnotu. I když se to nemusí zdát náročné, může být obtížné, když máte velkou tabulku a musíte spočítat počet sloupců. Pokud taky přidáte nebo odeberete sloupec v tabulce, budete muset přepočítat a aktualizovat col_index_num sloupec. U hodnot INDEX a MATCH se nevyžaduje žádné počítání, protože vyhledávací sloupec se liší od sloupce, který má vrácenou hodnotu.

  • V polích INDEX a MATCH můžete zadat řádek nebo sloupec v matici nebo 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, ve které můžete vyhledat jenom hodnotu v prvním sloupci tabulky, budou funkce INDEX a SHODA fungovat, pokud je vyhledávací hodnota v prvním sloupci, posledním sloupci nebo kdekoli mezi nimi.

  • Funkce INDEX a MATCH nabízejí flexibilitu dynamického odkazu na sloupec, který obsahuje vrácenou hodnotu. To znamená, že do tabulky můžete přidat sloupce bez porušení indexů a tabulek MATCH. Na druhou stranu funkce SVYHLEDAT přeruší, pokud potřebujete do tabulky přidat sloupec – protože na tabulku odkazuje statickým odkazem.

  • INDEX a MATCH nabízejí větší flexibilitu při shodách. Index a SHODA najdu přesnou shodu nebo hodnotu, která je větší nebo menší než vyhledávací hodnota. Funkce SVYHLEDAT bude hledat jen nejbližší shodu s hodnotou (ve výchozím nastavení) nebo přesnou hodnotou. Funkce SVYHLEDAT také ve výchozím nastavení předpokládá, že první sloupec v matici tabulky je seřazený podle abecedy, a předpokládejme, že tabulka není tímto způsobem nastavená, vrátí funkce SVYHLEDAT první nejbližší shodu v tabulce, což nemusí být data, která hledáte.

Syntaxe

Pokud chcete vytvořit syntaxi funkce INDEX/MATCH, musíte použít argument matice/odkaz z funkce INDEX a vnořit do něj syntaxi MATCH. Tato funkce má podobu:

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

Pomocí funkce INDEX/MATCH nahradíme funkce 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átí hodnotu z C2:C10, která bude MATCH(Kale, která je někde v matici B2:B10, ve které je vrácená hodnota první hodnota odpovídající Kale))

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 je range_lookup argument NEPRAVDA a funkce SVYHLEDAT nemůže najít přesnou shodu v datech, vrátí chybu #N/A.

Řešení:Pokud jste si jistí, že v tabulce existují příslušná data a funkce SVYHLEDAT je nezachytí, udělejte si čas, abyste ověřili, že odkazované buňky nemají skryté mezery nebo netiskne znaky. Ujistěte se také, že buňky dodržují správný datový typ. Například buňky s čísly by měly být formátovanéjako Číslo a ne Text.

Zvažte také použití funkce VYČISTIT nebo VYČISTIT k vyčištění dat v buňkách.

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

Pokud je range_lookup argument nastaven na HODNOTU PRAVDA a vyhledávací hodnota je menší než nejmenší hodnota v matici, zobrazí se chyba #N/A. 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 nemůžete změnit vyhledávací hodnotu a potřebujete větší flexibilitu s odpovídajícími hodnotami, zvažte použití funkce INDEX/MATCH místo funkce SVYHLEDAT – viz část výše v tomto č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 range_lookup argument nastaven na HODNOTU PRAVDA a jeden z vyhledávacích sloupců není seřazený vzestupně (A-Z), zobrazí se chyba #N/A.

Ř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. U funkce NEPRAVDA není řazení nutné.

  • 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 velká desetinná čísla, Excel vrátí chybu #N/A z důvodu přesnosti s plovoucí desetinnou čárkou. Čísla s plovoucí desetinnou čárkou jsou čísla, která následují za desetinnou čárkou. (Excel časové hodnoty jako čísla s plovoucí desetinnou čárkou.) Excel nelze ukládat čísla s velmi velkými plovoucími body, takže aby funkce fungovala správně, bude nutné zaokrouhlit čísla s plovoucí desetinnou čárkou 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?

Kdykoliv se můžete zeptat některého odborníka v technické komunitě Excelu nebo získat podporu v komunitě pro odpovědi.

Viz také

Potřebujete další pomoc?

Rozšiřte své znalosti a dovednosti
Projít školení
Získejte nové funkce jako první
Připojit se systém Microsoft Office insiderům

Byly tyto informace užitečné?

Jak jste spokojeni s kvalitou jazyka?
Co ovlivnilo váš názor?

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

×