Sledujte video - Funkcia Excel XLOOKUP (10 príkladov XLOOKUP)
Excel Funkcia XLOOKUP konečne prišiel.
Ak používate VLOOKUP alebo INDEX/MATCH, som si istý, že sa vám bude páčiť flexibilita, ktorú funkcia XLOOKUP ponúka.
V tomto návode sa budem zaoberať všetkým, čo je potrebné vedieť o funkcii XLOOKUP, a niekoľkými príkladmi, ktoré vám pomôžu vedieť, ako ju najlepšie používať.
Začnime teda!
Čo je to XLOOKUP?
XLOOKUP je nová funkcia Office 365 a je novou a vylepšenou verziou funkcie VLOOKUP/HLOOKUP.
Robí všetko, čo predtým robil VLOOKUP, a oveľa viac.
XLOOKUP je funkcia, ktorá vám umožní rýchlo vyhľadať hodnotu v množine údajov (vertikálnej alebo horizontálnej) a vrátiť zodpovedajúcu hodnotu v inom riadku/stĺpci.
Ak máte napríklad skóre pre študentov na skúške, pomocou XLOOKUP môžete pomocou mena študenta rýchlo skontrolovať, koľko študent dosiahol skóre.
Sila tejto funkcie bude ešte jasnejšia, keď sa do niektorých hlboko ponorím Príklady XLOOKUP neskôr v tomto návode.
Kým sa však dostanem k príkladom, vyvstáva veľká otázka - ako získam prístup k XLOOKUP?
Ako získať prístup k XLOOKUP?
Teraz je XLOOKUP k dispozícii iba pre používateľov Office 365.
Ak teda používate predchádzajúce verzie programu Excel (2010/2013/2016/2019), túto funkciu nebudete môcť používať.
Nie som si tiež istý, či to bude niekedy vydané pre predchádzajúce verzie alebo nie (možno spoločnosť Microsoft môže vytvoriť doplnok spôsobom, akým to urobili pre Power Query). Odteraz ho však budete môcť používať iba vtedy, ak používate Office 365.
Kliknutím sem inovujete na Office 365
V prípade, že už používate Office 365 (edícia Home, Personal alebo University) a nemáte k nemu prístup, môžete prejsť na kartu Súbor a potom kliknúť na položku Účet.
Bude existovať program Office Insider a môžete kliknúť a pripojiť sa k programu Office Insider. Získate tak prístup k funkcii XLOOKUP.
Očakávam, že XLOOKUP bude čoskoro k dispozícii pre všetky verzie Office 365.
Poznámka: XLOOKUP je k dispozícii aj pre Office 365 pre Mac a Excel pre web (Excel online)Syntax funkcie XLOOKUP
Nasleduje syntax funkcie XLOOKUP:
= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Ak ste použili VLOOKUP, všimnete si, že syntax je dosť podobná, samozrejme s niekoľkými úžasnými ďalšími funkciami.
Ak syntax a argument vyzerajú príliš veľa, nie je dôvod sa znepokojovať. Pokrývam ich niekoľkými jednoduchými príkladmi XLOOKUP neskôr v tomto návode, vďaka ktorým bude krištáľovo čistý.Funkcia XLOOKUP môže rozprávať 6 argumentov (3 povinné a 3 voliteľné):
- lookup_value - hodnota, ktorú hľadáte
- lookup_array - pole, v ktorom hľadanú hodnotu hľadáte
- return_array - pole, z ktorého chcete načítať a vrátiť hodnotu (zodpovedá pozícii, kde sa nachádza hodnota vyhľadávania)
- [if_not_found] - hodnota, ktorá sa má vrátiť v prípade, že sa hodnota vyhľadávania nenájde. V prípade, že tento argument nezadáte, vráti sa chyba #N/A
- [match_mode] - Tu môžete zadať požadovaný typ zhody:
- 0 - Presná zhoda, pričom hodnota lookup_value by sa mala presne zhodovať s hodnotou v poli lookup_array. Toto je predvolená možnosť.
- -1 - Hľadá presnú zhodu, ale ak je nájdená, vráti ďalšiu menšiu položku/hodnotu
- 1 - Hľadá presnú zhodu, ale ak je nájdená, vráti ďalšiu väčšiu položku/hodnotu
- 2 - Čiastočná zhoda pomocou zástupných znakov (* alebo ~)
- [režim hľadania] - Tu určíte, ako má funkcia XLOOKUP hľadať v poli lookup_array
- 1 - Toto je predvolená možnosť, kde funkcia v poli lookup_array začne hľadať hodnotu lookup_value zhora (prvá položka) nadol (posledná položka)
- -1 - Vykonáva vyhľadávanie zdola nahor. Užitočné, keď chcete nájsť poslednú zodpovedajúcu hodnotu v poli lookup_array
- 2 - Vykonáva binárne vyhľadávanie, kde je potrebné údaje zoradiť vzostupne. Ak nie sú zoradené, môže to viesť k chybným alebo nesprávnym výsledkom
- -2 - Vykonáva binárne vyhľadávanie, kde je potrebné údaje zoradiť zostupne. Ak nie sú zoradené, môže to viesť k chybným alebo nesprávnym výsledkom
Príklady funkcií XLOOKUP
Teraz sa dostaneme k zaujímavej časti - praktickým príkladom XLOOKUP.
Tieto príklady vám pomôžu lepšie porozumieť tomu, ako XLOOKUP funguje, čím sa líši od VLOOKUP a INDEX/MATCH a niektorým vylepšeniam a obmedzeniam tejto funkcie.
Kliknutím sem stiahnete ukážkový súbor a budete pokračovať
Príklad 1: Získajte hodnotu vyhľadávania
Predpokladajme, že máte nasledujúci súbor údajov a chcete získať matematické skóre pre Grega (vyhľadávacia hodnota).
Nasleduje vzorec, ktorý to robí:
= XLOOKUP (F2, A2: A15, B2: B15)
Vo vyššie uvedenom vzorci som práve použil povinné argumenty, kde hľadá názov (zhora nadol), nájde presnú zhodu a vráti zodpovedajúcu hodnotu z B2: B15.
Jeden zrejmý rozdiel, ktorý funkcia XLOOKUP a VLOOKUP má, je spôsob, akým zaobchádzajú s vyhľadávacím poľom. Vo VLOOKUP máte celé pole, kde je hodnota vyhľadávania v stĺpci úplne vľavo, a potom zadáte číslo stĺpca, z ktorého chcete výsledok načítať. XLOOKUP vám na druhej strane umožňuje zvoliť si lookup_array a return_array oddeleneJedna okamžitá výhoda toho, že lookup_array a return_array sú samostatné argumenty, znamená, že teraz môžete pozri doľava. VLOOKUP mal toto obmedzenie, kde môžete iba vyhľadať a nájsť hodnotu, ktorá je napravo. Ale s XLOOKUPom toto obmedzenie zmizlo.
Tu je príklad. Mám rovnaký súbor údajov, kde je názov vpravo a rozsah návratu_ je vľavo.
Nasleduje vzorec, ktorý môžem použiť na získanie skóre pre Grega v matematike (čo znamená pohľad doľava od hodnoty lookup_value)
= XLOOKUP (F2, D2: D15, A2: A15)
XLOOKUP rieši ďalší zásadný problém - V prípade, že vložíte nový stĺpec alebo ho presuniete, výsledné údaje budú stále správne. VLOOKUP by sa pravdepodobne zlomil alebo poskytol nesprávny výsledok v prípadoch, keď je väčšinou hodnota indexu stĺpca naprogramovaná pevne.
Príklad 2: Vyhľadajte a načítajte celý záznam
Zoberme si rovnaké údaje ako príklad.
V tomto prípade nechcem len získať Gregovo skóre v matematike. Chcem získať skóre vo všetkých predmetoch.
V tomto prípade môžem použiť nasledujúci vzorec:
= XLOOKUP (F2, A2: A15, B2: D15)
Vyššie uvedený vzorec používa rozsah return_array, ktorý je viac ako stĺpec (B2: D15). Keď sa teda vyhľadávacia hodnota nájde v A2: A15, vzorec vráti celý riadok zo súboru return_array.
Tiež nemôžete odstrániť iba bunky, ktoré sú súčasťou poľa, ktoré boli automaticky vyplnené. V tomto prípade nemôžete odstrániť H2 ani I2. Ak by ste to skúsili, nič by sa nestalo. Ak vyberiete tieto bunky, vzorec na paneli vzorcov bude sivý (čo znamená, že ho nemožno zmeniť)
Vzorec môžete vymazať v bunke G2 (kde sme ho pôvodne zadali), vymaže celý výsledok.
Toto je užitočné vylepšenie, pretože v prípade VLOOKUP budete musieť pre každý vzorec zadať číslo stĺpca osobitne.
Príklad 3: Obojsmerné vyhľadávanie pomocou XLOOKUP (horizontálne a vertikálne vyhľadávanie)
Nasleduje súbor údajov, kde chcem poznať skóre Grega v matematike (predmet v bunke G2).
To je možné vykonať pomocou obojsmerného vyhľadávania, kde hľadám meno v stĺpci A a názov predmetu v riadku 1. Výhodou tohto obojsmerného vyhľadávania je, že výsledok je nezávislý na mene študenta v názve predmetu. Ak zmením názov predmetu na chémiu, tento obojsmerný vzorec XLOOKUP bude stále fungovať a poskytne mi správny výsledok.
Nasleduje vzorec, ktorý vykoná obojsmerné vyhľadávanie a poskytne správny výsledok:
= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))
Tento vzorec používa vnorený XLOOKUP, kde pomocou neho najskôr načítam všetky známky študenta v bunke F2.
Výsledok XLOOKUP (F2, A2: A15, B2: D15) je {21,94,81}, čo je pole známok, ktoré v tomto prípade dosiahol Greg.
Toto sa potom znova použije vo vonkajšom vzorci XLOOKUP ako návratové pole. Vo vonkajšom vzorci XLOOKUP hľadám názov subjektu (ktorý je v bunke G1) a vyhľadávacie pole je B1: D1.
Ak je názov subjektu matematický, tento vonkajší vzorec XLOOKUP načítava prvú hodnotu z návratového poľa - čo je v tomto prípade {21,94,81}.
To isté platí pre to, čo sa doteraz dosahovalo pomocou kombinácie INDEX a MATCH
Kliknutím sem stiahnete ukážkový súbor a budete pokračovať
Príklad 4: Keď sa nenašla vyhľadávacia hodnota (spracovanie chýb)
Do vzorca XLOOKUP bolo teraz pridané spracovanie chýb.
Štvrtý argument vo funkcii XLOOKUP je [if_not_found], kde môžete zadať, čo chcete v prípade, že vyhľadávanie nemožno nájsť.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, kde chcete získať matematické skóre v prípade zhody a v prípade, že sa nenájde meno, chcete sa vrátiť - „Nezobrazilo sa“
Nasledujúci vzorec to urobí:
= XLOOKUP (F2, A2: A15, B2: B15, „Nezobrazilo sa“)
V tomto prípade mám pevne zakódované, čo chcem získať v prípade, že nedôjde k zhode. Na bunku alebo vzorec môžete použiť aj odkaz na bunku.
Príklad 5: Vnorené XLOOKUP (vyhľadávanie vo viacerých rozsahoch)
Genius argumentu [if_not_found] je, že vám umožňuje používať vnorený vzorec XLOOKUP.
Predpokladajme napríklad, že máte dva samostatné zoznamy, ako je uvedené nižšie. Aj keď mám tieto dve tabuľky na rovnakom hárku, môžete ich mať v samostatných listoch alebo dokonca v zošitoch.
Nasleduje vnorený vzorec XLOOKUP, ktorý skontroluje názov v oboch tabuľkách a vráti zodpovedajúcu hodnotu zo zadaného stĺpca.
= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))
Vo vyššie uvedenom vzorci som použil argument [if_not_found] na použitie iného vzorca XLOOKUP. To vám umožní pridať druhý XLOOKUP do rovnakého vzorca a skenovať dve tabuľky s jediným vzorcom.
Nie som si istý, koľko vnorených XLOOKUPov môžete použiť vo vzorci. Skúšal som do 10 a išlo to, potom som to vzdal 🙂
Príklad 6: Nájdite poslednú zodpovedajúcu hodnotu
Tento bol veľmi potrebný a XLOOKUP to umožnil. Teraz nemusíte hľadať spletité spôsoby, ako získať poslednú zodpovedajúcu hodnotu v rozsahu.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete skontrolovať, kedy bola posledná osoba najatá v každom oddelení a aký bol dátum prijatia.
Nasledujúci vzorec vyhľadá poslednú hodnotu pre každé oddelenie a poskytne názov posledného zamestnania:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)
Nasledujúci vzorec poskytne dátum prijatia posledného prenájmu pre každé oddelenie:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)
Pretože XLOOKUP má vstavanú funkciu na určenie smeru vyhľadávania (od prvého do posledného alebo od posledného do prvého), robí sa to pomocou jednoduchého vzorca. Pri zvislých údajoch sa funkcie VLOOKUP a INDEX/MATCH vždy pozerajú zhora nadol, ale pomocou funkcie XLOOKUP a môžu tiež určiť smer zdola nahor.
Príklad 7: Približná zhoda s XLOOKUP (Nájsť sadzbu dane)
Ďalším pozoruhodným vylepšením programu XLOOKUP je, že teraz existujú štyri režimy zhody (VLOOKUP má 2 a MATCH má 3).
Môžete zadať ktorýkoľvek zo štyroch argumentov, aby ste sa rozhodli, ako by mala byť zhodná s hodnotou vyhľadávania:
- 0 - Presná zhoda, pričom hodnota lookup_value by sa mala presne zhodovať s hodnotou v poli lookup_array. Toto je predvolená možnosť.
- -1 - Hľadá presnú zhodu, ale ak je nájdená, vráti ďalšiu menšiu položku/hodnotu
- 1 - Hľadá presnú zhodu, ale ak je nájdená, vráti ďalšiu väčšiu položku/hodnotu
- 2 - Čiastočná zhoda pomocou zástupných znakov (* alebo ~)
Nasleduje súbor údajov, kde chcem nájsť províziu každej osoby - a províziu je potrebné vypočítať pomocou tabuľky vpravo.
Nasleduje vzorec, ktorý to urobí:
= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2
Na vyhľadanie sa jednoducho použije predajná hodnota a zobrazí sa vyhľadávacia tabuľka vpravo. V tomto vzorci som použil -1 ako piaty argument ([mode_zodpovedania]), čo znamená, že bude hľadať presnú zhodu, a keď ju nenájde, vráti hodnotu menšiu ako je hodnota vyhľadávania. .
A ako som povedal, nemusíte si robiť starosti s tým, či sú vaše údaje zoradené.
Kliknutím sem stiahnete ukážkový súbor a budete pokračovať
Príklad 8: Horizontálne vyhľadávanie
XLOOKUP dokáže vykonávať vertikálne aj horizontálne vyhľadávanie.
Nasleduje súbor údajov, kde mám v riadkoch mená študentov a ich skóre, a chcem načítať skóre pre meno v bunke B7.
Nasledujúci vzorec to urobí:
= XLOOKUP (B7, B1: O1, B2: O2)
Toto nie je nič iné ako jednoduché vyhľadávanie (podobné tomu, ktoré sme videli v príklade 1), ale horizontálne.
Všetky príklady, ktoré uvádzam o vertikálnom vyhľadávaní, je možné vykonať aj pomocou horizontálneho vyhľadávania pomocou XLOOKUP (rozlúčka s VLOOKUP a HLOOKUP).
Príklad 9: Podmienené vyhľadávanie (pomocou XLOOKUP s inými vzorcami)
Toto je mierne pokročilý príklad a tiež ukazuje silu XLOOKUPu, keď potrebujete vykonať zložité vyhľadávanie.
Nasleduje súbor údajov, kde uvádzam mená študentov a ich skóre a chcem vedieť meno študenta, ktorý dosiahol maximálny počet bodov v každom predmete, a počet študentov, ktorí v každom predmete získali viac ako 80 bodov.
Nasleduje vzorec, ktorý poskytne meno študenta s najvyšším prospechom v každom predmete:
= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)
Pretože XLOOKUP môže byť použitý na vrátenie celého poľa, použil som ho najskôr na získanie všetkých známok pre požadovaný predmet.
Napríklad pre matematiku, keď používam XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), poskytne mi všetky výsledky v matematike. Potom môžem použiť funkciu MAX na nájdenie maximálneho skóre v tomto rozsahu.
Toto maximálne skóre sa potom stane mojou hodnotou vyhľadávania a rozsahom vyhľadávania by bolo pole vrátené spoločnosťou XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)
Toto používam v rámci iného vzorca XLOOKUP na načítanie mena študenta, ktorý dosiahol maximálny počet bodov.
A na spočítanie počtu študentov, ktorí dosiahli skóre viac ako 80, použite nasledujúci vzorec:
= COUNTIF (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")
Tento jednoducho používa vzorec XLOOKUP na získanie rozsahu všetkých hodnôt pre daný predmet. Potom ho zabalí do funkcie COUNTIF, aby získal počet skóre, ktoré sú viac ako 80.
Príklad 10: Použitie zástupných znakov v programe XLOOKUP
Rovnako ako môžete používať zástupné znaky vo VLOOKUP a MATCH, môžete to urobiť aj s XLOOKUP.
Ale je tu rozdiel.
V XLOOKUP musíte zadať, že používate zástupné znaky (v piatom argumente). Ak to neurčíte, XLOOKUP vám zobrazí chybu.
Nasleduje súbor údajov, kde uvádzam názvy spoločností a ich trhovú kapitalizáciu.
Chcem vyhľadať názov spoločnosti v stĺpci D a z tabuľky vľavo získať trhovú kapitalizáciu. A pretože mená v stĺpci D nie sú presné zhody, budem musieť použiť zástupné znaky.
Nasleduje vzorec, ktorý to urobí:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)
Vo vyššie uvedenom vzorci som použil zástupný znak hviezdičky (*) predtým ako po D2 (musí byť v úvodzovkách a spojený s D2 pomocou ampersandu).
Tento vzorec hovorí, že sa má pozrieť na všetky bunky, a ak obsahuje slovo v bunke D2 (ktorou je Apple), považuje to za presnú zhodu. Bez ohľadu na to, koľko a aké znaky sú pred a za textom v bunke D2.
A aby sa ubezpečil, že XLOOKUP prijíma zástupné znaky, piaty argument bol nastavený na 2 (zhoda so zástupnými znakmi).
Príklad 11: Nájdite poslednú hodnotu v stĺpci
Pretože XLOOKUP umožňuje vyhľadávanie zdola nahor, poslednú hodnotu v zozname môžete ľahko nájsť a zodpovedajúcu hodnotu načítať aj zo stĺpca.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete vedieť, aká je posledná spoločnosť a aká je trhová kapitalizácia tejto poslednej spoločnosti.
Nasledujúci vzorec vám poskytne názov poslednej spoločnosti:
= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)
Nasledujúci vzorec poskytne trhový strop poslednej spoločnosti v zozname:
= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)
Tieto vzorce opäť používajú zástupné znaky. V týchto som ako vyhľadávaciu hodnotu použil hviezdičku (*), čo znamená, že táto prvá bunka, s ktorou sa stretne, by sa považovala za presnú zhodu (ako hviezdička môže byť ľubovoľný znak a ľubovoľný počet znakov).
A keďže smer je zdola nahor (pre vertikálne usporiadané údaje), vráti poslednú hodnotu v zozname.
A druhý vzorec odvtedy používa samostatný výnosový_rozsah na získanie trhového stropu priezviska v zozname.
Kliknutím sem stiahnete ukážkový súbor a budete pokračovať
Čo keď nemáte XLOOKUP?
Pretože XLOOKUP bude pravdepodobne dostupný iba pre používateľov Office 365, jedným zo spôsobov, ako ho získať, je inovácia na Office 365.
Ak už máte edíciu Office 365 Home, Personal alebo University, už máte prístup k XLOOKUP. Všetko, čo musíte urobiť, je pripojiť sa k programu Office Insider.
Ak to chcete urobiť, prejdite na kartu Súbor, kliknite na položku Účet a potom kliknite na možnosť Office insider. Bola by možnosť pripojiť sa k programu insider.
V prípade, že máte ďalšie predplatné služieb Office 365 (napríklad Enterprise), som si istý, že XLOOKUP a ďalšie úžasné funkcie (ako sú dynamické polia, vzorce ako SORT a FILTER) budú čoskoro k dispozícii.
V prípade, že používate Excel 2010/2013/2016/2019, nebudete mať XLOOKUP a budete musieť naďalej používať kombináciu VLOOKUP, HLOOKUP a INDEX/MATCH, aby ste z vyhľadávacích vzorcov získali to najlepšie.
XLOOKUP Spätná kompatibilita
Toto je jedna vec, na ktorú si musíte dávať pozor - XLOOKUP je NIE je spätne kompatibilný.
To znamená, že ak vytvoríte súbor a použijete vzorec XLOOKUP a potom ho otvoríte vo verzii, ktorá nemá XLOOKUP, budú sa zobrazovať chyby.
Pretože XLOOKUP je obrovským krokom vpred správnym smerom, verím, že sa to stane predvoleným vzorcom vyhľadávania, ale určite bude trvať niekoľko rokov, kým sa široko osvojí. Koniec koncov, stále vidím niektorých ľudí, ktorí používajú Excel 2003.
Toto je 11 príkladov XLOOKUP, ktoré vám môžu pomôcť rýchlejšie vykonať všetky činnosti súvisiace s vyhľadávaním a referenciami a tiež uľahčiť používanie.
Dúfam, že ste našli tento návod užitočný!
