Příklad: Kombinace funkce SVYHLEDAT s funkcí KDYŽ

Další velice častou chybou, které se uživatelé dopouštějí při použití funkce SVYHLEDAT pro vyhledání rozdílů v seznamech, je nezadání druhého parametru (tabulky, která se má prohledávat) ve tvaru absolutního odkazu. Při kopírování vzorce směrem dolů pak je „tažena dolů“ i tato oblast a funkce pak vlastně prohledává tabulku jen od řádku, na kterém se nachází. Následující obrázek ukazuje závažnost této chyby:

Nezadání druhého parametru ve tvaru absolutního odkazu.

Nezadání druhého parametru ve tvaru absolutního odkazu.

Místo toho, aby funkce zapsaná v buňce G10 prohledávala tabulku D2:E49, pracuje s tabulkou D9:E57, což znamená, že vůbec nebere v úvahu předchozí buňky oblasti dat. V případě poslední buňky G49 tabulka zasahuje do jediné buňky původní oblasti dat – a sice do buňky D49.

Abychom se vyvarovali uvedené chyby, je nutno zadat do funkce SVYHLEDAT odkaz na tabulku takovým způsobem, aby se v případě kopírování odkaz na tabulku nepřeadresovával:

  • zápisem parametru prohledávané tabulky jako absolutního. Konkrétně do původní buňky G2:
    =SVYHLEDAT(A2;$D$1:$E$49;1;0)
  • zápisem parametru prohledávané tabulky jako celé sloupce. Konkrétně do původní buňky G2:
    =SVYHLEDAT(A2;D:E;1;0)

Pokud používáme vyhledávací funkce v dokumentech určených pro prezentaci apod., není žádoucí, aby se v buňkách objevovaly chybové hodnoty. Neznalý uživatel potom neví, o jakou chybu se jedná a může tak být snížena důvěryhodnost našeho dokumentu. Proto je vhodné chybové hodnoty odstranit, případně nahradit jinou hodnotou.

Zde si ukážeme jeden způsob řešení uvedeného (naleznete jej na listu SVYHLEDAT-E). Následující obrázek nám ukazuje, jakým způsobem upravíme vzorec:

Jak se zbavit chybového hlášení.

Jak se zbavit chybového hlášení.

Původní vzorec =SVYHLEDAT(A;2D:E;1;0) změníme na složitější zápis
=KDYŽ(JE.NEDEF(SVYHLEDAT(A;2D:E;1;0));“NENALEZENO“;SVYHLEDAT(A2;D:E;1;0))

Základem tohoto řešení je použití funkce JE.NEDEF, která ověří, zda její argument je chybová hodnota #N/A ( hodnota nedostupná ), a vrátí hodnotu PRAVDA nebo NEPRAVDA. Pokud tedy funkce SVYHLEDAT nenalezne příslušnou hodnotu, tak vrací hodnotu #N/A, která je pomocí funkce JE.NEDEF změněna na hodnotu PRAVDA. Tento argument je potom vložen do funkce KDYŽ a ta zapíše do buňky text „NENALEZENO“, nebo jakýkoliv jiný specifikovaný ve funkci.

Pokud funkce SVYHLEDAT nalezne nějakou hodnotu, funkce JE.NEDEF vrátí hodnotu NEPRAVDA a vnější funkce KDYŽ výpočet posune do části odpovídající nesplněné podmínce – a zde máme opět uloženou funkci pro vyhledání SVYHLEDAT.
Tento postup je často používán a patří k základní výbavě při práci s vyhledávacími vzorci.

Pro úplnost příkladu jsme si navíc nastavili pomocí podmíněného formátování buňky tak, že pokud je v nich uveden text NENALEZENO, tak bude buňka zformátována červeným písmem a tučně (Formát/Podmíněné formátování):

Zvýraznění hodnoty prostřednictvím podmíněného formátování.

Zvýraznění hodnoty prostřednictvím podmíněného formátování.

Funkce SVYHLEDAT nepatří mezi nejrychlejší a rychlost přepočítávání sešitů klesá s množstvím těchto funkcí. Není tedy výjimkou, že rozsáhlé modely obsahují stovky i tisíce těchto funkcí. Pokud si uvědomíme, že navržený postup eliminace chybových hodnot zdvojnásobuje počet funkcí, je zřejmé, že dojde ke značnému zpomalení sešitu.

  • Snaha o řešení formou vypnutí přepočítávání buněk (s ručním přepočtem pomocí F9) není nejvhodnější, protože se často zapomene provést tento ruční přepočet a v tabulce zůstávají neaktualizované údaje. Tabulka díky tomu může mít hodnoty rozdílné až o stovky tisíc od skutečnosti.
  • Řešení nahrazení většiny vzorců v sešitu jejich hodnotami (rychlost zpracování se několikanásobně zrychlí), má důsledek obdobný. Místo zapomenutí ručního přepočtu se zapomenou (nebo částečně zapomenou) při aktualizaci vstupních dat nahradit hodnoty původními vzorci.
  • Dalším možným problémem je vkládání a vyjímání buněk z těchto tabulek. Funkce SVYHLEDAT je na uvedené velice citlivá a špatně provedené změny mohou také vést a často vedou ke snížení vypovídací schopnosti funkce.

Další příklady z praxe:

Autor textu: Ing. Marek Laurenčík, společnost JUBELA, s.r.o.