Jak na složitější operace a funkce v Excelu

Excel – tabulkový procesor a součást balíku M365 (dříve MS Office) – je jedním z nejvýkonnějších a současně nejdostupnějších nástrojů pro zpracování a analýzu dat. Jeho základní funkce jsou dobře známé (viz naše články Úvod do Excelu a Excel pro pokročilé), ale nabízí také řadu pokročilých funkcionalit, které mohou výrazně pomoct zvýšení efektivity vaší práce s daty. Na následujících řádcích se tedy zaměříme na pokročilé funkce v Excelu, podíváme se na iterace a automatizaci a zmíníme i aktuální možnosti využití umělé inteligence.

Excel a pokročilé funkce pro analýzu dat

Excel poskytuje mnoho pokročilých funkcí, které umožňují uživatelům efektivně analyzovat a manipulovat s daty. Následující okruhy funkcí patří k těm nejpoužívanějším a nejúčinnějším:

SUMIF a COUNTIF

Funkce SUMIF a COUNTIF umožňují sčítání nebo počítání buněk, které splňují určité – vámi zvolené – kritérium. Jejich použití není nijak komplikované:

• SUMIF – sčítá hodnoty v rozsahu, které splňují zadané kritérium.
  • Příklad: =SUMIF(A1:A10; ">5", B1:B10) sčítá hodnoty v rozsahu B1–B10, kde odpovídající hodnota v rozsahu A1–A10 je větší než 5.
• COUNTIF – počítá počet buněk v rozsahu, které splňují zadané kritérium.
  • Příklad: =COUNTIF(A1:A10; "Jablko") počítá počet buněk v rozsahu A1–A10, které obsahují text "Jablko".

® Rada pro Excel: Nepoužívejte české uvozovky („“), ale anglické ("").

Tyto relativně jednoduché funkce využijete pro rychlou analýzu dat a identifikaci vzorců v rozsáhlejších datových souborech.

INDEX a MATCH

Funkce INDEX a MATCH jsou velmi mocné nástroje, které dokáží nahradit funkci VLOOKUP (funkce pro prohledávání napříč sloupci) – nabízejí totiž větší flexibilitu.

• INDEX – vrací hodnotu z konkrétní buňky v určeném rozsahu.
  • Příklad: =INDEX(B1:B10; 3) vrací hodnotu z třetí buňky v rozsahu B1–B10.
• MATCH – vyhledá specifickou hodnotu v rozsahu a vrátí relativní pozici této hodnoty.
  • Příklad: =MATCH("Jablko"; A1:A10; 0) vrátí pozici buňky, která obsahuje text "Jablko" v rozsahu A1–A10.

Kombinace těchto funkcí vám umožní provádět detailní vyhledávání a slouží jako odrazový můstek pro pokročilejší analýzu dat:

• Příklad: =INDEX(B1:B10, MATCH("Jablko"; A1:A10, 0)) vrátí hodnotu z rozsahu ve sloupci B, která odpovídá pozici, kde se v rozsahu sloupce A nachází "Jablko".

Funkce Array (Pole)

Funkce pole umožňují provádět výpočty na více hodnotách současně a vracet více výsledků. Některé z nejčastěji používaných funkcí pole zahrnují:

• TRANSPOSE: Převádí řádky na sloupce a naopak.
  • Příklad: =TRANSPOSE(A1:A3) převede hodnoty v buňkách A1 až A3 do řádků v nové oblasti.
• FREQUENCY: Vypočítá, kolikrát se hodnoty vyskytují v daném rozsahu.
  • Příklad: =FREQUENCY(B1:B10; D1:D5) vrátí pole s počty výskytů hodnot z B1 v intervalech určených D1.
• MMULT: Vynásobí dvě matice.
  • Příklad: =MMULT(A1:B2, C1:D2) vrátí matici, která je výsledkem násobení matic v rozsazích A1 a C1.

Iterace a pokročilé funkce pro finanční výpočty

Iterativní výpočty v Excelu umožňují řešit problémy, kde se hodnoty mění opakovaně, dokud se nedosáhne splnění určité podmínky nebo přesnosti. Zní to možná nesrozumitelně, ale iterativní přístup využijete kromě řešení matematických a inženýrských problémů také pro finanční výpočty a modely.

Iterativní výpočty v Excelu můžete povolit následujícím způsobem:

• Klikněte na „Soubor“ > „Možnosti“ > „Vzorce“.
• Zaškrtněte možnost „Povolit iterativní výpočty“.

