Pole, které potřebovala prohledat, obsahovalo proměnný počet znaků jako předčíslí, za nímž následovalo číslo šeku a pak končilo ještě dalšími znaky. Většina lidí si neuvědomuje, že funkce VLOOKUP bude podporovat zástupné znaky. Ve skutečnosti se tým aplikace Excel ve společnosti Microsoft domnívá, že zástupné znaky se ve VLOOKUP častěji objevují omylem než tím, že je lidé používají záměrně. Protože většina lidí neví, že VLOOKUP bude podporovat zástupné znaky, zdá se pravděpodobnější, že lidé provedou VLOOKUP, kde jejich vyhledávací hodnota náhodou obsahuje znak, který nebyl zamýšlen jako zástupný znak.

POUŽITÍ ZÁSTUPNÝCH ZNAKŮ

Standardní zástupné znaky jsou hvězdička (*), která znamená libovolný počet znaků, a otazník (?), který znamená libovolný jeden znak. Co když ale skutečně musíte hledat hvězdičku nebo otazník? Použijte ~* pro vyhledání hvězdičky nebo ~? pro vyhledání otazníku. Pro hledání tildy použijte ~~.

Použití těchto tří znaků je obzvláště obtížné, pokud máte stovky VLOOKUPů a hodnoty již obsahují zástupné znaky. Snaha převádět hodnoty za běhu pomocí vnořených funkcí SUBSTITUTE se stává nepřehlednou. Představte si =VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2,“~“,“~~“), „*“,“~*“),“?“,“~?“). Ve skutečnosti by bylo lepší, kdyby Microsoft nabízel možnost, která by vypnula chování zástupných znaků pro konkrétní list nebo sešit.

Zpět k původnímu problému. Museli byste hledat *jablko*, abyste našli slovo „jablko“ uvnitř jakéhokoli textu v prvním sloupci vyhledávací tabulky. Protože však buňka A2 na obrázku 1 již obsahuje slovo „jablko“, vzorec začíná =VLOOKUP(„*“&A2&“*“. V tomto případě používáte znak zřetězení (&), abyste hodnotu v A2 obalili hvězdičkou na každé straně. Případně můžete použít =VLOOKUP(CONCATENATE(„*“,A2, „*“), abyste slovo „jablko“ obalili hvězdičkami.

Ve vzorci =VLOOKUP(„*“&A2&“*“,$D$2:$E$6,2,FALSE) je správně nalezeno $956 z buňky E5.

Při kontrole výsledků z vyhledávání se zástupnými znaky dbejte zvýšené opatrnosti. Co kdybyste hledali jak „apple“, tak „crabapple“? Hledání „crabapple“ by bylo správné, ale protože VLOOKUP vrací první shodu ve vyhledávací tabulce, VLOOKUP pro *apple* by vrátil hodnotu pro „crabapple“, pokud by byla ve vyhledávací tabulce nalezena před „apple“.

Vyhledání a sečtení všech shod

Když jsem na semináři o Excelu pracoval s příkladem, někdo další se ptal na situaci, kdy může existovat více shod a vy byste chtěli najít a sečíst všechny shodné výsledky.

Protože zástupný znak *apple* fungoval s VLOOKUP tak dobře, vytvořil jsem datovou sadu, kde jsme mohli vyzkoušet, zda SUMIFS také umožní zástupné znaky. V níže uvedené funkci SUMIFS vzorec říká, že se mají sečíst hodnoty ve sloupci E, pokud odpovídající hodnota ve sloupci D obsahuje *Apple*. Tento vzorec funguje také.

PŘIPRAVUJEME OBSLUHU ZÁKLADNÍCH KART

Překvapilo vás, že VLOOKUP umí pracovat se zástupnými znaky? Prováděli jste někdy VLOOKUP, kde hodnota vyhledávání může obsahovat hvězdičku nebo otazník jako součást normálního textu a není určena k použití jako zástupný znak?

Pokud je odpověď na některou z těchto otázek kladná, pak by pro vás pravděpodobně mělo smysl, aby společnost Microsoft nabídla novou, vylepšenou verzi VLOOKUP, která by zástupné znaky ve výchozím nastavení ignorovala. Na konci této nové funkce by mohl být další volitelný argument, který by umožnil říci Excelu, aby použil zástupné znaky. Pokud byste tento argument vynechali, Excel by žádné zástupné znaky nepoužil. Mohli byste snadno hledat „142*154“ nebo „Proč?“ nebo „e*Trade“, aniž byste se museli obávat, že se hodnota jako „Electrical Trade“ zobrazí jako falešná shoda.

PŘICHÁZEJÍ RYCHLEJŠÍ VLOOKUPY

Microsoft již několik měsíců testuje novou, rychlejší verzi funkce VLOOKUP v kanálu Insider Monthly sestavení sady Office 365. V kanálu Insider Monthly se již několik měsíců testuje nová verze funkce VLOOKUP. Funkce je často desetkrát rychlejší než stará VLOOKUP, zejména při tisících vyhledávání proti stejné tabulce.

Nová logika by se měla rozšířit mezi všechny předplatitele Office 365 do konce roku 2019. Pokud vaše VLOOKUPy v současné době trvají méně než sekundu, možná si změny nevšimnete. Pokud však máte rozsáhlý pracovní list, jehož výpočet trvá 40 sekund, očekávejte, že se doba přepočtu sníží na přibližně čtyři sekundy nebo méně.