7 funkcí Excelu, které vám pomohou spravovat vaše finance

Chcete-li zobrazit podrobnější informace o objektu, klikněte na jeho název v prvním sloupci.

Poznámka: Značka verze označuje verzi aplikace Excel, ve které se poprvé objevila. Starší verze tuto funkci nemají. Například značka verze 2013 znamená, že funkce je dostupná v Excelu 2013 a všech následujících verzích.

Vrátí akumulovaný úrok z cenných papírů s pravidelnými platbami úroků.

Vrátí akumulované úroky z cenných papírů, za které se platí úroky při splatnosti.

Vrátí částku odpisu pro každé účetní období pomocí odpisové sazby.

Vrátí částku odpisu pro každé účetní období.

Vrátí počet dní od začátku platnosti kuponu do data dohody.

Vrátí počet dní v období kupónu, které obsahuje datum vypořádání.

Vrátí počet dní od data vypořádání do data splatnosti dalšího kupónu.

Vrátí sériové číslo dalšího data kupónu po datu dohody.

Vrátí počet kuponů mezi datem smlouvy a datem účinnosti.

Vrátí sériové číslo data předchozího kuponu před datem dohody.

Vrátí kumulativní (kumulativní celkovou) částku úroků zaplacených z půjčky mezi dvěma platebními obdobími.

Vrátí kumulativní (naběhlou celkovou) částku zaplacenou na splacení jistiny půjčky mezi dvěma obdobími.

Vrátí hodnotu odpisu aktiva za dané období vypočtenou pomocí metody pevných odpisů.

Vrátí odpisovou sazbu aktiva za dané období pomocí metody dvojitého degresivního zůstatku nebo jiné explicitně určené metody.

Vrátí diskontní sazbu cenného papíru.

Převede cenu v rublu vyjádřenou zlomkem na cenu v rublu vyjádřenou jako desetinné číslo.

Převede cenu v rublu vyjádřenou jako desetinné číslo na cenu v rublu vyjádřenou jako zlomek.

Vrátí Macaulayovu dobu trvání pro cenné papíry, které platí pravidelný úrok.

Vrátí skutečnou (efektivní) roční úrokovou sazbu.

Vrátí budoucí hodnotu investice.

Vrátí budoucí hodnotu původní jistiny po použití řady složených úrokových sazeb.

Vrátí úrokovou sazbu za plně investované cenné papíry.

Vrátí úrok z vkladu za dané období.

Vrátí vnitřní míru návratnosti pro řadu peněžních toků.

Vypočítá výplatu za zadané investiční období.

Vrátí upravenou Macaulayovu dobu trvání pro cenné papíry s předpokládanou nominální hodnotou 100 rublů.

Vrátí vnitřní míru návratnosti, při které mají kladné a záporné peněžní toky různé hodnoty míry.

Vrátí nominální roční úrokovou sazbu.

Vrátí celkový počet platebních období pro investici.

Vrátí čistou současnou hodnotu investice na základě řady pravidelných peněžních toků a diskontní sazby.

Vrátí cenu za 100 rublů nominální hodnoty cenných papírů s nepravidelným (krátkým nebo dlouhým) obdobím prvního kupónu.

Vrátí výnos z cenných papírů s nepravidelným (krátkým nebo dlouhým) obdobím prvního kupónu.

Vrátí cenu za 100 rublů nominální hodnoty cenných papírů s nepravidelným (krátkým nebo dlouhým) posledním obdobím kupónu.

Vrátí výnos z cenných papírů s nepravidelným (krátkým nebo dlouhým) posledním kupónovým obdobím.

Vrátí počet období potřebných k tomu, aby investice dosáhla dané hodnoty.

Vrátí pravidelnou platbu roční anuity.

Vrátí platbu z jistiny investovaného kapitálu za dané období.

Vrátí cenu za 100 rublů nominální hodnoty cenných papírů, ze kterých se pravidelně úročí.

Vrátí cenu za 100 rublů nominální hodnoty cenných papírů, u kterých byla provedena sleva.

Vrátí cenu za 100 rublů nominální hodnoty cenných papírů, za které se při splatnosti platí úrok.

Vrátí současnou hodnotu investice.

Vrátí úrokovou sazbu anuity za jedno období.

Vrátí částku obdrženou při splatnosti plně investovaných cenných papírů.

Vrátí ekvivalentní úrokovou sazbu pro růst investice.

Vrátí hodnotu odpisu aktiva za jedno období vypočtenou pomocí lineární metody.

Vrátí hodnotu odpisu aktiva za dané období vypočítanou pomocí metody součtu ročních čísel.

Vrátí výnos ekvivalentní dluhopisu ze státní pokladniční poukázky.