Nastavení iterativních výpočtů umožňuje Excelu opakovaně přepočítávat vzorce do okamžiku, kdy se dosáhne daného počtu iterací nebo určité úrovně přesnosti.

Příklad: výpočet vnitřní míry návratnosti (IRR)

Vnitřní míra návratnosti (Internal Rate of Return, IRR) je finanční ukazatel používaný k hodnocení ziskovosti investice. V Excelu lze IRR vypočítat pomocí iterativních výpočtů.

1. Nastavení listu:
   • Do buněk A1 až A6 zadejte následující hodnoty, které představují cash flow investice:
     • A1: -10000 (počáteční investice)
     • A2: 2000
     • A3: 3000
     • A4: 4000
     • A5: 5000
     • A6: 6000
2. Nastavení výchozí hodnoty IRR:
   • Do buňky B1 zadejte výchozí odhad IRR, například 0.1 (10%).
3. Výpočet NPV (čisté současné hodnoty):
   • Do buňky C1 zadejte vzorec pro výpočet NPV na základě odhadu IRR:

=A1 + A2/(1+B1) + A3/(1+B1)^2 + A4/(1+B1)^3 + A5/(1+B1)^4 + A6/(1+B1)^5

4. Iterativní výpočet IRR:
5. Do buňky B2 zadejte vzorec:
   =B1 - C1/(A2/(1+B1)^2 + A3/(1+B1)^3 + A4/(1+B1)^4 + A5/(1+B1)^5 + A6/(1+B1)^6)
6. Opakování iterací: Excel začne automaticky upravovat hodnotu v buňce B1 (IRR), dokud se hodnota v buňce C1 (NPV) nepřiblíží nule.

Tímto způsobem použije iterativní výpočet k nalezení hodnoty IRR, která snižuje NPV na nulu, což je definice vnitřní míry návratnosti.

Příklad: výpočet složeného úroku

Složený úrok je další oblastí, kde se často používají iterativní výpočty. Předpokládejme, že chceme vypočítat budoucí hodnotu investice s pravidelnými měsíčními příspěvky.

1. Nastavení listu:
   • Do buňky A1 zadejte počáteční hodnotu investice.
   • Do buňky A2 zadejte měsíční příspěvek.
   • Do buňky A3 zadejte roční úrokovou sazbu (např. 5%).
   • Do buňky A4 zadejte počet let investice.
2. Výpočet měsíční úrokové sazby:
   • Do buňky B1 zadejte vzorec =(1+A3)^(1/12)-1.
3. Výpočet počtu měsíců:
   • Do buňky B2 zadejte vzorec =A4*12.
4. Iterativní výpočet budoucí hodnoty:
   • Do buňky B3 zadejte vzorec =A1.
   • Do buňky B4 zadejte vzorec =B3*(1+B1)+A2.
5. Kopírování vzorce:
   • Přetáhněte vzorec z buňky B4 dolů po potřebný počet měsíců (B2).

Tento postup vám umožní vypočítat budoucí hodnotu investice s pravidelnými měsíčními příspěvky.

Zvýšení efektivity v odhadech trendů – prediktivní analýza

Využití pokročilých funkcí Excelu pro odhad budoucího vývoje využijete při plánování a rozhodování. Podíváme se na několik základních metod prediktivní analýzy, které můžete použít pro odhad hodnot a trendů.

Regresní analýza

Regresní analýza je statistická metoda, která umožňuje modelovat vztah mezi nezávislou proměnnou (předpovědní proměnnou) a závislou proměnnou (cílovou proměnnou).

Příklad:

Máme data o prodejích za posledních 12 měsíců a chceme předpovědět prodeje na další měsíc.

Data mohou být uspořádaná do dvou sloupců: měsíc (A1) a prodeje (B1).

V Excelu použijeme nástroj pro regresní analýzu:

• Klikněte na „Data“ > „Analýza dat“ > „Regrese“.
• V dialogovém okně vyberte vstupní rozsahy pro nezávislou a závislou proměnnou.
• Klikněte na „OK“ a Excel vypočítá regresní koeficienty a poskytne další statistiky.

Exponenciální vyrovnávání

Exponenciální vyrovnávání je metoda časové řady, která vyhlazuje data a umožňuje předpovědi založené na historických datech (datech za uplynulá období).

Příklad:

