Dynamické (podmienené) filtrovanie

Jednou z ďalších frekventovane žiadaných funkcionalít v Exceli je tzv. dynamické filtrovanie alebo použitie hierarchie. 

V princípe sa jedná o možnosť mať napríklad prvý drop - down, v ktorom si vyberiete kraj (Košický), v ďalšom drop - downe si vyberiete z miest, dostupných vrámci Košického kraja (Košice, Michalovce atď). Inými slovami, vrámci hierarchie vyberáte vždy relevantné informácie. Nasledujúca tabuľka reprezentuje hierachiu krajov, obcí a obchodníkov:

Spôsob, ktorý dnes ukážem nebude pozostávať z komplikovaných makier. 

Tvorba hierarchie:

Najprv si rozdelíme tabuľku na viacero podtabuliek:

Taktiež sme použili pomenované oblasti pre každú podtabuľku. Inými slovami: tabuľka KRAJ obsahuje všetky existujúce kraje vrámci filtra. ďalej tabuľka Košický, obsahuje všetky mesta vrámci Košického kraja, atď. Keď budete mať rozpitvanú celú tabuľku na menšie, gratulujem - definovali ste hierarchiu.

Skladanie hierarchie:

Vytvorte nový list a do bunky, v ktorej chcete mať prvý drop-down vložte validáciu dat ( DATA - Data Validation - List - KRAJ ):

O dva stĺpce vedľa vložte pomocou predchádzajúceho postupu nasledujúcu validáciu (prostredníctvom funkcie INDIRECT odkaz na pomenovanú oblasť, ktorej názov sa nachádza v prvom drop - downe, t.j. B4 bunke):

Danú techniku aplikujeme na obchodníka a samotný výsledok si môžete skúsit sami. Problém však nastane pri kliknutí na Bankú Štiavnicu alebo Bystricu. 

Pomenovaná oblasť nám totiž nesedí so samotným názvom ( Pomenované oblasti nedovolujú používať medzerník, preto som použil "_" miesto " " ). Pripravte si podpornú tabuľku SETTINGS s prekladmi a upravte posledný dropdown použitím kombinácii funkcií VLOOKUP, IFERROR a INDIRECT:

Ďalej odporúčam hrať sa s farbami resp. zarovnaním a výsledok môže vyzerať takto:

Bonusové riešenie:

Predstavte si situáciu, že zmeníte kraj a Obec spolu s Delaerom sa vynulujú. Táto dodatočná funkcionalita je dôležitá z rôznych - prevažne konsolidačných dôvodov. Nechcete predsa filtrovať alebo ukladat nezmyselné kombinácie. Spomenutý  reset prinúti užívateľa stále zadať zvalidovanú kombináciu.


Zvoľme si napríklad bunky B2, D2 a F2 - ktoré vrámci VBA označíme ako: cells(2,2), cells(2,4) a cells(2,6). Do nich sa budú zapisovat nové hodnoty, vybrané užívatelom (odporúčam daný riadok skryť alebo zamknúť a zmeniť farbu textu na bielu).


Ďalej vložíme do VBA prostredia pre ThisWorkbook nasledujúce makro:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


If Cells(4, 2).Value <> Cells(2, 2).Value Then
        Cells(2, 2).Value = Cells(4, 2).Value
        Cells(4, 4).Value = ""
        Cells(2, 4).Value = ""
        Cells(4, 6).Value = ""
        Cells(2, 6).Value = ""
End If
If Cells(4, 4).Value <> Cells(2, 4).Value Then
        Cells(2, 4).Value = Cells(4, 4).Value
        Cells(4, 6).Value = ""
        Cells(2, 6).Value = ""End If
If Cells(4, 6).Value <> Cells(2, 6).Value Then
        Cells(2, 6).Value = Cells(4, 6).ValueEnd If


End Sub

Vzorové riešenie si môžete stiahnuť kliknutím sem.

Ako si vypočítať hypotéku – Excel

 

Táto téma je v posledných rokoch často skloňovaná. Pri istom množstve kalkulačiek apod. síce máte k dispozícii výpočet anuity. Avšak niekedy je zaujímavé si porovnať, koľko percent z anuity predstavuje platba úrokov v banke a aký je jej vývoj.

Parametre nástroja:

1) Úroková sadzba sa za celé obdobie nemení (každá kalkulačka používa danú podmienku)

2) Doba fixácie je 5 rokov

3) obdobie sa pohybuje v rozmedí od 10 do 30 rokov s 5 ročnými intervalmi

4) Úroková báza je vždy 12 mesiacov

Vzorčeky:

Na použitie výpočtu anuity slúži nasledujúci vzorček:

=PMT(Úroková sadzba/12;Obdobie*12;Cena nehnuteľnosti - Vlastné prostriedky)

Kde vám vyjde záporná hodnota, preto odporúčam to ešte prenásobiť –1-čkou. V prípade, že chceme vidieť splatnú čiastku z každej anuity individuálne, použijeme nasledujúci vzorček:

=PPMT(Úroková sadzba/12;Číslo splátky;Počet splátok celkom;Cena nehnuteľnosti -Vlastné prostriedky)

Zvyšok je už len kompilácia jednoduchých funkcií a logiky tak, aby ste pri zadávaní parametrtov dbali iba na nasledujúce:

1) nadefinovali cenu nehnuteľnosti

2) Vlastné zdroje

3) Sadzbu (p.a.)

4) Koľko rokov chcete splácať

 

Výsledok je farebne označený, tak, aby ste videli predpokladanú mesačnú splátku a o koľko nehnuteľnosť preplatíte danou hypotékou:

