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

Jste zde: Úvodní stránka » excel » funkce-vyhledavaci » SVYHLEDAT-INDEX-POSUN-vice-vyhledavacich-kriterii
Microsoft Excel logo

SVYHLEDAT, INDEX, POSUN více vyhledávacích kritérií

Videokurzy Excel

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

Potřebuji pomocí funkcí vyhledat dle více vyhledávacích kritérií. Neboli v řádku hledám rok a měsíc a ve sloupci město a případně i pohlaví.

Úvodem

Mám k dispozici tabulku, kde mám například počty osob žijicích v jednotlivých městech podle pohlaví v letech a měsících (kdy Rok je v jednom řádku a Měsíc v druhém řádku), kdy pohlaví a města jsou ve dvou sloupcích (v prvním sloupci Pohlaví a ve druhém Město).

Mám k dispozici možnost vybrat Rok, Měsíc a následně i Město (případně i Pohlaví). Následně chci vědět daný počet obyvatel.

Chci využít funkce SVYHLEDAT (VLOOKUP) a INDEX.

Pro přehlednost je článek rozdělen na jednotlivé kapitoly:

Ukázka

Excel

Úkolem je napsat funkce, které hodnotu na zvoleném průsečíku zjistí.

Zdrojová data

Mám k dispozici tabulku, kde v řádcích jsou roky a pod rokem i měsíce. Ve sloupci jsou města (případně navíc ještě sloupec s pohlavím). V datech jsou například počty obyvatel (jedná se o demodata). Co potřebuji:

  • 1) Vybrat Rok, měsíc a město a získat dané číslo
  • 2) Vybrat Rok, měsíc a město pohlaví a získat dané číslo

Použiji funkce SVYHLEDAT a INDEX s využitím maticového zápisu ať se mohu dostat správný průsečík. Protože funkce SVYHLEDAT hledá jen podle jednoho sloupce, ukazuji i použití s funkci INDEX. Případně POSUN.

Poznámka

Předpokládám znalosti výše uvedených funkcí a základního použití maticových vzorců, proto jsem vytvořil jen jednu ukázku, ať zbytečně netvořím 3 články - pro každou funkci zvlášť.

Ukázka

Excel

SVYHLEDAT (VLOOKUP)

Funkce SVYHLEDAT hledá jen podle jednoho sloupce (prvního), proto se budeme odkazovat jen na název města (vím že najde jen první výskyt hledané hodnoty ve sloupci, což v ukázce nevadí - v praxi budete mít unikátní hodnoty).

Pro řádek ale můžeme spojit, protože využijeme funkci POZVYHLEDAT. Zde využívám možnosti spojovat řetězce (pro zřetězení využívám & tj. zpřehledním si práci, když nepoužiji funkci CONCATENATE). Z důvodů přehlednosti neodděluji znaky (dají se pak provádět další zajímavé triky), nebo když je potřeba přehlednost využívám podtržítko, někdo má rád mezeru (či jiný znak). Je na vás co se vám líbí.

Funkci pak musíme zadávat maticově, Ctrl+ Shift + Enter.

spojení řetězců E16&E15 nebo K16&"_"&H16 nebo K16&" "&H16 ... mezeru nemám rád :(

Samozřejmě podobně je potřeba označit oblast prohledávanou oblast:

C7:K7&C6:K6 pro další možnosti si jistě napíšete sami

Po vložení do funkce POZVYHLEDAT (MATCH) máte výsledek číslo sloupce:

POZVYHLEDAT(E16&E15;C7:K7&C6:K6;0)

Pak jen stačí vložit do funkce SVYHLEDAT a zadat maticově (pro sloupec máme jen jendo kritérium):

=SVYHLEDAT(H16;C8:K13;POZVYHLEDAT(E16&E15;C7:K7&C6:K6;0);NEPRAVDA)

Ukázka

Excel SVYYHLEDAT - podle více kriterií v maticovém tvaru

Poznámka

Čtyři kritéria lze rešit i s funkci SVYHLEDAT, jen se do zdrojové tabulky musí vložit pomocný sloupec se spojením Pohlaví a města, což zatím dělat nechci, protože jsou k dispozici funkce INDEX a POSUN mohu vyřešit i bez zásahu do zdrojových dat.

INDEX (INDEX)

Pro čtyři vyhledávací kritéria (dva ve sloupci, dva v řádku) již SVYHLEDAT nelze použít, ale nádherně lze použít funkci INDEX, případně POSUN (ta je popsána v následující kapitole).

Opět využijete možnost sloučit dvě vyhledávací kritéria pro řádky / sloupce, jako v předchozím případě.

Princip spojení již nebudu popisovat, jen upozorním, že jde o maticový vzorec, proto nezapomenout Ctrl + Shift + Enter.

{=INDEX(D8:K13;POZVYHLEDAT(K16&"_"&H16;B8:B13&"_"&C8:C13;0);POZVYHLEDAT(E16&" "&E15;D7:K7&" "&D6:K6;0))}

Ukázka

Excel INDEX - podle více kriterií v maticovém tvaru

POSUN (OFFSET)

Pro čtyři vyhledávací kritéria (dva ve sloupci, dva v řádku) lze použít i funkce POSUN (nebo INDEX ta je popsána v předchozí kapitole).

Opět využijete možnost sloučit dvě vyhledávací kritéria pro řádky / sloupce, jako v předchozím případě.

{=POSUN(C7;POZVYHLEDAT(K16&"_"&H16;B8:B13&"_"&C8:C13;0);POZVYHLEDAT(E16&" "&E15;D7:K7&" "&D6:K6;0))}

Ukázka

Excel POSUN - podle více kriterií v maticovém tvaru

Tip na jiná řešení

Co lze dále tvořit?

  • Sloučené řádky a sloupce - neboli s čím nám pomůže maticový vzorec, si můžeme pomoci pomocným řádkem a sloupcem
  • Využít funkci NEPŘÍMÝ.ODKAZ (INDIRECT) - pro dynamické tvorby oblastí (například v kolika řádcích hledat
  • ...

Sloučené řádky a sloupce - Ukázka

Pokud přidám pomocné sloupce mohu funkci SVYHLEDAT použít bez použití maticového výpočtu

=SVYHLEDAT(U16&"_"&R16;N8:V13;POZVYHLEDAT(O15&"_"&O16;N7:V7;0);NEPRAVDA) SVYHLEDAT Excel - hledání podle více kriterií

Využít funkci NEPŘÍMÝ.ODKAZ (INDIRECT) - Ukázka

Pokud přidám pomocné sloupce mohu funkci SVYHLEDAT použít bez použití maticového výpočtu

=INDEX(NEPŘÍMÝ.ODKAZ("D8:K13");POZVYHLEDAT(K16&H16;NEPŘÍMÝ.ODKAZ("B8:B13")&NEPŘÍMÝ.ODKAZ("C8:C13");0);POZVYHLEDAT(E16&E15;NEPŘÍMÝ.ODKAZ("D7:K7")&NEPŘÍMÝ.ODKAZ("D6:K6");0)) Excel INDEX - podle více kriterií v maticovém tvaru a NEPRIMYM.ODKAZEM

Tip: Dopočet, řádku, odkaz na jiný list, jehož výběr bude přes výběrovou buňku si již jednoduše doplníte sami.

Související články

Jedná se o pokročilejší použít funkcí, pokud si chcete zopakovat základy, můžete využít těchto článků:


Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor v přípravě.


Závěr

Napadá vás jiný trik, jak data získat, můžete zmínit v komentářích.

Článek byl aktualizován: 01.03.2019 09:43

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


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 | 730

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