• Máme data o denních návštěvách webové stránky za posledních 30 dní (A1 pro datum, B1 pro návštěvy) a chceme předpovědět budoucí počet návštěv.
• Použijeme exponenciální vyrovnávání pro předpověď návštěv na další den.
• V Excelu použijeme funkci ETS: =FORECAST.ETS(B31; B1:B30, A1:A30, 1, 1), která předpoví hodnotu pro B31 (návštěvy na další den) na základě předchozích 30 dní s použitím exponenciálního vyrovnávání.

Analýza sezónnosti

Analýza sezónnosti vám pomůže identifikovat opakující se vzory v datech, což je užitečné pro maloobchodní prodeje nebo třeba spotřebu energie.

Příklad:

• Máme měsíční data o prodejích za poslední tři roky (A1 pro měsíce, B1 pro prodeje) a chceme identifikovat sezónní vzory a předpovědět prodeje na následující měsíce.
• Použijeme funkci ETS.SEASONALITY: =FORECAST.ETS.DESEASONALIZE(B1:B36; A1:A36)

Online data v Excelu – tipy a triky

Jednou z velkých výhod Excelu je schopnost importovat a automaticky aktualizovat data z různých online zdrojů. Díky tomu můžete mít vždy aktuální informace. Podíváme na tedy na to, jak připojit Excel k online zdrojům dat a jak nastavit jejich automatickou aktualizaci.

Import dat z webových stránek

• Klikněte na „Data“ > „Z webu“.
• Zadejte URL adresu webové stránky, ze které chcete data importovat.
• Vyberte tabulku nebo data, která chcete importovat, a klikněte na „Načíst“.

Příklad: pokud chcete importovat tabulku aktuálních měnových kurzů z webové stránky, zadejte URL adresu této stránky a vyberte příslušnou tabulku obsahující měnové kurzy.

Automatická aktualizace dat

• Po importu dat klikněte na „Data“ > „Připojení“.
• Vyberte připojení, které chcete automaticky aktualizovat, a klikněte na „Vlastnosti“.
• V dialogovém okně vyberte záložku „Použití“.
• Zaškrtněte „Aktualizovat data při otevření souboru“ a/nebo nastavte interval automatické aktualizace (např. každých 60 minut).

Příklad: pro tabulku měnových kurzů, kterou jste importovali z webové stránky, nastavte automatickou aktualizaci každých 60 minut, aby byla vždy aktuální.

Ověření správnosti dat

Při importu a automatické aktualizaci dat byste měli vždy ověřit, že jsou data správná a relevantní:

• Po importu dat zkontrolujte formát a strukturu dat.
• Použijte nástroje jako „Ověření dat“ a „Podmíněné formátování“ k identifikaci a označení chybných nebo neobvyklých hodnot.

Příklad: pro tabulku měnových kurzů můžete použít podmíněné formátování k označení kurzů, které se liší o více než 5 % od průměru.

Excel a AI

Microsoft nabízí svoji AI (umělou inteligenci) Copilot, která je zamýšlená jako univerzální asistent do M365 (potažmo do celých Windows). Copilot vám tedy může výrazně zefektivnit práci s daty, předpověďmi a analýzou. Taková je aspoň představa Microsoftu a v tuto chvíli spíše hudba budoucnosti. Jaká je realita?

Copilot můžete pouštět pouze nad datovou tabulkou a teoreticky umí získávat informace z dat, přidávat vzorce a vytvářet grafy. Tzn. měl by umět data seřadit či přidat jednoduché podmínečné formátování. Problém ale je, že je reálné použití Copilota poněkud kostrbatější a pomalejší než manuální zadání na pár kliknutí. Potíž je ale v tom, že v praxi vytvoří jeden konkrétní graf a vzorec mnohdy napíše s chybou i při jednoduchém zadání.

Jedná se tedy zatím spíše o experimentální funkci pro nadšence než o efektivní využití umělé inteligence, která by měla šetřit čas a výrazně usnadňovat práci. Věříme ale, že vylepšení Copilota do budoucna přinesou významné změny.

Závěrem

Excel je skutečně mocný nástroj, ale každý mistr potřebuje své nástroje dokonale ovládat. Čím více si osvojíte pokročilé funkce, tím více uvolníte svůj potenciál a proměníte Excel v nezbytného spojence ve vašem každodenním pracovním životě. Ať už jde o složené vzorce, iterace nebo využití AI, hlavní je trénink a neustálé zlepšování. Tak co na to říkáte – je čas posunout se o úroveň výš?