V Excelu existuje přes 450 funkcí. Ale drtivou většinu práce vyřešíte jen malým zlomkem z nich. V tomhle videu vám ukážu deset excelových funkcí, které za vás v běžné praxi odvedou 80 % práce. Ne proto, že by byly nejnovější nebo nejvíc „wow“, ale proto, že jednoduše řeší skutečné každodenní problémy — součty, vyhledávání, filtrování, práci s textem a základní logiku. Tohle není seznam funkcí, které si zapíšete a zapomenete. Tohle jsou funkce, které když jednou pochopíte, začnete Excel používat rychleji, jistěji, a hlavně přestanete dělat spoustu věcí ručně. Jestli s Excelem pracujete pravidelně a chcete být rychlejší a jistější, tak tohle video je přesně pro vás.
Excelový soubor ke stažení:
SUMIFS
Funkce SUMIFS vám umožní sečíst jen ta čísla, která splňují více podmínek najednou. Místo ručního filtrování nebo pomocných sloupců dostanete výsledek jedním vzorcem. Ideální pro rychlé reporty, přehledy a kontrolu dat.
V příkladu chceme sečíst tržby z tabulky na základě čtyř podmínek. Napíšeme funkci SUMIFS, která dovoluje sčítat hodnoty na základě jedné nebo více podmínek. Prvním parametrem je součet, tedy hodnoty, které chceme sčítat, což je v tomto případě sloupec Tržba. A následně za sebou kupíme sloupce s kritérii a kritéria. Takže první oblastí kritérií jsou produkty, takže sloupec Produkt a jako kritérium vybraný produkt (L4). Následuje jako oblast kritérií sloupec s Regionem a jako kritérium vybraný region.
Jako další oblast kritérií bude sloupec s datumy, a jako první kritérium spodní limit datumu, takže “>=”& a spodní hranice datumu. To samé uděláme pro horní datum, takže kritérium bude “<=”& a horní limit datumu.
Funkce sečte tržby na základě vícenásobné podmínky.
COUNTIFS
Funkce COUNTIFS funguje podobně jako SUMIFS, ale místo součtů počítá řádky. Okamžitě zjistíte, kolik objednávek, zákazníků nebo řádků splňuje daná kritéria. Skvělé pro analýzu objemu, četnosti a kontroly dat.
Funkce COUNTIFS nesčítá hodnoty, ale počítá řádky. Takže ideální pro následující příklad, kde každý řádek představuje jeden prodej a my potřebujeme zjistit počet prodaných produktů podle vybraných datumů. Ve funkci COUNTIFS není oblast součtu, ale začíná se rovnou oblastí kritérií a pak následují kritéria.
Tato funkce spočítá počet podle řádků.
XLOOKUP
Funkce XLOOKUP je moderní vyhledávání, které nemá omezení jako funkce SVYHLEDAT neboli VLOOKUP. Vyhledává doprava i doleva, do kříže, zvládá chybové stavy i podmínky. Jakmile ji začnete používat, ke starým vyhledávacím funkcím se už nevrátíte.
Funkce XLOOKUP vyhledává jak doleva, tak doprava. Což se hodí v následujícím příkladu, kde potřebujeme vyhledávat doleva. Ve funkci XLOOKUP nejprve označíme co hledáme, což je první kód v tabulce. Následují sloupec, kde prohledáváme, tedy sloupec s kódy produktů. A jako poslední povinný parametr následuje, co chceme vrátit. Tedy v tomto případě sloupec s cenou. A pokud máme v datech položku, která se nevyskytuje ve vyhledávací tabulce, tak můžeme využít i nepovinný parametr pokud nenazeleno, kde můžeme vyplnit, co se má vrátit, pokud se hodnota nenajde.
Nejlepší vyhledávací funkce XLOOKUP
FILTER
Funkce FILTER zobrazí jen ta data, která splňují vaše podmínky. Výsledek se automaticky přepočítá při změně vstupních dat. Ideální náhrada klasického filtrování v tabulce pro dynamické přehledy.
Ve funkci FILTER nejprve označujeme pole, které chceme filtrovat, v našem případě celou zdrojovou tabulku. A jako druhý parametr zahrnuje určíme logickou podmínku, podle které filtrujeme. Takže ssloupec se zákazníky, a ti se mají rovnat vybranému zákazníkovi. Potvrdíme funkci a tabulka se vyfiltruje pro záznamy dle logické podmínky.
Filtrovat můžeme i podle hodnot. Pokud chceme vyfiltrovat tabulku pro záznamy, kterých se prodalo více než 10 kusů, tak označíme celé pole a jako logickou podmínku určíme, že sloupec s počtem musí být vyšší než vybraný počet.
UNIQUE
Funkce UNIQUE během vteřiny zobrazí seznam unikátních hodnot bez duplikátů. Žádné kontingenční tabulky ani ruční mazání. Funkce UNIQUE zvládá odstranění duplicit i na více sloupcích. V příkladu chceme vytvořit jedinečný seznam ze sloupce Produkt, takže ve funkci UNIQUE označíme sloupec Produkt. A tato funkce vrátí jedinečný seznam hodnot.
Funkce UNIQUE umí vrátit i jedinečný seznam kombinace mezi sloupci. Pokud chceme jedinečný seznam ze sloupce produkty a regiony, tak ve funkci UNIQUE označíme tyto dva sloupce.
SORT
Funkce SORT umožňuje dynamicky řadit data podle jednoho nebo více kritérií. Po změně dat se pořadí aktualizuje automaticky. Skvělé pro žebříčky, top přehledy a reporting.
Funkce je velmi jednoduchá, ve funkci nejprve označíme pole, které chceme filtrovat, tedy celou tabulku. Druhým parametrem určujeme, podle kterého sloupce chceme filtrovat. Chcete tabulku seřadit podle tržeb, což je sedmý sloupec, takže v tomto parametru bude pořadové číslo sloupce. A ve třetím parametru určujeme, zda se tabula seřadí vzestupně nebo sestupně.
ROZDĚLIT.TEXT / TEXTSPLIT
Funkce ROZDĚLIT.TEXT rozdělí obsah jedné buňky do více sloupců podle oddělovače, který si určíte. Ušetří hodiny ručního kopírování a úprav importovaných dat. Nezbytná funkce při práci s nečistými nebo kombinovanými texty.
Ve funkci ROZDĚLIT.TEXT nejprve označíme buňku s textem a následně určíme oddělovač. V našem případě je oddělovačem svislá čára a před ní i za ní je všude minimálně jedna mezera. Tato funkce rozdělí hodnoty podle oddělovače do vlastních buněk.
PROČISTIT / TRIM
Funkce PROČISTIT nepostradatelná funkce pro čištění textu od nadbytečných mezer, které v datech způsobují chyby. Nadbytečná mezera se totiž chová jako znak, takže vám kvůli nim nefungují součty, počty nebo vyhledávání. Nenápadná funkce s obrovským dopadem na kvalitu dat. Pokud vám někde zůstanou nadbytečné mezery, tak můžete funkci zabalit do funkce PROČISTIT a tato funkce odstraní všechny nadbytečné mezery.
KDYŽ / IF
Funkce KDYŽ umožňuje pracovat s podmínkami. Díky ní Excel reaguje – vrátí jinou hodnotu, text nebo výpočet podle podmínky, kterou specifikujete. Je základem pro automatizaci a logiku v tabulkách. Funkce KDYŽ je jednou z nejdůležitějších funkcí, kterou se v Excelu můžete naučit. V příkladu chceme označit řádky podle tržeb. pokud je tržba vyšší než 3 000 Kč, tak je v pořádku a na řádku chceme OK, pokud je nižší, tak chceme tržbu zkontrolovat. Takže funkce KDYŽ, kde podmínka je, že tržba je vyšší než 3 000 Kč. Pokud je podmínka splněná, tak chceme vrátit slovo “OK” a pokud podmínka splněná není, tak chceme vrátit slovo “Zkontrolovat”.
SOUČIN.SKALÁRNÍ / SUMPRODUCT
Funkce SOUČIN.SKALÁRNÍ zvládne vícekriteriální výpočty i bez pomocných sloupců. Umí sčítat, násobit i filtrovat data v jednom jediném vzorci. Tajná zbraň pro pokročilé Excel uživatele, zejména tam kde selhávají funkce jako SUMIFS nebo COUNTIFS. V příkladu máme sečíst tržby z tabulky podle měsíce a regionu. Důvod, proč tady nebude fungovat funkce SUMIFS bez pomocných sloupců je to, že ve funkci SUMIFS nemůžeme použít funkci pro převod datumů na měsíce.Takže použijeme funkci SOUČIN.SKALÁRNÍ. Kde v prvním poli ověříme první podmínku, zde převedeme datumy na slovní vyjádření měsíce. Takže funkce HONDOTA.NA.TEXT neboli funkce TEXT, kde označíme datum a jako formát “mmmm”. A tato funkce se musí rovnat vybranému měsíci. Tato část funkce vrátí pravdy a nepravdy, takže je převedeme na jedničky a nuly pomocí dvojitého negativu (–).
Ve druhém poli ověříme druhou podmínku, a to, že se region rovná vybranému regionu a opět převedeme pravdy a nepravdy na jedničky a nuly pomocí dvojitého negativu.
A jako třetí pole označíme tržby.
Tato funkce vrátí součet tržeb pro vybrané podmínky.
