Jak získat data ze seznamu / databáze

Co je to seznam (databáze) v Excelu jsme si ukázali tady.

Co znamená "získat data"

Pro zjištění souhrnných dat (jaká je celková cena náhradních dílů, kolik jízd bylo uskutečněno v červnu atd.) nabízí Excel tyto možnosti:

  • Filtrování záznamů a výpočet součtu, průměru atd. pouze z filtrovaných hodnot.
    Ano, tady nemůžeme použít funkce SUMA, PRŮMĚR atd. je třeba použít funkci SUBTOTAL.
  • Použití funkcí SUMIFS, COUNTIFS, AVERAGEIFS atd.
  • Použití databázových funkcí.
  • Vytvoření kontingenční tabulky.

Potřebujeme-li ze seznamu získat údaje o jednom konkrétním řádku, použije buď funkci SVYHLEDAT nebo kombinaci funkcí POZVYHLEDAT a INDEX. To není předmětem tohoto článku.

Filtrování dat

Pro filtrování můžeme použít nástroj Filtr (karta Data) a do listu doplnit požadované výpočty. Excel nabízí jedinou funkci, která dokáže pracovat s filtrovanými daty, a tou je funkce SUBTOTAL (kategorie Mat. a trig., ve starších verzích kategorie Matematické). Jedním z prametrů funkce určujete, jaký výsledek požadujete (součet, počet záznamů splňujích podmínku atd.).

U složitějších filtrů, které není možné nastavit pomocí nástroje Filtr, použijeme nástroj Upřesnit (karta Data) a příslušnou kriteriální tabulku.

Nevýhodou tohoto způsobu je to, že Excel skryje řádky, které neprošly filtrem, vzorce proto vkládáme pouze nad nebo pod filtrovanou tabulku.
Vložíte-li vzorec vedle filtrované tabulky a daný řádek filtrem neprošel, Excel tento řádek skryje a tím dočasně zmizí i vzorec.

Výhody: Jednoduché nastavení podmínek, ke všem výpočtům stačí znalost jediné funkce SUBTOTAL.

Nevýhody: Vzorce s výpočty je vhodné vkládat nad nebo pod tabulku.

Funkce ...IFS (SUMIFS, COUNTIFS, AVERAGEIFS)

Ukázková tabulka

Způsob fungování uvedených funkcí si ukážeme na příkladu: Z uvedené tabulky potřebujeme zjistit kolik šamponů se celkem prodalo: funkci SUMIFS zadáme aby sečetla hodnoty ve sloupci Prodano pouze z těch řádků, které mají ve sloupci Kategorie hodnotu Šampony.

Dostupnost funkcí

  • Excel nabízí uživatelům dlouhou dobu funkce SUMIF, COUNTIF a AVERAGEIF. Mají jedno společné omezení: můžete zadat pouze jedinou podmínku výběru.
  • Do Excelu 2010 doplnil Microsoft funkce SUMIFS, COUNTIFS a AVERAGEIFS, u kterých je možné zadávat více podmínek.
  • Do Excelu 2019 byly doplněny funkce MAXIFS a MINIFS.
  • Uživatelé Excelu 365 mají k dispozici všechny uvedené funkce.

Poznámka: Pořadí parametrů u funkcí SUMIFS a AVERAGEIFS se odlišuje od pořadí zadávání parametrů u funkcí SUMIFS a AVERAGEIFS. Zvažte možnost používání pouze funkcí, jejichž názvy končí na ...IFS.

Zadáte-li u kterékoliv z uvedených funkcí více podmínek, musí být tyto podmínky splněné všechny, aby byl řádek zahrnutý do výpočtu.

  • Požadavek "Sečti počet prodaných mýdel z regionu JČ" funkce SUMIFS zvládne. Jde o podmínky: "Ve sloupci Kategorie je hodnota Mýdla: a "ve sloupci Region je hodnota JČ".
  • Požadavek "Sečti počet prodaných mýdel z regionů JČ a ZČ" funce nezvládne. Jde o podmínku "Ve sloupci Region je hodnota JČ nebo hodnota ZČ".

Výhody: Jednoduché nastavení požadovaných podmínek.

Nevýhody: U více podmínek nelze nastavit operátor NEBO. Máte-li ve firmě různé verze Excelu, je třeba dát pozor na používání funkcí MAXIFS a MINIFS, které jsou k dispozici až od Excelu 2019.

Databázové funkce

Databázové funkce se často označují jako D-funkce, názvy všech databázových funkcí začínají písmenem D.

Výborná zpráva je to, že všechny databázové funkce v Excelu zadáváme stejným způsobem (tahle oblast je seznam, v tomto sloupci provedeš výpočet a tady máš podmínky, které musí daný řádek splnit). Proč to je výborné? Jak pochopíte jedinou databázovou funkci, umíte je zadat do vzorce všechny.

Co se výpočtu týká, DSUMA bude v daném sloupci sčítat, DPRŮMĚR spočítá ze stejných dat průměrnou hodnotu atd.

Podmínky výběru (podmínky, které musí řádek tabulky splnit, aby jeho hodnoty zahrnula funkce do výpočtu) zadáváme jako kriteriální tabulku. To má dvě výhody:

  • Kriteriální tabulky dovolují zadání složitých podmínek obsahující operátor A (AND) i operátor NEBO (OR).
  • Na jednu kriteriální tabulku se může odkazovat více databázových funkcí. To využijeme tam, kde pro danou skupinu řádků tabulky potřebujeme zjistit více informací.
    Např. z tabulky jízd potřebujeme pro dané vozidlo zjistit počet jízd a celkový počet najetých km. Funkcí DSUMA zjistíme najeté km, funkcí DPOČET určíme počet jízd. Obě funkce se odkazují na stejnou kriteriální tabulku, do které zadáváme SPZ požadovaného vozidla.

Výhody: Pomocí kriteriáních tabulek můžeme zadávat i složité podmíny výběru. Na jednu kriteriální tabulku se může odkazovat i více vzorců.

Nevýhody: Kriteriální tabulky vyžadují alespoň dva řádky, s tím je nutné počítat při umisťování vzorců.

Kontingenční tabulky

Kontingenční tabulka je nástroj pro vytvoření tabulky se souhrnnými údaji. Základní kontingenční tabulku vidíte na připojeném obrázku. 

Základní kontingenční tabulka

V horní části obrázku jsou označené sloupce, které kontingenční tabulka zpracovává, v dolní části vidíte kontingenční tabulku, která ukazuje tržby za jednotlivé kategorie zboží v jednotlivých lokalitách.

Připojený obrázek je ilustrační pro ty z vás, kteří se s kontingenčí tabulkou dosud nesetkali. Kontingenční tabuka je velmi silný nástroj a umožňuje rychle získat požadovaná data.

Výhody: Velmi rychle získáte novou tabulku se souhrnnými údaji.

inPage - webové stránky, doménawebhosting snadno.