SVYHLEDAT a POZVYHLEDAT - prakticky v Excel

Úvodem do SVYHLEDAT a POZVYHLEDAT

Ve firmách často vidím požadavek na propojená dvou tabulek na základě ID (odměny, nabídkové listy, atd.). Ač uživatelé ví, že existují funkce SVYHLEDAT, POZVYHLEDAT a smíšené odkazování nedokáží tyto věci spojit a efektivně tabulky propojit. Navíc pokud mají tabulky označené jako tabulky již se k úspěšnému zadaní vzorců nedokáži dostat (navíc najít postup smíšeného odkazování u tabulek definovaných jako tabulky není snadno dohledatelný, moc se o něm nepíše).

Proto jsem napsal tento článek, ať informace nemají k dispozici jen účastnící mých školení, ale můžete se inspirovat i vy. Pro přehlednost je článek rozdělen na kapitoly:

• Předpokládané znalosti - ať nemusím opakovat syntaxi funkcí SVYLHEDAT, POZVYHLEDAT
• Zdrojová data - ať nemusíte vytvářet, ukážeme si na odměnách pro zaměstnance
• Postup řešení klasickým vzorcem
• Postup řešení využitím tabulka jako Tabulka - triky na smíšené odkazování, které jen tak nenajdete ;)
• Související články
• Ke stažení

Předpokládané znalosti

V článku předpokládám znalost pár základních použití Excel, funkce a smíšené odkazování, potažmo tabulka jako tabulka. Pokud si nejste jistí, můžete se na témata před začátkem čtení podívat:

• Smíšené odkazování - aneb co to jsou ty dolary $
• Funkce SVYHLEDAT - včetně syntaxe a příkladů
• Funkce POZVYHLEDAT - včetně syntaxe a praktických příkladů
• Tabulka jako tabulka

Hotovo? Můžeme dále.

Zdrojová data

Ať si data nemusíte vytvářet, nebo hledat své tabulky. Dávám k dispozici své demo tabulky. Jedná se o fiktivní výrobní firmu, které chce odměnit své zaměstnance. K dispozici sjou dva listy, na jednom je seznam zaměstnanců. Každý zaměstnanec má jedinečné ID (osobní číslo) a kromě tohoto čísla tabulka obsahuje další údaje, jméno, příjmení, plat, titul atd. Na dalším listě je k dispozici sloupec ID (osobních čísel) těch zaměstnanců, kteří mají být odměněni 10% z platu. Neboli z velké tabulky potřebuji "dotáhnout" Oslovení, Jméno, Přijmení, Pohlaví, Plat, tak abych mohl "dopočíst" odměnu (=buňka*0,1).

Na dalších dvou listech jsou stejné tabulky, jen jsou nadefinovany jako Tabulka a mají svá jména ZAMESTANCI, ODMENY.

Zaměstnanci

Odměny

Zdrojová data

Soubor SVYHLEDAT a POZVYHLEDAT - Excel - zdrojová data ke stažení zdarma.

Staženo? Můžeme se vrhnout na zpracování

Postup řešení klasickým vzorcem

Nejjednodušší možností je několikrát zopakovat funkci SVYHLEDAT, ale při větším počtu sloupců se vám nechce psát několikrát stejnou funkci (navíc chcete ušetřit svůj, potažmo firemní čas, který můžete věnovat jiným smysluplnější věcem).

Při zamyšlení ve funkci SVYHLEDAT se opakuje pouze číslo sloupce. Pokud se zamyslíte tak požadované číslo sloupce, můžete zjistit z funkce POZVYHLEDAT. Pokud použijete smíšené odkazování, máte vyhráno.

=SVYHLEDAT($B8;Zaměstanci!$A:$L; POZVYHLEDAT(C$7;Zaměstanci!$A$1:$L$1;0);NEPRAVDA)

Postup řešení využitím tabulka jako Tabulka

Dalším elegantnějším postupem je využít možnost tabulky v Excel mít nadefinované (nastavené) jako Tabulka. Bohužel při využití smíšeného odkazování ("zamknutý" sloupec) se vám intuicí nemusí na první pohled podařit.

K odkazování se musí využít Trik jak zablokovat sloupec. Neboli využít odkaz na tabulku s názvem ODMENY a s názvem sloupce ID. Když stačí zadat (pro zablokovaný sloupec):

ODMENY[[ID]:[ID]]

Výsledkem je poté funkce využívající podmíněné odkazování s využitím tabulka jako Tabulka.

=SVYHLEDAT(ODMENY[[ID]:[ID]];ZAMESTANCI;POZVYHLEDAT(ODMENY[[#Záhlaví];[Oslovení]];ZAMESTANCI[#Záhlaví];0);NEPRAVDA)

Související články

S článkem souvisí následující články:

• Smíšené odkazování - aneb co to jsou ty dolary $
• Funkce SVYHLEDAT - včetně syntaxe a příkladů
• Funkce POZVYHLEDAT - včetně syntaxe a praktických příkladů
• Tabulka jako tabulka

Ke stažení

Soubor SVYHLEDAT a POZVYHLEDAT - Excel - zdrojová data ke stažení zdarma.

Závěrem

Využíváte ke spojování tabulek jiný trik? Kromě relací?

Č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ář.