SVYHLEDAT, NEPŘÍMÝ.ODKAZ - Excel

Úvodem

Máme k dispozici několik tabulek kurzů podle měny na samostatných listech. Kdy jednotlivá měna obsahuje (na samostatném listě) obsahuje datum a hodnotu kurzu příslušející pro tento den.

V listě Hledat je potřeba k zadanému dnu a měně příslušný kurz.

• Data
• Pomocná tabulka
• Řešení SVYHLEDAT a NEPŘÍMÝ.ODKAZ
  • přes pomocnou tabulku
  • maticovým zápisem - pomocné tabulky
  • využitím tabulka jako tabulka - aneb pojmenujte si tabulky
• Související články
• Data ke stažení

Poznámka

Lze využít i v rozšířené podobě kdy datum bude obsahovat i čas a tento údaj bude potřeba "zaokrouhlit"). Případně rozšířit o další sloupec ve kterém bude kraj pro který kurz platí, a oblast ve které se hledá vytvořit dynamicky.

Zdrojová data

1) Je potřeba mít k dispozici tabulky kurzů na samostatných listech. Kdy obsahují sloupec Datum a Kurz pro daný den. Jména listů podle názvu kurzů ( EUR, USD, ... ).

2) Cílovou tabulku, kde jsou k dispozici sloupce Datum a Měna . Na základě těchto dvou údajů se přiřadí patřičný kurz (vybere se z příslušného listu - dle údaje ve sloupci Měna) a z tabulky na daném listě se na základě údaje ze sloupce Datum vybere příslušný kurz.

Pomocná tabulka

Vhodné je připravit si tabulku, kterou využijete ve funkci SVYHLEDAT a pomocná tabulka umožní určit oblast dat, ve kterých se bude vyhledávat patřičný kurz.

Tip: U pomocné tabulky můžete využít i dynamické zjištění velikosti tabulky.

Řešení SVYHLEDAT a NEPŘÍMÝ.ODKAZ

Řešení je jednoduché. Začneme vyhledávací funkci SVYHLEDAT, kdy se vyhledává datum v patřičné tabulce. Jelikož tabulky ve kterých se hledá, se vyskytují na různých listech, využijeme funkci NEPŘÍMÝ.ODKAZ, která přiřadí (musí) patřičnou tabulku. Aby byla možnost tabulku přiřadit, je možno využít funkci SVYHLEDAT, funkce SVYHLEDAT v pomocné tabulce vyhledá patřičnou oblast (v této se bude vyhledávat).

=SVYHLEDAT(B5;NEPŘÍMÝ.ODKAZ(SVYHLEDAT(C5;Nastavení!$B$5:$C$8;2;NEPRAVDA));3;NEPRAVDA)

Řešení 2

Lze využít i maticový zápis tabulky, místo druhé funkce SVYHLEDAT (matice se mohou v různých verzích Excel zapisovat různě):

=SVYHLEDAT(B5;NEPŘÍMÝ.ODKAZ(SVYHLEDAT(C5;{"USD"\"USD!B5:D15";"EUR"\"EUR!B5:D15";"GBP"\"GBP!B5:D15";"CHF"\"CHF!B5:D15"};2;NEPRAVDA));3;NEPRAVDA)

Možná přehledněji:

=SVYHLEDAT(B5; NEPŘÍMÝ.ODKAZ(SVYHLEDAT(C5; {"USD"\"USD!B5:D15";"EUR"\"EUR!B5:D15";"GBP"\"GBP!B5:D15";"CHF"\"CHF!B5:D15"}; 2;NEPRAVDA));3;NEPRAVDA)

Tip: Počet znaků ve funkci je omezený, ptoto pro rozsáhleší tabulky se hodí první případ.

Řešení 3

Pokud máte tabulky definované jako tabulka s patřičnými názvy, například tabulku obsahujíci kurzy USD, máte pojemnovaou jako USD. Můžete funkci zjednodušit (nebudete potřebovat druhou funkci SVYHLEDAT, jméno v sloupci kurzu se shoduje s názvem tabulky )

=SVYHLEDAT(B5;NEPŘÍMÝ.ODKAZ(C5);3;NEPRAVDA)

Související články

Více o jednotlivých funkcích v článcích:

• SVYHLEDAT (VLOOKUP)
• NEPŘÍMÝ.ODKAZ (INDIRECT)
• Matice v Excel
• Tabulka jako tabulka

Ke stažení

Soubor Kurzy - data pro vyhledávání v tabulách - Excel ke stažení zdarma.

Závěrem

Máte další typ, jak vylepšit, případně jinou možnost na vyřešení úkolu (zdrojová data nelze změnit), můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:00

Pavel Lasák - autor webu

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ů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.