S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

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
  • F1 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.

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

  1. 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. 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. 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. 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. 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. Dobrý den,

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

    Děkuji.

Napsat komentář

Vaše emailová adresa nebude publikována.

*

smazat formulářOdeslat komentář