Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce-nove » XLOOKUP-vyhledavaci-funkce-Excel
Microsoft Excel logo

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

XLOOKUP - je nová vyhledávací funkce, které je od 28.8.2019 (u mě od 2.9.2019, kdy vychází první verze článků) oficiálně dostupná v Insider verzi Excel v Office 365. V tomto článku se podívám, jak funguje a co umí, jaké jsou možné komplikace, zrady, tipy, triky.

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):


č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;režim_shody;režim_vyhledávání)

Syntaxe EN

=XLOOKUP(lookup_value,lookup_array,return_array,[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)
  • [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) XLOOKUP - funkce Excel - základy

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;0;-1) XLOOKUP - funkce Excel - prohledávat od začátku, od konce

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 - funkce Excel - nejbižší vyšší / nižší

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)

bez vyplněného argumentu, skončí chybovou hodnotou:

=XLOOKUP("Pave?";C7:C11;E7:E11) XLOOKUP-funkce-Excel-zastupne-znaky.JPG

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.


Microsoft Excel VBA - stahuj logo

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: 03.09.2019 16:38

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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ů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 1085

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.