Zkuste si představit, že chcete vytvořit jednoduchý model. Třeba plán projektu nebo finanční model, kde se časová řada a celý model automaticky rozšiřuje nebo zkracuje podle počtu měsíců. A to bez kopírování sloupců, bez ručního přepisování dat a bez složitých funkcí KDYŽ, kde musíte ověřovat pravidla. Stačí vám změnit jedno číslo a celý model se přepočítá. Kouzlo? Ne, jen pár dynamických funkcí dostupných v Microsoft 365.
Excelový soubor ke stažení:
Dnešním úkolem je vytvořit jednoduchý model příjmů a nákladů domácnosti. V excelovém sešitu máme několik vstupů, které budou ovládat celý model. Máme zde počáteční datum, počet měsíců, celkové příjmy a výdaje domácnosti. Vedle máme ještě rozdělení výdajů dle procent.
Začneme tvorbou časové řady. Základem je funkce SEQUENCE, ve které vyplníme v parametru řádky jedničku, jelikož celá osa bude v jednom řádku. Následuje parametr sloupce, počet sloupců je určený počet měsíců v modelu, tedy buňkou B5. A chceme, aby časová řada začínala od nuly.
Funkci SEQUENCE zabalíme do funkce EDATE, která určí datumy. Ve funkci EDATE musíme určit počáteční datum, což je počáteční datum našeho modelu. A jako parametr měsíce poslouží funkce SEQUENCE. Funkce vrátí časovou řadu měsíců v dynamickém poli, jediné, co musíme na dynamické pole uplatnit je změna formátu z pořadového čísla na datum.
Na dynamickou časovou řadu navážeme teď celý model. Začneme příjmy. I pro tvorbu dynamické řady příjmů použijeme funkci SEQUENCE. V této funkci vyplníme opět jeden řádek, jako parametry sloupce vyplníme počet měsíců v modelu, počátek je částka příjmů a nechceme ji zvyšovat, takže velikost kroku je nula.
To samé uděláme s náklady. Ve funkci SEQUENCE vyplníme jeden řádek, počet sloupců je počet měsíců, počátek je hodnota nákladů a krok je nula.
Následuje rozpočítání nákladů podle procent. A zde už plně využijeme možností dynamických polí, kde označíme buňku B13 s křížkem. Tím se označí celé dynamické pole s výdaji a to vynásobíme procentuálním rozložením výdajů.
Následuje součet těchto nákladů. Jelikož chceme, aby byl model celý dynamický, tak pro součet použijeme funkci BYCOL. V této funkci se nejprve musí označit dynamické pole, což je buňka B15, a abychom se odkázali na dynamické pole, tak za buňkou musí následovat křížek. A jako výpočet chceme SUMA.
Úspory v jednotlivých měsících dostaneme jako příjmy mínus výdaje, s odkazem na dynamická pole.
Poslední výpočet je kumulativní součet úspor. Pro kumulativní součet musíme použít funkci SCAN. V této funkci nejprve začínáme od nuly. Následuje dynamické pole, které chceme sčítat, což jsou úspory. A jako výpočet musíme použít funkci LAMBDA. Ve funkci LAMBDA máme dvě proměnné, které můžeme nazvat třeba a, b. A tyto parametry chceme sečíst. Funkce SCAN doručí kumulativní součet dynamického pole.
Ještě vyřešíme podmíněné formátování záhlaví tabulky s časovou osou. Potřebujeme určit, zda jsou buňky prázdné. Použijeme funkci JE.PRÁZDNÉ, kde označíme buňky záhlaví a několik bunek navíc. Tato funkce vrátí pravdu, pokud jsou buňky prázdné a nepravdu, pokud v nich něco je.
Pro potřeby podmíněného formátu to ale potřebujeme přesně naopak, takže funkci zabalíme do funkce NE.
A tuto funkci vložíme do podmíněného formátu, kde formátujeme dle nového pravidla.
Jedna odpověď
👍👍😊