Vrátí cenu za nominální hodnotu 100 rublů pokladniční poukázky.

Vrátí výnos ze státní pokladniční poukázky.

Vrátí odpisovou sazbu aktiva za zadané nebo částečné období při použití metody degresivního zůstatku.

Vrátí vnitřní míru návratnosti pro plán peněžních toků, který nemusí být nutně pravidelný.

Vrátí čistou současnou hodnotu pro peněžní toky, které nejsou nutně periodické povahy.

Vrátí příjem z cenných papírů, ze kterých se pravidelně platí úroky.

Vrátí roční výnos diskontovaného cenného papíru (například pokladniční poukázky).

Vrátí roční výnos cenných papírů, které platí úrok při splatnosti.

Je důležité, aby se: Vypočítané výsledky vzorců a některých funkcí listu aplikace Excel se mohou mezi počítači se systémem Windows x86 nebo x86-64 a počítači se systémem Windows RT ARM mírně lišit. Zjistěte více o těchto rozdílech.

7 Excelové funkce pro ekonomy: jednoduché příklady pro složité úkoly

Výpočetní funkce jsou jádrem Excelu a to, co v podstatě odlišuje program od běžné kalkulačky. V programu je poměrně hodně funkcí – více než 400. Některé z nich jsou duplicitní, ale přesto jsou možnosti pro ekonomické výpočty v programu obrovské.
Pomocí vzorců můžete přesně vypočítat náklady na zboží, připravit analytické zprávy a plány. Vyřešit jak jednoduché ekonomické účetní problémy, tak sestavit kompletní rozvahu podniku. Už jsme udělali výběr excelových nástrojů pro účetní. Je na čase podívat se na hlavní funkce Excelu pro ekonoma, které mu zjednoduší práci s daty a velkými tabulkami.

Co jsou funkce Excelu a kde se nacházejí?

Funkce v Excelu jsou příkazy, které pomáhají během několika minut opravit data v buňkách, vypočítat hodnoty podle určitých kritérií a odstranit potřebné informace z obrovského pole. Funkce se často používají ve vzorcích, aby je zjednodušily a učinily je flexibilnějšími.

Jak jsme již řekli, program má mnoho funkcí – asi 10 kategorií: jsou matematické, logické, textové. A speciální funkce – finanční, statistické atd. Všechny funkce jsou v záložce „Vzorce“. Jakmile jste v něm, musíte kliknout na tlačítko „Vložit funkci“ na panelu nástrojů, po kterém se spustí „Průvodce funkcí“.

Stačí si v „Kategoriích“ vybrat požadovanou funkci pro výpočty a v rozbalovacím poli vyplnit údaje o struktuře.

Pokud víte, kterou funkci potřebujete, jednoduše ji zadejte do řádku vzorců, počínaje znakem “ async“ src=“https://finacademy.net/images/articles/621/02.jpg“ alt=“Tipy pro Excel“>

Není třeba si pamatovat, co která funkce dělá. Excel má praktické tipy, proč je ten či onen vzorec potřeba a jak jej používat.

Níže se podíváme na základní a často používané vzorce v Excelu pro ekonomy: IF, SUMIF, VLOOKUP, SUMPRODUCT, COUNT, AVERAGE a MAX/MIN.

Funkce IF pro porovnávání dat

Nejoblíbenější logická funkce, která je užitečná pro ekonomy a účetní. S jeho pomocí můžete porovnávat číselné a textové hodnoty podle určitých kritérií. Například vypočítat odpisy, slevy, přirážky nebo odstranit chyby z výpočtů. Někdy se ve statistických, textových a matematických vzorcích používá vestavěná funkce IF, která jim dává možnost „rozhodovat se“.

Funkce IF vám pomůže přesně porovnávat hodnoty a získat výsledek v závislosti na tom, zda je srovnání pravdivé nebo ne.

Takto vypadá vzorec:
=IF(logický_výraz,[hodnota_pokud_pravda],[hodnota_pokud_nepravda])

• Logický výraz je něco, co je třeba zkontrolovat nebo porovnat (číselná nebo textová data v buňkách)
• Value_if_true je to, co se objeví v buňce, pokud je porovnání pravdivé.
• Value_if_false je to, co se v buňce objeví, pokud je srovnání nesprávné.

Například obchod prodává doplňky pro muže a ženy. Tento měsíc je sleva 20% na všechny dámské produkty. Propagační předměty můžete třídit pomocí funkce KDYŽ pro textové hodnoty.

Zapišme vzorec do sloupce „Sleva“ takto:
=IF(B2=“žena“;20%;0)
A platí to pro všechny řádky. V buňkách, kde platí rovnost, uvidíme produkty se slevou.

