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.
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í.
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.
=XLOOKUP(co;prohledat;vrátit;pokud_nenalezeno;režim_shody;režim_vyhledávání)
=XLOOKUP(lookup_value;lookup_array;return_array;[if_not_found];[match_mode];[search_mode])
Popis argumentů
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)
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)
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)
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ě.
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ě
Jak na využití funkce SEQUENCE ve VBA. Tato část je v přípravě.
Další články související se funkci? Jelikož se jedná o nové funkce, musím nejprve další články sepsat. Následně doplním.
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
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.
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ů.
Můžete být prvními co zanechají smysluplný komentář.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 - 2024 |