Funkcia XLOOKUP je exkluzívna pre zasvätený program Office 365. Funkcia LOOKUP má mnoho funkcií, ktoré prekonávajú mnohé slabé stránky funkcií VLOOKUP a HLOOKUP, ale bohužiaľ zatiaľ nie sú k dispozícii. Ale nebojte sa, môžeme vytvoriť funkciu XLOOKUP, ktorá funguje úplne rovnako ako nadchádzajúca funkcia XLOOKUP MS Excel. Postupne do neho budeme pridávať funkcie.
Kód VBA funkcie XLOOKUP
Nasledujúca funkcia vyhľadávania UDF vyrieši mnoho problémov. Skopírujte ho alebo si stiahnite nižšie uvedený doplnok xl.
Funkcia XLOOKUP (lk ako variant, lCol ako rozsah, rCol ako rozsah) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Koniec funkcie
Vysvetlenie:
Vyššie uvedený kód je len základným INDEX-MATCH používaným vo VBA. To zjednodušuje mnoho vecí, s ktorými sa nový používateľ stretáva. Ak rieši zložitosť funkcie INDEX-MATCH a používa iba tri argumenty. Môžete ho skopírovať do súboru programu Excel alebo si stiahnuť súbor .xlam nižšie a nainštalovať ho ako doplnok programu Excel. Ak neviete, ako vytvoriť a používať doplnok, kliknite sem, pomôže vám to.
Doplnok XLOOKUP
Pozrime sa, ako to funguje na pracovnom hárku programu Excel.
Syntax XLOOKUP
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Toto je hodnota, ktorú chcete hľadať v súbore lookup_array.
lookup_array: Je to jednorozmerný rozsah, v ktorom chcete hľadať lookup_value.
result_array: Je to tiež jednorozmerný rozsah. Toto je rozsah, z ktorého chcete získať hodnotu.
Pozrime sa na túto funkciu XLOOKUP v prevádzke.
Príklady XLOOKUP:
Tu mám tabuľku údajov v programe Excel. Pozrime sa na niektoré funkcie pomocou tejto tabuľky údajov.
Funkčnosť 1. Presne Vyhľadajte na ľavej a pravej strane vyhľadávanej hodnoty.
Ako vieme, funkcia Excel VLOOKUP nemôže načítať hodnoty zľava od vyhľadávanej hodnoty. Na to musíte použiť komplexnú kombináciu INDEX-MATCH. Ale už nie.
Za predpokladu, že musíme získať všetky informácie dostupné v tabuľke niektorých čísel rolí. V takom prípade budete musieť načítať aj región, ktorý je v ľavej časti stĺpca s číslom role.
Napíšte tento vzorec I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Získame výsledok Sever pre zvitok číslo 112. Skopírujte alebo potiahnite vzorec v nižšie uvedených bunkách a vyplňte ich príslušnými oblasťami.
Ako to funguje?
Mechanizmus je jednoduchý. Táto funkcia vyhľadá lookup_value v lookup_array a vráti index prvej presnej zhody. Potom použije tento index na získanie hodnoty z result_array. Táto funkcia funguje perfektne s pomenovanými rozsahmi.
Podobne použite tento vzorec na získanie hodnoty z každého stĺpca.
Funkčnosť 2. Presné Horizontálne Vyhľadajte nad a pod vyhľadávacou hodnotou.
XLOOKUP funguje aj ako presná funkcia HLOOKUP. Funkcia HLOOKUP má rovnaké obmedzenia ako funkcia VLOOKUP. Nemôže načítať hodnotu nad hodnotu vyhľadávania. XLOOKUP však funguje nielen ako HLOOKUP, ale tiež prekonáva túto slabinu. Pozrime sa ako.
Hypoteticky, ak chcete porovnať dva záznamy. Záznam vyhľadávania, ktorý už máte. Záznam, s ktorým chcete porovnávať, sa nachádza nad rozsahom lookup_range. V takom prípade použite tento vzorec.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
potiahnutím vzorca nadol získate celý záznam porovnávania riadkov.
Funkčnosť 3. Číslo stĺpca a predvolená presná zhoda nie sú potrebné.
Keď používate funkciu VLOOKUP, musíte povedať číslo stĺpca, z ktorého chcete načítať hodnoty. Na to musíte spočítať stĺpce alebo použiť nejaké triky a využiť ďalšie funkcie. S týmto UDF XLOOKUP to nemusíte robiť.
Ak používate VLOOKUP iba na načítanie nejakej hodnoty z jedného stĺpca alebo na kontrolu, či v stĺpci existuje hodnota, je to podľa mňa najlepšie riešenie.
Funkčnosť 4. Nahrádza funkciu INDEX-MATCH, VLOOKUP, HLOOKUP
Pri jednoduchých úlohách naša funkcia XLOOKUP nahrádza vyššie uvedené funkcie.
Obmedzenia XLOOKUP:
Pokiaľ ide o zložité vzorce, ako napríklad VLOOKUP s Dynamic Col Index, kde VLOOKUP identifikuje vyhľadávací stĺpec s hlavičkami, tento XLOOKUP zlyhá.
Ďalším obmedzením je, že ak musíte v tabuľke vyhľadať viac náhodných stĺpcov alebo riadkov, táto funkcia bude zbytočná, pretože musíte tento vzorec písať znova a znova. Toto je možné prekonať použitím pomenovaných rozsahov.
Zatiaľ sme nepridali približnú funkcionalitu, takže približnú zhodu samozrejme nemôžete získať. Pridáme to príliš skoro.
Ak sa funkcii XLOOKUP nepodarí nájsť hľadanú hodnotu, vráti chybu #HODNOTA, nie #N/A.
Takže áno, chlapci, týmto spôsobom používate XLOOKUP na získavanie, vyhľadávanie a overovanie hodnôt v tabuľkách programu Excel. Túto používateľom definovanú funkciu môžete použiť na bezproblémové vyhľadávanie vľavo alebo hore od požadovanej hodnoty. Ak máte stále nejaké pochybnosti alebo akékoľvek konkrétne požiadavky súvisiace s touto funkciou alebo s dotazom súvisiacim s EXCEL 2010/2013/2016/2019/365 alebo VBA, opýtajte sa ho v sekcii komentárov nižšie. Odpoveď určite dostanete.
Vytvorte funkciu VBA na vrátenie poľa | Ak chcete vrátiť pole z funkcie definovanej používateľom, musíme ho deklarovať pri pomenovaní UDF.
Polia v programe Excel Formul | Zistite, aké polia sú v programe Excel.
Ako vytvoriť funkciu definovanú používateľom prostredníctvom VBA | Naučte sa vytvárať v programe Excel funkcie definované používateľom
Použitie funkcie definovanej používateľom (UDF) z iného zošita pomocou jazyka VBA v programe Microsoft Excel | Používateľom definovanú funkciu použite v inom zošite programu Excel
Vráťte chybové hodnoty z funkcií definovaných používateľom pomocou jazyka VBA v programe Microsoft Excel | Zistite, ako môžete vrátiť chybové hodnoty z funkcie definovanej používateľom
Populárne články:
Rozdeľte hárok programu Excel do viacerých súborov na základe stĺpca pomocou VBA | Tento kód VBA rozdelil základný list programu Excel na jedinečné hodnoty v zadanom stĺpci. Stiahnite si pracovný súbor.
Vypnúť výstražné správy pomocou VBA v programe Microsoft Excel 2016 | Na vypnutie výstražných správ, ktoré prerušujú spustený kód VBA, používame triedu Application.
Pridať a uložiť nový zošit pomocou jazyka VBA v programe Microsoft Excel 2016 | Na pridávanie a ukladanie zošitov pomocou jazyka VBA používame triedu zošity. Zošity. Pridajte nový zošit ľahko, ale…