Takto se používá funkce IF pro textové hodnoty s jednou podmínkou

Funkce SUMIFS a SUMIFS

Další užitečnou funkcí je SUMIF, která umožňuje sečíst několik číselných údajů podle určitého kritéria. Vzorec se skládá ze 2 částí:

• SUM je matematická funkce pro sčítání číselných hodnot. Zapisuje se jako =SUM(buňka/rozsah 1; buňka/rozsah 2; . ).
• a funkce IF diskutovaná výše.

Například musíte vypočítat celkovou částku naběhlých mezd manažerům a sestava obsahuje údaje pro všechny zaměstnance podniku.

Vzorec musí obsahovat následující argumenty:

• Vyberte rozsah všech zaměstnaneckých pozic – v našem případě B2:B10.
• Kritérium výběru zapíšeme oddělené středníkem – „manažer“.
• Rozsahem součtu jsou mzdy. Označujeme C2:C10.

A jedním kliknutím získáme celkovou výši platů manažerů:

Pomocí SUMIF můžete sečíst buňky, které splňují určité kritérium

Důležité! Funkce SUMIF je citlivá na správnost a přesnost kritérií. Sebemenší překlep může vést ke špatnému výsledku. To platí i pro názvy buněk. Vzorec vygeneruje chybu, pokud napíšete rozsah buněk v azbuce, nikoli v latince.

Složitější verzí tohoto vzorce je funkce SUMIFS. V podstatě se jedná o selektivní sčítání dat vybraných podle několika kritérií. Na rozdíl od SUMIF lze použít až 127 kritérií pro výběr dat. Pomocí tohoto vzorce lze například snadno vypočítat celkový zisk z dodávek různého zboží do několika zemí najednou.

Funkce SUMIFS může pracovat se zástupnými znaky a používat operátory pro výpočty, jako je „větší než“, „menší než“ a „rovno“. Pro pohodlí práce s funkcí je lepší používat v Excelu absolutní odkazy – při kopírování se nemění a umožňují automaticky přepočítat vzorec, pokud se data v buňce změnila.

Funkce VLOOKUP a GLOOKUP – vyhledávání dat ve velkém rozsahu

Ekonomové se často musí prokousávat obrovskými tabulkami, aby získali data, která potřebují pro analýzu. Nebo spojit dva stoly do jednoho, což také není nic neobvyklého. Funkce VLOOKUP nebo, jak se také nazývá, vertikální vyhledávání (anglická verze VLOOKUP) umožňuje rychle najít a extrahovat potřebná data ve sloupcích. Nebo přeneste data z jedné tabulky do odpovídajících buněk jiné.

Syntaxe nejjednodušší funkce VLOOKUP vypadá takto:
= SVYHLEDAT(hledaná_hodnota; tabulka; číslo_sloupce; [intervalové zobrazení]).

Například potřebujete rychle získat název produktu podle čísla v seznamu. Pomocí funkce VLOOKUP je to velmi snadné:

Funkce VLOOKUP umožňuje rychle najít potřebná data a přenést je do vybrané buňky.

V buňce C1 jsme uvedli číslo produktu. Poté jsme vybrali rozsah buněk, kde to máme hledat (A1:B10) a napsali sloupec číslo „2“, do kterého potřebujeme data vzít. Stiskli jsme Enter a obdrželi požadovaný produkt do vybrané buňky.

Existuje méně oblíbená funkce GLOOK. Funguje stejně jako VLOOKUP, ale hledá data v horizontálních seznamech.

Důležité! Chcete-li rozšířit rozsah této funkce, můžete zadat data pomocí zástupných znaků.

Jedním z cílů této funkce je zjednodušit přípravu zdrojových dat pro analytické a kontingenční tabulky. Jak si takovou tabulku připravit zdarma se dočtete v knize Kontingenční tabulky v Excelu.

Funkce SUMPRODUCT v Excelu

Čtvrtou funkcí na našem seznamu je SUMPRODUCT neboli součet produktů. Pomůže vám rychle zvládnout jakýkoli ekonomický úkol, kde jsou pole. Zahrnuje možnosti předchozích vzorců IF, SUMISF a SUMIFS a také umožňuje provádět výpočty ve 255 polích. Účetní ji milují a často ji používají k výpočtu mezd a dalších výdajů.

Co může být v tomto případě práce? Jak zadané rozsahy, tak celá datová pole. Chcete-li nastavit počet polí, je třeba je uvést oddělené středníky, poté vynásobit a poté sečíst. Zároveň je zde jedna podmínka – pole musí být stejného typu a délky.

