Dohledání hodnoty podle více sloupců najednou s využitím funkcí SVYHLEDAT nebo SUMIFS

V tomto návodu je popsáno, jak přiřazovat hodnoty na základě více kritérií než jednoho. Jsou zde popsané cesty přes funkci SUMIFS a přes funkci SVYHLEDAT, Pokud vás zajímá dosazování z více tabulek pomocí SVYHLEDAT, klikněte tady.

Vyjdeme z této ukázkové tabulky. V levé části je přehled zájezdů, u kterých sledujeme lokalitu, délku zájezdu a cenu.

původní tabulka

V pravé části chceme vybrat lokalitu a délku, a požadujeme, aby Excel přiřadil odpovídající cenu. Např. pro desetidenní Turecko má být výsledkem 9000 Kč. 

Máme v zásadě dva způsoby, jak to udělat. Můžeme použít funkci SUMIFS. V takovém případě nevytváříme žádné pomocné sloupce a samotný zápis je docela jednoduchý. Nevýhodou je, že výsledkem musí být číslo. V našem případě se jedná o cenu, což opravdu číslo je, ale kdyby tam byly texty, nelze SUMIFS použít.

V takovém případě bychom použili funkci SVYHLEDAT. Její výhodou je, zrcadlově, že umí zpracovat i texty, nevýhodou pak nutnost vytvořit mezisloupec.

Postup s funkcí SUMIFS

Funkce bude zapsaná takto:

  • =SUMIFS(C:C;A:A;G1;B:B;G2)

sumifs

Protože:

  • C:C proto, že ve sloupci je číselná hodnota, kterou chceme "sečíst". Ty uvozovky jsou proto, že předpokládáme, že se jedná pouze o jednu hodnotu, a součtem tedy bude samotná velikost této hodnoty. Logicky bychom mohli úplně stejně použít i AVERAGEIFS, MINIFS, MAXIFS.
  • A je sloupec s lokalitami
  • G1 proto, že hledáme pouze to, co má ve sloupci s lokalitami hodnotu Turecko (ta je v G1)
  • B je sloupec s trváním zájezdů
  • G2 proto, že hledáme pouze to, co má ve sloupci s délkami zájezdů hodnotu 10 dní (viz buňka G2)

Postup s funkcí SVYHLEDAT / VLOOKUP

S funkcí SVYHLEDAT je to trochu složitější. V původních datech musíme vytvořit nový sloupec, který spojí hodnoty (texty nebo čísla) z obou sloupců do jednoho. Použít můžeme funkce CONCAT, CONCATENATE nebo spojovací znak &.

concat

Tento sloupec by měl být před sloupcem s cenami, aby fungovala funkce SVYHLEDAT, jinak bychom museli zkombinovat POZVYHLEDAT a INDEX.

Pak můžeme zapsat samotnou funkci SVHLEDAT.

  • =SVYHLEDAT(CONCAT(G1:G2);C:D;2;0)
  1. Jejím prvním argumentem je "slepenec" buněk G1 a G2, v našem případě tedy "Turecko10 dní".
  2. Podle něj pak hledáne ve sloupcích C a D a přiřazujeme náležitou hodnotu.

svyhledat

K těmto dvěma způsobům můžeme přidat ještě třetí - použití funkce DZÍSKAT / DGET a něco velmi podobného lze vytvořit také pomocí kontingenční tabulky.

7 comments on “Dohledání hodnoty podle více sloupců najednou s využitím funkcí SVYHLEDAT nebo SUMIFS
  1. Anonym napsal:

    A vzhledem k tomu, že SUMIFS neexistoval před Excelem 2007 a o pomocné sloupce nikdo nestojí, tak se nejčastěji používá maticový vzorec a slepenec se vytváří „za letu“.

  2. Jiří Beran napsal:

    Vzhledem k tomu, že Excel 2003 už dnes nikdo nepoužívá a maticové vzorce nepoužívá 99% uživatelů, si myslím, že i tento návod může někomu pomoci.

  3. Anonym napsal:

    Excel 2003 už sice mizí, rozhodně se s ním ale potkáte. Chápu, že ne vždy na něj můžete brát v článcích ohled, a řešení nebude všeobjímající.
    Je fér zmínit, že existuje i další způsob, i když ho nechcete předvést.
    Pomocný sloupec – budiž, i když není nutný. Ale navíc díky SVYHLEDAT nutíte uživatele vložit ho do čistých dat a to je parádní tfujtajxl.
    Netvrdím, že návod nemůže pomoci.

  4. Irena napsal:

    Je to super, výklad srozumitelný, ale trochu mně mrzí, že si to člověk nemůže hned vyzkoušet na příkladu, jako to bylo u kontingenčních tabulek.

  5. Roman Matušek napsal:

    Dobrý den,

    díky za návod. Mě velmi pomohl. Nepotřeboval jsem „pomocný sloupec“, text jsem již měl spojený dříve, ale struktura příkladu, který byl podobný mému problému.

    Díky

  6. Tendr Habina napsal:

    Dobrý den,

    dá se funkce svyhledat použít i tabulce na google drive?

    Děkuji.

  7. Jiří Beran napsal:

    Ano, není tam ale přeložená – jmenuje se VLOOKUP

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář
TOPlist