Tweet

XLOOKUP (XLOOKUP) - nová vyhledávací funkce v Excel

XLOOKUP - úvod do funkce

Jak na vyhledávání v oblasti (aneb pokročilejší funkce SVYHLEDAT - VLOOKUP). Pro přehlednost je článek rozdělen na jednotlivé kapitoly (přehlednost a možnost přidávat další kapitoly):

• Základ - teorie syntaxe
• Základní ukázka použití
• Vyhledávaní od konce / začátku
• Vyhledávaní od největší od nejmenší
• Vyhledávání zástupné znaky
• Chyby ve vyhledávání
• Použití s VBA
• Související články
• Ke stažení

článek bude postupně rozšiřován na základě dotazů účastníků školení, dotazů od firemních klientů, případně z mých testů s touto novou funkcí.

Základ - teorie syntaxe

Funkce najde data v řádku daného sloupce a přiřadí hodnotu z jiného sloupce (podobná SVYHLEDAT VLOOKUP), kdy lze vyhledávat i přibližně (nejbližší nižší/veští), hledat od začátku do konce (nebo opačně) a případně využívat zástupné znaky.

Syntaxe CZ

=XLOOKUP(co;prohledat;vrátit;pokud_nenalezeno;režim_shody;režim_vyhledávání)

Syntaxe EN

=XLOOKUP(lookup_value;lookup_array;return_array;[if_not_found];[match_mode];[search_mode])

Argumenty

Popis argumentů

• lookup_value (co) - [povinný argument] - co se bude hledat
• lookup_array (prohledávat) - [povinný argument] - která oblast (sloupec) se bude prohledávat
• return_array (vrátit) - [povinný argument] - z které oblasti se bude vracet (sloupec)
• if_not_found (pokud_nenalezeno) - [nepovinný argument] - co zobrazit pokud nebude nalezeno
• [match_mode] režim shody - [nepoviný argument] - nastavuje jak se bude prohledávat
  • 0 - přesná shoda (pokud není uvedeno využívá standartně)
  • 1 - nejbližší větší hodnota, pokud neexistuje přesná shoda
  • -1 - nejbližší menší hodnota pokud neexistuje přesná shoda
  • 2 - Využití zástupného znaku * ? (* jakýkoliv počet znaků, ? Právě jeden znak)
• [search_mode] (režim vyhledávání) - [nepoviný argument] - nastavuje způsob hledání (prohledávání)
  • 1 pro vyhledávání od první do poslední (pokud není uvedeno využívá standartně)
  • -1 pro vyhledávání od poslední k první
  • 2 binární vyhledávání
  • -2 binární vyhledávání

Základní ukázka použití

Potřebuji najít, kolik vydělává (plat) Anna, případně plat pracovníka dle ID (osobního čísla).

=XLOOKUP("Anna";C7:C11;E7:E11) =XLOOKUP(3;B9:B13;E9:E13)

Pro ukázku jak lze vyřešit využitím SVYHLEDAT / VLOOKUP

=SVYHLEDAT("Anna";C6:E11;3;NEPRAVDA)

Vyhledávaní od konce / začátku

Problém u SVYHLEDAT je, že pokud se stejná hodnota opakuje, funkce najde jen první hodnotu. Naproti tomu XLOOKUP umí hledat od začátku (v základu), nebo od konce (takto můžete mít dva různé výsledky). Proto je jasné že k prostředním hodnotám se nedostanete (pokud například stejná hodnota bude více než dva krát).

Potřebuji například vrátit plat pro osobu s ID 4, ale bohužel je tam více než jednou. Mohu hledat od začátku do konce, případně od konce do začátku.

=XLOOKUP(4;B7:B13;E7:E13) =XLOOKUP(4;B7:B13;E7:E13;;0) =XLOOKUP(4;B7:B13;E7:E13;;0;) =XLOOKUP(4;B7:B13;E7:E13;"nenalezeno";0;-1)

Vyhledávaní od největší od nejmenší

Další možností je vyhledávat od nejbližší menší, případně nejbližší větší. Zde je jasné, že hodnoty musí být seřazeny od nejmenší ho největší, jinak funkce nebude fungovat správně.

Potřebuji najít jméno osoby která má plat nejbližší nižší zadanému (pokud není přesná shoda), případně člověka, který má plat nejbližší vyšší zadané hodnotě.

=XLOOKUP(10500;E7:E11;C7:C11;;-1) =XLOOKUP(10500;E7:E11;C7:C11;;1) =XLOOKUP(10500;E7:E11;C7:C11;"nenalezeno";1)

Vyhledávání zástupné znaky

Při hledaní s využitím zástupných znaků je potřeba nezapomenout na nepovinný argument - [match_mode] režim shody.

=XLOOKUP("Pave?";C7:C11;E7:E11;;2)

Pokud se hodnota nenalezne

=XLOOKUP("Pave?";C7:C11;E7:E11;"nenalezeno";2)

bez vyplněného argumentu po využití zástupných znaků, skončí chybovou hodnotou:

=XLOOKUP("Pave?";C7:C11;E7:E11)

Pokud uvedete co v případě že nenajde, zobrazní nenalezeno, ač se třeba v tabulce nachází Pavel, protože nehledá s využitím zástupných znaků.

=XLOOKUP("Pave?";C7:C11;E7:E11;"nenalezeno")

Další ukázky přípravě.

Chyby ve vyhledávání s funkci XLOOKUP

Pokud hodnota se nenajde hledanou hodnotu, vratí funkce chybovou hodnotu nenalezeno (#NENÍ_K_DISPOZICI), stejně jako funkce SVYHLEDAT.

=XLOOKUP("Petr";C7:C11;E7:E11)

Pro přibližné hledání musí být hodnoty seřazeny od nejmenší do největší.

v přípravě

Při hledaní využitím zástupných znaků není uveden argument (2)

=XLOOKUP("Pave?";C7:C11;E7:E11)

Další chyby a problémy, které mohou při použité funkce XLOOKUP nastat jsou v přípravě

Využití ve VBA

Jak na využití funkce SEQUENCE ve VBA. Tato část je v přípravě.

Další články související s funkcí SEQUENCE

Další články související se funkci? Jelikož se jedná o nové funkce, musím nejprve další články sepsat. Následně doplním.

• FILTER
• SORT
• SORTBY
• XMATCH

Ke stažení

Soubor ke stažení zdarma v přípravě. Soubor využívá makra. Zdarma při vydání článku obdrží (obdrželi) Patreon(i).

Článek byl aktualizován: 17.09.2021 12:36

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.