V dnešním videu si ukážeme řešení několika pokročilých excelových problémů. Ukážeme si, jak vyhledávat pomocí mnohonásobných kritérií, jak sčítat, vyhledávat a filtrovat pomocí zástupných znaků nebo jak sčítat hodnoty dle variabilních kritérií.
Excelový soubor ke stažení
Komplexní vyhledávání
V prvním příkladu máme za úkol dohledat z tabulky tržbu. Máme zde ovšem několik různých kritérií, tržba musí odpovídat zvolenému produktu a typu produktu, stejně jako musí spadat do vybraného roku a měsíce. K dohledání správné tržby použijeme vyhledávací kombinaci INDEX a POZVYHLEDAT neboli anglicky INDEX a MATCH. A budeme muset spojit několik kritérií dohromady.
Ve funkci INDEX nejprve označíme v parametru pole všechny číselné hodnoty. Následně v parametru řádky musíme použít funkci POZVYHLEDAT (MATCH), kde musíme spojit dvě kritéria, tedy produkt a typ produktu. Následně musíme spojit i dva sloupce ve funkci POZVYHLEDAT, tedy sloupec produkt a typ produktu.
To samé uděláme i v parametru sloupce. Opět použijeme funkci POZVYHLEDAT, kde spojíme dvě kritéria, měsíc a rok. Díky této kombinaci jsme schopní najít hledanou hodnotu na základě celkem 4 kritérií.
Zástupné znaky
Umět pracovat se zástupnými znaky neboli wildcards je v Excelu velmi užitečné. Se zástupnými znaky lze v Excelu počítat, sčítat, vyhledávat ale i filtrovat. V prvním příkladu chceme sečíst tržby pro bílé nebo červené produkty. Jelikož se před nebo za slovy bílé a červené může vyskytovat i jiné slovo, tak musíme použít zástupné znaky. Stejně tak nevíme, jak jsou slova bílé a červené skloňované, takže bychom měli ošetřit i skloňování. Navíc musíme sečíst dvě funkce SUMIF, jelikož zde máme logickou podmínku NEBO (bílé nebo červené produkty).
V dalším příkladu potřebujeme se zástupnými znaky vyhledávat. Ideální je použít novou funkci XLOOKUP, kde máme v režimu shody novou možnost – Shoda pomocí zástupných znaků. Díky této shodě můžeme zástupné znaky použít přímo v parametru o a následně v parametru shoda vybereme možnost 2 (shoda pomocí zástupných znaků).
Pomocí zástupných znaků můžeme i filtrovat data, a to pomocí funkce FILTER. V příkladu chceme do vlastní tabulky vyfiltrovat všechny bílé produkty.
Ve funkci FILTER ale musíme použít pomocnou funkci HLEDAT (FIND), pomocí které identifikujeme řádky, které budou obsahovat filtrované slovo. Abychom ale funkci HLEDAT mohli použít ve funkci FILTER, tak ji musíme zabalit do funkce JE.ČISLO. Tato funkce vrátí jedničku na řádcích, které vyhovují filtru. To celé následně zabalíme do funkce FILTER.
Součet buněk
V dalším příkladu potřebujeme sečíst určitý počet vybraných buněk, na základě několika kritérií. V příkladu máme celkem tři kritéria, chceme sečíst x tržeb, přičemž počet tržeb k součtu určuje buňka B12. Tržby chceme sečíst od data, které určíme v buňce B11 a tržby chceme sečíst pro produkt, který vybereme v buňce B10. K součtu tržeb použijeme funkci POSUN, neboli funkci OFFSET.
Ve funkci POSUN (OFFSET) stanovíme odkaz jako první buňku v tabulce, to je buňka odkud se funkce POSUN začne posouvat.
Následně musíme určit řádek, k čemuž použijeme funkci POZVYHLEDAT neboli funkci MATCH. Pomocí této funkce určím pořadové číslo řádku, a to podle zvoleného produktu.
V parametru sloupec ve funkci POSUN musíme rovněž použít funkci POZVYHLEDAT, pomocí kterého určíme pořadové číslo sloupce, a to podle datumu.
Parametr výška nepotřebujeme, ale potřebujeme parametr šířka, kde zvolíme buňku B12, tedy kolik buněk chceme sčítat.
Nakonec nezbývá než funkci POSUN zabalit do funkce SUMA, která vybrané tržby sečte.
Jedna odpověď
😊