Máte například měsíční report o tržbách v obchodě, ze kterého potřebujete zjistit, kolik se prodalo triček velikosti „M“.

K tomu použijte funkci SUMPRODUCT a zadejte 2 podmínky. Každý z nich vložíme do závorek a mezi ně vložíme „hvězdičku“, která se v Excelu čte jako spojka „a“.

Zapišme příkaz takto: =SUMPRODUCT((A5:A11=A13)*(B5:B11=B13)*C5:C11), kde

• první podmínka A5_A11=A13—hledaný rozsah a název požadovaného produktu
• druhá podmínka B5_B11=B13 – rozsah a velikost hledání
• C5:C11 – pole, ze kterého se bere součet

Pomocí funkce SUMPRODUCT jsme během pár minut zjistili, že obchod prodal za měsíc trička velikosti M v hodnotě 100 dolarů.

Chcete-li zjistit prodeje pro jiný produkt a velikost, stačí nahradit data v buňkách A13 a B13.

Tento příklad je příliš jednoduchý ve srovnání s objemy dat, se kterými musí ekonomové pracovat. Ale s vědomím toho, co tento vzorec dokáže, tabulky s miliony řádků a stovkami sloupců už nejsou tak děsivé a potřebná data jsou nalezena během několika minut.

Jak používat MAX, VLOOKUP a MATCH k řešení problémů

Funkce MIN a MAX vám pomohou najít nejmenší nebo největší hodnotu dat. Funkce MATCH vám pomůže najít číslo zadaného prvku ve zvoleném rozsahu. A vzorec VLOOKUP, připomeňme, umožňuje extrahovat potřebná data ze sloupců do zadaných buněk.

Podívejme se na kombinaci těchto funkcí na příkladu řešení problému v Excelu pro ekonomy. Vytvořme kombinovaný vzorec pro nalezení společnosti s největší částkou dluhu.

Nejprve si udělejme seznam dlužníků, uvedeme výši jejich dluhu a nastavíme datum ukončení úvěrové smlouvy.

Chcete-li problém vyřešit, můžete funkce použít postupně:

• Funkce MAX (=MAX(B2:B10)) vám pomůže najít největší dluh, kde B2:B10 je sloupec s údaji o dluhu.

• Pro vyhledání čísla dlužnické společnosti v seznamu je potřeba přidat do tabulky sloupec číslování. Protože funkce MATCH hledá data pouze v levém sloupci zvoleného rozsahu.

Funkci skládáme pomocí vzorce:
MATCH(hledaná_hodnota; vyhledávací_pole; [typ_shody])

V našem případě to bude =MATCH(14569;C2:C10;0), kde to, co hledáme, je maximální výše dluhu. Typ mapování bude „0“, protože jsme na sloupec dluhu nepoužili řazení.

• Ke zjištění názvu dlužné společnosti použijeme známou funkci VLOOKUP.

Bude to vypadat takto = SVYHLEDAT(D14;A2:B10;2), kde D4 je požadovaný, A2:B10 je tabulka nebo vybraný rozsah s názvy společností a číslováním a „2“ je číslo sloupce s dlužníky.

Stejného výsledku lze získat shromážděním jednoho vzorce ze 3:

=VLOOKUP(MATCH(MAX(C2:C10); C2:C10;0); A2:B10;2).

V ekonomických výpočtech vám funkce VLOOKUP pomůže rychle extrahovat požadovanou hodnotu z obrovského rozsahu dat. Hodnotu lze navíc zjistit pomocí různých výběrových kritérií. Například cenu produktu lze získat podle identifikátoru, sazbu daně podle úrovně příjmu atd.
Kromě výše uvedených funkcí ekonomové často používají pro výpočet průměrné mzdy například vzorec PRŮMĚR. Funkce POČET, kdy potřebujete spočítat počet zásilek podle zákazníka nebo cenu zboží za určité období. Mimochodem, na příkladu zásilek pomůže vzorec MIN/MAX sledovat rozsah, ve kterém se změnila cena produktu.

V Excelu je sto dalších matematických a finančních funkcí, které lze použít k řešení ekonomických problémů různé složitosti. Například zpracovávat objednávky, plánovat výrobní kapacitu, počítat daně, vést personální záznamy a řídit prodej. Ale k vyřešení takových problémů už nebudou stačit rady v Excelu. Zde se musíte ponořit hlouběji do programu a naučit se pracovat s jeho různými nástroji: od funkcí a kontingenčních tabulek až po grafy a makra.

Barevné grafy ukazují, že vaše data fungují lépe než mřížka Excelu! Osvojte si program Power BI a po kurzu „ACPM: Analýza obchodních dat ve financích“ vytvořte vizuální sestavy několika kliknutími!