Určitě to znáte taky, člověk se podívá kolik za rok vydělal, pak na momentální stav účtu a napadne ho, proboha kam se ty peníze poděly. V dnešní lekci proto zkusíme použít Tabulky k tomu, abychom si udělali trochu přehled za co a kolik ročně utrácíme.
Nebude to nic světoborného, ale třeba nám při našem nerovném boji s výdaji alespoň trochu pomůže. Zároveň se přitom trochu seznámíme s uživatelskými funkcemi.
Tabulky obsahují stovky vestavěných funkcí, od nejjednodušších pro součty a průměry až po velmi složité. Podrobný seznam najdete zde .
Jsou ale situace, kdy vám žádná z funkcí nevyhovuje. Pro podobné případy Tabulky umožňují napsat si s využitím Apps Scriptu vlastní funkci a tu pak v tabulce libovolně používat.
V této lekci si napíšeme funkci, která nám bude počítat naše výdaje.
Příprava tabulky
Nejprve si připravíme tabulku kam si budeme zapisovat naše očekávané výdaje.
Já už jí mám samozřejmě připravenou, takže si ji můžete otevřít.
Je sdílená jen pro čtení, takže údaje v ní nemůžete měnit a nedostanete se ani do editoru skriptů.
Přes Soubor — Uložit kopii si můžete uložit kopii na svůj Disk, v kopii už budete mít do editoru skriptů přístup a budete si moci zkoušet vlastní úpravy.
Na první list Plán výdajů si budeme zapisovat výdaje, na list Parametry si zadáme kategorie výdajů a frekvenci jejich opakování.
Kategorie
Na listu Parametry si do sloupce A zapište jednotlivé kategorie, do kterých chcete výdaje řadit. Kategorie si vytvořte libovolně, ale je dobré, když je budete dělit podle toho jestli výdaje v ní zařazené jsou nezbytné a nedají se měnit.
Splácet hypotéku prostě musíte, dojíždění do zaměstnání se taky nevyhnete, zatímco pařba s kamarády až tak nezbytný výdaj není.
Teď si oblast se seznamem kategorií pojmenujeme, abychom na ni mohli odkazovat jejím názvem.
Obecně si můžete v tabulkách jakoukoliv část tabulky pojmenovat a pak její název používat ve vzorcích. Místo vzorce SUM(A2:A100) pak můžete napsat vzorec třeba SUM(vydaje) pokud jste si oblast A2:A100 pojmenovali vydaje.
Musíte si samozřejmě pohlídat, aby vám data nepřesáhla pojmenovanou oblast. Pokud budete výdaje psát i do řádků 101, 102 atd. a oblast vydaje bude končit na řádku 100, pak vám vzorec SUM(vydaje) bude sčítat špatně, stejně jako vzorec SUM(A2:A100).
V menu zvolte Data — Pojmenované rozsahy, vpravo se vám objeví panel pro práci s pojmenovanými rozsahy. Klikněte na přidat rozsah, panel pak bude vypadat takto.
Název rozsahu PojmenovanýRozsah1 přepište na kategorie. Dál je potřeba určit odkud kam rozsah kategorie sahá. Můžete kliknout na ikonu tabulky a oblast poté vybrat myší v tabulce, nebo text v políčku ručně přepsat.
Do políčka se Vám automaticky doplní rozsah, který jste měli v tabulce vybraný. Na obrázku vidíte, že v mém případě jsem měl vybranou buňku A9. Vyberte nebo přepište rozsah na Parametry!A2:A15. Vidíte, že samotný nadpis na řádku 1 do rozsahu nezahrnujeme, ten slouží pouze pro vaši orientaci.
Pak už jen kliknete na OK a rozsah kategorie je pojmenován.
Oblast si klidně nadefinujte delší než kolik kategorií jste momentálně dali dohromady. Pokud v budoucnu doplníte další kategorie nezačnou Vám přepadávat mimo rozsah.
Dobrou pomůckou může být i to, že celému rozsahu nastavíte podbarvení. Budete tak mít vizuální kontrolu, že nově přidaná kategorie je ještě uvnitř definovaného rozsahu.
Úplně stejným způsobem si nadefinujeme ještě další 2 rozsahy, jeden nazvěte frekvence a oblast nadefinujte na Parametry!C2:D15.
Jako poslední si nadefinujte rozsah frekvence_nazvy a oblast bude Parametry!C2:C15.
Vidíte, že oba rozsahy jsou podobné, jen jednou zahrnuje jeden sloupec a podruhé 2 sloupce.
Teď si rozsah frekvence vyplňte. Ve sloupci Počet za rok je číslo, kterým se bude zadaný výdaj násobit. Pokud tedy do tabulky zadáte výdaj, který se opakuje pravidelně každý týden, tak do pravého sloupce uvedete 52 tedy počet týdnů v roce. Obdobně pro denní výdaj zadejte 365.
Do sloupce Frekvence slovy si pak zadejte vhodné slovní vyjádření, týdně, kvartálně apod. Označení je čistě na Vás, výpočet bude fungovat ať už položku nazvete jakkoliv. Pokud máte výdaje, které se opakují po dvou týdnech, klidně si doplňte řádek s počtem za rok 26 a vhodným názvem.
Ověření dat
Teď si ukážeme jak výdaje do tabulky zadávat, resp. jak donutit uživatele, aby do tabulky nemohl zadat co ho napadne.
Na listu Plán výdajů vyberte část sloupce A od buňky A2 řekněme do řádku 50. Pak z menu zvolte Data — Ověřování dat. Druhý způsob je kliknout na výběr pravým tlačítkem a vybrat volbu Ověření dat. Panel, který se otevře, vypadá takto.
Rozsah buněk už máme vybraný, protože jsme ho ručně vybrali před zobrazením panelu. Jinak bychom ho zde samozřejmě mohli vybrat přes ikonu tabulky, nebo ručně zadat.
Kritéria nastavíme tak jak vidíte na obrázku, tedy Seznam z rozsahu a vedle vypíšeme název pojmenovaného rozsahu kategorie, který jsme si předem připravili.
Volbu Zobrazit rozbalovací seznam v buňce ponechte zaškrtnutou.
Volbu U neplatných dat si nastavte podle svého, první volba jen varuje pokud do buňky zadáte hodnotu mimo definovaný seznam, druhá volba zadání úplně znemožní.
Tlačítkem Uložit panel zavřeme a zkusíme si do sloupce A zadat nějakou kategorii.
Pro rozbalení seznamu kategorií můžete myší kliknout na trojúhelníček v buňce, nebo jednodušeji stisknout mezerník. Šipkou pak vyberete kategorii a enterem vložíte do buňky.
Pokud stisknete místo mezerníku například klávesu d, ukáže vám tabulka všechny kategorie začínající na d , v našem případě to bude jen položka Dovolená.
Úplně stejně si nadefinujte zadávání do sloupce Frekvence, rozsah pro seznam bude frekvence_nazvy. Volbu U neplatných dat si tentokrát nastavte na Odmítnout zadání.
Je to z toho důvodu, že pokud bychom do sloupce zadali frekvenci, která by nebyla v seznamu, funkce pro výpočet by nedokázala spočítat výsledek.
Tak máme všechno připraveno a můžeme se konečně pustit do naší funkce.
Uživatelská funkce
Ve své kopii tabulky si zvolte Nástroje — Editor skriptu. Jak vidíte funkce pro výpočet je celkem jednoduchá.
function soucet_rok(castka, jak_casto){
if(jak_casto == '' || castka == '' || castka == 0) return 0;
var sheet = SpreadsheetApp.getActive();
var moznosti = sheet.getRangeByName('frekvence').getValues();
for(var i = 0; i < moznosti.length;i++){
if(moznosti[i][0] == jak_casto){
return moznosti[i][1] * castka;
}
}
return 'Nenalezeno: ' + jak_casto
}
Funkce dostane dva vstupní parametry, castka a jak_casto, tedy slovy vyjádřeno jak často se bude castka platit.
Parametr jak_casto tedy bude text v takovém tvaru jak jsme si nadefinovali v našem seznamu, tedy některé ze slov jednorázově, týdně, měsíčně atd.
Podle tohoto slova musíme najít číslo uvedené ve vedlejším sloupci, tedy k týdně potřebujeme získat číslo 26, k měsíčně 12 atd.
K tomu se nám bude hodit náš pojmenovaný seznam frekvence, který obsahuje jednak názvy a v druhém sloupci i hodnoty, kterými je třeba částku násobit.
Nejprve si ve funkci ošetříme situaci, kdy funkci zavoláme, ale buňky ze kterých si bude brát částku a frekvenci placení ještě nebudou vyplněné. To ošetříme hned na prvním řádku a pokud budou parametry prázdné nebo částka nulová vrátí funkce hodnotu nula.
Na dalším řádku získáme aktivní tabulku, tedy tu, ke které je náš skript připojen. Další řádek načte data z pojmenovaného rozsahu frekvence.
Pokud jste sledovali minulé lekce, pak víte, že tyto dva řádky lze spojit do jednoho takto.
var moznosti = SpreadsheetApp.getActive().getRangeByName('frekvence').getValues();
V proměnné moznosti pak budeme mít pole, kde každý z prvků je opět pole o dvou prvcích, první prvek pole moznosti je tedy v našem případě pole, které vypadá takto [“jednorázově”, 1] druhý prvek je [“týdně”, 52] atd.
Nyní už zbývá jen procházet pole moznosti dokud nenarazíme na shodu prvního prvku s hodnotou jak_casto. Jakmile najdeme shodu, funkce vynásobí hodnotu castka s druhým prvkem pole a získanou hodnotu vrátí.
Pokud by nastala situace, že se hodnota jak_casto v seznamu nenajde, vrátí funkce text, který o tom bude informovat. No a to je vše, můžeme se vrátit do tabulky a zkusit funkci použít.
Použití uživatelské funkce
Na listu Plán výdajů si do buňky E2 zadejte vzorec
=soucet_rok(D2,C2)
Pokud budou buňky C2 a D2 prázdné, měla by se v buňce E2 objevit nula. Pokud zadáte částku a ve sloupci C vyberete frekvenci plateb, měly by se v buňce E2 objevit vypočtené roční náklady na tuto položku.
Pokud vše funguje, pak stačí vzorec z buňky E2 rozkopírovat na další řádky sloupce E.
No a pak už jen svědomitě vyplnit jednotlivé plánované náklady a pokud budou přesahovat příjmy, tak popřemýšlet jestli si letos skutečně tak nutně potřebujete koupit novou Teslu, nebo jestli ještě rok nebo dva nevydržíte s vaším Superbem :-)
Pokud Vás tento článek zaujal a máte jakékoliv dotazy, obraťte se na nás na ahoj@appsatori.eu