Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce-vyhledavaci » SVYHLEDAT-POZVYHLEDAT-prakticky-Excel
Microsoft Excel logo

SVYHLEDAT a POZVYHLEDAT - prakticky v Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak zkombinovat funkce SVYHLEDAT a POZVYHLEDAT při slučování tabulek. Navíc využitím smíšeného odkazování v Tabulka jako tabulka.

Ú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

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:

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

Zdrojová data -SVYHLEDAT a POZVYHLEDAT - Excel

Odměny

Zdrojová data -SVYHLEDAT a POZVYHLEDAT - Excel

Zdrojová data

Soubor SVYHLEDAT a POZVYHLEDAT - Excel - zdrojová data soubor ve formátu *.xlsx 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) Zdrojová data -SVYHLEDAT a POZVYHLEDAT - Excel

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) Zdrojová data -SVYHLEDAT a POZVYHLEDAT - Excel

Související články

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


Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor SVYHLEDAT a POZVYHLEDAT - Excel - zdrojová data soubor ve formátu *.xlsx ke stažení zdarma.


Závěrem

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

Článek byl aktualizován: 08.02.2019 13:40

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 2258

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.