Funkce XLOOKUP je exkluzivní pro zasvěcený program Office 365. Funkce LOOKUP má mnoho funkcí, které překonávají mnohé slabiny funkcí VLOOKUP a HLOOKUP, ale bohužel pro nás prozatím nejsou k dispozici. Ale nebojte se, můžeme vytvořit funkci XLOOKUP, která funguje úplně stejně jako nadcházející funkce XLOOKUP MS Excel. Postupně do něj budeme přidávat funkce.
Kód VBA funkce XLOOKUP
Níže uvedená funkce vyhledávání UDF vyřeší mnoho problémů. Zkopírujte jej nebo si stáhněte níže uvedený doplněk xl.
Funkce XLOOKUP (lk jako varianta, lCol jako rozsah, rCol jako rozsah) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Koncová funkce
Vysvětlení:
Výše uvedený kód je pouze základní INDEX-MATCH používaný ve VBA. To zjednodušuje mnoho věcí, kterým nový uživatel čelí. Pokud řeší složitost funkce INDEX-MATCH a používá pouze tři argumenty. Můžete jej zkopírovat do souboru aplikace Excel nebo si stáhnout soubor .xlam níže a nainstalovat jej jako doplněk aplikace Excel. Pokud nevíte, jak vytvořit a používat doplněk, klikněte sem, pomůže vám to.
Doplněk XLOOKUP
podívejme se, jak to funguje na listu aplikace Excel.
Syntaxe XLOOKUP
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Toto je hodnota, kterou chcete hledat v lookup_array.
lookup_array: Jedná se o jednorozměrný rozsah, ve kterém chcete hledat lookup_value.
result_array: Je to také jednorozměrný rozsah. Toto je rozsah, ze kterého chcete načíst hodnotu.
Podívejme se na tuto funkci XLOOKUP v akci.
Příklady XLOOKUP:
Tady mám datovou tabulku v Excelu. Prozkoumejme některé funkce pomocí této datové tabulky.
Funkčnost 1. Přesný Vyhledávání na levé a pravé straně vyhledávací hodnoty.
Jak víme, že funkce Excel VLOOKUP nemůže načítat hodnoty zleva od vyhledávací hodnoty. K tomu musíte použít komplexní kombinaci INDEX-MATCH. Ale už ne.
Za předpokladu, že potřebujeme načíst všechny informace dostupné v tabulce některých čísel rolí. V takovém případě budete muset načíst také region, který je nalevo od sloupce s číslem role.
Napište tento vzorec, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Získáme výsledek Sever pro roli číslo 112. Zkopírujte nebo přetáhněte vzorec v níže uvedených buňkách a vyplňte je příslušnými oblastmi.
Jak to funguje?
Mechanismus je jednoduchý. Tato funkce vyhledá lookup_value v lookup_array a vrátí index první přesné shody. Poté použije tento index k načtení hodnoty z result_array. Tato funkce funguje perfektně s pojmenovanými rozsahy.
Podobně použijte tento vzorec k načtení hodnoty z každého sloupce.
Funkčnost 2. Přesně Horizontální Vyhledejte nad a pod vyhledávací hodnotou.
XLOOKUP funguje také jako přesná funkce HLOOKUP. Funkce HLOOKUP má stejné omezení jako funkce VLOOKUP. Nelze načíst hodnotu shora nad vyhledávací hodnotou. Ale XLOOKUP funguje nejen jako HLOOKUP, ale také překonává tuto slabost. Podívejme se jak.
Pokud chcete porovnat dva záznamy, hypoteticky. Záznam vyhledávání, který již máte. Záznam, se kterým chcete porovnávat, je nad rozsahem lookup_range. V takovém případě použijte tento vzorec.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
přetáhněte vzorec dolů a máte celý záznam porovnávání řádků.
Funkčnost 3. Není potřeba číslo sloupce a výchozí přesná shoda.
Když používáte funkci VLOOKUP, musíte sdělit číslo sloupce, ze kterého chcete načíst hodnoty. K tomu musíte počítat sloupce nebo použít nějaké triky, využít pomoc dalších funkcí. S tímto UDF XLOOKUPem to nemusíte dělat.
Pokud používáte VLOOKUP pouze pro načítání nějaké hodnoty z jednoho sloupce nebo pro kontrolu, zda ve sloupci existuje hodnota, je to podle mě nejlepší řešení.
Funkčnost 4. Nahrazuje funkci INDEX-MATCH, VLOOKUP, HLOOKUP
U jednoduchých úkolů naše funkce XLOOKUP nahrazuje výše uvedené funkce.
Omezení XLOOKUP:
Pokud jde o složité vzorce, například VLOOKUP s Dynamic Col Index, kde VLOOKUP identifikuje vyhledávací sloupec se záhlavími, tento XLOOKUP selže.
Dalším omezením je, že pokud budete muset vyhledat více náhodných sloupců nebo řádků z tabulky, bude tato funkce k ničemu, protože budete muset tento vzorec psát znovu a znovu. To lze překonat pomocí pojmenovaných rozsahů.
Prozatím jsme nepřidali přibližnou funkčnost, takže přibližnou shodu samozřejmě nemůžete získat. Brzy to přidáme.
Pokud funkce XLOOKUP nedokáže najít hodnotu vyhledávání, vrátí chybu #HODNOTA, nikoli #N/A.
Takže jo, lidi, takto používáte XLOOKUP k načítání, vyhledávání a ověřování hodnot v tabulkách Excelu. Tuto uživatelem definovanou funkci můžete použít pro bezproblémové vyhledávání vlevo nebo nahoře od vyhledávací hodnoty. Pokud máte stále nějaké pochybnosti nebo jakýkoli konkrétní požadavek související s touto funkcí nebo dotazem souvisejícím s EXCEL 2010/2013/2016/2019/365 nebo VBA, zeptejte se ho v sekci komentáře níže. Určitě dostanete odpověď.
Vytvořit funkci VBA pro návrat pole | Abychom vrátili pole z funkce definované uživatelem, musíme ho deklarovat, když pojmenujeme UDF.
Pole v aplikaci Excel Formul | Zjistěte, jaká pole jsou v aplikaci Excel.
Jak vytvořit funkci definovanou uživatelem prostřednictvím VBA | Zjistěte, jak v aplikaci Excel vytvářet funkce definované uživatelem
Použití funkce definované uživatelem (UDF) z jiného sešitu pomocí jazyka VBA v aplikaci Microsoft Excel | Použijte funkci definovanou uživatelem v jiném sešitu aplikace Excel
Vraťte chybové hodnoty z funkcí definovaných uživatelem pomocí jazyka VBA v aplikaci Microsoft Excel | Zjistěte, jak můžete vrátit chybové hodnoty z funkce definované uživatelem
Oblíbené články:
Rozdělit list aplikace Excel na více souborů na základě sloupce pomocí VBA | Tento kód VBA rozdělil list Excelu na jedinečné hodnoty v zadaném sloupci. Stáhněte si pracovní soubor.
Vypnout varovné zprávy pomocí VBA v aplikaci Microsoft Excel 2016 | K vypnutí varovných zpráv, které přerušují běžící kód VBA, používáme třídu Application.
Přidat a uložit nový sešit pomocí jazyka VBA v aplikaci Microsoft Excel 2016 | K přidání a uložení sešitů pomocí jazyka VBA používáme třídu Workbooks. Workbooks.Add přidává nový sešit snadno, ale…