Súbor s individuálnou fixáciou alebo zmenou sadzby počas refixácie si môžete vyžiadať prostredníctvom správy na facebooku, stačí kliknúť na odkaz:

Excel ERP systém – úvod

 

Za posledný rok som sa vrámci Excel vývoja stretol s rôznymi požiadavkami od klientov, ktoré boli v rozmedzí priam nereálnych až po vskutku rozumné.

Čo však mali všetky tieto požiadavky spoločné je business mapping. Dovolím si tvrdiť, že 90% ľudí považuje Excel ako ”iba” editor tabuliek a grafov. Niektorí by boli najradšej za jeho neexistenciu, ale či sa nám to páči alebo nie:

- Excel je editor ktorý je užitočné ovládať

- Excel používame na správu a návrh obsahu, ktorý nám pomáha rozhodovať sa – decision making support

- Excel vylepšujeme tak, aby nám údržba obsahu zaberala čo najmenej času

V Exceli, ak je človek šikovný tak dokáže urobiť defakto malý ERP systém bez toho, aby míňal státisíce za softwareové riešenie “na mieru”.  Nastáva moment pre rozlúštenie kľúčovej otázky:

Ako zistím, že je čas pre hľadanie analytika alebo outsourcing?

Nasledujúci obrázok prezentuje stav, kedy zainteresovaná osoba investuje neúmerné množstvo času, zatiaľ čo kvalita výstupov je pri najlepšom rovnaká. Je to stav, keď firemná komplexita naberá na obrátkach a reporty už nemapujú to, čo je potrebné.

Na druhú stranu firma nie je tak veľká aby potrebovala nového analytika za tisíce € mesačne alebo software za desiatky tisíc €.

Túto otázku Vám pomôžem rozlusknúť prípadovou štúdiou jednej menšej firmy spolu s úspešne implementovaným  riešením.

Predstavte si malý podnik, založený na zákazkach, ktoré sú realizované vo forme produktov (Spotreba materiálu a ľudskej práce). Štruktúra firmy nie je komplexná. Pozostáva z 2 vlastníkov, účtovníka a riaditeľa financií v jednej osobe (ďalej len finančník) a približne 18 pracovníkov, z ktorých traja sú hlavní pracovnící, zodpovední za evidenciu dochádzky a spotreby materiálu.

Ich proces fungovania a objekty, dôležité pre mapping popisujú nasledujúce obrázky:

Pre priblíženie komplexity nasledujúca schéma zobrazuje niektoré vzťahy, potrebné k mapovaniu:

Excel audit:

Firma pôvodne disponovala niekoľkými excelmi, ktoré sa pravidelne snažili udržiavať. Vstupy do exelov zaznamenávali traja hlavní pracovníci. Analytickú časť používa “finančník” a vlastníci.

Z hľadiska excelovskej znalosti su vedúci pracovníci na začiatočníckej úrovni. Zvládajú súbor otvoriť, zadať data a uložiť súbor (Nutná podmienka je prísť s tzv. “entry and save” riešením).    Finančník má upper-intermediate level znalosti. Ovláda financie a funckie s nimi späté, dokáže si pripraviť kontingenčnú tabuľku a pokúša sa používať nahrávané makrá.

Zákazník dodal k analýze samotné súbory:

Ako sa očakávalo, attendance s veľkosťou 322 kB obsahoval všetky vstupné data. Súbor Bridge následne urobil potrebné prepočty podľa vštkých dimenzií. Súbor contracts používa dataset zo súboru bridge viackrát pre následnú analýzu.

Body, k vylepšeniu:

- Vstupy sa neukládajú do databázovej podoby. Táto vlastnosť je dôležitá, lebo vedie k efektívnejšej práci vrámci excelu a zároveň tieto data sú potom modularizované (dajú sa použiť v inej aplikácii). Náklady na čistenie dát pri implementácii podnikového systému sú potom drahšie ako samotné kopírovanie zo správnej tabuľky

- Žiadna validácia. Prierezom cez všetky excely užívateľ nemá problém s vložením a analýzou nezmyselných hodnôt. Všeobecne neexistuje horší scenár ako mať zle zmapovaný biznis.

- Používanie nahraných makier. Nahrané makrá sú veľmi senzitívne na takmer akúkoľvek úpravu v reportoch. Stačí pridať alebo odstrániť riadok a procedúry vám v lepšom prípade vrátia chybu, v horšom rozbijú celý excel. Pre efektívne používanie makier je potreba nahrané procesy rozšíriť o používanie:

Deklarácia premenných

Validačne pravidlá

Operátory: IF / ELSE / END IF, FOR / NEXT, AND / OR apod.

- Komplexita. Pribúdaním nových zákazok, zákazníkov a striedaním zamestnancov sa stáva existujúce riešenie do budúcna prakticky neovládateľné.

Takzvané “high level” riešenie by malo vyzerať nasledujúco:

Architektúra je založená na vstupných formulároch s validačnými pravidlami, do ktorých užívateľ zadá hodnoty a uloží. Následne v pozadí beží proces výpočtov a makier, ktorý hodnoty pripraví na uloženie do databázových listov. Data budú tým pádom pripravené na analýzu a prehľady. Na základe setu reportov si koncový užívateľ vyberie povolené atribúty a po kliknutí sa separátne fungujúci proces rozhodne, ktoré data z tabuliek použiť bez toho, aby sa datové tabuľky menili.

V princípe sa jedná o oddelenie datovej časi od analytickej tak, aby data boli použiteľné do budúcna alebo individuálne.

V budúcom článku sa budem podrobne venovať riešeniu z “vlastnej dielne”.