V jednej tabuľke máte ID tovaru, v druhej jeho cenu a potrebujete ich spojiť. V Exceli na to poslúžia VLOOKUP a XLOOKUP: podľa spoločného údaja nájdu správny riadok a vrátia z neho požadovanú hodnotu. Ak máte XLOOKUP k dispozícii, na nové vzorce je zvyčajne pohodlnejší. Pri zošite určenom aj pre Excel 2016 alebo 2019 použite VLOOKUP, prípadne kombináciu INDEX a MATCH.
Spoločnému údaju budeme hovoriť kľúč. V našom príklade je ním ID produktu a výsledkom cena. Pri párovaní ID potrebujete presnú zhodu a pri kopírovaní vzorca pevný odkaz na cenník. Práve tieto dve drobnosti rozhodujú o tom, či dostanete správnu cenu.
VLOOKUP alebo XLOOKUP: ktorú funkciu použiť?
XLOOKUP je dostupný v Exceli pre Microsoft 365, Exceli 2021 a Exceli 2024 vrátane zodpovedajúcich vydaní pre Mac. Funguje aj v Exceli pre web. Microsoft výslovne uvádza, že Excel 2016 a Excel 2019 XLOOKUP nepodporujú. Bežná aktualizácia týchto vydaní ho nepridá.
| Čo potrebujete | VLOOKUP | XLOOKUP |
|---|---|---|
| Párovať údaje podľa ID | Áno, nastavte presnú zhodu pomocou 0 alebo FALSE. | Áno, presná zhoda je predvolená. |
| Vrátiť údaj naľavo od kľúča | Pri bežnom použití nie. Kľúč musí byť v prvom stĺpci zadanej oblasti. | Áno, vyhľadávací a výsledkový stĺpec zadávate samostatne. |
| Určiť stĺpec s výsledkom | Zadávate jeho poradové číslo v oblasti. | Vyberáte priamo rozsah s výsledkami. |
| Zobraziť vlastný text pri nenájdení | Pridajte napríklad funkciu IFNA. | Text zadáte priamo ako štvrtý argument. |
| Odovzdať zošit používateľovi Excelu 2016 alebo 2019 | Vhodná voľba, ak usporiadanie údajov vyhovuje. | Vzorec treba nahradiť podporovanou funkciou. |
Verziu vo Windowse zistíte cez Súbor → Konto → Informácie o produkte. Podrobné číslo zostavy zobrazí položka Informácie o programe Excel. Na Macu otvorte ponuku Excel → Informácie o programe Excel; postup na zistenie vydania Officeu opisuje aj Microsoft. Pri zdieľaní zošita berte do úvahy tiež verziu kolegu.
Ako priradiť cenu z druhej tabuľky: jeden príklad pre obe funkcie
Vytvorte hárok s názvom Cenník. Do buniek A1:B5 vložte nasledujúce ilustračné údaje. Hlavičky sú ID a Cena; ceny zadávajte ako čísla, symbol eura môžete pridať formátovaním buniek.
| ID | Cena |
|---|---|
| P101 | 19,90 |
| P102 | 34,50 |
| P103 | 8,00 |
| P104 | 59,00 |
V druhom hárku, napríklad Objednávky, máte ID v stĺpci B a cenu chcete doplniť do stĺpca C. Do B2 napíšte P102, do B3 hodnotu P101 a do B4 hodnotu P999, ktorá v cenníku nie je.
Ukážky používajú názvy funkcií zo slovenskej dokumentácie a bodkočiarky medzi argumentmi. Oddeľovač však závisí od regionálnych nastavení, nie iba od jazyka Excelu. Ak vaša inštalácia vyžaduje čiarky, nahraďte nimi bodkočiarky vo vzorcoch.
Vzorec VLOOKUP s presnou zhodou
Do bunky C2 na hárku Objednávky zadajte:
=VLOOKUP(B2;Cenník!$A$2:$B$5;2;0)B2obsahuje ID, ktoré hľadáte.Cenník!$A$2:$B$5je oblasť s ID a cenami. Kľúč musí byť v jej prvom stĺpci, v tomto prípade v stĺpci A.2znamená druhý stĺpec vybranej oblasti, teda cenu. Nejde o poradové číslo stĺpca v celom hárku.0vyžaduje presnú zhodu. Rovnaký význam máFALSE.
Ak by cenník začínal v stĺpci D a cena bola v stĺpci E, vo vzorci by stále zostalo číslo 2. Počíta sa od ľavého okraja zadanej oblasti. Podrobnosti uvádza dokumentácia funkcie VLOOKUP.
Vzorec XLOOKUP na rovnakých údajoch
Ak máte XLOOKUP, môžete do tej istej bunky C2 namiesto predchádzajúceho vzorca zadať:
=XLOOKUP(B2;Cenník!$A$2:$A$5;Cenník!$B$2:$B$5)Prvý argument je hľadané ID, druhý rozsah s ID a tretí rozsah s cenami. Číslo výsledkového stĺpca netreba a presná zhoda je predvolená. Oba rozsahy musia mať zodpovedajúce riadky: prvé ID patrí k prvej cene, druhé k druhej a tak ďalej.
Po potvrdení klávesom Enter vrátia oba vzorce pre P102 hodnotu 34,5. S formátom meny a dvoma desatinnými miestami ju uvidíte ako 34,50 €. Potiahnite vzorec za pravý dolný roh bunky C2 po bunku C4. Pre P101 dostanete cenu 19,90 €; pri P999 chybu nenájdenej hodnoty, označovanú ako #NEDOSTUPNÝ alebo #N/A podľa lokalizácie.
Prečo vo VLOOKUP nevynechať posledný argument
Štvrtý argument VLOOKUP je nepovinný, ale jeho vynechaním zapnete približnú zhodu. Tá predpokladá vzostupne zoradený prvý stĺpec. Ak presnú hodnotu nenájde, použije najväčšiu hodnotu, ktorá je menšia než hľadaná. Pri nezoradených údajoch môže vrátiť nesprávny výsledok.
Ilustračný príklad: pri hraniciach 10, 20, 30 a hľadanej hodnote 26 vyberie približná zhoda hranicu 20. Je to užitočné pri pásmach zliav či bodovom hodnotení, ako ukazuje aj návod Microsoftu na približné vyhľadávanie. Pri ID produktu však potrebujete jeho vlastnú cenu, preto použite 0 alebo FALSE. Pre VLOOKUP s presnou zhodou cenník zoraďovať nemusíte.
Čo robí znak dolára a prečo ho potrebuje aj XLOOKUP
Obyčajný odkaz je relatívny. Keď vzorec skopírujete o riadok nadol, z B2 sa stane B3. To je správne: v ďalšej objednávke hľadáte iné ID. Bez uzamknutia by sa však posunul aj cenník, napríklad z A2:B5 na A3:B6, a prvý produkt by z prehľadávanej oblasti vypadol.
Zápis $A$2:$B$5 uzamkne stĺpce aj riadky. Preto majú už úvodné vzorce pevný rozsah cenníka, zatiaľ čo kľúč B2 zostáva relatívny. Pri XLOOKUP treba takto uzamknúť oba rozsahy.
Počas úpravy vzorca označte odkaz a stláčaním F4 prepínajte jeho typ, ako opisuje návod na absolútne a relatívne odkazy. Na Macu môžete pri úprave odkazu použiť Command + T. Ďalšie klávesové skratky pre Excel nájdete v samostatnom prehľade.
Keď do cenníka pribúdajú nové riadky
Pevný rozsah sa pri kopírovaní neposúva, ale produkt dopísaný do riadka 6 už oblasť $A$2:$B$5 nezahŕňa. Pri rastúcom cenníku sa oplatí vytvoriť skutočnú excelovú tabuľku: označte údaje s hlavičkami, vo Windowse stlačte Ctrl + T a potvrďte, že tabuľka má hlavičky.
Ak tabuľku cez kartu Návrh tabuľky pomenujete CennikData a ponecháte stĺpce ID a Cena, XLOOKUP mimo tejto tabuľky môže vyzerať takto:
=XLOOKUP(B2;CennikData[ID];CennikData[Cena])Štruktúrované odkazy na stĺpce tabuľky sa prispôsobujú jej rozsahu. Keď nový produkt pridáte ako ďalší riadok tejto tabuľky, nemusíte ručne predlžovať odkazy na bunky.
Ako vyhľadať údaj naľavo od ID
Teraz uvažujme opačne usporiadaný cenník: v stĺpci A je cena a v stĺpci B ID. VLOOKUP sa pri bežnom použití nevie zo stĺpca B vrátiť do A. XLOOKUP stačí zadať rozsahy v správnom poradí:
=XLOOKUP(B2;Cenník!$B$2:$B$5;Cenník!$A$2:$A$5)V Exceli 2016 alebo 2019 použite kombináciu INDEX a MATCH. Pre rovnaký obrátený cenník bude vzorec:
=INDEX(Cenník!$A$2:$A$5;MATCH(B2;Cenník!$B$2:$B$5;0))MATCH nájde pozíciu ID v zadanom rozsahu a INDEX vráti cenu na rovnakej pozícii. Nula vo funkcii MATCH znamená presnú zhodu. Vyhľadávací a výsledkový rozsah preto musia začínať aj končiť na rovnakých riadkoch.
Vzorec nefunguje: čo skontrolovať podľa výsledku
#NEDOSTUPNÝ alebo #N/A, hoci ID v tabuľke vidíte
Pri vyhľadávacích vzorcoch táto chyba často znamená, že sa nenašla požadovaná zhoda. Najprv skontrolujte, či ID skutočne existuje v oblasti uvedenej vo vzorci. Produkt v riadku 6 sa pri rozsahu končiacom riadkom 5 nenájde. Potom overte typ údajov a skryté znaky.
- Číslo a text nie sú to isté. Hodnota
123uložená ako číslo sa pri presnom vyhľadávaní nemusí spárovať s rovnakými znakmi uloženými ako text. Typy kľúčov zjednoťte v oboch tabuľkách. - Počiatočná nula môže byť súčasťou ID. Ak je správny kód
00123, neprevádzajte ho bez rozmyslu na číslo123. Pri takýchto kódoch zachovajte textový tvar; pomôže návod, ako zachovať nulu na začiatku v Exceli. - Medzera navyše môže zmeniť kľúč. Skontrolujte nechcené medzery pred ID, za ním aj skryté znaky, najmä po kopírovaní z webu alebo prevode PDF do Excelu.
Microsoft tieto príčiny opisuje v návode na opravu chyby #N/A. Ak máte obyčajné číselné hodnoty omylom uložené ako text a nepotrebujete zachovať počiatočné nuly, označte ich a pri dostupnom upozornení zvoľte Konvertovať na číslo. Samotná zmena vzhľadu bunky nezaručuje zmenu uloženého typu.
Na bežné nechcené medzery a niektoré netlačiteľné znaky môžete v pomocnom stĺpci skúsiť =TRIM(CLEAN(A2)). Pred použitím vyčistených ID overte, že ste nezmenili ich význam. TRIM napríklad sám neodstráni nedeliteľnú medzeru, ktorá sa objavuje v údajoch z webu.
Ako pri chýbajúcej zhode zobraziť text „Nenájdené“
Po kontrole údajov môžete očakávanú chybu nenájdenej hodnoty nahradiť zrozumiteľným textom. Pri XLOOKUP pridajte štvrtý argument:
=XLOOKUP(B2;Cenník!$A$2:$A$5;Cenník!$B$2:$B$5;"Nenájdené")Pri VLOOKUP môžete použiť IFNA, ktorá zachytáva iba chybu #N/A:
=IFNA(VLOOKUP(B2;Cenník!$A$2:$B$5;2;0);"Nenájdené")IFERROR zachytáva aj ďalšie chyby, preto pri ladení môže zakryť poškodený odkaz či neznámu funkciu. Ani náhradný text neopraví preklep v ID; IFNA navyše zachytí aj #N/A prichádzajúcu zo zdrojovej bunky. Chýbajúcu cenu nenahrádzajte automaticky nulou, ktorá by mohla vyzerať ako platná cena. Rozdiel medzi chybami v Exceli a ich ošetrením vysvetľujeme podrobnejšie osobitne.
#NÁZOV? alebo neznámy XLOOKUP
Chyba #NÁZOV?, v angličtine #NAME?, znamená, že Excel nerozpoznal časť vzorca. Skontrolujte názov funkcie, pomenované oblasti a úvodzovky okolo textu. Microsoft odporúča opraviť zápis namiesto zakrývania chyby cez IFERROR.
Ak XLOOKUP funguje kolegovi a vám nie, overte svoje vydanie Excelu. Názov s predponou _xlfn. môže sprevádzať nepodporovanú funkciu; samotné odstránenie predpony ju nesprístupní. Zošit otvorte vo vydaní s XLOOKUP alebo vzorec prepíšte na VLOOKUP či INDEX a MATCH podľa usporiadania údajov.
#ODKAZ! alebo cena z nesprávneho stĺpca
Pri VLOOKUP skontrolujte číslo výsledkového stĺpca. Oblasť A2:B5 má dva stĺpce; požiadavka na tretí vráti #ODKAZ!, teda #REF!. Ak do širšej oblasti vložíte nový stĺpec medzi ID a cenu, pôvodné číslo môže ukazovať na iný údaj. Doláre chránia odkaz pri kopírovaní, nie význam tohto čísla. Po zmene štruktúry preto výsledok znova skontrolujte.
Čo sa stane, keď sa rovnaké ID opakuje?
VLOOKUP s presnou zhodou aj XLOOKUP pri predvolenom vyhľadávaní vrátia prvú zhodu zhora. Ak má rovnaký produkt v cenníku dve rôzne ceny, samotný vzorec vás na tento rozpor neupozorní.
Pri zjednotených textových ID z nášho príkladu môžete počet výskytov preveriť cez COUNTIF:
=COUNTIF(Cenník!$A$2:$A$5;B2)Výsledok 0 znamená, že sa kód v oblasti nenašiel, 1 jeden výskyt a vyššie číslo opakovanie. Pri týchto krátkych kódoch bez zástupných znakov je to jednoduchá kontrola pred párovaním.
Opakované ID však nemusí byť chyba: môže ísť o cenu pre inú oblasť alebo inú platnosť. Najprv určte, ktorý záznam potrebujete. Ak sú riadky skutočne nadbytočné, použite postup na kontrolu a odstránenie duplicít v Exceli; samotné opakovanie ID nie je dôvod na mazanie.
Ak zámerne potrebujete posledný výskyt podľa poradia riadkov, XLOOKUP môže hľadať od konca. Šiesty argument -1 mení smer vyhľadávania, piaty argument 0 ponecháva presnú zhodu:
=XLOOKUP(B2;Cenník!$A$2:$A$5;Cenník!$B$2:$B$5;"Nenájdené";0;-1)Posledný výskyt nie je automaticky najnovší záznam podľa dátumu. To platí iba vtedy, ak tomu zodpovedá poradie údajov.
Vyhľadávanie podľa dvoch podmienok: ID a oblasť
Pre tento variant použite cenník so stĺpcami A = ID, B = Cena a C = Oblasť. V objednávke je požadované ID v B2, oblasť v C2 a cenu chcete do D2. Ukážky pracujú s riadkami 2 až 5; rozsahy prispôsobte skutočnému cenníku.
Jedna cena cez XLOOKUP
=XLOOKUP(1;(Cenník!$A$2:$A$5=B2)*(Cenník!$C$2:$C$5=C2);Cenník!$B$2:$B$5;"Nenájdené")Obe porovnania vytvoria výsledky pravda alebo nepravda. Ich násobenie dá hodnotu 1 tam, kde v rovnakom riadku sedí ID aj oblasť. XLOOKUP hľadá túto jednotku a vráti príslušnú cenu. Ak kombinácii vyhovuje viac riadkov, stále dostanete iba prvú zhodu.
Všetky vyhovujúce riadky cez FILTER
Ak potrebujete vidieť všetky zodpovedajúce záznamy, použite FILTER s oboma podmienkami. Nasledujúci vzorec vráti ID, cenu aj oblasť:
=FILTER(Cenník!$A$2:$C$5;(Cenník!$A$2:$A$5=B2)*(Cenník!$C$2:$C$5=C2);"Nenájdené")Zadajte ho napríklad do E2 mimo excelovej tabuľky a nechajte voľné bunky pod ním aj napravo. Pri nájdených záznamoch sa výsledok rozšíri podľa počtu zhôd a zaberie tri stĺpce. Ak zhoda chýba, zobrazí sa text „Nenájdené“. FILTER je dostupný v Microsoft 365, Exceli 2021 a Exceli 2024 aj v Exceli pre web; Excel 2016 a 2019 ho nepodporujú.
Dve podmienky v Exceli 2016 alebo 2019
V staršom Exceli môžete vytvoriť pomocný kľúč. Do D2 na hárku Cenník zadajte nasledujúci vzorec a skopírujte ho po posledný riadok cenníka:
=A2&"|"&C2Operátor & spája text. Z ID a oblasti tak vznikne napríklad P102|Západ. Do D2 na hárku Objednávky potom vložte:
=INDEX(Cenník!$B$2:$B$5;MATCH(B2&"|"&C2;Cenník!$D$2:$D$5;0))Oddeľovač | nesmie byť súčasťou pôvodného ID ani názvu oblasti, aby sa rôzne dvojice nezliali do rovnakého kľúča. Vzorec vracia prvú zhodu; aj tu musí byť kombinácia ID a oblasti jednoznačná, ak má existovať iba jedna správna cena.
